upgrade/1.0.1/13_payments.sql

   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