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