DROP VIEW if exists public.v_entity_attributes;
CREATE VIEW public.v_entity_attributes
AS SELECT gen_random_uuid() AS entityattributesid,
    t1.entityid,
    t1.entityid * 10000 + 900 AS index,
    1 AS attributetype,
    'QualityQuestionGroup'::character varying(255) AS name,
    'Группа вопросов-критериев качества'::character varying(255) AS description,
    'IES.QUALITY_QUESTION_GROUP'::character varying(255) AS systemtype,
    NULL::integer AS maxlength,
    NULL::integer AS "precision",
    NULL::integer AS scale,
    NULL::character varying(255) AS defaultformat,
    false AS iscalculated,
    false AS isflag,
    false AS isrequired,
    4 AS mappingtype,
    NULL::character varying(255) AS mappingname,
    false AS save,
    NULL::character varying(255) AS linkattributename,
    NULL::character varying(1024) AS choiceattributedata,
    true AS isstored,
    ( SELECT string_agg(
                CASE
                    WHEN (( SELECT count(*) AS count
                       FROM ies.t_quality_question_group)) = 0 THEN '<lx:ConstOperand isNull="1" xmlns:lx="lexem" />'::text
                    ELSE ('<lx:QueryValue entity="IES.QUALITY_QUESTION_GROUP" top="1"  xmlns:lx="lexem">
         <condition>
             <lx:Criterion type="=">
                 <lx:Path OwnerQueryLevel="0">
                     <lx:StepString name="QualityQuestionGroupID" />
                 </lx:Path>
                 <lx:Case>'::text || (((('<When xmlns:lx="lexem">'::text || replace(regexp_replace(qqg.conditionlexem::text, '<lx:BaseObjectOperand Entity="[\w\.]\\+" PrimaryKey="(\\?<guid>[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})"\s\\*\/>'::text, '<lx:ConstOperand type="Guid">${guid}</lx:ConstOperand>'::text), '<lx:Path OwnerQueryLevel="0">'::text, '<lx:Path OwnerQueryLevel="1">'::text)) || '</When>
                     <Then xmlns:lx="lexem">
                         <lx:ConstOperand type="Guid">'::text) || qqg.qualityquestiongroupid::character varying(36)::text) || '</lx:ConstOperand>
                     </Then>'::text)) || '            <Else>
                         <lx:ConstOperand isNull="1" />
                     </Else>
                 </lx:Case>
             </lx:Criterion>
         </condition>
         <value>
             <lx:Path OwnerQueryLevel="0">
                 <lx:StepString name="QualityQuestionGroupID" />
             </lx:Path>
         </value>
     </lx:QueryValue>'::text
                END, ''::text) AS string_agg
           FROM ies.t_quality_question_group qqg) AS attrlexem,
    NULL::character varying(8000) AS pathsattributes,
    false AS iscashed,
    NULL::character varying(255) AS mappingtable,
    NULL::character varying(255) AS mappingreftable,
    NULL::character varying(255) AS reftablesearchmode,
    NULL::character varying(255) AS reftablehashfunc,
    NULL::character varying(8000) AS parametersdata,
    NULL::character varying(8000) AS customsdata,
    NULL::character varying(8000) AS requiredattributes,
    NULL::character varying(8000) AS sortingattribute,
    NULL::character varying(8000) AS enumtype,
    false AS isidentity,
    false AS iscustom,
    true AS removeaction,
    false AS isprimarykey,
    '-1'::integer AS attributeid
   FROM vclib.t_entities t1
  WHERE t1.entname::text = 'MEDICAL_REQUEST_ITEM'::text
UNION ALL
 SELECT gen_random_uuid() AS entityattributesid,
    t2.entityid,
    t2.entityid * 10000 + 900 + tp1.sectionlevel AS index,
    1 AS attributetype,
    tp1.sectionalias AS name,
    tp1.name::character varying(255) AS description,
        CASE
            WHEN t3.entname IS NOT NULL THEN (t3.schema::text || '.'::text) || t3.entname::text
            ELSE NULL::text
        END::character varying(255) AS systemtype,
    NULL::integer AS maxlength,
    NULL::integer AS "precision",
    NULL::integer AS scale,
    NULL::character varying(255) AS defaultformat,
    false AS iscalculated,
    false AS isflag,
    true AS isrequired,
    1 AS mappingtype,
    tp1.sectionalias AS mappingname,
    false AS save,
    NULL::character varying(255) AS linkattributename,
    NULL::character varying(1024) AS choiceattributedata,
    true AS isstored,
    NULL::character varying(8000) AS attrlexem,
    NULL::character varying(8000) AS pathsattributes,
    false AS iscashed,
    NULL::character varying(255) AS mappingtable,
    NULL::character varying(255) AS mappingreftable,
    NULL::character varying(255) AS reftablesearchmode,
    NULL::character varying(255) AS reftablehashfunc,
    NULL::character varying(8000) AS parametersdata,
    NULL::character varying(8000) AS customsdata,
    NULL::character varying(8000) AS requiredattributes,
    NULL::character varying(8000) AS sortingattribute,
    NULL::character varying(8000) AS enumtype,
    false AS isidentity,
    false AS iscustom,
    true AS removeaction,
    false AS isprimarykey,
    (900 + (( SELECT max(t_plan_section.sectionlevel) + 1
           FROM ies.t_plan_section)) - tp1.sectionlevel) * '-1'::integer AS attributeid
   FROM ies.t_plan_section tp1
     JOIN vclib.t_entities t2 ON t2.entname::text = 'SCHET_ITEM_FAKT'::text OR t2.entname::text = 'ITEM_PLAN_MODIFICATION'::text
     LEFT JOIN vclib.t_entities t3 ON t3.entityid = tp1.entities
  WHERE tp1.datebegin <= now()::timestamp without time zone AND (tp1.dateend IS NULL OR tp1.dateend >= now()::timestamp without time zone)
UNION ALL
 SELECT gen_random_uuid() AS entityattributesid,
    t2.entityid,
    NULL::integer AS index,
    1 AS attributetype,
    t1.sectionalias AS name,
    t1.name::character varying(255) AS description,
        CASE
            WHEN t3.entname IS NOT NULL THEN (t3.schema::text || '.'::text) || t3.entname::text
            ELSE NULL::text
        END::character varying(255) AS systemtype,
    NULL::integer AS maxlength,
    NULL::integer AS "precision",
    NULL::integer AS scale,
    NULL::character varying(255) AS defaultformat,
    false AS iscalculated,
    false AS isflag,
    true AS isrequired,
    1 AS mappingtype,
    t1.sectionalias AS mappingname,
    false AS save,
    NULL::character varying(255) AS linkattributename,
    NULL::character varying(1024) AS choiceattributedata,
    true AS isstored,
    NULL::character varying(8000) AS attrlexem,
    NULL::character varying(8000) AS pathsattributes,
    false AS iscashed,
    NULL::character varying(255) AS mappingtable,
    NULL::character varying(255) AS mappingreftable,
    NULL::character varying(255) AS reftablesearchmode,
    NULL::character varying(255) AS reftablehashfunc,
    NULL::character varying(8000) AS parametersdata,
    NULL::character varying(8000) AS customsdata,
    NULL::character varying(8000) AS requiredattributes,
    NULL::character varying(8000) AS sortingattribute,
    NULL::character varying(8000) AS enumtype,
    false AS isidentity,
    false AS iscustom,
    true AS removeaction,
    false AS isprimarykey,
    (900 + (( SELECT max(t_plan_section.sectionlevel) + 1
           FROM ies.t_plan_section)) - t1.sectionlevel) * '-1'::integer AS attributeid
   FROM ies.t_plan_section t1
     JOIN vclib.t_entities t2 ON t2.entname::text = 'PLAN_CLASSIFICATION'::text
     LEFT JOIN vclib.t_entities t3 ON t3.entityid = t1.entities
  WHERE t1.classificationtype = 0 AND t1.datebegin <= now()::timestamp without time zone AND (t1.dateend IS NULL OR t1.dateend >= now()::timestamp without time zone)
UNION ALL
 -- Поля для сгруппированных значений параметров в записях плана и факта
 SELECT gen_random_uuid() AS entityattributesid,
    e.entityid,
    NULL::integer AS index,
    0 AS attributetype,
    cs.fieldname AS name,
    COALESCE(cs.fielddescription, cs.fieldname)::character varying(255) AS description,
    'float'::character varying(255) AS systemtype,
    NULL::integer AS maxlength,
    31 AS "precision",
    10 AS scale,
    NULL::character varying(255) AS defaultformat,
    false AS iscalculated,
    false AS isflag,
    true AS isrequired,
    1 AS mappingtype,
    cs.fieldname AS mappingname,
    false AS save,
    NULL::character varying(255) AS linkattributename,
    NULL::character varying(1024) AS choiceattributedata,
    true AS isstored,
    NULL::character varying(8000) AS attrlexem,
    NULL::character varying(8000) AS pathsattributes,
    false AS iscashed,
    e.mappingtable,
    NULL::character varying(255) AS mappingreftable,
    NULL::character varying(255) AS reftablesearchmode,
    NULL::character varying(255) AS reftablehashfunc,
    NULL::character varying(8000) AS parametersdata,
    '<?xml version="1.0" encoding="utf-16"?><Customs xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Custom Name="FieldName" Value="' || cs.FieldName || '" /><Custom Name="PlanFactFieldType" Value="1" /></Customs>'::character varying(8000) AS customsdata,
    NULL::character varying(8000) AS requiredattributes,
    NULL::character varying(8000) AS sortingattribute,
    NULL::character varying(8000) AS enumtype,
    false AS isidentity,
    false AS iscustom,
    true AS removeaction,
    false AS isprimarykey,
    (900 - cs.priority) * '-1'::integer AS attributeid
   FROM ies.t_classification_setting cs,
    ( SELECT e_1.entityid,
            pkea.mappingtable
           FROM vclib.t_entities e_1
             LEFT JOIN vclib.t_entities_attributes pkea ON pkea.entityid = e_1.entityid AND pkea.isprimarykey = true
          WHERE e_1.entname::text = ANY (ARRAY['ITEM_FACT'::character varying::text, 'ITEM_PLAN'::character varying::text])) e
  WHERE cs.fieldname IS NOT NULL
UNION ALL
 -- Поля для сгруппированных значений параметров по разделу в записях плана и факта
 SELECT gen_random_uuid() AS entityattributesid,
    e.entityid,
    NULL::integer AS index,
    0 AS attributetype,
    sections.SectionAlias || cs.FieldName AS name,
    COALESCE(cs.FieldDescription || ' (по разделу ' || sections.SectionAlias  || ')', sections.SectionAlias  || cs.FieldName) AS description,
    'float'::character varying(255) AS systemtype,
    NULL::integer AS maxlength,
    31 AS "precision",
    10 AS scale,
    NULL::character varying(255) AS defaultformat,
    false AS iscalculated,
    false AS isflag,
    true AS isrequired,
    1 AS mappingtype,
    sections.SectionAlias || cs.FieldName as mappingname,
    false AS save,
    NULL::character varying(255) AS linkattributename,
    NULL::character varying(1024) AS choiceattributedata,
    true AS isstored,
    NULL::character varying(8000) AS attrlexem,
    NULL::character varying(8000) AS pathsattributes,
    false AS iscashed,
    e.mappingtable,
    NULL::character varying(255) AS mappingreftable,
    NULL::character varying(255) AS reftablesearchmode,
    NULL::character varying(255) AS reftablehashfunc,
    NULL::character varying(8000) AS parametersdata,
    '<?xml version="1.0" encoding="utf-16"?><Customs xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Custom Name="FieldName" Value="' || cs.FieldName || '" /><Custom Name="PlanFactFieldType" Value="4" />'
        || case 
            when sections.PlanSectionID = sections.LastSection then '<Custom Name="NotDisplayedInFilter" Value="True" />'
            else ''
        end
        || case 
            when sections.PlanSectionID = sections.LastSection then '<Custom Name="NotDisplayedColumns" Value="True" />'
            else ''
        end
        || '</Customs>'::character varying(8000) AS customsdata,
    NULL::character varying(8000) AS requiredattributes,
    NULL::character varying(8000) AS sortingattribute,
    NULL::character varying(8000) AS enumtype,
    false AS isidentity,
    false AS iscustom,
    true AS removeaction,
    false AS isprimarykey,
    (1000 - cs.priority) * '-1'::integer AS attributeid
   FROM ies.t_classification_setting cs,
    ( SELECT e_1.entityid,
            pkea.mappingtable
           FROM vclib.t_entities e_1
             LEFT JOIN vclib.t_entities_attributes pkea ON pkea.entityid = e_1.entityid AND pkea.isprimarykey = true
          WHERE e_1.entname::text = ANY (ARRAY['ITEM_FACT'::character varying::text, 'ITEM_PLAN'::character varying::text])) e,
    ( select *, (
            select PlanSectionID
            from IES.T_PLAN_SECTION
            where datebegin <= now()::timestamp without time zone AND (dateend IS NULL OR dateend >= now()::timestamp without time zone) and classificationtype = 0
            order by SectionLevel desc
            limit 1
        ) as LastSection
        from IES.T_PLAN_SECTION ps
        where ps.datebegin <= now()::timestamp without time zone AND (ps.dateend IS NULL OR ps.dateend >= now()::timestamp without time zone) and ps.classificationtype = 0
    ) sections
  WHERE cs.fieldname IS NOT NULL and cs.CalculateSectionsSum = true
UNION ALL
 -- Поля остатка плана
 SELECT gen_random_uuid() AS entityattributesid,
    e.entityid,
    NULL::integer AS index,
    0 AS attributetype,
    ((cs.fieldname::text || 'Rest'::text))::character varying(255) AS name,
    ((COALESCE(cs.fielddescription, cs.fieldname)::text || '(Остаток)'::text))::character varying(255) AS description,
    'float'::character varying(255) AS systemtype,
    NULL::integer AS maxlength,
    31 AS "precision",
    10 AS scale,
    NULL::character varying(255) AS defaultformat,
    false AS iscalculated,
    false AS isflag,
    true AS isrequired,
    1 AS mappingtype,
    ((cs.fieldname::text || 'Rest'::text))::character varying(255) AS mappingname,
    false AS save,
    NULL::character varying(255) AS linkattributename,
    NULL::character varying(1024) AS choiceattributedata,
    true AS isstored,
    NULL::character varying(8000) AS attrlexem,
    NULL::character varying(8000) AS pathsattributes,
    false AS iscashed,
    e.mappingtable,
    NULL::character varying(255) AS mappingreftable,
    NULL::character varying(255) AS reftablesearchmode,
    NULL::character varying(255) AS reftablehashfunc,
    NULL::character varying(8000) AS parametersdata,
    '<?xml version="1.0" encoding="utf-16"?><Customs xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Custom Name="FieldName" Value="' || cs.FieldName || '" /><Custom Name="PlanFactFieldType" Value="2" /></Customs>'::character varying(8000) AS customsdata,
    NULL::character varying(8000) AS requiredattributes,
    NULL::character varying(8000) AS sortingattribute,
    NULL::character varying(8000) AS enumtype,
    false AS isidentity,
    false AS iscustom,
    true AS removeaction,
    false AS isprimarykey,
    (900 - cs.priority) * '-1'::integer AS attributeid
   FROM ies.t_classification_setting cs,
    ( SELECT e_1.entityid,
            pkea.mappingtable
           FROM vclib.t_entities e_1
             LEFT JOIN vclib.t_entities_attributes pkea ON pkea.entityid = e_1.entityid AND pkea.isprimarykey = true
          WHERE e_1.entname::text = 'ITEM_PLAN'::text) e
  WHERE cs.fieldname IS NOT NULL
UNION ALL
 -- Поля остатка плана по разделу
 SELECT gen_random_uuid() AS entityattributesid,
    e.entityid,
    NULL::integer AS index,
    0 AS attributetype,
    ((sections.SectionAlias::text || cs.FieldName::text || 'Rest'::text))::character varying(255) AS Name,
    ((COALESCE(cs.FieldDescription, cs.FieldName)::text || ' (Остаток по разделу '::text || sections.SectionAlias::text || ')'::text))::character varying(255) AS Description,
    'float'::character varying(255) AS systemtype,
    NULL::integer AS maxlength,
    31 AS "precision",
    10 AS scale,
    NULL::character varying(255) AS defaultformat,
    false AS iscalculated,
    false AS isflag,
    true AS isrequired,
    1 AS mappingtype,
    ((sections.SectionAlias::text || cs.FieldName::text || 'Rest'::text))::character varying(255) as MappingName,
    false AS save,
    NULL::character varying(255) AS linkattributename,
    NULL::character varying(1024) AS choiceattributedata,
    true AS isstored,
    NULL::character varying(8000) AS attrlexem,
    NULL::character varying(8000) AS pathsattributes,
    false AS iscashed,
    e.mappingtable,
    NULL::character varying(255) AS mappingreftable,
    NULL::character varying(255) AS reftablesearchmode,
    NULL::character varying(255) AS reftablehashfunc,
    NULL::character varying(8000) AS parametersdata,
    '<?xml version="1.0" encoding="utf-16"?><Customs xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Custom Name="FieldName" Value="' || cs.FieldName || '" /><Custom Name="PlanFactFieldType" Value="6" />'
        || case 
            when sections.PlanSectionID = sections.LastSection then '<Custom Name="NotDisplayedInFilter" Value="True" />'
            else ''
        end
        || case 
            when sections.PlanSectionID = sections.LastSection then '<Custom Name="NotDisplayedColumns" Value="True" />'
            else ''
        end
        || '</Customs>'::character varying(8000) AS customsdata,
    NULL::character varying(8000) AS requiredattributes,
    NULL::character varying(8000) AS sortingattribute,
    NULL::character varying(8000) AS enumtype,
    false AS isidentity,
    false AS iscustom,
    true AS removeaction,
    false AS isprimarykey,
    (900 - cs.priority) * '-1'::integer AS attributeid
   FROM ies.t_classification_setting cs,
    ( SELECT e_1.entityid,
            pkea.mappingtable
           FROM vclib.t_entities e_1
             LEFT JOIN vclib.t_entities_attributes pkea ON pkea.entityid = e_1.entityid AND pkea.isprimarykey = true
          WHERE e_1.entname::text = 'ITEM_PLAN'::text) e,
    ( select *, (
            select PlanSectionID
            from IES.T_PLAN_SECTION
            where datebegin <= now()::timestamp without time zone AND (dateend IS NULL OR dateend >= now()::timestamp without time zone) and classificationtype = 0
            order by SectionLevel desc
            limit 1
        ) as LastSection
        from IES.T_PLAN_SECTION ps
        where ps.datebegin <= now()::timestamp without time zone AND (ps.dateend IS NULL OR ps.dateend >= now()::timestamp without time zone) and ps.classificationtype = 0
    ) sections
    where cs.FieldName is not null and cs.CalculateSectionsSum = true
UNION ALL
 SELECT gen_random_uuid() AS entityattributesid,
    540 AS entityid,
    5400024 AS index,
    4 AS attributetype,
    'SchetType'::character varying(255) AS name,
    '(Тип счёта)'::character varying(255) AS description,
    ''::character varying(255) AS systemtype,
    NULL::integer AS maxlength,
    NULL::integer AS "precision",
    NULL::integer AS scale,
    NULL::character varying(255) AS defaultformat,
    false AS iscalculated,
    false AS isflag,
    false AS isrequired,
    4 AS mappingtype,
    NULL::character varying(255) AS mappingname,
    true AS save,
    NULL::character varying(255) AS linkattributename,
    ((((('<?xml version="1.0" encoding="utf-16"?>
	<ChoiceItems xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
		<ChoiceItem Name="Счет ОМС" Index="693"></ChoiceItem>
		<ChoiceItem Name="Счет МТР от МО" Index="554"></ChoiceItem>
		<ChoiceItem Name="Исходящий счет по МТР" Index="539"></ChoiceItem>
		<ChoiceItem Name="Входящий счет по МТР" Index="538"></ChoiceItem> 
		<ChoiceItem Name="Исправленный счет МТР" Index="555"></ChoiceItem>
		<ChoiceItem Name="Временный счет(для расчета)" Index="562"></ChoiceItem>
		<ChoiceItem Name="Еженедельные диспансерные счета-реестры" Index="6666"></ChoiceItem>
		<ChoiceItem Name="Счёт-реестр ФМО" Index="65432"></ChoiceItem>
	    <ChoiceItem Name="Добавленный счет" Index="'::text || ((( SELECT t_entities.entityid
           FROM vclib.t_entities
          WHERE t_entities.entname::text = 'ADDED_SCHET'::text))::text)) || '"></ChoiceItem>
		<ChoiceItem Name="Счёт-реестр тестирования COVID-19" Index="'::text) || ((( SELECT t_entities.entityid
           FROM vclib.t_entities
          WHERE t_entities.entname::text = 'COVID_SCHET'::text))::text)) || '"></ChoiceItem>
	</ChoiceItems>'::text))::character varying(1024) AS choiceattributedata,
    true AS isstored,
    '<lx:Path OwnerQueryLevel="0" xmlns:lx="lexem">
    <lx:StepString name="ObjectProperties" />
    <lx:StepString name="ObjectTypeID" />
	</lx:Path>'::character varying(8000) AS attrlexem,
    NULL::character varying(8000) AS pathsattributes,
    false AS iscashed,
    NULL::character varying(255) AS mappingtable,
    NULL::character varying(255) AS mappingreftable,
    NULL::character varying(255) AS reftablesearchmode,
    NULL::character varying(255) AS reftablehashfunc,
    NULL::character varying(8000) AS parametersdata,
    NULL::character varying(8000) AS customsdata,
    NULL::character varying(8000) AS requiredattributes,
    NULL::character varying(8000) AS sortingattribute,
    NULL::character varying(8000) AS enumtype,
    false AS isidentity,
    false AS iscustom,
    true AS removeaction,
    false AS isprimarykey,
    '-1'::integer AS attributeid
UNION ALL
 SELECT gen_random_uuid() AS entityattributesid,
    e.entityid,
    NULL::integer AS index,
    0 AS attributetype,
    'IsDelete'::character varying(255) AS name,
    'Удалён'::character varying(255) AS description,
    'bool'::character varying(255) AS systemtype,
    NULL::integer AS maxlength,
    NULL::integer AS "precision",
    NULL::integer AS scale,
    NULL::character varying(255) AS defaultformat,
    false AS iscalculated,
    false AS isflag,
    true AS isrequired,
    1 AS mappingtype,
    'IsDelete'::character varying(255) AS mappingname,
    false AS save,
    NULL::character varying(255) AS linkattributename,
    NULL::character varying(1024) AS choiceattributedata,
    true AS isstored,
    NULL::character varying(8000) AS attrlexem,
    NULL::character varying(8000) AS pathsattributes,
    false AS iscashed,
    NULL::character varying(255) AS mappingtable,
    NULL::character varying(255) AS mappingreftable,
    NULL::character varying(255) AS reftablesearchmode,
    NULL::character varying(255) AS reftablehashfunc,
    NULL::character varying(8000) AS parametersdata,
    '<Customs><Custom Name="IsDeleteAttribute" Value="True" /></Customs>'::character varying(8000) AS customsdata,
    NULL::character varying(8000) AS requiredattributes,
    NULL::character varying(8000) AS sortingattribute,
    NULL::character varying(8000) AS enumtype,
    false AS isidentity,
    false AS iscustom,
    true AS removeaction,
    false AS isprimarykey,
    '-1'::integer AS attributeid
   FROM ies.t_postponed_delete_entity pde
     JOIN vclib.t_entities e ON ((e.schema::text || '.'::text) || e.entname::text) = pde.name::text
     JOIN vclib.t_entities_attributes pkea ON pkea.isprimarykey = true AND (pkea.entityid IN ( SELECT eh.baseentity
           FROM v_entity_hierarchy eh
          WHERE eh.entityid = e.entityid))
  WHERE pde.isactive = true;