nom_price as (

  -- Цены номенклатур по прайсу
  select
      it::integer
    , date_begin::date date_begin
    , coalesce(date_end::timestamp, 'infinity'::timestamp)::date date_end
    , (coalesce(pr_cost, 0.0))::numeric(32,2) price
  from unnest('{1972,1996,1997}'::bigint[], '{"2018-07-26","2018-11-06","2018-11-06"}'::varchar[], '{"2019-09-19","2019-09-19","2019-09-19"}'::varchar[], '{100.00,180.00,360.00}'::numeric[]::numeric[]) as t(it, date_begin, date_end, pr_cost)

),
pids as (

  SELECT abs(1998::bigint) pid
  WHERE 1998::bigint is not null
  UNION ALL
  SELECT
    n."@Номенклатура" pid
  FROM pids p
    JOIN "Номенклатура" n ON n."Раздел" = p.pid
  WHERE  n."Раздел@" is not null
),
use_types as (


        select "@ТипНоменклатуры" tp from "ТипНоменклатуры" where "ВидУчета" = ANY('{0,1}'::bigint[])


),
certs_types as (
  select "@ТипНоменклатуры" tp from "ТипНоменклатуры"
  where "Категория" = 8
    and "@ТипНоменклатуры" = any(array(table use_types))
),
its as (
  with abs_params as (
    select abs(unnest('{1998}'::bigint[]))
  )
  select "@Номенклатура" it
  -- items
  from "Номенклатура"
  where "@Номенклатура" = any(array(table abs_params))
    and "Раздел@" is not true
    and "ТипНоменклатуры" = ANY( ARRAY( TABLE use_types ) )

  union
  select "@Номенклатура" it
  -- items_in_foldes
  from "Номенклатура"
  where "Раздел" = ANY(ARRAY(
      with recursive folders_expand as (
        select "@Номенклатура" it
        from "Номенклатура"
        where "@Номенклатура" = any(array(table abs_params))
          and "Раздел@" is not null
        union
        select "@Номенклатура" it
        from folders_expand f
          join "Номенклатура" n on n."Раздел" = f.it
        where n."Раздел@" is not null
      )
      select it from folders_expand
    ))
    and ("Раздел" IS NOT NULL OR "Раздел@" IS NOT NULL) -- for index
    and "Раздел@" is not true
    and "ТипНоменклатуры" = ANY( ARRAY( TABLE use_types ) )

),
nom_price_date as (

-- Цены не партионных номенклатур
-- подбор актуального прайса (выбор по конечной дате последнего прайса)
select
it
, price
from nom_price
where (it, date_begin) in (
select
  it
  , max(date_begin)
from nom_price
group by it
)

),
pid_its as (
  select n."@Номенклатура" it
  from "Номенклатура" n
  where n."Раздел" = ANY(ARRAY(select pid from pids))
    and n."Раздел@" is not true
    and "ТипНоменклатуры" = ANY( ARRAY( TABLE use_types ) )

),
its_pid_its as (

  select it from (
      select it
      from its
      order by it
   ) t
   join (
      select it
      from pid_its
      order by it
   ) t1 using(it)

),
its_certs as (
  select "@Номенклатура" it, p.pid
  -- идентификаторы гашений сертификатов, с заменеными разделами на разделы родителей
  from "Номенклатура"
  join (
    with recursive folders_expand2 as (
      select "@Номенклатура" it, "Раздел" pid
      from "Номенклатура"
      where "ТипНоменклатуры" = ANY( ARRAY( TABLE certs_types ) )
        and "@Номенклатура" = ANY(ARRAY(SELECT it FROM its_pid_its))
        and not(select count(1) = 1 from its)
      union
      select "@Номенклатура" it, "Раздел" pid
      from folders_expand2 f
        join "Номенклатура" n on n."Раздел" = f.it
    )
    select it, pid from folders_expand2
  ) p on "Раздел" = p.it
  where "Раздел@" is null

),
it_prep as (
    select "@Номенклатура" it
    from "Номенклатура" n
    where n."Раздел@" is not true
        and n."@Номенклатура" = ANY(ARRAY(SELECT it FROM its_pid_its))

    union
    select "@Номенклатура" it
    from "Номенклатура" n
    where n."@Номенклатура" = ANY(ARRAY(SELECT it FROM its_certs))

),
itwh as (
-- находим все складские карточки, которые надо исследовать
with
nom_type_cte as (
-- типы номенклатур для исследования
select
  "@ТипНоменклатуры"
  , "ВидУчета"
  , "ПодвидУчета"
  , case when t."ПодвидУчета" in (1,2,3,4,5) then 'day' else 'day'::varchar end as quantum
, case when

     COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date = "CostRecalcWorker.GetQuantBeginDate"(

      "CostRecalcWorker.GetQuantByDate"(
        COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date
        , case when t."ПодвидУчета" in (1,2,3,4,5) then 'day' else 'day'::varchar end
      )
      , case when t."ПодвидУчета" in (1,2,3,4,5) then 'day' else 'day'::varchar end
     )
    then
     "CostRecalcWorker.GetQuantByDate"(
       COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date
       , case when t."ПодвидУчета" in (1,2,3,4,5) then 'day' else 'day'::varchar end
     )
    else
     "CostRecalcWorker.GetQuantByDate"(
       COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date - interval '1 msec'
      , case when t."ПодвидУчета" in (1,2,3,4,5) then 'day' else 'day'::varchar end
     )
    end as quant_date
from "ТипНоменклатуры" t
where "ВидУчета" IN (0, 1)

order by "@ТипНоменклатуры"
)
select
 wh."Номенклатура" it
, wh."НоменклатураРаздел" pid
, t."ВидУчета" tiv
, wh."Склад" wh
, wh."Остаток" qty
, wh."СуммаСебест" sumit
, wh."СуммаСебестБезНДС" sumxt
, t.quantum
, t.quant_date
from "СкладскаяКартотека" wh
left join nom_type_cte t on
  t."@ТипНоменклатуры" = wh."ТипНоменклатуры"

where true
and wh."Номенклатура" = ANY(ARRAY(table it_prep))

and wh."ТипНоменклатуры" is not null
and wh."ТипНоменклатуры" = any(array(select "@ТипНоменклатуры" from nom_type_cte order by "@ТипНоменклатуры"))
),
itwh_wca as (
-- Обогащаем данные из СкладскаяКартотека данными по актуальности рассчета
select distinct on (it, wh)
itwh.*
, case
    when cq.id is not null then null
    when cc.dt is not null then cc.dt
    else 'infinity'::timestamp
  end::timestamp wca
, cq.it is not null as in_queue
from
itwh itwh
left join cost_queue cq on (cq.it, cq.wh) = (itwh.it, itwh.wh)
left join cost_card cc on (cc.it, cc.wh) = (itwh.it, itwh.wh) and cq.id is null
where
cq.it is not null or cc.it is not null --Карточки по которым были движения

),
itwhf as (
select
  w.it
, w.pid
, w.tiv
, w.wh
, wca
, qty
, sumit
, sumxt
, quant_date
, quantum
, in_queue

from
itwh_wca w
where true
),
inv_nomdoc as (

-- для каждой складской карточки находим
-- * ближайшую "сверху" от интервала инвентаризацию
select
  itwhf.*
  -- inv - массив с информацией по инвентаризации (ДатаВремя, Количество, СуммаСебест, СуммаСебестБезНДС, АктИнвентаризации.ТипПроведения, Ид)
, (
    coalesce((
          select ARRAY[
            CASE
                WHEN tp = 1 THEN date_trunc('day',dt)
                WHEN tp = 2 THEN date_trunc('day',dt) + interval '1 day' - interval '1 msec'
                ELSE dt
            END,
            not_crctd.qty,
            not_crctd.sumit,
            not_crctd.sumxt,
            not_crctd.tp,
            not_crctd.nd,
            true
            ]::text[]
          from (
                select
                     "ДатаВремя" dt
                    , CASE WHEN tiv = 0 THEN coalesce("Количество",0) ELSE 0 END::numeric(32,6) qty
                    , coalesce("СуммаСебест", 0) sumit
                    , coalesce("СуммаСебестБезНДС", 0) sumxt
                    , coalesce(
                    (select "ТипПроведения" from "АктИнвентаризации" where "@Документ" = dcit."СкладскойДокумент" limit 1)
                    , 0) tp
                    , "@НоменклатураДокумента" nd
                from
                  "НоменклатураДокумента" dcit
                where
                  ("Номенклатура", "Склад", "Тип", "Закрыто") = (itwhf.it, itwhf.wh, 2, TRUE) AND

                    "ДатаВремя" <= COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

                  and Флаги[4] is true
                order by
                  "ДатаВремя" DESC
                limit 1
          ) as not_crctd
   ), Array['-infinity', 0, 0, 0, 0, 0, true]::text[])
  ) invit
from
 itwhf itwhf
where wca is null

),
wc_all_calc as (

select
  itwhf.*
, coalesce((
    select
      ARRAY[
          dt::text
        , qty::text
        , sumit::text
        , sumxt::text
        , 0
        , 0
        , actual::text
      ]::text[]
    from cost_rest cr
    where (cr.it, cr.wh) = (itwhf.it, itwhf.wh) and

        dt <= COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

    order by dt desc
    limit 1
  ),
  Array['-infinity', 0, 0, 0, 0, 0, true]::text[]
  ) inv
from
itwhf
itwhf
where wca is not null

),
inv as (

select
itwhf.*
, coalesce(
   coalesce((
  select
    ARRAY[
    dt::text
    , qty::text
    , sumit::text
    , sumxt::text
    , 0
    , 0
    , actual::text
    ]::text[]
  from cost_rest cr2
  where (cr2.it, cr2.wh) = (itwhf.it, itwhf.wh)
  and actual is not null
  and dt <
  "CostRecalcWorker.GetQuantBeginDate"(
      "CostRecalcWorker.GetQuantByDate"(
            coalesce((
              select
                min(dt) dt
              from cost_rest cr
              where true
                and actual is null

                    and dt <= COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

                and dt > COALESCE (invit[1]::timestamp, '-infinity'::timestamp)::date
                and (cr.it, cr.wh) = (itwhf.it, itwhf.wh)
            ),

                COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

            )
        , quantum)
    , quantum)

    and dt > COALESCE (invit[1]::timestamp, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

  order by dt desc
  limit 1
    ), invit),
    Array['-infinity', 0, 0, 0, 0, 0, true]::text[]
  ) inv
from
inv_nomdoc
itwhf

),
inv_res as (

select
  it
, pid
, tiv
, wh
, wca
, qty
, sumit
, sumxt
, case when inv[1] = '-infinity' then invit else inv end as inv
, quant_date
, quantum
, in_queue

from inv
union all
select
  it
, pid
, tiv
, wh
, wca
, qty
, sumit
, sumxt
, case when inv[1]::timestamp is null then Array['-infinity', 0, 0, 0, 0, 0, true]::text[] else inv end as inv
, quant_date
, quantum
, in_queue

from wc_all_calc

),
extr_prt as (
with
_prtextr_ as (
select
    parts.prt
  , case
      when parts.prt is not null and parts.prt_nd is null then -3 -- missing
      when parts.prt is not null and parts.actual is false then -1 -- prt and error
      --для актов инвентаризации партия ссылается на саму операцию
      when parts.prt is null and parts.prt_type = 2 then null     -- OK
      when parts.prt is null and parts.actual is true then -4     -- avg
      when parts.prt is null and parts.actual is false then -1    -- error
    end rstbc_code
  , parts.dt
  , parts.it
  , parts.wh
  , parts.rstbqty
  , parts.rstbsumit
  , parts.rstbsumxt
  , inv.quant_date
  , "CostRecalcWorker.GetQuantByDate"(COALESCE (inv.inv[1]::timestamp, '-infinity'::timestamp), inv.quantum)::bigint quant_begin
  , inv[7]::boolean inv_code
  , false is_nd
  , inv[1]::timestamp as wca -- дата актуальности складской карточки
  , in_queue
from inv_res inv
  join lateral (
    select coalesce("CostRecalcWorker.GetQuantByDate"(COALESCE (inv[1]::timestamp, '-infinity'::timestamp), quantum), 0) inv_quant
  ) inv_q on true
  join lateral (
      with agg_prt as (
      --агрегируем по prt, если партия списалась в 0, то зануляем метрики
      select
         prt
        , it
        , wh
        , bool_and(case when cr.id is NULL then cr.actual else true end) actual
        , sum(case when id is null then qty else 0 end)::double precision rstbqty
        , sum(case when id is null then cr.sumit else 0 end)::numeric(32,2) rstbsumit
        , sum(case when id is null then cr.sumxt else 0 end)::numeric(32,2) rstbsumxt
      from cost_result cr
        where (cr.it, cr.wh, cr.quant) = (inv.it, inv.wh, inv_q.inv_quant)
          and case when
            cr.manual is null
            and cr.actual is not null -- Условие для исключения списаний по точности учета, в остаток они не должны включаться
          then true
          else false
          end
      group by prt, it, wh
    )
        select
          prt
          , nd."@НоменклатураДокумента" prt_nd
          , nd."Тип" prt_type
          , nd."ДатаВремя" dt
          , it
          , wh
          , actual
          , case when inv.tiv <> 1 then rstbqty else 0 end::double precision rstbqty
          , rstbsumit
          , rstbsumxt
      from agg_prt
      left join "НоменклатураДокумента" nd on nd."@НоменклатураДокумента" = prt
      where TRUE

        and case
             when prt is NULL and actual is TRUE then FALSE /* исключаем нулевой остаток "успешно" */
             when actual is TRUE AND (rstbsumit, rstbsumxt, rstbqty) = (0, 0, 0)

               then FALSE /* выключаем если НЕ нужны 0 партии */

             else TRUE
           end
  ) parts on true
order by parts.prt
),
_cost_nd_prt_ as (
select
    raw_prt.prt
  , -2::int as rstbc_code -- wait - документы на расчете, подкраска синим
  , raw_prt.dt
  , raw_prt.it
  , raw_prt.wh
  , raw_prt.rstbqty
  , raw_prt.rstbsumit
  , raw_prt.rstbsumxt
  , raw_prt.quant_date
  , "CostRecalcWorker.GetQuantByDate"(COALESCE(raw_prt.inv_dt, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec', raw_prt.quantum)::bigint as quant_begin
  , raw_prt.inv_code
  , true as is_nd
  , raw_prt.inv_dt as wca -- дата актуальности складской карточки
  , in_queue
from (
  select
      nd.prt
    , nd.it
    , nd.wh
    , nd.dt
    , sum(rstbqty) rstbqty
    , sum(rstbsumit) rstbsumit
    , sum(rstbsumxt) rstbsumxt
    , bool_and(inv_code) inv_code
    , min(inv_dt) inv_dt
    , min(quantum) quantum
    , min(quant_date) quant_date
    , min(wca) wca
    , bool_or(in_queue) in_queue
  from (
    select
        case
          when "Тип" = 0 and "Количество" > 0 or "Тип" = 1 and "Количество" < 0 then "@НоменклатураДокумента"
          when cr.manual is true then cr.prt
          else 0
        end prt
      , case
          when cr.prt is not null and nd."@НоменклатураДокумента" is null then -3 -- missing
          --для актов инвентаризации партия ссылается на саму операцию
          when cr.prt is null and nd."Тип" = 2 then null -- OK
          when cr.id is null then -2 -- wait
          when cr.prt is null and cr.actual is true then -4 -- avg
          when cr.prt is null then -1 -- error
        end rstbc_code
      , "Номенклатура" it
      , "Склад" wh
      , case when inv.tiv <> 1 then (coalesce( cr.qty * cr.op, "Количество" * case when "Тип" = 1 then -1 else 1 end)) else 0 end rstbqty
      , coalesce( cr.sumit * cr.op, "СуммаСебест" * case when "Тип" = 1 then -1 else 1 end) rstbsumit
      , coalesce( cr.sumxt * cr.op, "СуммаСебестБезНДС" * case when "Тип" = 1 then -1 else 1 end) rstbsumxt
      , inv.inv[7]::boolean inv_code
      , inv.inv[1]::timestamp inv_dt
      , inv.quantum
      , inv.quant_date
      , inv.wca
      , nd."ДатаВремя" dt
      , in_queue
    from "НоменклатураДокумента" nd
      join inv_res inv on (inv.it, inv.wh) = (nd."Номенклатура", nd."Склад")
      left join cost_result cr
        on (cr.it, cr.wh) = (nd."Номенклатура", nd."Склад")
           and cr.prt is not null
           and cr.id = "@НоменклатураДокумента"
           and cr.manual = true and cr.manual is not null --<< for index cost_result-id-manual
           and cr.op = -1
    where -- В прогнозе подставляются расходы, поэтому нет необходимости отбирать на этом этапе
      nd."Закрыто"
      and nd."Тип" = ANY (ARRAY[0, 1])
      and (
        -- приходы
        nd."Тип" is not distinct from 0
        or
        -- возвраты от покупателей
        nd."Тип" is not distinct from 1
        and nd."Количество" < 0
        and nd."Флаги"[11] is true
        and not exists(
          select "Основание"
          from "СвязьНаименований" ln
          where
            ln."Следствие" = nd."@НоменклатураДокумента"
            and ln."Тип" in (9, 10, 11, 12)
          limit 1
        )
      )
      -- только поступления, продукты выпуска и приходы, образующие партии.
      and (nd."Раздел" is null or nd."Флаги"[17] is true) is true
      and "ДатаВремя" > COALESCE (inv[1]::timestamp, '-infinity'::timestamp)

        and "ДатаВремя" <= COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

  ) nd
  group by nd.it, nd.wh, nd.prt, nd.dt
) raw_prt
)
-- разворов массива партий в рекорд
select
  s.prt pid
, s.it
, s.wh
, s.rstbqty
, s.rstbsumit
, s.rstbsumxt
, case
    when is_nd then -2 -- Синий
    else
      case
        when s.prt <> 0 then rstbc_code -- Если партия подобрана, получаем подсветку из cost_result
        else -1 -- ошибка (красный)
      end
  end as rstbc_code
, s.prt
, s.wca
, null::text[] inv
, in_queue
, null::bigint quant_date
, null::text quantum
, null::bigint quant_begin

from (
select *
from _prtextr_
union
select *
from _cost_nd_prt_
where true
) s

),
rcpexp as (

-- вычисляем обороты "до" и "внутри" искомого периода
SELECT
  inv.*
, case when wca is not null then (
    case when wca < coalesce('2019-09-18'::date, 'infinity'::timestamp)::date + INTERVAL '1 day' - INTERVAL '1 msec'
      then "CostRecalcWorker.GetQuantByDate"(COALESCE(wca::timestamp, '-infinity'::timestamp)::date, inv.quantum)
      else
        coalesce(q.quant, 0)
      end
  )
  else (
    case when inv is null or inv[1]::timestamp = '-infinity' then 0
    else "CostRecalcWorker.GetQuantByDate"(coalesce(inv[1]::timestamp::timestamp, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec', inv.quantum) end
  ) end as quant_begin
, (
    SELECT
        ARRAY[
        -- rcpqty
        coalesce(sum(CASE WHEN "Тип" = 0 AND tiv = 0 THEN "Количество" END::numeric(32,6)), 0)
        -- rcpsumit
      , coalesce(sum(CASE WHEN "Тип" = 0 THEN "СуммаСебест" END), 0)
        -- rcpsumxt
      , coalesce(sum(CASE WHEN "Тип" = 0 THEN "СуммаСебестБезНДС" END), 0)
        -- rcpmdate
      , 0
        -- expqty
      , coalesce(sum(CASE WHEN "Тип" = 1 AND tiv = 0  THEN "Количество" END::numeric(32,6)), 0)
        -- expsumit
      , coalesce(sum(CASE WHEN "Тип" = 1 THEN "СуммаСебест" END), 0)
        -- expsumxt
      , coalesce(sum(CASE WHEN "Тип" = 1 THEN "СуммаСебестБезНДС" END), 0)
        -- expmdate
      , coalesce(min(actual_code), 1)
      ]::text[]
    FROM (
        select
            case
            when "Тип" not in (0, 1) then 0::double precision
            when "ДатаВремя" = inv[1]::timestamp and inv[5]::int <> 1 then 0::double precision
            when "ДатаВремя" = inv[1]::timestamp and inv[5]::int = 1 and "Раздел" is not distinct from inv[6]::int then 0::double precision
            else "Количество"
            end "Количество"
          , case
            when "Тип" not in (0, 1) then 0::numeric(32, 2)
            when "ДатаВремя" = inv[1]::timestamp and inv[5]::int <> 1 then 0::numeric(32, 2)
            when "ДатаВремя" = inv[1]::timestamp and inv[5]::int = 1 and "Раздел" is not distinct from inv[6]::int then 0::numeric(32, 2)
            else "СуммаСебест"
            end "СуммаСебест"
          , case
            when "Тип" not in (0, 1) then 0::numeric(32, 2)
            when "ДатаВремя" = inv[1]::timestamp and inv[5]::int <> 1 then 0::numeric(32, 2)
            when "ДатаВремя" = inv[1]::timestamp and inv[5]::int = 1 and "Раздел" is not distinct from inv[6]::int then 0::numeric(32, 2)
            else "СуммаСебестБезНДС"
            end "СуммаСебестБезНДС"
          , "Тип"
          , "ДатаВремя"
          , 0 actual_code
          , "@НоменклатураДокумента"
        from
          "НоменклатураДокумента" dcit
        WHERE
          ("Номенклатура", "Склад", "Закрыто") = (it, wh, TRUE) AND
          ("Тип" = ANY (ARRAY[0, 1])) AND
          "ДатаВремя" >= inv[1]::timestamp AND

            "ДатаВремя" <= COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

      ) dcitrec
  ) rcpexpb
FROM
  inv_res inv
LEFT JOIN LATERAL (
 select "CostRecalcWorker.GetQuantByDate"(COALESCE(rest.dt, '-infinity'::timestamp)::date, inv.quantum) quant
 from cost_rest rest
 where (rest.it, rest.wh) = (inv.it, inv.wh)
   and rest.dt <

     COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

 order by dt desc
 limit 1
) q on (wca is not null and wca >= coalesce('2019-09-18'::date, 'infinity'::timestamp)::date + INTERVAL '1 day' - INTERVAL '1 msec')

),
itwhext as (

-- "разворачиваем" остатки и обороты в нормальный вид
SELECT
  it
, wh
, rcpexp.pid

, (case when tiv <> 1 then (
  inv[2]::numeric(32,6)
  + rcpexpb[1]::numeric(32,6)
  - rcpexpb[5]::numeric(32,6)
) else 0 end )::double precision rstbqty
, (
  --rstbsumit
  inv[3]::numeric(32,2)
  + rcpexpb[2]::numeric(32,2)
  - rcpexpb[6]::numeric(32,2)
) rstbsumit
, (
  -- rstbsumxt
  inv[4]::numeric(32,2)
  + rcpexpb[3]::numeric(32,2)
  - rcpexpb[7]::numeric(32,2)
) rstbsumxt
-- следующие строки добавляют подкраску в отчете
, case when wca is not null then (
    (select
      (case actual when true then null when false then -1 else -2 end)
    from cost_rest cr
    where (cr.it, cr.wh) = (rcpexp.it, rcpexp.wh)
      and cr.quant <= rcpexp.quant_date
      and cr.quant >= coalesce(quant_begin, 0)
    order by cr.quant desc, dt desc
    limit 1
    )
  )
  else (
   least((case when coalesce(inv[7]::boolean, true) then null else -1 end),
    (
      select
        min(case actual when true then null when false then -1 else -2 end) nact
      from cost_rest cr2
      where quant <= rcpexp.quant_date
        and quant >= coalesce(quant_begin, 0)
        and (cr2.it, cr2.wh) = (rcpexp.it, rcpexp.wh)
    )
  )) end as rstbc_code
, inv[1]::timestamp wca
, inv
, in_queue
, quant_date
, quantum
, quant_begin
FROM
rcpexp

),
itwhnz as (
-- фильтруем полностью нулевые записи перед агрегацией
SELECT
  itwhext.*
FROM
extr_prt
itwhext
WHERE true

        and (rstbqty, rstbsumit, rstbsumxt) IS DISTINCT FROM (0, 0, 0)

    or rstbc_code is distinct from null

),
itaggwh as (

-- группировка до складской карточки
SELECT distinct

  coalesce(it, 0) it

, wh
, pid

    , case when rstbqty <> 0 then 0 else
       coalesce((select "СуммаСебест" / "Количество"
        from "НоменклатураДокумента"
        where ("Номенклатура", "Склад", "Закрыто") = (it, wh, True)
         and "Тип" IN (0, 1)
         and "ДатаВремя" < COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'
         and "Количество" <> 0
        order by "ДатаВремя" desc
        limit 1), 0)
      end "Себест"
    , case when rstbqty <> 0 then 0 else
       coalesce((select "СуммаСебестБезНДС" / "Количество"
        from "НоменклатураДокумента"
        where ("Номенклатура", "Склад", "Закрыто") = (it, wh, True)
         and "Тип" IN (0, 1)
         and "ДатаВремя" < COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'
         and "Количество" <> 0
        order by "ДатаВремя" desc
        limit 1), 0)
  end "СебестБезНДС"

    , prt

, (sum(rstbqty::numeric(32,6)) over w)::double precision rstbqty
, sum(rstbsumit) over w rstbsumit
, sum(rstbsumxt) over w rstbsumxt
, min(rstbc_code) over w rstbc_code

, max(wca) over w wca

, inv
, in_queue
, quant_date
, quantum
, quant_begin
FROM
 itwhnz
window w as (partition by

  it, wh, pid

)

),
pre_itagg_filter as (

-- применяем фильтрацию по отрицательным карточкам
SELECT itaggwh.*
FROM itaggwh
WHERE true
and (

    ( (itaggwh.rstbsumit, itaggwh.rstbsumxt, itaggwh.rstbqty) IS DISTINCT FROM (0,0,0) or rstbc_code is distinct from null )

)


),
itagg_filter as (
-- применяем прайс к складским карточкам
SELECT itaggwh.*
    , n."Раздел"
    , n."Раздел@"
    , n."НомНомер"
    , "Флаги"[6] as "ЖНВЛП"
    , t."ПодвидУчета"
    , t."Категория" as "ТипНоменклатуры.Категория"
    , case when n."Раздел@" is not TRUE then n."КодЕдиницаИзмерения" else null::text end as "КодЕдиницаИзмерения"
    , case when n."Раздел@" is not TRUE then ((ite."ЕИУ"[1])::json->'base'->>'abbr') else null::text end as "ЕдиницаИзмеренияНазвание"
    , case when n."Раздел@" is not TRUE then 0 else -4 end as "АгрегированныеМетрики"

FROM pre_itagg_filter itaggwh
left join "Номенклатура" n on n."@Номенклатура" = itaggwh.it
left join "НоменклатураРасш" ite on ite."@Номенклатура" = itaggwh.it

left join "ТипНоменклатуры" t on t."@ТипНоменклатуры" = n."ТипНоменклатуры"

),
all_orgs as (

select distinct "НашаОрганизация"
from "Склад"
where "@Лицо" = any(array(
  select distinct wh
  from itagg_filter
  order by wh
))

),
rst_prt_hand as (
-- вычисляем количества по ручным закреплениям партий
  select
      -- идентификатор партии
      pt.*
      , coalesce(
          (
            select sum(cr.qty)
            from
              cost_result cr
            where
              cr.prt = pt.pid
              and (cr.op = -1 and cr.manual) is True
              and (
                cr.ord is null
                and (
                  select

                    nd."ДатаВремя" <= COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

                    and nd."Флаги"[1] is distinct from true -- 'Удален'
                    and nd."Закрыто"
                  from "НоменклатураДокумента" nd
                  where "@НоменклатураДокумента" = cr.id
                  limit 1
                ) is true
              )
          )
        , 0
        )::double precision qty_hand
    from itagg_filter pt
),
expense_w_code as (

with itwh_code as (
-- Отберем все расходы с указанным кодом партии и без ручного установления партии
select wr.*
  , array(
    select array[exp_c."КодПартии"::text, exp_c."Количество"::text]::text
    from (
      select lj."КодПартии", nd."Количество"
      from "НоменклатураДокумента" nd
        join lateral (
          select ndr."КодПартии"
          from "НоменклатураДокументаРасш" ndr
          where
            ndr."@НоменклатураДокумента" = nd."@НоменклатураДокумента"
            and ndr."КодПартии" is not null
        ) lj on true
      where
        -- проведенные наименований по СК на интервале от последних расчитанных остатков до указанной даты
        (wr.it, wr.wh) = (nd."Номенклатура", nd."Склад")
        and nd."ДатаВремя" > coalesce(wca, '-infinity'::timestamp)::timestamp  -- Партии  от даты актуальности

          and nd."ДатаВремя" <= COALESCE ('2019-09-18'::date, '-infinity'::timestamp)::date + interval '1 day' - interval '1 msec'

        and nd."Закрыто"
        and "Тип" = ANY (ARRAY[0, 1, 2])
        and case when nd."Тип" = 1 and nd."Количество" > 0 then true else false end
        and not exists(
          select 1
          from cost_result cr
          where
            cr.id = nd."@НоменклатураДокумента"
            and cr.manual is true
          limit 1
        )
    ) exp_c
  )::text[] out_w_code
from
  (
    select distinct wr.it, wr.wh, wr.wca
    from itagg_filter wr
    where in_queue
  ) wr

),
-- TODO Не используется
exp_code AS (
-- Отберем все расходы с указанным кодом партии и без ручного установления партии
select distinct wr.*
  ,  itwh.out_w_code
from itagg_filter wr
  left join itwh_code itwh on (wr.it, wr.wh) = (itwh.it, itwh.wh)
),
un_exp_code as (
-- Разворот всех расходов по кодам патрий
select
    distinct prt_code[1]::text id_code
  , prt_code[2]::double precision qty
from (
  select unnest(out_w_code)::text[] prt_code
  from itwh_code
) t
),
group_un_exp_code as (
-- группировка суммарных расходов по кодам партий
select
    id_code
  , sum(qty) qty
from un_exp_code
group by id_code
),
prt_qty as (
-- Получение партий с указанием их кода и временеи, для определения приоритета для списания
select
    t.*
  , ndr."КодПартии"
  , nd."ДатаВремя"
from (
  -- сводим "отфильтрованные" остатки по партиям с ручными партиями,
  -- в промежутке между актуальностью остатка и временем построения отчета
  select
      it
    , wh
    , prt
    , sum(qty) qty
    , sum(sumit) sumit
    , sum(sumxt) sumxt
      -- Если с партии было списание, тогда партия находится в очереди на расчет
    , min(least(rstbc_code, case when abs(rstbqty - qty) > 0 then -2 else null end)) rstbc_code
  from (
    select
        it
      , wh
      , pid prt
      , rstbc_code
      , rstbqty
        -- остаток - "ручные" закрепления
      , coalesce(rstbqty - qty_hand, 0)::double precision qty
      , case
          when abs(rstbqty) > 1e-10 then coalesce(rstbsumit - ((rstbsumit/rstbqty)*qty_hand), 0)
          else 0
        end::numeric(32,2) as sumit
      , case
          when abs(rstbqty) > 1e-10 then coalesce(rstbsumxt - ((rstbsumxt/rstbqty)*qty_hand), 0)
          else 0
        end::numeric(32,2) as sumxt
    from rst_prt_hand rph
  ) q
  group by prt, it, wh
) t
left join "НоменклатураДокумента" nd on nd."@НоменклатураДокумента" = t.prt
left join "НоменклатураДокументаРасш" ndr on ndr."@НоменклатураДокумента" = nd."@НоменклатураДокумента"

        where qty > 0

),
prt_qty_agg as (
-- Агрегирование остатков по кодам партий, для упорядоченного списания
select
    prt_qty.*
  , coalesce(sum(qty) filter(where qty > 0) over w, 0) as total_sum
from prt_qty
window w as (partition by "КодПартии" order by "ДатаВремя", prt)
),
prt_qty_agg_w_exp as (
-- списание с партий расходов по кодам партий
select
    it
  , wh
  , prt
  , least(
      rstbc_code,
      case
        when abs(case when new_qty >= qty then qty else new_qty end - qty) > 0 then -2
        else null
      end
    )::int rstbc_code
  , qty
  , sumit
  , sumxt
  --новое количество = (кол-во - списание по коду партии) или кол-во без списания)
  , case when new_qty >= qty then
      qty -- списание не произошло
    else
      case when qty < 0 then
        qty -- Если по партии был отрицательный остаток, то  ее не списываем
      else
        case when new_qty > 0 then
          new_qty -- Частично списали по коду партии
        else 0 -- списали по партии в 0
        end
      end
    end as new_qty
from (
  select
      pqa.it
    , pqa.wh
    , pqa.prt
    , pqa.rstbc_code
    , pqa.qty
    , pqa.sumit
    , pqa.sumxt
    , total_sum - case when pqa.qty > 0 then coalesce(guec.qty, 0) else 0 end as new_qty
  from
    prt_qty_agg pqa
  left join  group_un_exp_code guec on pqa."КодПартии" = guec.id_code
) t
)
-- оставляем только партии с положительным остатком и рассчитываем пропорционально себестоимость
select
  it
, wh
, prt
, rstbc_code
, new_qty::numeric(32,6)::double precision as qty
, case
    when abs(qty) > 1e-10 then coalesce(((sumit/qty)*new_qty), 0)
    else 0
  end::numeric(32,2) as sumit
, case
    when abs(qty) > 1e-10 then coalesce(((sumxt/qty)*new_qty), 0)
    else 0
  end::numeric(32,2) as sumxt
from prt_qty_agg_w_exp
-- pid = 0 - Партия не подобрана
where true

        and new_qty > 0


),
main_orgs as (

WITH RECURSIVE T1 AS(
SELECT "@Лицо",
      "Раздел",
      "Раздел@" "Папка",
      1 as "УровеньВложенности",
      array[ "@Лицо" ]::int[] "Массив"
  FROM "Контрагент"
  WHERE  "@Лицо" = any(array(table all_orgs))
UNION -- удаляем дубликаты, без ALL
SELECT "Контрагент"."@Лицо",
      "Контрагент"."Раздел",
      "Контрагент"."Раздел@" "Папка",
      T1."УровеньВложенности" + 1 as "УровеньВложенности",
      T1."Массив" || array[ "Контрагент"."@Лицо" ]::int[]
  FROM T1 JOIN
       "Контрагент" ON T1."Раздел" = "Контрагент"."@Лицо" AND
       "Контрагент"."@Лицо" NOT IN ( SELECT( unnest( T1."Массив" ) ) )
)
select distinct main, sub
from (
  select "@Лицо" main, "Массив"[1] sub, "УровеньВложенности", max("УровеньВложенности") over w as "УровеньВложенностиМакс"
  from T1
  where
     ("Папка" IS NULL OR "Папка" = FALSE)
     and ("Раздел" is null or "Раздел" <> any(array(select "@Лицо" from T1)))
  window w as (partition by "Массив"[1])
) q
where "УровеньВложенности" = "УровеньВложенностиМакс"

),
not_prts as (

-- формирование метрик "Партия не подобрана"
  select
      wh
    , it
    , prt
    , rstbc_code_itwh as rstbc_code
    , qty
    , sumit
    , sumxt
  from(
    select
        itwh.wh
      , itwh.it
      , 0 as prt
      , min(itwh.rstbc_code) as rstbc_code_itwh
      , min(prt.rstbc_code) as rstbc_code_prt
      , sum(itwh.rstbqty - coalesce(prt.qty, 0)) qty
      , sum(itwh.rstbsumit - coalesce(prt.sumit, 0)) sumit
      , sum(itwh.rstbsumxt - coalesce(prt.sumxt, 0)) sumxt
    from itwhext itwh
    left join (
      select
          it
        , wh
        , min(coalesce(rstbc_code, 0)) as rstbc_code
        , sum(qty) as qty
        , sum(sumit) as sumit
        , sum(sumxt) as sumxt
      from expense_w_code


      where TRUE

      -- чтобы исключить записи входящие в диалог остатков по специальным отрицательным ключам
      -- Если документ сформировал партию, но просчитан с ошибкой или находится на расчете, выводим как документ
      -- подкрашиваем в зависимости от состояния документа и не агрегируем в партия не подобрана
         and prt is distinct from null -- wait or error or prt is not null

      -- Если есть фильтр по партии, то у нас не будет данных с неподобранной партией

      group by it, wh
    ) prt on (prt.it, prt.wh) = (itwh.it, itwh.wh)
    group by itwh.wh, itwh.it
  ) not_prt
  where true

    and (qty <> 0 or sumit <> 0.0 or sumxt <> 0.0
    -- Если в периоде нет проблемных партий
    -- но по складской карточке имеется документы с ошибкой (вскрытие с ошибкой)
    -- тогда необходимо вывести "Партия не подобрана" с нулевыми метриками
    or rstbc_code_itwh = -1 and rstbc_code_prt > -1)


),
rst_prt as (
-- получаем список партий с ручными списаниями и списаниями по коду партий
  select
      wh
    , it
    , prt       -- идентификатор партии (@НоменклатураДокумента)
    , qty rstbqty       -- количественный остаток
    , sumit rstbsumit    -- СуммаСебест
    , sumxt rstbsumxt    -- СуммаСебестБезНДС
    , rstbc_code
    , sumit СуммаЦен
    , sumxt СуммаЦенБезНДС
    , True as usecst
  from expense_w_code

  where TRUE

      -- чтобы исключить записи входящие в диалог остатков по специальным отрицательным ключам
      -- Если документ сформировал партию, но просчитан с ошибкой или находится на расчете, выводим как документ
      -- подкрашиваем в зависимости от состояния документа и не агрегируем в партия не подобрана
        and prt is distinct from null -- wait or error or prt is not null

  -- формирование метрик "Партия не подобрана"

                  union
                  select
                  wh
                  , it
                  , prt
                  , qty rstbqty       -- количественный остаток
                  , sumit rstbsumit    -- СуммаСебест
                  , sumxt rstbsumxt    -- СуммаСебестБезНДС
                  , rstbc_code
                  , sumit СуммаЦен
                  , sumxt СуммаЦенБезНДС
                  , True as usecst
              from not_prts

),
page_data_doc_parts as (

        -- ветка с партиями
SELECT
    itf.it

        , null::int pid

    , w."Раздел" wh
    , mo.main as o
    , rstbsumit "СуммаСебест"
    , rstbsumxt "СуммаСебестБезНДС"
    , rstbqty "Количество"
    , rstbc_code
    , -4 "АгрегированныеМетрики"
    , coalesce(nd."СкладскойДокумент", 0) dp
    , (case when tn."Признаки"[7] is TRUE then COALESCE(nd."Цена", 0)
               else COALESCE(pr.price, 0)
          end::numeric(32,2)) * rstbqty "СуммаЦен"
    , case when n."Раздел@" is true then true else null end iss
    , case when n."Раздел@" is not TRUE then n."КодЕдиницаИзмерения" else null::text end as "КодЕдиницаИзмерения"
    , case when n."Раздел@" is not TRUE then ((ite."ЕИУ"[1])::json->'base'->>'abbr') else null::text end as "ЕдиницаИзмеренияНазвание"
    , nd."Раздел"
    , n."НомНомер"
    , nd."Флаги"[6] as "ЖНВЛП"
    , ((ndr."Параметры")::hstore -> 'Series')::text as "Series"
    , ((ndr."Параметры")::hstore -> 'ManufacturerPrice')::text as "ManufacturerPrice"
    , tn."ПодвидУчета"
    , case when n."Раздел@" is not true then n."НомНомер" else null::text end "Номенклатура.НомНомер"
    , tn."Категория" "ТипНоменклатуры.Категория"
    , ne."Article" as "NomenclatureExt.Article"
    , case when (ndr."КодПартии" is null or position(' ' in ndr."КодПартии") > 0)
                then 1 -- опт
            when ndr."КодПартии" is not distinct from ''::varchar
                then 3 -- вскрытие
            when ndr."КодПартии" is not null
                then 2 -- торг.зал
            else NULL::integer
      end::smallint "ЕГАИСРегистр"
    , case (tn."Параметры"::json #>> '{suitability,precision}')::int
        when 1 then ((ndr."Параметры")::hstore -> 'ExpirationDate')::text
        else (regexp_split_to_array(((ndr."Параметры")::hstore -> 'ExpirationDate')::text, '\s+'))[1]
      end as "СрокГодности"
    , CASE
        WHEN usecst AND abs(rstbqty) > power(10, -12)
          THEN rstbsumit / rstbqty
        ELSE

            0

      END::numeric(32,6) "Себест"
    , CASE
        WHEN usecst AND abs(rstbqty) > power(10, -12)
          THEN rstbsumxt / rstbqty
          ELSE

              0

      END::numeric(32,6) "СебестБезНДС"

FROM
rst_prt itf
LEFT JOIN "Склад" w ON itf.wh = w."@Лицо"
LEFT JOIN "Склад" w1 ON w1."@Лицо" = w."Раздел"
JOIN main_orgs mo ON mo.sub = w."НашаОрганизация"
LEFT JOIN "НоменклатураДокумента" nd on nd."@НоменклатураДокумента" = itf.prt
left join "НоменклатураДокументаРасш" ndr on ndr."@НоменклатураДокумента" = nd."@НоменклатураДокумента"
left join "Номенклатура" n on n."@Номенклатура" = itf.it
left join "НоменклатураРасш" ite on ite."@Номенклатура" = itf.it
left join nom_price_date pr on itf.it = pr.it
left join "ТипНоменклатуры" tn on tn."@ТипНоменклатуры" = n."ТипНоменклатуры"
left join "NomenclatureExt" ne ON ne."@Номенклатура" = itf.it


),
ready_data as (

select
    it
    , pid
    , wh
    , o

        , dp
        , "ЖНВЛП"
        , "Series"
        , "ManufacturerPrice"
        , "ЕГАИСРегистр"
        , "СрокГодности"

    , "СуммаСебест"
    , "СуммаСебестБезНДС"
    , "Количество"
    , "СуммаЦен"
    , "NomenclatureExt.Article"
    , it "Номенклатура.@Номенклатура"
    , "Номенклатура.НомНомер"
    , "КодЕдиницаИзмерения"
    , "ЕдиницаИзмеренияНазвание"
    , "ПодвидУчета"
    , "ТипНоменклатуры.Категория"
    , "АгрегированныеМетрики"
    , coalesce(case when rstbc_code < -2 then -1 -- для остатков на дату подкраски меньше -2 равны -1
           else rstbc_code
      end, 0) "Индикация"
from
page_data_doc_parts


),
mv_group as (

        with recursive mv as (
    -- фильтруем полностью нулевые записи перед агрегацией
    SELECT
      it
      , wh
      , o

        , dp
        , "ЖНВЛП"
        , "Series"
        , "ManufacturerPrice"
        , "ЕГАИСРегистр"
        , "СрокГодности"

      , pid
      , "СуммаСебест"
      , "СуммаСебестБезНДС"
      , "Количество"
      , "СуммаЦен"
      , "NomenclatureExt.Article"
      , it "Номенклатура.@Номенклатура"
      , "Номенклатура.НомНомер"
      , "АгрегированныеМетрики"
      , "Индикация"
      , "КодЕдиницаИзмерения"
      , "ЕдиницаИзмеренияНазвание"
      , "ПодвидУчета"
      , "ТипНоменклатуры.Категория"
    FROM
      ready_data itwhext
),
itagg_hierarchy as (
  SELECT distinct it, pid, it as itt, array[pid] as pid_array, "Раздел@"
    FROM mv
     left join "Номенклатура" it
       on it."@Номенклатура" = mv.pid
    where it."Раздел@" is not false
    union
    select distinct agg.it, it."Раздел", it."@Номенклатура", pid_array || it."Раздел" , it."Раздел@"
    from itagg_hierarchy agg
     join "Номенклатура" it
       on it."@Номенклатура" = agg.pid
    where agg.pid is distinct from agg.itt
      and agg."Раздел@" is not false
),
nom_tree as(
  select
    pid_new
    , coalesce(pid_new, it) as it_order
    , ih.it
    , DENSE_RANK() over w  - 1 as deep
    , len
    , '@it-' || (lag(pid_new) over w)::text as "Раздел"
    , case when pid_new is not null then true else null end::bool as "Раздел@"
  from (
    select pid_array[len] as pid_new
      , it
      , case when pid_array[len] is null then 0 else len end len
    from (
      select
        it
        , pid_array
        , coalesce(array_length(pid_array, 1), 0) as len
      from itagg_hierarchy
    ) hs

    -- если искомая номенклатура раздел
    where true

      and 1998::bigint = any(array(table pids))

      -- тогда отбираем всё что ниже по иерархии
      and pid_array[len] = any(array(table pids))
      and pid_array[len] <> 1998::bigint
      or pid_array[len] is null

  ) ih
  window w as (partition by ih.it order by len desc)
),
itaggit as (
select distinct --on (coalesce(pid_new, ih.it))
  coalesce(pid_new, ih.it) as it
  , wh
  , o

        , dp
        , bool_or("ЖНВЛП") over w "ЖНВЛП"
        , max("Series") over w "Series"
        , max("ManufacturerPrice") over w "ManufacturerPrice"
        , max("ЕГАИСРегистр") over w "ЕГАИСРегистр"
        , max("СрокГодности") over w "СрокГодности"

  , deep
  , ih."Раздел"
  , ih."Раздел@"
  , sum("СуммаСебест") over w "СуммаСебест"
  , sum("СуммаСебестБезНДС") over w "СуммаСебестБезНДС"
  , sum("Количество") over w "Количество"
  , sum("СуммаЦен") over w "СуммаЦен"
  , max("NomenclatureExt.Article") over w "NomenclatureExt.Article"
  , max("Номенклатура.НомНомер") over w "Номенклатура.НомНомер"
  , max("ЕдиницаИзмеренияНазвание") over w "ЕдиницаИзмеренияНазвание"
  , max("ПодвидУчета") over w "ПодвидУчета"
  , max("ТипНоменклатуры.Категория") over w "ТипНоменклатуры.Категория"
  , min("АгрегированныеМетрики") over w "АгрегированныеМетрики"
  , min("Индикация") over w "Индикация"
from nom_tree ih
 left join mv on mv.it = ih.it
 window w as (partition by coalesce(pid_new, ih.it), wh, o

    , dp

 )
),
tree_nom as (
    select
        "@Номенклатура" it
      , "Раздел" pid
      , "Раздел@"
      , "Наименование"
      , "НомНомер"
      , "КодЕдиницаИзмерения"
    from "Номенклатура"
    where "@Номенклатура" = any(array(select it_order from nom_tree))
),
search_sort as (
  with recursive rec_order AS (
      SELECT
         n.it,
         ARRAY[ROW_NUMBER() OVER(
           ORDER BY
             case when n."Раздел@" is TRUE then 0 else 1 end,
             n."Наименование"
           )] AS order_path
      FROM
         tree_nom AS n
         --left join itwp mv on n.it = mv.it
      WHERE
         n.pid is not distinct from 1998::bigint
      UNION ALL
      SELECT
         n.it,
         r.order_path || ROW_NUMBER() OVER(
           PARTITION BY r.it
           ORDER BY
             n."Раздел@" NULLS LAST,
             n."Наименование"
           ) AS order_path
      FROM
         tree_nom AS n
         --LEFT JOIN itwp mv ON n.it = mv.it
         INNER JOIN rec_order AS r
         ON n.pid = r.it
    )
      SELECT
           it
       , order_path
       , array_length(order_path, 1) - 1 as "Глубина"
      FROM
         tree_nom AS n
       INNER JOIN rec_order AS r USING(it)
      where it is distinct from 1998::bigint
    ORDER BY r.order_path
)

select ROW_NUMBER() OVER (ORDER BY
    order_path
    , itg."Раздел@" DESC NULLS LAST
    , itg."Раздел"
    , "Наименование"
  ) number_rec
  , itg.it
  , itg.wh
  , itg.o

    , dp
    , "ЖНВЛП"
    , "Series"
    , "ManufacturerPrice"
    , "ЕГАИСРегистр"
    , "СрокГодности"

  , itg.deep
  , itg."Раздел"
  , itg."Раздел@" as "РазделНоменклатура"
  , "СуммаСебест"
  , "СуммаСебестБезНДС"
  , "Количество"
  , "СуммаЦен"
  , "NomenclatureExt.Article"
  , itg.it "Номенклатура.@Номенклатура"
  , "Номенклатура.НомНомер"
  , "КодЕдиницаИзмерения"
  , "ЕдиницаИзмеренияНазвание"
  , "ПодвидУчета"
  , "ТипНоменклатуры.Категория"
  , "АгрегированныеМетрики"
  , "Индикация"
from itaggit itg
 join "Номенклатура" it on it."@Номенклатура" = itg.it
 left join search_sort ss on ss.it = itg.it

),
get_hierarchy as (
with
dp_dimension as (
  select hstore(
	array_agg(keys."@Документ"::text),
	array_agg(hstore(
		array['Склад.Название'::text,
		'ТултипДетализации'::text,
		'ТипДокумента.@ТипДокумента'::text,
		'Документ.Время'::text,
		'Документ.Номер'::text,
		'НашаОрганизация.@Лицо'::text,
		'Документ.Дата'::text,
		'ДокументРасширение.Сумма'::text,
		'ТипДокумента.ТипДокумента'::text,
		'ТипДокумента.НазваниеКраткое'::text,
		'ИдРегламента'::text,
		'Лицо1.@Лицо'::text,
		'РП.Сотрудник.НазваниеКраткое'::text,
		'НашаОрганизация.Название'::text,
		'ТипДокумента'::text,
		'Розница'::text,
		'Лицо1.Название'::text,
		'Лицо1'::text,
		'Склад.Название2'::text,
		'Регламент.Ид'::text,
		'НазваниеДетализации'::text],
		array[wh."Название"::text,
		(Л1."Название"::text || ' ' || keys."Номер"::text || ' ' || keys."Лицо3"::text || ' ' || ЛС."Название"::text || ' ' || wh."Название"::text)::text,
		ТД."@ТипДокумента"::text,
		keys."Время"::text,
		keys."Номер"::text,
		keys."Лицо3"::text,
		keys."Дата"::text,
		dr."Сумма"::text,
		ТД."ТипДокумента"::text,
		ТД."НазваниеКраткое"::text,
		keys."ИдРегламента"::text,
		Л1."@Лицо"::text,
		ЛС."Название"::text,
		Лицо3."Название"::text,
		keys."ТипДокумента"::text,
		case when keys."Лицо1" is NULL then TRUE else FALSE end::text,
		Л1."Название"::text,
		keys."Лицо1"::text,
		(case when ТД."ТипДокумента" = any(array['ВнутрПрм','АктВыпуска']) then (
                                        coalesce(
                                          (
                                            SELECT
                                              wrh."Название"
                                            FROM "Склад" as wrh
                                            WHERE
                                              wrh."@Лицо" = (
                                                select "Лицо2"
                                                from "Документ"
                                                where "@Документ" = keys."@Документ"
                                              )
                                           ),(
                                            SELECT wrh."Название"
                                            FROM "Склад" as wrh
                                            WHERE wrh."@Лицо" = (
                                              select dn2."СкладДополнительный"
                                              from "СкладскойДокументРасширение" as dn2
                                              where dn2."@Документ" = keys."@Документ"
                                            )
                                          )
                                        )
                                      ) else null::text end)
                                      ::text,
		keys."ИдРегламента"::text,
		Л1."Название"::text]
	)::text)
	) as hs
  from "Документ" keys

  left join "Лицо" Л1 ON keys."Лицо1" = Л1."@Лицо"
left join "ДокументРасширение" dr ON keys."@Документ" = dr."@Документ"
left join "ТипДокумента" ТД ON keys."ТипДокумента" = ТД."@ТипДокумента"
left join "Лицо" ЛС ON keys."Сотрудник" = ЛС."@Лицо"
left join "СкладскойДокумент" wd ON wd."@Документ" = keys."@Документ"
left join "Лицо" Лицо3 ON keys."Лицо3" = Лицо3."@Лицо"
left join "Склад" wh ON wh."@Лицо" = wd."Склад"
  where keys."@Документ"=any(array(select dp from mv_group order by dp))

)

select
 null::text as "DBId"
, count(1) as "КоличествоСтрок"
  , null::int "Документ.@Документ"
  , null::text "Документ.Контрагент"
  , null::text "Склад.Название"
  , null::text "Документ.Время"
  , null::int "НашаОрганизация.@Лицо"
  , null::text "Документ.Дата"
  , null::NUMERIC(32,2) "ДокументРасширение.Сумма"
  , null::text "ТипДокумента.ТипДокумента"
  , null::text "ИдРегламента"
  , null::text "РП.Сотрудник.НазваниеКраткое"
  , null::text "ТипДокумента"
  , null::boolean "Розница"
  , null::text "Лицо1.Название"
  , null::int "Лицо1"
  , null::text "Склад.Название2"
  , null::uuid "Регламент.Ид"
  , null::int "ТипДокумента.@ТипДокумента"
  , null::text "Документ.Номер"
  , null::text "ТипДокумента.НазваниеКраткое"
  , null::int "Лицо1.@Лицо"
  , null::text "НашаОрганизация.Название"
  , null::text "КодЕдиницаИзмерения"
  , null::text "Номенклатура.НомНомер"
  , null::text "ЕдиницаИзмеренияНазвание"
  , null::text as "НазваниеДетализации"
  , null::text as "Раздел"
 , True as "Раздел@"
 , True as "ДетализацияРаздел@"
 , bool_and("РазделНоменклатура") as "РазделНоменклатура"
 , -4 as "АгрегированныеМетрики"
 , 'Итоги'::text as "ТипЗаписи"
  , sum("СуммаЦен") "СуммаЦен"
  , sum("СуммаСебестБезНДС") "СуммаСебестБезНДС"
  , sum("Количество") "Количество"
  , sum("СуммаСебест") "СуммаСебест"
  , case when sum("Количество") <> 0
                                                       then sum("СуммаЦен") / sum("Количество")
                                                       when sum("Количество") = 0.0
                                                       then sum("СуммаЦен")
                                                       ELSE 0
                                                 END  "Цена"
  , case when sum("Количество") <> 0 then sum(СуммаСебестБезНДС) / sum(Количество)
                      else sum(СуммаСебестБезНДС) end  "СебестБезНДС"
  ,  case when array_length(array_agg("СрокГодности"), 1) = 1
                                               then max("СрокГодности") else Null
                                               end  "СрокГодности"
  , case when sum("Количество") <> 0 then sum(СуммаСебест) / sum(Количество)
                      else sum(СуммаСебест) end  "Себест"
  , min("Индикация") as "Индикация"
  , max("ПодвидУчета") as "ПодвидУчета"
  , max("ТипНоменклатуры.Категория") as "ТипНоменклатуры.Категория"
  , max("NomenclatureExt.Article") as "NomenclatureExt.Article"
  , max("ЕГАИСРегистр") as "ЕГАИСРегистр"
  , max("Series") as "Series"
  , max("ManufacturerPrice") as "ManufacturerPrice"
  , bool_or("ЖНВЛП") as "ЖНВЛП"
from (select * from mv_group) mv
where True
and "РазделНоменклатура" is null
union all
select
 ('dp-' || dp::text )::text as "DBId"
, count(1) as "КоличествоСтрок"
  , dp as "Документ.@Документ"
  , min((dp_dimension.hs->dp::text)::hstore->'Документ.Контрагент') as "Документ.Контрагент"
  , max((dp_dimension.hs->dp::text)::hstore->'Склад.Название')::text as "Склад.Название"
  , max((dp_dimension.hs->dp::text)::hstore->'Документ.Время')::text as "Документ.Время"
  , max((dp_dimension.hs->dp::text)::hstore->'НашаОрганизация.@Лицо')::int as "НашаОрганизация.@Лицо"
  , max((dp_dimension.hs->dp::text)::hstore->'Документ.Дата')::text as "Документ.Дата"
  , max((dp_dimension.hs->dp::text)::hstore->'ДокументРасширение.Сумма')::NUMERIC(32,2) as "ДокументРасширение.Сумма"
  , max((dp_dimension.hs->dp::text)::hstore->'ТипДокумента.ТипДокумента')::text as "ТипДокумента.ТипДокумента"
  , max((dp_dimension.hs->dp::text)::hstore->'ИдРегламента')::text as "ИдРегламента"
  , max((dp_dimension.hs->dp::text)::hstore->'РП.Сотрудник.НазваниеКраткое')::text as "РП.Сотрудник.НазваниеКраткое"
  , max((dp_dimension.hs->dp::text)::hstore->'ТипДокумента')::text as "ТипДокумента"
  , max((dp_dimension.hs->dp::text)::hstore->'Розница')::boolean as "Розница"
  , max((dp_dimension.hs->dp::text)::hstore->'Лицо1.Название')::text as "Лицо1.Название"
  , max((dp_dimension.hs->dp::text)::hstore->'Лицо1')::int as "Лицо1"
  , max((dp_dimension.hs->dp::text)::hstore->'Склад.Название2')::text as "Склад.Название2"
  , max((dp_dimension.hs->dp::text)::hstore->'Регламент.Ид')::uuid as "Регламент.Ид"
  , max((dp_dimension.hs->dp::text)::hstore->'ТипДокумента.@ТипДокумента')::int as "ТипДокумента.@ТипДокумента"
  , max((dp_dimension.hs->dp::text)::hstore->'Документ.Номер')::text as "Документ.Номер"
  , max((dp_dimension.hs->dp::text)::hstore->'ТипДокумента.НазваниеКраткое')::text as "ТипДокумента.НазваниеКраткое"
  , max((dp_dimension.hs->dp::text)::hstore->'Лицо1.@Лицо')::int as "Лицо1.@Лицо"
  , max((dp_dimension.hs->dp::text)::hstore->'НашаОрганизация.Название')::text as "НашаОрганизация.Название"
  , null::text "КодЕдиницаИзмерения"
  , null::text "Номенклатура.НомНомер"
  , null::text "ЕдиницаИзмеренияНазвание"
  , max(coalesce((dp_dimension.hs->dp::text)::hstore->'НазваниеДетализации', 'None')) as "НазваниеДетализации"
  , null::text as "Раздел"
 , case when dp = 0 then null else True end as "Раздел@"
 , case when dp = 0 then null else True end as "ДетализацияРаздел@"
 , bool_and("РазделНоменклатура") as "РазделНоменклатура"
 , -4 as "АгрегированныеМетрики"
 , 'ДокументПартия' as "ТипЗаписи"
  , sum("СуммаЦен") "СуммаЦен"
  , sum("СуммаСебестБезНДС") "СуммаСебестБезНДС"
  , sum("Количество") "Количество"
  , sum("СуммаСебест") "СуммаСебест"
  , case when sum("Количество") <> 0
                                                       then sum("СуммаЦен") / sum("Количество")
                                                       when sum("Количество") = 0.0
                                                       then sum("СуммаЦен")
                                                       ELSE 0
                                                 END  "Цена"
  , case when sum("Количество") <> 0 then sum(СуммаСебестБезНДС) / sum(Количество)
                      else sum(СуммаСебестБезНДС) end  "СебестБезНДС"
  ,  case when array_length(array_agg("СрокГодности"), 1) = 1
                                               then max("СрокГодности") else Null
                                               end  "СрокГодности"
  , case when sum("Количество") <> 0 then sum(СуммаСебест) / sum(Количество)
                      else sum(СуммаСебест) end  "Себест"
  , min("Индикация") as "Индикация"
  , max("ПодвидУчета") as "ПодвидУчета"
  , max("ТипНоменклатуры.Категория") as "ТипНоменклатуры.Категория"
  , max("NomenclatureExt.Article") as "NomenclatureExt.Article"
  , max("ЕГАИСРегистр") as "ЕГАИСРегистр"
  , max("Series") as "Series"
  , max("ManufacturerPrice") as "ManufacturerPrice"
  , bool_or("ЖНВЛП") as "ЖНВЛП"
from dp_dimension, (select * from mv_group order by dp) mv
where True
and "РазделНоменклатура" is null
  group by
    dp

),
result_metrics as (

       SELECT * FROM get_hierarchy

),
order_hierarchy as (
  with recursive rec_order AS (
    SELECT
      n."DBId"
      , ARRAY[
          ROW_NUMBER() OVER(
            ORDER BY
              case
                when n."РазделНоменклатура" is TRUE then -1
                when n."DBId" like '%dp-0%' then -2
                else
                  case
                    when n."Раздел@" is TRUE then 0
                    else 1
                  end
              end,
              n."Документ.Дата" desc, n."Документ.Время" desc, n."Документ.@Документ"
          )
        ] AS order_path
      , array[n."DBId", n."НазваниеДетализации", n."Раздел"::text, n."Раздел@"::text, NULL::text] prep_path
    FROM
      result_metrics
      as n
     WHERE
       n."Раздел" is null
    UNION ALL
    SELECT
      n."DBId"
      , r.order_path || ROW_NUMBER() OVER(
          PARTITION BY "Раздел"
          ORDER BY
              case
                when n."РазделНоменклатура" is TRUE then -1
                when n."DBId" like '%dp-0%' then -2
                else
                  case
                    when n."Раздел@" is TRUE then 0
                    else 1
                  end
              end,
            n."Документ.Дата" desc, n."Документ.Время" desc, n."Документ.@Документ"
        ) AS order_path
      , prep_path || array[n."DBId", n."НазваниеДетализации", n."Раздел"::text, n."Раздел@"::text, NULL::text] prep_path
    FROM
      result_metrics
      AS n
    INNER JOIN rec_order AS r ON n."Раздел" = r."DBId"
  )
  select
    n.*
    , ROW_NUMBER() OVER (ORDER BY
      order_path
      , "Раздел@" DESC NULLS LAST,
      n."Документ.Дата" desc, n."Документ.Время" desc, n."Документ.@Документ"
    ) number_rec
    , "Глубина"
    , prep_path
  from
    result_metrics
    n
  left join (
    select
      n."DBId"
      , order_path
      , coalesce(array_length(order_path, 1) - 1, 0) as "Глубина"
      , prep_path
    from
      result_metrics
      AS n
    join rec_order AS r using("DBId")
    where
      n."DBId" is distinct from null
    ORDER BY
      r.order_path
  ) ss using("DBId")
  order by order_path nulls first
),
report_hierarchy as (

    select *
    from
      order_hierarchy

),
query as (

    SELECT * FROM report_hierarchy

        limit 10000::bigint offset 0::bigint

)
select * from query;