-- MaxCompute SQL 
-- ********************************************************************--
-- author:飘有
-- create time:2025-02-24 19:18:47
-- ********************************************************************--
set odps.sql.type.system.odps2=true;
set odps.sql.hive.compatible=true;
with pf as (
        SELECT  a.leads_entity_id
        ,a.employee_no
        ,a.employee_name
        ,a.participant_employee_id_new
        ,b.employee_no AS participant_employee_no --陪访人
        ,b.employee_name AS participant_employee_name --陪访人
        ,a.ds
        ,b1.leader_employee_no
        ,b1.leader_employee_name
        ,b1.manager_no
        ,b1.manager_name
        ,b1.hire_date
        ,b1.stage_cur
        ,b1.remark
        ,case when  b.employee_no = b1.leader_employee_no then '是'
        when  b.employee_no = b1.manager_no then '是' 
        when b.role_name IN ('区域经理','高级区域经理') then '是'  
        when b.role_name like '%小组长%' then '是'  
         else '否' end as participant_by_leader --只要陪访人是小组长和cm就算
FROM    (
            SELECT  leads_entity_id
                    ,employee_no
                    ,employee_name
                    ,participant_employee_id_new
                    ,ds
                    ,l2_dept_name
            FROM    kbreport.ads_gd_sign_detail_df
            LATERAL VIEW EXPLODE(SPLIT(participant_employee_id,",")) a AS participant_employee_id_new
            WHERE   ds >= '20250301'
            AND     participant_employee_id IS NOT NULL
            AND     visit_style LIKE '%上门%' --上门拜访
            AND     l2_dept_name IN ('教培行业线','医疗行业线','美业行业线','泛低频混业特区','教培&美业行业线','泛低频SKA')
        ) a
LEFT JOIN kbdw.dim_gd_sale_employee_bloc b
ON      a.participant_employee_id_new = b.os_user_id
AND     b.ds = a.ds 
LEFT JOIN kbreport.dim_gd_sales_ylmyjp b1
ON      a.employee_no = b1.employee_no
AND     SUBSTR(a.ds,1,6) = b1.calmonth
)
SELECT  c.l3_dept_name
        ,c.l4_dept_name
        ,c.l5_dept_name
        ,c.hire_date
        ,c.remark
        ,c.calmonth
        ,c.employee_no --小二
        ,c.employee_name --小二姓名
        ,c.leader_employee_no
        ,c.leader_employee_name
        ,c.manager_no
        ,c.manager_name   
        ,c.stage_cur --当前阶段
        ,CASE   WHEN c.legal_work_days_mon <= 5 THEN 'W1'
                WHEN c.legal_work_days_mon <= 10 THEN 'W2'
                WHEN c.legal_work_days_mon <= 15 THEN 'W3'
                WHEN c.legal_work_days_mon <= 20 THEN 'W4'
                WHEN c.legal_work_days_mon >= 21 AND c.legal_work_days_mon <= 40 THEN 'W5-W8'
                WHEN c.legal_work_days_mon >= 41 AND c.legal_work_days_mon <= 60 THEN 'W9-W12' ELSE 'W13+'
        END AS stage_cur_monstart --月初阶段
        --月中间任何一天W8以内
        ,CASE WHEN (c.legal_work_days_cur <=40 or c.legal_work_days_mon <=40 )  and c.hire_date <= concat(DATE_FORMAT(DATE_ADD(TO_DATE(NOW()),-1),'yyyy-MM'),'-15') and c.leader_employee_no<>a.employee_no  and COALESCE(c.remark,'展业中') not IN ('已离职') then 1 else 0 end as 是否需陪访
        --陪访记录
        ,a.leads_entity_id  
        ,a.participant_employee_id_new
        ,a.participant_employee_no --陪访人
        ,a.participant_employee_name --陪访人
        ,a.ds --陪访日期
FROM   kbreport.dim_gd_sales_ylmyjp  c
LEFT JOIN   pf a 
ON      a.employee_no = c.employee_no --当时的 
AND     SUBSTR(a.ds,1,6) = c.calmonth --当时的 
and  participant_by_leader = '是' --陪访人是小组长或者cm
--where c.l3_dept_name in ('医疗区域','教培区域业务部','文旅区域业务部','美业区域业务部')
--and  (c.legal_work_days_cur <=40 or c.legal_work_days_mon <=40 )  --W8以内
--and c.hire_date <= concat(DATE_FORMAT(DATE_ADD(TO_DATE(NOW()),-1),'yyyy-MM'),'-15') --当月15及之前入职
--and c.leader_employee_no <> a.employee_no  --非小组长
--and COALESCE(c.remark,'展业中') not IN ('已离职') --在职





select
  from 
       (
        select ds 
               ,ali_code
               ,leads_entity_id
               ,poi_id
               ,poi_name
               ,shop_id
               ,partner_id
               ,province_name
               ,city_name
               ,district_name
               ,atag_id
               ,visit_style
               ,participant_employee_id
               ,participant_employee_name               
          from kbdw.dwd_gd_info_sale_leads_claim_visit_sign_di
         where ds <=  
           and ds >=      
           and is_visit <> 0 
       ) p1 



select p1.ds 
       ,p1.leads_id
       ,p1.shop_id
       ,p1.partner_id
       ,p1.visit_style                                                                       as 拜访类型
       ,case when p1.participant_employee_id is not null then 1 else 0 end                   as 是否被陪访
       ,p1.is_transform_60d                                                                  as 是否60日内转化 
       ,p1.transform_date                                                                    as 转化日期
       ,p1.new_sign_amt                                                                      as 新签年费
       ,p1.new_recharge_amt                                                                  as 首充广告
       ,p2.ali_code
       ,p2.employee_name    
       ,p2.role_type
       ,p2.role_name       
       ,p2.l2_dept_name
       ,p2.l3_dept_name
       ,p2.l4_dept_name                 
       ,p2.group_leader_name                                                                 as 小组长
       ,p2.cm_name                                                                           as CM名字
       ,p2.hire_months                                                                       as 入职月份
       ,p2.hire_date                                                                         as 入职日期
       ,p1.participant_employee_id                                                           as 陪访人ID
       ,p3.employee_name                                                                     as 陪访人
       ,case when p3.employee_name = p2.group_leader_name then '小组长陪访'
             when p3.employee_name = p2.cm_name           then 'CM陪访'
             else '其他人陪访' end as 陪访人角色
  from 
       (
        select p1.ds 
               ,p1.leads_id
               ,p1.shop_id
               ,p1.partner_id
               ,p1.visit_style
               ,p1.ali_code
               ,p1.participant_employee_id
               ,p1.participant_employee_name
               ,case when p2.leads_entity_id is not null and datediff(to_date(p2.ds,'yyyyMMdd'), to_date(p1.ds,'yyyyMMdd'), 'dd') between 0 and 60 then 1 else 0 end as is_transform_60d 
               ,p2.ds as transform_date 
               ,p2.new_sign_amt               
               ,p2.new_recharge_amt
          from
               (
                select ds 
                       ,leads_id
                       ,shop_id
                       ,partner_id
                       ,case when visit_style = 1 then '上门拜访'
                             when visit_style = 2 then '外呼拜访'
                             when visit_style = 3 then '电话拜访'
                             when visit_style = 4 then '其他拜访'
                             else null end as visit_style
                       ,ali_code
                       ,participant_employee_id
                       ,participant_employee_name
                  from kbdw.dwd_kb_sale_visit_result_di 
                 where ds >=  '20260401'
                   and ds <= max_pt('kbdw.dwd_kb_sale_visit_result_di ')
                --    and participant_employee_id is not null
                 group by ds 
                       ,leads_id
                       ,shop_id
                       ,partner_id
                       ,case when visit_style = 1 then '上门'
                             when visit_style = 2 then '外呼拜访'
                             when visit_style = 3 then '电话拜访'
                             when visit_style = 4 then '其他拜访'
                             else '其他拜访' end 
                       ,ali_code
                       ,participant_employee_id
                       ,participant_employee_name                
               ) p1 
          left join
               (
                select ds 
                       ,leads_entity_id
                       ,ali_code
                       ,sum(new_recharge_amt) as new_recharge_amt
                       ,sum(new_sign_amt)     as new_sign_amt
                  from kbdw.dwd_gd_info_sale_leads_claim_visit_sign_di 
                 where ds >= '20260401'
                   and ds <= max_pt('kbdw.dwd_kb_sale_visit_result_di ')
                   and (new_sign_shop_cnt > 0 or new_recharge_amt > 0)
                 group by ds 
                       ,leads_entity_id
                       ,ali_code 
               ) p2 
            on p1.ds = p2.ds 
           and p1.leads_id = p2.leads_entity_id
           and p1.ali_code = p2.ali_code
       ) p1 
 inner join 
       ( -- 架构
        select ds 
               ,ali_code
               ,employee_name
               ,l2_dept_name
               ,l3_dept_name
               ,l4_dept_name
               ,role_type
               ,role_name
               ,group_leader_name
               ,cm_name
               ,hire_months
               ,hire_date
          from kbreport.dim_gd_c33_employee_info_ds
         where ds <= max_pt('kbreport.dim_gd_c33_employee_info_ds') 
           and ds >= '20260401'
           and l2_dept_name in ('医疗行业线','教培行业线')
           and is_leave = 0
       ) p2
    on p1.ali_code = p2.ali_code
   and p1.ds       = p2.ds 
  left join   
       ( -- 
        select ds 
               ,os_user_id
               ,employee_name 
               ,l2_dept_name
               ,l3_dept_name
               ,l4_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 = '医疗&教培业务部'
           and is_leave = 0
       ) p3 
    on p1.participant_employee_id = p3.os_user_id
   and p1.ds = p3.ds  