<?php /** * This file is part of the TelegramBot package. * * (c) Avtandil Kikabidze aka LONGMAN <akalongman@gmail.com> * * For the full copyright and license information, please view the LICENSE * file that was distributed with this source code. * Written by Marco Boretto <marco.bore@gmail.com> */ namespace Longman\TelegramBot; use Longman\TelegramBot\Entities\Chat; use Longman\TelegramBot\Entities\InlineQuery; use Longman\TelegramBot\Entities\Message; use Longman\TelegramBot\Entities\Update; use Longman\TelegramBot\Entities\User; use Longman\TelegramBot\Exception\TelegramException; /** * @package Telegram * @author Avtandil Kikabidze <akalongman@gmail.com> * @copyright Avtandil Kikabidze <akalongman@gmail.com> * @license http://opensource.org/licenses/mit-license.php The MIT License (MIT) * @link http://www.github.com/akalongman/php-telegram-bot */ /** * Class DB. */ class DB { /** * MySQL credentials * * @var array */ static protected $mysql_credentials = []; /** * PDO object * * @var PDO */ static protected $pdo; /** * Table prefix * * @var string */ static protected $table_prefix; /** * Telegram class object * * @var Telegram */ static protected $telegram; /** * Initialize * * @param array $credentials Database connection details * @param Telegram $telegram Telegram object to connect with this object * @param string $table_prefix Table prefix * * @return PDO PDO database object */ public static function initialize(array $credentials, Telegram $telegram, $table_prefix = null) { if (empty($credentials)) { throw new TelegramException('MySQL credentials not provided!'); } self::$telegram = $telegram; self::$mysql_credentials = $credentials; self::$table_prefix = $table_prefix; $dsn = 'mysql:host=' . $credentials['host'] . ';dbname=' . $credentials['database']; $options = [\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8']; try { $pdo = new \PDO($dsn, $credentials['user'], $credentials['password'], $options); $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_WARNING); //Define table if (!defined('TB_TELEGRAM_UPDATE')) { define('TB_TELEGRAM_UPDATE', self::$table_prefix.'telegram_update'); } if (!defined('TB_MESSAGE')) { define('TB_MESSAGE', self::$table_prefix.'message'); } if (!defined('TB_INLINE_QUERY')) { define('TB_INLINE_QUERY', self::$table_prefix.'inline_query'); } if (!defined('TB_CHOSEN_INLINE_QUERY')) { define('TB_CHOSEN_INLINE_QUERY', self::$table_prefix.'chosen_inline_query'); } if (!defined('TB_USER')) { define('TB_USER', self::$table_prefix.'user'); } if (!defined('TB_CHAT')) { define('TB_CHAT', self::$table_prefix.'chat'); } if (!defined('TB_USER_CHAT')) { define('TB_USER_CHAT', self::$table_prefix.'user_chat'); } } catch (\PDOException $e) { throw new TelegramException($e->getMessage()); } self::$pdo = $pdo; return self::$pdo; } /** * Check if database connection has been created * * @return bool */ public static function isDbConnected() { if (empty(self::$pdo)) { return false; } else { return true; } } /** * Fetch update(s) from DB * * @param int $limit Limit the number of updates to fetch * * @return array|bool Fetched data or false if not connected */ public static function selectTelegramUpdate($limit = null) { if (!self::isDbConnected()) { return false; } try { $query = 'SELECT `id` FROM `' . TB_TELEGRAM_UPDATE . '` '; $query .= 'ORDER BY `id` DESC'; if (!is_null($limit)) { $query .=' LIMIT :limit '; } $sth_select_telegram_update = self::$pdo->prepare($query); $sth_select_telegram_update->bindParam(':limit', $limit, \PDO::PARAM_INT); $sth_select_telegram_update->execute(); $results = $sth_select_telegram_update->fetchAll(\PDO::FETCH_ASSOC); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } return $results; } /** * Fetch message(s) from DB * * @param int $limit Limit the number of messages to fetch * * @return array|bool Fetched data or false if not connected */ public static function selectMessages($limit = null) { if (!self::isDbConnected()) { return false; } try { //message table $query = 'SELECT * FROM `' . TB_MESSAGE . '` '; $query .= 'WHERE ' . TB_MESSAGE . '.`update_id` != 0 '; $query .= 'ORDER BY ' . TB_MESSAGE . '.`message_id` DESC'; if (!is_null($limit)) { $query .=' LIMIT :limit '; } $sth = self::$pdo->prepare($query); $sth->bindParam(':limit', $limit, \PDO::PARAM_INT); $sth->execute(); $results = $sth->fetchAll(\PDO::FETCH_ASSOC); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } return $results; } /** * Convert from unix timestamp to timestamp * * @param int $time Unix timestamp * * @return null|string Timestamp if a time has been passed, else null */ protected static function getTimestamp($time = null) { if (is_null($time)) { return null; } return date('Y-m-d H:i:s', $time); } /** * Insert entry to telegram_update table * * @todo Needs to return something if successful * * @param int $id * @param int $message_id * @param int $inline_query_id * @param int $chosen_inline_query_id * * @return bool|null */ public static function insertTelegramUpdate($id, $message_id, $inline_query_id, $chosen_inline_query_id) { if (is_null($message_id) && is_null($inline_query_id) && is_null($chosen_inline_query_id)) { throw new TelegramException('Error both query_id and message_id are null'); } if (!self::isDbConnected()) { return false; } try { //telegram_update table $sth_insert_telegram_update = self::$pdo->prepare('INSERT IGNORE INTO `' . TB_TELEGRAM_UPDATE . '` ( `id`, `message_id`, `inline_query_id`, `chosen_inline_query_id` ) VALUES ( :id, :message_id, :inline_query_id, :chosen_inline_query_id ) '); $sth_insert_telegram_update->bindParam(':id', $id, \PDO::PARAM_INT); $sth_insert_telegram_update->bindParam(':message_id', $message_id, \PDO::PARAM_INT); $sth_insert_telegram_update->bindParam(':inline_query_id', $inline_query_id, \PDO::PARAM_INT); $sth_insert_telegram_update->bindParam(':chosen_inline_query_id', $chosen_inline_query_id, \PDO::PARAM_INT); $status = $sth_insert_telegram_update->execute(); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } } /** * Insert users and save their connection to chats * * @todo Needs to return something if successful * * @param Entities\User $user * @param string $date * @param Entities\Chat $chat * * @return bool|null */ public static function insertUser(User $user, $date, Chat $chat = null) { if (!self::isDbConnected()) { return false; } $user_id = $user->getId(); $username = $user->getUsername(); $first_name = $user->getFirstName(); $last_name = $user->getLastName(); try { //user table $sth1 = self::$pdo->prepare('INSERT INTO `' . TB_USER . '` ( `id`, `username`, `first_name`, `last_name`, `created_at`, `updated_at` ) VALUES ( :id, :username, :first_name, :last_name, :date, :date ) ON DUPLICATE KEY UPDATE `username`=:username, `first_name`=:first_name, `last_name`=:last_name, `updated_at`=:date '); $sth1->bindParam(':id', $user_id, \PDO::PARAM_INT); $sth1->bindParam(':username', $username, \PDO::PARAM_STR, 255); $sth1->bindParam(':first_name', $first_name, \PDO::PARAM_STR, 255); $sth1->bindParam(':last_name', $last_name, \PDO::PARAM_STR, 255); $sth1->bindParam(':date', $date, \PDO::PARAM_STR); $status = $sth1->execute(); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } //insert also the relationship to the chat if (!is_null($chat)) { $chat_id = $chat->getId(); try { //user_chat table $sth3 = self::$pdo->prepare('INSERT IGNORE INTO `' . TB_USER_CHAT . '` ( `user_id`, `chat_id` ) VALUES ( :user_id, :chat_id )'); $sth3->bindParam(':user_id', $user_id, \PDO::PARAM_INT); $sth3->bindParam(':chat_id', $chat_id, \PDO::PARAM_INT); $status = $sth3->execute(); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } } } /** * Insert request into database * * @param Entities\Update &$update * * @return bool */ public static function insertRequest(Update &$update) { $update_id = $update->getUpdateId(); if ($update->getUpdateType() == 'message') { $message = $update->getMessage(); $message_id = $message->getMessageId(); self::insertMessageRequest($message); return self::insertTelegramUpdate($update_id, $message_id, null, null); } elseif ($update->getUpdateType() == 'inline_query') { $inline_query = $update->getInlineQuery(); $inline_query_id = $inline_query->getId(); self::insertInlineQueryRequest($inline_query); return self::insertTelegramUpdate($update_id, null, $inline_query_id, null); } elseif ($update->getUpdateType() == 'chosen_inline_result') { $chosen_inline_query = $update->getChosenInlineResult(); if (!self::isDbConnected()) { return false; } try { //Inline query Table $mysql_query = 'INSERT INTO `' . TB_CHOSEN_INLINE_QUERY . '` ( `result_id`, `user_id`, `query`, `created_at` ) VALUES ( :result_id, :user_id, :query, :created_at )'; $sth_insert_chosen_inline_query = self::$pdo->prepare($mysql_query); $date = self::getTimestamp(time()); $result_id = $chosen_inline_query->getResultId(); $from = $chosen_inline_query->getFrom(); $user_id = null; if (is_object($from)) { $user_id = $from->getId(); self::insertUser($from, $date); } $query = $chosen_inline_query->getQuery(); $sth_insert_chosen_inline_query->bindParam(':result_id', $result_id, \PDO::PARAM_STR); $sth_insert_chosen_inline_query->bindParam(':user_id', $user_id, \PDO::PARAM_INT); $sth_insert_chosen_inline_query->bindParam(':query', $query, \PDO::PARAM_STR); $sth_insert_chosen_inline_query->bindParam(':created_at', $date, \PDO::PARAM_STR); $status = $sth_insert_chosen_inline_query->execute(); $chosen_inline_query_local_id = self::$pdo->lastInsertId(); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } return self::insertTelegramUpdate($update_id, null, null, $chosen_inline_query_local_id); } } /** * Insert inline query request into database * * @todo No return value at the end. Just return true? * * @param Entities\InlineQuery &$inline_query * * @return bool */ public static function insertInlineQueryRequest(InlineQuery &$inline_query) { if (!self::isDbConnected()) { return false; } try { //Inline query Table $mysql_query = 'INSERT IGNORE INTO `' . TB_INLINE_QUERY . '` ( `id`, `user_id`, `query`, `offset`, `created_at` ) VALUES ( :inline_query_id, :user_id, :query, :param_offset, :created_at )'; $sth_insert_inline_query = self::$pdo->prepare($mysql_query); $date = self::getTimestamp(time()); $inline_query_id = (int) $inline_query->getId(); $from = $inline_query->getFrom(); $user_id = null; if (is_object($from)) { $user_id = $from->getId(); self::insertUser($from, $date); } $query = $inline_query->getQuery(); $offset = $inline_query->getOffset(); $sth_insert_inline_query->bindParam(':inline_query_id', $inline_query_id, \PDO::PARAM_INT); $sth_insert_inline_query->bindParam(':user_id', $user_id, \PDO::PARAM_INT); $sth_insert_inline_query->bindParam(':query', $query, \PDO::PARAM_STR); $sth_insert_inline_query->bindParam(':param_offset', $offset, \PDO::PARAM_STR); $sth_insert_inline_query->bindParam(':created_at', $date, \PDO::PARAM_STR); $status = $sth_insert_inline_query->execute(); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } } /** * Insert Message request in db * * @param Entities\Message &$message * * @return bool If the insert was successful */ public static function insertMessageRequest(Message &$message) { if (!self::isDbConnected()) { return false; } $from = $message->getFrom(); $chat = $message->getChat(); $chat_id = $chat->getId(); $date = self::getTimestamp($message->getDate()); $forward_from = $message->getForwardFrom(); $forward_date = self::getTimestamp($message->getForwardDate()); $photo = $message->getPhoto(); $new_chat_participant = $message->getNewChatParticipant(); $new_chat_photo = $message->getNewChatPhoto(); $left_chat_participant = $message->getLeftChatParticipant(); $migrate_from_chat_id = $message->getMigrateFromChatId(); try { //chat table $sth2 = self::$pdo->prepare('INSERT INTO `' . TB_CHAT . '` ( `id`, `type`, `title`, `created_at` ,`updated_at`, `old_id` ) VALUES ( :id, :type, :title, :date, :date, :oldid ) ON DUPLICATE KEY UPDATE `type`=:type, `title`=:title, `updated_at`=:date '); $chat_title = $chat->getTitle(); $type = $chat->getType(); $sth2->bindParam(':id', $chat_id, \PDO::PARAM_INT); $sth2->bindParam(':type', $type, \PDO::PARAM_INT); $sth2->bindParam(':title', $chat_title, \PDO::PARAM_STR, 255); $sth2->bindParam(':date', $date, \PDO::PARAM_STR); $sth2->bindParam(':oldid', $migrate_from_chat_id, \PDO::PARAM_INT); $status = $sth2->execute(); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } //Insert user and the relation with the chat self::insertUser($from, $date, $chat); //Insert the forwarded message user in users table $forward_from = null; if (is_object($forward_from)) { self::insertUser($forward_from, $forward_date); $forward_from = $forward_from->getId(); } if ($new_chat_participant) { //Insert the new chat user self::insertUser($new_chat_participant, $date, $chat); $new_chat_participant = $new_chat_participant->getId(); } elseif ($left_chat_participant) { //Insert the left chat user self::insertUser($left_chat_participant, $date, $chat); $left_chat_participant = $left_chat_participant->getId(); } try { //message Table $sth = self::$pdo->prepare('INSERT IGNORE INTO `' . TB_MESSAGE . '` ( `id`, `user_id`, `date`, `chat_id`, `forward_from`, `forward_date`, `reply_to_message`, `text`, `audio`, `document`, `photo`, `sticker`, `video`, `voice`, `caption`, `contact`, `location`, `new_chat_participant`, `left_chat_participant`, `new_chat_title`,`new_chat_photo`, `delete_chat_photo`, `group_chat_created`, `supergroup_chat_created`, `channel_chat_created`, `migrate_from_chat_id`, `migrate_to_chat_id` ) VALUES ( :message_id, :user_id, :date, :chat_id, :forward_from, :forward_date, :reply_to_message, :text, :audio, :document, :photo, :sticker, :video, :voice, :caption, :contact, :location, :new_chat_participant, :left_chat_participant, :new_chat_title, :new_chat_photo, :delete_chat_photo, :group_chat_created, :supergroup_chat_created, :channel_chat_created, :migrate_from_chat_id, :migrate_to_chat_id )'); $message_id = $message->getMessageId(); $from_id = $from->getId(); $reply_to_message = $message->getReplyToMessage(); $reply_to_message_id = null; if (is_object($reply_to_message)) { $reply_to_message_id = $reply_to_message->getMessageId(); // please notice that, as explaied in the documentation, reply_to_message don't contain other // reply_to_message field so recursion deep is 1 self::insertMessageRequest($reply_to_message); } $text = $message->getText(); $audio = $message->getAudio(); $document = $message->getDocument(); $sticker = $message->getSticker(); $video = $message->getVideo(); $voice = $message->getVoice(); $caption = $message->getCaption(); $contact = $message->getContact(); $location = $message->getLocation(); $new_chat_title = $message->getNewChatTitle(); $delete_chat_photo = $message->getDeleteChatPhoto(); $group_chat_created = $message->getGroupChatCreated(); $supergroup_chat_created = $message->getSupergroupChatCreated(); $channel_chat_created = $message->getChannelChatCreated(); $migrate_from_chat_id = $message->getMigrateFromChatId(); $sth->bindParam(':message_id', $message_id, \PDO::PARAM_INT); $sth->bindParam(':user_id', $from_id, \PDO::PARAM_INT); $sth->bindParam(':date', $date, \PDO::PARAM_STR); $sth->bindParam(':chat_id', $chat_id, \PDO::PARAM_INT); $sth->bindParam(':forward_from', $forward_from, \PDO::PARAM_INT); $sth->bindParam(':forward_date', $forward_date, \PDO::PARAM_STR); $sth->bindParam(':reply_to_message', $reply_to_message_id, \PDO::PARAM_INT); $sth->bindParam(':text', $text, \PDO::PARAM_STR); $sth->bindParam(':audio', $audio, \PDO::PARAM_STR); $sth->bindParam(':document', $document, \PDO::PARAM_STR); $var = []; if (is_array($photo)) { foreach ($photo as $elm) { $var[] = json_decode($elm, true); } $photo = json_encode($var); } else { $photo = ''; } $sth->bindParam(':photo', $photo, \PDO::PARAM_STR); $sth->bindParam(':sticker', $sticker, \PDO::PARAM_STR); $sth->bindParam(':video', $video, \PDO::PARAM_STR); $sth->bindParam(':voice', $voice, \PDO::PARAM_STR); $sth->bindParam(':caption', $caption, \PDO::PARAM_STR); $sth->bindParam(':contact', $contact, \PDO::PARAM_STR); $sth->bindParam(':location', $location, \PDO::PARAM_STR); $sth->bindParam(':new_chat_participant', $new_chat_participant, \PDO::PARAM_INT); $sth->bindParam(':left_chat_participant', $left_chat_participant, \PDO::PARAM_INT); $sth->bindParam(':new_chat_title', $new_chat_title, \PDO::PARAM_STR); //Array of Photosize $var = []; if (is_array($new_chat_photo)) { foreach ($new_chat_photo as $elm) { $var[] = json_decode($elm, true); } $new_chat_photo = json_encode($var); } else { $new_chat_photo = ''; } $sth->bindParam(':new_chat_photo', $new_chat_photo, \PDO::PARAM_STR); $sth->bindParam(':delete_chat_photo', $delete_chat_photo, \PDO::PARAM_STR); $sth->bindParam(':group_chat_created', $group_chat_created, \PDO::PARAM_STR); $sth->bindParam(':supergroup_chat_created', $migrate_from_chat_id, \PDO::PARAM_INT); $sth->bindParam(':channel_chat_created', $supergroup_chat_created, \PDO::PARAM_INT); $sth->bindParam(':migrate_from_chat_id', $channel_chat_created, \PDO::PARAM_INT); $sth->bindParam(':migrate_to_chat_id', $migrate_from_chat_id, \PDO::PARAM_INT); $status = $sth->execute(); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } return true; } /** * Select Group and single Chats * * @param bool $select_groups * @param bool $select_super_groups * @param bool $select_users * @param string $date_from * @param string $date_to * * @return array|bool Selected chats or false if invalid arguments */ public static function selectChats( $select_groups = true, $select_super_groups = true, $select_users = true, $date_from = null, $date_to = null ) { if (!self::isDbConnected()) { return false; } if (!$select_groups & !$select_users & !$select_super_groups) { return false; } try { $query = 'SELECT * , ' . TB_CHAT . '.`id` AS `chat_id`, ' . TB_CHAT . '.`updated_at` AS `chat_updated_at`, ' . TB_USER . '.`id` AS `user_id` FROM `' . TB_CHAT . '` LEFT JOIN `' . TB_USER . '` ON ' . TB_CHAT . '.`id`=' . TB_USER . '.`id`'; //Building parts of query $chat_or_user = ''; $where = []; $tokens = []; if (!$select_groups || !$select_users || !$select_super_groups) { if ($select_groups) { $where[] = TB_CHAT . '.`type` = "group"'; } if ($select_super_groups) { $where[] = TB_CHAT . '.`type` = "supergroup"'; } if ($select_users) { $where[] = TB_CHAT . '.`type` = "private"'; } } if (! is_null($date_from)) { $where[] = TB_CHAT . '.`updated_at` >= :date_from'; $tokens[':date_from'] = $date_from; } if (! is_null($date_to)) { $where[] = TB_CHAT . '.`updated_at` <= :date_to'; $tokens[':date_to'] = $date_to; } $a=0; foreach ($where as $part) { if ($a) { $query .= ' AND ' . $part; } else { $query .= ' WHERE ' . $part; ++$a; } } $query .= ' ORDER BY ' . TB_CHAT . '.`updated_at` ASC'; $sth = self::$pdo->prepare($query); $sth->execute($tokens); $result = $sth->fetchAll(\PDO::FETCH_ASSOC); } catch (PDOException $e) { throw new TelegramException($e->getMessage()); } return $result; } }