<?php
class DictionaryDatabaseHandler extends DatabaseHandler
{
public $wordArrayResult;
public $translated;
public $wordIsSearched;
public $dictionaryTable = 'dictionary';
public $dictionaryTable2 = 'dictionary_merged';
public $ts;
function __construct(TranslateService $ts)
{
parent::__construct($ts->botUsername);
$this->ts = $ts;
$this->wordArrayResult = "";
$this->translated = false;
$this->wordIsSearched = false;
}
function init()
{
parent::init();
}
public function getTS(): TranslateService
{
return $this->ts;
}
public function getRowNumber($tableName)
{
$sql = "SELECT COUNT(*) as rowNumbers FROM `" . $this->database . "`.`$tableName`;";
try {
$result = $this->connection->query($sql);
if ($this->connection->error == "")
return $getResults = mysqli_fetch_assoc($result);
else
return $this->connection->error;
} catch (Exception $exc) {
return $this->connection->error;
}
}
public function addInRepairmentStack($userID, $word)
{
$sql = "INSERT INTO `" . $this->database . "`.`user_cover_requests` (`userID`,`word`)
VALUES ('"
. $userID . "' ,'"
. $word . "'"
. ");";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
return true;
}
public function createTableLike($tableName, &$newName)
{
$date = date("d-m-y");
$newTableExtension = "c_" . $date;
$newName = $tableName . "_" . $newTableExtension;
$sql = "CREATE TABLE `" . $this->database . "`.`$newName` LIKE `" . $this->database . "`.`$tableName`";
try {
$result = $this->connection->query($sql);
if ($this->connection->error == "")
return $getResults = mysqli_fetch_assoc($result);
else
return $this->connection->error;
} catch (Exception $exc) {
return $this->connection->error;
}
}
public function copyFromTableAtoB($tableA, $tableB)
{
$sql1 = "SELECT * INTO OUTFILE '/tmp/$tableB.txt' FROM `" . $this->database . "`.`$tableA`";
$sql2 = "LOAD DATA INFILE '/tmp/$tableB.txt' INTO TABLE `" . $this->database . "`.`$tableB";
try {
$result = $this->connection->query($sql1);
$result2 = $this->connection->query($sql2);
if ($this->connection->error == "")
return $getResults = mysqli_fetch_assoc($result2);
else
return "error " . $this->connection->error;
} catch (Exception $exc) {
return $this->connection->error;
}
}
public function getMessagesNumberInLastRequests($userID, $lastRecordsNumber = 60, $lastSeconds = 120)
{
// $sql= "SELECT Count(*) as sentNumber FROM `user_cover_requests` WHERE userID like '$userID' ORDER BY id DESC LIMIT $lastRecordsNumber";
$sql = "SELECT Count(*) as sentNumber FROM `user_cover_requests` WHERE userID like '$userID' AND timeSent > (NOW() - $lastSeconds )";
// $sql= "SELECT count(*) as sentNumber FROM (SELECT * FROM `user_cover_requests` ORDER BY id DESC LIMIT 60) as sub WHERE userID like '$userID'";
$result = $this->connection->query($sql);
$getResults = mysqli_fetch_assoc($result);
return $getResults['sentNumber'];
}
public function truncateUserActs()
{
$sql = "TRUNCATE TABLE `" . $this->database . "`.`user_activities`";
try {
$result = $this->connection->query($sql);
if ($this->connection->error == "")
return $getResults = mysqli_fetch_assoc($result);
else
return "error " . $this->connection->error;
} catch (Exception $exc) {
return $this->connection->error;
}
}
public function copyAndTruncate()
{
// $getResults = mysqli_fetch_assoc($result);
$date = date("d-m-y");
$newTableExtension = "c_" . $date;
$createResult = $this->createTableLike("user_activities", $newName);
$rowNumbers = $this->getRowNumber("user_activities");
if ($rowNumbers['rowNumbers'] > 5000) {
$copied = $this->copyFromTableAtoB("user_activities", $newName);
$this->truncateUserActs();
}
// $rowNumbers = $this->getRowNumber("user_activities_$newTableExtension");
$rowNumbers2 = $this->getRowNumber($newName);
return $rowNumbers2;
}
public function getUser($chatId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`dicusers` WHERE userid='" . $chatId . "'";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$userData = mysqli_fetch_assoc($result);
return new User($chatId, $userData['role'], $userData['keyboard'], $this);
}
public function addWordsToDb($wordArrayResult)
{
/* $catchedWord = $this->getWordFromDb($word);
if (empty($catchedNews['id'])) {
$this->insertWordsArrayToDb($wordArrayResult);
}
return $catchedNews['id']; */
}
public function lookForWordInDictionary($word)
{
$sql = "SELECT * FROM `" . $this->database . "`.`$this->dictionaryTable` where searchedWord='" . $word . "'";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
while ($wordArrayResult[] = mysqli_fetch_assoc($result));
return $wordArrayResult;
}
public function getSoundexSuggestions($word, $tg = null)
{
if ($this->isPersianLanguage($word)) {
$similarityRatio = 80;
} else {
$similarityRatio = 60;
}
// $sql = "SELECT * FROM `".$this->database."`.`$this->dictionaryTable` where searchedWord sounds like '" . $word . "'";
// $sql = "SELECT searchedWord, id, levenshtein_ratio(searchedWord, '" . $word . "') from ( SELECT searchedWord, id FROM `".$this->database."`.`$this->dictionaryTable` where searchedWord sounds like '" . $word . "' GROUP BY searchedWord ) AS soundexTable WHERE levenshtein_ratio(searchedWord, '" . $word . "') > $similarityRatio";
$sql = "SELECT id, searchedWord, levenshtein_ratio(searchedWord, '" . $word . "') from ( SELECT MAX(id) as id,searchedWord FROM `" . $this->database . "`.`$this->dictionaryTable` where searchedWord sounds like '" . $word . "' GROUP BY searchedWord ) AS soundexTable WHERE levenshtein_ratio(searchedWord, '" . $word . "') > $similarityRatio";
// $sql = "SELECT searchedWord, id FROM `".$this->database."`.`$this->dictionaryTable` where searchedWord sounds like '" . $word . "' GROUP BY searchedWord LIMIT 6";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
while ($suggest = mysqli_fetch_assoc($result)) {
$wordArrayResult[] = $suggest;
};
return $wordArrayResult;
}
public function getTrnslations2($word, $ln)
{
// $sql = "SELECT * FROM `".$this->database."`.`$this->dictionaryTable` where searchedWord='" . $word . "' AND status='".$ln."' limit 60";
// $sql = "SELECT * FROM `".$this->database."`.`$this->dictionaryTable` where searchedWord='" . $word . "' AND status='".$ln."' limit 60";
$sql = "SELECT id, artikel,word,perfect,plural,translation,soundFile FROM `" . $this->database . "`.`$this->dictionaryTable` where searchedWord='"
. $word . "' AND status='" . $ln . "' limit 60 UNION ALL
SELECT (id+255700) as id, artikel ,word, '' AS perfekt, '' AS plural, translation, '' AS soundFile FROM `" . $this->database . "`.`dicusers_contribution` where word='"
. $word . "' AND translation !='' AND adminOpinion ='adminConfirmed' or translation Like '%" . $word . "%' AND adminOpinion ='adminConfirmed' order by id";
// $sql= "SELECT id, artikel,word,perfect,plural,translation,soundFile FROM `".$this->database."`.`$this->dictionaryTable` where searchedWord='"
// . $word . "' AND status='".$ln."' limit 60" .
// $union = " UNION ALL ".
// $contSql = "SELECT id, artikel ,word, '' AS perfekt, '' AS plural, translation, '' AS soundFile FROM `".$this->database."`.`dicusers_contribution` where word='"
// . $word . "' AND translation !='' AND adminOpinion ='adminConfirmed' order by id";
// $sql = $contSql.$union.$sql;
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
while ($wordArrayResult[] = mysqli_fetch_assoc($result));
// $wordArrayResult2 = $this->lookForWordInContributionTable($word);
// $wordArrayResult3 = $this->lookForRelatedWordInContributionTable($word);
// $wordArrayResult = array_merge($wordArrayResult,$wordArrayResult2 );
// $wordArrayResult = array_merge($wordArrayResult,$wordArrayResult3 );
if (count($wordArrayResult) > 0 && !empty($wordArrayResult[0])) {
$this->wordArrayResult = $wordArrayResult;
$this->translated = true;
} else {
$this->isSearched($word);
$this->translated = false;
}
return $this->wordArrayResult;
}
public function getWordFromActivityById($wordId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`user_activities` where id=$wordId";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$activity = mysqli_fetch_assoc($result);
return $activity['searchedWord'];
}
public function getActivity($wordId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`user_activities` where id=$wordId";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$activity = mysqli_fetch_assoc($result);
return $activity;
}
public function setUserRole($role, $userId)
{
$sql = "UPDATE `" . $this->database . "`.`dicusers` SET role ='$role' WHERE userid='$userId'";
$result = $this->connection->query($sql) or die($this->connection->error);
return true;
}
public function setUserKeyboard($status, $userId)
{
$sql = "UPDATE `" . $this->database . "`.`dicusers` SET keyboard ='$status' WHERE userid='$userId'";
$result = $this->connection->query($sql) or die($this->connection->error);
return true;
}
public function setUserStep($userId, $step)
{
$sql = "UPDATE `" . $this->database . "`.`dicusers` SET step ='$step' WHERE userid='$userId'";
$result = $this->connection->query($sql) or die($this->connection->error);
return true;
}
public function getUserStep($userId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`dicusers` where userid='$userId'";
$result = $this->connection->query($sql) or die($this->connection->error);
$user = mysqli_fetch_assoc($result);
return $user['step'];
}
public function getWordById($wordId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`$this->dictionaryTable` where id=$wordId";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$news = mysqli_fetch_assoc($result);
return $news;
}
public function isSearched($word)
{
$sql = "SELECT * FROM `" . $this->database . "`.`user_activities` where searchedWord='$word'";
$result = $this->connection->query($sql) or die($this->connection->error);
$userSearchedWord = mysqli_fetch_assoc($result);
if(is_array($userSearchedWord))
$this->wordIsSearched = count($userSearchedWord) > 0;
else
$this->wordIsSearched = false;
}
public function isVerb($word)
{
$word = strtolower($word);
$sql = "SELECT * FROM `" . $this->database . "`.`dic_conjunction` where verb='$word'";
$result = $this->connection->query($sql) or die($this->connection->error);
$conjVerb = mysqli_fetch_assoc($result);
if (!empty($conjVerb['id']))
return $conjVerb['id'];
else
return 0;
}
public function getConnection(){
return $this->connection;
}
public function getConjuctionByVerbId($conjVerbId)
{
// $sql = "SELECT * FROM `".$this->database."`.`$this->dictionaryTable` where searchedWord sounds like '" . $word . "'";
// $SQL = "SELECT * FROM `".$this->database."`.`DIC_CONJUNCTION` WHERE 'INFINITIVE' LIKE (SELECT INFINITIVE FROM DIC_CONJUNCTION WHERE ID=".
// $CONJVERBID
// .")";
$sql = "SELECT * FROM `" . $this->database . "`.`dic_conjunction` where `infinitive` = (SELECT infinitive FROM `" . $this->database . "`.`dic_conjunction` where id=" . $conjVerbId . ")";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
while ($wordArrayResult[] = mysqli_fetch_assoc($result));
return $wordArrayResult;
}
public function updatePerfect($word, $perfect)
{
$sql = "UPDATE `" . $this->database . "`.`$this->dictionaryTable` SET `perfect`='$perfect' WHERE word='" . $word . "'";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
}
public function insertWordsWithTranslation($translationsArray, $ln, $searchedWord)
{
$status = $ln;
if (!is_array($translationsArray))
return false;
foreach ($translationsArray as $table) {
foreach ($table as $tr) {
if (!empty($tr[2])) {
$artikel = trim($tr[1]);
$word = trim($tr[2]);
$translation = $tr[5];
$this->insertWord($word, $translation, $artikel, $status, $searchedWord);
}
}
}
}
public function isWordAndTranslation($word, $translation, $ln, $searchedWord)
{
$sql = "SELECT * FROM `" . $this->database . "`.`$this->dictionaryTable` WHERE word='" . $word . "'"
. " AND translation='" . $translation . "' AND searchedword='" . $searchedWord . "' AND status='" . $ln . "'";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$words = mysqli_fetch_assoc($result);
if (count($words) > 0)
return true;
else
return false;
}
public function insertWord($word, $translation, $artikel, $ln, $searchedWord)
{
if ($this->isWordAndTranslation($word, $translation, $ln, $searchedWord))
return false;
$sql = "INSERT INTO `" . $this->database . "`.`$this->dictionaryTable` (`word`,`translation`,`artikel`,`status` ,`searchedword`)
VALUES ('"
. $word . "' ,'"
. $translation . "' ,'"
. $artikel . "' ,'"
. $ln . "' ,'"
. $searchedWord . "'"
. ");";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
return true;
}
public function addTranslationToWord($wordId, $translation)
{
$sql = "UPDATE `" . $this->database . "`.`$this->dictionaryTable` set translation ='" . $translation
. "' AND status='ready' where id=" . $wordId;
$result = $this->connection->query($sql);
}
public function incrementInvitationSentNumber($userId)
{
$sql = "UPDATE `" . $this->database . "`.`dicusers` set invitesent =invitesent +1 where userid=" . $userId;
$this->connection->query($sql);
// return true;
}
public function getSentInviteNumber($userId)
{
$sql = "SELECT invitesent FROM `" . $this->database . "`.`dicusers` where userid=" . $userId;
$result = $this->connection->query($sql);
$result = mysqli_fetch_assoc($result);
if(isset($result['invitesent']))
return $result['invitesent'];
else return 0;
}
public function isUser($userId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`dicusers` WHERE userid='" . $userId . "'";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$words = mysqli_fetch_assoc($result);
//$testMessage = count($words)." is user".PHP_EOL;
//file_put_contents ( "isusertestbot.html", $testMessage, FILE_APPEND | LOCK_EX );
if (isset($words) && count($words) > 0)
return true;
else
return false;
}
public function insertUser($userId, $userName, $name, $lastname, $botID)
{
if (!$this->isUser($userId)) {
$sql = "INSERT INTO `" . $this->database . "`.`dicusers` (`userid`,`username`,`name`,`lastname`)
VALUES ('"
. $userId . "' ,'"
. $userName . "' ,'"
. $name . "' ,'"
. $lastname . "'"
. ");";
$this->connection->query("SET NAMES utf8mb4");
$result = $this->connection->query($sql) or die($this->connection->error);
}
if (!$this->isUserRegisteredWithBotID($userId, $botID)) {
$sql = "INSERT INTO `" . $this->database . "`.`dicusers_bots` (`userID`,`botID`)
VALUES ('"
. $userId . "' ,'"
. $botID . "'"
. ");";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
}
return true;
}
public function insertUsersBots($userId, $botID, $active)
{
if (!$this->isUserRegisteredWithBotID($userId, $botID)) {
$sql = "INSERT INTO `" . $this->database . "`.`dicusers_bots` (`userID`,`botID`,active)
VALUES ('"
. $userId . "' ,'"
. $botID . "',"
. $active
. ");";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
} else {
$sql = "UPDATE `" . $this->database . "`.`dicusers_bots` set active=$active where
userID like '$userId' and botID like '$botID'";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
}
}
public function isUserRegisteredWithBotID($userId, $botID)
{
$sql = "SELECT * FROM `" . $this->database . "`.`dicusers_bots` WHERE userID='" . $userId . "' and botID='" . $botID . "'";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$words = mysqli_fetch_assoc($result);
//$testMessage = count($words)." is user".PHP_EOL;
//file_put_contents ( "isusertestbot.html", $testMessage, FILE_APPEND | LOCK_EX );
if (isset($words) && count($words) > 0)
return true;
else
return false;
}
public function getSomeUsers($notThisUser)
{
$sql = "SELECT * FROM `" . $this->database . "`.`dicusers` where step ='userIsTranslating' AND role ='TRANSLATOR' AND userid!='$notThisUser' ORDER BY RAND() LIMIT 7";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
while ($wordArrayResult[] = mysqli_fetch_assoc($result));
return $wordArrayResult;
}
public function insertUserActivity($userId, $searchedWord, $intentID)
{
$translated = $this->translated ? 1 : -1;
$sql = "INSERT INTO `" . $this->database . "`.`user_activities` (`userid`,`searchedWord`,`intent_id`,`translated`,`trdate`)
VALUES ('"
. $userId . "' ,'"
. $searchedWord . "' ,"
. $intentID . " ,'"
. $translated . "' ,"
. "now()"
. ");";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
return $this->connection->insert_id;
}
public function isOverUsage($userId, $limit, &$numberOfTranslated)
{
$sql = "SELECT * FROM `" . $this->database . "`.`user_activities` WHERE userid='" . $userId . "' AND date(trdate) = CURDATE()";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$count = mysqli_num_rows($result);
$numberOfTranslated = $count;
if ($count > $limit)
return true;
else
return false;
}
public function getNumberOfCurrencyConverts($userId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`user_activities` WHERE userid='" . $userId . "' AND intent_id=1 AND date(trdate) = CURDATE()";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$count = mysqli_num_rows($result);
return $count;
}
public function getNumberOfExtracts($userId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`user_activities` WHERE userid='" . $userId . "' AND intent_id=4 AND date(trdate) = CURDATE()";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$count = mysqli_num_rows($result);
return $count;
}
public function getNumberOfActivityWithIntentId($userId, $intentId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`user_activities` WHERE userid='" . $userId . "' AND intent_id=$intentId AND date(trdate) = CURDATE()";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$count = mysqli_num_rows($result);
return $count;
}
public function hasUserSuggested($userId, $suggestedNumber)
{
$sql = "SELECT * FROM `" . $this->database . "`.`dic_suggested_users` WHERE userid='" . $userId . "'";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
$count = mysqli_num_rows($result);
// $testMessage = var_export ( $userId.":".$count, true );
// file_put_contents ( "testsuggestedbot.html", $testMessage.PHP_EOL, FILE_APPEND | LOCK_EX );
if ($count >= $suggestedNumber)
return true;
else
return false;
}
public function isUserSuggested($newSuggestedUserId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`dic_suggested_users` WHERE suggestedid='" . $newSuggestedUserId . "'";
$result = $this->connection->query($sql) or die($this->connection->error);
$user = mysqli_fetch_assoc($result);
if (isset($user))
return true;
else
return false;
}
public function insertSuggested($userId, $newSuggestedUserId)
{
if ($this->isUserSuggested($newSuggestedUserId))
return false;
$sql = "INSERT INTO `" . $this->database . "`.`dic_suggested_users` (`userid`,`suggestedid`,`Datum`)
VALUES ('"
. $userId . "' ,'"
. $newSuggestedUserId . "' ,"
. "now()"
. ");";
$this->connection->query("SET NAMES utf8");
$result = $this->connection->query($sql) or die($this->connection->error);
return true;
}
public function isPersianLanguage($word)
{
return preg_match('/[^A-Za-z0-9]/', $word)
&& !preg_match('/ü/', $word)
&& !preg_match('/Ü/', $word)
&& !preg_match('/ä/', $word)
&& !preg_match('/Ä/', $word)
&& !preg_match('/ö/', $word)
&& !preg_match('/Ö/', $word)
&& !preg_match('/ß/', $word);
}
public function query($sql, $debug=false){
if($debug == true){
$this->getTS()->sendMessageLog($sql);
}
return parent::query($sql);
}
}