ALTER PROCEDURE [dbo].[TR_PO_MASTER_Search]
	@P_PO_TYPE  INT = NULL,
	@p_PO_CODE	varchar(15)  = NULL,
	@p_PO_ID	varchar(15)  = NULL,
	@p_TOTAL_AMT	decimal(18)  = NULL,
	@p_PO_NAME	nvarchar(200)  = NULL,
	@p_CONTRACT_ID	varchar(15)  = NULL,
	@p_SUP_ID	varchar(15)  = NULL,
	@p_SUP_NAME	nvarchar(200)  = NULL,
	@p_SUP_ADDR	nvarchar(200)  = NULL,
	@p_INPUT_DT	VARCHAR(20) = NULL,
	@p_PAYMENT_DT	VARCHAR(20) = NULL,
	@p_REQ_DOC_ID	varchar(15)  = NULL,
	@p_DELIVERY_DT	VARCHAR(20) = NULL,
	@p_PAYAPP_DT	VARCHAR(20) = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(15)  = NULL,
	@p_CREATE_DT	VARCHAR(20) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(15)  = NULL,
	@p_APPROVE_DT	VARCHAR(20) = NULL,
	@p_CONTRACT_CODE VARCHAR(15) = NULL,
	@P_FROM_DATE NVARCHAR(20) = NULL,
	@P_TO_DATE NVARCHAR(20) = NULL,
	@p_RECEIVE_BRANCH VARCHAR(15) = NULL,
	@P_TOP	INT = 10,
	@p_BRANCH_ID varchar(15) = null,
	@P_LEVEL varchar(10) = NULL,
	@p_BRANCH_LOGIN varchar(15) = null,
	--- them 4 dieu kien tim kiem sau khi UAT bao gom 
	@p_PYC_CODE VARCHAR(125) = NULL,
	@p_PYC_NAME NVARCHAR(250) = NULL,
	@p_TTCT_CODE VARCHAR(25) = NULL,
	@p_TTCT_NAME NVARCHAR(250) = NULL,
	@p_HH_TYPE_CODE VARCHAR(25) = NULL,
	@p_HH_TYPE_NAME NVARCHAR(250) = NULL
AS
BEGIN
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)

DECLARE @l_BRANCH_TYPE VARCHAR(15) = ''
SELECT @l_BRANCH_TYPE = BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_LOGIN
--LAY GIA TRI QUY DINH LA TSCD HAY CCLD
DECLARE @l_ASSET_VALUE DECIMAL = 30000000
SELECT @l_ASSET_VALUE = CONVERT(decimal(18,0), ParaValue) FROM SYS_PARAMETERS WHERE ParaKey = 'ASSET_VALUE'
	IF(@P_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
		SELECT A.*,C.CONTRACT_CODE, D.AUTH_STATUS_NAME,  E.SUP_CODE AS S_SUP_CODE, E.SUP_NAME AS S_SUP_NAME, E.ADDR AS S_ADDR,
				F.CONTRACT_CODE AS C_CONTRACT_CODE, F.[CONTRACT_NAME] AS C_CONTRACT_NAME,  F.TOTAL_AMT AS C_TOTAL_AMT ,
				ISNULL((SELECT SUM(TR_PO.TOTAL_AMT) FROM TR_PO_MASTER TR_PO WHERE TR_PO.CONTRACT_ID = A.CONTRACT_ID AND A.CONTRACT_ID IS NOT NULL AND A.CONTRACT_ID <> ''),0) AS C_DONE_AMT, 
				 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH, E.TAX_NO,PMTP.TR_REQ_ID,PMTP.TR_REQ_CODE,PL.REQ_NAME AS TEN_PYC,
		PL.REQ_CODE AS SO_TT_CT, PL.REQ_NAME AS TEN_TT_CT,A.DELIVERY_DT  AS NGAY_NHAN_TT, A.PAYAPP_DT AS NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,
		'' AS HH_TYPE_CODE,'' AS HH_TYPE_NAME, PL.REQ_ID AS TO_TRINH_ID,E.ACC_NUM,E.ACC_NAME,E.BANK_NAME,E.ACC_NAME_OUT,E.ACC_NUM_OUT,E.BANK_NAME_OUT, 0.0 AS TOTAL_ADV_AMT, 0.0 AS TOTAL_PAY_AMT
		FROM TR_PO_MASTER A 
		LEFT JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID  
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_SUPPLIER E ON E.SUP_ID= A.SUP_ID
		LEFT JOIN TR_CONTRACT F ON F.CONTRACT_ID = A.CONTRACT_ID --AND A.CONTRACT_ID IS NOT NULL
		LEFT JOIN dbo.TR_PO_MASTER_TEMP PMTP ON PMTP.PO_ID = A.PO_ID
		LEFT JOIN TR_REQUEST_DOC RQ ON A.REQ_DOC_ID= RQ.REQ_ID
		LEFT JOIN PL_REQUEST_DOC PL ON RQ.PL_REQ_ID= PL.REQ_ID

		WHERE 1 = 1
	   AND (A.PO_CODE LIKE '%' + @p_PO_CODE + '%' OR @p_PO_CODE IS NULL OR @p_PO_CODE = '')
		AND (A.PO_ID= @p_PO_ID OR @p_PO_ID IS NULL OR @p_PO_ID = '')
		AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
		AND (A.PO_NAME LIKE '%' + @p_PO_NAME + '%' OR @p_PO_NAME IS NULL OR @p_PO_NAME = '')
		AND (A.CONTRACT_ID LIKE '%' + @p_CONTRACT_ID + '%' OR @p_CONTRACT_ID IS NULL OR @p_CONTRACT_ID = '')
		AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
		AND (A.SUP_ADDR LIKE '%' + @p_SUP_ADDR + '%' OR @p_SUP_ADDR IS NULL OR @p_SUP_ADDR = '')
		AND (DATEDIFF(DAY,A.INPUT_DT , CONVERT(DATETIME,@p_INPUT_DT,103))  =0 OR @p_INPUT_DT IS NULL OR @p_INPUT_DT = '')
		AND (DATEDIFF(DAY,A.PAYMENT_DT , CONVERT(DATETIME,@p_PAYMENT_DT,103))  =0 OR @p_PAYMENT_DT IS NULL OR @p_PAYMENT_DT = '')
		AND (A.REQ_DOC_ID LIKE '%' + @p_REQ_DOC_ID + '%' OR @p_REQ_DOC_ID IS NULL OR @p_REQ_DOC_ID = '')
		AND (DATEDIFF(DAY,A.DELIVERY_DT , CONVERT(DATETIME,@p_DELIVERY_DT,103)) = 0 OR @p_DELIVERY_DT IS NULL OR @p_DELIVERY_DT = '')
		AND (DATEDIFF(DAY,A.PAYAPP_DT , CONVERT(DATETIME,@p_PAYAPP_DT,103)) = 0 OR @p_PAYAPP_DT IS NULL OR @p_PAYAPP_DT = '')
		AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		AND (A.INPUT_DT >= CONVERT(DATE,@P_FROM_DATE,103) OR @P_FROM_DATE = '' OR @P_FROM_DATE IS NULL)
		AND (A.INPUT_DT <= CONVERT(DATE,@P_TO_DATE,103) OR @P_TO_DATE = '' OR @P_TO_DATE IS NULL)
		AND (C.CONTRACT_CODE LIKE '%' + @P_CONTRACT_CODE + '%' OR @P_CONTRACT_CODE = '' OR @P_CONTRACT_CODE IS NULL)
		AND (A.PO_TYPE = @P_PO_TYPE OR @P_PO_TYPE IS NULL OR @P_PO_TYPE =-1) 
		--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
		AND (@p_RECEIVE_BRANCH IS NULL OR @p_RECEIVE_BRANCH = '' 
			OR EXISTS(SELECT PODT.PO_ID FROM TR_PO_DETAIL PODT
						WHERE (PODT.RECEIVE_BRANCH = @p_RECEIVE_BRANCH AND PODT.PO_ID = A.PO_ID )
							  OR (@l_BRANCH_TYPE = 'HS' AND PODT.PRICE > @l_ASSET_VALUE AND PODT.PO_ID = A.PO_ID)))
		AND A.RECORD_STATUS = '1'
		 AND ((@P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
		OR ((@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID) OR (@p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)))
		AND( RQ.REQ_CODE like '%'+@p_PYC_CODE +'%' OR @p_PYC_CODE IS NULL OR @p_PYC_CODE like '' )
		AND( PL.REQ_CODE like '%'+@p_TTCT_CODE +'%' OR @p_TTCT_CODE IS NULL OR @p_TTCT_CODE like '' )
		AND( PL.REQ_NAME like N'%'+@p_TTCT_NAME +'%' OR @p_TTCT_NAME IS NULL OR @p_TTCT_NAME like '' )
		AND((
		EXISTS(SELECT HH_ID FROM CM_HANGHOA HH LEFT JOIN CM_HANGHOA_TYPE HT ON HH.HH_TYPE_ID=HT.HH_TYPE_ID WHERE 
		 (HH.HH_CODE LIKE '%' + @p_HH_TYPE_CODE + '%' OR @p_HH_TYPE_CODE = '' OR @p_HH_TYPE_CODE IS NULL)
		AND (HH.HH_NAME LIKE N'%' + @p_HH_TYPE_NAME + '%' OR @p_HH_TYPE_NAME = '' OR @p_HH_TYPE_NAME IS NULL) AND HH.HH_ID IN (SELECT PT.GOODS_ID FROM dbo.TR_PO_DETAIL PT WHERE PT.PO_ID= A.PO_ID))
		
		) OR (( @p_HH_TYPE_NAME = '' OR @p_HH_TYPE_NAME IS NULL)AND ( @p_HH_TYPE_CODE = '' OR @p_HH_TYPE_CODE IS NULL)))
		--ORDER BY PO_CODE DESC, INPUT_DT DESC
		ORDER BY A.INPUT_DT DESC
   ELSE
		SELECT TOP(CONVERT(INT,@P_TOP)) A.*,C.CONTRACT_CODE, D.AUTH_STATUS_NAME,  E.SUP_CODE AS S_SUP_CODE, E.SUP_NAME AS S_SUP_NAME, E.ADDR AS S_ADDR,
				F.CONTRACT_CODE AS C_CONTRACT_CODE, F.[CONTRACT_NAME] AS C_CONTRACT_NAME,  F.TOTAL_AMT AS C_TOTAL_AMT ,
				ISNULL((SELECT SUM(TR_PO.TOTAL_AMT) FROM TR_PO_MASTER TR_PO WHERE TR_PO.CONTRACT_ID = A.CONTRACT_ID AND A.CONTRACT_ID IS NOT NULL AND A.CONTRACT_ID <> ''),0) AS C_DONE_AMT, 
				 @p_RECEIVE_BRANCH AS RECEIVE_BRANCH, E.TAX_NO,PMTP.TR_REQ_ID,PMTP.TR_REQ_CODE,PL.REQ_NAME AS TEN_PYC,
		PL.REQ_CODE AS SO_TT_CT, PL.REQ_NAME AS TEN_TT_CT,A.DELIVERY_DT  AS NGAY_NHAN_TT, A.PAYAPP_DT AS NGAY_DUYET_TT, '' AS NCC_CODE, '' NCC_NAME, '' AS NCC_ADDR,'' AS HH_TYPE_CODE,'' AS HH_TYPE_NAME, PL.REQ_ID AS TO_TRINH_ID,
		E.ACC_NUM,E.ACC_NAME,E.BANK_NAME,E.ACC_NAME_OUT,E.ACC_NUM_OUT,E.BANK_NAME_OUT,0.0 AS TOTAL_ADV_AMT, 0.0 AS TOTAL_PAY_AMT

		FROM TR_PO_MASTER A 
		LEFT JOIN TR_CONTRACT C ON A.CONTRACT_ID = C.CONTRACT_ID  
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_SUPPLIER E ON E.SUP_ID= A.SUP_ID
		LEFT JOIN TR_CONTRACT F ON F.CONTRACT_ID = A.CONTRACT_ID --AND A.CONTRACT_ID IS NOT NULL
		LEFT JOIN dbo.TR_PO_MASTER_TEMP PMTP ON PMTP.PO_ID = A.PO_ID
		LEFT JOIN TR_REQUEST_DOC RQ ON A.REQ_DOC_ID= RQ.REQ_ID
		LEFT JOIN PL_REQUEST_DOC PL ON RQ.PL_REQ_ID= PL.REQ_ID
		WHERE 1 = 1
	   AND (A.PO_CODE LIKE '%' + @p_PO_CODE + '%' OR @p_PO_CODE IS NULL OR @p_PO_CODE = '')
		AND (A.PO_ID= @p_PO_ID  OR @p_PO_ID IS NULL OR @p_PO_ID = '')
		AND (A.TOTAL_AMT=@p_TOTAL_AMT OR @p_TOTAL_AMT IS NULL OR @p_TOTAL_AMT =0)
		AND (A.PO_NAME LIKE '%' + @p_PO_NAME + '%' OR @p_PO_NAME IS NULL OR @p_PO_NAME = '')
		AND (A.CONTRACT_ID LIKE '%' + @p_CONTRACT_ID + '%' OR @p_CONTRACT_ID IS NULL OR @p_CONTRACT_ID = '')
		AND (A.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
		AND (A.SUP_ADDR LIKE '%' + @p_SUP_ADDR + '%' OR @p_SUP_ADDR IS NULL OR @p_SUP_ADDR = '')
		AND (DATEDIFF(DAY,A.INPUT_DT , CONVERT(DATETIME,@p_INPUT_DT,103))  =0 OR @p_INPUT_DT IS NULL OR @p_INPUT_DT = '')
		AND (DATEDIFF(DAY,A.PAYMENT_DT , CONVERT(DATETIME,@p_PAYMENT_DT,103))  =0 OR @p_PAYMENT_DT IS NULL OR @p_PAYMENT_DT = '')
		AND (A.REQ_DOC_ID LIKE '%' + @p_REQ_DOC_ID + '%' OR @p_REQ_DOC_ID IS NULL OR @p_REQ_DOC_ID = '')
		AND (DATEDIFF(DAY,A.DELIVERY_DT , CONVERT(DATETIME,@p_DELIVERY_DT,103)) = 0 OR @p_DELIVERY_DT IS NULL OR @p_DELIVERY_DT = '')
		AND (DATEDIFF(DAY,A.PAYAPP_DT , CONVERT(DATETIME,@p_PAYAPP_DT,103)) = 0 OR @p_PAYAPP_DT IS NULL OR @p_PAYAPP_DT = '')
		AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
		AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
		AND (DATEDIFF(DAY,A.CREATE_DT , CONVERT(DATETIME,@p_CREATE_DT,103)) = 0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
		AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
		AND (DATEDIFF(DAY,A.APPROVE_DT , CONVERT(DATETIME,@p_APPROVE_DT,103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
		AND (A.INPUT_DT >= CONVERT(DATE,@P_FROM_DATE,103) OR @P_FROM_DATE = '' OR @P_FROM_DATE IS NULL)
		AND (A.INPUT_DT <= CONVERT(DATE,@P_TO_DATE,103) OR @P_TO_DATE = '' OR @P_TO_DATE IS NULL)
		AND (C.CONTRACT_CODE LIKE '%' + @P_CONTRACT_CODE + '%' OR @P_CONTRACT_CODE = '' OR @P_CONTRACT_CODE IS NULL)
		AND (A.PO_TYPE = @P_PO_TYPE OR @P_PO_TYPE IS NULL OR @P_PO_TYPE =-1) 
		--AND (@p_RECEIVE_BRANCH IS NULL OR A.PO_ID IN (SELECT PO_ID FROM TR_PO_DETAIL WHERE RECEIVE_BRANCH=@p_RECEIVE_BRANCH))
		AND (@p_RECEIVE_BRANCH IS NULL OR @p_RECEIVE_BRANCH = '' 
			OR EXISTS(SELECT PODT.PO_ID FROM TR_PO_DETAIL PODT
						WHERE (PODT.RECEIVE_BRANCH = @p_RECEIVE_BRANCH AND PODT.PO_ID = A.PO_ID )
							  OR (@l_BRANCH_TYPE = 'HS' AND PODT.PRICE > @l_ASSET_VALUE AND PODT.PO_ID = A.PO_ID)))
		AND A.RECORD_STATUS = '1'
		 AND ((@P_LEVEL = 'ALL' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp))
        OR ((@P_LEVEL = 'UNIT' AND A.BRANCH_ID = @p_BRANCH_ID) OR (@p_BRANCH_ID = '' OR @p_BRANCH_ID IS NULL)))
		AND( RQ.REQ_CODE like '%'+@p_PYC_CODE +'%' OR @p_PYC_CODE IS NULL OR @p_PYC_CODE like '' )
		AND( PL.REQ_CODE like '%'+@p_TTCT_CODE +'%' OR @p_TTCT_CODE IS NULL OR @p_TTCT_CODE like '' )
		AND( PL.REQ_NAME like N'%'+@p_TTCT_NAME +'%' OR @p_TTCT_NAME IS NULL OR @p_TTCT_NAME like '' )
		AND((
		EXISTS(SELECT HH_ID FROM CM_HANGHOA HH LEFT JOIN CM_HANGHOA_TYPE HT ON HH.HH_TYPE_ID=HT.HH_TYPE_ID WHERE 
		 (HH.HH_CODE LIKE '%' + @p_HH_TYPE_CODE + '%' OR @p_HH_TYPE_CODE = '' OR @p_HH_TYPE_CODE IS NULL)
		AND (HH.HH_NAME LIKE N'%' + @p_HH_TYPE_NAME + '%' OR @p_HH_TYPE_NAME = '' OR @p_HH_TYPE_NAME IS NULL) AND HH.HH_ID IN (SELECT PT.GOODS_ID FROM dbo.TR_PO_DETAIL PT WHERE PT.PO_ID= A.PO_ID))
		
		) OR (( @p_HH_TYPE_NAME = '' OR @p_HH_TYPE_NAME IS NULL)AND ( @p_HH_TYPE_CODE = '' OR @p_HH_TYPE_CODE IS NULL)))
		--ORDER BY PO_CODE DESC, INPUT_DT DESC
		ORDER BY A.INPUT_DT DESC
   END