Untitled

-- DROP PROCEDURE master.prc_empl_out_update(in varchar, in varchar, inout varchar);

CREATE OR REPLACE PROCEDURE master.prc_empl_out_update(p_user_id character varying, p_empl_out_id character varying, INOUT o_result character varying DEFAULT 'SUCCESS'::character varying)
 LANGUAGE plpgsql
AS $procedure$

declare 

	v_end_date date;
	v_latest_att date;
	v_company_id varchar;

begin

	select end_date, company_id into v_end_date, v_company_id
	from master.empl_outsource where empl_out_id=p_empl_out_id;

	select "date" into v_latest_att
	from "transaction".trx_att_daily tad where empl_id =p_empl_out_id
	order by "date" desc 
	limit 1 ;

	update eksad_auth."user" 
	set company_id = v_company_id,
		is_active = case when (v_end_date > current_date)  then 1 else is_active end ,
		modified_dt = now(),
		modified_by = p_user_id
	where empl_id = p_empl_out_id ;  

	if v_latest_att < current_date and v_end_date > current_date then 
		
	INSERT INTO "transaction".trx_att_daily
	(company_id,company_group_id , empl_id, "date", work_shift_id, status, created_by, created_date, work_in, work_out, break_time, work_location_id, att_status, att_status_category,"day", work_shift_in, work_shift_out, work_shift_name, time_type_id, company_code, empl_name, nip, org_1, org_2, org_3, job_id, job_code, job_title, level_id, level_code, level_description, location_id, location_name, location_group_id, location_group_name, grade_id, grade, empl_status_id, empl_status)
	select 
	  distinct a.company_id, 
	  a.company_group_id ,
	  a.empl_id, 
	  a.last_att as date, 
	  case
		  when b.work_shift_id is not null and b.work_shift_id !='F' then b.work_shift_id 
		  when b.work_shift_id is not null and b.work_shift_id ='F' then  c.work_shift_default_id 
		  when b.work_shift_id is null and c.work_shift_default_id is not null then c.work_shift_default_id 
		  else d.work_shift_id 
	  end work_shift_id, 
	  case when c.work_shift_type_id = 'WORKSTYPE-001' and e.holiday_type_id ='HOLYTYPE_1' then 'ATT_LIBUR_NATIONAL'
	  	when c.work_shift_type_id = 'WORKSTYPE-001' and e.holiday_type_id ='HOLYTYPE_2' then 'ATT_LIBUR' 
	  	when c.work_shift_type_id = 'WORKSTYPE-001' and e.holiday_type_id ='HOLYTYPE_3' and e.location_type_id='HOLYLOC_1' then 'ATT_LIBUR'
	  	when c.work_shift_type_id = 'WORKSTYPE-001' and e.holiday_type_id ='HOLYTYPE_3' and e.location_type_id='HOLYLOC_2' and a.location_id=f.location_id then 'ATT_LIBUR'
	  	when c.work_shift_type_id = 'WORKSTYPE-001' and e.location_type_id = 'HOLYLOC_2' and f.location_id = a.location_id then 'ATT_LIBUR' 
	  	when c.work_shift_type_id = 'WORKSTYPE-002' and b.work_shift_id ='F' then 'ATT_LIBUR'
	  	when h.status = 'OFF' then 'ATT_LIBUR'
		else 'ATT_MASUK'
	  end status, 
	  p_user_id as created_by, 
	  now() as created_date, 
	  h.work_in, 
	  h.work_out, 
	  h.break as break_time ,
	  case when (c.work_shift_type_id = 'WORKSTYPE-002' and b.work_shift_id ='F') then 'WFH'
	  	when h.status = 'OFF' then 'WFH'
	  else j.work_location_id end work_location_id,
	  case when c.work_shift_type_id = 'WORKSTYPE-001' and e.holiday_type_id ='HOLYTYPE_1' then 'ATT_LIBUR_NATIONAL'
	  	when c.work_shift_type_id = 'WORKSTYPE-001' and e.holiday_type_id ='HOLYTYPE_2' then 'ATT_LIBUR' 
	  	when c.work_shift_type_id = 'WORKSTYPE-001' and e.holiday_type_id ='HOLYTYPE_3' and e.location_type_id='HOLYLOC_1' then 'ATT_LIBUR'
	  	when c.work_shift_type_id = 'WORKSTYPE-001' and e.holiday_type_id ='HOLYTYPE_3' and e.location_type_id='HOLYLOC_2' and a.location_id=f.location_id then 'ATT_LIBUR'
	  	when c.work_shift_type_id = 'WORKSTYPE-001' and e.location_type_id = 'HOLYLOC_2' and f.location_id = a.location_id then 'ATT_LIBUR' 
	  	when c.work_shift_type_id = 'WORKSTYPE-002' and b.work_shift_id ='F' then 'ATT_LIBUR'
	  	when h.status = 'OFF' then 'ATT_LIBUR'
	  	when k.att_condition_id is not null then 'ATT_HADIR'
		else 'ATT_TIDAK_HADIR'
	  end att_status,
	  case when k.att_condition_id is not null then 'BUSINESS_TRIP'
	  	when g.att_condition_id is not null then 'CUTI'
	  	end att_status_category,
	  trim(to_char(current_date + 1 ,'Day')) as day,
	  case when h.work_in is not null then concat(current_date + 1, ' ',h.work_in)::timestamp end as work_shift_in,
	  case when h.work_out is not null and h.work_out < h.work_in then concat(a.last_att + 1, ' ',h.work_out)::timestamp 
	  		when h.work_out is not null and h.work_out > h.work_in then concat(a.last_att, ' ',h.work_out)::timestamp 
	  end as work_shift_out,
	  h.work_shift_name ,
	  h.time_type_id ,
	  l.company_code ,
	  a.empl_name ,
	  a.nip ,
	  org2.name_3 AS org_1,
	  org2.name_4 AS org_2,
	  org2.name_5 AS org_3,
	  a.job_id ,
	  m.job_code ,
	  m.job_title ,
	  a.empl_level_id as level_id,
	  n.level_code,
	  n.description as level_description,
	  a.location_id ,
	  o.location_name ,
	  o.location_group_id ,
	  p.location_group_name ,
	  a.grade_id,
	  a.grade,
	  a.empl_status_id,
	  q.value as empl_status
	from 
	  (
	    select 
      a.company_id, 
      a.company_group_id ,
      a.empl_id, 
      a.job_id, 
      a.status, 
      a.end_date, 
      a.location_id ,
      a.empl_name,
      a.nip,
      a.position_id,
      a.grade_id,
      a.empl_level_id,
      a.empl_status_id ,
      b.grade ,
      generate_series(v_latest_att::date+1,current_date, '1 day'::interval)::date as last_att
    from master.empl a
    left join master.mst_grade b on a.grade_id =b.grade_id 
    union 
    select 
      a.company_id, 
      a.company_group_id ,
      a.empl_out_id, 
      a.job_id, 
      a.status, 
      a.end_date, 
      a.location_id ,
      a.empl_name,
      a.nip,
      a.position_id,
      null as grade_id,
      a.empl_level_id,
      'EMPLSTS_8' as empl_status_id,
      null as grade,
      generate_series(v_latest_att::date+1,current_date, '1 day'::interval)::date as last_att
    from master.empl_outsource a 
	  ) a 
	  left join master.mst_work_shift_schedule b on a.empl_id = b.empl_id and a.last_att = b."date" 
	  left join (
	    select 
	      a.work_assignment_id, 
	      a.work_shift_type_id, 
	      a.work_shift_default_id, 
	      b.job_id, 
	      c.location_id 
	    from master.mst_work_assignment a 
	      join master.mst_job b on a.company_id = b.company_id and b.status = 'Active' 
	      join master.mst_position c on a.company_id = c.company_id and b.job_id=c.job_id and c.status = 'Active' 
	    where a.job_id = 'ALL' and a.location_id = 'ALL' 
	    union all 
	    select 
	      a.work_assignment_id, 
	      a.work_shift_type_id, 
	      a.work_shift_default_id, 
	      b.job_id, 
	      c.location_id 
	    from master.mst_work_assignment a 
	      join master.mst_job b on a.company_id = b.company_id and a.job_id = b.job_id and b.status = 'Active' 
	      join master.mst_position c on a.company_id = c.company_id and b.job_id=c.job_id and c.status = 'Active' 
	    where a.job_id != 'ALL' and a.location_id = 'ALL' 
	    union all 
	    select 
	      a.work_assignment_id, 
	      a.work_shift_type_id, 
	      a.work_shift_default_id, 
	      b.job_id, 
	      c.location_id 
	    from master.mst_work_assignment a 
	      join master.mst_job b on a.company_id = b.company_id and b.status = 'Active' 
	      join master.mst_position c on a.company_id = c.company_id and b.job_id = c.job_id and a.location_id=c.location_id and c.status = 'Active' 
	    where a.job_id = 'ALL' and a.location_id != 'ALL' union all 
	    select 
	      a.work_assignment_id, 
	      a.work_shift_type_id, 
	      a.work_shift_default_id, 
	      a.job_id, 
	      a.location_id 
	    from master.mst_work_assignment a 
	    where a.job_id != 'ALL' and a.location_id != 'ALL') c on a.job_id = c.job_id and a.location_id = c.location_id 
	  left join master.mst_work_shift d on a.company_id = d.company_id and d.work_shift_type_id = 'WORKSTYPE-001' and d.is_lock = 1 
	  left join master.mst_holiday e on a.company_id = e.company_id and e.holiday_date = a.last_att
	  left join master.mst_holiday_location f on e.holiday_id = f.holiday_id 
	  left join "transaction".trx_att_condition g on a.empl_id = g.empl_id and a.last_att  = g."date" and g.category_id = 'LEAVE' and g.decision ='Approved'
	  left join master.mst_work_shift_schedule_log h on h.work_shift_id = (
	    case when b.work_shift_id is not null and b.work_shift_id !='F' then b.work_shift_id 
		  when b.work_shift_id is not null and b.work_shift_id ='F' then  c.work_shift_default_id 
		  when b.work_shift_id is null and c.work_shift_default_id is not null then c.work_shift_default_id 
		  else d.work_shift_id 
	  end 
	  ) 
	  and h.day = trim(to_char(a.last_att, 'Day')) and h."date" = a.last_att
	  left join master.mst_work_wfh_schedule j on a.empl_id=j.empl_id and a.last_att = j.date
	  left join "transaction".trx_att_condition k on a.empl_id = k.empl_id and a.last_att= k."date" and k.category_id = 'BUSINESS' and k.decision ='Approved'
	  join master.mst_company l on a.company_id =l.company_id 
	  left JOIN master.mst_position org1 ON org1.position_id::text = a.position_id::text
	  LEFT JOIN master.mst_org_template_unit org2 ON org2.org_template_id::text = org1.org_template_id::text AND org2.parent_code::text = org1.parent_code::text
	  left join master.mst_job m on a.job_id =m.job_id 
	  left join master.mst_level n on a.empl_level_id =n.level_id 
	  left join master.mst_location o on a.location_id =o.location_id 
	  left join master.mst_location_group p on o.location_group_id =p.location_group_id 
	  left join master.adm_param_value q on a.empl_status_id=q.param_value_id 
	  where --a.status='Active' and (a.end_date is null or a.end_date >= current_date + 1 ) 
	  a.empl_id=p_empl_out_id;
	

	INSERT INTO "transaction".trx_att_daily_photo
	(company_group_id, company_id, empl_id, att_daily_id, "date", status, created_by, created_date)
	select a.company_group_id , a.company_id , a.empl_id , a.att_daily_id , a."date" , 'Active', p_user_id, now()
	from "transaction".trx_att_daily a 
	where a.empl_id =p_empl_out_id
	and a."date" between v_latest_att::date+1 and current_date;    
	
	o_result := 'SUCCESS';

end if;
		
END; 
$procedure$
;