generate a csv file for the results of a survey
[platal.git] / modules / survey / survey.inc.php
1 <?php
2 /***************************************************************************
3 * Copyright (C) 2003-2007 Polytechnique.org *
4 * http://opensource.polytechnique.org/ *
5 * *
6 * This program is free software; you can redistribute it and/or modify *
7 * it under the terms of the GNU General Public License as published by *
8 * the Free Software Foundation; either version 2 of the License, or *
9 * (at your option) any later version. *
10 * *
11 * This program is distributed in the hope that it will be useful, *
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of *
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
14 * GNU General Public License for more details. *
15 * *
16 * You should have received a copy of the GNU General Public License *
17 * along with this program; if not, write to the Free Software *
18 * Foundation, Inc., *
19 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *
20 ***************************************************************************/
21
22 // {{{ class Survey : root of any survey, contains all questions
23 class Survey
24 {
25 // {{{ static properties and functions, regarding survey modes and question types
26 const MODE_ALL = 0;
27 const MODE_XANON = 1;
28 const MODE_XIDENT = 2;
29 private static $longModes = array(self::MODE_ALL => "sondage ouvert &#224; tout le monde, anonyme",
30 self::MODE_XANON => "sondage restreint aux polytechniciens, anonyme",
31 self::MODE_XIDENT => "sondage restreint aux polytechniciens, non anonyme");
32 private static $shortModes = array(self::MODE_ALL => "tout le monde, anonyme",
33 self::MODE_XANON => "polytechniciens, anonyme",
34 self::MODE_XIDENT => "polytechniciens, non anonyme");
35
36 public static function getModes($long = true) {
37 return ($long)? self::$longModes : self::$shortModes;
38 }
39
40 private static $types = array('text' => 'texte court',
41 'textarea' => 'texte long',
42 'num' => 'num&#233;rique',
43 'radio' => 'radio',
44 'checkbox' => 'checkbox',
45 'personal' => 'informations personnelles');
46
47 public static function getTypes()
48 {
49 return self::$types;
50 }
51
52 public static function isType($t)
53 {
54 return array_key_exists($t, self::$types);
55 }
56 // }}}
57
58 // {{{ properties, constructor and basic methods
59 private $id;
60 private $title;
61 private $description;
62 private $end;
63 private $mode;
64 private $promos;
65 private $valid;
66 private $questions;
67
68 public function __construct($args, $id = -1, $valid = false, $questions = null)
69 {
70 $this->update($args);
71 $this->id = $id;
72 $this->valid = $valid;
73 $this->questions = ($questions == null)? array() : $questions;
74 }
75
76 public function update($args)
77 {
78 $this->title = $args['title'];
79 $this->description = $args['description'];
80 if (preg_match('#^\d{2}/\d{2}/\d{4}$#', $args['end'])) {
81 $this->end = preg_replace('#^(\d{2})/(\d{2})/(\d{4})$#', '\3-\2-\1', $args['end']);
82 } else {
83 $this->end = (preg_match('#^\d{4}-\d{2}-\d{2}$#', $args['end']))? $args['end'] : '#';
84 }
85 $this->mode = $args['mode'];
86 if ($args['mode'] == 0) {
87 $args['promos'] = '';
88 }
89 $this->promos = ($args['promos'] == '' || preg_match('#^(\d{4}-?|(\d{4})?-\d{4})(,(\d{4}-?|(\d{4})?-\d{4}))*$#', $args['promos']))? $args['promos'] : '#';
90 }
91 // }}}
92
93 // {{{ functions to access general information
94 public function isMode($mode)
95 {
96 return ($this->mode == $mode);
97 }
98
99 public function checkPromo($promo)
100 {
101 if ($this->promos == '') {
102 return true;
103 }
104 $promos = explode('|', $this->promos);
105 foreach ($promos as $p) {
106 if ((preg_match('#^\d{4}$#', $p) && $p == $promo) ||
107 (preg_match('#^\d{4}-$#', $p) && intval(substr($p, 0, 4)) <= $promo) ||
108 (preg_match('#^-\d{4}$#', $p) && intval(substr($p, 1)) >= $promo) ||
109 (preg_match('#^\d{4}-\d{4}$#', $p) && intval(substr($p, 0, 4)) <= $promo && intval(substr($p, 5)) >= $promo)) {
110 return true;
111 }
112 }
113 return false;
114 }
115
116 public function isValid()
117 {
118 return $this->valid;
119 }
120
121 public function isEnded()
122 {
123 return (strtotime($this->end) - time() <= 0);
124 }
125
126 public function getTitle()
127 {
128 return $this->title;
129 }
130 // }}}
131
132 // {{{ function toArray() : converts a question (or the whole survey) to array, with results if the survey is ended
133 public function toArray($i = 'all')
134 {
135 if ($i != 'all' && $i != 'root') { // if a specific question is requested, then just returns this question converted to array
136 $i = intval($i);
137 if (array_key_exists($i, $this->questions)) {
138 return $this->questions[$i]->toArray();
139 } else {
140 return null;
141 }
142 } else { // else returns the root converted to array in any case
143 $a = array('title' => $this->title,
144 'description' => $this->description,
145 'end' => $this->end,
146 'mode' => $this->mode,
147 'promos' => $this->promos,
148 'valid' => $this->valid,
149 'type' => 'root');
150 if ($this->id != -1) {
151 $a['id'] = $this->id;
152 }
153 if ($this->isEnded()) { // if the survey is ended, then adds here the number of votes
154 $sql = 'SELECT COUNT(id)
155 FROM survey_votes
156 WHERE survey_id={?};';
157 $tot = XDB::query($sql, $this->id);
158 $a['votes'] = $tot->fetchOneCell();
159 }
160 if ($i == 'all' && count($this->questions) > 0) { // if the whole survey is requested, then returns all the questions converted to array
161 $qArr = array();
162 for ($k = 0; $k < count($this->questions); $k++) {
163 $q = $this->questions[$k]->toArray();
164 $q['id'] = $k;
165 if ($this->isEnded()) { // if the survey is ended, then adds here the results of this question
166 $sql = $this->questions[$k]->buildResultRequest();
167 if ($sql != '') {
168 $q['result'] = XDB::iterator($sql, $this->id, $k);
169 }
170 }
171 $qArr[$k] = $q;
172 }
173 $a['questions'] = $qArr;
174 }
175 return $a;
176 }
177 }
178 // }}}
179
180 // {{{ function toCSV() : builds a CSV file containing all the results of the survey
181 public function toCSV($sep = ',', $enc = '"', $asep='|')
182 {
183 $nbq = count($this->questions);
184 //require_once dirname(__FILE__) . '/../../classes/varstream.php';
185 VarStream::init();
186 global $csv_output;
187 $csv_output = '';
188 $csv = fopen('var://csv_output', 'w');
189 $line = ($this->isMode(self::MODE_XIDENT))? array('id', 'Nom', 'Prenom', 'Promo') : array('id');
190 for ($qid = 0; $qid < $nbq; $qid++) {
191 $line[] = $this->questions[$qid]->getCSVColumn(); // the fist line contains the questions
192 }
193 $users = array();
194 if ($this->isMode(self::MODE_XIDENT)) { // if the mode is non anonymous
195 $sql = 'SELECT v.id AS vid, a.nom, a.prenom, a.promo
196 FROM survey_votes AS v
197 INNER JOIN auth_user_md5 AS a
198 ON a.user_id=v.user_id
199 WHERE v.survey_id={?}
200 ORDER BY vid ASC;';
201 $res = XDB::iterator($sql, $this->id); // retrieves all users data
202 for ($u = $res->next(); $u != null; $u = $res->next()) {
203 $users[$u['vid']] = array('nom' => $u['nom'], 'prenom' => $u['prenom'], 'promo' => $u['promo']);
204 }
205 }
206 $sql = 'SELECT v.id AS vid, a.question_id AS qid, a.answer
207 FROM survey_votes AS v
208 LEFT JOIN survey_answers AS a
209 ON a.vote_id=v.id
210 WHERE v.survey_id={?}
211 ORDER BY vid ASC, qid ASC;';
212 $res = XDB::iterator($sql, $this->id); // retrieves all answers from database
213 $cur = $res->next();
214 $vid = -1;
215 $vid_ = 0;
216 $first = ($this->isMode(self::MODE_XIDENT))? 4 : 1;
217 while ($cur != null) {
218 if ($vid != $cur['vid']) { // if the vote id changes, then starts a new line
219 fputcsv($csv, $line, $sep, $enc); // stores the former line into $csv_output
220 $vid = $cur['vid'];
221 $line = array_fill(0, $first + $nbq, ''); // creates an array full of empty string
222 $line[0] = $vid_; // the first field is a 'clean' vote id (not the one stored in database)
223 if ($this->isMode(self::MODE_XIDENT)) { // if the mode is non anonymous
224 if (array_key_exists($vid, $users) && is_array($users[$vid])) { // and if the user data can be found
225 $line[1] = $users[$vid]['nom']; // adds the user data (in the first fields of the line)
226 $line[2] = $users[$vid]['prenom'];
227 $line[3] = $users[$vid]['promo'];
228 }
229 }
230 $vid_++;
231 }
232 $fid = $first + $cur['qid']; // computes the field id
233 if ($line[$fid] != '') { // if this field already contains something
234 $line[$fid] .= $asep; // then adds a separator before adding the new answer
235 }
236 $line[$fid] .= $cur['answer']; // adds the current answer to the correct field
237 $cur = $res->next(); // gets next answer
238 }
239 fputcsv($csv, $line, $sep, $enc); // stores the last line into $csv_output
240 return $csv_output;
241 }
242 // }}}
243
244 // {{{ function factory($type, $args) : builds a question according to the given type
245 public function factory($t, $args)
246 {
247 switch ($t) {
248 case 'text':
249 return new SurveyText($args);
250 case 'textarea':
251 return new SurveyTextarea($args);
252 case 'num':
253 return new SurveyNum($args);
254 case 'radio':
255 return new SurveyRadio($args);
256 case 'checkbox':
257 return new SurveyCheckbox($args);
258 case 'personal':
259 return new SurveyPersonal($args);
260 default:
261 return null;
262 }
263 }
264 // }}}
265
266 // {{{ questions manipulation functions
267 public function addQuestion($i, $c)
268 {
269 if ($this->valid || $i > count($this->questions)) {
270 return false;
271 } else {
272 array_splice($this->questions, $i, 0, array($c));
273 return true;
274 }
275 }
276
277 public function delQuestion($i)
278 {
279 if ($this->valid || !array_key_exists($i, $this->questions)) {
280 return false;
281 } else {
282 array_splice($this->questions, $i, 1);
283 return true;
284 }
285 }
286
287 public function editQuestion($i, $a)
288 {
289 if ($i == 'root') {
290 $this->update($a);
291 } else {
292 $i = intval($i);
293 if ($this->valid ||!array_key_exists($i, $this->questions)) {
294 return false;
295 } else {
296 $this->questions[$i]->update($a);
297 }
298 }
299 return true;
300 }
301 // }}}
302
303 // {{{ function checkSyntax() : checks syntax of the questions (currently the root only) before storing the survey in database
304 private static $errorMessages = array(
305 "dateformat" => "la date de fin de sondage est mal formatt&#233;e : elle doit respecter la syntaxe dd/mm/aaaa",
306 "datepassed" => "la date de fin de sondage est d&#233;j&#224; d&#233;pass&#233;e : vous devez pr&#233;ciser une date future",
307 "promoformat" => "les restrictions &#224; certaines promotions sont mal formatt&#233;es"
308 );
309
310 public function checkSyntax()
311 {
312 $rArr = array();
313 if (!preg_match('#^\d{4}-\d{2}-\d{2}$#', $this->end)) {
314 $rArr[] = array('question' => 'root', 'error' => self::$errorMessages["dateformat"]);
315 } else {
316 // checks that the end date given is not already passed
317 // (unless the survey has already been validated : an admin can have a validated survey expired)
318 if (!$this->valid && $this->isEnded()) {
319 $rArr[] = array('question' => 'root', 'error' => self::$errorMessages["datepassed"]);
320 }
321 }
322 if ($this->promos != '' && !preg_match('#^(\d{4}-?|(\d{4})?-\d{4})(,(\d{4}-?|(\d{4})?-\d{4}))*$#', $this->promos)) {
323 $rArr[] = array('question' => 'root', 'error' => self::$errorMessages["promoformat"]);
324 }
325 return (empty($rArr))? null : $rArr;
326 }
327 // }}}
328
329 // {{{ functions that manipulate surveys in database
330 // {{{ static function retrieveList() : gets the list of available survey (current, old and not validated surveys)
331 public static function retrieveList($type, $tpl = true)
332 {
333 switch ($type) {
334 case 'w':
335 case 'waiting' :
336 $where = 'valid=0';
337 break;
338 case 'c':
339 case 'current':
340 $where = 'valid=1 AND end > NOW()';
341 break;
342 case 'o':
343 case 'old':
344 $where = 'valid=1 AND end <= NOW()';
345 break;
346 default:
347 return null;
348 }
349 $sql = 'SELECT id, title, end, mode
350 FROM survey_surveys
351 WHERE '.$where.';';
352 if ($tpl) {
353 return XDB::iterator($sql);
354 } else {
355 return XDB::iterRow($sql);
356 }
357 }
358 // }}}
359
360 // {{{ static function retrieveSurvey() : gets a survey in database (and unserialize the survey object structure)
361 public static function retrieveSurvey($sid)
362 {
363 $sql = 'SELECT questions, title, description, end, mode, promos, valid
364 FROM survey_surveys
365 WHERE id={?}';
366 $res = XDB::query($sql, $sid);
367 $data = $res->fetchOneAssoc();
368 if (is_null($data) || !is_array($data)) {
369 return null;
370 }
371 $survey = new Survey($data, $sid, (boolean) $data['valid'], unserialize($data['questions']));
372 return $survey;
373 }
374 // }}}
375
376 // {{{ static function retrieveSurveyInfo() : gets information about a survey (title, description, end date, restrictions) but does not unserialize the survey object structure
377 public static function retrieveSurveyInfo($sid)
378 {
379 $sql = 'SELECT title, description, end, mode, promos, valid
380 FROM survey_surveys
381 WHERE id={?}';
382 $res = XDB::query($sql, $sid);
383 return $res->fetchOneAssoc();
384 }
385 // }}}
386
387 // {{{ function proposeSurvey() : stores a proposition of survey in database (before validation)
388 public function proposeSurvey()
389 {
390 $sql = 'INSERT INTO survey_surveys
391 SET questions={?},
392 title={?},
393 description={?},
394 author_id={?},
395 end={?},
396 mode={?},
397 promos={?},
398 valid=0;';
399 return XDB::execute($sql, serialize($this->questions), $this->title, $this->description, S::v('uid'), $this->end, $this->mode, $this->promos);
400 }
401 // }}}
402
403 // {{{ function updateSurvey() : updates a survey in database (before validation)
404 public function updateSurvey()
405 {
406 if ($this->id == -1) {
407 return false;
408 }
409 $sql = 'UPDATE survey_surveys
410 SET questions={?},
411 title={?},
412 description={?},
413 end={?},
414 mode={?},
415 promos={?}
416 WHERE id={?};';
417 return XDB::execute($sql, serialize($this->questions), $this->title, $this->description, $this->end, $this->mode, $this->promos, $this->id);
418 }
419 // }}}
420
421 // {{{ static function validateSurvey() : validates a survey
422 public static function validateSurvey($sid)
423 {
424 $sql = 'UPDATE survey_surveys
425 SET valid=1
426 WHERE id={?};';
427 return XDB::execute($sql, $sid);
428 }
429 // }}}
430
431 // {{{ functions vote() and hasVoted() : handles vote to a survey
432 public function vote($uid, $args)
433 {
434 XDB::execute('INSERT INTO survey_votes
435 SET survey_id={?}, user_id={?};', $this->id, $uid); // notes the user as having voted
436 $vid = XDB::insertId();
437 for ($i = 0; $i < count($this->questions); $i++) {
438 $ans = $this->questions[$i]->checkAnswer($args[$i]);
439 if (!is_null($ans) && is_array($ans)) {
440 foreach ($ans as $a) {
441 XDB::execute('INSERT INTO survey_answers
442 SET vote_id = {?},
443 question_id = {?},
444 answer = {?}', $vid, $i, $a);
445 }
446 }
447 }
448 }
449
450 public function hasVoted($uid)
451 {
452 $res = XDB::query('SELECT id
453 FROM survey_votes
454 WHERE survey_id={?} AND user_id={?};', $this->id, $uid); // checks whether the user has already voted
455 return ($res->numRows() != 0);
456 }
457 // }}}
458
459 // {{{ static function deleteSurvey() : deletes a survey (and all its votes)
460 public static function deleteSurvey($sid)
461 {
462 $sql = 'DELETE s.*, v.*, a.*
463 FROM survey_surveys AS s
464 LEFT JOIN survey_votes AS v
465 ON v.survey_id=s.id
466 LEFT JOIN survey_answers AS a
467 ON a.vote_id=v.id
468 WHERE s.id={?};';
469 return XDB::execute($sql, $sid);
470 }
471 // }}}
472
473 // {{{ static function purgeVotes() : clears all votes concerning a survey (I'm not sure whether it's really useful)
474 public static function purgeVotes($sid)
475 {
476 $sql = 'DELETE v.*, a.*
477 FROM survey_votes AS v
478 LEFT JOIN survey_answers AS a
479 ON a.vote_id=v.id
480 WHERE v.survey_id={?};';
481 return XDB::execute($sql, $sid);
482 }
483 // }}}
484
485 // }}}
486 }
487 // }}}
488
489 // {{{ abstract class SurveyQuestion
490 abstract class SurveyQuestion
491 {
492 // {{{ common properties, constructor, and basic methods
493 private $question;
494 private $comment;
495
496 public function __construct($args)
497 {
498 $this->update($args);
499 }
500
501 public function update($a)
502 {
503 $this->question = $a['question'];
504 $this->comment = $a['comment'];
505 }
506
507 abstract protected function getQuestionType();
508 // }}}
509
510 // {{{ function toArray() : converts to array
511 public function toArray()
512 {
513 return array('type' => $this->getQuestionType(), 'question' => $this->question, 'comment' => $this->comment);
514 }
515 // }}}
516
517 // {{{ function checkSyntax() : checks question elements (before storing into database), not currently needed (with new structure)
518 protected function checkSyntax()
519 {
520 return null;
521 }
522 // }}}
523
524 // {{{ function checkAnswer : returns a correctly formatted answer (or nothing empty string if error)
525 public function checkAnswer($ans)
526 {
527 return null;
528 }
529 // }}}
530
531 // {{{ functions regarding the results of a survey
532 public function buildResultRequest()
533 {
534 return '';
535 }
536
537 public function getCSVColumn()
538 {
539 return $this->question;
540 }
541 // }}}
542 }
543 // }}}
544
545 // {{{ abstract class SurveySimple and its derived classes : "opended" questions
546 // {{{ abstract class SurveySimple extends SurveyQuestion
547 abstract class SurveySimple extends SurveyQuestion
548 {
549 public function checkAnswer($ans)
550 {
551 return array($ans);
552 }
553
554 public function buildResultRequest()
555 {
556 $sql = 'SELECT answer
557 FROM survey_answers
558 WHERE vote_id IN (SELECT id FROM survey_votes WHERE survey_id={?})
559 AND question_id={?}
560 ORDER BY RAND()
561 LIMIT 5;';
562 return $sql;
563 }
564 }
565 // }}}
566
567 // {{{ class SurveyText extends SurveySimple : simple text field, allowing a few words
568 class SurveyText extends SurveySimple
569 {
570 public function getQuestionType()
571 {
572 return "text";
573 }
574 }
575 // }}}
576
577 // {{{ class SurveyTextarea extends SurveySimple : textarea field, allowing longer comments
578 class SurveyTextarea extends SurveySimple
579 {
580 public function getQuestionType()
581 {
582 return "textarea";
583 }
584 }
585 // }}}
586
587 // {{{ class SurveyNum extends SurveySimple : allows numerical answers
588 class SurveyNum extends SurveySimple
589 {
590 public function checkAnswer($ans)
591 {
592 return array(intval($ans));
593 }
594
595 protected function getQuestionType()
596 {
597 return "num";
598 }
599 }
600 // }}}
601 // }}}
602
603 // {{{ abstract class SurveyList and its derived classes : restricted questions that allows only a list of possible answers
604 // {{{ abstract class SurveyList extends SurveyQuestion
605 abstract class SurveyList extends SurveyQuestion
606 {
607 protected $choices;
608
609 public function update($args)
610 {
611 parent::update($args);
612 $this->choices = explode('|', $args['options']);
613 }
614
615 public function toArray()
616 {
617 $rArr = parent::toArray();
618 $rArr['choices'] = $this->choices;
619 $rArr['options'] = implode('|', $this->choices);
620 return $rArr;
621 }
622
623 public function buildResultRequest()
624 {
625 $sql = 'SELECT answer, COUNT(id) AS count
626 FROM survey_answers
627 WHERE vote_id IN (SELECT id FROM survey_votes WHERE survey_id={?})
628 AND question_id={?}
629 GROUP BY answer ASC';
630 return $sql;
631 }
632
633 public function getCSVColumn()
634 {
635 $r = parent::getCSVColumn();
636 if (empty($this->choices)) {
637 return $r;
638 }
639 $r .= ' [0 => '.$this->choices[0];
640 for ($k = 1; $k < count($this->choices); $k++) {
641 $r .= ', '.$k.' => '.$this->choices[$k];
642 }
643 $r .= ']';
644 return $r;
645 }
646 }
647 // }}}
648
649 // {{{ class SurveyRadio extends SurveyList : radio question, allows one answer among the list offered
650 class SurveyRadio extends SurveyList
651 {
652 public function checkAnswer($ans)
653 {
654 return (array_key_exists($ans, $this->choices))? array($ans) : null;
655 }
656
657 protected function getQuestionType()
658 {
659 return "radio";
660 }
661 }
662 // }}}
663
664 // {{{ class SurveyCheckbox extends SurveyList : checkbox question, allows any number of answers among the list offered
665 class SurveyCheckbox extends SurveyList
666 {
667 public function checkAnswer($ans)
668 {
669 $rep = array();
670 foreach ($ans as $a) {
671 $a = intval($a);
672 if (array_key_exists($a, $this->choices)) {
673 $rep[] = $a;
674 }
675 }
676 return (count($rep) == 0)? null : $rep;
677 }
678
679 protected function getQuestionType()
680 {
681 return "checkbox";
682 }
683 }
684 // }}}
685 // }}}
686
687 // {{{ class SurveyPersonal extends SurveyQuestion : allows easy and verified access to user's personal data (promotion, name...)
688 // actually this type of question should be suppressed (non anonymous surveys are possible with survey modes)
689 // and anyway it is not finished (checkAnswer implementation) : currently it does not store anything when a user votes
690 class SurveyPersonal extends SurveyQuestion
691 {
692 private $perm;
693
694 public function update($args)
695 {
696 $args['question'] = "Informations personnelles";
697 parent::update($args);
698 $this->perm['promo'] = isset($args['promo'])? 1 : 0;
699 $this->perm['name'] = isset($args['name'])? 1 : 0;
700 }
701
702 public function checkAnswer($ans)
703 {
704 if (intval($ans) == 1) {
705 // requete mysql qvb
706 return null;
707 } else {
708 return null;
709 }
710 }
711
712 protected function getQuestionType()
713 {
714 return "personal";
715 }
716
717 public function toArray()
718 {
719 $a = parent::toArray();
720 $a['promo'] = $this->perm['promo'];
721 $a['name'] = $this->perm['name'];
722 return $a;
723 }
724 }
725 // }}}
726
727 // vim:set et sw=4 sts=4 sws=4 foldmethod=marker enc=utf-8:
728 ?>