set odps.sql.type.system.odps2=true;
set odps.sql.hive.compatible=true;


select p2.ds 
       ,p2.l2_dept_name
       ,p2.l3_dept_name
       ,p2.l4_dept_name
       ,case when p2.l2_dept_name = '医疗行业线' and p2.l4_dept_name is null
             then case when p2.l3_dept_name = '西南大区' then '正堂'
                       when p2.l3_dept_name = '华东大区' then '杰豹'
                       when p2.l3_dept_name = '华北大区' then '云轩'
                       when p2.l3_dept_name = '医疗SKA' then '贺然'
                       else '瑾云' end 
             when p2.l2_dept_name = '教培行业线' and p2.l4_dept_name is null
             then case when p2.l3_dept_name = '华北大区' then '晓鸟'
                       when p2.l3_dept_name = '华南大区' then '侨木'
                       when p2.l3_dept_name = 'KA南区'  then '漂泊'
                       when p2.l3_dept_name = 'KA北区'  then '度白'
                       else '龙浦' end 
             else p3.管理 end as 管理
       ,p2.employee_name
       ,p2.ali_code
       ,p2.employee_type
       ,p2.role_name
       ,p2.role_type
       ,p2.remark
       ,p2.hire_months
       ,p2.hire_days
       ,p2.leave_date
       ,p2.hire_date
       ,p2.is_leave_thismonth
       ,p2.is_hire_thismonth
       ,p2.is_inservice_thismonth    
       ,0 as hc    
  from
       (
        select substring(ds,1,6) as smonth
               ,l2_dept_name
               ,l3_dept_name
          from kbreport.dim_gd_c33_employee_info_ds
         where ds <= MAX_PT('kbreport.dim_gd_c33_employee_info_ds')
           and ds >= '20260401'
           and l1_dept_name in ('医疗&教培业务部','美业&医疗&教培业务部')
           and l2_dept_name <> '销售运营'
           and team_type = '直营'
           and is_leave = 0 
         group by substring(ds,1,6)
               ,l2_dept_name
               ,l3_dept_name 
       ) p1 
 inner join
       (
        select date_format(date_add(to_date(ds,'yyyyMMdd'),-1),'yyyyMMdd') as ds 
               ,l2_dept_name
               ,l3_dept_name
               ,l4_dept_name
               ,employee_name
               ,ali_code
               ,employee_type
               ,role_name
               ,role_type
               ,remark
               ,hire_months
               ,hire_days
               ,date_format(DATE_ADD(leave_date,-1),'yyyyMMdd')                                                                                          as leave_date
               ,date_format(hire_date,'yyyyMMdd')                                                                                                        as hire_date
               ,case when substring(date_format(DATE_ADD(leave_date,-1),'yyyyMMdd'),1,6) = substring(date_format(date_add(to_date(ds,'yyyyMMdd'),-1),'yyyyMMdd'),1,6) then 1 else 0 end                           as is_leave_thismonth             
               ,case when substring(date_format(hire_date,'yyyyMMdd')              ,1,6) = substring(date_format(date_add(to_date(ds,'yyyyMMdd'),-1),'yyyyMMdd'),1,6) then 1 else 0 end                           as is_hire_thismonth             
               ,case when substring(date_format(DATE_ADD(leave_date,-1),'yyyyMMdd'),1,6) = substring(date_format(date_add(to_date(ds,'yyyyMMdd'),-1),'yyyyMMdd'),1,6) or leave_date is     null then 1 else 0 end as is_inservice_thismonth             
          from kbreport.dim_gd_c33_employee_info_ds
         where ds <= MAX_PT('kbreport.dim_gd_c33_employee_info_ds')
           and ds >= '20260401'         
           and l1_dept_name in ('医疗&教培业务部','美业&医疗&教培业务部')
           and coalesce(date_format(DATE_ADD(leave_date,-1),'yyyyMMdd'),'未离职') <> date_format(hire_date,'yyyyMMdd')    
       ) p2 
    on p1.smonth = substring(p2.ds,1,6) 
   and coalesce(p1.l2_dept_name,0) = coalesce(p2.l2_dept_name,0)  
   and coalesce(p1.l3_dept_name,0) = coalesce(p2.l3_dept_name,0)
  left join 
       ( -- 管理
        select substring(ds,1,6) as smonth
               ,l2_dept_name
               ,l3_dept_name
               ,l4_dept_name
               ,max(cm_nick_name) as 管理
          from kbreport.dim_gd_c33_employee_info_ds
         where ds <= MAX_PT('kbreport.dim_gd_c33_employee_info_ds')
           and ds >= '20260401'
           and l1_dept_name in ('医疗&教培业务部','美业&医疗&教培业务部')
           and l2_dept_name <> '销售运营'
           and is_leave = 0 
         group by substring(ds,1,6)
               ,l2_dept_name
               ,l3_dept_name
               ,l4_dept_name          
       ) p3 
    on substring(p2.ds,1,6) = p3.smonth 
   and coalesce(p2.l2_dept_name,'1') = coalesce(p3.l2_dept_name,'1')  
   and coalesce(p2.l3_dept_name,'1') = coalesce(p3.l3_dept_name,'1')
   and coalesce(p2.l4_dept_name,'1') = coalesce(p3.l4_dept_name,'1')

 union all -- hc 

select p1.ds 
       ,p1.l2_dept_name
       ,p1.l3_dept_name
       ,p1.l4_dept_name
       ,case when p1.l2_dept_name = '医疗行业线' and p1.l4_dept_name is null
             then case when p1.l3_dept_name = '西南大区' then '正堂'
                       when p1.l3_dept_name = '华东大区' then '杰豹'
                       when p1.l3_dept_name = '华北大区' then '云轩'
                       when p1.l3_dept_name = '医疗SKA' then '贺然'
                       else '瑾云' end 
             when p1.l2_dept_name = '教培行业线' and p1.l4_dept_name is null
             then case when p1.l3_dept_name = '华北大区' then '晓鸟'
                       when p1.l3_dept_name = '华南大区' then '侨木'
                       when p1.l3_dept_name = 'KA南区'  then '漂泊'
                       when p1.l3_dept_name = 'KA北区'  then '度白'
                       else '龙浦' end 
             else p1.管理 end as 管理
       ,null as employee_name
       ,null as ali_code
       ,null as employee_type
       ,null as role_name
       ,null as role_type
       ,null as remark
       ,null as hire_months
       ,null as hire_days
       ,null as leave_date
       ,null as hire_date
       ,null as is_leave_thismonth
       ,null as is_hire_thismonth
       ,null as is_inservice_thismonth  
       ,p2.hc_all   
  from 
       (
        select ds 
               ,l2_dept_name
               ,l3_dept_name
               ,l4_dept_name
               ,max(cm_nick_name) as 管理
          from kbreport.dim_gd_c33_employee_info_ds
         where ds <= MAX_PT('kbreport.dim_gd_c33_employee_info_ds')
           and ds >= '20260401'
           and l1_dept_name in ('医疗&教培业务部','美业&医疗&教培业务部')
           and team_type = '直营'
           and l2_dept_name <> '销售运营'
           and is_leave = 0 
         group by ds 
               ,l2_dept_name
               ,l3_dept_name
               ,l4_dept_name   
       ) p1 
  left join 
       (
        select l2_dept_name
               ,l3_dept_name
               ,case when l3_dept_name in ('教培SKA','KA北区','KA南区','医疗SKA','严肃医疗部') then null else l4_dept_name end as l4_dept_name
               ,calmonth
               ,hc_all 
          from kbreport.dim_kpi_hc  
       ) p2 
    on substring(p1.ds,1,6) = p2.calmonth 
   and coalesce(p1.l2_dept_name,'1') = coalesce(p2.l2_dept_name,'1')  
   and coalesce(p1.l3_dept_name,'1') = coalesce(p2.l3_dept_name,'1')
   and coalesce(p1.l4_dept_name,'1') = coalesce(p2.l4_dept_name,'1')