CREATE TEMP TABLE expertize
(
    lid int4 PRIMARY KEY,
    procedure_id int4,
    boss varchar(200),
    boss_date DATE,
    komitet varchar(200),
    komitet_date DATE,
    tsmets varchar(200),
    tsmets_date DATE,
    control_date_End DATE,
    expertize_date_End DATE,
    expertize_date_plan DATE
)
;

-- заполняем времнную таблицу eshop_rej
INSERT INTO expertize
SELECT
             l.id lid,
             e.procedure_id,
             MAX(CASE WHEN e.boss_id IS NOT NULL THEN u.last_name|| ' ' ||u.first_name|| ' ' ||u.middle_name ELSE NULL END) boss,
             MAX(CASE WHEN e.boss_id IS NOT NULL THEN e.expertize_date_plan ELSE NULL END) boss_date,
             MAX(CASE WHEN e.department_id = 10564 THEN u.last_name|| ' ' ||u.first_name|| ' ' ||u.middle_name ELSE NULL END) komitet,
             MAX(CASE WHEN e.department_id = 10564 THEN e.expertize_date_plan ELSE NULL END) komitet_date,
             MAX(CASE WHEN e.department_id IN (10565,57725) THEN u.last_name|| ' ' ||u.first_name|| ' ' ||u.middle_name ELSE NULL END) tsmets,
             MAX(CASE WHEN e.department_id IN (10565,57725) THEN e.expertize_date_plan ELSE NULL END) tsmets_date,
             MAX(control_date_End) AS control_date_End,
             MAX(expertize_date_End) AS expertize_date_End,
             MAX(expertize_date_plan) AS expertize_date_plan
  FROM po_procedure_expertize e
    LEFT JOIN lots l ON e.procedure_id = l.procedure_id
  JOIN users u ON COALESCE(e.boss_id,e.user_id) = u.id
 GROUP BY e.procedure_id, l.id
HAVING DATE_PART('year', MAX(CASE WHEN e.boss_id IS NOT NULL THEN e.expertize_date_plan ELSE NULL END)) IN (2022, 2023)
ORDER BY procedure_id;


-- создаем временную таблицу cons_lot выводим те лоты по которым больше 1 заказчика т.е. совместные
CREATE TEMP TABLE hist AS (
SELECT record_id,
             COUNT(DATE) AS count_date_uo
 FROM (SELECT record_id, DATE
                  FROM po_procedure_hIstory
           WHERE field = 'sed_registration_date' AND record_id IN (SELECT procedure_id FROM expertize)) a
                 GROUP BY record_id);

CREATE TEMP TABLE  nmck AS (
SELECT l.procedure_id,
SUM(start_price) AS nmсk
FROM procedures pr
JOIN lots l  ON l.procedure_id = pr.id
GROUP BY l.procedure_id);



CREATE TEMP TABLE date_c AS (
SELECT pr.id, record_id,
               DATE date_all,
                     ROW_NUMBER() OVER (PARTITION BY record_id ORDER BY DATE)  AS rating,
                     pr.date_notice_published,
                     CASE WHEN DATE::DATE<=COALESCE(pr.date_notice_published::DATE, NOW()) THEN 1 ELSE 0 END date_publ_sr,
                     psto.step_id,
                     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 step_priznak
        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);



SELECT
    a.* ,
    RANK() OVER(ORDER BY a.step_id,a.rating) rnk,
    ROW_NUMBER() OVER ( PARTITION BY a.step_id ) rn
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 a.id=4311985
ORDER BY a.rating;


create temp table date_count AS (
SELECT record_id lot_id,
			 min(case when a.step_id in ('assign_expert_uo','wait_expert_uo' ) then a.date_alla else null end) min_date_uo,
			 string_agg(exp_w_d::varchar ,';' ORDER BY date_alla) c_exp_wd, -- Количество рабочих дней после начала экспертизы УО
			 string_agg(red_w_d::varchar ,';' ORDER BY date_alla) c_red_wd, --Количество рабочих дней после опубликования заключения УО
			 sum(exp_w_d) s_exp_wd, -- Общее количество рабочих дней нахождения заявки на экспертизе
			 sum(red_w_d) s_red_wd, -- Общее количество рабочих дней нахождения заявки на доработке

			 string_agg(date_all_do::varchar,'; ' ORDER BY date_alla) AS date_all_do,
			 string_agg(date_all_posle::varchar,'; ' ORDER BY date_alla) AS date_all_posle,
			 sum(count_zakl_do) count_zakl_do,
			 sum(count_postup_no)count_postup_no,
			 sum(count_zakl_posle) count_zakl_posle,
			 string_agg(a.step_id_do,'; ' ORDER BY date_alla) AS steps_do,
			 string_agg(a.step_id_posle,'; 'ORDER BY date_alla) AS steps_posle,
			 max(case when rait_tek_st =1 then a.step_id_do else null end) status_now_pr
			 FROM (
SELECT a.record_id, a.step_id,
			CASE WHEN a.rating%2=1 and a.date_publ_sr = 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.date_publ_sr = 1 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,
      CASE WHEN a.rating%2=1 THEN b.date_all - a.date_all ELSE null END exp,
      CASE WHEN a.rating%2=0 then b.date_all - a.date_all ELSE null END red,
			CASE WHEN a.date_publ_sr = 1 and a.step_id<>'recall_request_review' then a.date_all::varchar
			     WHEN a.date_publ_sr = 1 and a.step_id='recall_request_review' then a.date_all::varchar ||'*'
					 ELSE null
			END date_all_do,
			CASE WHEN a.date_publ_sr = 0 and a.step_id<>'recall_request_review' then a.date_all::varchar
						WHEN a.date_publ_sr = 0 and a.step_id='recall_request_review' then a.date_all::varchar ||'*'
						ELSE null
			END date_all_posle,
			 b.date_all,
			 a.date_all date_alla,
			 case when a.step_id='wait_expert_uo' then 1 else 0 end count_postup_no,
			 case when a.date_publ_sr = 1 and b.step_id='expertize_complete' then 1 else 0 end count_zakl_do,
			 case when a.date_publ_sr = 0 and b.step_id='expertize_complete' then 1 else 0 end count_zakl_posle,
			 case when a.date_publ_sr = 1 and a.step_id = 'assign_expert_uo' then 'Назначение экспертизы в УО'
			 when a.date_publ_sr = 1 and a.step_id = 'wait_expert_uo' then 'На экспертизе заявки на закупку в УО'
			 when a.date_publ_sr = 1 and a.step_id = 'expertize_complete' then 'Экспертиза завершена'
			 when a.date_publ_sr = 1 and a.step_id = 'recall_request_review' then 'Рассмотрение запроса на отзыв'
			 end step_id_do,
			 			 case when a.date_publ_sr = 0 and a.step_id = 'assign_expert_uo' then 'Назначение экспертизы в УО'
			 when a.date_publ_sr = 0 and a.step_id = 'wait_expert_uo' then 'На экспертизе заявки на закупку в УО'
			 when a.date_publ_sr = 0 and a.step_id = 'expertize_complete' then 'Экспертиза завершена'
			 when a.date_publ_sr = 0 and a.step_id = 'recall_request_review' then 'Рассмотрение запроса на отзыв'
			 end step_id_posle,
			 case when a.date_publ_sr = 1 then row_number() over (partition by a.record_id order by a.date_all desc) else null end rait_tek_st
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)
;
create temp table fin AS (
	SELECT
		l.procedure_id,
		SUM(CASE WHEN pvtp.name = 2023 THEN pf.amount ELSE NULL END ) amount_23,
		SUM(CASE WHEN pvtp.name = 2024 THEN pf.amount ELSE NULL END ) amount_24
	  FROM expertize exp
		JOIN lots l ON l.procedure_id = exp.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
		JOIN po_vocab_time_periods pvtp On pvtp.id = pb.po_period_id
		GROUP BY l.procedure_id)
;
create temp table exp_date AS (
SELECT MAX(date) expertize_complete_date,
        record_id
	 FROM expertize exp
	 JOIN lots l ON l.procedure_id = exp.procedure_id
	 JOIN lots_history ps ON ps.record_id = l.id
   WHERE ps."to" in (
	  SELECT id::varchar
		  FROM procedure_steps ps
	   WHERE ps.step_id = 'edit_request')
GROUP BY record_id
)
;
create temp table cst AS (
		SELECT id,
		       string_agg( article_code,'; ') AS cel_st
		FROM (SELECT DISTINCT l.id, dea.article_code
	          FROM expertize exp
		        JOIN lots l ON l.procedure_id = exp.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_exp_account pea 	on pea.id = pa.po_exp_account_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
				JOIN po_budget pb On pb.id = pa.po_budget_id
				JOIN po_vocab_time_periods pvtp On pvtp.id = pb.po_period_id
			  ) q
		GROUP BY id
		)
;
create temp table fin_con AS (
	SELECT
		l.procedure_id,
		SUM(CASE WHEN pvtp.name = 2023 AND pf.type =1 THEN pf.amount ELSE NULL END) amountcf_23,
		SUM(CASE WHEN pvtp.name = 2023 AND pf.type =2 THEN pf.amount ELSE NULL END) amountc_23,
		SUM(CASE WHEN pvtp.name = 2024 AND pf.type =1 THEN pf.amount ELSE NULL END) amountc_24
	  FROM expertize exp
		JOIN lots l ON l.procedure_id = exp.procedure_id
		JOIN cm_contract_finances pf ON (pf.lot_id = l.id AND pf.type In (1,2) AND pf.actual IS TRUE)
		JOIN po_account pa ON pa.id = pf.po_account_id
		JOIN po_budget pb ON pb.id = pa.po_budget_id
		JOIN po_vocab_time_periods pvtp On pvtp.id = pb.po_period_id
		GROUP BY l.procedure_id)
;
create temp table ppr AS (
	SELECT ppr.procedure_id,
         COUNT(*) count_procedure_recall
   FROM expertize exp
	 JOIN po_procedure_recall_request ppr ON ppr.procedure_id = exp.procedure_id
	WHERE actual IS TRUE
	GROUP BY ppr.procedure_id
	)


	SELECT

			 c_grbs.full_name AS grbsname, -- ГРБС
			 c.full_name, --заказчик
       c1.full_name AS customer, --организатор
			 vpt.name AS way, --СОП
			 pr.title AS purchase,-- наименование закупки
			 n.nmсk, -- нмцк
			 date_count.count_postup_no, -- поступило на экспертизу (общее количество считается от количества направлено на экспертизу)
			 date_c.nach_exp_act, -- дата начала экспертизы
			 date_count.count_zakl_do,-- количество заключений
			 ppr.count_procedure_recall,-- количество отзывов
			 date_count.c_exp_wd, -- Количество рабочих  дней после начала экспертизы УО
			 date_count.s_exp_wd,	-- 	Общее количество рабочих дней нахождения заявки на экспертизе
			 vps.full_name lotstage, -- текущий статус
			 cc.contract_amount, -- цена контракта
			 n.nmсk - coalesce(cc.contract_amount,0) eco -- цена контракта снижения
			 ,'' -- примечение (пустое поле)

			/*,date_part('year',min_date_uo), pr.id AS id_procedure, min_date_uo,
       pp.request_order_number,






       case when pl.supply_impossible is true then 'да' else null end supply_impossible,
			 --stages_exp.date_all,
			 --stages_exp.date_all_recall,
			 ppr.count_procedure_recall,
			 h.count_date_uo,
			 --date_count.c_exp,
			 --date_count.c_red,
			 --date_count.s_exp,
			 --date_count.s_red,
			 date_count.c_exp_wd,
			 date_count.c_red_wd,
			 case when date_count.s_exp_wd is null then 0 else date_count.s_exp_wd end s_exp_wd,
			 case when date_count.s_red_wd is null then 0 else date_count.s_red_wd end s_red_wd,
			 date_count.date_all_do,
			 date_count.steps_do,
			 date_count.date_all_posle,
			 date_count.steps_posle,
			 date_count.count_zakl_do,
			 date_count.count_zakl_posle,
			 l.date_fulfilled,
			 l.date_placed,
			 f.amount_23,
	     f.amount_24,
			 exp.expertize_date_plan::date, -- Дата завершения контроля (план)
	     CASE WHEN vps.full_name ='Редактирование заявки на закупку' THEN (SELECT sum(type_::int) FROM sppr.work_days_all WHERE date_
	     BETWEEN exp_date.expertize_complete_date::date + INTERVAL  '1 day' AND current_date) ELSE NULL END work_days,
			 exp.komitet,
			 exp.tsmets,
			 exp.control_date_End::date,
			 pr.regIstry_number,
			 pl.purchase_code,
			 cst.cel_st,
			 cc.contract_rnk,
	     cc.contract_amount,
			 cc.date_sign::date,
			 fc.amountcf_23,
	     fc.amountc_23,
	     fc.amountc_24,
	     pr.date_notice_published::date,
			 date_count.status_now_pr
			 */
FROM procedures pr
JOIN po_procedures pp ON pr.id = pp.procedure_id
JOIN expertize exp ON exp.procedure_id = pr.id
JOIN vocab_procedure_types vpt ON pr.procedure_type = vpt.id
JOIN lots l ON l.procedure_id = pr.id
JOIN lot_customers lc ON lc.lot_id = l.id AND lc.actual = TRUE
JOIN contragents c ON c.id = lc.customer_id
JOIN contragents c1 ON c1.id = pr.organizer_contragent_id
JOIN po_list_org_hierarchy ploh ON ploh.contragent_id = c.id AND ploh.actual = TRUE AND ploh.parent_type = 1
JOIN contragents c_grbs ON c_grbs.id = ploh.parent_contragent_id
JOIN procedure_steps ps	ON ps.id = l.current_step
JOIN vocab_procedure_steps vps ON vps.pseudo::text = ps.step_id::text And vps.actual = true
LEFT JOIN nmck n ON n.procedure_id = pr.id
LEFT JOIN hist h ON h.record_id = pr.id
--LEFT JOIN stages_exp ON stages_exp.lot_id = l.id
LEFT JOIN date_count ON date_count.lot_id=l.id
LEFT JOIN fin f ON f.procedure_id = pr.id
LEFT JOIN exp_date ON exp_date.record_id=l.id
LEFT JOIN po_lots pl On pl.lot_id = l.id
LEFT JOIN cst ON cst.id = l.id
LEFT JOIN cm_contracts cc ON l.id = cc.lot_id
LEFT JOIN fin_con fc ON fc.procedure_id = pr.id
LEFT JOIN ppr ON ppr.procedure_id = pr.id
left Join (SELECT
						max(case when step_id='wait_expert_uo' then date_all else null end) nach_exp_act,
						id   FROM date_c group by id )date_c on date_c.id =pr.id
WHERE date_part('year',min_date_uo)=2023
AND pr.id <> 4173048 -- по просьбе Имедашвили Лаша убрать закупку, ошибочно вышедшую на экспертизу 28/02/23
-- and pr.id='4311985'
;