Umfragen
db.class.php
Go to the documentation of this file.
1 <?php
2 /*
3  * db.class.php
4  *
5  * Copyright 2012 Johannes <jojo@jojo-42>
6  *
7  * This program is free software; you can redistribute it and/or modify
8  * it under the terms of the GNU General Public License as published by
9  * the Free Software Foundation; either version 2 of the License, or
10  * (at your option) any later version.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
20  * MA 02110-1301, USA.
21  *
22  *
23  */
24 
25 require_once("Exceptions.class.php");
26 require_once("widget.class.php");
27 
28 if( !isset($config_included) ){
29  die("config not loaded");
30 }
31 
37 class db {
38 
39  public $dbh;
40  public $last_poll_insert_id = -1;
41 
45  function __construct($dbuser , $dbpass , $dbname ,$dbhost){
46  try {
47  $this->dbh = new PDO("mysql:dbname=".$dbname.";host=".$dbhost, $dbuser, $dbpass,
48  array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
49  } catch (PDOException $e) {
50  die( 'Connection failed: ' . $e->getMessage());
51  }
52  return true;
53  }
54 
55  /*---------------------------------
56  *
57  * GETTERS
58  *
59  --------------------------------*/
60 
66  function get_sql_all_assoc($sql){
67  $sqh = $this->dbh->query($sql);
68  if( $sqh !== false ){
69  return $sqh->fetchAll(PDO::FETCH_ASSOC);
70  }else{
71  return false;
72  }
73  }
74 
80  function get_poll_name_from_list($polls){
81  $name_list = array();
82  $sqh = $this->dbh->prepare("SELECT ID,name from `umfragen` WHERE `ID` = :id AND `status` != ".STATUS_DELETED);
83  foreach($polls as $pollID){
84  $sqh->execute(array(":id" => $pollID));
85  $res = $sqh->fetchAll(PDO::FETCH_ASSOC);
86  if( sizeof($res) == 1){
87  $name_list[ $res[0]["ID"] ] = $res[0]["name"];
88  }
89  }
90  return $name_list;
91  }
92 
97  function get_all_polls_array(){
98  $sqh = $this->dbh->query("SELECT * from `umfragen` WHERE `umfragen`.`status` != ".STATUS_DELETED);
99 
100  return $sqh->fetchAll(PDO::FETCH_ASSOC);
101  }
102 
107  function get_all_polls_array_from_status($stat){
108  $sqh = $this->dbh->query("SELECT * from `umfragen` WHERE `umfragen`.`status` = $stat");
109  return $sqh->fetchAll(PDO::FETCH_ASSOC);
110  }
111 
117  function get_poll_infoArray($pollID){
118  $sqh = $this->dbh->query("SELECT * from `umfragen` WHERE `ID` = $pollID AND `status` != ".STATUS_DELETED);
119  return $sqh->fetch(PDO::FETCH_ASSOC);
120  }
121 
128  function get_poll_result_for_user($pollID,$username){
129  $sqh = $this->dbh->query("SELECT * from `{$pollID}_results` WHERE `user` = '$username'");
130  if($sqh !== false){
131  return $sqh->fetch(PDO::FETCH_ASSOC);
132  }
133  return false;
134  }
135 
141  function get_widget_names($pollID){
142  $sqh = $this->dbh->query("SELECT name,ID from `{$pollID}`");
143  if($sqh !== false){
144  $res = $sqh->fetchAll(PDO::FETCH_ASSOC);
145  return $res;
146  }
147  return false;
148  }
149 
154  function get_config_array(){
155  $sqh = $this->dbh->query("SELECT * FROM `config`");
156  if( $sqh !== false ){
157  $res = $sqh->fetchALL(PDO::FETCH_ASSOC);
158  return $res;
159  }
160  }
161 
162 
168  function count_results($pollID){
169  $sqh = $this->dbh->query("SELECT COUNT(*) FROM `{$pollID}_results`");
170  if($sqh !== false){
171  $r= $sqh->fetch(PDO::FETCH_ASSOC);
172  return $r["COUNT(*)"];
173  }
174  return false;
175  }
176 
177 
178  /*
179  * @brief counts the results from a poll
180  * @param poll $poll
181  * @return array indexed by widgetID filled with integes. For checkBoxSingle the index is ID_true and ID_false. For matrix the questions are indexed in the list, the values are arrays indexed by the Id of the option.
182  */
183  /*/function extended_result_count(poll $poll){
184  // INIT
185  $list = array();
186  foreach($poll->widget_list as $w){
187  $list[ $w->ID ] = 0;
188  if( $w instanceof container){
189  if( $w instanceof matrix ){ // matrix container
190  foreach($w->widget_list as $o){
191  $list[$o->ID] = array();
192  foreach( $w->option_list as $oo ){
193  $list[$o->ID][$oo->ID] = 0;
194  }
195  }
196  foreach($w->option_list as $o){
197  $list[$o->ID] = 0;
198  }
199 
200  }else{ // other conainers
201  $childs = $w->get_all_childs();
202  foreach($childs as $o){
203  $list[$o->ID] = 0;
204  }
205  }
206  }
207  if( $w instanceof checkBoxSingle){
208  $list[ "{$w->ID}_true" ] = 0;
209  $list[ "{$w->ID}_false" ] = 0;
210  }
211  }
212 
213  // COUNT
214  foreach($poll->widget_list as $widget){
215  $list[$widget->ID] = 0;
216  if( $widget instanceof input_widget){
217  if( (!$widget instanceof text) AND (!$widget instanceof matrix) AND (!$widget instanceof textExt) ){
218  // selects and counts the saved values ands looks up the ID to the value.
219  if( $widget instanceof radioButtonList ){
220 
221  $sqh = $this->dbh->query("SELECT `{$widget->name}`,
222  COUNT(`{$widget->name}`) AS count,
223  `{$poll->ID}`.ID,
224  `{$poll->ID}`.value
225  FROM `umfragen`.`{$poll->ID}_results`
226  LEFT JOIN `{$poll->ID}`
227  ON `{$poll->ID}_results`.`{$widget->name}` = `{$poll->ID}`.`value`
228  GROUP BY `ID`;");
229  }else{
230  $sqh = $this->dbh->query("SELECT `{$widget->name}`,
231  COUNT(`{$widget->name}`) AS count
232  FROM `umfragen`.`{$poll->ID}_results`
233  GROUP BY `{$widget->name}`;");
234  }
235  if( (!isset($sqh)) OR ($sqh === false) ){
236  continue;
237  }
238  $res = $sqh->fetchAll(PDO::FETCH_ASSOC);
239  // walk through results ... one line is one "value" to count
240  foreach( $res as $val ){
241 
242  // radio button lists .. see sql left join above ID points so correct radiobutton widget
243  if( $widget instanceof radioButtonList ){
244  $list[ intval($val["ID"]) ] = intval($val["count"]);
245 
246  // checkboxlists .. value1,value2,value3
247  }else if ( $widget instanceof checkBoxList ){
248  $opt_vals = explode(",",$val[ "{$widget->name}" ]);
249  // go through each value
250  foreach($opt_vals as $opt_val){
251  // get widgetID belonging to the value.. if there are 2 same values there can be confusion
252  $id = -1 ;
253  foreach( $widget->option_list as $opt){
254  if( $opt->value == $opt_val){ $id = $opt->ID;break; }
255  }
256  // if widget found add widget and count to list or add count to existing count
257  if( $id != -1){
258  if( isset($list[ $id ]) ){
259  $list[ $id ] = $list[ $id ] + intval( $val[ "count" ] );
260  }else{
261  $list[ $id ] = intval( $val[ "count" ] );
262  }
263 
264  } // end if id found
265  } // end foreach option value
266 
267  // CheckboxSingle
268  }else if ($widget instanceof checkBoxSingle) {
269  $cnf_true = $widget->get_config("true");
270  $cnf_false = $widget->get_config("false");
271  if( $val[ $widget->ID ] == $cnf_true){
272  $list[ "{$widget->ID}_true" ] = intval($val["count"]);
273  }else if( $val[ $widget->ID ] == $cnf_false){
274  $list[ "{$widget->ID}_false" ] = intval($val["count"]);
275  }
276 
277  // other widgets !!
278  }else{
279 
280  }
281  }
282  } // end if widget not textwidget
283  if( $widget instanceof matrix ){
284  foreach( $widget->widget_list as $question ){
285  $sqh = $this->dbh->query("SELECT `{$question->ID}`,
286  COUNT(`{$question->ID}`) AS count,
287  `{$poll->ID}`.ID,
288  `{$poll->ID}`.value
289  FROM `umfragen`.`{$poll->ID}_results`
290  LEFT JOIN `{$poll->ID}`
291  ON `{$poll->ID}_results`.`{$question->ID}` = `{$poll->ID}`.`value`
292  GROUP BY `{$question->ID}`;");
293  if( (isset($sqh)) AND ($sqh !== false) ){
294  $res = $sqh->fetchAll(PDO::FETCH_ASSOC);
295  // walk through results ... one line is one "value" to count
296  foreach( $res as $val ){
297  $list[$question->ID][ intval($val["ID"]) ] = intval($val["count"]);
298  } // end foreach result
299  }// end if sqh
300  } // end foreach question
301  } // end if matrix
302 
303  } // end if not instance of output widget
304  } // end for each widget
305  return $list;
306 
307  #SELECT `2`,COUNT(`2`) AS anz FROM `umfragen`.`214_results` GROUP BY `2`
308 
309 
310  }*/
311 
317  public function get_schedule_widget_data($widget){
318  $names_str = "";
319  $data = array();
320  foreach( $widget->widget_list as $child ){
321  $data[$child->ID] = array();
322  $names_str .= ",`".$child->name."`";
323  if( $child instanceof scheduleName ){
324  $name_wid = $child;
325  }
326  }
327  $names_str = trim($names_str,",");
328 
329  $sqh = $this->dbh->query("SELECT $names_str from `{$widget->pollID}_results`");
330 
331  if( ($sqh !== false) AND (isset($name_wid)) ){
332  $res = $sqh->fetchAll(PDO::FETCH_ASSOC);
333  if( is_array($res) ){
334  foreach( $res as $row ){
335  if( (is_array($row)) AND (isset($row[$name_wid->ID])) AND ($row[$name_wid->ID] != "") ){
336  foreach( $widget->widget_list as $child ){
337  if( isset($row[ $child->name ]) ){
338  $data[ $child->ID ][] = $row[ $child->name ];
339  }// end if isset
340  } // end foreach child
341  }// end if name is set
342  } // end foreach row
343  } // end if result
344  } // end if SQL ok
345 
346  return $data;
347  }
348 
357  function export_to_csv(poll $poll,$delimiter,$enclosure,$download=true){
358  $sqh = $this->dbh->query("SELECT * from `{$poll->ID}_results`");
359  if($sqh !== false){
360  if($download){
361  // send response headers to the browser
362  header( 'Content-Type: text/csv' );
363  header( 'Content-Disposition: attachment;filename=export.csv');
364  }
365  $fp = fopen('php://output', 'w');
366 
367  if($sqh !== false){
368  $c = 0;
369  while($row = $sqh->fetch(PDO::FETCH_ASSOC) ) {
370  // handle first row Col names
371  if($c == 0){
372  // mapping colname to widgetID (simple poll)
373  if($poll->type == POLLTYPE_SIMPLE){
374  $names = array_keys($row);
375  $header = array();
376  foreach($names as $name){
377  if( is_numeric($name) ){
378  $w = $poll->get_widget_by_id( intval($name) );
379  if (isset($w)){
380  $header[] = $w->dispName;
381  }
382  }else{
383  $header[] = $name;
384  }
385  }
386  fputcsv($fp, $header,$delimiter,$enclosure);
387  }else if ($poll->type == POLLTYPE_ADVANCED){
388  $header = array_keys($row);
389  fputcsv($fp, $header,$delimiter,$enclosure);
390  }
391  }
392  // write line
393  fputcsv($fp, $row,$delimiter,$enclosure);
394  ++$c;
395  }
396  }
397 
398  fclose($fp);
399  }
400  }
401 
409  public function export_result($pollID,$sort="",$order="asc"){
410  if( ! in_array($order,array("asc","desc")) ){
411  $order = "asc";
412  }
413 
414  // check if sort colun exists
415  if( $sort != "" ){
416  $ok = false;
417  $sqh = $this->dbh->query("SHOW COLUMNS FROM `{$pollID}_results`");
418  if( $sqh !== false ){
419  $r = $sqh->fetchAll(PDO::FETCH_ASSOC);
420  foreach($r as $row){
421  if( $row["Field"] == $sort){
422  $ok = true;
423  }
424  }
425  }
426  if( $ok == false ){
427  return array();
428  }
429  }
430 
431  if( $sort == "" ){
432  $sqh = $this->dbh->query("SELECT * from `{$pollID}_results`");
433  }else{
434  $sqh = $this->dbh->query("SELECT * from `{$pollID}_results` ORDER BY `$sort` $order");
435  }
436 
437  if($sqh !== false ){
438  return $sqh->fetchAll(PDO::FETCH_ASSOC);
439  }else{
440  return array();
441  }
442  }
443 
444  /*---------------------------------
445  *
446  * LOAD
447  *
448  --------------------------------*/
449 
457  function load_child_from_parentID($pollID,$ID,$whitelist){
458  // no widget allowed
459  if( empty($whitelist) ){
460  return array();
461  // deactivates whitelist
462  }else if( in_array("*",$whitelist)){
463  $sqh = $this->dbh->query("SELECT `typ` , `ID`,`name`,`index`,`dispName`,`value`,`config`,`is_child`,`parent`,`is_required` FROM `$pollID` WHERE `parent` = $ID AND `is_child`=1 ORDER BY `index` ASC");
464  // filter widget types
465  }else{
466  $wlist = "";
467  foreach( $whitelist as $typ){
468  $wlist = $wlist." OR `typ` = '$typ'";
469  }
470  $wlist = trim($wlist," OR ");
471  $sqh = $this->dbh->query("SELECT `typ` , `ID`,`name`,`index`,`dispName`,`value`,`config`,`is_child`,`parent`,`is_required`
472  FROM `$pollID`
473  WHERE `parent` = $ID
474  AND ($wlist)
475  AND `is_child`=1
476  ORDER BY `index` ASC");
477  }
478 
479  $r = $sqh->fetchAll( PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE );
480  return $r; // false if not found
481  }
482 
489  function load_widget($pollID,$ID){
490  $sqh = $this->dbh->query("SELECT `typ` , `ID`,`name`,`index`,`dispName`,`value`,`is_required`,`config`,`is_child` FROM `$pollID` WHERE ID = $ID AND `is_child`=0");
491  if($sqh === false){
492  return false;
493  }
494  $r = $sqh->fetch( PDO::FETCH_CLASS | PDO::FETCH_CLASSTYPE );
495  if($r !== false){
496  $r->pollID = $pollID;
497  if($r->is_child == "1"){ $r->is_child = true;}else{$r->is_child = false;}
498  if($r->is_required == "1"){ $r->is_required = true;}else{$r->is_required = false;}
499  // radiobuttonLists and checkboxlists
500  if($r instanceof container){
501  // gets an array of widget objects
502  $l2 = array();
503  $l1 = array();
504 
505  $l = $this->load_child_from_parentID($pollID,$r->ID,$r->child_options_whitelist);
506  $l2 = $this->load_child_from_parentID($pollID,$r->ID,$r->child_widgets_whitelist);
507 
508  if( ($l !== false) AND ($l2 !== false) ){
509  $r->set_options($l);
510  $r->set_widgets($l2);
511  } // end if not false
512  } // end container widget
513  } // end if not false (Widget)
514  return $r;
515  }
516 
522  function load_poll($pollID){
523  // lade widgetID liste
524  $sqh = $this->dbh->query("SELECT `ID` FROM `$pollID` WHERE `is_child`=0 ORDER BY `index` ASC");
525  $list = $sqh->fetchAll(PDO::FETCH_ASSOC);
526  // lade widgets
527  $widget_list = array();
528  foreach($list as $w){
529  $wo = $this->load_widget($pollID,$w["ID"]);
530  if($wo !== false){
531  $widget_list[] = $wo;
532  }
533  }
534  return $widget_list;
535  }
536 
537 
538  /*---------------------------------
539  *
540  * INSERT
541  *
542  --------------------------------*/
543 
550  function insert_widget(&$w,$pollType){
551  if( !(isset($w->typ)) ){
552  throw new varException("Widget type not set");
553  }else if( $w->typ == "" ){
554  throw new varException("Widget type empty");
555  }
556  if( !(isset($w->index)) ){
557  throw new varException("Widget index not set");
558  }
559  if( !(isset($w->pollID)) ){
560  throw new varException("Widget pollID not set");
561  }else if( $w->pollID == "" ){
562  throw new varException("Widget pollID empty");
563  }
564 
565  if( $pollType == POLLTYPE_ADVANCED ){
566 
567  }else if ( $pollType == POLLTYPE_SIMPLE ){
568 
569  }else{
570 
571  }
572 
573  // radioButtonList are a set of other widgets .. so insert them
574  if($w instanceof container){
575  $childs = $w->get_all_childs();
576  foreach( $childs as $child ){
577  $this->insert_widget( $child , $pollType );
578  }
579 
580  }
581 
582  // void name -> simple mode
583  if( (!isset($w->name)) OR ($w->name == "") ){
584  $name = "-";
585  }else{
586  $name = $w->name;
587  }
588 
589  // void value
590  if( !isset($w->value) ){
591  $val = "";
592  }else {$val = $w->value;}
593 
594  if( !isset($w->config) ){
595  $cnf = "";
596  }else {
597  $w->serialize_config();
598  $cnf = $w->config;
599  }
600 
601 
602  // do SQL
603  $data = array(
604  ":t" => $w->typ,
605  ":n" => $name,
606  ":i" => $w->index,
607  ":dn" => $w->dispName,
608  ":req" => $w->is_required,
609  ":v" => $val,
610  ":c" => $cnf,
611  ":chil" => $w->is_child,
612  ":par" => $w->parent
613  );
614  $sqh = $this->dbh->prepare("INSERT INTO `{$w->pollID}` (`typ`,`name`,`index`,`dispName`,`is_required`,`value`,`config`,`is_child`,`parent`) VALUES (:t,:n,:i,:dn,:req,:v,:c,:chil,:par)");
615  // ALTER TABLE `121_results` ADD `test` VARCHAR( 256 ) NOT NULL
616  $r = $sqh->execute($data);
617  $w->ID = $this->dbh->lastInsertId();
618 
619  // update names to id if in simple mode
620  if( ($w->is_child === false) ){ // for all widgets witch are not childs
621  if( ($w->name == "-") OR ($w->name == "") ){
622  $w->name = "{$w->ID}";
623  }
624  $this->update_widget_field($w,"name",$w->name);
625  // options already inserted (recursive ... going through option list before this)
626  //... so update them to the widget ID in simple mode
627  if ($w instanceof container){
628  $childs = $w->get_all_childs();
629  foreach( $childs as $child ){
630  $this->update_widget_field($child,"parent",$w->ID);
631  }
632 
633  }
634  }
635 
636  if( ($w instanceof input_widget) AND ($w->direct_result === true) ){
637  if( ($w->is_child === true) ){
638  if( ($w->name == "-") OR ($w->name == "") ){
639  $w->name = "{$w->ID}";
640  }
641  $this->update_widget_field($w,"name",$w->name);
642  $this->add_widget_to_result_table($w);
643  }else{
644  $this->add_widget_to_result_table($w);
645  }
646 
647  }
648 
649  return $r;
650  }
651 
658  function insert_poll(&$p,$create_result_table=true){
659  // name is required
660  if ( !(isset($p->name)) ){
661  throw new varException("poll vars not set");
662  }
663  if( ($p->name == "") ){
664  throw new varException("poll vars empty");
665  }
666 
667  $anon = $p->anonymous; // bool
668  if($p->is_public){
669  $anon = "2"; // str 2
670  }
671 
672  $cnf = $p->serialize_config();
673  // do SQL
674  $data = array(
675  ":n" => $p->name,
676  ":o" => $p->owner,
677  ":ofn" => $p->owner_fn,
678  ":t" => $p->type,
679  ":i" => $p->noDisplay,
680  ":a" => $anon,
681  ":th" => $p->theme,
682  ":cnf" => $cnf
683  );
684  $sqh = $this->dbh->prepare("INSERT INTO `umfragen` (`name`,`owner`,`owner_fn`,`type`,`anonymous`,`noDisplay`,`theme`,`config`) VALUES (:n,:o,:ofn,:t,:a,:i,:th,:cnf)");
685  $r = $sqh->execute($data);
686  if ($r !== false){
687  $this->last_poll_insert_id = $this->dbh->lastInsertId();
688  $p->ID = intval($this->last_poll_insert_id);
689  $hash = $p->make_id_hash();
690  $p->hashID = $hash;
691  $this->dbh->exec("UPDATE `umfragen` SET `hashID` = '$hash' WHERE `ID`={$p->ID}");
692 
693  $sql = "CREATE TABLE `umfragen`.`{$p->ID}` (
694  `ID` int( 11 ) NOT NULL AUTO_INCREMENT ,
695  `typ` varchar( 15 ) COLLATE utf8_unicode_ci NOT NULL ,
696  `name` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL ,
697  `index` int( 11 ) NOT NULL ,
698  `dispName` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL ,
699  `value` longtext COLLATE utf8_unicode_ci NOT NULL ,
700  `is_required` tinyint( 1 ) NOT NULL DEFAULT '0',
701  `is_child` tinyint( 1 ) NOT NULL DEFAULT '0',
702  `parent` int( 11 ) NOT NULL DEFAULT '-1',
703  `config` longtext COLLATE utf8_unicode_ci,
704  PRIMARY KEY ( `ID` )
705  ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;";
706  $this->dbh->exec($sql);
707  $this->make_void_result_table($p);
708  return $p;
709  }
710  return false;
711  }
712 
717  function make_umfragen_table(){
718  $sql = "
719  CREATE TABLE IF NOT EXISTS `umfragen` (
720  `ID` int(11) NOT NULL AUTO_INCREMENT,
721  `hashID` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
722  `name` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
723  `groups` tinytext COLLATE utf8_unicode_ci NOT NULL,
724  `owner` int(11) NOT NULL DEFAULT '0',
725  `owner_fn` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
726  `timeout` datetime NOT NULL,
727  `active_since` datetime NOT NULL,
728  `status` int(11) NOT NULL DEFAULT '0',
729  `noDisplay` tinyint(1) NOT NULL DEFAULT '0',
730  `type` int(11) NOT NULL,
731  `anonymous` tinyint(1) NOT NULL DEFAULT '0',
732  `theme` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
733  `config` longtext COLLATE utf8_unicode_ci NOT NULL,
734  PRIMARY KEY (`ID`),
735  UNIQUE KEY `ID` (`ID`)
736  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;";
737  $this->dbh->exec($sql);
738  return true;
739  }
740 
745  function make_user_config_table(){
746  $sql =
747  "CREATE TABLE IF NOT EXISTS `user_config` (
748  `user` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
749  `polls_visited` longtext COLLATE utf8_unicode_ci NOT NULL,
750  `email` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
751  `config` longtext COLLATE utf8_unicode_ci NOT NULL
752  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
753  $this->dbh->exec($sql);
754  return true;
755  }
756 
761  function make_config_table(){
762  $sql =
763  "CREATE TABLE IF NOT EXISTS `config` (
764  `name` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
765  `value` longtext COLLATE utf8_unicode_ci NOT NULL
766  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
767  $this->dbh->exec($sql);
768  return true;
769  }
770 
775  function make_email_stack_table(){
776  $sql =
777  "CREATE TABLE IF NOT EXISTS `email_stack` (
778  `ID` INT UNSIGNED AUTO_INCREMENT,
779  `pollID` int(11) NOT NULL,
780  `type` VARCHAR(256) NOT NULL,
781  `fullName` VARCHAR(256) NOT NULL,
782  `uname` VARCHAR(256) NOT NULL,
783  `timestamp` DATETIME NOT NULL,
784  PRIMARY KEY (`ID`),
785  UNIQUE KEY `ID` (`ID`)
786  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
787  $this->dbh->exec($sql);
788  return true;
789  }
790 
795  function check_tables(){
796  $ok = array();
797  $sqh = $this->dbh->query("SHOW TABLES FROM `umfragen`");
798  $rows = $sqh->fetchAll(PDO::FETCH_ASSOC);
799  $tables = array();
800  foreach($rows as $r){
801  $tables[] = $r["Tables_in_umfragen"];
802  }
803  if( in_array("umfragen",$tables) ){
804  $ok["umfragen"] = true;
805  }else{
806  $ok["umfragen"] = false;
807  }
808  if( in_array("user_config",$tables) ){
809  $ok["user_config"] = true;
810  }else{
811  $ok["user_config"] = false;
812  }
813  if( in_array("config",$tables) ){
814  $ok["config"] = true;
815  }else{
816  $ok["config"] = false;
817  }
818  if( in_array("email_stack",$tables) ){
819  $ok["email_stack"] = true;
820  }else{
821  $ok["email_stack"] = false;
822  }
823  return $ok;
824  }
825 
826  /*---------------------------------
827  *
828  * DUPLICATE
829  *
830  --------------------------------*/
836  function duplicate_poll($pID){
837  $p = new poll();
838  $r = $p->load_from_id($this,$pID);
839  if ($r !== false){
840  if( (isset($_SESSION["user"]->ID)) AND (isset($_SESSION["user"]->fullName)) ){
841  $p->owner = $_SESSION["user"]->ID;
842  $p->owner_fn = $_SESSION["user"]->fullName;
843  }
844  // create new poll with widget table but without resulttable
845  $this->insert_poll($p,false); // poll, create_result_table
846  $p->save_name($this);
847  $p->save_groups($this);
848  $p->save_timeout($this);
849  foreach($p->widget_list as $wid){
850  // reset widget name and pollid
851  if( $wid->name == $wid->ID){
852  $wid->name = "-";
853  }
854  $wid->pollID = $p->ID;
855  if( $wid instanceof container ){
856  // reset pollID
857  foreach( $wid->option_list as $option ){
858  $option->pollID = $p->ID;
859  }
860  foreach( $wid->widget_list as $child ){
861  if( $child->name == $child->ID){ // simple mode -> name=ID
862  $child->name = "-";
863  }
864  $child->pollID = $p->ID;
865  }
866  }
867  }
868  $this->update_poll_field($p->ID,"status",$p->status);
869  // copy widgets
870  //$shh = $this->dbh->exec("INSERT `umfragen`.`{$p->ID}` SELECT * FROM `umfragen`.`$pID`"); //CREATE TABLE recipes_new LIKE production.recipes; INSERT recipes_new SELECT * FROM production.recipes;
871  foreach( $p->widget_list as $widget ){
872  if( is_numeric($widget->name) ){
873  $widget->name = "";
874  }
875  $this->insert_widget($widget, $p->type);
876  }
877  // make result table
878  $this->add_all_widgets_to_poll_result_table($p);
879  return $p;
880  }else{
881  return ;
882  }
883  }
884 
891  function duplicate_widget($pID,$wID){
892  $poll = new poll();
893  $r = $poll->load_from_id($this,$pID);
894 
895  //$widget = $this->load_widget($pID,$wID);
896  if ($r !== false){
897  $widget = clone $poll->get_widget_by_id($wID);
898  // radiobuttonlists .. grr ... need extra code ...
899  if($widget instanceof container){
900  // advanced polltype .. rename the names to name-copy ... for widget and option list (need be the same)
901  if($poll->type == POLLTYPE_ADVANCED){
902  $widget->name = $widget->name."-copy";
903  foreach($widget->option_list as $option){
904  $option->name = $widget->name;
905  }
906  foreach( $widget->widget_list as $child ){
907  $child->name = $child->name."-copy";
908  }
909  $poll->insert_widget($widget->index,$widget);
910  $this->insert_widget($widget,$poll->type);
911  // update parent IDs
912  $childs = $widget->get_all_childs();
913  foreach( $childs as $child ){
914  $this->update_widget_field($child,"parent",$widget->ID);
915  }
916  // first insert widget, then update names to ID
917  }else if($poll->type == POLLTYPE_SIMPLE){
918  $widget->name = "-";
919  foreach($widget->widget_list as $child){
920  $child->name="-";
921  }
922  $poll->insert_widget($widget->index,$widget);
923  $this->insert_widget($widget,$poll->type);
924  $this->update_widget_field($widget,"name",$widget->ID);
925  $widget->name = $widget->ID;
926 
927  $childs = $widget->get_all_childs();
928  foreach( $childs as $child ){
929  $this->update_widget_field($child,"name",$child->ID);
930  $option->name = $child->ID;
931  $this->update_widget_field($child,"parent",$widget->ID);
932  }/*
933  foreach($widget->option_list as $option){
934  $this->update_widget_field($option,"name","-");
935  $this->update_widget_field($option,"parent",$widget->ID);
936  }
937  if( $widget instanceof matrix){
938  foreach($widget->question_list as $option){
939  $this->update_widget_field($option,"name",$option->ID);
940  $this->update_widget_field($option,"parent",$widget->ID);
941  $option->name = $widget->ID;
942  }
943  }*/
944  }
945  }else{
946  $widget->name = "";
947  $poll->insert_widget($widget->index,$widget);
948  $this->insert_widget($widget,$poll->type);
949  }
950  // update shifted index
951  foreach($poll->widget_list as $wid){
952  $this->update_widget_field($wid,"index",$wid->index);
953  }
954 
955  return $widget->ID;
956  //$this->insert_widget($widget,$poll_type);
957  //$this->add_widget_to_result_table($wid);
958  }
959  }
960 
961 
962  /*---------------------------------
963  *
964  * RESULT TABLE STUFF
965  *
966  --------------------------------*/
975  function rename_result_column($pollID,$long_result,$old_name,$new_name){
976  if( $long_result === true ){
977  $sql = "ALTER TABLE `{$pollID}_results` CHANGE `{$old_name}` `$new_name` LONGTEXT";
978  }else{
979  $sql = "ALTER TABLE `{$pollID}_results` CHANGE `{$old_name}` `$new_name` VARCHAR( 256 )";
980  }
981  $sqh = $this->dbh->prepare($sql);
982  $sqh->execute(array());
983  }
984 
990  function add_widget_to_result_table($widget){
991  if( (isset($widget->long_result)) AND ($widget->long_result === true) ){
992  $sql = "ALTER TABLE `{$widget->pollID}_results` ADD `{$widget->name}` LONGTEXT";
993  }else{
994  $sql = "ALTER TABLE `{$widget->pollID}_results` ADD `{$widget->name}` VARCHAR( 256 )";
995  }
996  return $this->dbh->exec($sql);
997  }
998 
1010  function make_void_result_table(poll $poll){
1011  $sql = "CREATE TABLE `umfragen`.`{$poll->ID}_results` (
1012  `user` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL,
1013  `name` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL,
1014  `Vorname` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL,
1015  `Nachname` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL,
1016  `group` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL,
1017  `Datum` DATETIME COLLATE utf8_unicode_ci NOT NULL
1018  ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;";
1019  return $this->dbh->exec($sql);
1020  }
1021 
1027  function make_poll_result_table(poll $poll){
1028  $sql_widgets = "";
1029  foreach($poll->widget_list as $widget){
1030  if( (($widget instanceof input_widget) AND (!$widget instanceof matrix)) OR ($widget instanceof matrixQuestion) ){
1031  if( (isset($widget->long_result)) AND ($widget->long_result === true) ){
1032  $sql_widgets = $sql_widgets."`{$widget->name}` LONGTEXT COLLATE utf8_unicode_ci NOT NULL ,";
1033  }else{
1034  $sql_widgets = $sql_widgets."`{$widget->name}` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL ,";
1035  }
1036  }
1037  }
1038  $sql = "CREATE TABLE `umfragen`.`{$poll->ID}_results` (".$sql_widgets."
1039  `user` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL
1040  `name` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL,
1041  `Vorname` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL,
1042  `Nachname` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL,
1043  `group` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL,
1044  `Datum` DATETIME COLLATE utf8_unicode_ci NOT NULL
1045  ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;";
1046  return $this->dbh->exec($sql);
1047  }
1048 
1054  function add_all_widgets_to_poll_result_table(poll $poll){
1055  $sql_widgets = "";
1056  foreach($poll->widget_list as $widget){
1057  if( (isset($widget->long_result)) AND ($widget->long_result === true) ){
1058  $sql_widgets = $sql_widgets."`{$widget->name}` LONGTEXT COLLATE utf8_unicode_ci NOT NULL ,";
1059  }else{
1060  $sql_widgets = $sql_widgets."`{$widget->name}` varchar( 256 ) COLLATE utf8_unicode_ci NOT NULL ,";
1061  }
1062  }
1063  return $this->dbh->exec($sql_widgets);
1064  }
1065 
1073  function insert_poll_result($poll2){
1074  $return = array("value","action");
1075  $poll = clone $poll2;
1076  foreach( $poll->widget_list as $widget ){
1077  if ( $widget instanceof container ){
1078  foreach( $widget->widget_list as $child ){
1079  $poll->insert_widget(-1,$child);
1080  }
1081  }
1082  }
1083 
1084  // GET USER
1085  if( !isset( $_SESSION["user"]->name) ){
1086  $user = "";
1087  }else{
1088  $user = $_SESSION["user"]->name;
1089  }
1090  // GET FULL NAME
1091  if( !isset( $_SESSION["user"]->fullName) ){
1092  $fullName = "";
1093  }else{
1094  $fullName = $_SESSION["user"]->fullName;
1095  }
1096  if( !isset( $_SESSION["user"]->givenName) ){
1097  $givenName = "";
1098  }else{
1099  $givenName = $_SESSION["user"]->givenName;
1100  }
1101  if( !isset( $_SESSION["user"]->surName) ){
1102  $surName = "";
1103  }else{
1104  $surName = $_SESSION["user"]->surName;
1105  }
1106  // GET GROUP
1107  if( !isset( $_SESSION["user"]->group) ){
1108  $group = "";
1109  }else{
1110  $group = $_SESSION["user"]->group;
1111  }
1112  // GET GROUP NAME
1113  if( !isset( $_SESSION["user"]->groupName) ){
1114  $groupName = "";
1115  }else{
1116  $groupName = $_SESSION["user"]->groupName;
1117  }
1118 
1119  // PUBLIC AND ANONYMOUS POLLS
1120  if( $poll->anonymous === true ){
1121  $data = array(); $fields = ""; $vals = "";
1122  for($i=0 ; $i < sizeof($poll->widget_list) ; ++$i){
1123  $add_container = true;
1124  if( ($poll->widget_list[$i] instanceof container) AND ($poll->widget_list[$i]->direct_result === false) ){
1125  $add_container = false;
1126  }
1127 
1128  if( ($poll->widget_list[$i] instanceof input_widget) AND ( $add_container === true ) ){
1129  $data[":$i"] = $poll->widget_list[$i]->value;
1130  $fields = $fields."`{$poll->widget_list[$i]->name}`,";
1131  $vals = $vals.":$i,";
1132  }
1133  }
1134  $fields = trim($fields,",");
1135  $vals = trim($vals,",");
1136  $sqh = $this->dbh->prepare("INSERT INTO `{$poll->ID}_results` ($fields,`Datum`) VALUES ($vals,NOW())");
1137  $err = $sqh->execute($data);
1138  if( $err !== false ){
1139  $return["action"] = "insert";
1140  }
1141  $return["value"] = $err;
1142  return $return;
1143 
1144  // NON ANONYMOUS POLLS / update data ?
1145  }else{
1146  $rec = array();
1147  $sqrh = $this->dbh->query("SELECT * FROM `{$poll->ID}_results` WHERE `user` = '$user'");
1148  if ($sqrh !== false){
1149  $rec = $sqrh->fetchAll(PDO::FETCH_ASSOC);
1150  }
1151  // No record .. insert one
1152  if( sizeof($rec) == 0 ){
1153  $data = array(":user"=>$user,":FN"=>$fullName,":G"=>$groupName,":givenName"=>$givenName,":sn"=>$surName); $fields = "`user`,`name`,`group`,`Vorname`,`Nachname`,"; $vals = ":user,:FN,:G,:givenName,:sn,";
1154  for($i=0 ; $i < sizeof($poll->widget_list) ; ++$i){
1155  $add_container = true;
1156  if( ($poll->widget_list[$i] instanceof container) AND ($poll->widget_list[$i]->direct_result === false) ){
1157  $add_container = false;
1158  }
1159 
1160  if( ($poll->widget_list[$i] instanceof input_widget) AND ( $add_container === true ) ){
1161  $data[":$i"] = $poll->widget_list[$i]->value;
1162  $fields = $fields."`{$poll->widget_list[$i]->name}`,";
1163  $vals = $vals.":$i,";
1164  }
1165  }
1166  $fields = trim($fields,",");
1167  $vals = trim($vals,",");
1168 
1169  $sqh = $this->dbh->prepare("INSERT INTO `{$poll->ID}_results` ($fields,`Datum`) VALUES ($vals,NOW())");
1170 
1171  $err = $sqh->execute($data);
1172  if( $err !== false ){
1173  $return["action"] = "insert";
1174  }
1175  $return["value"] = $err;
1176  return $return;
1177 
1178  // update existing record
1179  }else if(sizeof($rec) == 1){
1180  $data = array(":FN"=>$fullName,":G"=>$groupName,":sn"=>$surName,":givenName"=>$givenName);
1181  $fields = "`Datum`= NOW(),`name`=:FN,`group`=:G, `Vorname` = :givenName , `Nachname` = :sn,";
1182  for($i=0 ; $i < sizeof($poll->widget_list) ; ++$i){
1183  $add_container = true;
1184  if( ($poll->widget_list[$i] instanceof container) AND ($poll->widget_list[$i]->direct_result === false) ){
1185  $add_container = false;
1186  }
1187 
1188  if( ($poll->widget_list[$i] instanceof input_widget) AND ( $add_container === true ) ){
1189  $data[":$i"] = $poll->widget_list[$i]->value;
1190  $fields = $fields."`{$poll->widget_list[$i]->name}` = :$i,";
1191  }
1192  }
1193  $fields = trim($fields,",");
1194  $sqh = $this->dbh->prepare("UPDATE `{$poll->ID}_results` SET $fields WHERE `user` = '$user'");
1195 
1196  $err = $sqh->execute($data);
1197 
1198  if( $err !== false ){
1199  $return["action"] = "update";
1200  // when klassenliste was used result is technical a UPDATE, but has to be reported as INSERT
1201  if( (isset($rec[0])) AND (isset($rec[0]["Datum"])) ){
1202  if( $rec[0]["Datum"] === "0000-00-00 00:00:00" ){
1203  $return["action"] = "insert";
1204  }
1205  }
1206  }
1207  $return["value"] = $err;
1208  return $return;
1209  } // end update or insert
1210  } // end if anonymous
1211 
1212  unset($poll);
1213  }
1214 
1221  public function klassenliste($au,$poll){
1222  if($poll->anonymous === false){
1223  // GET GROUP ID TO GROUP NAME MAPPING
1224  global $groups_whitelist;
1225  $group_mapping = $au->get_gid_to_name_mapping($groups_whitelist);
1226  // GET LIST OF EXISTING ENTRYS
1227  $sqrh = $this->dbh->query("SELECT `user`,`Datum` FROM `{$poll->ID}_results`");
1228  if ($sqrh === false){ // pollID_results existiert nicht ... hmm ...
1229  return;
1230  }
1231  $rec = $sqrh->fetchAll(PDO::FETCH_ASSOC);
1232  $username_list_old = array();
1233  $username_list_old_for_remove = array();
1234  foreach($rec as $ent){
1235  // do not remove if there ist data
1236  if($ent["Datum"] == "0000-00-00 00:00:00"){
1237  $username_list_old_for_remove[] = $ent["user"];
1238  }
1239  // even if there is data, the user exists in table and do not create any entry for him
1240  $username_list_old[] = $ent["user"];
1241  }
1242 
1243  if(in_array("*",$poll->groups)){
1244  $groups = array();
1245  $gg = array_keys($group_mapping);
1246  foreach($gg as $g){
1247  $groups[] = $g;
1248  }
1249 
1250  }else{
1251  $groups = $poll->groups;
1252  }
1253 
1254  if( sizeof($groups) != 0 ){
1255  // get user list of ALL users for specified group list
1256  $user_list_new = $au->get_all_users_from_grouplist($groups);
1257  if( sizeof($user_list_new) != 0){
1258  $username_list_new = array();
1259  foreach($user_list_new as $tmp_user){
1260  $username_list_new[] = $tmp_user["userName"];
1261  }
1262 
1263  // INSERT NEW USERS
1264  foreach($user_list_new as $user){
1265  if( !in_array($user["userName"],$username_list_old) ){
1266  $sql = "INSERT INTO `{$poll->ID}_results` (`user`,`Datum`,`name`,`group`,`Vorname`,`Nachname`) VALUES ('{$user["userName"]}','0000-00-00 00:00:00','{$user["fullName"]}','{$group_mapping[$user["groupID"]]}','{$user["givenName"]}','{$user["surName"]}')";
1267  $this->dbh->exec($sql);
1268  }
1269  }
1270 
1271  // REMOVE UNUSED USERS
1272  foreach($username_list_old_for_remove as $user_old){
1273  if( !in_array($user_old,$username_list_new) ){
1274  $sql = "DELETE FROM `{$poll->ID}_results` WHERE `{$poll->ID}_results`.`user` = '$user_old'";
1275  $this->dbh->exec($sql);
1276  }
1277  }
1278  } // end if new user list not empty
1279  } // end if poll groups set
1280  } // end if poll not anonymous
1281  }
1282 
1288  public function garbage_collect_result_table(poll $poll){
1289  $blacklist = array("Datum","user","group","name","Vorname","Nachname");
1290  $widget_list = array();
1291  $results_complete = array();
1292  $cols = array();
1293 
1294  // get columns in result table except those from the blacklist
1295  $sqh = $this->dbh->query("SHOW COLUMNS FROM `{$poll->ID}_results`");
1296  if($sqh != false){
1297  $rows = $sqh->fetchAll(PDO::FETCH_ASSOC);
1298  foreach($rows as $r){
1299  if(!in_array($r["Field"],$blacklist) ){
1300  $cols[] = $r["Field"];
1301  }
1302  }
1303  }else{
1304  return false;
1305  }
1306 
1307  // get all results
1308  $sqh = $this->dbh->query("SELECT * FROM `{$poll->ID}_results`");
1309  $results_complete = $sqh->fetchAll(PDO::FETCH_ASSOC);
1310 
1311  //get widget list
1312  foreach($poll->widget_list as $widget){
1313  if( ($widget instanceof input_widget) ){ // separator and label have no result
1314  if( $widget instanceof container ){ // containerwidgets have lists of widgets with input
1315  foreach( $widget->widget_list as $child ){
1316  $widget_list[] = $child->name;
1317  }
1318  if( $widget->direct_result === true ){
1319  //if( (!$widget instanceof matrix) and (!$widget instanceof textExt) ){ // matrix has no direct input , but radiobuttonlists etc have one
1320  $widget_list[] = $widget->name;
1321  }
1322  }else{ // all other widgets excluding containers
1323  $widget_list[] = $widget->name;
1324  }
1325  }
1326  }
1327 
1328  // check for orphaned columns
1329  // go through each column of the result table
1330  foreach($cols as $col){
1331  // if the column is not in the widget list
1332  if( ! in_array($col,$widget_list) ){
1333  // check if column is complete void
1334  $complete_void = true;
1335  foreach($results_complete as $row){
1336  $value = $row[$col];
1337  if( $value != ""){
1338  $complete_void = false;
1339  }
1340  }
1341  // if void -> purge
1342  if($complete_void === true){
1343  $sql = "ALTER TABLE `{$poll->ID}_results` DROP COLUMN `$col`";
1344  $this->dbh->exec($sql);
1345  } //end drop
1346  }//end if column not in w list
1347  }// end foreach cols
1348 
1349  return true;
1350  }
1351 
1357  public function get_visited_polls_for_user($user){
1358  $sqh = $this->dbh->query("SELECT polls_visited FROM `user_config` WHERE `user` = '$user'");
1359  if( $sqh !== false ){
1360  $res = $sqh->fetch(PDO::FETCH_ASSOC);
1361  if( $res !== false ){
1362  return explode(",",$res["polls_visited"]);
1363  }
1364  }
1365  return array();
1366  }
1367 
1373  public function garbage_collect_visited_polls($user){
1374  $visited = $this->get_visited_polls_for_user($user);
1375  $exist = array();
1376  $defunct = array();
1377 
1378  $sqh = $this->dbh->query("SELECT ID FROM `umfragen`");
1379  if( $sqh !== false ){
1380  $res = $sqh->fetchAll(PDO::FETCH_ASSOC);
1381  if( $res !== false ){
1382  foreach( $res as $row ){
1383  $exist[] = $row["ID"];
1384  }
1385  foreach( $visited as $p ){
1386  if( !in_array($p,$exist) ){
1387  $defunct[] = $p;
1388  }
1389  }
1390  }
1391  }
1392  // return all the entries from array1 that are not present array2
1393  return array_diff($visited,$defunct);
1394  }
1395 
1402  public function update_visited_polls($user,$pollID){
1403  // check if we need to update or insert the row
1404  $sqh = $this->dbh->query("SELECT polls_visited FROM `user_config` WHERE `user` = '$user'");
1405  $insert = false;
1406  if( $sqh !== false ){
1407  $res = $sqh->fetch(PDO::FETCH_ASSOC);
1408  if( $res === false ){
1409  $insert = true;
1410  }
1411  }
1412  // update the array
1413  $visited = $this->garbage_collect_visited_polls($user);
1414  $visited[]=$pollID;
1415  $visstr = implode(",",$visited);
1416  if( $insert ){
1417  $sqh = $this->dbh->exec("INSERT INTO `user_config` (`polls_visited`,`user`) VALUES ('$visstr','$user')");
1418  }else{
1419  $sqh = $this->dbh->exec("UPDATE `user_config` SET `polls_visited` = '$visstr' WHERE `user` = '$user'");
1420  }
1421  if( $sqh !== false ){
1422  return true;
1423  }
1424  return false;
1425  }
1426 
1433  public function remove_visited_poll($user,$pollID){
1434  $visited = $this->get_visited_polls_for_user($user);
1435  $visited = array_diff($visited,array($pollID));
1436  $visstr = implode(",",$visited);
1437  $sqh = $this->dbh->exec("UPDATE `user_config` SET `polls_visited` = '$visstr' WHERE `user` = '$user'");
1438  }
1439 
1440  /*---------------------------------
1441  *
1442  * UPDATE
1443  *
1444  --------------------------------*/
1450  function mass_update_widget($wlist){
1451  $data = array();
1452  if (sizeof($wlist)==0){
1453  return false;
1454  }
1455  foreach($wlist as $w){
1456  $w->check_vital_vars();
1457  if( !isset($w->value) ){
1458  $val = ""; // void value
1459  }else {$val = $w->value;}
1460 
1461  $cnf = "";
1462  if( isset($w->config) ){
1463  if( is_array($w->config)){
1464  $w->serialize_config();
1465  }
1466  if(is_string($w->config)){
1467  $cnf = $w->config;
1468  }else{
1469  $cnf = "";
1470  }
1471  }
1472 
1473  $data[] = array(
1474  ":t" => $w->typ,
1475  ":n" => $w->name,
1476  ":i" => $w->index,
1477  ":dn" => $w->dispName,
1478  ":req" => (int) $w->is_required,
1479  ":v" => $val,
1480  ":c" => $cnf,
1481  ":ID" => $w->ID
1482  );
1483  }
1484 
1485  #$pollID = mysql_real_escape_string($wlist[0]->pollID);
1486  $pollID = $wlist[0]->pollID;
1487  $sqh = $this->dbh->prepare("UPDATE `$pollID` SET `typ` = :t , `name` = :n ,`index` = :i ,`dispName` = :dn, `value` = :v, `config` = :c, `is_required` = :req WHERE `ID` = :ID");
1488  foreach($data as $d){
1489  $sqh->execute($d);
1490  }
1491 
1492  }
1493 
1501  function update_widget_field($w,$field,$data){
1502  // typ, name,index and pollID are required
1503  if ( !(isset($w->typ)) OR !(isset($w->name)) OR !(isset($w->index)) OR !(isset($w->pollID)) ){
1504  throw new varException("Widget vars not set");
1505  }
1506  if( ($w->typ == "") OR ($w->name == "") ){
1507  throw new varException("Widget vars empty");
1508  }
1509 
1510  $sqh = $this->dbh->prepare("UPDATE `{$w->pollID}` SET `$field` = :data WHERE `ID` = '{$w->ID}'");
1511  return $sqh->execute( array( ":data" => $data) );
1512 
1513  }
1514 
1522  public function update_poll_field($ID,$field,$data){
1523  $sqh = $this->dbh->prepare("UPDATE umfragen SET `$field` = :data WHERE `ID` = $ID");
1524  return $sqh->execute( array(":data" => $data) );
1525  }
1526 
1534  public function update_user_config_field($user,$field,$data){
1535  // check if we need to update or insert the row
1536  $sqh = $this->dbh->query("SELECT polls_visited FROM `user_config` WHERE `user` = '$user'");
1537  $insert = false;
1538  if( $sqh !== false ){
1539  $res = $sqh->fetch(PDO::FETCH_ASSOC);
1540  if( $res === false ){
1541  $insert = true;
1542  }
1543  }
1544 
1545  // do stuff
1546  if( $insert ){
1547  $sqh = $this->dbh->prepare("INSERT INTO user_config (`user`,`$field`) VALUES ('$user',:data)");
1548  }else{
1549  $sqh = $this->dbh->prepare("UPDATE user_config SET `$field` = :data WHERE `user` = '$user'");
1550  }
1551  return $sqh->execute( array(":data" => $data) );
1552  }
1553 
1561  public function email_stack_add($data){
1562  if( (isset($data["pollID"])) AND (isset($data["type"])) AND (isset($data["fullName"])) AND (isset($data["uname"])) ){
1563  $sqh = $this->dbh->prepare( "INSERT INTO `email_stack` (`pollID`,`type`,`fullName`,`uname`,`timestamp`) VALUES (:pollID,:type,:fullName,:uname,NOW())" );
1564  $data2 = array(
1565  ":pollID" => $data["pollID"],
1566  ":type" => $data["type"],
1567  ":fullName" => $data["fullName"],
1568  ":uname" => $data["uname"]
1569  );
1570  $sqh->execute($data2);
1571  }else{
1572  throw new exception("invalid data");
1573  }
1574  }
1575 
1580  public function email_stack_get(){
1581  $sqh = $this->dbh->query("SELECT * FROM `email_stack` ORDER BY `ID` DESC LIMIT 1");
1582  $res = $sqh->fetch(PDO::FETCH_ASSOC);
1583  if( (is_array($res)) AND (sizeof($res) == 0) ){
1584  $res = false;
1585  }
1586  return $res;
1587  }
1588 
1593  public function email_stack_get_all(){
1594  $sqh = $this->dbh->query("SELECT * FROM `email_stack` ORDER BY `ID` DESC");
1595  $res = $sqh->fetchAll(PDO::FETCH_ASSOC);
1596  return $res;
1597  }
1598 
1604  public function email_stack_del($ID){
1605  $this->dbh->exec("DELETE FROM `email_stack` WHERE `ID` = $ID");
1606  return true;
1607  }
1608 
1615  public function update_config_field($name,$value){
1616  $create = false;
1617  // check if we need to create a record or if we update
1618  $sqh = $this->dbh->query("SELECT * FROM `config` WHERE `name` = '$name'");
1619  if( $sqh != false){
1620  $res = $sqh->fetch(PDO::FETCH_ASSOC);
1621  if( (sizeof($res) == 0) OR ($res === false) ){
1622  $create = true;
1623  }
1624  }else{
1625  $create = true;
1626  }
1627 
1628  // set value
1629  if( $create === false ){
1630  $sqh = $this->dbh->prepare("UPDATE `config` SET `value` = :data WHERE `name` = '$name'");
1631  return $sqh->execute( array(":data" => $value) );
1632  }else{
1633  $sqh = $this->dbh->prepare("INSERT INTO `config` (`name`,`value`) VALUES (:name,:data)");
1634  return $sqh->execute( array(":name" => $name,":data" => $value) );
1635  }
1636  }
1637 
1638 
1645  public function load_user_config_field($user,$field){
1646  if( is_array($field) ){
1647  $fieldstr = "";
1648  foreach( $field as $f){
1649  $fieldstr = $fieldstr.",".$f;
1650  }
1651  $fieldstr = trim($fieldstr,",");
1652  $sqh = $this->dbh->query("SELECT $fieldstr FROM `user_config` WHERE `user`='$user'");
1653  }else{
1654  $sqh = $this->dbh->query("SELECT $field FROM `user_config` WHERE `user`='$user'");
1655  }
1656  if( $sqh !== false ){
1657  $res = $sqh->fetch(PDO::FETCH_ASSOC);
1658  if( (is_array($res)) AND (sizeof($res) == 1) ){
1659  return $res[$field];
1660  }else{
1661  return $res;
1662  }
1663  }else{
1664  return ;
1665  }
1666  }
1667 
1674  public function update_widget_name($widget,$new_name){
1675  if( (isset($widget->long_result)) AND ($widget->long_result === true) ){
1676  $this->rename_result_column($widget->pollID,true,$widget->name,$new_name);
1677  }else{
1678  $this->rename_result_column($widget->pollID,false,$widget->name,$new_name);
1679  }
1680  $this->update_widget_field($widget,"name",$new_name);
1681  }
1682 
1688  public function convert_polltype_S2A($poll){
1689  if( $poll->type == POLLTYPE_SIMPLE ){
1690  foreach( $poll->widget_list as $widget ){
1691  if( ($widget instanceof input_widget) ){
1692  $this->update_widget_name($widget,$widget->name);
1693  if( $widget instanceof container ){
1694  foreach( $widget->widget_list as $child ){
1695  $this->update_widget_name($child,$child->name);
1696  } // end foreach question
1697  } // end if matrix
1698  } // end ifnot output widget
1699  } // end foreach widget
1700  $this->update_poll_field($poll->ID,"type",POLLTYPE_ADVANCED);
1701  } // end if simple mode
1702  }
1703 
1709  public function convert_polltype_A2S($poll){
1710  if( $poll->type == POLLTYPE_ADVANCED ){
1711  foreach( $poll->widget_list as $widget ){
1712  if( ($widget instanceof input_widget) ){
1713  $this->update_widget_name($widget,strval($widget->ID));
1714  if( $widget instanceof container ){
1715  foreach( $widget->widget_list as $child ){
1716  $this->update_widget_name($child,strval($child->ID));
1717  } // end foreach question
1718  } // end if matrix
1719  }// end ifnot output widget
1720  }// end foreach widget
1721  $this->update_poll_field($poll->ID,"type",POLLTYPE_SIMPLE);
1722  }//end if advanced mode
1723  }
1724 
1730  public function is_results_empty($pid){
1731  $blacklist = array("Datum","user","group","name");
1732 
1733  $sql = "SELECT * FROM `{$pid}_results`";
1734  $sqh = $this->dbh->query($sql);
1735  if( $sqh !== false ){
1736  $res = $sqh->fetchAll(PDO::FETCH_ASSOC);
1737  $data = array();
1738 
1739  if( sizeof($res) == 0 ){
1740  return true;
1741  }else{
1742  foreach( $res as $r ){
1743  $keys = array_keys($r);
1744  for( $i=0 ; $i<sizeof($r) ; ++$i ){
1745  if( !in_array($keys[$i],$blacklist) ){
1746  if( (!isset($r[ $keys[$i] ])) OR ($r[ $keys[$i] ] == "") OR ($r[ $keys[$i] ] == "NULL") OR ($r[ $keys[$i] ] == "null") ){
1747 
1748  }else{
1749  $data[] = $r;
1750  }
1751  // exit if we find one data!
1752  if( sizeof($data) != 0 ){
1753  return false;
1754  }
1755  } // end if not blacklist
1756  } // end foreach db col
1757  } // end foreach db row
1758  if( sizeof($data) == 0 ){
1759  return true;
1760  }
1761  } // end if data
1762  }else{
1763  return true;
1764  } // end if poll exists
1765  return false;
1766  }
1767 
1773  public function anonymize_results($pollID){
1774  $sql = "UPDATE `{$pollID}_results` SET `user` = NULL , group = NULL , name = NULL, `Vorname` = NULL, `Nachname` = NULL";
1775  }
1776 
1777  /*---------------------------------
1778  *
1779  * DELETE
1780  *
1781  --------------------------------*/
1787  function drop_poll($id){
1788  $sql = "DROP TABLE `$id`";
1789  $this->dbh->exec($sql);
1790  return true;
1791  }
1792 
1798  function drop_poll_results($id){
1799  $sql = "DROP TABLE `{$id}_results`";
1800  $this->dbh->exec($sql);
1801  return true;
1802  }
1803 
1809  function delete_poll($id){
1810  $sql = "DELETE FROM `umfragen`.`umfragen` WHERE `umfragen`.`ID` = $id";
1811  $this->dbh->exec($sql);
1812  return true;
1813  }
1814 
1821  function delete_widget($pollID,$wID){
1822 
1823  //"ALTER TABLE `142_results` DROP `1`"
1824 
1825  $sql = "DELETE FROM `umfragen`.`$pollID` WHERE `ID` = $wID";
1826  $this->dbh->exec($sql);
1827  return true;
1828  }
1829 
1836  function delete_result_for_user_list($pollID,$username_list){
1837  if(is_array($username_list)){
1838  $list = "";
1839  $data = array();
1840  $c=0;
1841  foreach($username_list as $user){
1842  $list=$list.":$c,";
1843  $data[":$c"] = $user;
1844  ++$c;
1845  $this->remove_visited_poll($user,$pollID);
1846  }
1847  $list = trim($list,",");
1848  $sqh = $this->dbh->prepare("DELETE FROM `umfragen`.`{$pollID}_results` WHERE user IN ($list)");
1849  $sqh->execute($data);
1850  return true;
1851  }else{
1852  return false;
1853  }
1854  }
1855 
1861  function delete_all_result($pollID){
1862  $sqh = $this->dbh->query("SELECT `user` FROM `umfragen`.`{$pollID}_results`");
1863  $old_usernames = $sqh->fetchAll();
1864  if( $old_usernames !== false ){
1865  foreach( $old_usernames as $row){
1866  $this->remove_visited_poll($old_usernames["user"],$pollID);
1867  }
1868  }
1869  $sql = "DELETE FROM `umfragen`.`{$pollID}_results`";
1870  $this->dbh->exec($sql);
1871  return true;
1872  }
1873 }
1874 // ! no ending newLine
1875 ?>