/server/clientFunc/evaluationData.php

<?php
/**
 * Copyright 2016, Georg-August-Universität Göttingen
 *
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use
 * this file except in compliance with the License. You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software distributed under the
 * License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
 * either express or implied. See the License for the specific language governing permissions
 * and limitations under the License.
 *
 * Information
 * Master thesis title: Automated importance assessment for new technologies and standards
 *
 * University: Georg August Universität Göttingen - Institute of Computer Science
 * Software Engineering for Distributed Systems
 *
 * Author: Florian Unger
 * Submission date: 2016-11-11
 *
 */

//error_reporting(E_ALL);
//ini_set('display_errors', '1');

require '../class/classes.php';
require '../config/config.php';
require '../func/func.php';

header('Content-type: application/json');
header('Cache-Control: no-cache, must-revalidate');

/**
 * Store the evaluated data to database for comparison and future work.
 */

/*
 * Database connection.
 */
$mysqli = new ROSmysqli();

/*
 * Catch Data.
 */
if (file_get_contents("php://input") != '') {
    $data = json_decode(file_get_contents("php://input"));
    addSearch($data, $mysqli);
} elseif (isset($_GET['searchid'])) {
    $term = getSearchTerm($_GET['searchid'], $mysqli);
    $resultItems = getSearchResults($_GET['searchid'], $mysqli);
    echo '{"resultItems":[' . $resultItems . '],' . $term . ', "idSearch":' . $_GET['searchid'] . ' , "searchTime":"' . getSearchDateTime($_GET['searchid'], $mysqli) . '"}';
}

/**
 * Get SearchTerm in JSON Format of a search.
 * @param $search_done_id
 *  Identifier of the actual search
 * @param $mysqli
 *  Database connection
 * @return string
 *  SearchTerm in JSON format.
 */
function getSearchTerm($search_done_id, $mysqli)
{
    $term = '';

    $query = "SELECT search_term.id, search_term.term, search_term.searchTopic_id "
        . "FROM  `search_done` ,  `search_term` "
        . "WHERE search_done.searchTerm_id = search_term.id "
        . "AND search_done.id =" . $search_done_id;
    if ($result = $mysqli->query($query)) {
        while ($row = $result->fetch_assoc()) {
            //"term":{"id":12,"term":"UML","searchTopicId":2}
            $term = '"term":{"id":' . $row['id'];
            $term .= ',"term":"' . $row['term'];
            $term .= '","searchTopicId":' . $row['searchTopic_id'];
            $term .= '}';
        }
    }
    return $term;
}

/**
 * Query the evaluation results of a search.
 * @param $search_done_id
 * @param $mysqli
 * @return string
 */
function getSearchResults($search_done_id, $mysqli)
{
    $searchResults = '';
    $searchResults = queryResult($mysqli, 'int', $searchResults, $search_done_id);
    $searchResults = queryResult($mysqli, 'double', $searchResults, $search_done_id);
    $searchResults = queryResult($mysqli, 'category', $searchResults, $search_done_id);
    $searchResults = queryResult($mysqli, 'text', $searchResults, $search_done_id);
    $searchResults = queryResult($mysqli, 'json', $searchResults, $search_done_id);
    return $searchResults;
}

/**
 * Query the evaluation results of a table.
 * @param $mysqli
 * @param $tableType
 * @param $searchResults
 * @param $search_done_id
 * @return string
 */
function queryResult($mysqli, $tableType, $searchResults, $search_done_id)
{
    $query = "SELECT value, id_evaluation, valueCanNotBeFound FROM value_" . $tableType . " WHERE search_done_id =" . $search_done_id;
    if($tableType == 'json')
    {
        $query = "SELECT value, id_evaluation, valueCanNotBeFound, object FROM value_" . $tableType . " WHERE search_done_id =" . $search_done_id;
    }
    if ($result = $mysqli->query($query)) {
        while ($row = $result->fetch_assoc()) {
            if ($searchResults != '') {
                $searchResults .= ', ';
            }
            if($tableType != 'json')
            {
                $searchResults .= '{"value":"' . $row['value'] . '",' .
                    '"type":"' . $tableType . '",';
            }
            else
            {
                $searchResults .= '{"value":' . $row['value'] . ','.
                    '"type":"' . $row['object'] . '",';
            }

            if($row['valueCanNotBeFound'] == 2)
            {
                $searchResults .= '"valueCanNotBeFound":"noValA",';
            }
            else
            {
                $searchResults .= '"valueCanNotBeFound":"val",';
            }
            $searchResults .= '"idEvaluation":' . $row['id_evaluation'] . '}';
        }
    }
    return $searchResults;
}

/**
 * Insert new search. Get id of this search to map the evaluated data.
 * @param $data
 *  Decoded JSON data to be added to database.
 * @param $mysqli
 *  Database connection.
 */
function addSearch($data, $mysqli)
{
    $searchId = $data->idSearch * 1;
    if($searchId > 0)
    {
        $query = "UPDATE search_done SET  `timestamp` =  now() WHERE  `search_done`.`id` =" . $searchId;
        $mysqli->query($query);
        $search_done_id = $searchId;
    }
    else{
        $termId = $data->term->id * 1;
        $query = "INSERT INTO search_done (searchTerm_id) VALUES (" . $termId . ")";
        $mysqli->query($query);
        $search_done_id = $mysqli->insert_id;
    }

    addEvaluationValues($search_done_id, $data, $mysqli);
}

/**
 * Add evaluation values to database
 * @param $search_done_id
 *  Identifier of the actual search
 * @param $data
 *  Evaluated data to be stored
 * @param $mysqli
 *  Database connection
 */
function addEvaluationValues($search_done_id, $data, $mysqli)
{
    /*
 * Values for insert statements for the appropriate tables.
 * Depending on the type of the resultItems the values will be stored in appropriate tables.
 * E.g.
 * $valuesInt = ('12', '2', '1'), (...)
 * INSERT INTO value_int (`value`, `search_done_id`, `id_evaluation`) VALUES ($valuesInt)
 *
 */
    $valuesDouble = '';
    $valuesInt = '';
    $valuesText = '';
    $valuesCategory = '';
    foreach ($data->resultItems as $item) {
        $dataType = $item->type . '';
        switch ($dataType) {
            case 'int':
                if(!updateValue($search_done_id, $item, $mysqli, 'value_int'))
                {
                    $valuesInt = getInputStream($search_done_id, $item, $valuesInt);
                }
                break;
            case 'double':
                if(!updateValue($search_done_id, $item, $mysqli, 'value_double'))
                {
                    $valuesDouble = getInputStream($search_done_id, $item, $valuesDouble);
                }
                break;
            case 'category':
                if(!updateValue($search_done_id, $item, $mysqli, 'value_category'))
                {
                    $valuesCategory = getInputStream($search_done_id, $item, $valuesCategory);
                }
                break;
            case 'hyperlinkList':
                if(!updateValueJson($search_done_id, $item, $mysqli))
                {
                    insertValueJson($search_done_id, $item, $mysqli, 'hyperlinkList');
                }
                //var_dump();

                break;
            default: //either this is a text value or it is not a defined type and will be stored as text
                if(!updateValue($search_done_id, $item, $mysqli, 'value_text'))
                {
                    $valuesText = getInputStream($search_done_id, $item, $valuesText);
                }

        }
    }
    if ($valuesInt != '') {
        $query = "INSERT INTO value_int (`value`, `search_done_id`, `id_evaluation`, `valueCanNotBeFound`) VALUES " . $valuesInt;
        $mysqli->query($query);
    }
    if ($valuesDouble != '') {
        $query = "INSERT INTO value_double (`value`, `search_done_id`, `id_evaluation`, `valueCanNotBeFound`) VALUES " . $valuesDouble;
        $mysqli->query($query);
    }
    if ($valuesCategory != '') {
        $query = "INSERT INTO value_category (`value`, `search_done_id`, `id_evaluation`, `valueCanNotBeFound`) VALUES " . $valuesCategory;
        $mysqli->query($query);
    }
    if ($valuesText != '') {
        $query = "INSERT INTO value_text (`value`, `search_done_id`, `id_evaluation`, `valueCanNotBeFound`) VALUES " . $valuesText;
        $mysqli->query($query);
    }

    //Only "idSearch": must be added
    $jsonOutput = file_get_contents("php://input");
    $pos = strrpos($jsonOutput, '}');
    if ($pos === false) {
        $pos = strlen($jsonOutput) - 1;
    }
    $jsonOutput[$pos] = ' '; //remove }
    $jsonOutput .= ', "idSearch":' . $search_done_id . ' , "searchTime":"' . getSearchDateTime($search_done_id, $mysqli) . '"}';
    echo $jsonOutput;
}

/**
 * Get date and time of search
 * @param $search_done_id
 * @param $mysqli
 * @return string
 */
function getSearchDateTime($search_done_id, $mysqli)
{
    $searchTime = '';
    $query = "SELECT DATE_FORMAT(  `timestamp` ,  '%d.%m.%Y - %H:%i' ) AS searchTime FROM  `search_done` WHERE id=" . $search_done_id;
    if ($result = $mysqli->query($query)) {
        while ($row = $result->fetch_assoc()) {
            $searchTime = $row['searchTime'];
        }
    }
    return $searchTime;
}

/**
 * Extend input stream with further tuple.
 * @param $search_done_id
 *  Identifier of the actual search
 * @param $item
 *  One evaluation that should be stored
 * @param $valueStream
 *  Data stream of tuple to be inserted in database
 * @return string
 *  Data stream of tuple to be inserted in database extend by new item
 */
function getInputStream($search_done_id, $item, $valueStream)
{
    if ($valueStream != '') {
        $valueStream .= ', ';
    }
    $value = handleInputValue($item->value, $item->valueCanNotBeFound);

    return $valueStream . "(" . $value . ", '" . $search_done_id . "', '" . $item->idEvaluation . "', "
    . handleValueCanNotBeFound($item->valueCanNotBeFound) .")";
}

/**
 * Tries to update the values. Return true in case of update and false else.
 * @param $search_done_id
 * @param $item
 * @param $mysqli
 * @param $table
 * @return bool
 *
 */
function updateValue($search_done_id, $item, $mysqli, $table)
{
    $query = "SELECT id FROM ".$table." WHERE `search_done_id` = ".$search_done_id." AND `id_evaluation` = " . $item->idEvaluation;
    $mysqli->query($query);
    $id = 0; //id of the existing value
    if ($result = $mysqli->query($query)) {
        while ($row = $result->fetch_assoc()) {
            $id = $row['id'];
        }
    }
    if($id > 0)
    {
        $value = handleInputValue($item->value, $item->valueCanNotBeFound);

        $query = "UPDATE ".$table." SET  `value` =  " . $value . ", `valueCanNotBeFound` =  '"
            .handleValueCanNotBeFound($item->valueCanNotBeFound)."' WHERE  id =" . $id;


        $mysqli->query($query);
        return true;
    }
    return false;
}

/**
 * Updates values that are stored as json objects.
 * @param $search_done_id
 * @param $item
 * @param $mysqli
 * @return bool
 */
function updateValueJson($search_done_id, $item, $mysqli)
{
    $query = "SELECT id FROM value_json WHERE `search_done_id` = ".$search_done_id." AND `id_evaluation` = " . $item->idEvaluation;
    $mysqli->query($query);
    $id = 0; //id of the existing value
    if ($result = $mysqli->query($query)) {
        while ($row = $result->fetch_assoc()) {
            $id = $row['id'];
        }
    }
    if($id > 0)
    {
        $json = json_encode($item->value);
        $query = "UPDATE value_json SET  `value` =  '" . $json . "', `valueCanNotBeFound` =  '"
            .handleValueCanNotBeFound($item->valueCanNotBeFound)."' WHERE  id =" . $id;
        $mysqli->query($query);
        return true;
    }
    return false;
}

/**
 * Insert values that are stored as json objects.
 * @param $search_done_id
 * @param $item
 * @param $mysqli
 * @param $object
 */
function insertValueJson($search_done_id, $item, $mysqli, $object)
{
    $json = json_encode($item->value);
    $query = "INSERT INTO value_json (`value`, `object`, `search_done_id`, `id_evaluation`, `valueCanNotBeFound` ) VALUES ('" .
        $json . "', '" .
        $object . "', " .
        $search_done_id . ", " .
        $item->idEvaluation . ", '" .
        handleValueCanNotBeFound($item->valueCanNotBeFound) . "')";
    $mysqli->query($query);
}

/**
 * Proof whether the value is null or not
 * @param $value
 * @param $noVal
 * @return string
 */
function handleInputValue($value, $noVal)
{
    $value = $value . '';
    if($value != '' &&  $noVal != 'noValA')
    {
        return "'".$value . "'";
    }
    return 'NULL';
}

/**
 * Handles string state to database state of the attribute.
 * @param $valueCanNotBeFound
 * @return int
 */
function handleValueCanNotBeFound($valueCanNotBeFound)
{
    if($valueCanNotBeFound == 'noValA')
    {
        return 2;
    }
    return 1;
}