1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* This file is part of the TelegramBot package. |
4
|
|
|
* |
5
|
|
|
* (c) Avtandil Kikabidze aka LONGMAN <[email protected]> |
6
|
|
|
* |
7
|
|
|
* For the full copyright and license information, please view the LICENSE |
8
|
|
|
* file that was distributed with this source code. |
9
|
|
|
* Written by Marco Boretto <[email protected]> |
10
|
|
|
*/ |
11
|
|
|
|
12
|
|
|
namespace Longman\TelegramBot; |
13
|
|
|
|
14
|
|
|
use Exception; |
15
|
|
|
use Longman\TelegramBot\Entities\CallbackQuery; |
16
|
|
|
use Longman\TelegramBot\Entities\Chat; |
17
|
|
|
use Longman\TelegramBot\Entities\ChosenInlineResult; |
18
|
|
|
use Longman\TelegramBot\Entities\InlineQuery; |
19
|
|
|
use Longman\TelegramBot\Entities\Message; |
20
|
|
|
use Longman\TelegramBot\Entities\ReplyToMessage; |
21
|
|
|
use Longman\TelegramBot\Entities\Update; |
22
|
|
|
use Longman\TelegramBot\Entities\User; |
23
|
|
|
use Longman\TelegramBot\Exception\TelegramException; |
24
|
|
|
use PDO; |
25
|
|
|
use PDOException; |
26
|
|
|
|
27
|
|
|
class DB |
28
|
|
|
{ |
29
|
|
|
/** |
30
|
|
|
* MySQL credentials |
31
|
|
|
* |
32
|
|
|
* @var array |
33
|
|
|
*/ |
34
|
|
|
static protected $mysql_credentials = []; |
35
|
|
|
|
36
|
|
|
/** |
37
|
|
|
* PDO object |
38
|
|
|
* |
39
|
|
|
* @var PDO |
40
|
|
|
*/ |
41
|
|
|
static protected $pdo; |
42
|
|
|
|
43
|
|
|
/** |
44
|
|
|
* Table prefix |
45
|
|
|
* |
46
|
|
|
* @var string |
47
|
|
|
*/ |
48
|
|
|
static protected $table_prefix; |
49
|
|
|
|
50
|
|
|
/** |
51
|
|
|
* Telegram class object |
52
|
|
|
* |
53
|
|
|
* @var Telegram |
54
|
|
|
*/ |
55
|
|
|
static protected $telegram; |
56
|
|
|
|
57
|
|
|
/** |
58
|
|
|
* Initialize |
59
|
|
|
* |
60
|
|
|
* @param array $credentials Database connection details |
61
|
|
|
* @param Telegram $telegram Telegram object to connect with this object |
62
|
|
|
* @param string $table_prefix Table prefix |
63
|
|
|
* @param string $encoding Database character encoding |
64
|
|
|
* |
65
|
|
|
* @return PDO PDO database object |
66
|
|
|
* @throws TelegramException |
67
|
|
|
*/ |
68
|
9 |
|
public static function initialize( |
69
|
|
|
array $credentials, |
70
|
|
|
Telegram $telegram, |
71
|
|
|
$table_prefix = null, |
72
|
|
|
$encoding = 'utf8mb4' |
73
|
|
|
) { |
74
|
9 |
|
if (empty($credentials)) { |
75
|
|
|
throw new TelegramException('MySQL credentials not provided!'); |
76
|
|
|
} |
77
|
|
|
|
78
|
9 |
|
$dsn = 'mysql:host=' . $credentials['host'] . ';dbname=' . $credentials['database']; |
79
|
9 |
|
$options = [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES ' . $encoding]; |
80
|
|
|
try { |
81
|
9 |
|
$pdo = new PDO($dsn, $credentials['user'], $credentials['password'], $options); |
82
|
9 |
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); |
83
|
|
|
} catch (PDOException $e) { |
84
|
|
|
throw new TelegramException($e->getMessage()); |
85
|
|
|
} |
86
|
|
|
|
87
|
9 |
|
self::$pdo = $pdo; |
88
|
9 |
|
self::$telegram = $telegram; |
89
|
9 |
|
self::$mysql_credentials = $credentials; |
90
|
9 |
|
self::$table_prefix = $table_prefix; |
91
|
|
|
|
92
|
9 |
|
self::defineTables(); |
93
|
|
|
|
94
|
9 |
|
return self::$pdo; |
95
|
|
|
} |
96
|
|
|
|
97
|
|
|
/** |
98
|
|
|
* External Initialize |
99
|
|
|
* |
100
|
|
|
* Let you use the class with an external already existing Pdo Mysql connection. |
101
|
|
|
* |
102
|
|
|
* @param PDO $external_pdo_connection PDO database object |
103
|
|
|
* @param Telegram $telegram Telegram object to connect with this object |
104
|
|
|
* @param string $table_prefix Table prefix |
105
|
|
|
* |
106
|
|
|
* @return PDO PDO database object |
107
|
|
|
* @throws TelegramException |
108
|
|
|
*/ |
109
|
|
|
public static function externalInitialize( |
110
|
|
|
$external_pdo_connection, |
111
|
|
|
Telegram $telegram, |
112
|
|
|
$table_prefix = null |
113
|
|
|
) { |
114
|
|
|
if ($external_pdo_connection === null) { |
115
|
|
|
throw new TelegramException('MySQL external connection not provided!'); |
116
|
|
|
} |
117
|
|
|
|
118
|
|
|
self::$pdo = $external_pdo_connection; |
119
|
|
|
self::$telegram = $telegram; |
120
|
|
|
self::$mysql_credentials = []; |
121
|
|
|
self::$table_prefix = $table_prefix; |
122
|
|
|
|
123
|
|
|
self::defineTables(); |
124
|
|
|
|
125
|
|
|
return self::$pdo; |
126
|
|
|
} |
127
|
|
|
|
128
|
|
|
/** |
129
|
|
|
* Define all the tables with the proper prefix |
130
|
|
|
*/ |
131
|
9 |
|
protected static function defineTables() |
132
|
|
|
{ |
133
|
|
|
$tables = [ |
134
|
9 |
|
'callback_query', |
135
|
|
|
'chat', |
136
|
|
|
'chosen_inline_result', |
137
|
|
|
'edited_message', |
138
|
|
|
'inline_query', |
139
|
|
|
'message', |
140
|
|
|
'request_limiter', |
141
|
|
|
'telegram_update', |
142
|
|
|
'user', |
143
|
|
|
'user_chat', |
144
|
|
|
]; |
145
|
9 |
|
foreach ($tables as $table) { |
146
|
9 |
|
$table_name = 'TB_' . strtoupper($table); |
147
|
9 |
|
if (!defined($table_name)) { |
148
|
9 |
|
define($table_name, self::$table_prefix . $table); |
149
|
|
|
} |
150
|
|
|
} |
151
|
9 |
|
} |
152
|
|
|
|
153
|
|
|
/** |
154
|
|
|
* Check if database connection has been created |
155
|
|
|
* |
156
|
|
|
* @return bool |
157
|
|
|
*/ |
158
|
9 |
|
public static function isDbConnected() |
159
|
|
|
{ |
160
|
9 |
|
return self::$pdo !== null; |
161
|
|
|
} |
162
|
|
|
|
163
|
|
|
/** |
164
|
|
|
* Get the PDO object of the connected database |
165
|
|
|
* |
166
|
|
|
* @return PDO |
167
|
|
|
*/ |
168
|
|
|
public static function getPdo() |
169
|
|
|
{ |
170
|
|
|
return self::$pdo; |
171
|
|
|
} |
172
|
|
|
|
173
|
|
|
/** |
174
|
|
|
* Fetch update(s) from DB |
175
|
|
|
* |
176
|
|
|
* @param int $limit Limit the number of updates to fetch |
177
|
|
|
* @param string $id Check for unique update id |
178
|
|
|
* |
179
|
|
|
* @return array|bool Fetched data or false if not connected |
180
|
|
|
* @throws TelegramException |
181
|
|
|
*/ |
182
|
|
|
public static function selectTelegramUpdate($limit = null, $id = null) |
183
|
|
|
{ |
184
|
|
|
if (!self::isDbConnected()) { |
185
|
|
|
return false; |
186
|
|
|
} |
187
|
|
|
|
188
|
|
|
try { |
189
|
|
|
$sql = ' |
190
|
|
|
SELECT `id` |
191
|
|
|
FROM `' . TB_TELEGRAM_UPDATE . '` |
192
|
|
|
'; |
193
|
|
|
|
194
|
|
|
if ($id !== null) { |
195
|
|
|
$sql .= ' WHERE `id` = :id'; |
196
|
|
|
} else { |
197
|
|
|
$sql .= ' ORDER BY `id` DESC'; |
198
|
|
|
} |
199
|
|
|
|
200
|
|
|
if ($limit !== null) { |
201
|
|
|
$sql .= ' LIMIT :limit'; |
202
|
|
|
} |
203
|
|
|
|
204
|
|
|
$sth = self::$pdo->prepare($sql); |
205
|
|
|
|
206
|
|
|
if ($limit !== null) { |
207
|
|
|
$sth->bindValue(':limit', $limit, PDO::PARAM_INT); |
208
|
|
|
} |
209
|
|
|
if ($id !== null) { |
210
|
|
|
$sth->bindValue(':id', $id); |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
$sth->execute(); |
214
|
|
|
|
215
|
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC); |
216
|
|
|
} catch (PDOException $e) { |
217
|
|
|
throw new TelegramException($e->getMessage()); |
218
|
|
|
} |
219
|
|
|
} |
220
|
|
|
|
221
|
|
|
/** |
222
|
|
|
* Fetch message(s) from DB |
223
|
|
|
* |
224
|
|
|
* @param int $limit Limit the number of messages to fetch |
225
|
|
|
* |
226
|
|
|
* @return array|bool Fetched data or false if not connected |
227
|
|
|
* @throws TelegramException |
228
|
|
|
*/ |
229
|
|
|
public static function selectMessages($limit = null) |
230
|
|
|
{ |
231
|
|
|
if (!self::isDbConnected()) { |
232
|
|
|
return false; |
233
|
|
|
} |
234
|
|
|
|
235
|
|
|
try { |
236
|
|
|
$sql = ' |
237
|
|
|
SELECT * |
238
|
|
|
FROM `' . TB_MESSAGE . '` |
239
|
|
|
ORDER BY `id` DESC |
240
|
|
|
'; |
241
|
|
|
|
242
|
|
|
if ($limit !== null) { |
243
|
|
|
$sql .= ' LIMIT :limit'; |
244
|
|
|
} |
245
|
|
|
|
246
|
|
|
$sth = self::$pdo->prepare($sql); |
247
|
|
|
|
248
|
|
|
if ($limit !== null) { |
249
|
|
|
$sth->bindValue(':limit', $limit, PDO::PARAM_INT); |
250
|
|
|
} |
251
|
|
|
|
252
|
|
|
$sth->execute(); |
253
|
|
|
|
254
|
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC); |
255
|
|
|
} catch (PDOException $e) { |
256
|
|
|
throw new TelegramException($e->getMessage()); |
257
|
|
|
} |
258
|
|
|
} |
259
|
|
|
|
260
|
|
|
/** |
261
|
|
|
* Convert from unix timestamp to timestamp |
262
|
|
|
* |
263
|
|
|
* @param int $time Unix timestamp (if null, current timestamp is used) |
264
|
|
|
* |
265
|
|
|
* @return string |
266
|
|
|
*/ |
267
|
7 |
|
protected static function getTimestamp($time = null) |
268
|
|
|
{ |
269
|
7 |
|
if ($time === null) { |
270
|
6 |
|
$time = time(); |
271
|
|
|
} |
272
|
|
|
|
273
|
7 |
|
return date('Y-m-d H:i:s', $time); |
274
|
|
|
} |
275
|
|
|
|
276
|
|
|
/** |
277
|
|
|
* Convert array of Entity items to a JSON array |
278
|
|
|
* |
279
|
|
|
* @todo Find a better way, as json_* functions are very heavy |
280
|
|
|
* |
281
|
|
|
* @param array|null $entities |
282
|
|
|
* @param mixed $default |
283
|
|
|
* |
284
|
|
|
* @return mixed |
285
|
|
|
*/ |
286
|
6 |
|
public static function entitiesArrayToJson($entities, $default = null) |
287
|
|
|
{ |
288
|
6 |
|
if (!is_array($entities)) { |
289
|
6 |
|
return $default; |
290
|
|
|
} |
291
|
|
|
|
292
|
|
|
// Convert each Entity item into an object based on its JSON reflection |
293
|
|
|
$json_entities = array_map(function ($entity) { |
294
|
|
|
return json_decode($entity, true); |
295
|
|
|
}, $entities); |
296
|
|
|
|
297
|
|
|
return json_encode($json_entities); |
298
|
|
|
} |
299
|
|
|
|
300
|
|
|
/** |
301
|
|
|
* Insert entry to telegram_update table |
302
|
|
|
* |
303
|
|
|
* @todo Add missing values! See https://core.telegram.org/bots/api#update |
304
|
|
|
* |
305
|
|
|
* @param string $id |
306
|
|
|
* @param string $chat_id |
307
|
|
|
* @param string $message_id |
308
|
|
|
* @param string $inline_query_id |
309
|
|
|
* @param string $chosen_inline_result_id |
310
|
|
|
* @param string $callback_query_id |
311
|
|
|
* @param string $edited_message_id |
312
|
|
|
* |
313
|
|
|
* @return bool If the insert was successful |
314
|
|
|
* @throws TelegramException |
315
|
|
|
*/ |
316
|
|
|
public static function insertTelegramUpdate( |
317
|
|
|
$id, |
318
|
|
|
$chat_id = null, |
319
|
|
|
$message_id = null, |
320
|
|
|
$inline_query_id = null, |
321
|
|
|
$chosen_inline_result_id = null, |
322
|
|
|
$callback_query_id = null, |
323
|
|
|
$edited_message_id = null |
324
|
|
|
) { |
325
|
|
|
if ($message_id === null && $inline_query_id === null && $chosen_inline_result_id === null && $callback_query_id === null && $edited_message_id === null) { |
326
|
|
|
throw new TelegramException('message_id, inline_query_id, chosen_inline_result_id, callback_query_id, edited_message_id are all null'); |
327
|
|
|
} |
328
|
|
|
|
329
|
|
|
if (!self::isDbConnected()) { |
330
|
|
|
return false; |
331
|
|
|
} |
332
|
|
|
|
333
|
|
|
try { |
334
|
|
|
$sth = self::$pdo->prepare(' |
335
|
|
|
INSERT IGNORE INTO `' . TB_TELEGRAM_UPDATE . '` |
336
|
|
|
(`id`, `chat_id`, `message_id`, `inline_query_id`, `chosen_inline_result_id`, `callback_query_id`, `edited_message_id`) |
337
|
|
|
VALUES |
338
|
|
|
(:id, :chat_id, :message_id, :inline_query_id, :chosen_inline_result_id, :callback_query_id, :edited_message_id) |
339
|
|
|
'); |
340
|
|
|
|
341
|
|
|
$sth->bindValue(':id', $id); |
342
|
|
|
$sth->bindValue(':chat_id', $chat_id); |
343
|
|
|
$sth->bindValue(':message_id', $message_id); |
344
|
|
|
$sth->bindValue(':edited_message_id', $edited_message_id); |
345
|
|
|
$sth->bindValue(':inline_query_id', $inline_query_id); |
346
|
|
|
$sth->bindValue(':chosen_inline_result_id', $chosen_inline_result_id); |
347
|
|
|
$sth->bindValue(':callback_query_id', $callback_query_id); |
348
|
|
|
|
349
|
|
|
return $sth->execute(); |
350
|
|
|
} catch (PDOException $e) { |
351
|
|
|
throw new TelegramException($e->getMessage()); |
352
|
|
|
} |
353
|
|
|
} |
354
|
|
|
|
355
|
|
|
/** |
356
|
|
|
* Insert users and save their connection to chats |
357
|
|
|
* |
358
|
|
|
* @param User $user |
359
|
|
|
* @param string $date |
360
|
|
|
* @param Chat $chat |
361
|
|
|
* |
362
|
|
|
* @return bool If the insert was successful |
363
|
|
|
* @throws TelegramException |
364
|
|
|
*/ |
365
|
6 |
|
public static function insertUser(User $user, $date, Chat $chat = null) |
366
|
|
|
{ |
367
|
6 |
|
if (!self::isDbConnected()) { |
368
|
|
|
return false; |
369
|
|
|
} |
370
|
|
|
|
371
|
|
|
try { |
372
|
6 |
|
$sth = self::$pdo->prepare(' |
373
|
6 |
|
INSERT INTO `' . TB_USER . '` |
374
|
|
|
(`id`, `is_bot`, `username`, `first_name`, `last_name`, `language_code`, `created_at`, `updated_at`) |
375
|
|
|
VALUES |
376
|
|
|
(:id, :is_bot, :username, :first_name, :last_name, :language_code, :created_at, :updated_at) |
377
|
|
|
ON DUPLICATE KEY UPDATE |
378
|
|
|
`is_bot` = VALUES(`is_bot`), |
379
|
|
|
`username` = VALUES(`username`), |
380
|
|
|
`first_name` = VALUES(`first_name`), |
381
|
|
|
`last_name` = VALUES(`last_name`), |
382
|
|
|
`language_code` = VALUES(`language_code`), |
383
|
|
|
`updated_at` = VALUES(`updated_at`) |
384
|
|
|
'); |
385
|
|
|
|
386
|
6 |
|
$sth->bindValue(':id', $user->getId()); |
387
|
6 |
|
$sth->bindValue(':is_bot', $user->getIsBot(), PDO::PARAM_INT); |
388
|
6 |
|
$sth->bindValue(':username', $user->getUsername()); |
389
|
6 |
|
$sth->bindValue(':first_name', $user->getFirstName()); |
390
|
6 |
|
$sth->bindValue(':last_name', $user->getLastName()); |
391
|
6 |
|
$sth->bindValue(':language_code', $user->getLanguageCode()); |
392
|
6 |
|
$sth->bindValue(':created_at', $date); |
393
|
6 |
|
$sth->bindValue(':updated_at', $date); |
394
|
|
|
|
395
|
6 |
|
$status = $sth->execute(); |
396
|
|
|
} catch (PDOException $e) { |
397
|
|
|
throw new TelegramException($e->getMessage()); |
398
|
|
|
} |
399
|
|
|
|
400
|
|
|
// Also insert the relationship to the chat into the user_chat table |
401
|
6 |
|
if ($chat instanceof Chat) { |
402
|
|
|
try { |
403
|
6 |
|
$sth = self::$pdo->prepare(' |
404
|
6 |
|
INSERT IGNORE INTO `' . TB_USER_CHAT . '` |
405
|
|
|
(`user_id`, `chat_id`) |
406
|
|
|
VALUES |
407
|
|
|
(:user_id, :chat_id) |
408
|
|
|
'); |
409
|
|
|
|
410
|
6 |
|
$sth->bindValue(':user_id', $user->getId()); |
411
|
6 |
|
$sth->bindValue(':chat_id', $chat->getId()); |
412
|
|
|
|
413
|
6 |
|
$status = $sth->execute(); |
414
|
|
|
} catch (PDOException $e) { |
415
|
|
|
throw new TelegramException($e->getMessage()); |
416
|
|
|
} |
417
|
|
|
} |
418
|
|
|
|
419
|
6 |
|
return $status; |
420
|
|
|
} |
421
|
|
|
|
422
|
|
|
/** |
423
|
|
|
* Insert chat |
424
|
|
|
* |
425
|
|
|
* @param Chat $chat |
426
|
|
|
* @param string $date |
427
|
|
|
* @param string $migrate_to_chat_id |
428
|
|
|
* |
429
|
|
|
* @return bool If the insert was successful |
430
|
|
|
* @throws TelegramException |
431
|
|
|
*/ |
432
|
6 |
|
public static function insertChat(Chat $chat, $date, $migrate_to_chat_id = null) |
433
|
|
|
{ |
434
|
6 |
|
if (!self::isDbConnected()) { |
435
|
|
|
return false; |
436
|
|
|
} |
437
|
|
|
|
438
|
|
|
try { |
439
|
6 |
|
$sth = self::$pdo->prepare(' |
440
|
6 |
|
INSERT IGNORE INTO `' . TB_CHAT . '` |
441
|
|
|
(`id`, `type`, `title`, `username`, `all_members_are_administrators`, `created_at` ,`updated_at`, `old_id`) |
442
|
|
|
VALUES |
443
|
|
|
(:id, :type, :title, :username, :all_members_are_administrators, :created_at, :updated_at, :old_id) |
444
|
|
|
ON DUPLICATE KEY UPDATE |
445
|
|
|
`type` = VALUES(`type`), |
446
|
|
|
`title` = VALUES(`title`), |
447
|
|
|
`username` = VALUES(`username`), |
448
|
|
|
`all_members_are_administrators` = VALUES(`all_members_are_administrators`), |
449
|
|
|
`updated_at` = VALUES(`updated_at`) |
450
|
|
|
'); |
451
|
|
|
|
452
|
6 |
|
$chat_id = $chat->getId(); |
453
|
6 |
|
$chat_type = $chat->getType(); |
454
|
|
|
|
455
|
6 |
|
if ($migrate_to_chat_id) { |
|
|
|
|
456
|
|
|
$chat_type = 'supergroup'; |
457
|
|
|
|
458
|
|
|
$sth->bindValue(':id', $migrate_to_chat_id); |
459
|
|
|
$sth->bindValue(':old_id', $chat_id); |
460
|
|
|
} else { |
461
|
6 |
|
$sth->bindValue(':id', $chat_id); |
462
|
6 |
|
$sth->bindValue(':old_id', $migrate_to_chat_id); |
463
|
|
|
} |
464
|
|
|
|
465
|
6 |
|
$sth->bindValue(':type', $chat_type); |
466
|
6 |
|
$sth->bindValue(':title', $chat->getTitle()); |
467
|
6 |
|
$sth->bindValue(':username', $chat->getUsername()); |
468
|
6 |
|
$sth->bindValue(':all_members_are_administrators', $chat->getAllMembersAreAdministrators(), PDO::PARAM_INT); |
469
|
6 |
|
$sth->bindValue(':created_at', $date); |
470
|
6 |
|
$sth->bindValue(':updated_at', $date); |
471
|
|
|
|
472
|
6 |
|
return $sth->execute(); |
473
|
|
|
} catch (PDOException $e) { |
474
|
|
|
throw new TelegramException($e->getMessage()); |
475
|
|
|
} |
476
|
|
|
} |
477
|
|
|
|
478
|
|
|
/** |
479
|
|
|
* Insert request into database |
480
|
|
|
* |
481
|
|
|
* @todo self::$pdo->lastInsertId() - unsafe usage if expected previous insert fails? |
482
|
|
|
* |
483
|
|
|
* @param Update $update |
484
|
|
|
* |
485
|
|
|
* @return bool |
486
|
|
|
* @throws TelegramException |
487
|
|
|
*/ |
488
|
|
|
public static function insertRequest(Update $update) |
489
|
|
|
{ |
490
|
|
|
if (!self::isDbConnected()) { |
491
|
|
|
return false; |
492
|
|
|
} |
493
|
|
|
|
494
|
|
|
$update_id = $update->getUpdateId(); |
495
|
|
|
$update_type = $update->getUpdateType(); |
496
|
|
|
|
497
|
|
|
if (count(self::selectTelegramUpdate(1, $update_id)) === 1) { |
498
|
|
|
throw new TelegramException('Duplicate update received!'); |
499
|
|
|
} |
500
|
|
|
|
501
|
|
|
// @todo Make this simpler: if ($message = $update->getMessage()) ... |
502
|
|
|
if ($update_type === 'message') { |
503
|
|
|
$message = $update->getMessage(); |
504
|
|
|
|
505
|
|
View Code Duplication |
if (self::insertMessageRequest($message)) { |
|
|
|
|
506
|
|
|
$message_id = $message->getMessageId(); |
507
|
|
|
$chat_id = $message->getChat()->getId(); |
508
|
|
|
|
509
|
|
|
return self::insertTelegramUpdate( |
510
|
|
|
$update_id, |
511
|
|
|
$chat_id, |
512
|
|
|
$message_id |
513
|
|
|
); |
514
|
|
|
} |
515
|
|
View Code Duplication |
} elseif ($update_type === 'edited_message') { |
|
|
|
|
516
|
|
|
$edited_message = $update->getEditedMessage(); |
517
|
|
|
|
518
|
|
|
if (self::insertEditedMessageRequest($edited_message)) { |
519
|
|
|
$edited_message_local_id = self::$pdo->lastInsertId(); |
520
|
|
|
$chat_id = $edited_message->getChat()->getId(); |
521
|
|
|
|
522
|
|
|
return self::insertTelegramUpdate( |
523
|
|
|
$update_id, |
524
|
|
|
$chat_id, |
525
|
|
|
null, |
526
|
|
|
null, |
527
|
|
|
null, |
528
|
|
|
null, |
529
|
|
|
$edited_message_local_id |
530
|
|
|
); |
531
|
|
|
} |
532
|
|
|
} elseif ($update_type === 'channel_post') { |
533
|
|
|
$channel_post = $update->getChannelPost(); |
534
|
|
|
|
535
|
|
View Code Duplication |
if (self::insertMessageRequest($channel_post)) { |
|
|
|
|
536
|
|
|
$message_id = $channel_post->getMessageId(); |
537
|
|
|
$chat_id = $channel_post->getChat()->getId(); |
538
|
|
|
|
539
|
|
|
return self::insertTelegramUpdate( |
540
|
|
|
$update_id, |
541
|
|
|
$chat_id, |
542
|
|
|
$message_id |
543
|
|
|
); |
544
|
|
|
} |
545
|
|
View Code Duplication |
} elseif ($update_type === 'edited_channel_post') { |
|
|
|
|
546
|
|
|
$edited_channel_post = $update->getEditedChannelPost(); |
547
|
|
|
|
548
|
|
|
if (self::insertEditedMessageRequest($edited_channel_post)) { |
549
|
|
|
$edited_channel_post_local_id = self::$pdo->lastInsertId(); |
550
|
|
|
$chat_id = $edited_channel_post->getChat()->getId(); |
551
|
|
|
|
552
|
|
|
return self::insertTelegramUpdate( |
553
|
|
|
$update_id, |
554
|
|
|
$chat_id, |
555
|
|
|
null, |
556
|
|
|
null, |
557
|
|
|
null, |
558
|
|
|
null, |
559
|
|
|
$edited_channel_post_local_id |
560
|
|
|
); |
561
|
|
|
} |
562
|
|
|
} elseif ($update_type === 'inline_query') { |
563
|
|
|
$inline_query = $update->getInlineQuery(); |
564
|
|
|
|
565
|
|
|
if (self::insertInlineQueryRequest($inline_query)) { |
566
|
|
|
$inline_query_id = $inline_query->getId(); |
567
|
|
|
|
568
|
|
|
return self::insertTelegramUpdate( |
569
|
|
|
$update_id, |
570
|
|
|
null, |
571
|
|
|
null, |
572
|
|
|
$inline_query_id |
573
|
|
|
); |
574
|
|
|
} |
575
|
|
|
} elseif ($update_type === 'chosen_inline_result') { |
576
|
|
|
$chosen_inline_result = $update->getChosenInlineResult(); |
577
|
|
|
|
578
|
|
|
if (self::insertChosenInlineResultRequest($chosen_inline_result)) { |
579
|
|
|
$chosen_inline_result_local_id = self::$pdo->lastInsertId(); |
580
|
|
|
|
581
|
|
|
return self::insertTelegramUpdate( |
582
|
|
|
$update_id, |
583
|
|
|
null, |
584
|
|
|
null, |
585
|
|
|
null, |
586
|
|
|
$chosen_inline_result_local_id |
587
|
|
|
); |
588
|
|
|
} |
589
|
|
|
} elseif ($update_type === 'callback_query') { |
590
|
|
|
$callback_query = $update->getCallbackQuery(); |
591
|
|
|
|
592
|
|
|
if (self::insertCallbackQueryRequest($callback_query)) { |
593
|
|
|
$callback_query_id = $callback_query->getId(); |
594
|
|
|
|
595
|
|
|
return self::insertTelegramUpdate( |
596
|
|
|
$update_id, |
597
|
|
|
null, |
598
|
|
|
null, |
599
|
|
|
null, |
600
|
|
|
null, |
601
|
|
|
$callback_query_id |
602
|
|
|
); |
603
|
|
|
} |
604
|
|
|
} |
605
|
|
|
|
606
|
|
|
return false; |
607
|
|
|
} |
608
|
|
|
|
609
|
|
|
/** |
610
|
|
|
* Insert inline query request into database |
611
|
|
|
* |
612
|
|
|
* @param InlineQuery $inline_query |
613
|
|
|
* |
614
|
|
|
* @return bool If the insert was successful |
615
|
|
|
* @throws TelegramException |
616
|
|
|
*/ |
617
|
|
View Code Duplication |
public static function insertInlineQueryRequest(InlineQuery $inline_query) |
|
|
|
|
618
|
|
|
{ |
619
|
|
|
if (!self::isDbConnected()) { |
620
|
|
|
return false; |
621
|
|
|
} |
622
|
|
|
|
623
|
|
|
try { |
624
|
|
|
$sth = self::$pdo->prepare(' |
625
|
|
|
INSERT IGNORE INTO `' . TB_INLINE_QUERY . '` |
626
|
|
|
(`id`, `user_id`, `location`, `query`, `offset`, `created_at`) |
627
|
|
|
VALUES |
628
|
|
|
(:id, :user_id, :location, :query, :offset, :created_at) |
629
|
|
|
'); |
630
|
|
|
|
631
|
|
|
$date = self::getTimestamp(); |
632
|
|
|
$user_id = null; |
633
|
|
|
|
634
|
|
|
$user = $inline_query->getFrom(); |
635
|
|
|
if ($user instanceof User) { |
636
|
|
|
$user_id = $user->getId(); |
637
|
|
|
self::insertUser($user, $date); |
638
|
|
|
} |
639
|
|
|
|
640
|
|
|
$sth->bindValue(':id', $inline_query->getId()); |
641
|
|
|
$sth->bindValue(':user_id', $user_id); |
642
|
|
|
$sth->bindValue(':location', $inline_query->getLocation()); |
643
|
|
|
$sth->bindValue(':query', $inline_query->getQuery()); |
644
|
|
|
$sth->bindValue(':offset', $inline_query->getOffset()); |
645
|
|
|
$sth->bindValue(':created_at', $date); |
646
|
|
|
|
647
|
|
|
return $sth->execute(); |
648
|
|
|
} catch (PDOException $e) { |
649
|
|
|
throw new TelegramException($e->getMessage()); |
650
|
|
|
} |
651
|
|
|
} |
652
|
|
|
|
653
|
|
|
/** |
654
|
|
|
* Insert chosen inline result request into database |
655
|
|
|
* |
656
|
|
|
* @param ChosenInlineResult $chosen_inline_result |
657
|
|
|
* |
658
|
|
|
* @return bool If the insert was successful |
659
|
|
|
* @throws TelegramException |
660
|
|
|
*/ |
661
|
|
View Code Duplication |
public static function insertChosenInlineResultRequest(ChosenInlineResult $chosen_inline_result) |
|
|
|
|
662
|
|
|
{ |
663
|
|
|
if (!self::isDbConnected()) { |
664
|
|
|
return false; |
665
|
|
|
} |
666
|
|
|
|
667
|
|
|
try { |
668
|
|
|
$sth = self::$pdo->prepare(' |
669
|
|
|
INSERT INTO `' . TB_CHOSEN_INLINE_RESULT . '` |
670
|
|
|
(`result_id`, `user_id`, `location`, `inline_message_id`, `query`, `created_at`) |
671
|
|
|
VALUES |
672
|
|
|
(:result_id, :user_id, :location, :inline_message_id, :query, :created_at) |
673
|
|
|
'); |
674
|
|
|
|
675
|
|
|
$date = self::getTimestamp(); |
676
|
|
|
$user_id = null; |
677
|
|
|
|
678
|
|
|
$user = $chosen_inline_result->getFrom(); |
679
|
|
|
if ($user instanceof User) { |
680
|
|
|
$user_id = $user->getId(); |
681
|
|
|
self::insertUser($user, $date); |
682
|
|
|
} |
683
|
|
|
|
684
|
|
|
$sth->bindValue(':result_id', $chosen_inline_result->getResultId()); |
685
|
|
|
$sth->bindValue(':user_id', $user_id); |
686
|
|
|
$sth->bindValue(':location', $chosen_inline_result->getLocation()); |
687
|
|
|
$sth->bindValue(':inline_message_id', $chosen_inline_result->getInlineMessageId()); |
688
|
|
|
$sth->bindValue(':query', $chosen_inline_result->getQuery()); |
689
|
|
|
$sth->bindValue(':created_at', $date); |
690
|
|
|
|
691
|
|
|
return $sth->execute(); |
692
|
|
|
} catch (PDOException $e) { |
693
|
|
|
throw new TelegramException($e->getMessage()); |
694
|
|
|
} |
695
|
|
|
} |
696
|
|
|
|
697
|
|
|
/** |
698
|
|
|
* Insert callback query request into database |
699
|
|
|
* |
700
|
|
|
* @param CallbackQuery $callback_query |
701
|
|
|
* |
702
|
|
|
* @return bool If the insert was successful |
703
|
|
|
* @throws TelegramException |
704
|
|
|
*/ |
705
|
|
|
public static function insertCallbackQueryRequest(CallbackQuery $callback_query) |
706
|
|
|
{ |
707
|
|
|
if (!self::isDbConnected()) { |
708
|
|
|
return false; |
709
|
|
|
} |
710
|
|
|
|
711
|
|
|
try { |
712
|
|
|
$sth = self::$pdo->prepare(' |
713
|
|
|
INSERT IGNORE INTO `' . TB_CALLBACK_QUERY . '` |
714
|
|
|
(`id`, `user_id`, `chat_id`, `message_id`, `inline_message_id`, `data`, `created_at`) |
715
|
|
|
VALUES |
716
|
|
|
(:id, :user_id, :chat_id, :message_id, :inline_message_id, :data, :created_at) |
717
|
|
|
'); |
718
|
|
|
|
719
|
|
|
$date = self::getTimestamp(); |
720
|
|
|
$user_id = null; |
721
|
|
|
|
722
|
|
|
$user = $callback_query->getFrom(); |
723
|
|
|
if ($user instanceof User) { |
724
|
|
|
$user_id = $user->getId(); |
725
|
|
|
self::insertUser($user, $date); |
726
|
|
|
} |
727
|
|
|
|
728
|
|
|
$message = $callback_query->getMessage(); |
729
|
|
|
$chat_id = null; |
730
|
|
|
$message_id = null; |
731
|
|
|
if ($message instanceof Message) { |
732
|
|
|
$chat_id = $message->getChat()->getId(); |
733
|
|
|
$message_id = $message->getMessageId(); |
734
|
|
|
|
735
|
|
|
$is_message = self::$pdo->query(' |
736
|
|
|
SELECT * |
737
|
|
|
FROM `' . TB_MESSAGE . '` |
738
|
|
|
WHERE `id` = ' . $message_id . ' |
739
|
|
|
AND `chat_id` = ' . $chat_id . ' |
740
|
|
|
LIMIT 1 |
741
|
|
|
')->rowCount(); |
742
|
|
|
|
743
|
|
|
if ($is_message) { |
744
|
|
|
self::insertEditedMessageRequest($message); |
745
|
|
|
} else { |
746
|
|
|
self::insertMessageRequest($message); |
747
|
|
|
} |
748
|
|
|
} |
749
|
|
|
|
750
|
|
|
$sth->bindValue(':id', $callback_query->getId()); |
751
|
|
|
$sth->bindValue(':user_id', $user_id); |
752
|
|
|
$sth->bindValue(':chat_id', $chat_id); |
753
|
|
|
$sth->bindValue(':message_id', $message_id); |
754
|
|
|
$sth->bindValue(':inline_message_id', $callback_query->getInlineMessageId()); |
755
|
|
|
$sth->bindValue(':data', $callback_query->getData()); |
756
|
|
|
$sth->bindValue(':created_at', $date); |
757
|
|
|
|
758
|
|
|
return $sth->execute(); |
759
|
|
|
} catch (PDOException $e) { |
760
|
|
|
throw new TelegramException($e->getMessage()); |
761
|
|
|
} |
762
|
|
|
} |
763
|
|
|
|
764
|
|
|
/** |
765
|
|
|
* Insert Message request in db |
766
|
|
|
* |
767
|
|
|
* @todo Complete with new fields: https://core.telegram.org/bots/api#message |
768
|
|
|
* |
769
|
|
|
* @param Message $message |
770
|
|
|
* |
771
|
|
|
* @return bool If the insert was successful |
772
|
|
|
* @throws TelegramException |
773
|
|
|
*/ |
774
|
6 |
|
public static function insertMessageRequest(Message $message) |
775
|
|
|
{ |
776
|
6 |
|
if (!self::isDbConnected()) { |
777
|
|
|
return false; |
778
|
|
|
} |
779
|
|
|
|
780
|
6 |
|
$date = self::getTimestamp($message->getDate()); |
781
|
|
|
|
782
|
|
|
// Insert chat, update chat id in case it migrated |
783
|
6 |
|
$chat = $message->getChat(); |
784
|
6 |
|
self::insertChat($chat, $date, $message->getMigrateToChatId()); |
785
|
|
|
|
786
|
|
|
// Insert user and the relation with the chat |
787
|
6 |
|
$user = $message->getFrom(); |
788
|
6 |
|
if ($user instanceof User) { |
789
|
6 |
|
self::insertUser($user, $date, $chat); |
790
|
|
|
} |
791
|
|
|
|
792
|
|
|
// Insert the forwarded message user in users table |
793
|
6 |
|
$forward_date = null; |
794
|
6 |
|
$forward_from = $message->getForwardFrom(); |
795
|
6 |
|
if ($forward_from instanceof User) { |
796
|
|
|
self::insertUser($forward_from, $forward_date); |
797
|
|
|
$forward_from = $forward_from->getId(); |
798
|
|
|
$forward_date = self::getTimestamp($message->getForwardDate()); |
799
|
|
|
} |
800
|
6 |
|
$forward_from_chat = $message->getForwardFromChat(); |
801
|
6 |
|
if ($forward_from_chat instanceof Chat) { |
802
|
|
|
self::insertChat($forward_from_chat, $forward_date); |
803
|
|
|
$forward_from_chat = $forward_from_chat->getId(); |
804
|
|
|
$forward_date = self::getTimestamp($message->getForwardDate()); |
805
|
|
|
} |
806
|
|
|
|
807
|
|
|
// New and left chat member |
808
|
6 |
|
$new_chat_members_ids = null; |
809
|
6 |
|
$left_chat_member_id = null; |
810
|
|
|
|
811
|
6 |
|
$new_chat_members = $message->getNewChatMembers(); |
812
|
6 |
|
$left_chat_member = $message->getLeftChatMember(); |
813
|
6 |
|
if (!empty($new_chat_members)) { |
814
|
|
|
foreach ($new_chat_members as $new_chat_member) { |
815
|
|
|
if ($new_chat_member instanceof User) { |
816
|
|
|
// Insert the new chat user |
817
|
|
|
self::insertUser($new_chat_member, $date, $chat); |
818
|
|
|
$new_chat_members_ids[] = $new_chat_member->getId(); |
819
|
|
|
} |
820
|
|
|
} |
821
|
|
|
$new_chat_members_ids = implode(',', $new_chat_members_ids); |
822
|
6 |
|
} elseif ($left_chat_member instanceof User) { |
823
|
|
|
// Insert the left chat user |
824
|
|
|
self::insertUser($left_chat_member, $date, $chat); |
825
|
|
|
$left_chat_member_id = $left_chat_member->getId(); |
826
|
|
|
} |
827
|
|
|
|
828
|
|
|
try { |
829
|
6 |
|
$sth = self::$pdo->prepare(' |
830
|
6 |
|
INSERT IGNORE INTO `' . TB_MESSAGE . '` |
831
|
|
|
( |
832
|
|
|
`id`, `user_id`, `chat_id`, `date`, `forward_from`, `forward_from_chat`, `forward_from_message_id`, |
833
|
|
|
`forward_date`, `reply_to_chat`, `reply_to_message`, `text`, `entities`, `audio`, `document`, |
834
|
|
|
`photo`, `sticker`, `video`, `voice`, `video_note`, `caption`, `contact`, |
835
|
|
|
`location`, `venue`, `new_chat_members`, `left_chat_member`, |
836
|
|
|
`new_chat_title`,`new_chat_photo`, `delete_chat_photo`, `group_chat_created`, |
837
|
|
|
`supergroup_chat_created`, `channel_chat_created`, |
838
|
|
|
`migrate_from_chat_id`, `migrate_to_chat_id`, `pinned_message` |
839
|
|
|
) VALUES ( |
840
|
|
|
:message_id, :user_id, :chat_id, :date, :forward_from, :forward_from_chat, :forward_from_message_id, |
841
|
|
|
:forward_date, :reply_to_chat, :reply_to_message, :text, :entities, :audio, :document, |
842
|
|
|
:photo, :sticker, :video, :voice, :video_note, :caption, :contact, |
843
|
|
|
:location, :venue, :new_chat_members, :left_chat_member, |
844
|
|
|
:new_chat_title, :new_chat_photo, :delete_chat_photo, :group_chat_created, |
845
|
|
|
:supergroup_chat_created, :channel_chat_created, |
846
|
|
|
:migrate_from_chat_id, :migrate_to_chat_id, :pinned_message |
847
|
|
|
) |
848
|
|
|
'); |
849
|
|
|
|
850
|
6 |
|
$user_id = null; |
851
|
6 |
|
if ($user instanceof User) { |
852
|
6 |
|
$user_id = $user->getId(); |
853
|
|
|
} |
854
|
6 |
|
$chat_id = $chat->getId(); |
855
|
|
|
|
856
|
6 |
|
$reply_to_message = $message->getReplyToMessage(); |
857
|
6 |
|
$reply_to_message_id = null; |
858
|
6 |
|
if ($reply_to_message instanceof ReplyToMessage) { |
859
|
|
|
$reply_to_message_id = $reply_to_message->getMessageId(); |
860
|
|
|
// please notice that, as explained in the documentation, reply_to_message don't contain other |
861
|
|
|
// reply_to_message field so recursion deep is 1 |
862
|
|
|
self::insertMessageRequest($reply_to_message); |
863
|
|
|
} |
864
|
|
|
|
865
|
6 |
|
$sth->bindValue(':message_id', $message->getMessageId()); |
866
|
6 |
|
$sth->bindValue(':chat_id', $chat_id); |
867
|
6 |
|
$sth->bindValue(':user_id', $user_id); |
868
|
6 |
|
$sth->bindValue(':date', $date); |
869
|
6 |
|
$sth->bindValue(':forward_from', $forward_from); |
870
|
6 |
|
$sth->bindValue(':forward_from_chat', $forward_from_chat); |
871
|
6 |
|
$sth->bindValue(':forward_from_message_id', $message->getForwardFromMessageId()); |
872
|
6 |
|
$sth->bindValue(':forward_date', $forward_date); |
873
|
|
|
|
874
|
6 |
|
$reply_to_chat_id = null; |
875
|
6 |
|
if ($reply_to_message_id) { |
|
|
|
|
876
|
|
|
$reply_to_chat_id = $chat_id; |
877
|
|
|
} |
878
|
6 |
|
$sth->bindValue(':reply_to_chat', $reply_to_chat_id); |
879
|
6 |
|
$sth->bindValue(':reply_to_message', $reply_to_message_id); |
880
|
|
|
|
881
|
6 |
|
$sth->bindValue(':text', $message->getText()); |
882
|
6 |
|
$sth->bindValue(':entities', $t = self::entitiesArrayToJson($message->getEntities(), null)); |
883
|
6 |
|
$sth->bindValue(':audio', $message->getAudio()); |
884
|
6 |
|
$sth->bindValue(':document', $message->getDocument()); |
885
|
6 |
|
$sth->bindValue(':photo', $t = self::entitiesArrayToJson($message->getPhoto(), null)); |
886
|
6 |
|
$sth->bindValue(':sticker', $message->getSticker()); |
887
|
6 |
|
$sth->bindValue(':video', $message->getVideo()); |
888
|
6 |
|
$sth->bindValue(':voice', $message->getVoice()); |
889
|
6 |
|
$sth->bindValue(':video_note', $message->getVideoNote()); |
890
|
6 |
|
$sth->bindValue(':caption', $message->getCaption()); |
891
|
6 |
|
$sth->bindValue(':contact', $message->getContact()); |
892
|
6 |
|
$sth->bindValue(':location', $message->getLocation()); |
893
|
6 |
|
$sth->bindValue(':venue', $message->getVenue()); |
894
|
6 |
|
$sth->bindValue(':new_chat_members', $new_chat_members_ids); |
895
|
6 |
|
$sth->bindValue(':left_chat_member', $left_chat_member_id); |
896
|
6 |
|
$sth->bindValue(':new_chat_title', $message->getNewChatTitle()); |
897
|
6 |
|
$sth->bindValue(':new_chat_photo', $t = self::entitiesArrayToJson($message->getNewChatPhoto(), null)); |
898
|
6 |
|
$sth->bindValue(':delete_chat_photo', $message->getDeleteChatPhoto()); |
899
|
6 |
|
$sth->bindValue(':group_chat_created', $message->getGroupChatCreated()); |
900
|
6 |
|
$sth->bindValue(':supergroup_chat_created', $message->getSupergroupChatCreated()); |
901
|
6 |
|
$sth->bindValue(':channel_chat_created', $message->getChannelChatCreated()); |
902
|
6 |
|
$sth->bindValue(':migrate_from_chat_id', $message->getMigrateFromChatId()); |
903
|
6 |
|
$sth->bindValue(':migrate_to_chat_id', $message->getMigrateToChatId()); |
904
|
6 |
|
$sth->bindValue(':pinned_message', $message->getPinnedMessage()); |
905
|
|
|
|
906
|
6 |
|
return $sth->execute(); |
907
|
|
|
} catch (PDOException $e) { |
908
|
|
|
throw new TelegramException($e->getMessage()); |
909
|
|
|
} |
910
|
|
|
} |
911
|
|
|
|
912
|
|
|
/** |
913
|
|
|
* Insert Edited Message request in db |
914
|
|
|
* |
915
|
|
|
* @param Message $edited_message |
916
|
|
|
* |
917
|
|
|
* @return bool If the insert was successful |
918
|
|
|
* @throws TelegramException |
919
|
|
|
*/ |
920
|
|
|
public static function insertEditedMessageRequest(Message $edited_message) |
921
|
|
|
{ |
922
|
|
|
if (!self::isDbConnected()) { |
923
|
|
|
return false; |
924
|
|
|
} |
925
|
|
|
|
926
|
|
|
try { |
927
|
|
|
$edit_date = self::getTimestamp($edited_message->getEditDate()); |
928
|
|
|
|
929
|
|
|
// Insert chat |
930
|
|
|
$chat = $edited_message->getChat(); |
931
|
|
|
self::insertChat($chat, $edit_date); |
932
|
|
|
|
933
|
|
|
// Insert user and the relation with the chat |
934
|
|
|
$user = $edited_message->getFrom(); |
935
|
|
|
if ($user instanceof User) { |
936
|
|
|
self::insertUser($user, $edit_date, $chat); |
937
|
|
|
} |
938
|
|
|
|
939
|
|
|
$sth = self::$pdo->prepare(' |
940
|
|
|
INSERT IGNORE INTO `' . TB_EDITED_MESSAGE . '` |
941
|
|
|
(`chat_id`, `message_id`, `user_id`, `edit_date`, `text`, `entities`, `caption`) |
942
|
|
|
VALUES |
943
|
|
|
(:chat_id, :message_id, :user_id, :edit_date, :text, :entities, :caption) |
944
|
|
|
'); |
945
|
|
|
|
946
|
|
|
$user_id = null; |
947
|
|
|
if ($user instanceof User) { |
948
|
|
|
$user_id = $user->getId(); |
949
|
|
|
} |
950
|
|
|
|
951
|
|
|
$sth->bindValue(':chat_id', $chat->getId()); |
952
|
|
|
$sth->bindValue(':message_id', $edited_message->getMessageId()); |
953
|
|
|
$sth->bindValue(':user_id', $user_id); |
954
|
|
|
$sth->bindValue(':edit_date', $edit_date); |
955
|
|
|
$sth->bindValue(':text', $edited_message->getText()); |
956
|
|
|
$sth->bindValue(':entities', self::entitiesArrayToJson($edited_message->getEntities(), null)); |
957
|
|
|
$sth->bindValue(':caption', $edited_message->getCaption()); |
958
|
|
|
|
959
|
|
|
return $sth->execute(); |
960
|
|
|
} catch (PDOException $e) { |
961
|
|
|
throw new TelegramException($e->getMessage()); |
962
|
|
|
} |
963
|
|
|
} |
964
|
|
|
|
965
|
|
|
/** |
966
|
|
|
* Select Groups, Supergroups, Channels and/or single user Chats (also by ID or text) |
967
|
|
|
* |
968
|
|
|
* @param $select_chats_params |
969
|
|
|
* |
970
|
|
|
* @return array|bool |
971
|
|
|
* @throws TelegramException |
972
|
|
|
*/ |
973
|
|
|
public static function selectChats($select_chats_params) |
974
|
|
|
{ |
975
|
|
|
if (!self::isDbConnected()) { |
976
|
|
|
return false; |
977
|
|
|
} |
978
|
|
|
|
979
|
|
|
// Set defaults for omitted values. |
980
|
|
|
$select = array_merge([ |
981
|
|
|
'groups' => true, |
982
|
|
|
'supergroups' => true, |
983
|
|
|
'channels' => true, |
984
|
|
|
'users' => true, |
985
|
|
|
'date_from' => null, |
986
|
|
|
'date_to' => null, |
987
|
|
|
'chat_id' => null, |
988
|
|
|
'text' => null, |
989
|
|
|
], $select_chats_params); |
990
|
|
|
|
991
|
|
|
if (!$select['groups'] && !$select['users'] && !$select['supergroups'] && !$select['channels']) { |
992
|
|
|
return false; |
993
|
|
|
} |
994
|
|
|
|
995
|
|
|
try { |
996
|
|
|
$query = ' |
997
|
|
|
SELECT * , |
998
|
|
|
' . TB_CHAT . '.`id` AS `chat_id`, |
999
|
|
|
' . TB_CHAT . '.`username` AS `chat_username`, |
1000
|
|
|
' . TB_CHAT . '.`created_at` AS `chat_created_at`, |
1001
|
|
|
' . TB_CHAT . '.`updated_at` AS `chat_updated_at` |
1002
|
|
|
'; |
1003
|
|
|
if ($select['users']) { |
1004
|
|
|
$query .= ' |
1005
|
|
|
, ' . TB_USER . '.`id` AS `user_id` |
1006
|
|
|
FROM `' . TB_CHAT . '` |
1007
|
|
|
LEFT JOIN `' . TB_USER . '` |
1008
|
|
|
ON ' . TB_CHAT . '.`id`=' . TB_USER . '.`id` |
1009
|
|
|
'; |
1010
|
|
|
} else { |
1011
|
|
|
$query .= 'FROM `' . TB_CHAT . '`'; |
1012
|
|
|
} |
1013
|
|
|
|
1014
|
|
|
// Building parts of query |
1015
|
|
|
$where = []; |
1016
|
|
|
$tokens = []; |
1017
|
|
|
|
1018
|
|
|
if (!$select['groups'] || !$select['users'] || !$select['supergroups'] || !$select['channels']) { |
1019
|
|
|
$chat_or_user = []; |
1020
|
|
|
|
1021
|
|
|
$select['groups'] && $chat_or_user[] = TB_CHAT . '.`type` = "group"'; |
1022
|
|
|
$select['supergroups'] && $chat_or_user[] = TB_CHAT . '.`type` = "supergroup"'; |
1023
|
|
|
$select['channels'] && $chat_or_user[] = TB_CHAT . '.`type` = "channel"'; |
1024
|
|
|
$select['users'] && $chat_or_user[] = TB_CHAT . '.`type` = "private"'; |
1025
|
|
|
|
1026
|
|
|
$where[] = '(' . implode(' OR ', $chat_or_user) . ')'; |
1027
|
|
|
} |
1028
|
|
|
|
1029
|
|
|
if (null !== $select['date_from']) { |
1030
|
|
|
$where[] = TB_CHAT . '.`updated_at` >= :date_from'; |
1031
|
|
|
$tokens[':date_from'] = $select['date_from']; |
1032
|
|
|
} |
1033
|
|
|
|
1034
|
|
|
if (null !== $select['date_to']) { |
1035
|
|
|
$where[] = TB_CHAT . '.`updated_at` <= :date_to'; |
1036
|
|
|
$tokens[':date_to'] = $select['date_to']; |
1037
|
|
|
} |
1038
|
|
|
|
1039
|
|
|
if (null !== $select['chat_id']) { |
1040
|
|
|
$where[] = TB_CHAT . '.`id` = :chat_id'; |
1041
|
|
|
$tokens[':chat_id'] = $select['chat_id']; |
1042
|
|
|
} |
1043
|
|
|
|
1044
|
|
|
if (null !== $select['text']) { |
1045
|
|
|
$text_like = '%' . strtolower($select['text']) . '%'; |
1046
|
|
|
if ($select['users']) { |
1047
|
|
|
$where[] = '( |
1048
|
|
|
LOWER(' . TB_CHAT . '.`title`) LIKE :text1 |
1049
|
|
|
OR LOWER(' . TB_USER . '.`first_name`) LIKE :text2 |
1050
|
|
|
OR LOWER(' . TB_USER . '.`last_name`) LIKE :text3 |
1051
|
|
|
OR LOWER(' . TB_USER . '.`username`) LIKE :text4 |
1052
|
|
|
)'; |
1053
|
|
|
$tokens[':text1'] = $text_like; |
1054
|
|
|
$tokens[':text2'] = $text_like; |
1055
|
|
|
$tokens[':text3'] = $text_like; |
1056
|
|
|
$tokens[':text4'] = $text_like; |
1057
|
|
|
} else { |
1058
|
|
|
$where[] = 'LOWER(' . TB_CHAT . '.`title`) LIKE :text'; |
1059
|
|
|
$tokens[':text'] = $text_like; |
1060
|
|
|
} |
1061
|
|
|
} |
1062
|
|
|
|
1063
|
|
|
if (!empty($where)) { |
1064
|
|
|
$query .= ' WHERE ' . implode(' AND ', $where); |
1065
|
|
|
} |
1066
|
|
|
|
1067
|
|
|
$query .= ' ORDER BY ' . TB_CHAT . '.`updated_at` ASC'; |
1068
|
|
|
|
1069
|
|
|
$sth = self::$pdo->prepare($query); |
1070
|
|
|
$sth->execute($tokens); |
1071
|
|
|
|
1072
|
|
|
return $sth->fetchAll(PDO::FETCH_ASSOC); |
1073
|
|
|
} catch (PDOException $e) { |
1074
|
|
|
throw new TelegramException($e->getMessage()); |
1075
|
|
|
} |
1076
|
|
|
} |
1077
|
|
|
|
1078
|
|
|
/** |
1079
|
|
|
* Get Telegram API request count for current chat / message |
1080
|
|
|
* |
1081
|
|
|
* @param integer $chat_id |
1082
|
|
|
* @param string $inline_message_id |
1083
|
|
|
* |
1084
|
|
|
* @return array|bool Array containing TOTAL and CURRENT fields or false on invalid arguments |
1085
|
|
|
* @throws TelegramException |
1086
|
|
|
*/ |
1087
|
|
|
public static function getTelegramRequestCount($chat_id = null, $inline_message_id = null) |
1088
|
|
|
{ |
1089
|
|
|
if (!self::isDbConnected()) { |
1090
|
|
|
return false; |
1091
|
|
|
} |
1092
|
|
|
|
1093
|
|
|
try { |
1094
|
|
|
$sth = self::$pdo->prepare('SELECT |
1095
|
|
|
(SELECT COUNT(DISTINCT `chat_id`) FROM `' . TB_REQUEST_LIMITER . '` WHERE `created_at` >= :created_at_1) AS LIMIT_PER_SEC_ALL, |
1096
|
|
|
(SELECT COUNT(*) FROM `' . TB_REQUEST_LIMITER . '` WHERE `created_at` >= :created_at_2 AND ((`chat_id` = :chat_id_1 AND `inline_message_id` IS NULL) OR (`inline_message_id` = :inline_message_id AND `chat_id` IS NULL))) AS LIMIT_PER_SEC, |
1097
|
|
|
(SELECT COUNT(*) FROM `' . TB_REQUEST_LIMITER . '` WHERE `created_at` >= :created_at_minute AND `chat_id` = :chat_id_2) AS LIMIT_PER_MINUTE |
1098
|
|
|
'); |
1099
|
|
|
|
1100
|
|
|
$date = self::getTimestamp(); |
1101
|
|
|
$date_minute = self::getTimestamp(strtotime('-1 minute')); |
1102
|
|
|
|
1103
|
|
|
$sth->bindValue(':chat_id_1', $chat_id); |
1104
|
|
|
$sth->bindValue(':chat_id_2', $chat_id); |
1105
|
|
|
$sth->bindValue(':inline_message_id', $inline_message_id); |
1106
|
|
|
$sth->bindValue(':created_at_1', $date); |
1107
|
|
|
$sth->bindValue(':created_at_2', $date); |
1108
|
|
|
$sth->bindValue(':created_at_minute', $date_minute); |
1109
|
|
|
|
1110
|
|
|
$sth->execute(); |
1111
|
|
|
|
1112
|
|
|
return $sth->fetch(); |
1113
|
|
|
} catch (Exception $e) { |
1114
|
|
|
throw new TelegramException($e->getMessage()); |
1115
|
|
|
} |
1116
|
|
|
} |
1117
|
|
|
|
1118
|
|
|
/** |
1119
|
|
|
* Insert Telegram API request in db |
1120
|
|
|
* |
1121
|
|
|
* @param string $method |
1122
|
|
|
* @param array $data |
1123
|
|
|
* |
1124
|
|
|
* @return bool If the insert was successful |
1125
|
|
|
* @throws TelegramException |
1126
|
|
|
*/ |
1127
|
|
|
public static function insertTelegramRequest($method, $data) |
1128
|
|
|
{ |
1129
|
|
|
if (!self::isDbConnected()) { |
1130
|
|
|
return false; |
1131
|
|
|
} |
1132
|
|
|
|
1133
|
|
|
try { |
1134
|
|
|
$sth = self::$pdo->prepare('INSERT INTO `' . TB_REQUEST_LIMITER . '` |
1135
|
|
|
(`method`, `chat_id`, `inline_message_id`, `created_at`) |
1136
|
|
|
VALUES |
1137
|
|
|
(:method, :chat_id, :inline_message_id, :created_at); |
1138
|
|
|
'); |
1139
|
|
|
|
1140
|
|
|
$chat_id = isset($data['chat_id']) ? $data['chat_id'] : null; |
1141
|
|
|
$inline_message_id = isset($data['inline_message_id']) ? $data['inline_message_id'] : null; |
1142
|
|
|
|
1143
|
|
|
$sth->bindValue(':chat_id', $chat_id); |
1144
|
|
|
$sth->bindValue(':inline_message_id', $inline_message_id); |
1145
|
|
|
$sth->bindValue(':method', $method); |
1146
|
|
|
$sth->bindValue(':created_at', self::getTimestamp()); |
1147
|
|
|
|
1148
|
|
|
return $sth->execute(); |
1149
|
|
|
} catch (Exception $e) { |
1150
|
|
|
throw new TelegramException($e->getMessage()); |
1151
|
|
|
} |
1152
|
|
|
} |
1153
|
|
|
|
1154
|
|
|
/** |
1155
|
|
|
* Bulk update the entries of any table |
1156
|
|
|
* |
1157
|
|
|
* @param string $table |
1158
|
|
|
* @param array $fields_values |
1159
|
|
|
* @param array $where_fields_values |
1160
|
|
|
* |
1161
|
|
|
* @return bool |
1162
|
|
|
* @throws TelegramException |
1163
|
|
|
*/ |
1164
|
3 |
|
public static function update($table, array $fields_values, array $where_fields_values) |
1165
|
|
|
{ |
1166
|
3 |
|
if (empty($fields_values) || !self::isDbConnected()) { |
1167
|
|
|
return false; |
1168
|
|
|
} |
1169
|
|
|
|
1170
|
|
|
try { |
1171
|
|
|
// Building parts of query |
1172
|
3 |
|
$tokens = $fields = $where = []; |
1173
|
|
|
|
1174
|
|
|
// Fields with values to update |
1175
|
3 |
View Code Duplication |
foreach ($fields_values as $field => $value) { |
|
|
|
|
1176
|
3 |
|
$token = ':' . count($tokens); |
1177
|
3 |
|
$fields[] = "`{$field}` = {$token}"; |
1178
|
3 |
|
$tokens[$token] = $value; |
1179
|
|
|
} |
1180
|
|
|
|
1181
|
|
|
// Where conditions |
1182
|
3 |
View Code Duplication |
foreach ($where_fields_values as $field => $value) { |
|
|
|
|
1183
|
3 |
|
$token = ':' . count($tokens); |
1184
|
3 |
|
$where[] = "`{$field}` = {$token}"; |
1185
|
3 |
|
$tokens[$token] = $value; |
1186
|
|
|
} |
1187
|
|
|
|
1188
|
3 |
|
$sql = 'UPDATE `' . $table . '` SET ' . implode(', ', $fields); |
1189
|
3 |
|
$sql .= count($where) > 0 ? ' WHERE ' . implode(' AND ', $where) : ''; |
1190
|
|
|
|
1191
|
3 |
|
return self::$pdo->prepare($sql)->execute($tokens); |
1192
|
|
|
} catch (Exception $e) { |
1193
|
|
|
throw new TelegramException($e->getMessage()); |
1194
|
|
|
} |
1195
|
|
|
} |
1196
|
|
|
} |
1197
|
|
|
|
In PHP, under loose comparison (like
==
, or!=
, orswitch
conditions), values of different types might be equal.For
string
values, the empty string''
is a special case, in particular the following results might be unexpected: