跳至正文

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'

0-30天免仓租费用

SELECT customer_code,in_order_id,sku,barcode,batch_no,instock_time,created_time,instock_days,qty_total,volume_m,qty_total*0.6*volume_m as '仓租' from f_warehouse_rent_detail WHERE customer_code='bjealing' and created_time>='2021-12-01 00:00:00' and created_time<='2021-12-31 23:59:59'and instock_days<=30 ORDER BY in_order_id,sku,batch_no,created_time,instock_days

马哥熙宇 面单汇总查询(批量申请)

SELECT o.warehouse_code,o.customer_code,o.order_no1,o.status_name,o.created_time,
b.sku,i.gross_weight,b.product_quantity,o.shipway_code,b.waybill_quantity,(i.gross_weight*b.product_quantity/b.waybill_quantity) as '运单重量',
REPLACE (
        JSON_EXTRACT (b.apply_result, '$[*].trackingNo'),
        '"',
        ''
    ) AS '快递单号',
b.created_time
from out_order_apply_waybill_batches b 
LEFT JOIN out_order o on o.id=b.out_order_id 
LEFT JOIN out_order_item i on i.out_order_id=o.id and i.sku=b.sku
LEFT JOIN b_product p on p.sku=b.sku and p.customer_code=o.customer_code
left join f_out_order_fee f on f.out_order_number = o.order_no1
WHERE b.created_time>'2022-01-01 00:00:00' and o.shipway_code='DHL'
ORDER BY o.warehouse_code,o.customer_code,o.order_no1

Excel 每个面单一行 VBA

Sub aa()
Dim shp As Shape
Dim iCount As Long
Dim arr As Variant

iCount = Sheet1.[A65536].End(xlUp).Row
For i = 2 To iCount
Sheet1.Range("J" & i).Select
arr = Split(Sheet1.Range("L" & i).Value, ", ")

For r = 1 To Sheet1.Range("J" & i).Value
iCount2 = Sheet2.[A65536].End(xlUp).Row
Rows(i).Copy Sheet2.Rows(iCount2 + 1)
Sheet2.Range("L" & (iCount2 + 1)).NumberFormatLocal = "@"
Sheet2.Range("L" & (iCount2 + 1)).Value = Replace(Replace(arr(r - 1), "[", ""), "]", "")


Next

Next

End Sub

马哥熙宇 面单汇总查询(单个申请)

SELECT * from out_order_apply_tracking_no n 

LEFT JOIN out_order o on o.id=n.out_order_id 

WHERE n.out_order_id NOT IN (SELECT out_order_id from out_order_apply_waybill_batches)

AND n.created_time BETWEEN '2021-04-01 00:00:00' AND '2022-01-01 00:00:00'

包裹转运入库费用，加上sku

SELECT f.order_no1,f.receipt_time,expression, group_concat(i.sku Separator ';') 
from f_in_order_fee f LEFT JOIN in_order_item i on f.in_order_id=i.in_order_id
WHERE 
f.customer_code='GTI' 
and in_order_business_type_name='包裹转运' 
and receipt_time BETWEEN '2022-03-01 00:00:00' and '2022-04-01 00:00:00'  
GROUP BY f.order_no1,expression

excel计算月工时


'计算小时数

Sub subline(i)

Range("A" & i).Select

Dim datum, kommen, gehen

datum = Range("A" & i).Text
datum = Replace(datum, ".", "/")
kommen = datum & " " & Range("B" & i).Text & ":00"

gehen = datum & " " & Range("C" & i).Text & ":00"

'If IsDate(datum) And IsDate(kommen) And IsDate(gehen) And IsDate(Range("B" & i).Text) And IsDate(Range("C" & i).Text) Then
If IsDate(datum) And IsDate(Range("B" & i).Text) And IsDate(Range("C" & i).Text) Then

Else

GoTo LastLine

End If

Dim k, g, kh, gh As Date

k = CDate(kommen)

g = CDate(gehen)

kh = Range("B" & i).Text

gh = Range("C" & i).Text

If IsDate(k) And IsDate(g) And IsDate(kh) And IsDate(gh) Then

Else

GoTo LastLine

End If



If k > g Then

g = DateAdd("D", 1, g)

End If

Dim hourDiff

hourDiff = DateDiff("n", k, g) / 60

Range("H" & i).Value = hourDiff

'计算休息时间

Dim restTime

restTime = 0

If kh > gh Then

restTime = 1

GoTo FullRest

End If

'到的时间在10：30之前

If kh < CDate("10:30:00") Then

    If gh >= CDate("11:00:00") And gh < CDate("13:00:00") Then
    
    restTime = 0.25
    
    ElseIf gh >= CDate("13:00:00") And gh < CDate("16:00:00") Then
    
    restTime = 0.75
    
    ElseIf gh >= CDate("16:00:00") Then
    
    restTime = 1
    
    End If
        
ElseIf kh >= CDate("10:30:00") And kh < CDate("13:00:00") Then

    If gh >= CDate("13:00:00") And gh < CDate("16:00:00") Then
    
    restTime = 0.5
    
    ElseIf gh >= CDate("16:00:00") Then
    
    restTime = 0.75
    
    End If
    
ElseIf kh >= CDate("13:00:00") And kh < CDate("15:00:00") Then

    If gh >= CDate("16:00:00") Then
    
    restTime = 0.25
    
    End If

End If

FullRest:

Range("I" & i).Value = restTime

'计算实际工作时长

Range("E" & i).Value = hourDiff - restTime

LastLine:

End Sub

Sub main()

Range("H" & 6).Value = "总小时数"

Range("I" & 6).Value = "休息时长"

For i = 11 To 42

subline (i)

Next

End Sub

Alien DPD Excel文件处理 （每条快递单号一行）

Sub aa()
Dim shp As Shape
Dim iCount As Long
Dim arr As Variant

iCount = Sheet1.[A1048576].End(xlUp).Row
For i = 2 To iCount

    arr = Split(Sheet1.Range("E" & i).Value, ",")
    arrlength = UBound(arr) - LBound(arr)
   
    If arrlength = -1 Then
        arrlength = 1
        arr = Array("")
    End If
       
    For r = 1 To arrlength
        iCount2 = Sheet2.[A1048576].End(xlUp).Row
        Sheet1.Rows(i).Copy Sheet2.Rows(iCount2 + 1)
        Sheet2.Range("E" & (iCount2 + 1)).NumberFormatLocal = "@"
        Sheet2.Range("E" & (iCount2 + 1)).Value = Replace(Replace(arr(r - 1), "[", ""), "]", "")
    Next

Next

End Sub