DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

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