Revize 2a99773b

Feature #8346 Implementce úpravy dat - Server

application/controller/UpdateController.php

@@ +1 @@
+<?php
+require "../model/DB.php";
+require "../model/Tag.php";
+require "../model/Word.php";
+require "../model/Lemma.php";
+$DB = new DB();
+
+$id = 4;
+
+$res = ["tag_id"=>1, "tag"=>"N-----------", "lemma_id"=>1, "lemma"=>"xxx", "pos"=>0, "id"=>4, "context"=>"", "date"=>"2015-06-08 00:00:00", "description"=>"",
+        "description2"=>"koho/co", "description3"=>"", "ending"=>"", "finished"=>true, "namedentity"=>0, "position1"=>"53", "position2"=>"38", "positiondetail"=>"",
+        "word"=>"abatyšeZměna2"];
+
+$tag = new Tag($res["tag_id"], $res["tag"]);
+$lemma = new Lemma($res["lemma_id"], $res["lemma"], $res["pos"]);
+$word = new Word($res["id"],  $res["context"], $res["date"], $res["description"], $res["description2"], $res["description3"], $res["ending"],
+    $res["finished"], $res["namedentity"], $res["position1"], $res["position2"], $res["positiondetail"], $res["word"], $lemma, $tag);
+
+$DB->update();

application/model/DB.php

@@ -27 +27 @@
         $result = false;
         try {
             $this->stmt = $this->pdo->prepare($sql);
-            $this->stmt->execute($cond);
+            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) {
@@ -35 +64 @@
             return false;
         }
     }
-}
+
+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', 'dalimil1');
+define('DB_NAME', 'dalim2');
 define('DB_CHARSET', 'utf8');
 define('DB_USER', 'postgres');
 define('DB_PASSWORD', 'a');

application/view/Index.php

@@ -1 +1 @@
 <!-- TESTOVACI INDEX-->
 <?
 include "../controller/TableController.php"
+include "../controller/UpdateController.php"
 ?>
 <script>
     function doSearch () {
         // (A1) GET SEARCH TERM
         var data = new FormData();
-
-        // (A2) AJAX - USE HTTP:// NOT FILE://
+        //data.append("manuscript", "0,3"); //příklad pro filtraci manuscriptů, asi se ještě uprav
+        data.append("page", "0");
+        data.append("items_per_page", "50");
+        data.append("lemma", "nováLemma");
+        data.append("word", "novéWord2");
+        data.append("context", "novýContext");
+        data.append("description", "novádescription");
+        data.append("description2", "novádescription2");
+        data.append("description3", "novádescription3");
+        data.append("ending", "novéEnding");
+        data.append("finished", false);
+        data.append("namedentity",1);
+        data.append("position1", "1");
+        data.append("position2", "2");
+        data.append("positiondetail", "posdet");
+        data.append("prefix", "prefix");
+        data.append("suffix", "suffix");
+        data.append("id", 4);
+        data.append("lemma_id", 1);
+        data.append("tag_id", 1);
+        data.append("pos", 1);
+        data.append("manuscript", [1,2,3,4]);
+        data.append("wordform_id", 4);
+        data.append("tag", "A---------")
+        //data.append("finished", "false");
+        // data.append("tag_pos", "V");
+        // data.append("tag_verb_aspect", "P");
+        // data.append("tag_number", "S");
+        // data.append("word", "okáza");
         var xhr = new XMLHttpRequest();
-        xhr.open("POST", "../controller/TableController.php");
+        xhr.open("POST", "../controller/UpdateController.php");
         xhr.onload = function(){
             let results = document.getElementById("results"),
-                search = this.response;
+                search = JSON.parse(this.response);
             results.innerHTML = "";
             console.log(search);
-            let parsedJSON = JSON.parse(search);
-            console.log(parsedJSON);
-            if (parsedJSON != null) { for (let s of parsedJSON) {
-                results.innerHTML += "<div>" + s.id + "</div>";
-            }}
         };
         xhr.send(data);
         return false;