linequest/controller/forms.php

<?php

/**
 * Este arquivo é parte do software linequest
 * Ambiente de questionários para a coleta de dados
 *
 * Laboratório de Informática na Educação - LInE
 * https://www.usp.br/line/
 *
 * Utilize os atributos definidos abaixo para
 * configurar o ambiente de questionários.
 *
 * @author Lucas Calion
 * @author Igor Félix
 */

 require_once ('../config/linequest.php');

 function cleanTitle ($title) {
    $clearstring = filter_var($title, FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_HIGH);
    $clearstring = trim(preg_replace("/[^a-zA-Z0-9 ]/",'',$clearstring));
    $clearstring = trim(preg_replace("/[[:space:]]+/",'_',$clearstring));
    return strtolower($clearstring);
 }

 function connect () {
    global $CFG, $DB;
    $DB  = new mysqli($CFG->dbhost, $CFG->dbuser, $CFG->dbpass, $CFG->dbname);
    if ($DB->connect_error) {
       die("Connection failed: " . $DB->connect_error);
    }
    $DB->set_charset("utf8");
 }

 function get_id_by_uuid ($uuid) {
    global $DB; connect();

    $sql = "SELECT id FROM answers WHERE uuid = ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    $stmt->bind_param('s', $uuid);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($userID);
    if ($stmt->fetch()) {
       $stmt->free_result();
       $stmt->close();
       $DB->close();
       return $userID;
    }
    $stmt->free_result();
    $stmt->close();
    $DB->close();
    return -1;
 }

 function getQuestionaireInfo ($hash) {
    global $DB; connect();

    $sql = "SELECT id,title FROM questionnaire WHERE view_hash = ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    $stmt->bind_param('s', $hash);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($qid, $title);
    if ($stmt->fetch()) {
       $stmt->free_result();
       $stmt->close();
       $DB->close();
       return ['qid' => $qid, 'title' => $title];
    }
    throw new Exception("User attempted to submit data to a non-existent questionnaire.");
 }

 function getQuestionaireInfoBySecret ($hash) {
    global $DB; connect();

    $sql = "SELECT id, title, view_hash, created_at FROM questionnaire WHERE edit_hash = ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    $stmt->bind_param('s', $hash);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($qid, $title,$view_hash, $date);
    if ($stmt->fetch()) {
       $stmt->free_result();
       $stmt->close();
       $DB->close();
       return ['qid' => $qid, 'title' => $title, 'view_hash'=>$view_hash, 'date'=>$date];
    }
    throw new Exception("User attempted to submit data to a non-existent questionnaire.");
 }

 function storeUserSubmission ($data, $qid, $uuid) {
    $id_p = get_id_by_uuid($uuid);
    if ($id_p >= 0) {
       updateUserRecord($id_p, $qid, $data);
       return;
    }
    $sql =  "INSERT INTO answers (q_id, uuid, json)
       VALUES (?, ?, ?)";


    global $DB; connect();

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: ($DB->errno) $DB->error");
    }

    if (!$stmt->bind_param("iss", $qid, $uuid, $data)) {
        die("Binding parameters failed: ($stmt->errno) $stmt->error");
    }

    if (!$stmt->execute()) {
        die("Execute failed: ($stmt->errno) $stmt->error");
    }

    $stmt->close();
    $DB->close();
 }

 function updateUserRecord ($id, $qid, $data) {
    global $DB; connect();
    $sql =  "UPDATE answers SET json = ?
       WHERE id = ?";
    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: ($DB->errno) $DB->error");
    }

    if (!$stmt->bind_param("si", $data, $id)) {
        die("Binding parameters failed: ($stmt->errno) $stmt->error");
    }

    if (!$stmt->execute()) {
        die("Execute failed: ($stmt->errno) $stmt->error");
    }

    $stmt->close();
    $DB->close();
 }

 function storeNewForm ($form, $keys, $source) {
    global $DB; connect();
    $sql = "INSERT INTO questionnaire(email, title, description, thanks, view_hash, edit_hash, source) VALUES (?, ?, ?, ?, ?, ?, ?)";
    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }
    if (!$stmt->bind_param("sssssss", $form['email'], $form['title'], $form['description'], $form['thanks'], $keys[1], $keys[0], $source)) {
        die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    if (!$stmt->execute()) {
        die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    $qid = $stmt->insert_id;
    $stmt->close();
    $DB->close();
    $questions = $form['questions'];
    foreach ($questions as $question) {
       if ($question->type === 'PAGEBREAK') continue;
       storeQuestionText($qid, $question);
       if (in_array($question->type, ['M','S'])) {
          storeQuestionMapping($qid, $question);
       }
    }

 }

 function storeQuestionText($qid, $question) {
    global $DB; connect();
    $sql = "INSERT INTO question_text(q_id, question, text) VALUES (?, ?, ?)";
    $number = substr($question->number, 1);
    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: ($DB->errno) $DB->error");
    }
    if (!$stmt->bind_param("iis", $qid, $number, $question->text)) {
       die("Binding parameters failed: ($stmt->errno) $stmt->error");
    }

    if (!$stmt->execute()) {
       die("Execute failed: ($stmt->errno) $stmt->error");
    }
    $stmt->close();
    $DB->close();
 }

 function storeQuestionMapping ($qid, $question) {
    global $DB; connect();
    $sql = "INSERT INTO question_mapping(q_id, question, value, text) VALUES (?, ?, ?, ?)";
    $number = substr($question->number, 1);
    if (!($stmt = $DB->prepare($sql))) {
      die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }
    foreach ($question->choices as $choice) {
       if (!$stmt->bind_param("iiss", $qid, $number, $choice->value, $choice->text)) {
         die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
       }

       if (!$stmt->execute()) {
          die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
       }
    }
    $stmt->close();
    $DB->close();
 }

 function getFormThanks ($hash) {
    global $DB; connect();

    $sql = "SELECT title, thanks FROM questionnaire WHERE view_hash = ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    if (!$stmt->bind_param("s", $hash)) {
       die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    if (!$stmt->execute()) {
       die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    $stmt->store_result();
    $stmt->bind_result($title, $thanks);
    if ($stmt->fetch()) {
       $result = ['title'=>$title, 'thanks'=>$thanks];
       $stmt->free_result();
       $stmt->close();
       $DB->close();
       return $result;
    }
    throw new Exception("User attempted to access data of a non-existent questionnaire.");
 }

 function checkFormAccess ($email, $secret) {
    global $DB; connect();

    $sql = "SELECT id FROM questionnaire WHERE edit_hash = ? AND email = ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    if (!$stmt->bind_param("ss", $secret, $email)) {
       die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    if (!$stmt->execute()) {
       die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    $stmt->store_result();
    $stmt->bind_result($qid);
    $found = false;
    if ($stmt->fetch()) {
       $found = true;
    }
    $stmt->free_result();
    $stmt->close();
    $DB->close();
    return $found;
 }

 function get_user_form ($secret) {
    global $DB; connect();

    $sql = "SELECT id FROM questionnaire WHERE edit_hash = ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    if (!$stmt->bind_param("s", $secret)) {
       die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    if (!$stmt->execute()) {
       die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    $stmt->store_result();
    $stmt->bind_result($qid);
    if ($stmt->fetch()) {
       $stmt->free_result();
       $stmt->close();
       $DB->close();
       return $qid;
    }
    die('User attempted to acess data from an unknown questionnaire!');
 }

 function get_total_rows ($form_id) {
    global $DB; connect();

    $sql = "SELECT count(id) FROM answers WHERE q_id = ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    if (!$stmt->bind_param("i", $form_id)) {
       die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    if (!$stmt->execute()) {
       die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    $stmt->store_result();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->free_result();
    $stmt->close();
    $DB->close();

    return $count;
 }

 function get_records_pagination($form_id, $page, $no_of_records_per_page) {
    global $DB; connect();

    $sql = "SELECT id, json FROM answers WHERE q_id = ? LIMIT ?, ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    if (!$stmt->bind_param("iii", $form_id, $page,$no_of_records_per_page)) {
       die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    if (!$stmt->execute()) {
       die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
    }

    $stmt->store_result();
    $stmt->bind_result($id, $json);
    $all_data = array();
    while ($stmt->fetch()) {
       $data = json_decode($json);
       $data->id = $id;
       $all_data[] = $data;
    }
    $stmt->free_result();
    $stmt->close();
    $DB->close();

    return $all_data;
 }

 function loadQuestionsMapping ($qid) {
    global $DB; connect();

    $sql = "SELECT question, value, text FROM question_mapping WHERE q_id = ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    $stmt->bind_param('i', $qid);
    $stmt->execute();
    $stmt->store_result();
    $result = array();
    $stmt->bind_result($number, $value, $text);
    while ($stmt->fetch()){
       $question = new stdClass;
       $question->number = $number;
       $question->value = $value;
       $question->text = $text;
       $result[] = $question;
    }
    $stmt->free_result();
    $stmt->close();
    $DB->close();
    return $result;
 }

 function loadQuestionsText ($qid) {
    global $DB; connect();

    $sql = "SELECT question, text FROM question_text WHERE q_id = ?";

    if (!($stmt = $DB->prepare($sql))) {
        die("Prepare failed: (" . $DB->errno . ") " . $DB->error);
    }

    $stmt->bind_param('i', $qid);
    $stmt->execute();
    $stmt->store_result();
    $result = array();
    $stmt->bind_result($number, $text);
    while ($stmt->fetch()){
       $question = new stdClass;
       $question->number = $number;
       $question->text = $text;
       $result[] = $question;
    }
    $stmt->free_result();
    $stmt->close();
    $DB->close();
    return $result;
 }

 ?>