275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
|
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
|
-
+
|
}
$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 c.fin >= date() ELSE 1 END AS a_jour
WHEN c.fin IS NOT NULL THEN c.fin <= date() 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 ?,?;',
|
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
|
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
|
-
+
+
-
+
|
* @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 c.fin >= 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
* @param integer $id_cotisation Numéro de cotisation
* @return array Infos sur la cotisation, et champ expiration
* (si NULL = cotisation jamais enregistrée, si 1 = cotisation ponctuelle enregistrée, sinon date d'expiration)
*/
public function isMemberUpToDate($id, $id_cotisation)
{
$db = DB::getInstance();
return $db->first('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 c.fin >= 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
FROM cotisations AS c
LEFT JOIN cotisations_membres AS cm ON cm.id_cotisation = c.id AND cm.id_membre = ?
WHERE c.id = ? ORDER BY cm.date DESC;',
(int)$id, (int)$id_cotisation);
|