798 lines
24 KiB
PHP
798 lines
24 KiB
PHP
<?php
|
|
/** \file include\db_questionaire.php
|
|
* \brief DI webinterface database functions - (evaluation) questionaire
|
|
* \author Rob Schalken, Core|Vision
|
|
* \version $Revision: 26247 $
|
|
* \date $Date: 2016-02-29 10:40:22 +0100 (Mon, 29 Feb 2016) $
|
|
*
|
|
* This file contains database functions for the (evaluation) questionaire.
|
|
*/
|
|
|
|
/**
|
|
* Read all questionaires for a customer
|
|
*
|
|
* Inputs:
|
|
* - i18n Language for the questionaire
|
|
* - questionare_ids Database id for the questionaire(s)
|
|
* - customer_ids Customer id or array with customer ids; may be 'null'
|
|
* - type Select type of enquete (feedback/draaiboek/NULL)
|
|
*
|
|
* Returns: Array with questionaires ('actie','id', 'datum', 'titel' and array of
|
|
* questions)
|
|
*/
|
|
function db_fetch_questionaires($i18n, $questionaire_ids = NULL, $customer_ids = NULL, $type = NULL)
|
|
{
|
|
if( !$questonaire_ids ) {
|
|
if( !$customer_ids ) {
|
|
// Search also for all underlying customers
|
|
$row_customers = db_search_customers();
|
|
|
|
// Add own id to array => when not in array
|
|
$found_cust = 0;
|
|
if( is_array($row_customers) ) {
|
|
foreach( $row_customers as $row_customer ) {
|
|
if( $row_customer['id'] == $_PAGE_INFO['login']['customer']['id'] ) {
|
|
$found_cust = 1;
|
|
}
|
|
}
|
|
}
|
|
if( !$found_cust ) {
|
|
$current_customer = db_fetch_customer($_PAGE_INFO['login']['customer']['id'], 1);
|
|
array_push($row_customers, $current_customer);
|
|
}
|
|
|
|
// build array with customer ids
|
|
$customer_ids = array();
|
|
foreach( $row_customers as $row_customer )
|
|
$customer_ids[] = $row_customer['id'];
|
|
|
|
// All underlying templates we have, now add global templates
|
|
array_push($customer_ids, "NULL");
|
|
}
|
|
else if( !is_array($customer_ids) ) {
|
|
// convert single customer id into an array
|
|
$customer_ids = array($customer_ids);
|
|
}
|
|
}
|
|
else {
|
|
// don't use customer ids when questionaire ids are provided
|
|
$customer_ids = null;
|
|
}
|
|
|
|
|
|
// get questionaire(s)
|
|
$query = "SELECT enquete.id,enquete.level,enquete.datum,enquete_i18n.data as titel ";
|
|
$query .= "FROM enquete,enquete_i18n ";
|
|
$query .= "WHERE ";
|
|
$query .= "enquete.id = enquete_i18n.enquete AND ";
|
|
|
|
// Use type
|
|
if (!is_null($type)) {
|
|
$query .= "enquete.type ='" . $type . "' AND ";
|
|
}
|
|
|
|
$query .= "enquete_i18n.i18n='" . $i18n . "' AND ";
|
|
$query .= "enquete_i18n.vraag IS NULL AND ";
|
|
if( $customer_ids )
|
|
// Check for global templates
|
|
if (in_array("NULL", $customer_ids)) {
|
|
$query .= "(";
|
|
}
|
|
|
|
$query .= "enquete.klant IN (" . implode(",", $customer_ids) . ") ";
|
|
|
|
// Check for global templates
|
|
if (in_array("NULL", $customer_ids)) {
|
|
$query .= "OR enquete.klant is NULL) ";
|
|
}
|
|
elseif( is_array($questionaire_ids) )
|
|
$query .= "enquete.id IN (" . implode(",", $questionaire_ids) . ")";
|
|
else
|
|
$query .= "enquete.id=" . $questionaire_ids;
|
|
$questionaires = db_fetch_data($query);
|
|
|
|
if( !$questionaires ) return FALSE;
|
|
|
|
// build the result array
|
|
$result = array();
|
|
foreach( $questionaires as $questionaire ) {
|
|
$questionaire['vragen'] = db_fetch_questionaire($questionaire['id'], $i18n);
|
|
array_push($result, $questionaire);
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Read a questionaire
|
|
* Private function, always use 'db_fetch_questionaires()'
|
|
*
|
|
* Inputs:
|
|
* - dbid Database id for the questionaire
|
|
* - i18n Language
|
|
*
|
|
* Returns: Array with questions
|
|
*/
|
|
function db_fetch_questionaire($dbid, $i18n)
|
|
{
|
|
// fetch the questions
|
|
$query = "SELECT enquete_vraag.actie,enquete_vraag.id,enquete_vraag.volgorde,enquete_vraag.type,enquete_vraag.antwoorden,enquete_i18n.data as vraag ";
|
|
$query .= "FROM enquete_vraag,enquete_i18n ";
|
|
$query .= "WHERE ";
|
|
$query .= "enquete_i18n.vraag=enquete_vraag.id AND ";
|
|
$query .= "enquete_vraag.enquete=" . $dbid . " AND ";
|
|
$query .= "enquete_i18n.i18n='" . $i18n . "' AND ";
|
|
$query .= "enquete_i18n.key IS NULL ";
|
|
$query .= "ORDER BY enquete_vraag.volgorde";
|
|
$questionaire = db_fetch_data($query);
|
|
if( !$questionaire ) return FALSE;
|
|
|
|
// re-arrange the questions and get additional info
|
|
$result = array();
|
|
foreach( $questionaire as $question ) {
|
|
$query = "SELECT `key`,`data` ";
|
|
$query .= "FROM enquete_i18n ";
|
|
$query .= "WHERE ";
|
|
$query .= "i18n='" . $i18n . "' AND ";
|
|
$query .= "enquete=" . $dbid . " AND ";
|
|
$query .= "vraag=" . $question['id'];
|
|
$question_info = db_fetch_data($query);
|
|
// re-arrange question_inofo
|
|
foreach( $question_info as $question_data )
|
|
$question_info[$question_data['key']] = $question_data['data'];
|
|
// prepare the meta-info arrays 'match' or 'antwoorden'
|
|
switch( $question['type'] ) {
|
|
case 'text':
|
|
case 'simpletext':
|
|
$question['match'] = $question['antwoorden'];
|
|
unset($question['antwoorden']);
|
|
break;
|
|
case 'set':
|
|
case 'enum':
|
|
$answers = array();
|
|
foreach( explode(",", $question['antwoorden']) as $answer ) {
|
|
// preserve order
|
|
$answers[] = array('key' => $answer, 'antwoord' => $question_info[$answer]);
|
|
}
|
|
$question['antwoorden'] = $answers;
|
|
break;
|
|
}
|
|
|
|
$result[$question['id']] = $question;
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Read a single questionaire
|
|
*
|
|
* Inputs:
|
|
* - id Question id
|
|
*
|
|
* Returns: Array with question info
|
|
*/
|
|
function db_fetch_single_questionaire($id,$i18n,$dbid){
|
|
|
|
// Initial values
|
|
// $query = "SELECT * FROM enquete_vraag where id='" . $id . "'";
|
|
|
|
|
|
$query = "SELECT enquete_vraag.actie,enquete_vraag.id,enquete_vraag.volgorde,enquete_vraag.type,enquete_vraag.antwoorden,enquete_i18n.data as vraag ";
|
|
$query .= "FROM enquete_vraag,enquete_i18n ";
|
|
$query .= "WHERE ";
|
|
$query .= "enquete_i18n.vraag=enquete_vraag.id AND ";
|
|
$query .= "enquete_vraag.id=" . $id . " AND ";
|
|
$query .= "enquete_i18n.i18n='" . $i18n . "' AND ";
|
|
$query .= "enquete_i18n.key IS NULL ";
|
|
$query .= "ORDER BY enquete_vraag.volgorde";
|
|
$questionaire = db_fetch_data($query);
|
|
|
|
if( !$questionaire ) return FALSE;
|
|
|
|
if(is_array($questionaire)){
|
|
|
|
// re-arrange the questions and get additional info
|
|
$result = array();
|
|
foreach( $questionaire as $question ) {
|
|
|
|
|
|
|
|
$query = "SELECT `key`,`data` ";
|
|
$query .= "FROM enquete_i18n ";
|
|
$query .= "WHERE ";
|
|
$query .= "i18n='" . $i18n . "' AND ";
|
|
$query .= "enquete=" . $dbid . " AND ";
|
|
$query .= "vraag=" . $question['id'];
|
|
$question_info = db_fetch_data($query);
|
|
|
|
if(is_array($question_info)){
|
|
// re-arrange question_inofo
|
|
foreach($question_info as $question_data ){
|
|
$question_info[$question_data['key']] = $question_data['data'];
|
|
}
|
|
// prepare the meta-info arrays 'match' or 'antwoorden'
|
|
switch( $question['type'] ) {
|
|
case 'text':
|
|
case 'simpletext':
|
|
$question['match'] = $question['antwoorden'];
|
|
unset($question['antwoorden']);
|
|
break;
|
|
case 'set':
|
|
case 'enum':
|
|
$answers = array();
|
|
foreach( explode(",", $question['antwoorden']) as $answer ) {
|
|
// preserve order
|
|
$answers[] = array('key' => $answer, 'antwoord' => $question_info[$answer]);
|
|
}
|
|
$question['antwoorden'] = $answers;
|
|
break;
|
|
}
|
|
}
|
|
|
|
$result[$question['id']] = $question;
|
|
}
|
|
}else{
|
|
$result = $questionaire;
|
|
}
|
|
return $result;
|
|
|
|
}
|
|
|
|
|
|
|
|
function db_fetch_questionaires_enquete($enquete){
|
|
$result = null;
|
|
|
|
$result = db_fetch_data("SELECT * FROM enquete_vraag WHERE enquete='" . $enquete . "'");
|
|
|
|
return $result;
|
|
}
|
|
|
|
|
|
/**
|
|
* Store a questionaire
|
|
*
|
|
* TO DO
|
|
*/
|
|
function db_store_questionaire($user_id, $project_id, $dbid, $answers , $latitude, $longitude , $referencenr)
|
|
{
|
|
global $_PAGE_INFO;
|
|
|
|
// Check coordinates
|
|
if(is_null($latitude)){
|
|
|
|
if(isset($_SESSION[$_PAGE_INFO['id']]['app']['gps']['lat']) && !empty($_SESSION[$_PAGE_INFO['id']]['app']['gps']['lat'])){
|
|
$latitude = $_SESSION[$_PAGE_INFO['id']]['app']['gps']['lat'];
|
|
}
|
|
}
|
|
|
|
if(is_null($longitude)){
|
|
if(isset($_SESSION[$_PAGE_INFO['id']]['app']['gps']['lon']) && !empty($_SESSION[$_PAGE_INFO['id']]['app']['gps']['lon'])){
|
|
$longitude = $_SESSION[$_PAGE_INFO['id']]['app']['gps']['lon'];
|
|
}
|
|
}
|
|
|
|
$check_entry = db_get_enquete_entry_id($dbid, $user_id, $project_id);
|
|
|
|
// Check if there is not already an entry for this user in combination with the enquete en project
|
|
if(empty($check_entry)){
|
|
// Create enquete entry
|
|
|
|
// store user info etc
|
|
$query = "INSERT INTO enquete_entry (enquete,gebruiker,project,datum,latitude,longitude) VALUES (";
|
|
$query .= $dbid . ",";
|
|
$query .= $user_id . ",";
|
|
$query .= ($project_id ? $project_id : "NULL") . ",";
|
|
$query .= "NOW() ,";
|
|
$query .= (!is_numeric($latitude)) ? "NULL," : "'" . ($latitude) . "',";
|
|
$query .= (!is_numeric($longitude)) ? "NULL)" : "'" . ($longitude) . "')";
|
|
|
|
if( !db_store_data($query) ) return FALSE;
|
|
$id = db_fetch_last_id();
|
|
DBG('create entry');
|
|
}else{
|
|
$id = $check_entry[0]['id'];
|
|
}
|
|
|
|
// Anser is array (always size of one)
|
|
foreach( $answers as $question => $answer ) {
|
|
$query = "SELECT * FROM enquete_data WHERE enquete_entry=" . $id . " AND vraag=" . $question . " AND volgnummer=" . $referencenr . "";
|
|
}
|
|
|
|
$questionaire = db_fetch_data($query);
|
|
|
|
$update = false;
|
|
|
|
// Check if there is already an question
|
|
if(!empty($questionaire)){
|
|
$update = true;
|
|
}
|
|
|
|
// Check if question already is stored in db
|
|
if($update){
|
|
|
|
foreach( $answers as $question => $answer ) {
|
|
// update question
|
|
$query = "UPDATE enquete_data SET antwoord=";
|
|
if( is_array($answer) )
|
|
$query .= "'" . specialchars(implode(",", $answer)) . "'";
|
|
else
|
|
$query .= "'" . specialchars($answer) . "'";
|
|
|
|
$query .=" WHERE enquete_entry='" . $id . "' AND vraag='" . $question . "' AND volgnummer=" . $referencenr . "";
|
|
}
|
|
db_store_data($query);
|
|
|
|
}else{
|
|
// Store question
|
|
$query = "INSERT INTO enquete_data (enquete_entry,vraag,antwoord,volgnummer) VALUES ";
|
|
$sep = "";
|
|
foreach( $answers as $question => $answer ) {
|
|
$query .= $sep . "(";
|
|
$query .= $id . ",";
|
|
$query .= $question . ",";
|
|
if( is_array($answer) )
|
|
$query .= "'" . specialchars(implode(",", $answer)) . "',";
|
|
else
|
|
$query .= "'" . specialchars($answer) . "',";
|
|
|
|
$query .= "'" . $referencenr . "'";
|
|
$query .= ")";
|
|
//$sep = ",";
|
|
|
|
}
|
|
db_store_data($query);
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
//return FALSE;
|
|
}
|
|
|
|
function db_get_enquete_entry_id($enquete, $user, $project,$choice=0){
|
|
$result = null;
|
|
|
|
if($choice == 0){
|
|
$query = "SELECT * FROM enquete_entry WHERE enquete='" . $enquete . "' AND gebruiker='". $user ."' AND project='" . $project . "'";
|
|
}elseif($choice == 1){
|
|
$query = "SELECT * FROM enquete_entry WHERE enquete='" . $enquete . "' AND project='" . $project . "'";
|
|
}elseif($choice == 2){
|
|
$query = "SELECT * FROM enquete_entry WHERE gebruiker='" . $user . "' AND project='" . $project . "'";
|
|
}
|
|
$result = db_fetch_data($query);
|
|
|
|
return $result;
|
|
}
|
|
|
|
|
|
function db_get_question($entry,$choice=0,$question="", $answer=""){
|
|
$result = null;
|
|
|
|
if($choice == 0){
|
|
$query = "SELECT * FROM enquete_data WHERE enquete_entry='" . $entry . "' ORDER BY vraag DESC LIMIT 1";
|
|
}
|
|
elseif($choice == 1){
|
|
$query = "SELECT * FROM enquete_data WHERE enquete_entry='" . $entry . "' ORDER BY volgnummer,vraag";
|
|
}elseif($choice == 2){
|
|
$query = "SELECT * FROM enquete_data WHERE enquete_entry='" . $entry . "' AND vraag='" . $question . "' AND antwoord='" . $answer . "'";
|
|
}elseif($choice == 3){
|
|
$query = "SELECT * FROM enquete_data WHERE (enquete_entry='" . $entry . "' AND vraag='" . $question[0] . "' AND antwoord='" . $answer[0] . "') OR (enquete_entry='" . $entry . "' AND vraag='" . $question[1] . "' AND antwoord='" . $answer[1] . "')";
|
|
}elseif($choice == 4){
|
|
$query = "SELECT * FROM enquete_data WHERE enquete_entry='" . $entry . "' AND vraag='" . $question . "'";
|
|
}
|
|
|
|
|
|
$result = db_fetch_data($query);
|
|
|
|
return $result;
|
|
|
|
}
|
|
|
|
|
|
function db_store_flag($enquete_entry){
|
|
$result = 1;
|
|
$query = "UPDATE enquete_data set opgeslagen='yes' WHERE enquete_entry='" . $enquete_entry . "'";
|
|
|
|
if(!db_store_data($query)){
|
|
$result = 0;
|
|
}
|
|
return $result;
|
|
|
|
}
|
|
|
|
/**
|
|
* Update a questionaire
|
|
*
|
|
* TO DO
|
|
*/
|
|
function db_update_questionaire()
|
|
{
|
|
return FALSE;
|
|
}
|
|
|
|
/**
|
|
* Read the answers for a questionaire by id
|
|
*
|
|
* Inputs:
|
|
* - dbid Database ID, for
|
|
* - what The questionaire (what == 0), project ID (what == 1)
|
|
* or a single entry (what = 2); see QUESTIONAIRE_xxx
|
|
* defines below
|
|
*
|
|
* Returns: Array with questionaire answers ('datum', 'gebruiker', 'project'
|
|
* and array with "vraag"/"antwoord" pairs)
|
|
*/
|
|
define('QUESTIONAIRE_ALL', 0);
|
|
define('QUESTIONAIRE_PROJECT', 1);
|
|
define('QUESTIONAIRE_SINGLE', 2);
|
|
function db_fetch_questionaire_answers($dbid, $what, $type = NULL)
|
|
{
|
|
$query = "SELECT enquete_entry.id,enquete_entry.gebruiker,enquete_entry.enquete,enquete_entry.project,enquete_entry.datum,enquete_entry.longitude,enquete_entry.latitude ";
|
|
$query .= "FROM enquete_entry ";
|
|
|
|
// Use type
|
|
if (!is_null($type)) {
|
|
$query .= ",enquete ";
|
|
}
|
|
|
|
if( $what == QUESTIONAIRE_ALL )
|
|
$query .= "WHERE enquete=" . $dbid;
|
|
else if( $what == QUESTIONAIRE_PROJECT ) {
|
|
$query .= "WHERE project=" . $dbid . " ";
|
|
|
|
// Use type
|
|
if (!is_null($type)) {
|
|
$query .= "AND enquete.id=enquete_entry.enquete AND enquete.type='" . $type . "' ";
|
|
}
|
|
|
|
$query .= "ORDER BY enquete_entry.id DESC";
|
|
}
|
|
else if( $what == QUESTIONAIRE_SINGLE )
|
|
$query .= "WHERE id=" . $dbid;
|
|
// else: everything by default; this is not the intended interface :-)
|
|
if( !($result = db_fetch_data($query)) ) return FALSE;
|
|
for( $i = 0; $i < count($result); $i++ ) {
|
|
$query = "SELECT enquete_entry, vraag,antwoord ";
|
|
$query .= "FROM enquete_data ";
|
|
$query .= "WHERE enquete_entry=" . $result[$i]['id'];
|
|
$result[$i]['antwoorden'] = db_fetch_data($query);
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
|
|
|
|
function db_fetch_questionaire_answer($enquete_entry, $question, $refnr){
|
|
$result = null;
|
|
$query = "SELECT * FROM enquete_data WHERE vraag='" . $question . "' AND enquete_entry='" . $enquete_entry. "' AND volgnummer='" . $refnr . "';";
|
|
|
|
$result = db_fetch_data($query);
|
|
|
|
return $result;
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/**
|
|
* Store the answers for a questionaire
|
|
*
|
|
* Inputs:
|
|
* - user_id User id
|
|
* - project_id Project id
|
|
* - dbid Database id for the questionaire
|
|
* - answers Array with an array of answers for each question
|
|
*
|
|
* Returns: Success (database id) or failure (boolean FALSE)
|
|
*/
|
|
function db_store_questionaire_answers($user_id, $project_id, $dbid, $answers , $latitude, $longitude)
|
|
{
|
|
// store user info etc
|
|
$query = "INSERT INTO enquete_entry (enquete,gebruiker,project,datum,latitude,longitude) VALUES (";
|
|
$query .= $dbid . ",";
|
|
$query .= $user_id . ",";
|
|
$query .= ($project_id ? $project_id : "NULL") . ",";
|
|
$query .= "NOW() ,";
|
|
$query .= (!is_numeric($latitude)) ? "NULL," : "'" . ($latitude) . "',";
|
|
$query .= (!is_numeric($longitude)) ? "NULL)" : "'" . ($longitude) . "')";
|
|
|
|
if( !db_store_data($query) ) return FALSE;
|
|
$id = db_fetch_last_id();
|
|
|
|
$query = "INSERT INTO enquete_data (enquete_entry,vraag,antwoord) VALUES ";
|
|
$sep = "";
|
|
foreach( $answers as $question => $answer ) {
|
|
$query .= $sep . "(";
|
|
$query .= $id . ",";
|
|
$query .= $question . ",";
|
|
if( is_array($answer) )
|
|
$query .= "'" . specialchars(implode(",", $answer)) . "'";
|
|
else
|
|
$query .= "'" . specialchars($answer) . "'";
|
|
$query .= ")";
|
|
$sep = ",";
|
|
}
|
|
db_store_data($query);
|
|
|
|
if( $project_id ) {
|
|
require_once("include/report_evaluation.php");
|
|
|
|
$file['filename'] = _("MTinfo") . " " . strtolower(_("Project")) . " " . strtolower(_("evaluation")) . "_" . str_replace("-", "", $date) . ".pdf";
|
|
$file['mimetype'] = "application/pdf";
|
|
$file['document'] = report_evaluation($id, "S");
|
|
db_project_send_mail_for_file_upload($project_id, 'evaluatie', $file);
|
|
}
|
|
|
|
return $id;
|
|
}
|
|
|
|
|
|
/**
|
|
* Fetch project questionaire (feedback templates)
|
|
*
|
|
* Inputs:
|
|
* - project: Project id
|
|
*
|
|
* Return: array containing selected questionaires
|
|
*/
|
|
function db_fetch_project_questionaire($project) {
|
|
// Fetch questionaires
|
|
$questionaires = db_fetch_data("SELECT * FROM enquete_project where project='" . $project . "'");
|
|
|
|
return $questionaires;
|
|
}
|
|
|
|
|
|
/**
|
|
* Fetch enquete (enquete info)
|
|
*
|
|
* Inputs:
|
|
* - id: enquete id
|
|
*
|
|
* Return: array containing selected enquete info
|
|
*/
|
|
function db_fetch_enqeute($id, $type = NULL) {
|
|
// Initial values
|
|
$query = "SELECT * FROM enquete where id='" . $id . "'";
|
|
|
|
// Use type
|
|
if (!is_null($type)) {
|
|
$query .= " AND `type`='" . $type . "'";
|
|
}
|
|
|
|
// Fetch questionaires
|
|
$enquete = db_fetch_data($query);
|
|
|
|
return $enquete;
|
|
}
|
|
|
|
|
|
/**
|
|
* Fetch dependencies from questionaire
|
|
*
|
|
* Inputs:
|
|
* - question: Question id
|
|
* - $choice: Choice (0/1)
|
|
*
|
|
* Return: array containing selected dependencies
|
|
*/
|
|
function db_fetch_dependency_questionaire($question, $choice=0) {
|
|
|
|
// Fetch dependencies
|
|
if($choice == 0){
|
|
// Get question with dependeny
|
|
$dependencies= db_fetch_data("SELECT * FROM enquete_dependency WHERE vraag='" . $question . "'");
|
|
}else if($choice == 1){
|
|
// Get question where other question depends on
|
|
$dependencies= db_fetch_data("SELECT * FROM enquete_dependency WHERE dependency='" . $question . "'");
|
|
}
|
|
|
|
return $dependencies;
|
|
}
|
|
|
|
/**
|
|
* db_fetch_enquete_translations
|
|
*
|
|
* Inputs:
|
|
* - question: Question id
|
|
* - lang: language
|
|
*
|
|
* Return: array containing selected translation
|
|
*/
|
|
function db_fetch_enquete_translations($question, $lang='nl',$enquete,$choice=0){
|
|
$result = 0;
|
|
|
|
if ($question == NULL){
|
|
$result = db_fetch_data("SELECT * FROM enquete_i18n WHERE vraag is NULL and i18n='" . $lang . "' and enquete='" . $enquete . "' ");
|
|
}else{
|
|
|
|
$query = "SELECT * FROM enquete_i18n WHERE vraag = '" . $question . "' and i18n='" . $lang . "'";
|
|
if($choice == 1){
|
|
$query .=" AND `key` is NULL";
|
|
}
|
|
|
|
$result = db_fetch_data($query);
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Store project questionaire (feedback templates)
|
|
*
|
|
* Inputs:
|
|
* - project: Project id
|
|
* - questionaire: Array containing selected questionaires
|
|
*
|
|
* Return: 1 (OK)/ 0(Error)
|
|
*/
|
|
function db_store_project_questionaire($project, $questionaires) {
|
|
// Initial return value
|
|
$result = 0;
|
|
|
|
// Start transaction
|
|
db_start_transaction();
|
|
|
|
// Delete older feedback templates
|
|
db_store_data("DELETE FROM enquete_project WHERE project='" . $project . "'");
|
|
|
|
if (is_array($questionaires)) {
|
|
foreach($questionaires as $questionaire) {
|
|
db_store_data("INSERT INTO enquete_project(project, enquete,level) VALUES('" . $project . "','" . $questionaire['id'] . "','" . $questionaire['level'] . "')");
|
|
}
|
|
}
|
|
|
|
// Commit transaction
|
|
if (db_commit_transaction()) {
|
|
$result = 1;
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
function db_get_questions_by_type($enquete, $type){
|
|
$result = null;
|
|
$result = db_fetch_data("SELECT * FROM enquete_vraag WHERE enquete='" . $enquete . "' AND type='" . $type . "'");
|
|
|
|
return $result;
|
|
|
|
}
|
|
|
|
function check_buttons($enquete){
|
|
$result = null;
|
|
|
|
$result = db_fetch_data("SELECT * FROM enquete_vraag WHERE enquete='" . $enquete . "' AND type LIKE 'button%'");
|
|
|
|
return $result;
|
|
}
|
|
|
|
function db_store_check_in_out($info){
|
|
|
|
$already_stored = true;
|
|
|
|
$return = array();
|
|
$return['return'] = true;
|
|
|
|
// Store check in data
|
|
if(is_array($info)){
|
|
// Check if user already checked in
|
|
$check = user_check_inout_info($info['project_id'], $info['name']);
|
|
|
|
if(isset($check) && !empty($check)){
|
|
if($check['actie'] == 'incheck'){
|
|
$info['actie'] = 'uitcheck';
|
|
$return['return']= false;
|
|
$return['t_in'] = $check['t'];
|
|
$return['t_out'] = $info['t'];
|
|
}else{
|
|
$already_stored = true;
|
|
//$info['actie'] = 'uitcheck';
|
|
}
|
|
}
|
|
|
|
if($already_stored){
|
|
$query = "INSERT INTO project_incheck_uitcheck (project, gebruiker, naam, mobielnr, t, latitude, longitude, actie) values (";
|
|
|
|
$query .= ($info['project_id'] ? $info['project_id'] : "NULL") . ",";
|
|
$query .= ($info['user_id'] ? $info['user_id'] : "NULL") . ",";
|
|
$query .= ($info['name'] ? "'" . $info['name'] ."'" : "NULL") . ",";
|
|
$query .= ($info['mobile_nr'] ? "'" . $info['mobile_nr'] ."'" : "NULL") . ",";
|
|
$query .= ($info['t'] ? $info['t'] : "NULL") . ",";
|
|
$query .= ($info['lat'] ? $info['lat'] : "NULL") . ",";
|
|
$query .= ($info['long'] ? $info['long'] : "NULL") . ",";
|
|
$query .= ($info['actie'] ? "'" . $info['actie'] ."'" : "NULL") . ")";
|
|
|
|
if(!db_store_data($query)){
|
|
return -1;
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
return $return;
|
|
}
|
|
|
|
function db_store_check_in_out_2($info){
|
|
|
|
// Store check in data
|
|
if(is_array($info)){
|
|
|
|
$query = "INSERT INTO project_incheck_uitcheck (project, gebruiker, naam, mobielnr, t, latitude, longitude, actie,lwb) values (";
|
|
|
|
$query .= ($info['project_id'] ? $info['project_id'] : "NULL") . ",";
|
|
$query .= ($info['user_id'] ? $info['user_id'] : "NULL") . ",";
|
|
$query .= ($info['name'] ? "'" . $info['name'] ."'" : "NULL") . ",";
|
|
$query .= ($info['mobile_nr'] ? "'" . $info['mobile_nr'] ."'" : "NULL") . ",";
|
|
$query .= ($info['t'] ? $info['t'] : "NULL") . ",";
|
|
$query .= ($info['lat'] ? $info['lat'] : "NULL") . ",";
|
|
$query .= ($info['long'] ? $info['long'] : "NULL") . ",";
|
|
$query .= ($info['actie'] ? "'" . $info['actie'] ."'" : "NULL") . ",";
|
|
$query .= ($info['lwb'] ? "'" . $info['lwb'] ."'" : "NULL") . ")";
|
|
|
|
if(!db_store_data($query)){
|
|
return -1;
|
|
}
|
|
}
|
|
return 1;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
function user_check_inout_info($project_id,$name,$choice = 0,$mobile=""){
|
|
|
|
|
|
$result = null;
|
|
if(!$choice){
|
|
$result = db_fetch_data("SELECT * from project_incheck_uitcheck where project='" . $project_id . "' and naam='". $name ."' ORDER by t DESC;");
|
|
}else if($choice == 1){
|
|
$result = db_fetch_data("SELECT * from project_incheck_uitcheck where project='" . $project_id . "' and gebruiker='". $name ."' ORDER by t DESC LIMIT 1;");
|
|
return($result[0]);
|
|
}else if($choice == 2){
|
|
$result = db_fetch_data("SELECT * from project_incheck_uitcheck where project='" . $project_id . "' and naam='". $name ."' and mobielnr='" . $mobile . "' ORDER by t DESC LIMIT 1;");
|
|
return($result[0]);
|
|
}else{
|
|
$result = db_fetch_data("SELECT * from project_incheck_uitcheck where project='" . $project_id . "' and naam='". $name ."' ORDER by t DESC LIMIT 2;");
|
|
return($result);
|
|
}
|
|
|
|
|
|
return($result[0]);
|
|
|
|
}
|
|
|
|
function get_higher_project_users($user_level, $user_project , $current_user_level){
|
|
|
|
if($current_user_level == 8){
|
|
$result = db_fetch_data("SELECT * FROM project_gebruiker WHERE project='" . $user_project . "' and level < '" . $user_level . "' and rol='normaal';");
|
|
}
|
|
|
|
if($current_user_level == 9){
|
|
$result = db_fetch_data("SELECT * FROM project_gebruiker WHERE project='" . $user_project . "' and level <= '" . $user_level . "' and rol='normaal';");
|
|
}
|
|
|
|
|
|
return($result);
|
|
}
|
|
|
|
|
|
function delete_user_check_inout($project, $user, $t){
|
|
|
|
$result = db_store_data("DELETE from project_incheck_uitcheck where project='" . $project . "' and gebruiker='" . $user . "' and t='" . $t . "';");
|
|
return $result;
|
|
|
|
}
|
|
|
|
|
|
|
|
?>
|