-- 分片区 

select 

case when a.region_id='未知' then '未知'

       else a.salesregion_name end salesregion_name,

sum(a.total_follow_csm_qty) total_follow_csm_qty, -- 跟进消费者

sum(a.total_unfollow_csm_qty) total_unfollow_csm_qty, -- 未跟进消费者

sum(aa.inc_csm_qty) inc_csm_qty,

sum(a.total_core_csm_qty) total_core_csm_qty,

sum(aa.inc_core_csm_qty) inc_core_csm_qty,

sum(a.total_core_csm_mem_qty) total_core_csm_mem_qty,

sum(aa.inc_core_csm_mem_qty) inc_core_csm_mem_qty,

sum(a.total_active_core_csm_qty) total_active_core_csm_qty,

sum(d.active_consumer_qty) as active_core_csm_target,

sum(a.total_active_core_csm_qty)/sum(d.active_consumer_qty) as active_core_csm_target_ratio,

sum(fa.sunkol_qty) sunkol_qty,

sum(case when '${p_cmpy_code}' = '5137' then fa.tq_kol_qty else fa.kol_qty end) kol_qty,

sum(fa.moonkol_qty) moonkol_qty,

sum(fa.kolonsult_qty) kolonsult_qty,

sum(fa.starkol_qty) starkol_qty,

sum(fa.koc_qty) koc_qty,

sum(fa.poc_qty) poc_qty,

sum(b.mc_visit_times) as inc_visit_qty,

sum(b.mc_present_times) as inc_present_qty,

sum(c.act_times) as inc_mc_qty,

sum(c.act_fee_act) as inc_mc_amt,

sum(d.core_consumer_qty) as csm_124_target_qty,

round(sum(a.total_core_csm_qty)/sum(d.core_consumer_qty),4) csm_124_target_ratio

from (

-- 计算总数-已跟进消费者总数&未跟进消费者总数&核心消费者总数&核心消费者中会员覆盖数&活跃核心消费者数量（筛选结束时间之前的数据）

select 

   IFNULL(cd.cms_region_id,'未知') as region_id,

   IFNULL(cd.cms_region_name,'未知') as region_name,

   IFNULL(cd.cms_area_id,'未知') as salesregion_id,

   IFNULL(cd.cms_area_name,'未知') as salesregion_name,

   count(distinct case when cd.is_follow=1 then cd.one_id else null end) total_follow_csm_qty,

   -- 已跟进消费者总数

   count(distinct case when cd.is_follow<>1 then cd.one_id else null end) total_unfollow_csm_qty,

   -- 未跟进消费者总数

   count(distinct case when cd.is_follow=1 and cd.is_core_consumer=1 then cd.one_id else null end) total_core_csm_qty,

   -- 核心消费者总数

   count(distinct case when cd.is_follow=1 and is_core_consumer=1 and cd.member_id is not null then cd.one_id else null end) total_core_csm_mem_qty,

   -- 核心消费者中会员覆盖数

   count(distinct case when cd.is_follow=1 and cd.is_core_consumer=1 then td.one_id else null end) total_active_core_csm_qty

       -- 活跃核心消费者数量

from dm_r_cm_csm_cmpy_consumer_detail as cd 

-- 筛选有权限的数据

inner join (

-- 取只有公司权限数据

select 

distinct 

fa.company_code,

'1' as salesregion_id

from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa 

left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb

on fa.company_code=fb.company_code

union all 

-- 取只有片区的权限数据

select 

distinct 

fa.company_code,

dco.l5_id as salesregion_id

from (select company_code,per_code  from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa

left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb

on fa.company_code=fb.company_code

left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id

on fa.per_code=dco.l5_code

where fb.company_code is null

) as ta 

on cd.company_code=ta.company_code

and case when ta.salesregion_id<>'1' then cd.cms_area_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end

-- 如果权限表里片区字段为1，表示拥有该公司下的所有权限；

-- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限；

-- 原来的逻辑

-- left join (select * from dm_r_cm_csm_mc_ic_times_detail where business_year_id=(select max(business_year_id) from dm_r_cm_csm_mc_ic_times_detail))  as td -- 各财年触达次数

-- on cd.one_id=td.one_id   -- 本财年活跃核心消费者

--    and cd.cms_company_id=td.cms_company_id

-- 新的逻辑

left join [shuffle] (select one_id,cms_company_id 

   from dm_r_cm_csm_mc_ic_times_detail 

   where business_year_id=(select max(business_year_id) from dm_r_cm_csm_mc_ic_times_detail)

   group by one_id,cms_company_id 

   having sum(mc_ic_times)>1 -- 触达次数大于1的数据为活跃消费者

   )  as td -- 各财年触达次数

on cd.one_id=td.one_id   -- 本财年活跃核心消费者

   and cd.cms_company_id=td.cms_company_id

where 1=1

  and cd.is_emp= 0 and cd.is_acct = 0 and cd.is_authentication=1

  and cd.company_cate_code = '${p_cmpy_code}'

      and cd.csm_create_date <= '${edate}'

${if(len(region_code)==0,"","AND cd.cms_region_name  = '"+ region_code +"'")}

${if(len(area_code)==0,"","AND cd.cms_area_name  = '"+ area_code +"'")}

group by IFNULL(cd.cms_region_id,'未知') ,

    IFNULL(cd.cms_region_name,'未知'),

    IFNULL(cd.cms_area_id,'未知'),

    IFNULL(cd.cms_area_name,'未知')

) as a 

left join (

-- 新增消费者数量&新增核心消费者&核心消费者中新增会员覆盖数

select 

   IFNULL(cd.cms_region_id,'未知') as region_id,

   IFNULL(cd.cms_region_name,'未知') as region_name,

   IFNULL(cd.cms_area_id,'未知') as salesregion_id,

   IFNULL(cd.cms_area_name,'未知') as salesregion_name,

   count(distinct case when cd.csm_create_date >= '${p_begin_day}' then cd.one_id else null end) inc_csm_qty,

   -- 新增消费者

   count(distinct case when cd.is_core_consumer=1 and cd.csm_create_date >= '${p_begin_day}' then cd.one_id else null end) inc_core_csm_qty,

   -- 新增核心消费者

       count(distinct case when is_core_consumer=1 and cd.csm_create_date >= '${p_begin_day}' and cd.member_id is not null then cd.one_id else null end) inc_core_csm_mem_qty

   -- 核心消费者中新增会员覆盖数

from dm_r_cm_csm_cmpy_consumer_detail as cd 

-- 筛选有权限的数据

inner join (

-- 取只有公司权限数据

select 

distinct 

fa.company_code,

'1' as salesregion_id

from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa 

left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb

on fa.company_code=fb.company_code

union all 

-- 取只有片区的权限数据

select 

distinct 

fa.company_code,

dco.l5_id as salesregion_id

from (select company_code,per_code  from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa

left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb

on fa.company_code=fb.company_code

left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id

on fa.per_code=dco.l5_code

where fb.company_code is null

) as ta 

on cd.company_code=ta.company_code

and case when ta.salesregion_id<>'1' then cd.cms_area_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end

-- 如果权限表里片区字段为1，表示拥有该公司下的所有权限；

-- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限；

where 1=1

  and cd.is_emp= 0 and cd.is_acct = 0 and cd.is_authentication=1 and cd.is_follow=1

  and cd.company_cate_code = '${p_cmpy_code}'

      and cd.csm_create_date <= '${edate}'

${if(len(region_code)==0,"","AND cd.cms_region_name  = '"+ region_code +"'")}

${if(len(area_code)==0,"","AND cd.cms_area_name  = '"+ area_code +"'")}

group by IFNULL(cd.cms_region_id,'未知') ,

    IFNULL(cd.cms_region_name,'未知'),

    IFNULL(cd.cms_area_id,'未知'),

    IFNULL(cd.cms_area_name,'未知')

) as aa 

on a.region_id=aa.region_id and a.salesregion_id=aa.salesregion_id    

left join (

-- 核心消费者分城市  

select 

   IFNULL(cd.cms_region_id,'未知') as region_id,

   IFNULL(cd.cms_region_name,'未知') as region_name,

   IFNULL(cd.cms_area_id,'未知') as salesregion_id,

   IFNULL(cd.cms_area_name,'未知') as salesregion_name,

   count(distinct case when sdk.k_type_query = 'SunKOL' then cd.one_id else null end) sunkol_qty,

       --太阳级KOL总数量

   count(distinct case when sdk.k_type_query like '%KOL%' then cd.one_id else null end) kol_qty,

       -- KOL总数

   count(distinct case when sdk.k_type_query = 'KOLGeneral' then cd.one_id else null end) tq_kol_qty,

   -- 特曲普通会员

   count(distinct case when sdk.k_type_query = 'KOLConsult' then cd.one_id else null end) kolonsult_qty,

   -- KOL 品牌顾问

   count(distinct case when sdk.k_type_query = 'MoonKOL' then cd.one_id else null end) moonkol_qty,

       -- 月亮级KOL总数量

   count(distinct case when sdk.k_type_query = 'StarKOL' then cd.one_id else null end) starkol_qty,

       -- 星星级KOL总数量

   count(distinct case when sdk.k_type_query = 'KOC' then cd.one_id else null end) koc_qty,

       --   KOC总数量

   count(distinct case when sdk.k_type_query = 'POC' then cd.one_id else null end) poc_qty

       -- POC总数量

from dm_r_cm_csm_cmpy_consumer_detail as cd 

-- 筛选有权限的数据

inner join (

-- 取只有公司权限数据

select 

distinct 

fa.company_code,

'1' as salesregion_id

from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa 

left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb

on fa.company_code=fb.company_code

union all 

-- 取只有片区的权限数据

select 

distinct 

fa.company_code,

dco.l5_id as salesregion_id

from (select company_code,per_code  from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa

left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb

on fa.company_code=fb.company_code

left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id

on fa.per_code=dco.l5_code

where fb.company_code is null

) as ta 

on cd.company_code=ta.company_code

and case when ta.salesregion_id<>'1' then cd.cms_area_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end

-- 如果权限表里片区字段为1，表示拥有该公司下的所有权限；

-- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限；

left join [shuffle] ( -- 获取筛选结束日期时的k_type类型,且取最新的一条

        select

        sd.consumer_id,

        sd.field_value as k_type_query,

        ROW_NUMBER() over(partition by consumer_id,create_date order by create_time desc) as rn_num

        from dim.dim_csm_track_scd as sd

        where sd.field_type='k_type'  -- 限制类型为k_type

        and sd.start_date <=  DATE_FORMAT('${edate}', '%Y%m%d')

        and ifnull(sd.end_date,"99991231")  >= DATE_FORMAT('${edate}', '%Y%m%d')  -- business_date为前端传过来的筛选时间的结束日期

        ) as sdk

        on sdk.rn_num=1 and cd.ma_consumer_id=sdk.consumer_id

where 1=1

  and cd.is_emp= 0 and cd.is_acct = 0 and cd.is_follow=1 and cd.is_authentication=1

  and cd.company_cate_code = '${p_cmpy_code}'

      and cd.csm_create_date <= '${edate}'

${if(len(region_code)==0,"","AND cd.cms_region_name  = '"+ region_code +"'")}

${if(len(area_code)==0,"","AND cd.cms_area_name  = '"+ area_code +"'")}

group by  IFNULL(cd.cms_region_id,'未知'),

   IFNULL(cd.cms_region_name,'未知'),

   IFNULL(cd.cms_area_id,'未知'),

   IFNULL(cd.cms_area_name,'未知')

) as fa

on a.region_id=fa.region_id and a.salesregion_id=fa.salesregion_id

left join (

-- 消费者礼赠拜访指标(省份城市+销售公司)

  select 

    IFNULL(cd.region_id,'未知') region_id,

    IFNULL(cd.salesregion_id,'未知') salesregion_id,

    sum(mc_visit_times) as mc_visit_times, -- 拜访人数

    sum(mc_present_times) as mc_present_times  -- 礼赠人数

  from dm_t_cm_csm_date_consumer as cd

  -- 筛选有权限的数据

inner join (

-- 取只有公司权限数据

select 

distinct 

fa.company_code,

'1' as salesregion_id

from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa 

left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb

on fa.company_code=fb.company_code

union all 

-- 取只有片区的权限数据

select 

distinct 

fa.company_code,

dco.l5_id as salesregion_id

from (select company_code,per_code  from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa

left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb

on fa.company_code=fb.company_code

left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id

on fa.per_code=dco.l5_code

where fb.company_code is null

) as ta 

on cd.company_code=ta.company_code

and case when ta.salesregion_id<>'1' then cd.salesregion_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end

-- 如果权限表里片区字段为1，表示拥有该公司下的所有权限；

-- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限；

  where 1=1

  and company_cate_code = '${p_cmpy_code}'

  and business_date>='${p_begin_day}'

    ${if(len(region_code)==0,"","AND region_name  = '"+ region_code +"'")}

  ${if(len(area_code)==0,"","AND salesregion_name  = '"+ area_code +"'")}

  group by  IFNULL(cd.region_id,'未知'),

    IFNULL(cd.salesregion_id,'未知')

  having sum(mc_visit_times)>0 or sum(mc_present_times)>0

) as b on a.region_id=b.region_id and a.salesregion_id=b.salesregion_id

left join (

-- 活动场次和活动费用 --分城市

select 

IFNULL(ct.region_id,'未知') region_id,

    IFNULL(ct.salesregion_id,'未知') salesregion_id,

sum(inc_act_qty) as act_times, -- 活动场次

sum(inc_act_fee_amt) as act_fee_act -- 活动费用

from dm_r_cm_csm_core_csm_target as ct -- 核心目标达成明细表

-- 筛选有权限的数据

inner join (

-- 取只有公司权限数据

select 

distinct 

fa.company_code,

'1' as salesregion_id

from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa 

left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb

on fa.company_code=fb.company_code

union all 

-- 取只有片区的权限数据

select 

distinct 

fa.company_code,

dco.l5_id as salesregion_id

from (select company_code,per_code  from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa

left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb

on fa.company_code=fb.company_code

left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id

on fa.per_code=dco.l5_code

where fb.company_code is null

) as ta 

on ct.company_code=ta.company_code

and case when ta.salesregion_id<>'1' then ct.salesregion_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end

-- 如果权限表里片区字段为1，表示拥有该公司下的所有权限；

-- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限；

inner join (select * from dim.man_dim_cmpy_cate where theme='consumer') dcc  

on ct.cms_company_id=dcc.company_id

where 1=1

and dcc.company_cate_code = '${p_cmpy_code}'

and create_date>='${p_begin_day}'

    ${if(len(region_code)==0,"","AND region_name  = '"+ region_code +"'")}

  ${if(len(area_code)==0,"","AND salesregion_name  = '"+ area_code +"'")}

group by IFNULL(ct.region_id,'未知'),

    IFNULL(ct.salesregion_id,'未知')

) as c on a.region_id=c.region_id and a.salesregion_id=c.salesregion_id

left join (

  -- 124目标人数指标(省份城市)

  select 

    IFNULL(region_id,'未知') region_id,

    IFNULL(area_id,'未知') as salesregion_id,

    sum(core_consumer_qty) as core_consumer_qty, -- 目标核心消费者人数 

    sum(ifnull(active_consumer_qty,0)) as active_consumer_qty  -- 目标活跃消费者人数

    from man_consumer_target 

  where year_id = (select max(year_id) from man_consumer_target) -- 手工124目标取最新导入的那一年

    and cmpy_id = '${p_cmpy_code}'

  ${if(len(province_code)== 0,"","AND region_name = '" + region_code + "'")}

  ${if(len(city_code)== 0,"","AND area_name = '" + area_code + "'")}

  group by IFNULL(region_id,'未知'),

    IFNULL(area_id,'未知')

) as d on a.region_id=d.region_id and a.salesregion_id=d.salesregion_id

group by case when a.region_id='未知' then '未知'

       else a.salesregion_name end

having

sum(a.total_core_csm_qty)>0

order by

sum(a.total_core_csm_qty) desc;