<?php
class LearnSystemDatabase extends DictionaryDatabaseHandler
{
public $table = 'dicusers_contribution';
public $database = CONFIG_DB_NAME_GERMAN;
public function getConnection(){
return $this->connection;
}
public function lookForRelatedWordInContributionTable($word)
{
$sql = "SELECT * FROM `" . $this->database . "`.`$this->table` where word='" . $word . "' AND relatedWord !='' AND adminOpinion ='adminConfirmed'";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
while ($wordArrayResult[] = mysqli_fetch_assoc($result));
// $word = mysqli_fetch_assoc($result);
// return $this->lookForWordInDictionary($word['relatedWord']);
return $wordArrayResult;
}
public function removeAddedTranslationForPersian($userId){
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET word = NULL where userid='$userId' AND status = 'open' order by id DESC LIMIT 1";
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
}
public function removeAddedTranslation($userId){
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET translation = NULL where userid='$userId' AND status = 'open' order by id DESC LIMIT 1";
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
}
public function lookForWordInContributionTable($word)
{
$sql = "SELECT * FROM `" . $this->database . "`.`$this->table` where word='" . $word . "' AND translation !='' AND adminOpinion ='adminConfirmed'";
// $sql = "SELECT * FROM `".$this->database."`.`$this->table` where word='" . $word . "' AND adminOpinion ='adminConfirmed' ";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
while ($wordArrayResult[] = mysqli_fetch_assoc($result));
// $testMessage = var_export ( $wordArrayResult, true );
// file_put_contents ( "databasetest311.html", $testMessage.PHP_EOL, FILE_APPEND | LOCK_EX );
return $wordArrayResult;
}
public function getLastContribution($userId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`$this->table` where userid='$userId' AND status = 'open' order by id DESC LIMIT 1";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$contribution = mysqli_fetch_assoc($result);
return $contribution;
}
public function getContributionByUserId($userId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`$this->table` where userid='$userId' AND status = 'open' order by id DESC LIMIT 1";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$contribution = mysqli_fetch_assoc($result);
return $contribution;
}
public function getContributionById($contId)
{
$sql = "SELECT * FROM `" . $this->database . "`.`$this->table` where id=$contId";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$contribution = mysqli_fetch_assoc($result);
return $contribution;
}
public function setContributionAdminStatus($contId, $adminOpinion)
{
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET adminOpinion ='$adminOpinion' WHERE id=$contId ";
$sql2 = "SELECT * FROM `" . $this->database . "`.`$this->table` WHERE id=$contId";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$result2 = $this->getConnection()->query($sql2);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$contribution = mysqli_fetch_assoc($result2); // return word
return $contribution;
}
public function setContributionStatus($userId, $status, $message_id)
{
// $sql = "UPDATE `".$this->database."`.`$this->table` SET status ='$status' WHERE userid='$userId' AND message_id='$message_id'";
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET status ='$status' WHERE userid='$userId' AND status='open' order by id DESC LIMIT 1";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
return true;
}
public function increaseContributionRate($contId)
{
// $sql = "UPDATE `".$this->database."`.`$this->table` SET status ='$status' WHERE userid='$userId' AND message_id='$message_id'";
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET rate =rate+1 WHERE id = $contId;";
$sql2 = "SELECT rate FROM `" . $this->database . "`.`$this->table` WHERE id = $contId;";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$result = $this->getConnection()->query($sql2);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$rate = mysqli_fetch_assoc($result);
return $rate['rate'];
}
public function decreaseContributionRate($contId)
{
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET rate =rate-1 WHERE id = $contId;";
$sql2 = "SELECT rate FROM `" . $this->database . "`.`$this->table` WHERE id = $contId;";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$result = $this->getConnection()->query($sql2);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$rate = mysqli_fetch_assoc($result);
return $rate['rate'];
}
public function isInDatabase($word, $meaning)
{
$sql = "SELECT word FROM `" . $this->database . "`.`$this->dictionaryTable` WHERE word = '$word' AND translation like '%$meaning%'";
$sql2 = "SELECT word FROM `" . $this->database . "`.`$this->table` WHERE word = '$word' AND translation like '%$meaning%' AND adminOpinion ='adminConfirmed'";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$result2 = $this->getConnection()->query($sql2);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$word1 = mysqli_fetch_assoc($result);
$word2 = mysqli_fetch_assoc($result2);
if (count($word1) > 0 || count($word2) > 0)
return true;
else
return false;
}
public function setTranslatedWord($translation, $userId)
{
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET translation ='$translation' WHERE userid='$userId' AND status='open'";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
return true;
}
public function setTranslatedWordForPersian($translation, $userId)
{
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET word ='$translation' WHERE userid='$userId' AND status='open'";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
return true;
}
public function setRelatedWord($relatedWord, $userId)
{
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET relatedWord ='$relatedWord' WHERE userid='$userId' AND status='open'";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
return true;
}
public function setContributionProblemType($problemType, $userId, $message_id)
{
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET problemType ='$problemType' WHERE userid='$userId' AND message_id='$message_id'";
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
return true;
}
public function setContributionArtikel($artikel, $contId)
{
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET artikel ='$artikel' WHERE id = '$contId'";
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
return true;
}
public function setContributionType($wordType, $contId)
{
$sql = "UPDATE `" . $this->database . "`.`$this->table` SET type ='$wordType' WHERE id = '$contId'";
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
return true;
}
public function insertContribution($word, $askedByUser, $userId, $messageId)
{
$status = 'open';
$sql = "INSERT INTO `" . $this->database . "`.`$this->table` (`word`,`askedByUser`,`userid`,`status`,`message_id`, `insertedTime`)
VALUES ('"
. $word . "' ,'"
. $askedByUser . "' ,'"
. $userId . "' ,'"
. $status . "' ,'"
. $messageId . "', "
. " now() "
. ");";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw (new Exception($sql.PHP_EOL.$this->getConnection()->error));
}
return $this->getConnection()->insert_id;
}
public function insertContributionPersian($word, $askedByUser, $userId, $messageId)
{
$status = 'open';
$sql = "INSERT INTO `" . $this->database . "`.`$this->table` (`translation`,`askedByUser`,`userid`,`status`,`message_id`, `insertedTime`)
VALUES ('"
. $word . "' ,'"
. $askedByUser . "' ,'"
. $userId . "' ,'"
. $status . "' ,'"
. $messageId . "', "
. " now() "
. ");";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw (new Exception($sql.PHP_EOL.$this->getConnection()->error));
}
return $this->getConnection()->insert_id;
}
public function getTranslationsTrustOfUser($userId)
{
$sql = "SELECT count(id) as plusRate FROM `" . $this->database . "`.`$this->table` where userid = $userId AND adminOpinion ='adminConfirmed'";
$sql2 = "SELECT count(id) as minusRate FROM `" . $this->database . "`.`$this->table` where userid = $userId AND adminOpinion ='adminRefused'";
$this->getConnection()->query("SET NAMES utf8");
$result = $this->getConnection()->query($sql);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$result2 = $this->getConnection()->query($sql2);
if(!empty($this->getConnection()->error)){
throw new Exception($sql.PHP_EOL.$this->getConnection()->error);
}
$row = mysqli_fetch_assoc($result);
$row2 = mysqli_fetch_assoc($result2);
return $row['plusRate'] - $row2['minusRate'];
}
}