IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[rpt_MW_IN_PNK_Excel_BanViet]'
GO
ALTER PROCEDURE [dbo].[rpt_MW_IN_PNK_Excel_BanViet]
@IN_ID	varchar(15) = NULL
AS
SELECT Row_number() over(order by A.IN_ID) AS STT,A.*, B.GROUP_CODE, B.GROUP_NAME,C.MATERIAL_ACCTNO, C.MATERIAL_ACCTNO_NHNN, C.MATERIAL_CODE, A. MATERIAL_NAME,D.UNIT_ID, D.UNIT_CODE, D.UNIT_NAME
FROM MW_IN A 
LEFT JOIN MW_GROUP B ON A.GROUP_ID = B.GROUP_ID
LEFT JOIN MW_MATERIAL C ON A.MATERIAL_ID = C.MATERIAL_ID
LEFT JOIN CM_UNIT D ON C.UNIT_ID= D.UNIT_ID
WHERE  IN_MASTER_ID= @IN_ID OR @IN_ID IS NULL

SELECT TOP 1 N'TP. Hồ Chí Minh, ngày ' + RIGHT('0' + CAST(DATEPART(DD, A.CREATE_DT) AS VARCHAR(2)), 2)
+ N' tháng ' + RIGHT('0' + CAST(DATEPART(MM, A.CREATE_DT) AS VARCHAR(2)), 2)
+ N' năm ' + RIGHT(YEAR(A.CREATE_DT), 4) AS CREATE_DT
FROM MW_IN A 
WHERE  IN_MASTER_ID= @IN_ID OR @IN_ID IS NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[rpt_TR_REQ_PAY_Job_DT_ById]'
GO

ALTER   PROC [dbo].[rpt_TR_REQ_PAY_Job_DT_ById]
@p_REQ_PAY_ID varchar(15) = NULL,
@p_BRANCH_CREATE varchar(15) = NULL,
@p_DEP_ID varchar(15) = NULL,
@p_CHECKR_ID varchar(15) = NULL,
@p_CHECKR_ID_KT varchar(15) = NULL,
@p_APPROVE_DT_KT varchar(15) = NULL
AS
BEGIN
	SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 1)) AS STT, JF.REQ_CODE, A.FULLNAME, A.JOB_PLACE, FORMAT (A.FRMDATE, 'dd/MM/yyyy '), FORMAT (A.TODATE, 'dd/MM/yyyy '), 
	CAST(DATEDIFF(day,A.FRMDATE, A.TODATE) as decimal(18, 0)) AS NUMBER_DAY,
	A.DAY_RATE, A.JOB_COST, A.COST_RESIDENCE, A.TYPE_TRANS, A.COST_AMT, A.COST_OTHER, A.COST_MOVE,
	A.DAY_RATE + A.JOB_COST + A.COST_RESIDENCE + A.COST_AMT + A.COST_OTHER AS TOTAL_AMT
	FROM TR_REQ_PAY_Job_DT A 
	LEFT JOIN TR_REQUEST_JOB_FORM JF ON A.REQ_ID = JF.REQ_ID
	--LEFT JOIN TR_REQ_PAY_JOB J ON A.REQ_ID = J.REQ_ID
	LEFT JOIN TR_REQ_PAYMENT TR ON A.REQ_PAY_ID = TR.REQ_PAY_ID
	LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID = @p_BRANCH_CREATE
	LEFT JOIN TL_USER T ON T.TLNANME = A.FULLNAME
	WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID

	SELECT TOP 1 N'Từ ngày ' + CAST(FORMAT (A.FRMDATE, 'dd/MM/yyyy ') as nvarchar) + ', ' AS FRMDATE
	FROM TR_REQ_PAY_Job_DT A
	WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
	ORDER BY A.FRMDATE ASC

	SELECT TOP 1 N'đến ngày ' + CAST(FORMAT (A.TODATE, 'dd/MM/yyyy ') as nvarchar) AS TODATE
	FROM TR_REQ_PAY_Job_DT A
	WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
	ORDER BY A.TODATE DESC

	SELECT TOP 1 B.REQ_PAY_CODE, ISNULL(A.AMT_PAY,0) AS AMT_PAY
	FROM TR_REQ_PAYMENT_DT A
	LEFT JOIN TR_REQ_PAYMENT B ON A.PAY_ID = B.REQ_PAY_ID
	WHERE A.PAY_ID =@p_REQ_PAY_ID
	
	DECLARE @AMT_PAY DECIMAL(18,2)
	SELECT TOP 1 @AMT_PAY = ISNULL(A.AMT_PAY,0)
	FROM TR_REQ_PAYMENT_DT A
	WHERE A.PAY_ID =@p_REQ_PAY_ID

	SELECT @AMT_PAY - ISNULL(SUM(A.COST_AMT),0) AS 'TIENHOANUNGCHITHEM', ISNULL(SUM(A.JOB_COST),0) AS SUM_JOB_COST, ISNULL(SUM(A.COST_RESIDENCE),0) AS SUM_COST_RESIDENCE, ISNULL(SUM(A.COST_MOVE),0) AS SUM_COST_MOVE, ISNULL(SUM(A.COST_AMT),0) AS SUM_COST_AMT FROM TR_REQ_PAY_Job_DT A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
END
GO
IF @@ERROR <> 0 SET NOEXEC ON