· 8 years ago · Jan 17, 2018, 07:20 AM
1BEGIN
2
3SET @backup = (SELECT backup_limit FROM mango_api_setting);
4
5SET @lastImportedData = (SELECT import_id
6 FROM mango_import_temporally_transaction
7 ORDER BY import_id DESC LIMIT 1);
8
9INSERT INTO mango_import_transactions
10SELECT null, country_code, trans_key, reference_id, network_id, retailer_id, retailer, program_id, user_id, 0,
11payment_type, payment_method, payment_details, tier_campain_id, tier_status, percent_upto, percent_cashback,
12percent_mongoes_bonus, percent_promo_bonus, SUM(purchase_in_usd), SUM(commission_in_usd), exchange_rate,
13SUM(purchase_amount), SUM(commission_amount), SUM(cashback_amount), SUM(airasiabig_point), cashback_point,
14SUM(mongoes_bonus), SUM(points_bonus), SUM(total_cashback), SUM(mangoes_earned), status, reason, notification_sent,
15transaction_date, now(), updated, process_date
16FROM mango_import_temporally_transaction A
17WHERE A.import_id = @lastImportedData
18AND reference_id NOT IN
19(
20 SELECT reference_id
21 FROM mango_import_transactions
22)
23GROUP BY A.reference_id;
24
25CREATE TABLE IF NOT EXISTS sumTransaction LIKE mango_import_temporally_transaction;
26
27DELETE FROM sumTransaction;
28
29INSERT INTO sumTransaction
30SELECT transaction_id, import_id, country_code, trans_key, reference_id, network_id, retailer_id, retailer, program_id, user_id, 0,
31 payment_type, payment_method, payment_details, tier_campain_id, tier_status, percent_upto, percent_cashback,
32 percent_mongoes_bonus, percent_promo_bonus, SUM(purchase_in_usd), SUM(commission_in_usd), exchange_rate,
33 SUM(purchase_amount), SUM(commission_amount), SUM(cashback_amount), FLOOR(SUM(airasiabig_point)), cashback_point,
34 SUM(mongoes_bonus), SUM(points_bonus), SUM(total_cashback), SUM(mangoes_earned), status, reason, notification_sent,
35 transaction_date, created, updated, process_date
36FROM mango_import_temporally_transaction
37WHERE import_id = @lastImportedData
38AND status = 'pending'
39GROUP BY reference_id;
40
41UPDATE mango_import_transactions a
42JOIN sumTransaction b
43ON a.reference_id = b.reference_id
44SET a.percent_mongoes_bonus = b.percent_mongoes_bonus,
45 a.tier_status = b.tier_status,
46 a.purchase_amount = b.purchase_amount,
47 a.commission_amount = b.commission_amount,
48 a.purchase_in_usd = b.purchase_in_usd,
49 a.commission_in_usd = b.commission_in_usd,
50 a.cashback_amount = b.cashback_amount,
51 a.mongoes_bonus = b.mongoes_bonus,
52 a.airasiabig_point = b.airasiabig_point,
53 a.total_cashback = b.total_cashback,
54 a.mangoes_earned = b.mangoes_earned,
55 a.status = b.status,
56 a.updated = now()
57WHERE b.status = 'pending' AND a.status <> 'paid' AND a.status <> 'rejected' AND
58 (b.total_cashback > a.total_cashback OR b.airasiabig_point > a.airasiabig_point);
59
60DELETE FROM sumTransaction;
61
62INSERT INTO sumTransaction
63SELECT transaction_id, import_id, country_code, trans_key, reference_id, network_id, retailer_id, retailer, program_id, user_id, 0,
64 payment_type, payment_method, payment_details, tier_campain_id, tier_status, percent_upto, percent_cashback,
65 percent_mongoes_bonus, percent_promo_bonus, SUM(purchase_in_usd), SUM(commission_in_usd), exchange_rate,
66 SUM(purchase_amount), SUM(commission_amount), SUM(cashback_amount), FLOOR(SUM(airasiabig_point)), cashback_point,
67 SUM(mongoes_bonus), SUM(points_bonus), SUM(total_cashback), SUM(mangoes_earned), status, reason, notification_sent,
68 transaction_date, created, updated, process_date
69FROM mango_import_temporally_transaction
70WHERE import_id = @lastImportedData
71AND status = 'declinded'
72GROUP BY reference_id;
73
74UPDATE mango_import_transactions a
75JOIN sumTransaction b
76ON a.reference_id = b.reference_id
77SET a.percent_mongoes_bonus = b.percent_mongoes_bonus,
78 a.tier_status = b.tier_status,
79 a.purchase_amount = b.purchase_amount,
80 a.commission_amount = b.commission_amount,
81 a.purchase_in_usd = b.purchase_in_usd,
82 a.commission_in_usd = b.commission_in_usd,
83 a.cashback_amount = b.cashback_amount,
84 a.mongoes_bonus = b.mongoes_bonus,
85 a.airasiabig_point = b.airasiabig_point,
86 a.total_cashback = b.total_cashback,
87 a.mangoes_earned = b.mangoes_earned,
88 a.status = b.status,
89 a.updated = now()
90WHERE b.status = 'declinded' AND a.status <> 'confirmed' AND a.status <> 'paid' AND a.status <> 'rejected';
91
92DELETE FROM sumTransaction;
93
94INSERT INTO sumTransaction
95SELECT transaction_id, import_id, country_code, trans_key, reference_id, network_id, retailer_id, retailer, program_id, user_id, 0,
96 payment_type, payment_method, payment_details, tier_campain_id, tier_status, percent_upto, percent_cashback,
97 percent_mongoes_bonus, percent_promo_bonus, SUM(purchase_in_usd), SUM(commission_in_usd), exchange_rate,
98 SUM(purchase_amount), SUM(commission_amount), SUM(cashback_amount), FLOOR(SUM(airasiabig_point)), cashback_point,
99 SUM(mongoes_bonus), SUM(points_bonus), SUM(total_cashback), SUM(mangoes_earned), status, reason, notification_sent,
100 transaction_date, created, updated, process_date
101FROM mango_import_temporally_transaction
102WHERE import_id = @lastImportedData
103AND status = 'confirmed'
104GROUP BY reference_id;
105
106UPDATE mango_import_transactions a
107JOIN sumTransaction b
108ON a.reference_id = b.reference_id
109SET a.percent_mongoes_bonus = b.percent_mongoes_bonus,
110 a.tier_status = b.tier_status,
111 a.purchase_amount = b.purchase_amount,
112 a.commission_amount = b.commission_amount,
113 a.purchase_in_usd = b.purchase_in_usd,
114 a.commission_in_usd = b.commission_in_usd,
115 a.cashback_amount = b.cashback_amount,
116 a.mongoes_bonus = b.mongoes_bonus,
117 a.airasiabig_point = b.airasiabig_point,
118 a.total_cashback = b.total_cashback,
119 a.mangoes_earned = b.mangoes_earned,
120 a.status = b.status,
121 a.updated = now()
122WHERE (a.status <> 'confirmed' AND a.status <> 'paid' AND a.status <> 'rejected') OR
123 (a.status = 'confirmed' AND (b.total_cashback > a.total_cashback OR b.airasiabig_point > a.airasiabig_point));
124
125DROP TABLE sumTransaction;
126
127IF @lastImportedData = '' THEN
128 SET @lastImportedData = 1;
129END IF;
130
131SET @keepRange = @lastImportedData - @backup;
132
133IF @keepRange > 0 THEN
134 DELETE FROM mango_import_temporally_transaction
135 WHERE import_id < @keepRange;
136 DELETE FROM mango_api_log
137 WHERE import_id < @keepRange;
138 DELETE FROM mango_import_error_log
139 WHERE import_id < @keepRange;
140 DELETE FROM mango_api_data
141 WHERE import_id < @keepRange;
142 DELETE FROM mango_import_cache_update
143 WHERE cache_id < @keepRange;
144 DELETE FROM mango_import_cache_insert
145 WHERE cache_id < @keepRange;
146END IF;
147
148END