1
SELECT
2
o.customer_code as '货主代码',
3
o.warehouse_code as '仓库代码',
4
o.type_name as '出库类型',
5
o.business_type_name as '出库业务类型',
6
o.order_no1 as '出库单号',
7
o.created_time as '订单创建时间',
8
o.status_name as '出库状态',
9
o.skus as '商品sku',
10
a.barcode as '商品条码',
11
a.quantity as '数量',
12
a.product_name as '商品名称',
13
o.shipped_time as '发货时间',
14
s.confirm_time as '上架确认时间'
15
from out_order o
16
LEFT JOIN out_inventory_allocation a ON a.out_order_id=o.id
17
LEFT JOIN in_shelf s ON s.in_order_id=a.in_order_id
18
WHERE
19
(o.customer_code = 'ouhua-lowe' or o.customer_code = 'GTI') and
20
(o.shipped_time BETWEEN '2021-08-01 00:00:00' and '2021-09-01 00:00:00') and
21
a.batch_no = s.batch_no
22
GROUP BY o.order_no1
23
ORDER BY o.customer_code

1
SELECT
2
i.customer_code,
3
i.status_name,
4
i.in_order_no1,
5
i.sku,
6
i.barcode,
7
i.product_name,
8
i.batch_no,
9
s.created_time as '上架时间',
10
i.quality_name,
11
i.qty_total as '总数',
12
i.qty_avail as '可用数量',
13
i.qty_lock1 as '入库锁定',
14
i.qty_lock4 as '出库锁定',
15
i.location_code,
16
i.remark
17
from i_inventory_snapshot i
18
LEFT JOIN in_shelf s ON s.customer_id = i.customer_id
19
WHERE
20
i.customer_code = 'GTI'
21
and i.snapshot_date = '2021-08-01'
22
and i.in_order_id = s.in_order_id
23
and i.sku = s.sku
24
and i.batch_no = s.batch_no
25
and i.qty_total > 0
26
and i.qty_lock1 = 0

1
SELECT
2
a.location_code,
3
a.id as "allocationId",
4
a.barcode,
5
a.out_order_id,
6
a.out_order_no,
7
a.quantity
8
from out_order o
9
LEFT JOIN out_inventory_allocation a on o.id = a.out_order_id
10
WHERE
11
o.status_combination_code = '20' and
12
a.location_code = 'K79'

1
SELECT
2
o.order_no1,
3
e.barcode,
4
i.inner_index
5
from out_wave_item i
6
LEFT JOIN out_wave w on i.out_wave_id = w.id
7
LEFT JOIN out_order o on o.id = i.out_order_id
8
LEFT JOIN out_order_item e on e.out_order_id = i.out_order_id
9
WHERE
10
w.wave_no='1636447245901'

1
SELECT
2
s.warehouse_code as '仓库代码',
3
s.customer_code as '货主代码',
4
s.order_no1 as '入库单号',
5
o.in_order_business_type_name as '业务类型',
6
s.created_time as '上架时间',
7
s.is_confirm as '确认库存',
8
s.sku as '商品SKU',
9
s.barcode as '商品条码',
10
s.quantity as '上架数量',
11
s.location_code as '上架库位',
12
s.box_no as '箱号',
13
s.box_qty as '箱数',
14
s.product_name as '商品名称',
15
s.quality_name as '商品品质',
16
s.batch_no as '批次号码',
17
s.created_by_user_code as '上架人',
18
s.confirm_by_user_code as '确认人',
19
s.confirm_time as '确认时间',
20
s.update_by_user_code as '更新人',
21
s.update_time as '更新时间'
22
from in_shelf s
23
LEFT JOIN in_order o on o.id = s.in_order_id
24
WHERE
25
s.created_time > '2021-12-13 00:00:00' and
26
s.created_time < '2021-12-15 00:00:00' and
27
s.warehouse_code = 'de05'
28
ORDER BY s.created_time

1
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
2
from out_return_order r
3
LEFT JOIN out_order_item i on i.out_order_id=r.out_order_id
4
LEFT JOIN in_order o on r.in_order_id=o.id
5
LEFT JOIN i_inventory y on y.in_order_id=r.in_order_id and i.sku=y.sku
6
WHERE r.customer_code = 'leqi'

1
Sub FormatPics()
2

3
Dim Shap As InlineShape
4

5
For Each Shap In ActiveDocument.InlineShapes
6

7
If Shap.Type = wdInlineShapePicture Then
8

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

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

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

15
End If
16

17
Next
18

19
End Sub

1
SELECT
2
o.customer_code as '货主',
3
o.order_no1 as '出库单号',
4
o.status_name as '出库单状态',
5
o.shipped_time as '发货时间',
6
i.sku as 'sku',
7
b.barcode as '条码',
8
i.quantity as '数量',
9
p.length as '长cm',
10
p.width as '宽cm',
11
p.height as '高cm',
12
p.length*p.width*p.height/1000000 as '单件体积m³',
13
i.quantity*p.length*p.width*p.height/1000000 as '总体积m³',
14
p.product_name as '商品名称'
15
from out_order o
16
LEFT JOIN out_order_item i on i.out_order_id = o.id
17
LEFT JOIN b_product p on i.sku=p.sku and i.customer_id=p.customer_id
18
LEFT JOIN b_product_barcode b on b.sku = p.sku and b.customer_id = p.customer_id
19
WHERE o.customer_code='bjealing'
20
and o.status_code>=110
21
and o.shipped_time>'2021-08-01 00:00:00' and o.shipped_time<'2022-01-01 00:00:00'
22
ORDER BY shipped_time

1
SELECT s.customer_code as '货主代码',
2
s.snapshot_date as '快照日期',
3
s.in_order_no1 as '入库单号',
4
s.batch_no as '批次号',
5
s.sku as 'sku',
6
s.barcode as '商品条码',
7
s.qty_total as '商品总数',
8
p.length as '长cm',
9
p.width as '宽cm',
10
p.height as '高cm',
11
p.length*p.width*p.height/1000000 as '单件体积m³',
12
s.qty_total*p.length*p.width*p.height/1000000 as '总体积m³',
13
p.product_name as '产品名称',
14
s.location_code as '库位'
15
FROM i_inventory_snapshot s
16
LEFT JOIN b_product p on p.sku=s.sku and p.customer_id=s.customer_id
17
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

1
SELECT s.customer_code as '货主代码',
2
s.in_order_no1 as '入库单号',
3
s.batch_no as '批次号',
4
s.sku as 'sku',
5
s.barcode as '商品条码',
6
s.qty_total as '商品总数',
7
p.length as '长cm',
8
p.width as '宽cm',
9
p.height as '高cm',
10
p.length*p.width*p.height/1000000 as '单件体积m³',
11
s.qty_total*p.length*p.width*p.height/1000000 as '总体积m³',
12
p.product_name as '产品名称',
13
s.location_code as '库位'
14
FROM i_inventory s
15
LEFT JOIN b_product p on p.sku=s.sku and p.customer_id=s.customer_id
16
WHERE qty_total>0 and s.customer_code='bjealing'

1
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
2
LEFT JOIN b_product p on r.sku=p.sku and r.customer_id=p.customer_id
3
LEFT JOIN in_order o on o.id=r.in_order_id
4
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'

1
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

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

1
Sub aa()
2
Dim shp As Shape
3
Dim iCount As Long
4
Dim arr As Variant
5

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

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

17
Next
18

19
Next
20

21
End Sub

1
SELECT * from out_order_apply_tracking_no n
2

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

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

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

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

1
'计算小时数
2

3
Sub subline(i)
4

5
Range("A" & i).Select
6

7
Dim datum, kommen, gehen
8

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

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

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

18
Else
19

20
GoTo LastLine
21

22
End If
23

24
Dim k, g, kh, gh As Date
25

26
k = CDate(kommen)
27

28
g = CDate(gehen)
29

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

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

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

36
Else
37

38
GoTo LastLine
39

40
End If
41

42
If k > g Then
43

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

46
End If
47

48
Dim hourDiff
49

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

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

54
'计算休息时间
55

56
Dim restTime
57

58
restTime = 0
59

60
If kh > gh Then
61

62
restTime = 1
63

64
GoTo FullRest
65

66
End If
67

68
'到的时间在10：30之前
69

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

72
    If gh >= CDate("11:00:00") And gh < CDate("13:00:00") Then
73

74
    restTime = 0.25
75

76
    ElseIf gh >= CDate("13:00:00") And gh < CDate("16:00:00") Then
77

78
    restTime = 0.75
79

80
    ElseIf gh >= CDate("16:00:00") Then
81

82
    restTime = 1
83

84
    End If
85

86
ElseIf kh >= CDate("10:30:00") And kh < CDate("13:00:00") Then
87

88
    If gh >= CDate("13:00:00") And gh < CDate("16:00:00") Then
89

90
    restTime = 0.5
91

92
    ElseIf gh >= CDate("16:00:00") Then
93

94
    restTime = 0.75
95

96
    End If
97

98
ElseIf kh >= CDate("13:00:00") And kh < CDate("15:00:00") Then
99

100
    If gh >= CDate("16:00:00") Then
101

102
    restTime = 0.25
103

104
    End If
105

106
End If
107

108
FullRest:
109

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

112
'计算实际工作时长
113

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

116
LastLine:
117

118
End Sub
119

120
Sub main()
121

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

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

126
For i = 11 To 42
127

128
subline (i)
129

130
Next
131

132
End Sub

1
Sub aa()
2
Dim shp As Shape
3
Dim iCount As Long
4
Dim arr As Variant
5

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

9
    arr = Split(Sheet1.Range("E" & i).Value, ",")
10
    arrlength = UBound(arr) - LBound(arr)
11

12
    If arrlength = -1 Then
13
        arrlength = 1
14
        arr = Array("")
15
    End If
16

17
    For r = 1 To arrlength
18
        iCount2 = Sheet2.[A1048576].End(xlUp).Row
19
        Sheet1.Rows(i).Copy Sheet2.Rows(iCount2 + 1)
20
        Sheet2.Range("E" & (iCount2 + 1)).NumberFormatLocal = "@"
21
        Sheet2.Range("E" & (iCount2 + 1)).Value = Replace(Replace(arr(r - 1), "[", ""), "]", "")
22
    Next
23

24
Next
25

26
End Sub