"survey_list.php", "name" => get_lang('Survey')); Display::display_header($tool_name); switch($screen) { // first screen : base case '1' : echo '

'.get_lang('SelectDataYouWantToCompare').'

'; echo get_lang('HelpComparative'); $questions = SurveyManager::listQuestions($surveyid); $menu_left = ''; /* $menu_right = ''; */ echo '
'.$menu_left.'
'; break; //second screen : details case '2' : echo '

'.get_lang('PreciseWhatYouWantToCompare').'

'; $left_questions = $_POST['left']; $right_questions = $_POST['right']; // prepare the left menu $menu_left = ''; // prepare the right menu /* $menu_right = ''; */ echo '
'.$menu_left.'
'; break; // screen 3 : results case '3' : echo '

'.get_lang('ComparativeResults').'

'; $params = array_merge($_POST['left'] , $_POST['right']); $tbl_user_survey = Database::get_main_table(MAIN_SURVEY_USER_TABLE); $tbl_questions = Database::get_course_table('questions'); $tbl_answers = Database::get_course_table('survey_report'); //$which_answers = 'WHERE qid'; //$which_questions = 'AND qid IN ()'; $names = $emails = $organisations = $questions = array(); $excel_file_name = 'export_survey-'.$surveyid.$db_name.'.csv'; echo ''.get_lang('ExportInExcel').''; 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('^]*>(.*)

','\\1', $row[0]); $row[0] = eregi_replace('(<[^ ]*) (style=."."[^>]*)(>)','\\1\\3', $row[0]); $row[0] = eregi_replace('(<[^ ]*) (style=.""[^>]*)(>)','\\1\\3', $row[0]); $row[0] = eregi_replace('(<[^ ]*)( style=."[^"]*")([^>]*)(>)','\\1\\2\\4', $row[0]); $html_questions .= '
'; $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('^]*>(.*)

','\\1', $row[0]); $row[0] = eregi_replace('(<[^ ]*) (style=."."[^>]*)(>)','\\1\\3', $row[0]); $row[0] = eregi_replace('(<[^ ]*) (style=.""[^>]*)(>)','\\1\\3', $row[0]); $row[0] = eregi_replace('(<[^ ]*)( style=."[^"]*")([^>]*)(>)','\\1\\2\\4', $row[0]); $html_questions .= '
'; $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 ''; $excel .= get_lang('Name').';'; } if(count($emails)) { $nbColumns++; $select[] = 'email'; $which_emails = ' AND email IN ('.implode(',',$emails).')'; echo ''; $excel .= get_lang('EmailAddress').';'; } if(count($organisations)) { $nbColumns++; $select[] = 'organization'; $which_organisations = ' AND organization IN ('.implode(',',$organisations).')'; echo ''; $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 ''; for($i=0; $i<=$nbColumns; $i++){ echo ''; if($i!=0) $excel .= str_replace(array("\r","\n"),"",$user_survey[$i]['answer'].';'); } $excel .= "\r\n"; echo ''; } $max = max(array_keys($stats)); echo ''; echo ''; for($i=1 ; $i<$max+1; $i++){ echo ''; } echo ''; echo '
'.stripslashes($row[0]).''.stripslashes($row[0]).''.get_lang('Name').''.get_lang('EmailAddress').''.get_lang('Organisation').'
'.(!empty($user_survey[$i]['answer']) ? $user_survey[$i]['answer'] : '-').'
Stats'; $valeurs = array_count_values($stats[$i]); foreach($valeurs as $key=>$value){ if(!empty($key)) echo $key.' : '.$value.' votes
'; } echo '
'; $archivePath=api_get_path(SYS_PATH).$archiveDirName.'/'; $handle = fopen($archivePath.$excel_file_name, 'w'); fwrite($handle, $excel); fclose($handle); chmod($archivePath.$excel_file_name, 0755); break; } ?>