• Membres ayant cotisé les années passées mais pas encore l'année en cours
  • version initiale : Guillaume Leroux mimi.laroue@free.fr
  • généralisation dates : JCE lesanges@zaclys.net

SELECT
	users.nom,
	users.email,
	users.id,
	s.label AS 'Activité'
FROM users
	INNER JOIN services_users su ON su.id_user = users.id
	INNER JOIN services s ON s.id = su.id_service
WHERE (
	s.label = "Cotisation " || strftime('%Y', DATE('now', '-1 year'))
	OR
	s.label = "Cotisation " || strftime('%Y', DATE('now', '-2 year'))
	OR
	s.label = "Cotisation " || strftime('%Y', DATE('now', '-3 year'))
	)
AND users.id NOT IN (
	SELECT	 users.id
	FROM users
	INNER JOIN services_users su ON su.id_user = users.id
	INNER JOIN services s ON s.id = su.id_service
	WHERE s.label = "Cotisation " || strftime('%Y', DATE())
	)
GROUP BY users.id
ORDER BY users.nom
LIMIT 1000
;

• Membres inscrits à une activité sans avoir d'adhésion. (Eric Buissonnet e.buissonnet@free.fr)

SELECT
  users.nom,
  users.email,
  users.id AS _user_id,
  s.label AS 'Activité',
  f.label AS 'Tarif',
  su.paid AS 'Payé',
  su.date AS 'Date'
FROM users
  INNER JOIN services_users su ON su.id_user = users.id
  INNER JOIN services s ON s.id = su.id_service
  INNER JOIN services_fees f ON f.id = su.id_fee
WHERE s.id != 1
	AND su.date >= s.start_date
	AND su.id_user NOT IN
	(
		SELECT id_user
		FROM services_users sus
		WHERE sus.id_service = 1
		AND sus.date >= s.start_date
	)
ORDER BY users.nom ASC LIMIT 1000;

• liste des adhérents ayant versé une cotisation ou un don à l’association ; voir la doc paheko

SELECT
  su.date AS 'Date d''inscription',
  su.expiry_date AS 'Date d''expiration',
  SUM(tl.debit)/100 AS 'Montant réglé',
  expected_amount - SUM(tl.debit)/100 AS 'Reste à régler',
  users.*,
  users.id AS _user_id
FROM users
INNER JOIN (
  SELECT MAX(expiry_date) AS expiry_date,
    DATE, id_user, id, expected_amount
  FROM services_users
-- Modifier ici l'ID de l'activité
  WHERE id_service = 1 AND paid = 1
    AND (expiry_date >= DATE() OR expiry_date IS NULL)
  GROUP BY id_user) AS su
  ON su.id_user = users.id
LEFT JOIN acc_transactions_users tu
  ON tu.id_service_user = su.id
LEFT JOIN acc_transactions_lines tl
  ON tl.id_transaction = tu.id_transaction
GROUP BY users.id