• ++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