Artifact 48d8e7ac836e33a8165b38e7d715b1ad4b5afd645c020f397e3795d19240932a:


<?php
declare(strict_types=1);

namespace Garradin\Users;

use Garradin\Entities\Users\Category;
use Garradin\Entities\Users\User;

use Garradin\Files\Files;
use Garradin\Entities\Files\File;

use Garradin\Config;
use Garradin\CSV;
use Garradin\CSV_Custom;
use Garradin\DB;
use Garradin\DynamicList;
use Garradin\Search;
use Garradin\Utils;
use Garradin\UserException;

use KD2\SMTP;
use KD2\DB\EntityManager as EM;

class Users
{
	static public function create(): User
	{
		$default_category = Config::getInstance()->default_category;
		$user = new User;
		$user->set('id_category', $default_category);
		return $user;
	}

	static public function iterateAssocByCategory(?int $id_category = null): iterable
	{
		$where = $id_category ? sprintf('id_category = %d', $id_category) : 'id_category IN (SELECT id FROM users_categories WHERE hidden = 0)';

		$sql = sprintf('SELECT id, %s AS name FROM users WHERE %s ORDER BY name COLLATE U_NOCASE;',
			DynamicFields::getNameFieldsSQL(),
			$where);

		foreach (DB::getInstance()->iterate($sql) as $row) {
			yield $row->id => $row->name;
		}
	}

	static protected function iterateEmails(array $sql, string $email_column = '_email'): \Generator
	{
		foreach (DB::getInstance()->iterate(implode(' UNION ALL ', $sql)) as $row) {
			yield $row->$email_column => $row;
		}
	}

	/**
	 * Return a list for all emails by category
	 * @param  int|null $id_category If NULL, then all categories except hidden ones will be returned
	 */
	static public function iterateEmailsByCategory(?int $id_category = null): iterable
	{
		$db = DB::getInstance();
		$fields = DynamicFields::getEmailFields();
		$sql = [];
		$where = $id_category ? sprintf('id_category = %d', $id_category) : 'id_category IN (SELECT id FROM users_categories WHERE hidden = 0)';

		foreach ($fields as $field) {
			$sql[] = sprintf('SELECT *, %s AS _email, NULL AS preferences FROM users WHERE %s AND %1$s IS NOT NULL', $db->quoteIdentifier($field), $where);
		}

		return self::iterateEmails($sql);
	}

	/**
	 * Return a list of all emails by service (user must be active)
	 */
	static public function iterateEmailsByActiveService(int $id_service): iterable
	{
		$db = DB::getInstance();

		// Create a temporary table
		if (!$db->test('sqlite_temp_master', 'type = \'table\' AND name=\'users_active_services\'')) {
			$db->exec('DROP TABLE IF EXISTS users_active_services;
				CREATE TEMPORARY TABLE IF NOT EXISTS users_active_services (id, service);
				INSERT INTO users_active_services SELECT id_user, id_service FROM (
					SELECT id_user, id_service, MAX(expiry_date) FROM services_users
					WHERE expiry_date IS NULL OR expiry_date >= date()
					GROUP BY id_user, id_service
				);
				DELETE FROM users_active_services WHERE id IN (SELECT id FROM users WHERE id_category IN (SELECT id FROM users_categories WHERE hidden =1));');
		}

		$fields = DynamicFields::getEmailFields();
		$sql = [];

		foreach ($fields as $field) {
			$sql[] = sprintf('SELECT u.*, u.%s AS _email, NULL AS preferences FROM users u INNER JOIN users_active_services s ON s.id = u.id
				WHERE s.service = %d AND %1$s IS NOT NULL', $db->quoteIdentifier($field), $id_service);
		}

		return self::iterateEmails($sql);
	}

	static public function iterateEmailsBySearch(int $id_search): iterable
	{
		$db = DB::getInstance();

		$s = Search::get($id_search);
		// Make sure the query is protected and safe, by doing a protectSelect
		$s->query(['limit' => 1]);

		$header = $s->getHeader();
		$id_column = null;

		if (in_array('id', $header)) {
			$id_column = 'id';
		}
		elseif (in_array('_user_id', $header)) {
			$id_column = '_user_id';
		}
		else {
			throw new UserException('La recherche ne comporte pas de colonne "id" ou "_user_id", et donc ne permet pas l\'envoi d\'email.');
		}

		// We only need the user id, store it in a temporary table for now
		$db->exec('DROP TABLE IF EXISTS users_tmp_search; CREATE TEMPORARY TABLE IF NOT EXISTS users_tmp_search (id);');
		$db->exec(sprintf('INSERT INTO users_tmp_search SELECT %s FROM (%s)', $id_column, $s->SQL()));

		$fields = DynamicFields::getEmailFields();

		$sql = [];

		foreach ($fields as $field) {
			$sql[] = sprintf('SELECT u.*, u.%s AS _email, NULL AS preferences FROM users u INNER JOIN users_tmp_search AS s ON s.id = u.id', $db->quoteIdentifier($field));
		}

		return self::iterateEmails($sql);
	}

	static public function listByCategory(?int $id_category = null): DynamicList
	{
		$df = DynamicFields::getInstance();
		$number_field = $df->getNumberField();
		$name_fields = $df->getNameFields();

		$columns = [
			'_user_id' => [
				'select' => 'users.id',
			],
		];

		$number_column = [
			'label' => 'Num.',
			'select' => 'users.' . $number_field,
		];

		$identity_column = [
			'label' => $df->getNameLabel(),
			'select' => $df->getNameFieldsSQL('users'),
			'order' => 'identity COLLATE U_NOCASE %s',
		];

		$fields = $df->getListedFields();

		foreach ($fields as $key => $config) {
			// Skip number field
			if ($key === $number_field) {
				if (null !== $number_column) {
					$columns['number'] = $number_column;
					$number_column = null;
				}

				continue;
			}
			// Skip name fields
			elseif (in_array($key, $name_fields)) {
				if (null !== $identity_column) {
					$columns['identity'] = $identity_column;
					$identity_column = null;
				}

				continue;
			}

			$columns[$key] = [
				'label'  => $config->label,
				'select' => 'users.' . $key,
			];

			if ($df->isText($key)) {
				$columns[$key]['order'] = sprintf('%s COLLATE U_NOCASE %%s', $key);
			}
		}

		if (null !== $identity_column) {
			$columns['identity'] = $identity_column;
		}

		$tables = User::TABLE;
		$db = DB::getInstance();

		if ($db->test('users', 'is_parent = 1')) {
			$tables .= ' LEFT JOIN users b ON b.id = users.id_parent';

			$columns['id_parent'] = [
				'label'  => 'Rattaché à',
				'select' => 'users.id_parent',
				'order'  => 'users.id_parent IS NULL, _parent_name COLLATE U_NOCASE %s, identity COLLATE U_NOCASE %1$s',
			];

			$columns['_parent_name'] = [
				'select' => sprintf('CASE WHEN users.id_parent IS NOT NULL THEN %s ELSE NULL END', $df->getNameFieldsSQL('b')),
			];

			$columns['is_parent'] = [
				'label' => 'Responsable',
				'select' => 'users.is_parent',
				'order' => 'users.is_parent DESC, identity COLLATE U_NOCASE %1$s',
			];
		}

		if (!$id_category) {
			$conditions = sprintf('users.id_category IN (SELECT id FROM users_categories WHERE hidden = 0)');
		}
		elseif ($id_category > 0) {
			$conditions = sprintf('users.id_category = %d', $id_category);
		}
		else {
			$conditions = '1';
		}

		$order = 'identity';

		if (!isset($columns[$order])) {
			$order = key($fields) ?? 'number';
		}

		$list = new DynamicList($columns, $tables, $conditions);
		$list->orderBy($order, false);

		return $list;
	}

	static public function get(int $id): ?User
	{
		return EM::findOneById(User::class, $id);
	}

	static public function getName(int $id): ?string
	{
		$name = DynamicFields::getNameFieldsSQL();
		return EM::getInstance(User::class)->col(sprintf('SELECT %s FROM @TABLE WHERE id = ?;', $name), $id);
	}

	static public function getFromNumber(string $number): ?User
	{
		$field = DynamicFields::getNumberField();
		return EM::findOne(User::class, 'SELECT * FROM @TABLE WHERE ' . $field . ' = ?', $number);
	}

	static public function getNameFromNumber(string $number): ?string
	{
		$name = DynamicFields::getNameFieldsSQL();
		$field = DynamicFields::getNumberField();
		return EM::getInstance(User::class)->col(sprintf('SELECT %s FROM @TABLE WHERE %s = ?;', $name, $field), $number);
	}

	static public function deleteSelected(array $ids): void
	{
		$ids = array_map('intval', $ids);

		if ($logged_user_id = Session::getUserId()) {
			if (in_array($logged_user_id, $ids)) {
				throw new UserException('Il n\'est pas possible de supprimer son propre compte.');
			}
		}

		foreach ($ids as $id) {
			Files::delete(File::CONTEXT_USER . '/' . $id);
		}

		$db = DB::getInstance();

		// Suppression du membre
		$db->delete(User::TABLE, $db->where('id', $ids));
	}

	static public function changeCategorySelected(int $category_id, array $ids): void
	{
		$db = DB::getInstance();

		if (!$db->test(Category::TABLE, 'id = ?', $category_id)) {
			throw new \InvalidArgumentException('Invalid category ID: ' . $category_id);
		}

		$ids = array_map('intval', $ids);

		// Don't allow current user ID to change his/her category
		$logged_user_id = Session::getUserId();
		$ids = array_filter($ids, fn($a) => $a != $logged_user_id);

		$db->update(User::TABLE,
			['id_category' => $category_id],
			$db->where('id', $ids)
		);
	}

	static public function exportSelected(string $format, array $ids): void
	{
		$db = DB::getInstance();

		$ids = array_map('intval', $ids);
		$where = $db->where('id', $ids);
		$name = sprintf('Liste de %d membres', count($ids));
		self::exportWhere($format, $name, $where);
	}

	static public function exportCategory(string $format, int $id_category): void
	{
		if ($id_category == -1) {
			$name = 'Tous les membres';
			$where = '1';
		}
		elseif (!$id_category) {
			$name = 'Membres sauf catégories cachées';
			$where = 'id_category NOT IN (SELECT id FROM users_categories WHERE hidden = 1)';
		}
		else {
			$cat = Categories::get($id_category);
			$name = sprintf('Membres - %s', $cat->name);
			$where = sprintf('id_category = %d', $id_category);
		}

		self::exportWhere($format, $name, $where);
	}

	static public function export(string $format): void
	{
		self::exportWhere($format, 'Tous les membres', '1');
	}

	static protected function exportWhere(string $format, string $name, string $where): void
	{
		$df = DynamicFields::getInstance();
		$db = DB::getInstance();

		$header = $df->listAssocNames();
		$columns = array_keys($header);
		$columns = array_map([$db, 'quoteIdentifier'], $columns);
		$columns = implode(', ', $columns);

		$i = $db->iterate(sprintf('SELECT %s FROM users WHERE %s;', $columns, $where));

		$callback = function (&$row) use ($df) {
			foreach ($df->fieldsByType('date') as $f) {
				if (isset($row->{$f->name})) {
					$row->{$f->name} = \DateTime::createFromFormat('!Y-m-d', $row->{$f->name});
				}
			}
			foreach ($df->fieldsByType('datetime') as $f) {
				if (isset($row->{$f->name})) {
					$row->{$f->name} = \DateTime::createFromFormat('!Y-m-d H:i:s', $row->{$f->name});
				}
			}
		};

		CSV::export($format, $name, $i, $header, $callback);
	}

	static public function importReport(CSV_Custom $csv, bool $ignore_ids, int $logged_user_id): array
	{
		$report = ['created' => [], 'modified' => [], 'unchanged' => [], 'has_logged_user' => false];

		foreach (self::iterateImport($csv, $ignore_ids) as $line => $user) {
			if ($user->id == $logged_user_id) {
				$report['has_logged_user'] = true;
				continue;
			}

			try {
				$user->selfCheck();
			}
			catch (UserException $e) {
				throw new UserException(sprintf('Ligne %d (%s) : %s', $line, $user->name(), $e->getMessage()));
			}

			if (!$user->exists()) {
				$report['created'][] = $user;
			}
			elseif ($user->isModified()) {
				$report['modified'][] = $user;
			}
			else {
				$report['unchanged'][] = $user;
			}
		}

		return $report;
	}

	static public function import(CSV_Custom $csv, bool $ignore_ids, int $logged_user_id): void
	{
		$db = DB::getInstance();
		$db->begin();

		foreach (self::iterateImport($csv, $ignore_ids) as $user) {
			if ($user->id == $logged_user_id) {
				continue;
			}

			$user->save();
		}

		$db->commit();
	}

	static public function iterateImport(CSV_Custom $csv, bool $ignore_ids): \Generator
	{
		$number_field = DynamicFields::getNumberField();

		foreach ($csv->iterate() as $i => $row) {
			if ($ignore_ids || !isset($row->$number_field)) {
				$user = self::create();
				$user->$number_field = null;
				$user->setNumberIfEmpty();
				unset($row->$number_field);
			}
			else {
				$user = self::getFromNumber($row->$number_field);
			}

			$user->importForm((array)$row);

			yield $i => $user;
		}
	}
}