DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

-- BIEU DO O4 GIA TRI MUA SAM  EXEC [DB_BUY_VALUE_BAR] 'phuongtln1','',2022','2022'

ALTER   PROC [dbo].[DB_BUY_VALUE_BAR]

@p_USER_LOGIN VARCHAR(250) = NULL,

@p_FILTER VARCHAR(100) = NULL,

@p_FROM_YEAR VARCHAR(10) = NULL,

@p_TO_YEAR VARCHAR(10) = NULL

AS

BEGIN

	--DECLARE @DEP_ID_LOG VARCHAR(15) =(SELECT DEP_ID FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)

	--DECLARE @BRN_ID_LOG VARCHAR(15) =(SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)

	--DECLARE @DEP_ID_LOGIN VARCHAR(15), @DEP_GROUP VARCHAR(15),@p_BRANCH_LOGIN VARCHAR(15)

	--SET @DEP_ID_LOGIN =(SELECT DEP_ID FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)

	--SET @DEP_GROUP =(SELECT GROUP_ID FROM CM_DEPARTMENT WHERE DEP_ID =@DEP_ID_LOGIN)

	--SET @p_BRANCH_LOGIN =(SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)

	--DECLARE @TABLE_ROLE TABLE (ROLENAME NVARCHAR(100))

	--DECLARE @USERID INT

	--SET @USERID =(SELECT ID FROM TL_USER WHERE TLNANME = 'tscd_hoiso'@p_USER_LOGIN)

	-----

	--INSERT INTO @TABLE_ROLE SELECT B.DisplayName FROM AbpUserRoles A

	--							INNER JOIN ABPROLES B ON A.RoleId = B.Id

	--							WHERE A.UserId = '13922'@USERID

	--DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15))

	--DECLARE @tmp_DEP TABLE(DEP_ID VARCHAR(15))

	--INSERT INTO @tmp_DEP  SELECT @DEP_ID_LOGIN

	--IF(EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLENAME ='Admin'))

	--BEGIN

	--		insert into @tmp  SELECT BRANCH_ID  FROM [dbo].CM_BRANCH

	--		INSERT INTO @tmp_DEP SELECT DEP_ID FROM CM_DEPARTMENT

	--END

	----- KIEM TRA NEU STAFF THI CHI THAY DEP_ID CUA MINH

	--IF(EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLENAME ='Staff') OR @p_USER_LOGIN ='' OR @p_USER_LOGIN IS NULL)

	--BEGIN

	--		DELETE FROM @tmp

	--		INSERT INTO @tmp  SELECT @p_BRANCH_LOGIN

	--		INSERT INTO @tmp_DEP SELECT DEP_ID FROM CM_DEPARTMENT WHERE GROUP_ID =@DEP_GROUP

	--		--SET @p_BRANCH_ID =@p_BRANCH_LOGIN

	--END

	--IF(EXISTS(SELECT * FROM @TABLE_ROLE WHERE ROLENAME ='Manager') OR @p_USER_LOGIN ='' OR @p_USER_LOGIN IS NULL)

	--BEGIN

	--		DELETE FROM @tmp

	--		INSERT INTO @tmp  SELECT BRANCH_ID FROM CM_BRANCH

	--		--SET @p_BRANCH_ID =@p_BRANCH_LOGIN

	--		INSERT INTO @tmp_DEP  SELECT DEP_ID FROM CM_DEPARTMENT WHERE BRANCH_ID =@p_BRANCH_LOGIN

	--		INSERT INTO @tmp_DEP SELECT DEP_ID FROM CM_DEPARTMENT WHERE GROUP_ID =@DEP_GROUP

	--END

	-----END CAU HINH WORKFLOW

	----- TIENLEE 01/03/2022

	DECLARE @YEAR VARCHAR(10)--, @TEMP DECIMAL(18,2)

	DECLARE @TABLE_YEAR TABLE(

		[YEAR] VARCHAR(10) NULL,

		[SUMARY_BUY_RECEIVE] DECIMAL(18,2) NULL,

		[SUMARY_BUY_MADE] DECIMAL(18,2) NULL,

		[SUMARY_BUY_DOING] DECIMAL(18,2) NULL,

		[SUMARY_BUY_REAL] DECIMAL(18,2) NULL,

		[SUMARY_BUY_SAVE] DECIMAL(18,2) NULL

	)

	DECLARE @TABLE_MONTH TABLE(

		[YEAR] VARCHAR(10) NULL,

		[MONTH] VARCHAR(10) NULL,

		[SUMARY_BUY_RECEIVE] DECIMAL(18,2) NULL,

		[SUMARY_BUY_MADE] DECIMAL(18,2) NULL,

		[SUMARY_BUY_DOING] DECIMAL(18,2) NULL,

		[SUMARY_BUY_REAL] DECIMAL(18,2) NULL,

		[SUMARY_BUY_SAVE] DECIMAL(18,2) NULL

	)

	DECLARE @TABLE_MONTH_RECEIVE TABLE(

		[YEAR] VARCHAR(10) NULL,

		[MONTH] VARCHAR(2) NULL,

		[SUMARY_BUY_RECEIVE] DECIMAL(18,2) NULL

	)

	DECLARE @TABLE_MONTH_MADE TABLE(

		[YEAR] VARCHAR(10) NULL,

		[MONTH] VARCHAR(2) NULL,

		[SUMARY_BUY_MADE] DECIMAL(18,2) NULL

	)

	DECLARE @TABLE_MONTH_DOING TABLE(

		[YEAR] VARCHAR(10) NULL,

		[MONTH] VARCHAR(2) NULL,

		[SUMARY_BUY_DOING] DECIMAL(18,2) NULL

	)

	DECLARE @TABLE_MONTH_REAL TABLE(

		[YEAR] VARCHAR(10) NULL,

		[MONTH] VARCHAR(2) NULL,

		[SUMARY_BUY_REAL] DECIMAL(18,2) NULL

	)

	DECLARE @TABLE_MONTH_SAVE TABLE(

		[YEAR] VARCHAR(10) NULL,

		[MONTH] VARCHAR(2) NULL,

		[SUMARY_BUY_SAVE] DECIMAL(18,2) NULL

	)

	DECLARE CUR_FILTER_YEAR CURSOR FOR

	SELECT YEAR(D.APPROVE_DT) FROM TR_REQUEST_DOC D

	WHERE 1=1

	AND (YEAR(D.APPROVE_DT)<= CAST(@p_TO_YEAR AS INT) OR CAST(@p_TO_YEAR AS INT) IS NULL OR CAST(@p_TO_YEAR AS INT) ='')

	AND (YEAR(D.APPROVE_DT)>= CAST(@p_FROM_YEAR AS INT) OR CAST(@p_FROM_YEAR AS INT) IS NULL OR CAST(@p_FROM_YEAR AS INT) ='')

	AND (D.APPROVE_DT IS NOT NULL AND D.APPROVE_DT <>'')

	GROUP BY YEAR(D.APPROVE_DT)

	OPEN CUR_FILTER_YEAR

	FETCH NEXT FROM CUR_FILTER_YEAR

		  INTO @YEAR

	WHILE @@FETCH_STATUS = 0

	BEGIN

		DECLARE @RECEIVE DECIMAL(18,2),@DOING DECIMAL(18,2),@MADE DECIMAL(18,2),@REAL DECIMAL(18,2)

		-- FILTER YEAR --

		-- RECEIVE

		SELECT @RECEIVE=ROUND( SUM(DT.PRICE_ETM*DT.QUANTITY)/1000000000,2) FROM TR_REQUEST_DOC_DT DT

		LEFT JOIN TR_REQUEST_DOC D ON DT.REQ_DOC_ID= D.REQ_ID

		WHERE (D.PROCESS_ID = 'DMMS' OR D.PROCESS_ID = 'DVCM' OR D.PROCESS_ID = 'APPROVE' OR D.PROCESS_ID = 'GDK_PYC')

		AND (D.REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_PROCESS PR WHERE PR.PROCESS_ID ='DMMS'))

		AND (D.REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_PROCESS_CHILD) OR D.PROCESS_ID = 'APPROVE')

		AND (YEAR(D.APPROVE_DT) = @YEAR)

		AND (D.DMMS_ID ='DMMS00000000001' OR D.DMMS_ID ='DM0000000000003')

		--- ĐÃ THỰC HIỆN

		SELECT @MADE=ROUND( SUM(DT.PRICE_ETM*DT.QUANTITY)/1000000000,2) FROM TR_REQUEST_DOC_DT DT

		LEFT JOIN TR_REQUEST_DOC D ON DT.REQ_DOC_ID= D.REQ_ID

		WHERE 1=1

		AND ((D.USER_DVMS IS NOT NULL AND D.USER_DVMS <> '' AND 

		(D.REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_PROCESS_CHILD CH WHERE CH.TYPE_JOB='TP' AND CH.STATUS_JOB='P' AND CH.PROCESS_ID IN (SELECT ID FROM PL_REQUEST_PROCESS)))))

		AND (D.REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='DMMS'))

		AND (YEAR(D.APPROVE_DT) = @YEAR)

		AND (D.DMMS_ID ='DMMS00000000001' OR D.DMMS_ID ='DM0000000000003')

		-- ĐANG THỰC HIỆN

		SELECT @DOING=ROUND( SUM(DT.PRICE*DT.QUANTITY)/1000000000,2) FROM TR_REQUEST_DOC_DT DT

		LEFT JOIN TR_REQUEST_DOC D ON DT.REQ_DOC_ID= D.REQ_ID

		WHERE 1=1

		--AND ((D.USER_DVMS IS NOT NULL AND D.USER_DVMS <> '' AND 

		--		(D.REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_PROCESS_CHILD CH WHERE CH.TYPE_JOB IN ('TP') AND CH.STATUS_JOB IN('C')))))

		AND (D.REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_PROCESS))

		AND (YEAR(D.APPROVE_DT) = @YEAR)

		AND (D.DMMS_ID ='DMMS00000000001' OR D.DMMS_ID ='DM0000000000003')

		AND D.REQ_ID IN (

				SELECT prpc.REQ_ID FROM PL_REQUEST_PROCESS_CHILD prpc WHERE prpc.TYPE_JOB IN ('KS','TP') AND prpc.STATUS_JOB='C'  AND

				EXISTS (SELECT prpc1.REQ_ID FROM PL_REQUEST_PROCESS_CHILD prpc1 WHERE prpc1.TYPE_JOB='XL'AND prpc1.STATUS_JOB='P' AND prpc1.REQ_ID=prpc.REQ_ID )

		)

		-- REAL

		SELECT @REAL = ROUND( SUM(DT.PRICE*DT.QUANTITY)/1000000000,2) FROM TR_REQUEST_DOC_DT DT

		LEFT JOIN TR_REQUEST_DOC D ON DT.REQ_DOC_ID= D.REQ_ID

		WHERE 1=1

		AND ((D.USER_DVMS IS NOT NULL AND D.USER_DVMS <> '' AND 

				(D.REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_PROCESS_CHILD CH WHERE CH.TYPE_JOB='TP' AND CH.STATUS_JOB='P'))))

		AND (D.REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_PROCESS WHERE PROCESS_ID ='DMMS'))

		AND (YEAR(D.APPROVE_DT) = @YEAR)

		AND (D.DMMS_ID ='DMMS00000000001' OR D.DMMS_ID ='DM0000000000003')

		INSERT INTO @TABLE_YEAR([YEAR],SUMARY_BUY_RECEIVE,SUMARY_BUY_DOING,SUMARY_BUY_MADE,SUMARY_BUY_REAL,SUMARY_BUY_SAVE)

		VALUES(@YEAR,@RECEIVE,@DOING,@MADE,@REAL,@MADE-@REAL)

		-- FILTER MONTH --

		-- RECEIVE

		INSERT INTO @TABLE_MONTH_RECEIVE([YEAR],[MONTH],SUMARY_BUY_RECEIVE)

		SELECT @YEAR,MONTH(D.APPROVE_DT),ROUND( SUM(DT.PRICE_ETM*DT.QUANTITY)/1000000000,2) FROM TR_REQUEST_DOC_DT DT

		LEFT JOIN TR_REQUEST_DOC D ON DT.REQ_DOC_ID= D.REQ_ID

		WHERE (D.PROCESS_ID = 'DMMS' OR D.PROCESS_ID = 'DVCM' OR D.PROCESS_ID = 'APPROVE' OR D.PROCESS_ID = 'GDK_PYC')

		AND (YEAR(D.APPROVE_DT) = @YEAR)

		AND (D.APPROVE_DT IS NOT NULL AND D.APPROVE_DT <>'')

		GROUP BY MONTH(D.APPROVE_DT)

		-- DOING

		INSERT INTO @TABLE_MONTH_DOING([YEAR],[MONTH],SUMARY_BUY_DOING)

		SELECT @YEAR,MONTH(D.APPROVE_DT),ROUND( SUM(DT.PRICE_ETM*DT.QUANTITY)/1000000000,2) FROM TR_REQUEST_DOC_DT DT

		LEFT JOIN TR_REQUEST_DOC D ON DT.REQ_DOC_ID= D.REQ_ID

		WHERE (D.PROCESS_ID = 'DMMS' OR D.PROCESS_ID = 'DVCM' OR D.PROCESS_ID = 'APPROVE' OR D.PROCESS_ID = 'GDK_PYC')

		AND (YEAR(D.APPROVE_DT) = @YEAR)

		AND (D.APPROVE_DT IS NOT NULL AND D.APPROVE_DT <>'')

		----LUCTV 08082022 ĐÃ THỰC HIỆN CÓ NGHĨA LÀ ĐƯỢC ĐIỀU PHỐI

		AND ((D.USER_DVMS IS NOT NULL AND D.USER_DVMS <> '') OR D.PROCESS_ID = 'APPROVE')

		---- END LUCTV

		GROUP BY MONTH(D.APPROVE_DT)

		-- MADE

		INSERT INTO @TABLE_MONTH_MADE([YEAR],[MONTH],SUMARY_BUY_MADE)

		SELECT @YEAR,MONTH(D.APPROVE_DT),ROUND( SUM(DT.PRICE*DT.QUANTITY)/1000000000,2) FROM TR_REQUEST_DOC_DT DT

		LEFT JOIN TR_REQUEST_DOC D ON DT.REQ_DOC_ID= D.REQ_ID

		LEFT JOIN PL_REQUEST_PROCESS_CHILD P ON P.REQ_ID = D.REQ_ID

		WHERE P.STATUS_JOB = 'P' AND P.TYPE_JOB = 'XL'

		AND (YEAR(D.APPROVE_DT) = @YEAR)

		AND (D.APPROVE_DT IS NOT NULL AND D.APPROVE_DT <>'')

		GROUP BY MONTH(D.APPROVE_DT)

		-- REAL

		INSERT INTO @TABLE_MONTH_REAL([YEAR],[MONTH],SUMARY_BUY_REAL)

		SELECT @YEAR,MONTH(D.APPROVE_DT),ROUND( SUM(DT.PRICE*DT.QUANTITY)/1000000000,2) FROM TR_REQUEST_DOC_DT DT

		LEFT JOIN TR_REQUEST_DOC D ON DT.REQ_DOC_ID= D.REQ_ID

		LEFT JOIN PL_REQUEST_PROCESS_CHILD P ON P.REQ_ID = D.REQ_ID

		WHERE P.STATUS_JOB = 'P' AND P.TYPE_JOB = 'XL'

		AND (YEAR(D.APPROVE_DT) = @YEAR)

		AND (D.APPROVE_DT IS NOT NULL AND D.APPROVE_DT <>'')

		GROUP BY MONTH(D.APPROVE_DT)

		-- MEGA

		INSERT INTO @TABLE_MONTH([YEAR],[MONTH],SUMARY_BUY_RECEIVE,SUMARY_BUY_DOING,SUMARY_BUY_MADE,SUMARY_BUY_REAL,SUMARY_BUY_SAVE)

		SELECT R.[YEAR],R.[MONTH],R.SUMARY_BUY_RECEIVE,M.SUMARY_BUY_MADE,D.SUMARY_BUY_DOING,RE.SUMARY_BUY_REAL,

		(M.SUMARY_BUY_MADE-RE.SUMARY_BUY_REAL)  FROM @TABLE_MONTH_RECEIVE R

		LEFT JOIN @TABLE_MONTH_DOING D ON D.[YEAR] = R.[YEAR] AND D.[MONTH] = R.[MONTH]

		LEFT JOIN @TABLE_MONTH_MADE M ON M.[YEAR] = R.[YEAR] AND M.[MONTH] = R.[MONTH]

		LEFT JOIN @TABLE_MONTH_REAL RE ON M.[YEAR] = R.[YEAR] AND RE.[MONTH] = R.[MONTH]

		FETCH NEXT FROM CUR_FILTER_YEAR

			  INTO @YEAR

	END

	CLOSE CUR_FILTER_YEAR

	DEALLOCATE CUR_FILTER_YEAR

	SELECT * FROM @TABLE_YEAR ORDER BY [YEAR] ASC

	SELECT * FROM @TABLE_MONTH ORDER BY [YEAR] ASC

	------------------------

END