ALTER PROC dbo.rpt_TR_REQUEST_CAR
@p_REQ_ID		VARCHAR(15) = NULL
AS
BEGIN
	SELECT A.REQ_CODE,B.TLFullName AS EMP_NAME,B.BRANCH_NAME,A.QUANTITY_TRIP,A.MAKER_PHONE,A.ORIGN,a.DESTINATION,
	CONVERT(VARCHAR,DAY(A.FROM_DATE),2) +'/'+CONVERT(VARCHAR,MONTH(A.FROM_DATE),2) +'/'+CONVERT(VARCHAR,YEAR(A.FROM_DATE),2) AS FROM_DATE,
	CONVERT(VARCHAR,DAY(A.TO_DATE),2) +'/'+CONVERT(VARCHAR,MONTH(A.TO_DATE),2) +'/'+CONVERT(VARCHAR,YEAR(A.TO_DATE),2) AS TO_DATE,
	N' Ngày '+CONVERT(VARCHAR,DAY(A.CREATE_DT),2) +N' Tháng '+CONVERT(VARCHAR,MONTH(A.CREATE_DT),2) +N' Năm '+CONVERT(VARCHAR,YEAR(A.CREATE_DT),2) AS [DATE],A.NOTES--17052023_secretkey
	FROM dbo.TR_REQUEST_CAR A
	LEFT JOIN dbo.TL_USER B ON B.TLNANME= A.MAKER_ID
	WHERE REQ_ID=@p_REQ_ID

	--Phần chữ kí--
	DECLARE @S1 NVARCHAR(500),@S2 NVARCHAR(500),@S3 NVARCHAR(500),@S4 NVARCHAR(500),@S5 NVARCHAR(500)
	SET @S1=(SELECT TOP(1) B.TLFullName  +CHAR(10) + N' Đã xác nhận vào lúc ' +CHAR(10) + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='CV_APP')
	SET @S2=(SELECT TOP(1) B.TLFullName + CHAR(10) + N' Đã xác nhận vào lúc ' +CHAR(10)  + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='APPNEW')
	SET @S3=(SELECT TOP(1) B.TLFullName + CHAR(10) + N' Đã xác nhận vào lúc ' +CHAR(10)  + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='CV_XL')
	SET @S4=(SELECT TOP(1) B.TLFullName + CHAR(10) + N' Đã xác nhận vào lúc '+ CHAR(10) + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='TDV_C_APP')
	SET @S5=(SELECT TOP(1) B.TLFullName + CHAR(10) + N' Đã xác nhận vào lúc '+ CHAR(10) + ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM dbo.PL_REQUEST_PROCESS A LEFT JOIN dbo.TL_USER B ON (A.CHECKER_ID=B.TLNANME) WHERE REQ_ID=@p_REQ_ID AND PROCESS_ID='DONE')
    IF(EXISTS(SELECT * FROM TR_REQUEST_JOB_FORM trjf JOIN TL_USER tu ON trjf.MAKER_ID=tu.TLNANME WHERE tu.TLSUBBRID=''))
    SELECT * FROM CM_BRANCH cb

	SELECT @S1 AS S1,@S2 AS S2,@S3 AS S3,@S4 AS S4,@S5 AS S5
	
	------XĂNG DẦU-------
	DECLARE @SUM_XD FLOAT = 0, @TOTAL_AMOUNT_XD FLOAT = 0
	SELECT  @TOTAL_AMOUNT_XD = SUM(AMOUNT*QUANTITY) FROM TR_REQUEST_CAR_DETAIL WHERE REQ_ID =  @p_REQ_ID AND FEE_TYPE = 'XD'
	SELECT  @SUM_XD = SUM(NUMBER_LITERS) FROM TR_REQ_CAR_FUEL WHERE REQ_ID =  @p_REQ_ID 

	DECLARE @REFUEL_DATE NVARCHAR(100)
	SELECT TOP 1 @REFUEL_DATE = FORMAT(REFUEL_DATE,'dd/MM/yyyy')  
	FROM TR_REQ_CAR_FUEL 
	WHERE REQ_ID = @p_REQ_ID 
	ORDER BY REFUEL_DATE DESC

	--------RỬA XE--------
	DECLARE @COUNT_RX INT = 0, @TOTAL_AMOUNT_RX FLOAT = 0
	SELECT  @COUNT_RX = SUM(QUANTITY) FROM TR_REQUEST_CAR_DETAIL WHERE REQ_ID =  @p_REQ_ID AND FEE_TYPE = 'RX'
	SELECT  @TOTAL_AMOUNT_RX = SUM(AMOUNT*QUANTITY) FROM TR_REQUEST_CAR_DETAIL WHERE REQ_ID =  @p_REQ_ID AND FEE_TYPE = 'RX'

	---------CẦU ĐƯỜNG BẾN BÃI---------
	DECLARE @COUNT_CDBB INT = 0, @TOTAL_AMOUNT_CDBB FLOAT = 0
	SELECT  @COUNT_CDBB = SUM(QUANTITY) FROM TR_REQUEST_CAR_DETAIL WHERE REQ_ID =  @p_REQ_ID AND FEE_TYPE = 'CDBB'
	SELECT  @TOTAL_AMOUNT_CDBB = SUM(AMOUNT*QUANTITY) FROM TR_REQUEST_CAR_DETAIL WHERE REQ_ID =  @p_REQ_ID AND FEE_TYPE = 'CDBB'
	
	---------CÔNG TÁC PHÍ---------
	DECLARE @COUNT_CTP INT = 0, @TOTAL_AMOUNT_CTP FLOAT = 0
	SELECT  @COUNT_CTP =  SUM(QUANTITY) FROM TR_REQUEST_CAR_DETAIL WHERE REQ_ID =  @p_REQ_ID AND FEE_TYPE = 'CTP'
	SELECT  @TOTAL_AMOUNT_CTP = SUM(AMOUNT*QUANTITY) FROM TR_REQUEST_CAR_DETAIL WHERE REQ_ID =  @p_REQ_ID AND FEE_TYPE = 'CTP'

	SELECT  A.DRIVER_NAME AS DRIVER_NAME, C.N_PLATE AS BIENSOXE, E.CAR_TYPE_NAME AS LOAIXE,C.POWER_RATE AS DMNL,
	ISNULL(B.OLD_INDEX_NUMBER,0) AS KMD, ISNULL(B.NEW_INDEX_NUMBER,0) AS KMC, ISNULL(B.INDEX_NUMBER,0) AS KMSD,
	ISNULL(B.OLD_FUEL_NUMBER,0) AS SOTONDAU, ISNULL(B.NEW_FUEL_NUMBER,0) AS SOCONLAI, ISNULL(b.CURR_POWER_RATE,0) AS SOTIEUTHU,
	FORMAT(A.FROM_DATE,'dd/MM/yyyy,hh:mm:ss tt') AS FROM_DATE,FORMAT(A.TO_DATE,'dd/MM/yyyy,hh:mm:ss tt') AS TO_DATE,
	---XĂNG DẦU
	@REFUEL_DATE AS REFUEL_DATE, ISNULL(FORMAT(@TOTAL_AMOUNT_XD,'#,#', 'vi-VN'),0) AS TOTAL_AMOUNT_XD, ISNULL(@SUM_XD,0) AS SUM_XD,
	--RỬA XE
	ISNULL(@COUNT_RX,0) AS COUNT_RX, ISNULL(FORMAT(@TOTAL_AMOUNT_RX,'#,#', 'vi-VN'),0) AS TOTAL_AMOUNT_RX,
	---CẦU ĐƯỜNG BẾN BÃI
	ISNULL(@COUNT_CDBB,0) AS COUNT_CDBB, ISNULL(FORMAT(@TOTAL_AMOUNT_CDBB,'#,#', 'vi-VN'),0) AS TOTAL_AMOUNT_CDBB,
	---CÔNG TÁC PHÍ
	ISNULL(@COUNT_CTP,0) AS COUNT_CTP, ISNULL(FORMAT(@TOTAL_AMOUNT_CTP,'#,#', 'vi-VN'),0) AS TOTAL_AMOUNT_CTP
	FROM TR_REQUEST_CAR A
	LEFT JOIN CAR_DRIVE B ON A.CAR_DR_ID = B.CAR_DR_ID
	LEFT JOIN CAR_MASTER C ON A.CAR_ID = C.CAR_ID
	--LEFT JOIN TL_USER D ON A.DRIVER_ID = D.TLNANME
	LEFT JOIN CAR_TYPE E ON C.CAR_TYPE_ID = E.CAR_TYPE_ID
	WHERE A.REQ_ID = @p_REQ_ID

--    EPASS
	SELECT  QUANTITY AS 'SOLUONG', UNIT AS 'DONVITINH', FORMAT(AMOUNT,'#,#', 'vi-VN')  AS 'DONGIA', NOTES AS 'GHICHU', FORMAT(QUANTITY * AMOUNT,'#,#', 'vi-VN') AS 'TONGITEN'  FROM TR_REQUEST_CAR_DETAIL WHERE REQ_ID =  @p_REQ_ID AND FEE_TYPE = 'EP'
--    Công tác phí ngoài giờ
	SELECT  QUANTITY AS 'SOLUONG', UNIT AS 'DONVITINH', FORMAT(AMOUNT,'#,#', 'vi-VN')  AS 'DONGIA', NOTES AS 'GHICHU', FORMAT(QUANTITY * AMOUNT,'#,#', 'vi-VN') AS 'TONGITEN'  FROM TR_REQUEST_CAR_DETAIL WHERE REQ_ID =  @p_REQ_ID AND FEE_TYPE = 'NG'
END