v_按天业绩统计_净实收计算 视图

CREATE ALGORITHM=UNDEFINED DEFINER=`neuron_new`@`%` SQL SECURITY DEFINER VIEW `v_按天业绩统计_净实收计算` AS with `vsy_normalized` as (select coalesce(`vsy`.`开单人姓名`,`vsy`.`开单人手机号`) AS `咨询师姓名`,`vsy`.`开单人手机号` AS `开单人标识`,`vsy`.`订单号` AS `订单号`,`vsy`.`实付金额` AS `实付金额原始`,(case when ((`vsy`.`是否退款` collate utf8mb4_general_ci) = ('是' collate utf8mb4_general_ci)) then '是' else '否' end) AS `是否退款`,`vsy`.`实付金额` AS `原始退款金额`,`vsy`.`完成时间` AS `完成时间`,`vsy`.`客户手机号` AS `客户手机号`,`vsy`.`支付时间` AS `交易时间`,cast(`vsy`.`支付时间` as date) AS `交易日期`,(case when (((`vsy`.`是否退款` collate utf8mb4_general_ci) = ('是' collate utf8mb4_general_ci)) and ((`vsy`.`售后状态` collate utf8mb4_general_ci) = ('已售后' collate utf8mb4_general_ci))) then `vsy`.`实付金额` else 0 end) AS `微盟售后实付金额`,NULL AS `手工退款金额`,NULL AS `smai_业绩判定类型` from `v_销售业绩` `vsy`), `smai_normalized` as (select `smai`.`order_consultant` AS `咨询师姓名`,`smai`.`order_consultant` AS `开单人标识`,`smai`.`id` AS `订单号`,`smai`.`transaction_amount` AS `实付金额原始`,(case when (`smai`.`refund_amount` > 0) then '是' else '否' end) AS `是否退款`,`smai`.`refund_amount` AS `原始退款金额`,`smai`.`complete_time` AS `完成时间`,`smai`.`contact_phone` AS `客户手机号`,`smai`.`refund_time` AS `交易时间`,cast(`smai`.`refund_time` as date) AS `交易日期`,0 AS `微盟售后实付金额`,`smai`.`refund_amount` AS `手工退款金额`,(case when ((`smai`.`customer_pool_performance_nature` collate utf8mb4_general_ci) = ('一开业绩' collate utf8mb4_general_ci)) then '一开业绩' when (((`smai`.`customer_pool_performance_nature` collate utf8mb4_general_ci) = ('二开业绩' collate utf8mb4_general_ci)) and ((`smai`.`is_cross_store` collate utf8mb4_general_ci) = ('跨门店' collate utf8mb4_general_ci))) then '一开业绩' when (((`smai`.`customer_pool_performance_nature` collate utf8mb4_general_ci) = ('二开业绩' collate utf8mb4_general_ci)) and ((`smai`.`is_cross_store` collate utf8mb4_general_ci) = ('同门店' collate utf8mb4_general_ci))) then '二开业绩' else '其他' end) AS `smai_业绩判定类型` from `sales_amount_manual_input` `smai`), `card_order_normalized` as (select coalesce(`co`.`sales_name`,`co`.`sales_id`) AS `咨询师姓名`,`co`.`sales_id` AS `开单人标识`,`co`.`order_no` AS `订单号`,`co`.`payment_amount` AS `实付金额原始`,(case when (`co`.`refund_amount` > 0) then '是' else '否' end) AS `是否退款`,`co`.`refund_amount` AS `原始退款金额`,`co`.`finish_time` AS `完成时间`,`co`.`buyer_phone` AS `客户手机号`,`co`.`created_at` AS `交易时间`,cast(`co`.`created_at` as date) AS `交易日期`,0 AS `微盟售后实付金额`,`co`.`refund_amount` AS `手工退款金额`,'一开业绩' AS `smai_业绩判定类型` from `crm_member_orders` `co`), `BaseDataUnion` as (select `vsy_normalized`.`咨询师姓名` AS `咨询师姓名`,`vsy_normalized`.`开单人标识` AS `开单人标识`,`vsy_normalized`.`订单号` AS `订单号`,`vsy_normalized`.`实付金额原始` AS `实付金额原始`,`vsy_normalized`.`是否退款` AS `是否退款`,`vsy_normalized`.`原始退款金额` AS `原始退款金额`,`vsy_normalized`.`完成时间` AS `完成时间`,`vsy_normalized`.`客户手机号` AS `客户手机号`,`vsy_normalized`.`交易时间` AS `交易时间`,`vsy_normalized`.`交易日期` AS `交易日期`,`vsy_normalized`.`微盟售后实付金额` AS `微盟售后实付金额`,`vsy_normalized`.`手工退款金额` AS `手工退款金额`,`vsy_normalized`.`smai_业绩判定类型` AS `smai_业绩判定类型` from `vsy_normalized` union all select `smai_normalized`.`咨询师姓名` AS `咨询师姓名`,`smai_normalized`.`开单人标识` AS `开单人标识`,`smai_normalized`.`订单号` AS `订单号`,`smai_normalized`.`实付金额原始` AS `实付金额原始`,`smai_normalized`.`是否退款` AS `是否退款`,`smai_normalized`.`原始退款金额` AS `原始退款金额`,`smai_normalized`.`完成时间` AS `完成时间`,`smai_normalized`.`客户手机号` AS `客户手机号`,`smai_normalized`.`交易时间` AS `交易时间`,`smai_normalized`.`交易日期` AS `交易日期`,`smai_normalized`.`微盟售后实付金额` AS `微盟售后实付金额`,`smai_normalized`.`手工退款金额` AS `手工退款金额`,`smai_normalized`.`smai_业绩判定类型` AS `smai_业绩判定类型` from `smai_normalized` union all select `card_order_normalized`.`咨询师姓名` AS `咨询师姓名`,`card_order_normalized`.`开单人标识` AS `开单人标识`,`card_order_normalized`.`订单号` AS `订单号`,`card_order_normalized`.`实付金额原始` AS `实付金额原始`,`card_order_normalized`.`是否退款` AS `是否退款`,`card_order_normalized`.`原始退款金额` AS `原始退款金额`,`card_order_normalized`.`完成时间` AS `完成时间`,`card_order_normalized`.`客户手机号` AS `客户手机号`,`card_order_normalized`.`交易时间` AS `交易时间`,`card_order_normalized`.`交易日期` AS `交易日期`,`card_order_normalized`.`微盟售后实付金额` AS `微盟售后实付金额`,`card_order_normalized`.`手工退款金额` AS `手工退款金额`,`card_order_normalized`.`smai_业绩判定类型` AS `smai_业绩判定类型` from `card_order_normalized`), `BaseDataWithPosition` as (select `bdu`.`咨询师姓名` AS `咨询师姓名`,`bdu`.`开单人标识` AS `开单人标识`,`bdu`.`订单号` AS `订单号`,`bdu`.`实付金额原始` AS `实付金额原始`,`bdu`.`是否退款` AS `是否退款`,`bdu`.`原始退款金额` AS `原始退款金额`,`bdu`.`完成时间` AS `完成时间`,`bdu`.`客户手机号` AS `客户手机号`,`bdu`.`交易时间` AS `交易时间`,`bdu`.`交易日期` AS `交易日期`,`bdu`.`微盟售后实付金额` AS `微盟售后实付金额`,`bdu`.`手工退款金额` AS `手工退款金额`,`bdu`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`erc`.`position` AS `开单人职位`,`erc`.`dept_level1` AS `开单人所属门店` from (`BaseDataUnion` `bdu` left join `employee_roster` `erc` on(((`bdu`.`咨询师姓名` collate utf8mb4_general_ci) = (`erc`.`employee_name` collate utf8mb4_general_ci))))), `FilteredBaseData` as (select `BaseDataWithPosition`.`咨询师姓名` AS `咨询师姓名`,`BaseDataWithPosition`.`开单人标识` AS `开单人标识`,`BaseDataWithPosition`.`订单号` AS `订单号`,`BaseDataWithPosition`.`实付金额原始` AS `实付金额原始`,`BaseDataWithPosition`.`是否退款` AS `是否退款`,`BaseDataWithPosition`.`原始退款金额` AS `原始退款金额`,`BaseDataWithPosition`.`完成时间` AS `完成时间`,`BaseDataWithPosition`.`客户手机号` AS `客户手机号`,`BaseDataWithPosition`.`交易时间` AS `交易时间`,`BaseDataWithPosition`.`交易日期` AS `交易日期`,`BaseDataWithPosition`.`微盟售后实付金额` AS `微盟售后实付金额`,`BaseDataWithPosition`.`手工退款金额` AS `手工退款金额`,`BaseDataWithPosition`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`BaseDataWithPosition`.`开单人职位` AS `开单人职位`,`BaseDataWithPosition`.`开单人所属门店` AS `开单人所属门店` from `BaseDataWithPosition` where ((`BaseDataWithPosition`.`咨询师姓名` is not null) and (`BaseDataWithPosition`.`咨询师姓名` <> '') and (`BaseDataWithPosition`.`开单人职位` is not null) and (`BaseDataWithPosition`.`开单人职位` like '%美学规划师%'))), `CustomerWithID` as (select `fbd`.`咨询师姓名` AS `咨询师姓名`,`fbd`.`开单人标识` AS `开单人标识`,`fbd`.`订单号` AS `订单号`,`fbd`.`实付金额原始` AS `实付金额原始`,`fbd`.`是否退款` AS `是否退款`,`fbd`.`原始退款金额` AS `原始退款金额`,`fbd`.`完成时间` AS `完成时间`,`fbd`.`客户手机号` AS `客户手机号`,`fbd`.`交易时间` AS `交易时间`,`fbd`.`交易日期` AS `交易日期`,`fbd`.`微盟售后实付金额` AS `微盟售后实付金额`,`fbd`.`手工退款金额` AS `手工退款金额`,`fbd`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`fbd`.`开单人职位` AS `开单人职位`,`fbd`.`开单人所属门店` AS `开单人所属门店`,`cdv`.`id` AS `客户档案ID`,`cdv`.`name` AS `客户姓名`,`cdv`.`sys_user_id` AS `当前客户池咨询师ID`,`cdv`.`sys_user_name` AS `客户池所属咨询师姓名_原始`,`cdv`.`pool_id` AS `客户池ID`,`cdv`.`pool_name` AS `客户池名称` from (`FilteredBaseData` `fbd` left join `neuron`.`customer_detail_view` `cdv` on(((`fbd`.`客户手机号` collate utf8mb4_general_ci) = (`cdv`.`main_phone` collate utf8mb4_general_ci))))), `CustomerPoolProxyAtOrderTime` as (select `cwid`.`咨询师姓名` AS `咨询师姓名`,`cwid`.`开单人标识` AS `开单人标识`,`cwid`.`订单号` AS `订单号`,`cwid`.`实付金额原始` AS `实付金额原始`,`cwid`.`是否退款` AS `是否退款`,`cwid`.`原始退款金额` AS `原始退款金额`,`cwid`.`完成时间` AS `完成时间`,`cwid`.`客户手机号` AS `客户手机号`,`cwid`.`交易时间` AS `交易时间`,`cwid`.`交易日期` AS `交易日期`,`cwid`.`微盟售后实付金额` AS `微盟售后实付金额`,`cwid`.`手工退款金额` AS `手工退款金额`,`cwid`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`cwid`.`开单人职位` AS `开单人职位`,`cwid`.`开单人所属门店` AS `开单人所属门店`,`cwid`.`客户档案ID` AS `客户档案ID`,`cwid`.`客户姓名` AS `客户姓名`,`cwid`.`当前客户池咨询师ID` AS `当前客户池咨询师ID`,`cwid`.`客户池所属咨询师姓名_原始` AS `客户池所属咨询师姓名_原始`,`cwid`.`客户池ID` AS `客户池ID`,`cwid`.`客户池名称` AS `客户池名称`,`cpp`.`proxy_manager_id` AS `代理人钉钉用户ID`,`cpp`.`original_manager_id` AS `原管理员钉钉用户ID`,`cpp`.`proxy_start_time` AS `代理开始时间`,`cpp`.`proxy_end_time` AS `代理结束时间`,(case when (`cpp`.`id` is not null) then 1 else 0 end) AS `是否有代理关系`,row_number() OVER (PARTITION BY `cwid`.`订单号`
ORDER BY
    (case when (`cpp`.`id` is not null) then 0 else 1 end),`cpp`.`proxy_start_time` desc )  AS `proxy_rn` from (`CustomerWithID` `cwid` left join `customer_pool_proxy` `cpp` on(((`cwid`.`客户池ID` = `cpp`.`customer_pool_id`) and (`cpp`.`STATUS` = 1) and (`cpp`.`del_flag` = 0) and (`cwid`.`交易时间` >= `cpp`.`proxy_start_time`) and (`cwid`.`交易时间` <= `cpp`.`proxy_end_time`))))), `OriginalManagerInfo` as (select `cppoat`.`咨询师姓名` AS `咨询师姓名`,`cppoat`.`开单人标识` AS `开单人标识`,`cppoat`.`订单号` AS `订单号`,`cppoat`.`实付金额原始` AS `实付金额原始`,`cppoat`.`是否退款` AS `是否退款`,`cppoat`.`原始退款金额` AS `原始退款金额`,`cppoat`.`完成时间` AS `完成时间`,`cppoat`.`客户手机号` AS `客户手机号`,`cppoat`.`交易时间` AS `交易时间`,`cppoat`.`交易日期` AS `交易日期`,`cppoat`.`微盟售后实付金额` AS `微盟售后实付金额`,`cppoat`.`手工退款金额` AS `手工退款金额`,`cppoat`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`cppoat`.`开单人职位` AS `开单人职位`,`cppoat`.`开单人所属门店` AS `开单人所属门店`,`cppoat`.`客户档案ID` AS `客户档案ID`,`cppoat`.`客户姓名` AS `客户姓名`,`cppoat`.`当前客户池咨询师ID` AS `当前客户池咨询师ID`,`cppoat`.`客户池所属咨询师姓名_原始` AS `客户池所属咨询师姓名_原始`,`cppoat`.`客户池ID` AS `客户池ID`,`cppoat`.`客户池名称` AS `客户池名称`,`cppoat`.`代理人钉钉用户ID` AS `代理人钉钉用户ID`,`cppoat`.`原管理员钉钉用户ID` AS `原管理员钉钉用户ID`,`cppoat`.`代理开始时间` AS `代理开始时间`,`cppoat`.`代理结束时间` AS `代理结束时间`,`cppoat`.`是否有代理关系` AS `是否有代理关系`,`cppoat`.`proxy_rn` AS `proxy_rn`,`emp_original`.`员工姓名` AS `原管理员姓名`,`emp_original`.`手机号` AS `原管理员手机号`,coalesce(`suv_original_phone`.`id`,`suv_original_name`.`id`) AS `原管理员系统用户ID` from (((`CustomerPoolProxyAtOrderTime` `cppoat` left join `v_钉钉员工表` `emp_original` on(((`cppoat`.`原管理员钉钉用户ID` collate utf8mb4_general_ci) = (`emp_original`.`钉钉用户ID` collate utf8mb4_general_ci)))) left join `neuron`.`sys_user_view` `suv_original_phone` on(((`emp_original`.`手机号` is not null) and (`emp_original`.`手机号` <> '') and (`suv_original_phone`.`phone` = (`emp_original`.`手机号` collate utf8mb4_general_ci))))) left join `neuron`.`sys_user_view` `suv_original_name` on(((isnull(`emp_original`.`手机号`) or (`emp_original`.`手机号` = '') or isnull(`suv_original_phone`.`id`)) and (`emp_original`.`员工姓名` is not null) and (`emp_original`.`员工姓名` <> '') and (`suv_original_name`.`name` = (`emp_original`.`员工姓名` collate utf8mb4_general_ci)))))), `ProxyManagerSysUser` as (select `omi`.`咨询师姓名` AS `咨询师姓名`,`omi`.`开单人标识` AS `开单人标识`,`omi`.`订单号` AS `订单号`,`omi`.`实付金额原始` AS `实付金额原始`,`omi`.`是否退款` AS `是否退款`,`omi`.`原始退款金额` AS `原始退款金额`,`omi`.`完成时间` AS `完成时间`,`omi`.`客户手机号` AS `客户手机号`,`omi`.`交易时间` AS `交易时间`,`omi`.`交易日期` AS `交易日期`,`omi`.`微盟售后实付金额` AS `微盟售后实付金额`,`omi`.`手工退款金额` AS `手工退款金额`,`omi`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`omi`.`开单人职位` AS `开单人职位`,`omi`.`开单人所属门店` AS `开单人所属门店`,`omi`.`客户档案ID` AS `客户档案ID`,`omi`.`客户姓名` AS `客户姓名`,`omi`.`当前客户池咨询师ID` AS `当前客户池咨询师ID`,`omi`.`客户池所属咨询师姓名_原始` AS `客户池所属咨询师姓名_原始`,`omi`.`客户池ID` AS `客户池ID`,`omi`.`客户池名称` AS `客户池名称`,`omi`.`代理人钉钉用户ID` AS `代理人钉钉用户ID`,`omi`.`原管理员钉钉用户ID` AS `原管理员钉钉用户ID`,`omi`.`代理开始时间` AS `代理开始时间`,`omi`.`代理结束时间` AS `代理结束时间`,`omi`.`是否有代理关系` AS `是否有代理关系`,`omi`.`proxy_rn` AS `proxy_rn`,`omi`.`原管理员姓名` AS `原管理员姓名`,`omi`.`原管理员手机号` AS `原管理员手机号`,`omi`.`原管理员系统用户ID` AS `原管理员系统用户ID`,coalesce(`suv_phone`.`id`,`suv_name`.`id`) AS `代理人系统用户ID`,`emp_proxy`.`员工姓名` AS `代理人姓名`,`emp_proxy`.`手机号` AS `代理人手机号` from (((`OriginalManagerInfo` `omi` left join `v_钉钉员工表` `emp_proxy` on(((`omi`.`代理人钉钉用户ID` collate utf8mb4_general_ci) = (`emp_proxy`.`钉钉用户ID` collate utf8mb4_general_ci)))) left join `neuron`.`sys_user_view` `suv_phone` on(((`emp_proxy`.`手机号` is not null) and (`emp_proxy`.`手机号` <> '') and (`suv_phone`.`phone` = (`emp_proxy`.`手机号` collate utf8mb4_general_ci))))) left join `neuron`.`sys_user_view` `suv_name` on(((isnull(`emp_proxy`.`手机号`) or (`emp_proxy`.`手机号` = '') or isnull(`suv_phone`.`id`)) and (`emp_proxy`.`员工姓名` is not null) and (`emp_proxy`.`员工姓名` <> '') and (`suv_name`.`name` = (`emp_proxy`.`员工姓名` collate utf8mb4_general_ci))))) where ((`omi`.`proxy_rn` = 1) or isnull(`omi`.`proxy_rn`))), `FinalConsultantInfo` as (select `pmsu`.`咨询师姓名` AS `咨询师姓名`,`pmsu`.`开单人标识` AS `开单人标识`,`pmsu`.`订单号` AS `订单号`,`pmsu`.`实付金额原始` AS `实付金额原始`,`pmsu`.`是否退款` AS `是否退款`,`pmsu`.`原始退款金额` AS `原始退款金额`,`pmsu`.`完成时间` AS `完成时间`,`pmsu`.`客户手机号` AS `客户手机号`,`pmsu`.`交易时间` AS `交易时间`,`pmsu`.`交易日期` AS `交易日期`,`pmsu`.`微盟售后实付金额` AS `微盟售后实付金额`,`pmsu`.`手工退款金额` AS `手工退款金额`,`pmsu`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`pmsu`.`开单人职位` AS `开单人职位`,`pmsu`.`开单人所属门店` AS `开单人所属门店`,`pmsu`.`客户档案ID` AS `客户档案ID`,`pmsu`.`客户姓名` AS `客户姓名`,`pmsu`.`当前客户池咨询师ID` AS `当前客户池咨询师ID`,`pmsu`.`客户池所属咨询师姓名_原始` AS `客户池所属咨询师姓名_原始`,`pmsu`.`客户池ID` AS `客户池ID`,`pmsu`.`客户池名称` AS `客户池名称`,`pmsu`.`代理人钉钉用户ID` AS `代理人钉钉用户ID`,`pmsu`.`原管理员钉钉用户ID` AS `原管理员钉钉用户ID`,`pmsu`.`代理开始时间` AS `代理开始时间`,`pmsu`.`代理结束时间` AS `代理结束时间`,`pmsu`.`是否有代理关系` AS `是否有代理关系`,`pmsu`.`proxy_rn` AS `proxy_rn`,`pmsu`.`原管理员姓名` AS `原管理员姓名`,`pmsu`.`原管理员手机号` AS `原管理员手机号`,`pmsu`.`原管理员系统用户ID` AS `原管理员系统用户ID`,`pmsu`.`代理人系统用户ID` AS `代理人系统用户ID`,`pmsu`.`代理人姓名` AS `代理人姓名`,`pmsu`.`代理人手机号` AS `代理人手机号`,(case when ((`pmsu`.`是否有代理关系` = 1) and (`pmsu`.`代理人系统用户ID` is not null)) then `pmsu`.`代理人系统用户ID` when ((`pmsu`.`是否有代理关系` = 1) and (`pmsu`.`原管理员系统用户ID` is not null)) then `pmsu`.`原管理员系统用户ID` when (`pmsu`.`当前客户池咨询师ID` is not null) then `pmsu`.`当前客户池咨询师ID` else NULL end) AS `最终客户池咨询师ID`,(case when (`pmsu`.`是否有代理关系` = 1) then coalesce(`pmsu`.`原管理员姓名`,`pmsu`.`客户池所属咨询师姓名_原始`) else `pmsu`.`客户池所属咨询师姓名_原始` end) AS `原客池归属咨询师姓名`,(case when (`pmsu`.`是否有代理关系` = 1) then `pmsu`.`代理人姓名` else NULL end) AS `代理客池咨询师姓名`,(case when (`pmsu`.`是否有代理关系` = 1) then concat(`pmsu`.`代理开始时间`,' 至 ',`pmsu`.`代理结束时间`) else NULL end) AS `代理时间段` from `ProxyManagerSysUser` `pmsu`), `JoinedData` as (select `fci`.`咨询师姓名` AS `咨询师姓名`,`fci`.`开单人标识` AS `开单人标识`,`fci`.`订单号` AS `订单号`,`fci`.`实付金额原始` AS `实付金额原始`,`fci`.`是否退款` AS `是否退款`,`fci`.`原始退款金额` AS `原始退款金额`,`fci`.`完成时间` AS `完成时间`,`fci`.`客户手机号` AS `客户手机号`,`fci`.`交易时间` AS `交易时间`,`fci`.`交易日期` AS `交易日期`,`fci`.`微盟售后实付金额` AS `微盟售后实付金额`,`fci`.`手工退款金额` AS `手工退款金额`,`fci`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`fci`.`开单人职位` AS `开单人职位`,`fci`.`开单人所属门店` AS `开单人所属门店`,`fci`.`客户档案ID` AS `客户档案ID`,`fci`.`客户姓名` AS `客户姓名`,`fci`.`当前客户池咨询师ID` AS `当前客户池咨询师ID`,`fci`.`客户池所属咨询师姓名_原始` AS `客户池所属咨询师姓名_原始`,`fci`.`客户池ID` AS `客户池ID`,`fci`.`客户池名称` AS `客户池名称`,`fci`.`代理人钉钉用户ID` AS `代理人钉钉用户ID`,`fci`.`原管理员钉钉用户ID` AS `原管理员钉钉用户ID`,`fci`.`代理开始时间` AS `代理开始时间`,`fci`.`代理结束时间` AS `代理结束时间`,`fci`.`是否有代理关系` AS `是否有代理关系`,`fci`.`proxy_rn` AS `proxy_rn`,`fci`.`原管理员姓名` AS `原管理员姓名`,`fci`.`原管理员手机号` AS `原管理员手机号`,`fci`.`原管理员系统用户ID` AS `原管理员系统用户ID`,`fci`.`代理人系统用户ID` AS `代理人系统用户ID`,`fci`.`代理人姓名` AS `代理人姓名`,`fci`.`代理人手机号` AS `代理人手机号`,`fci`.`最终客户池咨询师ID` AS `最终客户池咨询师ID`,`fci`.`原客池归属咨询师姓名` AS `原客池归属咨询师姓名`,`fci`.`代理客池咨询师姓名` AS `代理客池咨询师姓名`,`fci`.`代理时间段` AS `代理时间段`,`sysuv`.`name` AS `客户池所属咨询师姓名_修正`,`sysuv`.`phone` AS `客户池所属咨询师手机号`,`ercc`.`dept_level1` AS `客户池咨询师所属门店`,`sysuv`.`role_name` AS `客户池咨询师角色` from ((`FinalConsultantInfo` `fci` left join `neuron`.`sys_user_view` `sysuv` on(((`fci`.`最终客户池咨询师ID` collate utf8mb4_general_ci) = (`sysuv`.`id` collate utf8mb4_general_ci)))) left join `employee_roster` `ercc` on(((`sysuv`.`name` collate utf8mb4_general_ci) = (`ercc`.`employee_name` collate utf8mb4_general_ci))))), `PerformanceCalculated` as (select `jd`.`咨询师姓名` AS `咨询师姓名`,`jd`.`开单人标识` AS `开单人标识`,`jd`.`订单号` AS `订单号`,`jd`.`实付金额原始` AS `实付金额原始`,`jd`.`是否退款` AS `是否退款`,`jd`.`原始退款金额` AS `原始退款金额`,`jd`.`完成时间` AS `完成时间`,`jd`.`客户手机号` AS `客户手机号`,`jd`.`交易时间` AS `交易时间`,`jd`.`交易日期` AS `交易日期`,`jd`.`微盟售后实付金额` AS `微盟售后实付金额`,`jd`.`手工退款金额` AS `手工退款金额`,`jd`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`jd`.`开单人职位` AS `开单人职位`,`jd`.`开单人所属门店` AS `开单人所属门店`,`jd`.`客户档案ID` AS `客户档案ID`,`jd`.`客户姓名` AS `客户姓名`,`jd`.`当前客户池咨询师ID` AS `当前客户池咨询师ID`,`jd`.`客户池所属咨询师姓名_原始` AS `客户池所属咨询师姓名_原始`,`jd`.`客户池ID` AS `客户池ID`,`jd`.`客户池名称` AS `客户池名称`,`jd`.`代理人钉钉用户ID` AS `代理人钉钉用户ID`,`jd`.`原管理员钉钉用户ID` AS `原管理员钉钉用户ID`,`jd`.`代理开始时间` AS `代理开始时间`,`jd`.`代理结束时间` AS `代理结束时间`,`jd`.`是否有代理关系` AS `是否有代理关系`,`jd`.`proxy_rn` AS `proxy_rn`,`jd`.`原管理员姓名` AS `原管理员姓名`,`jd`.`原管理员手机号` AS `原管理员手机号`,`jd`.`原管理员系统用户ID` AS `原管理员系统用户ID`,`jd`.`代理人系统用户ID` AS `代理人系统用户ID`,`jd`.`代理人姓名` AS `代理人姓名`,`jd`.`代理人手机号` AS `代理人手机号`,`jd`.`最终客户池咨询师ID` AS `最终客户池咨询师ID`,`jd`.`原客池归属咨询师姓名` AS `原客池归属咨询师姓名`,`jd`.`代理客池咨询师姓名` AS `代理客池咨询师姓名`,`jd`.`代理时间段` AS `代理时间段`,`jd`.`客户池所属咨询师姓名_修正` AS `客户池所属咨询师姓名_修正`,`jd`.`客户池所属咨询师手机号` AS `客户池所属咨询师手机号`,`jd`.`客户池咨询师所属门店` AS `客户池咨询师所属门店`,`jd`.`客户池咨询师角色` AS `客户池咨询师角色`,(case when (`jd`.`smai_业绩判定类型` is not null) then `jd`.`smai_业绩判定类型` when (isnull(`jd`.`最终客户池咨询师ID`) or (`jd`.`最终客户池咨询师ID` = '') or (`jd`.`最终客户池咨询师ID` = '0')) then '一开业绩' when (((`jd`.`开单人标识` collate utf8mb4_general_ci) = (`jd`.`客户池所属咨询师手机号` collate utf8mb4_general_ci)) or ((`jd`.`咨询师姓名` collate utf8mb4_general_ci) = (`jd`.`客户池所属咨询师姓名_修正` collate utf8mb4_general_ci))) then '一开业绩' when (((`jd`.`开单人所属门店` is not null) and (`jd`.`客户池咨询师所属门店` is not null) and ((`jd`.`开单人所属门店` collate utf8mb4_general_ci) <> (`jd`.`客户池咨询师所属门店` collate utf8mb4_general_ci))) or ((`jd`.`开单人所属门店` is not null) and isnull(`jd`.`客户池咨询师所属门店`)) or (isnull(`jd`.`开单人所属门店`) and (`jd`.`客户池咨询师所属门店` is not null))) then '一开业绩' when (((`jd`.`开单人所属门店` collate utf8mb4_general_ci) = (`jd`.`客户池咨询师所属门店` collate utf8mb4_general_ci)) and ((`jd`.`开单人标识` collate utf8mb4_general_ci) <> (`jd`.`最终客户池咨询师ID` collate utf8mb4_general_ci))) then '二开-归属他人' else '其他' end) AS `业绩判定类型` from `JoinedData` `jd`), `RefundAllocation` as (select `pc`.`咨询师姓名` AS `咨询师姓名`,`pc`.`开单人标识` AS `开单人标识`,`pc`.`订单号` AS `订单号`,`pc`.`实付金额原始` AS `实付金额原始`,`pc`.`是否退款` AS `是否退款`,`pc`.`原始退款金额` AS `原始退款金额`,`pc`.`完成时间` AS `完成时间`,`pc`.`客户手机号` AS `客户手机号`,`pc`.`交易时间` AS `交易时间`,`pc`.`交易日期` AS `交易日期`,`pc`.`微盟售后实付金额` AS `微盟售后实付金额`,`pc`.`手工退款金额` AS `手工退款金额`,`pc`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`pc`.`开单人职位` AS `开单人职位`,`pc`.`开单人所属门店` AS `开单人所属门店`,`pc`.`客户档案ID` AS `客户档案ID`,`pc`.`客户姓名` AS `客户姓名`,`pc`.`当前客户池咨询师ID` AS `当前客户池咨询师ID`,`pc`.`客户池所属咨询师姓名_原始` AS `客户池所属咨询师姓名_原始`,`pc`.`客户池ID` AS `客户池ID`,`pc`.`客户池名称` AS `客户池名称`,`pc`.`代理人钉钉用户ID` AS `代理人钉钉用户ID`,`pc`.`原管理员钉钉用户ID` AS `原管理员钉钉用户ID`,`pc`.`代理开始时间` AS `代理开始时间`,`pc`.`代理结束时间` AS `代理结束时间`,`pc`.`是否有代理关系` AS `是否有代理关系`,`pc`.`proxy_rn` AS `proxy_rn`,`pc`.`原管理员姓名` AS `原管理员姓名`,`pc`.`原管理员手机号` AS `原管理员手机号`,`pc`.`原管理员系统用户ID` AS `原管理员系统用户ID`,`pc`.`代理人系统用户ID` AS `代理人系统用户ID`,`pc`.`代理人姓名` AS `代理人姓名`,`pc`.`代理人手机号` AS `代理人手机号`,`pc`.`最终客户池咨询师ID` AS `最终客户池咨询师ID`,`pc`.`原客池归属咨询师姓名` AS `原客池归属咨询师姓名`,`pc`.`代理客池咨询师姓名` AS `代理客池咨询师姓名`,`pc`.`代理时间段` AS `代理时间段`,`pc`.`客户池所属咨询师姓名_修正` AS `客户池所属咨询师姓名_修正`,`pc`.`客户池所属咨询师手机号` AS `客户池所属咨询师手机号`,`pc`.`客户池咨询师所属门店` AS `客户池咨询师所属门店`,`pc`.`客户池咨询师角色` AS `客户池咨询师角色`,`pc`.`业绩判定类型` AS `业绩判定类型`,(coalesce(`pc`.`微盟售后实付金额`,0) + coalesce(`pc`.`手工退款金额`,0)) AS `总退款金额`,(case when (`pc`.`业绩判定类型` = '一开业绩') then `pc`.`咨询师姓名` when ((`pc`.`业绩判定类型` = '二开-归属他人') or (`pc`.`业绩判定类型` = '二开业绩')) then `pc`.`客户池所属咨询师姓名_修正` else `pc`.`咨询师姓名` end) AS `退款承担咨询师姓名`,(case when (`pc`.`业绩判定类型` = '一开业绩') then `pc`.`咨询师姓名` when ((`pc`.`业绩判定类型` = '二开-归属他人') or (`pc`.`业绩判定类型` = '二开业绩')) then `pc`.`客户池所属咨询师姓名_修正` else `pc`.`咨询师姓名` end) AS `业绩归属咨询师姓名` from `PerformanceCalculated` `pc`), `DistinctOrders` as (select `ra`.`咨询师姓名` AS `咨询师姓名`,`ra`.`开单人标识` AS `开单人标识`,`ra`.`订单号` AS `订单号`,`ra`.`实付金额原始` AS `实付金额原始`,`ra`.`是否退款` AS `是否退款`,`ra`.`原始退款金额` AS `原始退款金额`,`ra`.`完成时间` AS `完成时间`,`ra`.`客户手机号` AS `客户手机号`,`ra`.`交易时间` AS `交易时间`,`ra`.`交易日期` AS `交易日期`,`ra`.`微盟售后实付金额` AS `微盟售后实付金额`,`ra`.`手工退款金额` AS `手工退款金额`,`ra`.`smai_业绩判定类型` AS `smai_业绩判定类型`,`ra`.`开单人职位` AS `开单人职位`,`ra`.`开单人所属门店` AS `开单人所属门店`,`ra`.`客户档案ID` AS `客户档案ID`,`ra`.`客户姓名` AS `客户姓名`,`ra`.`当前客户池咨询师ID` AS `当前客户池咨询师ID`,`ra`.`客户池所属咨询师姓名_原始` AS `客户池所属咨询师姓名_原始`,`ra`.`客户池ID` AS `客户池ID`,`ra`.`客户池名称` AS `客户池名称`,`ra`.`代理人钉钉用户ID` AS `代理人钉钉用户ID`,`ra`.`原管理员钉钉用户ID` AS `原管理员钉钉用户ID`,`ra`.`代理开始时间` AS `代理开始时间`,`ra`.`代理结束时间` AS `代理结束时间`,`ra`.`是否有代理关系` AS `是否有代理关系`,`ra`.`proxy_rn` AS `proxy_rn`,`ra`.`原管理员姓名` AS `原管理员姓名`,`ra`.`原管理员手机号` AS `原管理员手机号`,`ra`.`原管理员系统用户ID` AS `原管理员系统用户ID`,`ra`.`代理人系统用户ID` AS `代理人系统用户ID`,`ra`.`代理人姓名` AS `代理人姓名`,`ra`.`代理人手机号` AS `代理人手机号`,`ra`.`最终客户池咨询师ID` AS `最终客户池咨询师ID`,`ra`.`原客池归属咨询师姓名` AS `原客池归属咨询师姓名`,`ra`.`代理客池咨询师姓名` AS `代理客池咨询师姓名`,`ra`.`代理时间段` AS `代理时间段`,`ra`.`客户池所属咨询师姓名_修正` AS `客户池所属咨询师姓名_修正`,`ra`.`客户池所属咨询师手机号` AS `客户池所属咨询师手机号`,`ra`.`客户池咨询师所属门店` AS `客户池咨询师所属门店`,`ra`.`客户池咨询师角色` AS `客户池咨询师角色`,`ra`.`业绩判定类型` AS `业绩判定类型`,`ra`.`总退款金额` AS `总退款金额`,`ra`.`退款承担咨询师姓名` AS `退款承担咨询师姓名`,`ra`.`业绩归属咨询师姓名` AS `业绩归属咨询师姓名`,row_number() OVER (PARTITION BY `ra`.`订单号`
ORDER BY
    `ra`.`交易时间` desc,`ra`.`完成时间` desc )  AS `rn` from `RefundAllocation` `ra`), `OrderDetails` as (select `do`.`交易日期` AS `交易日期`,`do`.`交易时间` AS `交易时间`,`do`.`订单号` AS `订单号`,`do`.`咨询师姓名` AS `开单人姓名`,`do`.`开单人所属门店` AS `开单人所属门店`,`do`.`客户手机号` AS `客户手机号`,`do`.`客户姓名` AS `客户姓名`,`do`.`客户池ID` AS `客户池ID`,`do`.`客户池名称` AS `客户池名称`,`do`.`原客池归属咨询师姓名` AS `原客池归属咨询师姓名`,`do`.`客户池所属咨询师姓名_原始` AS `原客池咨询师姓名_原始`,`do`.`是否有代理关系` AS `是否有代理关系`,`do`.`代理客池咨询师姓名` AS `代理客池咨询师姓名`,`do`.`代理时间段` AS `代理时间段`,`do`.`代理人姓名` AS `代理人姓名`,`do`.`代理人手机号` AS `代理人手机号`,`do`.`客户池所属咨询师姓名_修正` AS `最终客户池咨询师姓名`,`do`.`客户池咨询师所属门店` AS `最终客户池咨询师所属门店`,`do`.`业绩判定类型` AS `业绩判定类型`,`do`.`业绩归属咨询师姓名` AS `业绩归属咨询师姓名`,`do`.`退款承担咨询师姓名` AS `退款承担咨询师姓名`,`do`.`实付金额原始` AS `实付金额原始`,`do`.`总退款金额` AS `总退款金额`,(`do`.`实付金额原始` - `do`.`总退款金额`) AS `净实收金额`,`do`.`是否退款` AS `是否退款`,`do`.`完成时间` AS `完成时间` from `DistinctOrders` `do` where (`do`.`rn` = 1)) select `od`.`交易日期` AS `交易日期`,`od`.`交易时间` AS `交易时间`,`od`.`订单号` AS `订单号`,`od`.`开单人姓名` AS `开单人姓名`,`od`.`开单人所属门店` AS `开单人所属门店`,`od`.`客户手机号` AS `客户手机号`,`od`.`客户姓名` AS `客户姓名`,`od`.`客户池ID` AS `客户池ID`,`od`.`客户池名称` AS `客户池名称`,`od`.`原客池归属咨询师姓名` AS `原客池归属咨询师姓名`,`od`.`原客池咨询师姓名_原始` AS `原客池咨询师姓名_原始`,(case when (`od`.`是否有代理关系` = 1) then '是' else '否' end) AS `是否有代理关系`,`od`.`代理客池咨询师姓名` AS `代理客池咨询师姓名`,`od`.`代理时间段` AS `代理时间段`,`od`.`代理人姓名` AS `代理人姓名`,`od`.`代理人手机号` AS `代理人手机号`,`od`.`最终客户池咨询师姓名` AS `最终客户池咨询师姓名`,`od`.`最终客户池咨询师所属门店` AS `最终客户池咨询师所属门店`,`od`.`业绩判定类型` AS `业绩判定类型`,`od`.`业绩归属咨询师姓名` AS `业绩归属咨询师姓名`,`od`.`退款承担咨询师姓名` AS `退款承担咨询师姓名`,`od`.`实付金额原始` AS `实付金额原始`,`od`.`总退款金额` AS `总退款金额`,`od`.`净实收金额` AS `净实收金额`,`od`.`是否退款` AS `是否退款`,`od`.`完成时间` AS `完成时间` from `OrderDetails` `od` where ((`od`.`交易日期` >= '2025-11-08') and (`od`.`交易日期` <= '2025-11-18')) order by `od`.`交易日期` desc,`od`.`交易时间` desc,`od`.`订单号`