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