Artifact 2361eca67b5e4c9a8c572322d20594e4584420d8b29daafb362723718e30e2c1:


<?php

namespace Garradin\Services;

use Garradin\Config;
use Garradin\DB;
use Garradin\DynamicList;
use Garradin\Plugin;
use Garradin\Utils;
use Garradin\Users\Emails;
use Garradin\Entities\Services\Reminder;
use KD2\DB\EntityManager;

use const Garradin\WWW_URL;
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);
	}

	static public function listSentForUser(int $user_id)
	{
		$columns = [
			'label' => [
				'label' => 'Activité',
				'select' => 's.label',
			],
			'delay' => [
				'label' => 'Délai du rappel',
				'select' => 'r.delay',
			],
			'date' => [
				'label' => 'Date d\'envoi du message',
				'select' => 'srs.sent_date',
			],
		];

		$tables = 'services_reminders_sent srs
			INNER JOIN services_reminders r ON r.id = srs.id_reminder
			INNER JOIN services s ON s.id = srs.id_service';
		$conditions = sprintf('srs.id_user = %d', $user_id);

		$list = new DynamicList($columns, $tables, $conditions);
		$list->orderBy('date', true);
		return $list;
	}

	static public function listSentForReminder(int $reminder_id)
	{
		return DB::getInstance()->get('SELECT srs.sent_date, r.delay, s.label, rs.id AS sent_id, s.id AS service_id
			FROM services_reminders_sent srs
			INNER JOIN services_reminders r ON r.id = srs.id_reminder
			INNER JOIN services s ON s.id = srs.id_service
			WHERE rs.id_reminder = ?;', $reminder_id);
	}

	static public function listForService(int $service_id)
	{
		return DB::getInstance()->get('SELECT * FROM services_reminders WHERE id_service = ? ORDER BY delay, subject;', $service_id);
	}

	/**
	 * Remplacer les tags dans le contenu/sujet du mail
	 * @param  string $content Chaîne à traiter
	 * @param  array  $data    Données supplémentaires à utiliser comme tags (tableau associatif)
	 * @return string          $content dont les tags ont été remplacés par le contenu correct
	 */
	static public function replaceTagsInContent(string $content, ?array $data = null)
	{
		$config = Config::getInstance();
		$tags = [
			'#NOM_ASSO'		=>	$config->get('nom_asso'),
			'#ADRESSE_ASSO'	=>	$config->get('adresse_asso'),
			'#EMAIL_ASSO'	=>	$config->get('email_asso'),
			'#SITE_ASSO'	=>	$config->get('site_asso'),
			'#URL_RACINE'	=>	WWW_URL,
			'#URL_SITE'		=>	WWW_URL,
			'#URL_ADMIN'	=>	ADMIN_URL,
		];

		if (!empty($data) && is_array($data))
		{
			foreach ($data as $key=>$value)
			{
				$key = '#' . strtoupper($key);
				$tags[$key] = $value;
			}
		}

		return strtr($content, $tags);
	}

	/**
	 * Envoi de mail pour rappel automatisé
	 */
	static public function sendAuto(\stdClass $reminder)
	{
		$replace = [
			'identite'        => $reminder->identity,
			'date_rappel'     => Utils::date_fr($reminder->reminder_date, 'd/m/Y'),
			'date_expiration' => Utils::date_fr($reminder->expiry_date, 'd/m/Y'),
			'nb_jours'        => $reminder->nb_days,
			'delai'           => $reminder->delay,
		];

		$subject = self::replaceTagsInContent($reminder->subject, $replace);
		$text = self::replaceTagsInContent($reminder->body, $replace);

		// Envoi du mail
		Emails::queue(Emails::CONTEXT_PRIVATE, [$reminder->email => $reminder], null, $subject, $text);

		$db = DB::getInstance();
		$db->insert('services_reminders_sent', [
			'id_service'  => $reminder->id_service,
			'id_user'     => $reminder->id_user,
			'id_reminder' => $reminder->id_reminder,
			'due_date'    => $reminder->reminder_date,
		]);

		Plugin::fireSignal('reminder.send.after', $reminder);

		return true;
	}

	/**
	 * Envoi des rappels automatiques par e-mail
	 * @return boolean TRUE en cas de succès
	 */
	static public function sendPending()
	{
		$db = DB::getInstance();
		$config = Config::getInstance();

		$sql = 'SELECT
			date(su.expiry_date, sr.delay || \' days\') AS reminder_date,
			ABS(julianday(date()) - julianday(expiry_date)) AS nb_days,
			MAX(sr.delay) AS delay, sr.subject, sr.body, s.label, s.description,
			su.expiry_date, sr.id AS id_reminder, su.id_service, su.id_user,
			m.email, m.%s AS identity
			FROM services_reminders sr
			INNER JOIN services s ON s.id = sr.id_service
			-- Select latest subscription to a service (MAX) only
			INNER JOIN (SELECT MAX(expiry_date) AS expiry_date, id_user, id_service FROM services_users GROUP BY id_user, id_service) AS su ON s.id = su.id_service
			-- Join with users, but not ones part of a hidden category
			INNER JOIN membres m ON su.id_user = m.id
				AND m.email IS NOT NULL
				AND (m.id_category NOT IN (SELECT id FROM users_categories WHERE hidden = 1))
			-- Join with sent reminders to exclude users that already have received this reminder
			LEFT JOIN (SELECT id, MAX(due_date) AS due_date, id_user, id_reminder FROM services_reminders_sent GROUP BY id_user, id_reminder) AS srs ON su.id_user = srs.id_user AND srs.id_reminder = sr.id
			WHERE
				date() > date(su.expiry_date, sr.delay || \' days\')
				AND (srs.id IS NULL OR srs.due_date < date(su.expiry_date, (sr.delay - 1) || \' days\'))
			GROUP BY su.id_user, sr.id_service
			ORDER BY su.id_user;';

		$sql = sprintf($sql, $config->get('champ_identite'));

		foreach ($db->iterate($sql) as $row)
		{
			self::sendAuto($row);
		}

		return true;
	}
}