每月对账单
出库明细
# 出库明细
select psod.bar_code 条码,
psod.goods_name 商品名称,
pb.name 品牌名称,
psod.goods_color 商品规格,
psod.specs_name 商品尺码,
pg.goods_price 商品原价,
pg.goods_code 商品编号,
psod.send_num 商品数量,
pg.member_price 会员价,
ifnull(pg.category_name, '') 品类,
ifnull(pg.thin_thick, '') 薄厚,
ifnull(pg.long_short, '') 长短,
ifnull(pg.suit_people, '') 适应人群,
ifnull(pg.suit_season, '') 适应季节,
ifnull(pg.accessory, '') 配饰,
ifnull(pg.style_no, '') 款号,
pg.buy_price 采购价,
pg.collection_premium 手续费,
pg.postage 运费,
psoo.send_store_name 出库门店,
psoo.received_store_name 入库门店,
psod.order_id 出库单号,
psoo.send_time 出库时间,
case psoo.status when 'S' then '已入库' when 'AW' then '待审核' when 'W' then '待入库' end 出库单状态
from p_stock_order_details psod
left join p_goods pg on pg.goods_id = psod.goods_id
left join p_brand pb on pb.id = pg.brand_id
left join p_stock_out_order psoo on psoo.id = psod.order_id
where 1 = 1
and psoo.send_time between '2019-05-01 00:00:00' and '2019-05-31 23:59:59'
and psod.order_type = 1
and (psoo.status = 'W' or psoo.status = 'AW' or psoo.status = 'S');
运营导出对账单（不带398）
SELECT po.store_no AS 门店编号,
ps.store_name AS 门店名称,
po.contacts AS 联系人,
po.mobile_phone AS 手机,
po.detailed_address AS 地址,
# IFNULL(pag.activity_id, '无') AS 活动ID,
IFNULL(pg.category_name, '无') AS 品类,
concat("\t", IFNULL(pg.goods_code, '无')) AS 商品编号,
IFNULL(pg.arrival_date, '无') AS 到货日期,
IFNULL(pg.in_date, '无') AS 入库日期,
IFNULL(pg.supplier, '无') AS 供应商,
IFNULL(pg.buyer, '无') AS 采购人,
IFNULL(pg.collection_premium, '无') AS 手续费,
IFNULL(pg.suit_people, '无') AS 适应人群,
IFNULL(po.remark, '无') AS 备注,
IFNULL(pb.`name`, '未知') AS 品牌,
pg.goods_name AS 商品,
pg.style_no AS 款号,
pg.goods_color AS 颜色,
pod.specs_name AS 尺码,
concat("\t", IFNULL(pod.bar_code, '未知')) AS 条码,
pod.goods_num AS 数量,
pg.goods_price AS 商品价格,
IFNULL(pg.member_price, '无') AS 会员价,
pod.goods_price AS 购买价格,
IFNULL(pg.buy_price, '无') AS 采购价格,
IFNULL(pg.goods_pic, '无') AS 图片,
po.create_time AS 订单时间,
concat("\t", po.id) AS 订单号,
concat("\t", IFNULL(pp.id, '无')) AS 系统流水号,
CASE
WHEN po.trade_type = 'JSAPI' THEN '小程序'
WHEN po.trade_type = 'APP' THEN 'APP'
ELSE '未知' END AS 下单方式,
CASE
WHEN po.pay_type = 1 THEN '电子支付'
WHEN po.pay_type = 2 THEN '现金支付'
WHEN po.pay_type = 3 THEN '现金支付(无码商品)'
ELSE '未知' END AS 支付类型,
CASE
WHEN po.order_status = 'C' THEN '已完成'
WHEN po.order_status = 'S' THEN '支付成功'
WHEN po.order_status = 'RS' THEN '已退款'
WHEN po.order_status = 'AU' THEN '退款审核中'
ELSE '未知' END AS 订单状态,
CASE
WHEN po.logistic_status = 'LU' THEN '已发货'
WHEN po.logistic_status = 'LW' THEN '待发货'
WHEN po.logistic_status = 'LS' THEN '已收货'
WHEN po.logistic_status = 'LP' THEN '部分发货'
ELSE '未知' END AS 发货状态,
CASE
WHEN po.is_offline = 1 THEN '线下'
WHEN po.is_offline = 0 OR po.is_offline IS NULL THEN '线上'
ELSE '未知' END AS 线上线下,
CASE WHEN po.coupon_price IS NULL THEN 0 ELSE po.coupon_price END AS 优惠券金额,
CASE
WHEN pp.total_fee IS NOT NULL THEN pp.total_fee
ELSE po.price - IFNULL(po.coupon_price, 0) END 实付金额,
'线上普通支付订单' AS 订单类型,
CASE
WHEN po.pay_channel = 'wxpay' THEN '微信'
WHEN po.pay_channel = 'alipay' THEN '支付宝'
WHEN po.pay_channel = 'balancepay' THEN '余额'
ELSE '微信' END AS 支付渠道,
NULL AS 收银员,
po.store_type as 门店类型,
po.member_level as 会员等级,
pmo.marketing_id as 关联营销ID,
concat("\t", pp.transaction_id) 第三方流水号,
case pod.goods_type when 0 then '普通商品' when 1 then '特卖商品' end 商品类型,
pod.remark 订单明细备注
FROM p_order po
LEFT JOIN p_order_details pod ON po.id = pod.order_id
LEFT JOIN p_goods pg ON pg.goods_id = pod.goods_id
LEFT JOIN p_brand pb ON pb.id = pg.brand_id
LEFT JOIN p_pay pp ON pp.order_id = po.id
LEFT JOIN p_store ps ON ps.store_no = po.store_no
# LEFT JOIN p_activity_goods pag ON pag.goods_id = pg.goods_id
LEFT JOIN p_marketing_order pmo on (pmo.order_id = pod.order_id AND pmo.bar_code = pod.bar_code)
WHERE 1 = 1
AND po.order_type IS NULL
AND (po.order_status = 'C' or order_status = 'RS' or order_status = 'S' or order_status = 'AU')
AND (po.is_offline = 0 OR po.is_offline IS NULL)
AND po.pay_type = 1
AND po.create_time >= '2020-04-01 00:00:00'
AND '2020-04-12 23:59:59' >= po.create_time
UNION
SELECT po.store_no AS 门店编号,
ps.store_name AS 门店名称,
po.contacts AS 联系人,
po.mobile_phone AS 手机,
po.detailed_address AS 地址,
# IFNULL(pag.activity_id, '无') AS 活动ID,
IFNULL(pg.category_name, '无') AS 品类,
concat("\t", IFNULL(pg.goods_code, '无')) AS 商品编号,
IFNULL(pg.arrival_date, '无') AS 到货日期,
IFNULL(pg.in_date, '无') AS 入库日期,
IFNULL(pg.supplier, '无') AS 供应商,
IFNULL(pg.buyer, '无') AS 采购人,
IFNULL(pg.collection_premium, '无') AS 手续费,
IFNULL(pg.suit_people, '无') AS 适应人群,
IFNULL(po.remark, '无') AS 备注,
IFNULL(pb.`name`, '未知') AS 品牌,
pg.goods_name 商品,
pg.style_no AS 款号,
pg.goods_color as 颜色,
pod.specs_name as 尺码,
concat("\t", IFNULL(pod.bar_code, '未知')) AS 条码,
pod.goods_num as 数量,
pg.goods_price as 商品价格,
IFNULL(pg.member_price, '无') AS 会员价,
pod.goods_price as 购买价格,
IFNULL(pg.buy_price, '无') as 采购价格,
IFNULL(pg.goods_pic, '无') as 图片,
po.create_time as 订单时间,
concat("\t", po.id) as 订单号,
concat("\t", IFNULL(pp.id, '无')) AS 系统流水号,
CASE
WHEN po.trade_type = 'JSAPI' THEN '小程序'
WHEN po.trade_type = 'APP' THEN 'APP'
ELSE '未知' END AS 下单方式,
CASE
WHEN po.pay_type = 1 THEN '电子支付'
WHEN po.pay_type = 2 THEN '现金支付'
WHEN po.pay_type = 3 THEN '现金支付(无码商品)'
ELSE '未知' END 支付类型,
CASE
WHEN po.order_status = 'C' THEN '已完成'
WHEN po.order_status = 'S' THEN '支付成功'
WHEN po.order_status = 'RS' THEN '已退款'
WHEN po.order_status = 'AU' THEN '退款审核中'
ELSE '未知' END AS 订单状态,
CASE
WHEN po.logistic_status = 'LU' THEN '已发货'
WHEN po.logistic_status = 'LW' THEN '待发货'
WHEN po.logistic_status = 'LS' THEN '已收货'
WHEN po.logistic_status = 'LP' THEN '部分发货'
ELSE '未知' END 发货状态,
CASE
WHEN po.is_offline = 1 THEN '线下'
WHEN po.is_offline = 0 OR po.is_offline IS NULL THEN '线上'
ELSE '未知' END AS `线上/线下`,
CASE WHEN po.coupon_price IS NULL THEN 0 ELSE po.coupon_price END 优惠金额,
CASE
WHEN pp.total_fee IS NOT NULL THEN pp.total_fee
ELSE po.price - IFNULL(po.coupon_price, 0) END 实付金额,
'扫码购订单' AS 订单类型,
CASE
WHEN po.pay_channel = 'wxpay' THEN '微信'
WHEN po.pay_channel = 'alipay' THEN '支付宝'
WHEN po.pay_channel = 'balancepay' THEN '余额'
ELSE '微信' END AS 支付渠道,
pocr.nick_name AS 收银员,
po.store_type as 门店类型,
po.member_level as 会员登记,
pmo.marketing_id as 关联营销ID,
concat("\t", pp.transaction_id) 第三方流水号,
case pod.goods_type when 0 then '普通商品' when 1 then '特卖商品' end 商品类型,
pod.remark 订单明细备注
FROM p_order po
LEFT JOIN p_order_details pod ON po.id = pod.order_id
LEFT JOIN p_goods pg ON pg.goods_id = pod.goods_id
LEFT JOIN p_brand pb ON pb.id = pg.brand_id
LEFT JOIN p_pay pp ON pp.order_id = po.id
LEFT JOIN p_store ps ON ps.store_no = po.store_no
# LEFT JOIN p_activity_goods pag ON pag.goods_id = pg.goods_id
LEFT JOIN p_order_confirm_record pocr ON pocr.order_id = po.id
LEFT JOIN p_marketing_order pmo on (pmo.order_id = pod.order_id AND pmo.bar_code = pod.bar_code)
WHERE 1 = 1
AND po.order_type IS NULL
AND (po.order_status = 'C' or order_status = 'RS' or order_status = 'S' or order_status = 'AU')
AND po.is_offline = 1
AND po.pay_type = 1
AND po.create_time >= '2020-04-01 00:00:00'
AND '2020-04-12 23:59:59' >= po.create_time
UNION
SELECT po.store_no AS 门店编号,
ps.store_name AS 门店名称,
po.contacts AS 联系人,
po.mobile_phone AS 手机,
po.detailed_address AS 地址,
# IFNULL(pag.activity_id, '无') AS 活动ID,
IFNULL(pg.category_name, '无') AS 品类,
concat("\t", IFNULL(pg.goods_code, '无')) AS 商品编号,
IFNULL(pg.arrival_date, '无') AS 到货日期,
IFNULL(pg.in_date, '无') AS 入库日期,
IFNULL(pg.supplier, '无') AS 供应商,
IFNULL(pg.buyer, '无') AS 采购人,
IFNULL(pg.collection_premium, '无') AS 手续费,
IFNULL(pg.suit_people, '无') AS 适应人群,
IFNULL(po.remark, '无') AS 备注,
IFNULL(pb.`name`, '未知') AS 品牌,
pg.goods_name 商品,
pg.style_no AS 款号,
pg.goods_color 颜色,
pod.specs_name 尺码,
concat("\t", IFNULL(pod.bar_code, '未知')) AS 条码,
pod.goods_num 数量,
pg.goods_price 商品价格,
IFNULL(pg.member_price, '无') AS 会员价,
pod.goods_price 购买价格,
IFNULL(pg.buy_price, '无') 采购价格,
IFNULL(pg.goods_pic, '无') 图片,
po.create_time 订单时间,
concat("\t", po.id) 订单号,
concat("\t", IFNULL(pp.id, '无')) AS 系统流水号,
CASE
WHEN po.trade_type = 'JSAPI' THEN '小程序'
WHEN po.trade_type = 'APP' THEN 'APP'
ELSE '未知' END AS 下单方式,
CASE
WHEN po.pay_type = 1 THEN '电子支付'
WHEN po.pay_type = 2 THEN '现金支付'
WHEN po.pay_type = 3 THEN '现金支付(无码商品)'
ELSE '未知' END 支付类型,
CASE
WHEN po.order_status = 'C' THEN '已完成'
WHEN po.order_status = 'S' THEN '支付成功'
WHEN po.order_status = 'RS' THEN '已退款'
WHEN po.order_status = 'AU' THEN '退款审核中'
ELSE '未知' END AS 订单状态,
CASE
WHEN po.logistic_status = 'LU' THEN '已发货'
WHEN po.logistic_status = 'LW' THEN '待发货'
WHEN po.logistic_status = 'LS' THEN '已收货'
WHEN po.logistic_status = 'LP' THEN '部分发货'
ELSE '未知' END 发货状态,
CASE
WHEN po.is_offline = 1 THEN '线下'
WHEN po.is_offline = 0 OR po.is_offline IS NULL THEN '线上'
ELSE '未知' END AS 线上线下,
IF(po.coupon_price IS NULL, 0, po.coupon_price) 优惠金额,
IF(pp.total_fee IS NOT NULL, pp.total_fee, po.price - IFNULL(po.coupon_price, 0)) 实付金额,
'现金订单(有码)' AS 订单类型,
CASE
WHEN po.pay_channel = 'wxpay' THEN '微信'
WHEN po.pay_channel = 'alipay' THEN '支付宝'
WHEN po.pay_channel = 'balancepay' THEN '余额'
ELSE '现金' END AS 支付渠道,
pocr.nick_name AS 收银员,
po.store_type as 门店类型,
po.member_level as 会员等级,
pmo.marketing_id as 关联营销ID,
concat("\t", pp.transaction_id) 第三方流水号,
case pod.goods_type when 0 then '普通商品' when 1 then '特卖商品' end 商品类型,
pod.remark 订单明细备注
FROM p_order po
LEFT JOIN p_order_details pod ON po.id = pod.order_id
LEFT JOIN p_goods pg ON pg.goods_id = pod.goods_id
LEFT JOIN p_brand pb ON pb.id = pg.brand_id
LEFT JOIN p_pay pp ON pp.order_id = po.id
LEFT JOIN p_store ps ON ps.store_no = po.store_no
# LEFT JOIN p_activity_goods pag ON pag.goods_id = pg.goods_id
LEFT JOIN p_order_confirm_record pocr ON pocr.order_id = po.id
LEFT JOIN p_marketing_order pmo on (pmo.order_id = pod.order_id AND pmo.bar_code = pod.bar_code)
WHERE 1 = 1
AND po.order_type IS NULL
AND (po.order_status = 'C' or order_status = 'RS' or order_status = 'S' or order_status = 'AU')
AND po.is_offline = 1
AND po.pay_type = 2
AND po.create_time >= '2020-04-01 00:00:00'
AND '2020-04-12 23:59:59' >= po.create_time;
线上退货对账单
# 线上退货对账单
select pro.update_time '退货时间',
concat("\t", pro.id) '退货单号',
concat("\t", pro.order_id) '原订单号',
case po.trade_type when 'JSAPI' then '小程序' when 'APP' then 'APP' end '下单方式',
case po.pay_channel when 'wxpay' then '微信' when 'alipay' then '支付宝' when 'balancepay' then '余额' end '支付渠道',
po.create_time '订单时间',
po.contacts '联系人',
po.mobile_phone '手机',
pod.goods_name '商品',
pb.name '品牌',
pg.style_no '款号',
pg.goods_color '颜色',
pod.specs_name '尺码',
concat("\t", pod.bar_code) '商品条码',
pg.goods_price '商品价格',
pg.member_price '会员价',
pod.goods_price '购买价格',
pg.buy_price '采购价格',
pro.refund_price '退款金额',
case prfo.whole_or_single when 1 then '整单退款' when 2 then '单品退款' end '整单/单品'
from p_refund_order pro
left join p_order po on po.id = pro.order_id
left join p_return_order prfo on prfo.order_id = pro.order_id
left join p_order_details pod on pod.id = prfo.order_detail_id
left join p_goods pg on pg.goods_id = pod.goods_id
left join p_brand pb on pb.id = pg.brand_id
where 1 = 1
and pro.update_time between '2020-03-01 00:00:00' and '2020-03-31 23:59:59'
and pro.refund_order_status = 'S';
线下退货对账单
select ps.store_name 门店,
case po.pay_type when 1 then '电子' when 2 then '现金' end 支付类型,
po.pay_channel 支付渠道,
concat("\t", psro.order_id) 订单号,
psro.order_date 订单日期,
if(psro.goods_type = 1, '特卖商品', '普通商品') 商品类型,
concat("\t", psro.bar_code) 条码,
psro.brand_name 品牌,
psro.goods_color 颜色,
psro.specs_name 尺码,
psro.goods_qty 数量,
psro.refundable_amount 应退款金额,
psro.refund_amount 实退款金额,
psro.return_date 退款时间,
pp.id 系统流水号,
concat("\t", pp.transaction_id) 第三方流水号
from p_store_return_order psro
left join p_order po on po.id = psro.order_id
left join p_pay pp on psro.order_id = pp.order_id
left join p_store ps on ps.store_no = psro.store_no
where 1 = 1
AND psro.return_date >= '2020-04-01 00:00:00'
AND '2020-04-31 23:59:59' >= psro.return_date;
门店对账单
select pfp.gmt_payment 支付日期,
ps.store_name 门店,
pfp.payment_no 支付单号,
concat("\t", pft.transaction_no) 流水号,
pfp.payment_amount 支付金额
from p_finance_payment pfp
left join p_store ps on ps.store_no = pfp.store_no
left join p_finance_transaction pft on pft.payment_no = pfp.payment_no
where 1 = 1
and pfp.payment_status = 'S'
and pfp.gmt_payment between '2020-01-01' and '2020-01-15';
门店结算单面板数据
select pfso.settle_order_id 结算周期,
ps.store_name 门店,
pfso.gmt_start 开始时间,
pfso.gmt_end 结束时间,
pfso.profit_amount 分成金额,
pfso.expense_amount 费用金额,
pfso.allowance_amount 补贴金额,
pfso.carried_amount 结转金额,
pfso.settle_amount 结算金额,
case pfso.settle_status
when -1 then '待提交'
when 0 then '待提交'
when 1 then '待确认'
when 2 then '待结算'
when 3 then '已结算' end 结算状态
from p_finance_settle_order pfso
left join p_store ps on pfso.store_no = ps.store_no
where settle_order_id = 201949;
加盟店退货明细
select psro.store_no 门店编号,
ps.store_name 门店名称,
pb.name 品牌,
psro.goods_name 商品名称,
pg.style_no 款号,
psro.bar_code 条码,
psro.goods_color 颜色,
psro.specs_name 尺码,
psro.goods_name 商品售价,
psro.goods_qty 退回件数,
psro.return_order_id 退货单号,
case psro.status
when 'S' then '退货成功'
when 'RS' then '退货成功'
when 'F' then '退货失败'
when 'U' then '退货中' end 退货进度,
psro.order_id 原订单号,
po.create_time 订单时间,
psro.return_date 退货时间
from p_store_return_order psro
left join p_order po on po.id = psro.order_id
left join p_store ps on ps.store_no = psro.store_no
left join p_goods pg on pg.goods_id = psro.goods_id
left join p_brand pb on pb.id = pg.brand_id
where 1 = 1
and psro.store_no in (
'S00000030',
'S00000029',
'S00000028'
);
快递对账
# 快递对账
select
concat("\t", pp.transaction_id) 微信流水号,
po.activity_id 活动ID,
concat("\t", po.id) 订单ID,
pol.logistics_name 快递公司,
concat("\t", pol.logistics_no) 快递单号,
po.remark 备注,
po.contacts 联系人,
po.mobile_phone 电话,
(select pa.shortname
from p_area pa
where pa.id = pua.province) 省,
(select pa.shortname
from p_area pa
where pa.id = pua.city) 市,
(select pa.shortname
from p_area pa
where pa.id = pua.area) 区县,
po.detailed_address 详细地址,
pg.category_name 品类,
pg.suit_people 适用人群,
pod.goods_name 商品名称,
pg.goods_code 商品编码,
pg.goods_color 商品颜色,
pgs.specs_name 尺码,
pod.goods_num 数量,
po.price 订单支付,
po.create_time 下单时间
from p_order po
left join p_order_logistics pol on pol.order_id = po.id
left join p_order_details pod on pod.order_id = po.id
left join p_goods pg on pg.goods_id = pod.goods_id
left join p_goods_stock pgs on pgs.goods_id = pod.goods_id and pgs.specs_name = pod.specs_name
left join p_user_address pua on pua.id = po.user_address_id
left join p_pay pp on pp.order_id = po.id
where 1 = 1
and po.logistic_status != 'LW'
and (po.is_offline = 0 or po.is_offline is null)
and (po.order_type = 2 or po.order_type is null)
and (po.pay_type = 1 or po.pay_type is null)
and po.create_time between '2018-07-01 00:00:00' and '2018-12-31 23:59:59'
order by po.create_time desc;
爱库存订单发货状态
# 爱库存订单发货状态
select concat("\t", po.id) 系统订单ID,
concat("\t", pp.id) 系统流水ID,
concat("\t", pp.transaction_id) 第三方流水号,
concat("\t", psod.supplier_ext_order_id) 爱库存订单ID,
po.contacts 联系人,
po.mobile_phone 手机,
po.detailed_address 地址,
po.remark 备注,
pb.name 品牌,
pod.goods_name 商品,
pg.goods_color 颜色,
pod.specs_name 尺码,
pgs.bar_code 条码,
po.create_time 下单时间,
case po.order_status
when 'S'
then '支付成功'
when 'AU'
then '退款审核中'
when 'RS'
then '退款成功'
when 'C'
then '已完成' end 系统订单状态,
case
po.logistic_status
when 'LU'
then '已发货'
when 'LP'
then '部分发货'
when 'LS'
then '已收货'
when 'LW'
then '待发货' end 发货状态,
pg.category_name 品类,
concat("\t", pg.goods_code) 商品编码,
psa.name 爱库存活动名称,
psa.start_date 爱库存活动开始时间,
psa.end_date 爱库存活动结束时间,
psg.settlement_price 爱库存结算金额,
pod.goods_price 销售价,
po.coupon_price 订单优惠金额,
pod.goods_num 数量,
case psod.status
when 1
then '用户已申请取消'
when 0
then '正常购买'
when 2
then '平台同意'
else psod.status end 爱库存明细状态,
case psod.product_status
when 0
then '未捞单'
when 1
then '已捞单或处理中'
when 2
then '已发货'
when 4
then '用户取消不发货'
when 11
then '系统取消不发货'
else psod.product_status end 爱库存商品状态,
case psod.after_sale_status
when 0
then '未申请售后'
when 1
then '用户申请售后'
when 11
then '待退货'
else psod.after_sale_status end 爱库存售后状态
from p_order po
left join p_pay pp on pp.order_id = po.id
left join p_supplier_order pso on pso.order_id = po.id
left join p_order_details pod on pod.order_id = po.id
left join p_goods pg on pg.goods_id = pod.goods_id
left join p_brand pb on pb.id = pg.brand_id
left join p_goods_stock pgs on pgs.goods_id = pod.goods_id and pgs.specs_name = pod.specs_name
left join p_supplier_activity psa on psa.id = pg.live_id
left join p_supplier_goods psg on psg.product_id = pg.product_id
left join p_supplier_order_detail psod on psod.order_detail_id = pod.id
where 1 = 1
and pg.supplier_no = 'aikucun'
and (po.order_status = 'S' or po.order_status = 'C' or po.order_status = 'RS' or po.order_status = 'AU')
and po.create_time between '2019-09-01 00:00:00' and '2019-09-30 23:59:59'
order by po.create_time desc;
第三方对账
SELECT po.store_no AS storeNo,
ps.store_name AS storeName,
po.contacts AS contacts,
po.mobile_phone AS mobilePhone,
po.detailed_address AS detailedAddress,
IFNULL(pag.activity_id, '无') AS activityId,
IFNULL(pg.category_name, '无') AS categoryName,
IFNULL(pg.goods_code, '无') AS goodsCode,
IFNULL(pg.arrival_date, '无') AS arrivalDate,
IFNULL(pg.in_date, '无') AS inDate,
IFNULL(pg.supplier, '无') AS supplier,
IFNULL(pg.buyer, '无') AS buyer,
IFNULL(pg.collection_premium, '无') AS collectionPremium,
IFNULL(pg.suit_people, '无') AS suitPeople,
IFNULL(po.remark, '无') AS remark,
IFNULL(pb.`name`, '未知') AS brandName,
pg.goods_name AS goodsName,
pg.style_no AS styleNo,
pg.goods_color AS goodsColor,
pod.specs_name AS specsName,
IFNULL(pod.bar_code, '未知') AS barCode,
pod.goods_num AS goodsNum,
pg.goods_price AS goodsPrice,
IFNULL(pg.member_price, '无') AS memberPrice,
pod.goods_price AS presentPrice,
IFNULL(pg.buy_price, '无') AS buyPrice,
IFNULL(pg.goods_pic, '无') AS goodsPic,
po.create_time AS createTime,
concat("\t", po.id) AS orderId,
concat("\t", IFNULL(pp.id, '无')) AS payNo,
concat("\t", IFNULL(pp.transaction_id, '无')) AS transactionId,
CASE
WHEN po.trade_type = 'JSAPI' THEN '小程序'
WHEN po.trade_type = 'APP' THEN 'APP'
ELSE '未知' END AS tradeType,
CASE
WHEN po.pay_type = 1 THEN '电子支付'
WHEN po.pay_type = 2 THEN '现金支付'
WHEN po.pay_type = 3 THEN '现金支付(无码商品)'
ELSE '未知' END AS payType,
CASE
WHEN po.order_status = 'C' THEN '已完成'
WHEN po.order_status = 'S' THEN '支付成功'
WHEN po.order_status = 'RS' THEN '已退款'
WHEN po.order_status = 'AU' THEN '退款审核中'
ELSE '未知' END AS orderStatus,
CASE
WHEN po.logistic_status = 'LU' THEN '已发货'
WHEN po.logistic_status = 'LW' THEN '待发货'
WHEN po.logistic_status = 'LS' THEN '已收货'
WHEN po.logistic_status = 'LP' THEN '部分发货'
ELSE '未知' END AS logisticStatus,
CASE
WHEN po.is_offline = 1 THEN '线下'
WHEN po.is_offline = 0 OR po.is_offline IS NULL THEN '线上'
ELSE '未知' END AS isOffline,
CASE WHEN po.coupon_price IS NULL THEN 0 ELSE po.coupon_price END AS couponPrice,
CASE
WHEN pp.total_fee IS NOT NULL THEN pp.total_fee
ELSE po.price - IFNULL(po.coupon_price, 0) END paidAmount,
'线上普通支付订单' AS orderType,
CASE
WHEN po.pay_channel = 'wxpay' THEN '微信'
WHEN po.pay_channel = 'alipay' THEN '支付宝'
WHEN po.pay_channel = 'balancepay' THEN '余额'
ELSE '微信' END AS payChannel,
NULL AS cashier,
po.store_type as storeType,
po.member_level as memberLevel,
pmo.marketing_id as marketingId
FROM p_order po
LEFT JOIN p_order_details pod ON po.id = pod.order_id
LEFT JOIN p_goods pg ON pg.goods_id = pod.goods_id
LEFT JOIN p_brand pb ON pb.id = pg.brand_id
LEFT JOIN p_pay pp ON pp.order_id = po.id
LEFT JOIN p_store ps ON ps.store_no = po.store_no
LEFT JOIN p_activity_goods pag ON pag.goods_id = pg.goods_id
LEFT JOIN p_marketing_order pmo on (pmo.order_id = pod.order_id AND pmo.bar_code = pod.bar_code)
WHERE 1 = 1
AND po.order_type IS NULL
AND (po.order_status = 'C' or order_status = 'RS' or order_status = 'S' or order_status = 'AU')
AND (po.is_offline = 0 OR po.is_offline IS NULL)
AND po.pay_type = 1
AND po.create_time >= '2019-08-01 00:00:00'
AND '2019-08-31 23:59:59' >= po.create_time
UNION
SELECT po.store_no AS 门店号,
ps.store_name AS 门店名,
po.contacts AS 联系人,
po.mobile_phone AS 手机号,
po.detailed_address AS 地址,
IFNULL(pag.activity_id, '无') AS activityId,
IFNULL(pg.category_name, '无') AS categoryName,
IFNULL(pg.goods_code, '无') AS goodsCode,
IFNULL(pg.arrival_date, '无') AS arrivalDate,
IFNULL(pg.in_date, '无') AS inDate,
IFNULL(pg.supplier, '无') AS supplier,
IFNULL(pg.buyer, '无') AS buyer,
IFNULL(pg.collection_premium, '无') AS collectionPremium,
IFNULL(pg.suit_people, '无') AS suitPeople,
IFNULL(po.remark, '无') AS 备注,
IFNULL(pb.`name`, '未知') AS 品牌名,
pg.goods_name 商品名,
pg.style_no AS styleNo,
pg.goods_color 商品颜色,
pod.specs_name 商品尺码,
IFNULL(pod.bar_code, '未知') AS barCode,
pod.goods_num 商品数量,
pg.goods_price 商品原价,
IFNULL(pg.member_price, '无') AS 会员价,
pod.goods_price 商品现价,
IFNULL(pg.buy_price, '无') `成本价/采购价`,
IFNULL(pg.goods_pic, '无') 商品图,
po.create_time 下单时间,
concat("\t", po.id) 订单编号,
concat("\t", IFNULL(pp.id, '无')) AS 流水号,
concat("\t", IFNULL(pp.transaction_id, '无')) AS 第三方流水号,
CASE
WHEN po.trade_type = 'JSAPI' THEN '小程序'
WHEN po.trade_type = 'APP' THEN 'APP'
ELSE '未知' END AS 下单方式,
CASE
WHEN po.pay_type = 1 THEN '电子支付'
WHEN po.pay_type = 2 THEN '现金支付'
WHEN po.pay_type = 3 THEN '现金支付(无码商品)'
ELSE '未知' END 支付方式,
CASE
WHEN po.order_status = 'C' THEN '已完成'
WHEN po.order_status = 'S' THEN '支付成功'
WHEN po.order_status = 'RS' THEN '已退款'
WHEN po.order_status = 'AU' THEN '退款审核中'
ELSE '未知' END AS orderStatus,
CASE
WHEN po.logistic_status = 'LU' THEN '已发货'
WHEN po.logistic_status = 'LW' THEN '待发货'
WHEN po.logistic_status = 'LS' THEN '已收货'
WHEN po.logistic_status = 'LP' THEN '部分发货'
ELSE '未知' END 发货状态,
CASE
WHEN po.is_offline = 1 THEN '线下'
WHEN po.is_offline = 0 OR po.is_offline IS NULL THEN '线上'
ELSE '未知' END AS `线上/线下`,
CASE WHEN po.coupon_price IS NULL THEN 0 ELSE po.coupon_price END 优惠金额,
CASE
WHEN pp.total_fee IS NOT NULL THEN pp.total_fee
ELSE po.price - IFNULL(po.coupon_price, 0) END 实际支付金额,
'扫码购订单' AS 订单类型,
CASE
WHEN po.pay_channel = 'wxpay' THEN '微信'
WHEN po.pay_channel = 'alipay' THEN '支付宝'
WHEN po.pay_channel = 'balancepay' THEN '余额'
ELSE '微信' END AS 支付渠道,
pocr.nick_name AS cashier,
po.store_type as storeType,
po.member_level as memberLevel,
pmo.marketing_id as marketingId
FROM p_order po
LEFT JOIN p_order_details pod ON po.id = pod.order_id
LEFT JOIN p_goods pg ON pg.goods_id = pod.goods_id
LEFT JOIN p_brand pb ON pb.id = pg.brand_id
LEFT JOIN p_pay pp ON pp.order_id = po.id
LEFT JOIN p_store ps ON ps.store_no = po.store_no
LEFT JOIN p_activity_goods pag ON pag.goods_id = pg.goods_id
LEFT JOIN p_order_confirm_record pocr ON pocr.order_id = po.id
LEFT JOIN p_marketing_order pmo on (pmo.order_id = pod.order_id AND pmo.bar_code = pod.bar_code)
WHERE 1 = 1
AND po.order_type IS NULL
AND (po.order_status = 'C' or order_status = 'RS' or order_status = 'S' or order_status = 'AU')
AND po.is_offline = 1
AND po.pay_type = 1
AND po.create_time >= '2019-08-01 00:00:00'
AND '2019-08-31 23:59:59' >= po.create_time
UNION
SELECT po.store_no AS 门店号,
ps.store_name AS 门店名,
po.contacts AS 联系人,
po.mobile_phone AS 手机号,
po.detailed_address AS 地址,
IFNULL(pag.activity_id, '无') AS activityId,
IFNULL(pg.category_name, '无') AS categoryName,
IFNULL(pg.goods_code, '无') AS goodsCode,
IFNULL(pg.arrival_date, '无') AS arrivalDate,
IFNULL(pg.in_date, '无') AS inDate,
IFNULL(pg.supplier, '无') AS supplier,
IFNULL(pg.buyer, '无') AS buyer,
IFNULL(pg.collection_premium, '无') AS collectionPremium,
IFNULL(pg.suit_people, '无') AS suitPeople,
IFNULL(po.remark, '无') AS 备注,
IFNULL(pb.`name`, '未知') AS 品牌名,
pg.goods_name 商品名,
pg.style_no AS styleNo,
pg.goods_color 商品颜色,
pod.specs_name 商品尺码,
IFNULL(pod.bar_code, '未知') AS barCode,
pod.goods_num 商品数量,
pg.goods_price 商品原价,
IFNULL(pg.member_price, '无') AS 会员价,
pod.goods_price 商品现价,
IFNULL(pg.buy_price, '无') `成本价/采购价`,
IFNULL(pg.goods_pic, '无') 商品图,
po.create_time 下单时间,
concat("\t", po.id) 订单编号,
concat("\t", IFNULL(pp.id, '无')) AS 流水号,
concat("\t", IFNULL(pp.transaction_id, '无')) AS 第三方流水号,
CASE
WHEN po.trade_type = 'JSAPI' THEN '小程序'
WHEN po.trade_type = 'APP' THEN 'APP'
ELSE '未知' END AS 下单方式,
CASE
WHEN po.pay_type = 1 THEN '电子支付'
WHEN po.pay_type = 2 THEN '现金支付'
WHEN po.pay_type = 3 THEN '现金支付(无码商品)'
ELSE '未知' END 支付方式,
CASE
WHEN po.order_status = 'C' THEN '已完成'
WHEN po.order_status = 'S' THEN '支付成功'
WHEN po.order_status = 'RS' THEN '已退款'
WHEN po.order_status = 'AU' THEN '退款审核中'
ELSE '未知' END AS orderStatus,
CASE
WHEN po.logistic_status = 'LU' THEN '已发货'
WHEN po.logistic_status = 'LW' THEN '待发货'
WHEN po.logistic_status = 'LS' THEN '已收货'
WHEN po.logistic_status = 'LP' THEN '部分发货'
ELSE '未知' END 发货状态,
CASE
WHEN po.is_offline = 1 THEN '线下'
WHEN po.is_offline = 0 OR po.is_offline IS NULL THEN '线上'
ELSE '未知' END AS `线上/线下`,
CASE WHEN po.coupon_price IS NULL THEN 0 ELSE po.coupon_price END 优惠金额,
CASE
WHEN pp.total_fee IS NOT NULL THEN pp.total_fee
ELSE po.price - IFNULL(po.coupon_price, 0) END 实际支付金额,
'现金订单(有码)' AS 订单类型,
CASE
WHEN po.pay_channel = 'wxpay' THEN '微信'
WHEN po.pay_channel = 'alipay' THEN '支付宝'
WHEN po.pay_channel = 'balancepay' THEN '余额'
ELSE '现金' END AS 支付渠道,
pocr.nick_name AS cashier,
po.store_type as storeType,
po.member_level as memberLevel,
pmo.marketing_id as marketingId
FROM p_order po
LEFT JOIN p_order_details pod ON po.id = pod.order_id
LEFT JOIN p_goods pg ON pg.goods_id = pod.goods_id
LEFT JOIN p_brand pb ON pb.id = pg.brand_id
LEFT JOIN p_pay pp ON pp.order_id = po.id
LEFT JOIN p_store ps ON ps.store_no = po.store_no
LEFT JOIN p_activity_goods pag ON pag.goods_id = pg.goods_id
LEFT JOIN p_order_confirm_record pocr ON pocr.order_id = po.id
LEFT JOIN p_marketing_order pmo on (pmo.order_id = pod.order_id AND pmo.bar_code = pod.bar_code)
WHERE 1 = 1
AND po.order_type IS NULL
AND (po.order_status = 'C' or order_status = 'RS' or order_status = 'S' or order_status = 'AU')
AND po.is_offline = 1
AND po.pay_type = 2
AND po.create_time >= '2019-08-01 00:00:00'
AND '2019-08-31 23:59:59' >= po.create_time;
其他导出
出入库/退库/退货/销售模板
-- 加盟店出库模板
select psoo.send_store_name '出库门店',
psoo.received_store_name '入库门店',
psoo.id '出库单号',
psoo.send_time '出库时间',
psoo.sender_name '出库人',
case psoo.status
when 'S' then '已完成'
when 'W' then '待入库'
when 'AF' then '审核拒绝'
when 'AW' then '待审核' end '状态',
psod.goods_name '商品名称',
concat("\t",psod.bar_code) '商品条形码',
psod.brand_name '品牌名称',
pg.style_no '款号',
pg.goods_color '商品规格',
psod.specs_name '商品尺码',
psod.send_num '商品数量',
pg.goods_price '商品原价',
pg.member_price '会员价'
from p_stock_order_details psod
left join p_stock_out_order psoo on psod.order_id = psoo.id and psod.order_type = 1
left join p_goods pg on psod.goods_id = pg.goods_id
where 1 = 1
and psod.order_type = 1
and psoo.send_store_name = '中央仓'
and psoo.send_time between '2019-10-14 14:51:36' and '2019-10-19 14:51:36';
-- 加盟店入库模板
select psoo.send_store_name '出库门店',
psoo.received_store_name '入库门店',
psoo.id '入库单号',
psoo.received_time '入库时间',
psoo.receiver_name '入库人',
case psoo.status
when 'S' then '已完成'
when 'W' then '待入库'
when 'AF' then '审核拒绝'
when 'AW' then '待审核' end '状态',
psod.goods_name '商品名称',
concat("\t",psod.bar_code) '商品条形码',
psod.brand_name '品牌名称',
pg.style_no '款号',
pg.goods_color '商品规格',
psod.specs_name '商品尺码',
psod.received_num '商品数量',
pg.goods_price '商品原价',
pg.member_price '会员价'
from p_stock_order_details psod
left join p_stock_in_order psoo on psod.order_id = psoo.id and psod.order_type = 2
left join p_goods pg on psod.goods_id = pg.goods_id
where 1 = 1
and psod.order_type = 2
and psoo.received_store_name = '中央仓'
and psoo.received_time between '2019-10-14 14:51:36' and '2019-10-19 14:51:36';
-- 加盟店出库产生退库模板
select psoo.send_store_name '出库门店',
psoo.received_store_name '入库门店',
psoo.id '出库单号',
psbo.create_time '退库时间',
case psbo.status when 'S' then '同意' when 'W' then '待操作' when 'F' then '拒绝' end '退库状态',
psbo.remark '操作人',
psof.goods_name '商品名称',
concat("\t",psof.bar_code) '商品条形码',
psof.brand_name '品牌名称',
pg.style_no '款号',
pg.goods_color '商品规格',
psof.specs_name '商品尺码',,
case psof.lom when 'less' then '少入' when 'more' then '多出' end '多出/少入',
psof.lom_num '商品数量',
pg.goods_price '商品原价',
pg.member_price '会员价'
from p_stock_order_feedback psof
left join p_stock_back_order psbo on psof.back_order_id = psbo.id
left join p_stock_out_order psoo on psoo.id = psbo.stock_out_order_id
left join p_goods pg on psof.goods_id = pg.goods_id
where 1 = 1
and psof.lom = 'less'
and psoo.send_store_name = '中央仓'
and psbo.create_time between '2019-10-14 14:51:36' and '2019-10-19 14:51:36';
-- 加盟店退货模板
select psro.store_no 门店编号,
ps.store_name 门店名称,
pb.name 品牌,
psro.goods_name 商品名称,
pg.style_no 款号,
concat("\t", psro.bar_code) 条码,
psro.goods_color 颜色,
psro.specs_name 尺码,
psro.refundable_amount 应退款金额,
psro.refund_amount 实退款金额,
psro.goods_qty 退回件数,
psro.return_order_id 退货单号,
case psro.status
when 'S' then '退货成功'
when 'RS' then '退货成功'
when 'F' then '退货失败'
when 'U' then '退货中' end 退货进度,
psro.order_id 原订单号,
po.create_time 订单时间,
psro.return_date 退货时间,
case when psro.goods_type = 1 then '特卖商品' when psro.goods_type = 0 then '普通商品' end 商品类型
from p_store_return_order psro
left join p_order po on po.id = psro.order_id
left join p_store ps on ps.store_no = psro.store_no
left join p_goods pg on pg.goods_id = psro.goods_id
left join p_brand pb on pb.id = pg.brand_id
where 1 = 1
and psro.store_no ='S00000020';
-- 加盟店销售订单模板
SELECT
po.store_no AS 门店号,
ps.store_name AS 门店名,
po.contacts AS 联系人,
po.mobile_phone AS 手机号,
po.detailed_address AS 地址,
IFNULL(po.remark,'无') AS 备注,
IFNULL(pb.`name`,'未知') AS 品牌名,
pg.goods_name 商品名,
pg.style_no AS 款号,
pg.goods_color 商品颜色,
pod.specs_name 商品尺码,
concat("\t",IFNULL(pod.bar_code,'未知')) AS 条码,
pod.goods_num 商品数量,
pod.goods_price 商品现价,
po.create_time 下单时间,
concat("\t",po.id) 订单编号,
concat("\t",IFNULL(pp.id,'无')) AS 流水号,
CASE WHEN po.trade_type = 'JSAPI' THEN '小程序' WHEN po.trade_type = 'APP' THEN 'APP' ELSE '未知' END AS 下单方式,
CASE WHEN po.pay_type = 1 THEN '电子支付' WHEN po.pay_type = 2 THEN '现金支付' WHEN po.pay_type = 3 THEN '现金支付(无码商品)' ELSE '未知' END 支付方式,
CASE WHEN po.order_status = 'C' THEN '已完成' WHEN po.order_status = 'S' THEN '支付成功' WHEN po.order_status='RS' THEN '已退款' WHEN po.order_status='AU' THEN '退款审核中' ELSE '未知' END AS 订单状态,
CASE WHEN po.logistic_status = 'LU' THEN '已发货' WHEN po.logistic_status = 'LW' THEN '待发货' WHEN po.logistic_status = 'LS' THEN '已收货' WHEN po.logistic_status = 'LP' THEN '部分发货' ELSE '未知' END 发货状态,
CASE WHEN po.is_offline = 1 THEN '线下' WHEN po.is_offline = 0 OR po.is_offline IS NULL THEN '线上' ELSE '未知' END AS `线上/线下`,
CASE WHEN po.coupon_price IS NULL THEN 0 ELSE po.coupon_price END 优惠金额,
CASE WHEN pp.total_fee IS NOT NULL THEN pp.total_fee ELSE po.price - IFNULL(po.coupon_price,0) END 实际支付金额,
'扫码购订单' AS 订单类型,
CASE WHEN po.pay_channel = 'wxpay' THEN '微信' WHEN po.pay_channel = 'alipay' THEN '支付宝' WHEN po.pay_channel = 'balancepay' THEN '余额' ELSE '微信' END AS 支付渠道,
case when pod.goods_type = 1 then '特卖商品' when pod.goods_type = 0 then '普通商品' end 商品类型
FROM
p_order po
LEFT JOIN p_order_details pod ON po.id = pod.order_id
LEFT JOIN p_goods pg ON pg.goods_id = pod.goods_id
LEFT JOIN p_brand pb ON pb.id = pg.brand_id
LEFT JOIN p_pay pp ON pp.order_id = po.id
LEFT JOIN p_store ps ON ps.store_no = po.store_no
WHERE
1 = 1
AND (po.order_status ='C' or order_status='RS' or order_status='S' or order_status='AU')
AND po.is_offline = 1
and po.store_no = 'S00000002';
导出商品库存
# 商品库存
select pgc.store_name 门店名,
pgc.brand_name 品牌,
pgc.goods_name 商品名,
pg.arrival_date 到货日期,
pgc.goods_num 库存,
pg.goods_price 原价,
pg.member_price 会员价,
pg.buy_price 成本价,
pg.collection_premium 手续费,
pg.supplier 供应商,
concat("\t", pgc.bar_code) 条码,
pgc.goods_color 颜色,
pgc.specs_name 尺码,
concat("\t", pgc.goods_code) 商品编码,
ifnull(pg.suit_season, '无') 适用季节,
ifnull(pgc.suit_people, '无') 适用人群,
ifnull(pgc.category_name, '无') 类别,
ifnull(pg.thin_thick, '无') 薄厚款,
ifnull(pg.long_short, '无') 长短款,
ifnull(pg.accessory, '无') 配饰
from p_goods_cache pgc
left join p_goods pg on pg.goods_id = pgc.goods_id
where pgc.goods_num > 0;
其他查询
线下店有码商品入库与售出差异
# 线下店有码商品入库与售出差异
select
a.goods_name 商品名称,
a.goods_id 商品ID,
a.specs_name 规格,
a.bar_code 条码,
a.name 品牌,
a.num 售出数量,
b.num 入库数量
from
(
SELECT
pod.goods_name,
pod.goods_id,
pod.specs_name,
pb.name,
pss.bar_code,
sum(pod.goods_num) num
FROM p_order_details pod
LEFT JOIN p_order po ON po.id = pod.order_id
LEFT JOIN p_store_stock pss ON pss.goods_id = pod.goods_id
AND pss.specs_name = pod.specs_name
AND pss.store_no = po.store_no
left join p_goods pg on pg.goods_id = pod.goods_id
left join p_brand pb on pb.id = pg.brand_id
WHERE 1 = 1
AND po.store_no = 'S00000007'
AND (po.order_status = 'S'
OR po.order_status = 'C'
OR po.order_status = 'AU')
AND po.pay_type = 2
GROUP BY pod.goods_name,
pod.goods_id,
pod.specs_name,
pss.bar_code) a,
(
SELECT
psod.goods_name,
psod.goods_id,
psod.specs_name,
psod.bar_code,
psod.brand_name,
sum(psod.received_num) num
FROM p_stock_order_details psod
LEFT JOIN p_stock_in_order psio ON psio.id = psod.order_id
WHERE 1 = 1
AND psio.received_store_no = 'S00000007'
AND (psod.order_type = 2 or psod.order_type is null)
GROUP BY psod.goods_name,
psod.goods_id,
psod.specs_name,
psod.bar_code,
psod.brand_name
) b
where 1 = 1
and b.goods_id = a.goods_id
and b.specs_name = a.specs_name
and b.num < a.num;
对比入库数和现有库存数
# 对比入库数和现有库存数
select
pss.bar_code,
pss.goods_id,
pss.specs_name,
pss.goods_qty,
psod.goods_id,
psod.specs_name,
psod.bar_code,
psod.received_num
from p_store_stock pss
left join p_stock_in_order psio on psio.received_store_no = pss.store_no
left join p_stock_order_details psod
on psod.bar_code = pss.bar_code and psod.order_id = psio.id and psod.order_type = 2
where 1 = 1
and pss.store_no = 'S00000007';
库存查重
# 库存查重
select *
from p_store_stock
where 1 = 1
and goods_qty > 0
group by bar_code
having count(bar_code) > 1;
售出条码查重
# 售出条码查重
select *
from p_order_details
where bar_code in
(select bar_code
from p_order_details pod
left join p_order po on pod.order_id = po.id
left join p_goods pg on pg.goods_id = pod.goods_id
left join p_brand pb on pb.id = pg.brand_id
where 1 = 1
and bar_code != ''
# and pb.id = 936
and (po.order_status = 'S' or po.order_status = 'C')
group by bar_code
having count(bar_code) > 1);
出库条码查重
# 出库条码查重
select *
from p_store_stock
where bar_code in
(select psod.bar_code
from p_stock_order_details psod
left join p_stock_out_order psoo on psoo.id = psod.order_id
left join p_goods pg on pg.goods_id = psod.goods_id
left join p_brand pb on pb.id = pg.brand_id
left join p_store ps on ps.store_no = psoo.send_store_no
where 1 = 1
and psod.order_type = 1
and psoo.send_store_no = 'S00000001'
# and pb.id = 936
group by bar_code
having count(bar_code) > 1);
查询无详情出库单
# 查询无详情出库单
select *
from p_stock_out_order psoo
left join (
select count(1) qty, order_id
from p_stock_order_details
where order_type = 1
group by order_id
) a on a.order_id = psoo.id
where 1 = 1
and qty is null
and status != 'Q'
and status != 'AF';
补录无详情出库单
# 补录无详情出库单
insert into p_stock_order_details (order_type, order_id, goods_name, send_num, goods_id, specs_name,
bar_code, goods_color, brand_id, brand_name, scan_times)
select 1,
2733,
goods_name,
1,
goods_id,
specs_name,
bar_code,
goods_color,
brand_id,
name,
1
from p_stock_out_temp_bak psotb
left join p_brand pb on pb.id = psotb.brand_id
where stock_out_order_id = 2733;
查询已出库条码存在于最后一次出库门店的库存大于0的记录
# 查询已出库条码存在于最后一次出库门店的库存大于0的记录
select max(psod.id) id,
psod.bar_code,
psod.goods_name,
psod.brand_name,
psoo.id,
psoo.send_store_no,
psoo.send_store_name,
psoo.create_time,
psoo.status
from p_stock_order_details psod
left join p_stock_out_order psoo on psoo.id = psod.order_id and psod.order_type = 1
left join p_store_stock pss on pss.store_no = psoo.send_store_no
where 1 = 1
and (psoo.status = 'W' or psoo.status = 'AW' or psoo.status = 'S')
and pss.goods_qty > 0
group by psod.bar_code, psod.id
order by id desc;
查询中央仓出过库且现库存为1且未存在入库记录的条码（概念同上一条）
# 查询中央仓出过库且现库存为1且未存在入库记录的条码
select *
from p_store_stock pss
left join p_goods pg on pg.goods_id = pss.goods_id
left join p_stock_order_details psod on psod.bar_code = pss.bar_code and psod.order_type = 1
left join p_stock_out_order psoo on psoo.id = psod.order_id
where 1 = 1
and pss.goods_qty = 1
and pss.store_no = 'S00000001'
and pg.supplier_no is null
and pss.bar_code is not null
and psoo.send_store_no = 'S00000001'
and (psoo.status = 'W' or psoo.status = 'AW' or psoo.status = 'S')
and psoo.create_time between '2019-03-01 00:00:00' and '2019-06-30 00:00:00'
and psod.order_type = 1
and psod.bar_code not in (
select psod2.bar_code
from p_stock_order_details psod2
left join p_stock_in_order psio on psio.id = psod2.order_id and psod2.order_type = 2
where 1 = 1
and pss.bar_code = psod2.bar_code
);
查询中央仓出过库且现库存为1且未存在入库记录的条码v2（概念同上一条）
# 查询中央仓出过库且现库存为1且未存在入库记录的条码v2（概念同上一条）
select *
from p_store_stock pss
where 1 = 1
and goods_qty > 0
and store_no = 'S00000001'
and pss.bar_code in (
select psod1.bar_code
from p_stock_order_details psod1
left join p_stock_out_order psoo1 on psoo1.id = psod1.order_id and psod1.order_type = 1
where 1 = 1
and (psoo1.status = 'W' or psoo1.status = 'AW' or psoo1.status = 'S')
and psoo1.send_store_no = 'S00000001'
and psod1.order_type = 1
)
and pss.bar_code not in (
select psod2.bar_code
from p_stock_order_details psod2
left join p_stock_in_order psoo2 on psoo2.id = psod2.order_id and psod2.order_type = 2
where 1 = 1
and psod2.order_type = 2
)
and pss.bar_code not in (
select p_stock_order_feedback.bar_code
from p_stock_order_feedback
);
添加商品到出库车
# 添加商品到出库车*
insert into p_stock_out_temp (user_name, send_store_no, received_store_no, goods_id, specs_name, goods_name, goods_num,
add_time, bar_code, goods_color, brand_id, is_codeless, scan_times)
select ps.store_name,
pss.store_no,
'S00000010',
pss.goods_id,
pss.specs_name,
pg.goods_name,
1,
now(),
pss.bar_code,
pg.goods_color,
pg.brand_id,
0,
1
from p_store_stock pss
left join p_goods pg on pg.goods_id = pss.goods_id
left join p_store ps on ps.store_no = pss.store_no
where 1 = 1
and pss.store_no = 'S00000001'
and pss.bar_code in (
'BNN93207161059',
'BNN93207171110',
'BNN93207171370'
);
导出9.9会费统计
# 导出9.9会费统计
select
ps.store_name,
po.wx_uid,
wu.nick_name,
wu.verify_phone,
pod.goods_name,
pod.original_price,
pod.goods_price,
po.create_time,
po.id,
pp.id,
po.trade_type,
po.pay_type,
po.logistic_status,
po.is_offline,
po.coupon_price,
po.paid_amount,
po.order_type,
po.pay_channel,
pocr.nick_name
from p_order_details pod
left join p_order po on po.id=pod.order_id
left join p_store ps on ps.store_no=po.store_no
left join wx_user wu on wu.wx_uid=po.wx_uid
left join p_pay pp on pp.order_id=po.id
left join p_order_confirm_record pocr on pocr.order_id=po.id
where 1=1
and( po.order_status='C' or po.order_status='S')
and pod.goods_id=10
order by create_time desc
导出199优惠券来源订单
# 查询所有获得199优惠券的用户
select
*
from
p_coupon_user pcu
left join p_coupon_brand pcb on pcb.coupon_id = pcu.coupon_id
where
1 = 1
and pcb.brand_id = 1585
group by
wx_uid;
# 199来源订单
select po.id, ps.store_name, puu.verify_phone, po.wx_uid, po.price, po.create_time
from p_order po
inner join (
select wx_uid,
pcu.create_time
from p_coupon_user pcu
left join p_coupon_brand pcb on pcb.coupon_id = pcu.coupon_id
where 1 = 1
and pcb.brand_id = 1585
group by wx_uid
) a on a.wx_uid = po.wx_uid
left join p_store ps on ps.store_no = po.store_no
left join p_union_user puu on puu.wx_uid = po.wx_uid
where 1 = 1
and (po.order_status = 'C' or po.order_status = 'S')
and po.create_time between '2019-08-02 00:00:00' and '2019-08-22 23:59:59'
and po.price >= 199
and po.price < 399
group by po.wx_uid
order by po.create_time;
# 399来源订单
select po.id, ps.store_name, puu.verify_phone, po.wx_uid, po.price, po.create_time
from p_order po
inner join (
select wx_uid,
pcu.create_time
from p_coupon_user pcu
left join p_coupon_brand pcb on pcb.coupon_id = pcu.coupon_id
where 1 = 1
and pcb.brand_id = 1586
group by wx_uid
) a on a.wx_uid = po.wx_uid
left join p_store ps on ps.store_no = po.store_no
left join p_union_user puu on puu.wx_uid = po.wx_uid
where 1 = 1
and (po.order_status = 'C' or po.order_status = 'S')
and po.create_time between '2019-08-02 00:00:00' and '2019-08-22 23:59:59'
and po.price >= 399
order by po.create_time;
重置入库单
# 已扫商品添加到入库车
insert into p_stock_in_temp (stock_out_order_id, send_store_no, received_store_no, goods_id, specs_name, goods_name,
goods_num, user_name, add_time, bar_code, goods_color, brand_id, is_confirm, is_codeless,
scan_times)
select '3104',
psoo.send_store_no,
psoo.received_store_no,
goods_id,
specs_name,
goods_name,
received_num,
'13722975711',
now(),
bar_code,
goods_color,
brand_id,
1,
0,
1
from p_stock_order_details psod
left join p_stock_out_order psoo on psoo.id = psod.order_id
where order_type = 2
and order_id = 3248;
# 清除已入库商品数量
update p_store_stock pss
left join p_stock_order_details psod on psod.bar_code = pss.bar_code
set goods_qty = 0
where store_no = (select received_store_no from p_stock_in_order where id = 3248)
and psod.order_type = 2
and psod.order_id = 3248;
# 删除已入库详情
delete
from p_stock_order_details
where order_type = 2
and order_id = 3248;
# 删除入库单
delete
from p_stock_in_order
where id = 3248;
# 删除退库详情
delete
from p_stock_order_feedback
where back_order_id = (select id from p_stock_back_order where stock_out_order_id = 3104);
# 删除退库单
delete
from p_stock_back_order
where stock_out_order_id = 3104;
# 重置出库单
update p_stock_out_order
set status = 'W',
stock_in_order_id=null,
total_received_num=null,
receiver_name=null,
received_time=null
where id = 3104;
退供应商做订单
# 退供应商做订单
insert into p_order_details (order_id, goods_name, goods_pic, goods_num, goods_price, goods_id, specs_name,
original_price, discount_price,
member_price, goods_color, bar_code)
select '348750905551884307',
pg.goods_name,
pg.goods_pic,
1,
pg.buy_price,
pg.goods_id,
pss.specs_name,
pg.goods_price,
pg.discount_price,
pg.member_price,
pg.goods_color,
pss.bar_code
from p_store_stock pss
inner join p_goods pg on pss.goods_id = pg.goods_id
where 1 = 1
and pss.store_no = 'S00000013'
and pg.brand_id = 1548;
优惠券2005购满赠订单明细
# 优惠券2005购满赠订单明细
select ps.store_name, wu.nick_name, wu.verify_phone, pod.*
from p_order_details pod
left join p_order po on po.id = pod.order_id
left join p_coupon_user pcu on pcu.wx_uid = po.wx_uid
left join p_store ps on ps.store_no = po.store_no
left join wx_user wu on wu.wx_uid = po.wx_uid
where 1 = 1
and pcu.coupon_id = 2005
and year(po.create_time) = year(pcu.create_time)
and month(po.create_time) = month(pcu.create_time)
and day(po.create_time) = day(pcu.create_time)
# and po.update_time=pcu.create_time
and po.price >= 300;
