-
- $recongps = array();
-
- $res = XDB::query("SELECT r.id, short_name AS method, period_start, period_end, status,
- payment_count, sum_amounts, sum_commissions
- FROM payment_reconcilations AS r
- LEFT JOIN payment_methods AS m ON r.method_id=m.id
- WHERE recongroup_id IS NULL
- ORDER BY period_end DESC, period_start DESC");
- foreach ($res->fetchAllAssoc() as $recon)
- $recongps[] = array('recons' => array($recon), 'transfers' => array());
-
- $res = XDB::query("SELECT recongroup_id AS id
- FROM payment_reconcilations
- GROUP BY recongroup_id
- ORDER BY MAX(period_end) DESC, MIN(period_start) DESC");
- foreach ($res->fetchAllAssoc() as $recongp) {
- $res = XDB::query("SELECT r.id, short_name AS method, period_start, period_end, status,
- payment_count, sum_amounts, sum_commissions
- FROM payment_reconcilations AS r
- LEFT JOIN payment_methods AS m ON r.method_id=m.id
- WHERE recongroup_id={?}
- ORDER BY period_end DESC, period_start DESC",
- $recongp['id']);
- $recongp['recons'] = $res->fetchAllAssoc();
-
- $res = XDB::query("SELECT id, payment_id, amount, account_id, message, date
- FROM payment_transfers
- WHERE recongroup_id={?}",
- $recongp['id']);
- $recongp['transfers'] = $res->fetchAllAssoc();
-
- $recongps[] = $recongp;
- }
- $page->assign_by_ref('recongps', $recongps);
- }
+
+ $recongps = array();
+
+ // récupère les réconciliations non groupées
+ $res = XDB::query("SELECT r.id, short_name AS method, period_start, period_end, status,
+ payment_count, sum_amounts, sum_commissions
+ FROM payment_reconcilations AS r
+ LEFT JOIN payment_methods AS m ON (r.method_id = m.id)
+ WHERE recongroup_id IS NULL
+ ORDER BY period_end DESC, period_start DESC");
+ foreach ($res->fetchAllAssoc() as $recon)
+ $recongps[] = array('recons' => array($recon), 'transfers' => array());
+
+ // ne récupère que les 18 derniers groupements
+ $res = XDB::query("SELECT recongroup_id AS id
+ FROM payment_reconcilations
+ GROUP BY recongroup_id
+ ORDER BY MAX(period_end) DESC, MIN(period_start) DESC
+ LIMIT 18");
+ foreach ($res->fetchAllAssoc() as $recongp) {
+ $res = XDB::query("SELECT r.id, short_name AS method, period_start, period_end, status,
+ payment_count, sum_amounts, sum_commissions
+ FROM payment_reconcilations AS r
+ LEFT JOIN payment_methods AS m ON (r.method_id = m.id)
+ WHERE recongroup_id = {?}
+ ORDER BY period_end DESC, period_start DESC",
+ $recongp['id']);
+ $recongp['recons'] = $res->fetchAllAssoc();
+
+ $res = XDB::query('SELECT t.id, t.payment_id, t.amount, t.message, t.date
+ FROM payment_transfers AS t
+ WHERE recongroup_id = {?}',
+ $recongp['id']);
+ $transfers = $res->fetchAllAssoc();
+ foreach ($transfers as $id => $t) {
+ if ($t['date'] == NULL) { // si le virement n'est pas fait, on va récupérer le rib associé au paiment
+ $ownertmp = XDB::fetchOneCell('SELECT b.owner
+ FROM payment_bankaccounts AS b
+ LEFT JOIN payments AS p ON (p.rib_id = b.id)
+ WHERE p.id = {?}', $t['payment_id']);
+ } else { // sinon on prend celui associé au virement
+ $ownertmp = XDB::fetchOneCell('SELECT b.owner
+ FROM payment_bankaccounts AS b
+ LEFT JOIN payment_transfers AS t ON (t.account_id = b.id)
+ WHERE t.id = {?}', $t['id']);
+ }
+ $transfers[$id]['owner'] = $ownertmp;
+ }
+ $recongp['transfers'] = $transfers;
+
+ $recongps[] = $recongp;
+ }
+ $page->assign_by_ref('recongps', $recongps);
+ }