sql 语句记录

Excel转Json :https://www.bejson.com/json/col2json/

PDF分割工具:https://deftpdf.com/zh/split-pdf-down-the-middle

一个时间段内的,已发货订单数据,包含上架时间

SELECT 
o.customer_code as '货主代码',
o.warehouse_code as '仓库代码',
o.type_name as '出库类型',
o.business_type_name as '出库业务类型',
o.order_no1 as '出库单号',
o.status_name as '出库状态',
o.skus as '商品sku',
a.barcode as '商品条码',
a.product_name as '商品名称',
o.shipped_time as '发货时间',
s.confirm_time as '上架确认时间' 
from out_order o 
LEFT JOIN out_inventory_allocation a ON a.out_order_id=o.id 
LEFT JOIN in_shelf s ON s.in_order_id=a.in_order_id 
WHERE (o.customer_code='ouhua-lowe' or o.customer_code='GTI')
and (o.shipped_time BETWEEN '2021-08-01 00:00:00' and '2021-09-01 00:00:00') 
and a.batch_no=s.batch_no group by o.order_no1 ORDER BY o.customer_code

一个时间段内的,尚未发货的在库信息列表,包含上架时间

SELECT i.customer_code,i.status_name,i.in_order_no1,i.sku,
i.barcode,i.product_name,i.batch_no,s.created_time as '上架时间',
i.quality_name,i.qty_total as '总数',i.qty_avail as '可用数量',
i.qty_lock1 as '入库锁定',i.qty_lock4 as '出库锁定',i.location_code,i.remark
from i_inventory_snapshot i LEFT JOIN in_shelf s ON s.customer_id=i.customer_id
WHERE
i.customer_code='GTI'
and i.snapshot_date='2021-08-01'
and i.in_order_id=s.in_order_id
and i.sku=s.sku
and i.batch_no = s.batch_no
and i.qty_total>0
and i.qty_lock1=0

数据库导出备份

mysqldump -uroot -p hallonomie> /hallonomie_0725.sql

postman下架 status_combination_code =20 已分配, location_code 库位

SELECT a.location_code,	a.id as "allocationId",a.barcode,a.out_order_id,a.out_order_no,a.quantity
from out_order o 
LEFT JOIN out_inventory_allocation a on o.id=a.out_order_id 
WHERE o.status_combination_code='20' and a.location_code='K79'