-- BIEU DO O1 TINH TRANG TAI SAN (SO LUONG)
ALTER   PROC [dbo].[DB_STATUS_ASSET_QUANTITY_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(2) = NULL,
@p_TO_MONTH VARCHAR(2) = NULL,
@p_FROM_YEAR VARCHAR(4) = NULL,
@p_TO_YEAR VARCHAR(4) = 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_FILTER = 'year')
  BEGIN
	SELECT DISTINCT 
		YEAR(A.USE_DATE_KT) AS YEAR, COUNT(*) AS TOTAL_COUNT
	FROM ASS_MASTER A
	WHERE 1=1
	AND A.USE_DATE_KT IS NOT NULL
	AND (YEAR(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(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.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,
		TOTAL_COUNT INT 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(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(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.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,TOTAL_COUNT,[YEAR])
		(
			SELECT N'0 - 3 năm' BUY_DATE_KT,COUNT(*) AS TOTAL_COUNT,@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 (MONTH(A.USE_DATE_KT)>= @p_FROM_MONTH OR @p_FROM_MONTH IS NULL OR @p_FROM_MONTH ='')
			AND (MONTH(A.USE_DATE_KT)<= @p_TO_MONTH OR @p_TO_MONTH IS NULL OR @p_TO_MONTH ='')
		)
		UNION ALL
		(
			SELECT N'3 - 5 năm' USE_DATE_KT,COUNT(*) AS TOTAL_COUNT,@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.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)>= @p_FROM_MONTH OR @p_FROM_MONTH IS NULL OR @p_FROM_MONTH ='')
			AND (MONTH(A.USE_DATE_KT)<= @p_TO_MONTH OR @p_TO_MONTH IS NULL OR @p_TO_MONTH ='')	
		)
		UNION ALL
		(
			SELECT N'Trên 5 năm' USE_DATE_KT,COUNT(*) AS TOTAL_COUNT,@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.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)>= @p_FROM_MONTH OR @p_FROM_MONTH IS NULL OR @p_FROM_MONTH ='')
			AND (MONTH(A.USE_DATE_KT)<= @p_TO_MONTH OR @p_TO_MONTH IS NULL OR @p_TO_MONTH ='')	
		)

		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
  ELSE IF(@p_FILTER = 'month')
  BEGIN
	SELECT DISTINCT 
		MONTH(A.USE_DATE_KT) AS [YEAR], COUNT(*) AS TOTAL_COUNT
	FROM ASS_MASTER A
	WHERE 1=1
	AND A.USE_DATE_KT IS NOT NULL
	AND (YEAR(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 (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 (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 MONTH(USE_DATE_KT)
	ORDER BY MONTH(USE_DATE_KT) ASC

	DECLARE @MONTH INT
	DECLARE @TABLE_MONTH TABLE(
		USE_DATE_KT NVARCHAR(100) NULL,
		TOTAL_COUNT INT NULL,
		[YEAR] INT NULL
	)
	DECLARE CUR_FILTER_YEAR CURSOR FOR
	SELECT DISTINCT 
		MONTH(USE_DATE_KT) AS USE_DATE_KT
	FROM ASS_MASTER A WHERE 1=1
	AND USE_DATE_KT IS NOT NULL
	AND (YEAR(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 (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 (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 @MONTH

	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @TABLE_MONTH(USE_DATE_KT,TOTAL_COUNT,[YEAR])
		(
			SELECT N'0 - 3 năm' BUY_DATE_KT,COUNT(*) AS TOTAL_COUNT,@MONTH AS [YEAR] FROM ASS_MASTER A
			WHERE 1=1
			AND (CAST(@p_TO_YEAR AS INT)-YEAR(USE_DATE_KT) >= 0)
			AND (MONTH(A.USE_DATE_KT) <= CAST(@MONTH AS INT))
			AND (CAST(@p_TO_YEAR AS INT)-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)
		)
		UNION ALL
		(
			SELECT N'3 - 5 năm' USE_DATE_KT,COUNT(*) AS TOTAL_COUNT,@MONTH AS [YEAR] FROM ASS_MASTER A
			WHERE 1=1
			AND (CAST(@p_TO_YEAR AS INT)-YEAR(USE_DATE_KT) > 3)
			AND (MONTH(A.USE_DATE_KT) <= CAST(@MONTH AS INT))
			AND (CAST(@p_TO_YEAR AS INT)-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.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID)
		)
		UNION ALL
		(
			SELECT N'Trên 5 năm' USE_DATE_KT,COUNT(*) AS TOTAL_COUNT,@MONTH AS [YEAR] FROM ASS_MASTER A
			WHERE 1=1
			AND (CAST(@p_TO_YEAR AS INT)-YEAR(USE_DATE_KT) > 5)
			AND (MONTH(A.USE_DATE_KT) <= CAST(@MONTH 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.BRANCH_ID=@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='' OR A.DEPT_ID = @p_BRANCH_ID)
		)

		FETCH NEXT FROM CUR_FILTER_YEAR
			  INTO @MONTH
	END

	CLOSE CUR_FILTER_YEAR
	DEALLOCATE CUR_FILTER_YEAR

	SELECT * FROM @TABLE_MONTH
	ORDER BY [YEAR] ASC
  END
END