ALTER PROCEDURE [dbo].[CM_SUPPLIER_Search]  
@p_SUP_CODE varchar(15) = NULL,  
@p_SUP_NAME Nvarchar(300) = NULL,  
@p_SUP_TYPE_ID varchar(15) = NULL,  
@p_REGION_ID varchar(15) = NULL,  
@p_ADDR nvarchar(200) = NULL,  
@p_EMAIL varchar(50) = NULL,  
@p_TAX_NO varchar(20) = NULL,  
@p_TEL varchar(20) = NULL,  
@p_CONTACT_PERSON nvarchar(200) = NULL,  
@p_DISCIPLINES NVARCHAR(15) = NULL,  
@p_NOTES nvarchar(1000) = NULL,  
@p_RECORD_STATUS varchar(1) = NULL,  
@p_MAKER_ID varchar(20) = NULL,  
@p_CREATE_DT VARCHAR(20) = NULL,  
@p_AUTH_STATUS varchar(50) = NULL,  
@p_CHECKER_ID varchar(20) = NULL,  
@p_APPROVE_DT VARCHAR(20) = NULL,  
@p_TRN_TYPE VARCHAR(50) = NULL,  
@p_REF_ID VARCHAR(20) = NULL,
@p_TOTAL_AMT DECIMAL(18,0),  
@p_TOP INT = NULL,
@p_EDITOR_ID varchar(15) =  NULL,
@p_ACC_NUM varchar(100)= NULL,
@p_ACC_NUM_OUT varchar(100)=NULL
AS  
BEGIN -- PAGING
------thieuvq -BEGIN 231219------  
--SELECT * FROM TR_CONTRACT A  
--INNER JOIN TR_CONTRACT_DT B ON A.CONTRACT_ID = B.CONTRACT_ID AND B.GOODS_ID = ''  
--SELECT * FROM TR_CONTRACT_DT  
--SELECT * FROM CM_GOODS  
----SELECT * FROM wsiSplit('TR_REQ_DOC_RE;GOO000000000452',';') WHERE value LIKE 'GOO%'  
--SELECT DATEADD(MONTH,3,GETDATE())  
 
SET @p_TOP  = 1000
IF @p_REF_ID <> '' AND @p_REF_ID IS NOT NULL  
BEGIN
IF(@p_TRN_TYPE='PO-PYC') 
BEGIN 
-- PAGING BEGIN
	SELECT A.*,T.SUP_TYPE_NAME,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME, '' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT, U.TLFullName  
	-- SELECT END
	FROM CM_SUPPLIER A  
	INNER JOIN dbo.TR_REQUEST_DOC_DT TR ON TR.SUP_ID=A.SUP_ID AND TR.REQ_DOC_ID=@p_REF_ID  
	LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS  
	LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'
	LEFT JOIN TL_USER U ON A.EDITOR_ID = U.TLNANME
	LEFT JOIN CM_SUPPLIERTYPE T ON T.SUP_TYPE_ID = A.SUP_TYPE_ID--,T.SUP_TYPE_NAME
	WHERE 1 = 1  
	AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')  
	AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')  
	AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')  
	AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')  
	AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')  
	AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')  
	AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')  
	AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')  
	AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')  
	AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')  
	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 = '')
	--PHONGNT 4/3/2022
	AND (A.ACC_NUM LIKE '%' + @p_ACC_NUM + '%' OR @p_ACC_NUM IS NULL OR @p_ACC_NUM = '')  
	AND (A.ACC_NUM_OUT LIKE '%' + @p_ACC_NUM_OUT + '%' OR @p_ACC_NUM_OUT IS NULL OR @p_ACC_NUM_OUT = '')  
	AND A.RECORD_STATUS = '1'
-- PAGING END 
END 
---CO THE TRUYEN GOODS_ID XUONG HOAC TRUYEN HANG MUC --> XUONG NAY LAY RA GOODS_ID  
ELSE IF(@p_TOTAL_AMT <= (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE ROLE_ID='MSTT'))  
BEGIN  
-- PAGING BEGIN
SELECT A.*,T.SUP_TYPE_NAME,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME,'' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT, U.TLFullName  
-- SELECT END
FROM CM_SUPPLIER A  
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS  
LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'  
LEFT JOIN TL_USER U ON A.EDITOR_ID = U.TLNANME
LEFT JOIN CM_SUPPLIERTYPE T ON T.SUP_TYPE_ID = A.SUP_TYPE_ID--,T.SUP_TYPE_NAME
WHERE 1 = 1  
AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')  
AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')  
AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')  
AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')  
AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')  
AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')  
AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')  
AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')  
AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')  
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')  
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 = '') 
--PHONGNT 4/3/2022
AND (A.ACC_NUM LIKE '%' + @p_ACC_NUM + '%' OR @p_ACC_NUM IS NULL OR @p_ACC_NUM = '')  
AND (A.ACC_NUM_OUT LIKE '%' + @p_ACC_NUM_OUT + '%' OR @p_ACC_NUM_OUT IS NULL OR @p_ACC_NUM_OUT = '') 
AND A.RECORD_STATUS = '1'
-- PAGING END
END  
ELSE  
-- PAGING BEGIN
SELECT A.*,T.SUP_TYPE_NAME,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME,'' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT, U.TLFullName  
-- SELECT END
FROM CM_SUPPLIER A  
LEFT JOIN  
(  
SELECT A.SUP_ID FROM TR_CONTRACT A  
INNER JOIN TR_CONTRACT_DT B ON A.CONTRACT_ID = B.CONTRACT_ID --AND B.GOODS_ID = @GD_ID  
WHERE B.GOODS_ID = @p_REF_ID AND A.APPROVE_DT IS NOT NULL AND  
((DATEADD(MONTH,6,A.APPROVE_DT) >= GETDATE() AND (A.REQUEST_ID = '' or A.REQUEST_ID IS NULL)) ---LAY HD KY CACH DAY 6 THANG  
OR A.REQUEST_ID IS not NULL OR A.REQUEST_ID <> '')---HOP DONG NGUYEN TAC THI KHONG BAT BUOC NHAP SO PHIEU YEU CAU MUA SAM  
) SUP ON SUP.SUP_ID = A.SUP_ID  
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS  
LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'  
LEFT JOIN TL_USER U ON A.EDITOR_ID = U.TLNANME
LEFT JOIN CM_SUPPLIERTYPE T ON T.SUP_TYPE_ID = A.SUP_TYPE_ID--,T.SUP_TYPE_NAME
WHERE 1 = 1  
AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')  
AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')  
AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')  
AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')  
AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')  
AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')  
AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')  
AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')  
AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')  
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')  
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 = '')  
--PHONGNT 4/3/2022
AND (A.ACC_NUM LIKE '%' + @p_ACC_NUM + '%' OR @p_ACC_NUM IS NULL OR @p_ACC_NUM = '')  
AND (A.ACC_NUM_OUT LIKE '%' + @p_ACC_NUM_OUT + '%' OR @p_ACC_NUM_OUT IS NULL OR @p_ACC_NUM_OUT = '') 
AND A.RECORD_STATUS = '1'
-- PAGING END
END  
ELSE  
------END 231219  
IF(@p_TOP IS NULL OR @p_TOP = '' OR @p_TOP = 0) 
-- PAGING BEGIN
SELECT A.*,T.SUP_TYPE_NAME,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME,'' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT, U.TLFullName  
-- SELECT END
FROM CM_SUPPLIER A LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS  
LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'  
LEFT JOIN TL_USER U ON A.EDITOR_ID = U.TLNANME
LEFT JOIN CM_SUPPLIERTYPE T ON T.SUP_TYPE_ID = A.SUP_TYPE_ID--,T.SUP_TYPE_NAME
WHERE 1 = 1  
AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')  
AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')  
AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')  
AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')  
AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')  
AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')  
AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')  
AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')  
AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')  
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')  
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 = '')  
--PHONGNT 4/3/2022
AND (A.ACC_NUM LIKE '%' + @p_ACC_NUM + '%' OR @p_ACC_NUM IS NULL OR @p_ACC_NUM = '')  
AND (A.ACC_NUM_OUT LIKE '%' + @p_ACC_NUM_OUT + '%' OR @p_ACC_NUM_OUT IS NULL OR @p_ACC_NUM_OUT = '') 
AND A.RECORD_STATUS = '1'
-- PAGING END
ELSE  
-- PAGING BEGIN
SELECT TOP(@p_TOP)A.*,T.SUP_TYPE_NAME,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME,'' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT, U.TLFullName
-- SELECT END
FROM CM_SUPPLIER A LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS  
LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'  
LEFT JOIN TL_USER U ON A.EDITOR_ID = U.TLNANME
LEFT JOIN CM_SUPPLIERTYPE T ON T.SUP_TYPE_ID = A.SUP_TYPE_ID--,T.SUP_TYPE_NAME
WHERE 1 = 1  
AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')  
AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')  
AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')  
AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')  
AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')  
AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')  
AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')  
AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')  
AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')  
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')  
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 = '')  
--PHONGNT 4/3/2022
AND (A.ACC_NUM LIKE '%' + @p_ACC_NUM + '%' OR @p_ACC_NUM IS NULL OR @p_ACC_NUM = '')  
AND (A.ACC_NUM_OUT LIKE '%' + @p_ACC_NUM_OUT + '%' OR @p_ACC_NUM_OUT IS NULL OR @p_ACC_NUM_OUT = '') 
AND A.RECORD_STATUS = '1'  
-- PAGING END
END -- PAGING  

--EXEC CM_SUPPLIER_Search '','','','','','','','','','','','','','','','',100