Untitled

WITH sum AS (SELECT pp.procedure_id,
                                string_agg(distinct ppf.e_code, ','::text) AS kvr,
                                sum(case when pvtp.name = 2020 then ppf.amount else null end ) amount_19,
                                sum(case when pvtp.name = 2021 then ppf.amount else null end ) amount_20,
                                sum(case when pvtp.name = 2022 then ppf.amount else null end ) amount_21,
                                sum(case when pvtp.name > 2022 then ppf.amount else null end ) amount_21p
                        from po_plan_position pp
                        join po_plan_position_finances ppf on pp.id = ppf.po_plan_position_id
                        join po_vocab_time_periods pvtp on pvtp.id = ppf.year
                        where  ppf.actual = true and pp.actual = true
                        group by pp.procedure_id ),
    sum_c AS (SELECT pf.lot_id,
                                --string_agg(distinct ppf.e_code, ','::text) AS kvr,
                                sum(case when pvtp.name = 2020 then pf.amount else null end ) amount_19,
                                sum(case when pvtp.name = 2021 then pf.amount else null end ) amount_20,
                                sum(case when pvtp.name = 2022 then pf.amount else null end ) amount_21,
                                sum(case when pvtp.name > 2022 then pf.amount else null end ) amount_21p
                        from cm_contract_finances pf
                        join cm_contract_stages ccs     on ccs.id = pf.stage_id and ccs.actual = true
                        JOIN po_account pa ON pa.id = pf.po_account_id
                        /*join po_exp_account pea       on pea.id = pa.po_exp_account_id
                        JOIN gpo_po_exp_account gpea ON gpea.po_exp_code_id = pea.id
                        JOIN gpo_exp_account gea ON gea.id = gpea.gpo_exp_account_id*/
                        join po_budget pb       on pb.id = pa.po_budget_id and pb.contragent_id --key_pbs
                        join po_vocab_time_periods pvtp on pvtp.id = pb.po_period_id
                        where pb.actual = true and pf.type = 1
                        group by pf.lot_id),
    sum_p AS (SELECT pf.lot_id,
                                --string_agg(distinct ppf.e_code, ','::text) AS kvr,
                                sum(case when pvtp.name = 2020 then pf.amount else null end ) amount_19,
                                sum(case when pvtp.name = 2021 then pf.amount else null end ) amount_20,
                                sum(case when pvtp.name = 2022 then pf.amount else null end ) amount_21,
                                sum(case when pvtp.name > 2022 then pf.amount else null end ) amount_21p
                        from cm_contract_finances pf
                        join cm_contract_stages ccs     on ccs.id = pf.stage_id and ccs.actual = true
                        JOIN po_account pa ON pa.id = pf.po_account_id
                        /*join po_exp_account pea       on pea.id = pa.po_exp_account_id
                        JOIN gpo_po_exp_account gpea ON gpea.po_exp_code_id = pea.id
                        JOIN gpo_exp_account gea ON gea.id = gpea.gpo_exp_account_id*/
                        join po_budget pb       on  pb.id = pa.po_budget_id and pb.contragent_id --key_pbs
                        join po_vocab_time_periods pvtp on pvtp.id = pb.po_period_id
                        where pb.actual = true and pf.type = 2
                        group by pf.lot_id
                )

select
                        c.full_name orgname, c.inn orginn,--c.id orgid,
                        c_grbs.full_name grbsname, c_grbs.inn grbsinn,--c_grbs.id grbsid,
                        pl_big.schedule_id,
                        SCH.plan_number pg_n, -- РН Плана-графика
                        po_plan.plan_number pz_n,
                        pl_big.purchase_code pg_ikz,    --  ИКЗ Плана-графика
                        pl_big.purchase_code_in_plan pz_ikz,    --  ИКЗ Плана закупок
                        l_big.plan_position_number pg_rn, -- РН позиции Плана-графика
                        vps_big.full_name lotstage_big,
                        pp.type_spec_purch,
                        --plsp.special_type,--Тип особых закупок
                        --plsp.special_type_pg,--наименование типа для планов-графиков
                        pp.order_number proc_num,--Порядковый номер процедуры в плане проведения закупок
                        pr.title subject,
                        --vpt.name sop,
                        --plc.name oneex_reason,
                        l.date_placed, --Планируемый срок размещения
                        l.date_fulfilled, --Планируемый срок исполнения
                        --pl.contract_fulfill_date,  --Срок исполнения контракта
                        vps.full_name lotstage,
                        l.scheduled_price_total /*start_price*/ start_price,
                        (COALESCE(sum.amount_19, 0) + COALESCE(sum.amount_20, 0) + COALESCE(sum.amount_21, 0) + COALESCE(sum.amount_21p, 0)) as sum_all,
                        sum.amount_19 as sum_cur,
                        sum.amount_20 as sum_first_plan,
                        sum.amount_21 as sum_second_plan,
                        sum.amount_21p as sum_future_plan,
                        sum.kvr,
            (COALESCE(sum_c.amount_19, 0) + COALESCE(sum_c.amount_20, 0) + COALESCE(sum_c.amount_21, 0) + COALESCE(sum_c.amount_21p, 0)) as sum_all_con,
            sum_p.amount_19 as sum_cur_con_f,
            sum_c.amount_19 as sum_cur_con,
                        sum_c.amount_20 as sum_first_plan_con,
                        sum_c.amount_21 as sum_second_plan_con,
                        sum_c.amount_21p as sum_future_plan_con,
                        --kosgu.kosgu,
                        cc.contract_rnk,
                        cc.contract_amount,
                        s.full_name as suppliername,
                        s.inn as supplierinn,
                        pp.detail_parent_id procedure_id_parent, --id родительской процецедуры
                        pr.id procedure_id,
                        l.id lot_id,
            case when cc.contract_rnk is not null then 'https://new.gz-spb.ru/#cm/contract/view/lot/' || l.id::text else null end link_contract,
                        'https://new.gz-spb.ru/#po/schedule/year/id/'||pl_big.schedule_id::text||'/type/all' link_pg,
                        'https://new.gz-spb.ru/#po/schedule/viewschedulespecialposition/position/'||pp.detail_parent_id||'/schedule/'||pl_big.schedule_id::text link_spec,
                        'https://new.gz-spb.ru/#po/schedule/viewdetailspecialposition/procedure/'||pr.id::text link_detailspec
                from contragents c --
                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 po_plan on c.id = po_plan.customer_org_id  --
                join po_schedules sch on po_plan.id = sch.plan_id--
                join po_lots pl_big     on sch.id = pl_big.schedule_id  --
                join lots l_big on pl_big.lot_id = l_big.id
                join po_procedures pp on l_big.procedure_id = pp.detail_parent_id
                join procedures pr on pp.procedure_id = pr.id
                join lots l     on l.procedure_id = pr.id

                left join procedure_steps ps_big        on ps_big.id = l_big.current_step --and ps.actual = true
                left join vocab_procedure_steps vps_big on vps_big.pseudo::text = ps_big.step_id::text and vps_big.actual = true

                --left join po_lots pl  on pl.lot_id = l.id
                --left join po_list_contract_single_supplier_reasons plc        on plc.code = pp.basis_for_single_supplier
                /*left join
                        (select code special_type, matend_full_name) special_type_pg, maprch_full_name) special_type_pz
                                from po_list_special_purchases
                                where actual = true
                                group by code
                        )plsp on plsp.special_type = pp.type_spec_purch */
                --left join vocab_procedure_types vpt   on vpt.id = pr.procedure_type
                left join procedure_steps ps    on ps.id = l.current_step --and ps.actual = true
                left join vocab_procedure_steps vps     on vps.pseudo::text = ps.step_id::text and vps.actual = true
                left join cm_contracts cc on l.id = cc.lot_id
                left join contragents s on s.id = cc.supplier_id
        left join
                /*(SELECT pp.procedure_id,
                                string_agg(distinct ppf.e_code, ','::text) AS kvr,
                                sum(case when pvtp.name = 2019 then ppf.amount else null end ) amount_19,
                                sum(case when pvtp.name = 2020 then ppf.amount else null end ) amount_20,
                                sum(case when pvtp.name = 2021 then ppf.amount else null end ) amount_21,
                                sum(case when pvtp.name > 2021 then ppf.amount else null end ) amount_21p
                        from po_plan_position pp
                        join    po_plan_position_finances ppf on pp.id = ppf.po_plan_position_id
                        join po_vocab_time_periods pvtp on pvtp.id = ppf.year
                        where ppf.actual = true and pp.actual = true
                        group by pp.procedure_id
                ) */ sum on sum.procedure_id = pr.id
         left join
                /*(SELECT pf.lot_id,
                                --string_agg(distinct ppf.e_code, ','::text) AS kvr,
                                sum(case when pvtp.name = 2019 then pf.amount else null end ) amount_19,
                                sum(case when pvtp.name = 2020 then pf.amount else null end ) amount_20,
                                sum(case when pvtp.name = 2021 then pf.amount else null end ) amount_21,
                                sum(case when pvtp.name > 2021 then pf.amount else null end ) amount_21p
                        from cm_contract_finances pf
                        join cm_contract_stages ccs     on ccs.id = pf.stage_id and ccs.actual = true
                        JOIN po_account pa ON pa.id = pf.po_account_id
--                        join po_exp_account pea       on pea.id = pa.po_exp_account_id
--                        JOIN gpo_po_exp_account gpea ON gpea.po_exp_code_id = pea.id
--                        JOIN gpo_exp_account gea ON gea.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
                        where pb.actual = true and pf.type = 1
                        group by pf.lot_id
                ) */ sum_c on sum_c.lot_id = cc.lot_id
                left join
                /* (SELECT pf.lot_id,
                                --string_agg(distinct ppf.e_code, ','::text) AS kvr,
                                sum(case when pvtp.name = 2019 then pf.amount else null end ) amount_19,
                                sum(case when pvtp.name = 2020 then pf.amount else null end ) amount_20,
                                sum(case when pvtp.name = 2021 then pf.amount else null end ) amount_21,
                                sum(case when pvtp.name > 2021 then pf.amount else null end ) amount_21p
                        from cm_contract_finances pf
                        join cm_contract_stages ccs     on ccs.id = pf.stage_id and ccs.actual = true
                        JOIN po_account pa ON pa.id = pf.po_account_id
--                        join po_exp_account pea       on pea.id = pa.po_exp_account_id
--                        JOIN gpo_po_exp_account gpea ON gpea.po_exp_code_id = pea.id
--                        JOIN gpo_exp_account gea ON gea.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
                        where pb.actual = true and pf.type = 2
                        group by pf.lot_id
                ) */ sum_p on sum_p.lot_id = cc.lot_id
        /*LEFT JOIN
                (SELECT pp.lot_id,
                  string_agg((pp.kvr || CASE WHEN (pp.rs IS NOT NULL) THEN (' - '::text || pp.rs) ELSE (' - '::text || pp.kosgu) END), ','::text) AS kosgu
                         FROM (SELECT DISTINCT pf.lot_id,
                                                                kvr_1.kvr,
                                                                kvr_1.kosgu,
                                                                kvr_1.rs
                                                         FROM cm_contract_finances pf
                                                                 LEFT JOIN po_account pa ON pa.id = pf.po_account_id
                                                                 LEFT JOIN
                                                                        ( SELECT pea.id AS po_exp_code_id,
                                                                                                string_agg((gea.e_code)::text, ','::text) AS kvr,
                                                                                                string_agg((gea.kosgu)::text, ','::text) AS kosgu,
                                                                                                string_agg((
                                                                                                                CASE
                                                                                                                                WHEN (length((pea.po_exp_code)::text) > 3) THEN pea.po_exp_code
                                                                                                                                ELSE NULL::character varying
                                                                                                                END)::text, ','::text) AS rs
                                                                                         FROM po_exp_account pea
                                                                                                 JOIN gpo_po_exp_account gpea ON gpea.po_exp_code_id = pea.id
                                                                                                 JOIN gpo_exp_account gea ON gea.id = gpea.gpo_exp_account_id
                                                                                        GROUP BY pea.id
                                                                        ) kvr_1 ON kvr_1.po_exp_code_id = pa.po_exp_account_id
                                                                 LEFT JOIN po_budget pb ON pb.id = pa.po_budget_id
                                                        WHERE pb.actual = true
                                        ) pp
                        GROUP BY pp.lot_id
                ) kosgu ON kosgu.lot_id = l.id*/
        where --($X{IN, c_grbs.id::int, grbs})
            --and ($X{IN, c.id::int, pbs})
                l.actual = true and pr.actual = true --and pr.id =      1668915
                and l.id is not null
                and c.actual = true
                and pp.detail_parent_id is not null
                and sch.plan_number like '2020%'
        --order by c.full_name, pp.order_number
    --key_org_sec
    --key_grbs
    and c.id --key_pbs