USE [gAMSPro_VCCB_v2]
GO
/****** Object:  StoredProcedure [dbo].[rpt_THONG_KE_THIET_BI_CNTT_2018_TONGHOP_BanViet]    Script Date: 10/26/2020 2:26:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_THONG_KE_THIET_BI_CNTT_2018_TONGHOP_BanViet] @p_Fromdate VARCHAR(10) =NULL, @p_Todate VARCHAR(10) =NULL,
    @p_Branch_ID VARCHAR(15) =NULL, @p_Level VARCHAR(10) =NULL, @p_BRANCH_LOGIN VARCHAR(15) =NULL, @p_Div_id VARCHAR(15) =NULL,
    @p_User_ID VARCHAR(15) =NULL, @p_AssType VARCHAR(15) =NULL
-------------------------------
AS
DECLARE @tmp_branch TABLE(BRANCH_ID VARCHAR(15));
INSERT INTO @tmp_branch
SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_Branch_ID);
DECLARE @tmp_login TABLE(BRANCH_ID VARCHAR(15));
INSERT INTO @tmp_login
SELECT BRANCH_ID FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN;

DECLARE @returnTable TABLE
(
DEP_ID VARCHAR(15),
DEP_CODE VARCHAR(15),
DEP_LV1 VARCHAR(15),
DEP_LV2 VARCHAR(15),
BRANCH_CODE VARCHAR(15),
DEP_NAME NVARCHAR(200),

QTY INT,
SL_LAPTOP INT,
SL_PC INT,
SL_PC_RAP INT,
SL_MAYIN INT,
SL_MAY_SCAN INT,
SL_UPS INT,



SL_LAPTOP_0_3 INT,
SL_PC_0_3 INT,
SL_PC_RAP_0_3 INT,
SL_MAYIN_0_3 INT,
SL_MAY_SCAN_0_3 INT,
SL_UPS_0_3 INT,

SL_LAPTOP_3_5 INT,
SL_PC_3_5 INT,
SL_PC_RAP_3_5 INT,
SL_MAYIN_3_5 INT,
SL_MAY_SCAN_3_5 INT,
SL_UPS_3_5 INT,

SL_LAPTOP_5_ INT,
SL_PC_5_ INT,
SL_PC_RAP_5_ INT,
SL_MAYIN_5_ INT,
SL_MAY_SCAN_5_ INT,
SL_UPS_5_ INT,
-- luctv 15 10 2019 bo sung them cac so lieu > 7 nam
SL_LAPTOP_7_ INT,
SL_PC_7_ INT,
SL_PC_RAP_7_ INT,
SL_MAYIN_7_ INT,
SL_MAY_SCAN_7_ INT,
SL_UPS_7_ INT
);

--LUCTV 29 08 2019 BO SUNG THEM BIEN BRANCH_CODE_FAT DE LAY MA DON VI CHA CUA PHONG BAN, DON VI
DECLARE @TMP TABLE(ID VARCHAR(15), BRANCH_CODE VARCHAR(15), DEP_NAME NVARCHAR(200), BRANCH_ID VARCHAR(15), BRANCH_CODE_FAT VARCHAR(15));
-- LUCTV 28-08-2019 INSERT BANG TEMP LAN THU NHAT BAO GOM DANH SACH CAC PHONG BAN CUA DON VI, DU CHON ALL HAY UNIT
INSERT INTO @TMP
SELECT
    A.DEP_CODE, B.BRANCH_CODE, MAX(A.DEP_NAME) AS DEP_NAME, A.BRANCH_ID, BF.BRANCH_CODE
  FROM CM_DEPARTMENT A
	   LEFT JOIN CM_BRANCH B ON A.BRANCH_ID =B.BRANCH_ID
	   LEFT JOIN CM_BRANCH BF ON B.FATHER_ID = BF.BRANCH_ID
 WHERE(A.DEP_ID = @p_Div_id OR @p_Div_id IS NULL OR @p_Div_id ='')
	 AND (A.BRANCH_ID =@p_Branch_ID)
  --((@P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))
 	--OR (@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
 	--AND ((@P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))
 	--OR (@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 
 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')
 GROUP BY A.DEP_CODE, B.BRANCH_CODE, A.BRANCH_ID,BF.BRANCH_CODE
 --- LUCTV 28-08-2019 INSERT BANG TEMP LAN THU 2 BAO GOM DANH SACH CAC DON VI TRUC THUOC CUA DON VI DC CHON (MA PHONG BAN SE = NULL, TEN PHONG BAN = TEN DON VI)
 INSERT INTO @TMP
SELECT
    NULL DEP_CODE, B.BRANCH_CODE, B.BRANCH_NAME DEP_NAME,B.BRANCH_ID, BF.BRANCH_CODE
    FROM CM_BRANCH B
	LEFT JOIN CM_BRANCH BF ON B.FATHER_ID = BF.BRANCH_ID
    --LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID
 WHERE B.BRANCH_ID <> @p_Branch_ID AND B.BRANCH_ID <> @p_BRANCH_LOGIN AND B.BRANCH_CODE <>'000'
 AND 
  ((@P_LEVEL = 'ALL' AND B.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))
 	OR (@P_LEVEL = 'UNIT' AND B.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
 	--AND ((@P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))
 	--OR (@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 
 	---OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> ''
 ORDER BY B.BRANCH_CODE, BF.BRANCH_CODE DESC

--PRINT [@TMP]
DECLARE @COUNT INT=0;
DECLARE @DEP_CODE VARCHAR(15) =NULL;
DECLARE @BRANCH_CODE VARCHAR(15) =NULL;
DECLARE @DEP_NAME NVARCHAR(200) =NULL;
DECLARE @BRANCH_ID NVARCHAR(200) =NULL;
DECLARE @BRANCH_CODE_FAT VARCHAR(20) =NULL;
DECLARE @SL INT=0;
DECLARE @GROUP_GENERAL_ID VARCHAR(15) =NULL;
WHILE((SELECT COUNT(*)FROM @TMP)>0)BEGIN
	
    SET @COUNT=@COUNT+1;
    SET @DEP_CODE=(SELECT TOP 1. ID FROM @TMP);

    SET @BRANCH_CODE=(SELECT TOP 1. BRANCH_CODE FROM @TMP);

    SET @DEP_NAME=(SELECT TOP 1. DEP_NAME FROM @TMP);

	SET @BRANCH_ID = (SELECT TOP 1. BRANCH_ID FROM @TMP);

	SET @BRANCH_CODE_FAT =(SELECT TOP 1. BRANCH_CODE_FAT FROM @TMP);

	PRINT @BRANCH_CODE

    DECLARE @TMP_GROUP_GENERAL TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);

    DECLARE @SL_LAPTOP INT=0;
    DECLARE @SL_PC INT=0;
    DECLARE @SL_PC_RAP INT=0;
    DECLARE @SL_MAYIN INT=0;
    DECLARE @SL_MAY_SCAN INT=0;
    DECLARE @SL_UPS INT=0;

    --0 đến 3 năm -- LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI TIEN HANH GROUP THEO MA DON VI
    DECLARE @TMP_GROUP_GENERAL_0_3 TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);
    INSERT INTO @TMP_GROUP_GENERAL_0_3
    SELECT
        B.GROUP_GENERAL_ID, COUNT(B.GROUP_GENERAL_ID) AS SL
      FROM dbo.ASS_MASTER A
           INNER JOIN dbo.ASS_GROUP_GENERAL B ON B.GROUP_ID=A.GROUP_ID
           LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID
		   LEFT JOIN ASS_STATUS ST ON ST.STATUS_ID = A.ASS_STATUS
     WHERE 1= 1 AND ((@DEP_CODE IS NOT NULL AND CT.DEP_CODE=@DEP_CODE AND A.BRANCH_ID = @BRANCH_ID) OR @DEP_CODE IS NULL AND A.BRANCH_ID = @BRANCH_ID)
	 AND DATEDIFF(YEAR, A.USE_DATE, GETDATE())<=3
	 AND A.AMORT_STATUS NOT IN ( 'DTL', 'VNM')
	AND ((@DEP_CODE IS NULL 
	AND ((@p_Level = 'ALL' AND   A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))
 	OR (@p_Level = 'UNIT'  AND  A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
 	--AND ((@p_Level = 'ALL'  AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))
 	--OR (@p_Level = 'UNIT' AND  A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 
 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')
	) OR A.BRANCH_ID = @BRANCH_ID)
    GROUP BY B.GROUP_GENERAL_ID;

    DECLARE @SL_LAPTOP_0_3 INT=0;
    DECLARE @SL_PC_0_3 INT=0;
    DECLARE @SL_PC_RAP_0_3 INT=0;
    DECLARE @SL_MAYIN_0_3 INT=0;
    DECLARE @SL_MAY_SCAN_0_3 INT=0;
    DECLARE @SL_UPS_0_3 INT=0;
    SELECT
        @SL_LAPTOP_0_3=SOLUONG
      FROM @TMP_GROUP_GENERAL_0_3
     WHERE GROUP_GENERAL_ID='ASGG00000000001';
    SELECT
        @SL_PC_0_3=SOLUONG
      FROM @TMP_GROUP_GENERAL_0_3
     WHERE GROUP_GENERAL_ID='ASGG00000000002';

    SELECT
        @SL_PC_RAP_0_3=SOLUONG
      FROM @TMP_GROUP_GENERAL_0_3
     WHERE GROUP_GENERAL_ID='ASGG00000000004';

    SELECT
        @SL_MAYIN_0_3=SOLUONG
      FROM @TMP_GROUP_GENERAL_0_3
     WHERE GROUP_GENERAL_ID='ASGG00000000003';
    SELECT
        @SL_MAY_SCAN_0_3=SOLUONG
      FROM @TMP_GROUP_GENERAL_0_3
     WHERE GROUP_GENERAL_ID='ASGG00000000005';
    SELECT
        @SL_UPS_0_3=SOLUONG
      FROM @TMP_GROUP_GENERAL_0_3
     WHERE GROUP_GENERAL_ID='ASGG00000000006';

    DELETE FROM @TMP_GROUP_GENERAL_0_3;

    --3 đến 5 năm - LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI TIEN HANH GROUP THEO MA DON VI
    DECLARE @TMP_GROUP_GENERAL_3_5 TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);

    INSERT INTO @TMP_GROUP_GENERAL_3_5
    SELECT
        B.GROUP_GENERAL_ID, COUNT(B.GROUP_GENERAL_ID) AS SL
      FROM dbo.ASS_MASTER A
           INNER JOIN dbo.ASS_GROUP_GENERAL B ON B.GROUP_ID=A.GROUP_ID
           LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID
		   LEFT JOIN ASS_STATUS ST ON ST.STATUS_ID = A.ASS_STATUS
     WHERE 1= 1 AND ((@DEP_CODE IS NOT NULL AND CT.DEP_CODE=@DEP_CODE AND A.BRANCH_ID = @BRANCH_ID) OR @DEP_CODE IS NULL AND A.BRANCH_ID = @BRANCH_ID)
	 AND DATEDIFF(YEAR, A.USE_DATE, GETDATE()) >3  AND DATEDIFF(YEAR, A.USE_DATE, GETDATE())<=5
	 AND A.AMORT_STATUS NOT IN ( 'DTL', 'VNM' )
	AND ((@DEP_CODE IS NULL 
	AND ((@p_Level = 'ALL' AND   A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))
 	OR (@p_Level = 'UNIT'  AND  A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
 	--AND ((@p_Level = 'ALL'  AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))
 	--OR (@p_Level = 'UNIT' AND  A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 
 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')
	) OR A.BRANCH_ID = @BRANCH_ID)
    GROUP BY B.GROUP_GENERAL_ID;

    DECLARE @SL_LAPTOP_3_5 INT=0;
    DECLARE @SL_PC_3_5 INT=0;
    DECLARE @SL_PC_RAP_3_5 INT=0;
    DECLARE @SL_MAYIN_3_5 INT=0;
    DECLARE @SL_MAY_SCAN_3_5 INT=0;
    DECLARE @SL_UPS_3_5 INT=0;


    SELECT
        @SL_LAPTOP_3_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_3_5
     WHERE GROUP_GENERAL_ID='ASGG00000000001';
    SELECT
        @SL_PC_3_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_3_5
     WHERE GROUP_GENERAL_ID='ASGG00000000002';
    SELECT
        @SL_PC_RAP_3_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_3_5
     WHERE GROUP_GENERAL_ID='ASGG00000000004';
    SELECT
        @SL_MAYIN_3_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_3_5
     WHERE GROUP_GENERAL_ID='ASGG00000000003';
    SELECT
        @SL_MAY_SCAN_3_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_3_5
     WHERE GROUP_GENERAL_ID='ASGG00000000005';
    SELECT
        @SL_UPS_3_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_3_5
     WHERE GROUP_GENERAL_ID='ASGG00000000006';

    DELETE FROM @TMP_GROUP_GENERAL_3_5;

    --   >5 - 7  năm - LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI TIEN HANH GROUP THEO MA DON VI
    DECLARE @TMP_GROUP_GENERAL_5 TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);

    INSERT INTO @TMP_GROUP_GENERAL_5
    SELECT
        B.GROUP_GENERAL_ID, COUNT(B.GROUP_GENERAL_ID) AS SL
      FROM dbo.ASS_MASTER A
           INNER JOIN dbo.ASS_GROUP_GENERAL B ON B.GROUP_ID=A.GROUP_ID
           LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID
		   LEFT JOIN ASS_STATUS ST ON ST.STATUS_ID = A.ASS_STATUS
     WHERE 1= 1 AND ((@DEP_CODE IS NOT NULL AND CT.DEP_CODE=@DEP_CODE AND A.BRANCH_ID = @BRANCH_ID) OR @DEP_CODE IS NULL AND A.BRANCH_ID = @BRANCH_ID)
	 AND DATEDIFF(YEAR, A.USE_DATE, GETDATE())>5 AND  DATEDIFF(YEAR, A.USE_DATE, GETDATE()) <=7
     --AND A.AMORTIZED_MONTH>(5 * 12)
	 AND A.AMORT_STATUS NOT IN ( 'DTL', 'VNM' )

		AND ((@DEP_CODE IS NULL 
	AND ((@p_Level = 'ALL'  AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))
 	OR (@p_Level = 'UNIT'   AND  A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
 	--AND ((@p_Level = 'ALL'  AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))
 	--OR (@p_Level = 'UNIT'   AND A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 
 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')  
	) OR A.BRANCH_ID = @BRANCH_ID)
     GROUP BY B.GROUP_GENERAL_ID;

    DECLARE @SL_LAPTOP_5 INT=0;
    DECLARE @SL_PC_5 INT=0;
    DECLARE @SL_PC_RAP_5 INT=0;
    DECLARE @SL_MAYIN_5 INT=0;
    DECLARE @SL_MAY_SCAN_5 INT=0;
    DECLARE @SL_UPS_5 INT=0;

    SELECT
        @SL_LAPTOP_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_5
     WHERE GROUP_GENERAL_ID='ASGG00000000001';
    SELECT
        @SL_PC_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_5
     WHERE GROUP_GENERAL_ID='ASGG00000000002';
    SELECT
        @SL_PC_RAP_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_5
     WHERE GROUP_GENERAL_ID='ASGG00000000004';
    SELECT
        @SL_MAYIN_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_5
     WHERE GROUP_GENERAL_ID='ASGG00000000003';
    SELECT
        @SL_MAY_SCAN_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_5
     WHERE GROUP_GENERAL_ID='ASGG00000000005';
    SELECT
        @SL_UPS_5=SOLUONG
      FROM @TMP_GROUP_GENERAL_5
     WHERE GROUP_GENERAL_ID='ASGG00000000006';

	 DELETE FROM @TMP_GROUP_GENERAL_5;
	 -- luctv 15 10 2019 bo sung cac so lieu >7 nam
	 -->7 năm - LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI TIEN HANH GROUP THEO MA DON VI
		DECLARE @TMP_GROUP_GENERAL_7 TABLE(GROUP_GENERAL_ID VARCHAR(15), SOLUONG INT);
		INSERT INTO @TMP_GROUP_GENERAL_7
		SELECT
			B.GROUP_GENERAL_ID, COUNT(B.GROUP_GENERAL_ID) AS SL
		  FROM dbo.ASS_MASTER A
			   INNER JOIN dbo.ASS_GROUP_GENERAL B ON B.GROUP_ID=A.GROUP_ID
			   LEFT JOIN dbo.CM_DEPARTMENT CT ON A.DEPT_ID=CT.DEP_ID
			   LEFT JOIN ASS_STATUS ST ON ST.STATUS_ID = A.ASS_STATUS
		WHERE 1= 1 AND ((@DEP_CODE IS NOT NULL AND CT.DEP_CODE=@DEP_CODE AND A.BRANCH_ID = @BRANCH_ID) OR @DEP_CODE IS NULL AND A.BRANCH_ID = @BRANCH_ID)
	  AND DATEDIFF(YEAR, A.USE_DATE, GETDATE()) >7
     --AND A.AMORTIZED_MONTH>(5 * 12)
	 AND A.AMORT_STATUS NOT IN ( 'DTL', 'VNM' )
		AND ((@DEP_CODE IS NULL 
	AND ((@p_Level = 'ALL'  AND  A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_branch))
 	OR (@p_Level = 'UNIT'   AND  A.BRANCH_ID = @p_BRANCH_ID) OR @p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)
 	--AND ((@p_Level = 'ALL'  AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_login))
 	--OR (@p_Level = 'UNIT'   AND A.BRANCH_ID = @p_BRANCH_LOGIN) OR @p_BRANCH_LOGIN = '' OR @p_BRANCH_LOGIN IS NULL 
 	--OR @p_BRANCH_ID IS NOT NULL OR @p_BRANCH_ID <> '')  
	) OR A.BRANCH_ID = @BRANCH_ID)
     GROUP BY B.GROUP_GENERAL_ID;
	DECLARE @SL_LAPTOP_7 INT=0;
    DECLARE @SL_PC_7 INT=0;
    DECLARE @SL_PC_RAP_7 INT=0;
    DECLARE @SL_MAYIN_7 INT=0;
    DECLARE @SL_MAY_SCAN_7 INT=0;
    DECLARE @SL_UPS_7 INT=0;
    SELECT
        @SL_LAPTOP_7=SOLUONG
      FROM @TMP_GROUP_GENERAL_7
     WHERE GROUP_GENERAL_ID='ASGG00000000001';
    SELECT
        @SL_PC_7=SOLUONG
      FROM @TMP_GROUP_GENERAL_7
     WHERE GROUP_GENERAL_ID='ASGG00000000002';
    SELECT
        @SL_PC_RAP_7=SOLUONG
      FROM @TMP_GROUP_GENERAL_7
     WHERE GROUP_GENERAL_ID='ASGG00000000004';
    SELECT
        @SL_MAYIN_7=SOLUONG
      FROM @TMP_GROUP_GENERAL_7
     WHERE GROUP_GENERAL_ID='ASGG00000000003';
    SELECT
        @SL_MAY_SCAN_7=SOLUONG
      FROM @TMP_GROUP_GENERAL_7
     WHERE GROUP_GENERAL_ID='ASGG00000000005';
    SELECT
        @SL_UPS_7=SOLUONG
      FROM @TMP_GROUP_GENERAL_7
     WHERE GROUP_GENERAL_ID='ASGG00000000006';
	 --end luctv

    DELETE FROM @TMP_GROUP_GENERAL_7;
    SET @SL_LAPTOP=@SL_LAPTOP_0_3+@SL_LAPTOP_3_5+@SL_LAPTOP_5+@SL_LAPTOP_7;
    SET @SL_PC=@SL_PC_0_3+@SL_PC_3_5+@SL_PC_5+@SL_PC_7;
    SET @SL_PC_RAP=@SL_PC_RAP_0_3+@SL_PC_RAP_3_5+@SL_PC_RAP_5+@SL_PC_7;
    SET @SL_MAYIN=@SL_MAYIN_0_3+@SL_MAYIN_3_5+@SL_MAYIN_5+@SL_MAYIN_7;
    SET @SL_MAY_SCAN=@SL_MAY_SCAN_0_3+@SL_MAY_SCAN_3_5+@SL_MAY_SCAN_5+@SL_MAY_SCAN_7;
    SET @SL_UPS=@SL_UPS_0_3+@SL_UPS_3_5+@SL_UPS_5+@SL_UPS_7;

    INSERT INTO @returnTable(DEP_ID, DEP_CODE, DEP_LV1,DEP_LV2, BRANCH_CODE, DEP_NAME, QTY, SL_LAPTOP, SL_PC, SL_PC_RAP, SL_MAYIN, SL_MAY_SCAN, SL_UPS,
    SL_LAPTOP_0_3, SL_PC_0_3, SL_PC_RAP_0_3, SL_MAYIN_0_3, SL_MAY_SCAN_0_3, SL_UPS_0_3, SL_LAPTOP_3_5, SL_PC_3_5, SL_PC_RAP_3_5,
    SL_MAYIN_3_5, SL_MAY_SCAN_3_5, SL_UPS_3_5, SL_LAPTOP_5_, SL_PC_5_, SL_PC_RAP_5_, SL_MAYIN_5_, SL_MAY_SCAN_5_, SL_UPS_5_,
	SL_LAPTOP_7_, SL_PC_7_, SL_PC_RAP_7_, SL_MAYIN_7_, SL_MAY_SCAN_7_, SL_UPS_7_)
    VALUES(@DEP_CODE, ISNULL(@DEP_CODE,@BRANCH_CODE),ISNULL(@DEP_CODE,@BRANCH_CODE),@DEP_CODE,ISNULL(@BRANCH_CODE_FAT,@BRANCH_CODE), @DEP_NAME, 0, -- QTY - int
    @SL_LAPTOP, -- SL_LAPTOP - int
    @SL_PC, @SL_PC_RAP, @SL_MAYIN, @SL_MAY_SCAN, @SL_UPS, @SL_LAPTOP_0_3, @SL_PC_0_3, @SL_PC_RAP_0_3, @SL_MAYIN_0_3, @SL_MAY_SCAN_0_3,
    @SL_UPS_0_3, @SL_LAPTOP_3_5, @SL_PC_3_5, @SL_PC_RAP_3_5, @SL_MAYIN_3_5, @SL_MAY_SCAN_3_5, @SL_UPS_3_5, @SL_LAPTOP_5, @SL_PC_5,
    @SL_PC_RAP_5, @SL_MAYIN_5, @SL_MAY_SCAN_5, @SL_UPS_5,
	@SL_LAPTOP_7, @SL_PC_7,@SL_PC_RAP_7, @SL_MAYIN_7, @SL_MAY_SCAN_7, @SL_UPS_7);
	-- LUCTV 28082019 BO SUNG THEM DIEU KIEN NEU MA PHONG = NULL THI XOA 1 DONG WHERE THEO BRANCH_ID
	IF(@DEP_CODE IS NOT NULL)
	BEGIN
		DELETE FROM @TMP WHERE ID=@DEP_CODE;
	END
	ELSE
	BEGIN
	 DELETE FROM @TMP WHERE BRANCH_ID = @BRANCH_ID;
	END
END;
SELECT * FROM @returnTable ORDER BY BRANCH_CODE DESC,DEP_CODE DESC;