Artifact d83ba1ba188ff5efee2376bdc76ff425310be17e:


<?php

namespace Garradin\Services;

use Garradin\Config;
use Garradin\DB;
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 NOCASE;');
	}

	static public function listGroupedWithFees(?int $user_id = null)
	{
		$services = DB::getInstance()->getGrouped('SELECT id, label, duration, start_date, end_date FROM services;');
		$fees = Fees::listAllByService($user_id);
		$out = [];

		foreach ($fees as $fee) {
			$id = $fee->id_service;

			if (!array_key_exists($id, $out)) {
				$out[$id] = (object) $services[$id];
				$out[$id]->fees = [];
			}

			$out[$id]->fees[] = $fee;
		}

		return $out;
	}

	static public function listWithStats()
	{
		$db = DB::getInstance();
		return $db->get('SELECT s.*,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_service = s.id AND expiry_date >= date() AND paid = 1) AS nb_users_ok,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_service = s.id AND expiry_date < date()) AS nb_users_expired,
			(SELECT COUNT(DISTINCT id_user) FROM services_users WHERE id_service = s.id AND paid = 0) AS nb_users_unpaid
			FROM services s
			ORDER BY transliterate_to_ascii(s.label) COLLATE NOCASE;');
	}
}