<?php
class ChatmatDatabaseHandler extends DatabaseHandler {
private $wordArrayResult;
private $tableName;
function __construct($botUsername){
parent::__construct($botUsername);
$this->wordArrayResult="";
$this->tableName = "`dictionary`";
$this->tableUsers = "`chatmat_users`";
}
public function addWordsToDb($wordArrayResult){
$catchedWord = $this->getWordFromDb($word);
if(empty($catchedNews['id'])){
$this->insertWordsArrayToDb($wordArrayResult);
}
return $catchedNews['id'];
}
public function getTrnslations($word, $ln) {
$sql = "SELECT * FROM `telegram_german_bot`.".$this->tableName." where searchedWord='" . $word . "' AND status='".$ln."'";
$this->connection->query ( "SET NAMES utf8" );
$result = $this->connection->query ( $sql ) or die ( $this->connection->error );
while ($wordArrayResult[] = mysqli_fetch_assoc($result));
if(count ($wordArrayResult) > 0 && !empty($wordArrayResult[0]) )
$this->wordArrayResult = $wordArrayResult;
return $this->wordArrayResult;
}
public function makeTranslationTextMessage() {
$translated ="";
foreach ( $this->wordArrayResult as $word ) {
$perfectForm = "";
if($word['word']){
$artikel = "";
switch (trim($word['artikel'])){
case "n":
$artikel = "Das - ";
break;
case "m":
$artikel = "Der - ";
break;
case "f":
$artikel = "Die - ";
break;
default:
if(substr($word['word'], -1)=="n")
$perfectForm = "<a href='https://telegram.me/translateGerman_bot'>(</a>".
$this->findPerfect($word)."<a href='https://telegram.me/translateGerman_bot'>)</a>";
break;
}
// if(!$this->hasUserSuggested($userId, 2))
// $perfectForm ="(perfekt)";
$translated.=" <a href='https://telegram.me/translateGerman_bot'>".$artikel."</a>" ."<b>". trim($word['word']).":</b> ".$word['translation'].$perfectForm.PHP_EOL;
}
}
return $translated. " 😊".PHP_EOL;
}
public function getWordById($wordId) {
$sql = "SELECT * FROM `telegram_german_bot`.".$this->tableName." where id=$wordId";
$result = $this->connection->query ( $sql ) or die ( $this->connection->error );
$news = mysqli_fetch_assoc($result);
return $news;
}
public function findPerfect($word) {
// if($word)
$perfect = $word['perfect'];
$word = $word['word'];
if (!$perfect) {
$url = "http://api.verbix.com/conjugator/html?language=deu&tableurl=http://tools.verbix.com/webverbix/personal/template.htm&verb=$word";
$contents = file_get_contents ( $url );
$regex = '/Participio:<\/b> <span class="normal">(.*?)<\/span>/';
preg_match ( $regex, $contents, $treffer, PREG_OFFSET_CAPTURE, 3 );
$regex = '/Participio:<\/b> <span class="irregular">(.*?)<\/span>/';
preg_match ( $regex, $contents, $trefferIre, PREG_OFFSET_CAPTURE, 3 );
if (! empty ( $treffer [1] [0] ) || ! empty ($trefferIre [1] [0])){
$perfect = $treffer [1] [0];
if(empty($perfect))
$perfect= $trefferIre [1] [0];
$perfect = str_replace("ä", "ä", $perfect);
$perfect = str_replace("ü", "ü", $perfect);
$perfect = str_replace("ö", "ö", $perfect);
}
else
$perfect = "-";
$this->updatePerfect($word,$perfect);
}
return $perfect;
}
public function updatePerfect($word,$perfect){
$sql = "UPDATE `telegram_german_bot`.".$this->tableName." 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;
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 `telegram_german_bot`.".$this->tableName." 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 `telegram_german_bot`.".$this->tableName." (`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){
$status ="translated";
$sql = "UPDATE `telegram_german_bot`.".$this->tableName." set translation ='".$translation
."' AND status='ready' where id=".$wordId;
$result = $this->connection->query ( $sql );
$this->connection->error();
}
public function getStep($userId){
$sql = "SELECT step FROM `telegram_german_bot`.".$this->tableUsers." WHERE userid='".$userId."'";
$this->connection->query ( "SET NAMES utf8" );
$result = $this->connection->query ( $sql ) or die ( $this->connection->error );
$words = mysqli_fetch_assoc($result);
$wordsArray = mysqli_fetch_array($result);
return $result;
}
public function updateStep($step, $userId){
$sql = "UPDATE `telegram_german_bot`.".$this->tableUsers." set step='".$step."' WHERE userid='".$userId."'";
$this->connection->query ( "SET NAMES utf8" );
$result = $this->connection->query ( $sql ) or die ( $this->connection->error );
// $words = mysqli_fetch_assoc($result);
}
public function isUser($userId){
$sql = "SELECT * FROM `telegram_german_bot`.".$this->tableUsers." 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(count($words)>0)
return true;
else
return false;
}
public function insertUser($userId,$userName,$name ,$lastname){
if($this->isUser($userId))
return false;
$sql = "INSERT INTO `telegram_german_bot`.".$this->tableUsers." (`userid`,`username`,`name`,`lastname`)
VALUES ('"
. $userId."' ,'"
. $userName."' ,'"
. $name."' ,'"
. $lastname."'"
.");";
$this->connection->query ( "SET NAMES utf8" );
$result = $this->connection->query ( $sql ) or die( $this->connection->error );
return true;
}
public function insertUserActivity($userId,$searchedWord,$translated){
$sql = "INSERT INTO `telegram_german_bot`.`user_activities` (`userid`,`searchedWord`,`translated`,`trdate`)
VALUES ('"
. $userId."' ,'"
. $searchedWord."' ,'"
. $translated."' ,"
."now()"
.");";
$this->connection->query ( "SET NAMES utf8" );
$result = $this->connection->query ( $sql ) or die( $this->connection->error );
return true;
}
public function isOverUsage($userId,$limit){
$sql = "SELECT * FROM `telegram_german_bot`.`user_activities` WHERE userid='".$userId."' AND trdate = CURDATE()";
$this->connection->query ( "SET NAMES utf8" );
$result = $this->connection->query ( $sql ) or die ( $this->connection->error );
$count = mysqli_num_rows($result);
if($count > $limit)
return true;
else
return false;
}
public function hasUserSuggested($userId,$suggestedNumber){
$sql = "SELECT * FROM `telegram_german_bot`.`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 `telegram_german_bot`.`dic_suggested_users` WHERE suggestedid='".$newSuggestedUserId."'";
$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 insertSuggested($userId,$newSuggestedUserId){
if($this->isUserSuggested($newSuggestedUserId))
return false;
$sql = "INSERT INTO `telegram_german_bot`.`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;
}
}