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

declare	@p_USER_LOGIN VARCHAR(250) = NULL,

@p_BRANCH_ID VARCHAR(15) = NULL,

@p_YEAR VARCHAR(15) = NULL

	DECLARE @RESULT TABLE (

		AMORT_STATUS VARCHAR(15),

		TOTAL_COUNT FLOAT

	)

	DECLARE @BRANCH_LOGIN varchar(15) ='', @DEP_LOGIN varchar(15) =''

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

  IF(NOT EXISTS(SELECT * FROM CM_DEPARTMENT cd WHERE cd.BRANCH_ID = @BRANCH_LOGIN AND cd.DEP_ID = @DEP_LOGIN AND (cd.DEP_CODE = '0690604' OR cd.DEP_CODE = '0690802' OR cd.DEP_CODE = '0690803')))

  BEGIN

    IF(@BRANCH_LOGIN = 'DV0001') SET @p_BRANCH_ID = @DEP_LOGIN

    ELSE SET @p_BRANCH_ID = @BRANCH_LOGIN

  END

	IF(EXISTS(	SELECT A.AMORT_STATUS AS AMORT_STATUS, COUNT(A.AMORT_STATUS) AS TOTAL_COUNT

				FROM ASS_MASTER A

				WHERE 1=1

				--AND (A.AMORT_STATUS = 'DKH' OR A.AMORT_STATUS = 'CKH' OR A.AMORT_STATUS = 'VNM' 

				--OR (A.AMORT_STATUS <> 'DKH' OR A.AMORT_STATUS <> 'CKH' OR A.AMORT_STATUS <> 'VNM'))

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

				--AND (YEAR(A.BUY_DATE_KT) <= CONVERT(INT,@p_YEAR) OR @p_YEAR IS NULL OR @p_YEAR = '' )

				GROUP BY A.AMORT_STATUS)

		)

		BEGIN

				INSERT INTO @RESULT

				SELECT A.AMORT_STATUS AS AMORT_STATUS, COUNT(A.AMORT_STATUS) AS TOTAL_COUNT

				FROM ASS_MASTER A

				WHERE 1=1

				--AND (A.AMORT_STATUS = 'DKH' OR A.AMORT_STATUS = 'CKH' OR A.AMORT_STATUS = 'VNM' 

				--OR (A.AMORT_STATUS <> 'DKH' OR A.AMORT_STATUS <> 'CKH' OR A.AMORT_STATUS <> 'VNM'))

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

				--AND (YEAR(A.BUY_DATE_KT) <= CONVERT(INT,@p_YEAR) OR @p_YEAR IS NULL OR @p_YEAR = '' )

				GROUP BY A.AMORT_STATUS

			------------- Tài sản chờ thu hồi-----------------------------

				DECLARE @TOTAL_COUNT_CTH INT = 0

				SELECT @TOTAL_COUNT_CTH = COUNT(*) FROM ASS_MASTER A 

				INNER JOIN ASS_COLLECT_MULTI_DT B ON A.ASSET_ID = B.ASSET_ID 

				INNER JOIN ASS_COLLECT_MULTI_MASTER C ON B.COL_MULTI_MASTER_ID = C.COL_MULTI_MASTER_ID 

				WHERE C.AUTH_STATUS_KT <> 'A' AND (C.AUTH_STATUS) ='A' -- LUCTV 05-03-23 BO SUNG THEM DIEU KIEN 

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

				--AND (YEAR(A.BUY_DATE_KT) <= CONVERT(INT,@p_YEAR) OR @p_YEAR IS NULL OR @p_YEAR = '' )

				------------- Tài sản chờ thu hồi-----------------------------

				------------- Tài sản đang sử dụng-----------------------------

				DECLARE @TOTAL_COUNT_DSD INT = 0

				SELECT @TOTAL_COUNT_DSD = SUM(TOTAL_COUNT) FROM @RESULT WHERE AMORT_STATUS IN ('KHX','DKH','CKH','NKH','KKH','DPB')

				------------- Tài sản đang sử dụng-----------------------------

				------------- Tài sản tồn kho-----------------------------

				DECLARE @TOTAL_COUNT_TK INT = 0

				SELECT @TOTAL_COUNT_TK = SUM(TOTAL_COUNT) FROM @RESULT WHERE AMORT_STATUS IN ('VNM')

				------------- Tài sản tồn kho-----------------------------

				DELETE FROM @RESULT

				INSERT INTO @RESULT( AMORT_STATUS, TOTAL_COUNT) VALUES('CTH', @TOTAL_COUNT_CTH)

				INSERT INTO @RESULT( AMORT_STATUS, TOTAL_COUNT) VALUES('DSD', @TOTAL_COUNT_DSD)

				INSERT INTO @RESULT( AMORT_STATUS, TOTAL_COUNT) VALUES('TK', @TOTAL_COUNT_TK)

				-------Chuyển từ số lượng sang % -------------

				--DECLARE @SUM_TOTAL_COUNT INT = 0

				--SELECT @SUM_TOTAL_COUNT = SUM(TOTAL_COUNT) FROM @RESULT

				--UPDATE @RESULT SET TOTAL_COUNT = ROUND(TOTAL_COUNT * 100 / @SUM_TOTAL_COUNT, 2)

				-------Chuyển từ số lượng sang % -------------

		END

		ELSE

		BEGIN

			INSERT INTO @RESULT( AMORT_STATUS, TOTAL_COUNT) VALUES('CTH', 0)

			INSERT INTO @RESULT( AMORT_STATUS, TOTAL_COUNT) VALUES('DSD', 0)

			INSERT INTO @RESULT( AMORT_STATUS, TOTAL_COUNT) VALUES('TK', 0)

		END

	SELECT * FROM @RESULT