2 /***************************************************************************
3 * Copyright (C) 2003-2010 Polytechnique.org *
4 * http://opensource.polytechnique.org/ *
6 * This program is free software; you can redistribute it and/or modify *
7 * it under the terms of the GNU General Public License as published by *
8 * the Free Software Foundation; either version 2 of the License, or *
9 * (at your option) any later version. *
11 * This program is distributed in the hope that it will be useful, *
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of *
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
14 * GNU General Public License for more details. *
16 * You should have received a copy of the GNU General Public License *
17 * along with this program; if not, write to the Free Software *
19 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
20 ***************************************************************************/
22 /* sort en affichant une erreur */
23 function cb_erreur($text) {
26 $mymail = new PlMailer();
27 $mymail->addTo($globals->money
->email
);
28 $mymail->setFrom("webmaster@" . $globals->mail
->domain
);
29 $mymail->setSubject("erreur lors d'un télépaiement (CyberPaiement)");
30 $mymail->setTxtBody("\n\n".var_export($_REQUEST,true
));
32 echo "Notification sent.\n";
36 /* sort en affichant une erreur */
37 function paypal_erreur($text, $send=true
)
39 global $erreur, $globals;
44 $mymail = new PlMailer();
45 $mymail->addTo($globals->money
->email
);
46 $mymail->setFrom("webmaster@" . $globals->mail
->domain
);
47 $mymail->setSubject("erreur lors d'un télépaiement (PayPal)");
48 $mymail->setTxtBody("\n\n".var_export($_REQUEST,true
));
51 Platal
::page()->trigError($text);
54 /* http://fr.wikipedia.org/wiki/Formule_de_Luhn */
55 function luhn($nombre) {
58 for ($i = 0; $i < strlen($s); $i++
) {
60 $sum +
= ($i %
2) ?
(2*$dgt) %
9 : $dgt;
65 /* calcule la clé d'acceptation a partir de 5 champs */
66 function cle_accept($d1,$d2,$d3,$d4,$d5)
72 $n = $m1 +
$m2 +
$m3 +
$m4;
73 $alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
74 return $alpha{$n-1}.$m1.$m2.$m3.$m4;
77 /* decode the comment */
78 function comment_decode($comment) {
79 $comment = urldecode($comment);
80 if (is_utf8($comment)) {
83 return utf8_encode($comment);
87 /* check if a RIB account number is valid */
88 function check_rib($rib)
90 if(strlen($rib) != 23) return false
;
93 $rib = strtr(strtoupper($rib),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','12345678912345678923456789');
94 $bank = substr($rib,0,5);
95 $counter = substr($rib,5,5);
96 $account = substr($rib,10,11);
97 $key = substr($rib,21,2);
100 return 0 == fmod(89 * $bank +
15 * $counter +
3 * $account +
$key, 97);
103 class PaymentModule
extends PLModule
108 'payment' => $this->make_hook('payment', AUTH_MDP
, 'payment'),
109 'payment/cyber_return' => $this->make_hook('cyber_return', AUTH_PUBLIC
, 'user', NO_HTTPS
),
110 'payment/cyber2_return' => $this->make_hook('cyber2_return', AUTH_PUBLIC
, 'user', NO_HTTPS
),
111 'payment/paypal_return' => $this->make_hook('paypal_return', AUTH_PUBLIC
, 'user', NO_HTTPS
),
112 '%grp/paiement' => $this->make_hook('xnet_payment', AUTH_MDP
),
113 '%grp/payment' => $this->make_hook('xnet_payment', AUTH_MDP
),
114 '%grp/payment/cyber_return' => $this->make_hook('cyber_return', AUTH_PUBLIC
, 'user', NO_HTTPS
),
115 '%grp/payment/cyber2_return' => $this->make_hook('cyber2_return', AUTH_PUBLIC
, 'user', NO_HTTPS
),
116 '%grp/payment/paypal_return' => $this->make_hook('paypal_return', AUTH_PUBLIC
, 'user', NO_HTTPS
),
117 'admin/payments' => $this->make_hook('admin', AUTH_MDP
, 'admin'),
118 'admin/payments/methods' => $this->make_hook('adm_methods', AUTH_MDP
, 'admin'),
119 'admin/payments/transactions'=> $this->make_hook('adm_transactions', AUTH_MDP
, 'admin'),
120 'admin/payments/reconcile' => $this->make_hook('adm_reconcile', AUTH_MDP
, 'admin'),
121 'admin/payments/bankaccounts'=> $this->make_hook('adm_bankaccounts', AUTH_MDP
, 'admin'),
125 function handler_payment(&$page, $ref = -1)
129 $this->load('money.inc.php');
131 if (!empty($GLOBALS['IS_XNET_SITE'])) {
132 if (!$globals->asso('id')) {
135 $res = XDB
::query("SELECT asso_id
137 WHERE asso_id = {?} AND id = {?}",
138 $globals->asso('id'), $ref);
139 if (!$res->numRows()) {
143 $page->changeTpl('payment/index.tpl');
144 $page->setTitle('Télépaiements');
147 $op = Env
::v('op', 'select');
148 $meth = new PayMethod(Env
::i('methode', -1));
149 $pay = new Payment($ref);
151 if($pay->flags
->hasflag('old')){
152 $page->trigError("La transaction selectionnée est périmée.");
153 $pay = new Payment();
155 $val = Env
::v('montant') != 0 ? Env
::v('montant') : $pay->amount_def
;
157 if (($e = $pay->check($val)) !== true
) {
158 $page->trigError($e);
162 $pay->init($val, $meth);
163 $pay->prepareform($pay);
165 $res = XDB
::iterator("SELECT timestamp, amount
166 FROM payment_transactions
167 WHERE uid = {?} AND ref = {?}
168 ORDER BY timestamp DESC",
169 S
::v('uid', -1), $ref);
171 if ($res->total()) $page->assign('transactions', $res);
174 $val = floor($val).".".substr(floor(($val - floor($val))*100+
100),1);
175 $page->assign('montant',$val);
176 $page->assign('comment',Env
::v('comment'));
178 $page->assign('meth', $meth);
179 $page->assign('pay', $pay);
180 $page->assign('evtlink', $pay->event());
183 function handler_cyber_return(&$page, $uid = null
)
185 /* reference banque (numero de transaction) */
186 $champ901 = Env
::s('CHAMP901');
187 /* cle d'acceptation */
188 $champ905 = Env
::s('CHAMP905');
190 $champ906 = Env
::s('CHAMP906');
191 /* email renvoye par la banque */
192 $champ104 = Env
::s('CHAMP104');
193 /* reference complete de la commande */
194 $champ200 = Env
::s('CHAMP200');
195 /* montant de la transaction */
196 $champ201 = Env
::s('CHAMP201');
198 $champ202 = Env
::s('CHAMP202');
199 $montant = "$champ201 $champ202";
201 /* on extrait les informations sur l'utilisateur */
202 $user = User
::get($uid);
204 cb_erreur("uid invalide");
208 /* on extrait la reference de la commande */
209 if (!ereg('-xorg-([0-9]+)$', $champ200, $matches)) {
210 cb_erreur("référence de commande invalide");
213 echo ($ref = $matches[1]);
214 $res = XDB
::query("SELECT mail, text, confirmation
216 WHERE id={?}", $ref);
217 if (!list($conf_mail, $conf_title, $conf_text) = $res->fetchOneRow()) {
218 cb_erreur("référence de commande inconnue");
221 /* on extrait le code de retour */
222 if ($champ906 != "0000") {
223 $res = XDB
::query('SELECT rcb.text, c.id, c.text
224 FROM payment_codeRCB AS rcb
225 LEFT JOIN payment_codeC AS c ON (rcb.codeC = c.id)
226 WHERE rcb.id = {?}', $champ906);
227 if (list($rcb_text, $c_id, $c_text) = $res->fetchOneRow()) {
228 cb_erreur("erreur lors du paiement : $c_text ($c_id)");
230 cb_erreur("erreur inconnue lors du paiement");
234 /* on fait l'insertion en base de donnees */
235 XDB
::execute("INSERT INTO payment_transactions (id, uid, ref, fullref, amount, pkey, comment)
236 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?})",
237 $champ901, $user->id(), $ref, $champ200, $montant, $champ905, Env
::v('comment'));
239 // We check if it is an Xnet payment and then update the related ML.
240 $res = XDB
::query('SELECT eid
242 WHERE paiement_id = {?}', $ref);
243 if ($eid = $res->fetchOneCell()) {
244 require_once dirname(__FILE__
) . '/xnetevents/xnetevents.inc.php';
245 $evt = get_event_detail($eid);
246 subscribe_lists_event($uid, $evt, 1, $montant, true
);
249 /* on genere le mail de confirmation */
250 $conf_text = str_replace(
251 array('<prenom>', '<nom>', '<promo>', '<montant>', '<salutation>', '<cher>', 'comment>'),
252 array($user->firstName(), $user->lastName(), $user->promo(), $montant,
253 $user->isFemale() ?
'Chère' : 'Cher', $user->isFemale() ?
'Chère' : 'Cher',
254 Env
::v('comment')), $conf_text);
257 $mymail = new PlMailer();
258 $mymail->setFrom($conf_mail);
259 $mymail->addCc($conf_mail);
260 $mymail->setSubject($conf_title);
261 $mymail->setWikiBody($conf_text);
262 $mymail->sendTo($user);
264 /* on envoie les details de la transaction à telepaiement@ */
265 $mymail = new PlMailer();
266 $mymail->setFrom("webmaster@" . $globals->mail
->domain
);
267 $mymail->addTo($globals->money
->email
);
268 $mymail->setSubject($conf_title);
269 $msg = 'utilisateur : ' . $user->login() . ' (' . $user->id() . ')' . "\n" .
270 'mail : ' . $user->forlifeEmail() . "\n\n" .
271 "paiement : $conf_title ($conf_mail)\n".
272 "reference : $champ200\n".
273 "montant : $montant\n\n".
274 "dump de REQUEST:\n".
275 var_export($_REQUEST,true
);
276 $mymail->setTxtBody($msg);
281 function handler_cyber2_return(&$page, $uid = null
)
283 global $globals, $platal;
285 /* on vérifie la signature */
286 $vads_params = array();
287 foreach($_REQUEST as $key => $value)
288 if(substr($key,0,5) == "vads_")
289 $vads_params[$key] = $value;
291 $signature = sha1(join('+',$vads_params).'+'.$globals->money
->cyperplus_key
);
292 //if($signature != Env::v('signature')) {
293 // cb_erreur("signature invalide");
296 /* on extrait les informations sur l'utilisateur */
297 $user = User
::get(Env
::v('vads_cust_id'));
299 cb_erreur("uid invalide");
302 /* on extrait la reference de la commande */
303 if (!ereg('-([0-9]+)$', Env
::v('vads_order_id'), $matches)) {
304 cb_erreur("référence de commande invalide");
308 $res = XDB
::query("SELECT mail, text, confirmation
310 WHERE id={?}", $ref);
311 if (!list($conf_mail, $conf_title, $conf_text) = $res->fetchOneRow()) {
312 cb_erreur("référence de commande inconnue");
315 /* on extrait le montant */
316 if (Env
::v('vads_currency') != "978") {
317 cb_erreur("monnaie autre que l'euro");
319 $montant = sprintf("%.02f", ((float)Env
::v('vads_amount'))/100) . " EUR";
321 /* on extrait le code de retour */
322 if (Env
::v('vads_result') != "00") {
323 cb_erreur("erreur lors du paiement : ?? (".Env
::v('vads_result').")");
326 /* on fait l'insertion en base de donnees */
327 XDB
::execute("INSERT INTO payment_transactions (id, uid, ref, fullref, amount, pkey, comment)
328 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?})",
329 Env
::v('vads_trans_date'), $user->id(), $ref, Env
::v('vads_order_id'), $montant, "", Env
::v('vads_order_info'));
330 echo "Paiement stored.\n";
332 // We check if it is an Xnet payment and then update the related ML.
333 $res = XDB
::query('SELECT eid
335 WHERE paiement_id = {?}', $ref);
336 if ($eid = $res->fetchOneCell()) {
337 require_once dirname(__FILE__
) . '/xnetevents/xnetevents.inc.php';
338 $evt = get_event_detail($eid);
339 subscribe_lists_event($user->id(), $evt, 1, $montant, true
);
342 /* on genere le mail de confirmation */
343 $conf_text = str_replace(
344 array('<prenom>', '<nom>', '<promo>', '<montant>', '<salutation>', '<cher>', 'comment>'),
345 array($user->firstName(), $user->lastName(), $user->promo(), $montant,
346 $user->isFemale() ?
'Chère' : 'Cher', $user->isFemale() ?
'Chère' : 'Cher',
347 Env
::v('comment')), $conf_text);
350 $mymail = new PlMailer();
351 $mymail->setFrom($conf_mail);
352 $mymail->addCc($conf_mail);
353 $mymail->setSubject($conf_title);
354 $mymail->setWikiBody($conf_text);
355 $mymail->sendTo($user);
357 /* on envoie les details de la transaction à telepaiement@ */
358 $mymail = new PlMailer();
359 $mymail->setFrom("webmaster@" . $globals->mail
->domain
);
360 $mymail->addTo($globals->money
->email
);
361 $mymail->setSubject($conf_title);
362 $msg = 'utilisateur : ' . $user->login() . ' (' . $user->id() . ')' . "\n" .
363 'mail : ' . $user->forlifeEmail() . "\n\n" .
364 "paiement : $conf_title ($conf_mail)\n".
365 "reference : " . Env
::v('vads_order_id') . "\n".
366 "montant : $montant\n\n".
367 "dump de REQUEST:\n".
368 var_export($_REQUEST,true
);
369 $mymail->setTxtBody($msg);
371 echo "Notifications sent.\n";
375 function handler_paypal_return(&$page, $uid = null
)
377 $page->changeTpl('payment/retour_paypal.tpl');
379 /* reference banque (numero de transaction) */
380 $no_transaction = Env
::s('tx');
381 /* token a renvoyer pour avoir plus d'information */
382 $clef = Env
::s('sig');
384 $status = Env
::s('st');
386 $reason = ($status == 'Pending')? Env
::s('pending_reason'): Env
::s('reason_code');
387 /* reference complete de la commande */
388 $fullref = Env
::s('cm');
389 /* montant de la transaction */
390 $montant_nb = Env
::s('amt');
392 $montant_dev = Env
::s('cc');
393 $montant = "$montant_nb $montant_dev";
395 /* on extrait le code de retour */
396 if ($status != "Completed") {
398 paypal_erreur("erreur lors du paiement : $status - $reason");
400 paypal_erreur("Paiement annulé", false
);
403 /* on extrait les informations sur l'utilisateur */
404 $user = User
::get($uid);
406 paypal_erreur("uid invalide");
409 /* on extrait la reference de la commande */
410 if (!ereg('-xorg-([0-9]+)$', $fullref, $matches)) {
411 paypal_erreur("référence de commande invalide");
415 $res = XDB
::query("SELECT mail, text, confirmation
417 WHERE id = {?}", $ref);
418 if (!list($conf_mail,$conf_title,$conf_text) = $res->fetchOneRow()) {
419 paypal_erreur("référence de commande inconnue");
422 /* on fait l'insertion en base de donnees */
423 XDB
::execute("INSERT INTO payment_transactions (id, uid, ref, fullref, amount, pkey, comment)
424 VALUES ({?}, {?}, {?}, {?}, {?}, {?}, {?})",
425 $no_transaction, $user->id(), $ref, $fullref, $montant, $clef, Env
::v('comment'));
427 // We check if it is an Xnet payment and then update the related ML.
428 $res = XDB
::query('SELECT eid
430 WHERE paiement_id = {?}', $ref);
431 if ($eid = $res->fetchOneCell()) {
432 require_once dirname(__FILE__
) . '/xnetevents/xnetevents.inc.php';
433 $evt = get_event_detail($eid);
434 subscribe_lists_event($user->id(), $evt, 1, $montant, true
);
437 /* on genere le mail de confirmation */
438 $conf_text = str_replace(array('<prenom>', '<nom>', '<promo>', '<montant>', '<salutation>', '<cher>'),
439 array($user->firstName(), $user->lastName(), $user->promo(), $montant,
440 $user->isFemale() ?
'Chère' : 'Cher',
441 $user->isFemale() ?
'Chère' : 'Cher'), $conf_text);
444 $mymail = new PlMailer();
445 $mymail->setFrom($conf_mail);
446 $mymail->addCc($conf_mail);
447 $mymail->setSubject($conf_title);
448 $mymail->setWikiBody($conf_text);
449 $mymail->sendTo($user);
451 /* on envoie les details de la transaction à telepaiement@ */
452 $mymail = new PlMailer();
453 $mymail->setFrom("webmaster@" . $globals->mail
->domain
);
454 $mymail->addTo($globals->money
->email
);
455 $mymail->setSubject($conf_title);
456 $msg = 'utilisateur : ' . $user->login() . ' (' . $user->id() . ')' . "\n" .
457 'mail : ' . $user->forlifeEmail() . "\n\n" .
458 "paiement : $conf_title ($conf_mail)\n".
459 "reference : $champ200\n".
460 "montant : $montant\n\n".
461 "dump de REQUEST:\n".
462 var_export($_REQUEST,true
);
463 $mymail->setTxtBody($msg);
466 $page->assign('texte', $conf_text);
467 $page->assign('erreur', $erreur);
470 function handler_xnet_payment(&$page, $pid = null
)
474 $perms = S
::v('perms');
475 if (!$perms->hasFlag('groupmember')) {
479 $res = XDB
::query("SELECT 1
480 FROM group_events AS e
481 INNER JOIN group_event_participants AS ep ON (ep.eid = e.eid AND uid = {?})
482 WHERE e.paiement_id = {?} AND e.asso_id = {?}",
483 S
::i('uid'), $pid, $globals->asso('id'));
484 if ($res->numRows() == 0) {
489 if (!is_null($pid)) {
490 return $this->handler_payment($page, $pid);
492 $page->changeTpl('payment/xnet.tpl');
495 "SELECT id, text, url
497 WHERE asso_id = {?} AND NOT FIND_IN_SET('old', flags)
498 ORDER BY id DESC", $globals->asso('id'));
499 $tit = $res->fetchAllAssoc();
500 $page->assign('titres', $tit);
503 // TODO: replug sort.
506 foreach($tit as $foo) {
509 $res = XDB
::query('SELECT t.uid, timestamp AS `date`, t.comment, amount
510 FROM payment_transactions AS t
511 WHERE t.ref = {?}', $pid);
512 $trans[$pid] = User
::getBulkUsersWithUIDs($res->fetchAllAssoc(), 'uid', 'user');
514 foreach ($trans[$pid] as $i => $t) {
515 $sum +
= strtr(substr($t['amount'], 0, strpos($t['amount'], 'EUR')), ',', '.');
516 $trans[$pid][$i]['amount'] = str_replace('EUR', '€', $t['amount']);
518 $trans[$pid][] = array('nom' => 'somme totale',
519 'amount' => strtr($sum, '.', ',').' €');
521 $res = XDB
::iterRow("SELECT e.eid, e.short_name, e.intitule, ep.nb, ei.montant, ep.paid
522 FROM group_events AS e
523 LEFT JOIN group_event_participants AS ep ON (ep.eid = e.eid AND uid = {?})
524 INNER JOIN group_event_items AS ei ON (ep.eid = ei.eid AND ep.item_id = ei.item_id)
525 WHERE e.paiement_id = {?}",
527 $event[$pid] = array();
528 $event[$pid]['paid'] = 0;
530 $event[$pid]['topay'] = 0;
531 while(list($eid, $shortname, $title, $nb, $montant, $paid) = $res->next()) {
532 $event[$pid]['topay'] +
= ($nb * $montant);
533 $event[$pid]['eid'] = $eid;
534 $event[$pid]['shortname'] = $shortname;
535 $event[$pid]['title'] = $title;
536 $event[$pid]['ins'] = !is_null($nb);
537 $event[$pid]['paid'] = $paid;
540 $res = XDB
::query("SELECT amount
541 FROM payment_transactions AS t
542 WHERE ref = {?} AND uid = {?}", $pid, S
::v('uid'));
543 $montants = $res->fetchColumn();
545 foreach ($montants as $m) {
546 $p = strtr(substr($m, 0, strpos($m, 'EUR')), ',', '.');
547 $event[$pid]['paid'] +
= trim($p);
550 $page->register_modifier('decode_comment', 'decode_comment');
551 $page->assign('trans', $trans);
552 $page->assign('event', $event);
555 function handler_admin(&$page, $action = 'list', $id = null
) {
556 $page->setTitle('Administration - Paiements');
557 $page->assign('title', 'Gestion des télépaiements');
558 $table_editor = new PLTableEditor('admin/payments','payments','id');
559 //$table_editor->add_join_table('payment_transactions','ref',true); => on ne supprime jamais une transaction
560 $table_editor->add_sort_field('flags');
561 $table_editor->add_sort_field('id', true
, true
);
562 $table_editor->on_delete("UPDATE payments SET flags = 'old' WHERE id = {?}", "Le paiement a été archivé");
563 $table_editor->describe('text','intitulé',true
);
564 $table_editor->describe('url','site web',false
);
565 $table_editor->describe('amount_def','montant par défaut',false
);
566 $table_editor->describe('amount_min','montant minimum',false
);
567 $table_editor->describe('amount_max','montant maximum',false
);
568 $table_editor->describe('mail','email contact',true
);
569 $table_editor->describe('confirmation','message confirmation',false
);
571 // adds a column with the start date of the linked event if there is one
572 $table_editor->add_option_table('group_events','group_events.paiement_id = t.id');
573 $table_editor->add_option_field('group_events.debut', 'related_event', 'évènement', 'timestamp');
575 $table_editor->apply($page, $action, $id);
578 function handler_adm_transactions(&$page, $payment_id = null
, $action = "list", $id = null
) {
579 // show transactions. FIXME: should not be modifiable
580 $page->setTitle('Administration - Paiements - Transactions');
581 $page->assign('title', "Liste des transactions pour le paiement {$payment_id}");
583 if ($payment_id == null
)
584 $page->trigError("Aucun ID de paiement fourni.");
586 $table_editor = new PLTableEditor("admin/transactions/{$payment_id}",'payment_transactions','id');
587 $table_editor->set_where_clause(XDB
::format('ref = {?}', $payment_id));
588 $table_editor->apply($page, 'list', $id); // only the 'list' action is allowed
589 $page->assign("readonly","readonly"); // don't show modification features
592 function handler_adm_bankaccounts(&$page, $action = "list", $id = null
) {
593 // managment of bank account used for money transfert
594 $page->setTitle('Administration - Paiements - RIBs');
595 $page->assign('title', "Liste des RIBs");
597 $table_editor = new PLTableEditor("admin/payments/bankaccounts",'payment_bankaccounts','id');
598 $table_editor->describe('asso_id','ID du groupe',false
);
599 $table_editor->describe('owner','titulaire',true
);
600 $table_editor->add_option_table('groups','groups.id = t.asso_id');
601 $table_editor->add_option_field('groups.diminutif', 'group_name', 'groupe', 'varchar','account');
604 if ($action == "update" && Post
::has("account") && !check_rib(Post
::v("account"))) {
605 $page->trigError("Le RIB n'est pas valide");
606 $table_editor->apply($page, "edit", $id);
610 $table_editor->apply($page, $action, $id);
613 function handler_adm_methods(&$page, $action = "list", $id = null
) {
614 // show and edit payment methods
615 $page->setTitle('Administration - Paiements - Méthodes');
616 $page->assign('title', "Méthodes de paiement");
617 $table_editor = new PLTableEditor("admin/payments/methods",'payment_methods','id');
618 $table_editor->apply($page, $action, $id);
621 function handler_adm_reconcile(&$page, $step = 'list', $param = null
) {
622 // reconciles logs with transactions
623 // FIXME: the admin is considered to be fair => he doesn't hack the $step value, nor other params
624 $page->setTitle('Administration - Paiements - Réconciliations');
625 $page->changeTpl('payment/reconcile.tpl');
626 $page->assign('step', $step);
628 if (substr($step,0,4) != 'step') {
630 unset($_SESSION['paymentrecon_method']);
631 unset($_SESSION['paymentrecon_data']);
632 unset($_SESSION['paymentrecon_id']);
634 } elseif (isset($_SESSION['paymentrecon_data'])) {
635 // create temporary table with imported data
636 XDB
::execute("CREATE TEMPORARY TABLE payment_tmp (
637 reference VARCHAR(255) PRIMARY KEY,
640 commission DECIMAL(9,2)
642 foreach ($_SESSION['paymentrecon_data'] as $i)
643 XDB
::execute("INSERT INTO payment_tmp VALUES ({?},{?},{?},{?})",
644 $i['reference'], $i['date'], $i['amount'], $i['commission']);
647 if ($step == 'list' ||
$step == 'delete' ||
$step == 'edit' ||
$step == 'step5') {
649 if ($step == 'delete' && $param != null
) {
650 S
::assert_xsrf_token();
651 XDB
::execute("DELETE FROM payment_reconcilations WHERE id={?}", $param);
652 // FIXME: hardcoding !!!
653 XDB
::execute("UPDATE payment_transactions SET recon_id=NULL,commission=NULL WHERE recon_id={?} AND method_id=2", $param);
654 XDB
::execute("UPDATE payment_transactions SET recon_id=NULL WHERE recon_id={?} AND method_id=1", $param);
655 $page->trigSuccess("L'entrée ".$param." a été supprimée.");
657 } elseif ($step == 'edit') {
658 $page->trigError("L'édition n'est pas implémentée.");
660 } elseif ($step == 'step5') {
661 $page->trigSuccess("La réconciliation est terminée. Il est maintenant nécessaire de générer les virements.");
664 // show list of reconciliations, with a "add" button
665 $page->assign('title', "Réconciliation - Liste");
666 $table_editor = new PLTableEditor("admin/payments/reconcile",'payment_reconcilations','id');
667 $table_editor->describe('payment_count','transactions',true
);
668 $table_editor->describe('period_start','from',true
);
669 $table_editor->describe('period_end','to',true
);
670 $table_editor->describe('sum_amounts','total (€)',true
);
671 $table_editor->describe('sum_commissions','coms (€)',true
);
672 $table_editor->describe('comments','comments',false
);
673 $table_editor->apply($page, 'list');
675 } elseif ($step == 'new' ||
$step == 'massadd') {
676 pl_redirect("admin/payments/reconcile/step1");
678 } elseif ($step == 'step1') {
679 $page->assign('title', "Étape 1");
681 // was a payment method choosen ?
682 if ($param != null
) {
683 $_SESSION['paymentrecon_method'] = (int)$param;
684 pl_redirect("admin/payments/reconcile/step2");
687 // ask to choose a payment method
688 $res = XDB
::query("SELECT id, text FROM payment_methods");
689 $page->assign('methods', $res->fetchAllAssoc());
692 } elseif ( $step == 'step2' ) {
693 $page->assign('title', "Étape 2");
695 // import logs formated in CVS
696 $fields = array('date','reference','amount','commission');
697 $importer = new PaymentLogsImporter();
698 $importer->apply($page, 'admin/payments/reconcile/step2', $fields);
700 // if import is finished
701 $result = $importer->get_result();
702 if($result != null
) {
703 $_SESSION['paymentrecon_data'] = $result;
704 pl_redirect("admin/payments/reconcile/step3");
707 } elseif ($step == 'step3' ) {
708 $page->assign('title', "Étape 3");
710 // compute reconcilation summary data
711 $res = XDB
::query("SELECT MIN(date) AS period_start, MAX(date) AS period_end,
712 count(*) AS payment_count, SUM(amount) AS sum_amounts,
713 SUM(commission) AS sum_commissions
715 $recon = $res->fetchOneAssoc();
716 $recon['method_id'] = $_SESSION['paymentrecon_method'];
718 // create reconciliation item in database
719 if(Post
::has('next')) {
720 S
::assert_xsrf_token();
723 $recon['period_start'] = preg_replace('/([0-9]{1,2})\/([0-9]{1,2})\/([0-9]{4})/','\3-\2-\1',Post
::v('period_start'));
724 $recon['period_end'] = preg_replace('/([0-9]{1,2})\/([0-9]{1,2})\/([0-9]{4})/','\3-\2-\1',Post
::v('period_end'));
725 // FIXME: save checks to be done at next step
727 // Create reconcilation item in database
728 // FIXME: check if period doesn't overlap with others for the same method_id
729 XDB
::execute("INSERT INTO payment_reconcilations (method_id, period_start, period_end,
730 payment_count, sum_amounts, sum_commissions)
731 VALUES ({?}, {?}, {?}, {?}, {?}, {?})",
732 $recon['method_id'], $recon['period_start'], $recon['period_end'],
733 $recon['payment_count'], $recon['sum_amounts'], $recon['sum_commissions']);
734 $_SESSION['paymentrecon_id'] = XDB
::insertId();
736 // reconcile simple cases (trans.commission n'est modifié que s'il vaut NULL)
737 XDB
::execute("UPDATE payment_transactions AS trans, payment_tmp AS tmp
738 SET trans.recon_id={?}, trans.commission=tmp.commission
739 WHERE trans.fullref=tmp.reference
740 AND trans.amount=tmp.amount AND DATE(trans.ts_confirmed)=tmp.date
741 AND (trans.commission IS NULL OR trans.commission=tmp.commission)
742 AND method_id={?} AND recon_id IS NULL AND status='confirmed'",
743 $_SESSION['paymentrecon_id'], $recon['method_id']);
745 pl_redirect("admin/payments/reconcile/step4");
747 // show summary of the imported data + ask form start/end of reconcilation period
749 $recon['period_start'] = preg_replace('/([0-9]{4})-([0-9]{2})-([0-9]{2})/', '\3/\2/\1', $recon['period_start']);
750 $recon['period_end'] = preg_replace('/([0-9]{4})-([0-9]{2})-([0-9]{2})/', '\3/\2/\1', $recon['period_end']);
751 $page->assign('recon', $recon);
754 } elseif ($step == 'step4' ) {
755 $page->assign('title', "Étape 4");
757 // get reconcilation summary informations
758 $res = XDB
::query("SELECT * FROM payment_reconcilations WHERE id={?}", $_SESSION['paymentrecon_id']);
759 $recon = $res->fetchOneAssoc();
760 $page->assign('recon', $recon);
762 if (Post
::has('force')) {
763 S
::assert_xsrf_token();
764 foreach (Post
::v('force') as $id => $value) {
765 XDB
::execute("UPDATE payment_transactions AS trans, payment_tmp AS tmp
766 SET trans.recon_id={?}, trans.commission=tmp.commission
767 WHERE trans.id={?} AND trans.fullref=tmp.reference",
768 $_SESSION['paymentrecon_id'], $id);
770 $page->trigSuccess('La réconciliation a été forcée pour '.count(Post
::v('force')).' transaction(s).');
772 } elseif (Post
::has('next')) {
773 if (strlen($recon['comments'])<3) {
774 $page->trigError("Le commentaire doit contenir au moins 3 caractères.");
776 XDB
::execute("UPDATE payment_reconcilations SET status='transfering' WHERE id={?}", $_SESSION['paymentrecon_id']);
777 pl_redirect('admin/payments/reconcile/step5');
780 } elseif (Post
::has('savecomments')) {
781 S
::assert_xsrf_token();
782 $recon['comments'] = Post
::v('comments');
783 $page->assign('recon', $recon);
784 XDB
::execute("UPDATE payment_reconcilations SET comments={?} WHERE id={?}", $recon['comments'], $_SESSION['paymentrecon_id']);
785 $page->trigSuccess('Les commentaires ont été enregistrés.');
788 // reconcilation results - ok
789 $res = XDB
::query("SELECT count(*),SUM(amount),SUM(commission)
790 FROM payment_transactions WHERE recon_id={?}",
792 list($ok_count,$ok_sum_amounts,$ok_sum_coms) = $res->fetchOneRow();
793 $page->assign('ok_count', $ok_count);
795 // reconcilation results - ref exists, but some data differs
796 $res = XDB
::query("SELECT id, fullref, method_id, ts_confirmed, trans.amount, trans.commission, status, recon_id,
797 reference, date, tmp.amount as amount2, tmp.commission as commission2
798 FROM payment_transactions AS trans
799 INNER JOIN payment_tmp AS tmp ON trans.fullref=tmp.reference
800 WHERE trans.recon_id IS NULL OR trans.recon_id != {?}",
802 $differs = $res->fetchAllAssoc();
803 $page->assign_by_ref('differs', $differs);
804 $page->assign('differ_count', count($differs));
806 // reconcilation results - ref doesn't exists in database
807 $res = XDB
::query("SELECT tmp.*
808 FROM payment_tmp AS tmp
809 LEFT JOIN payment_transactions AS trans ON trans.fullref=tmp.reference
810 WHERE trans.fullref IS NULL");
811 $only_import = $res->fetchAllAssoc();
812 $page->assign_by_ref('only_import', $only_import);
813 $page->assign('onlyim_count', count($only_import));
815 // reconcilation results - exists in database but not in import
816 $res = XDB
::query("SELECT trans.*
817 FROM payment_transactions AS trans
818 LEFT JOIN payment_tmp AS tmp ON trans.fullref=tmp.reference
819 WHERE {?}<=DATE(trans.ts_confirmed) AND DATE(trans.ts_confirmed)<={?}
820 AND tmp.reference IS NULL AND method_id={?}",
821 $recon['period_start'], $recon['period_end'], $recon['method_id']);
822 $only_database = $res->fetchAllAssoc();
823 $page->assign_by_ref('only_database', $only_database);
824 $page->assign('onlydb_count', count($only_database));
827 $page->trigError('Bad parameters.');
831 function handler_adm_transferts(&$page) {
832 // list/log all bank transferts and link them to individual transactions
836 class PaymentLogsImporter
extends CSVImporter
{
839 public function __construct() {
840 parent
::__construct('');
841 $this->registerFunction('systempay_commission', 'Compute BPLC commission', array($this,"compute_systempay_commission"));
842 $this->registerFunction('payment_id', 'Autocompute payment ID', array($this,"compute_payment_id"));
843 //$this->forceValue('payment_id','func_payment_id');
846 public function run($action = null
, $insert_relation = null
, $update_relation = null
) {
847 $this->result
= array();
848 foreach ($this->data
as $line) {
849 $a = $this->makeAssoc($line, $insert_relation);
850 $a['date'] = preg_replace('/([0-9]{2})\/([0-9]{2})\/([0-9]{4}) .*/','\3-\2-\1', $a['date']);
851 $this->result
[] = $a;
855 public function get_result() {
856 return $this->result
;
859 static public function compute_systempay_commission($line, $key, $relation) {
860 if($key!='commission' ||
!array_key_exists('carte',$line)) return null
;
861 $amount = self
::getValue($line, 'amount', $relation['amount']);
862 if ($line['carte'] == 'CB')
863 return 0.20 +
round($amount*0.005, 2);
865 return 0.20 +
round($amount*0.005, 2) +
0.76;
868 static public function compute_payment_id($line, $key, $relation) {
869 if ($key != 'payment_id') return null
;
870 $reference = self
::getValue($line, 'reference', $relation['reference']);
871 if (ereg('-([0-9]+)$', $reference, $matches))
878 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8: