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
;
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");
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
;
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
);
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
;
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
;
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;
94 -- vim:set syntax=mysql: