Advertisement
GLASHATAY_007

Untitled

Apr 19th, 2023
99
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH expertize AS (
  2. SELECT e.procedure_id
  3.   FROM po_procedure_expertize e
  4.  GROUP BY e.procedure_id
  5. HAVING date_part('year', MAX(CASE WHEN e.boss_id IS NOT NULL THEN e.expertize_date_plan ELSE NULL END)) IN (2023) --Ограничение по плановому году экспертизы в УО
  6. ORDER BY procedure_id
  7. ),
  8.  
  9.                
  10. -- НМЦК
  11. nmck AS (
  12. SELECT l.procedure_id,
  13. SUM(start_price) AS nmсk
  14. FROM procedures pr
  15. JOIN lots l On l.procedure_id = pr.id
  16. WHERE pr.id in (SELECT * FROM expertize)
  17. GROUP BY l.procedure_id)
  18.  
  19. -- Вспомогательная таблица
  20. , date_c AS (
  21. SELECT pr.id, record_id,
  22.                date date_all,
  23.                      ROW_NUMBER() OVER (PARTITION BY record_id ORDER BY date)  AS rating,
  24.                      psto.step_id
  25.         FROM (
  26.         SELECT date,
  27.                      CASE WHEN "from"='NULL' THEN null ELSE "from"::integer END "from",
  28.                      CASE WHEN  "to"='NULL' THEN null ELSE "to"::integer END "to",
  29.                      lh.record_id
  30.         FROM lots_history lh
  31.         WHERE lh.record_id IN (SELECT DISTINCT l.id
  32.             FROM expertize e
  33.             LEFT JOIN lots l ON l.procedure_id=e.procedure_id)
  34.         AND lh.field = 'current_step'
  35.         ORDER BY date
  36.         ) a
  37.         LEFT JOIN procedure_steps psfrom on psfrom.id  = a.from
  38.         LEFT JOIN procedure_steps psto on psto.id  = a.to
  39.         JOIN lots l on l.id = a.record_id
  40.     JOIN procedures pr on l.procedure_id = pr.id
  41.         WHERE (psto.step_id = 'expertize_complete' OR
  42.                     psto.step_id = 'assign_expert_uo' OR
  43.                     psto.step_id = 'recall_request_review' OR
  44.                     psto.step_id = 'wait_expert_uo')
  45.                     AND
  46.                     (case when date_part('year', date::date) < 2023 and psto.step_id = 'assign_expert_uo' then 1
  47.                           when date_part('year', date::date) >= 2023 and psto.step_id = 'wait_expert_uo' then 1
  48.                                 when psto.step_id = 'recall_request_review' then 1
  49.                                 when psto.step_id = 'expertize_complete' then 1
  50.                                 else 0 end) = 1
  51.         ORDER BY record_id, date),
  52.  
  53. -- Подсчет количества рабочих дней нахождения заявки на экспертизе/редактировании
  54. date_count AS (
  55. SELECT record_id lot_id,
  56.              SUM(exp_w_d) s_exp_wd, -- Общее количество рабочих дней нахождения заявки на экспертизе
  57.              SUM(red_w_d) s_red_wd, -- Общее количество рабочих дней нахождения заявки на доработке
  58.              MIN(case when step_id in ('assign_expert_uo','wait_expert_uo' ) then a.date_all else null end) min_date_uo,
  59.              MAX(case when step_id in ('assign_expert_uo','wait_expert_uo' ) then a.date_all else null end) max_date_uo,
  60.              COUNT(case when step_id in ('assign_expert_uo','wait_expert_uo' ) then a.date_all else null end) count_date_uo
  61.              FROM (
  62. SELECT a.record_id,
  63.             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_
  64.                                                                      BETWEEN a.date_all::date + INTERVAL  '1 day' AND b.date_all::date)  ELSE null END exp_w_d,
  65.             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_
  66.                                                                      BETWEEN a.date_all::date + INTERVAL  '1 day' AND b.date_all::date)  ELSE null END red_w_d,
  67.                 a.date_all, a.step_id                                        
  68. FROM date_c a
  69. 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
  70. JOIN lots l on l.id = a.record_id
  71. JOIN procedures pr on l.procedure_id = pr.id
  72. --WHERE b.record_id IS NOT NULL
  73. )a
  74. GROUP BY record_id),
  75.  
  76. kbk AS (
  77. SELECT distinct l.id lid,
  78.     dea.grbs_code
  79.     ||dea.section_code
  80.     ||dea.article_code
  81.     ||dea.e_code
  82.     ||dea.kosgu kbk
  83. FROM expertize e
  84. JOIN lots l on e.procedure_id = l.procedure_id
  85. JOIN po_finances pf On (pf.lot_id = l.id)
  86. JOIN po_account pa On (pa.id = pf.po_account_id)
  87. JOIN po_budget pb On (pb.id = pa.po_budget_id And pb.actual = true )
  88. JOIN po_vocab_time_periods pvtp On (pvtp.id = pb.po_period_id)
  89. JOIN po_exp_account pea On (pea.id = pa.po_exp_account_id)
  90. JOIN (
  91.             Select min(gpo_budget_type_code) gpo_budget_type_code,budget_type
  92.             From gpo_vocab_budget_links
  93.             Where actual Is True
  94.             Group by budget_type
  95.  )gvbl On ((gvbl.budget_type)::text = (pea.budget_type)::text)
  96. JOIN gpo_list_budget glb On ((glb.code)::text = (gvbl.gpo_budget_type_code)::text)
  97. LEFT JOIN po_vocab_time_periods pvtp_a On (pvtp_a.id = pb.po_accept_period_id)
  98. LEFT JOIN gpo_po_exp_account gpea On (gpea.po_exp_code_id = pea.id)
  99. LEFT JOIN gpo_exp_account dea On (dea.id = gpea.gpo_exp_account_id))
  100.  
  101.  
  102.  
  103.        
  104. SELECT pr.id AS id_procedure,
  105.              c.full_name, --заказчик
  106.              pr.title AS purchase,
  107.              n.nmсk,
  108.              date_count.min_date_uo min_date,
  109.              date_count.max_date_uo max_date,
  110.              date_count.count_date_uo,
  111.              date_count.s_exp_wd,
  112.              date_count.s_red_wd,
  113.              kbk.kbkc
  114. FROM expertize exp
  115. JOIN procedures pr ON exp.procedure_id = pr.id
  116. JOIN lots l ON l.procedure_id = pr.id
  117. Inner Join public.po_lots pl on pl.lot_id = l.id
  118. JOIN lot_customers lc ON lc.lot_id = l.id AND lc.actual = TRUE
  119. JOIN contragents c ON c.id = lc.customer_id
  120. LEFT JOIN nmck n ON n.procedure_id = pr.id
  121. LEFT JOIN date_count ON date_count.lot_id=l.id
  122. LEFT JOIN kbk ON kbk.lid=l.id
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement