MOBILE_API/DB/SPs/DeleteCustomer.sql

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