Overview
Comment:Fix reminders sent when that reminder was already sent before but since has been renewed
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 85aaa801cb8ff111365d2debf210604187750f787683c933c2bde51778313e76
User & Date: bohwaz on 2021-06-01 21:28:26
Other Links: manifest | tags
Context
2021-06-01
21:34
Fix backup restore with custom NOCASE collation, add migration check-in: bbba6082eb user: bohwaz tags: trunk, stable
21:28
Fix reminders sent when that reminder was already sent before but since has been renewed check-in: 85aaa801cb user: bohwaz tags: trunk
21:27
Try to have a deterministic output check-in: 5fd3652de0 user: bohwaz tags: trunk
Changes

Modified src/include/data/1.1.0_schema.sql from [26acbef5df] to [cce4f78c19].

106
107
108
109
110
111
112
113

114
115
116
117
118
119
120
...
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
(
    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)

);

CREATE UNIQUE INDEX IF NOT EXISTS srs_index ON services_reminders_sent (id_user, id_service, id_reminder, date);

CREATE INDEX IF NOT EXISTS srs_reminder ON services_reminders_sent (id_reminder);
CREATE INDEX IF NOT EXISTS srs_user ON services_reminders_sent (id_user);

................................................................................
    format TEXT NOT NULL,
    published TEXT NOT NULL CHECK (datetime(published) = published),
    modified TEXT NOT NULL CHECK (datetime(modified) = modified),
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

CREATE UNIQUE INDEX web_pages_path ON web_pages (path);
CREATE UNIQUE INDEX web_pages_file_path ON web_pages (file_path);
CREATE INDEX web_pages_parent ON web_pages (parent);
CREATE INDEX web_pages_published ON web_pages (published);
CREATE INDEX web_pages_title ON web_pages (title);

-- FIXME: rename to english
CREATE TABLE IF NOT EXISTS recherches
-- Recherches enregistrées
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_membre INTEGER NULL REFERENCES membres (id) ON DELETE CASCADE, -- Si non NULL, alors la recherche ne sera visible que par le membre associé







|
>







 







|
|
|
|
|







106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
...
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
(
    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,

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

CREATE UNIQUE INDEX IF NOT EXISTS srs_index ON services_reminders_sent (id_user, id_service, id_reminder, date);

CREATE INDEX IF NOT EXISTS srs_reminder ON services_reminders_sent (id_reminder);
CREATE INDEX IF NOT EXISTS srs_user ON services_reminders_sent (id_user);

................................................................................
    format TEXT NOT NULL,
    published TEXT NOT NULL CHECK (datetime(published) = published),
    modified TEXT NOT NULL CHECK (datetime(modified) = modified),
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS web_pages_path ON web_pages (path);
CREATE UNIQUE INDEX IF NOT EXISTS web_pages_file_path ON web_pages (file_path);
CREATE INDEX IF NOT EXISTS web_pages_parent ON web_pages (parent);
CREATE INDEX IF NOT EXISTS web_pages_published ON web_pages (published);
CREATE INDEX IF NOT EXISTS web_pages_title ON web_pages (title);

-- FIXME: rename to english
CREATE TABLE IF NOT EXISTS recherches
-- Recherches enregistrées
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_membre INTEGER NULL REFERENCES membres (id) ON DELETE CASCADE, -- Si non NULL, alors la recherche ne sera visible que par le membre associé

Modified src/include/data/schema.sql from [26acbef5df] to [cce4f78c19].

106
107
108
109
110
111
112
113

114
115
116
117
118
119
120
...
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
(
    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)

);

CREATE UNIQUE INDEX IF NOT EXISTS srs_index ON services_reminders_sent (id_user, id_service, id_reminder, date);

CREATE INDEX IF NOT EXISTS srs_reminder ON services_reminders_sent (id_reminder);
CREATE INDEX IF NOT EXISTS srs_user ON services_reminders_sent (id_user);

................................................................................
    format TEXT NOT NULL,
    published TEXT NOT NULL CHECK (datetime(published) = published),
    modified TEXT NOT NULL CHECK (datetime(modified) = modified),
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

CREATE UNIQUE INDEX web_pages_path ON web_pages (path);
CREATE UNIQUE INDEX web_pages_file_path ON web_pages (file_path);
CREATE INDEX web_pages_parent ON web_pages (parent);
CREATE INDEX web_pages_published ON web_pages (published);
CREATE INDEX web_pages_title ON web_pages (title);

-- FIXME: rename to english
CREATE TABLE IF NOT EXISTS recherches
-- Recherches enregistrées
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_membre INTEGER NULL REFERENCES membres (id) ON DELETE CASCADE, -- Si non NULL, alors la recherche ne sera visible que par le membre associé







|
>







 







|
|
|
|
|







106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
...
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
(
    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,

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

CREATE UNIQUE INDEX IF NOT EXISTS srs_index ON services_reminders_sent (id_user, id_service, id_reminder, date);

CREATE INDEX IF NOT EXISTS srs_reminder ON services_reminders_sent (id_reminder);
CREATE INDEX IF NOT EXISTS srs_user ON services_reminders_sent (id_user);

................................................................................
    format TEXT NOT NULL,
    published TEXT NOT NULL CHECK (datetime(published) = published),
    modified TEXT NOT NULL CHECK (datetime(modified) = modified),
    title TEXT NOT NULL,
    content TEXT NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS web_pages_path ON web_pages (path);
CREATE UNIQUE INDEX IF NOT EXISTS web_pages_file_path ON web_pages (file_path);
CREATE INDEX IF NOT EXISTS web_pages_parent ON web_pages (parent);
CREATE INDEX IF NOT EXISTS web_pages_published ON web_pages (published);
CREATE INDEX IF NOT EXISTS web_pages_title ON web_pages (title);

-- FIXME: rename to english
CREATE TABLE IF NOT EXISTS recherches
-- Recherches enregistrées
(
    id INTEGER NOT NULL PRIMARY KEY,
    id_membre INTEGER NULL REFERENCES membres (id) ON DELETE CASCADE, -- Si non NULL, alors la recherche ne sera visible que par le membre associé

Modified src/include/lib/Garradin/Entities/Services/Reminder.php from [4702329012] to [0d2c963b3c].

76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
				'select' => 'm.' . $identity,
			],
			'email' => [
				'label' => 'Adresse e-mail',
				'select' => 'm.email',
			],
			'date' => [
				'label' => 'Date',
				'select' => 'srs.date',
			],
		];

		$tables = 'services_reminders_sent srs
			INNER JOIN membres m ON m.id = srs.id_user';
		$conditions = sprintf('srs.id_reminder = %d', $this->id());

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

}







|
|













76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
				'select' => 'm.' . $identity,
			],
			'email' => [
				'label' => 'Adresse e-mail',
				'select' => 'm.email',
			],
			'date' => [
				'label' => 'Date d\'envoi',
				'select' => 'srs.sent_date',
			],
		];

		$tables = 'services_reminders_sent srs
			INNER JOIN membres m ON m.id = srs.id_user';
		$conditions = sprintf('srs.id_reminder = %d', $this->id());

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

}

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

34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
..
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
...
118
119
120
121
122
123
124

125
126
127
128
129
130
131
...
139
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
				'select' => 's.label',
			],
			'delay' => [
				'label' => 'Délai du rappel',
				'select' => 'r.delay',
			],
			'date' => [
				'label' => 'Date d\'envoi',
				'select' => 'srs.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 rs.date AS sent_date, r.delay, s.label, rs.id AS sent_id, s.id AS service_id
			FROM services_reminders_sent rs
			INNER JOIN services_reminders r ON r.id = rs.id_reminder
			INNER JOIN services s ON s.id = rs.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);
	}
................................................................................
		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;
	}

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







|
|







 







|
|
|
|







 







>







 







|
|
|
|
>








|
|












34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
..
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
...
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
...
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
				'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);
	}
................................................................................
		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,
			'due_date'    => $reminder->reminder_date,
		]);

		Plugin::fireSignal('rappels.auto', $reminder);

		return true;
	}

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