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

ALTER   PROC [dbo].[DB_STATUS_ASSET_QUANTITY_PIE]

@p_USER_LOGIN VARCHAR(250) = NULL,

@p_BRANCH_ID VARCHAR(15) = NULL,

@p_YEAR VARCHAR(15) = NULL

AS

BEGIN

	DECLARE @RESULT TABLE (

		TYPE_ID 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

	INSERT INTO @RESULT

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

	FROM ASS_MASTER A

	WHERE 1=1 AND A.RECORD_STATUS = '1' AND A.AUTH_STATUS = 'A' AND A.ENTRY_BOOKED = 'Y' AND A.AMORT_STATUS <> 'DTL'

	AND (A.BRANCH_ID = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '' OR A.DEPT_ID = @p_BRANCH_ID OR (A.TYPE_ID ='TSCD' AND ISNULL(A.BRANCH_CREATE,'') =@p_BRANCH_ID) )

	AND (YEAR(A.BUY_DATE_KT) <= @p_YEAR OR @p_YEAR IS NULL OR @p_YEAR = '' )

	AND ((A.[TYPE_ID] = 'TSCD' AND  YEAR(A.LIQUIDATION_DT) >=  @p_YEAR OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT ='') 

							OR (A.[TYPE_ID] = 'CCLD' AND A.AMORT_STATUS <> 'VNM' AND  YEAR(A.LIQUIDATION_DT) >  @p_YEAR OR A.LIQUIDATION_DT IS NULL OR A.LIQUIDATION_DT =''))

	AND (YEAR(USE_DATE_KT) <=@p_YEAR OR (A.TYPE_ID ='TSCD' AND  (A.USE_DATE_KT IS NULL OR A.USE_DATE_KT ='')))

	GROUP BY A.TYPE_ID

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

	-------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 % -------------

	SELECT * FROM @RESULT

END