ALTER PROCEDURE [dbo].[USP_WG_CustomerSaveBulk]
@XMLCustomers VARCHAR(max),
@Mode NVARCHAR(50),
@ProfileID INT,
@MerchantID INT,
@LastIP NVARCHAR(40),
@Password NVARCHAR(100) = NULL,
@PasswordExpiryLimit int=0,
@IsAdminID Int=null
,@AdminID INT=null -- added by Govind
,@LogDescription NVARCHAR(max)='' -- added by Govind
AS
BEGIN
SET NOCOUNT OFF;
BEGIN TRY
BRANCH_ONE:
IF @Mode = 'insert'
BEGIN
Declare @CustTable Table(CustID Bigint,PasswordFormail varchar(100))
DECLARE @xmlDataHandle INT
DECLARE @CustomerNotSave TABLE
(
merchantid INT,
profileid INT,
loginid INT,
[password] NVARCHAR(100),
pin INT,
email NVARCHAR(100),
fname NVARCHAR(50),
lname NVARCHAR(50),
address NVARCHAR(100),
city NVARCHAR(50),
state NVARCHAR(2),
zip NVARCHAR(50),
shippingaddress NVARCHAR(100),
shippingcity NVARCHAR(100),
shippingstate NVARCHAR(100),
shippingzip NVARCHAR(100),
phone NVARCHAR(100),
cell NVARCHAR(100),
f1 NVARCHAR(100),
f2 NVARCHAR(100),
f3 NVARCHAR(100),
f4 NVARCHAR(100),
isactive BIT,
qblistid NVARCHAR(100),
lastlogin DATETIME,
lastip NVARCHAR(100),
insertedon DATETIME,
modifiedon DATETIME,
allowsms BIT,
[status] NVARCHAR(100),-- Added For Sending Status
PasswordFormail NVARCHAR(100),
ccname NVARCHAR(100),
card NVARCHAR(100),
expMonth NVARCHAR(100),
expYear NVARCHAR(100)
)
EXEC Sp_xml_preparedocument
@xmlDataHandle output,
@XMLCustomers
DECLARE @LoginID INT
DECLARE @Pin INT
DECLARE @Email NVARCHAR(200)
DECLARE @Fname NVARCHAR(100)
DECLARE @LName NVARCHAR(100)
DECLARE @Address NVARCHAR(200)
DECLARE @City NVARCHAR(100)
DECLARE @State NVARCHAR(4)
DECLARE @Zip NVARCHAR(100)
DECLARE @ShippingAddress NVARCHAR(200) = NULL
DECLARE @ShippingCity NVARCHAR(100) = NULL
DECLARE @ShippingState NVARCHAR(4) = NULL
DECLARE @ShippingZip NVARCHAR(100) = NULL
DECLARE @Phone NVARCHAR(50)
DECLARE @Cell NVARCHAR(50)
DECLARE @F1 NVARCHAR(max)
DECLARE @F2 NVARCHAR(max)
DECLARE @F3 NVARCHAR(max)
DECLARE @F4 NVARCHAR(max)
DECLARE @IsActive BIT
DECLARE @QbListID NVARCHAR(200)
DECLARE @LastLogin DATETIME
DECLARE @InsertedOn DATETIME
DECLARE @ModifiedOn DATETIME = NULL
DECLARE @AllowSMS BIT = NULL
DECLARE @xmlPassword NVARCHAR(100) = NULL
DECLARE @xmlPasswordForMail NVARCHAR(100) = NULL
DECLARE @xmlCCname NVARCHAR(100) = NULL
DECLARE @xmlCard NVARCHAR(100) = NULL
DECLARE @xmlMonth NVARCHAR(100) = NULL
DECLARE @xmlyear NVARCHAR(100) = NULL
DECLARE @XmlCCTypeID NVARCHAR(100) = NULL
DECLARE cur_insertbulkcustomer CURSOR FOR
SELECT (SELECT dbo.Getloginidforcustomer('loginid'))
,
(
SELECT dbo.Getloginidforcustomer('pin')),
Isnull(_xmlCustomer._email, ''),
_xmlCustomer._fname,
Isnull(_xmlCustomer._lname, ''),
_xmlCustomer._address,
_xmlCustomer._city,
_xmlCustomer._state,
_xmlCustomer._zip
,
_xmlCustomer._phone,
_xmlCustomer._cell,
_xmlCustomer._f1,
_xmlCustomer._f2,
_xmlCustomer._f3,
_xmlCustomer._f4,
_xmlCustomer._isactive,
_xmlCustomer._qblistid,
_xmlCustomer._lastlogin,
Getdate(),
_xmlCustomer._password,
_xmlCustomer._PasswordFormail,
_xmlCustomer._Ccname,
_xmlCustomer._card,
_xmlCustomer._expyear,
_xmlCustomer._expmonth,
_xmlCustomer._CCTypeID
FROM OPENXML(@xmlDataHandle, '/CustomerData/Customer', 2)
WITH (
_loginid INT '@LoginID'
,
_pin INT '@Pin',
_email NVARCHAR(100) '@Email',
_fname NVARCHAR(50) '@FName',
_lname NVARCHAR(50) '@LName',
_address NVARCHAR(100) '@Address',
_city NVARCHAR(50) '@City',
_state NVARCHAR(2) '@State',
_zip NVARCHAR(50) '@Zip'
,
_phone NVARCHAR(25) '@Phone',
_cell NVARCHAR(25) '@Cell',
_f1 NVARCHAR(max) '@F1',
_f2 NVARCHAR(max) '@F2',
_f3 NVARCHAR(max) '@F3',
_f4 NVARCHAR(max) '@F4',
_isactive BIT '@IsActive',
_qblistid NVARCHAR(100) '@QbListID',
_lastlogin DATETIME '@LastLogin',
_insertedon DATETIME '@InsertedOn',
_password NVARCHAR(100) '@xmlPassword' ,
_PasswordFormail nvarchar(100) '@PasswordFormail',
--_Ccname nvarchar(100) '@CCname ',
_Ccname nvarchar(100) '@xmlCCname',
_card nvarchar(100) '@xmlCard',
-- _expyear nvarchar(100) '@ExpMonth ',
_expyear nvarchar(100) '@xmlExpMonth',
-- _expmonth nvarchar(100) '@ExpYear ',
_expmonth nvarchar(100) '@xmlExpYear',
_CCTypeID nvarchar(100) '@CardTypeID'
)_xmlCustomer
OPEN cur_insertbulkcustomer
FETCH next FROM cur_insertbulkcustomer INTO @LoginID, @Pin, @Email, @Fname , @LName, @Address, @City, @State, @Zip, @Phone, @Cell, @F1, @F2, @F3,
@F4, @IsActive, @QbListID, @LastLogin, @InsertedOn,@xmlPassword,@xmlPasswordForMail,@XmlCCname,@xmlCard,@xmlMonth,@xmlyear,@XmlCCTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @CustID INT
SELECT @CustID = dbo.Udf_iscustomerexistbyfnamelname(@Fname, @LName, @MerchantID)
IF @CustID = 0
BEGIN
INSERT INTO customer
(merchantid,
profileid,
loginid,
[password],
pin,
email,
fname,
lname,
address,
city,
state,
zip,
shippingaddress,
shippingcity,
shippingstate,
shippingzip,
phone,
cell,
f1,
f2,
f3,
f4,
isactive,
qblistid,
lastlogin,
lastip,
insertedon,
modifiedon,
allowsms,
AdminID)
VALUES (@MerchantID,
@Profileid,
@LoginID,
@xmlPassword,
@Pin,
@Email,
@Fname,
@LName,
@Address,
@City,
@State,
@Zip,
@ShippingAddress,
@ShippingCity,
@ShippingState,
@ShippingZip,
@Phone,
@Cell,
@F1,
@F2,
@F3,
@F4,
'true',
@QbListID,
@LastLogin,
@LastIP,
Getdate(),
@ModifiedOn,
@AllowSMS,
@IsAdminID)
--Start code by ram on 28 Jan 2014 to add Password request in log
declare @CustomerID int
SET @CustomerID = SCOPE_IDENTITY()
if @xmlCard<> ''
begin
exec [USP_WG_CustomerCardForBulkUpload] @customerID=@CustomerID,@nameOnCard=@XmlCCname,@CardTypeID=@XmlCCTypeID,@card=@xmlCard,
@ExpMonth=@xmlMonth, @ExpYear=@xmlyear,@Address=@Address,@Zip=@Zip,@LastIP=@LastIP,@Mode='Insert',@Default=0,
@IsBilling=0
end
exec USP_WG_InsertInPwdLog @xmlPassword,@CustomerID,'Customer',@PasswordExpiryLimit
insert into @CustTable (CustID,PasswordFormail)values(@CustomerID,@xmlPasswordForMail)
--End code by ram on 28 Jan 2014 to add Password request in log
-- CRD 185 Admin Logs (Changes by Govind) Start
SET @LogDescription = @LogDescription +'Customer Name :'+@Fname+' '+ @LName
-- CRD 185 Admin Logs (Changes by Govind) END
END
ELSE
BEGIN
INSERT INTO @CustomerNotSave
(merchantid,
profileid,
loginid,
[password],
pin,
email,
fname,
lname,
address,
city,
state,
zip,
shippingaddress,
shippingcity,
shippingstate,
shippingzip,
phone,
cell,
f1,
f2,
f3,
f4,
isactive,
qblistid,
lastlogin,
lastip,
insertedon,
modifiedon,
allowsms,
[status],
ccname,
card ,
expMonth,
expYear
)
VALUES (@MerchantID,
@Profileid,
@LoginID,
@Password,
@Pin,
@Email,
@Fname,
@LName,
@Address,
@City,
@State,
@Zip,
@ShippingAddress,
@ShippingCity,
@ShippingState,
@ShippingZip,
@Phone,
@Cell,
@F1,
@F2,
@F3,
@F4,
@IsActive,
@QbListID,
@LastLogin,
@LastIP,
Getdate(),
@ModifiedOn,
@AllowSMS,
'Customer Already Exists.',
@xmlCCname,
@xmlCard ,
@xmlMonth ,
@xmlyear
)
END
--if @xmlCard<> ''
-- begin
-- exec USP_WG_CustomerCardForBulkUpload @customerID=@CustID,@nameOnCard=@XmlCCname,@CardTypeID=1,@card=@xmlCard,
-- @ExpMonth=@xmlMonth, @ExpYear=@xmlyear,@Address=@Address,@Zip=@Zip,@LastIP=@LastIP,@Mode='Insert',@Default=0,
-- @IsBilling=0
-- end
FETCH next FROM cur_insertbulkcustomer INTO @LoginID, @Pin,
@Email,
@Fname
,
@LName, @Address, @City, @State, @Zip, @Phone, @Cell, @F1, @F2
,
@F3,
@F4,
@IsActive, @QbListID, @LastLogin, @InsertedOn,@xmlPassword,@xmlPasswordForMail,@XmlCCname,@xmlCard,@xmlMonth,@xmlyear,@XmlCCTypeID
END
CLOSE cur_insertbulkcustomer
DEALLOCATE cur_insertbulkcustomer
-- CRD 185 Admin Logs (Changes by Govind) Start
IF(@AdminID <> 0)
BEGIN
SET @LogDescription = 'Performed Customer BULK Add.' + @LogDescription
EXEC USP_WG_RecordAdminActivity @CustomerID,5,@AdminID,@MerchantID,@ProfileID,'Customer','BulkInsert',@LogDescription,'Customer',NULL,NULL
END
-- CRD 185 Admin Logs (Changes by Govind) END
SELECT fname AS 'First Name',
lname AS 'Last Name',
address,
city,
state,
zip AS 'Zip Code',
phone AS 'Phone Home',
cell AS 'Phone Mobile',
email AS 'Email ID',
status,
password ,
'' as xmlPassword ,
'' as PasswordFormail ,
ccname as CCname,
expMonth as ExpMonth,
--'' as ExpMonth,
card as CardNum,
expYear as Expyear,
--'' as Expyear,
'' as CardType,
'' as CardTypeID,
'' Xmlcard,
--'' as Xmlcard,
'' as xmlCCname,
'' as xmlExpMonth,
'' as xmlExpYear,
'' as RSACard
FROM @CustomerNotSave
Select * from Customer C join @CustTable Ct on c.CustomerID =ct.CustID
return 1
END
This Stored Procedure has being written using OpenXml.Now, I have to Use Nodes() Method instead of OpenXml
Midhun TpPosted Sep 20, 2016, 12:14 AM