ALTER PROCEDURE dbo.MW_OUT_Search @p_TRN_Date VARCHAR(20) =NULL, @p_TRN_TIME VARCHAR(50) =NULL, @p_BRN_ID VARCHAR(15) =NULL,
    @p_DEPT_ID VARCHAR(15) =NULL, @p_QTY VARCHAR(20)=NULL, @p_PRICE VARCHAR(25) =NULL, @p_TOTAL_AMT NUMERIC(18,2)=NULL, @p_NOTES NVARCHAR(1000) =NULL,
    @p_OUT_DESC NVARCHAR(500) =NULL, @p_AUTH_STATUS VARCHAR(1) =NULL, @p_MAKER_ID VARCHAR(100) =NULL, @p_CREATE_DT varchar(25)=NULL,
    @p_CHECKER_ID VARCHAR(100) =NULL, @p_APPROVE_DT varchar(25)=NULL, @p_KT_AUTH_STATUS VARCHAR(1) =NULL, @p_KT_MAKER_ID VARCHAR(100) =NULL,
    @p_KT_CREATE_DT varchar(25)=NULL, @p_KT_CHECKER_ID VARCHAR(100) =NULL, @p_KT_APPROVE_DT varchar(25)=NULL, @p_RECORD_STATUS VARCHAR(1) =NULL,
    @p_MAKER_LOGIN VARCHAR(15) =NULL, @p_LEVEL VARCHAR(50) ='UNIT', @p_CORE_NOTE NVARCHAR(500) =NULL, @p_FR_BUY_DATE VARCHAR(10) =NULL, --Tu ngay nhap
    @p_TO_BUY_DATE VARCHAR(10) =NULL, @p_KT_IS_DO VARCHAR(1) =NULL, @p_TOP INT=NULL, @p_BRANCH_CREATE VARCHAR(15) =NULL,@p_WARE_ID VARCHAR(15) = NULL,@p_OUT_CODE NVARCHAR(30) = NULL
    ,@p_STATUS VARCHAR(15) = NULL, @p_IS_CANCEL VARCHAR(1), @p_MW_REQ_ID VARCHAR(50)
AS 
BEGIN -- PAGING
DECLARE @tmp TABLE(BRANCH_ID VARCHAR(15))
INSERT INTO @tmp
SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_CREATE)


  DECLARE @DEP_ID VARCHAR(20),@BRANCH_ID VARCHAR(20)
	DECLARE  @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20))

  INSERT INTO @ROLE_LOGIN
  SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_CHECKER_ID) tugr --WHERE tugr.ROLE_ID IN ('GDDV','GDDV_QLTS','TBP_QLTS')

	SET @DEP_ID= (SELECT DEP_ID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_LOGIN)
	SET @BRANCH_ID= (SELECT TLSUBBRID FROM dbo.TL_USER WHERE TLNANME=@p_MAKER_LOGIN)
  IF(EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('GDV,KSV','DV0001','DEP000000000068') FGUBRV WHERE FGUBRV.TLNANME = @p_MAKER_LOGIN)) SET @p_LEVEL='ALL'
IF(@p_TOP IS NULL OR @p_TOP='' OR @p_TOP=0)
-- PAGING BEGIN
    SELECT A.*, B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AS KT_STATUS, TL.TLFullName EMP_NAME, TL1.TLFullName APPROVE_NAME, TL2.TLFullName KT_EMP_NAME,
        TL3.TLFullName KT_APPROVE_NAME, BR.BRANCH_ID AS FRM_BRANCH_ID, DP.DEP_ID AS FRM_DEP_ID, BR.BRANCH_NAME FRM_BRANCH_NAME,
			DP.DEP_NAME FRM_DEP_NAME, BRC.BRANCH_CODE BRANCH_CODE_CR, BRC.BRANCH_NAME BRANCH_NAME_CR
            , AL.WARE_CODE, AL.WARE_NAME AS WARE_HOUSE_NAME
            , CASE WHEN A.IS_CANCEL = '1' THEN N'Phiếu bị huỷ' ELSE AA.CONTENT END STATUS_NAME
            ,A.MW_REQ_ID AS MW_REQ_NAME
-- SELECT END
    FROM MW_OUT A
            LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
            LEFT JOIN CM_AUTH_STATUS C ON A.KT_AUTH_STATUS=C.AUTH_STATUS
            LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
            LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
            LEFT JOIN TL_USER TL2 ON A.KT_MAKER_ID=TL2.TLNANME
            LEFT JOIN TL_USER TL3 ON A.KT_CHECKER_ID=TL3.TLNANME
            LEFT JOIN dbo.CM_BRANCH BR ON A.BRN_ID=BR.BRANCH_ID
            LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEPT_ID
			LEFT JOIN CM_ALLCODE AA ON A.STATUS = AA.CDVAL AND AA.CDNAME='MW_OUT_STATUS'
			LEFT JOIN CM_BRANCH BRC ON A.BRANCH_CREATE = BRC.BRANCH_ID
      LEFT JOIN CM_WARE AL ON A.WARE_ID = AL.WARE_ID
    WHERE 1=1 AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)
            AND(A.KT_AUTH_STATUS=@p_KT_AUTH_STATUS OR @p_KT_AUTH_STATUS='' OR @p_KT_AUTH_STATUS IS NULL)
            AND(CONVERT(DATE, A.TRN_Date, 103)>=CONVERT(DATE, @p_FR_BUY_DATE, 103)OR @p_FR_BUY_DATE IS NULL OR @p_FR_BUY_DATE='')
            AND(CONVERT(DATE, A.TRN_Date, 103)<=CONVERT(DATE, @p_TO_BUY_DATE, 103)OR @p_TO_BUY_DATE IS NULL OR @p_TO_BUY_DATE='')
            AND((A.KT_MAKER_ID IS NOT NULL AND @p_KT_IS_DO='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
                OR((A.KT_MAKER_ID IS NULL AND @p_KT_IS_DO='N'))OR @p_KT_IS_DO IS NULL OR @p_KT_IS_DO='')
            AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp))
                OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)))
			--AND(A.OUT_ID=@p_OUT_ID OR @p_OUT_ID='' OR @p_OUT_ID IS NULL)
		  AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
      AND(A.MAKER_ID LIKE N'%'+@p_MAKER_ID+'%' OR @p_MAKER_ID='' OR @p_MAKER_ID IS NULL)
      AND(((A.BRN_ID =@BRANCH_ID) AND ISNULL(A.DEPT_ID,'') = ISNULL(@DEP_ID,''))
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('GDV,KSV','DV0001','DEP000000000068') FGUBRV WHERE FGUBRV.TLNANME = @p_MAKER_LOGIN)
            --OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('KSV',A.BRN_ID,A.DEPT_ID) FGUBRV WHERE FGUBRV.TLNANME = @p_CHECKER_ID)
            )
			AND(A.WARE_ID=@p_WARE_ID OR @p_WARE_ID='' OR @p_WARE_ID IS NULL)
			AND(A.OUT_CODE LIKE N'%'+@p_OUT_CODE+'%' OR @p_OUT_CODE IS NULL OR @p_OUT_CODE='')
     AND(A.STATUS = @p_STATUS  OR @p_STATUS IS NULL OR @p_STATUS='')
       AND(A.IS_CANCEL = @p_IS_CANCEL  OR @p_IS_CANCEL IS NULL OR @p_IS_CANCEL='')
			AND (A.RECORD_STATUS='1')
      AND(A.MW_REQ_ID LIKE N'%'+@p_MW_REQ_ID+'%' OR @p_MW_REQ_ID='' OR @p_MW_REQ_ID IS NULL)
      OR A.MAKER_ID = @p_MAKER_ID OR A.MAKER_ID = @p_MAKER_LOGIN
	ORDER BY A.CREATE_DT DESC
-- PAGING END@p_IS_CANCEL
ELSE
-- PAGING BEGIN
	SELECT TOP(CONVERT(INT, @p_TOP))A.*, B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AS KT_STATUS, TL.TLFullName EMP_NAME, TL1.TLFullName APPROVE_NAME,
		TL2.TLFullName KT_EMP_NAME, TL3.TLFullName KT_APPROVE_NAME, BR.BRANCH_ID AS FRM_BRANCH_ID, DP.DEP_ID AS FRM_DEP_ID, BR.BRANCH_NAME FRM_BRANCH_NAME,
		DP.DEP_NAME FRM_DEP_NAME, BRC.BRANCH_CODE BRANCH_CODE_CR, BRC.BRANCH_NAME BRANCH_NAME_CR
        , AL.WARE_CODE, AL.WARE_NAME AS WARE_HOUSE_NAME
            , CASE WHEN A.IS_CANCEL = '1' THEN N'Phiếu bị huỷ' ELSE AA.CONTENT END STATUS_NAME
            ,A.MW_REQ_ID AS MW_REQ_NAME
-- SELECT END
	FROM MW_OUT A
			LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
			LEFT JOIN CM_AUTH_STATUS C ON A.KT_AUTH_STATUS=C.AUTH_STATUS
			LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
			LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
			LEFT JOIN TL_USER TL2 ON A.KT_MAKER_ID=TL2.TLNANME
			LEFT JOIN TL_USER TL3 ON A.KT_CHECKER_ID=TL3.TLNANME
			LEFT JOIN dbo.CM_BRANCH BR ON A.BRN_ID=BR.BRANCH_ID
			LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEPT_ID
			LEFT JOIN CM_ALLCODE AA ON A.STATUS = AA.CDVAL AND AA.CDNAME='MW_OUT_STATUS'
			LEFT JOIN CM_BRANCH BRC ON A.BRANCH_CREATE = BRC.BRANCH_ID
      LEFT JOIN CM_WARE AL ON A.WARE_ID = AL.WARE_ID
	WHERE 1=1 AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)
			AND(A.KT_AUTH_STATUS=@p_KT_AUTH_STATUS OR @p_KT_AUTH_STATUS='' OR @p_KT_AUTH_STATUS IS NULL)
			AND(CONVERT(DATE, A.TRN_Date, 103)>=CONVERT(DATE, @p_FR_BUY_DATE, 103)OR @p_FR_BUY_DATE IS NULL OR @p_FR_BUY_DATE='')
			AND(CONVERT(DATE, A.TRN_Date, 103)<=CONVERT(DATE, @p_TO_BUY_DATE, 103)OR @p_TO_BUY_DATE IS NULL OR @p_TO_BUY_DATE='')
			AND((A.KT_MAKER_ID IS NOT NULL AND @p_KT_IS_DO='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat
				OR((A.KT_MAKER_ID IS NULL AND @p_KT_IS_DO='N'))OR @p_KT_IS_DO IS NULL OR @p_KT_IS_DO='')
			AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp))
				OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL)))
			--AND(A.OUT_ID=@p_OUT_ID OR @p_OUT_ID='' OR @p_OUT_ID IS NULL)
			AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL)
      AND(A.MAKER_ID LIKE N'%'+@p_MAKER_ID+'%' OR @p_MAKER_ID='' OR @p_MAKER_ID IS NULL)
      AND(((A.BRN_ID =@BRANCH_ID) AND ISNULL(A.DEPT_ID,'') = ISNULL(@DEP_ID,''))
            OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('GDV,KSV','DV0001','DEP000000000068') FGUBRV WHERE FGUBRV.TLNANME = @p_MAKER_LOGIN)
            --OR EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE_VB('KSV',A.BRN_ID,A.DEPT_ID) FGUBRV WHERE FGUBRV.TLNANME = @p_CHECKER_ID)
            )
			AND(A.WARE_ID=@p_WARE_ID OR @p_WARE_ID='' OR @p_WARE_ID IS NULL)
			AND(A.OUT_CODE LIKE N'%'+@p_OUT_CODE+'%' OR @p_OUT_CODE IS NULL OR @p_OUT_CODE='')
     AND(A.STATUS = @p_STATUS  OR @p_STATUS IS NULL OR @p_STATUS='')
      AND(A.IS_CANCEL = @p_IS_CANCEL OR @p_IS_CANCEL IS NULL OR @p_IS_CANCEL='')
			AND (A.RECORD_STATUS='1')
      AND(A.MW_REQ_ID LIKE N'%'+@p_MW_REQ_ID+'%' OR @p_MW_REQ_ID='' OR @p_MW_REQ_ID IS NULL)
      OR A.MAKER_ID = @p_MAKER_ID OR A.MAKER_ID = @p_MAKER_LOGIN
			ORDER BY A.CREATE_DT DESC
-- PAGING END
END -- PAGING