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

-- 目的：新签消耗看板
-- 使用对象：所有销售小二
-- 上线日期：2026.1.12
-- 维护日期：2026.1.20 新增首充金额数据
-- 维护日期：2026.2.26 新增行业

-- 消耗数据

select p1.new_recharge_date
       ,p1.partner_id
       ,p1.partner_name
       ,p1.new_recharge_amt                    --  首充金额
       ,0 as 备用首充金额
       -- 消耗数据
       ,p2.ds       
       ,p2.shop_id
       ,p2.shop_name
       ,p2.is_insign
       ,p2.is_ading
       ,p2.cost_all
       ,p2.cash_cost
       ,p2.ddt_ad_income_amt
       ,p2.ocpc_ad_income_amt
       ,p2.cpa_ad_income_amt
       ,p2.online_hours
       ,p2.leads_cnt
       ,p2.cpa_cash_cost
       ,p2.ocpc_cash_cost
       ,p2.account_budget
       ,p2.settle_adcost_amt_1d
       ,p2.cash_settle_adcost_amt_1d
       ,p2.cash_balance_amt
       ,p2.cash_balance_consumable_days        
       ,p2.ali_code
       ,p2.employee_name
       ,p2.is_leave
       ,p2.role_type
       ,p2.l1_dept_name
       ,p2.l2_dept_name
       ,p2.l3_dept_name
       ,p2.l4_dept_name
       ,p2.l5_dept_name
       ,p2.l6_dept_name
       ,p2.brand_id
       ,p2.brand_name
       ,p2.partner_layer
       ,p2.province_name
       ,p2.city_name
       ,p2.district_name
       ,p1.sale_srvline
       ,p1.l2_srvline  
       ,p1.l3_srvline      
  from 
       ( -- 首充分母
        select ds as new_recharge_date -- 首充日期
               ,partner_id      
               ,partner_name
               ,new_recharge_amt
               ,case when l2_srvline = '美业' then '美业' else sale_srvline end as sale_srvline
               ,l2_srvline
               ,l3_srvline
          from kbreport.dwd_gd_sign_employee_income_detail_ds 
         where ds >= '20250701'
           and new_recharge_amt > 0                  
           and sale_srvline in ('教培','医疗','美业')
       ) p1  
  left join 
       ( -- 消耗数据
        select p1.ds
               ,p1.partner_id
               ,p1.shop_id 
               ,p1.shop_name 
               ,p1.is_insign
               ,p1.is_ading
               ,p1.cost_all
               ,p1.cash_cost
               ,p1.ddt_ad_income_amt
               ,p1.ocpc_ad_income_amt
               ,p1.cpa_ad_income_amt
               ,p1.online_hours
               ,p1.leads_cnt
               ,p1.cpa_cash_cost
               ,p1.ocpc_cash_cost
               ,p1.account_budget
               ,p1.settle_adcost_amt_1d
               ,p1.cash_settle_adcost_amt_1d
               ,p1.cash_balance_amt
               ,p1.cash_balance_consumable_days               
               ,p2.ali_code
               ,p2.employee_name 
               ,p2.is_leave
               ,p2.role_type
               ,p2.l1_dept_name
               ,p2.l2_dept_name
               ,p2.l3_dept_name
               ,p2.l4_dept_name
               ,p2.l5_dept_name
               ,p2.l6_dept_name       
               ,p3.brand_id
               ,p3.brand_name
               ,p3.partner_layer
               ,p4.province_name
               ,p4.city_name
               ,p4.district_name
               ,p4.l1_srvline_name
               ,p4.l2_srvline_name  
          from
               ( -- 消耗数据
                select p1.*
                       ,p2.employee_no
                  from
                       ( -- 消耗明细数据：人✖️门店粒度
                        select *
                          from kbreport.dwd_gd_ad_operate_detail_di
                         where ds >= '20250701'
                         --   and (cost_all > 0 or is_ading > 0) 
                       ) p1 
                 inner join  
                       ( -- 人户关系
                        select ds 
                               ,partner_id
                               ,employee_no
                          from kbdw.dim_kb_sale_employee_partner_relation
                         where ds >= '20250701'
                           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      
               ) p1 
         inner join 
               ( -- 架构
                select ds 
                       ,ali_code
                       ,employee_name
                       ,l1_dept_name
                       ,l2_dept_name
                       ,l3_dept_name
                       ,l4_dept_name
                       ,l5_dept_name
                       ,l6_dept_name
                       ,case when role_type = 'KA' or team_info rlike 'KA' then 'KA' else role_type end as role_type   
                       ,is_leave
                  from kbreport.dim_gd_c33_employee_info_ds
                 where ds <= MAX_PT('kbreport.dim_gd_c33_employee_info_ds')
                   and ds >= '20250701'
                   and team_type = '直营'
                   and is_leave = 0
                   and l1_dept_name in ('医疗&教培业务部','美业&医疗&教培业务部')
                   and l2_dept_name in ('医疗行业线','教培行业线','教培&美业行业线','泛低频SKA')
               ) p2   
            on p1.ds = p2.ds   
           and p1.employee_no = p2.ali_code
          left join 
               ( -- 商户信息
                select partner_id
                       ,max(partner_name)    as partner_name
                       ,max(brand_id)        as brand_id
                       ,max(brand_name)      as brand_name
                       ,max(partner_layer)   as partner_layer
                  from gd_info_cdm.mds_gd_info_partner_basic_ds
                 where ds = MAX_PT('gd_info_cdm.mds_gd_info_partner_basic_ds')               
                 group by partner_id
               ) p3 
            on p1.partner_id = p3.partner_id
          left join 
               ( -- 门店信息
                select shop_id
                       ,max(province_name)      as province_name
                       ,max(city_name)          as city_name
                       ,max(district_name)      as district_name
                       ,max(l1_srvline_name)    as l1_srvline_name 
                       ,max(l2_srvline_name)    as l2_srvline_name          
                  from gd_info_cdm.mds_gd_info_shop_basic_ds
                 where ds = MAX_PT('gd_info_cdm.mds_gd_info_shop_basic_ds') 
                 group by shop_id       
              ) p4 
           on p1.shop_id = p4.shop_id   
         ) p2 
      on p1.partner_id = p2.partner_id

   union all -- 首充金额

select p1.new_recharge_date
       ,p1.partner_id
       ,p1.partner_name
       ,0 as new_recharge_amt
       ,p1.new_recharge_amt  as 备用首充金额
       -- 消耗数据
       ,new_recharge_date as ds       
       ,null as shop_id
       ,null as shop_name
       ,null as is_insign
       ,null as is_ading
       ,0 as cost_all
       ,0 as cash_cost
       ,0 as ddt_ad_income_amt
       ,0 as ocpc_ad_income_amt
       ,0 as cpa_ad_income_amt
       ,0 as online_hours
       ,0 as leads_cnt
       ,0 as cpa_cash_cost
       ,0 as ocpc_cash_cost
       ,0 as account_budget
       ,0 as settle_adcost_amt_1d
       ,0 as cash_settle_adcost_amt_1d
       ,0 as cash_balance_amt
       ,0 as cash_balance_consumable_days        
       ,p2.ali_code
       ,p2.employee_name
       ,p2.is_leave
       ,p2.role_type
       ,p2.l1_dept_name
       ,p2.l2_dept_name
       ,p2.l3_dept_name
       ,p2.l4_dept_name
       ,p2.l5_dept_name
       ,p2.l6_dept_name
       ,p2.brand_id
       ,p2.brand_name
       ,null as partner_layer
       ,null as province_name
       ,null as city_name
       ,null as district_name
       ,p1.sale_srvline
       ,p1.l2_srvline 
       ,p1.l3_srvline
  from 
       ( -- 首充分母
        select ds as new_recharge_date -- 首充日期
               ,partner_id      
               ,partner_name
               ,new_recharge_amt
               ,case when l2_srvline = '美业' then '美业' else sale_srvline end as sale_srvline
               ,l2_srvline      
               ,l3_srvline         
          from kbreport.dwd_gd_sign_employee_income_detail_ds 
         where ds >= '20250701'
           and new_recharge_amt > 0                  
       ) p1  
  left join 
       ( -- 人户关系
        select p1.*
               ,p2.*
          from
               ( -- 架构
                select ds 
                       ,ali_code
                       ,employee_name
                       ,l1_dept_name
                       ,l2_dept_name
                       ,l3_dept_name
                       ,l4_dept_name
                       ,l5_dept_name
                       ,l6_dept_name
                       ,case when role_type = 'KA' or team_info rlike 'KA' then 'KA' else role_type end as role_type   
                       ,is_leave
                  from kbreport.dim_gd_c33_employee_info_ds
                 where ds <= MAX_PT('kbreport.dim_gd_c33_employee_info_ds')
                   and ds >= '20250701'
                   and team_type = '直营'
                   and is_leave = 0
                   and l1_dept_name in ('医疗&教培业务部','美业&医疗&教培业务部')
                   and l2_dept_name in ('医疗行业线','教培行业线','教培&美业行业线','泛低频SKA')                
               ) p1 
         inner join
               ( -- 商户 
                select partner_id
                       ,max(partner_name)    as partner_name
                       ,max(brand_id)        as brand_id
                       ,max(brand_name)      as brand_name
                       ,max(ali_code)        as employee_no
                       ,max(l1_srvline_name) as l1_srvline_name
                       ,max(l2_srvline_name) as l2_srvline_name
                  from gd_info_cdm.mds_gd_info_partner_basic_ds
                 where ds = MAX_PT('gd_info_cdm.mds_gd_info_partner_basic_ds')               
                 group by partner_id                      
               ) p2 
            on p1.ali_code = p2.employee_no
       ) p2
    on p1.partner_id        = p2.partner_id
   and p1.new_recharge_date = p2.ds 
;