# 销售统计
SELECT  t.in_user_id, t.in_user_name, concat(t.in_user_id) AS sort_value, sum(CASE WHEN t.data_mark = 'A' THEN t.counts ELSE 0 END) AS counts, sum(CASE WHEN t.data_mark = 'A' THEN t.price ELSE 0 END) AS price, sum(CASE WHEN t.data_mark = 'A' THEN t.sell_price ELSE 0 END) AS sell_price, sum(CASE WHEN t.data_mark = 'A' THEN t.avg_price ELSE 0 END) AS avg_price, sum(CASE WHEN t.data_mark = 'A' THEN t.gift_price ELSE 0 END) AS gift_price, sum(CASE WHEN t.data_mark = 'A' THEN t.discount_price ELSE 0 END) AS discount_price, sum(CASE WHEN t.data_mark = 'A' THEN t.point_price ELSE 0 END) AS point_price, sum(CASE WHEN t.data_mark = 'A' THEN t.statistics_price ELSE 0 END) AS statistics_price, sum(CASE WHEN t.data_mark = 'A' THEN t.self_modify_price ELSE 0 END) AS self_modify_price, sum(CASE WHEN t.data_mark = 'A' THEN t.statistics_in_price ELSE 0 END) AS statistics_in_price, sum(CASE WHEN t.data_mark = 'A' THEN t.statistics_gross_profit ELSE 0 END) AS statistics_gross_profit, sum(CASE WHEN t.data_mark = 'A' THEN t.in_price ELSE 0 END) AS in_price, sum(CASE WHEN t.data_mark = 'A' THEN t.gross_profit ELSE 0 END) AS gross_profit, sum(CASE WHEN t.data_mark = 'A' THEN t.avg_statistics_gross_profit ELSE 0 END) AS avg_statistics_gross_profit, sum(CASE WHEN t.data_mark = 'A' THEN t.examine_price ELSE 0 END) AS examine_price, sum(CASE WHEN t.data_mark = 'A' THEN t.examine_gross_profit ELSE 0 END) AS examine_gross_profit, sum(CASE WHEN t.data_mark = 'A' THEN t.avg_examine_gross_profit ELSE 0 END) AS avg_examine_gross_profit, sum(CASE WHEN t.data_mark = 'B' THEN t.counts ELSE 0 END) AS counts_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.price ELSE 0 END) AS price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.sell_price ELSE 0 END) AS sell_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.avg_price ELSE 0 END) AS avg_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.gift_price ELSE 0 END) AS gift_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.discount_price ELSE 0 END) AS discount_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.point_price ELSE 0 END) AS point_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.statistics_price ELSE 0 END) AS statistics_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.self_modify_price ELSE 0 END) AS self_modify_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.statistics_in_price ELSE 0 END) AS statistics_in_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.statistics_gross_profit ELSE 0 END) AS statistics_gross_profit_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.in_price ELSE 0 END) AS in_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.gross_profit ELSE 0 END) AS gross_profit_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.avg_statistics_gross_profit ELSE 0 END) AS avg_statistics_gross_profit_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.examine_price ELSE 0 END) AS examine_price_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.examine_gross_profit ELSE 0 END) AS examine_gross_profit_chain, sum(CASE WHEN t.data_mark = 'B' THEN t.avg_examine_gross_profit ELSE 0 END) AS avg_examine_gross_profit_chain
 FROM (SELECT *, 'A' AS data_mark
 FROM (SELECT t.in_user_id, t.in_user_name, IFNULL(sum(t.count), 0) counts, IFNULL(sum(t.price), 0) price, IFNULL(sum(t.sell_price), 0) sell_price, ROUND(sum(t.price) / sum(t.count), 2) avg_price, IFNULL(SUM(t.gift_price), 0) gift_price, IFNULL(sum(t.discount_price), 0) discount_price, IFNULL(sum(t.point_price), 0) point_price, IFNULL(sum(t.self_modify_price), 0) self_modify_price, sum(t.actualPaymentPrice) statistics_price, sum(IFNULL(t.statistics_in_price, 0)) statistics_in_price, sum(t.statistics_gross_profit) statistics_gross_profit, sum(IFNULL(t.in_price, 0)) in_price, sum(t.gross_profit) gross_profit, ROUND(sum(t.statistics_gross_profit) / sum(t.count), 2) avg_statistics_gross_profit, SUM(IFNULL(t.examine_price, 0)) examine_price, SUM(t.examine_gross_profit) examine_gross_profit, ROUND(sum(t.examine_gross_profit) / sum(t.count), 2) avg_examine_gross_profit
 FROM (WITH adjustPriceTemp AS (SELECT t1.mkc_id AS mkc_id, sum(if(t17.type = 3, t16.adjust_price, 0.00)) AS self_modify_price, sum(if(t17.type = 2
 AND t16.is_adjust_after_trade = false, t16.adjust_price, 0.00)) AS before_in_source_modify_price, sum(if(t17.type = 2
 AND t16.is_adjust_after_trade = true, t16.adjust_price, 0.00)) AS after_in_source_modify_price, sum(if(t17.type = 2, t16.adjust_price, 0.00)) AS in_source_modify_price
 FROM t_product_stock_detail t1
 LEFT JOIN t_order_detail t2
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t1.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_return_detail t6
 ON t1.mkc_id = t6.mkc_id
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 AND t7.is_delete = 0
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 LEFT JOIN t_product_adjust_price_detail t16
 ON t16.mkc_id = t1.mkc_id
 LEFT JOIN t_product_adjust_price t17
 ON t17.id = t16.adjust_price_id
 AND t17.status = 3
 WHERE t3.order_state = 9
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t6.is_delete = 0
 AND t7.is_delete = 0
 AND t16.is_delete = 0
 AND t2.is_mobile = TRUE
 AND t17.type IN (2, 3)
 AND t7.check2_time BETWEEN '2024-11-01 00:00:00'
 AND '2024-11-18 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6) GROUP BY t1.mkc_id)
 SELECT t2.id AS orderDetailId, 0 - (IFNULL(t1.inprice, 0) - IFNULL(t16.self_modify_price, 0.00) - IFNULL(t16.in_source_modify_price, 0.00) + IFNULL(t1.transfer_price, 0)) statistics_in_price, 0 - t1.inprice in_price, 0 - t6.return_price + IFNULL(t1.inprice, 0) - IFNULL(t16.self_modify_price, 0.00) - IFNULL(t16.in_source_modify_price, 0.00) + IFNULL(t1.transfer_price, 0) statistics_gross_profit, 0 - t6.return_price + IFNULL(t1.inprice, 0) gross_profit, 0 - t1.examine_price examine_price, 0 - (t6.return_price - IFNULL(t1.examine_price, 0)) examine_gross_profit, t2.is_mobile is_mobile, t7.check2_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, ifnull(t3.order_parent_id, t3.order_id) order_id, '退款' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid sku_id, t2.detail_type, t1.insource_id insource_id, NULL insourceId2, t1.imei i_mei, t1.imei_2 imei2, t1.imei_3 imei3, t1.imei_4 imei4, t1.mkc_id, t1.instock_time in_stock_time, t1.invoice_type invoice_flag, '-1' AS count, 0 - t2.price price, 0 - t2.price price2, 0 - t2.sell_price sell_price, 0 - t2.gift_price gift_price, 0 - t2.discount_price discount_price, 0 - t2.point_price point_price, 0 - t6.return_price statistics_price, 0 - t6.return_price actualPaymentPrice, 0 - IFNULL(t16.self_modify_price, 0.00) self_modify_price, 0 - IFNULL(t16.after_in_source_modify_price, 0.00) in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid AS product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) AS customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t7.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1
 AND t2.product_stock_type != 3)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t1.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_return_detail t6
 ON t1.mkc_id = t6.mkc_id
 AND t6.product_stock_type != 3
 AND t6.is_delete = 0
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 AND t7.is_delete = 0
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 LEFT JOIN adjustPriceTemp t16
 ON t16.mkc_id = t1.mkc_id
 WHERE t3.order_state = 9
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t7.return_type NOT IN (7, 8)
 AND t7.is_delete = 0
 AND t2.is_mobile = TRUE
 AND t2.product_stock_type != 3
 AND t7.check2_time BETWEEN '2024-11-01 00:00:00'
 AND '2024-11-18 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT t2.id AS orderDetailId, t1.statistics_price statistics_in_price, t1.inprice in_price, (t2.statictisc_price + IFNULL(t2.gift_price, 0) - IFNULL(t1.statistics_price, 0)) statistics_gross_profit, (t2.statictisc_price + IFNULL(t2.gift_price, 0) - IFNULL(t1.inprice, 0)) gross_profit, t1.examine_price examine_price, (t2.statictisc_price + IFNULL(t2.gift_price, 0) - IFNULL(t1.examine_price, 0)) examine_gross_profit, t2.is_mobile is_mobile, t3.order_complete_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, CASE WHEN t3.order_state = 9 THEN ifnull(t3.order_parent_id, t3.order_id) ELSE t3.order_id END order_id, '销售' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid sku_id, t2.detail_type, t1.insource_id insource_id, NULL insourceId2, t1.imei imei, t1.imei_2 i_mei2, t1.imei_3 i_mei3, t1.imei_4 i_mei4, t1.mkc_id, t1.instock_time in_stock_time, t1.invoice_type invoice_flag, '1' AS count, t2.price price, t2.price price2, t2.sell_price sell_price, t2.gift_price gift_price, t2.discount_price discount_price, t2.point_price point_price, t2.statictisc_price statistics_price, (t2.price - t2.discount_price - t2.point_price) actualPaymentPrice, t1.modify_price self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t3.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1
 AND t2.product_stock_type != 3)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t1.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t2.is_mobile = TRUE
 AND t2.product_stock_type != 3
 AND t3.order_complete_time BETWEEN '2024-11-01 00:00:00'
 AND '2024-11-18 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT t2.id AS orderDetailId, 0 - t16.adjust_price statistics_in_price, 0 in_price, t16.adjust_price statistics_gross_profit, 0 gross_profit, 0 examine_price, 0 examine_gross_profit, t2.is_mobile is_mobile, t17.handle_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, CASE WHEN t3.order_state = 9 THEN ifnull(t3.order_parent_id, t3.order_id) ELSE t3.order_id END order_id, '交易完成价保' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid sku_id, t2.detail_type, t1.insource_id insource_id, NULL insourceId2, t1.imei imei, t1.imei_2 imei2, t1.imei_3 imei3, t1.imei_4 imei4, t1.mkc_id, t1.instock_time in_stock_time, t1.invoice_type invoice_flag, 0 AS count, 0 price, 0 price2, 0 sell_price, 0 gift_price, 0 discount_price, 0 point_price, 0 statistics_price, 0 actualPaymentPrice, if(t17.type = 3, t16.adjust_price, 0.00) self_modify_price, if(t17.type = 2, t16.adjust_price, 0.00) in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t3.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1
 AND t2.product_stock_type != 3)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t1.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 LEFT JOIN t_product_adjust_price_detail t16
 ON t16.mkc_id = t1.mkc_id
 LEFT JOIN t_product_adjust_price t17
 ON t17.id = t16.adjust_price_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t2.is_mobile = TRUE
 AND t2.product_stock_type != 3
 AND t17.status = 3
 AND t17.type IN (2, 3)
 AND t16.is_adjust_after_trade = true
 AND t17.handle_time BETWEEN '2024-11-01 00:00:00'
 AND '2024-11-18 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT max(t10.id) AS orderDetailId, SUM(t10.statistics_in_price) statistics_in_price, SUM(t10.in_price) in_price, SUM(t10.statistics_gross_profit) statistics_gross_profit, sum(t10.gross_profit) gross_profit, SUM(t10.examine_price) examine_price, SUM(t10.examine_gross_profit) examine_gross_profit, t10.is_mobile is_mobile, t10.date date, t10.order_complete_time, t10.area_id area_id, t10.accountingBodyId, t10.accountingBodyName, t10.order_id order_id, t10.business_type business_type, t10.order_type order_type, t10.user_id user_id, t10.ppid sku_id, t10.detail_type, t10.insource_id insource_id, t10.inSourceId2 inSourceId2, t10.i_mei_or_barcode i_mei, t10.imei2 imei2, t10.imei3 imei3, t10.imei4 imei4, t10.mkc_id, t10.in_stock_time, t10.invoice_flag, SUM(t10.count) count, SUM(t10.price) price, SUM(t10.price) price2, SUM(t10.sell_price) sell_price, SUM(t10.gift_price) gift_price, SUM(t10.discount_price) discount_price, SUM(t10.point_price) point_price, SUM(t10.statistics_price) statistics_price, SUM(t10.actualPaymentPrice) actualPaymentPrice, SUM(t10.self_modify_price) self_modify_price, 0 in_source_modify_price, t10.in_user_id in_user_id, t10.in_user_name in_user_name, t10.product_color product_color, t10.product_id product_id, t10.cid cid, t10.brand_id brand_id, t10.product_name product_name, t10.customer_type customer_type, t10.order_customer_type order_customer_type, t10.customer_name customer_name, t10.customer_phone customer_phone, t10.contract_name contract_name, t10.contract_mobile contract_mobile, t10.trader_name trader_name, t10.comment comment, t10.product_comment product_comment, t10.label_id label_id, t10.label_name label_name, t10.detail_parent_id, t10.serial_number_mark_name serial_number_mark_name, t10.serial_number_mark_color serial_number_mark_color, t10.serial_number_mark serial_number_mark, t10.aux_sale_man_names, t10.sample_product
 FROM (SELECT t2.id, SUM(IFNULL(IFNULL(t1.statistics_price * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)) statistics_in_price, SUM(IFNULL(IFNULL(t1.inprice * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)) in_price, (((t2.statictisc_price + IFNULL(t2.gift_price, 0)) * if(t2.virtual_product = 0, sum(t1.counts), t2.counts)) - SUM(IFNULL(IFNULL(t1.statistics_price * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0))) statistics_gross_profit, (((t2.statictisc_price + IFNULL(t2.gift_price, 0)) * if(t2.virtual_product = 0, sum(t1.counts), t2.counts)) - (SUM(IFNULL(IFNULL(t1.inprice * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)))) gross_profit, SUM(IFNULL(IFNULL(t1.examine_price * t1.counts, t2.examine_price), 0)) examine_price, (((t2.statictisc_price + IFNULL(t2.gift_price, 0)) * if(t2.virtual_product = 0, sum(t1.counts), t2.counts)) - (SUM(IFNULL(IFNULL(t1.examine_price * t1.counts, t2.examine_price), 0)))) examine_gross_profit, t2.is_mobile is_mobile, t3.order_complete_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, CASE WHEN t3.order_state = 9 THEN ifnull(t3.order_parent_id, t3.order_id) ELSE t3.order_id END order_id, '销售' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid ppid, t2.detail_type, IFNULL(t1.insource_id, vpcc.in_source_id) insource_id, t16.channel inSourceId2, t4.bar_code i_mei_or_barcode, '' imei2, '' imei3, '' imei4, NULL AS mkc_id, NULL in_stock_time, NULL invoice_flag, if(t2.virtual_product = 0, sum(t1.counts), t2.counts) count, t2.price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) price, t2.sell_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) sell_price, t2.gift_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) gift_price, t2.discount_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) discount_price, t2.point_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) point_price, t2.statictisc_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) statistics_price, (t2.price - t2.discount_price - t2.point_price) * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) actualPaymentPrice, SUM(IFNULL(t1.modify_price * t1.counts, 0)) self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t3.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product, t3.invoice_status
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_order_detail_extend vpcc
 ON t2.id = vpcc.id
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t2.is_mobile = FALSE
 AND t3.order_complete_time BETWEEN '2024-11-01 00:00:00'
 AND '2024-11-18 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6) GROUP BY t2.id, t1.insource_id, t4.bar_code, t4.product_color, t4.cid, t4.brand_id, t4.product_name, t3.trader_name, la.id, la.label_name, sysc.name, sysc.value, sysc.id, t36.aux_sale_man_names, t1.sample_product, t40.id, t40.name
 UNION ALL
 SELECT t2.id, 0 - SUM(IFNULL(IFNULL(t1.statistics_price * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)) statistics_in_price, 0 - SUM(IFNULL(IFNULL(t1.inprice * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)) in_price, 0 - ((t6.return_price * t6.return_count * count(t2.id)) - SUM(IFNULL(IFNULL(t1.statistics_price * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0))) statistics_gross_profit, 0 - ((t6.return_price * t6.return_count * count(t2.id)) - (SUM(IFNULL(IFNULL(t1.inprice * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)))) gross_profit, 0 - SUM(IFNULL(IFNULL(t1.examine_price * t1.counts, t2.examine_price), 0)) examine_price, 0 - ((t6.return_price * t6.return_count * count(t2.id)) - (SUM(IFNULL(IFNULL(t1.examine_price * t1.counts, t2.examine_price), 0)))) examine_gross_profit, t2.is_mobile is_mobile, t7.check2_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, ifnull(t3.order_parent_id, t3.order_id) order_id, '退款' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid ppid, t2.detail_type, IFNULL(t1.insource_id, vpcc.in_source_id) insource_id, t16.channel inSourceId2, t4.bar_code i_mei_or_barcode, '' imei2, '' imei3, '' imei4, NULL AS mkc_id, NULL in_stock_time, NULL invoice_flag, 0 - count(t2.id) * t6.return_count count, 0 - (t2.price * count(t2.id)) * t6.return_count price, 0 - (t2.sell_price * count(t2.id)) * t6.return_count sell_price, 0 - (t2.gift_price * count(t2.id)) * t6.return_count gift_price, 0 - (t2.discount_price * count(t2.id)) * t6.return_count discount_price, 0 - (t2.point_price * count(t2.id)) * t6.return_count point_price, 0 - (t6.return_price * count(t2.id)) * t6.return_count actualPaymentPrice, 0 - (t6.return_price * count(t2.id)) * t6.return_count statistics_price, 0 - SUM(IFNULL(t1.modify_price, 0)) self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t7.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product, t3.invoice_status
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN (SELECT order_detail_id, avg(return_price) return_price, is_delete, return_id, return_count
 FROM t_return_detail
 WHERE is_delete = 0
 AND order_detail_type = 1 GROUP BY order_detail_id, return_id, is_delete, return_count) t6
 ON t2.id = t6.order_detail_id
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_order_detail_extend vpcc
 ON t2.id = vpcc.id
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state = 9
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t6.is_delete = 0
 AND t7.is_delete = 0
 AND t7.return_type NOT IN (7, 8)
 AND t2.is_mobile = FALSE
 AND t7.check2_time BETWEEN '2024-11-01 00:00:00'
 AND '2024-11-18 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6) GROUP BY t2.id, t1.insource_id, t4.bar_code, t4.product_color, t4.cid, t4.brand_id, t4.product_name, t6.return_price, t6.return_count, t7.check2_time, t7.check2_user_name, t7.comment, la.id, la.label_name, sysc.name, sysc.value, sysc.id, t36.aux_sale_man_names, t1.sample_product, t40.id, t40.name) t10
 WHERE is_mobile IS NOT NULL
 AND area_id IS NOT NULL
 AND order_id IS NOT NULL GROUP BY t10.business_type, t10.is_mobile, t10.date, t10.order_complete_time, t10.area_id, t10.order_id, t10.order_type, t10.user_id, t10.ppid, t10.insource_id, t10.i_mei_or_barcode, t10.in_user_id, t10.in_user_name, t10.product_color, t10.product_id, t10.cid, t10.brand_id, t10.product_name, t10.customer_type, t10.order_customer_type, t10.customer_name, t10.customer_phone, t10.contract_name, t10.contract_mobile, t10.trader_name, t10.inSourceId2, t10.detail_type, t10.comment, t10.product_comment, t10.label_id, t10.label_name, t10.detail_parent_id, t10.imei2, t10.imei3, t10.imei4, t10.mkc_id, t10.in_stock_time, t10.invoice_flag, t10.serial_number_mark_name, t10.serial_number_mark_color, t10.serial_number_mark, t10.aux_sale_man_names, t10.sample_product, t10.accountingBodyId, t10.accountingBodyName
 UNION ALL
 SELECT a.id AS orderDetailId, 0 AS statistics_in_price, 0 AS in_price, 0 - IFNULL(b.deduction_price, 0) AS statistics_gross_profit, 0 - IFNULL(b.deduction_price, 0) AS gross_profit, 0 AS examine_price, 0 - IFNULL(b.deduction_price, 0) AS examine_gross_profit, t2.is_mobile is_mobile, b.return_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, CASE WHEN t3.order_state = 9 THEN ifnull(t3.order_parent_id, t3.order_id) ELSE t3.order_id END order_id, '交易完成返销' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid sku_id, t2.detail_type, t1.insource_id insource_id, t16.channel inSourceId2, if(t4.is_mobile = true, t1.imei, t4.bar_code) imei, if(t4.is_mobile = true, t1.imei_2, '') imei2, if(t4.is_mobile = true, t1.imei_3, '') imei3, if(t4.is_mobile = true, t1.imei_4, '') imei4, if(t4.is_mobile = true, t1.mkc_id, NULL) mkc_id, NULL in_stock_time, NULL invoice_flag, 0 AS count, 0 AS price, t2.price AS price2, 0 AS sell_price, 0 AS gift_price, 0 AS discount_price, 0 AS point_price, 0 AS statistics_price, 0 - IFNULL(b.deduction_price, 0) AS actualPaymentPrice, 0 AS self_modify_price, 0 AS in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t3.COMMENT COMMENT, t2.COMMENT product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail a
 INNER JOIN t_operator_business b
 ON a.id = b.order_detail_id
 INNER JOIN t_order_detail t2
 ON a.detail_parent_id = t2.id
 LEFT JOIN t_order c FORCE INDEX (torder_ix_order_id)
 ON a.order_id = c.order_id
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND c.order_complete_time <= b.return_time
 AND b.`status` = 2
 AND b.is_delete = 0
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND b.version = 0
 AND b.return_time BETWEEN '2024-11-01 00:00:00'
 AND '2024-11-18 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT t2.id AS orderDetailId, 0 statistics_in_price, 0 in_price, 0 - (t6.return_price * t6.return_count) statistics_gross_profit, 0 - (t6.return_price * t6.return_count) gross_profit, 0 examine_price, 0 - (t6.return_price * t6.return_count) examine_gross_profit, t2.is_mobile is_mobile, t7.check2_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, ifnull(t3.order_parent_id, t3.order_id) order_id, '价格保障' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid ppid, t2.detail_type, IFNULL(t1.insource_id, vpcc.in_source_id) insource_id, t16.channel inSourceId2, if(t4.is_mobile = true, t1.imei, t4.bar_code) imei, t1.imei_2 imei2, t1.imei_3 imei3, t1.imei_4 imei4, if(t4.is_mobile = true, t1.mkc_id, NULL) mkc_id, NULL in_stock_time, NULL invoice_flag, 0 count, 0 price, 0 price2, 0 sell_price, 0 gift_price, 0 discount_price, 0 point_price, 0 - t6.return_price * t6.return_count actualPaymentPrice, 0 - t6.return_price * t6.return_count statistics_price, 0 self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t7.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_return_detail t6
 ON t2.id = t6.order_detail_id
 AND t6.mkc_id IS NULL
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_order_detail_extend vpcc
 ON t2.id = vpcc.id
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t6.is_delete = 0
 AND t7.is_delete = 0
 AND t7.return_type = 8
 AND t7.check2_time BETWEEN '2024-11-01 00:00:00'
 AND '2024-11-18 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT t2.id AS orderDetailId, 0 statistics_in_price, 0 in_price, 0 - (t6.return_price * t6.return_count) statistics_gross_profit, 0 - (t6.return_price * t6.return_count) gross_profit, 0 examine_price, 0 - (t6.return_price * t6.return_count) examine_gross_profit, t2.is_mobile is_mobile, t7.check2_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, ifnull(t3.order_parent_id, t3.order_id) order_id, '价格保障' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid ppid, t2.detail_type, IFNULL(t1.insource_id, vpcc.in_source_id) insource_id, t16.channel inSourceId2, if(t4.is_mobile = true, t1.imei, t4.bar_code) imei, t1.imei_2 imei2, t1.imei_3 imei3, t1.imei_4 imei4, if(t4.is_mobile = true, t1.mkc_id, NULL) mkc_id, NULL in_stock_time, NULL invoice_flag, 0 count, 0 price, 0 price2, 0 sell_price, 0 gift_price, 0 discount_price, 0 point_price, 0 - t6.return_price * t6.return_count actualPaymentPrice, 0 - t6.return_price * t6.return_count statistics_price, 0 self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t7.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_return_detail t6
 ON t6.mkc_id = t1.mkc_id
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_order_detail_extend vpcc
 ON t2.id = vpcc.id
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t6.is_delete = 0
 AND t7.is_delete = 0
 AND t7.return_type = 8
 AND t7.check2_time BETWEEN '2024-11-01 00:00:00'
 AND '2024-11-18 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6) ORDER BY date DESC, orderDetailId DESC) t
 LEFT JOIN t_category category
 ON t.cid = category.id
 LEFT JOIN t_depart_info t8
 ON t.area_id = t8.id GROUP BY t.in_user_id, t.in_user_name) a
 UNION SELECT *, 'B' AS data_mark
 FROM (SELECT t.in_user_id, t.in_user_name, IFNULL(sum(t.count), 0) counts, IFNULL(sum(t.price), 0) price, IFNULL(sum(t.sell_price), 0) sell_price, ROUND(sum(t.price) / sum(t.count), 2) avg_price, IFNULL(SUM(t.gift_price), 0) gift_price, IFNULL(sum(t.discount_price), 0) discount_price, IFNULL(sum(t.point_price), 0) point_price, IFNULL(sum(t.self_modify_price), 0) self_modify_price, sum(t.actualPaymentPrice) statistics_price, sum(IFNULL(t.statistics_in_price, 0)) statistics_in_price, sum(t.statistics_gross_profit) statistics_gross_profit, sum(IFNULL(t.in_price, 0)) in_price, sum(t.gross_profit) gross_profit, ROUND(sum(t.statistics_gross_profit) / sum(t.count), 2) avg_statistics_gross_profit, SUM(IFNULL(t.examine_price, 0)) examine_price, SUM(t.examine_gross_profit) examine_gross_profit, ROUND(sum(t.examine_gross_profit) / sum(t.count), 2) avg_examine_gross_profit
 FROM (WITH adjustPriceTemp AS (SELECT t1.mkc_id AS mkc_id, sum(if(t17.type = 3, t16.adjust_price, 0.00)) AS self_modify_price, sum(if(t17.type = 2
 AND t16.is_adjust_after_trade = false, t16.adjust_price, 0.00)) AS before_in_source_modify_price, sum(if(t17.type = 2
 AND t16.is_adjust_after_trade = true, t16.adjust_price, 0.00)) AS after_in_source_modify_price, sum(if(t17.type = 2, t16.adjust_price, 0.00)) AS in_source_modify_price
 FROM t_product_stock_detail t1
 LEFT JOIN t_order_detail t2
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t1.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_return_detail t6
 ON t1.mkc_id = t6.mkc_id
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 AND t7.is_delete = 0
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 LEFT JOIN t_product_adjust_price_detail t16
 ON t16.mkc_id = t1.mkc_id
 LEFT JOIN t_product_adjust_price t17
 ON t17.id = t16.adjust_price_id
 AND t17.status = 3
 WHERE t3.order_state = 9
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t6.is_delete = 0
 AND t7.is_delete = 0
 AND t16.is_delete = 0
 AND t2.is_mobile = TRUE
 AND t17.type IN (2, 3)
 AND t7.check2_time BETWEEN '2024-10-14 00:00:00'
 AND '2024-10-31 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6) GROUP BY t1.mkc_id)
 SELECT t2.id AS orderDetailId, 0 - (IFNULL(t1.inprice, 0) - IFNULL(t16.self_modify_price, 0.00) - IFNULL(t16.in_source_modify_price, 0.00) + IFNULL(t1.transfer_price, 0)) statistics_in_price, 0 - t1.inprice in_price, 0 - t6.return_price + IFNULL(t1.inprice, 0) - IFNULL(t16.self_modify_price, 0.00) - IFNULL(t16.in_source_modify_price, 0.00) + IFNULL(t1.transfer_price, 0) statistics_gross_profit, 0 - t6.return_price + IFNULL(t1.inprice, 0) gross_profit, 0 - t1.examine_price examine_price, 0 - (t6.return_price - IFNULL(t1.examine_price, 0)) examine_gross_profit, t2.is_mobile is_mobile, t7.check2_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, ifnull(t3.order_parent_id, t3.order_id) order_id, '退款' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid sku_id, t2.detail_type, t1.insource_id insource_id, NULL insourceId2, t1.imei i_mei, t1.imei_2 imei2, t1.imei_3 imei3, t1.imei_4 imei4, t1.mkc_id, t1.instock_time in_stock_time, t1.invoice_type invoice_flag, '-1' AS count, 0 - t2.price price, 0 - t2.price price2, 0 - t2.sell_price sell_price, 0 - t2.gift_price gift_price, 0 - t2.discount_price discount_price, 0 - t2.point_price point_price, 0 - t6.return_price statistics_price, 0 - t6.return_price actualPaymentPrice, 0 - IFNULL(t16.self_modify_price, 0.00) self_modify_price, 0 - IFNULL(t16.after_in_source_modify_price, 0.00) in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid AS product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) AS customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t7.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1
 AND t2.product_stock_type != 3)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t1.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_return_detail t6
 ON t1.mkc_id = t6.mkc_id
 AND t6.product_stock_type != 3
 AND t6.is_delete = 0
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 AND t7.is_delete = 0
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 LEFT JOIN adjustPriceTemp t16
 ON t16.mkc_id = t1.mkc_id
 WHERE t3.order_state = 9
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t7.return_type NOT IN (7, 8)
 AND t7.is_delete = 0
 AND t2.is_mobile = TRUE
 AND t2.product_stock_type != 3
 AND t7.check2_time BETWEEN '2024-10-14 00:00:00'
 AND '2024-10-31 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT t2.id AS orderDetailId, t1.statistics_price statistics_in_price, t1.inprice in_price, (t2.statictisc_price + IFNULL(t2.gift_price, 0) - IFNULL(t1.statistics_price, 0)) statistics_gross_profit, (t2.statictisc_price + IFNULL(t2.gift_price, 0) - IFNULL(t1.inprice, 0)) gross_profit, t1.examine_price examine_price, (t2.statictisc_price + IFNULL(t2.gift_price, 0) - IFNULL(t1.examine_price, 0)) examine_gross_profit, t2.is_mobile is_mobile, t3.order_complete_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, CASE WHEN t3.order_state = 9 THEN ifnull(t3.order_parent_id, t3.order_id) ELSE t3.order_id END order_id, '销售' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid sku_id, t2.detail_type, t1.insource_id insource_id, NULL insourceId2, t1.imei imei, t1.imei_2 i_mei2, t1.imei_3 i_mei3, t1.imei_4 i_mei4, t1.mkc_id, t1.instock_time in_stock_time, t1.invoice_type invoice_flag, '1' AS count, t2.price price, t2.price price2, t2.sell_price sell_price, t2.gift_price gift_price, t2.discount_price discount_price, t2.point_price point_price, t2.statictisc_price statistics_price, (t2.price - t2.discount_price - t2.point_price) actualPaymentPrice, t1.modify_price self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t3.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1
 AND t2.product_stock_type != 3)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t1.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t2.is_mobile = TRUE
 AND t2.product_stock_type != 3
 AND t3.order_complete_time BETWEEN '2024-10-14 00:00:00'
 AND '2024-10-31 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT t2.id AS orderDetailId, 0 - t16.adjust_price statistics_in_price, 0 in_price, t16.adjust_price statistics_gross_profit, 0 gross_profit, 0 examine_price, 0 examine_gross_profit, t2.is_mobile is_mobile, t17.handle_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, CASE WHEN t3.order_state = 9 THEN ifnull(t3.order_parent_id, t3.order_id) ELSE t3.order_id END order_id, '交易完成价保' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid sku_id, t2.detail_type, t1.insource_id insource_id, NULL insourceId2, t1.imei imei, t1.imei_2 imei2, t1.imei_3 imei3, t1.imei_4 imei4, t1.mkc_id, t1.instock_time in_stock_time, t1.invoice_type invoice_flag, 0 AS count, 0 price, 0 price2, 0 sell_price, 0 gift_price, 0 discount_price, 0 point_price, 0 statistics_price, 0 actualPaymentPrice, if(t17.type = 3, t16.adjust_price, 0.00) self_modify_price, if(t17.type = 2, t16.adjust_price, 0.00) in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t3.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1
 AND t2.product_stock_type != 3)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t1.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 LEFT JOIN t_product_adjust_price_detail t16
 ON t16.mkc_id = t1.mkc_id
 LEFT JOIN t_product_adjust_price t17
 ON t17.id = t16.adjust_price_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t2.is_mobile = TRUE
 AND t2.product_stock_type != 3
 AND t17.status = 3
 AND t17.type IN (2, 3)
 AND t16.is_adjust_after_trade = true
 AND t17.handle_time BETWEEN '2024-10-14 00:00:00'
 AND '2024-10-31 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT max(t10.id) AS orderDetailId, SUM(t10.statistics_in_price) statistics_in_price, SUM(t10.in_price) in_price, SUM(t10.statistics_gross_profit) statistics_gross_profit, sum(t10.gross_profit) gross_profit, SUM(t10.examine_price) examine_price, SUM(t10.examine_gross_profit) examine_gross_profit, t10.is_mobile is_mobile, t10.date date, t10.order_complete_time, t10.area_id area_id, t10.accountingBodyId, t10.accountingBodyName, t10.order_id order_id, t10.business_type business_type, t10.order_type order_type, t10.user_id user_id, t10.ppid sku_id, t10.detail_type, t10.insource_id insource_id, t10.inSourceId2 inSourceId2, t10.i_mei_or_barcode i_mei, t10.imei2 imei2, t10.imei3 imei3, t10.imei4 imei4, t10.mkc_id, t10.in_stock_time, t10.invoice_flag, SUM(t10.count) count, SUM(t10.price) price, SUM(t10.price) price2, SUM(t10.sell_price) sell_price, SUM(t10.gift_price) gift_price, SUM(t10.discount_price) discount_price, SUM(t10.point_price) point_price, SUM(t10.statistics_price) statistics_price, SUM(t10.actualPaymentPrice) actualPaymentPrice, SUM(t10.self_modify_price) self_modify_price, 0 in_source_modify_price, t10.in_user_id in_user_id, t10.in_user_name in_user_name, t10.product_color product_color, t10.product_id product_id, t10.cid cid, t10.brand_id brand_id, t10.product_name product_name, t10.customer_type customer_type, t10.order_customer_type order_customer_type, t10.customer_name customer_name, t10.customer_phone customer_phone, t10.contract_name contract_name, t10.contract_mobile contract_mobile, t10.trader_name trader_name, t10.comment comment, t10.product_comment product_comment, t10.label_id label_id, t10.label_name label_name, t10.detail_parent_id, t10.serial_number_mark_name serial_number_mark_name, t10.serial_number_mark_color serial_number_mark_color, t10.serial_number_mark serial_number_mark, t10.aux_sale_man_names, t10.sample_product
 FROM (SELECT t2.id, SUM(IFNULL(IFNULL(t1.statistics_price * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)) statistics_in_price, SUM(IFNULL(IFNULL(t1.inprice * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)) in_price, (((t2.statictisc_price + IFNULL(t2.gift_price, 0)) * if(t2.virtual_product = 0, sum(t1.counts), t2.counts)) - SUM(IFNULL(IFNULL(t1.statistics_price * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0))) statistics_gross_profit, (((t2.statictisc_price + IFNULL(t2.gift_price, 0)) * if(t2.virtual_product = 0, sum(t1.counts), t2.counts)) - (SUM(IFNULL(IFNULL(t1.inprice * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)))) gross_profit, SUM(IFNULL(IFNULL(t1.examine_price * t1.counts, t2.examine_price), 0)) examine_price, (((t2.statictisc_price + IFNULL(t2.gift_price, 0)) * if(t2.virtual_product = 0, sum(t1.counts), t2.counts)) - (SUM(IFNULL(IFNULL(t1.examine_price * t1.counts, t2.examine_price), 0)))) examine_gross_profit, t2.is_mobile is_mobile, t3.order_complete_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, CASE WHEN t3.order_state = 9 THEN ifnull(t3.order_parent_id, t3.order_id) ELSE t3.order_id END order_id, '销售' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid ppid, t2.detail_type, IFNULL(t1.insource_id, vpcc.in_source_id) insource_id, t16.channel inSourceId2, t4.bar_code i_mei_or_barcode, '' imei2, '' imei3, '' imei4, NULL AS mkc_id, NULL in_stock_time, NULL invoice_flag, if(t2.virtual_product = 0, sum(t1.counts), t2.counts) count, t2.price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) price, t2.sell_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) sell_price, t2.gift_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) gift_price, t2.discount_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) discount_price, t2.point_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) point_price, t2.statictisc_price * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) statistics_price, (t2.price - t2.discount_price - t2.point_price) * if(t2.virtual_product = 0, sum(t1.counts), t2.counts) actualPaymentPrice, SUM(IFNULL(t1.modify_price * t1.counts, 0)) self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t3.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product, t3.invoice_status
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_order_detail_extend vpcc
 ON t2.id = vpcc.id
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t2.is_mobile = FALSE
 AND t3.order_complete_time BETWEEN '2024-10-14 00:00:00'
 AND '2024-10-31 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6) GROUP BY t2.id, t1.insource_id, t4.bar_code, t4.product_color, t4.cid, t4.brand_id, t4.product_name, t3.trader_name, la.id, la.label_name, sysc.name, sysc.value, sysc.id, t36.aux_sale_man_names, t1.sample_product, t40.id, t40.name
 UNION ALL
 SELECT t2.id, 0 - SUM(IFNULL(IFNULL(t1.statistics_price * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)) statistics_in_price, 0 - SUM(IFNULL(IFNULL(t1.inprice * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)) in_price, 0 - ((t6.return_price * t6.return_count * count(t2.id)) - SUM(IFNULL(IFNULL(t1.statistics_price * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0))) statistics_gross_profit, 0 - ((t6.return_price * t6.return_count * count(t2.id)) - (SUM(IFNULL(IFNULL(t1.inprice * t1.counts, if(null = 1
 AND t2.jiuxun_service_flag = 1, t2.jiuxun_service_cost_price, t2.in_price)), 0)))) gross_profit, 0 - SUM(IFNULL(IFNULL(t1.examine_price * t1.counts, t2.examine_price), 0)) examine_price, 0 - ((t6.return_price * t6.return_count * count(t2.id)) - (SUM(IFNULL(IFNULL(t1.examine_price * t1.counts, t2.examine_price), 0)))) examine_gross_profit, t2.is_mobile is_mobile, t7.check2_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, ifnull(t3.order_parent_id, t3.order_id) order_id, '退款' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid ppid, t2.detail_type, IFNULL(t1.insource_id, vpcc.in_source_id) insource_id, t16.channel inSourceId2, t4.bar_code i_mei_or_barcode, '' imei2, '' imei3, '' imei4, NULL AS mkc_id, NULL in_stock_time, NULL invoice_flag, 0 - count(t2.id) * t6.return_count count, 0 - (t2.price * count(t2.id)) * t6.return_count price, 0 - (t2.sell_price * count(t2.id)) * t6.return_count sell_price, 0 - (t2.gift_price * count(t2.id)) * t6.return_count gift_price, 0 - (t2.discount_price * count(t2.id)) * t6.return_count discount_price, 0 - (t2.point_price * count(t2.id)) * t6.return_count point_price, 0 - (t6.return_price * count(t2.id)) * t6.return_count actualPaymentPrice, 0 - (t6.return_price * count(t2.id)) * t6.return_count statistics_price, 0 - SUM(IFNULL(t1.modify_price, 0)) self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t7.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product, t3.invoice_status
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN (SELECT order_detail_id, avg(return_price) return_price, is_delete, return_id, return_count
 FROM t_return_detail
 WHERE is_delete = 0
 AND order_detail_type = 1 GROUP BY order_detail_id, return_id, is_delete, return_count) t6
 ON t2.id = t6.order_detail_id
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_order_detail_extend vpcc
 ON t2.id = vpcc.id
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state = 9
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t6.is_delete = 0
 AND t7.is_delete = 0
 AND t7.return_type NOT IN (7, 8)
 AND t2.is_mobile = FALSE
 AND t7.check2_time BETWEEN '2024-10-14 00:00:00'
 AND '2024-10-31 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6) GROUP BY t2.id, t1.insource_id, t4.bar_code, t4.product_color, t4.cid, t4.brand_id, t4.product_name, t6.return_price, t6.return_count, t7.check2_time, t7.check2_user_name, t7.comment, la.id, la.label_name, sysc.name, sysc.value, sysc.id, t36.aux_sale_man_names, t1.sample_product, t40.id, t40.name) t10
 WHERE is_mobile IS NOT NULL
 AND area_id IS NOT NULL
 AND order_id IS NOT NULL GROUP BY t10.business_type, t10.is_mobile, t10.date, t10.order_complete_time, t10.area_id, t10.order_id, t10.order_type, t10.user_id, t10.ppid, t10.insource_id, t10.i_mei_or_barcode, t10.in_user_id, t10.in_user_name, t10.product_color, t10.product_id, t10.cid, t10.brand_id, t10.product_name, t10.customer_type, t10.order_customer_type, t10.customer_name, t10.customer_phone, t10.contract_name, t10.contract_mobile, t10.trader_name, t10.inSourceId2, t10.detail_type, t10.comment, t10.product_comment, t10.label_id, t10.label_name, t10.detail_parent_id, t10.imei2, t10.imei3, t10.imei4, t10.mkc_id, t10.in_stock_time, t10.invoice_flag, t10.serial_number_mark_name, t10.serial_number_mark_color, t10.serial_number_mark, t10.aux_sale_man_names, t10.sample_product, t10.accountingBodyId, t10.accountingBodyName
 UNION ALL
 SELECT a.id AS orderDetailId, 0 AS statistics_in_price, 0 AS in_price, 0 - IFNULL(b.deduction_price, 0) AS statistics_gross_profit, 0 - IFNULL(b.deduction_price, 0) AS gross_profit, 0 AS examine_price, 0 - IFNULL(b.deduction_price, 0) AS examine_gross_profit, t2.is_mobile is_mobile, b.return_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, CASE WHEN t3.order_state = 9 THEN ifnull(t3.order_parent_id, t3.order_id) ELSE t3.order_id END order_id, '交易完成返销' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid sku_id, t2.detail_type, t1.insource_id insource_id, t16.channel inSourceId2, if(t4.is_mobile = true, t1.imei, t4.bar_code) imei, if(t4.is_mobile = true, t1.imei_2, '') imei2, if(t4.is_mobile = true, t1.imei_3, '') imei3, if(t4.is_mobile = true, t1.imei_4, '') imei4, if(t4.is_mobile = true, t1.mkc_id, NULL) mkc_id, NULL in_stock_time, NULL invoice_flag, 0 AS count, 0 AS price, t2.price AS price2, 0 AS sell_price, 0 AS gift_price, 0 AS discount_price, 0 AS point_price, 0 AS statistics_price, 0 - IFNULL(b.deduction_price, 0) AS actualPaymentPrice, 0 AS self_modify_price, 0 AS in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t3.COMMENT COMMENT, t2.COMMENT product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail a
 INNER JOIN t_operator_business b
 ON a.id = b.order_detail_id
 INNER JOIN t_order_detail t2
 ON a.detail_parent_id = t2.id
 LEFT JOIN t_order c FORCE INDEX (torder_ix_order_id)
 ON a.order_id = c.order_id
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND c.order_complete_time <= b.return_time
 AND b.`status` = 2
 AND b.is_delete = 0
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND b.version = 0
 AND b.return_time BETWEEN '2024-10-14 00:00:00'
 AND '2024-10-31 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT t2.id AS orderDetailId, 0 statistics_in_price, 0 in_price, 0 - (t6.return_price * t6.return_count) statistics_gross_profit, 0 - (t6.return_price * t6.return_count) gross_profit, 0 examine_price, 0 - (t6.return_price * t6.return_count) examine_gross_profit, t2.is_mobile is_mobile, t7.check2_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, ifnull(t3.order_parent_id, t3.order_id) order_id, '价格保障' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid ppid, t2.detail_type, IFNULL(t1.insource_id, vpcc.in_source_id) insource_id, t16.channel inSourceId2, if(t4.is_mobile = true, t1.imei, t4.bar_code) imei, t1.imei_2 imei2, t1.imei_3 imei3, t1.imei_4 imei4, if(t4.is_mobile = true, t1.mkc_id, NULL) mkc_id, NULL in_stock_time, NULL invoice_flag, 0 count, 0 price, 0 price2, 0 sell_price, 0 gift_price, 0 discount_price, 0 point_price, 0 - t6.return_price * t6.return_count actualPaymentPrice, 0 - t6.return_price * t6.return_count statistics_price, 0 self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t7.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_return_detail t6
 ON t2.id = t6.order_detail_id
 AND t6.mkc_id IS NULL
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_order_detail_extend vpcc
 ON t2.id = vpcc.id
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t6.is_delete = 0
 AND t7.is_delete = 0
 AND t7.return_type = 8
 AND t7.check2_time BETWEEN '2024-10-14 00:00:00'
 AND '2024-10-31 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6)
 UNION ALL
 SELECT t2.id AS orderDetailId, 0 statistics_in_price, 0 in_price, 0 - (t6.return_price * t6.return_count) statistics_gross_profit, 0 - (t6.return_price * t6.return_count) gross_profit, 0 examine_price, 0 - (t6.return_price * t6.return_count) examine_gross_profit, t2.is_mobile is_mobile, t7.check2_time date, t3.order_complete_time, t3.area_id area_id, t40.id AS accountingBodyId, t40.name AS accountingBodyName, ifnull(t3.order_parent_id, t3.order_id) order_id, '价格保障' AS business_type, t3.order_type order_type, t3.user_id user_id, t2.ppid ppid, t2.detail_type, IFNULL(t1.insource_id, vpcc.in_source_id) insource_id, t16.channel inSourceId2, if(t4.is_mobile = true, t1.imei, t4.bar_code) imei, t1.imei_2 imei2, t1.imei_3 imei3, t1.imei_4 imei4, if(t4.is_mobile = true, t1.mkc_id, NULL) mkc_id, NULL in_stock_time, NULL invoice_flag, 0 count, 0 price, 0 price2, 0 sell_price, 0 gift_price, 0 discount_price, 0 point_price, 0 - t6.return_price * t6.return_count actualPaymentPrice, 0 - t6.return_price * t6.return_count statistics_price, 0 self_modify_price, 0 in_source_modify_price, t2.in_user_id in_user_id, t2.in_user_name in_user_name, t4.product_color product_color, t4.productid product_id, t4.cid cid, t4.brand_id brand_id, t4.product_name, t5.user_type customer_type, t3.customer_type AS order_customer_type, if(t3.order_type = 3
 OR t3.order_type = 4, t11.special_username, t5.real_name) customer_name, t5.user_mobile AS customer_phone, t3.contract_name AS contract_name, t3.contract_mobile AS contract_mobile, t3.trader_name trader_name, t7.comment comment, t2.comment product_comment, la.id AS label_id, la.label_name label_name, t2.detail_parent_id, sysc.name serial_number_mark_name, sysc.value serial_number_mark_color, sysc.id serial_number_mark, t36.aux_sale_man_names, t1.sample_product
 FROM t_order_detail t2
 LEFT JOIN t_product_stock_detail t1
 ON (t1.order_detail_id = t2.id
 AND t1.order_detail_type = 1)
 LEFT JOIN t_order t3
 ON t2.order_id = t3.order_id
 LEFT JOIN t_product_info t4
 ON t2.ppid = t4.ppriceid
 LEFT JOIN t_user t5
 ON t5.id = t3.user_id
 LEFT JOIN t_return_detail t6
 ON t6.mkc_id = t1.mkc_id
 LEFT JOIN t_return t7
 ON t6.return_id = t7.id
 LEFT JOIN t_depart_info t8
 ON t3.area_id = t8.id
 LEFT JOIN t_operator_business t16
 ON (t2.id = t16.order_detail_id)
 LEFT JOIN t_after_services_buy t17
 ON t2.id = t17.bind_order_detail_id
 AND t17.is_delete = 0
 LEFT JOIN t_special_user t11
 ON t11.user_id = t3.user_id
 LEFT JOIN t_product_label pl
 ON t4.productid = pl.product_id
 AND pl.is_delete = 0
 LEFT JOIN t_label la
 ON pl.label_id = la.id
 AND la.is_delete = 0
 LEFT JOIN t_sys_config sysc
 ON t1.serial_number_mark = sysc.id
 AND sysc.is_delete = 0
 LEFT JOIN t_order_detail_extend vpcc
 ON t2.id = vpcc.id
 LEFT JOIN t_finance_accounting_body t40
 ON t40.id = t8.accounting_body_id
 LEFT JOIN t_order_extend t36
 ON t3.order_id = t36.order_id
 WHERE t3.order_state IN (3, 9)
 AND t2.is_delete = 0
 AND t3.is_delete = 0
 AND t5.is_delete = 0
 AND t6.is_delete = 0
 AND t7.is_delete = 0
 AND t7.return_type = 8
 AND t7.check2_time BETWEEN '2024-10-14 00:00:00'
 AND '2024-10-31 23:59:59'
 AND t3.area_id IN (216)
 AND t3.area_id IN (216)
 AND t2.product_stock_type IN (0, 1, 2, 6) ORDER BY date DESC, orderDetailId DESC) t
 LEFT JOIN t_category category
 ON t.cid = category.id
 LEFT JOIN t_depart_info t8
 ON t.area_id = t8.id GROUP BY t.in_user_id, t.in_user_name) b) t GROUP BY t.in_user_id, t.in_user_name ORDER BY counts DESC, sort_value DESC
 LIMIT 0,10;