• Membres qui sont inscrits à une activité sans avoir d'adhésion.

SELECT
  membres.nom,
  membres.email,
  membres.id AS _user_id,
  s.label AS 'Activité',
  f.label AS 'Tarif',
  su.paid AS 'Payé',
  su.date AS 'Date'
FROM membres
  INNER JOIN services_users su ON su.id_user = membres.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 membres.nom ASC 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

• Dons année (jce lesanges@free.fr)

SELECT
	acc_transactions_users.id_user AS 'Reçu',
	users.id AS 'idPers',
	users.nom AS 'Nom',
	printf('%.2f', SUM(acc_transactions_lines.credit)/100.) AS 'Montant',
	users.adresse AS 'Adresse',
	users.code_postal AS 'Code postal',
	users.ville AS 'Ville'
FROM
	acc_transactions_users,
	users,
	acc_transactions
	INNER JOIN acc_transactions_lines
	ON acc_transactions_lines.id_transaction = acc_transactions.id
WHERE (
	strftime('%Y', acc_transactions.date) = :YEAR
	AND
		acc_transactions_lines.credit > 0
	AND
		acc_transactions_users.id_transaction = acc_transactions.id
	AND
		acc_transactions_users.id_user = users.id
	)
GROUP BY acc_transactions_users.id_user
ORDER BY users.nom COLLATE NOCASE