DB_Script/Back/proc_notification.sql

USE [FastMoneyPro_Remit]
GO

/****** Object:  StoredProcedure [dbo].[proc_notification]    Script Date: 2/15/2024 3:06:30 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[proc_notification] @user VARCHAR(50)
	,@portal VARCHAR(20) = NULL
	,@branch_id INT = NULL
AS
-- EXEC [proc_notification] @user='atit'
--#1064 - add renew id
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN
	IF @portal = 'AGENT'
	BEGIN
		--SELECT COUNT('A') AS [count] , CAST(COUNT('A') AS VARCHAR) + 'Vault Deposit Request(s) pending' AS [msg],
		--  'Approve Deposit Request(s)' AS Msg1,'/AgentNew/vaulttransfer/approvetransfertovaultlist.aspx' AS [link] 
		--FROM BRANCH_CASH_IN_OUT (NOLOCK) 
		--WHERE branchId = @branch_id
		--AND HEAD = 'Transfer To Vault'
		--AND MODE = 'C'
		--AND createdBy <> @user
		--AND APPROVEDBY IS NULL
		--UNION ALL
		--SELECT COUNT('A') AS [count] , CAST(COUNT('A') AS VARCHAR) + 'Vault Deposit Request(s) pending' AS [msg],
		--  'Approve Deposit Request(s)' AS Msg1,'/AgentNew/ApproveCashTransfer/List.aspx' AS [link] 
		--FROM BRANCH_CASH_IN_OUT B(NOLOCK) 
		--INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.AC_MASTER AM(NOLOCK) ON AM.ACCT_NUM = B.TOACC
		--WHERE AM.AGENT_ID = @branch_id
		--AND AM.ACCT_RPT_CODE = 'BVA'
		--AND HEAD = 'Transfer From Vault'
		--AND MODE = 'CV'
		--AND APPROVEDBY IS NULL
		RETURN
	END

	IF (
			SELECT dbo.FNAHasRight(@User, '90100000')
			) = 'N'
		RETURN

	DECLARE @NotificationList TABLE (
		[count] INT
		,Msg VARCHAR(100)
		,Msg1 VARCHAR(50)
		,Link VARCHAR(100)
		)

	INSERT INTO @NotificationList
	--   SELECT COUNT('A') AS [count] , CAST(COUNT('A') AS VARCHAR) + ' Modification Request(s) pending' AS [msg],
	--'Approve Modify Txn(s)' AS Msg1,'/Remit/Transaction/ApproveModification/List.aspx' AS [link]
	--   FROM    tranModifyLog TL WITH ( NOLOCK )
	--   WHERE   TL.status = 'Request' AND TL.MsgType = 'Modify'
	--UNION ALL
	--SELECT  COUNT('A') AS [count] ,CAST(COUNT('A') AS VARCHAR) + ' Cancel Request(s) pending' AS [msg], 'Approve Cancel Txn(s)' AS Msg1,
	--        '/Remit/Transaction/Cancel/ApproveReqUnapprovedTxn.aspx' AS [link]
	--FROM    vwRemitTran trn WITH ( NOLOCK )
	--        INNER JOIN tranCancelrequest A WITH ( NOLOCK ) ON A.controlNo = trn.controlNo
	--WHERE   trn.tranStatus = 'CancelRequest'
	--        AND A.cancelStatus = 'CancelRequest'
	--UNION ALL
	--SELECT  COUNT('A') AS [count] ,
	--        CAST(COUNT('A') AS VARCHAR) + ' Blocked Transaction(s)' AS [msg], 'Approve Blocked Txn(s)' AS Msg1,
	--        '/Remit/Transaction/BlockTransaction/List.aspx' AS [link]
	--FROM    remitTran trn WITH ( NOLOCK )
	--WHERE   trn.tranStatus = 'Block'
	--UNION ALL
	--SELECT  COUNT('A') AS [count] ,CAST(COUNT('A') AS VARCHAR) + ' Locked Transaction(s)' AS [msg], 'Release Locked Txn(s)' AS Msg1,
	--        '/Remit/Transaction/UnlockTransaction/List.aspx' AS [link]
	--FROM    remitTran trn WITH ( NOLOCK )
	--WHERE   trn.tranStatus = 'Lock'
	--      --  AND trn.tranType = 'D'
	--UNION ALL
	SELECT COUNT('A') AS [count]
		,CAST(COUNT('A') AS VARCHAR) + ' Txn(s) Pending For Approval' AS [msg]
		,'Approve Txn(s)' AS Msg1
		,'/Remit/Transaction/Approve/Manage.aspx' AS [link]
	FROM dbo.remitTran
	WHERE tranStatus = 'Hold'
		AND payStatus = 'Unpaid'
		AND approvedBy IS NULL
	--  AND tranType = 'D'
	
	UNION ALL
	
	SELECT COUNT('A') AS [count]
		,CAST(COUNT('A') AS VARCHAR) + '(CR) Txn Approval Pending' AS [msg]
		,'Approve International Txn(s)' AS Msg1
		,'/Remit/Transaction/ApproveTxn/holdTxnList.aspx' AS [link]
	FROM dbo.remitTranTemp
	WHERE tranStatus IN ('Hold')
		AND payStatus = 'Unpaid'
		AND approvedBy IS NULL
		AND tranType = 'I'
	
	UNION ALL
	
	SELECT COUNT('A') AS [count]
		,CAST(COUNT('A') AS VARCHAR) + ' Mobile/Web Txn Approval Pending' AS [msg]
		,'Approve Mobile/Web Txn(s)' AS Msg1
		,'/Remit/Transaction/ApproveTxn/holdTxnListMobile.aspx?country=JAPAN' AS [link]
	FROM dbo.remitTranTemp
	WHERE tranStatus IN (
			'Hold'
			,'Compliance Hold'
			,'OFAC Hold'
			,'OFAC/Compliance Hold'
			)
		AND payStatus = 'Unpaid'
		AND approvedBy IS NULL
		AND tranType = 'M'
	
	UNION ALL
	
	SELECT COUNT('A') AS [count]
		,CAST(COUNT('A') AS VARCHAR) + ' &nbsp; Slip upload(s) Pending For Approval' AS [msg]
		,'Slip Deposit(s)' AS Msg1
		,'/MobileRemit/Admin/DepositSlip/Manage.aspx' AS [link]
	FROM dbo.[depositSlip]
	WHERE STATUS = 0
		AND approveddate IS NULL
	
	UNION ALL
	
	-- SELECT  COUNT('A') AS [count] ,
	--            CAST(COUNT('A') AS VARCHAR) + ' &nbsp; Renew id(s) Pending For Approval' AS [msg], 'Renew Id(s)' AS Msg1,
	--            '/MobileRemit/Admin/Operation/RenewID/Manage.aspx' AS [link]
	--    FROM    dbo.customerdocumenttmp where ISNULL(isDeleted,0)=0 and approvedby IS NULL
	--UNION ALL
	SELECT COUNT('A') AS [count]
		,CAST(COUNT('A') AS VARCHAR) + ' &nbsp; Partner Ticket(s) For Resolve' AS [msg]
		,'Partner Trouble Ticket(s)' AS Msg1
		,'/Remit/Transaction/TroubleTicket/UnResolvedList.aspx' AS [link]
	FROM tranModifyLog
	WHERE fieldName = 'STATUS_SYNC'
		AND STATUS IN ('Not Resolved')

	SELECT *
	FROM @NotificationList
	WHERE [count] > 0
END;