Overview
Comment:Implement sending of reminders
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA1: a77f4d0ca36a628552fadeaff85d594fc9c16cf0
User & Date: bohwaz on 2020-11-11 19:08:37
Other Links: branch diff | manifest | tags
Context
2020-11-11
22:21
Implement projects view (analytical) check-in: 18c3a56ab9 user: bohwaz tags: dev
19:08
Implement sending of reminders check-in: a77f4d0ca3 user: bohwaz tags: dev
19:08
Improve handling of cases where URI or HOST cannot be found check-in: 2dd6d2e7ce user: bohwaz tags: dev
Changes

Modified src/cron.php from [4160290bf1] to [d355372705].

1
2
3


4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?php

namespace Garradin;



require_once __DIR__ . '/include/init.php';

// Exécution des tâches automatiques

if (ENABLE_AUTOMATIC_BACKUPS && $config->get('frequence_sauvegardes') && $config->get('nombre_sauvegardes'))
{
	$s = new Sauvegarde;
	$s->auto();
}

/*
// Exécution des rappels automatiques
$rappels = new Rappels;

if ($rappels->countAll())
{
	$rappels->sendPending();
}
*/



>
>











<

<
<
<
<
|
<
<
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

17




18


<?php

namespace Garradin;

use Garradin\Services\Reminders;

require_once __DIR__ . '/include/init.php';

// Exécution des tâches automatiques

if (ENABLE_AUTOMATIC_BACKUPS && $config->get('frequence_sauvegardes') && $config->get('nombre_sauvegardes'))
{
	$s = new Sauvegarde;
	$s->auto();
}


// Exécution des rappels automatiques




Reminders::sendPending();


Modified src/include/data/1.0.0_migration.sql from [2ef23b0ff0] to [b8b1c41876].

130
131
132
133
134
135
136
137


138
139
140
141
142
143
144
		WHEN c.fin IS NOT NULL THEN c.fin
		ELSE NULL
	END
	FROM cotisations_membres cm
	INNER JOIN cotisations c ON c.id = cm.id_cotisation;

INSERT INTO services_reminders SELECT * FROM rappels;
INSERT INTO services_reminders_sent SELECT id, id_membre, id_cotisation, id_rappel, date FROM rappels_envoyes;



DROP TABLE cotisations;
DROP TABLE cotisations_membres;
DROP TABLE rappels;
DROP TABLE rappels_envoyes;

-- Suppression inutilisées







|
>
>







130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
		WHEN c.fin IS NOT NULL THEN c.fin
		ELSE NULL
	END
	FROM cotisations_membres cm
	INNER JOIN cotisations c ON c.id = cm.id_cotisation;

INSERT INTO services_reminders SELECT * FROM rappels;
INSERT INTO services_reminders_sent SELECT id, id_membre, id_cotisation,
	CASE WHEN id_rappel IS NULL THEN (SELECT MAX(id) FROM rappels) ELSE id_rappel END, date
	FROM rappels_envoyes;

DROP TABLE cotisations;
DROP TABLE cotisations_membres;
DROP TABLE rappels;
DROP TABLE rappels_envoyes;

-- Suppression inutilisées

Modified src/include/data/1.0.0_schema.sql from [8324ddb304] to [dec3798374].

96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
CREATE TABLE IF NOT EXISTS services_reminders_sent
-- Enregistrement des rappels envoyés à qui et quand
(
    id INTEGER NOT NULL PRIMARY KEY,

    id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_reminder INTEGER NULL REFERENCES services_reminders (id) ON DELETE CASCADE,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date)
);

--
-- WIKI
--







|







96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
CREATE TABLE IF NOT EXISTS services_reminders_sent
-- Enregistrement des rappels envoyés à qui et quand
(
    id INTEGER NOT NULL PRIMARY KEY,

    id_user INTEGER NOT NULL REFERENCES membres (id) ON DELETE CASCADE,
    id_service INTEGER NOT NULL REFERENCES services (id) ON DELETE CASCADE,
    id_reminder INTEGER NOT NULL REFERENCES services_reminders (id) ON DELETE CASCADE,

    date TEXT NOT NULL DEFAULT CURRENT_DATE CHECK (date(date) IS NOT NULL AND date(date) = date)
);

--
-- WIKI
--

Modified src/include/lib/Garradin/Services/Reminders.php from [8d39c92907] to [79db0ae213].

1
2
3
4
5
6


7
8
9



10
11
12
13
14
15
16
<?php

namespace Garradin\Services;

use Garradin\Config;
use Garradin\DB;


use Garradin\Entities\Services\Reminder;
use KD2\DB\EntityManager;




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;');
	}






>
>



>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php

namespace Garradin\Services;

use Garradin\Config;
use Garradin\DB;
use Garradin\Plugin;
use Garradin\Utils;
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 NOCASE;');
	}
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
112
113
114
115
116
117
118
119
120
121
122



123
124
125

126
127
128
129
130
131
132

133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
		}

		return strtr($content, $tags);
	}

	/**
	 * Envoi de mail pour rappel automatisé
	 * @param  array $data Données du rappel automatisé
	 * @return boolean     TRUE
	 */
	static public function sendAuto($reminder)
	{
		$replace = (array) $data;

		$replace['date_rappel'] = Utils::sqliteDateToFrench($replace['date_rappel']);
		$replace['date_expiration'] = Utils::sqliteDateToFrench($replace['expiration']);
		$replace['nb_jours'] = abs($replace['nb_jours']);
		$replace['delai'] = abs($replace['delai']);


		$subject = self::replaceTagsInContent($data->sujet, $replace);
		$text = self::replaceTagsInContent($data->texte, $replace);

		// Envoi du mail
		Utils::sendEmail(Utils::EMAIL_CONTEXT_PRIVATE, $data->email, $subject, $text, $data->id);

		$db = DB::getInstance();
		$db->insert('services_reminders_sent', [
			'id_service'  => $reminder->id_service,
			'id_user'     => $reminder->id_user,
			'id_reminder' => $reminder->id_reminder,
			// On enregistre la date de mise en œuvre du rappel
			// et non pas la date d'envoi effective du rappel
			// car l'envoi du rappel peut ne pas être effectué
			// le jour où il aurait dû être envoyé (la magie des cron)
			'date'        => $reminder->sent_date,
		]);

		Plugin::fireSignal('rappels.auto', $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 MIN(sr.delay), sr.subject, sr.body,



			s.label, s.description, su.id_service, su.id_user,
			m.email, m.%s AS identity, su.expiry_date
			FROM services_users su

			INNER JOIN services_reminders sr ON sr.id_service = 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_categorie NOT IN (SELECT id FROM membres_categories WHERE cacher = 1))
			-- Join with sent reminders to exclude users that already have received this reminder
			LEFT JOIN services_reminders_sent srs ON srs.id_reminder = sr.id AND srs.id_user = su.id_user

			WHERE date() > date(su.expiry_date, sr.delay || \' days\')
			AND srs.id IS NULL
			GROUP BY su.id_user, su.id_service
			ORDER BY su.id_user;';

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

		$db->begin();

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

		$db->commit();
		return true;
	}
}







<
<

|

|
>
|
|
|
|
>

|
|


|






<
<
<
<
<
















|
>
>
>
|
|

>







>
|
|
|




<
<





<



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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144


145
146
147
148
149

150
151
152
		}

		return strtr($content, $tags);
	}

	/**
	 * Envoi de mail pour rappel automatisé


	 */
	static public function sendAuto(\stdClass $reminder)
	{
		$replace = [
			'identite'        => $reminder->identity,
			'date_rappel'     => Utils::sqliteDateToFrench($reminder->reminder_date),
			'date_expiration' => Utils::sqliteDateToFrench($reminder->expiry_date),
			'nb_jours'        => $reminder->nb_days,
			'delai'           => $reminder->delay,
		];

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

		// Envoi du mail
		Utils::sendEmail(Utils::EMAIL_CONTEXT_PRIVATE, $reminder->email, $subject, $text, $reminder->id_user);

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





		]);

		Plugin::fireSignal('rappels.auto', $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_users su
			INNER JOIN services s ON s.id = su.id_service
			INNER JOIN services_reminders sr ON sr.id_service = 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_categorie NOT IN (SELECT id FROM membres_categories WHERE cacher = 1))
			-- Join with sent reminders to exclude users that already have received this reminder
			LEFT JOIN services_reminders_sent srs ON srs.id_reminder = sr.id AND srs.id_user = su.id_user
			WHERE
				date() > date(su.expiry_date, sr.delay || \' days\')
				AND srs.id IS NULL
			GROUP BY su.id_user, s.id
			ORDER BY su.id_user;';

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



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


		return true;
	}
}

Modified src/templates/services/reminders/_form.tpl from [cf129dd53f] to [101bc0b369].

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
			{input type="text" name="subject" required=1 source=$reminder default=$default_subject label="Sujet du message envoyé"}

			<dt><label for="f_delay_type_0">Délai d'envoi</label> <b title="(Champ obligatoire)">obligatoire</b></dt>
			{input type="radio" name="delay_type" value=0 default=$delay_type label="Le jour de l'expiration de l'activité"}
			<dd>
				{input type="radio" name="delay_type" value=1 default=$delay_type}
				{input type="number" name="delay_before" min=1 max=999 default=$delay_before size=4}
				<label for="f_delay_1">jours <strong>avant</strong> expiration</label>
			</dd>
			<dd>
				{input type="radio" name="delay_type" value=2 default=$delay_type}
				{input type="number" name="delay_after" min=1 max=999 size=4 default=$delay_after}
				<label for="f_delay_2">jours <strong>après</strong> expiration</label>
			</dd>
			{input type="textarea" name="body" required=1 source=$reminder default=$default_body label="Texte du message envoyé" help="Pour inclure dans le contenu du mail le nom du membre, utilisez #IDENTITE, pour inclure le délai de l'envoi utilisez #NB_JOURS." cols="90" rows="15"}
		</dl>
	</fieldset>

	<p class="submit">
		{csrf_field key=$csrf_key}







|




|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
			{input type="text" name="subject" required=1 source=$reminder default=$default_subject label="Sujet du message envoyé"}

			<dt><label for="f_delay_type_0">Délai d'envoi</label> <b title="(Champ obligatoire)">obligatoire</b></dt>
			{input type="radio" name="delay_type" value=0 default=$delay_type label="Le jour de l'expiration de l'activité"}
			<dd>
				{input type="radio" name="delay_type" value=1 default=$delay_type}
				{input type="number" name="delay_before" min=1 max=999 default=$delay_before size=4}
				<label for="f_delay_type_1">jours <strong>avant</strong> expiration</label>
			</dd>
			<dd>
				{input type="radio" name="delay_type" value=2 default=$delay_type}
				{input type="number" name="delay_after" min=1 max=999 size=4 default=$delay_after}
				<label for="f_delay_type_2">jours <strong>après</strong> expiration</label>
			</dd>
			{input type="textarea" name="body" required=1 source=$reminder default=$default_body label="Texte du message envoyé" help="Pour inclure dans le contenu du mail le nom du membre, utilisez #IDENTITE, pour inclure le délai de l'envoi utilisez #NB_JOURS." cols="90" rows="15"}
		</dl>
	</fieldset>

	<p class="submit">
		{csrf_field key=$csrf_key}