"survey_list.php", "name" => get_lang('Survey')); Display::display_header($tool_name); switch($screen) { // first screen : base case '1' : echo '
'; $with_answers = false; $answers_required = $header_order = array(); $i=0; $nbColumns = 0; foreach($params as $param){ $param = explode('|',$param); switch ($param[0]){ // which type ? case 'name' : $names[]='"'.$param[1].'"'; break; case 'email' : $emails[]='"'.$param[1].'"'; break; case 'organisation' : $organisations[]='"'.$param[1].'"'; break; case 'question' : $questions[]='"'.$param[1].'"'; $sql = 'SELECT caption FROM '.$tbl_questions.' WHERE qid='.$param[1]; $rs = api_sql_query($sql); if($row = mysql_fetch_array($rs)){ $row[0] = eregi_replace('^ | '.stripslashes($row[0]).' | '; $excel_questions .= stripslashes($row[0]).';'; } $header_order[$param[1]] = $i; $i++; break; case 'answer' : $answers_required[] = $param; $sql = 'SELECT caption FROM '.$tbl_questions.' as questions INNER JOIN '.$tbl_answers.' as answers ON answers.qid = questions.qid AND answers.qid='.$param[1]; $rs = api_sql_query($sql); if($row = mysql_fetch_array($rs)){ if(!in_array('"'.$param[1].'"',$questions)){ $row[0] = eregi_replace('^'.stripslashes($row[0]).' | '; $excel_questions .= stripslashes($row[0]).';'; $questions[]='"'.$param[1].'"'; $header_order[$param[1]] = $i; $i++; } } break; } } $select = array(); $excel = ''; if(count($names)){ $nbColumns++; $select[] = 'CONCAT(lastname," ",firstname) as name'; $which_names = ' AND CONCAT(lastname," ",firstname) IN ('.implode(',',$names).')'; echo ''.get_lang('Name').' | '; $excel .= get_lang('Name').';'; } if(count($emails)) { $nbColumns++; $select[] = 'email'; $which_emails = ' AND email IN ('.implode(',',$emails).')'; echo ''.get_lang('EmailAddress').' | '; $excel .= get_lang('EmailAddress').';'; } if(count($organisations)) { $nbColumns++; $select[] = 'organization'; $which_organisations = ' AND organization IN ('.implode(',',$organisations).')'; echo ''.get_lang('Organisation').' | '; $excel .= get_lang('Organisation').';'; } if(count($questions)){ $nbColumns += count($questions); $select[] = 'questions.qid, caption, answer'; $which_questions = ' AND questions.qid IN ('.implode(',',$questions).')'; echo $html_questions; } $excel .= $excel_questions; echo ''; $excel .="\r\n"; $sql = 'SELECT user_survey.id as id_user_survey, '.implode(',',$select).' FROM '.$tbl_answers.' as answers,'.$tbl_user_survey.' as user_survey,'.$tbl_questions.' as questions WHERE answers.qid = questions.qid AND answers.user_id = user_survey.user_id AND user_survey.survey_id='.$surveyid.' AND user_survey.db_name="'.$db_name.'" '; $sql .= $which_names.' '.$which_emails.' '.$which_organisations.' '.$which_questions; $rs = api_sql_query($sql, __FILE__, __LINE__); $answers_to_keep = $user_survey_done = array(); while($row = mysql_fetch_array($rs)) { $ok = false; if(count($answers_required)==0){ $ok=true; } else { foreach($answers_required as $param){ $key = $param[1]; $answer = $param[2]; $sql = 'SELECT 1 FROM '.$tbl_answers.' as answers INNER JOIN '.$tbl_user_survey.' as user_survey ON user_survey.id = '.$row['id_user_survey'].' AND user_survey.survey_id='.$surveyid.' AND user_survey.db_name="'.$db_name.'" WHERE answers.qid='.$key.' AND answers.answer = "'.$answer.'"'; $rs2 = api_sql_query($sql); if(mysql_num_rows($rs2)>0){ $ok = true; } } } if($ok){ $answers_to_keep[] = $row; } } $stats = array(); $users_survey = array(); foreach($answers_to_keep as $answer) { if(in_array($answer['answer'],array('a1','a2','a3','a4','a5','a6','a7','a8','a9','a10',))){ $sql = 'SELECT '.$answer['answer'].' FROM '.$db_name.'.questions WHERE survey_id = '.$surveyid.' AND questions.qid='.$answer['qid']; $rsAnswer = api_sql_query($sql, __FILE__,__LINE__); $answer['answer'] = mysql_result($rsAnswer,0); } $i=0; $users_survey[$answer['id_user_survey']][0]['answer'] = $answer['id_user_survey']; $i++; if(count($names)){ $users_survey[$answer['id_user_survey']][$i]['answer'] = $answer['name']; $i++; } if(count($emails)){ $users_survey[$answer['id_user_survey']][$i]['answer'] = $answer['email']; $i++; } if(count($organisations)){ $users_survey[$answer['id_user_survey']][$i]['answer'] = $answer['organization']; $i++; } if(count($questions)){ $users_survey[$answer['id_user_survey']][$i+$header_order[$answer['qid']]]['answer'] = $answer['answer']; $stats[$i+$header_order[$answer['qid']]][] = $answer['answer']; } } foreach($users_survey as $user_survey){ echo '
'.(!empty($user_survey[$i]['answer']) ? $user_survey[$i]['answer'] : '-').' | '; if($i!=0) $excel .= str_replace(array("\r","\n"),"",$user_survey[$i]['answer'].';'); } $excel .= "\r\n"; echo '|
Stats | '; for($i=1 ; $i<$max+1; $i++){ echo '';
$valeurs = array_count_values($stats[$i]);
foreach($valeurs as $key=>$value){
if(!empty($key))
echo $key.' : '.$value.' votes '; } echo ' | ';
}
echo '