Revize d9606f0d

Feature #8345 Implementce filterů dat - Server

application/controller/TableController.php

@@ -1 +1 @@
 <?php
 require "../model/DB.php";
+require "../model/Tag.php";
+require "../model/Word.php";
+require "../model/Lemma.php";
 $DB = new DB();
 
-// (B) SEARCH FOR USERS
+$parArray = Array();
+$whereQuery = createWhereQuery($parArray);
+
 $results = $DB->select(
-    "SELECT * FROM dd_wordform limit 10"
-);
+        "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);
+
+$output = Array();
+$output["data"] = Array();
+$temp_value = null;
+foreach ($results as $res) {
+    if ($temp_value != null && $res["id"] != $temp_value->id) {
+        array_push($output["data"], $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"]);
+    }
+}
+$output["count"] = count($output["data"]);
+if (array_key_exists("page", $_POST) && array_key_exists("items_per_page", $_POST)) {
+    $output["data"] = array_slice($output["data"], $_POST['page'] * $_POST['items_per_page'], $_POST['items_per_page']);
+} else {
+    $output["data"] = array_slice($output["data"], 0, 50);
+}
+echo json_encode(count($output)==0 ? null : $output);
+
 
-// (C) OUTPUT RESULTS
-$result = json_encode(count($results)==0 ? null : $results);
-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("finished", $_POST) && $_POST['finished'] != "") {
+        $array['finished'] = $_POST['finished'];
+        $whereQuery .= " w.finished = :finished 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("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) {
@@ -39 +68 @@
 
 // (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/Lemma.php

@@ +1 @@
+<?php
+
+class Lemma {
+    public $id;
+    public $lemma;
+    public $pos;
+
+    public function __construct($id, $lemma, $pos)
+    {
+        $this->id = $id;
+        $this->lemma = $lemma;
+        $this->pos = $pos;
+    }
+}

application/model/Tag.php

@@ +1 @@
+<?php
+
+class Tag {
+    public $id;
+    public $tag;
+
+    function __construct($id, $tag)
+    {
+        $this->id = $id;
+        $this->tag = $tag;
+    }
+}

application/model/Word.php

@@ +1 @@
+<?php
+class Word {
+    public $id;
+    public $context;
+    public $date;
+    public $description;
+    public $description2;
+    public $description3;
+    public $ending;
+    public $finished;
+    public $namedentity;
+    public $position1;
+    public $position2;
+    public $positiondetail;
+    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)
+    {
+        $this->id = $id;
+        $this->context = $context;
+        $this->date = $date;
+        $this->description = $description;
+        $this->description2 = $description2;
+        $this->description3 = $description3;
+        $this->ending = $ending;
+        $this->finished = $finished;
+        $this->namedentity = $namedentity;
+        $this->position1 = $position1;
+        $this->position2 = $position2;
+        $this->positiondetail = $positiondetail;
+        $this->word = $word;
+        $this->lemma = $lemma;
+        $this->tag = $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("page", "0");
+        data.append("items_per_page", "50");
+        //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.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;