SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReloadPlanFactStatistics]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReloadPlanFactStatistics]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2019-04-15
-- Description: Заполнение таблицы статиски план/факт
-- =============================================
CREATE PROCEDURE [dbo].[sp_ReloadPlanFactStatistics]
AS
BEGIN

	declare @sql nvarchar(max)
	SELECT @sql = 'DECLARE @currentDate datetime = GETDATE(), @empty uniqueidentifier = ''00000000-0000-0000-0000-000000000000''
	TRUNCATE TABLE [FIES].[T_PLAN_FACT_STATISTICS]
	INSERT INTO [FIES].[T_PLAN_FACT_STATISTICS] ([PlanFactStatisticsID], ' + 
		+ cast((select '[' + ps.SectionAlias + '], ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
		+ '[Mo], [TfomsBranch], [PlanPeriod], [PlanParameter], [Periods], [PlanValue], [FactValue], [PlanSum], [FactSum])'
		+ ' SELECT NEWID(), '
		+ cast((select 'pl.' + ps.SectionAlias + ', ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
		+ 'p.Mo, tbmo.TfomsBranch, p.PlanPeriod, pv.PlanParameter, ps.Periods, SUM(CASE '
		+ cast((select 'WHEN ps.Periods = ' + cast(ps.Periods as varchar(max)) + ' THEN mv.' + ps.PeriodAlias + ' ' from FIES.T_PERIOD_SECTION ps for xml path('')) as varchar(max)) 
		+ 'END), 0, 0, 0 '
		+ 'FROM FIES.T_PLAN_VALUE pv JOIN FIES.T_PLAN_ITEM pl on pl.PlanItemID = pv.PlanItem JOIN FIES.T_PLAN p on p.PlanID = pl.[Plan] JOIN FIES.T_PLAN_REQUEST pr on pr.PlanID = p.PlanID JOIN FIES.T_TFOMS_BRANCH_MO tbmo on tbmo.Mo = p.Mo JOIN FIES.T_PLAN_ITEM_MONTH_VALUES pimv on pimv.PlanValue = pv.PlanValueID JOIN FIES.T_MONTH_VALUES mv on mv.MonthValuesID = pimv.MonthValuesID, (SELECT Periods from FIES.T_PERIOD_SECTION) ps '
		+ 'WHERE pl.Approved = 1 AND pr.RequestTime < @currentDate AND pr.[Status] = 5 AND pr.TfomsStatus = 5 '
		+ 'AND NOT EXISTS(SELECT * FROM FIES.T_PLAN_ITEM pl1 JOIN FIES.T_PLAN p1 on p1.PlanID = pl1.[Plan] JOIN FIES.T_PLAN_REQUEST pr1 on pr1.PlanID = p1.PlanID '
				+ 'WHERE p1.Mo = p.Mo AND p1.PlanPeriod = p.PlanPeriod AND '
				+ cast((select 'pl1.' + ps.SectionAlias + ' = pl.' + ps.SectionAlias + ' AND ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
				+ 'pr1.RequestTime > pr.RequestTime AND pl1.Approved = 1 AND pr1.RequestTime < @currentDate AND pr.[Status] = 5 AND pr.TfomsStatus = 5) '
		+ 'GROUP BY '
		+ cast((select 'pl.' + ps.SectionAlias + ', ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
		+ 'p.Mo, tbmo.TfomsBranch, p.PlanPeriod, pv.PlanParameter, ps.Periods
	'
		+ 'UPDATE pfs SET pfs.FactValue = fi.Value, pfs.FactSum = fi.ValueSum FROM [FIES].[T_PLAN_FACT_STATISTICS] pfs JOIN ('
		+ 'SELECT fi.Mo, '
		+ cast((select 'fi.' + ps.SectionAlias + ', ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
		+ 'pp.PlanPeriodID, fi.PlanParameter, ps.Periods, SUM(fi.Value) Value, SUM(fi.ValueSum) ValueSum '
		+ 'FROM FIES.T_FACT_ITEM fi '
		+ 'JOIN FIES.T_PLAN_PERIOD pp on pp.[Year] = fi.FactYear JOIN FIES.T_PERIOD_SECTION ps on ps.PeriodSectionID = fi.PeriodSection '
		+ 'GROUP BY fi.Mo, '
		+ cast((select 'fi.' + ps.SectionAlias + ', ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
		+ 'pp.PlanPeriodID, fi.PlanParameter, ps.Periods) fi '
		+ 'ON fi.Mo = pfs.Mo AND '
		+ cast((select 'fi.' + ps.SectionAlias + ' = pfs.' + ps.SectionAlias + ' AND ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
		+ 'fi.PlanPeriodID = pfs.PlanPeriod AND fi.PlanParameter = pfs.PlanParameter AND fi.Periods = pfs.Periods
	'
		+ 'UPDATE pfs set pfs.PlanSum = pfs.PlanValue * CASE '
		+ cast((select 'WHEN pfs.Periods = ' + cast(ps.Periods as varchar(max)) + ' THEN tarif.' + ps.PeriodAlias + ' ' from FIES.T_PERIOD_SECTION ps for xml path('')) as varchar(max))
		+ 'END '
		+ 'FROM [FIES].[T_PLAN_FACT_STATISTICS] pfs '
		+ 'JOIN FIES.T_PLAN_PARAMETER pp on pp.PayParametrID = pfs.PlanParameter '
		+ 'JOIN (SELECT tci.Mo, '
		+ cast((select 'tci.' + ps.SectionAlias + ', ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
		+ 'tfvt.AgeGroup, '
		+ cast((select 'mv.' + ps.PeriodAlias + ', ' from FIES.T_PERIOD_SECTION ps for xml path('')) as varchar(max))
		+ 'tf.PlanPeriod '
		+ 'FROM FIES.T_TP_FIN_CLOSED_MONTH_TARIFS tarif JOIN FIES.T_TP_FIN_CLOSE_ITEM tci ON tci.TpFinCloseItemID = tarif.TpFinCloseItem JOIN FIES.T_TP_FIN_CLOSE tc ON tc.TpFinCloseID = tci.TpFinClose JOIN FIES.T_TP_FIN tf on tf.TpFinID = tc.TpFin JOIN FIES.T_MONTH_VALUES mv on mv.MonthValuesID = tarif.MonthValuesID LEFT JOIN FIES.T_TP_FIN_CLOSED_VALUE tfv on tfv.TpFinClosedValueID = tarif.TpFinClosedValue LEFT JOIN FIES.T_TP_FIN_ITEM_TARIF tfvt on tfvt.TpFinItemTarifID = tfv.TpFinItemTarif '
		+ 'WHERE tf.TpDate < @currentDate '
		+ 'AND NOT EXISTS(SELECT * FROM FIES.T_TP_FIN_CLOSED_MONTH_TARIFS tarif1 JOIN FIES.T_TP_FIN_CLOSE_ITEM tci1 ON tci1.TpFinCloseItemID = tarif1.TpFinCloseItem JOIN FIES.T_TP_FIN_CLOSE tc1 ON tc1.TpFinCloseID = tci.TpFinClose JOIN FIES.T_TP_FIN tf1 on tf1.TpFinID = tc1.TpFin LEFT JOIN FIES.T_TP_FIN_CLOSED_VALUE tfv1 on tfv1.TpFinClosedValueID = tarif1.TpFinClosedValue LEFT JOIN FIES.T_TP_FIN_ITEM_TARIF tfvt1 on tfvt1.TpFinItemTarifID = tfv1.TpFinItemTarif '
			+ 'WHERE tci1.Mo = tci.Mo AND '
			+ cast((select 'IsNull(tci1.' + ps.SectionAlias + ', ISNULL(tci.' + ps.SectionAlias + ', @empty)) = ISNULL(tci.' + ps.SectionAlias + ', @empty) AND ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
			+ 'IsNull(tfvt1.AgeGroup, ISNULL(tfvt.AgeGroup, @empty)) = ISNULL(tfvt.AgeGroup, @empty) AND tf1.TpDate >= tf.TpDate AND tf1.CreateDate > tf.CreateDate AND tf1.TpDate < @currentDate)) tarif '
		+ 'ON tarif.Mo = pfs.Mo AND '
		+ cast((select 'IsNull(tarif.' + ps.SectionAlias + ', pfs.' + ps.SectionAlias + ') = pfs.' + ps.SectionAlias + ' AND ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
		+ 'IsNull(tarif.AgeGroup, ISNULL(pp.AgeGroup, @empty)) = ISNULL(pp.AgeGroup, @empty) AND tarif.PlanPeriod = pfs.PlanPeriod'

	exec sp_executesql @sql

END
GO