Outils pour utilisateurs
sql:start
Différences
Ci-dessous, les différences entre deux révisions de la page.
| Les deux révisions précédentes Révision précédente | |||
|
sql:start [2024/03/06 20:38] jce ajouts requêtes |
— (Version actuelle) | ||
|---|---|---|---|
| Ligne 1: | Ligne 1: | ||
| - | ====== Exemples de requêtes sql ====== | ||
| - | ===== Membres ===== | ||
| - | * Membres qui sont inscrits à une activité sans avoir d' | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | membres.nom, | ||
| - | membres.email, | ||
| - | membres.id AS _user_id, | ||
| - | s.label AS ' | ||
| - | f.label AS ' | ||
| - | su.paid AS ' | ||
| - | su.date AS ' | ||
| - | 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' | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | users.nom, | ||
| - | users.email, | ||
| - | users.id AS _user_id, | ||
| - | s.label AS ' | ||
| - | f.label AS ' | ||
| - | su.paid AS ' | ||
| - | su.date AS ' | ||
| - | 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 [[https:// | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | su.date AS 'Date d'' | ||
| - | su.expiry_date AS 'Date d'' | ||
| - | SUM(tl.debit)/ | ||
| - | expected_amount - SUM(tl.debit)/ | ||
| - | 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' | ||
| - | 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 | ||
| - | </ | ||
| - | |||
| - | ===== Compta ===== | ||
| - | |||
| - | ===== Dons ===== | ||
| - | * Dons année (jce < | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | acc_transactions_users.id_user as ' | ||
| - | users.id as ' | ||
| - | users.nom as ' | ||
| - | printf(' | ||
| - | users.adresse as ' | ||
| - | users.code_postal as 'Code postal', | ||
| - | users.ville as ' | ||
| - | FROM | ||
| - | acc_transactions_users, | ||
| - | users, | ||
| - | acc_transactions | ||
| - | INNER JOIN acc_transactions_lines | ||
| - | ON acc_transactions_lines.id_transaction = acc_transactions.id | ||
| - | WHERE ( | ||
| - | strftime(' | ||
| - | 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 | ||
| - | </ | ||
sql/start.1709753905.txt.gz · Dernière modification: 2024/03/06 20:38 de jce
Outils de la page
