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(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