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

-- BIEU DO O3 NGAN SACH

ALTER   PROC dbo.DB_BUDGET_PIE

@p_USER_LOGIN VARCHAR(250) = NULL,

@p_YEAR VARCHAR(4) = NULL, -- YEAR

@p_DATE VARCHAR(20) = NULL, -- DD/MM/YYYY

@p_GD_ID VARCHAR(15) = NULL, -- GD_NAME

@p_PLAN_TYPE_ID VARCHAR(15) = NULL, -- BUDGET TYPE

@p_GD_TYPE_ID VARCHAR(15) = NULL, -- BUDGET GROUP

@p_BRANCH_ID VARCHAR(15) = NULL, -- BRANCH NAME

@p_FILTER VARCHAR(15) = NULL -- BRANCH NAME

AS

BEGIN

		DECLARE @TABLE_BRANCH TABLE(

			BRANCH_ID VARCHAR(15)

		)

			DECLARE @BRANCH_LOGIN varchar(15) =''

      DECLARE @DEP_LOGIN varchar(15) =''

			SELECT @BRANCH_LOGIN = TLSUBBRID, @DEP_LOGIN = SECUR_CODE FROM TL_USER WHERE TLNANME = @p_USER_LOGIN

			insert into @TABLE_BRANCH SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID_Funct](@BRANCH_LOGIN)

			DECLARE @TONG_CHI_PHI FLOAT = 0

			DECLARE @RESULT TABLE(

				[MADE] FLOAT,

				[DOING] FLOAT,

				[RESIDUAL] FLOAT

			)

			INSERT INTO @RESULT select

			ISNULL(SUM(SD.NS_SU_DUNG),0) AS [MADE],

			ISNULL(SUM(NSDT.NS_DANG_TRINH),0) AS [DOING] ,

			--SUM(NS_CHO.NS_TRANSFER) AS NS_TRANSFER , SUM(NS_NHAN.NS_RECIVER) AS NS_TRANSFER, 

			--SUM(NS_SD_TT.NS_SD_REAL) AS DA_THUC_HIEN,

			ISNULL(SUM(A.AMT_APP),0) + ISNULL(SUM(NS_NHAN.NS_RECIVER),0) - ISNULL(SUM(NS_CHO.NS_TRANSFER),0) - ISNULL(SUM(SD.NS_SU_DUNG),0) - ISNULL(SUM(NSDT.NS_DANG_TRINH),0) AS [RESIDUAL]

			FROM PL_TRADEDETAIL

			A	

			LEFT JOIN PL_MASTER M ON A.PLAN_ID =M.PLAN_ID

			LEFT JOIN CM_GOODS G ON A.GOODS_ID=G.GD_ID

			LEFT JOIN CM_GOODSTYPE GT ON GT.GD_TYPE_CODE IN (SELECT G.GD_TYPE_CODE FROM WSISPLIT(G.GD_CODE,'.') J JOIN CM_GOODSTYPE G ON G.GD_TYPE_CODE = J.VALUE)

			LEFT JOIN

			(

				SELECT A.TRADE_ID, SUM (A.TOTAL_AMT) AS NS_SU_DUNG

				FROM PL_REQUEST_DOC_DT A

				INNER JOIN PL_REQUEST_DOC B ON A.REQ_ID = B.REQ_ID

				WHERE 1 = 1

				AND ((@p_FILTER = 'cumulative' AND  B.EFFEC_DT <= CONVERT(datetime,@p_DATE,103)) OR (@p_FILTER = 'year' AND  YEAR(B.EFFEC_DT) <= CONVERT(INT,@p_YEAR)))

				AND B.PROCESS_ID ='APPROVE'

				GROUP BY A.TRADE_ID

			) SD ON SD.TRADE_ID = A.TRADE_ID

			LEFT JOIN

			(

				SELECT A.TRADE_ID, SUM (A.TOTAL_AMT) AS NS_DANG_TRINH

				FROM PL_REQUEST_DOC_DT A

				INNER JOIN PL_REQUEST_DOC B ON A.REQ_ID = B.REQ_ID

				INNER JOIN PL_REQUEST_PROCESS D ON B.REQ_ID = D.REQ_ID AND D.[STATUS] = 'C'

				WHERE ((@p_FILTER = 'cumulative' AND  D.APPROVE_DT <= CONVERT(datetime,@p_DATE,103)) OR (@p_FILTER = 'year' AND  YEAR(D.APPROVE_DT) <= CONVERT(INT,@p_YEAR)))

				AND D.PROCESS_ID NOT IN ('APPROVE','APPNEW')

				GROUP BY A.TRADE_ID

			) NSDT ON NSDT.TRADE_ID = A.TRADE_ID

			---- NS MANG ĐI CHO

			LEFT JOIN

			(

				SELECT A.FR_TRADE_ID, SUM (A.TOTAL_AMT) AS NS_TRANSFER

				FROM PL_REQUEST_TRANSFER A

				INNER JOIN PL_REQUEST_DOC B ON A.REQ_DOC_ID = B.REQ_ID

				WHERE 1=1 

				AND ((@p_FILTER = 'cumulative' AND  B.EFFEC_DT <= CONVERT(datetime,@p_DATE,103)) OR (@p_FILTER = 'year' AND  YEAR(B.EFFEC_DT) <= CONVERT(INT,@p_YEAR)))

				AND B.PROCESS_ID ='APPROVE'

				GROUP BY A.FR_TRADE_ID

			) NS_CHO ON NS_CHO.FR_TRADE_ID = A.TRADE_ID

			---- NS NHẬN ĐƯỢC

			LEFT JOIN

			(

				SELECT A.TO_TRADE_ID, SUM (A.TOTAL_AMT) AS NS_RECIVER

				--- BẢNG SỬ DỤNG NGÂN SÁCH

				FROM PL_REQUEST_TRANSFER A

				--- BẢNG CHỨA THÔNG TIN CỘT MỐC NGÀY HOÀN TẤT TỜ TRÌNH

				INNER JOIN PL_REQUEST_DOC B ON A.REQ_DOC_ID = B.REQ_ID

				WHERE 1=1

				AND ((@p_FILTER = 'cumulative' AND  B.EFFEC_DT <= CONVERT(datetime,@p_DATE,103)) OR (@p_FILTER = 'year' AND  YEAR(B.EFFEC_DT) <= CONVERT(INT,@p_YEAR)))

					AND B.PROCESS_ID ='APPROVE'

				GROUP BY A.TO_TRADE_ID

			) NS_NHAN ON NS_NHAN.TO_TRADE_ID = A.TRADE_ID

			--- NS SỬ DỤNG THỰC TẾ

--			LEFT JOIN

--			(

--			SELECT A.TRADE_ID, SUM (A.AMT_EXE) AS NS_SD_REAL

--			FROM TR_REQ_PAY_BUDGET A

--			INNER JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID

--			WHERE 1=1 

--			--AND B.APPROVE_DT_KT <= CONVERT(datetime,@p_DATE,103) 

--			AND B.AUTH_STATUS_KT ='A'

--

--			AND

--			(

--			EXISTS(

--						SELECT PLAN_TYPE_ID FROM CM_PLAN_TYPE PL WHERE PLAN_TYPE_ID 

--						IN

--					(SELECT PLAN_TYPE_ID FROM PL_MASTER PLM

--						LEFT JOIN PL_TRADEDETAIL TRD ON TRD.GOODS_ID = GD_ID

--						WHERE PL.PLAN_TYPE_ID = @p_PLAN_TYPE_ID

--						AND PLM.PLAN_ID = TRD.PLAN_ID

--					) 

--				)OR @p_PLAN_TYPE_ID IS NULL OR @p_PLAN_TYPE_ID = ''

--			)

--			AND (B.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR B.DEP_ID = @p_BRANCH_ID)

--			AND ((EXISTS(SELECT A.ID FROM PL_ROLE_DATA_CONFIG A WHERE A.ROLE_TYPE = 'DASHBOARD_ALL' AND A.BRANCH_ID = @BRANCH_LOGIN AND A.DEP_ID = @DEP_LOGIN)

--          AND EXISTS(SELECT BRANCH_ID FROM @TABLE_BRANCH X WHERE X.BRANCH_ID = B.BRANCH_ID))

--        OR (NOT EXISTS(SELECT A.ID FROM PL_ROLE_DATA_CONFIG A WHERE A.ROLE_TYPE = 'DASHBOARD_ALL' AND A.BRANCH_ID = @BRANCH_LOGIN AND A.DEP_ID = @DEP_LOGIN)

--          AND B.BRANCH_ID = @BRANCH_LOGIN AND (B.DEP_ID = @DEP_LOGIN OR @BRANCH_LOGIN <> 'DV0001')))

--			--AND 

--			GROUP BY A.TRADE_ID

--

--			) NS_SD_TT ON NS_SD_TT.TRADE_ID = A.TRADE_ID

			WHERE 1=1

			AND (M.BRANCH_ID= @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR M.DEPT_ID = @p_BRANCH_ID)

			AND ((EXISTS(SELECT A.ID FROM PL_ROLE_DATA_CONFIG A WHERE A.ROLE_TYPE = 'DASHBOARD_ALL' AND A.BRANCH_ID = @BRANCH_LOGIN AND A.DEP_ID = @DEP_LOGIN)

          AND EXISTS(SELECT BRANCH_ID FROM @TABLE_BRANCH X WHERE X.BRANCH_ID = M.BRANCH_ID))

        OR (NOT EXISTS(SELECT A.ID FROM PL_ROLE_DATA_CONFIG A WHERE A.ROLE_TYPE = 'DASHBOARD_ALL' AND A.BRANCH_ID = @BRANCH_LOGIN AND A.DEP_ID = @DEP_LOGIN)

          AND M.BRANCH_ID = @BRANCH_LOGIN AND (M.DEPT_ID = @DEP_LOGIN OR @BRANCH_LOGIN <> 'DV0001')))

			AND (M.PLAN_TYPE_ID= @p_PLAN_TYPE_ID OR @p_PLAN_TYPE_ID IS NULL OR @p_PLAN_TYPE_ID = '')

			AND (GT.GD_TYPE_ID= @p_GD_TYPE_ID OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')

			AND (YEAR(M.YEAR)=@p_YEAR OR @p_YEAR IS NULL OR @p_YEAR = '' )

			select @TONG_CHI_PHI = CASE WHEN ISNULL(MADE,0) + ISNULL(DOING,0) + ISNULL(RESIDUAL,0) = 0 THEN NULL ELSE ISNULL(MADE,0) + ISNULL(DOING,0) + ISNULL(RESIDUAL,0) END from @RESULT 

			----------CHUYỂN SANG %-----------

			UPDATE @RESULT SET 

			MADE = ROUND(MADE * 100 / @TONG_CHI_PHI,2),

			DOING = ROUND(DOING * 100 / @TONG_CHI_PHI,2),

			RESIDUAL = ROUND(RESIDUAL * 100 / @TONG_CHI_PHI,2)

			----------CHUYỂN SANG %-----------

			SELECT * FROM @RESULT

END