aswi2021mr-proper-gitlab/application/model/DB.php @ 082263b5

<?php
class DB {
    // (A) CONNECT TO DATABASE
    public $error = "";
    private $pdo = null;
    private $stmt = null;
    function __construct () {
        try {
            $this->pdo = new PDO(
                "pgsql:host=".DB_HOST.";dbname=".DB_NAME,
                DB_USER, DB_PASSWORD, [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
                ]
            );
        } catch (Exception $ex) { die($ex->getMessage()); }
    }

    // (B) CLOSE CONNECTION
    function __destruct(){
        if ($this->stmt!==null) { $this->stmt = null; }
        if ($this->pdo!==null) { $this->pdo = null; }
    }

    // (C) RUN A SELECT QUERY
    function select($sql, $params){
        $result = false;
        try {
            $this->stmt = $this->pdo->prepare($sql);
            if (array_key_exists("lemma", $params)) {
                $this->stmt->bindParam(':lemma',$params['lemma'], PDO::PARAM_STR);
            }
            if (array_key_exists("word", $params)) {
                $this->stmt->bindParam(':word',$params['word'], PDO::PARAM_STR);
            }
            if (array_key_exists("position1", $params)) {
                $this->stmt->bindParam(':position1',$params['position1'], PDO::PARAM_INT);
            }
            if (array_key_exists("position2", $params)) {
                $this->stmt->bindParam(':position2',$params['position2'], PDO::PARAM_INT);
            }
            if (array_key_exists("positiondetail", $params)) {
                $this->stmt->bindParam(':positiondetail',$params['positiondetail'], PDO::PARAM_INT);
            }
            if (array_key_exists("tag", $params)) {
                $this->stmt->bindParam(':tag',$params['tag'], PDO::PARAM_STR);
            }
            if (array_key_exists("tag", $params)) {
                $this->stmt->bindParam(':tag',$params['tag'], PDO::PARAM_STR);
            }
            if (array_key_exists("finished", $params)) {
                $this->stmt->bindParam(':finished',$params['finished']);
            }
            if (array_key_exists("manuscript", $params)) {
                for ($x = 0; $x < count($params["manuscript"]); $x += 1) {
                    $this->stmt->bindParam(':manuscript'.$x,$params["manuscript"][$x], PDO::PARAM_INT);
                }
            }
            $this->stmt->execute();
            $result = $this->stmt->fetchAll();
            return $result;
        } catch (Exception $ex) {
            $this->error = $ex->getMessage();
            return false;
        }
    }

    function getUser($username) {
        $this->stmt = $this->pdo->prepare('SELECT id, username, password, role FROM users WHERE username = :username');
        $this->stmt->bindParam(':username', $username);
        $this->stmt->execute();
        return $this->stmt->fetchAll();
    }

    function createUser($userDetails) {
        $this->stmt = $this->pdo->prepare('INSERT INTO users (username, password, role) VALUES (:username, :password, :role)');
        $password = password_hash($userDetails['username'], PASSWORD_DEFAULT);
        $this->stmt->bindParam(':username', $userDetails['username']);
        $this->stmt->bindParam(':password',$password);
        $this->stmt->bindParam(':role',$userDetails['role']);
        $this->stmt->execute();
        return $this->stmt->fetchAll();
    }

    function updateUserRole($userId, $role) {
        $this->stmt = $this->pdo->prepare("UPDATE users SET role = :role WHERE id = :userId");
        $this->stmt->bindParam(':role', $role);
        $this->stmt->bindParam(':userId',$userId);
        $this->stmt->execute();
        return $this->stmt->fetchAll();
    }

    function deleteUser($userId) {
        $this->stmt = $this->pdo->prepare("DELETE from users WHERE id = :userId");
        $this->stmt->bindParam(':userId',$userId);
        $this->stmt->execute();
        return $this->stmt->fetchAll();
    }



function update(){

    // ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| Tabulka dd_wordform |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

    $query = "UPDATE dd_wordform
                SET ";


    if (array_key_exists("context", $_POST) && $_POST['context'] != "") {
        $query .= " context = :context, ";
    }
        $query .= " date = CURRENT_DATE,";

    if (array_key_exists("description", $_POST) && $_POST['description'] != "") {
        $query .= " description = :description, ";
    }
    if (array_key_exists("description2", $_POST) && $_POST['description2'] != "") {
        $query .= " description2 = :description2, ";
    }
    if (array_key_exists("description3", $_POST) && $_POST['description3'] != "") {
        $query .= " description3 = :description3, ";
    }
    if (array_key_exists("ending", $_POST) && $_POST['ending'] != "") {
        $query .= " ending = :ending, ";
    }
    if (array_key_exists("finished", $_POST) && $_POST['finished'] != "") {
        $query .= " finished = :finished, ";
    }
    if (array_key_exists("namedentity", $_POST) && $_POST['namedentity'] != "") {
        $query .= " namedentity = :namedentity, ";
    }
    if (array_key_exists("position1", $_POST) && $_POST['position1'] != "") {
        $query .= " position1 = :position1, ";
    }
    if (array_key_exists("position2", $_POST) && $_POST['position2'] != "") {
        $query .= " position2 = :position2, ";
    }
    if (array_key_exists("positiondetail", $_POST) && $_POST['positiondetail'] != "") {
        $query .= " positiondetail = :positiondetail ,";
    }
    if (array_key_exists("prefix", $_POST) && $_POST['prefix'] != "") {
        $query .= " prefix = :prefix, ";
    }
    if (array_key_exists("suffix", $_POST) && $_POST['suffix'] != "") {
        $query .= " suffix = :suffix, ";
    }
    if (array_key_exists("word", $_POST) && $_POST['word'] != "") {
        $query .= " word = :word, ";
    }
    if (array_key_exists("lemma_id", $_POST) && $_POST['lemma_id'] != "") {
        $query .= " lemma_id = :lemma_id, ";
    }
    if (array_key_exists("tag_id", $_POST) && $_POST['tag_id'] != "") {
        $query .= " tag_id = :tag_id ";
    }

    $query .= " WHERE ";
    if (array_key_exists("id", $_POST) && $_POST['id'] != "") {
        $query .= " id = :id ;";
    }

    $this->stmt = $this->pdo->prepare($query);

    if (array_key_exists("context", $_POST) && $_POST['context'] != "") {
        $this->stmt->bindParam(':context', $_POST['context'], PDO::PARAM_STR);
    }
    if (array_key_exists("description", $_POST) && $_POST['description'] != "") {
        $this->stmt->bindParam(':description', $_POST['description'], PDO::PARAM_STR);
    }
    if (array_key_exists("description2", $_POST) && $_POST['description2'] != "") {
        $this->stmt->bindParam(':description2', $_POST['description2'], PDO::PARAM_STR);
    }
    if (array_key_exists("description3", $_POST) && $_POST['description3'] != "") {
        $this->stmt->bindParam(':description3', $_POST['description3'], PDO::PARAM_STR);
    }
    if (array_key_exists("ending", $_POST) && $_POST['ending'] != "") {
        $this->stmt->bindParam(':ending', $_POST['ending'], PDO::PARAM_STR);
    }
    if (array_key_exists("finished", $_POST) && $_POST['finished'] != "") {
        $this->stmt->bindParam(':finished', $_POST['finished']);
    }
    if (array_key_exists("namedentity", $_POST) && $_POST['namedentity'] != "") {
        $this->stmt->bindParam(':namedentity', $_POST['namedentity'], PDO::PARAM_INT);
    }
    if (array_key_exists("position1", $_POST) && $_POST['position1'] != "") {
        $this->stmt->bindParam(':position1', $_POST['position1'], PDO::PARAM_STR);
    }
    if (array_key_exists("position2", $_POST) && $_POST['position2'] != "") {
        $this->stmt->bindParam(':position2', $_POST['position2'], PDO::PARAM_STR);
    }
    if (array_key_exists("positiondetail", $_POST) && $_POST['positiondetail'] != "") {
        $this->stmt->bindParam(':positiondetail', $_POST['positiondetail'], PDO::PARAM_STR);
    }
    if (array_key_exists("prefix", $_POST) && $_POST['prefix'] != "") {
        $this->stmt->bindParam(':prefix', $_POST['prefix'], PDO::PARAM_STR);
    }
    if (array_key_exists("suffix", $_POST) && $_POST['suffix'] != "") {
        $this->stmt->bindParam(':suffix', $_POST['suffix'], PDO::PARAM_STR);
    }
    if (array_key_exists("word", $_POST) && $_POST['word'] != "") {
        $this->stmt->bindParam(':word', $_POST['word'], PDO::PARAM_STR);
    }
    if (array_key_exists("lemma_id", $_POST) && $_POST['lemma_id'] != "") {
        $this->stmt->bindParam(':lemma_id', $_POST['lemma_id'], PDO::PARAM_INT);
    }
    if (array_key_exists("tag_id", $_POST) && $_POST['tag_id'] != "") {
        $this->stmt->bindParam(':tag_id', $_POST['tag_id'], PDO::PARAM_INT);
    }
    if (array_key_exists("id", $_POST) && $_POST['id'] != "") {
        $this->stmt->bindParam(':id', $_POST['id'], PDO::PARAM_INT);
    }

    $this->stmt->execute();

    // ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| Tabulka dd_lemma |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

    $query = "UPDATE dd_lemma
                 SET ";

    if (array_key_exists("lemma", $_POST) && $_POST['lemma'] != "") {
        $query .= " lemma = :lemma , ";
    }
    if (array_key_exists("pos", $_POST) && $_POST['pos'] != "") {
        $query .= " pos = :pos  ";
    }

    $query .= "WHERE ";
    if (array_key_exists("lemma_id", $_POST) && $_POST['lemma_id'] != "") {
        $query .= " id = :lemma_id ;";
    }


    $this->stmt = $this->pdo->prepare($query);
    if (array_key_exists("lemma", $_POST) && $_POST['lemma'] != "") {
        $this->stmt->bindParam(':lemma', $_POST['lemma'], PDO::PARAM_STR);
    }
    if (array_key_exists("pos", $_POST) && $_POST['pos'] != "") {
        $this->stmt->bindParam(':pos', $_POST['pos'], PDO::PARAM_INT);
    }
    if (array_key_exists("lemma_id", $_POST) && $_POST['lemma_id'] != "") {
        $this->stmt->bindParam(':lemma_id', $_POST['lemma_id'], PDO::PARAM_INT);
    }
    $this->stmt->execute();

    // ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| Tabulka dd_manuscript |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

    if (array_key_exists("wordform_id", $_POST) && $_POST['wordform_id'] != "") {
        $query = "SELECT * FROM dd_manuscript WHERE ";
        $query .= " wordform_id = :wordform_id ;";
    }

    $this->stmt = $this->pdo->prepare($query);
    if (array_key_exists("wordform_id", $_POST) && $_POST['wordform_id'] != "") {
        $this->stmt->bindParam(':wordform_id', $_POST['wordform_id'], PDO::PARAM_INT);
    }
    $this->stmt->execute();
    $result = $this->stmt->fetchAll();

    $to_insert = [];
    $to_delete = [];
    $contained = [];
    $found = false;

    foreach ($result as $res) {
        $integerIDs = array_map('intval', explode(',', $_POST['manuscript']));
        foreach ($integerIDs as $new_value){
            if($new_value == $res['manuscript']){
                $found = true;
                array_push($contained, $new_value);
            }
        }
        if($found == false){
            array_push($to_delete, $res);
        }
        $found = false;
    }
    $to_insert = array_diff($integerIDs, $contained);

    foreach ($to_delete as $id_to_delete){
        $query = "DELETE FROM dd_manuscript WHERE ";
        $query .= "manuscript = " . $id_to_delete['manuscript'] . " AND ";
        $query .= " wordform_id = :wordform_id ;";


        $this->stmt = $this->pdo->prepare($query);
        if (array_key_exists("wordform_id", $_POST) && $_POST['wordform_id'] != "") {
            $this->stmt->bindParam(':wordform_id', $_POST['wordform_id'], PDO::PARAM_INT);
        }
        //
        $this->stmt->execute();
        var_dump($query);
    }

    foreach ($to_insert as $id_to_insert){
        $query = "INSERT INTO dd_manuscript VALUES ( ";
        $query .= " :wordform_id , ";
        $query .= " " . $id_to_insert . " ); ";

        $this->stmt = $this->pdo->prepare($query);
        if (array_key_exists("wordform_id", $_POST) && $_POST['wordform_id'] != "") {
            $this->stmt->bindParam(':wordform_id', $_POST['wordform_id'], PDO::PARAM_INT);
        }
        $this->stmt->execute();
    }

    // ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| Tabulka dd_tag |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

    $query = "UPDATE dd_tag
                 SET ";

    if (array_key_exists("tag", $_POST) && $_POST['tag'] != "") {
        $query .= " tag = :tag  ";
    }

    $query .= "WHERE ";
    if (array_key_exists("tag_id", $_POST) && $_POST['tag_id'] != "") {
        $query .= " id = :tag_id ;";
    }


    $this->stmt = $this->pdo->prepare($query);
    if (array_key_exists("tag", $_POST) && $_POST['tag'] != "") {
        $this->stmt->bindParam(':tag', $_POST['tag'], PDO::PARAM_STR);
    }
    if (array_key_exists("tag_id", $_POST) && $_POST['tag_id'] != "") {
        $this->stmt->bindParam(':tag_id', $_POST['tag_id'], PDO::PARAM_INT);
    }
    $this->stmt->execute();
}}

// (D) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'dalimil2');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'postgres');
define('DB_PASSWORD', 'a');