Overview
Comment:Fix reminder date in message body Fix: get last reminder sent, not any random one
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable
Files: files | file ages | folders
SHA3-256: 3faf645a604570ec6588dcbde85eacadcfb415a0e0973bdc70e960b1f5f46112
User & Date: bohwaz on 2021-06-01 22:03:50
Other Links: manifest | tags
Context
2021-06-01
22:08
Order reminders by sent order check-in: 2273bb1c80 user: bohwaz tags: trunk, stable
22:03
Fix reminder date in message body Fix: get last reminder sent, not any random one check-in: 3faf645a60 user: bohwaz tags: trunk, stable
21:37
Fix index check-in: db1d98471a user: bohwaz tags: trunk, stable
Changes

Modified src/include/lib/Garradin/Services/Reminders.php from [db0958aeaf] to [c3c5d5e6f2].

101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
...
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
	/**
	 * Envoi de mail pour rappel automatisé
	 */
	static public function sendAuto(\stdClass $reminder)
	{
		$replace = [
			'identite'        => $reminder->identity,
			'date_rappel'     => Utils::date_fr($reminder->reminder_date),
			'date_expiration' => Utils::date_fr($reminder->expiry_date),
			'nb_jours'        => $reminder->nb_days,
			'delai'           => $reminder->delay,
		];

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

................................................................................
		$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 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 OR srs.due_date < date(su.expiry_date, sr.delay || \' 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;
	}
}







|
|







 







|









|


|













101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
...
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
	/**
	 * 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);

................................................................................
		$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;
	}
}