with projects as (
    select distinct
           p.project_id,
           p.project_version_id,
           p.name as project_name,
           p.status_id,
           p.category_id,
           spc.name as category_name,
           p.project_date,
           sp_pj.short_name AS project_status,
           case
               when (now()::date > p.project_date) and (p.status_id <> 370)
               then now()::date - p.project_date
               else null
           end as project_delay,
           p.date_start,
           p.date_end,
           p.project_result,
           p.result_value_plan,
           p.result_value_fact,
           p.measure_unit_id,
           spm.name as measure_unit_name,
           p.is_deadline_missed_risk,
           p.lead_executor_id,
           null::text as lead_executor_name,
           ord.order_number,
           sp_vpm.name as vice_prime_minister_name,
           ord_r.deadline_postpone_count,
           ord_r.due_date,
           ord_r.current_deadline,
           ord_r.short_name,
           ord_r.checkpoint_count,
           sp_ord_rs.short_name as order_result_status,
           spp.name as order_problem,
           c.name as ct_name,
           c.checkpoint_type_id,
           spct.name as ct_category_name,
           c.plan_end_date,
           c.new_end_date,
           (c.new_end_date - c.plan_end_date) as ct_delay,
           ord.order_id,
           ord.order_version_id,
           sp_os.short_name as order_status,
           ord.full_description,
           ord.curator_vpm_id as filter_vice_prime_minister,
           ord.curator_department_id as filter_department,
           ord.federal_district_id as filter_district,
           ord.federal_subject_id as filter_subject,
           null::numeric as fin_value,
           fe.event_date as field_event_date,
           count (fe.event_date) as field_event_count,
           ord.order_date as first_order_date,
           ord.short_description as first_order_description,
           ord.has_doc,
           ord.document_link
      from order_control.project p
      left join order_control.order ord
        on ord.project_id = p.project_id
       and not ord.obsolete
      left join order_control.sp_order_status sp_os
        on ord.status_id = sp_os.id
       and not sp_os.obsolete
      left join order_control.sp_project_status sp_pj
        on p.status_id = sp_pj.id
       and not sp_pj.obsolete
      left join order_control.order_problem ord_p
        on ord.order_id = ord_p.order_id
       and not ord_p.obsolete
      left join order_control.sp_problem spp
        on ord_p.problem_id = spp.id
       and not spp.obsolete
      left join order_control.order_executor ord_e
        on ord_e.order_id = ord.order_id
       and not ord_e.obsolete
      left join order_control.sp_vice_prime_minister sp_vpm
        on ord_e.vice_prime_minister_id = sp_vpm.id
       and not sp_vpm.obsolete
      left join order_control.field_event fe
        on ord.order_id = fe.order_id
       and not fe.obsolete
      left join order_control.sp_category spc
        on spc.id = p.category_id
       and not spc.obsolete
      left join sp_measure_unit spm
        on p.measure_unit_id = spm.id
       and not spm.obsolete
      left join order_control.order_result ord_r 
        on ord_r.project_id = p.project_id
       and ord_r.order_id = ord.order_id
       and not ord_r.obsolete
      left join order_control.sp_order_result_status sp_ord_rs
        on ord_r.status_id = sp_ord_rs.id
       and not sp_ord_rs.obsolete
      left join order_control.checkpoint c
        on c.project_id = p.project_id
       and c.order_id = ord.order_id
       and not c.obsolete
      left join sp_checkpoint_type spct
        on c.checkpoint_type_id = spct.id
       and not spct.obsolete
     where not p.obsolete
     group by project_id, order_id
) 
select * from projects;