Overview
Comment: | Implement list of users subscribed to an activity |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | dev | 1.0.0-alpha4 |
Files: | files | file ages | folders |
SHA1: |
20999f9c70181a5e64b28430af1c7d1a |
User & Date: | bohwaz on 2020-11-02 01:44:43 |
Other Links: | branch diff | manifest | tags |
Context
2020-11-02
| ||
01:45 | Bump version check-in: 5643f1667b user: bohwaz tags: dev, 1.0.0-alpha4 | |
01:44 | Implement list of users subscribed to an activity check-in: 20999f9c70 user: bohwaz tags: dev, 1.0.0-alpha4 | |
2020-11-01
| ||
23:15 | Remove old code check-in: f3be99a19d user: bohwaz tags: dev | |
Changes
Modified src/include/data/1.0.0_migration.sql from [1a4afa91ca] to [05b3dff999].
︙ | ︙ | |||
99 100 101 102 103 104 105 106 | -- Transfert des rapprochements UPDATE acc_transactions_lines SET reconciled = 1 WHERE id_transaction IN (SELECT id_operation FROM compta_rapprochement); --------- MIGRATION COTISATIONS ---------- INSERT INTO services SELECT id, intitule, description, duree, debut, fin FROM cotisations; INSERT INTO services_users SELECT cm.id, cm.id_membre, cm.id_cotisation, | > > > > > > < > < < < < < | 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | -- Transfert des rapprochements UPDATE acc_transactions_lines SET reconciled = 1 WHERE id_transaction IN (SELECT id_operation FROM compta_rapprochement); --------- MIGRATION COTISATIONS ---------- INSERT INTO services SELECT id, intitule, description, duree, debut, fin FROM cotisations; INSERT INTO services_fees (id, label, amount, id_service, id_account) SELECT id, intitule, CAST(montant*100 AS integer), id, (SELECT id FROM acc_accounts WHERE code = (SELECT compte FROM compta_categories WHERE id = id_categorie_compta)) FROM cotisations WHERE montant > 0 OR id_categorie_compta IS NOT NULL; INSERT INTO services_users SELECT cm.id, cm.id_membre, cm.id_cotisation, cm.id_cotisation, 1, cm.date, CASE WHEN c.duree IS NOT NULL THEN date(cm.date, '+'||c.duree||' days') WHEN c.fin IS NOT NULL THEN c.fin ELSE NULL END FROM cotisations_membres cm INNER JOIN cotisations c ON c.id = cm.id_cotisation; INSERT INTO services_reminders SELECT * FROM rappels; INSERT INTO services_reminders_sent SELECT id, id_membre, id_cotisation, id_rappel, date FROM rappels_envoyes; DROP TABLE cotisations; DROP TABLE cotisations_membres; DROP TABLE rappels; DROP TABLE rappels_envoyes; |
︙ | ︙ |
Modified src/include/data/1.0.0_schema.sql from [9620b90e42] to [1cfe53e295].
︙ | ︙ | |||
67 68 69 70 71 72 73 | id INTEGER NOT NULL PRIMARY KEY, id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE, id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, id_fee INTEGER NULL REFERENCES services_fees (id) ON DELETE CASCADE, paid INTEGER NOT NULL DEFAULT 0, | | > > | 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | id INTEGER NOT NULL PRIMARY KEY, id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE, id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, id_fee INTEGER NULL REFERENCES services_fees (id) ON DELETE CASCADE, paid INTEGER NOT NULL DEFAULT 0, date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date), expiry_date TEXT NULL CHECK (date(expiry_date) IS NULL OR date(expiry_date) = expiry_date) ); CREATE UNIQUE INDEX IF NOT EXISTS su_unique ON services_users (id_user, id_service, date); CREATE INDEX IF NOT EXISTS su_expiry ON services_users (id_service, expiry_date); CREATE TABLE IF NOT EXISTS services_reminders -- Rappels de devoir renouveller une cotisation ( id INTEGER NOT NULL PRIMARY KEY, id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE, |
︙ | ︙ |
Added src/include/lib/Garradin/DynamicList.php version [1e9885b5f5].
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | <?php namespace Garradin; class DynamicList { protected $columns; protected $tables; protected $conditions; protected $group; protected $order; protected $count = 'COUNT(*)'; protected $desc = true; protected $per_page = 100; protected $page = 1; private $count_result; public function __construct(array $columns, string $tables, string $conditions) { $this->columns = $columns; $this->tables = $tables; $this->conditions = $conditions; $this->order = key($columns); } public function __get($key) { return $this->$key; } public function setConditions(string $conditions) { $this->conditions = $conditions; } public function orderBy(string $key, bool $desc) { if (!array_key_exists($key, $this->columns)) { throw new UserException('Invalid order: ' . $key); } $this->order = $key; $this->desc = $desc; } public function groupBy(string $value) { $this->group = $value; } public function count() { if (null === $this->count_result) { $sql = sprintf('SELECT %s FROM %s WHERE %s;', $this->count, $this->tables, $this->conditions); $this->count_result = DB::getInstance()->firstColumn($sql); } return $this->count_result; } public function paginationURL() { $query = array_merge($_GET, ['p' => '[ID]']); $url = Utils::getSelfURL($query); return $url; } public function orderURL(string $order, bool $desc) { $query = array_merge($_GET, ['o' => $order, 'd' => (int) $desc]); $url = Utils::getSelfURL($query); return $url; } public function setCount(string $count) { $this->count = $count; } public function iterate() { $start = ($this->page - 1) * $this->per_page; $columns = []; foreach ($this->columns as $alias => $properties) { $select = isset($properties['select']) ? $properties['select'] : $alias; $columns[] = sprintf('%s AS %s', $select, $alias); } $columns = implode(', ', $columns); $order = isset($this->columns[$this->order]['order']) ? $this->columns[$this->order]['order'] : $this->order; if ($this->desc) { $order .= ' DESC'; } $group = $this->group ? 'GROUP BY ' . $this->group : ''; $sql = sprintf('SELECT %s FROM %s WHERE %s %s ORDER BY %s LIMIT %d,%d;', $columns, $this->tables, $this->conditions, $group, $order, $start, $this->per_page); return DB::getInstance()->iterate($sql); } public function loadFromQueryString() { if (!empty($_GET['o'])) { $this->orderBy($_GET['o'], !empty($_GET['d'])); } if (!empty($_GET['p'])) { $this->page = (int)$_GET['p']; } } } |
Modified src/include/lib/Garradin/Entities/Services/Service.php from [06d8b7c8a0] to [5346e73a9e].
1 2 3 4 5 6 7 8 9 10 11 | <?php namespace Garradin\Entities\Services; use Garradin\Entity; use Garradin\ValidationException; use Garradin\Utils; use Garradin\Services\Fees; class Service extends Entity { | > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 | <?php namespace Garradin\Entities\Services; use Garradin\Config; use Garradin\DynamicList; use Garradin\Entity; use Garradin\ValidationException; use Garradin\Utils; use Garradin\Services\Fees; class Service extends Entity { |
︙ | ︙ | |||
67 68 69 70 71 72 73 | parent::importForm($source); } public function fees() { return new Fees($this->id()); } | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 | parent::importForm($source); } public function fees() { return new Fees($this->id()); } public function distinctUsersList(): DynamicList { $identity = Config::getInstance()->get('champ_identite'); $columns = [ 'id_user' => [ 'select' => 'su.id_user', ], 'identity' => [ 'label' => 'Membre', 'select' => 'm.' . $identity, 'order' => sprintf('transliterate_to_ascii(m.%s) COLLATE NOCASE', $identity), ], 'status' => [ 'label' => 'Statut', 'select' => 'CASE WHEN su.expiry_date < date() THEN -1 WHEN su.expiry_date >= date() THEN 1 ELSE 0 END', ], 'paid' => [ 'label' => 'Payé ?', 'select' => 'su.paid', ], 'expiry' => [ 'label' => 'Date d\'expiration', 'select' => 'MAX(su.expiry_date)', ], 'fee' => [ 'label' => 'Tarif', 'select' => 'sf.label', ], 'date' => [ 'label' => 'Date d\'inscription', 'select' => 'su.date', ], ]; $tables = 'services_users su INNER JOIN membres m ON m.id = su.id_user INNER JOIN services_fees sf ON sf.id = su.id_fee'; $conditions = sprintf('su.id_service = %d', $this->id()); $list = new DynamicList($columns, $tables, $conditions); $list->groupBy('su.id_user'); $list->orderBy('date', true); $list->setCount('COUNT(DISTINCT id_user)'); return $list; } public function unpaidUsersList(): DynamicList { $list = $this->distinctUsersList(); $conditions = sprintf('su.id_service = %d AND su.paid = 0', $this->id()); $list->setConditions($conditions); return $list; } public function expiredUsersList(): DynamicList { $list = $this->distinctUsersList(); $conditions = sprintf('su.id_service = %d AND su.expiry_date < date()', $this->id()); $list->setConditions($conditions); return $list; } } |
Modified src/templates/services/details.tpl from [53383293d7] to [58468d6a82].
|
| | > > | < < < | > | | > > > > | > > > > | < < < < < < < < < < < | | | | | < | | | > > | > | < | > > | | | | | | > | > | > | > > > > > > > | | < < < | | | | | | | | < | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | {include file="admin/_head.tpl" title="%s — Liste des membres inscrits"|args:$service.label current="membres/services"} {include file="services/_nav.tpl" current="index"} <nav class="tabs"> <ul class="sub"> <li> {$service.label} — {if $service.duration} {$service.duration} jours {elseif $service.start_date} du {$service.start_date|format_sqlite_date_to_french} au {$service.end_date|format_sqlite_date_to_french} {else} ponctuelle {/if} </li> <li{if $type == 'all'} class="current"{/if}><a href="?id={$service.id}">Tous les inscrits</a></li> <li{if $type == 'expired'} class="current"{/if}><a href="?id={$service.id}&type=expired">Inscription expirée</a></li> <li{if $type == 'unpaid'} class="current"{/if}><a href="?id={$service.id}&type=unpaid">En attente de règlement</a></li> </ul> </nav> <dl class="cotisation"> <dt>Nombre de membres inscrits</dt> <dd> {$list->count()} <em class="help">(N'apparaît ici que l'inscription la plus récente de chaque membre.)</em> </dd> </dl> <table class="list"> <thead class="userOrder"> <tr> {foreach from=$list.columns key="key" item="column"} <?php if (!isset($column['label'])) { continue; } ?> <td class="{if $list->order == $key}cur {if $list->desc}desc{else}asc{/if}{/if}"> {$column.label} <a href="{$list->orderURL($key, false)}" class="icn up">↑</a> <a href="{$list->orderURL($key, true)}" class="icn dn">↓</a> </td> {/foreach} <td></td> </tr> </thead> <tbody> {foreach from=$list->iterate() item="row"} <tr> <th><a href="../membres/fiche.php?id={$row.id_user}">{$row.identity}</a></th> <td> {if $row.status == 1} <b class="confirm">À jour</b> {elseif $row.status == -1} <b class="error">En retard</b> {else} Pas d'expiration {/if} </td> <td>{if $row.paid}<b class="confirm">Oui</b>{else}<b class="error">Non</b>{/if}</td> <td>{$row.expiry|format_sqlite_date_to_french}</td> <td>{$row.fee}</td> <td>{$row.date|format_sqlite_date_to_french}</td> <td class="actions"> {linkbutton shape="user" label="Toutes les activités de ce membre" href="services/user.php?id=%d"|args:$row.id_user} {linkbutton shape="alert" label="Rappels envoyés" href="services/reminders/user.php?id=%d"|args:$row.id_user} </td> </tr> {/foreach} </tbody> </table> {pagination url=$list->paginationURL() page=$list.page bypage=$list.per_page total=$list->count()} {include file="admin/_foot.tpl"} |
Modified src/www/admin/services/details.php from [4373893e11] to [9cae948e89].
1 2 3 4 5 | <?php namespace Garradin; require_once __DIR__ . '/../_inc.php'; | | | < | | | < | | | < < < | > > | < > > | | < > | | > | | < < | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | <?php namespace Garradin; require_once __DIR__ . '/../_inc.php'; use Garradin\Services\Services; require_once __DIR__ . '/_inc.php'; $session->requireAccess('membres', Membres::DROIT_ADMIN); $service = Services::get((int) qg('id')); if (!$service) { throw new UserException("Cette activité n'existe pas"); } $type = qg('type'); if ('unpaid' == $type) { $list = $service->unpaidUsersList(); } elseif ('expired' == $type) { $list = $service->expiredUsersList(); } else { $type = 'all'; $list = $service->distinctUsersList(); } $list->loadFromQueryString(); $tpl->assign(compact('list', 'service', 'type')); $tpl->display('services/details.tpl'); |
Modified src/www/admin/static/admin.css from [690708c7eb] to [61508b24a8].
︙ | ︙ | |||
700 701 702 703 704 705 706 | } table.list caption { text-align: center; font-size: 1.2em; } | | | 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 | } table.list caption { text-align: center; font-size: 1.2em; } table.list tbody td.desc { font-size: .9em; color: #666; } table.list.auto { width: auto; } |
︙ | ︙ | |||
827 828 829 830 831 832 833 | .userOrder .cur { background: rgb(217, 134, 40); background: rgba(var(--gSecondColor), 1.0); color: #fff; color: rgb(var(--gBgColor)); } | | > > > > > | | | | | | | > | > > | > > | 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 | .userOrder .cur { background: rgb(217, 134, 40); background: rgba(var(--gSecondColor), 1.0); color: #fff; color: rgb(var(--gBgColor)); } table.list .userOrder td, table.list .userOrder th { position: relative; padding-left: 2.3em; } table.list .userOrder .check { padding-left: .5em; } .userOrder .icn { position: absolute; left: .3em; top: 0; color: rgb(var(--gMainColor)); text-decoration: none; font-size: 1.5em; line-height: .5em; width: 1em; height: .5em; text-align: center; vertical-align: middle; font-weight: normal; text-shadow: 0px 0px 1px rgb(var(--gBgColor)); margin: 0; } .userOrder .icn.dn { margin-top: .6em; } thead .icn:hover { color: darkred; text-shadow: none; } |
︙ | ︙ |