1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
# 刷新应收款基准币种
UPDATE ecw_receivable er
SET base_currency_id = (SELECT er.import_currency5
FROM ecw_region er
WHERE er.id = (SELECT eoo.objective_country_id
FROM ecw_order_objective eoo
WHERE eoo.order_id = er.order_id))
WHERE base_currency_id IS NULL;
# 刷新银行明细流水号
UPDATE ecw_receipt_item AS t1
JOIN (
SELECT
id,
CONCAT(
'YHMX',
DATE_FORMAT(create_time, '%Y%m%d'),
LPAD(ROW_NUMBER() OVER (PARTITION BY DATE(create_time) ORDER BY create_time), 5, '0')
) AS new_serial_number
FROM ecw_receipt_item
) AS t2
ON t1.id = t2.id
SET t1.serial_number = t2.new_serial_number
WHERE t1.serial_number IS NULL;
# 刷新银行明细审核通过时间
UPDATE ecw_receipt_item
SET approval_time = update_time
WHERE approval_time IS NULL AND `status` = 1;
# 刷新银行明细核销币种
UPDATE ecw_receipt_item eri SET eri.write_off_currency_id = (
SELECT er.base_currency_id
FROM ecw_receivable er
WHERE er.receipt_id = eri.receipt_id
AND er.deleted = 0
AND er.base_currency_id IS NOT NULL
LIMIT 1
) WHERE eri.deleted = 0 AND eri.write_off_currency_id IS NULL;
# 刷新银行明细汇率，银行收款明细-币种≠应收所在自编号对应的核算币种，但是跟收款单-应收费用的币种相同
UPDATE ecw_receipt_item eri
SET eri.rate = (
SELECT era.write_off_rate
FROM ecw_receipt_account era
WHERE eri.receipt_id = era.receipt_id
AND era.currency_id = eri.currency_id
AND era.deleted = 0
AND era.write_off_rate IS NOT NULL
LIMIT 1
)
WHERE eri.`status` = 1
AND eri.deleted = 0
AND EXISTS (
SELECT 1
FROM ecw_receipt_account era
WHERE eri.receipt_id = era.receipt_id
AND era.currency_id = eri.currency_id
AND era.deleted = 0
AND era.write_off_rate IS NOT NULL
);
# 查询银行收款明细-币种≠应收所在自编号对应的核算币种，跟收款单-应收费用的币种也无法匹配
SELECT
eri.id,
eri.serial_number,
eri.receipt_id,
era.id aid,
eri.currency_id ic,
era.currency_id ac,
era.write_off_rate
FROM
ecw_receipt_item eri
LEFT JOIN ecw_receipt_account era ON eri.receipt_id = era.receipt_id
AND eri.currency_id = era.currency_id
AND era.deleted = 0
WHERE
eri.`status` = 1
AND eri.deleted = 0
AND era.id IS NULL;
# 刷新银行收款明细-币种≠应收所在自编号对应的核算币种，跟收款单-应收费用的币种也无法匹配
UPDATE ecw_receipt_item eri
JOIN (
SELECT
eri.id
FROM
ecw_receipt_item eri
LEFT JOIN ecw_receipt_account era
ON eri.receipt_id = era.receipt_id
AND eri.currency_id = era.currency_id
AND era.deleted = 0
WHERE
eri.`status` = 1
AND eri.deleted = 0
AND era.id IS NULL
) AS to_update ON eri.id = to_update.id
SET eri.rate = IFNULL((
SELECT eer.currency_rate
FROM ecw_exchange_rate eer
WHERE eer.source_currency_id = eri.currency_id
AND eer.target_currency_id = eri.write_off_currency_id
),1)
WHERE eri.`status` = 1
AND eri.deleted = 0;
# 更新收款单生成路径
UPDATE ecw_receipt SET generate_path = 0 WHERE generate_path IS NULL;
# 刷新应收款优惠金额
UPDATE ecw_receivable er
SET er.discount_total = 0 WHERE er.discount_total IS NULL;
# 刷新应收款汇率
# 应收明细币种=基准币种
UPDATE ecw_receivable er
SET er.exchange_rate = 1 WHERE er.exchange_rate IS NULL AND er.receipt_id IS NOT NULL AND er.currency_id = er.base_currency_id AND er.deleted = 0;
# 应收明细基准币种是美元
UPDATE ecw_receivable er
SET er.exchange_rate = (
SELECT
era.write_off_rate
FROM
ecw_receipt_account era
WHERE
era.receipt_id = er.receipt_id
AND era.currency_id = er.currency_id
AND era.deleted = 0
AND era.write_off_rate IS NOT NULL
LIMIT 1
)
WHERE
er.receipt_id IS NOT NULL
AND er.base_currency_id = 1
AND er.currency_id != 1
AND er.deleted = 0;
# 应收明细基准币种不是美元
UPDATE ecw_receivable er
SET er.exchange_rate = (SELECT eer.currency_rate FROM ecw_exchange_rate eer WHERE eer.source_currency_id = er.currency_id AND eer.target_currency_id = er.base_currency_id )
WHERE er.exchange_rate IS NULL AND er.receipt_id IS NOT NULL AND er.base_currency_id != 1 AND er.deleted = 0;
# 刷新应收款基准金额
UPDATE ecw_receivable er
SET er.base_amount = er.exchange_rate * (er.tax_amount - er.discount_total)
WHERE er.base_amount IS NULL AND er.receipt_id IS NOT NULL AND er.exchange_rate IS NOT NULL;
# 修改收款单已核销待开票状态
UPDATE `ecw_receipt` SET `state` = 4 WHERE `state` = 5;
# 刷新应收明细账户ID
UPDATE ecw_receipt_item eri SET eri.account_id = (SELECT eba.id FROM ecw_bank_account eba WHERE eba.ba_account_name = eri.account_name AND eba.ba_bank_name = eri.account_bank_name AND eba.ba_account_num = eri.account_no AND eba.deleted = 0) WHERE eri.deleted = 0;
# 刷新收款明细-应收明细-收入归属(银行帐号设置收入归属之后执行)
UPDATE ecw_receivable_write_off_record erwor
SET erwor.income_belong = (SELECT eba.ba_income_belong FROM ecw_receipt_item eri LEFT JOIN ecw_bank_account eba ON eri.account_id = eba.id WHERE erwor.receipt_item_id = eri.id)
WHERE erwor.income_belong IS NULL;