283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
|
}
$desc = $desc ? 'DESC' : 'ASC';
return $db->get('SELECT cm.id_membre, cm.date, cm.id, m.numero,
m.'.$champ_id.' AS nom, c.montant,
CASE WHEN c.duree IS NOT NULL THEN date(cm.date, \'+\'||c.duree||\' days\') >= date()
WHEN c.fin IS NOT NULL THEN (date() <= c.fin AND date() >= c.debut) ELSE 1 END AS a_jour
FROM cotisations_membres AS cm
INNER JOIN cotisations AS c ON c.id = cm.id_cotisation
INNER JOIN membres AS m ON m.id = cm.id_membre
WHERE
cm.id_cotisation = ?
AND m.id_categorie NOT IN (SELECT mc.id FROM membres_categories AS mc WHERE mc.cacher = 1)
GROUP BY cm.id_membre ORDER BY '.$order.' '.$desc.' LIMIT ?,?;',
|
|
>
|
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
|
}
$desc = $desc ? 'DESC' : 'ASC';
return $db->get('SELECT cm.id_membre, cm.date, cm.id, m.numero,
m.'.$champ_id.' AS nom, c.montant,
CASE WHEN c.duree IS NOT NULL THEN date(cm.date, \'+\'||c.duree||\' days\') >= date()
WHEN c.fin IS NOT NULL THEN (cm.date <= c.fin AND cm.date >= c.debut)
ELSE 1 END AS a_jour
FROM cotisations_membres AS cm
INNER JOIN cotisations AS c ON c.id = cm.id_cotisation
INNER JOIN membres AS m ON m.id = cm.id_membre
WHERE
cm.id_cotisation = ?
AND m.id_categorie NOT IN (SELECT mc.id FROM membres_categories AS mc WHERE mc.cacher = 1)
GROUP BY cm.id_membre ORDER BY '.$order.' '.$desc.' LIMIT ?,?;',
|
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
|
* @return array Liste des cotisations en cours de validité
*/
public function listSubscriptionsForMember($id)
{
$db = DB::getInstance();
return $db->get('SELECT c.*,
CASE WHEN c.duree IS NOT NULL THEN date(cm.date, \'+\'||c.duree||\' days\') >= date()
WHEN c.fin IS NOT NULL THEN (cm.id IS NOT NULL AND date() <= c.fin AND date() >= c.debut)
WHEN cm.id IS NOT NULL THEN 1 ELSE 0 END AS a_jour,
CASE WHEN c.duree IS NOT NULL THEN date(cm.date, \'+\'||c.duree||\' days\')
WHEN c.fin IS NOT NULL THEN c.fin ELSE 1 END AS expiration,
(julianday(date()) - julianday(CASE WHEN c.duree IS NOT NULL THEN date(cm.date, \'+\'||c.duree||\' days\')
WHEN c.fin IS NOT NULL THEN c.fin END)) AS nb_jours
FROM cotisations_membres AS cm
INNER JOIN cotisations AS c ON c.id = cm.id_cotisation
WHERE cm.id_membre = ?
AND ((c.fin IS NOT NULL AND date() <= c.fin AND date() >= c.debut) OR c.fin IS NULL)
GROUP BY cm.id_cotisation
ORDER BY cm.date DESC;', (int)$id);
}
/**
* Ce membre est-il à jour sur cette cotisation ?
* @param integer $id Numéro de membre
|
|
|
|
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
|
* @return array Liste des cotisations en cours de validité
*/
public function listSubscriptionsForMember($id)
{
$db = DB::getInstance();
return $db->get('SELECT c.*,
CASE WHEN c.duree IS NOT NULL THEN date(cm.date, \'+\'||c.duree||\' days\') >= date()
WHEN c.fin IS NOT NULL THEN (cm.id IS NOT NULL AND cm.date <= c.fin AND cm.date >= c.debut)
WHEN cm.id IS NOT NULL THEN 1 ELSE 0 END AS a_jour,
CASE WHEN c.duree IS NOT NULL THEN date(cm.date, \'+\'||c.duree||\' days\')
WHEN c.fin IS NOT NULL THEN c.fin ELSE 1 END AS expiration,
(julianday(date()) - julianday(CASE WHEN c.duree IS NOT NULL THEN date(cm.date, \'+\'||c.duree||\' days\')
WHEN c.fin IS NOT NULL THEN c.fin END)) AS nb_jours
FROM cotisations_membres AS cm
INNER JOIN cotisations AS c ON c.id = cm.id_cotisation
WHERE cm.id_membre = ?
AND ((c.fin IS NOT NULL AND cm.date <= c.fin AND cm.date >= c.debut) OR c.fin IS NULL)
GROUP BY cm.id_cotisation
ORDER BY cm.date DESC;', (int)$id);
}
/**
* Ce membre est-il à jour sur cette cotisation ?
* @param integer $id Numéro de membre
|