| 1 | DROP TABLE IF EXISTS payment_bankaccounts; |
| 2 | CREATE TABLE payment_bankaccounts ( |
| 3 | id integer PRIMARY KEY auto_increment, |
| 4 | asso_id integer NOT NULL, |
| 5 | account varchar(23) NOT NULL, |
| 6 | owner varchar(100) NOT NULL, |
| 7 | status set('new','used','old') NOT NULL default 'new' |
| 8 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 9 | |
| 10 | #INSERT INTO payment_bankaccounts VALUES (NULL,,"30002004690000008524R29","Amis de l'Espace Dirigea","used"); |
| 11 | INSERT INTO payment_bankaccounts VALUES (NULL,239,"10207001172019602580784","ASCCX","used"); |
| 12 | INSERT INTO payment_bankaccounts VALUES (NULL,42,"14707000010892101692291","AX (BPLC)","used"); |
| 13 | INSERT INTO payment_bankaccounts VALUES (NULL,42,"30066109310001022770164","AX (CIC)","used"); |
| 14 | INSERT INTO payment_bankaccounts VALUES (NULL,42,"30002004200000009372U74","AX (LCL)","used"); |
| 15 | INSERT INTO payment_bankaccounts VALUES (NULL,31,"10107001820002105034095","Binet Point Gamma","used"); |
| 16 | INSERT INTO payment_bankaccounts VALUES (NULL,73,"30003020600003729601589","GTX","used"); |
| 17 | INSERT INTO payment_bankaccounts VALUES (NULL,246,"20041000012241609M02035","Humanix - Jacques Bellev","used"); |
| 18 | #INSERT INTO payment_bankaccounts VALUES (NULL,,"10107001820092105033751","Kes des élèves","used"); |
| 19 | INSERT INTO payment_bankaccounts VALUES (NULL,214,"30003022160005198020072","Khomiss (Aurélien Lajoie","used"); |
| 20 | #INSERT INTO payment_bankaccounts VALUES (NULL,,"30003021900002011521283","Maison des X","used"); |
| 21 | INSERT INTO payment_bankaccounts VALUES (NULL,181,"10107001820012105055968","Raid Polytechnique 2004","used"); |
| 22 | INSERT INTO payment_bankaccounts VALUES (NULL,165,"20041010123576371A03369","Sabix","used"); |
| 23 | INSERT INTO payment_bankaccounts VALUES (NULL,11,"30002089410000023857R03","X-eConfiance 'Mathieu Be","used"); |
| 24 | INSERT INTO payment_bankaccounts VALUES (NULL,251,"30003022200005041343575","X-Achats 'Francois Rena","used"); |
| 25 | INSERT INTO payment_bankaccounts VALUES (NULL,252,"30002008190000045217G86","X-Automobile 'F. Tronel","used"); |
| 26 | INSERT INTO payment_bankaccounts VALUES (NULL,6,"30002005940000434521B52","X-Aviation 'Francis Fouq","used"); |
| 27 | INSERT INTO payment_bankaccounts VALUES (NULL,96,"30003041110003726598647","X-Biotech (M.O.Bevierre)","used"); |
| 28 | INSERT INTO payment_bankaccounts VALUES (NULL,57,"15589335720697076254012","X-Bordelais (T Leblond)","used"); |
| 29 | INSERT INTO payment_bankaccounts VALUES (NULL,4,"30003005080003728293253","X-Consult","used"); |
| 30 | INSERT INTO payment_bankaccounts VALUES (NULL,18,"30066100210001067980188","X-Environnement (P Worbe","used"); |
| 31 | INSERT INTO payment_bankaccounts VALUES (NULL,3,"30003031900005066357935","X-Finance - Ariane Chaze","used"); |
| 32 | INSERT INTO payment_bankaccounts VALUES (NULL,7,"30002004200000009372U74","X-Gaziers - Compte AX LC","used"); |
| 33 | INSERT INTO payment_bankaccounts VALUES (NULL,21,"30588610978071800010189","X-Golf (Guy Marchand)","used"); |
| 34 | INSERT INTO payment_bankaccounts VALUES (NULL,202,"30003034210005003887246","X-HEC CapInvest (A Santo","used"); |
| 35 | INSERT INTO payment_bankaccounts VALUES (NULL,174,"30002006840000005831S15","X-Mer","used"); |
| 36 | INSERT INTO payment_bankaccounts VALUES (NULL,166,"30066108700001028630170","X-Mines au Feminin","used"); |
| 37 | INSERT INTO payment_bankaccounts VALUES (NULL,219,"30002004200000009372U74","X-Nucleaire - Compte AX","used"); |
| 38 | INSERT INTO payment_bankaccounts VALUES (NULL,82,"30003038320005055982303","X-Pierre (Quoc-Giao Tran","used"); |
| 39 | INSERT INTO payment_bankaccounts VALUES (NULL,233,"30002004200000009372U74","X-PI - Compte AX LCL","used"); |
| 40 | INSERT INTO payment_bankaccounts VALUES (NULL,248,"12548029983443030151039","X-Renouvelables 'Jerome","used"); |
| 41 | INSERT INTO payment_bankaccounts VALUES (NULL,179,"30066106410001050600128","X-Sursaut H Levy-Lambert","used"); |
| 42 | INSERT INTO payment_bankaccounts VALUES (NULL,223,"30066100410001126780124","X-Theatre","used"); |
| 43 | |
| 44 | DROP TABLE IF EXISTS payment_reconcilations; |
| 45 | CREATE TABLE payment_reconcilations ( |
| 46 | id INTEGER PRIMARY KEY auto_increment, |
| 47 | method_id INTEGER NOT NULL, |
| 48 | period_start DATE NOT NULL, |
| 49 | period_end DATE NOT NULL, |
| 50 | recongroup_id INTEGER DEFAULT NULL, |
| 51 | status ENUM('pending','transfering','closed') NOT NULL DEFAULT 'pending', |
| 52 | payment_count INTEGER NOT NULL, |
| 53 | sum_amounts DECIMAL(9,2) NOT NULL, # transaction amount, before taking the commission |
| 54 | sum_commissions DECIMAL(9,2) NOT NULL, |
| 55 | comments text NOT NULL |
| 56 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 57 | |
| 58 | ALTER TABLE payment_transactions ADD method_id INTEGER DEFAULT NULL AFTER id; # NULL if not initiated from the site |
| 59 | ALTER TABLE payment_transactions CHANGE timestamp ts_confirmed DATETIME DEFAULT NULL; # NULL = not confirmed |
| 60 | ALTER TABLE payment_transactions ADD ts_initiated DATETIME DEFAULT NULL AFTER ts_confirmed; # NULL = not initiated |
| 61 | ALTER TABLE payment_transactions CHANGE amount amount_tmp VARCHAR(15); |
| 62 | ALTER TABLE payment_transactions ADD amount DECIMAL(9,2) NOT NULL AFTER amount_tmp; # only local currency allowed (EUR) |
| 63 | ALTER TABLE payment_transactions ADD commission DECIMAL(9,2) DEFAULT NULL AFTER amount; |
| 64 | ALTER TABLE payment_transactions ADD status ENUM('confirmed','pending','canceled') NOT NULL DEFAULT 'pending'; |
| 65 | ALTER TABLE payment_transactions ADD recon_id INTEGER DEFAULT NULL; # NULL = not reconciliated |
| 66 | UPDATE payment_transactions SET method_id = 0 WHERE length(id)=7; |
| 67 | UPDATE payment_transactions SET method_id = 1 WHERE length(id)=15 OR length(id)=17; |
| 68 | UPDATE payment_transactions SET method_id = 2 WHERE length(id)=14; |
| 69 | UPDATE payment_transactions SET status = 'confirmed'; |
| 70 | UPDATE payment_transactions SET amount=CONVERT(REPLACE(REPLACE(amount_tmp," EUR",""),",","."),DECIMAL(9,2)); |
| 71 | ALTER TABLE payment_transactions ADD KEY method_id (method_id); |
| 72 | ALTER TABLE payment_transactions ADD KEY ref (ref); |
| 73 | # ALTER TABLE payment_transactions ADD UNIQUE KEY fullref (fullref); |
| 74 | #fullref dupliqués : |
| 75 | #select t1.* from payment_transactions as t1 join payment_transactions as t2 using(fullref) group by(t1.id) having count(*)!=1 order by fullref; |
| 76 | ALTER TABLE payment_transactions DROP amount_tmp; |
| 77 | |
| 78 | DROP TABLE IF EXISTS payment_transfers; |
| 79 | CREATE TABLE payment_transfers ( |
| 80 | id INTEGER PRIMARY KEY auto_increment, |
| 81 | recongroup_id INTEGER NOT NULL, |
| 82 | payment_id INTEGER NOT NULL, |
| 83 | amount DECIMAL(9,2) NOT NULL, |
| 84 | account_id INTEGER DEFAULT NULL, |
| 85 | message VARCHAR(255) NOT NULL, |
| 86 | date DATE DEFAULT NULL # NULL = not done |
| 87 | ) ENGINE=InnoDB, CHARSET=utf8; |
| 88 | |
| 89 | ALTER TABLE payment_methods ADD short_name VARCHAR(10) NOT NULL; |
| 90 | ALTER TABLE payment_methods ADD flags SET('deferred_com') DEFAULT ''; |
| 91 | UPDATE payment_methods SET short_name='paypal', flags='' WHERE id=1; |
| 92 | UPDATE payment_methods SET short_name='bplc2', flags='deferred_com' WHERE id=2; |
| 93 | |
| 94 | -- vim:set syntax=mysql: |
| 95 | |