SELECT payment / * * / PM. ` name ` AS pm_name, / * payment table < = = = > the name * * / / / * the company of affiliation pop_p. ` name ` AS purchaser_name, / * purchaser table < = = = > name * / cs. Id AS cs_id, table customer/supplier / * < = = = > id field * / supplier / * * / cs name AS cs_name, / * table < = = = > customer/supplier name * order number / / * * / o.o rder_code, table / * order - > child order number * * / / / * order time O.o rder_time, table / * order - > order time. * / / * express * / ec ` name ` AS e_name, / * express table - > name * / / * Courier number * / ds sheet_code, / * b2b order delivery information table - > Courier number * / / * delivery time * / op. Deliver_time, / * * b2b order the performance table - > the delivery time/completion time / * * / o.f inish_time, / * * / end table order - > time. Sw type AS sw_type, / * type * / warehouse table - > warehouse / * * / sku oi item_id, / * B2B order commodities list - > ID * / / * * / sw warehouse. ` name ` AS warehouse_name, / * warehouse table - > name * * / / / * source Oc. Channel_name, / * order channel table - > channel name * / / * * / order type grown ub_channel, / * table order - > order sub channels * commodity name / / * * / i. ` name ` AS item_name, / * * / commodities - > table name Brand / * * / ib. ` name ` AS brand_name, / * brand commodities brand table - > * / classification / * * / IC. ` name ` AS cate_name, / * commodity classification - > category name * * / / / * after single ID rpi. ID AS Return_id, / * after-sales returns detailed table - > id * / / * * / refund time rpi. Refund_time, / * after-sales returns detailed table - > refund time * / / * * / refund reason NAME AS province_name,/* Province_name -> province_name,/* province_name -> province_name */ Cs.special_partner_type,/* Customer supplier list -> Special Partner */ Cs.platform,/* Customer supplier list -> Platform 1 B2C 2 B2B */ -- Shipping /* OI B2B Order Itemlist */ /* Quantity of goods (box gauge quantity) x */ /* Quantity */ oi.qty * oi.item_spec_amount AS num, */ * sale_price */ oi.sale_price * oi.qty AS total_sale_price, / * total price = total price promotions amount * / / * * price/oi total_deal_price, / * always * / / * freight. * / oi total_ship_price, /* total cash coupon */ / total cash coupon */ oi.total cash_coupon_price, /* total cash coupon */ /* total_balance_price, /* total_balance_price, /* total_balance_price, /* Total payment amount = Total transaction price + Total freight + Total package material fee - Total cash coupon - Total activity coupon + Total tax amount - Total usage balance */ /* Total payment amount */ oi.total_pay_price, */ * Supply_price * oi.qty AS total_cost_price */ * Supply_price * oi.qty AS total_cost_price Non-package tax will be recorded) */ /* Comprehensive tax */ oi. total_ACTUal_tax_price AS total_TAX_price, /* Total freight - total amount of activity coupon + supplier's commodity supply price x commodity quantity (carton quantity) +...... */ /* Supplymerchant */ oi.total_ship_price-oi.total_coupon_price + Oi.supply_price * oi.qty + IFNULL(case when o.order_time >= 'the 2018-07-18 17:00:00'
then oi.total_tax_price
elseoi.total_actual_tax_price end, 0) AS should_pay_price, /*(Total amount of payment = Total transaction price + total freight + Total packaging material fee - Total cash coupon - Total activity coupon + total tax amount - total usage balance) - (Total freight - total amount of coupon + supplier's commodity supply price x Quantity of goods (carton quantity) +......) */ /* revenue */ oi.total_pay_price - (oi.total_ship_price - oi.total_coupon_price + OI.supply_price * OI.qty + IFNULL(case when o.order_time >= 'the 2018-07-18 17:00:00'
then oi.total_tax_price
elseoi.total_actual_tax_price end, 0) ) AS income_price, */ * Rpi b2B_return_process_item */ * Rpi. */ * Selling price */ oi.sale_price * Oi.qty * rpi.item_quantity /(Oi.qty * Rpi.item_quantity */ oi.item_spec_amount) AS total_sale_price, /*(Total transaction price = Total Sale price - Promotional amount)x Quantity of quality inspection goods /(Quantity of goods (carton quantity)x carton specification)*/ /* Transaction price */ oi.total_deal_price * rpi.item_quantity /(oi.qty * oi.item_spec_amount) AS total_deal_price, */ * Total freight */ oi.total_ship_price * rpi.item_quantity /(oi.qty * Oi.item_spec_amount) AS total_ship_price, Item_quantity/(oi.qty * oi.item_spec_amount) AS total_cash_coupon_price, Item_quantity/(oi.qty * oi.item_spec_amount) AS total_coupon_price, Item_quantity/(oi.qty * oi.item_spec_amount) AS total_promotion_price, Item_quantity/(oi.qty * oi.item_spec_amount) AS total_balance_price, Item_quantity/(oi.qty * oi.item_spec_amount) AS total_pay_price, /* Supply_price * oi.qty * rpi.item_quantity/(oi.qty * oi.item_spec_amount) AS total_cost_price, Item_quantity/(oi.qty * oi.item_spec_amount) AS total_tax_price, */ (oi.total_ship_price-oi.total_coupon_price + Oi.supply_price * oi.qty + IFNULL(case when o.order_time >= 'the 2018-07-18 17:00:00'
then oi.total_tax_price
elseoi.total_actual_tax_price end, 0) ) * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS should_pay_price, /* supply_price */ (oi.total_pay_price - (oi.total_ship_price - oi.total_coupon_price + OI.supply_price * oi.qty + IFNULL(case when o.order_time >= 'the 2018-07-18 17:00:00'
then oi.total_tax_price
elseoi.total_actual_tax_price end, 0) ) ) * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS income_price, FROM mia_b2b_order_performance AS op/* b2b_orders */ LEFT JOIN mia_b2b_orders */ AS o ON op.b2b_order_id = O.ID LEFT JOIN mia_b2b_order_item /* AS oi ON O. ID = oi. Order_id LEFT JOIN stock_warehouse/* warehouse table */ sw ON Oi.warehouse_id = sw.id LEFT JOIN customer_supplier/* Supplier_id */ cs ON Cs. id = sw.supplier_id LEFT JOIN province/* Supplier_id */ pro ON cs.license_province_id = pro.id LEFT JOIN PROCUrement_contract POP_PC /* Supplier contract table */ ON POP_PC.supplier_id = Sw.supplier_id LEFT JOIN Duplicate /* Purchasers list */ POP_P ON POP_P.id = POP_PC.purchaser_id LEFT JOIN Order_item_id = oi.id LEFT JOIN mia_b2b. B2b_item /* Item list */ AS I ON oi.item_id = I.ID LEFT JOIN Item_brand /* merchandise brand table */ ib ON i.brand_id = ib.id LEFT JOIN mia_b2b.item_category/* Merchandise category table */ IC ON i.category_id = ic.id LEFT JOIN mia_b2b_dst_sheet /* B2B order shipping information table */ ds ON ds.b2b_order_id = O. ID LEFT JOIN Express_company /* express table */ ec ON ds.express_id = ec.id LEFT JOIN order_channel/* order channel table */ oc ON oc.channel_id = O.channel LEFT JOIN pay_mode/* payment mode table */ PM ON O.pay_mode = pm.id WHERE TRUE AND -- shipment /* mia_b2b_order_performance B2B order fulfillment table shipment time */ op.deliver_time BETWEEN'{$start_date}' AND '{$end_date}'-- Refund /*b2b_return_process_item After-sale Return processing detail Table Refund time */ rpi.refund_time BETWEEN'{$start_date}' AND '{$end_date}'AND /*mia_b2b.b2b_order_item 2: Picked goods are not produced; 3: Production is ready for delivery; 4: In delivery; 5: Signed for; 6. 7: suspension of delivery; 8. 9: Confirm cancellation; Status <> 10 AND /*b2b_orders paid */ O. i_paid = 1 /*stock_warehouse */ sw.id notin '$warehouse_ids'/* GROUP BY -- shipping /* MIa_b2b.b2b_order_item Order itemlist */ oi.id -- refund /*b2b_return_process_item Return time */ rpi.idCopy the code