SELECT
/*支付方式*/ pm.`name` AS pm_name,/*支付方式表<===>name*/
/*所属公司*/ pop_p.`name` AS purchaser_name,/*采购商表<===>name*/
cs.id AS cs_id,/*客户供应商表<===>id字段*/
/*供应商*/ cs.name AS cs_name,/*客户供应商表<===>name*/
/*订单号*/ o.order_code,/*订单主表->子订单号*/
/*下单时间*/ o.order_time,/*订单主表->下单时间*/
/*快递公司*/ ec.`name` AS e_name,/*快递表->name*/
/*快递单号*/ ds.sheet_code,/*b2b订单发货信息表->快递单号*/
/*发货时间*/ op.deliver_time,/*b2b订单履约表->发货时间*/
/*完成时间*/ o.finish_time,/*订单主表->完结时间*/
sw.type AS sw_type,/*仓库表->仓库类型*/
/*sku*/ oi.item_id,/*B2B订单商品明细表->商品ID*/
/*仓库*/ sw.`name` AS warehouse_name,/*仓库表->仓库名称*/
/*来源*/ oc.channel_name,/*订单渠道表->渠道名称*/
/*订单类型*/ o.sub_channel,/*订单主表->订单子渠道*/
/*商品名称*/ i.`name` AS item_name,/*商品表->商品名称*/
/*品牌*/ ib.`name` AS brand_name,/*商品品牌表->商品品牌*/
/*分类*/ ic.`name` AS cate_name,/*商品分类表->分类名称*/
/*售后单ID*/ rpi.id AS return_id,/*售后退货处理详细表->id*/
/*退款时间*/ rpi.refund_time,/*售后退货处理详细表->退款时间*/
/*退款原因*/ rpi.process_result,/*售后退货处理详细表->详细办理结果*/
/*省*/ pro. NAME AS province_name,/*省份表->省份名称*/
cs.special_partner_type,/*客户供应商表->特殊合作伙伴*/
cs.platform,/*客户供应商表->平台 1 b2c 2 b2b*/
--- 发货
/*oi B2B订单商品明细表*/
/*商品数量（箱规数量）x 商品箱规规格*/
/*数量*/ oi.qty * oi.item_spec_amount AS num,
/*销售价（箱规维度）x 商品数量（箱规数量）*/
/*销售价*/ oi.sale_price * oi.qty AS total_sale_price,
/*总成交价=总售价-促销优惠金额*/
/*成交价*/ oi.total_deal_price,
/*总运费*/
/*运费*/ oi.total_ship_price,
/*现金券总金额*/
/*现金券*/ oi.total_cash_coupon_price,
/*活动券总金额*/
/*活动券*/ oi.total_coupon_price,
/*促销优惠总金额*/
/*减免金额*/ oi.total_promotion_price,
/*总使用余额*/
/*总使用余额*/ oi.total_balance_price,
/*总支付金额=总成交价+总运费+总包材费-总现金券-总活动券 + 总税费金额-总使用余额*/
/*支付金额*/ oi.total_pay_price,
/*供应商商品供货价格 x 商品数量（箱规数量）*/
/*成本价*/ oi.supply_price * oi.qty AS total_cost_price,
/*商品真正需要交给海关的总税费金额（包税，非包税都会记录）*/
/*综合税*/ oi.total_actual_tax_price AS total_tax_price,
/*总运费-活动券总金额+供应商商品供货价格x商品数量（箱规数量）+ ...... */
/*应付商家*/ oi.total_ship_price - oi.total_coupon_price + oi.supply_price * oi.qty +
IFNULL(case when o.order_time >= '2018-07-18 17:00:00'
then oi.total_tax_price
else oi.total_actual_tax_price end, 0)
AS should_pay_price,
/*(总支付金额=总成交价+总运费+总包材费-总现金券-总活动券 + 总税费金额-总使用余额) - (总运费-动券总金额+供应商商品供货价格x商品数量（箱规数量）+ ......)*/
/*收入*/ oi.total_pay_price - (oi.total_ship_price - oi.total_coupon_price + oi.supply_price * oi.qty +
IFNULL(case when o.order_time >= '2018-07-18 17:00:00'
then oi.total_tax_price
else oi.total_actual_tax_price end, 0)
) AS income_price,
--- 退款
/*oi B2B订单商品明细表*/
/*rpi b2b_return_process_item 售后退货处理详细表*/
/*质检商品数量*/
/*数量*/ rpi.item_quantity AS num,
/*销售价（箱规维度）x 商品数量（箱规数量）x质检商品数量/(商品数量（箱规数量）x商品箱规规格) */
/*销售价*/ oi.sale_price * oi.qty * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS total_sale_price,
/*(总成交价=总售价-促销优惠金额)x质检商品数量/(商品数量（箱规数量）x商品箱规规格)*/
/*成交价*/ oi.total_deal_price * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS total_deal_price,
/*总运费x质检商品数量/(商品数量（箱规数量）x商品箱规规格)*/
/*运费*/ oi.total_ship_price * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS total_ship_price,
/*现金卷*/ oi.total_cash_coupon_price * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS total_cash_coupon_price,
/*活动卷*/ oi.total_coupon_price * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS total_coupon_price,
/*减免金额*/ oi.total_promotion_price * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS total_promotion_price,
/*总使用余额*/ oi.total_balance_price * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS total_balance_price,
/*支付金额*/ oi.total_pay_price * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS total_pay_price,
/*成本价*/ oi.supply_price * oi.qty * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS total_cost_price,
/*综合税*/ oi.total_actual_tax_price * rpi.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 >= '2018-07-18 17:00:00'
then oi.total_tax_price
else oi.total_actual_tax_price end, 0)
) * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS should_pay_price,
/*收入*/ (
oi.total_pay_price - (
oi.total_ship_price - oi.total_coupon_price + oi.supply_price * oi.qty +
IFNULL(case when o.order_time >= '2018-07-18 17:00:00'
then oi.total_tax_price
else oi.total_actual_tax_price end, 0)
)
) * rpi.item_quantity / (oi.qty * oi.item_spec_amount) AS income_price,
FROM
mia_b2b.b2b_order_performance AS op/*b2b订单履约表*/
LEFT JOIN
mia_b2b.b2b_orders/*订单主表*/ AS o ON op.b2b_order_id = o.id
LEFT JOIN
mia_b2b.b2b_order_item/*订单商品明细表*/ AS oi ON o.id = oi.order_id
LEFT JOIN
stock_warehouse/*仓库表*/ sw ON oi.warehouse_id = sw.id
LEFT JOIN
customer_supplier/*客户供应商表*/ cs ON cs.id = sw.supplier_id
LEFT JOIN
province/*省份表*/ pro ON cs.license_province_id = pro.id
LEFT JOIN
procurement_contract pop_pc/*供应商合同表*/ ON pop_pc.supplier_id = sw.supplier_id
LEFT JOIN
purchaser/*采购商表*/ pop_p ON pop_p.id = pop_pc.purchaser_id
LEFT JOIN
mia_b2b.b2b_return_process_item/*售后退货处理详细表*/ AS rpi ON rpi.order_item_id = oi.id
LEFT JOIN
mia_b2b.b2b_item/*商品表*/ AS i ON oi.item_id = i.id
LEFT JOIN
item_brand/*商品品牌表*/ ib ON i.brand_id = ib.id
LEFT JOIN
mia_b2b.item_category/*商品分类表*/ ic ON i.category_id = ic.id
LEFT JOIN
mia_b2b.b2b_dst_sheet/*b2b订单发货信息表*/ ds ON ds.b2b_order_id = o.id
LEFT JOIN
express_company/*快递表*/ ec ON ds.express_id = ec.id
LEFT JOIN
order_channel/*订单渠道表*/ oc ON oc.channel_id = o.channel
LEFT JOIN
pay_mode/*支付方式表*/ pm ON o.pay_mode = pm.id
WHERE
TRUE
AND
-- 发货
/*mia_b2b.b2b_order_performance b2b订单履约表 发货时间*/
op.deliver_time BETWEEN '{$start_date}' AND '{$end_date}'
-- 退款
/*b2b_return_process_item 售后退货处理详细表 退款时间*/
rpi.refund_time BETWEEN '{$start_date}' AND '{$end_date}'
AND
/*mia_b2b.b2b_order_item 订单商品明细表*/
/*订单商品状态（1：未拣货；2：已拣货未生产；3：已生产待出库;4：配送中；5：已签收；6：完成；7：暂停发货；8：取消；9：确认取消；10：确认换货）*/
oi.status <> 10
AND
/*b2b_orders订单主表 已付款*/
o.is_paid = 1
/*stock_warehouse 仓库表*/
sw.id not in '$warehouse_ids'/*自营入驻仓库与自有仓库对应表-状态正常的-b2b_warehouse_id-b2b仓库id字段*/
GROUP BY
-- 发货
/*mia_b2b.b2b_order_item 订单商品明细表*/
oi.id
-- 退款
/*b2b_return_process_item 售后退货处理详细表 退款时间*/
rpi.id
