· 10 years ago · Sep 27, 2016, 08:58 AM
1DROP TEMPORARY TABLE IF EXISTS tmp_pns;
2
3DROP TEMPORARY TABLE IF EXISTS tmp_pns_first_payment_received_at;
4
5CREATE TEMPORARY TABLE tmp_pns (
6 site_id ENUM('NZ','AU','WORLD') NOT NULL,
7 order_id INT(10) UNSIGNED NOT NULL,
8 currency VARCHAR(3) NOT NULL,
9 customer_id INT(10) UNSIGNED NOT NULL,
10 customer_name VARCHAR(255) DEFAULT NULL,
11 customer_email VARCHAR(255) DEFAULT NULL,
12 is_school TINYINT UNSIGNED NOT NULL,
13 order_placed_at DATETIME DEFAULT NULL,
14 first_payment_received_at DATETIME DEFAULT NULL,
15 ordered_amount DECIMAL(15, 6),
16 discount_amount DECIMAL(15, 6),
17 paid_amount DECIMAL(15, 6),
18 refunded_amount DECIMAL(15, 6),
19 shipped_amount DECIMAL(15, 6),
20 total_cancelled_amount DECIMAL(15, 6),
21 cancelled_amount DECIMAL(15, 6),
22 cancellation_fee_amount DECIMAL(15, 6),
23 cancelled_amount_before_payment DECIMAL(15, 6),
24 cancellation_fee_amount_before_payment DECIMAL(15, 6),
25 age_days INT(10),
26 payment_method_fee_amount DECIMAL(15, 6),
27 shipped_payment_method_fee_amount DECIMAL(15, 6),
28 unshipped_payment_method_fee_amount DECIMAL(15, 6),
29 expected_payment_amount DECIMAL(15, 6),
30 PRIMARY KEY (site_id, order_id)
31);
32
33CREATE TEMPORARY TABLE tmp_pns_first_payment_received_at (
34 site_id ENUM('NZ','AU','WORLD') NOT NULL,
35 order_id INT(10) UNSIGNED NOT NULL,
36 first_payment_received_at DATETIME DEFAULT NULL,
37 PRIMARY KEY (site_id, order_id)
38);
39
40INSERT INTO
41 tmp_pns
42 (
43 order_id,
44 site_id,
45 currency,
46 customer_id,
47 customer_name,
48 customer_email,
49 is_school,
50 order_placed_at,
51 ordered_amount,
52 discount_amount,
53 paid_amount,
54 refunded_amount,
55 shipped_amount,
56 total_cancelled_amount,
57 age_days,
58 first_payment_received_at
59 )
60 SELECT
61 ops.order_id,
62 ops.site_id,
63 ops.currency,
64 MAX(ops.customer_id) AS customer_id,
65 MAX(ops.customer_name) AS customer_name,
66 MAX(ops.customer_email) AS customer_email,
67 MAX(ops.is_school) AS is_school,
68 MIN(ops.order_placed_at) AS order_placed_at,
69 SUM(COALESCE(ops.price, 0) + COALESCE(ops.giftwrap_fee, 0) + IF(type = :_normalType, COALESCE(ops.shipping_fee, 0), 0)) AS ordered_amount,
70 SUM(IF(ops.discount_amount > 0, ops.discount_amount,0)) AS discount_amount,
71 SUM(IF(ops.payment_received_at <= :_asOfDate, COALESCE(ops.payment_amount, 0), 0)) AS paid_amount,
72 SUM(IF(ops.refunded_at <= :_asOfDate, ABS(COALESCE(ops.refunded_amount, 0)), 0)) AS refunded_amount,
73 SUM(IF(ops.shipped_at <= :_asOfDate AND (ops.cancelled_at IS NULL OR ops.cancelled_at > :_asOfDate), COALESCE(ops.price, 0) + COALESCE(ops.giftwrap_fee, 0) + IF(type = :_normalType, COALESCE(ops.shipping_fee, 0), 0), 0)) AS shipped_amount,
74 SUM(IF(ops.cancelled_at <= :_asOfDate, COALESCE(ops.price, 0) + COALESCE(ops.giftwrap_fee, 0) + IF(type = :_normalType, COALESCE(ops.shipping_fee, 0), 0), 0)) AS total_cancelled_amount,
75 DATEDIFF(:_asOfDate, ops.order_placed_at) AS age_days,
76 MIN(ops.payment_received_at) AS first_payment_received_at
77 FROM
78 order_payment_shipment ops
79 WHERE
80 ops.order_placed_at <= :_asOfDate
81 GROUP BY
82 order_id,
83 site_id
84 HAVING
85 paid_amount > 0
86 AND
87 ((ordered_amount > shipped_amount + total_cancelled_amount) OR ordered_amount = 0)
88 AND
89 paid_amount > shipped_amount
90;
91
92INSERT INTO
93 tmp_pns_first_payment_received_at
94 (site_id, order_id, first_payment_received_at)
95SELECT
96 site_id, order_id, first_payment_received_at
97FROM
98 tmp_pns
99;
100
101UPDATE
102 tmp_pns tmp
103 LEFT JOIN customer_orders co
104 ON co.orders_id = tmp.order_id
105 AND
106 co.site = tmp.site_id
107 LEFT JOIN (
108 SELECT
109 ops.order_id,
110 ops.site_id,
111 SUM(IF(ops.cancelled_at <= :_asOfDate AND ops.cancelled_at > tmp2.first_payment_received_at, COALESCE(ops.price, 0) + COALESCE(ops.giftwrap_fee, 0) + IF(type = :_normalType, COALESCE(ops.shipping_fee, 0), 0), 0)) AS cancelled_amount,
112 SUM(IF(ops.cancelled_at <= :_asOfDate AND ops.cancelled_at > tmp2.first_payment_received_at, COALESCE(ops.cancellation_fee, 0), 0)) AS cancellation_fee_amount,
113 SUM(IF(ops.cancelled_at <= :_asOfDate AND ops.cancelled_at <= tmp2.first_payment_received_at, COALESCE(ops.price, 0) + COALESCE(ops.giftwrap_fee, 0) + IF(type = :_normalType, COALESCE(ops.shipping_fee, 0), 0), 0)) AS cancelled_amount_before_payment,
114 SUM(IF(ops.cancelled_at <= :_asOfDate AND ops.cancelled_at <= tmp2.first_payment_received_at, COALESCE(ops.cancellation_fee, 0), 0)) AS cancellation_fee_amount_before_payment
115 FROM
116 order_payment_shipment ops
117 JOIN
118 tmp_pns_first_payment_received_at tmp2
119 ON tmp2.order_id = ops.order_id
120 AND
121 tmp2.site_id = ops.site_id
122 WHERE
123 ops.order_placed_at <= :_asOfDate
124 GROUP BY
125 order_id,
126 site_id
127 ) ops_sq
128 ON ops_sq.order_id = tmp.order_id
129 AND
130 ops_sq.site_id = tmp.site_id
131SET
132 tmp.payment_method_fee_amount = ROUND(COALESCE(co.payment_method_fee, 0), 6),
133 tmp.shipped_payment_method_fee_amount = ROUND(COALESCE(co.payment_method_fee, 0) * (IF(tmp.shipped_amount > 0, 1, 0)), 6),
134 tmp.unshipped_payment_method_fee_amount = ROUND(COALESCE(co.payment_method_fee, 0) * (IF(tmp.shipped_amount <= 0, 1, 0)), 6),
135 tmp.expected_payment_amount = ROUND(tmp.ordered_amount - tmp.discount_amount - ops_sq.cancelled_amount_before_payment + ops_sq.cancellation_fee_amount_before_payment + COALESCE(co.payment_method_fee, 0), 6),
136 tmp.cancelled_amount = ops_sq.cancelled_amount,
137 tmp.cancellation_fee_amount = ops_sq.cancellation_fee_amount,
138 tmp.cancelled_amount_before_payment = ops_sq.cancelled_amount_before_payment,
139 tmp.cancellation_fee_amount_before_payment = ops_sq.cancellation_fee_amount_before_payment
140;
141
142
143SELECT
144 tmp.*,
145 tmp.ordered_amount - tmp.discount_amount- tmp.shipped_amount - tmp.cancelled_amount AS unshipped_amount,
146 LEAST(tmp.expected_payment_amount, tmp.paid_amount) - tmp.cancelled_amount - tmp.shipped_amount - (COALESCE(co.payment_method_fee, 0) * (IF(tmp.shipped_amount > 0, 1, 0))) AS pns_amount,
147 GREATEST(tmp.paid_amount - tmp.expected_payment_amount, 0) AS overpaid_amount,
148 SUM(IF(cp.bank_id IN (:_adminCreditBankId, :_cashRewardBankId, :_giftVoucherBankId), COALESCE(cp.amount, 0), 0)) AS cash_component,
149 SUM(IF(cp.bank_id NOT IN (:_adminCreditBankId, :_cashRewardBankId, :_giftVoucherBankId, :_couponBankId), COALESCE(cp.amount, 0), 0)) AS non_cash_component,
150 GROUP_CONCAT(DISTINCT b.name) AS banks,
151 COALESCE(co.payment_method_fee, 0) AS payment_method_fee
152FROM
153 tmp_pns tmp
154 LEFT JOIN customer_payment cp
155 ON cp.orders_id = tmp.order_id
156 AND
157 cp.site = tmp.site_id
158 AND
159 cp.amount > 0
160 AND
161 date_received <= :_asOfDate
162 LEFT JOIN bank b
163 ON cp.bank_id = b.id
164 LEFT JOIN customer_orders co
165 ON co.orders_id = tmp.order_id
166 AND
167 co.site = tmp.site_id
168GROUP BY
169 cp.orders_id,
170 cp.site,
171 tmp.order_id,
172 tmp.site_id
173HAVING
174 ABS(pns_amount) >= 0.01
175;