with t2 as -- OA当月实时业绩
         (select '当月正常'                                                             as lx,
                 res.contract_no                                                        as 合同编号,
                 lx.title                                                               as 合同类型,
                 sum(res.res)                                                           as 实际业绩,
                 from_unixtime(res.intime)                                              as 业绩核算时间,
                 case
                     when day(from_unixtime(res.intime)) > 25
                         then left(date_add(from_unixtime(res.intime), interval 1 month), 7)
                     else left(from_unixtime(res.intime), 7) end                        as 所属财月,
                 us.realName                                                            as 业绩所属人,
                 us.workcode                                                            as 业绩所属人工号,
                 case
                     when res.achieve_stype = 0 then '非转介绍业绩'
                     when res.achieve_stype = 1 then '转介绍业绩'
                     when res.achieve_stype = 2 then '执行人业绩'
                     end                                                                as 分成类型,
                 null                                                                   as 分成比例,
                 dep.d5                                                                 as 业绩所属人公司,
                 concat_ws('/', dep.d2, dep.d3, dep.d4, dep.d5, dep.d6, dep.d7, dep.d8) as 业绩所属人组织架构,
                 base.cid,
                 if(lx.title regexp '资质|出让|求购', '资质', '其他')                   as 资质合同类型
          from dw_layer.dw_conm_account_rt res -- ,dw_layer.dw_conm_account_rt)) res
                   left join ods_oa.kl_conm_base base on base.id = res.cid
                   left join ods_oa.kl_users us on us.id = res.ouid -- 合同所属人
                   left join ods_oa.dept_ext dep on dep.id = us.departmentID -- 合同所属公司
                   left join (SELECT * FROM ods_oa.kl_auth_rule WHERE `pid` = '210') lx on lx.id = base.stype
                   left join ods_oa.dept_abbr abbr on abbr.id = dep.id
                   left join ods_oa.kl_conm_ycpersonnel yc on res.cid = yc.conmid
          -- 业绩时间在筛选时间范围内
          where res.intime
              between unix_timestamp(
                      str_to_date('2025-08-26',
                                  '%Y-%m-%d'))
              and unix_timestamp(
                          date_add(str_to_date('2025-09-25', '%Y-%m-%d'), interval 1
                                   day)) - 1
-- -- 操作时间：上月26到（当月26+预留时间）
            and res.ctime >= unix_timestamp(str_to_date(
                  concat('2025-09', '-26'), '%Y-%m-%d'))
            and res.ctime <= unix_timestamp(
                                     date_add(str_to_date(concat('2025-09', '-25'), '%Y-%m-%d'),
                                              interval 1
                                              day)) - 1
              + ifnull((select periodTime -- 先到这张表取对应月份的预留期
                        from ods_oa.kl_periodtimeconfig
                        where yearMonth = '2025-09'),
                       (select -- 如果第一张表没取到，取这张表里面的默认预留期
                               setting
                        from ods_oa.kl_sysconfig
                        where name = 'periodTime')) -- 当月预留期
            and res.baseuser_industry IN (1, 25, 28)
            and res.is_del = 0
            and res.status = 0
          group by res.id
          union all
-- 当月上传
          select '当月上传'                                                             as lx,
                 res.contract_no                                                        as 合同编号,
                 lx.title                                                               as 合同类型,
                 sum(res.res)                                                           as 实际业绩,
                 from_unixtime(res.ac_intime)                                           as 业绩核算时间,
                 case
                     when day(from_unixtime(res.ac_intime)) > 25
                         then left(date_add(from_unixtime(res.ac_intime), interval 1 month), 7)
                     else left(from_unixtime(res.ac_intime), 7) end                     as 所属财月,
                 us.realName                                                            as 业绩所属人,
                 us.workcode                                                            as 业绩所属人工号,
                 case
                     when res.achieve_stype = 0 then '非转介绍业绩'
                     when res.achieve_stype = 1 then '转介绍业绩'
                     when res.achieve_stype = 2 then '执行人业绩'
                     end                                                                as 分成类型,
                 null                                                                   as 分成比例,
                 dep.d5                                                                 as 业绩所属人公司,
                 concat_ws('/', dep.d2, dep.d3, dep.d4, dep.d5, dep.d6, dep.d7, dep.d8) as 业绩所属人组织架构,
                 base.cid,
                 if(lx.title regexp '资质|出让|求购', '资质', '其他')                   as 资质合同类型
#     from_unixtime(res.ac_intime) as 操作时间
          from dw_layer.dw_conm_account_rt res
                   left join ods_oa.kl_conm_base base on base.id = res.cid
                   left join (SELECT * FROM ods_oa.kl_auth_rule WHERE `pid` = '210') lx on lx.id = base.stype
              #          left join ods_oa.kl_department_ext dep
#                    on replace(res.contract_prefx, 'R', '') = dep.contract_prefx
#          left join ods_oa.dept_abbr abbr on abbr.id = dep.id
                   left join ods_oa.kl_conm_ycpersonnel yc on res.cid = yc.conmid
                   left join ods_oa.kl_users us on us.id = res.ouid -- 合同所属人
                   left join ods_oa.dept_ext dep on dep.id = us.departmentID -- 合同所属公司
          -- 业绩时间在筛选时间范围内
          where res.intime
              between unix_timestamp(
                      str_to_date('2025-08-26',
                                  '%Y-%m-%d'))
              and unix_timestamp(
                          date_add(str_to_date('2025-09-25', '%Y-%m-%d'), interval 1
                                   day)) - 1
            -- 实际业绩时间在筛选时间范围内
            and res.ac_intime >= unix_timestamp(str_to_date('2025-08-26',
                                                            '%Y-%m-%d'))
            and res.ac_intime <= unix_timestamp(
                                         date_add(str_to_date('2025-09-25', '%Y-%m-%d'), interval 1
                                                  day)) - 1

            and res.baseuser_industry IN (1, 25, 28)
            -- and abbr.short_name is not null
            and res.is_del = 3 -- 删除类型：上传
            and res.status = 0
          -- AND res.baseuser_industry IN (25, 28)
          group by res.id

          union all
          select '历史驳回'                                                             as lx,
                 res.contract_no                                                        as 合同编号,
                 lx.title                                                               as 合同类型,
                 sum(-res.res)                                                          as 实际业绩,
                 from_unixtime(res.del_time)                                            as 业绩核算时间,
                 case
                     when day(from_unixtime(res.del_time)) > 25
                         then left(date_add(from_unixtime(res.del_time), interval 1 month), 7)
                     else left(from_unixtime(res.del_time), 7) end                      as 所属财月,
                 us.realName                                                            as 业绩所属人,
                 us.workcode                                                            as 业绩所属人工号,
                 case
                     when res.achieve_stype = 0 then '非转介绍业绩'
                     when res.achieve_stype = 1 then '转介绍业绩'
                     when res.achieve_stype = 2 then '执行人业绩'
                     end                                                                as 分成类型,
                 null                                                                   as 分成比例,
                 dep.d5                                                                 as 业绩所属人公司,
                 concat_ws('/', dep.d2, dep.d3, dep.d4, dep.d5, dep.d6, dep.d7, dep.d8) as 业绩所属人组织架构,
                 base.cid,
                 if(lx.title regexp '资质|出让|求购', '资质', '其他')                   as 资质合同类型
#     from_unixtime(del_time) as 操作时间


          from dw_layer.dw_conm_account_rt res
                   left join ods_oa.kl_conm_base base on base.id = res.cid
                   left join (SELECT * FROM ods_oa.kl_auth_rule WHERE `pid` = '210') lx on lx.id = base.stype
              #          left join ods_oa.kl_department_ext dep
#                    on replace(res.contract_prefx, 'R', '') = dep.contract_prefx
#          left join ods_oa.dept_abbr abbr on abbr.id = dep.id
                   left join ods_oa.kl_conm_ycpersonnel yc on res.cid = yc.conmid
                   left join ods_oa.kl_users us on us.id = res.ouid -- 合同所属人
                   left join ods_oa.dept_ext dep on dep.id = us.departmentID -- 合同所属公司
          where res.intime -- 业绩时间：小于上月26
              < unix_timestamp(str_to_date(
                      concat('2025-09', '-26'), '%Y-%m-%d'))
            -- 操作时间：操作时间在筛选时间范围内，如果筛选的开始时间和结束时间在26号，25号节点，则加上预留期
            and del_time between
              unix_timestamp(str_to_date('2025-08-26', '%Y-%m-%d')) - 1
                  + if(day('2025-08-26') = 26, ifnull((select periodTime
                                                         from ods_oa.kl_periodtimeconfig
                                                         where yearMonth = '2025-09'),
                                                        (select -- 如果第一张表没取到，取这张表里面的默认预留期
                                                                setting
                                                         from ods_oa.kl_sysconfig
                                                         where name = 'periodTime')), 0)
              and unix_timestamp(
                          date_add(str_to_date('2025-09-25', '%Y-%m-%d'), interval 1
                                   day)) - 1
                  + if(day('2025-09-25') = 25, ifnull((select periodTime -- 先到这张表取对应月份的预留期
                                                       from ods_oa.kl_periodtimeconfig
                                                       where yearMonth = '2025-09'),
                                                      (select -- 如果第一张表没取到，取这张表里面的默认预留期
                                                              setting
                                                       from ods_oa.kl_sysconfig
                                                       where name = 'periodTime')), 0) -- 当月预留期
            and res.baseuser_industry IN (1, 25, 28)
            -- and abbr.short_name is not null
            and res.is_del = 1                                                         -- 删除类型：驳回
            and res.status = 0
          -- AND res.baseuser_industry IN (25, 28)
          group by res.id

          union all
          select '历史正常'                                                             as lx,
                 res.contract_no                                                        as 合同编号,
                 lx.title                                                               as 合同类型,
                 sum(res.res)                                                           as 实际业绩,
                 from_unixtime(res.ctime)                                               as 业绩核算时间,
                 case
                     when day(from_unixtime(res.ctime)) > 25
                         then left(date_add(from_unixtime(res.ctime), interval 1 month), 7)
                     else left(from_unixtime(res.ctime), 7) end                         as 所属财月,
                 us.realName                                                            as 业绩所属人,
                 us.workcode                                                            as 业绩所属人工号,
                 case
                     when res.achieve_stype = 0 then '非转介绍业绩'
                     when res.achieve_stype = 1 then '转介绍业绩'
                     when res.achieve_stype = 2 then '执行人业绩'
                     end                                                                as 分成类型,
                 null                                                                   as 分成比例,
                 dep.d5                                                                 as 业绩所属人公司,
                 concat_ws('/', dep.d2, dep.d3, dep.d4, dep.d5, dep.d6, dep.d7, dep.d8) as 业绩所属人组织架构,
                 base.cid,
                 if(lx.title regexp '资质|出让|求购', '资质', '其他')                   as 资质合同类型
#     from_unixtime(res.ctime) as 操作时间


          from dw_layer.dw_conm_account_rt res
                   left join ods_oa.kl_conm_base base on base.id = res.cid
                   left join (SELECT * FROM ods_oa.kl_auth_rule WHERE `pid` = '210') lx on lx.id = base.stype
              #          left join ods_oa.kl_department_ext dep
#                    on replace(res.contract_prefx, 'R', '') = dep.contract_prefx
#          left join ods_oa.dept_abbr abbr on abbr.id = dep.id
                   left join ods_oa.kl_conm_ycpersonnel yc on res.cid = yc.conmid
                   left join ods_oa.kl_users us on us.id = res.ouid -- 合同所属人
                   left join ods_oa.dept_ext dep on dep.id = us.departmentID -- 合同所属公司
          where res.intime -- 业绩时间：小于上月26
              < unix_timestamp(str_to_date(
                      concat('2025-09', '-26'), '%Y-%m-%d'))
            -- 操作时间：操作时间在筛选时间范围内，如果筛选的开始时间和结束时间在26号，25号节点，则加上预留期
            and res.ctime between
              unix_timestamp(str_to_date('2025-08-26', '%Y-%m-%d')) - 1
                  + if(day('2025-08-26') = 26, ifnull((select periodTime
                                                         from ods_oa.kl_periodtimeconfig
                                                         where yearMonth = '2025-09'),
                                                        (select -- 如果第一张表没取到，取这张表里面的默认预留期
                                                                setting
                                                         from ods_oa.kl_sysconfig
                                                         where name = 'periodTime')), 0)
              and unix_timestamp(
                          date_add(str_to_date('2025-09-25', '%Y-%m-%d'), interval 1
                                   day)) - 1
                  + if(day('2025-09-25') = 25, ifnull((select periodTime -- 先到这张表取对应月份的预留期
                                                       from ods_oa.kl_periodtimeconfig
                                                       where yearMonth = '2025-09'),
                                                      (select -- 如果第一张表没取到，取这张表里面的默认预留期
                                                              setting
                                                       from ods_oa.kl_sysconfig
                                                       where name = 'periodTime')), 0) -- 当月预留期
            and res.baseuser_industry IN (1, 25, 28)
            -- and abbr.short_name is not null
            and res.is_del = 0
            and res.status = 0
          --  AND res.baseuser_industry IN (25, 28)
          group by res.id

          union all

-- 历史上传

          select '历史上传'                                                             as lx,
                 res.contract_no                                                        as 合同编号,
                 lx.title                                                               as 合同类型,
                 sum(res.res)                                                           as 实际业绩,
                 from_unixtime(res.intime)                                              as 业绩核算时间,
                 case
                     when day(from_unixtime(res.intime)) > 25
                         then left(date_add(from_unixtime(res.intime), interval 1 month), 7)
                     else left(from_unixtime(res.intime), 7) end                        as 所属财月,
                 us.realName                                                            as 业绩所属人,
                 us.workcode                                                            as 业绩所属人工号,
                 case
                     when res.achieve_stype = 0 then '非转介绍业绩'
                     when res.achieve_stype = 1 then '转介绍业绩'
                     when res.achieve_stype = 2 then '执行人业绩'
                     end                                                                as 分成类型,
                 null                                                                   as 分成比例,
                 dep.d5                                                                 as 业绩所属人公司,
                 concat_ws('/', dep.d2, dep.d3, dep.d4, dep.d5, dep.d6, dep.d7, dep.d8) as 业绩所属人组织架构,
                 base.cid,
                 if(lx.title regexp '资质|出让|求购', '资质', '其他')                   as 资质合同类型
#     from_unixtime(res.intime) as 操作时间


          from dw_layer.dw_conm_account_rt res
                   left join ods_oa.kl_conm_base base on base.id = res.cid
                   left join (SELECT * FROM ods_oa.kl_auth_rule WHERE `pid` = '210') lx on lx.id = base.stype
              #          left join ods_oa.kl_department_ext dep
#                    on replace(res.contract_prefx, 'R', '') = dep.contract_prefx
#          left join ods_oa.dept_abbr abbr on abbr.id = dep.id
                   left join ods_oa.kl_conm_ycpersonnel yc on res.cid = yc.conmid
                   left join ods_oa.kl_users us on us.id = res.ouid -- 合同所属人
                   left join ods_oa.dept_ext dep on dep.id = us.departmentID -- 合同所属公司
          -- 业绩时间在筛选时间范围内
          where res.intime
              between unix_timestamp(
                      str_to_date('2025-08-26',
                                  '%Y-%m-%d'))
              and unix_timestamp(
                          date_add(str_to_date('2025-09-25', '%Y-%m-%d'), interval 1
                                   day)) - 1
            -- 实际业绩时间小于上月26
            and res.ac_intime <
                unix_timestamp(str_to_date(concat('2025-09', '-26'), '%Y-%m-%d'))

            and res.baseuser_industry IN (1, 25, 28)
            --  and abbr.short_name is not null
            and res.is_del = 3
            and res.status = 0
          -- AND res.baseuser_industry IN (25, 28)
          group by res.id
          union all
          select '历史已付定金'                                                         as lx,
                 res.contract_no                                                        as 合同编号,
                 lx.title                                                               as 合同类型,
                 sum(res.res)                                                           as 实际业绩,
                 from_unixtime(res.intime)                                              as 业绩核算时间,
                 case
                     when day(from_unixtime(res.intime)) > 25
                         then left(date_add(from_unixtime(res.intime), interval 1 month), 7)
                     else left(from_unixtime(res.intime), 7) end                        as 所属财月,
                 us.realName                                                            as 业绩所属人,
                 us.workcode                                                            as 业绩所属人工号,
                 case
                     when res.achieve_stype = 0 then '非转介绍业绩'
                     when res.achieve_stype = 1 then '转介绍业绩'
                     when res.achieve_stype = 2 then '执行人业绩'
                     end                                                                as 分成类型,
                 null                                                                   as 分成比例,
                 dep.d5                                                                 as 业绩所属人公司,
                 concat_ws('/', dep.d2, dep.d3, dep.d4, dep.d5, dep.d6, dep.d7, dep.d8) as 业绩所属人组织架构,
                 base.cid,
                 if(lx.title regexp '资质|出让|求购', '资质', '其他')                   as 资质合同类型
#     from_unixtime(res.intime) as 操作时间

          from dw_layer.dw_conm_account_rt res
                   left join ods_oa.kl_conm_base base on base.id = res.cid
                   left join (SELECT * FROM ods_oa.kl_auth_rule WHERE `pid` = '210') lx on lx.id = base.stype
              #          left join ods_oa.kl_department_ext dep
#                    on replace(res.contract_prefx, 'R', '') = dep.contract_prefx
#          left join ods_oa.dept_abbr abbr on abbr.id = dep.id
                   left join ods_oa.kl_conm_ycpersonnel yc on res.cid = yc.conmid
                   left join ods_oa.kl_users us on us.id = res.ouid -- 合同所属人
                   left join ods_oa.dept_ext dep on dep.id = us.departmentID -- 合同所属公司
          -- 业绩时间在筛选时间范围内
          where res.intime
              between unix_timestamp(
                      str_to_date('2025-08-26',
                                  '%Y-%m-%d'))
              and unix_timestamp(
                          date_add(str_to_date('2025-09-25', '%Y-%m-%d'), interval 1
                                   day)) - 1
            and res.baseuser_industry IN (1, 25, 28)
            -- and abbr.short_name is not null
            and res.is_del = 2
            and res.status = 0
          --  AND res.baseuser_industry IN (25, 28)
          group by res.id),
     t3 as (
-- KBOSS正常核算业绩

         select ht.contractnumber                        as 合同编号,
                case
                    when jylx = 0 then '交易-出让'
                    when jylx = 1 then '交易-求购/手续'
                    when jylx = 2 then '非交易'
                    when htlb = 0 then '注册合同'
                    when htlb = 1 then '许可证合同'
                    when htlb = 2 then '注销合同'
                    when htlb = 3 then '委托代理记账合同'
                    when htlb = 4 then '税筹合同'
                    when htlb = 5 then '知产合同(产品包)'
                    end                                  as 合同类型,
                (ma.amountOfPerformance) * (zxr.fcbl)       实际业绩,
                ma.yjjsrq                                as 业绩核算时间,
                case
                    when day(ma.yjjsrq) > 25 then left(date_add(ma.yjjsrq, interval 1 month), 7)
                    else left(ma.yjjsrq, 7) end          as 所属财月,
                source.LASTNAME                          as 业绩所属人,
                source.WORKCODE                             业绩所属人工号,
                case
                    when zxr.fclx = 0 then '内部分成'
                    when zxr.fclx = 1 then '转介绍'
                    end                                  as 分成类型,

                concat(round(zxr.fcbl * 100, 0), '%')    as 分成比例,
                dep.d4                                   as 业绩所属人分公司,
                concat_ws('/', dep.d7, dep.d6, dep.d5, dep.d4, dep.d3_1, dep.d3, dep.d2,
                          dep.d1)                        as 业绩所属人现组织架构,
                concat_ws('/', cdep.d7, cdep.d6, cdep.d5, cdep.d4, cdep.d3_1, cdep.d3, cdep.d2,
                          cdep.d1)                       as 合同创建组织,
                cdep.d4                                  as 合同创建公司,
                null                                     as 分成人原组织,
                concat_ws('/', trandep.d7, trandep.d6, trandep.d5, trandep.d4, trandep.d3_1, trandep.d3, trandep.d2,
                          trandep.d1)                    as 转介绍原组织,
                trandep.d4                               as 转介绍原公司,
                if(ht.jylx in (0, 1, 2), '资质', '其他') as 资质合同类型


         from ods_kboss.uf_performancesupplement ma #业绩核算表
                  left join ods_kboss.uf_ndustriacontrac ht on ma.contractname = ht.id #合同表
                  left join ods_kboss.uf_performancesupplement_dt2 zxr on ma.id = zxr.mainId
                  left join ods_kboss.hrmresource source on source.id = zxr.fcr
                  left join ods_kboss.dept_ext dep on dep.id = source.DEPARTMENTID
                  left join ods_kboss.dept_ext cdep on cdep.id = ht.applicationsector
                  left join ods_kboss.uf_businessopportunityinformation bus on bus.id = ht.ownership
                  left join ods_boss.transfer_clue tran on tran.link_sn = bus.businessopportunitycode #商机
                  left join ods_kboss.dept_ext trandep on trandep.id = tran.user_department
         where ma.yjjsrq between '2025-08-26' and '2025-09-25'
           --  concat_ws('/', dep.d7, dep.d6, dep.d5, dep.d4, dep.d3_1, dep.d3, dep.d2, dep.d1) regexp '企业部'
           and source.LASTNAME not regexp '测试'

         union all
-- KBOSS业绩补录
         select ht.contractnumber                        as 合同编号,
                case
                    when ht.jylx = 0 then '交易-出让'
                    when ht.jylx = 1 then '交易-求购/手续'
                    when ht.jylx = 2 then '非交易'
                    when ht.htlb = 0 then '注册合同'
                    when ht.htlb = 1 then '许可证合同'
                    when ht.htlb = 2 then '注销合同'
                    when ht.htlb = 3 then '委托代理记账合同'
                    when ht.htlb = 4 then '税筹合同'
                    when ht.htlb = 5 then '知产合同(产品包)'
                    end                                  as 合同类型,
                case
                    when ma.yjbllx in (2, 3) and zxr.yjje is not null then -zxr.yjje

                    when zxr.yjje is not null then zxr.yjje
                    else yj.sjyjje end                   as 实际业绩,
                ma.blrq                                  as 业绩核算时间,
                case
                    when day(ma.blrq) > 25 then left(date_add(ma.blrq, interval 1 month), 7)
                    else left(ma.blrq, 7) end            as 所属财月,
                hrmzxr.LASTNAME                          as 业绩所属人,
                hrmzxr.WORKCODE                          as 业绩所属人工号,
                case
                    when zxr.fclx = 0 then '内部分成'
                    when zxr.fclx = 1 then '转介绍'
                    end                                  as 分成类型,
                concat(round(zxr.fcbl * 100, 0), '%')    as 分成比例,
                dep.d4                                   as 业绩所属人分公司,

                concat_ws('/', dep.d7, dep.d6, dep.d5, dep.d4, dep.d3_1, dep.d3, dep.d2,
                          dep.d1)                        as 业绩所属人现组织架构,
                concat_ws('/', cdep.d7, cdep.d6, cdep.d5, cdep.d4, cdep.d3_1, cdep.d3, cdep.d2,
                          cdep.d1)                       as 合同创建组织,
                cdep.d4                                  as 合同创建公司,
                null                                     as 分成人原组织,
                concat_ws('/', trandep.d7, trandep.d6, trandep.d5, trandep.d4, trandep.d3_1, trandep.d3, trandep.d2,
                          trandep.d1)                    as 转介绍原组织,
                trandep.d4                               as 转介绍原公司,
                if(ht.jylx in (0, 1, 2), '资质', '其他') as 资质合同类型

         from ods_kboss.uf_pfmcsupplement ma #业绩补录表
                  left join ods_kboss.uf_pfmcsupplement_dt3 yj on yj.mainid = ma.id # ?
                  left join ods_kboss.uf_pfmcsupplement_dt5 zxr on ma.id = zxr.mainId
                  left join ods_kboss.uf_ndustriacontrac ht on ma.htmc = ht.id
                  left join ods_kboss.hrmresource hrmzxr on hrmzxr.id = zxr.fcr
                  left join ods_kboss.dept_ext dep on dep.id = hrmzxr.DEPARTMENTID
                  left join ods_kboss.dept_ext cdep on cdep.id = ht.applicationsector
                  left join ods_kboss.uf_businessopportunityinformation bus on bus.id = ht.ownership
                  left join ods_boss.transfer_clue tran on tran.link_sn = bus.businessopportunitycode #商机
                  left join ods_kboss.dept_ext trandep on trandep.id = tran.user_department
         where ma.blrq between '2025-08-26' and '2025-09-25'
           -- concat_ws('/', dep.d7, dep.d6, dep.d5, dep.d4, dep.d3_1, dep.d3, dep.d2, dep.d1) regexp '企业部'
           and hrmzxr.LASTNAME not regexp '测试'
           and ma.yjbllx <> 5 -- 退款退业绩类型不取, 因为退款表里面有同样的数据, 这里取的话数据就会重复

#    and htbh='琼FJ202308003'
         union all
-- KBOSS退业绩
         select ht.contractnumber                           as 合同编号,
                case
                    when ht.jylx = 0 then '交易-出让'
                    when ht.jylx = 1 then '交易-求购/手续'
                    when ht.jylx = 2 then '非交易'
                    when ht.htlb = 0 then '注册合同'
                    when ht.htlb = 1 then '许可证合同'
                    when ht.htlb = 2 then '注销合同'
                    when ht.htlb = 3 then '委托代理记账合同'
                    when ht.htlb = 4 then '税筹合同'
                    when ht.htlb = 5 then '知产合同(产品包)'
                    end                                     as 合同类型,
                -zxr.sdyj                                      实际业绩,
                tk.modedatacreatedate                       as 业绩核算时间,
                case
                    when day(tk.modedatacreatedate) > 25
                        then left(date_add(tk.modedatacreatedate, interval 1 month), 7)
                    else left(tk.modedatacreatedate, 7) end as 财月,

                zxrhrm.LASTNAME                             as 业绩所属人,
                zxrhrm.WORKCODE                             as 业绩所属人工号,

                case
                    when zxr.fclx = 0 then '内部分成'
                    when zxr.fclx = 1 then '转介绍'
                    end                                     as 分成类型,
                concat(round(zxr.fpbl * 100, 0), '%')       as 分成比例,

                dep.d4                                      as 业绩所属人分公司,
                concat_ws('/', dep.d7, dep.d6, dep.d5, dep.d4, dep.d3_1, dep.d3, dep.d2,
                          dep.d1)                           as 业绩所属人现组织架构,
                concat_ws('/', cdep.d7, cdep.d6, cdep.d5, cdep.d4, cdep.d3_1, cdep.d3, cdep.d2,
                          cdep.d1)                          as 合同创建组织,
                cdep.d4                                     as 合同创建公司,
                null                                        as 分成人原组织,
                concat_ws('/', trandep.d7, trandep.d6, trandep.d5, trandep.d4, trandep.d3_1, trandep.d3, trandep.d2,
                          trandep.d1)                       as 转介绍原组织,
                trandep.d4                                  as 转介绍原公司,
                if(ht.jylx in (0, 1, 2), '资质', '其他')    as 资质合同类型
         from ods_kboss.uf_refund tk
                  left join ods_kboss.uf_ndustriacontrac ht on tk.contractTitle = ht.id
                  left join ods_kboss.hrmresource hrm on hrm.id = ht.applicant
                  left join (select mainId, fclx, fcr, fpbl, sdyj
                             from ods_kboss.uf_refund_dt2
                             group by mainId, fclx, fcr) zxr on zxr.mainId = tk.id -- and zxr.fclx = '0'
                  left join ods_kboss.hrmresource zxrhrm on zxrhrm.id = zxr.fcr
                  left join ods_kboss.dept_ext dep on dep.id = zxrhrm.DEPARTMENTID
                  left join ods_kboss.dept_ext cdep on cdep.id = ht.applicationsector
                  left join ods_kboss.uf_businessopportunityinformation bus on bus.id = ht.ownership
                  left join ods_boss.transfer_clue tran on tran.link_sn = bus.businessopportunitycode #商机
                  left join ods_kboss.dept_ext trandep on trandep.id = tran.user_department
         where tk.modedatacreatedate between '2025-08-26' and '2025-09-25'
           -- concat_ws('/', dep.d7, dep.d6, dep.d5, dep.d4, dep.d3_1, dep.d3, dep.d2, dep.d1) regexp '企业部'
           and zxrhrm.LASTNAME not regexp '测试'

#    and     ht.contractnumber ='琼FJ202308003'


     ),
     t4 as (select con.code                                                as 合同编号,
                   case
                       when con.business_form = 'ENTERPRISE' then '企业'
                       when con.business_form = 'TALENT' and con.type = 'LPRCHT' then '人才'
                       when con.business_form = 'QUALIFICATION' then '资质'
                       else pt2.name end                                   as 业态,
                   pt.name                                                 as 合同类型,
                   res.actual_achieve_amount / 100                         as 实际业绩,
                   res.point_time                                          as 业绩核算时间,
                   res.achieve_month                                       as 所属财月,
                   us.name                                                 as 业绩所属人,
                   us.work_code                                            as 业绩所属人工号,
                   res.divide_type                                         as 分成类型,
                   concat(round(res.divide_achieve_ratio, 0), '%')         as 分成比例,
                   dep.d4                                                  as 业绩所属人分公司,
                   concat_ws('/', dep.d7, dep.d6, dep.d5, dep.d4, dep.d3_1, dep.d3, dep.d2,
                             dep.d1)                                       as 业绩所属人现组织架构,

                   if(con.business_form = 'QUALIFICATION', '资质', '其他') as 资质合同类型,

                   concat_ws('/', resdep2.d7, resdep2.d6, resdep2.d5, resdep2.d4, resdep2.d3_1, resdep2.d3, resdep2.d2,
                             resdep2.d1)                                   as 考核业绩原组织,
                   resdep2.d4                                              as 考核业绩原公司


            from ods_boss.achieve_detail res

                     left join ods_boss.ng_kb_user us on us.ng_user_id = res.achieve_user_id
                     left join ods_boss.contract con on res.contract_id = con.id
                     left join (select *
                                from ods_boss.ct_contract_tag
                                where tag_type = 3
                                  AND tag_value = 1) tag
                               on tag.contract_id = con.id -- 同步到OA的合同不会出现在这个表里面,从而得到在BOSS上创建的合同
                     left join ods_boss.kb_department resdep on resdep.id = res.first_department_id
                     left join ods_kboss.dept_ext resdep2 on resdep2.id = resdep.kb_department_id
                     left join ods_boss.pt_spec_template pt on pt.contract_type = con.type -- 意向业务类型
                     left join ods_boss.pt_spec_template pt2 on pt2.contract_type = con.business_form -- 业态
                     left join ods_kboss.dept_ext dep on dep.id = us.department_id

            where res.is_delete = 0
              and tag.contract_id is null
              and achieve_month = '2025-09'
              and us.name not regexp '测试'),

     detail as (select 合同编号,
                       case
                           when 分成类型 regexp '转介绍' and
                                concat_ws('/', trandep.d2, trandep.d3, trandep.d4, trandep.d5, trandep.d6, trandep.d7,
                                          trandep.d8) is not null and
                                concat_ws('/', trandep.d2, trandep.d3, trandep.d4, trandep.d5, trandep.d6, trandep.d7,
                                          trandep.d8) <> ''
                               then concat_ws('/', trandep.d2, trandep.d3, trandep.d4, trandep.d5, trandep.d6,
                                              trandep.d7, trandep.d8)
                           else 业绩所属人组织架构 end as 考核业绩原组织,

                       case
                           when 分成类型 regexp '转介绍' and
                                concat_ws('/', trandep.d2, trandep.d3, trandep.d4, trandep.d5, trandep.d6, trandep.d7,
                                          trandep.d8) is not null and
                                concat_ws('/', trandep.d2, trandep.d3, trandep.d4, trandep.d5, trandep.d6, trandep.d7,
                                          trandep.d8) <> ''
                               then trandep.d5
                           else 业绩所属人公司 end     as 考核业绩原公司,


                       合同类型,
                       合同类型 as 业态,
                       资质合同类型,
                       实际业绩,
                       业绩核算时间,
                       所属财月,
                       业绩所属人,
                       业绩所属人工号,
                       分成类型,
                       '0%'                               分成比例,

                       业绩所属人组织架构              as 业绩所属人现组织,
                       'OA'                            as 平台
                from t2 a
                         left join (select cid, u_company from ods_oa.kl_cus_referral group by cid) re on re.cid = a.cid
                         left join ods_oa.dept_ext trandep on trandep.id = re.u_company

                union all
                -- KBOSS
                select 合同编号,
                       case

                           when 分成类型 regexp '转介绍' and 转介绍原组织 is not null and 转介绍原组织 <> '' then 转介绍原组织
                           else 合同创建组织 end as 考核业绩原组织,
                       case

                           when 分成类型 regexp '转介绍' and 转介绍原组织 is not null and 转介绍原组织 <> '' then 转介绍原公司
                           else 合同创建公司 end as 考核业绩原公司,

                       合同类型,
                       合同类型 as 业态,
                       资质合同类型,
                       实际业绩,
                       业绩核算时间,
                       所属财月,
                       业绩所属人,
                       业绩所属人工号,
                       分成类型,
                       分成比例,
                       业绩所属人现组织架构      as 业绩所属人现组织,
                       'KBOSS'                   as 平台
                from t3
                union all
                select 合同编号,
                       考核业绩原组织,
                       考核业绩原公司,
                       合同类型,
                       业态,
                       资质合同类型,
                       实际业绩,
                       业绩核算时间,
                       所属财月,
                       业绩所属人,
                       业绩所属人工号,
                       分成类型,
                       分成比例,

                       业绩所属人现组织架构 as 业绩所属人现组织,
                       'BOSS'               as 平台

                from t4),



     jgj as (select

                 if(考核业绩原组织 like '%投资并购部%', '投资并购部',
                    replace(考核业绩原公司,'公司','')) branch,


                 case
                     when
                         (考核业绩原组织 like '%企业部%'
                             or (考核业绩原组织 like '%对外合作部%' and 业态 like '%企业%')
                             or (考核业绩原组织 like '%对外合作部%' and 合同类型 like '%人才引出%'))
                             and
                         if(资质合同类型 = '资质' and 分成类型 like '%所属%' and 考核业绩原组织 like '%企业部%', 1, 0) <>
                         1 then '企业部'
                     when 考核业绩原组织 like '%人才部%'
                         or (考核业绩原组织 like '%对外合作部%' and 业态 like '%人才%')
                         or (考核业绩原组织 like '%对外合作部%' and 合同类型 like '%人才引进%') then '人才部'
                     when 考核业绩原组织 like '%资质部%'
                         or 考核业绩原组织 like '%投资并购部%'
                         -- or 考核业绩原组织 like '%资质事业部%'  -- 这个页面不展示
                         or (分成类型 like '%所属%' and 资质合同类型 = '资质' and 考核业绩原组织 regexp '企业部|对外合作部')
                         then '资质部' -- 企业部的人创建的资质合同算给资质分公司
                     when substring_index(substring_index(考核业绩原组织, '/', 3), '/', -1) = '大数据事业部'
                         then '大数据事业部'
                     when substring_index(substring_index(考核业绩原组织, '/', 3), '/', -1) = '软件事业部'
                         then '软件事业部'
                     when 考核业绩原组织 like '%特种设备%' then '特种设备'
                     when 考核业绩原组织 like '%资质事业部%' then '资质事业部'
                     end       as   dep,
                 sum(实际业绩)/10000 as   res

             from detail
             where 考核业绩原组织 like '%建管家事业群%'
             group by branch, dep),
     lt as ( -- 猎头部业绩（当月填报），包括猎头事业部（重庆猎头部）
         select
             CASE WHEN jy.branch='建管家重庆' then '建管家重庆'
                  else jy.branch end as branch,
             jy.month_target, region_name, ifnull(jy.month_res, 0)  res,
             jy.people as 人数
         from (select branch, region_name from bi_data.cmo_branch where type = 2-- and id not in  (23)
              ) dept
                  right join (select *
                              from bi_data.cmo_headhunting qua
                              where
                                  time_node = '2025-09') jy on dept.branch = jy.branch
         where dept.branch<>'建管家成都' and  dept.branch<>'卓猎网兰州'
         group by dept.branch
         order by branch desc
     ),
     zz as (
         select b.branch as 分公司,
                '资质业绩'       as 业绩类型,
                '资质部' as 部门,
                ifnull(a.业绩,0)+ifnull(b.oa_per,0)+ifnull(b.transaction_xx,0)+ifnull(zjs_res_xx,0) as 业绩,
                b.month_target as 当月目标,
                b.people as 人数
         from bi_data.cmo_branch br
                  left join
              (
                  select branch           as 分公司,
                         sum(res) as 业绩
                  from jgj
                  where dep ='资质部' and  branch not regexp '投资并购部'
                  group by branch
              ) a on a.分公司=br.branch

                  left join (select *
                             from bi_data.cmo_qualifications qua
                             where time_node ='2025-09'
         ) b on br.branch = b.branch
         where type = 1
           and br.id not in (30, 31,37,38,39,40,41,49,50,51,52,53,54)
           and br.is_delete=0

     ),


     lp as (select b.branch                              as 分公司,
                   '人才业绩'                            as 业绩类型,
                   '人才部'                              as 部门,
                   ifnull(a.业绩, 0)  as 业绩,
                   b.当月目标,
                   b.人数
            from (
                     select branch           as 分公司,
                            sum(res) as 业绩
                     from jgj
                     where dep ='人才部'
                     group by branch
                 ) a
                     right join (select branch, sum(month_target) 当月目标, sum(people) as 人数
                                 from bi_data.cmo_talent rc
                                 where time_node = '2025-09'
                                 group by branch) b on a.分公司 = b.branch


            -- 企业业绩和（当月实时业绩）
            union all
            select b.branch                              as 分公司,
                   '企业业绩'                            as 业绩类型,
                   '企业部'                              as 部门,
                   ifnull(a.业绩, 0)  as 业绩,
                   b.当月目标,
                   b.人数
            from (
                     select branch           as 分公司,
                            sum(res) as 业绩
                     from jgj
                     where dep ='企业部'
                     group by branch


                 ) a
                     right join (select branch,
                                        sum(month_target) as 当月目标,
                                        sum(people)       as 人数
                                 from bi_data.cmo_enterprise
                                 where time_node = '2025-09'
                                 group by branch) b on a.分公司 = b.branch

     )



-- 销售大区
select 大区,

       sum(业绩)                                       as 业绩,
       sum(人数)                                       as 人数,

       if(sum(当月目标) = 0, sum(业绩), sum(当月目标)) as 目标,
       ifnull(sum(业绩)/ if(sum(当月目标)=0,sum(业绩),sum(当月目标)),0) as 完成度,
       sum(业绩)/sum(人数) as 在职人均,
       case when 大区='销售一区' then 1
            when 大区='销售二区' then 2
            when 大区='销售三区' then 3
            when 大区='销售四区' then 4
            when 大区='华中大区' then 4.1
            when 大区='华东大区' then 4.2
            when 大区='华南大区' then 4.3
            when 大区='华西大区' then 4.4
            when 大区='华北大区' then 4.5
            when 大区='北京运营中心' then 10
           end as 排序


from (select 分公司, 业绩类型, 部门, 业绩, 当月目标, 人数
      from lp -- 猎聘

      union all
      select 分公司, 业绩类型, 部门, 业绩, 当月目标, 人数
      from zz -- 资质
      where  分公司 not regexp '交易中心|投资并购'
      union all
      select -- 猎头业绩和（当月填报业绩）
             branch            as 分公司,
             '猎头业绩'        as 业绩类型,
             '猎头部'          as 部门,
             sum(res)          as 业绩,
             sum(month_target) as 当月目标,
             sum(人数)         as 人数
      from lt

      group by branch) t
         left join (select distinct if(d5 = '建管家北京公司', d4, d5)                      as 大区,
                                    if(d5 = '建管家北京公司', d5, d4)                      as 公司,
                                    replace(if(d5 = '建管家北京公司', d5, d4), '公司', '') as 公司显示值
                    from ods_kboss.dept_ext
                    where (d5 regexp '大区|^销售.*区$'
                        and d4 regexp '公司')
                       or d5 = '建管家北京公司'
                    order by 大区, 公司
) b on b.公司显示值 = t.分公司
where 1 = 1
group by b.大区

-- -------------------猎聘事业部（只包含人才部， 企业部）
union all
select
    '猎聘事业部' as 大区,

    sum(业绩) as 业绩,
    sum(人数) as 人数,
    sum(当月目标) as 当月目标,
    sum(业绩)/  sum(当月目标) as 完成率,
    sum(业绩)/ sum(人数) as 在职人均,
    5 as 排序


from lp
-- ---------------------资质事业部（包含资质部， 投资并购部）
union all
select
    '资质事业部'                                          as 大区,


    ifnull(sum(业绩),0) as 业绩,
    sum(人数)                                             as 人数,
    sum(当月目标)                                         as 当月目标,
    ifnull(sum(业绩),0)/   sum(当月目标) as 完成率,
    ifnull(sum(业绩),0) /          sum(人数)  as 在职人均,
    6 as 排序
from (select 分公司,
             业绩,
             当月目标,
             人数
      from zz
      where 分公司 not regexp '交易中心'
      union all

      select '资质事业部'   as 分公司,

             ifnull(res, 0) as 业绩,
             null           as 当月目标,
             null           as 人数
      from jgj
      where dep = '资质事业部') t
-- --------------------猎头事业部
union all
select -- 猎头业绩和（当月填报业绩）
       '猎头事业部'      as 大区,


       sum(res)          as 业绩,
       sum(人数)         as 人数,
       sum(month_target) as 当月目标,
       sum(res)/     sum(month_target) as 完成率,
       sum(res)/     sum(人数)  as 在职人均,
       7 as 排序
from lt

-- ------------------------大数据事业部
union all
select
    c.branch as 大区,


    ifnull(b.res,0)  as 业绩,
    c.people as 人数,
    c.`target_mon` as 当月目标,
    ifnull(b.res,0)/   c.`target_mon` as 完成率,
    b.res/   c.people as 在职人均,
    case when c.branch='大数据事业部' then 8
         else
             8.5 end  as 排序

from (select dep ,
             sum(`res`) as res
      from jgj
      where dep in ('大数据事业部','软件事业部')
      group by  dep
     ) b
         right join (select branch, time_node2 as mon, target_mon as target_mon, people
                     from (select
                               case when branch='建工大数据' then '大数据事业部' else branch end as branch,
                               time_node,
                               target_mon,
                               people,
                               day(time_node),
                               case
                                   when day(time_node) > 25
                                       then left(date_add(time_node, interval 1 month), 7)
                                   else left(time_node, 7) end as time_node2
                           from bi_data.cmo_jiangong
                           where target_mon is not null
                           order by branch, time_node desc) t
                     where time_node2 = '2025-09'
                     group by branch, time_node2)  c on b.dep = c.branch

-- -----------------金融事业部
union all
select
    '金融事业部' as 大区,

    sum(业绩) as 业绩,
    人数,
    月度目标 as 当月目标,
    sum(业绩) /    月度目标 as 完成率,
    sum(业绩) /    人数 as 在职人均,
    9 as 排序

from (select time_node,
             branch,
             if(day(time_node) > 26, left(date_add(time_node, interval 1 month), 7),
                left(time_node, 7)) as 财月,
             annual_target          as 年度目标,
             target_mon             as 月度目标,
             sum(res_day)           as 业绩,
             people as 人数
      from bi_data.cmo_caishui
      group by 财月) t
where 财月 = '2025-09'

union all

select
    '特种设备公司' as 大区,
    ifnull(res.总业绩,0) as 业绩,
    people as 人数,
    month_target as 当月目标,
    ifnull(res.总业绩,0)/ ifnull(month_target,0) as 完成率,
    ifnull(res.总业绩,0)/ifnull(people,0) as 在职人均,
    11 排序

from  bi_data.cmo_tezhongshebei
          left join (
    select
        '特种设备' as 公司,
        sum(res) as 总业绩
    from jgj
    WHERE dep ='特种设备'
) res on 1=1
where time_node='2025-09'
order by 排序