-- bi0003
-- 需求方：SKA
-- 目的：SKA相关管理数据看板及波动
-- 上线日期：


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


-- 人粒度,天粒度
with tmp_hww_bi0003_employee_detail as (

select p1.smonth
       ,p1.ds
       ,p1.employee_no_xq           -- 新签小二工号
       ,p1.employee_name_xq         -- 新签小二名字
       ,p1.l1_dept_name_xq     
       ,p1.l2_dept_name_xq     
       ,p1.l3_dept_name_xq   
       -- 指标
       ,p1.insgin_brand_cnt
       ,p1.insgin_pid_cnt
       ,p1.insgin_shop_cnt    
       ,p1.new_sign_shop_cnt
       ,p1.new_sign_amt
       ,p1.cps_income_amt_1d
       ,p1.actual_pay_fee
       ,p1.kq_shop_cnt
       ,p1.kq_sign_amt
       ,p1.renew_shop_cnt
       ,p1.renew_sign_income_amt_1d
       ,p1.new_recharge_amt   
       ,p1.pg_zz
       ,p1.pg_dz
       ,p1.pg_all
       ,p1.1d_cost_all
       ,p1.1d_cash_cost
       ,p1.online_hours
       ,p1.leads_cnt
       -- 上月月底在签
       ,p2.smonth as last_smonth
       ,p2.insgin_brand_cnt as insgin_brand_cnt_last_month
       ,p2.insgin_pid_cnt as insgin_pid_cnt_last_month
       ,p2.insgin_shop_cnt as insgin_shop_cnt_last_month       
    --    ,case when p2.smonth = date_format(add_months(to_date(p1.ds,'yyyyMMdd'),-1),'yyyyMM') then p2.insgin_brand_cnt end as insgin_brand_cnt_last
    --    ,case when p2.smonth = date_format(add_months(to_date(p1.ds,'yyyyMMdd'),-1),'yyyyMM') then p2.insgin_pid_cnt end   as insgin_pid_cnt_last
    --    ,case when p2.smonth = date_format(add_months(to_date(p1.ds,'yyyyMMdd'),-1),'yyyyMM') then p2.insgin_shop_cnt end  as insgin_shop_cnt_last
  from 
       (
        select substring(ds,1,6) as smonth
               ,ds
               ,employee_no_xq           -- 新签小二工号
               ,employee_name_xq         -- 新签小二名字
               ,l1_dept_name_xq     
               ,l2_dept_name_xq     
               ,l3_dept_name_xq          
               -- 指标            
               -- 在签
               ,count(distinct case when is_insign = 1 then p1.brand_code end)                         as insgin_brand_cnt            -- 在约品牌数
               ,count(distinct case when is_insign = 1 then p1.partner_id end)                         as insgin_pid_cnt              -- 在约商户数
               ,count(distinct case when is_insign = 1 then p1.shop_id end)                            as insgin_shop_cnt             -- 在约门店数
               -- 签约&首充
               ,count(distinct case when is_new_sign = 1       then shop_id end)                       as new_sign_shop_cnt           -- 新签门店数,新签包含扩签
               ,sum(           case when is_new_sign = 1       then new_sign_amt end)                  as new_sign_amt                -- 新签年费
               ,sum(           case when cps_income_amt_1d > 0 then cps_income_amt_1d end)             as cps_income_amt_1d           -- cps
               ,sum(           case when actual_pay_fee > 0    then actual_pay_fee end)                as actual_pay_fee              -- 智能体
               ,count(distinct case when is_kq       = 1       then shop_id end)                       as kq_shop_cnt                 -- 扩签门店数
               ,sum(           case when is_kq       = 1       then new_sign_amt end)                  as kq_sign_amt                 -- 扩签年费
               ,count(distinct case when is_xq       = 1       then shop_id end)                       as renew_shop_cnt              -- 续签门店数
               ,sum(           case when is_xq       = 1       then renew_sign_income_amt_1d end)      as renew_sign_income_amt_1d    -- 续签金额
               ,sum(           case when new_recharge_amt > 0  then new_recharge_amt end)              as new_recharge_amt            -- 广告首充
               -- 品广收入
               ,sum(           case when pg_zz > 0             then pg_zz  end)                        as pg_zz                -- 钻展收入
               ,sum(           case when pg_dz > 0             then pg_dz  end)                        as pg_dz                -- 顶展收入
               ,sum(           case when pg_all > 0            then pg_all end)                        as pg_all               -- 非标总收入
               -- 广告消耗
               ,sum(p1.1d_cost_all)                                                                    as 1d_cost_all          -- 毛消耗
               ,sum(p1.1d_cash_cost)                                                                   as 1d_cash_cost         -- 现金消耗
               ,sum(p1.online_hours)                                                                   as online_hours         -- 当天在投时长   
               -- 客资
               ,sum(p1.leads_cnt)                                                                      as leads_cnt            -- 客资量
          from kbreport.dws_gd_sign_employee_shop_detail_di p1 
         where ds >= '20240101'
         group by substring(ds,1,6)
               ,ds
               ,p1.employee_no_xq          
               ,p1.employee_name_xq        
               ,p1.l1_dept_name_xq     
               ,p1.l2_dept_name_xq     
               ,p1.l3_dept_name_xq           
       ) p1 
  left join 
       (  -- 上月最后一天数据+最新一天
        select substring(ds,1,6) as smonth
               ,ds
               ,p1.employee_no_xq           -- 新签小二工号
               ,p1.employee_name_xq         -- 新签小二名字
               ,p1.l1_dept_name_xq     
               ,p1.l2_dept_name_xq     
               ,p1.l3_dept_name_xq          
               -- 指标            
               -- 在签
               ,count(distinct case when is_insign = 1 then p1.brand_code end)     as insgin_brand_cnt            -- 在约品牌数
               ,count(distinct case when is_insign = 1 then p1.partner_id end)     as insgin_pid_cnt              -- 在约商户数
               ,count(distinct case when is_insign = 1 then p1.shop_id end)        as insgin_shop_cnt             -- 在约门店数          
          from kbreport.dws_gd_sign_employee_shop_detail_di p1 
         where ds in ('20250131','20250228','20250331','20250430','20250531','20250630','20250731','20250831','20250930','20251031','20251130','20251231','20260131','20260228',date_format(DATEADD(current_date,-1,'dd'),'yyyyMMdd')) 
           and shop_id is not null
         group by ds
               ,substring(ds,1,6)
               ,p1.employee_no_xq          
               ,p1.employee_name_xq        
               ,p1.l1_dept_name_xq     
               ,p1.l2_dept_name_xq     
               ,p1.l3_dept_name_xq   
       ) p2 
    on p1.employee_no_xq   = p2.employee_no_xq
   and p1.l2_dept_name_xq  = p2.l2_dept_name_xq
   and p1.l3_dept_name_xq  = p2.l3_dept_name_xq 
 where p2.smonth <= date_format(add_months(to_date(p1.ds,'yyyyMMdd'),0),'yyyyMM')
   and p2.smonth >= date_format(add_months(to_date(p1.ds,'yyyyMMdd'),-1),'yyyyMM')

)



select p1.*
       ,p2.new_sign_shop_cnt_target
       ,p2.new_sign_target
       ,p2.pg_target
  from 
       ( -- 小二过程指标
        select '当天' as type 
               ,p1.ds
               ,p1.employee_no_xq           -- 新签小二工号
               ,p1.employee_name_xq         -- 新签小二名字
               ,p1.l1_dept_name_xq     
               ,p1.l2_dept_name_xq     
               ,p1.l3_dept_name_xq   
               -- 日指标
               ,max(p1.insgin_brand_cnt)         as 在签品牌数
               ,max(p1.insgin_pid_cnt)           as 在签商户数
               ,max(p1.insgin_shop_cnt)          as 在签门店数 
               ,max(p1.new_sign_shop_cnt)        as 新签门店数
               ,max(p1.new_sign_amt)             as 新签金额 
               ,max(p1.cps_income_amt_1d)        as cps收入
               ,max(p1.actual_pay_fee)           as 智能体收入 
               ,max(p1.kq_shop_cnt)              as 扩签门店数 
               ,max(p1.kq_sign_amt)              as 扩签金额 
               ,max(p1.renew_shop_cnt)           as 续签门店数
               ,max(p1.renew_sign_income_amt_1d) as 续签金额
               ,max(p1.new_recharge_amt)         as 首充金额
               ,max(p1.pg_zz)                    as 钻展收入
               ,max(p1.pg_dz)                    as 顶展收入
               ,max(p1.pg_all)                   as 品广收入
               ,max(p1.1d_cost_all)              as 广告毛消耗
               ,max(p1.1d_cash_cost)             as 现金消耗
               ,max(p1.online_hours)             as 在投时长
               ,max(p1.leads_cnt)                as 客资量
               -- 当月最新在签
               ,max(case when p1.last_smonth = smonth then p1.insgin_brand_cnt_last_month else null end) as 当月在签品牌数
               ,max(case when p1.last_smonth = smonth then p1.insgin_pid_cnt_last_month   else null end) as 当月在签pid数
               ,max(case when p1.last_smonth = smonth then p1.insgin_shop_cnt_last_month  else null end) as 当月在签门店数    
               -- 上月在签
               ,max(case when p1.last_smonth = date_format(add_months(to_date(ds,'yyyyMMdd'),-1),'yyyyMM') then p1.insgin_brand_cnt_last_month else null end) as 上月在签品牌数
               ,max(case when p1.last_smonth = date_format(add_months(to_date(ds,'yyyyMMdd'),-1),'yyyyMM') then p1.insgin_pid_cnt_last_month   else null end) as 上月在签pid数
               ,max(case when p1.last_smonth = date_format(add_months(to_date(ds,'yyyyMMdd'),-1),'yyyyMM') then p1.insgin_shop_cnt_last_month  else null end) as 上月在签门店数           
          from tmp_hww_bi0003_employee_detail p1 
         group by '当天'  
               ,p1.ds
               ,p1.employee_no_xq           -- 新签小二工号
               ,p1.employee_name_xq         -- 新签小二名字
               ,p1.l1_dept_name_xq     
               ,p1.l2_dept_name_xq     
               ,p1.l3_dept_name_xq          
       ) p1 
  left join 
       ( -- 小二目标
        select smonth
               ,employee_name                -- 员工名字
               ,employee_no                 -- 工号
               ,role_name                   -- 角色
               ,new_sign_shop_cnt_target    -- 新签数目标
               ,new_sign_target             -- 新签目标
               ,pg_target                   -- 品牌广告目标
          from kbreport.ska_employee_targets
       ) p2 
    on p1.employee_name_xq  = p2.employee_name
   and substring(p1.ds,1,6) = p2.smonth 

