DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_CASA_ByRefId] 

@p_REF_ID VARCHAR(200) = NULL, 

@p_BRANCH_ID VARCHAR(50) = NULL, 

@RefNo VARCHAR(500) = NULL

AS

DECLARE @l_SUM_AMT DECIMAL,

        @l_REF_NO VARCHAR(20),

        @l_ASSET_ID VARCHAR(20);

DECLARE @l_TRN_TYPE VARCHAR(50) = '';

DECLARE @l_CASA_CR_ACCT VARCHAR(20) = '';

DECLARE @l_CASA_DR_ACCT VARCHAR(20) = '';

DECLARE @l_CASA_CR_ACCT_NAME NVARCHAR(500) = '';

DECLARE @l_CASA_DR_ACCT_NAME NVARCHAR(500) = '';

DECLARE @MW_ENTRI TABLE (ET_ID varchar(15), REF_NO varchar(20), TRN_ID varchar(15), REF_ID varchar(15), TRN_TYPE nvarchar(40), ENTRY_PAIR varchar(15), DO_BRN varchar(15), DRCR varchar(2), ACCT varchar(100), BRN_ID varchar(20), DEPT_ID varchar(15), AMT decimal(18, 2), CURRENCY varchar(20), EXC_RATE numeric(18, 2), EXP_TO_CORE varchar(1), TRN_DATE datetime, TRN_DESC nvarchar(1000), MAKER_ID varchar(15), CHECKER_ID varchar(15))

INSERT INTO @MW_ENTRI 

SELECT * FROM PAY_ENTRIES_POST 

WHERE TRN_ID = @p_REF_ID AND (DO_BRN = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' ) AND (REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')

--SELECT * FROM @MW_ENTRI

DECLARE @ENTRY_PAIR VARCHAR(100)

SET @ENTRY_PAIR = (SELECT TOP 1 ENTRY_PAIR FROM @MW_ENTRI WHERE LEN(ACCT)>9)--------------*********

DECLARE @GDV VARCHAR(50), @KSV VARCHAR(50)

SET @GDV = (SELECT TOP 1  MAKER_ID FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REF_ID)

SET @KSV =(SELECT TOP 1  CHECKER_ID FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REF_ID)

--SELECT @ENTRY_PAIR

DECLARE @TABLE_REF TABLE (REF_NO VARCHAR(50))

INSERT INTO @TABLE_REF SELECT DISTINCT REF_NO FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REF_ID AND (REF_NO =@RefNo OR @RefNo IS NULL OR @RefNo ='')

DECLARE @TABLE_RETURN TABLE (TRN_ID VARCHAR(15), PrintDate VARCHAR(25),SUM_AMT DECIMAL(18,0),SUM_stringMoney VARCHAR(100),CASA_CR_ACCT VARCHAR(20),

CASA_CR_ACCT_NAME VARCHAR(250), CASA_DR_ACCT VARCHAR(20),CASA_DR_ACCT_NAME NVARCHAR(250),REF_NO VARCHAR(50),TRN_DESC VARCHAR(250), G_D_V NVARCHAR(50), 

K_S_V NVARCHAR(50), RATE VARCHAR(15), G_DV VARCHAR(35), DV_KD VARCHAR(50),TELL VARCHAR(50),SUP VARCHAR(50), DIR VARCHAR(50))

DECLARE @COUNT_SL INT, @REF_REFENCE VARCHAR(50)

SET @COUNT_SL =1

WHILE ((SELECT COUNT (*) FROM @TABLE_REF)>0)

BEGIN

	SET @REF_REFENCE=(SELECT TOP 1 REF_NO FROM @TABLE_REF)

	SET @COUNT_SL = @COUNT_SL+1

	INSERT INTO @TABLE_RETURN SELECT TOP 1 TRN_ID,ISNULL(FORMAT(TRN_DATE,'dd/MM/yyyy'),'') ,

	(SELECT SUM (AMT)/2 FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND TRN_ID =@p_REF_ID),

	REPLACE(dbo.ReadMoney((SELECT SUM (AMT) /2 FROM PAY_ENTRIES_POST WHERE  REF_NO =@REF_REFENCE AND TRN_ID =@p_REF_ID)),'  ',' '), 

	(SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND LEN(ACCT) >9 AND DRCR ='C' AND TRN_ID =@p_REF_ID),

	[dbo].[FN_GET_ACC_NAME_PAY]((SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND LEN(ACCT) >9 AND DRCR ='C' AND TRN_ID =@p_REF_ID)),

	(SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND LEN(ACCT) >9 AND DRCR ='D' AND TRN_ID =@p_REF_ID),

	[dbo].[FN_GET_ACC_NAME_PAY]((SELECT TOP 1 ACCT FROM PAY_ENTRIES_POST WHERE REF_NO =@REF_REFENCE AND LEN(ACCT) >9 AND DRCR ='D' AND TRN_ID =@p_REF_ID)),@REF_REFENCE, UPPER(TRN_DESC) , 

	CHAR(10) + dbo.fChuyenCoDauThanhKhongDau((SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME =@GDV)),CHAR(10) + dbo.fChuyenCoDauThanhKhongDau((SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME =@KSV)),

	'1', UPPER(@GDV),'HOI SO','Teller', 'Supervisor', 'Director'

	FROM PAY_ENTRIES_POST WHERE TRN_ID =@p_REF_ID AND REF_NO =@REF_REFENCE

	DELETE FROM @TABLE_REF WHERE REF_NO =@REF_REFENCE

END

SELECT * FROM @TABLE_RETURN

--SELECT MAX(KQ.TRN_ID) AS TRN_ID , MAX(KQ.PrintDate) AS PrintDate, SUM(KQ.SUM_AMT) AS SUM_AMT, MAX(KQ.SUM_stringMoney) AS SUM_stringMoney, MAX(KQ.CASA_CR_ACCT) AS CASA_CR_ACCT,

--MAX(KQ.CASA_CR_ACCT_NAME) AS CASA_CR_ACCT_NAME,MAX(KQ.CASA_DR_ACCT) AS CASA_DR_ACCT,MAX(KQ.CASA_DR_ACCT_NAME) AS CASA_DR_ACCT_NAME, KQ.REF_NO, MAX(KQ.TRN_DESC) AS TRN_DESC, MAX(KQ.MAKER_ID) AS  [USER_NAME]

--FROM 

--(

--SELECT  A.TRN_ID, 

--	convert(varchar(10),DAY(A.TRN_DATE))+N'/'+convert(varchar(10),MONTH(A.TRN_DATE)) +N'/'+convert(varchar(10),YEAR(A.TRN_DATE)) AS PrintDate,

--	A.SUM_AMT,

--    A.SUM_stringMoney,

--	C.CR_ACCT AS CASA_CR_ACCT, C.CASA_CR_ACCT_NAME,	

--	B.DR_ACCT AS CASA_DR_ACCT, B.CASA_DR_ACCT_NAME,

--	D.TRN_DESC,

--    D.REF_NO,

--   '' AS REF_NO_SUM, 

--	A.MAKER_ID

--FROM 

--(

--	SELECT A.TRN_ID,

--		   CONVERT(DATE,A.TRN_DATE,103) TRN_DATE,

--           SUM(A.AMT) AS SUM_AMT,

--           dbo.ReadMoney(SUM(A.AMT)) AS SUM_stringMoney,

--		   A.MAKER_ID

--	FROM @MW_ENTRI A

--	WHERE DRCR = 'D'

--	GROUP BY A.TRN_ID, CONVERT(DATE,A.TRN_DATE,103), A.REF_NO,A.MAKER_ID

--) AS A

--LEFT JOIN 

--(

--	SELECT TOP 1 A.REF_NO, A.TRN_ID, A.ACCT DR_ACCT, [dbo].[FN_GET_ACC_NAME_PAY](A.ACCT) AS CASA_DR_ACCT_NAME

--	FROM @MW_ENTRI A

--	WHERE LEN(A.ACCT) > 9 AND A.DRCR = 'D' AND ENTRY_PAIR = @ENTRY_PAIR

--	GROUP BY A.REF_NO

--	) AS B ON B.TRN_ID = A.TRN_ID

--LEFT JOIN 

--(

--	SELECT TOP 1 A.REF_NO, A.TRN_ID, A.ACCT CR_ACCT, [dbo].[FN_GET_ACC_NAME_PAY](A.ACCT) CASA_CR_ACCT_NAME

--	FROM @MW_ENTRI A	

--	WHERE LEN(A.ACCT) > 9 AND A.DRCR = 'C' AND ENTRY_PAIR = @ENTRY_PAIR

--	GROUP BY A.REF_NO

--	) AS C ON C.TRN_ID = A.TRN_ID

--LEFT JOIN 

--(

--	SELECT TOP 100 A.TRN_DESC, A.TRN_ID, A.REF_NO

--	FROM @MW_ENTRI A		

--	) AS D ON D.TRN_ID = A.TRN_ID

----LEFT JOIN

----(

----    SELECT STUFF((SELECT ', ' + C1.REF_NO

----                FROM @MW_ENTRI C1

----                WHERE C2.TRN_ID = C1.TRN_ID

----                GROUP BY C1.TRN_ID,C1.REF_NO                

----                FOR XML PATH('')

----            ),1,1,'') AS REF_NO_SUM, C2.TRN_ID

----    FROM @MW_ENTRI C2

----    GROUP BY C2.TRN_ID, C2.REF_NO

----) AS E ON E.TRN_ID = A.TRN_ID

--) AS KQ

--GROUP BY KQ.REF_NO