PT BIA

??/****** Object:  StoredProcedure [dbo].[CBIACLOSINGBJR]    Script Date: 09/12/2022 10.03.48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CBIACLOSINGBJR]

AS

DECLARE @Now VARCHAR(20) = CONVERT(VARCHAR, GETDATE(), 112) 

DECLARE @Month VARCHAR(20) = CONVERT(VARCHAR, MONTH(GETDATE()), 112) 

DECLARE @Yesterday VARCHAR(20) = CONVERT(VARCHAR,DATEADD(day, -1, CAST(GETDATE() AS date)),112)

DECLARE @YesterdayDate VARCHAR(20) = CONVERT(VARCHAR,DAY(DATEADD(day, -1, CAST(GETDATE() AS date))),112)

DECLARE @StartDateLast VARCHAR(20) = CONVERT(VARCHAR,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0), 112)

DECLARE @EndDateLast VARCHAR(20) = CONVERT(VARCHAR, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)), 112)

DECLARE @StartDate VARCHAR(20) = CONVERT(VARCHAR,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0), 112)

DECLARE @EndDate VARCHAR(20) = CONVERT(VARCHAR,dateadd(ms, -3, dateadd(mm, datediff(mm, 0, getdate()) + 1, 0)), 112)

------ YesterdayDate = hari terakhir data masuk yg akan dihitung berdasarkan createdate, harus minus 1 dari now (tanggal berjalannya job)

------ job dijalankan daily setiap jam 1 malam

IF(@YesterdayDate = (SELECT DISTINCT Value1 FROM dbo.PCMEPGENPARAM WHERE Parameter = 'CBIACLOSINGBJRDATE')) 

BEGIN

	DECLARE @TblMD TABLE

	(

		BlockID VARCHAR(20)

		,AreaHA VARCHAR(20)

		,TBS DECIMAL(22,2)

	)

	INSERT INTO @TblMD

	SELECT md.BlockID, md.AreaHA, SUM(md.TBS)

	FROM dbo.CUSPYPREMIPANEN AS md

	WHERE ((LEFT(md.ChangeDate,8) BETWEEN @StartDateLast AND @Yesterday))

		AND (md.StartDate BETWEEN @StartDateLast AND @EndDateLast)

	GROUP BY md.BlockID, md.AreaHA

	INSERT INTO CUSPERIODBJR

	SELECT @StartDate AS SatartDate

			,@EndDate AS EndDate

			,LEFT(@Now,4) AS [Year]

			,tblmd.BlockID

			,@Month AS [Month]

			,CONVERT(DECIMAL(18,2),(tblmd.TBS/CONVERT(DECIMAL(22,2),blk.AreaHAmeter)) * 300000) AS BJR

			,'Service' AS CreateBy

			,REPLACE(CONVERT(varchar, GETDATE(),101),'/','') + REPLACE(CONVERT(varchar, GETDATE(),108),':','') AS CreateDate

			,'Service' AS ChangeBy

			,REPLACE(CONVERT(varchar, GETDATE(),101),'/','') + REPLACE(CONVERT(varchar, GETDATE(),108),':','') AS ChangeDate

	FROM @TblMD AS tblmd

	LEFT JOIN dbo.CUSMDBLOCK AS blk

		ON tblmd.BlockID = blk.BlockID

END