WITH CTE AS
(
select 
--*
p.Mo, p.PlanPeriod, p.PlanID
from FIES.T_PLAN p 
join FIES.T_PLAN_REQUEST pr on p.PlanID = pr.PlanID
where --p.Mo = '501601' and 
exists (
		select *
		from FIES.T_PLAN_REQUEST_SMO_ITEM si
		left join FIES.T_PLAN_REQUEST_SMO s on s.PlanRequestSmoID = si.PlanRequestSmo
		where s.planrequest = p.PlanID)
group by p.Mo, p.PlanPeriod, p.PlanID
)
select
regexp_replace(cast(query_to_xml('
select *
from FIES.T_PLAN_REQUEST_SMO_ITEM si
join FIES.T_PLAN_REQUEST_SMO s on s.PlanRequestSmoID = si.PlanRequestSmo
left join FIES.T_MONTH_VALUES mv on mv.MonthValuesID = si.MonthValuesID
where s.PlanRequest = ''' || p.PlanID || ''' order by s.smo', false, true, '') as varchar)
,'<[^r].[^>]*>[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}<\/.[^>]*>', '', 'gm')
as x 
, p.PlanPeriod, p.Mo, coalesce(p.CreateDate, CAST(CAST(pp.Year AS VARCHAR(4)) || '0101' AS timestamp)) as CreateDate
,p.PlanID
into temp table tmp
--* --from cte
from FIES.T_PLAN p
join FIES.T_PLAN_REQUEST pr on p.PlanID = pr.PlanID
join FIES.T_PLAN_PERIOD pp on pp.PlanPeriodID = p.PlanPeriod
--join FIES.t_mo m on m.mcod = p.mo
join CTE c on c.planid = p.planid;
--order by p.CreateDate

  
select x
,(select t2.PlanID from tmp t2 where t2.Mo = c1.Mo and t2.PlanPeriod = c1.PlanPeriod and t2.CreateDate = c1.CreateDate limit 1) as PlanID
,c1.CreateDate
, cast('импортировано' as varchar(20)) as comment, c1.mo, c1.PlanPeriod
into temp table tmp2
from (
select mo, PlanPeriod, x, min(t1.CreateDate) as CreateDate
from tmp t1
group by mo, PlanPeriod, x
) c1;


INSERT INTO FIES.T_SMO_COEF_GROUP
          (SmoCoefGroupID
          ,DateStart
          ,Comment
          ,Mo
          ,PlanPeriod)
select PlanID, CreateDate, comment, mo, planperiod 
from 
(select *, row_number() over (partition by mo, PlanPeriod, PlanID order by mo, PlanPeriod, PlanID,  char_length(x) desc ) num
from
tmp2) t0
where t0.num = 1

--select * from fies.t_mo where mcod like '50%'

--select * from #tmp  where mo = '500101' and planperiod = 'E550625C-6D74-450F-B8F1-3176AB1A314B'
drop table tmp;
drop table tmp2;

DO 
$$ 
declare _type_ int;
		_periods text := '';
		_sections text := '';
		_sectionsNull text := '';
		_sectionsNullSet text := '';
		_sqlcommand text := '';
BEGIN

SELECT EntityID into _type_ FROM VCLIB.T_ENTITIES WHERE EntName = 'SMO_COEF_MONTH_VALUES';

_periods :=
(SELECT string_agg(PeriodAlias || ', ', '')
FROM FIES.T_PERIOD_SECTION
--ORDER BY Priority
);
--RAISE NOTICE '%', _periods;
_sections :=
(SELECT string_agg(SectionAlias || ', ', '')
FROM FIES.T_PLAN_SECTION
--ORDER BY SectionLevel
);
--RAISE NOTICE '%', _sections;
_sectionsNull :=
(SELECT string_agg(' AND ' || SectionAlias || ' IS NULL ', '')
FROM FIES.T_PLAN_SECTION
--ORDER BY SectionLevel
);
--RAISE NOTICE '%', _sectionsNull;
_sectionsNullSet :=
(SELECT string_agg('NULL, ', '')
FROM FIES.T_PLAN_SECTION
--ORDER BY SectionLevel
);
--RAISE NOTICE '%', _sectionsNullSet;

--select @sections
--RAISE NOTICE '%', CAST(_type_ as varchar(10));

_sqlcommand := 
'select gen_random_uuid() AS MonthValuesID, ' || CAST(_type_ as varchar(10)) || ' as type_,' ||
_periods || _sections ||
'  
s.Smo,cg.SmoCoefGroupID 
into temp table temp_items
from  FIES.T_PLAN_REQUEST_SMO_ITEM si
join FIES.T_MONTH_VALUES mv on mv.MonthValuesID = si.MonthValuesID
join FIES.T_PLAN_REQUEST_SMO s on s.PlanRequestSmoID = si.PlanRequestSmo
join FIES.T_SMO_COEF_GROUP cg on s.PlanRequest = cg.SmoCoefGroupID;

INSERT INTO FIES.T_MONTH_VALUES
           (MonthValuesID,
		   ' || _periods || '
            type_)
select MonthValuesID, '  || _periods || ' type_
from temp_items;

INSERT INTO FIES.T_SMO_COEF_MONTH_VALUES
           (MonthValuesID,
		   ' || _sections || '
            SmoCoefGroup
           ,Smo
           )
SELECT MonthValuesID, ' || _sections  || '
SmoCoefGroupID,Smo
from temp_items;

drop table temp_items;
';
EXECUTE _sqlcommand;
--RAISE NOTICE '%', _sqlcommand;

--exec sp_sqlexec @sqlcommand

_sqlcommand := 
'select gen_random_uuid() AS MonthValuesID, ' || CAST(_type_ as varchar(10)) || ' as type_,' ||
_periods ||
'  
vd.Smo,cg.SmoCoefGroupID 
into temp table temp_items
from FIES.T_SMO_COEF_GROUP cg
join FIES.T_SMO_COEF_MONTH_VALUES_DEFAULT vd on vd.Mo = cg.Mo
join FIES.T_MONTH_VALUES mv on mv.MonthValuesID = vd.MonthValuesID
where not exists (select * from FIES.T_SMO_COEF_MONTH_VALUES scmv
				  where scmv.SmoCoefGroup = cg.SmoCoefGroupID ' || _sectionsNull  || ' );


INSERT INTO FIES.T_MONTH_VALUES
           (MonthValuesID,
		   ' || _periods ||'
            type_)
select MonthValuesID, ' || _periods || ' type_
from temp_items;

INSERT INTO FIES.T_SMO_COEF_MONTH_VALUES
           (MonthValuesID,
		   ' || _sections || '
            SmoCoefGroup
           ,Smo
           )
SELECT MonthValuesID, ' || _sectionsNullSet || '
SmoCoefGroupID,Smo
from temp_items;

drop table temp_items;
';

--select @sqlcommand
EXECUTE _sqlcommand;
--RAISE NOTICE '%', _sqlcommand;

END;
$$;
