--CREATE PROC [dbo].[sp_REPORTS_ChargeExtract]
DECLARE
	@PeriodID INT,
	@CompanyID INT,
	@ExpenseCodeGroupID INT
--AS
BEGIN
	BEGIN TRY DROP TABLE #ProcessedData END TRY BEGIN CATCH END CATCH
	SET @CompanyID = 1
	SET @PeriodID = 2019
	SET @ExpenseCodeGroupID = 1034 --1040 1034


	DECLARE @singleQuote CHAR = CHAR(39),
			@SCExemptID INT = (SELECT AttributeDefinitionID FROM ExtendedAttributeDefinition WHERE AttributeKey = 'SCEXEMPT' AND CompanyID IN(@CompanyID, 0)), --AttributeDefinitionID
			@ProtectedRightsID INT = (SELECT AttributeDefinitionID FROM ExtendedAttributeDefinition WHERE AttributeKey = 'PROTECTEDRIGHTS' AND CompanyID IN(@CompanyID, 0)),
			@RentGroupID INT = (SELECT AttributeDefinitionID FROM ExtendedAttributeDefinition where AttributeKey = 'RNTGROUP' AND CompanyID IN (@CompanyID, 0)),
			@NotCalcID INT = (SELECT AttributeDefinitionID FROM ExtendedAttributeDefinition WHERE AttributeKey = 'BUDGETEXEMPT' AND CompanyID IN (@CompanyID, 0)),
			@ServiceChargesECGID INT  = (SELECT ExpenseCodeGroupID FROM ExpenseCodeGroup WHERE name = 'Service Charge' and CompanyID IN (@CompanyID, 0) and @PeriodID BETWEEN StartPeriod and ISNULL(endperiod, 9999))

	--DECLARE @April datetime = CAST(@PeriodID as varchar(4)) + '-04-01 00:00:00.000'
	DECLARE @July datetime = CAST(@PeriodID as varchar(4)) + '-07-01 00:00:00.000'

	SELECT
				' ' as [*],
				@singleQuote + u.OriginalCode + @singleQuote as [Reference],
				'L' as [Ref Type],
				'CH' as [Charge Type],
				' ' as [Charge Code],
				CAST(SUM(bud.PayFreqValue) as decimal(19,2)) as [Charge Value],
				'N' as [VAT Flag],
				' ' as [VAT Value],
				Case
					when @CompanyID = 1 AND pf.Name LIKE 'Weekly%'
						then convert(varchar(max), [dbo].GetEffectiveDateByFreqID(@periodID, pf.PaymentFrequencyID), 105)
					when @CompanyID = 1 AND pf.Name IN('Monthly', 'Annual')
						then '01-04-' + CAST(@PeriodID as varchar(4))
					when @CompanyID = 2 AND pf.Name LIKE 'Weekly%'
						then convert(varchar(max), [dbo].GetEffectiveDateByFreqID(@periodID -1, pf.PaymentFrequencyID), 105)
					when @CompanyID = 2 AND pf.Name ='Monthly'
						then '01-07-' + CAST(@PeriodID -1  as varchar(4))
					when @CompanyID = 3 AND pf.Name LIKE 'Weekly%'
						then convert(varchar(max), [dbo].GetEffectiveDateByFreqID(@periodID, pf.PaymentFrequencyID), 105)
					when @CompanyID = 3 AND pf.Name ='Monthly'
						then '01-04-' + CAST(@PeriodID as varchar(4))
					when @CompanyID = 4 AND pf.Name LIKE 'Weekly%'
						then convert(varchar(max), [dbo].GetEffectiveDateByFreqID(@periodID, pf.PaymentFrequencyID), 105)
					when @CompanyID = 4 AND pf.Name ='Monthly'
						then '01-04-' + CAST(@PeriodID -1  as varchar(4))
					else ''
				END as [Effective Date],
				' ' as [Termination Date],
				' ' as [Secure / Other],
				ISNULL(eavRentGroup.Value, 'NOT SET') as [Rent Group],
				' ' as [Accounts Company],
				' ' as [Cost Centre],
				' ' as [Expense Code],
				@ExpenseCodeGroupID as [Expense Code Group ID],
				eavProtRights.Value as [ProtRights],
				ISNULL(eavNotCalc.Value, 'false') as [NotCalc]
	--SELECT *
	INTO		#ProcessedData
	FROM		Unit u
	INNER JOIN	ManagementUnit mu
		ON		mu.MUID = u.MUID
		AND		@PeriodID BETWEEN mu.StartPeriod AND ISNULL(mu.EndPeriod, 9999)
	INNER JOIN	ChargeUnit cu
		ON		mu.MUID = cu.MUID
		AND		@PeriodID BETWEEN cu.StartPeriod AND ISNULL(cu.EndPeriod, 9999)
		AND		cu.OriginalCode NOT LIKE '%_DUM'
	INNER JOIN	ChargeUnitUnits cuu
		ON		cuu.UnitID = u.UnitID
		AND		cuu.CUID = cu.CUID
		AND		@PeriodID BETWEEN cuu.StartPeriod AND ISNULL(cuu.EndPeriod, 9999)
	INNER JOIN	HierarchyNode hn
		ON		mu.ParentNodeID = hn.NodeID
		AND		@PeriodID BETWEEN hn.StartPeriod AND ISNULL(hn.EndPeriod,9999)
	LEFT JOIN	ExtendedAttributeValues eavSCExempt
		ON		mu.MUID = eavSCExempt.ObjectID
		AND		eavSCExempt.AttributeDefinitionID = @SCExemptID
		AND		@PeriodID BETWEEN eavSCExempt.StartPeriod AND ISNULL(eavSCExempt.EndPeriod, 9999)
		AND		(eavSCExempt.Value IS NULL or eavSCExempt.Value IN('FALSE', 'false', '0', 'f', 'F'))
	FULL JOIN	ExtendedAttributeValues eavNotCalc
		ON		cu.CUID = eavNotCalc.ObjectID
		AND		eavNotCalc.AttributeDefinitionID = @NotCalcID
		AND		@PeriodID BETWEEN eavNotCalc.StartPeriod AND ISNULL(eavNotCalc.EndPeriod, 9999)
		AND		(eavNotCalc.Value IS NULL  OR eavNotCalc.Value  IN ('TRUE', 'T', '1', 'YES', 'Y'))
	FULL JOIN	ExtendedAttributeValues eavProtRights
		ON		eavProtRights.ObjectID = u.UnitID
		AND		@PeriodID BETWEEN eavProtRights.StartPeriod AND ISNULL(eavProtRights.EndPeriod,9999)
		AND		eavProtRights.AttributeDefinitionID = @ProtectedRightsID
	FULL JOIN	ExtendedAttributeValues eavRentGroup
		ON		eavRentGroup.ObjectID = u.UnitID
		AND		@PeriodID BETWEEN eavRentGroup.StartPeriod AND ISNULL(eavRentGroup.EndPeriod, 9999)
		AND		eavRentGroup.AttributeDefinitionID = @RentGroupID
	INNER JOIN	fn_CORE_Budgets_BASE(@PeriodID, @CompanyID, CAST(@companyID as varchar(1)), DEFAULT, DEFAULT, DEFAULT, DEFAULT) bud
		ON		mu.MUID = bud.MUID
		AND		u.UnitID = bud.UnitID
		AND		@ExpenseCodeGroupID = bud.ExpenseCodeGroupID
		and		bud.IsExcluded = 0
		AND		bud.IsOmitted = 0
	INNER JOIN	ExpenseCode ec
		ON		ec.ExpenseCodeID = bud.ExpenseCodeID
		AND		ec.CompanyID = @CompanyID
		AND		@PeriodID BETWEEN ec.StartPeriod AND ISNULL(ec.EndPeriod, 9999)
	INNER JOIN	ExpenseCodeGroup ecg
		ON		@ExpenseCodeGroupID = ecg.ExpenseCodeGroupID
		AND		ecg.CompanyID = @CompanyID
		AND		@PeriodID BETWEEN ecg.StartPeriod AND ISNULL(ecg.EndPeriod, 9999)
	INNER JOIN	WorkflowDefinition wd
		ON		ecg.WorkflowGUID = wd.WorkflowGUID
		AND		wd.CompanyID IN(@CompanyID, 0)
	INNER JOIN	WorkflowStepDefinition wfsd
		ON		wfsd.WorkflowGUID = wd.WorkflowGUID
		AND		FinalStep = 1
	INNER JOIN Workflowstatus wfs
		ON		wfs.MUID = mu.MUID
		AND		wfs.WorkflowGUID = wd.WorkflowGUID
		AND		wfs.CompanyID IN (@CompanyID, 0)
		AND		wfs.CurrentStepGUID = wfsd.StepGUID
		AND		@PeriodID = wfs.PeriodID
	INNER JOIN	PaymentFrequency pf
		ON		pf.PaymentFrequencyID = bud.PayFrq
		AND		pf.CompanyID IN (@CompanyID, 0)
	WHERE	    @companyID = CAST(LEFT(Ancestry,1) as int)
	AND			@PeriodID between u.StartPeriod AND ISNULL(u.EndPeriod, 9999)
	--AND			(ecg.ExpenseCodeGroupID = @ServiceChargesECGID AND eavProtRights.Value NOT IN ('TRUE', 'true', 'T', 't', '1'))
	GROUP BY	u.Name, u.UnitID, u.OriginalCode, eavProtRights.Value, eavRentGroup.Value, eavNotCalc.Value , pf.PaymentFrequencyID, pf.Name --, ecg.ExpenseCodeGroupID, ecg.Name -- eavRentGroup.Value, eavProtRights.Value,  pf.Name,
	order by	u.OriginalCode

	DELETE FROM #ProcessedData where [Expense Code Group ID] = @ServiceChargesECGID and ProtRights IN ('TRUE', 'true', 'T', 't', '1')
	DELETE FROM #ProcessedData where NotCalc IN ('TRUE', 'true', 'T', 't', '1')


	SELECT 		[*],[Reference],[Ref Type],[Charge Type],[Charge Code],[Charge Value],[VAT Flag],[VAT Value],[Effective Date],[Termination Date],[Secure / Other],
				[Rent Group],[Accounts Company],[Cost Centre],[Expense Code]
	FROM		#ProcessedData
	order by	Reference
END