WITH expertize AS (
SELECT e.procedure_id
  FROM po_procedure_expertize e
 GROUP BY e.procedure_id
HAVING date_part('year', MAX(CASE WHEN e.boss_id IS NOT NULL THEN e.expertize_date_plan ELSE NULL END)) IN (2023) --Ограничение по плановому году экспертизы в УО
ORDER BY procedure_id
),


-- НМЦК
nmck AS (
SELECT l.procedure_id,
SUM(start_price) AS nmсk
FROM procedures pr
JOIN lots l On l.procedure_id = pr.id
WHERE pr.id in (SELECT * FROM expertize)
GROUP BY l.procedure_id)

-- Вспомогательная таблица
, date_c AS (
SELECT pr.id, record_id,
               date date_all,
                     ROW_NUMBER() OVER (PARTITION BY record_id ORDER BY date)  AS rating,
                     psto.step_id
        FROM (
        SELECT date,
                     CASE WHEN "from"='NULL' THEN null ELSE "from"::integer END "from",
                     CASE WHEN  "to"='NULL' THEN null ELSE "to"::integer END "to",
                     lh.record_id
        FROM lots_history lh
        WHERE lh.record_id IN (SELECT DISTINCT l.id
            FROM expertize e
            LEFT JOIN lots l ON l.procedure_id=e.procedure_id)
        AND lh.field = 'current_step'
        ORDER BY date
        ) a
        LEFT JOIN procedure_steps psfrom on psfrom.id  = a.from
        LEFT JOIN procedure_steps psto on psto.id  = a.to
        JOIN lots l on l.id = a.record_id
    JOIN procedures pr on l.procedure_id = pr.id
        WHERE (psto.step_id = 'expertize_complete' OR
                    psto.step_id = 'assign_expert_uo' OR
                    psto.step_id = 'recall_request_review' OR
                    psto.step_id = 'wait_expert_uo')
                    AND
                    (case when date_part('year', date::date) < 2023 and psto.step_id = 'assign_expert_uo' then 1
                          when date_part('year', date::date) >= 2023 and psto.step_id = 'wait_expert_uo' then 1
                                when psto.step_id = 'recall_request_review' then 1
                                when psto.step_id = 'expertize_complete' then 1
                                else 0 end) = 1
        ORDER BY record_id, date),

-- Подсчет количества рабочих дней нахождения заявки на экспертизе/редактировании
date_count AS (
SELECT record_id lot_id,
             SUM(exp_w_d) s_exp_wd, -- Общее количество рабочих дней нахождения заявки на экспертизе
             SUM(red_w_d) s_red_wd, -- Общее количество рабочих дней нахождения заявки на доработке
             MIN(case when step_id in ('assign_expert_uo','wait_expert_uo' ) then a.date_all else null end) min_date_uo,
             MAX(case when step_id in ('assign_expert_uo','wait_expert_uo' ) then a.date_all else null end) max_date_uo,
             COUNT(case when step_id in ('assign_expert_uo','wait_expert_uo' ) then a.date_all else null end) count_date_uo
             FROM (
SELECT a.record_id,
            CASE WHEN a.rating%2=1 and b.step_id<>'recall_request_review' THEN (SELECT SUM(type_::int) FROM sppr.work_days_all WHERE date_
                                                                     BETWEEN a.date_all::date + INTERVAL  '1 day' AND b.date_all::date)  ELSE null END exp_w_d,
            CASE WHEN a.rating%2=0 and a.step_id<>'recall_request_review' THEN (SELECT SUM(type_::int) FROM sppr.work_days_all WHERE date_
                                                                     BETWEEN a.date_all::date + INTERVAL  '1 day' AND b.date_all::date)  ELSE null END red_w_d,
                a.date_all, a.step_id
FROM date_c a
LEFT JOIN (select record_id, date_all, rating-1 rating, step_id from date_c) b ON a.record_id = b.record_id AND a.rating = b.rating
JOIN lots l on l.id = a.record_id
JOIN procedures pr on l.procedure_id = pr.id
--WHERE b.record_id IS NOT NULL
)a
GROUP BY record_id),

kbk AS (
SELECT distinct l.id lid,
    dea.grbs_code
    ||dea.section_code
    ||dea.article_code
    ||dea.e_code
    ||dea.kosgu kbk
FROM expertize e
JOIN lots l on e.procedure_id = l.procedure_id
JOIN po_finances pf On (pf.lot_id = l.id)
JOIN po_account pa On (pa.id = pf.po_account_id)
JOIN po_budget pb On (pb.id = pa.po_budget_id And pb.actual = true )
JOIN po_vocab_time_periods pvtp On (pvtp.id = pb.po_period_id)
JOIN po_exp_account pea On (pea.id = pa.po_exp_account_id)
JOIN (
            Select min(gpo_budget_type_code) gpo_budget_type_code,budget_type
            From gpo_vocab_budget_links
            Where actual Is True
            Group by budget_type
 )gvbl On ((gvbl.budget_type)::text = (pea.budget_type)::text)
JOIN gpo_list_budget glb On ((glb.code)::text = (gvbl.gpo_budget_type_code)::text)
LEFT JOIN po_vocab_time_periods pvtp_a On (pvtp_a.id = pb.po_accept_period_id)
LEFT JOIN gpo_po_exp_account gpea On (gpea.po_exp_code_id = pea.id)
LEFT JOIN gpo_exp_account dea On (dea.id = gpea.gpo_exp_account_id))




SELECT pr.id AS id_procedure,
             c.full_name, --заказчик
             pr.title AS purchase,
             n.nmсk,
             date_count.min_date_uo min_date,
             date_count.max_date_uo max_date,
             date_count.count_date_uo,
             date_count.s_exp_wd,
             date_count.s_red_wd,
             kbk.kbk,
             pl.purchase_code
FROM expertize exp
JOIN procedures pr ON exp.procedure_id = pr.id
JOIN lots l ON l.procedure_id = pr.id
Inner Join public.po_lots pl on pl.lot_id = l.id
JOIN lot_customers lc ON lc.lot_id = l.id AND lc.actual = TRUE
JOIN contragents c ON c.id = lc.customer_id
LEFT JOIN nmck n ON n.procedure_id = pr.id
LEFT JOIN date_count ON date_count.lot_id=l.id
LEFT JOIN kbk ON kbk.lid=l.id