
ALTER VIEW [dbo].[V_ENTITY_ATTRIBUTES]
AS
SELECT 
    NEWID() AS [EntityAttributesID] ,
    t2.EntityID AS [EntityID],
    t2.EntityID*10000+900+t1.SectionLevel AS [Index],
    1 AS [AttributeType],
    t1.SectionAlias AS [Name],
    t1.Name AS  [Description],
    ISNULL(t3.EntName, t4.EntName) AS   [SystemType],
    CAST(NULL  AS int) AS [MaxLength],
    CAST(NULL  AS int) AS [Precision],
    CAST(NULL  AS int) AS [Scale],
    CAST(NULL  AS varchar(255)) AS   [DefaultFormat],
    CAST(0 as bit) AS  [IsCalculated],
    CAST(0 as bit) AS   [IsFlag] ,
    CASE WHEN t2.EntityID = 541 THEN CAST(1 as bit) ELSE CAST(0 as bit) END AS   [IsRequired],
    1 AS [MappingType],
    t1.SectionAlias AS   [MappingName],
    CAST(0 as bit) AS   [Save],
    CAST(NULL  AS varchar(255)) AS   [LinkAttributeName],
    CAST(NULL  AS varchar(1024)) AS   [ChoiceAttributeData],
    CAST(1 as bit) AS   [IsStored],
    CAST(NULL  AS varchar(max)) AS   [AttrLexem],
    CAST(NULL  AS varchar(max)) AS   [PathsAttributes],
    CAST(0  AS bit) AS   [IsCashed]

    FROM T_PLAN_SECTION t1
    JOIN T_ENTITIES t2 on t2.EntityID in (541, 546, 549, 563, 535, 536)
    LEFT JOIN T_ENTITIES t3 on t3.EntityID = t1.Entities
    LEFT JOIN T_ADDITIONAL_ENTITIES t4 on t4.EntityID = t1.Entities
    
    UNION ALL
    SELECT 
    NEWID() AS [EntityAttributesID] ,
    t2.EntityID AS [EntityID],
    t2.EntityID*10000+900+t1.[Priority] AS [Index],
    0 AS [AttributeType],
    t1.PeriodAlias AS [Name],
    t1.Name AS  [Description],
    'int' AS   [SystemType],
    CAST(NULL  AS int) AS [MaxLength],
    CAST(NULL  AS int) AS [Precision],
    CAST(NULL  AS int) AS [Scale],
    CAST(NULL  AS varchar(255)) AS   [DefaultFormat],
    CAST(0 as bit) AS  [IsCalculated],
    CAST(0 as bit) AS   [IsFlag] ,
    CAST(1 as bit) AS   [IsRequired],
    1 AS [MappingType],
    t1.PeriodAlias AS   [MappingName],
    CAST(0 as bit) AS   [Save],
    CAST(NULL  AS varchar(255)) AS   [LinkAttributeName],
    CAST(NULL  AS varchar(1024)) AS   [ChoiceAttributeData],
    CAST(1 as bit) AS   [IsStored],
    CAST(NULL  AS varchar(max)) AS   [AttrLexem],
    CAST(NULL  AS varchar(max)) AS   [PathsAttributes],
    CAST(0  AS bit) AS   [IsCashed]
    FROM T_PERIOD_SECTION t1
    JOIN T_ENTITIES t2 on t2.EntityID= 600
    where t1.PeriodSection is null

    UNION ALL

    SELECT 
    NEWID() AS [EntityAttributesID] ,
    t2.EntityID AS [EntityID],
    t2.EntityID*10000+920+t1.ParametrOrder AS [Index],
    1 AS [AttributeType],
    t1.[ParameterAlias] AS [Name],
    t1.Name AS  [Description],
    'PLAN_VALUE' AS   [SystemType],
    CAST(NULL  AS int) AS [MaxLength],
    CAST(NULL  AS int) AS [Precision],
    CAST(NULL  AS int) AS [Scale],
    CAST(NULL  AS varchar(255)) AS   [DefaultFormat],
    CAST(0 as bit) AS  [IsCalculated],
    CAST(0 as bit) AS   [IsFlag] ,
    CAST(0 as bit) AS   [IsRequired],
    4 AS [MappingType],
    CAST(NULL as varchar(3)) AS   [MappingName],
    CAST(0 as bit) AS   [Save],
    CAST(NULL  AS varchar(255)) AS   [LinkAttributeName],
    CAST(NULL  AS varchar(1024)) AS   [ChoiceAttributeData],
    CAST(1 as bit) AS   [IsStored],
    CAST('<lx:ChildCount xmlns:lx="lexem">
    <condition>
        <lx:Criterion type="=">
            <lx:Path OwnerQueryLevel="0">
                <lx:StepInt index="5440002" />
                <lx:StepInt index="5420001" />
            </lx:Path>
            <lx:ConstOperand type="Guid">'+ cast(t1.PayParametrID as varchar(40))+'</lx:ConstOperand>
        </lx:Criterion>
    </condition>
    <lx:Path OwnerQueryLevel="0">
        <lx:StepInt index="5410003" />
    </lx:Path>
    <lx:Maximum>
        <lx:Path OwnerQueryLevel="0">
        <lx:StepCurrent />
    </lx:Path>
    </lx:Maximum>
</lx:ChildCount>'  AS varchar(max)) AS   [AttrLexem],

    '{5410003}' AS   [PathsAttributes],
    CAST(0  AS bit) AS   [IsCashed]

    FROM [dbo].[T_PLAN_PARAMETER] t1
    JOIN T_ENTITIES t2 on t2.EntityID= 541
    

    UNION ALL

    SELECT 
    NEWID() AS [EntityAttributesID] ,
    t2.EntityID AS [EntityID],
    t2.EntityID*10000+1000+(t3.DisplayOrder*10+t1.ParametrOrder) AS [Index],
    1 AS [AttributeType],
    t3.TypeAlias +  t1.GroupAlias AS [Name],
    t1.Name + ' (' + t3.Name + ')' AS  [Description],
    'LIMIT_VALUE' AS   [SystemType],
    CAST(NULL  AS int) AS [MaxLength],
    CAST(NULL  AS int) AS [Precision],
    CAST(NULL  AS int) AS [Scale],
    CAST(NULL  AS varchar(255)) AS   [DefaultFormat],
    CAST(0 as bit) AS  [IsCalculated],
    CAST(0 as bit) AS   [IsFlag] ,
    CAST(0 as bit) AS   [IsRequired],
    4 AS [MappingType],
    CAST(NULL as varchar(3)) AS   [MappingName],
    CAST(0 as bit) AS   [Save],
    CAST(NULL  AS varchar(255)) AS   [LinkAttributeName],
    CAST(NULL  AS varchar(1024)) AS   [ChoiceAttributeData],
    CAST(1 as bit) AS   [IsStored],
    CAST('<lx:QueryValue entity="LIMIT_VALUE" top="1" xmlns:lx="lexem">
    <condition>
        <lx:Criteria type="And">
            <lx:Criterion type="=">
                <lx:Path OwnerQueryLevel="0">
                    <lx:StepInt index="5470002" />
                    <lx:StepInt index="5460001" />
                </lx:Path>
                <lx:Path OwnerQueryLevel="1">
                    <lx:StepInt index="5460001" />
                </lx:Path>
            </lx:Criterion>
            <lx:Criterion type="=">
                <lx:Path OwnerQueryLevel="0">
                    <lx:StepInt index="5470006" />
                    <lx:StepInt index="5520001" />
                </lx:Path>
                <lx:ConstOperand type="Guid">'+ cast(t1.ParameterGroupID as varchar(36)) +'</lx:ConstOperand>
            </lx:Criterion>
            <lx:Criterion type="=">
                <lx:Path OwnerQueryLevel="0">
                    <lx:StepInt index="5470005" />
                    <lx:StepInt index="5510001" />
                </lx:Path>
                <lx:ConstOperand type="Guid">'+ cast(t3.PlanLimitTypeID as varchar(36)) +'</lx:ConstOperand>
            </lx:Criterion>
        </lx:Criteria>
    </condition>
    <value>
        <lx:Path OwnerQueryLevel="0">
            <lx:StepInt index="5470001" />
        </lx:Path>
    </value>
</lx:QueryValue>'  AS varchar(max)) AS   [AttrLexem],
    CAST(NULL AS VARCHAR(MAX)) AS   [PathsAttributes],
    CAST(0  AS bit) AS   [IsCashed]
    from T_PARAMETER_GROUP t1, T_ENTITIES t2, T_PLAN_LIMIT_TYPE t3
    where t2.EntityID = 546

    UNION ALL

    -- ССЫЛКА НА ДЕЙСТВУЮЩУЮ ЗАПИСЬ ЗАЯВКИ

    SELECT 
    NEWID() AS [EntityAttributesID] ,
    t2.EntityID AS [EntityID],
    t2.EntityID*10000+888 AS [Index],
    1 AS [AttributeType],
    'ActualPlanItem' AS [Name],
    'Действующая запись плана' AS  [Description],
    t2.EntName AS   [SystemType],
    CAST(NULL  AS int) AS [MaxLength],
    CAST(NULL  AS int) AS [Precision],
    CAST(NULL  AS int) AS [Scale],
    CAST(NULL  AS varchar(255)) AS   [DefaultFormat],
    CAST(0 as bit) AS  [IsCalculated],
    CAST(0 as bit) AS   [IsFlag] ,
    CAST(0 as bit) AS   [IsRequired],
    4 AS [MappingType],
    CAST(NULL  AS varchar(255)) AS   [MappingName],
    CAST(0 as bit) AS   [Save],
    CAST(NULL  AS varchar(255)) AS   [LinkAttributeName],
    CAST(NULL  AS varchar(1024)) AS   [ChoiceAttributeData],
    CAST(1 as bit) AS   [IsStored],
    '<lx:QueryValue entity="PLAN_ITEM" top="1"  xmlns:lx="lexem">
  <condition>
    <lx:Criteria type="And">
      <lx:Criterion type="=">
        <lx:Path OwnerQueryLevel="0">
          <lx:StepInt index="5410002" />
          <lx:StepInt index="5390006" />
        </lx:Path>
        <lx:Path OwnerQueryLevel="1">
          <lx:StepInt index="5410002" />
          <lx:StepInt index="5390006" />
        </lx:Path>
      </lx:Criterion>
      <lx:Criterion type="=">
        <lx:Path OwnerQueryLevel="0">
          <lx:StepInt index="5410002" />
          <lx:StepInt index="5390004" />
        </lx:Path>
        <lx:Path OwnerQueryLevel="1">
          <lx:StepInt index="5410002" />
          <lx:StepInt index="5390004" />
        </lx:Path>
      </lx:Criterion>' +
    (
            SELECT dbo.Joint('<lx:Criterion type="=">
                <lx:Path OwnerQueryLevel="0">
                  <lx:StepInt index="'+CAST(t2.EntityID*10000+900+t1.SectionLevel AS VARCHAR(20))+'" />
                </lx:Path>
                <lx:Path OwnerQueryLevel="1">
                  <lx:StepInt index="'+CAST(t2.EntityID*10000+900+t1.SectionLevel AS VARCHAR(20))+'" />
                </lx:Path>
              </lx:Criterion>', '')
            FROM T_PLAN_SECTION t1
            JOIN T_ENTITIES t2 on t2.EntityID= 541
            LEFT JOIN T_ENTITIES t3 on t3.EntityID = t1.Entities
            LEFT JOIN T_ADDITIONAL_ENTITIES t4 on t4.EntityID = t1.Entities
    )
    + '<lx:Criterion type="=">
        <lx:Path OwnerQueryLevel="0">
          <lx:StepInt index="5410002" />
          <lx:StepAs as="PLAN_REQUEST" />
          <lx:StepInt index="5400002" />
        </lx:Path>
        <lx:ChoiceOperand EntityIndex="540" AttributeIndex="5400002" PrimaryKey="5" />
      </lx:Criterion>
      <lx:Criterion type="=">
        <lx:Path OwnerQueryLevel="0">
          <lx:StepInt index="5410002" />
          <lx:StepAs as="PLAN_REQUEST" />
          <lx:StepInt index="5400007" />
        </lx:Path>
        <lx:ChoiceOperand EntityIndex="540" AttributeIndex="5400007" PrimaryKey="5" />
      </lx:Criterion>
    </lx:Criteria>
  </condition>
  <orderBy>
    <lx:Path OwnerQueryLevel="0">
      <lx:StepInt index="5410002" />
      <lx:StepAs as="PLAN_REQUEST" />
      <lx:StepInt index="5400006" />
    </lx:Path>
    <desc>True</desc>
  </orderBy>
  <value>
    <lx:Path OwnerQueryLevel="0">
      <lx:StepInt index="5410001" />
    </lx:Path>
  </value>
</lx:QueryValue>' AS   [AttrLexem],
    CAST(NULL  AS varchar(max)) AS   [PathsAttributes],
    CAST(0  AS bit) AS   [IsCashed]
    FROM T_ENTITIES t2 
    where EntityID = 541

    UNION ALL

    -- ССЫЛКА НА ПРЕДЫДУЩУЮ ЗАПИСЬ ЗАЯВКИ

    SELECT 
    NEWID() AS [EntityAttributesID] ,
    t2.EntityID AS [EntityID],
    t2.EntityID*10000+889 AS [Index],
    1 AS [AttributeType],
    'PrevPlanItem' AS [Name],
    'Предыдущая запись плана' AS  [Description],
    t2.EntName AS   [SystemType],
    CAST(NULL  AS int) AS [MaxLength],
    CAST(NULL  AS int) AS [Precision],
    CAST(NULL  AS int) AS [Scale],
    CAST(NULL  AS varchar(255)) AS   [DefaultFormat],
    CAST(0 as bit) AS  [IsCalculated],
    CAST(0 as bit) AS   [IsFlag] ,
    CAST(0 as bit) AS   [IsRequired],
    4 AS [MappingType],
    CAST(NULL  AS varchar(255)) AS   [MappingName],
    CAST(0 as bit) AS   [Save],
    CAST(NULL  AS varchar(255)) AS   [LinkAttributeName],
    CAST(NULL  AS varchar(1024)) AS   [ChoiceAttributeData],
    CAST(1 as bit) AS   [IsStored],
    '<lx:QueryValue entity="PLAN_ITEM" top="1"  xmlns:lx="lexem">
  <condition>
    <lx:Criteria type="And">
      <lx:Criterion type="&lt;=">
        <lx:Path OwnerQueryLevel="0">
         <lx:StepInt index="5410002" />
         <lx:StepInt index="5390006" />
         <lx:StepInt index="5500002" />
        </lx:Path>
        <lx:Path OwnerQueryLevel="1">
          <lx:StepInt index="5410002" />
          <lx:StepInt index="5390006" />
          <lx:StepInt index="5500002" />
        </lx:Path>
      </lx:Criterion>
      <lx:Criterion type="=">
        <lx:Path OwnerQueryLevel="0">
          <lx:StepInt index="5410002" />
          <lx:StepInt index="5390004" />
        </lx:Path>
        <lx:Path OwnerQueryLevel="1">
          <lx:StepInt index="5410002" />
          <lx:StepInt index="5390004" />
        </lx:Path>
      </lx:Criterion>' +
    (
            SELECT dbo.Joint('<lx:Criterion type="=">
                <lx:Path OwnerQueryLevel="0">
                  <lx:StepInt index="'+CAST(t2.EntityID*10000+900+t1.SectionLevel AS VARCHAR(20))+'" />
                </lx:Path>
                <lx:Path OwnerQueryLevel="1">
                  <lx:StepInt index="'+CAST(t2.EntityID*10000+900+t1.SectionLevel AS VARCHAR(20))+'" />
                </lx:Path>
              </lx:Criterion>', '')
            FROM T_PLAN_SECTION t1
            JOIN T_ENTITIES t2 on t2.EntityID= 541
            LEFT JOIN T_ENTITIES t3 on t3.EntityID = t1.Entities
            LEFT JOIN T_ADDITIONAL_ENTITIES t4 on t4.EntityID = t1.Entities
    )
    + '<lx:Criterion type="=">
        <lx:Path OwnerQueryLevel="0">
          <lx:StepInt index="5410002" />
          <lx:StepAs as="PLAN_REQUEST" />
          <lx:StepInt index="5400002" />
        </lx:Path>
        <lx:ChoiceOperand EntityIndex="540" AttributeIndex="5400002" PrimaryKey="5" />
      </lx:Criterion>
      <lx:Criterion type="=">
        <lx:Path OwnerQueryLevel="0">
          <lx:StepInt index="5410002" />
          <lx:StepAs as="PLAN_REQUEST" />
          <lx:StepInt index="5400007" />
        </lx:Path>
        <lx:ChoiceOperand EntityIndex="540" AttributeIndex="5400007" PrimaryKey="5" />
      </lx:Criterion>
      <lx:Criterion type="&lt;">
                <lx:Path OwnerQueryLevel="0">
                    <lx:StepInt index="5410002" />
                    <lx:StepAs as="PLAN_REQUEST" />
                    <lx:StepInt index="5400006" />
                </lx:Path>
                <lx:Path OwnerQueryLevel="1">
                    <lx:StepInt index="5410002" />
                    <lx:StepAs as="PLAN_REQUEST" />
                    <lx:StepInt index="5400006" />
                </lx:Path>
        </lx:Criterion>
    </lx:Criteria>
  </condition>
  <orderBy>
    <lx:Path OwnerQueryLevel="0">
      <lx:StepInt index="5410002" />
      <lx:StepAs as="PLAN_REQUEST" />
      <lx:StepInt index="5400006" />
    </lx:Path>
    <desc>True</desc>
  </orderBy>
  <value>
    <lx:Path OwnerQueryLevel="0">
      <lx:StepInt index="5410001" />
    </lx:Path>
  </value>
</lx:QueryValue>' AS   [AttrLexem],
    CAST(NULL  AS varchar(max)) AS   [PathsAttributes],
    CAST(0  AS bit) AS   [IsCashed]
    FROM T_ENTITIES t2 
    where EntityID = 541

GO


