X-Git-Url: http://git.polytechnique.org/?a=blobdiff_plain;f=upgrade%2F1.1.2%2F02_payments.sql;h=99332d34300b12d951255fdf5c38ed7742dccadd;hb=fb052bdeb13817b3dda3f7443fe7b2717b7413ca;hp=64359f774e5b6e369b07f2daf9b50d6f09e47eda;hpb=fbb196fa48be340d43f25fbab1752724e9e0802c;p=platal.git

diff --git a/upgrade/1.1.2/02_payments.sql b/upgrade/1.1.2/02_payments.sql
index 64359f7..99332d3 100644
--- a/upgrade/1.1.2/02_payments.sql
+++ b/upgrade/1.1.2/02_payments.sql
@@ -1,4 +1,25 @@
-ALTER TABLE payments MODIFY COLUMN flags SET('unique', 'old', 'donation') NOT NULL DEFAULT '';
+ALTER TABLE payment_transactions ADD method_id INTEGER DEFAULT NULL AFTER id; # NULL if not initiated from the site
+ALTER TABLE payment_transactions CHANGE timestamp ts_confirmed DATETIME DEFAULT NULL; # NULL = not confirmed
+ALTER TABLE payment_transactions ADD ts_initiated DATETIME DEFAULT NULL AFTER ts_confirmed; # NULL = not initiated
+ALTER TABLE payment_transactions CHANGE amount amount_tmp VARCHAR(15);
+ALTER TABLE payment_transactions ADD amount DECIMAL(9,2) NOT NULL AFTER amount_tmp; # only local currency allowed (EUR)
+ALTER TABLE payment_transactions ADD commission DECIMAL(9,2) DEFAULT NULL AFTER amount;
+ALTER TABLE payment_transactions ADD status ENUM('confirmed','pending','canceled') NOT NULL DEFAULT 'pending';
+ALTER TABLE payment_transactions ADD recon_id INTEGER DEFAULT NULL; # NULL = not reconciliated
+UPDATE payment_transactions SET method_id = 0 WHERE length(id)=7;
+UPDATE payment_transactions SET method_id = 1 WHERE length(id)=15 OR length(id)=17;
+UPDATE payment_transactions SET method_id = 2 WHERE length(id)=14;
+UPDATE payment_transactions SET status = 'confirmed';
+UPDATE payment_transactions SET amount=CONVERT(REPLACE(REPLACE(amount_tmp," EUR",""),",","."),DECIMAL(9,2));
+ALTER TABLE payment_transactions ADD KEY method_id (method_id);
+ALTER TABLE payment_transactions ADD KEY ref (ref);
+# ALTER TABLE payment_transactions ADD UNIQUE KEY fullref (fullref);
+#fullref dupliqués :
+#select t1.* from payment_transactions as t1 join payment_transactions as t2 using(fullref) group by(t1.id) having count(*)!=1 order by fullref;
+ALTER TABLE payment_transactions DROP amount_tmp;
+
 ALTER TABLE payment_transactions ADD COLUMN display BOOL NOT NULL DEFAULT FALSE;
+ALTER TABLE payments MODIFY COLUMN flags SET('unique', 'old', 'donation') NOT NULL DEFAULT '';
 
 -- vim:set syntax=mysql:
+