USE [gAMSPro_VietcapitalBank_v2]
GO
/****** Object:  StoredProcedure [dbo].[PL_MASTER_Search_New]    Script Date: 19-Aug-20 14:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PL_MASTER_Search_New]
@P_PLAN_CODE nvarchar(15) = NULL,
@p_PLAN_NAME NVARCHAR(200) = NULL,
@P_YEAR NVARCHAR(4) = NULL,
@P_COST_ID nvarchar(15) = NULL,
@P_BRANCH_ID nvarchar(15) = NULL,
@P_BRANCHLOGIN NVARCHAR(15) = NULL,
@P_PLAN_TYPE_ID NVARCHAR(15) = NULL,
@p_DEP_ID NVARCHAR(15) = NULL,
@p_GOOD_NAME NVARCHAR(200) = NULL,
@p_USER_LOGIN VARCHAR(200) = NULL,
@P_TOP INT = 10,
@P_LEVEL varchar(10) = NULL
AS
BEGIN	
	SET @P_TOP = NULL
	declare @tmp table(BRANCH_ID varchar(15))
	insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@P_BRANCHLOGIN)

	DECLARE @BRANCH_TYPE VARCHAR(15),@DEPLOGIN VARCHAR(20),@ROLE_USER VARCHAR(15),@IS_ALL BIT

	
	SET @IS_ALL=0
	DECLARE @DVDM_ID TABLE( DVDM_ID VARCHAR(20))
	
	SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@P_BRANCHLOGIN)
	SET @DEPLOGIN =(SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)
	


	IF(EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='PL_MASTER' AND BRANCH_ID=@P_BRANCHLOGIN AND DEP_ID=@DEPLOGIN) OR EXISTS(SELECT ID FROM PL_ROLE_DATA_CONFIG WHERE ROLE_TYPE='HANHCHINH' AND BRANCH_ID=@P_BRANCHLOGIN AND DEP_ID=@DEPLOGIN))
		SET @IS_ALL=1
	SET @ROLE_USER = (SELECT RoleName FROM dbo.TL_USER WHERE TLNANME=@p_USER_LOGIN)


	IF(@IS_ALL=1 OR @ROLE_USER='TGD')
	BEGIN
	 INSERT INTO @DVDM_ID
	 SELECT  DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1
	END
	ELSE
	BEGIN
	 INSERT INTO @DVDM_ID
	 SELECT  PC.DVDM_ID FROM 
									dbo.PL_COSTCENTER PC 
									LEFT JOIN dbo.PL_COSTCENTER_DT PT ON PT.COST_ID=PC.COST_ID
									WHERE PT.DEP_ID=@DEPLOGIN AND PT.BRANCH_ID=@P_BRANCHLOGIN AND EXISTS(SELECT DVDM_ID FROM dbo.CM_DVDM WHERE IS_DVDM=1)
		
	END
		IF (@P_TOP = 0 OR @P_TOP IS NULL)
		BEGIN
		SELECT L.*, D.BRANCH_CODE,LDT.QUANTITY_APP,LDT.QUANTITY_USE, LDT.QUANTITY_REMAIN,
		LDT.QUANTITY_ETM,LDT.QUANTITY_REMAIN_ETM,LDT.AMT_APPROVE,LDT.AMT_EXE,LDT.AMT_REMAIN,LDT.AMT_ETM,LDT.AMT_REMAIN_ETM	,LDT.AMT_TRANSFER,LDT.AMT_RECEIVE_TRANSFER,
		
		D.BRANCH_NAME PGD, E.DEP_CODE, E.DEP_NAME,CC.DVDM_CODE  AS COST_CODE,CC.DVDM_NAME AS COST_NAME,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,
		CASE WHEN @BRANCH_TYPE='HS' THEN D.BRANCH_NAME + '-' + E.DEP_NAME
		ELSE D.BRANCH_NAME END AS DVCP_NAME
		FROM PL_MASTER L 
		INNER JOIN (
			SELECT DT.PLAN_ID,
			SUM(ISNULL(DT.QUANTITY,0)) AS QUANTITY_APP,SUM(ISNULL(DT.QUANTITY_EXE,0)) AS  QUANTITY_USE,
			SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_EXE,0)) AS QUANTITY_REMAIN,SUM(ISNULL(DT.QUANTITY_ETM,0)) AS  QUANTITY_ETM,
			SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0)) AS QUANTITY_REMAIN_ETM,

			SUM(ISNULL(DT.AMT_APP,0)) AS AMT_APPROVE,SUM(ISNULL(DT.AMT_EXE,0)) AS  AMT_EXE,
			SUM(ISNULL(DT.AMT_TF,0)) AS AMT_TRANSFER,SUM(ISNULL(DT.AMT_RECEIVE_TF,0)) AS AMT_RECEIVE_TRANSFER,
			SUM(ISNULL(DT.AMT_APP,0)+ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_EXE,0)) AS AMT_REMAIN,
			SUM(ISNULL(DT.AMT_ETM,0)) AS  AMT_ETM,
			SUM(ISNULL(DT.AMT_APP,0) +ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0)) AS AMT_REMAIN_ETM 
			FROM dbo.PL_TRADEDETAIL DT 
			LEFT JOIN dbo.CM_GOODS CG ON DT.GOODS_ID=CG.GD_ID
			WHERE ( CG.GD_NAME LIKE '%' + @p_GOOD_NAME +'%' OR @p_GOOD_NAME IS NULL OR @p_GOOD_NAME='')
			GROUP BY DT.PLAN_ID
		) LDT ON LDT.PLAN_ID = L.PLAN_ID
		LEFT JOIN CM_BRANCH D ON  D.BRANCH_ID = L.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT E ON E.DEP_ID = L.DEPT_ID
		LEFT JOIN dbo.CM_DVDM CC ON CC.DVDM_ID=L.COST_ID
		LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=L.PLAN_TYPE_ID
		WHERE 1=1
			AND (L.PLAN_CODE LIKE '%' + @P_PLAN_CODE + '%' OR @P_PLAN_CODE IS NULL OR @P_PLAN_CODE = '')
			AND (L.PLAN_NAME LIKE '%' + @p_PLAN_NAME + '%' OR @p_PLAN_NAME = '' OR @p_PLAN_NAME IS NULL)
			AND (L.[YEAR] LIKE '%' + @P_YEAR + '%' OR @P_YEAR IS NULL OR @P_YEAR = '')
			AND (L.PLAN_TYPE_ID = @P_PLAN_TYPE_ID OR @P_PLAN_TYPE_ID IS NULL OR @P_PLAN_TYPE_ID = '')
			AND (L.COST_ID = @P_COST_ID OR @P_COST_ID IS NULL OR @P_COST_ID = '')
			AND (L.DEPT_ID = @P_DEP_ID OR @P_DEP_ID IS NULL OR @P_DEP_ID = '')
			AND (L.BRANCH_ID = @P_BRANCH_ID OR @P_BRANCH_ID IS NULL OR @P_BRANCH_ID = '')
			AND ((@BRANCH_TYPE='HS' AND((L.BRANCH_ID=@P_BRANCHLOGIN AND  L.DEPT_ID=@DEPLOGIN ) OR L.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))) OR (@BRANCH_TYPE <>'HS' AND L.BRANCH_ID=@P_BRANCHLOGIN))
			
			AND L.RECORD_STATUS = '1'	
		END
		ELSE 
		BEGIN

				IF (@P_TOP <> 0 AND @P_TOP IS NOT NULL )
				BEGIN
				
			SELECT L.*, D.BRANCH_CODE,LDT.QUANTITY_APP,LDT.QUANTITY_USE, LDT.QUANTITY_REMAIN,
LDT.QUANTITY_ETM,LDT.QUANTITY_REMAIN_ETM,LDT.AMT_APPROVE,LDT.AMT_EXE,LDT.AMT_REMAIN,LDT.AMT_ETM,LDT.AMT_REMAIN_ETM	,LDT.AMT_TRANSFER,LDT.AMT_RECEIVE_TRANSFER,
		
		D.BRANCH_NAME PGD, E.DEP_CODE, E.DEP_NAME,CC.DVDM_CODE  AS COST_CODE,CC.DVDM_NAME AS COST_NAME,PT.PLAN_TYPE_CODE,PT.PLAN_TYPE_NAME,
		CASE WHEN @BRANCH_TYPE='HS' THEN D.BRANCH_NAME + '-' + E.DEP_NAME
		ELSE D.BRANCH_NAME END AS DVCP_NAME
		FROM PL_MASTER L 
		INNER JOIN (
			SELECT DT.PLAN_ID,
			SUM(ISNULL(DT.QUANTITY,0)) AS QUANTITY_APP,SUM(ISNULL(DT.QUANTITY_EXE,0)) AS  QUANTITY_USE,
			SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_EXE,0)) AS QUANTITY_REMAIN,SUM(ISNULL(DT.QUANTITY_ETM,0)) AS  QUANTITY_ETM,
			SUM(ISNULL(DT.QUANTITY,0)-ISNULL(DT.QUANTITY_ETM,0)) AS QUANTITY_REMAIN_ETM,

			SUM(ISNULL(DT.AMT_APP,0)) AS AMT_APPROVE,SUM(ISNULL(DT.AMT_EXE,0)) AS  AMT_EXE,
			SUM(ISNULL(DT.AMT_TF,0)) AS AMT_TRANSFER,SUM(ISNULL(DT.AMT_RECEIVE_TF,0)) AS AMT_RECEIVE_TRANSFER,
			SUM(ISNULL(DT.AMT_APP,0)+ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_EXE,0)) AS AMT_REMAIN,
			SUM(ISNULL(DT.AMT_ETM,0)) AS  AMT_ETM,
			SUM(ISNULL(DT.AMT_APP,0) +ISNULL(DT.AMT_RECEIVE_TF,0)-ISNULL(DT.AMT_TF,0)-ISNULL(DT.AMT_ETM,0)) AS AMT_REMAIN_ETM 
			FROM dbo.PL_TRADEDETAIL DT 
			LEFT JOIN dbo.CM_GOODS CG ON DT.GOODS_ID=CG.GD_ID
			WHERE ( CG.GD_NAME LIKE '%' + @p_GOOD_NAME +'%' OR @p_GOOD_NAME IS NULL OR @p_GOOD_NAME='')
			GROUP BY DT.PLAN_ID
		) LDT ON LDT.PLAN_ID = L.PLAN_ID
		LEFT JOIN CM_BRANCH D ON  D.BRANCH_ID = L.BRANCH_ID
		LEFT JOIN CM_DEPARTMENT E ON E.DEP_ID = L.DEPT_ID
		LEFT JOIN dbo.CM_DVDM CC ON CC.DVDM_ID=L.COST_ID
		LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=L.PLAN_TYPE_ID
		WHERE 1=1
			AND (L.PLAN_CODE LIKE '%' + @P_PLAN_CODE + '%' OR @P_PLAN_CODE IS NULL OR @P_PLAN_CODE = '')
			AND (L.PLAN_NAME LIKE '%' + @p_PLAN_NAME + '%' OR @p_PLAN_NAME = '' OR @p_PLAN_NAME IS NULL)
			AND (L.[YEAR] LIKE '%' + @P_YEAR + '%' OR @P_YEAR IS NULL OR @P_YEAR = '')
			AND (L.PLAN_TYPE_ID = @P_PLAN_TYPE_ID OR @P_PLAN_TYPE_ID IS NULL OR @P_PLAN_TYPE_ID = '')
			AND (L.COST_ID = @P_COST_ID OR @P_COST_ID IS NULL OR @P_COST_ID = '')
			AND (L.DEPT_ID = @P_DEP_ID OR @P_DEP_ID IS NULL OR @P_DEP_ID = '')
				AND (L.BRANCH_ID = @P_BRANCH_ID OR @P_BRANCH_ID IS NULL OR @P_BRANCH_ID = '')
			AND ((@BRANCH_TYPE='HS' AND((L.BRANCH_ID=@P_BRANCHLOGIN AND  L.DEPT_ID=@DEPLOGIN ) OR L.COST_ID IN (SELECT DVDM_ID FROM @DVDM_ID))) OR (@BRANCH_TYPE <>'HS' AND L.BRANCH_ID=@P_BRANCHLOGIN))
			AND L.RECORD_STATUS = '1'	
				
			END
			
		END
END