select a.employee_number as x_employee_number, a.name as x_employee_name, case g.name when '正式员工' then '正式员工' when '兼职人员' then '兼职人员' when 'Rehiring retiree' then '退休返聘' when 'Outsourcing' then '编外人员' when '实习生' then '实习生' end as x_employee_type_rep, c.name as x_department_name, b_2.name as x_dep_manager, b_1.name as x_parent, d.name as x_job_name, a.hiredate as x_hiredate, case a.contract_id when a.contract_id THEN '是' ELSE '否' END as x_if_contract, case a.contract_id when a.contract_id THEN '是' ELSE '' END as x_contract_category, null as x_date_end, a.hiredate + INTERVAL'1 month' as x_late_date, case a.date_end when a.date_end THEN '0' ELSE 30-to_number(to_char(now()-a.hiredate+ interval '1 days','dd'), '999999999') END as x_contract_rest_days from hr_employee as a left join hr_department c on a.department_id=c.id left join hr_employee b_2 on c.manager_id=b_2.id left join hr_employee b_1 on a.parent_id=b_1.id left join hr_job d on a.job_id=d.id left join hr_employee_type g on g.id = a.employee_type_rep where a.active=True and a.work_activity='in_service' and a.contract_id isnull and a.id not in ( select DISTINCT employee_id from hr_contract where active=True and state='done' ) union select a.employee_number as x_employee_number, a.name as x_employee_name, case g.name when '正式员工' then '正式员工' when '兼职人员' then '兼职人员' when 'Rehiring retiree' then '退休返聘' when 'Outsourcing' then '编外人员' when '实习生' then '实习生' end as x_employee_type_rep, c.name as x_department_name, b_2.name as x_dep_manager, b_1.name as x_parent, d.name as x_job_name, a.hiredate as x_hiredate, case a.contract_id when a.contract_id THEN '是' ELSE '否' END as x_if_contract, case f.name when 'Labor Contract' then '劳动合同' when 'None Disclosure Agreement' then '保密协议' when 'Competitive Restriction' then '竞业协议' when 'Service Agreement' then '劳务协议' when '兼职协议' then '兼职协议' end as x_contract_category, hc.date_end as x_date_end, case hc.date_end when hc.date_end then hc.date_end + INTERVAL'1 month' else a.hiredate + INTERVAL'1 month' end as x_late_date, case a.date_end when a.date_end THEN '0' ELSE 30-to_number(to_char(now()-a.hiredate+ interval '1 days','dd'), '999999999') END as x_contract_rest_days from hr_employee as a left join hr_department c on a.department_id=c.id left join hr_employee b_2 on c.manager_id=b_2.id left join hr_employee b_1 on a.parent_id=b_1.id left join hr_job d on a.job_id=d.id left join hr_contract hc on a.id in (select employee_id from hr_contract where date_end=( select max(date_end) from hr_contract where active=True and state='done') ) left join hr_contract_agreement_category f on hc.contract_agreement_id=f.id left join hr_employee_type g on g.id = a.employee_type_rep where a.active=True and a.work_activity='in_service' and hc.date_end= (select max(date_end) from hr_contract where active=True and state='done') and hc.date_end < now() and hc.active=True and hc.state='done'