IF OBJECT_ID('pj_ESFB_LMSSMS_Alert','P')IS NOT NULL
BEGIN 
	DROP PROCEDURE pj_ESFB_LMSSMS_Alert
END
GO 
CREATE PROCEDURE pj_ESFB_LMSSMS_Alert
(
	@JobID		nVarchar(50) = NULL
)
AS
BEGIN 
	SET NOCOUNT ON

--DECLARE @JobID		nVarchar(50) = NULL
	
	DECLARE @StartTime	Datetime
	DECLARE @Endtime	Datetime
	DECLARE @ErrorTime	Datetime
	DECLARE	@ErrorMsg	nVarchar(max)
	
	CREATE TABLE #BranchDate
	(
		OurBranchID		nVarchar(10) ,
		NthWorkingDate	SmallDateTime ,
		WorkingDate		SmallDateTime ,
		BranchName		nVarchar(100),
		CityName		nVarchar(300),
		LanguageID      nVarchar(3)
	)
	
	CREATE TABLE #ESB_AlertMsgLog
	(
		OurBranchID			nVarchar(6)	,
		ApplicationID		nVarchar(20),
		AccountID			nVarchar(20),
		Loanseries			SmallInt,
		TrxDate				SmallDateTime,
		EventID				nVarchar(25),
		StageID				nVarchar(25),
		SMSKey				nVarchar(max),
		ClientID			nVarchar(20),
		MobileNo			nVarchar(30),
		LanguageID			nVarchar(3),
		LoanAmount			Numeric(18,2),
		Name				nVarchar(300),
		AlertMessage		nVarchar(Max)	,
		TrxBatchID			VARCHAR(8)		,
		ReversedAmount		Money	,
		ReversalDate		smalldatetime	,
		CreatedOn           Datetime,
		CollectedAmount     Money			,
		CollectionDate      Smalldatetime	,	
		BankAccountNo		nVarchar(25)	,
		MeetingTime			nVarchar(25)	
		
		
	)
	
		INSERT INTO #BranchDate
		SELECT t_SystemBranchStatus.OurbranchID,dbo.f_GetNextNthWorkingDate(t_SystemBranchStatus.OurBranchID,SODDATE,3),SODDATE,t_SystemBranchSetting.BranchName,dbo.fn_GetUserCodedesc('CITYID',t_SystemBranchSetting.CityID)
		,ISNULL(t_SystemBranchSetting.LanguageID,'EN') LanguageID
		FROM t_SystemBranchStatus
		INNER JOIN t_SystemBranchSetting
		ON t_SystemBranchStatus.OurBranchID	=	t_SystemBranchSetting.OurBranchID
		AND t_SystemBranchSetting.BranchStatusID='A'
	
		SELECT @StartTime			= LastRunStartTime,
				@Endtime			= LastRunEndTime
		FROM t_JobConfiguration
		WHERE JobID					=	@JobID

		IF (@StartTime IS NOT NULL AND @Endtime  IS NULL)
		BEGIN
			GOTO EndStatment;
		END
		
		UPDATE t_JobConfiguration
		SET LastRunStartTime	=	GETDATE(),
			LastRunEndTime		=	 NULL
		WHERE JobID			=	@JobID
			   
		DECLARE @AlertMessage			nVarchar(Max)
		DECLARE @EventID				nVarchar(25)
		DECLARE @BankID					BankID
		
		SELECT @BankID			 =	 BankID
		FROM t_SystemBankSetting
		
		BEGIN TRY 

		

		-------FUNDTRANSFER----------

		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
				  WHERE EventID = 'FUNDTRANSFER')
		BEGIN
			
			 INSERT INTO #ESB_AlertMsgLog
			 (
			  OurBranchID,
			  ApplicationID,
			  ClientID,
			  AccountID,
			  BankAccountNo,
			  LoanAmount,
			  TrxDate,
			  EventID,
			  SMSKey,
			  LanguageID		 
			 )
			 SELECT   t_ClientBankTransaction.OurBranchID,
					  t_ClientBankTransaction.ApplicationID,
					  t_ClientBankTransaction.ClientID,
					  t_ClientBankTransaction.LoanAccountID,
					  t_ClientBankTransaction.AccountID,
					  t_ClientBankTransaction.TrxAmount,
					  t_ClientBankTransaction.TrxDate,
					  'FUNDTRANSFER',
					  t_ClientBankTransaction.OurBranchID + t_ClientBankTransaction.ApplicationID+ CONVERT(VARCHAR,t_ClientBankTransaction.TrxDate,106),
					  #BranchDate.LanguageID  
			 FROM t_ClientBankTransaction WITH(NOLOCK)
			 INNER JOIN #BranchDate
			 ON #BranchDate.OurBranchID			  =	t_ClientBankTransaction.OurBranchID
			 AND #BranchDate.WorkingDate          = t_ClientBankTransaction.TrxDate
			 WHERE t_ClientBankTransaction.RecordStatusID = 'A'
			 AND t_ClientBankTransaction.TrxStatusID      = 'COM'
			 AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog  WITH(NOLOCK)
						WHERE t_ESB_AlertMsgLog.OurBranchID	= t_ClientBankTransaction.OurBranchID
						AND t_ESB_AlertMsgLog.SMSKey		= t_ClientBankTransaction.OurBranchID + t_ClientBankTransaction.ApplicationID+ CONVERT(VARCHAR,t_ClientBankTransaction.TrxDate,106) 
						AND t_ESB_AlertMsgLog.EventID		= 'FUNDTRANSFER'
					--	AND t_ESB_AlertMsgLog.LanguageID	=  #BranchDate.LanguageID
						)

			 
			UPDATE 	#ESB_AlertMsgLog
			SET #ESB_AlertMsgLog.AlertMessage	=	REPLACE(REPLACE(REPLACE(REPLACE(t_ESB_AlertFormat.MessageFormat,'{Amount}',CAST(ISNULL(#ESB_AlertMsgLog.LoanAmount,0.00)AS nVarchar(20))),'{Date}'
			, ISNULL(CONVERT(VARCHAR,#ESB_AlertMsgLog.TrxDate,106),'')),'{application}', ISNULL(#ESB_AlertMsgLog.ApplicationID,0)),'{AccountNo}',ISNULL(CAST(REPLICATE('X',LEN(#ESB_AlertMsgLog.BankAccountNo) - 4) AS NVARCHAR(30)) + RIGHT(#ESB_AlertMsgLog.BankAccountNo,4),''))
			FROM #ESB_AlertMsgLog
			INNER JOIN t_ESB_AlertFormat 
			ON #ESB_AlertMsgLog.EventID			=  t_ESB_AlertFormat.EventID
			AND #ESB_AlertMsgLog.LanguageID		=  t_ESB_AlertFormat.LanguageID
			AND t_ESB_AlertFormat.EventID		= 'FUNDTRANSFER'

		END

		---Disbursement Failure
		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
				  WHERE EventID = 'DISBFAIL')
		BEGIN
			
			 INSERT INTO #ESB_AlertMsgLog
			 (
			  OurBranchID,
			  ApplicationID,
			  ClientID,
			  AccountID,
			  BankAccountNo,
			  LoanAmount,
			  TrxDate,
			  EventID,
			  SMSKey,
			  LanguageID		 
			 )
			 SELECT   t_ClientBankTransaction.OurBranchID,
					  t_ClientBankTransaction.ApplicationID,
					  t_ClientBankTransaction.ClientID,
					  t_ClientBankTransaction.LoanAccountID,
					  t_ClientBankTransaction.AccountID,
					  t_ClientBankTransaction.TrxAmount,
					  t_ClientBankTransaction.TrxDate,
					  'DISBFAIL',
					  t_ClientBankTransaction.OurBranchID + t_ClientBankTransaction.ApplicationID+ CONVERT(VARCHAR,t_ClientBankTransaction.TrxDate,106) ,
					  #BranchDate.LanguageID  
			 FROM t_ClientBankTransaction WITH(NOLOCK)
			 INNER JOIN #BranchDate
			 ON #BranchDate.OurBranchID			  =	t_ClientBankTransaction.OurBranchID
			 AND #BranchDate.WorkingDate          = t_ClientBankTransaction.TrxDate
			 WHERE t_ClientBankTransaction.RecordStatusID = 'A'
			 AND t_ClientBankTransaction.TrxStatusID      IN ('ERR','ERA')
			 AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog  WITH(NOLOCK)
						WHERE t_ESB_AlertMsgLog.OurBranchID	= t_ClientBankTransaction.OurBranchID
						AND t_ESB_AlertMsgLog.SMSKey		= t_ClientBankTransaction.OurBranchID + t_ClientBankTransaction.ApplicationID+ CONVERT(VARCHAR,t_ClientBankTransaction.TrxDate,112) 
						AND t_ESB_AlertMsgLog.EventID		= 'DISBFAIL'
					--	AND t_ESB_AlertMsgLog.LanguageID	=  #BranchDate.LanguageID
						)

			UPDATE     #ESB_AlertMsgLog
            SET #ESB_AlertMsgLog.AlertMessage		=    REPLACE(REPLACE (REPLACE(t_ESB_AlertFormat.MessageFormat,'{LoanAmount}',CAST(ISNULL(#ESB_AlertMsgLog.LoanAmount,0.00)AS nVarchar(20)))
			,' {AccountNo}', ISNULL(#ESB_AlertMsgLog.AccountID,0)),'{SBAccountNo}',ISNULL(CAST(REPLICATE('X',LEN(#ESB_AlertMsgLog.BankAccountNo)- 4) AS NVARCHAR(30)) + RIGHT(#ESB_AlertMsgLog.BankAccountNo,4),''))
            FROM #ESB_AlertMsgLog
            INNER JOIN t_ESB_AlertFormat
            ON #ESB_AlertMsgLog.EventID				=  t_ESB_AlertFormat.EventID
            AND #ESB_AlertMsgLog.LanguageID			=  t_ESB_AlertFormat.LanguageID
            AND t_ESB_AlertFormat.EventID			= 'DISBFAIL'

		END



		--Collection Intimation
		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
				  WHERE EventID = 'COLINI')
		BEGIN
			 INSERT INTO #ESB_AlertMsgLog
			 (
			  OurBranchID,
			  AccountID,			   
			  LoanAmount,
			  TrxDate,
			  ApplicationID,
			  EventID,
			  SMSKey,
			  LanguageID		 
			 )
			 SELECT t_LoanInstallment.OurBranchID,
			        t_LoanInstallment.AccountID,
					t_LoanInstallment.InstallmentAmount,
					t_LoanInstallment.InstallmentDueDate,
					 ApplicationID,
					'COLINI',
					t_LoanInstallment.OurBranchID + t_LoanInstallment.AccountID + CAST(t_LoanInstallment.LoanSeries AS nvarchar(10)),
					#BranchDate.LanguageID  
			 FROM t_LoanInstallment
			 INNER JOIN #BranchDate
			 ON #BranchDate.OurBranchID			= t_LoanInstallment.OurBranchID
			 INNER JOIN t_Loan
			 ON  t_Loan.OurBranchID				= t_LoanInstallment.OurBranchID
			 AND t_Loan.AccountID				= t_LoanInstallment.AccountID
			 AND t_Loan.LoanSeries				= t_LoanInstallment.LoanSeries
			 AND T_LOAN.LoanStatusID			IN ('A','N')
--			 WHERE t_LoanInstallment.InstallmentDueDate		= DATEADD (DAY,3,CAST(#BranchDate.WorkingDate AS DATE)) 
			 WHERE t_LoanInstallment.InstallmentDueDate		= DATEADD (DAY,3,CAST(GETDATE() AS DATE)) 
			 AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog  WITH(NOLOCK)
						WHERE t_ESB_AlertMsgLog.OurBranchID	= t_LoanInstallment.OurBranchID
						AND t_ESB_AlertMsgLog.SMSKey		= t_LoanInstallment.OurBranchID + t_LoanInstallment.AccountID + CAST(t_LoanInstallment.LoanSeries AS nvarchar(10)) 
						AND t_ESB_AlertMsgLog.EventID		= 'COLINI'
						--AND t_ESB_AlertMsgLog.LanguageID	=  #BranchDate.LanguageID
						)

			 UPDATE #ESB_AlertMsgLog
			 SET MeetingTime = t_Group.MeetingTime
			 FROM #ESB_AlertMsgLog
			 INNER JOIN t_AccountCustomer WITH (NOLOCK)
			 ON  #ESB_AlertMsgLog.OurbranchID		= t_AccountCustomer.OurBranchID
			 AND #ESB_AlertMsgLog.AccountID			= t_AccountCustomer.AccountID
			 INNER JOIN t_GroupMember WITH (NOLOCK)
			 ON  t_AccountCustomer.OurbranchID		= t_GroupMember.OurBranchID
			 AND t_AccountCustomer.ClientID			= t_GroupMember.ClientID
			 INNER JOIN t_Group WITH (NOLOCK)
			 ON  t_GroupMember.OurBranchID			= t_Group.OurBranchID
			 AND t_GroupMember.GroupID				= t_Group.GroupID
			 WHERE #ESB_AlertMsgLog.OurBranchID		= t_AccountCustomer.OurBranchID
			 AND #ESB_AlertMsgLog.AccountID			= t_AccountCustomer.AccountID
			 AND #ESB_AlertMsgLog.EventID			= 'COLINI'
			  
			UPDATE 	#ESB_AlertMsgLog
			SET #ESB_AlertMsgLog.AlertMessage	=	REPLACE(REPLACE (REPLACE(t_ESB_AlertFormat.MessageFormat,'{Amount}',CAST(ISNULL(#ESB_AlertMsgLog.LoanAmount,0.00)AS nVarchar(20))),'{Date}', ISNULL(CONVERT(VARCHAR,#ESB_AlertMsgLog.TrxDate,106),'')),'{Time}'
			,ISNULL(#ESB_AlertMsgLog.MeetingTime,''))
			FROM #ESB_AlertMsgLog
			INNER JOIN t_ESB_AlertFormat 
			ON #ESB_AlertMsgLog.EventID			=  t_ESB_AlertFormat.EventID
			AND #ESB_AlertMsgLog.LanguageID		=  t_ESB_AlertFormat.LanguageID
			AND t_ESB_AlertFormat.EventID		= 'COLINI'

		END 

		--Collection Reversal
		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
				  WHERE EventID = 'COLRVE')
		BEGIN   	
			INSERT INTO #ESB_AlertMsgLog
			(
				OurBranchID ,                
				AccountID   ,
				LoanSeries  ,       
				CreatedOn   ,             
				CollectedAmount,
				CollectionDate,
				EventID,
				TrxBatchID,
				TrxDate ,
				SMSKey,
				LanguageID  
			)
			SELECT                   
				t_Transaction.OurBranchID,
				t_Transaction.AccountID,
				t_Transaction.LoanSeries,
				GETDATE(),
				ABS(SUM(t_Transaction.Amount)),
				t_Transaction.TrxDate,
				'COLRVE',
				t_Transaction.TrxBatchID,
				t_Transaction.TrxDate,
				t_Transaction.OurBranchID+t_Transaction.AccountID+CAST(t_Transaction.LoanSeries AS nVarchar(10))+CONVERT(VARCHAR,t_Transaction.TrxDate,106),
				#BranchDate.LanguageID   
			FROM t_Transaction WITH(NOLOCK)
			INNER JOIN #BranchDate	
			ON #BranchDate.OurBranchID	=	t_Transaction.OurBranchID
			INNER JOIN t_ProductLoan WITH(NOLOCK)
			ON t_Transaction.ProductID	= t_ProductLoan.ProductID
			AND t_ProductLoan.BankID	= @BankID
			WHERE DeletedOn IS NULL
				--AND t_Transaction.OurBranchID NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
				AND TrxFlagID = ''
				AND AccountTypeID = 'C'
				AND TrxAmount < 0  ----
				AND T_Transaction.TrxDate = #BranchDate.WorkingDate
				AND ModuleID IN (3116,3117,3096) 
				AND NOT EXISTS(		SELECT 1
									FROM t_ESB_AlertMsgLog WITH(NOLOCK)
									WHERE t_ESB_AlertMsgLog.OurBranchID		=	t_Transaction.OurBranchID
									AND t_ESB_AlertMsgLog.EventID		    = 'COLRVE'
									AND t_ESB_AlertMsgLog.SMSKey		    = t_Transaction.OurBranchID+t_Transaction.AccountID+CAST(t_Transaction.LoanSeries AS nVarchar(10))+CONVERT(VARCHAR,t_Transaction.TrxDate,112)
									
								) 
			GROUP BY t_Transaction.OurBranchID,
					 t_Transaction.AccountID,
					 t_Transaction.LoanSeries,			
					 t_Transaction.TrxDate,
					 t_Transaction.TrxBatchID,
					 #BranchDate.LanguageID  


			UNION 

				SELECT                   
				t_ExternalCollectHistory.BrnetBranchID,
				t_ExternalCollectHistory.AccountID,
				0,
				GETDATE(),
				ABS(SUM(t_ExternalCollectHistory.DemandAmount)),
				t_ExternalCollectHistory.TrxDate,
				'COLRVE',
				t_ExternalCollectHistory.TrxBatchID,
				t_ExternalCollectHistory.TrxDate,
				t_ExternalCollectHistory.BrnetBranchID+t_ExternalCollectHistory.AccountID+CONVERT(VARCHAR,t_ExternalCollectHistory.TrxDate,106),
				#BranchDate.LanguageID   
			FROM t_ExternalCollectHistory WITH(NOLOCK)
			INNER JOIN #BranchDate	
			ON #BranchDate.OurBranchID	=	t_ExternalCollectHistory.BrnetBranchID
			WHERE t_ExternalCollectHistory.TrxDate = #BranchDate.WorkingDate
			AND NOT EXISTS(	SELECT 1
							FROM t_ESB_AlertMsgLog WITH(NOLOCK)
							WHERE t_ESB_AlertMsgLog.OurBranchID		=	t_ExternalCollectHistory.BrnetBranchID
							AND t_ESB_AlertMsgLog.EventID		    = 'COLRVE'
							AND t_ESB_AlertMsgLog.SMSKey		    = t_ExternalCollectHistory.BrnetBranchID+t_ExternalCollectHistory.AccountID+CONVERT(VARCHAR,t_ExternalCollectHistory.TrxDate,112)
						) 
			GROUP BY t_ExternalCollectHistory.BrnetBranchID,
					 t_ExternalCollectHistory.AccountID,
					 t_ExternalCollectHistory.TrxDate,
					 t_ExternalCollectHistory.TrxBatchID,
					 #BranchDate.LanguageID  
			

				
			UPDATE 	#ESB_AlertMsgLog
			SET #ESB_AlertMsgLog.AlertMessage	=  REPLACE(REPLACE(REPLACE(t_ESB_AlertFormat.MessageFormat,'{AccountNo}',ISNULL(#ESB_AlertMsgLog.AccountID,'')),'{Date}',CONVERT(nvarchar(10),ISNULL(#ESB_AlertMsgLog.TrxDate,''),106)),'{Amount}'
			,CAST(ISNULL(#ESB_AlertMsgLog.CollectedAmount,0.00)AS nVarchar(20)))
			FROM #ESB_AlertMsgLog
			INNER JOIN t_ESB_AlertFormat
			ON #ESB_AlertMsgLog.EventID			=	t_ESB_AlertFormat.EventID
			AND #ESB_AlertMsgLog.LanguageID		=	t_ESB_AlertFormat.LanguageID
			AND t_ESB_AlertFormat.EventID		= 'COLRVE'
	       
				
		END

		--disb Reversal

		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
					WHERE EventID = 'DISPREV')
		BEGIN
			INSERT INTO #ESB_AlertMsgLog
				(
					OurBranchID,
					AccountID,
					LoanSeries,
					CreatedOn,
					ReversalDate,
					EventID,
					TrxBatchID,
					TrxDate,
					SMSKey,
					LanguageID      
				)
			SELECT	
				t_LoanReversal.OurBranchID,
				t_LoanReversal.AccountID,
				t_LoanReversal.LoanSeries,
				GETDATE(),
				t_LoanReversal.ReversalDate,
				'DISPREV',
				t_LoanReversal.TrxBatchID,
				t_LoanReversal.TrxDate,
				t_LoanReversal.OurBranchID+t_LoanReversal.AccountID+CAST(t_LoanReversal.LoanSeries AS nVarchar(10))+CONVERT(VARCHAR,t_LoanReversal.TrxDate,106),
				#BranchDate.LanguageID      
			FROM  t_LoanReversal WITH( NOLOCK)
			INNER JOIN #BranchDate	
			ON #BranchDate.OurBranchID	=	t_LoanReversal.OurBranchID	
			WHERE t_LoanReversal.TrxDate	=	#BranchDate.WorkingDate
			--AND	  t_LoanReversal.OurBranchID NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
			AND	  t_LoanReversal.ReversalTypeID	IN ('D','L')--(D,L)
			AND	  NOT EXISTS(
								SELECT 1 
								FROM t_ESB_AlertMsgLog WITH(NOLOCK)	
								WHERE t_ESB_AlertMsgLog.OurBranchID = t_LoanReversal.OurBranchID	
								AND t_ESB_AlertMsgLog.SMSKey		= t_LoanReversal.OurBranchID+t_LoanReversal.AccountID+CAST(t_LoanReversal.LoanSeries AS nVarchar(10))+CONVERT(VARCHAR,t_LoanReversal.TrxDate,112)		
								AND	t_ESB_AlertMsgLog.EventID		= 'DISPREV'		
							)	
							
			UPDATE #ESB_AlertMsgLog
			SET  LoanAmount    = BookedAmount,
			     ApplicationID = t_Loan.ApplicationID
			FROM t_Loan
			WHERE t_Loan.OurBranchID	= #ESB_AlertMsgLog.OurBranchID
			AND t_Loan.AccountID		= #ESB_AlertMsgLog.AccountID
			AND t_Loan.LoanSeries		= #ESB_AlertMsgLog.LoanSeries
			--AND t_Loan.TrxBatchID		= #ESB_AlertMsgLog.TrxBatchID			
			
			
			UPDATE 	#ESB_AlertMsgLog
			SET #ESB_AlertMsgLog.AlertMessage	=	REPLACE(REPLACE(REPLACE(t_ESB_AlertFormat.MessageFormat,'{Amount}',CAST(ISNULL(#ESB_AlertMsgLog.LoanAmount,0.00)AS nVarchar(20))),'{application}', ISNULL(#ESB_AlertMsgLog.ApplicationID,0)),'{AccountNo}'
			,ISNULL(CAST(REPLICATE('X',LEN(#ESB_AlertMsgLog.AccountID) - 4) AS NVARCHAR(30)) + RIGHT(#ESB_AlertMsgLog.AccountID,4),''))
			FROM #ESB_AlertMsgLog
			INNER JOIN t_ESB_AlertFormat 
			ON #ESB_AlertMsgLog.EventID			=  t_ESB_AlertFormat.EventID
			AND #ESB_AlertMsgLog.LanguageID		=  t_ESB_AlertFormat.LanguageID
			AND t_ESB_AlertFormat.EventID		= 'DISPREV'
				
			
		END	

			
		
		--LOAN CLOSURE 
		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
					WHERE EventID = 'LOANCLOSURE')
		BEGIN
			INSERT INTO #ESB_AlertMsgLog
			(
				OurBranchID			,
				AccountID			,
				LoanSeries			,
				ClientID,
				TrxDate				,
				EventID				,
				SMSKey				,
				LanguageID      	
			)
			SELECT 
					t_Loan.OurBranchID			,
					t_Loan.AccountID			,
					t_Loan.LoanSeries			,
					t_AccountCustomer.ClientID	,
					t_Loan.ClosedDate			,
					'LOANCLOSURE'				,
					t_Loan.OurBranchID+t_Loan.AccountID+CAST(t_Loan.LoanSeries AS nVarchar(10))+CONVERT(VARCHAR,t_Loan.ClosedDate,106),
					#BranchDate.LanguageID
			FROM t_Loan WITH(NOLOCK)
			INNER JOIN #BranchDate
			ON t_Loan.OurBranchID	=	#BranchDate.OurBranchID
			INNER JOIN t_AccountCustomer WITH(NOLOCK)
			ON t_Loan.OurBranchID	=	t_AccountCustomer.OurBranchID
			AND t_Loan.AccountID	=	t_AccountCustomer.AccountID
			INNER JOIN t_Client	WITH(NOLOCK)
			ON t_Client.ClientID	=	t_AccountCustomer.ClientID
			WHERE t_Loan.ClosedDate	=	#BranchDate.WorkingDate
			AND t_Loan.LoanStatusID	IN ('F','I','P','O')
			AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog
							WHERE t_ESB_AlertMsgLog.OurBranchID		=	t_Loan.OurBranchID
							AND t_ESB_AlertMsgLog.SMSKey			=	t_Loan.OurBranchID+t_Loan.AccountID+CAST(t_Loan.LoanSeries AS nVarchar(10))+CONVERT(VARCHAR,t_Loan.ClosedDate,112)
							AND t_ESB_AlertMsgLog.EventID			=	'LOANCLOSURE'
							--AND t_ESB_AlertMsgLog.LanguageID		=	#BranchDate.LanguageID
							)
		END
						
		--COLLECTION
		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
					WHERE EventID = 'COLLECTION')
		BEGIN
			INSERT INTO #ESB_AlertMsgLog
			(
				OurBranchID			,
				AccountID			,
				LoanSeries			,
				TrxDate				,
				LoanAmount			,
				EventID				,
				SMSKey				,
				LanguageID      	
			)	
			SELECT                   
				t_Transaction.OurBranchID,
				t_Transaction.AccountID,
				t_Transaction.LoanSeries,
				t_Transaction.TrxDate,
				SUM(t_Transaction.Amount),              
				'COLLECTION',
				t_Transaction.OurBranchID+t_Transaction.AccountID+CAST(t_Transaction.LoanSeries AS nVarchar(10))+CONVERT(VARCHAR,t_Transaction.TrxDate,106)+CAST(t_Transaction.TrxBatchID AS nVarchar(10)),
				#BranchDate.LanguageID
			FROM t_Transaction WITH(NOLOCK)
			INNER JOIN #BranchDate
			ON t_Transaction.OurBranchID	=	#BranchDate.OurBranchID
			INNER JOIN t_ProductLoan WITH(NOLOCK)
			ON t_Transaction.ProductID	= t_ProductLoan.ProductID
			AND t_ProductLoan.BankID	= @BankID
			WHERE DeletedOn IS NULL
				AND TrxFlagID = ''
				AND AccountTypeID = 'C'
				AND TrxAmount > 0  ----
				AND T_Transaction.TrxDate = #BranchDate.WorkingDate
				AND ModuleID IN (3110,3109,3106,5281,8529,3000)
				AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog
								WHERE t_ESB_AlertMsgLog.OurBranchID		=	t_Transaction.OurBranchID
								AND t_ESB_AlertMsgLog.SMSKey			=	t_Transaction.OurBranchID+t_Transaction.AccountID+CAST(t_Transaction.LoanSeries AS nVarchar(10))+CONVERT(VARCHAR,t_Transaction.TrxDate,112)+CAST(t_Transaction.TrxBatchID AS nVarchar(10))
								AND t_ESB_AlertMsgLog.EventID			=	'COLLECTION'
								--AND t_ESB_AlertMsgLog.LanguageID		=	#BranchDate.LanguageID
								)		
			GROUP BY t_Transaction.OurBranchID,
					 t_Transaction.AccountID,
					 t_Transaction.LoanSeries,			
					 t_Transaction.TrxDate,
					 t_Transaction.TrxBatchID	,
					 #BranchDate.BranchName		,
					 #BranchDate.LanguageID			

			UNION
			SELECT	T_ExternalCollection.AccountBranchID,
					T_ExternalCollection.AccountID, 
					0 LoanSeries,
					GETDATE(),
					SUM(T_ExternalCollection.TrxAmount),
					'COLLECTION',
					T_ExternalCollection.AccountBranchID+T_ExternalCollection.AccountID+CONVERT(VARCHAR,T_ExternalCollection.TrxDate,112),
					#BranchDate.LanguageID
			FROM T_ExternalCollection ,#BranchDate 
			WHERE T_ExternalCollection.TrxDate = #BranchDate.WorkingDate
			AND T_ExternalCollection.AccountBranchID = #BranchDate.OurBranchID
			AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog
							WHERE t_ESB_AlertMsgLog.OurBranchID = T_ExternalCollection.AccountBranchID
							AND t_ESB_AlertMsgLog.SMSKey        = T_ExternalCollection.AccountBranchID+T_ExternalCollection.AccountID+CONVERT(VARCHAR,T_ExternalCollection.TrxDate,112)
							AND t_ESB_AlertMsgLog.EventID		= 'COLLECTION')
			GROUP BY	T_ExternalCollection.AccountBranchID,
						T_ExternalCollection.AccountID,
						T_ExternalCollection.BRClientID,
						T_ExternalCollection.TrxDate,
						#BranchDate.WorkingDate,
						#BranchDate.LanguageID
		END



					
		UPDATE 	#ESB_AlertMsgLog
		SET #ESB_AlertMsgLog.AlertMessage	=  REPLACE(REPLACE(REPLACE(t_ESB_AlertFormat.MessageFormat,'{LoanAmount}',CAST(ISNULL(#ESB_AlertMsgLog.LoanAmount,0.00)AS nVarchar(20))),'{AccountNo}',ISNULL(#ESB_AlertMsgLog.AccountID,''))
		,'{Date}',CONVERT(nvarchar(10),ISNULL(#ESB_AlertMsgLog.TrxDate,''),106))
		FROM #ESB_AlertMsgLog
		INNER JOIN t_ESB_AlertFormat
		ON #ESB_AlertMsgLog.EventID			=	t_ESB_AlertFormat.EventID
		AND #ESB_AlertMsgLog.LanguageID		=	t_ESB_AlertFormat.LanguageID
		WHERE #ESB_AlertMsgLog.AlertMessage IS NULL
			
		UPDATE #ESB_AlertMsgLog
		SET #ESB_AlertMsgLog.ClientID = t_AccountCustomer.ClientID
		FROM t_AccountCustomer WITH(NOLOCK)
		WHERE t_AccountCustomer.OurBranchID = #ESB_AlertMsgLog.OurBranchID
		AND t_AccountCustomer.AccountID		= #ESB_AlertMsgLog.AccountID
		
		UPDATE #ESB_AlertMsgLog
		SET #ESB_AlertMsgLog.AlertMessage	=	REPLACE(#ESB_AlertMsgLog.AlertMessage,'{NAME}',t_Client.Name),
			#ESB_AlertMsgLog.MobileNo		=	t_Client.Mobile
		FROM t_Client	
		WHERE t_Client.ClientID				=	#ESB_AlertMsgLog.ClientID
		AND t_Client.IsMobileVerified		=  1
		
		BEGIN TRAN
		   
		   INSERT INTO t_NotificationAlert  
		   (          
			NFTypeID,  
			MobileNo,  
			Content,  
			Subject,  
			CreatedOn,  
			LanguageID,
			SMSStatusTimestamp  
		   )   
		   SELECT  
				'S',  
				MobileNo,  
				AlertMessage,  
				EventID,  
				GETDATE(),  
				ISNULL(#ESB_AlertMsgLog.LanguageID,'EN'),
				GETDATE()
			FROM #ESB_AlertMsgLog	
			WHERE MobileNo IS NOT NULL
			AND AlertMessage IS NOT NULL
			AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog 
						WHERE	t_ESB_AlertMsgLog.OurbranchID	=	#ESB_AlertMsgLog.OurbranchID	
							AND	t_ESB_AlertMsgLog.SMSKey		=	#ESB_AlertMsgLog.SMSKey 
							AND t_ESB_AlertMsgLog.EventID		=	#ESB_AlertMsgLog.EventID
							--AND t_ESB_AlertMsgLog.LanguageID	=	#ESB_AlertMsgLog.LanguageID
							)
							
							
		INSERT INTO t_ESB_AlertMsgLog	
		(
			OurbranchID,
			MobileNo,
			SMSDate,
			SMSKey,
			EventID,
			LanguageID
		)
		SELECT OurbranchID,
				MobileNo,
				TrxDate,
				SMSKey,
				EventID,
				LanguageID
		FROM #ESB_AlertMsgLog		
		WHERE MobileNo IS NOT NULL
		AND AlertMessage IS NOT NULL
		AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog 
						WHERE	t_ESB_AlertMsgLog.OurbranchID	=	#ESB_AlertMsgLog.OurbranchID	
							AND	t_ESB_AlertMsgLog.SMSKey		=	#ESB_AlertMsgLog.SMSKey 
							AND t_ESB_AlertMsgLog.EventID		=	#ESB_AlertMsgLog.EventID
							--AND t_ESB_AlertMsgLog.LanguageID	=	#ESB_AlertMsgLog.LanguageID
							)
			
		UPDATE t_JobConfiguration
		SET LastRunEndTime	= GETDATE()
		WHERE JobID			= @JobID

		INSERT INTO t_JobLogDetail
		(
			JobID		,
			StartTime	,
			EndTime		,
			StatusID
		)
		SELECT	@JobID,
				@StartTime,
				@Endtime,
				'S'
				
		
		
		IF @@ERROR = 0 
		BEGIN
			IF @@TRANCOUNT > 0
			BEGIN
			
				COMMIT TRANSACTION
				
			END
		END
	
	END TRY
	
	BEGIN CATCH
	
	SET @ErrorMsg = ERROR_MESSAGE()
	RAISERROR(@ErrorMsg,16,1)
	
	IF @@ERROR > 0 
	BEGIN
		IF @@TRANCOUNT > 0
		BEGIN
		
			ROLLBACK TRANSACTION
			
		END			
	END
		
	END CATCH
	
	EndStatment:
		
		IF @@ERROR = 0 
		BEGIN
			IF @@TRANCOUNT > 0
			BEGIN
			
				COMMIT TRANSACTION
				
			END
		END
	
	
	IF @@ERROR > 0 
	BEGIN
		IF @@TRANCOUNT > 0
		BEGIN
		
			ROLLBACK TRANSACTION
			
		END			
	END
		
	
	DROP TABLE #ESB_AlertMsgLog
	DROP TABLE #BranchDate
	
	SET NOCOUNT OFF	  			
END
GO