/*
交接说明：直营销售收入与目标明细汇总 ODPS SQL

一、脚本用途
目标达成看板

三、最终输出粒度
最终结果以 union all 汇总多类明细行，核心字段包括：
- 日期：ds、当前日期、当月天数、时间进度。
- 业务维度：行业线、二级类目、三级类目、PID、门店ID。
- 指标：新签收入/目标、新签年费/目标、新签门店/目标、新签消耗/目标、
  品牌广告目标、存量收入目标、全部收入目标、续签、断约回签、广告首充、
  非标收入、cps收入、全部广告消耗等。
- 人员维度：工号、名字、骨干标签、入职月份分层、部门层级、团队类型、
  员工类型、角色类型、是否展业、离职/入职日期、base地、CM名字、小组长、管理。

四、主要处理逻辑
1. tmp_gd_sign_employee_income_detail CTE 先统一收入和消耗口径：
   - 从 kbreport.dwd_gd_sign_employee_income_detail_ds 汇总新签/续签/断约回签、
     广告首充、非标收入、CPS 收入，过滤全 0 记录。
   - 从 kbreport.dwd_gd_ad_operate_detail_di 计算“新签消耗”，按广告归属关系匹配销售人员。
     2026-03-31 及以前使用行业差异化消耗归因窗口；2026-04-01 起统一按首充后 6 个月内归因。
   - 计算“首充二充封顶金额”，用于后续医疗行业新签消耗封顶。
   - 计算“存量&全部广告消耗”，按月归属关系和类目映射补齐行业类目。

2. 主查询把收入/消耗明细与人员组织表关联：
   - 人员信息来自 kbreport.dim_gd_c33_employee_info_ds，取每月人员最新记录。
   - 只保留 team_type = '直营'，并排除销售运营、医疗/教培商家成长等部门。
   - 教培SKA 在 2026-05-01 起统一归并展示为“教培KA”。
   - 骨干标签和是否展业来自 kbreport.dim_gd_sales_ylmyjp。

3. 新签收入计算口径：
   新签收入 = 新签年费 + 非标收入 + 非KA新签消耗 + 2026年1-3月部分行业广告首充计入金额。
   - KA 角色在 2026年1-3月不计广告首充激励。
   - 非KA 角色 2026年1-3月按行业和月份使用不同广告首充系数：
     2026-01：美业 50%，医疗 20%，教培 50%；
     2026-02：美业 40%，教培 40%；
     2026-03：美业 30%，教培 30%。
   - 医疗行业/医疗行业线的新签消耗会按 PID + 门店ID 做累计，并受首充二充封顶金额限制。

4. 补齐行逻辑：
   - KPI 目标来自 kbreport.dim_kpi_hc，按人员和月份补齐目标字段，实际指标置 0。
   - 人员花名册补齐行用于保证无业绩人员也能出现在结果中，指标字段置 0。
   - 当天订单补充来自 gd_info_alake.dwd_gd_info_sign_c3_order_sign_di，
     用于把当天成功支付的新签年费、广告、非标订单并入明细。

五、接手维护时优先检查的硬编码
- 数据起始日期：多处写死为 20251001、20260401。
- 月度广告归属快照：20260402、20260507、20260602、20260702。
- 类目映射版本：autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2 的 modified_date = '20260331'。
- 角色判断：通过 role_type rlike 'KA' 区分 KA/非KA。
- 部门排除名单和“教培SKA -> 教培KA”的归并规则。
- 当天订单补充使用 substring(pay_time,1,10) = TO_DATE(NOW())，如果跑历史回补需要改这个口径。
*/


set odps.sql.select.output.showcolumntype=true;
set odps.sql.type.system.odps2=true;
set odps.sql.hive.compatible=true;
with tmp_gd_sign_employee_income_detail as (

select ds
       ,ali_code
       ,sale_srvline
       ,l2_srvline
       ,l3_srvline
       ,partner_id
       ,shop_id 
       ,coalesce(sum(new_sign_amt)       ,0)                                                      as 新签年费
       ,coalesce(sum(new_sign_shop_cnt)  ,0)                                                      as 新签门店数
       ,coalesce(sum(renew_sign_amt)     ,0)                                                      as 续签年费
       ,coalesce(sum(renew_sign_shop_cnt),0)                                                      as 续签门店数
       ,coalesce(sum(signback_amt)       ,0)                                                      as 断约回签年费
       ,coalesce(sum(signback_shop_cnt)  ,0)                                                      as 断约回签门店数
       ,if(coalesce(sum(new_recharge_amt),0) >= 30000,30000,coalesce(sum(new_recharge_amt),0))    as 广告首充
       ,coalesce(sum(brand_ad_income_amt),0)                                                      as 非标收入
       ,coalesce(sum(cps_income_amt)     ,0)                                                      as cps收入
       ,0                                                                                         as 新签消耗
       ,0                                                                                         as 全部广告消耗
       ,0                                                                                         as 首充二充封顶金额
  from kbreport.dwd_gd_sign_employee_income_detail_ds
 where ds <= max_pt('kbreport.dwd_gd_sign_employee_income_detail_ds') 
   and ds >= '20260401'
   and coalesce(brand_ad_income_amt,0) 
       + coalesce(new_sign_amt,0)
       + coalesce(renew_sign_amt,0)
       + coalesce(renew_sign_shop_cnt,0)
       + coalesce(new_sign_shop_cnt,0)
       + coalesce(signback_amt,0)
       + coalesce(signback_shop_cnt,0)
       + coalesce(new_recharge_amt,0)
       + coalesce(brand_ad_income_amt,0)    
       + coalesce(cps_income_amt,0) <> 0
 group by ds
       ,ali_code
       ,sale_srvline
       ,l2_srvline
       ,l3_srvline
       ,partner_id
       ,shop_id 


 union all -- 新签消耗

select p1.ds
       ,p1.ali_code
       ,p2.sale_srvline
       ,p2.l2_srvline
       ,p2.l3_srvline
       ,p2.partner_id
       ,null as shop_id 
       ,0    as 新签年费
       ,0    as 新签门店数
       ,0    as 续签年费
       ,0    as 续签门店数
       ,0    as 断约回签年费
       ,0    as 断约回签门店数
       ,0    as 广告首充
       ,0    as 非标收入
       ,0    as cps收入
       ,sum(case when p1.ds <= '20260331' then 
                 case when sale_srvline = '教培' and datediff(to_date(p1.ds,'yyyyMMdd'), to_date(p2.new_recharge_date,'yyyyMMdd'), 'mm') <= 5 then cash_cost 
                      when l2_srvline   = '美业' and datediff(to_date(p1.ds,'yyyyMMdd'), to_date(p2.new_recharge_date,'yyyyMMdd'), 'mm') <= 5 then cash_cost 
                      when sale_srvline = '医疗' and datediff(to_date(p1.ds,'yyyyMMdd'), to_date(p2.new_recharge_date,'yyyyMMdd'), 'mm') <= 3 then cash_cost 
                 else 0 end
                 when p1.ds >= '20260401' and datediff(to_date(p1.ds,'yyyyMMdd'), to_date(p2.new_recharge_date,'yyyyMMdd'), 'mm') <= 5 then cash_cost 
                 else 0 end) as 新签消耗
       ,0 as 全部广告消耗       
       ,max(首充二充封顶金额) as 首充二充封顶金额
  from  
       ( 
        select p1.ds 
               ,p1.partner_id
               ,p2.employee_no    as ali_code
               ,sum(p1.cash_cost) as cash_cost
          from
               ( 
                select *
                  from kbreport.dwd_gd_ad_operate_detail_di
                 where ds >= '20251001'
                   and cost_all > 0
               ) p1 
         inner join  
               ( 
                select ds 
                       ,partner_id
                       ,employee_no
                  from kbdw.dim_kb_sale_employee_partner_relation
                 where ds >= '20251001'
                   and new_relation_type in ('ADVERTISING_BELONG')  
                 group by ds 
                       ,partner_id
                       ,employee_no
               ) p2     
            on p1.partner_id = p2.partner_id    
           and p1.ds         = p2.ds  
         group by p1.ds 
               ,p1.partner_id
               ,p2.employee_no     
       ) p1 
 inner join 
       ( 
        select min(p1.new_recharge_date) as new_recharge_date
               ,partner_id
               ,max(p1.sale_srvline) as sale_srvline
               ,max(p1.l2_srvline)   as l2_srvline
               ,max(p1.l3_srvline)   as l3_srvline
               ,sum(coalesce(new_recharge_amt,0) + coalesce(ad_recharge_amt_1d,0)) as 首充二充封顶金额
          from
               (
                select new_recharge_date
                       ,partner_id
                       ,sale_srvline
                       ,l2_srvline
                       ,l3_srvline
                       ,new_recharge_amt  
                       ,ad_recharge_amt_1d
                  from
                       (
                        select ds as new_recharge_date 
                               ,partner_id      
                               ,sale_srvline          
                               ,l2_srvline
                               ,l3_srvline
                               ,new_recharge_amt      
                               ,0 as ad_recharge_amt_1d
                               ,row_number() over(partition by partner_id order by ds desc) as rk 
                          from kbreport.dwd_gd_sign_employee_income_detail_ds 
                         where ds >= '20251001'
                           and ds <= date_format(DATE_ADD(TO_DATE(NOW()),-1),'yyyyMMdd') 
                           and new_recharge_amt > 0      
                           and sale_srvline in ('教培','医疗','美业')                        
                       ) p1 
                 where p1.rk = 1 

                 union all 

                select p1.续充日期
                       ,p1.partner_id
                       ,p1.sale_srvline
                       ,p1.l2_srvline
                       ,p1.l3_srvline
                       ,0 as new_recharge_amt
                       ,ad_recharge_amt_1d
                  from
                       (
                        select p2.续充日期 
                               ,p1.partner_id
                               ,sale_srvline
                               ,l2_srvline
                               ,l3_srvline
                               ,p2.ad_recharge_amt_1d
                               ,ROW_NUMBER()over(partition by p2.partner_id order by p2.续充日期) as ranks
                          from
                               (
                                select ds as new_recharge_date 
                                       ,partner_id      
                                       ,sale_srvline          
                                       ,l2_srvline
                                       ,l3_srvline
                                       ,new_recharge_amt      
                                  from kbreport.dwd_gd_sign_employee_income_detail_ds 
                                 where ds >= '20251001'
                                   and ds <= date_format(DATE_ADD(TO_DATE(NOW()),-1),'yyyyMMdd') 
                                   and new_recharge_amt > 0      
                                   and sale_srvline in ('教培','医疗','美业')                
                               ) p1 
                          left join 
                               (
                                select ds as 续充日期
                                       ,advertiser_id
                                       ,login_id                      as partner_id
                                       ,cash_recharge_amount / 10000  as ad_recharge_amt_1d 
                                       ,ROW_NUMBER()over(partition by login_id order by ds) as rank 
                                  from amap_ad.dws_gd_ad_advertiser_subject_base_di
                                 where substr(ds,1,6)>='202509'
                                   and cash_recharge_amount > 0
                                   and second_recharge_date is not null  
                               ) p2 
                            on p1.partner_id = p2.partner_id
                           and p1.new_recharge_date < p2.续充日期                   
                       ) p1   
                 where p1.ranks = 1 
               ) p1  
         group by p1.partner_id            
       ) p2
    on p1.partner_id = p2.partner_id
 where p1.ds >= '20251001'
 group by p1.ds
       ,p1.ali_code
       ,p2.sale_srvline
       ,p2.l2_srvline
       ,p2.l3_srvline
       ,p2.partner_id

 union all --存量&全部

select p1.ds
       ,p1.ali_code
       ,p2.sale_srvline
       ,p2.l2_srvline
       ,p2.l3_srvline
       ,p1.partner_id
       ,p1.shop_id 
       ,0 as 新签年费
       ,0 as 新签门店数
       ,0 as 续签年费
       ,0 as 续签门店数
       ,0 as 断约回签年费
       ,0 as 断约回签门店数
       ,0 as 广告首充
       ,0 as 非标收入
       ,0 as cps收入
       ,0 as 新签消耗
       ,sum(p1.cash_cost) as 全部广告消耗 
       ,0                 as 首充二充封顶金额
  from
       (
        select p1.ds 
               ,p1.partner_id
               ,p1.shop_id
               ,p2.ali_code
               ,p1.cash_cost
          from
               ( 
                select *
                  from kbreport.dwd_gd_ad_operate_detail_di
                 where ds >= '20251001' 
                   and cash_cost > 0
               ) p1 
         inner join 
               ( 
                select p1.ds 
                       ,p1.partner_id
                       ,p1.ali_code 
                       ,row_number() over(partition by substring(ds,1,6),p1.partner_id order by p1.cl_flag desc) as rn
                  from      
                       (
                        
                        select ds 
                               ,partner_id
                               ,employee_no as ali_code
                               ,1 as cl_flag 
                          from kbdw.dim_kb_sale_employee_partner_relation
                         where ds in ('20260402','20260507','20260602','20260702')
                           and new_relation_type in ('ADVERTISING_BELONG')  
                         group by ds 
                               ,partner_id
                               ,employee_no    

                         union all 

                        select p2.ds 
                               ,p1.partner_id
                               ,p1.ali_code
                               ,2 as cl_flag
                          from
                               ( 
                                select ds  
                                       ,partner_id    
                                       ,ali_code  
                                       ,2 as cl_flag
                                  from kbreport.dwd_gd_sign_employee_income_detail_ds 
                                 where ds >= '20251001'
                                   and ds <= date_format(DATE_ADD(TO_DATE(NOW()),-1),'yyyyMMdd') 
                                   and new_recharge_amt > 0      
                                   and sale_srvline in ('教培','医疗','美业')                         
                               ) p1  
                         inner join 
                               ( 
                                select ds
                                       ,partner_id
                                       ,employee_no
                                  from kbdw.dim_kb_sale_employee_partner_relation
                                 where ds in (least('20260531',max_pt('kbdw.dim_kb_sale_employee_partner_relation')))
                                   and new_relation_type in ('ADVERTISING_BELONG')  
                                 group by ds
                                       ,partner_id
                                       ,employee_no                          
                               ) p2 
                            on p1.partner_id = p2.partner_id
                       ) p1 
               ) p2 
            on p1.partner_id = p2.partner_id
           and substring(p1.ds,1,6) = substring(p2.ds,1,6)    
         where p2.rn = 1  
       ) p1 
 inner join 
       ( 
        select p1.partner_id
               ,max(p2.sale_srvline)   as sale_srvline
               ,max(p2.l2_srvline)     as l2_srvline
               ,max(p2.l3_srvline)     as l3_srvline
          from
               ( 
                select partner_id
                       ,max(coalesce(atag_id_level3,atag_id_level2,atag_id_level1)) as atag_id
                  from gd_info_cdm.dim_gd_info_partner
                 where ds = MAX_PT('gd_info_cdm.dim_gd_info_partner')
                 group by partner_id               
               ) p1 
          left join 
               ( 
                select atagid
                       ,srv_line        as sale_srvline
                       ,l1_srvline      as l2_srvline
                       ,l2_srvline      as l3_srvline
                  from autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2
                 where modified_date = '20260331'                    
               ) p2 
            on p1.atag_id = p2.atagid
         group by p1.partner_id
       ) p2 
    on p1.partner_id = p2.partner_id
 group by p1.ds
       ,p1.ali_code
       ,p2.sale_srvline
       ,p2.l2_srvline
       ,p2.l3_srvline
       ,p1.partner_id
       ,p1.shop_id 
)




select p1.*
       ,新签年费 + 非标收入 
       +case when 角色类型 not rlike 'KA' then 新签消耗 else 0 end
       +case when substring(ds,1,6) in ('202601','202602','202603') and 角色类型 rlike 'KA' then 0
        when substring(ds,1,6) in ('202601') and 角色类型 not rlike 'KA' then 
        case when 二级类目   = '美业' then 广告首充 * 0.5
             when 行业线    = '医疗' then 广告首充 * 0.2
             when 行业线    = '教培' then 广告首充 * 0.5 
             else 0 end 
        when substring(ds,1,6) in ('202602') and 角色类型 not rlike 'KA' then 
        case when 二级类目   = '美业' then 广告首充 * 0.4
             when 行业线    = '教培' then 广告首充 * 0.4 
             else 0 end   
        when substring(ds,1,6) in ('202603') and 角色类型 not rlike 'KA' then 
        case when 二级类目   = '美业' then 广告首充 * 0.3
             when 行业线    = '教培' then 广告首充 * 0.3 
             else 0 end 
        else 0 end as 新签收入
  from
(
select p1.*
       ,case when p1.二级部门 = '医疗行业线' or p1.行业线 = '医疗' then
             case when p1.新签消耗累计 <= 首充二充封顶金额 then 新签消耗原始值
             when p1.新签消耗累计 > 首充二充封顶金额 and 新签消耗累计到昨天 <= 首充二充封顶金额 then 首充二充封顶金额 - 新签消耗累计到昨天
             when p1.新签消耗累计 > 首充二充封顶金额 and 新签消耗累计到昨天 > 首充二充封顶金额  then 0 
             else 0 end 
             else 新签消耗原始值 end as 新签消耗
  from 
(
select p1.*
       ,sum(p1.新签消耗原始值) over(partition by p1.PID,p1.门店ID order by p1.ds asc)                                        as 新签消耗累计    
       ,sum(p1.新签消耗原始值) over(partition by p1.PID,p1.门店ID order by p1.ds asc) - p1.新签消耗原始值                      as 新签消耗累计到昨天     
       ,p1.首充二充封顶金额 - (sum(p1.新签消耗原始值) over(partition by p1.PID,p1.门店ID order by p1.ds asc) - p1.新签消耗原始值) as 临界当天新签消耗
       ,p2.CM名字
       ,p2.小组长
       ,case when p1.二级部门 = '医疗行业线' and p1.四级部门 is null
             then case when p1.三级部门 = '西南大区' then '1正堂'
                       when p1.三级部门 = '华东大区' then '1杰豹'
                       when p1.三级部门 = '华北大区' then '1云轩'
                       when p1.三级部门 = '医疗SKA' then '1贺然'
                       else '0瑾云' end 
             when p1.二级部门 = '教培行业线' and p1.四级部门 is null
             then case when p1.三级部门 = '华北大区' then '1晓鸟'
                       when p1.三级部门 = '华南大区' then '1侨木'
                       when p1.三级部门 = '教培KA'  then '1漂泊'
                       when p1.三级部门 = 'KA南区'  then '1漂泊'
                       when p1.三级部门 = 'KA北区'  then '1度白'
                       else '0龙浦' end 
             else p3.管理 end as 管理
  from
(
select p1.ds 
       ,DAY(DATE_ADD(TO_DATE(NOW()),-1))                                                as 当前日期           
       ,DAY(LAST_DAY(DATE_ADD(TO_DATE(NOW()),-1)))                                      as 当月天数
       ,DAY(DATE_ADD(TO_DATE(NOW()),-1)) / DAY(LAST_DAY(DATE_ADD(TO_DATE(NOW()),-1)))   as 时间进度
       ,p1.sale_srvline as 行业线
       ,p1.l2_srvline   as 二级类目
       ,p1.l3_srvline   as 三级类目
       ,p1.partner_id   as PID
       ,p1.shop_id      as 门店ID
       ,0               as 新签收入目标
       ,0               as 新签年费目标
       ,0               as 新签门店目标
       ,0               as 新签消耗目标       
       ,0               as 品牌广告目标
       ,0               as 存量收入目标 
       ,0               as 全部收入目标
       ,p1.新签年费 + p1.非标收入 
       +case when p2.role_type not rlike 'KA' then p1.新签消耗 else 0 end
       +case when substring(p1.ds,1,6) in ('202601','202602','202603') and p2.role_type rlike 'KA' then 0
        when substring(p1.ds,1,6) in ('202601') and p2.role_type not rlike 'KA' then 
        case when l2_srvline   = '美业' then p1.广告首充 * 0.5
             when sale_srvline = '医疗' then p1.广告首充 * 0.2
             when sale_srvline = '教培' then p1.广告首充 * 0.5 
             else 0 end 
        when substring(p1.ds,1,6) in ('202602') and p2.role_type not rlike 'KA' then 
        case when l2_srvline   = '美业' then p1.广告首充 * 0.4
             when sale_srvline = '教培' then p1.广告首充 * 0.4 
             else 0 end   
        when substring(p1.ds,1,6) in ('202603') and p2.role_type not rlike 'KA' then 
        case when l2_srvline   = '美业' then p1.广告首充 * 0.3
             when sale_srvline = '教培' then p1.广告首充 * 0.3 
             else 0 end 
        else 0 end as 新签收入原始值
       ,p1.新签年费
       ,p1.新签门店数
       ,p1.续签年费
       ,p1.续签门店数
       ,p1.断约回签年费
       ,p1.断约回签门店数
       ,p1.广告首充
       ,p1.非标收入
       ,p1.cps收入
       ,p1.首充二充封顶金额
       ,case when p2.role_type not rlike 'KA' then p1.新签消耗 else 0 end as 新签消耗原始值
       ,case when p2.role_type rlike 'KA' then p1.全部广告消耗 else 0 end as 全部广告消耗
       ,p1.ali_code             as 工号
       ,p2.employee_name        as 名字
       ,p2.ggbq                 as 骨干标签
       ,p2.hire_months_stratum  as 入职月份分层           
       ,p2.l2_dept_name         as 二级部门
       ,case when p1.ds >= '20260501' and p2.l3_dept_name = '教培SKA' then '教培KA' else p2.l3_dept_name end as 三级部门
       ,p2.l4_dept_name         as 四级部门
       ,p2.l5_dept_name         as 五级部门
       ,p2.team_type            as 团队类型
       ,p2.employee_type        as 员工类型
       ,p2.role_type            as 角色类型
       ,p2.role_name            as 角色名称    
       ,p2.is_zy                as 是否展业     
       ,p2.leave_date           as 离职日期 
       ,p2.hire_date            as 入职日期
       ,p2.city_name            as base地
  from
       ( 
        select ds
               ,ali_code
               ,sale_srvline
               ,l2_srvline
               ,l3_srvline
               ,partner_id
               ,shop_id 
               ,新签年费
               ,新签门店数
               ,续签年费
               ,续签门店数
               ,断约回签年费
               ,断约回签门店数
               ,广告首充
               ,非标收入
               ,新签消耗
               ,全部广告消耗
               ,cps收入
               ,首充二充封顶金额
          from tmp_gd_sign_employee_income_detail
       ) p1 
 inner join
       ( 
        select p1.*
               ,p2.ggbq
               ,p2.is_zy
          from
               (
                select *
                       ,row_number() over(partition by substring(ds,1,6),ali_code order by ds desc) as rk 
                  from kbreport.dim_gd_c33_employee_info_ds
                 where ds <= max_pt('kbreport.dim_gd_c33_employee_info_ds')
                   and ds >= '20260401' 
                   and team_type = '直营'
                   and l2_dept_name not in ('销售运营','医疗商家成长部','教培商家成长部','销售运营部')         
               ) p1 
          left join 
               (
                select calmonth
                       ,employee_no
                       ,ggbq
                       ,coalesce(remark,'展业中') as is_zy
                  from kbreport.dim_gd_sales_ylmyjp        
                 group by calmonth
                       ,employee_no
                       ,ggbq 
                       ,coalesce(remark,'展业中')                         
               ) p2 
            on p1.ali_code = p2.employee_no
           and substring(p1.ds,1,6) = p2.calmonth
         where p1.rk = 1 
       ) p2 
    on p1.ali_code = p2.ali_code
   and substring(p1.ds,1,6) = substring(p2.ds,1,6)  

 union all 

select p1.ds  
       ,当前日期           
       ,当月天数
       ,时间进度
       ,null            as 行业线
       ,null            as 二级类目
       ,null            as 三级类目
       ,null            as PID
       ,null            as 门店ID
       ,新签收入目标      as 新签收入目标
       ,新签年费目标      as 新签年费目标
       ,新签门店目标      as 新签门店目标
       ,新签消耗目标      as 新签消耗目标
       ,品牌广告目标      as 品牌广告目标
       ,存量收入目标      as 存量收入目标
       ,全部收入目标      as 全部收入目标
       ,0               as 新签收入
       ,0               as 新签年费
       ,0               as 新签门店数
       ,0               as 续签年费
       ,0               as 续签门店数
       ,0               as 断约回签年费
       ,0               as 断约回签门店数
       ,0               as 广告首充
       ,0               as 非标收入
       ,0               as cps收入
       ,0               as 首充二充封顶金额
       ,0               as 新签消耗
       ,0               as 全部广告消耗
       ,p1.ali_code             as 工号
       ,p2.employee_name        as 名字
       ,p2.ggbq                 as 骨干标签
       ,p2.hire_months_stratum  as 入职月份分层           
       ,p1.l2_dept_name         as 二级部门
       ,case when p1.ds >= '20260501' and p1.l3_dept_name = '教培SKA' then '教培KA' else p1.l3_dept_name end as 三级部门
       ,p1.l4_dept_name         as 四级部门
       ,p1.l5_dept_name         as 五级部门
       ,p2.team_type            as 团队类型
       ,p2.employee_type        as 员工类型
       ,p2.role_type            as 角色类型
       ,p2.role_name            as 角色名称    
       ,p2.is_zy                as 是否展业     
       ,p2.leave_date           as 离职日期 
       ,p2.hire_date            as 入职日期
       ,p2.city_name            as base地
  from
       (
        select concat(calmonth,'01') as ds 
               ,DAY(DATE_ADD(TO_DATE(NOW()),-1))                                                as 当前日期           
               ,DAY(LAST_DAY(DATE_ADD(TO_DATE(NOW()),-1)))                                      as 当月天数
               ,DAY(DATE_ADD(TO_DATE(NOW()),-1)) / DAY(LAST_DAY(DATE_ADD(TO_DATE(NOW()),-1)))   as 时间进度
               ,null            as 行业线
               ,null            as 二级类目
               ,null            as 三级类目
               ,null            as PID
               ,null            as 门店ID
               ,kpi_sr          as 新签收入目标
               ,kpi_xqnf        as 新签年费目标
               ,kpi_xqmd        as 新签门店目标
               ,kpi_new_consume as 新签消耗目标
               ,brand_ad_target as 品牌广告目标
               ,kpi_clsr        as 存量收入目标
               ,kpi_total_ka    as 全部收入目标
               ,ali_code              
               ,l2_dept_name    
               ,l3_dept_name    
               ,l4_dept_name    
               ,l5_dept_name    
        from kbreport.dim_kpi_hc       
       ) p1 
  left join 
       (
        select p1.*
               ,p2.ggbq
               ,p2.is_zy
          from
               (
                select *
                       ,row_number() over(partition by substring(ds,1,6),ali_code order by ds desc) as rk 
                  from kbreport.dim_gd_c33_employee_info_ds
                 where ds <= max_pt('kbreport.dim_gd_c33_employee_info_ds')
                   and ds >= '20260401' 
                   and team_type = '直营'
                   and l2_dept_name not in ('销售运营','医疗商家成长部','教培商家成长部','销售运营部')         
               ) p1 
          left join 
               (
                select calmonth
                       ,employee_no
                       ,ggbq
                       ,coalesce(remark,'展业中') as is_zy
                  from kbreport.dim_gd_sales_ylmyjp        
                 group by calmonth
                       ,employee_no
                       ,ggbq    
                       ,coalesce(remark,'展业中')                      
               ) p2 
            on p1.ali_code = p2.employee_no
           and substring(p1.ds,1,6) = p2.calmonth
         where p1.rk = 1        
       ) p2 
    on p1.ali_code = p2.ali_code
   and substring(p1.ds,1,6) = substring(p2.ds,1,6)  

 union all  

select concat(substring(ds,1,6),'01')   as ds 
       ,DAY(DATE_ADD(TO_DATE(NOW()),-1))                                                as 当前日期           
       ,DAY(LAST_DAY(DATE_ADD(TO_DATE(NOW()),-1)))                                      as 当月天数
       ,DAY(DATE_ADD(TO_DATE(NOW()),-1)) / DAY(LAST_DAY(DATE_ADD(TO_DATE(NOW()),-1)))   as 时间进度
       ,null                            as 行业线
       ,null                            as 二级类目
       ,null                            as 三级类目
       ,null                            as PID
       ,null                            as 门店ID
       ,0                               as 新签收入目标
       ,0                               as 新签年费目标
       ,0                               as 新签门店目标
       ,0                               as 新签消耗目标
       ,0                               as 品牌广告目标
       ,0                               as 存量收入目标
       ,0                               as 全部收入目标
       ,0                               as 新签收入
       ,0                               as 新签年费
       ,0                               as 新签门店数
       ,0                               as 续签年费
       ,0                               as 续签门店数
       ,0                               as 断约回签年费
       ,0                               as 断约回签门店数
       ,0                               as 广告首充
       ,0                               as 非标收入
       ,0                               as cps收入
       ,0                               as 首充二充封顶金额
       ,0                               as 新签消耗
       ,0                               as 全部广告消耗
       ,ali_code                        as 工号
       ,employee_name                   as 名字
       ,ggbq                            as 骨干标签    
       ,hire_months_stratum             as 入职月份分层                
       ,l2_dept_name                    as 二级部门
       ,case when ds >= '20260501' and l3_dept_name = '教培SKA' then '教培KA' else l3_dept_name end as 三级部门                    
       ,l4_dept_name                    as 四级部门
       ,l5_dept_name                    as 五级部门
       ,team_type                       as 团队类型
       ,employee_type                   as 员工类型
       ,role_type                       as 角色类型
       ,role_name                       as 角色名称 
       ,p2.remark                       as 是否展业
       ,p1.leave_date                   as 离职日期
       ,p1.hire_date                    as 入职日期
       ,p1.city_name                    as base地
  from 
       ( 
        select *    
               ,row_number() over(partition by substring(ds,1,6),ali_code order by ds desc) as rn 
          from kbreport.dim_gd_c33_employee_info_ds
         where ds <= max_pt('kbreport.dim_gd_c33_employee_info_ds')
           and ds >= '20260401' 
           and team_type = '直营'
           and l2_dept_name not in ('销售运营','医疗商家成长部','教培商家成长部','销售运营部')         
       ) p1 
  left join 
       ( 
        select calmonth
               ,employee_no
               ,ggbq
               ,coalesce(remark,'展业中') as remark
          from kbreport.dim_gd_sales_ylmyjp        
         group by calmonth
               ,employee_no
               ,ggbq  
               ,coalesce(remark,'展业中')                        
       ) p2 
   on p1.ali_code = p2.employee_no
  and substring(p1.ds,1,6) = p2.calmonth
where p1.rn = 1  

union all 

select pay_date  as ds
       ,DAY(DATE_ADD(TO_DATE(NOW()),-1))                                                as 当前日期           
       ,DAY(LAST_DAY(DATE_ADD(TO_DATE(NOW()),-1)))                                      as 当月天数
       ,DAY(DATE_ADD(TO_DATE(NOW()),-1)) / DAY(LAST_DAY(DATE_ADD(TO_DATE(NOW()),-1)))   as 时间进度
       ,p2.sale_srvline as 行业线
       ,p2.l2_srvline   as 二级类目
       ,p2.l3_srvline   as 三级类目
       ,p2.partner_id   as PID
       ,null            as 门店ID
       ,0               as 新签收入目标
       ,0               as 新签年费目标
       ,0               as 新签消耗目标
       ,0               as 新签门店目标
       ,0               as 品牌广告目标
       ,0               as 存量收入目标       
       ,0               as 全部收入目标
       ,coalesce(case when p1.product_type = '新签年费' then actual_pay_fee else 0 end,0) 
       +coalesce(case when p1.product_type = '非标' then actual_pay_fee else 0 end)      as 新签收入
       ,coalesce(case when p1.product_type = '新签年费' then actual_pay_fee else 0 end,0) as 新签年费
       ,coalesce(case when p1.product_type = '新签年费' then 1 else 0 end,0)              as 新签门店数
       ,0 as 续签年费
       ,0 as 续签门店数
       ,0 as 断约回签年费
       ,0 as 断约回签门店数 
       ,coalesce(case when p1.product_type = '广告' then actual_pay_fee else 0 end)      as 广告首充
       ,coalesce(case when p1.product_type = '非标' then actual_pay_fee else 0 end)      as 非标收入
       ,0                       as cps收入
       ,0                       as 首充二充封顶金额
       ,0                       as 新签消耗
       ,0                       as 全部广告消耗   
       ,p1.ali_code             as 工号
       ,p3.employee_name        as 名字
       ,p3.ggbq                 as 骨干标签   
       ,p3.hire_months_stratum  as 入职月份分层                          
       ,p3.l2_dept_name         as 二级部门
       ,case when pay_date >= '20260501' and l3_dept_name = '教培SKA' then '教培KA' else l3_dept_name end as 三级部门    
       ,p3.l4_dept_name         as 四级部门
       ,p3.l5_dept_name         as 五级部门
       ,p3.team_type            as 团队类型
       ,p3.employee_type        as 员工类型
       ,p3.role_type            as 角色类型
       ,p3.role_name            as 角色名称    
       ,p3.is_zy                as 是否展业  
       ,p3.leave_date           as 离职日期         
       ,p3.hire_date            as 入职日期
       ,p3.city_name            as base地
  from
       ( 
        select com_name
               ,com_id
               ,atag_id
               ,case when product_type = '1' then '新签年费' 
                     when product_type = '4' then '智能体'    
                     when product_type = '5' then '非标'
                     when product_type = '2' then '广告'    
                     else '其他' end as product_type
               ,date_format(pay_time,'yyyyMMdd') as pay_date  
               ,date_format(pay_time,'yyyyMM') as pay_month  
               ,pay_time
               ,coalesce(actual_pay_fee,0) / 100 as actual_pay_fee
               ,ali_code
          from gd_info_alake.dwd_gd_info_sign_c3_order_sign_di
         where ds >= date_format(DATEADD(CURRENT_DATE, -1, 'mm'),'yyyyMM01')
           and total_fee > 0 
           and substring(pay_time,1,10) = TO_DATE(NOW())
           and order_status = 'SUCCESS'      
           and ali_code is not null
       ) p1 
 inner join 
       (
        select p1.partner_id
               ,max(p2.sale_srvline)   as sale_srvline
               ,max(p2.l2_srvline)     as l2_srvline
               ,max(p2.l3_srvline)     as l3_srvline
          from
               ( 
                select partner_id
                       ,max(coalesce(atag_id_level3,atag_id_level2,atag_id_level1)) as atag_id
                  from gd_info_cdm.dim_gd_info_partner
                 where ds = MAX_PT('gd_info_cdm.dim_gd_info_partner')
                 group by partner_id               
               ) p1 
          left join 
               ( 
                select atagid
                       ,srv_line        as sale_srvline
                       ,l1_srvline      as l2_srvline
                       ,l2_srvline      as l3_srvline
                  from autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2
                 where modified_date = '20260331'                    
               ) p2 
            on p1.atag_id = p2.atagid
         group by p1.partner_id
       ) p2 
    on p1.com_id = p2.partner_id
 inner join 
       ( 
        select p1.*
               ,p2.ggbq
               ,p2.remark as is_zy
          from
               (
                select *
                       ,row_number() over(partition by substring(ds,1,6),ali_code order by ds desc) as rk 
                  from kbreport.dim_gd_c33_employee_info_ds
                 where ds <= max_pt('kbreport.dim_gd_c33_employee_info_ds')
                   and ds >= '20260401' 
                   and team_type = '直营'
                   and l2_dept_name not in ('销售运营','医疗商家成长部','教培商家成长部','销售运营部')         
               ) p1 
          left join 
               (
                select calmonth
                       ,employee_no
                       ,ggbq
                       ,coalesce(remark,'展业中') as remark    
                  from kbreport.dim_gd_sales_ylmyjp        
                 group by calmonth
                       ,employee_no
                       ,ggbq  
                       ,coalesce(remark,'展业中')                            
               ) p2 
            on p1.ali_code = p2.employee_no
           and substring(p1.ds,1,6) = p2.calmonth
         where p1.rk = 1 
       ) p3 
    on p1.ali_code = p3.ali_code
   and substring(p1.pay_date,1,6) = substring(p3.ds,1,6) 
) p1 
left join 
( 
select max(cm_nick_name) as CM名字
       ,max(coalesce(group_leader_nick_name,group_leader_name)) as 小组长
       ,l2_dept_name
       ,case when l3_dept_name = '教培SKA' then '教培KA' else l3_dept_name end as l3_dept_name
       ,l4_dept_name
       ,l5_dept_name 
  from kbreport.dim_gd_c33_employee_info_ds
 where ds = max_pt('kbreport.dim_gd_c33_employee_info_ds')  
   and team_type = '直营'
 group by l2_dept_name
       ,case when l3_dept_name = '教培SKA' then '教培KA' else l3_dept_name end
       ,l4_dept_name
       ,l5_dept_name 
) p2 
 on coalesce(p1.二级部门,'无') = coalesce(p2.l2_dept_name,'无')
and coalesce(p1.三级部门,'无') = coalesce(p2.l3_dept_name,'无')
and coalesce(p1.四级部门,'无') = coalesce(p2.l4_dept_name,'无')
and coalesce(p1.五级部门,'无') = coalesce(p2.l5_dept_name,'无')
inner join
( 
select l2_dept_name
       ,case when l3_dept_name = '教培SKA' then '教培KA' else l3_dept_name end as l3_dept_name
       ,l4_dept_name
       ,max(cm_nick_name) as 管理
       ,substring(ds,1,6) as smonth
  from kbreport.dim_gd_c33_employee_info_ds
 where ds <= max_pt('kbreport.dim_gd_c33_employee_info_ds')
   and ds >= '20260401' 
   and team_type = '直营'
   and is_leave = 0
   and l2_dept_name not in ('销售运营','医疗商家成长部','教培商家成长部','销售运营部')         
 group by l2_dept_name
       ,case when l3_dept_name = '教培SKA' then '教培KA' else l3_dept_name end
       ,l4_dept_name
       ,substring(ds,1,6)       
) p3 
 on substring(p1.ds,1,6)    = p3.smonth
and coalesce(p1.二级部门,'无') = coalesce(p3.l2_dept_name,'无')
and coalesce(p1.三级部门,'无') = coalesce(p3.l3_dept_name,'无')
and coalesce(p1.四级部门,'无') = coalesce(p3.l4_dept_name,'无')
) p1 
) p1 