v_销售业绩 视图

CREATE ALGORITHM=UNDEFINED DEFINER=`neuron_new`@`%` SQL SECURITY DEFINER VIEW `v_销售业绩` AS select `o`.`order_no` AS `订单号`,cast(from_unixtime((`o`.`pay_time` / 1000)) as datetime) AS `支付时间`,cast(from_unixtime((`o`.`finish_time` / 1000)) as datetime) AS `完成时间`,cast(from_unixtime((`r`.`refund_time` / 1000)) as datetime) AS `退款时间`,(case `o`.`order_type` when 1 then (_utf8mb4'普通订单' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'拼团订单' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'秒杀订单' collate utf8mb4_unicode_ci) when 4 then (_utf8mb4'普通预售订单' collate utf8mb4_unicode_ci) when 5 then (_utf8mb4'阶梯团订单' collate utf8mb4_unicode_ci) when 6 then (_utf8mb4'抽奖订单' collate utf8mb4_unicode_ci) when 7 then (_utf8mb4'换货订单' collate utf8mb4_unicode_ci) when 8 then (_utf8mb4'视频号订单' collate utf8mb4_unicode_ci) when 9 then (_utf8mb4'视频号橱窗订单' collate utf8mb4_unicode_ci) when 10 then (_utf8mb4'分销订单' collate utf8mb4_unicode_ci) when 12 then (_utf8mb4'导购订单' collate utf8mb4_unicode_ci) when 13 then (_utf8mb4'云店订单' collate utf8mb4_unicode_ci) when 14 then (_utf8mb4'连锁总部订单' collate utf8mb4_unicode_ci) when 15 then (_utf8mb4'全渠道B2B订单' collate utf8mb4_unicode_ci) when 16 then (_utf8mb4'礼品卡订单' collate utf8mb4_unicode_ci) when 17 then (_utf8mb4'积分订单' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `订单类型`,(case `o`.`order_status` when 0 then (_utf8mb4'创建' collate utf8mb4_unicode_ci) when 1 then (_utf8mb4'部分支付' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'已支付' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'待发货' collate utf8mb4_unicode_ci) when 4 then (_utf8mb4'部分发货' collate utf8mb4_unicode_ci) when 5 then (_utf8mb4'已发货' collate utf8mb4_unicode_ci) when 7 then (_utf8mb4'确认收货' collate utf8mb4_unicode_ci) when 8 then (_utf8mb4'已完成' collate utf8mb4_unicode_ci) when 9 then (_utf8mb4'已取消' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `订单状态`,(case `o`.`pay_status` when 0 then (_utf8mb4'未支付' collate utf8mb4_unicode_ci) when 1 then (_utf8mb4'支付中' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'已支付' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'部分退款' collate utf8mb4_unicode_ci) when 4 then (_utf8mb4'全额退款' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `支付状态`,(case when (`o`.`rights_status` in (1,2)) then (_utf8mb4'是' collate utf8mb4_unicode_ci) else (_utf8mb4'否' collate utf8mb4_unicode_ci) end) AS `是否退款`,(case when isnull(`o`.`rights_status`) then (_utf8mb4'无售后' collate utf8mb4_unicode_ci) when (`o`.`rights_status` = 0) then (_utf8mb4'无售后' collate utf8mb4_unicode_ci) when (`o`.`rights_status` = 1) then (_utf8mb4'售后中' collate utf8mb4_unicode_ci) when (`o`.`rights_status` = 2) then (_utf8mb4'已售后' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `售后状态`,(case `o`.`order_source` when 0 then (_utf8mb4'其他' collate utf8mb4_unicode_ci) when 1 then (_utf8mb4'小程序' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'APP' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'H5' collate utf8mb4_unicode_ci) when 4 then (_utf8mb4'PC' collate utf8mb4_unicode_ci) when 5 then (_utf8mb4'线下' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `订单来源`,(case `o`.`channel_type` when 0 then (_utf8mb4'微信小程序' collate utf8mb4_unicode_ci) when 1 then (_utf8mb4'微信公众号' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'微信APP' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'微信外H5' collate utf8mb4_unicode_ci) when 4 then (_utf8mb4'自有APP' collate utf8mb4_unicode_ci) when 5 then (_utf8mb4'PC官网' collate utf8mb4_unicode_ci) when 6 then (_utf8mb4'微盟小程序' collate utf8mb4_unicode_ci) when 7 then (_utf8mb4'支付宝小程序' collate utf8mb4_unicode_ci) when 8 then (_utf8mb4'字节小程序' collate utf8mb4_unicode_ci) when 9 then (_utf8mb4'快手小程序' collate utf8mb4_unicode_ci) when 10 then (_utf8mb4'百度小程序' collate utf8mb4_unicode_ci) when 11 then (_utf8mb4'QQ小程序' collate utf8mb4_unicode_ci) when 12 then (_utf8mb4'企业微信H5' collate utf8mb4_unicode_ci) when 13 then (_utf8mb4'H5' collate utf8mb4_unicode_ci) when 14 then (_utf8mb4'京东小程序' collate utf8mb4_unicode_ci) when 15 then (_utf8mb4'视频号' collate utf8mb4_unicode_ci) when 16 then (_utf8mb4'视频号橱窗' collate utf8mb4_unicode_ci) when 17 then (_utf8mb4'抖音原生小程序' collate utf8mb4_unicode_ci) when 18 then (_utf8mb4'支付宝生活号' collate utf8mb4_unicode_ci) when 19 then (_utf8mb4'饿了么小程序' collate utf8mb4_unicode_ci) when 20 then (_utf8mb4'美团小程序' collate utf8mb4_unicode_ci) when 21 then (_utf8mb4'微信小程序直播' collate utf8mb4_unicode_ci) when 22 then (_utf8mb4'快手磁力金牛' collate utf8mb4_unicode_ci) when 23 then (_utf8mb4'抖音小程序直播' collate utf8mb4_unicode_ci) when 24 then (_utf8mb4'微盟收银' collate utf8mb4_unicode_ci) when 25 then (_utf8mb4'微盟线下商城' collate utf8mb4_unicode_ci) when 26 then (_utf8mb4'视频号名片' collate utf8mb4_unicode_ci) when 27 then (_utf8mb4'视频号小店' collate utf8mb4_unicode_ci) when 28 then (_utf8mb4'视频号原生页' collate utf8mb4_unicode_ci) when 29 then (_utf8mb4'视频号名片小店' collate utf8mb4_unicode_ci) when 30 then (_utf8mb4'企业微信小程序' collate utf8mb4_unicode_ci) when 31 then (_utf8mb4'抖音小程序' collate utf8mb4_unicode_ci) when 32 then (_utf8mb4'抖音小程序webview' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `渠道类型`,(case `o`.`delivery_type` when 1 then (_utf8mb4'快递' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'自提' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'同城配送' collate utf8mb4_unicode_ci) when 4 then (_utf8mb4'无需物流' collate utf8mb4_unicode_ci) else (_utf8mb4'其他' collate utf8mb4_unicode_ci) end) AS `配送方式`,`o`.`total_amount` AS `订单总金额`,`o`.`should_pay_amount` AS `应付金额`,`o`.`pay_amount` AS `实付金额`,`o`.`goods_amount` AS `商品金额`,`o`.`freight_amount` AS `运费金额`,`o`.`total_discount_amount` AS `优惠金额`,`o`.`product_name` AS `商品名称`,coalesce(`pv`.`verified_order_person`,`e_op`.`employee_name`,`e_share`.`employee_name`) AS `开单人姓名`,coalesce(`e_pv`.`mobile_phone`,`e_op`.`mobile_phone`,`e_share`.`mobile_phone`) AS `开单人手机号`,coalesce(`e_pv`.`dept_level1`,`e_op`.`dept_level1`,`e_share`.`dept_level1`) AS `开单人一级部门`,coalesce(`e_pv`.`dept_level2`,`e_op`.`dept_level2`,`e_share`.`dept_level2`) AS `开单人二级部门`,coalesce(`e_pv`.`position`,`e_op`.`position`,`e_share`.`position`) AS `开单人职位`,coalesce(`e_pv`.`weimob_wid`,`e_op`.`weimob_wid`,`e_share`.`weimob_wid`,cast(`o`.`share_wid` as char charset utf8mb4)) AS `开单人WID`,`o`.`buyer_nick_name` AS `客户昵称`,`o`.`buyer_phone` AS `客户手机号`,`o`.`vid_name` AS `门店名称`,`o`.`receiver_province` AS `收货省份`,`o`.`receiver_city` AS `收货城市`,`o`.`receiver_county` AS `收货区县`,`o`.`operator_name` AS `操作员姓名`,`o`.`consultation_doctor` AS `咨询医生`,`o`.`cancel_reason` AS `取消原因` from (((((`wm_orders` `o` left join `performance_verification` `pv` on(((`o`.`order_no` = `pv`.`order_no`) and (`pv`.`verification_status` = '同意')))) left join `employee_roster` `e_pv` on((`pv`.`verified_order_person` = (`e_pv`.`employee_name` collate utf8mb4_unicode_ci)))) left join `employee_roster` `e_op` on(((`o`.`operator_id` = (`e_op`.`weimob_wid` collate utf8mb4_unicode_ci)) or (`o`.`operator_name` = (`e_op`.`employee_name` collate utf8mb4_unicode_ci))))) left join `employee_roster` `e_share` on(((cast(`o`.`share_wid` as char charset utf8mb4) collate utf8mb4_unicode_ci) = (`e_share`.`weimob_wid` collate utf8mb4_unicode_ci)))) left join (select `wm_order_rights`.`order_no` AS `order_no`,max(`wm_order_rights`.`refund_time`) AS `refund_time` from `wm_order_rights` where ((`wm_order_rights`.`rights_status` = 2) and (`wm_order_rights`.`is_deleted` = 0)) group by `wm_order_rights`.`order_no`) `r` on((`o`.`order_no` = `r`.`order_no`))) where (`o`.`is_deleted` = 0) union all select `c`.`order_no` AS `订单号`,`c`.`finish_payment_time` AS `支付时间`,cast(from_unixtime((`c`.`finish_time` / 1000)) as datetime) AS `完成时间`,cast(from_unixtime((`rc`.`refund_time` / 1000)) as datetime) AS `退款时间`,(_utf8mb4'会员订单' collate utf8mb4_unicode_ci) AS `订单类型`,(case `c`.`status` when 0 then (_utf8mb4'创建' collate utf8mb4_unicode_ci) when 1 then (_utf8mb4'待发货' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'待收货' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'待评价' collate utf8mb4_unicode_ci) when 7 then (_utf8mb4'已完成' collate utf8mb4_unicode_ci) when 8 then (_utf8mb4'已取消' collate utf8mb4_unicode_ci) when 9 then (_utf8mb4'已关闭' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `订单状态`,(case `c`.`pay_status` when 0 then (_utf8mb4'未支付' collate utf8mb4_unicode_ci) when 1 then (_utf8mb4'支付中' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'已支付' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'部分退款' collate utf8mb4_unicode_ci) when 4 then (_utf8mb4'全额退款' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `支付状态`,(case when (`rc`.`refund_time` is not null) then (_utf8mb4'是' collate utf8mb4_unicode_ci) when ((`c`.`refund_status` is not null) and (`c`.`refund_status` > 0)) then (_utf8mb4'是' collate utf8mb4_unicode_ci) else (_utf8mb4'否' collate utf8mb4_unicode_ci) end) AS `是否退款`,(case when (`rc`.`refund_time` is not null) then (_utf8mb4'已售后' collate utf8mb4_unicode_ci) when ((`c`.`refund_status` is not null) and (`c`.`refund_status` > 0)) then (_utf8mb4'售后中' collate utf8mb4_unicode_ci) else (_utf8mb4'无售后' collate utf8mb4_unicode_ci) end) AS `售后状态`,(case `c`.`order_source` when 0 then (_utf8mb4'其他' collate utf8mb4_unicode_ci) when 1 then (_utf8mb4'小程序' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'APP' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'H5' collate utf8mb4_unicode_ci) when 4 then (_utf8mb4'PC' collate utf8mb4_unicode_ci) when 5 then (_utf8mb4'线下' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `订单来源`,(case `c`.`channel_type` when 0 then (_utf8mb4'微信小程序' collate utf8mb4_unicode_ci) when 1 then (_utf8mb4'微信公众号' collate utf8mb4_unicode_ci) when 2 then (_utf8mb4'微信APP' collate utf8mb4_unicode_ci) when 3 then (_utf8mb4'微信外H5' collate utf8mb4_unicode_ci) when 4 then (_utf8mb4'自有APP' collate utf8mb4_unicode_ci) when 5 then (_utf8mb4'PC官网' collate utf8mb4_unicode_ci) when 6 then (_utf8mb4'微盟小程序' collate utf8mb4_unicode_ci) when 7 then (_utf8mb4'支付宝小程序' collate utf8mb4_unicode_ci) when 8 then (_utf8mb4'字节小程序' collate utf8mb4_unicode_ci) when 9 then (_utf8mb4'快手小程序' collate utf8mb4_unicode_ci) when 10 then (_utf8mb4'百度小程序' collate utf8mb4_unicode_ci) when 11 then (_utf8mb4'QQ小程序' collate utf8mb4_unicode_ci) when 12 then (_utf8mb4'企业微信H5' collate utf8mb4_unicode_ci) when 13 then (_utf8mb4'H5' collate utf8mb4_unicode_ci) when 14 then (_utf8mb4'京东小程序' collate utf8mb4_unicode_ci) when 15 then (_utf8mb4'视频号' collate utf8mb4_unicode_ci) when 16 then (_utf8mb4'视频号橱窗' collate utf8mb4_unicode_ci) when 17 then (_utf8mb4'抖音原生小程序' collate utf8mb4_unicode_ci) when 18 then (_utf8mb4'支付宝生活号' collate utf8mb4_unicode_ci) when 19 then (_utf8mb4'饿了么小程序' collate utf8mb4_unicode_ci) when 20 then (_utf8mb4'美团小程序' collate utf8mb4_unicode_ci) when 21 then (_utf8mb4'微信小程序直播' collate utf8mb4_unicode_ci) when 22 then (_utf8mb4'快手磁力金牛' collate utf8mb4_unicode_ci) when 23 then (_utf8mb4'抖音小程序直播' collate utf8mb4_unicode_ci) when 24 then (_utf8mb4'微盟收银' collate utf8mb4_unicode_ci) when 25 then (_utf8mb4'微盟线下商城' collate utf8mb4_unicode_ci) when 26 then (_utf8mb4'视频号名片' collate utf8mb4_unicode_ci) when 27 then (_utf8mb4'视频号小店' collate utf8mb4_unicode_ci) when 28 then (_utf8mb4'视频号原生页' collate utf8mb4_unicode_ci) when 29 then (_utf8mb4'视频号名片小店' collate utf8mb4_unicode_ci) when 30 then (_utf8mb4'企业微信小程序' collate utf8mb4_unicode_ci) when 31 then (_utf8mb4'抖音小程序' collate utf8mb4_unicode_ci) when 32 then (_utf8mb4'抖音小程序webview' collate utf8mb4_unicode_ci) else (_utf8mb4'未知' collate utf8mb4_unicode_ci) end) AS `渠道类型`,(_utf8mb4'无需物流' collate utf8mb4_unicode_ci) AS `配送方式`,`c`.`total_amount` AS `订单总金额`,`c`.`should_payment_amount` AS `应付金额`,`c`.`payment_amount` AS `实付金额`,`c`.`total_amount` AS `商品金额`,0.00 AS `运费金额`,(`c`.`should_payment_amount` - `c`.`payment_amount`) AS `优惠金额`,(_utf8mb4'会员卡' collate utf8mb4_unicode_ci) AS `商品名称`,(convert(`e_sales`.`employee_name` using utf8mb4) collate utf8mb4_unicode_ci) AS `开单人姓名`,(convert(`c`.`sales_id` using utf8mb4) collate utf8mb4_unicode_ci) AS `开单人手机号`,(convert(`e_sales`.`dept_level1` using utf8mb4) collate utf8mb4_unicode_ci) AS `开单人一级部门`,(convert(`e_sales`.`dept_level2` using utf8mb4) collate utf8mb4_unicode_ci) AS `开单人二级部门`,(convert(`e_sales`.`position` using utf8mb4) collate utf8mb4_unicode_ci) AS `开单人职位`,(convert(`e_sales`.`weimob_wid` using utf8mb4) collate utf8mb4_unicode_ci) AS `开单人WID`,(convert(`c`.`buyer_name` using utf8mb4) collate utf8mb4_unicode_ci) AS `客户昵称`,(convert(`c`.`buyer_phone` using utf8mb4) collate utf8mb4_unicode_ci) AS `客户手机号`,(convert(`c`.`vid_name` using utf8mb4) collate utf8mb4_unicode_ci) AS `门店名称`,(cast(NULL as char(50) charset utf8mb4) collate utf8mb4_unicode_ci) AS `收货省份`,(cast(NULL as char(50) charset utf8mb4) collate utf8mb4_unicode_ci) AS `收货城市`,(cast(NULL as char(50) charset utf8mb4) collate utf8mb4_unicode_ci) AS `收货区县`,(convert(`c`.`sales_name` using utf8mb4) collate utf8mb4_unicode_ci) AS `操作员姓名`,(cast(NULL as char(100) charset utf8mb4) collate utf8mb4_unicode_ci) AS `咨询医生`,(convert(`c`.`cancel_reason` using utf8mb4) collate utf8mb4_unicode_ci) AS `取消原因` from ((`crm_member_orders` `c` left join `employee_roster` `e_sales` on((`c`.`sales_id` = (`e_sales`.`mobile_phone` collate utf8mb4_unicode_ci)))) left join (select `wm_order_rights`.`order_no` AS `order_no`,max(`wm_order_rights`.`refund_time`) AS `refund_time` from `wm_order_rights` where ((`wm_order_rights`.`rights_status` = 2) and (`wm_order_rights`.`is_deleted` = 0)) group by `wm_order_rights`.`order_no`) `rc` on((`c`.`order_no` = `rc`.`order_no`))) where (`c`.`status` <> 8) order by `支付时间` desc