1<?php 2// (c) Copyright by authors of the Tiki Wiki CMS Groupware Project 3// 4// All Rights Reserved. See copyright.txt for details and a complete list of authors. 5// Licensed under the GNU LESSER GENERAL PUBLIC LICENSE. See license.txt for details. 6// $Id$ 7 8//this script may only be included - so its better to die if called directly. 9if (strpos($_SERVER["SCRIPT_NAME"], basename(__FILE__)) !== false) { 10 header("location: index.php"); 11 exit; 12} 13 14/** 15 * 16 */ 17class SurveyLib extends TikiLib 18{ 19 private $surveysTable; 20 private $questionsTable; 21 private $optionsTable; 22 23 function __construct() 24 { 25 parent::__construct(); 26 27 $this->surveysTable = $this->table('tiki_surveys'); 28 $this->questionsTable = $this->table('tiki_survey_questions'); 29 $this->optionsTable = $this->table('tiki_survey_question_options'); 30 $this->votesTable = $this->table('tiki_user_votings'); 31 } 32 33 /** 34 * @param $offset 35 * @param $maxRecords 36 * @param $sort_mode 37 * @param $find 38 * @return array 39 */ 40 public function list_surveys($offset, $maxRecords, $sort_mode, $find, $perm = 'take_survey') 41 { 42 $conditions = []; 43 if ($find) { 44 $conditions['search'] = $this->surveysTable->expr('(`name` like ? or `description` like ?)', ["%$find%", "%$find%"]); 45 } 46 $surveys = $this->surveysTable->fetchAll( 47 $this->surveysTable->all(), 48 $conditions, 49 $maxRecords, 50 $offset, 51 $this->surveysTable->sortMode($sort_mode) 52 ); 53 $surveys = Perms::filter(['type' => 'survey'], 'object', $surveys, ['object' => 'surveyId'], $perm); 54 55 foreach ($surveys as & $survey) { 56 $survey['questions'] = $this->questionsTable->fetchOne( 57 $this->questionsTable->count(), 58 ['surveyId' => $survey['surveyId']] 59 ); 60 } 61 62 $retval["data"] = $surveys; 63 $retval["cant"] = count($surveys); 64 return $retval; 65 } 66 67 /** 68 * @param $surveyId 69 */ 70 public function add_survey_hit($surveyId) 71 { 72 global $prefs, $user; 73 74 if (StatsLib::is_stats_hit()) { 75 $this->surveysTable->update( 76 [ 77 'taken' => $this->surveysTable->increment(1), 78 'lastTaken' => $this->now 79 ], 80 ['surveyId' => $surveyId] 81 ); 82 } 83 } 84 85 /** 86 * @param $questionId 87 * @param $value 88 * @return int 89 */ 90 public function register_survey_text_option_vote($questionId, $value) 91 { 92 $conditions = [ 93 'questionId' => $questionId, 94 'qoption' => $value, 95 ]; 96 97 $result = $this->optionsTable->fetchColumn('optionId', $conditions); 98 if (! empty($result)) { 99 $optionId = $result[0]; 100 $this->optionsTable->update( 101 [ 102 'votes' => $this->optionsTable->increment(1), 103 ], 104 $conditions 105 ); 106 } else { 107 $optionId = $this->optionsTable->insert( 108 [ 109 'questionId' => $questionId, 110 'qoption' => $value, 111 'votes' => 1, 112 ] 113 ); 114 } 115 return $optionId; 116 } 117 118 /** 119 * @param $questionId 120 * @param $rate 121 */ 122 public function register_survey_rate_vote($questionId, $rate) 123 { 124 $conditions = ['questionId' => $questionId]; 125 126 $this->questionsTable->update( 127 [ 128 'votes' => $this->questionsTable->increment(1), 129 'value' => $this->questionsTable->increment($rate), 130 ], 131 $conditions 132 ); 133 $this->questionsTable->update( 134 [ 135 'average' => $this->questionsTable->expr('`value`/`votes`'), 136 ], 137 $conditions 138 ); 139 } 140 141 /** 142 * @param $questionId 143 * @param $optionId 144 */ 145 public function register_survey_option_vote($questionId, $optionId) 146 { 147 $this->optionsTable->update( 148 [ 149 'votes' => $this->optionsTable->increment(1), 150 ], 151 [ 152 'questionId' => $questionId, 153 'optionId' => $optionId, 154 ] 155 ); 156 } 157 158 /** 159 * @param $surveyId 160 */ 161 public function clear_survey_stats($surveyId) 162 { 163 $conditions = ['surveyId' => $surveyId]; 164 165 $this->surveysTable->update( 166 ['taken' => 0], 167 $conditions 168 ); 169 170 $questions = $this->questionsTable->fetchAll( 171 $this->questionsTable->all(), 172 $conditions 173 ); 174 175 176 // Remove all the options for each question for text, wiki and fgal types 177 foreach ($questions as $question) { 178 $qconditions = ['questionId' => (int) $question['questionId']]; 179 180 if (in_array($question['type'], ['t', 'g', 'x'])) { 181 // same table used for options and responses (nice) 182 $this->optionsTable->deleteMultiple($qconditions); 183 } else { 184 $this->optionsTable->updateMultiple(['votes' => 0], $qconditions); 185 } 186 } 187 $this->questionsTable->updateMultiple( 188 [ 189 'average' => 0, 190 'value' => 0, 191 'votes' => 0 192 ], 193 $conditions 194 ); 195 196 $this->get()->table('tiki_user_votings')->deleteMultiple( 197 ['id' => 'survey' . $surveyId] 198 ); 199 } 200 201 /** 202 * @param $surveyId 203 * @param $name 204 * @param $description 205 * @param $status 206 * @return mixed 207 */ 208 public function replace_survey($surveyId, $name, $description, $status) 209 { 210 $newId = $this->surveysTable->insertOrUpdate( 211 [ 212 'name' => $name, 213 'description' => $description, 214 'status' => $status, 215 ], 216 ['surveyId' => empty($surveyId) ? 0 : $surveyId] 217 ); 218 return $newId ? $newId : $surveyId; 219 } 220 221 /** 222 * @param $questionId 223 * @param $question 224 * @param $type 225 * @param $surveyId 226 * @param $position 227 * @param $options 228 * @param string $mandatory 229 * @param int $min_answers 230 * @param int $max_answers 231 * @return mixed 232 */ 233 public function replace_survey_question( 234 $questionId, 235 $question, 236 $type, 237 $surveyId, 238 $position, 239 $options, 240 $mandatory = 'n', 241 $min_answers = 0, 242 $max_answers = 0 243 ) { 244 245 if ($mandatory != 'y') { 246 $mandatory = 'n'; 247 } 248 $min_answers = (int) $min_answers; 249 $max_answers = (int) $max_answers; 250 251 $newId = $this->questionsTable->insertOrUpdate( 252 [ 253 'type' => $type, 254 'position' => $position, 255 'question' => $question, 256 'options' => $options, 257 'mandatory' => $mandatory, 258 'min_answers' => $min_answers, 259 'max_answers' => $max_answers, 260 ], 261 [ 262 'questionId' => $questionId, 263 'surveyId' => $surveyId, 264 ] 265 ); 266 267 $questionId = $newId ? $newId : $questionId; 268 269 $userOptions = $this->parse_options($options); 270 271 $questionOptions = $this->optionsTable->fetchAll( 272 ['optionId','qoption'], 273 ['questionId' => $questionId] 274 ); 275 276 // Reset question options only if not a 'text', 'wiki' or 'filegal choice', because their options are dynamically generated 277 if (! in_array($type, ['t', 'g', 'x'])) { 278 foreach ($questionOptions as $qoption) { 279 if (! in_array($qoption['qoption'], $userOptions)) { 280 $this->optionsTable->delete([ 281 'questionId' => $questionId, 282 'optionId' => $qoption['optionId'], 283 ]); 284 } else { 285 $idx = array_search($qoption["qoption"], $userOptions); 286 unset($userOptions[$idx]); 287 } 288 } 289 foreach ($userOptions as $option) { 290 $this->optionsTable->insert([ 291 'questionId' => $questionId, 292 'qoption' => $option, 293 'votes' => 0, 294 ]); 295 } 296 } 297 298 return $questionId; 299 } 300 301 /** 302 * @param $surveyId 303 * @return array 304 */ 305 public function get_survey($surveyId) 306 { 307 return $this->surveysTable->fetchRow( 308 $this->surveysTable->all(), 309 ['surveyId' => $surveyId] 310 ); 311 } 312 313 /** 314 * @param $questionId 315 * @return bool 316 */ 317 public function get_survey_question($questionId) 318 { 319 $question = $this->questionsTable->fetchRow( 320 $this->questionsTable->all(), 321 ['questionId' => $questionId] 322 ); 323 324 $options = $this->optionsTable->fetchRow( 325 $this->optionsTable->all(), 326 ['questionId' => $questionId] 327 ); 328 329 $qoptions = []; 330 $votes = 0; 331 332 foreach ($options as $option) { 333 $qoptions[] = $option; 334 $votes += $option["votes"]; 335 } 336 337 $question["ovotes"] = $votes; 338 $question["qoptions"] = $qoptions; 339 return $question; 340 } 341 342 /** 343 * @param $surveyId 344 * @param $offset 345 * @param $maxRecords 346 * @param $sort_mode 347 * @param $find 348 * @return array 349 */ 350 public function list_survey_questions($surveyId, $offset, $maxRecords, $sort_mode, $find, $u = '') 351 { 352 $filegallib = TikiLib::lib('filegal'); 353 354 $conditions = ['surveyId' => $surveyId]; 355 if ($find) { 356 $conditions['question'] = $this->questionsTable->like('%' . $find . '%'); 357 } 358 359 $questions = $this->questionsTable->fetchAll( 360 $this->questionsTable->all(), 361 $conditions, 362 -1, 363 -1, 364 $this->questionsTable->sortMode($sort_mode) 365 ); 366 $ret = []; 367 368 if ($u) { 369 $userVotedOptions = $this->get_user_voted_options($surveyId, $u); 370 } else { 371 $userVotedOptions = []; 372 } 373 374 foreach ($questions as & $question) { 375 // save user options 376 $userOptions = $this->parse_options($question["options"]); 377 378 if (! empty($question['options'])) { 379 if (in_array($question['type'], ['g', 'x', 'h'])) { 380 $question['explode'] = $userOptions; 381 } elseif (in_array($question['type'], ['r', 's'])) { 382 $question['explode'] = array_fill(1, $question['options'], ' '); 383 } elseif (in_array($question['type'], ['t'])) { 384 $question['cols'] = $question['options']; 385 } 386 } 387 388 $questionOptions = $this->optionsTable->fetchAll( 389 $this->optionsTable->all(), 390 ['questionId' => $question["questionId"]], 391 -1, 392 -1, 393 $question['type'] === 'g' ? 394 ['votes' => 'desc'] : 395 ['optionId' => 'asc'] 396 ); 397 $question["options"] = count($questionOptions); 398 399 if ($question["type"] == 'r') { 400 $maxwidth = 5; 401 } else { 402 $maxwidth = 10; 403 } 404 $question["width"] = $question["average"] * 200 / $maxwidth; 405 $ret2 = []; 406 $votes = 0; 407 $total_votes = 0; 408 foreach ($questionOptions as & $questionOption) { 409 $total_votes += (int) $questionOption['votes']; 410 } 411 412 $ids = []; 413 TikiLib::lib('smarty')->loadPlugin('smarty_modifier_escape'); 414 415 foreach ($questionOptions as & $questionOption) { 416 if (in_array($questionOption['optionId'], $userVotedOptions)) { 417 $questionOption['uservoted'] = true; 418 } else { 419 $questionOption['uservoted'] = false; 420 } 421 422 if ($total_votes) { 423 $average = ($questionOption["votes"] / $total_votes) * 100; 424 } else { 425 $average = 0; 426 } 427 428 $votes += $questionOption["votes"]; 429 $questionOption["average"] = $average; 430 $questionOption["width"] = $average * 2; 431 $questionOption['qoptionraw'] = $questionOption['qoption']; 432 if ($question['type'] == 'x') { 433 $questionOption['qoption'] = TikiLib::lib('parser')->parse_data($questionOption['qoption']); 434 } else { 435 $questionOption['qoption'] = smarty_modifier_escape($questionOption['qoption']); 436 } 437 438 // when question with multiple options 439 // we MUST respect the user defined order 440 if (in_array($question['type'], ['m', 'c'])) { 441 $ret2[array_search($questionOption['qoptionraw'], $userOptions)] = $questionOption; 442 } else { 443 $ret2[] = $questionOption; 444 } 445 446 $ids[$questionOption['qoption']] = true; 447 } 448 449 // For a multiple choice from a file gallery, show all files in the stats results, even if there was no vote for those files 450 if ($question['type'] == 'g' && $question['options'] > 0) { 451 $files = $filegallib->get_files(0, -1, '', '', $userOptions[0], false, false, false, true, false, false, false, false, '', false, false); 452 foreach ($files['data'] as $f) { 453 if (! isset($ids[$f['id']])) { 454 $ret2[] = [ 455 'qoption' => $f['id'], 456 'votes' => 0, 457 'average' => 0, 458 'width' => 0 459 ]; 460 } 461 } 462 unset($files); 463 } 464 465 $question["qoptions"] = $ret2; 466 $question["ovotes"] = $votes; 467 $ret[] = $question; 468 } 469 470 $retval = []; 471 $retval["data"] = $ret; 472 $retval["cant"] = count($questions); 473 return $retval; 474 } 475 476 /** 477 * @param $questionId 478 * @return bool 479 */ 480 public function remove_survey_question($questionId) 481 { 482 $conditions = ['questionId' => $questionId]; 483 484 $this->optionsTable->deleteMultiple($conditions); 485 $this->questionsTable->delete($conditions); 486 return true; 487 } 488 489 /** 490 * @param $surveyId 491 * @return bool 492 */ 493 public function remove_survey($surveyId) 494 { 495 496 $conditions = ['surveyId' => $surveyId]; 497 498 $this->surveysTable->delete($conditions); 499 500 $questions = $this->questionsTable->fetchColumn('questionId', $conditions); 501 502 foreach ($questions as $question) { 503 $this->optionsTable->deleteMultiple(['questionId' => (int) $question['questionId']]); 504 } 505 $this->questionsTable->deleteMultiple($conditions); 506 507 $this->remove_object('survey', $surveyId); 508 509 $this->get()->table('tiki_user_votings')->deleteMultiple( 510 ['id' => 'survey' . $surveyId] 511 ); 512 return true; 513 } 514 515 // Check mandatory fields and min/max number of answers and register vote/answers if ok 516 /** 517 * @param $surveyId 518 * @param $questions 519 * @param $answers 520 * @param null $error_msg 521 * @return bool 522 */ 523 public function register_answers($surveyId, $questions, $answers, &$error_msg = null) 524 { 525 global $user; 526 527 if ($surveyId <= 0 || empty($questions)) { 528 return false; 529 } 530 531 $errors = []; 532 foreach ($questions as $question) { 533 $key = 'question_' . $question['questionId']; 534 $nb_answers = empty($answers[$key]) ? 0 : 1; 535 $multiple_choice = in_array($question['type'], ['m', 'g']); 536 if ($multiple_choice) { 537 $nb_answers = is_array($answers[$key]) ? count($answers[$key]) : 0; 538 if ($question['max_answers'] < 1) { 539 $question['max_answers'] = $nb_answers; 540 } 541 } 542 $q = empty($question['question']) ? '.' : ' "<b>' . $question['question'] . '</b>".'; 543 if ($multiple_choice) { 544 if ($question['mandatory'] == 'y') { 545 $question['min_answers'] = max(1, $question['min_answers']); 546 } 547 548 if ($question['min_answers'] == $question['max_answers'] && $nb_answers != $question['min_answers']) { 549 $errors[] = sprintf(tra('%d choice(s) must be made for the question'), $question['min_answers']) . $q; 550 } elseif ($nb_answers < $question['min_answers']) { 551 $errors[] = sprintf(tra('At least %d choice(s) must be made for the question'), $question['min_answers']) . $q; 552 } elseif ($question['max_answers'] > 0 && $nb_answers > $question['max_answers']) { 553 $errors[] = sprintf(tra('Fewer than %d choice(s) must be made for the question'), $question['max_answers']) . $q; 554 } 555 } elseif ($question['mandatory'] == 'y' && $nb_answers == 0 && $question["type"] !== 'h') { 556 $errors[] = sprintf(tra('At least %d choice(s) must be made for the question'), 1) . $q; 557 } 558 } 559 560 if (count($errors) > 0) { 561 if ($error_msg !== null) { 562 $error_msg = $errors; 563 } 564 return false; 565 } else { 566 // no errors, so record answers 567 // 568 // format for answers recorded in tiki_user_votings is "surveyX.YY" 569 // where X is surveyId and YY is the questionId 570 // and optionId is the id in tiki_survey_question_options 571 572 $this->register_user_vote($user, 'survey' . $surveyId, 0); 573 574 foreach ($questions as $question) { 575 $questionId = $question["questionId"]; 576 577 if (isset($answers["question_" . $questionId])) { 578 if ($question["type"] == 'm') { 579 // If we have a multiple question 580 $ids = array_keys($answers["question_" . $questionId]); 581 582 // Now for each of the options we increase the number of votes 583 foreach ($ids as $optionId) { 584 $this->register_survey_option_vote($questionId, $optionId); 585 $this->register_user_vote($user, 'survey' . $surveyId . '.' . $questionId, $optionId); 586 } 587 } elseif ($question["type"] == 'g') { 588 // If we have a multiple choice of file from a gallery 589 $ids = $answers["question_" . $questionId]; 590 591 // Now for each of the options we increase the number of votes 592 foreach ($ids as $optionId) { 593 $this->register_survey_text_option_vote($questionId, $optionId); 594 $this->register_user_vote($user, 'survey' . $surveyId . '.' . $questionId, $optionId); 595 } 596 } elseif ($question["type"] !== 'h') { 597 $value = $answers["question_" . $questionId]; 598 599 if ($question["type"] == 'r' || $question["type"] == 's') { 600 $this->register_survey_rate_vote($questionId, $value); 601 $this->register_user_vote($user, 'survey' . $surveyId . '.' . $questionId, $value); 602 } elseif ($question["type"] == 't' || $question["type"] == 'x') { 603 $optionId = $this->register_survey_text_option_vote($questionId, $value); 604 $this->register_user_vote($user, 'survey' . $surveyId . '.' . $questionId, $optionId); 605 } else { 606 $this->register_survey_option_vote($questionId, $value); 607 $this->register_user_vote($user, 'survey' . $surveyId . '.' . $questionId, $value); 608 } 609 } 610 } 611 } 612 } 613 614 return true; 615 } 616 617 public function reorderQuestions($surveyId, $questionIds) 618 { 619 $counter = 1; 620 foreach ($questionIds as $id) { 621 $this->questionsTable->update( 622 ['position' => $counter], 623 [ 624 'questionId' => $id, 625 'surveyId' => $surveyId, 626 ] 627 ); 628 $counter++; 629 } 630 } 631 632 /** 633 * @return array question types: initial => translated label 634 */ 635 public function get_types() 636 { 637 return [ 638 'c' => tra('One choice'), 639 'm' => tra('Multiple choices'), 640 'g' => tra('Thumbnails'), 641 't' => tra('Short text'), 642 'x' => tra('Wiki textarea'), 643 'r' => tra('Rate (1 to 5)'), 644 's' => tra('Rate (1 to 10)'), 645 'h' => tra('Heading'), 646 ]; 647 } 648 649 /** 650 * @param string $options comma-separated options string (use \, to include a comma) 651 * @return array 652 */ 653 private function parse_options($options) 654 { 655 if (! empty($options)) { 656 $comma = '~COMMA~'; 657 $options = str_replace('\,', $comma, $options); 658 $options = explode(',', $options); 659 foreach ($options as & $option) { 660 $option = trim(str_replace($comma, ',', $option)); 661 } 662 } else { 663 $options = []; 664 } 665 return $options; 666 } 667 668 private function get_user_voted_options($surveyId, $u) 669 { 670 $conditions['id'] = $this->votesTable->like('survey' . $surveyId . '%'); 671 $conditions['user'] = $u; 672 $result = $this->votesTable->fetchAll(['optionId'], $conditions); 673 foreach ($result as $r) { 674 $ret[] = $r['optionId']; 675 } 676 return $ret; 677 } 678 679 function list_users_that_voted($surveyId) 680 { 681 $conditions['id'] = 'survey' . $surveyId; 682 $conditions['optionId'] = 0; 683 $result = $this->votesTable->fetchAll(['user'], $conditions); 684 foreach ($result as $r) { 685 $ret[] = $r['user']; 686 } 687 return array_unique($ret); 688 } 689} 690 691$srvlib = new SurveyLib; 692