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(GETDATE()),2) +N' Tháng '+CONVERT(VARCHAR,MONTH(GETDATE()),2) +N' Năm '+CONVERT(VARCHAR,YEAR(GETDATE()),2) AS [DATE],A.NOTES
	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 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 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 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 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 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 1 FROM TR_REQUEST_JOB_FORM trjf JOIN TL_USER tu ON trjf.MAKER_ID=tu.TLNANME WHERE tu.DEP_ID='DEP000000000014'))
    BEGIN
        SET @S5=@S4
    END
    

	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 = COUNT(*) 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 = COUNT(*) 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'
	

	SELECT  D.TLFullName AS DRIVER_NAME, C.N_PLATE AS BIENSOXE, E.CAR_TYPE_NAME AS LOAIXE,B.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, b.CURR_POWER_RATE 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(@TOTAL_AMOUNT_XD,0) AS TOTAL_AMOUNT_XD, ISNULL(@SUM_XD,0) AS SUM_XD,
	--RỬA XE
	ISNULL(@COUNT_RX,0) AS COUNT_RX, ISNULL(@TOTAL_AMOUNT_RX,0) AS TOTAL_AMOUNT_RX,
	---CẦU ĐƯỜNG BẾN BÃI
	ISNULL(@COUNT_CDBB,0) AS COUNT_CDBB, ISNULL(@TOTAL_AMOUNT_CDBB,0) AS TOTAL_AMOUNT_CDBB
	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

END