0%

重新实现monthly步骤

1 bi新建已收、已付、应收、应付表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `order_product_set_pay`(
`id` int(10) unsigned NOT NULL,
`order_product_id` int(10) unsigned NOT NULL,
`parent_order_product_id` int(10) unsigned DEFAULT '0' COMMENT 'parent order_product_id',
`has_settlement` decimal(10,2) NOT NULL DEFAULT '0.00',
`should_settlement` decimal(10,2) NOT NULL DEFAULT '0.00',
`has_pay` decimal(10,2) NOT NULL DEFAULT '0.00',
`should_pay` decimal(10,2) NOT NULL DEFAULT '0.00',
`settlement_pay_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`pay_pay_status` varchar(255) NOT NULL DEFAULT 'Pending',
`pay_pay_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


alter table `order_product_set_pay` drop primary key;
alter table `order_product_set_pay` add `settlement_id` int(10) unsigned NOT NULL;

2 从bi表中查询数据

1 已收
(总收)/比例
比例 =

mysql查询优化

优化结果

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
SELECT
op.order_product_id,
op.parent_order_product_id,
op.product_retail,
op.product_discount,
op.insurance_retail,
op.order_id,
op.product_cost,
opi.`bill_status` AS bill_status,
fs.settlement_id,
fs.type,
fs.`status`,
fs.paid_usd_amount AS should_usd,
fs.updated_at AS modify_time,
(
SELECT
sum((
CASE
WHEN (cu.`code`='CNY') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"CNY":', - 1), 6), 2)
WHEN (cu.`code`='GBP') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"GBP":', - 1), 6), 2)
WHEN (cu.`code`='AUD') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"AUD":', - 1), 6), 2)
WHEN (cu.`code`='NZD') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"NZD":', - 1), 6), 2)
WHEN (cu.`code`='EUR') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"EUR":', - 1), 6), 2)
WHEN (cu.`code`='CAD') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"CAD":', - 1), 6), 2)
WHEN (cu.`code`='SUR') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"SUR":', - 1), 6), 2)
WHEN (cu.`code`='JPY') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"JPY":', - 1), 6), 2)
WHEN (cu.`code`='ZAR') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"ZAR":', - 1), 6), 2)
WHEN (cu.`code`='NOK') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"NOK":', - 1), 6), 2)
WHEN (cu.`code`='PHP') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"PHP":', - 1), 6), 2)
WHEN (cu.`code`='SEK') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"SEK":', - 1), 6), 2)
WHEN (cu.`code`='CHF') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"CHF":', - 1), 6), 2)
WHEN (cu.`code`='DKK') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"DKK":', - 1), 6), 2)
WHEN (cu.`code`='HKD') THEN
ROUND((opii.`amount`)/LEFT(SUBSTRING_INDEX(o.rate_info, '"HKD":', - 1), 6), 2)
WHEN (cu.`code`='USD') THEN opii.`amount`
END
)) has_paid
FROM
`bi`.`order_product_item` opii
LEFT JOIN `bi`.`order` AS o ON opii.order_id= o.order_id
LEFT JOIN `ori_new`.`financial_bill` AS fb ON opii.bill_id= fb.id
LEFT JOIN `bi`.`order_product` AS opp ON opii.order_product_id= opp.order_product_id
LEFT JOIN `ori_new`.`tff_provider` tp on opp.provider_id=tp.provider_id
LEFT JOIN `bi`.`currency` cu on tp.currency_id=cu.currency_id
WHERE
opii.order_product_id = op.order_product_id
AND (
opii.`bill_status` = 'Finished'
OR opii.`bill_status` = 'Paid'
)
) has_paid,
(
SELECT
fb.updated_at
FROM
`ori_new`.`financial_bill` fb
LEFT JOIN `bi`.`order_product_item` opii ON opii.bill_id= fb.id
WHERE
opii.order_product_id = op.order_product_id
AND opii.`bill_status` = 'Paid'
limit 1
) `pay_pay_time`,
(
SELECT
sum(opa.product_retail) AS total_retail
FROM
`bi`.`order_product` opa
WHERE
opa.order_id = op.order_id
AND opa.product_line != 'combine'
) total_retail,
(
SELECT
sum(opa.insurance_retail) AS insurance_total
FROM
`bi`.`order_product` opa
WHERE
opa.order_id = op.order_id
AND opa.product_line != 'combine'
) insurance_total,
(
SELECT
count(opa.order_product_id) AS op_num
FROM
`bi`.`order_product` opa
WHERE
opa.order_id = op.order_id
) op_num
FROM
`bi`.`order_product` op
LEFT JOIN `bi`.`order_product_item` opi ON op.order_product_id = opi.order_product_id
LEFT JOIN `ori_new`.financial_settlement AS fs ON fs.order_id = op.order_id
LEFT JOIN `ori_new`.financial_payment_method AS p ON fs.payment_method_id = p.payment_method_id
WHERE
op.order_product_id in ({0})
AND op.product_line != 'combine'

用过的命令

1
2
sudo awk -F '[ ;]+' '{if($2>2) print $1,$2}' test.txt | awk  '{print $1}' |xargs  |tr "\n" " "|sed 's/ /,/g'
sudo sed -i 's/api.bi.tff.com/127.0.0.1:5003/g' `find ./dist/ -name '*js'`