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_VALUE_BAR]

@p_USER_LOGIN VARCHAR(250) = NULL,

@p_TYPE_ID VARCHAR(5) = NULL,

@p_GROUP_ID VARCHAR(15) = NULL,

@p_BRANCH_ID VARCHAR(15) = NULL,

@p_FROM_MONTH VARCHAR(4) = NULL,

@p_TO_MONTH VARCHAR(4) = NULL,

@p_FROM_YEAR VARCHAR(10) = NULL,

@p_TO_YEAR VARCHAR(10) = NULL,

@p_FILTER VARCHAR(100) = NULL

AS

BEGIN

	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(@p_TYPE_ID = 'ALL') SET @p_TYPE_ID=''

	SELECT DISTINCT 

		YEAR(USE_DATE_KT) AS [YEAR],ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) AS BUY_PRICE

	FROM ASS_MASTER A

	WHERE 1=1

	AND A.BUY_DATE_KT IS NOT NULL

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

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

	AND (A.TYPE_ID=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')

	AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR A.GROUP_ID IN (SELECT GROUP_ID FROM ASS_GROUP WHERE PARENT_ID = @p_GROUP_ID))

	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 (MONTH(A.USE_DATE_KT)>= CAST(@p_FROM_MONTH AS INT) OR CAST(@p_FROM_MONTH AS INT) IS NULL OR CAST(@p_FROM_MONTH AS INT) ='')

	AND (MONTH(A.USE_DATE_KT)<= CAST(@p_TO_MONTH AS INT) OR CAST(@p_TO_MONTH AS INT) IS NULL OR CAST(@p_TO_MONTH AS INT) ='')

	GROUP BY YEAR(USE_DATE_KT)

	ORDER BY YEAR(USE_DATE_KT) ASC

	DECLARE @YEAR INT

	DECLARE @TABLE TABLE(

		USE_DATE_KT NVARCHAR(100) NULL,

		BUY_PRICE DECIMAL(18,2) NULL,

		[YEAR] VARCHAR(10) NULL

	)

	DECLARE CUR_FILTER_YEAR CURSOR FOR

	SELECT DISTINCT 

		YEAR(USE_DATE_KT) AS USE_DATE_KT

	FROM ASS_MASTER A WHERE 1=1

	AND USE_DATE_KT IS NOT NULL

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

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

	AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')

	--AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='')

	AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR A.GROUP_ID IN (SELECT GROUP_ID FROM ASS_GROUP WHERE PARENT_ID = @p_GROUP_ID))

	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 (MONTH(A.USE_DATE_KT)>= CAST(@p_FROM_MONTH AS INT) OR CAST(@p_FROM_MONTH AS INT) IS NULL OR CAST(@p_FROM_MONTH AS INT) ='')

	AND (MONTH(A.USE_DATE_KT)<= CAST(@p_TO_MONTH AS INT) OR CAST(@p_TO_MONTH AS INT) IS NULL OR CAST(@p_TO_MONTH AS INT) ='')

	OPEN CUR_FILTER_YEAR

	FETCH NEXT FROM CUR_FILTER_YEAR

		  INTO @YEAR

	WHILE @@FETCH_STATUS = 0

	BEGIN

		INSERT INTO @TABLE(USE_DATE_KT,BUY_PRICE,[YEAR])

		(

			SELECT N'0 - 3 năm' USE_DATE_KT,ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) BUY_PRICE,@YEAR AS [YEAR] FROM ASS_MASTER A

			WHERE 1=1

			AND (@YEAR-YEAR(USE_DATE_KT) >= 0)

			AND (@YEAR-YEAR(USE_DATE_KT) <= 3)

			AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')

			AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='')

			--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 (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR A.GROUP_ID IN (SELECT GROUP_ID FROM ASS_GROUP WHERE PARENT_ID = @p_GROUP_ID))

			AND (MONTH(A.USE_DATE_KT)>= CAST(@p_FROM_MONTH AS INT) OR CAST(@p_FROM_MONTH AS INT) IS NULL OR CAST(@p_FROM_MONTH AS INT) ='')

			AND (MONTH(A.USE_DATE_KT)<= CAST(@p_TO_MONTH AS INT) OR CAST(@p_TO_MONTH AS INT) IS NULL OR CAST(@p_TO_MONTH AS INT) ='')

		)

		UNION ALL

		(

			SELECT N'3 - 5 năm' USE_DATE_KT,ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) BUY_PRICE,@YEAR AS [YEAR] FROM ASS_MASTER A

			WHERE 1=1

			AND (@YEAR-YEAR(USE_DATE_KT) > 3)

			AND (@YEAR-YEAR(USE_DATE_KT) <= 5)

			AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')

			--AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='')

			AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR A.GROUP_ID IN (SELECT GROUP_ID FROM ASS_GROUP WHERE PARENT_ID = @p_GROUP_ID))

			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 (MONTH(A.USE_DATE_KT)>= CAST(@p_FROM_MONTH AS INT) OR CAST(@p_FROM_MONTH AS INT) IS NULL OR CAST(@p_FROM_MONTH AS INT) ='')

			AND (MONTH(A.USE_DATE_KT)<= CAST(@p_TO_MONTH AS INT) OR CAST(@p_TO_MONTH AS INT) IS NULL OR CAST(@p_TO_MONTH AS INT) ='')

		)

		UNION ALL

		(

			SELECT N'Trên 5 năm' USE_DATE_KT,ROUND(ISNULL(SUM(BUY_PRICE),0)/1000000000,2) BUY_PRICE,@YEAR AS [YEAR] FROM ASS_MASTER A

			WHERE 1=1

			AND (@YEAR-YEAR(USE_DATE_KT) > 5)

			AND (A.[TYPE_ID]=@p_TYPE_ID OR @p_TYPE_ID IS NULL OR @p_TYPE_ID ='')

			--AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='')

			AND (A.GROUP_ID=@p_GROUP_ID OR @p_GROUP_ID IS NULL OR @p_GROUP_ID ='' OR A.GROUP_ID IN (SELECT GROUP_ID FROM ASS_GROUP WHERE PARENT_ID = @p_GROUP_ID))

			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 (MONTH(A.USE_DATE_KT)>= CAST(@p_FROM_MONTH AS INT) OR CAST(@p_FROM_MONTH AS INT) IS NULL OR CAST(@p_FROM_MONTH AS INT) ='')

			AND (MONTH(A.USE_DATE_KT)<= CAST(@p_TO_MONTH AS INT) OR CAST(@p_TO_MONTH AS INT) IS NULL OR CAST(@p_TO_MONTH AS INT) ='')

		)

		FETCH NEXT FROM CUR_FILTER_YEAR

			  INTO @YEAR

	END

	CLOSE CUR_FILTER_YEAR

	DEALLOCATE CUR_FILTER_YEAR

	SELECT * FROM @TABLE	

	ORDER BY [YEAR] ASC

END