Artifact baf59edfaac1fc8a73cbf57dace591c4c422b7bd8b33360249c5c778ebddf744:


<?php

namespace Garradin\Services;

use Garradin\Config;
use Garradin\DB;
use Garradin\DynamicList;
use Garradin\Users\Categories;
use Garradin\Entities\Services\Service;
use KD2\DB\EntityManager;

class Services
{
	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;
			$out[$service->id]->fees = [];
		}

		foreach ($fees as $fee) {
			if (isset($out[$fee->id_service])) {
				$out[$fee->id_service]->fees[] = $fee;
			}
		}

		return $out;
	}

	static public function listWithStats(bool $current_only = true): DynamicList
	{
		$db = DB::getInstance();
		$hidden_cats = array_keys(Categories::listHidden());

		$sql = sprintf('DROP TABLE IF EXISTS services_list_stats;
			CREATE TEMP TABLE IF NOT EXISTS services_list_stats (id_service, id_user, ok, expired, paid);
			INSERT INTO services_list_stats SELECT
				id_service, id_user,
				CASE WHEN (expiry_date IS NULL OR expiry_date >= date()) AND paid = 1 THEN 1 ELSE 0 END,
				CASE WHEN expiry_date < date() THEN 1 ELSE 0 END,
				paid
			FROM services_users su
			INNER JOIN (SELECT id, MAX(date) FROM services_users GROUP BY id_user, id_service) su2 ON su2.id = su.id
			INNER JOIN users u ON u.id = su.id_user WHERE %s',
			$db->where('u.id_category', 'NOT IN', $hidden_cats));

		$db->exec($sql);


		$columns = [
			'id' => [],
			'duration' => [],
			'start_date' => [],
			'end_date' => [],
			'label' => [
				'label' => 'Activité',
			],
			'date' => [
				'label' => 'Période',
				'order' => 'start_date %s, duration %1$s',
				'select' => 'CASE WHEN start_date IS NULL THEN duration ELSE NULL END',
			],
			'nb_users_ok' => [
				'label' => 'Membres à jour',
				'order' => null,
				'select' => '(SELECT COUNT(DISTINCT id_user) FROM services_list_stats WHERE id_service = services.id AND ok = 1)',
			],
			'nb_users_expired' => [
				'label' => 'Membres expirés',
				'order' => null,
				'select' => '(SELECT COUNT(DISTINCT id_user) FROM services_list_stats WHERE id_service = services.id AND expired = 1)',
			],
			'nb_users_unpaid' => [
				'label' => 'Membres en attente de règlement',
				'order' => null,
				'select' => '(SELECT COUNT(DISTINCT id_user) FROM services_list_stats WHERE id_service = services.id AND paid = 0)',
			],
		];

		$current_condition = $current_only ? '(end_date IS NULL OR end_date >= datetime())' : '(end_date IS NOT NULL AND end_date < datetime())';

		$list = new DynamicList($columns, 'services', $current_condition);
		$list->setPageSize(null);
		$list->orderBy('label', false);
		return $list;
	}

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