vszHs8FM

1BEGIN
2
3//All transaction are firstly store in mango_import_temporally_transaction and is label an import id to keep track
4//From there on this script below should handle the transaction move from 
5//  mango_import_temporally_transaction to mango_import_transaction
6
7//This get the latest incoming transaction import number
8SET @lastImportedData = (SELECT import_id
9                         FROM mango_import_temporally_transaction
10                         ORDER BY import_id DESC LIMIT 1);
11
12
13//First insert data that doesn't exist in mango_import_transaction
14INSERT INTO mango_import_transactions
15SELECT null, country_code, trans_key, reference_id, network_id, retailer_id, retailer, program_id, user_id, 0,
16payment_type, payment_method, payment_details, tier_campain_id, tier_status, percent_upto, percent_cashback,
17percent_mongoes_bonus, percent_promo_bonus, SUM(purchase_in_usd), SUM(commission_in_usd), exchange_rate,
18SUM(purchase_amount), SUM(commission_amount), SUM(cashback_amount), SUM(airasiabig_point), cashback_point,
19SUM(mongoes_bonus), SUM(points_bonus), SUM(total_cashback), SUM(mangoes_earned), status, reason, notification_sent,
20transaction_date, now(), updated, process_date
21FROM mango_import_temporally_transaction A
22WHERE A.import_id = @lastImportedData
23AND reference_id NOT IN
24(
25    SELECT reference_id
26    FROM mango_import_transactions
27)
28GROUP BY A.reference_id;
29
30//Reduce the data record size by creating a new table and inserting the relevant records 
31CREATE TABLE IF NOT EXISTS sumTransaction LIKE mango_import_temporally_transaction;
32
33//Just in case
34DELETE FROM sumTransaction; 
35
36//Insert only pending transaction sum them up if reference id is the same
37INSERT INTO sumTransaction
38SELECT transaction_id, import_id, country_code, trans_key, reference_id, network_id, retailer_id, retailer, program_id, user_id, 0,
39    payment_type, payment_method, payment_details, tier_campain_id, tier_status, percent_upto, percent_cashback,
40    percent_mongoes_bonus, percent_promo_bonus, SUM(purchase_in_usd), SUM(commission_in_usd), exchange_rate,
41    SUM(purchase_amount), SUM(commission_amount), SUM(cashback_amount), FLOOR(SUM(airasiabig_point)), cashback_point,
42    SUM(mongoes_bonus), SUM(points_bonus), SUM(total_cashback), SUM(mangoes_earned), status, reason, notification_sent,
43    transaction_date, created, updated, process_date
44FROM mango_import_temporally_transaction
45WHERE import_id = @lastImportedData
46AND status = 'pending'
47GROUP BY reference_id;
48
49//Update pending transaction if total cashback or point is higher than the one in transaction table
50UPDATE mango_import_transactions a
51JOIN sumTransaction b
52ON a.reference_id = b.reference_id
53SET a.percent_mongoes_bonus = b.percent_mongoes_bonus,
54    a.tier_status = b.tier_status,
55    a.purchase_amount = b.purchase_amount,
56    a.commission_amount = b.commission_amount,
57    a.purchase_in_usd = b.purchase_in_usd,
58    a.commission_in_usd = b.commission_in_usd,
59    a.cashback_amount = b.cashback_amount,
60    a.mongoes_bonus = b.mongoes_bonus,
61    a.airasiabig_point = b.airasiabig_point,
62    a.total_cashback = b.total_cashback,
63    a.mangoes_earned = b.mangoes_earned,
64    a.status = b.status,
65    a.updated = now()
66WHERE b.status = 'pending' AND a.status <> 'paid' AND a.status <> 'rejected' AND
67     (b.total_cashback > a.total_cashback OR b.airasiabig_point > a.airasiabig_point);
68     
69DELETE FROM sumTransaction;
70
71//Insert only declined transaction sum them up if reference id is the same 
72INSERT INTO sumTransaction
73SELECT transaction_id, import_id, country_code, trans_key, reference_id, network_id, retailer_id, retailer, program_id, user_id, 0,
74    payment_type, payment_method, payment_details, tier_campain_id, tier_status, percent_upto, percent_cashback,
75    percent_mongoes_bonus, percent_promo_bonus, SUM(purchase_in_usd), SUM(commission_in_usd), exchange_rate,
76    SUM(purchase_amount), SUM(commission_amount), SUM(cashback_amount), FLOOR(SUM(airasiabig_point)), cashback_point,
77    SUM(mongoes_bonus), SUM(points_bonus), SUM(total_cashback), SUM(mangoes_earned), status, reason, notification_sent,
78    transaction_date, created, updated, process_date
79FROM mango_import_temporally_transaction
80WHERE import_id = @lastImportedData
81AND status = 'declinded'
82GROUP BY reference_id;
83
84//Update to declined transaction, if status in transaction is confirm then dont
85UPDATE mango_import_transactions a
86JOIN sumTransaction b
87ON a.reference_id = b.reference_id
88SET a.percent_mongoes_bonus = b.percent_mongoes_bonus,
89    a.tier_status = b.tier_status,
90    a.purchase_amount = b.purchase_amount,
91    a.commission_amount = b.commission_amount,
92    a.purchase_in_usd = b.purchase_in_usd,
93    a.commission_in_usd = b.commission_in_usd,
94    a.cashback_amount = b.cashback_amount,
95    a.mongoes_bonus = b.mongoes_bonus,
96    a.airasiabig_point = b.airasiabig_point,
97    a.total_cashback = b.total_cashback,
98    a.mangoes_earned = b.mangoes_earned,
99    a.status = b.status,
100    a.updated = now()
101WHERE b.status = 'declinded' AND a.status <> 'confirmed' AND a.status <> 'paid' AND a.status <> 'rejected';
102 
103DELETE FROM sumTransaction;
104
105//Insert only confirmed transaction sum them up if reference id is the same  
106INSERT INTO sumTransaction
107SELECT transaction_id, import_id, country_code, trans_key, reference_id, network_id, retailer_id, retailer, program_id, user_id, 0,
108    payment_type, payment_method, payment_details, tier_campain_id, tier_status, percent_upto, percent_cashback,
109    percent_mongoes_bonus, percent_promo_bonus, SUM(purchase_in_usd), SUM(commission_in_usd), exchange_rate,
110    SUM(purchase_amount), SUM(commission_amount), SUM(cashback_amount), FLOOR(SUM(airasiabig_point)), cashback_point,
111    SUM(mongoes_bonus), SUM(points_bonus), SUM(total_cashback), SUM(mangoes_earned), status, reason, notification_sent,
112    transaction_date, created, updated, process_date
113FROM mango_import_temporally_transaction
114WHERE import_id = @lastImportedData
115AND status = 'confirmed'
116GROUP BY reference_id;
117
118//Update to confirmed transaction if total cashback or point is higher than the one in transaction table then update that too
119UPDATE mango_import_transactions a
120JOIN sumTransaction b
121ON a.reference_id = b.reference_id
122SET a.percent_mongoes_bonus = b.percent_mongoes_bonus,
123    a.tier_status = b.tier_status,
124    a.purchase_amount = b.purchase_amount,
125    a.commission_amount = b.commission_amount,
126    a.purchase_in_usd = b.purchase_in_usd,
127    a.commission_in_usd = b.commission_in_usd,
128    a.cashback_amount = b.cashback_amount,
129    a.mongoes_bonus = b.mongoes_bonus,
130    a.airasiabig_point = b.airasiabig_point,
131    a.total_cashback = b.total_cashback,
132    a.mangoes_earned = b.mangoes_earned,
133    a.status = b.status,
134    a.updated = now()
135WHERE (a.status <> 'confirmed' AND a.status <> 'paid' AND a.status <> 'rejected') OR
136      (a.status = 'confirmed' AND (b.total_cashback > a.total_cashback OR b.airasiabig_point > a.airasiabig_point));
137       
138DROP TABLE sumTransaction;
139 
140END