Outputs iso-8859-1 in addresses list as excel cannot open utf-8 csv properly.

[platal.git] / upgrade / 1.0.1 / 13_payments.sql

diff --git a/upgrade/1.0.1/13_payments.sql b/upgrade/1.0.1/13_payments.sql
index 590434e..0196b9a 100644 (file)
--- a/upgrade/1.0.1/13_payments.sql
+++ b/upgrade/1.0.1/13_payments.sql
@@ -1,3 +1,4 @@
+DROP TABLE IF EXISTS payment_bankaccounts;
 CREATE TABLE payment_bankaccounts (
   id integer PRIMARY KEY auto_increment,
   asso_id integer NOT NULL,
@@ -40,11 +41,13 @@ INSERT INTO payment_bankaccounts VALUES (NULL,248,"12548029983443030151039","X-R
 INSERT INTO payment_bankaccounts VALUES (NULL,179,"30066106410001050600128","X-Sursaut H Levy-Lambert","used");
 INSERT INTO payment_bankaccounts VALUES (NULL,223,"30066100410001126780124","X-Theatre","used");
 
+DROP TABLE IF EXISTS payment_reconcilations;
 CREATE TABLE payment_reconcilations (
   id INTEGER PRIMARY KEY auto_increment,
   method_id INTEGER NOT NULL,
   period_start DATE NOT NULL,
   period_end DATE NOT NULL,
+  recongroup_id INTEGER DEFAULT NULL,
   status ENUM('pending','transfering','closed') NOT NULL DEFAULT 'pending',
   payment_count INTEGER NOT NULL,
   sum_amounts DECIMAL(9,2) NOT NULL, # transaction amount, before taking the commission
@@ -52,38 +55,21 @@ CREATE TABLE payment_reconcilations (
   comments text NOT NULL
 ) ENGINE=InnoDB, CHARSET=utf8;
 
-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;
-
+DROP TABLE IF EXISTS payment_transfers;
 CREATE TABLE payment_transfers (
-  id integer PRIMARY KEY auto_increment,
-  payment_id integer NOT NULL,
+  id INTEGER PRIMARY KEY auto_increment,
+  recongroup_id INTEGER NOT NULL,
+  payment_id INTEGER NOT NULL,
   amount DECIMAL(9,2) NOT NULL,
+  account_id INTEGER DEFAULT NULL,
   message VARCHAR(255) NOT NULL,
-  date DATE # NULL = not done
+  date DATE DEFAULT NULL # NULL = not done
 ) ENGINE=InnoDB, CHARSET=utf8;
 
-CREATE TABLE payment_recon_transfer (
-  recon_id INTEGER NOT NULL,
-  transfer_id INTEGER NOT NULL,
-  PRIMARY KEY (recon_id,transfer_id)
-) ENGINE=InnoDB, CHARSET=utf8;
+ALTER TABLE payment_methods ADD short_name VARCHAR(10) NOT NULL;
+ALTER TABLE payment_methods ADD flags SET('deferred_com') DEFAULT '';
+UPDATE payment_methods SET short_name='paypal', flags='' WHERE id=1;
+UPDATE payment_methods SET short_name='bplc2', flags='deferred_com' WHERE id=2;
 
 -- vim:set syntax=mysql:
+