From f56ceafecc0deae130734dfc0243804f02f46586 Mon Sep 17 00:00:00 2001 From: Damien Bobillot Date: Sun, 3 Oct 2010 06:34:23 +0200 Subject: [PATCH] Integration of new features for admin > payments : - view transactions - manage bank account numbers (RIBs) - reconcilation between bank data and platal database - manage money transfers towards groups - manage the list of payment methods --- modules/payment.php | 321 +++++++++++++++++++++++++++++++++++++++- templates/payment/reconcile.tpl | 218 +++++++++++++++++++++++++++ upgrade/1.0.1/payments.sql | 87 +++++++++++ 3 files changed, 624 insertions(+), 2 deletions(-) create mode 100644 templates/payment/reconcile.tpl create mode 100644 upgrade/1.0.1/payments.sql diff --git a/modules/payment.php b/modules/payment.php index d24ebcf..7f290e1 100644 --- a/modules/payment.php +++ b/modules/payment.php @@ -84,6 +84,21 @@ function comment_decode($comment) { } } +/* check if a RIB account number is valid */ +function check_rib($rib) +{ + if(strlen($rib) != 23) return false; + + // extract fields + $rib = strtr(strtoupper($rib),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','12345678912345678923456789'); + $bank = substr($rib,0,5); + $counter = substr($rib,5,5); + $account = substr($rib,10,11); + $key = substr($rib,21,2); + + // check + return 0 == fmod(89 * $bank + 15 * $counter + 3 * $account + $key, 97); +} class PaymentModule extends PLModule { @@ -100,7 +115,10 @@ class PaymentModule extends PLModule '%grp/payment/cyber2_return' => $this->make_hook('cyber2_return', AUTH_PUBLIC, 'user', NO_HTTPS), '%grp/payment/paypal_return' => $this->make_hook('paypal_return', AUTH_PUBLIC, 'user', NO_HTTPS), 'admin/payments' => $this->make_hook('admin', AUTH_MDP, 'admin'), - + 'admin/payments/methods' => $this->make_hook('adm_methods', AUTH_MDP, 'admin'), + 'admin/payments/transactions'=> $this->make_hook('adm_transactions', AUTH_MDP, 'admin'), + 'admin/payments/reconcile' => $this->make_hook('adm_reconcile', AUTH_MDP, 'admin'), + 'admin/payments/bankaccounts'=> $this->make_hook('adm_bankaccounts', AUTH_MDP, 'admin'), ); } @@ -538,7 +556,7 @@ class PaymentModule extends PLModule $page->setTitle('Administration - Paiements'); $page->assign('title', 'Gestion des télépaiements'); $table_editor = new PLTableEditor('admin/payments','payments','id'); - $table_editor->add_join_table('payment_transactions','ref',true); + //$table_editor->add_join_table('payment_transactions','ref',true); => on ne supprime jamais une transaction $table_editor->add_sort_field('flags'); $table_editor->add_sort_field('id', true, true); $table_editor->on_delete("UPDATE payments SET flags = 'old' WHERE id = {?}", "Le paiement a été archivé"); @@ -556,6 +574,305 @@ class PaymentModule extends PLModule $table_editor->apply($page, $action, $id); } + + function handler_adm_transactions(&$page, $payment_id = null, $action = "list", $id = null) { + // show transactions. FIXME: should not be modifiable + $page->setTitle('Administration - Paiements - Transactions'); + $page->assign('title', "Liste des transactions pour le paiement {$payment_id}"); + + if ($payment_id == null) + $page->trigError("Aucun ID de paiement fourni."); + + $table_editor = new PLTableEditor("admin/transactions/{$payment_id}",'payment_transactions','id'); + $table_editor->set_where_clause(XDB::format('ref = {?}', $payment_id)); + $table_editor->apply($page, 'list', $id); // only the 'list' action is allowed + $page->assign("readonly","readonly"); // don't show modification features + } + + function handler_adm_bankaccounts(&$page, $action = "list", $id = null) { + // managment of bank account used for money transfert + $page->setTitle('Administration - Paiements - RIBs'); + $page->assign('title', "Liste des RIBs"); + + $table_editor = new PLTableEditor("admin/payments/bankaccounts",'payment_bankaccounts','id'); + $table_editor->describe('asso_id','ID du groupe',false); + $table_editor->describe('owner','titulaire',true); + $table_editor->add_option_table('groups','groups.id = t.asso_id'); + $table_editor->add_option_field('groups.diminutif', 'group_name', 'groupe', 'varchar','account'); + + // check RIB key + if ($action == "update" && Post::has("account") && !check_rib(Post::v("account"))) { + $page->trigError("Le RIB n'est pas valide"); + $table_editor->apply($page, "edit", $id); + return; + } + + $table_editor->apply($page, $action, $id); + } + + function handler_adm_methods(&$page, $action = "list", $id = null) { + // show and edit payment methods + $page->setTitle('Administration - Paiements - Méthodes'); + $page->assign('title', "Méthodes de paiement"); + $table_editor = new PLTableEditor("admin/payments/methods",'payment_methods','id'); + $table_editor->apply($page, $action, $id); + } + + function handler_adm_reconcile(&$page, $step = 'list', $param = null) { + // reconciles logs with transactions + // FIXME: the admin is considered to be fair => he doesn't hack the $step value, nor other params + $page->setTitle('Administration - Paiements - Réconciliations'); + $page->changeTpl('payment/reconcile.tpl'); + $page->assign('step', $step); + + if (substr($step,0,4) != 'step') { + // clean up + unset($_SESSION['paymentrecon_method']); + unset($_SESSION['paymentrecon_data']); + unset($_SESSION['paymentrecon_id']); + + } elseif (isset($_SESSION['paymentrecon_data'])) { + // create temporary table with imported data + XDB::execute("CREATE TEMPORARY TABLE payment_tmp ( + reference VARCHAR(255) PRIMARY KEY, + date DATE, + amount DECIMAL(9,2), + commission DECIMAL(9,2) + )"); + foreach ($_SESSION['paymentrecon_data'] as $i) + XDB::execute("INSERT INTO payment_tmp VALUES ({?},{?},{?},{?})", + $i['reference'], $i['date'], $i['amount'], $i['commission']); + } + + if ($step == 'list' || $step == 'delete' || $step == 'edit' || $step == 'step5') { + // actions + if ($step == 'delete' && $param != null) { + S::assert_xsrf_token(); + XDB::execute("DELETE FROM payment_reconcilations WHERE id={?}", $param); + // FIXME: hardcoding !!! + XDB::execute("UPDATE payment_transactions SET recon_id=NULL,commission=NULL WHERE recon_id={?} AND method_id=2", $param); + XDB::execute("UPDATE payment_transactions SET recon_id=NULL WHERE recon_id={?} AND method_id=1", $param); + $page->trigSuccess("L'entrée ".$param." a été supprimée."); + + } elseif ($step == 'edit') { + $page->trigError("L'édition n'est pas implémentée."); + + } elseif ($step == 'step5') { + $page->trigSuccess("La réconciliation est terminée. Il est maintenant nécessaire de générer les virements."); + } + + // show list of reconciliations, with a "add" button + $page->assign('title', "Réconciliation - Liste"); + $table_editor = new PLTableEditor("admin/payments/reconcile",'payment_reconcilations','id'); + $table_editor->describe('payment_count','transactions',true); + $table_editor->describe('period_start','from',true); + $table_editor->describe('period_end','to',true); + $table_editor->describe('sum_amounts','total (€)',true); + $table_editor->describe('sum_commissions','coms (€)',true); + $table_editor->describe('comments','comments',false); + $table_editor->apply($page, 'list'); + + } elseif ($step == 'new' || $step == 'massadd') { + pl_redirect("admin/payments/reconcile/step1"); + + } elseif ($step == 'step1') { + $page->assign('title', "Étape 1"); + + // was a payment method choosen ? + if ($param != null) { + $_SESSION['paymentrecon_method'] = (int)$param; + pl_redirect("admin/payments/reconcile/step2"); + + } else { + // ask to choose a payment method + $res = XDB::query("SELECT id, text FROM payment_methods"); + $page->assign('methods', $res->fetchAllAssoc()); + } + + } elseif ( $step == 'step2' ) { + $page->assign('title', "Étape 2"); + + // import logs formated in CVS + $fields = array('date','reference','amount','commission'); + $importer = new PaymentLogsImporter(); + $importer->apply($page, 'admin/payments/reconcile/step2', $fields); + + // if import is finished + $result = $importer->get_result(); + if($result != null) { + $_SESSION['paymentrecon_data'] = $result; + pl_redirect("admin/payments/reconcile/step3"); + } + + } elseif ($step == 'step3' ) { + $page->assign('title', "Étape 3"); + + // compute reconcilation summary data + $res = XDB::query("SELECT MIN(date) AS period_start, MAX(date) AS period_end, + count(*) AS payment_count, SUM(amount) AS sum_amounts, + SUM(commission) AS sum_commissions + FROM payment_tmp"); + $recon = $res->fetchOneAssoc(); + $recon['method_id'] = $_SESSION['paymentrecon_method']; + + // create reconciliation item in database + if(Post::has('next')) { + S::assert_xsrf_token(); + + // get parameters + $recon['period_start'] = preg_replace('/([0-9]{1,2})\/([0-9]{1,2})\/([0-9]{4})/','\3-\2-\1',Post::v('period_start')); + $recon['period_end'] = preg_replace('/([0-9]{1,2})\/([0-9]{1,2})\/([0-9]{4})/','\3-\2-\1',Post::v('period_end')); + // FIXME: save checks to be done at next step + + // Create reconcilation item in database + // FIXME: check if period doesn't overlap with others for the same method_id + XDB::execute("INSERT INTO payment_reconcilations (method_id, period_start, period_end, + payment_count, sum_amounts, sum_commissions) + VALUES ({?}, {?}, {?}, {?}, {?}, {?})", + $recon['method_id'], $recon['period_start'], $recon['period_end'], + $recon['payment_count'], $recon['sum_amounts'], $recon['sum_commissions']); + $_SESSION['paymentrecon_id'] = XDB::insertId(); + + // reconcile simple cases (trans.commission n'est modifié que s'il vaut NULL) + XDB::execute("UPDATE payment_transactions AS trans, payment_tmp AS tmp + SET trans.recon_id={?}, trans.commission=tmp.commission + WHERE trans.fullref=tmp.reference + AND trans.amount=tmp.amount AND DATE(trans.ts_confirmed)=tmp.date + AND (trans.commission IS NULL OR trans.commission=tmp.commission) + AND method_id={?} AND recon_id IS NULL AND status='confirmed'", + $_SESSION['paymentrecon_id'], $recon['method_id']); + + pl_redirect("admin/payments/reconcile/step4"); + + // show summary of the imported data + ask form start/end of reconcilation period + } else { + $recon['period_start'] = preg_replace('/([0-9]{4})-([0-9]{2})-([0-9]{2})/', '\3/\2/\1', $recon['period_start']); + $recon['period_end'] = preg_replace('/([0-9]{4})-([0-9]{2})-([0-9]{2})/', '\3/\2/\1', $recon['period_end']); + $page->assign('recon', $recon); + } + + } elseif ($step == 'step4' ) { + $page->assign('title', "Étape 4"); + + // get reconcilation summary informations + $res = XDB::query("SELECT * FROM payment_reconcilations WHERE id={?}", $_SESSION['paymentrecon_id']); + $recon = $res->fetchOneAssoc(); + $page->assign('recon', $recon); + + if (Post::has('force')) { + S::assert_xsrf_token(); + foreach (Post::v('force') as $id => $value) { + XDB::execute("UPDATE payment_transactions AS trans, payment_tmp AS tmp + SET trans.recon_id={?}, trans.commission=tmp.commission + WHERE trans.id={?} AND trans.fullref=tmp.reference", + $_SESSION['paymentrecon_id'], $id); + } + $page->trigSuccess('La réconciliation a été forcée pour '.count(Post::v('force')).' transaction(s).'); + + } elseif (Post::has('next')) { + if (strlen($recon['comments'])<3) { + $page->trigError("Le commentaire doit contenir au moins 3 caractères."); + } else { + XDB::execute("UPDATE payment_reconcilations SET status='transfering' WHERE id={?}", $_SESSION['paymentrecon_id']); + pl_redirect('admin/payments/reconcile/step5'); + } + + } elseif (Post::has('savecomments')) { + S::assert_xsrf_token(); + $recon['comments'] = Post::v('comments'); + $page->assign('recon', $recon); + XDB::execute("UPDATE payment_reconcilations SET comments={?} WHERE id={?}", $recon['comments'], $_SESSION['paymentrecon_id']); + $page->trigSuccess('Les commentaires ont été enregistrés.'); + } + + // reconcilation results - ok + $res = XDB::query("SELECT count(*),SUM(amount),SUM(commission) + FROM payment_transactions WHERE recon_id={?}", + $recon['id']); + list($ok_count,$ok_sum_amounts,$ok_sum_coms) = $res->fetchOneRow(); + $page->assign('ok_count', $ok_count); + + // reconcilation results - ref exists, but some data differs + $res = XDB::query("SELECT id, fullref, method_id, ts_confirmed, trans.amount, trans.commission, status, recon_id, + reference, date, tmp.amount as amount2, tmp.commission as commission2 + FROM payment_transactions AS trans + INNER JOIN payment_tmp AS tmp ON trans.fullref=tmp.reference + WHERE trans.recon_id IS NULL OR trans.recon_id != {?}", + $recon['id']); + $differs = $res->fetchAllAssoc(); + $page->assign_by_ref('differs', $differs); + $page->assign('differ_count', count($differs)); + + // reconcilation results - ref doesn't exists in database + $res = XDB::query("SELECT tmp.* + FROM payment_tmp AS tmp + LEFT JOIN payment_transactions AS trans ON trans.fullref=tmp.reference + WHERE trans.fullref IS NULL"); + $only_import = $res->fetchAllAssoc(); + $page->assign_by_ref('only_import', $only_import); + $page->assign('onlyim_count', count($only_import)); + + // reconcilation results - exists in database but not in import + $res = XDB::query("SELECT trans.* + FROM payment_transactions AS trans + LEFT JOIN payment_tmp AS tmp ON trans.fullref=tmp.reference + WHERE {?}<=DATE(trans.ts_confirmed) AND DATE(trans.ts_confirmed)<={?} + AND tmp.reference IS NULL AND method_id={?}", + $recon['period_start'], $recon['period_end'], $recon['method_id']); + $only_database = $res->fetchAllAssoc(); + $page->assign_by_ref('only_database', $only_database); + $page->assign('onlydb_count', count($only_database)); + + } else { + $page->trigError('Bad parameters.'); + } + } + + function handler_adm_transferts(&$page) { + // list/log all bank transferts and link them to individual transactions + } +} + +class PaymentLogsImporter extends CSVImporter { + protected $result; + + public function __construct() { + parent::__construct(''); + $this->registerFunction('systempay_commission', 'Compute BPLC commission', array($this,"compute_systempay_commission")); + $this->registerFunction('payment_id', 'Autocompute payment ID', array($this,"compute_payment_id")); + //$this->forceValue('payment_id','func_payment_id'); + } + + public function run($action = null, $insert_relation = null, $update_relation = null) { + $this->result = array(); + foreach ($this->data as $line) { + $a = $this->makeAssoc($line, $insert_relation); + $a['date'] = preg_replace('/([0-9]{2})\/([0-9]{2})\/([0-9]{4}) .*/','\3-\2-\1', $a['date']); + $this->result[] = $a; + } + } + + public function get_result() { + return $this->result; + } + + static public function compute_systempay_commission($line, $key, $relation) { + if($key!='commission' || !array_key_exists('carte',$line)) return null; + $amount = self::getValue($line, 'amount', $relation['amount']); + if ($line['carte'] == 'CB') + return 0.20 + round($amount*0.005, 2); + else + return 0.20 + round($amount*0.005, 2) + 0.76; + } + + static public function compute_payment_id($line, $key, $relation) { + if ($key != 'payment_id') return null; + $reference = self::getValue($line, 'reference', $relation['reference']); + if (ereg('-([0-9]+)$', $reference, $matches)) + return $matches[1]; + else + return null; + } } // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: diff --git a/templates/payment/reconcile.tpl b/templates/payment/reconcile.tpl new file mode 100644 index 0000000..75a9d37 --- /dev/null +++ b/templates/payment/reconcile.tpl @@ -0,0 +1,218 @@ +{**************************************************************************} +{* *} +{* Copyright (C) 2003-2010 Polytechnique.org *} +{* http://opensource.polytechnique.org/ *} +{* *} +{* This program is free software; you can redistribute it and/or modify *} +{* it under the terms of the GNU General Public License as published by *} +{* the Free Software Foundation; either version 2 of the License, or *} +{* (at your option) any later version. *} +{* *} +{* This program is distributed in the hope that it will be useful, *} +{* but WITHOUT ANY WARRANTY; without even the implied warranty of *} +{* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *} +{* GNU General Public License for more details. *} +{* *} +{* You should have received a copy of the GNU General Public License *} +{* along with this program; if not, write to the Free Software *} +{* Foundation, Inc., *} +{* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *} +{* *} +{**************************************************************************} + +

Réconciliation - {$title}

+ +{if $step eq 'step1'} + + + + + + {foreach from=$methods item=method} + + + + {/foreach} +
Choix de la méthode de paiement
+ {$method.text} +
+ +{elseif $step eq 'step2'} + +{include core=csv-importer.tpl} + +{elseif $step eq 'step3'} + +
+ {xsrf_token_field} + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
Récapitulatif des informations de réconciliation
ID de la méthode de paiement:{$recon.method_id}
Début de période : + + jj/mm/aaaa +
Fin de période : + + jj/mm/aaaa +
Nombre de transactions :{$recon.payment_count}
Total des paiements :{$recon.sum_amounts|string_format:"%.2f"|replace:'.':','}€(environ {$recon.sum_amounts/$recon.payment_count|string_format:"%.2f"|replace:'.':','}€/paiement)
Total des commissions :{$recon.sum_commissions|string_format:"%.2f"|replace:'.':','}€(environ {$recon.sum_commissions/$recon.sum_amounts*100|string_format:"%.2f"|replace:'.':','}%)
+ +
+ + + + + + + + + +
À l'étape suivante, une comparaison entre les transactions existantes et la liste importé va être réalisée.
Vérification à faire : +
+
+
+
+ +

+
+ +{elseif $step eq 'step4'} + +

ok : {$ok_count}
+differ : {$differ_count}
+onlydb : {$onlydb_count}
+onlyim : {$onlyim_count}
+total (excepted onlydb) : {$ok_count+$differ_count+$onlyim_count} (doit être égal à {$recon.payment_count}) +

+ +

Enregistrements avec champs qui diffèrent

+ +{if $differ_count ne 0} + + + + + + +{foreach from=$differs item=i} + + + + + + + + + + +{/foreach} +
Référencemethod_idDateMontantComStatutrecon_idAction
{$i.fullref}
{$i.reference}		{$i.method_id}
 		{$i.ts_confirmed}
{$i.date}		{$i.amount}
{$i.amount2}		{$i.commission}
{$i.commission2}		{$i.status}
 		{$i.recon_id}
 
{xsrf_token_field}
+{else} +

Aucun

+{/if} + +

Enregistrements uniquement dans la base

+ +{if $onlydb_count ne 0} + +{assign var='headerstatus' value='doheader'} +{foreach from=$only_database item=i} + {if $headerstatus eq 'doheader'} + {assign var='headerstatus' value='headerdone'} + + {foreach from=$i key=k item=v} + + {/foreach} + + {/if} + + {foreach from=$i key=k item=v} + + {/foreach} + +{/foreach} +
{$k}
{$v}
+{else} +

Aucun

+{/if} + +

Enregistrements uniquement dans l'import

+ +{if $onlyim_count ne 0} + +{assign var='headerstatus' value='doheader'} +{foreach from=$only_import item=i} + {if $headerstatus eq 'doheader'} + {assign var='headerstatus' value='headerdone'} + + {foreach from=$i key=k item=v} + + {/foreach} + + {/if} + + {foreach from=$i key=k item=v} + + {/foreach} + +{/foreach} +
{$k}
{$v}
+{else} +

Aucun

+{/if} + +

Commentaires

+ +

Les tableaux si dessus ne seront pas enregistrés, il convient donc de reprendre leur contenu dans +le champ de commentaires si dessous, si nécesssaire.

+ +
+{xsrf_token_field} + +

+
+ +

Suite

+ +
+

+
+ +{else} {* defaults to "list" *} +{assign var='dontshowback' value='dontshowback'} + +TODO: listing + +{/if} + +{if $dontshowback} +

+back +

+{/if} diff --git a/upgrade/1.0.1/payments.sql b/upgrade/1.0.1/payments.sql new file mode 100644 index 0000000..6c251cc --- /dev/null +++ b/upgrade/1.0.1/payments.sql @@ -0,0 +1,87 @@ +CREATE TABLE payment_bankaccounts ( + id integer PRIMARY KEY auto_increment, + asso_id integer NOT NULL, + account varchar(23) NOT NULL, + owner varchar(100) NOT NULL, + status set('new','used','old') NOT NULL default 'new' +); + +#INSERT INTO payment_bankaccounts VALUES (NULL,,"30002004690000008524R29","Amis de l'Espace Dirigea","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,239,"10207001172019602580784","ASCCX","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,42,"14707000010892101692291","AX (BPLC)","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,42,"30066109310001022770164","AX (CIC)","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,42,"30002004200000009372U74","AX (LCL)","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,31,"10107001820002105034095","Binet Point Gamma","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,73,"30003020600003729601589","GTX","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,246,"20041000012241609M02035","Humanix - Jacques Bellev","used"); +#INSERT INTO payment_bankaccounts VALUES (NULL,,"10107001820092105033751","Kes des élèves","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,214,"30003022160005198020072","Khomiss (Aurélien Lajoie","used"); +#INSERT INTO payment_bankaccounts VALUES (NULL,,"30003021900002011521283","Maison des X","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,181,"10107001820012105055968","Raid Polytechnique 2004","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,165,"20041010123576371A03369","Sabix","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,11,"30002089410000023857R03","X-eConfiance 'Mathieu Be","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,251,"30003022200005041343575","X-Achats 'Francois Rena","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,252,"30002008190000045217G86","X-Automobile 'F. Tronel","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,6,"30002005940000434521B52","X-Aviation 'Francis Fouq","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,96,"30003041110003726598647","X-Biotech (M.O.Bevierre)","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,57,"15589335720697076254012","X-Bordelais (T Leblond)","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,4,"30003005080003728293253","X-Consult","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,18,"30066100210001067980188","X-Environnement (P Worbe","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,3,"30003031900005066357935","X-Finance - Ariane Chaze","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,7,"30002004200000009372U74","X-Gaziers - Compte AX LC","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,21,"30588610978071800010189","X-Golf (Guy Marchand)","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,202,"30003034210005003887246","X-HEC CapInvest (A Santo","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,174,"30002006840000005831S15","X-Mer","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,166,"30066108700001028630170","X-Mines au Feminin","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,219,"30002004200000009372U74","X-Nucleaire - Compte AX","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,82,"30003038320005055982303","X-Pierre (Quoc-Giao Tran","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,233,"30002004200000009372U74","X-PI - Compte AX LCL","used"); +INSERT INTO payment_bankaccounts VALUES (NULL,248,"12548029983443030151039","X-Renouvelables 'Jerome","used"); +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"); + +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, + 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 + sum_commissions DECIMAL(9,2) NOT NULL, + comments text NOT NULL +); + +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; + +CREATE TABLE payment_transfers ( + id integer PRIMARY KEY auto_increment, + payment_id integer NOT NULL, + amount DECIMAL(9,2) NOT NULL, + message VARCHAR(255) NOT NULL, + date DATE # NULL = not done +); + +CREATE TABLE payment_recon_transfer ( + recon_id INTEGER NOT NULL, + transfer_id INTEGER NOT NULL, + PRIMARY KEY (recon_id,transfer_id) +); -- 2.1.4