Revize 2eab68b9

Feature #8345 Implementce filterů dat - Server

vytvoření filtrů

application/controller/TableController.php

@@ -2 +2 @@
 require "../model/DB.php";
 require "../model/Tag.php";
 require "../model/Word.php";
+require "../model/Lemma.php";
 $DB = new DB();
 
-// (B) SEARCH FOR USERS
-$results = $DB->select(
-    "SELECT w.id, w.context, w.date, w.description, w.description2, w.description3, w.ending, w.finished,
-w.namedentity, w.position1, w.position2, w.positiondetail, w.word, w.tag_id, w.lemma_id, t.tag, l.lemma, l.pos FROM dd_wordform as w left join dd_tag as t on w.tag_id = t.id left join dd_lemma as l on w.lemma_id = l.id order by w.id LIMIT 100"
-);
+$parArray = Array();
+$whereQuery = createWhereQuery($parArray);
 
-var_dump($results);
+if (array_key_exists("manuscript", $_POST) && $_POST['manuscript'] != "") {
+    $results = $DB->select(
+        "SELECT w.id, w.context, w.date, w.description, w.description2, w.description3, w.ending, w.finished,
+w.namedentity, w.position1, w.position2, w.positiondetail, w.word, w.tag_id, w.lemma_id, t.tag, l.lemma, m.manuscript,
+l.pos FROM dd_manuscript as m left join dd_wordform as w on m.wordform_id = w.id left join dd_tag as t on w.tag_id = t.id left join dd_lemma as l on w.lemma_id = l.id ".$whereQuery." order by w.id", $parArray);
+} else {
+    $results = $DB->select(
+        "SELECT w.id, w.context, w.date, w.description, w.description2, w.description3, w.ending, w.finished,
+w.namedentity, w.position1, w.position2, w.positiondetail, w.word, w.tag_id, w.lemma_id, t.tag, l.lemma,
+l.pos FROM dd_wordform as w left join dd_tag as t on w.tag_id = t.id left join dd_lemma as l on w.lemma_id = l.id ".$whereQuery." order by w.id", $parArray);
+}
 
 $output = Array();
+$temp_value = null;
 foreach ($results as $res) {
-    $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);
-    array_push($output, $word);
+    if ($temp_value != null && $res["id"] != $temp_value->id) {
+        array_push($output, $temp_value);
+        $temp_value = null;
+    }
+    if ($temp_value == null) {
+        $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);
+        if (array_key_exists("manuscript", $res)) {
+            array_push($word->manuscript, $res["manuscript"]);
+        }
+        $temp_value = $word;
+    } else {
+        array_push($temp_value->manuscript, $res["manuscript"]);
+    }
 }
+echo json_encode(count($output)==0 ? null : $output);
+
 
-$result = json_encode(count($output)==0 ? null : $output);
-echo $result;
+function createWhereQuery(&$array) {
+    $whereQuery = "WHERE";
+    if (array_key_exists("lemma", $_POST) && $_POST['lemma'] != "") {
+        $array['lemma'] = $_POST['lemma']."%";
+        $whereQuery .= " l.lemma LIKE :lemma AND";
+    }
+    if (array_key_exists("word", $_POST) && $_POST['word'] != "") {
+        $array['word'] = $_POST['word'];
+        $whereQuery .= " w.word = :word AND";
+    }
+    if (array_key_exists("position1", $_POST) && $_POST['position1'] != "") {
+        $array['position1'] = $_POST['position1'];
+        $whereQuery .= " w.position1 = :position1 AND";
+    }
+    if (array_key_exists("position2", $_POST) && $_POST['position2'] != "") {
+        $array['position2'] = $_POST['position2'];
+        $whereQuery .= " w.position2 = :position2 AND";
+    }
+    if (array_key_exists("positiondetail", $_POST) && $_POST['positiondetail'] != "") {
+        $array['positiondetail'] = $_POST['positiondetail'];
+        $whereQuery .= " w.positiondetail = :positiondetail AND";
+    }
+    if (array_key_exists("manuscript", $_POST) && $_POST['manuscript'] != "") {
+        $array['manuscript'] = Array();
+        $nums = explode(",", $_POST['manuscript']);
+        $whereQuery .= " m.manuscript in (";
+        for ($x = 0; $x < count($nums); $x += 1) {
+            $array['manuscript'][$x] = $nums[$x];
+            $whereQuery .= ":manuscript".$x.",";
+        }
+        $whereQuery = substr_replace($whereQuery, "", -1, 1);
+        $whereQuery .= ") AND";
+    }
+    $tag = "____________%";
+    if (array_key_exists("tag_pos", $_POST) && $_POST['tag_pos'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_pos'], 0, 1);
+    }
+    if (array_key_exists("tag_case", $_POST) && $_POST['tag_case'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_case'], 1, 1);
+    }
+    if (array_key_exists("tag_number", $_POST) && $_POST['tag_number'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_number'], 2, 1);
+    }
+    if (array_key_exists("tag_gender", $_POST) && $_POST['tag_gender'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_gender'], 3, 1);
+    }
+    if (array_key_exists("tag_degree", $_POST) && $_POST['tag_degree'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_degree'], 4, 1);
+    }
+    if (array_key_exists("tag_shape", $_POST) && $_POST['tag_shape'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_shape'], 5, 1);
+    }
+    if (array_key_exists("tag_num", $_POST) && $_POST['tag_num'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_num'], 6, 1);
+    }
+    if (array_key_exists("tag_sentence", $_POST) && $_POST['tag_sentence'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_sentence'], 7, 1);
+    }
+    if (array_key_exists("tag_verb_person", $_POST) && $_POST['tag_verb_person'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_verb_person'], 8, 1);
+    }
+    if (array_key_exists("tag_verb_time", $_POST) && $_POST['tag_verb_time'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_verb_time'], 9, 1);
+    }
+    if (array_key_exists("tag_verb_degree", $_POST) && $_POST['tag_verb_degree'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_verb_degree'], 10, 1);
+    }
+    if (array_key_exists("tag_verb_aspect", $_POST) && $_POST['tag_verb_aspect'] != "") {
+        $tag = substr_replace($tag, $_POST['tag_verb_aspect'], 11, 1);
+    }
+    $array['tag'] = $tag;
+    $whereQuery .= " t.tag LIKE :tag";
+    return $whereQuery;
+}

application/model/DB.php

@@ -23 +23 @@
     }
 
     // (C) RUN A SELECT QUERY
-    function select($sql, $cond=null){
+    function select($sql, $params){
         $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("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) {
@@ -39 +62 @@
 
 // (D) DATABASE SETTINGS - CHANGE TO YOUR OWN!
 define('DB_HOST', 'localhost');
-define('DB_NAME', 'dalimil1');
+define('DB_NAME', 'dalimil2');
 define('DB_CHARSET', 'utf8');
 define('DB_USER', 'postgres');
 define('DB_PASSWORD', 'a');

application/model/Word.php

@@ -15 +15 @@
     public $word;
     public $lemma;
     public $tag;
+    public $manuscript = Array();
 
     function __construct($id, $context, $date, $description, $description2, $description3, $ending, $finished, $namedentity, $position1, $position2, $positiondetail, $word, $lemma, $tag)
     {

application/view/Index.php

@@ -6 +6 @@
     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("lemma", "ok");
+        // 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.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;