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.created_time as '订单创建时间',
o.status_name as '出库状态',
o.skus as '商品sku',
a.barcode as '商品条码',
a.quantity 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'

一件代发 波次装箱复核 index对应出库单号码

SELECT 
o.order_no1,
e.barcode,
i.inner_index 
from out_wave_item i 
LEFT JOIN out_wave w on i.out_wave_id = w.id  
LEFT JOIN out_order o on o.id = i.out_order_id 
LEFT JOIN out_order_item e on e.out_order_id = i.out_order_id 
WHERE 
w.wave_no='1636447245901'

上架记录(包含业务类型)

SELECT 
s.warehouse_code as '仓库代码',
s.customer_code as '货主代码',
s.order_no1 as '入库单号',
o.in_order_business_type_name as '业务类型',
s.created_time as '上架时间',
s.is_confirm as '确认库存',
s.sku as '商品SKU',
s.barcode as '商品条码',
s.quantity as '上架数量',
s.location_code as '上架库位',
s.box_no as '箱号',
s.box_qty as '箱数',
s.product_name as '商品名称',
s.quality_name as '商品品质',
s.batch_no as '批次号码',
s.created_by_user_code as '上架人',
s.confirm_by_user_code as '确认人',
s.confirm_time as '确认时间',
s.update_by_user_code as '更新人',
s.update_time as '更新时间'
from in_shelf s 
LEFT JOIN in_order o on o.id = s.in_order_id 
WHERE 
s.created_time > '2021-12-13 00:00:00' and 
s.created_time < '2021-12-15 00:00:00' and
s.warehouse_code = 'de05'
ORDER BY s.created_time

退货签收sku及再入库库位

SELECT r.customer_code,r.out_order_no,i.sku,i.quantity,r.created_time,r.in_order_no,o.status_name,y.location_code
from out_return_order r 
LEFT JOIN out_order_item i on i.out_order_id=r.out_order_id 
LEFT JOIN in_order o on r.in_order_id=o.id
LEFT JOIN i_inventory y on y.in_order_id=r.in_order_id and i.sku=y.sku
WHERE r.customer_code = 'leqi'

word批量处理图片大小

Sub FormatPics()

Dim Shap As InlineShape

For Each Shap In ActiveDocument.InlineShapes

If Shap.Type = wdInlineShapePicture Then

Shap.LockAspectRatio = msoFalse '不锁定纵横比

Shap.Width = CentimetersToPoints(25) '宽10CM

Shap.Height = CentimetersToPoints(15) '高7CM

End If

Next

End Sub

时间段内出库物品体积

SELECT 
o.customer_code as '货主',
o.order_no1 as '出库单号',
o.status_name as '出库单状态',
o.shipped_time as '发货时间',
i.sku as 'sku',
b.barcode as '条码',
i.quantity as '数量',
p.length as '长cm',
p.width as '宽cm',
p.height as '高cm',
p.length*p.width*p.height/1000000 as '单件体积m³',
i.quantity*p.length*p.width*p.height/1000000 as '总体积m³',
p.product_name as '商品名称'
from out_order o
LEFT JOIN out_order_item i on i.out_order_id = o.id
LEFT JOIN b_product p on i.sku=p.sku and i.customer_id=p.customer_id
LEFT JOIN b_product_barcode b on b.sku = p.sku and b.customer_id = p.customer_id
WHERE o.customer_code='bjealing' 
and o.status_code>=110
and o.shipped_time>'2021-08-01 00:00:00' and o.shipped_time<'2022-01-01 00:00:00'
ORDER BY shipped_time

月初在库商品体积

SELECT s.customer_code as '货主代码',
s.snapshot_date as '快照日期',
s.in_order_no1 as '入库单号',
s.batch_no as '批次号',
s.sku as 'sku',
s.barcode as '商品条码',
s.qty_total as '商品总数',
p.length as '长cm',
p.width as '宽cm',
p.height as '高cm',
p.length*p.width*p.height/1000000 as '单件体积m³',
s.qty_total*p.length*p.width*p.height/1000000 as '总体积m³',
p.product_name as '产品名称',
s.location_code as '库位'
FROM i_inventory_snapshot s 
LEFT JOIN b_product p on p.sku=s.sku and p.customer_id=s.customer_id
WHERE (snapshot_date  ='2021-08-01' or snapshot_date  ='2021-09-01' or snapshot_date  ='2021-10-01' or snapshot_date  ='2021-11-01' or snapshot_date  ='2021-12-01') and qty_total>0 and s.customer_code='bjealing' ORDER BY snapshot_date

当前库存商品体积

SELECT s.customer_code as '货主代码',
s.in_order_no1 as '入库单号',
s.batch_no as '批次号',
s.sku as 'sku',
s.barcode as '商品条码',
s.qty_total as '商品总数',
p.length as '长cm',
p.width as '宽cm',
p.height as '高cm',
p.length*p.width*p.height/1000000 as '单件体积m³',
s.qty_total*p.length*p.width*p.height/1000000 as '总体积m³',
p.product_name as '产品名称',
s.location_code as '库位'
FROM i_inventory s 
LEFT JOIN b_product p on p.sku=s.sku and p.customer_id=s.customer_id
WHERE qty_total>0 and s.customer_code='bjealing'

入库体积

SELECT o.order_no1,r.sku,r.quantity,p.length*p.width*p.height/1000000,r.quantity*p.length*p.width*p.height/1000000,r.created_time,p.product_name from in_receipt r
LEFT JOIN b_product p on r.sku=p.sku and r.customer_id=p.customer_id
LEFT JOIN in_order o on o.id=r.in_order_id
WHERE r.created_time>'2021-11-01 00:00:00' and r.created_time<'2021-12-01 00:00:00' and r.customer_code='bjealing'