You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
104 lines
4.3 KiB
104 lines
4.3 KiB
SET QUOTED_IDENTIFIER ON
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
/*
|
|
DeleteCustomer @email='mahendrapandit10@gmail.com'
|
|
SELECT * FROM dbo.customerMaster(NOLOCK) AS CM WHERE email='mahendrapandit10@gmail.com'
|
|
SELECT * FROM dbo.customerMaster_Deleted(NOLOCK) AS CM WHERE email='mahendrapandit10@gmail.com'
|
|
|
|
*/
|
|
|
|
ALTER PROC [dbo].[DeleteCustomer](@email VARCHAR(100))
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
BEGIN
|
|
|
|
DECLARE @customerId BIGINT=NULL, @balance MONEY=NULL, @txnCnt BIGINT=0,@virtualAccount VARCHAR(50)
|
|
|
|
SELECT
|
|
@balance=CM.availableBalance,@customerId=CM.customerId ,@virtualAccount=CM.walletAccountNo
|
|
FROM dbo.customerMaster(NOLOCK) AS CM WHERE CM.email=@email
|
|
|
|
|
|
IF @customerId IS NULL
|
|
BEGIN
|
|
DECLARE @isDeleted BIT = 0
|
|
IF EXISTS(SELECT 'x' FROM dbo.CustomerMasterTemp(NOLOCK) WHERE username = @email)
|
|
BEGIN
|
|
DELETE FROM dbo.CustomerMasterTemp WHERE username = @email
|
|
SET @isDeleted = 1
|
|
END
|
|
|
|
IF EXISTS(SELECT 'x' FROM dbo.mobile_userRegistration (NOLOCK) WHERE username = @email)
|
|
BEGIN
|
|
DELETE FROM dbo.mobile_userRegistration WHERE username = @email
|
|
SET @isDeleted = 1
|
|
END
|
|
|
|
IF @isDeleted = 1
|
|
BEGIN
|
|
SELECT 0,'Customer deleted from temp or user reg' Msg, NULL Id
|
|
RETURN
|
|
END
|
|
|
|
SELECT 1,'cannot delete the customer.CustomerId is null' Msg, NULL Id
|
|
RETURN
|
|
END
|
|
|
|
IF ISNULL(@balance,0)<>0
|
|
BEGIN
|
|
SELECT 1,'cannot delete the customer. Balance Not equal to Zero' Msg, NULL Id
|
|
RETURN
|
|
END
|
|
|
|
SELECT @txnCnt=COUNT('x') FROM dbo.tranSenders(NOLOCK) AS TS WHERE TS.customerId=@customerId
|
|
IF @txnCnt>0
|
|
BEGIN
|
|
SELECT 1,'cannot delete the customer. Has done Txn' Msg, NULL Id
|
|
RETURN
|
|
END
|
|
|
|
BEGIN TRAN
|
|
|
|
INSERT INTO customerMaster_Deleted
|
|
(
|
|
customerId,membershipId,firstName,middleName,lastName1,lastName2,country,address,state,zipCode,district,city,email,homePhone,workPhone,mobile,nativeCountry,dob,placeOfIssue,customerType,
|
|
occupation,isBlackListed,createdBy,createdDate,modifiedBy,modifiedDate,approvedBy,approvedDate,isDeleted,lastTranId,relationId,relativeName,address2,fullName,postalCode,idExpiryDate,
|
|
idType,idNumber,telNo,companyName,gender,salaryRange,bonusPointPending,Redeemed,bonusPoint,todaysSent,todaysNoOfTxn,agentId,branchId,memberIDissuedDate,memberIDissuedByUser,
|
|
memberIDissuedAgentId,memberIDissuedBranchId,totalSent,idIssueDate,onlineUser,customerPassword,customerStatus,isActive,islocked,sessionId,lastLoginTs,howDidYouHear,ansText,
|
|
ansEmail,state2,ipAddress,marketingSubscription,paidTxn,firstTxnDate,verifyDoc1,verifyDoc2,verifiedBy,verifiedDate,verifyDoc3,isForcedPwdChange,bankName,bankAccountNo,walletAccountNo,
|
|
availableBalance,obpId,CustomerBankName,referelCode,isEmailVerified,verificationCode,SelfieDoc,HasDeclare,AuditDate,AuditBy,SchemeStartDate,invalidAttemptCount
|
|
)
|
|
SELECT
|
|
customerId,membershipId,firstName,middleName,lastName1,lastName2,country,address,state,zipCode,district,city,email,homePhone,workPhone,mobile,nativeCountry,dob,placeOfIssue,customerType,
|
|
occupation,isBlackListed,createdBy,createdDate,modifiedBy,modifiedDate,approvedBy,approvedDate,isDeleted,lastTranId,relationId,relativeName,address2,fullName,postalCode,idExpiryDate,
|
|
idType,idNumber,telNo,companyName,gender,salaryRange,bonusPointPending,Redeemed,bonusPoint,todaysSent,todaysNoOfTxn,agentId,branchId,memberIDissuedDate,memberIDissuedByUser,
|
|
memberIDissuedAgentId,memberIDissuedBranchId,totalSent,idIssueDate,onlineUser,customerPassword,customerStatus,isActive,islocked,sessionId,lastLoginTs,howDidYouHear,ansText,
|
|
ansEmail,state2,ipAddress,marketingSubscription,paidTxn,firstTxnDate,verifyDoc1,verifyDoc2,verifiedBy,verifiedDate,verifyDoc3,isForcedPwdChange,bankName,bankAccountNo,walletAccountNo,
|
|
availableBalance,obpId,CustomerBankName,referelCode,isEmailVerified,verificationCode,SelfieDoc,HasDeclare,AuditDate,AuditBy,SchemeStartDate,invalidAttemptCount
|
|
FROM dbo.customerMaster(NOLOCK) AS CM
|
|
WHERE CM.customerId=@customerId
|
|
|
|
DELETE FROM dbo.customerMaster WHERE customerId=@customerId
|
|
DELETE FROM dbo.mobile_userRegistration WHERE username = @email
|
|
|
|
DELETE FROM FastMoneyPro_Account.dbo.ac_master WHERE acct_num=@virtualAccount AND ISNULL(clr_bal_amt,0.0)=0.0
|
|
|
|
COMMIT TRAN
|
|
|
|
--SELECT * FROM dbo.customerMaster(NOLOCK) AS CM WHERE CM.email=@email
|
|
|
|
IF @@TRANCOUNT<>0
|
|
BEGIN
|
|
SELECT '1','Error Occured while deleting customer' Msg, NULL Id
|
|
RETURN
|
|
END
|
|
|
|
SELECT '0','Customer Deleted' Msg , NULL Id
|
|
RETURN
|
|
END
|
|
|
|
|
|
GO
|
|
|