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()
	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], [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, SUM(pv.Value), 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 '
		+ 'WHERE pl.Approved = 1 AND pr.RequestTime < @currentDate '
		+ '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)'
		+ '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
	'
		+ 'UPDATE ps SET ps.FactValue = fi.Value, ps.FactSum = fi.ValueSum FROM [FIES].[T_PLAN_FACT_STATISTICS] ps 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, 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 '
		+ '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) fi '
		+ 'ON fi.Mo = ps.Mo AND '
		+ cast((select 'fi.' + ps.SectionAlias + ' = ps.' + ps.SectionAlias + ' AND ' from FIES.T_PLAN_SECTION ps for xml path('')) as varchar(max))
		+ 'fi.PlanPeriodID = ps.PlanPeriod AND fi.PlanParameter = ps.PlanParameter'

	exec sp_executesql @sql

END
GO