Overview
Comment:Switch to U_NOCASE collation
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable | 1.1.19
Files: files | file ages | folders
SHA3-256: 7e45abbf2c87c3b8d3f16d7e49a417491b6dadea2b18f9b3bd42d0f54cb2bbff
User & Date: bohwaz on 2022-01-12 00:14:08
Other Links: manifest | tags
Context
2022-01-12
01:46
Make sure we disable foreign keys when replacing table check-in: a6914f978c user: bohwaz tags: trunk, stable, 1.1.19
00:14
Switch to U_NOCASE collation check-in: 7e45abbf2c user: bohwaz tags: trunk, stable, 1.1.19
00:13
Fix database on upgrade for UTF-8 and NOCASE index issues, bump version check-in: 5598d4d31e user: bohwaz tags: trunk
Changes

Modified src/include/lib/Garradin/Accounting/Accounts.php from [52e1ce9bc9] to [c5f3b228de].

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

	/**
	 * Return common accounting accounts from current chart
	 * (will not return analytical and volunteering accounts)
	 */
	public function listCommonTypes(): array
	{
		return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? AND type != 0 AND type NOT IN (?) ORDER BY code COLLATE NOCASE;',
			$this->chart_id, Account::TYPE_ANALYTICAL);
	}

	/**
	 * Return all accounts from current chart
	 */
	public function listAll(): array
	{
		return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? ORDER BY code COLLATE NOCASE;',
			$this->chart_id);
	}

	public function listForCodes(array $codes): array
	{
		return DB::getInstance()->getGrouped('SELECT code, id, label FROM acc_accounts WHERE id_chart = ?;', $this->chart_id);
	}

	/**
	 * Return all accounts from current chart
	 */
	public function export(): \Generator
	{
		$res = $this->em->DB()->iterate($this->em->formatQuery('SELECT
			code, label, description, position, type, user AS added
			FROM @TABLE WHERE id_chart = ? ORDER BY code COLLATE NOCASE;'),
			$this->chart_id);

		foreach ($res as $row) {
			$row->type = Account::TYPES_NAMES[$row->type];
			$row->position = Account::POSITIONS_NAMES[$row->position];
			$row->added = $row->added ? 'Ajouté' : '';
			yield $row;
		}
	}

	/**
	 * Return only analytical accounts
	 */
	public function listAnalytical(): array
	{
		return $this->em->DB()->getAssoc($this->em->formatQuery('SELECT id, label FROM @TABLE WHERE id_chart = ? AND type = ? ORDER BY label COLLATE NOCASE;'), $this->chart_id, Account::TYPE_ANALYTICAL);
	}

	/**
	 * Return only analytical accounts
	 */
	public function listVolunteering(): array
	{
		return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? AND type = ? ORDER BY code COLLATE NOCASE;',
			$this->chart_id, Account::TYPE_VOLUNTEERING);
	}

	/**
	 * List common accounts, grouped by type
	 * @return array
	 */







|








|















|















|







|







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

	/**
	 * Return common accounting accounts from current chart
	 * (will not return analytical and volunteering accounts)
	 */
	public function listCommonTypes(): array
	{
		return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? AND type != 0 AND type NOT IN (?) ORDER BY code COLLATE U_NOCASE;',
			$this->chart_id, Account::TYPE_ANALYTICAL);
	}

	/**
	 * Return all accounts from current chart
	 */
	public function listAll(): array
	{
		return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? ORDER BY code COLLATE U_NOCASE;',
			$this->chart_id);
	}

	public function listForCodes(array $codes): array
	{
		return DB::getInstance()->getGrouped('SELECT code, id, label FROM acc_accounts WHERE id_chart = ?;', $this->chart_id);
	}

	/**
	 * Return all accounts from current chart
	 */
	public function export(): \Generator
	{
		$res = $this->em->DB()->iterate($this->em->formatQuery('SELECT
			code, label, description, position, type, user AS added
			FROM @TABLE WHERE id_chart = ? ORDER BY code COLLATE U_NOCASE;'),
			$this->chart_id);

		foreach ($res as $row) {
			$row->type = Account::TYPES_NAMES[$row->type];
			$row->position = Account::POSITIONS_NAMES[$row->position];
			$row->added = $row->added ? 'Ajouté' : '';
			yield $row;
		}
	}

	/**
	 * Return only analytical accounts
	 */
	public function listAnalytical(): array
	{
		return $this->em->DB()->getAssoc($this->em->formatQuery('SELECT id, label FROM @TABLE WHERE id_chart = ? AND type = ? ORDER BY label COLLATE U_NOCASE;'), $this->chart_id, Account::TYPE_ANALYTICAL);
	}

	/**
	 * Return only analytical accounts
	 */
	public function listVolunteering(): array
	{
		return $this->em->all('SELECT * FROM @TABLE WHERE id_chart = ? AND type = ? ORDER BY code COLLATE U_NOCASE;',
			$this->chart_id, Account::TYPE_VOLUNTEERING);
	}

	/**
	 * List common accounts, grouped by type
	 * @return array
	 */
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
					'label'    => $label,
					'type'     => $key,
					'accounts' => [],
				];
			}
		}

		$query = $this->em->iterate('SELECT * FROM @TABLE WHERE id_chart = ? AND type != 0 ' . $types . ' ORDER BY type, code COLLATE NOCASE;',
			$this->chart_id);

		foreach ($query as $row) {
			if (!isset($out[$row->type])) {
				$out[$row->type] = (object) [
					'label'    => Account::TYPES_NAMES[$row->type],
					'type'     => $row->type,







|







131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
					'label'    => $label,
					'type'     => $key,
					'accounts' => [],
				];
			}
		}

		$query = $this->em->iterate('SELECT * FROM @TABLE WHERE id_chart = ? AND type != 0 ' . $types . ' ORDER BY type, code COLLATE U_NOCASE;',
			$this->chart_id);

		foreach ($query as $row) {
			if (!isset($out[$row->type])) {
				$out[$row->type] = (object) [
					'label'    => Account::TYPES_NAMES[$row->type],
					'type'     => $row->type,

Modified src/include/lib/Garradin/Accounting/Reports.php from [4ecd1e93c2] to [1d9d7b8cf9].

87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
			FROM acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			INNER JOIN acc_accounts a ON a.id = l.id_analytical
			INNER JOIN acc_years y ON y.id = t.id_year
			GROUP BY %s
			ORDER BY %s;';

		$order = $order_code ? 'a.code COLLATE NOCASE' : 'a.label COLLATE NOCASE';

		if ($by_year) {
			$group = 'y.id, a.id';
			$order = 'y.start_date DESC, ' . $order;
		}
		else {
			$group = 'a.id, y.id';







|







87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
			FROM acc_transactions_lines l
			INNER JOIN acc_transactions t ON t.id = l.id_transaction
			INNER JOIN acc_accounts a ON a.id = l.id_analytical
			INNER JOIN acc_years y ON y.id = t.id_year
			GROUP BY %s
			ORDER BY %s;';

		$order = $order_code ? 'a.code COLLATE U_NOCASE' : 'a.label COLLATE U_NOCASE';

		if ($by_year) {
			$group = 'y.id, a.id';
			$order = 'y.start_date DESC, ' . $order;
		}
		else {
			$group = 'a.id, y.id';
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
		return (int)$a - (int)$b * -1;
	}

	static public function getClosingSumsWithAccounts(array $criterias, ?string $order = null, bool $reverse = false, bool $remove_zero = true): array
	{
		$where = self::getWhereClause($criterias);

		$order = $order ?: 'a.code COLLATE NOCASE';
		$reverse = $reverse ? '* -1' : '';
		$remove_zero = $remove_zero ? 'HAVING sum != 0' : '';

		$query = 'SELECT a.code, a.id, a.label, a.position, SUM(l.credit) AS credit, SUM(l.debit) AS debit,
			SUM(l.credit - l.debit) %s AS sum
			FROM %s l
			INNER JOIN %s t ON t.id = l.id_transaction







|







248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
		return (int)$a - (int)$b * -1;
	}

	static public function getClosingSumsWithAccounts(array $criterias, ?string $order = null, bool $reverse = false, bool $remove_zero = true): array
	{
		$where = self::getWhereClause($criterias);

		$order = $order ?: 'a.code COLLATE U_NOCASE';
		$reverse = $reverse ? '* -1' : '';
		$remove_zero = $remove_zero ? 'HAVING sum != 0' : '';

		$query = 'SELECT a.code, a.id, a.label, a.position, SUM(l.credit) AS credit, SUM(l.debit) AS debit,
			SUM(l.credit - l.debit) %s AS sum
			FROM %s l
			INNER JOIN %s t ON t.id = l.id_transaction
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
		$sql = sprintf('SELECT a.id, a.code, a.label, a.description, a.type,
			SUM(l.credit) - SUM(l.debit) AS sum
			FROM %s a
			INNER JOIN %s t ON t.id = l.id_transaction
			INNER JOIN %s l ON a.id = l.id_account
			WHERE a.type != 0 AND %s
			GROUP BY l.id_account
			ORDER BY a.type, a.code COLLATE NOCASE;', Account::TABLE, Transaction::TABLE, Line::TABLE, $where);

		$group = null;

		foreach (DB::getInstance()->iterate($sql) as $row) {
			if (null !== $group && $row->type !== $group->type) {
				yield $group;
				$group = null;







|







375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
		$sql = sprintf('SELECT a.id, a.code, a.label, a.description, a.type,
			SUM(l.credit) - SUM(l.debit) AS sum
			FROM %s a
			INNER JOIN %s t ON t.id = l.id_transaction
			INNER JOIN %s l ON a.id = l.id_account
			WHERE a.type != 0 AND %s
			GROUP BY l.id_account
			ORDER BY a.type, a.code COLLATE U_NOCASE;', Account::TABLE, Transaction::TABLE, Line::TABLE, $where);

		$group = null;

		foreach (DB::getInstance()->iterate($sql) as $row) {
			if (null !== $group && $row->type !== $group->type) {
				yield $group;
				$group = null;
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
			t.id_year, a.id AS id_account, t.id, t.date, t.reference,
			l.debit, l.credit, l.reference AS line_reference, t.label, l.label AS line_label,
			a.label AS account_label, a.code AS account_code
			FROM acc_transactions t
			INNER JOIN acc_transactions_lines l ON l.id_transaction = t.id
			INNER JOIN %s
			WHERE %s
			ORDER BY a.code COLLATE NOCASE, t.date, t.id;', $join, $where);

		$account = null;
		$debit = $credit = 0;

		foreach ($db->iterate($sql) as $row) {
			if (null !== $account && $account->id != $row->id_account) {
				yield $account;







|







428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
			t.id_year, a.id AS id_account, t.id, t.date, t.reference,
			l.debit, l.credit, l.reference AS line_reference, t.label, l.label AS line_label,
			a.label AS account_label, a.code AS account_code
			FROM acc_transactions t
			INNER JOIN acc_transactions_lines l ON l.id_transaction = t.id
			INNER JOIN %s
			WHERE %s
			ORDER BY a.code COLLATE U_NOCASE, t.date, t.id;', $join, $where);

		$account = null;
		$debit = $credit = 0;

		foreach ($db->iterate($sql) as $row) {
			if (null !== $account && $account->id != $row->id_account) {
				yield $account;

Modified src/include/lib/Garradin/Config.php from [7d53795c85] to [086e7a057d].

236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
		$this->assert(!empty($champs->get($this->champ_identite)), sprintf('Le champ spécifié pour identité, "%s" n\'existe pas', $this->champ_identite));
		$this->assert(!empty($champs->get($this->champ_identifiant)), sprintf('Le champ spécifié pour identifiant, "%s" n\'existe pas', $this->champ_identifiant));

		$db = DB::getInstance();

		// Check that this field is actually unique
		if (isset($this->_modified['champ_identifiant'])) {
			$sql = sprintf('SELECT (COUNT(DISTINCT %s COLLATE NOCASE) = COUNT(*)) FROM membres WHERE %1$s IS NOT NULL AND %1$s != \'\';', $this->champ_identifiant);
			$is_unique = (bool) $db->firstColumn($sql);

			$this->assert($is_unique, sprintf('Le champ "%s" comporte des doublons et ne peut donc pas servir comme identifiant unique de connexion.', $this->champ_identifiant));
		}

		$this->assert($db->test('users_categories', 'id = ?', $this->categorie_membres), 'Catégorie de membres inconnue');
	}







|







236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
		$this->assert(!empty($champs->get($this->champ_identite)), sprintf('Le champ spécifié pour identité, "%s" n\'existe pas', $this->champ_identite));
		$this->assert(!empty($champs->get($this->champ_identifiant)), sprintf('Le champ spécifié pour identifiant, "%s" n\'existe pas', $this->champ_identifiant));

		$db = DB::getInstance();

		// Check that this field is actually unique
		if (isset($this->_modified['champ_identifiant'])) {
			$sql = sprintf('SELECT (COUNT(DISTINCT %s COLLATE U_NOCASE) = COUNT(*)) FROM membres WHERE %1$s IS NOT NULL AND %1$s != \'\';', $this->champ_identifiant);
			$is_unique = (bool) $db->firstColumn($sql);

			$this->assert($is_unique, sprintf('Le champ "%s" comporte des doublons et ne peut donc pas servir comme identifiant unique de connexion.', $this->champ_identifiant));
		}

		$this->assert($db->test('users_categories', 'id = ?', $this->categorie_membres), 'Catégorie de membres inconnue');
	}

Modified src/include/lib/Garradin/Files/Storage/SQLite.php from [3cfb59d324] to [99e5f6c9f5].

118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
	{
		$sql = 'SELECT * FROM @TABLE WHERE path = ? LIMIT 1;';
		return EM::findOne(File::class, $sql, $path);
	}

	static public function list(string $path): array
	{
		return EM::getInstance(File::class)->all('SELECT * FROM @TABLE WHERE parent = ? ORDER BY type DESC, name COLLATE NOCASE ASC;', $path);
	}

	static public function listDirectoriesRecursively(string $path): array
	{
		$files = [];
		$it = DB::getInstance()->iterate('SELECT path FROM files WHERE (parent = ? OR parent LIKE ?) AND type = ? ORDER BY path;', $path, $path . '/%', File::TYPE_DIRECTORY);








|







118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
	{
		$sql = 'SELECT * FROM @TABLE WHERE path = ? LIMIT 1;';
		return EM::findOne(File::class, $sql, $path);
	}

	static public function list(string $path): array
	{
		return EM::getInstance(File::class)->all('SELECT * FROM @TABLE WHERE parent = ? ORDER BY type DESC, name COLLATE U_NOCASE ASC;', $path);
	}

	static public function listDirectoriesRecursively(string $path): array
	{
		$files = [];
		$it = DB::getInstance()->iterate('SELECT path FROM files WHERE (parent = ? OR parent LIKE ?) AND type = ? ORDER BY path;', $path, $path . '/%', File::TYPE_DIRECTORY);

Modified src/include/lib/Garradin/Membres.php from [d8d2f9c7cf] to [38f3fd4508].

157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
            {
                throw new UserException('Ce numéro de membre est déjà attribué à un autre membre.');
            }
        }

        $this->_checkFields($data, true, $require_password);

        if (isset($data[$id]) && $db->test('membres', $id . ' = ? COLLATE NOCASE', $data[$id]))
        {
            throw new UserException('La valeur du champ '.$id.' est déjà utilisée par un autre membre, or ce champ doit être unique à chaque membre.');
        }

        if (isset($data['passe']) && trim($data['passe']) != '')
        {
            Session::checkPasswordValidity($data['passe']);







|







157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
            {
                throw new UserException('Ce numéro de membre est déjà attribué à un autre membre.');
            }
        }

        $this->_checkFields($data, true, $require_password);

        if (isset($data[$id]) && $db->test('membres', $id . ' = ? COLLATE U_NOCASE', $data[$id]))
        {
            throw new UserException('La valeur du champ '.$id.' est déjà utilisée par un autre membre, or ce champ doit être unique à chaque membre.');
        }

        if (isset($data['passe']) && trim($data['passe']) != '')
        {
            Session::checkPasswordValidity($data['passe']);
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210

        unset($data['id']);

        $this->_checkFields($data, $check_editable, false);
        $champ_id = $config->get('champ_identifiant');

        if (!empty($data[$champ_id])
            && $db->firstColumn('SELECT 1 FROM membres WHERE '.$champ_id.' = ? COLLATE NOCASE AND id != ? LIMIT 1;', $data[$champ_id], (int)$id))
        {
            throw new UserException('La valeur du champ '.$champ_id.' est déjà utilisée par un autre membre, or ce champ doit être unique à chaque membre.');
        }

        if (isset($data['numero']))
        {
            if (!preg_match('/^\d+$/', $data['numero']))







|







196
197
198
199
200
201
202
203
204
205
206
207
208
209
210

        unset($data['id']);

        $this->_checkFields($data, $check_editable, false);
        $champ_id = $config->get('champ_identifiant');

        if (!empty($data[$champ_id])
            && $db->firstColumn('SELECT 1 FROM membres WHERE '.$champ_id.' = ? COLLATE U_NOCASE AND id != ? LIMIT 1;', $data[$champ_id], (int)$id))
        {
            throw new UserException('La valeur du champ '.$champ_id.' est déjà utilisée par un autre membre, or ce champ doit être unique à chaque membre.');
        }

        if (isset($data['numero']))
        {
            if (!preg_match('/^\d+$/', $data['numero']))

Modified src/include/lib/Garradin/Membres/Session.php from [3164774ac4] to [f6b4098759].

100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
	{
		$champ_id = Config::getInstance()->get('champ_identifiant');

		// Ne renvoie un membre que si celui-ci a le droit de se connecter
		$query = 'SELECT m.id, m.%1$s AS login, m.passe AS password, m.secret_otp AS otp_secret
			FROM membres AS m
			INNER JOIN users_categories AS c ON c.id = m.id_category
			WHERE m.%1$s = ? COLLATE NOCASE AND c.perm_connect >= %2$d
			LIMIT 1;';

		$query = sprintf($query, $champ_id, self::ACCESS_READ);

		return $this->db->first($query, $login);
	}








|







100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
	{
		$champ_id = Config::getInstance()->get('champ_identifiant');

		// Ne renvoie un membre que si celui-ci a le droit de se connecter
		$query = 'SELECT m.id, m.%1$s AS login, m.passe AS password, m.secret_otp AS otp_secret
			FROM membres AS m
			INNER JOIN users_categories AS c ON c.id = m.id_category
			WHERE m.%1$s = ? COLLATE U_NOCASE AND c.perm_connect >= %2$d
			LIMIT 1;';

		$query = sprintf($query, $champ_id, self::ACCESS_READ);

		return $this->db->first($query, $login);
	}

231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
	public function recoverPasswordSend($id)
	{
		$db = DB::getInstance();
		$config = Config::getInstance();

		$champ_id = $config->get('champ_identifiant');

		$membre = $db->first('SELECT id, email, passe, clef_pgp FROM membres WHERE '.$champ_id.' = ? COLLATE NOCASE LIMIT 1;', trim($id));

		if (!$membre || trim($membre->email) == '')
		{
			return false;
		}

		// valide pour 1 heure minimum







|







231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
	public function recoverPasswordSend($id)
	{
		$db = DB::getInstance();
		$config = Config::getInstance();

		$champ_id = $config->get('champ_identifiant');

		$membre = $db->first('SELECT id, email, passe, clef_pgp FROM membres WHERE '.$champ_id.' = ? COLLATE U_NOCASE LIMIT 1;', trim($id));

		if (!$membre || trim($membre->email) == '')
		{
			return false;
		}

		// valide pour 1 heure minimum

Modified src/include/lib/Garradin/Recherche.php from [91b2769fec] to [469ee57460].

229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
			$champs = Config::getInstance()->get('champs_membres');

			$columns['id_category'] = (object) [
					'textMatch'=> false,
					'label'    => 'Catégorie',
					'type'     => 'enum',
					'null'     => false,
					'values'   => $db->getAssoc('SELECT id, name FROM users_categories ORDER BY name COLLATE NOCASE;'),
				];

			foreach ($champs->getList() as $champ => $config)
			{
				$column = (object) [
					'textMatch'=> $champs->isText($champ),
					'label'    => $config->title,







|







229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
			$champs = Config::getInstance()->get('champs_membres');

			$columns['id_category'] = (object) [
					'textMatch'=> false,
					'label'    => 'Catégorie',
					'type'     => 'enum',
					'null'     => false,
					'values'   => $db->getAssoc('SELECT id, name FROM users_categories ORDER BY name COLLATE U_NOCASE;'),
				];

			foreach ($champs->getList() as $champ => $config)
			{
				$column = (object) [
					'textMatch'=> $champs->isText($champ),
					'label'    => $config->title,
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
			$query_columns = array_merge(['t.id', 't.date', 't.label', 'l.debit', 'l.credit', 'a.code'], $query_columns);
		}

		$query_columns[] = $order;

		if ($target_columns[$order]->textMatch)
		{
			$order = sprintf('%s COLLATE NOCASE', $db->quoteIdentifier($order));
		}
		else
		{
			$order = $db->quoteIdentifier($order);
		}

		$query_columns = array_unique($query_columns);







|







538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
			$query_columns = array_merge(['t.id', 't.date', 't.label', 'l.debit', 'l.credit', 'a.code'], $query_columns);
		}

		$query_columns[] = $order;

		if ($target_columns[$order]->textMatch)
		{
			$order = sprintf('%s COLLATE U_NOCASE', $db->quoteIdentifier($order));
		}
		else
		{
			$order = $db->quoteIdentifier($order);
		}

		$query_columns = array_unique($query_columns);

Modified src/include/lib/Garradin/Services/Fees.php from [f232ea6af4] to [79eefc5ba2].

41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
	 * If $user_id is specified, then it will return a column 'user_amount' containing the amount that this specific user should pay
	 */
	static public function listAllByService(?int $user_id = null)
	{
		$db = DB::getInstance();

		$sql = 'SELECT *, CASE WHEN amount THEN amount ELSE NULL END AS user_amount
			FROM services_fees ORDER BY id_service, amount IS NULL, label COLLATE NOCASE;';
		$result = $db->get($sql);

		if (!$user_id) {
			return $result;
		}

		foreach ($result as &$row) {







|







41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
	 * If $user_id is specified, then it will return a column 'user_amount' containing the amount that this specific user should pay
	 */
	static public function listAllByService(?int $user_id = null)
	{
		$db = DB::getInstance();

		$sql = 'SELECT *, CASE WHEN amount THEN amount ELSE NULL END AS user_amount
			FROM services_fees ORDER BY id_service, amount IS NULL, label COLLATE U_NOCASE;';
		$result = $db->get($sql);

		if (!$user_id) {
			return $result;
		}

		foreach ($result as &$row) {
74
75
76
77
78
79
80
81
82
83
84
85

		$sql = sprintf('SELECT f.*,
			(%s AND (expiry_date IS NULL OR expiry_date >= date()) AND paid = 1) AS nb_users_ok,
			(%1$s AND expiry_date < date()) AS nb_users_expired,
			(%1$s AND paid = 0) AS nb_users_unpaid
			FROM services_fees f
			WHERE id_service = ?
			ORDER BY amount, label COLLATE NOCASE;', $condition);

		return $db->get($sql, $this->service_id);
	}
}







|




74
75
76
77
78
79
80
81
82
83
84
85

		$sql = sprintf('SELECT f.*,
			(%s AND (expiry_date IS NULL OR expiry_date >= date()) AND paid = 1) AS nb_users_ok,
			(%1$s AND expiry_date < date()) AS nb_users_expired,
			(%1$s AND paid = 0) AS nb_users_unpaid
			FROM services_fees f
			WHERE id_service = ?
			ORDER BY amount, label COLLATE U_NOCASE;', $condition);

		return $db->get($sql, $this->service_id);
	}
}

Modified src/include/lib/Garradin/Services/Reminders.php from [c3c5d5e6f2] to [53373807bd].

14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
use const Garradin\ADMIN_URL;

class Reminders
{
	static public function list()
	{
		return DB::getInstance()->get('SELECT s.label AS service_label, sr.* FROM services_reminders sr INNER JOIN services s ON s.id = sr.id_service
			ORDER BY s.label COLLATE NOCASE;');
	}

	static public function get(int $id)
	{
		return EntityManager::findOneById(Reminder::class, $id);
	}








|







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
use const Garradin\ADMIN_URL;

class Reminders
{
	static public function list()
	{
		return DB::getInstance()->get('SELECT s.label AS service_label, sr.* FROM services_reminders sr INNER JOIN services s ON s.id = sr.id_service
			ORDER BY s.label COLLATE U_NOCASE;');
	}

	static public function get(int $id)
	{
		return EntityManager::findOneById(Reminder::class, $id);
	}

Modified src/include/lib/Garradin/Services/Services.php from [244e4a733a] to [039f3505c8].

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
	static public function get(int $id)
	{
		return EntityManager::findOneById(Service::class, $id);
	}

	static public function listAssoc()
	{
		return DB::getInstance()->getAssoc('SELECT id, label FROM services ORDER BY label COLLATE NOCASE;');
	}

	static public function count()
	{
		return DB::getInstance()->count(Service::TABLE, 1);
	}

	static public function listGroupedWithFees(?int $user_id = null, bool $current_only = true)
	{
		$where = $current_only ? 'WHERE end_date IS NULL OR end_date >= date()' : 'WHERE end_date IS NOT NULL AND end_date < date()';

		$sql = sprintf('SELECT
			id, label, duration, start_date, end_date, description,
			CASE WHEN end_date IS NOT NULL THEN end_date WHEN duration IS NOT NULL THEN date(\'now\', \'+\'||duration||\' days\') ELSE NULL END AS expiry_date
			FROM services %s ORDER BY label COLLATE NOCASE;', $where);

		$services = DB::getInstance()->getGrouped($sql);
		$fees = Fees::listAllByService($user_id);
		$out = [];

		foreach ($services as $service) {
			$out[$service->id] = $service;







|














|







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
	static public function get(int $id)
	{
		return EntityManager::findOneById(Service::class, $id);
	}

	static public function listAssoc()
	{
		return DB::getInstance()->getAssoc('SELECT id, label FROM services ORDER BY label COLLATE U_NOCASE;');
	}

	static public function count()
	{
		return DB::getInstance()->count(Service::TABLE, 1);
	}

	static public function listGroupedWithFees(?int $user_id = null, bool $current_only = true)
	{
		$where = $current_only ? 'WHERE end_date IS NULL OR end_date >= date()' : 'WHERE end_date IS NOT NULL AND end_date < date()';

		$sql = sprintf('SELECT
			id, label, duration, start_date, end_date, description,
			CASE WHEN end_date IS NOT NULL THEN end_date WHEN duration IS NOT NULL THEN date(\'now\', \'+\'||duration||\' days\') ELSE NULL END AS expiry_date
			FROM services %s ORDER BY label COLLATE U_NOCASE;', $where);

		$services = DB::getInstance()->getGrouped($sql);
		$fees = Fees::listAllByService($user_id);
		$out = [];

		foreach ($services as $service) {
			$out[$service->id] = $service;
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

		$sql = sprintf('SELECT s.*,
			(%s AND (expiry_date IS NULL OR expiry_date >= date()) AND paid = 1) AS nb_users_ok,
			(%1$s AND expiry_date < date()) AS nb_users_expired,
			(%1$s AND paid = 0) AS nb_users_unpaid
			FROM services s
			WHERE 1 AND %s
			ORDER BY s.label COLLATE NOCASE;', $condition, $current_condition);

		return $db->get($sql);
	}

	static public function countOldServices(): int
	{
		return DB::getInstance()->count(Service::TABLE, 'end_date IS NOT NULL AND end_date < datetime()');
	}
}







|









66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

		$sql = sprintf('SELECT s.*,
			(%s AND (expiry_date IS NULL OR expiry_date >= date()) AND paid = 1) AS nb_users_ok,
			(%1$s AND expiry_date < date()) AS nb_users_expired,
			(%1$s AND paid = 0) AS nb_users_unpaid
			FROM services s
			WHERE 1 AND %s
			ORDER BY s.label COLLATE U_NOCASE;', $condition, $current_condition);

		return $db->get($sql);
	}

	static public function countOldServices(): int
	{
		return DB::getInstance()->count(Service::TABLE, 'end_date IS NOT NULL AND end_date < datetime()');
	}
}

Modified src/include/lib/Garradin/UserTemplate/Sections.php from [b168f12ea3] to [5d9f8c3096].

247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
		}

		if (empty($params['order'])) {
			$params['order'] = 'name';
		}

		if ($params['order'] == 'name') {
			$params['order'] .= ' COLLATE NOCASE';
		}

		foreach (self::sql($params, $tpl, $line) as $row) {
			$file = Files::get($row['path']);

			if (null === $file) {
				continue;







|







247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
		}

		if (empty($params['order'])) {
			$params['order'] = 'name';
		}

		if ($params['order'] == 'name') {
			$params['order'] .= ' COLLATE U_NOCASE';
		}

		foreach (self::sql($params, $tpl, $line) as $row) {
			$file = Files::get($row['path']);

			if (null === $file) {
				continue;

Modified src/include/lib/Garradin/Users/Categories.php from [b3ce602bbd] to [9baeb0d492].

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
	static public function get(int $id): ?Category
	{
		return EM::findOneById(Category::class, $id);
	}

	static public function listSimple(): array
	{
		return DB::getInstance()->getAssoc(sprintf('SELECT id, name FROM %s ORDER BY name COLLATE NOCASE;', Category::TABLE));
	}

	static public function listWithStats(): array
	{
		return DB::getInstance()->getGrouped(sprintf('SELECT c.id, c.*,
			(SELECT COUNT(*) FROM membres WHERE id_category = c.id) AS count
			FROM %s c ORDER BY c.name COLLATE NOCASE;', Category::TABLE));
	}

	static public function listHidden(): array
	{
		return DB::getInstance()->getAssoc(sprintf('SELECT id, name FROM %s WHERE hidden = 1
			ORDER BY name COLLATE NOCASE;', Category::TABLE));
	}

	static public function listNotHidden(): array
	{
		return DB::getInstance()->getAssoc(sprintf('SELECT id, name FROM %s WHERE hidden = 0
			ORDER BY name COLLATE NOCASE;', Category::TABLE));
	}
}







|






|





|





|


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
	static public function get(int $id): ?Category
	{
		return EM::findOneById(Category::class, $id);
	}

	static public function listSimple(): array
	{
		return DB::getInstance()->getAssoc(sprintf('SELECT id, name FROM %s ORDER BY name COLLATE U_NOCASE;', Category::TABLE));
	}

	static public function listWithStats(): array
	{
		return DB::getInstance()->getGrouped(sprintf('SELECT c.id, c.*,
			(SELECT COUNT(*) FROM membres WHERE id_category = c.id) AS count
			FROM %s c ORDER BY c.name COLLATE U_NOCASE;', Category::TABLE));
	}

	static public function listHidden(): array
	{
		return DB::getInstance()->getAssoc(sprintf('SELECT id, name FROM %s WHERE hidden = 1
			ORDER BY name COLLATE U_NOCASE;', Category::TABLE));
	}

	static public function listNotHidden(): array
	{
		return DB::getInstance()->getAssoc(sprintf('SELECT id, name FROM %s WHERE hidden = 0
			ORDER BY name COLLATE U_NOCASE;', Category::TABLE));
	}
}

Modified src/include/lib/Garradin/Web/Web.php from [8972356329] to [19b827614f].

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
			$page->save();
		}
		 */
	}

	static public function listCategories(string $parent): array
	{
		$sql = 'SELECT * FROM @TABLE WHERE parent = ? AND type = ? ORDER BY title COLLATE NOCASE;';
		return EM::getInstance(Page::class)->all($sql, $parent, Page::TYPE_CATEGORY);
	}

	static public function listPages(string $parent, bool $order_by_date = true): array
	{
		$order = $order_by_date ? 'published DESC' : 'title COLLATE NOCASE';
		$sql = sprintf('SELECT * FROM @TABLE WHERE parent = ? AND type = %d ORDER BY %s;', Page::TYPE_PAGE, $order);
		return EM::getInstance(Page::class)->all($sql, $parent);
	}

	static public function listAll(string $parent): array
	{
		$sql = 'SELECT * FROM @TABLE WHERE parent = ? ORDER BY title COLLATE NOCASE;';
		return EM::getInstance(Page::class)->all($sql, $parent);
	}

	static public function get(string $path): ?Page
	{
		$page = EM::findOne(Page::class, 'SELECT * FROM @TABLE WHERE path = ?;', $path);








|





|






|







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
			$page->save();
		}
		 */
	}

	static public function listCategories(string $parent): array
	{
		$sql = 'SELECT * FROM @TABLE WHERE parent = ? AND type = ? ORDER BY title COLLATE U_NOCASE;';
		return EM::getInstance(Page::class)->all($sql, $parent, Page::TYPE_CATEGORY);
	}

	static public function listPages(string $parent, bool $order_by_date = true): array
	{
		$order = $order_by_date ? 'published DESC' : 'title COLLATE U_NOCASE';
		$sql = sprintf('SELECT * FROM @TABLE WHERE parent = ? AND type = %d ORDER BY %s;', Page::TYPE_PAGE, $order);
		return EM::getInstance(Page::class)->all($sql, $parent);
	}

	static public function listAll(string $parent): array
	{
		$sql = 'SELECT * FROM @TABLE WHERE parent = ? ORDER BY title COLLATE U_NOCASE;';
		return EM::getInstance(Page::class)->all($sql, $parent);
	}

	static public function get(string $path): ?Page
	{
		$page = EM::findOne(Page::class, 'SELECT * FROM @TABLE WHERE path = ?;', $path);