ALTER   PROC [dbo].[TR_REQUEST_CAR_Search]
@p_REQ_CODE varchar(150) = NULL,
@p_USER_ID varchar(15) = NULL,
@p_PHONE_USER varchar(15) = NULL,
@p_BRANCH_ID varchar(15) = NULL,
@p_BRANCH_CREATE varchar(15) = NULL,
@p_DEP_ID varchar(15) = NULL,
@p_DEP_CREATE varchar(15) = NULL,
@p_ORIGN nvarchar(100) = NULL,
@p_DESTINATION nvarchar(100) = NULL,
@p_IS_SEND_APPR varchar(15) = NULL,
@p_SEND_APPR_DT varchar(20) = NULL,
@p_SIGN_USER varchar(15) = NULL,
@p_SIGN_DT varchar(20) = NULL,
@p_FROM_DATE varchar(20) = NULL,
@p_TO_DATE varchar(20) = NULL,
@p_NOTES nvarchar(MAX) = NULL,
@p_CAR_ID varchar(15) = NULL,
@p_DRIVER_ID varchar(15) = NULL,
@p_QUANTITY_TRIP int = NULL,
@p_NOTES_TRIP nvarchar(MAX) = NULL,
@p_UNIT_TRIP varchar(5) = NULL,
@p_TO_TRIP nvarchar(100) = NULL,
@p_FROM_TRIP nvarchar(100) = NULL,
@p_MILRAGE_TRIP int = NULL,
@p_START_TIME_TRIP varchar(20) = NULL,
@p_QUANTITY_RETURN_TRIP int = NULL,
@p_NOTES_RETURN_TRIP varchar(MAX) = NULL,
@p_UNIT_RETURN_TRIP varchar(5) = NULL,
@p_TO_RETURN_TRIP nvarchar(100) = NULL,
@p_FROM_RETURN_TRIP nvarchar(100) = NULL,
@p_MILRAGE_RETURN_TRIP int = NULL,
@p_START_TIME_RETURN_TRIP varchar(20) = NULL,
@p_AUTH_STATUS varchar(1) = NULL,
@p_APPROVE_DT varchar(20) = NULL,
@p_MODEL varchar(20) = NULL,
@p_N_PLATE varchar(20) = NULL,
@p_USER_LOGIN varchar(20) = NULL,
@p_FDATE varchar(20)=NUll,
@p_TDATE varchar(20)=NUll,
@p_TOP	INT = NULL
AS

BEGIN -- PAGING
	--DECLARE @LST_ROLE TABLE(ROLENAME VARCHAR(20))
	--INSERT INTO @LST_ROLE (ROLENAME) (SELECT C.DisplayName FROM TL_USER A JOIN AbpUserRoles B ON B.UserId = A.ID JOIN AbpRoles C ON C.Id=B.RoleId WHERE A.TLNANME =@p_USER_LOGIN)
	--INSERT INTO @LST_ROLE (ROLENAME) (SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE  CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE) AND TLNAME=@p_USER_LOGIN)
	DECLARE @LST_ROLE TABLE(ROLENAME VARCHAR(20),BRANCH_ID VARCHAR(20),DEP_ID VARCHAR(20))
	INSERT INTO @LST_ROLE (ROLENAME,BRANCH_ID,DEP_ID) (SELECT C.DisplayName,A.TLSUBBRID,A.DEP_ID FROM TL_USER A JOIN AbpUserRoles B ON B.UserId = A.ID JOIN AbpRoles C ON C.Id=B.RoleId WHERE A.TLNANME =@p_USER_LOGIN)
	INSERT INTO @LST_ROLE (ROLENAME,BRANCH_ID,DEP_ID) (SELECT ROLE_NEW,BRANCH_ID,DEP_ID FROM dbo.TL_SYS_ROLE_MAPPING WHERE  CAST(EXP_DATE AS DATE) >= CAST(GETDATE() AS DATE) AND TLNAME=@p_USER_LOGIN)
	
	IF(@p_TOP IS NULL OR @P_TOP = '' OR @P_TOP = 0)
	-- PAGING BEGIN
	SELECT A.*,B.CONTENT AS AUTH_STATUS_PROCESS_NAME, D.BRANCH_NAME,E.TLFullName AS CHECKER_NAME,F.TLFullName AS MAKER_NAME,RPN.NOTES AS PROCESS_STATUS_NEXT,
	(CASE 
		WHEN (A.PROCESS_ID IN ('CVSENDCONF','MAKER_APP') AND A.MAKER_ID=@p_USER_LOGIN ) THEN 'Y'
		WHEN (A.PROCESS_ID IN ('APPNEW', 'TDV_C_APP') AND EXISTS(SELECT * FROM @LST_ROLE WHERE ROLENAME IN('GDDV','PGD', 'TP', 'PP','TPGD','PPGD'))) THEN 'Y'
		WHEN (A.PROCESS_ID IN ('CV_APP') AND EXISTS (SELECT 1 FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr WHERE tugr.ROLE_ID='CVDDX' AND tugr.BRANCH_ID=A.BRANCH_CREATE)) THEN 'Y'
		WHEN (A.PROCESS_ID IN ('DONE') AND (EXISTS  (SELECT A.* FROM TL_USER A JOIN AbpUserRoles B ON B.UserId = A.ID JOIN AbpRoles C ON C.Id=B.RoleId WHERE C.DisplayName IN('GDDV','PP')))) THEN 'Y'
		ELSE 'N' 
	END) AS IS_APPROVE
	-- SELECT END
	FROM TR_REQUEST_CAR A 
	LEFT JOIN CM_ALLCODE B ON ( B.CDVAL  = A.AUTH_STATUS AND B.CDNAME='AUTH_STATUS' AND B.CDTYPE='TR_REQ_CAR')
	--LEFT JOIN CAR_MASTER C ON C.CAR_ID = A.CAR_ID
	LEFT JOIN CM_BRANCH D  ON D.BRANCH_ID=A.BRANCH_ID
	LEFT JOIN TL_USER E  ON E.TLNANME=A.CHECKER_ID
	LEFT JOIN TL_USER F  ON F.TLNANME=A.MAKER_ID
	LEFT JOIN dbo.PL_REQUEST_PROCESS RPN 
	ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') AND (Temp.IS_HAS_CHILD IS NULL OR Temp.IS_HAS_CHILD =0))
	LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPNC
	ON RPNC.PROCESS_ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') AND Temp.IS_HAS_CHILD=1) AND (RPNC.STATUS_JOB = 'C' OR RPNC.STATUS_JOB = 'R')
	WHERE 1 = 1
		AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR  @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
		AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
		AND (A.AUTH_STATUS = @p_AUTH_STATUS 
		OR (@p_AUTH_STATUS='M' AND (EXISTS(SELECT 1 from wsiSplit(RPN.ROLE_USER,',')WHERE value =@p_USER_LOGIN) OR EXISTS (SELECT 1 from wsiSplit(RPN.ROLE_USER,',')WHERE value IN(SELECT ROLENAME FROM @LST_ROLE WHERE (BRANCH_ID=RPN.BRANCH_ID OR RPN.BRANCH_ID IS NULL OR RPN.BRANCH_ID ='') AND (DEP_ID=RPN.DEP_ID OR RPN.DEP_ID IS NULL OR RPN.DEP_ID='')))))
		--OR (@p_AUTH_STATUS='M' AND (EXISTS(SELECT 1 from wsiSplit(RPN.ROLE_USER,',')WHERE value =@p_USER_LOGIN) OR EXISTS (SELECT value from wsiSplit(RPN.ROLE_USER,',')WHERE value IN(SELECT ROLENAME FROM @LST_ROLE))))
--    OR (@p_AUTH_STATUS='M' AND RPN.ROLE_USER = 'CVDDX' AND EXISTS(SELECT 1 FROM CM_ALLCODE ca LEFT JOIN TL_USER tu ON ca.CDVAL= tu.TLNANME WHERE ca.CDNAME='REQCAR' AND ca.CDTYPE='TR' AND ca.CDVAL=@p_USER_LOGIN AND tu.TLSUBBRID=RPN.BRANCH_ID))
		OR (@p_AUTH_STATUS='M' AND (RPNC.TLNAME = @p_USER_LOGIN))
    OR (@p_AUTH_STATUS='M' AND ( A.AUTH_STATUS='E' OR a.IS_SEND_APPR<>'Y') AND (A.MAKER_ID= @p_USER_LOGIN))
		OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		AND (A.MO_NAME LIKE '%' + @p_MODEL + '%' OR  @p_MODEL IS NULL OR @p_MODEL = '')
		AND (A.N_PLATE LIKE '%' + @p_N_PLATE + '%' OR  @p_N_PLATE IS NULL OR @p_N_PLATE = '')
		AND(CONVERT(DATE, A.REQ_DT)>=CONVERT(DATE, @p_FDATE, 103)OR @p_FDATE IS NULL OR @p_FDATE='')
		AND(CONVERT(DATE, A.REQ_DT)<=CONVERT(DATE, @p_TDATE, 103)OR @p_TDATE IS NULL OR @p_TDATE='')
		AND ((A.MAKER_ID=@p_USER_LOGIN) OR A.EMP_ID=@p_USER_LOGIN OR A.DRIVER_ID=@p_USER_LOGIN OR A.USER_UPDATE = @p_USER_LOGIN 
--    OR (EXISTS(SELECT 1 FROM CM_ALLCODE WHERE CDNAME='REQCAR' AND CDVAL=@p_USER_LOGIN) AND A.PROCESS_ID NOT IN('INSERT','CANCEL','SEND','REJECT'))
		OR (EXISTS(SELECT 1 FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr WHERE tugr.ROLE_ID='CVDDX' AND tugr.BRANCH_ID=A.BRANCH_CREATE ) AND A.PROCESS_ID NOT IN('INSERT','CANCEL','SEND','REJECT'))
		OR (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',A.BRANCH_CREATE,A.DEP_CREATE)WHERE TLNANME=@p_USER_LOGIN) AND A.PROCESS_ID IN ('APPNEW','TDV_C_APP'))
		OR (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV','DV0001','DEP000000000014')WHERE TLNANME=@p_USER_LOGIN) AND A.PROCESS_ID IN ('DONE'))
		OR (A.USER_UPDATE=@p_USER_LOGIN AND  A.PROCESS_ID IN('CV_SEND','USER_SEND'))
		OR(EXISTS(SELECT 1 FROM dbo.PL_REQUEST_PROCESS prp WHERE prp.REQ_ID=A.REQ_ID AND prp.CHECKER_ID=@p_USER_LOGIN AND prp.STATUS='P' AND A.AUTH_STATUS<>'D'))
		OR(EXISTS(SELECT 1 FROM dbo.PL_REQUEST_PROCESS_CHILD prpc WHERE prpc.REQ_ID=A.REQ_ID AND prpc.TLNAME=@p_USER_LOGIN AND prpc.STATUS_JOB='P' AND A.AUTH_STATUS<>'D'))
		OR(A.AUTH_STATUS='A' AND EXISTS(SELECT 1 FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr WHERE tugr.ROLE_ID='KSV' OR tugr.ROLE_ID='GDV'))
		OR(EXISTS(SELECT 1 FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr WHERE tugr.ROLE_ID='TBP' AND tugr.DEPT_ID='DEP000000000014'))
		)
		ORDER BY A.REQ_ID DESC
	-- PAGING END
	ELSE
	-- PAGING BEGIN

	SELECT  TOP(CONVERT(INT,@P_TOP))A.*,B.CONTENT AS AUTH_STATUS_PROCESS_NAME, D.BRANCH_NAME,E.TLFullName AS CHECKER_NAME,F.TLFullName AS MAKER_NAME,RPN.NOTES AS PROCESS_STATUS_NEXT,
	(CASE 
		WHEN (A.PROCESS_ID IN ('CVSENDCONF','MAKER_APP') AND A.MAKER_ID=@p_USER_LOGIN ) THEN 'Y'
		WHEN (A.PROCESS_ID IN ('APPNEW', 'TDV_C_APP') AND EXISTS(SELECT * FROM @LST_ROLE WHERE ROLENAME IN('GDDV','PGD', 'TP', 'PP','TPGD','PPGD'))) THEN 'Y'
		WHEN (A.PROCESS_ID IN ('CV_APP') AND EXISTS (SELECT 1 FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr WHERE tugr.ROLE_ID='CVDDX' AND tugr.BRANCH_ID=A.BRANCH_CREATE)) THEN 'Y'
		WHEN (A.PROCESS_ID IN ('DONE') AND (EXISTS  (SELECT A.* FROM TL_USER A JOIN AbpUserRoles B ON B.UserId = A.ID JOIN AbpRoles C ON C.Id=B.RoleId WHERE C.DisplayName IN('GDDV','PP')))) THEN 'Y'
		ELSE 'N' 
	END) AS IS_APPROVE
	-- SELECT END

	FROM TR_REQUEST_CAR A 
	LEFT JOIN CM_ALLCODE B ON ( B.CDVAL  = A.AUTH_STATUS AND B.CDNAME='AUTH_STATUS' AND B.CDTYPE='TR_REQ_CAR')
	--LEFT JOIN CAR_MASTER C ON C.CAR_ID = A.CAR_ID
	LEFT JOIN CM_BRANCH D  ON D.BRANCH_ID=A.BRANCH_ID
	LEFT JOIN TL_USER E  ON E.TLNANME=A.CHECKER_ID
	LEFT JOIN TL_USER F  ON F.TLNANME=A.MAKER_ID
	LEFT JOIN dbo.PL_REQUEST_PROCESS RPN 
	ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') AND (Temp.IS_HAS_CHILD IS NULL OR Temp.IS_HAS_CHILD =0))
	LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPNC
	ON RPNC.PROCESS_ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') AND Temp.IS_HAS_CHILD=1) AND (RPNC.STATUS_JOB = 'C' OR RPNC.STATUS_JOB = 'R')
	WHERE 1 = 1
		AND (A.REQ_CODE LIKE '%' + @p_REQ_CODE + '%' OR  @p_REQ_CODE IS NULL OR @p_REQ_CODE = '')
		AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
		AND (A.AUTH_STATUS = @p_AUTH_STATUS 
		OR (@p_AUTH_STATUS='M' AND (EXISTS(SELECT 1 from wsiSplit(RPN.ROLE_USER,',')WHERE value =@p_USER_LOGIN) OR EXISTS (SELECT 1 from wsiSplit(RPN.ROLE_USER,',')WHERE value IN(SELECT ROLENAME FROM @LST_ROLE WHERE (BRANCH_ID=RPN.BRANCH_ID OR RPN.BRANCH_ID IS NULL OR RPN.BRANCH_ID ='') AND (DEP_ID=RPN.DEP_ID OR RPN.DEP_ID IS NULL OR RPN.DEP_ID='')))))
		--OR (@p_AUTH_STATUS='M' AND (EXISTS(SELECT value from wsiSplit(RPN.ROLE_USER,',')WHERE value =@p_USER_LOGIN) OR EXISTS (SELECT value from wsiSplit(RPN.ROLE_USER,',')WHERE value IN(SELECT ROLENAME FROM @LST_ROLE))))
    OR (@p_AUTH_STATUS='M' AND  ( A.AUTH_STATUS='E' OR a.IS_SEND_APPR<>'Y') AND (A.MAKER_ID= @p_USER_LOGIN))
--    OR (@p_AUTH_STATUS='M' AND RPN.ROLE_USER = 'CVDDX' AND EXISTS(SELECT 1 FROM CM_ALLCODE ca LEFT JOIN TL_USER tu ON ca.CDVAL= tu.TLNANME WHERE ca.CDNAME='REQCAR' AND ca.CDTYPE='TR' AND ca.CDVAL=@p_USER_LOGIN AND tu.TLSUBBRID=RPN.BRANCH_ID))
		OR (@p_AUTH_STATUS='M' AND (RPNC.TLNAME = @p_USER_LOGIN))
		OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
		AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
		AND (A.MO_NAME LIKE '%' + @p_MODEL + '%' OR  @p_MODEL IS NULL OR @p_MODEL = '')
		AND (A.N_PLATE LIKE '%' + @p_N_PLATE + '%' OR  @p_N_PLATE IS NULL OR @p_N_PLATE = '')
		AND(CONVERT(DATE, A.REQ_DT)>=CONVERT(DATE, @p_FDATE, 103)OR @p_FDATE IS NULL OR @p_FDATE='')
		AND(CONVERT(DATE, A.REQ_DT)<=CONVERT(DATE, @p_TDATE, 103)OR @p_TDATE IS NULL OR @p_TDATE='')
		AND ((A.MAKER_ID=@p_USER_LOGIN) OR A.EMP_ID=@p_USER_LOGIN OR A.DRIVER_ID=@p_USER_LOGIN OR A.USER_UPDATE = @p_USER_LOGIN 
--		OR (EXISTS(SELECT 1 FROM CM_ALLCODE WHERE CDNAME='REQCAR' AND CDVAL=@p_USER_LOGIN) AND A.PROCESS_ID NOT IN('INSERT','CANCEL','SEND','REJECT'))
	  OR (EXISTS(SELECT 1 FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr WHERE tugr.ROLE_ID='CVDDX' AND tugr.BRANCH_ID=A.BRANCH_CREATE ) AND A.PROCESS_ID NOT IN('INSERT','CANCEL','SEND','REJECT'))
		OR (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV',A.BRANCH_CREATE,A.DEP_CREATE)WHERE TLNANME=@p_USER_LOGIN) AND A.PROCESS_ID IN ('APPNEW','TDV_C_APP'))
		OR (EXISTS(SELECT 1 FROM dbo.FN_GET_USER_BY_ROLE('GDDV','DV0001','DEP000000000014')WHERE TLNANME=@p_USER_LOGIN) AND A.PROCESS_ID IN ('DONE'))
		OR (A.USER_UPDATE=@p_USER_LOGIN AND  A.PROCESS_ID IN('CV_SEND','USER_SEND'))
		OR(EXISTS(SELECT 1 FROM dbo.PL_REQUEST_PROCESS prp WHERE prp.REQ_ID=A.REQ_ID AND prp.CHECKER_ID=@p_USER_LOGIN AND prp.STATUS='P' AND A.AUTH_STATUS<>'D'))
		OR(EXISTS(SELECT 1 FROM dbo.PL_REQUEST_PROCESS_CHILD prpc WHERE prpc.REQ_ID=A.REQ_ID AND prpc.TLNAME=@p_USER_LOGIN AND prpc.STATUS_JOB='P' AND A.AUTH_STATUS<>'D'))
		OR(A.AUTH_STATUS='A' AND EXISTS(SELECT 1 FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr WHERE tugr.ROLE_ID='KSV' OR tugr.ROLE_ID='GDV'))
		OR(EXISTS(SELECT 1 FROM dbo.TL_USER_GET_ROLES(@p_USER_LOGIN) tugr WHERE tugr.ROLE_ID='TBP' AND tugr.DEPT_ID='DEP000000000014'))
		)
		ORDER BY A.REQ_ID DESC
	-- PAGING END

END -- PAGING