alert("HI");'; */ //split up results to extend statistics -> NOT WORKING YET! DO NOT ENABLE THIS! //$showcombinedresults = 0; //don't call this script directly! if (isset($_REQUEST['homedir'])) {die('You cannot start this script directly');} /** * Generates statistics * * @param int $surveyid The survey id * @param mixed $allfields * @param mixed $q2show * @param mixed $usegraph * @param string $outputType Optional - Can be xls, html or pdf - Defaults to pdf * @param string $pdfOutput Sets the target for the PDF output: DD=File download , F=Save file to local disk * @param string $statlangcode Lamguage for statistics * @param mixed $browse Show browse buttons * @return buffer */ function generate_statistics($surveyid, $allfields, $q2show='all', $usegraph=0, $outputType='pdf', $pdfOutput='DD',$statlangcode=null, $browse = true) { //$allfields =""; global $connect, $dbprefix, $clang, $rooturl, $rootdir, $homedir, $homeurl, $tempdir, $tempurl, $scriptname, $chartfontfile, $chartfontsize, $admintheme, $pdfdefaultfont, $pdffontsize; $fieldmap=createFieldMap($surveyid, "full"); if (is_null($statlangcode)) { $statlang=$clang; } else { $statlang = new limesurvey_lang($statlangcode); } /* * this variable is used in the function shortencode() which cuts off a question/answer title * after $maxchars and shows the rest as tooltip (in html mode) */ $maxchars = 13; //we collect all the html-output within this variable $statisticsoutput =''; /** * $outputType: html || pdf || */ /** * get/set Survey Details */ //no survey ID? -> come and get one if (!isset($surveyid)) {$surveyid=returnglobal('sid');} //Get an array of codes of all available languages in this survey $surveylanguagecodes = GetAdditionalLanguagesFromSurveyID($surveyid); $surveylanguagecodes[] = GetBaseLanguageFromSurveyID($surveyid); // Set language for questions and answers to base language of this survey $language=$statlangcode; if ($usegraph==1) { //for creating graphs we need some more scripts which are included here require_once(dirname(__FILE__).'/../classes/pchart/pchart/pChart.class'); require_once(dirname(__FILE__).'/../classes/pchart/pchart/pData.class'); require_once(dirname(__FILE__).'/../classes/pchart/pchart/pCache.class'); $MyCache = new pCache($tempdir.'/'); //pick the best font file if font setting is 'auto' if ($chartfontfile=='auto') { $chartfontfile='vera.ttf'; if ( $language=='ar') { $chartfontfile='KacstOffice.ttf'; } elseif ($language=='fa' ) { $chartfontfile='KacstFarsi.ttf'; } } } if($q2show=='all' ) { $summarySql=" SELECT gid, parent_qid, qid, type " ." FROM {$dbprefix}questions WHERE parent_qid=0" ." AND sid=$surveyid "; $summaryRs = db_execute_assoc($summarySql); foreach($summaryRs as $field) { $myField = $surveyid."X".$field['gid']."X".$field['qid']; // Multiple Options get special treatment if ($field['type'] == "M") {$myField = "M$myField";} if ($field['type'] == "P") {$myField = "P$myField";} //numerical input will get special treatment (arihtmetic mean, standard derivation, ...) if ($field['type'] == "N") {$myField = "N$myField";} if ($field['type'] == "Q") {$myField = "Q$myField";} // textfields get special treatment if ($field['type'] == "S" || $field['type'] == "T" || $field['type'] == "U"){$myField = "T$myField";} //statistics for Date questions are not implemented yet. if ($field['type'] == "D") {$myField = "D$myField";} if ($field['type'] == "F" || $field['type'] == "H") { //Get answers. We always use the answer code because the label might be too long elsewise $query = "SELECT code, answer FROM ".db_table_name("answers")." WHERE qid='".$field['qid']."' AND scale_id=0 AND language='{$language}' ORDER BY sortorder, answer"; $result = db_execute_num($query) or safe_die ("Couldn't get answers!
$query
".$connect->ErrorMsg()); $counter2=0; //check all the answers while ($row=$result->FetchRow()) { $myField = "$myField{$row[0]}"; } //$myField = "{$surveyid}X{$flt[1]}X{$flt[0]}{$row[0]}[]"; } if($q2show=='all') $summary[]=$myField; //$allfields[]=$myField; } } else { // This gets all the 'to be shown questions' from the POST and puts these into an array if (!is_array($q2show)) $summary = returnglobal('summary'); else $summary = $q2show; //print_r($_POST); //if $summary isn't an array we create one if (isset($summary) && !is_array($summary)) { $summary = explode("+", $summary); } } /** * pdf Config */ if($outputType=='pdf') { require_once('classes/tcpdf/config/lang/eng.php'); require_once('classes/tcpdf/mypdf.php'); // create new PDF document $pdf = new MyPDF(); $pdf->SetFont($pdfdefaultfont,'',$pdffontsize); $surveyInfo = getSurveyInfo($surveyid,$language); // set document information $pdf->SetCreator(PDF_CREATOR); $pdf->SetAuthor('LimeSurvey'); $pdf->SetTitle('Statistic Survey '.$surveyid); $pdf->SetSubject($surveyInfo['surveyls_title']); $pdf->SetKeywords('LimeSurvey, Statistics, Survey '.$surveyid.''); $pdf->SetDisplayMode('fullpage', 'two'); // set default header data $pdf->SetHeaderData("statistics.png", 10, $statlang->gT("Quick statistics") , $statlang->gT("Survey")." ".$surveyid." '".$surveyInfo['surveyls_title']."'"); // set header and footer fonts $pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN)); $pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA)); // set default monospaced font $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED); //set margins $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT); $pdf->SetHeaderMargin(PDF_MARGIN_HEADER); $pdf->SetFooterMargin(PDF_MARGIN_FOOTER); //set auto page breaks $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM); //set image scale factor $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO); //set some language-dependent strings $pdf->setLanguageArray($l); } if($outputType=='xls') { /** * Initiate the Spreadsheet_Excel_Writer */ include_once(dirname(__FILE__)."/classes/pear/Spreadsheet/Excel/Writer.php"); if($pdfOutput=='F') $workbook = new Spreadsheet_Excel_Writer($tempdir.'/statistic-survey'.$surveyid.'.xls'); else $workbook = new Spreadsheet_Excel_Writer(); $workbook->setVersion(8); // Inform the module that our data will arrive as UTF-8. // Set the temporary directory to avoid PHP error messages due to open_basedir restrictions and calls to tempnam("", ...) if (!empty($tempdir)) { $workbook->setTempDir($tempdir); } if($pdfOutput!='F') $workbook->send('statistic-survey'.$surveyid.'.xls'); // Creating the first worksheet $sheet =& $workbook->addWorksheet(utf8_decode('results-survey'.$surveyid)); $sheet->setInputEncoding('utf-8'); $sheet->setColumn(0,20,20); $separator="~|"; /**XXX*/ } /** * Start generating */ // creates array of post variable names for (reset($_POST); $key=key($_POST); next($_POST)) { $postvars[]=$key;} $aQuestionMap=array(); foreach ($fieldmap as $field) { if(isset($field['qid']) && $field['qid']!='') $aQuestionMap[]=$field['sid'].'X'.$field['gid'].'X'.$field['qid']; } /* * Iterate through postvars to create "nice" data for SQL later. * * Remember there might be some filters applied which have to be put into an SQL statement */ if(isset($postvars)) foreach ($postvars as $pv) { //Only do this if there is actually a value for the $pv if (in_array($pv, $allfields) || in_array(substr($pv,1),$aQuestionMap) || in_array($pv,$aQuestionMap)) { $firstletter=substr($pv,0,1); /* * these question types WON'T be handled here: * M = Multiple Options * T - Long Free Text * Q - Multiple Short Text * D - Date * N - Numerical Input * K - Multiple Numerical Input */ if ($pv != "sid" && $pv != "display" && $firstletter != "M" && $firstletter != "P" && $firstletter != "T" && $firstletter != "Q" && $firstletter != "D" && $firstletter != "N" && $firstletter != "K" && $pv != "summary" && substr($pv, 0, 2) != "id" && substr($pv, 0, 9) != "datestamp") //pull out just the fieldnames { //put together some SQL here $thisquestion = db_quote_id($pv)." IN ("; foreach ($_POST[$pv] as $condition) { $thisquestion .= "'$condition', "; } $thisquestion = substr($thisquestion, 0, -2) . ")"; //we collect all the to be selected data in this array $selects[]=$thisquestion; } //M - Multiple Options //P - Multiple Options with comments elseif ($firstletter == "M" || $firstletter == "P") { $mselects=array(); //create a list out of the $pv array list($lsid, $lgid, $lqid) = explode("X", $pv); $aquery="SELECT title FROM ".db_table_name("questions")." WHERE parent_qid=$lqid AND language='{$language}' and scale_id=0 ORDER BY question_order"; $aresult=db_execute_num($aquery) or safe_die ("Couldn't get subquestions
$aquery
".$connect->ErrorMsg()); // go through every possible answer while ($arow=$aresult->FetchRow()) { // only add condition if answer has been chosen if (in_array($arow[0], $_POST[$pv])) { $mselects[]=db_quote_id(substr($pv, 1, strlen($pv)).$arow[0])." = 'Y'"; } } if ($mselects) { $thismulti=implode(" OR ", $mselects); $selects[]="($thismulti)"; $mselects = ""; } } //N - Numerical Input //K - Multiple Numerical Input elseif ($firstletter == "N" || $firstletter == "K") { //value greater than if (substr($pv, strlen($pv)-1, 1) == "G" && $_POST[$pv] != "") { $selects[]=db_quote_id(substr($pv, 1, -1))." > ".sanitize_int($_POST[$pv]); } //value less than if (substr($pv, strlen($pv)-1, 1) == "L" && $_POST[$pv] != "") { $selects[]=db_quote_id(substr($pv, 1, -1))." < ".sanitize_int($_POST[$pv]); } } //"id" is a built in field, the unique database id key of each response row elseif (substr($pv, 0, 2) == "id") { if (substr($pv, strlen($pv)-1, 1) == "G" && $_POST[$pv] != "") { $selects[]=db_quote_id(substr($pv, 0, -1))." > '".$_POST[$pv]."'"; } if (substr($pv, strlen($pv)-1, 1) == "L" && $_POST[$pv] != "") { $selects[]=db_quote_id(substr($pv, 0, -1))." < '".$_POST[$pv]."'"; } } //T - Long Free Text //Q - Multiple Short Text elseif (($firstletter == "T" || $firstletter == "Q" ) && $_POST[$pv] != "") { $selectSubs = array(); //We intepret and * and % as wildcard matches, and use ' OR ' and , as the seperators $pvParts = explode(",",str_replace('*','%', str_replace(' OR ',',',$_POST[$pv]))); if(is_array($pvParts) AND count($pvParts)){ foreach($pvParts AS $pvPart){ $selectSubs[]=db_quote_id(substr($pv, 1, strlen($pv)))." LIKE '".trim($pvPart)."'"; } if(count($selectSubs)){ $selects[] = ' ('.implode(' OR ',$selectSubs).') '; } } } //D - Date elseif ($firstletter == "D" && $_POST[$pv] != "") { //Date equals if (substr($pv, -1, 1) == "=") { $selects[]=db_quote_id(substr($pv, 1, strlen($pv)-2))." = '".$_POST[$pv]."'"; } else { //date less than if (substr($pv, -1, 1) == "<") { $selects[]= db_quote_id(substr($pv, 1, strlen($pv)-2)) . " >= '".$_POST[$pv]."'"; } //date greater than if (substr($pv, -1, 1) == ">") { $selects[]= db_quote_id(substr($pv, 1, strlen($pv)-2)) . " <= '".$_POST[$pv]."'"; } } } //check for datestamp of given answer elseif (substr($pv, 0, 9) == "datestamp") { //timestamp equals $formatdata=getDateFormatData($_SESSION['dateformat']); if (substr($pv, -1, 1) == "E" && !empty($_POST[$pv])) { $datetimeobj = new Date_Time_Converter($_POST[$pv], $formatdata['phpdate'].' H:i'); $_POST[$pv]=$datetimeobj->convert("Y-m-d"); $selects[] = db_quote_id('datestamp')." >= '".$_POST[$pv]." 00:00:00' and ".db_quote_id('datestamp')." <= '".$_POST[$pv]." 23:59:59'"; } else { //timestamp less than if (substr($pv, -1, 1) == "L" && !empty($_POST[$pv])) { $datetimeobj = new Date_Time_Converter($_POST[$pv], $formatdata['phpdate'].' H:i'); $_POST[$pv]=$datetimeobj->convert("Y-m-d H:i:s"); $selects[]= db_quote_id('datestamp')." < '".$_POST[$pv]."'"; } //timestamp greater than if (substr($pv, -1, 1) == "G" && !empty($_POST[$pv])) { $datetimeobj = new Date_Time_Converter($_POST[$pv], $formatdata['phpdate'].' H:i'); $_POST[$pv]=$datetimeobj->convert("Y-m-d H:i:s"); $selects[]= db_quote_id('datestamp')." > '".$_POST[$pv]."'"; } } } } else { $statisticsoutput .= ""; } } //end foreach -> loop through filter options to create SQL //count number of answers $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid"); //if incompleted answers should be filtert submitdate has to be not null if (incompleteAnsFilterstate() == "inc") {$query .= " WHERE submitdate is null";} elseif (incompleteAnsFilterstate() == "filter") {$query .= " WHERE submitdate is not null";} $result = db_execute_num($query) or safe_die ("Couldn't get total
$query
".$connect->ErrorMsg()); //$total = total number of answers while ($row=$result->FetchRow()) {$total=$row[0];} //are there any filters that have to be taken care of? if (isset($selects) && $selects) { //filter incomplete answers? if (incompleteAnsFilterstate() == "filter" || incompleteAnsFilterstate() == "inc") {$query .= " AND ";} else {$query .= " WHERE ";} //add filter criteria to SQL $query .= implode(" AND ", $selects); } //$_POST['sql'] is a post field that is sent from the statistics script to the export script in order // to export just those results filtered by this statistics script. It can also be passed to the statistics // script to filter from external scripts. elseif (!empty($_POST['sql']) && !isset($_POST['id='])) { $newsql=substr($_POST['sql'], strpos($_POST['sql'], "WHERE")+5, strlen($_POST['sql'])); //for debugging only //$query = $_POST['sql']; //filter incomplete answers? if (incompleteAnsFilterstate() == "inc") {$query .= " AND ".$newsql;} elseif (incompleteAnsFilterstate() == "filter") {$query .= " AND ".$newsql;} else {$query .= " WHERE ".$newsql;} } //get me some data Scotty $result=db_execute_num($query) or safe_die("Couldn't get results
$query
".$connect->ErrorMsg()); //put all results into $results while ($row=$result->FetchRow()) {$results=$row[0];} if ($total) { $percent=sprintf("%01.2f", ($results/$total)*100); } switch($outputType) { case "xls": $xlsRow = 0; $sheet->write($xlsRow,0,$statlang->gT("Number of records in this query:")); $sheet->write($xlsRow,1,$results); ++$xlsRow; $sheet->write($xlsRow,0,$statlang->gT("Total records in survey:")); $sheet->write($xlsRow,1,$total); if($total) { ++$xlsRow; $sheet->write($xlsRow,0,$statlang->gT("Percentage of total:")); $sheet->write($xlsRow,1,$percent."%"); } break; case 'pdf': // add summary to pdf $array = array(); //$array[] = array($statlang->gT("Results"),""); $array[] = array($statlang->gT("Number of records in this query:"), $results); $array[] = array($statlang->gT("Total records in survey:"), $total); if($total) $array[] = array($statlang->gT("Percentage of total:"), $percent."%"); $pdf->addPage('P','A4'); $pdf->Bookmark($pdf->delete_html($statlang->gT("Results")), 0, 0); $pdf->titleintopdf($statlang->gT("Results"),$statlang->gT("Survey")." ".$surveyid); $pdf->tableintopdf($array); $pdf->addPage('P','A4'); break; case 'html': $statisticsoutput .= "
\n\n" ."\t\n" ."\t' ."\n" ."\t' ."\n"; //only calculate percentage if $total is set if ($total) { $percent=sprintf("%01.2f", ($results/$total)*100); $statisticsoutput .= "\t' ."\n"; } $statisticsoutput .="
".$statlang->gT("Results")."
".$statlang->gT("Number of records in this query:").'$results
".$statlang->gT("Total records in survey:").'$total
".$statlang->gT("Percentage of total:").'$percent%
\n"; break; default: break; } //put everything from $selects array into a string connected by AND if (isset ($selects) && $selects) {$sql=implode(" AND ", $selects);} elseif (!empty($newsql)) {$sql = $newsql;} if (!isset($sql) || !$sql) {$sql="NULL";} //only continue if we have something to output if ($results > 0) { if($outputType=='html' && $browse === true) { //add a buttons to browse results $statisticsoutput .= "
\n" ."\t\t

\n" ."\t\t\t\n" ."\t\t\t\n" ."\t\t\t\n" ."\t\t

"; } } //end if (results > 0) //Show Summary results if (isset($summary) && $summary) { //let's run through the survey $runthrough=$summary; //START Chop up fieldname and find matching questions //GET LIST OF LEGIT QIDs FOR TESTING LATER $lq = "SELECT DISTINCT qid FROM ".db_table_name("questions")." WHERE sid=$surveyid and parent_qid=0"; $lr = db_execute_assoc($lq); //loop through the IDs while ($lw = $lr->FetchRow()) { //this creates an array of question id's' $legitqids[] = $lw['qid']; } //loop through all selected questions foreach ($runthrough as $rt) { $firstletter = substr($rt, 0, 1); // 1. Get answers for question ############################################################## //M - Multiple Options, therefore multiple fields if ($firstletter == "M" || $firstletter == "P") { //get SGQ data list($qsid, $qgid, $qqid) = explode("X", substr($rt, 1, strlen($rt)), 3); //select details for this question $nquery = "SELECT title, type, question, parent_qid, other FROM ".db_table_name("questions")." WHERE language='{$language}' AND parent_qid=0 AND qid='$qqid'"; $nresult = db_execute_num($nquery) or safe_die ("Couldn't get question
$nquery
".$connect->ErrorMsg()); //loop through question data while ($nrow=$nresult->FetchRow()) { $qtitle=$nrow[0]; $qtype=$nrow[1]; $qquestion=FlattenText($nrow[2]); $qlid=$nrow[3]; $qother=$nrow[4]; } //1. Get list of answers $query="SELECT title, question FROM ".db_table_name("questions")." WHERE parent_qid='$qqid' AND language='{$language}' and scale_id=0 ORDER BY question_order"; $result=db_execute_num($query) or safe_die("Couldn't get list of subquestions for multitype
$query
".$connect->ErrorMsg()); //loop through multiple answers while ($row=$result->FetchRow()) { $mfield=substr($rt, 1, strlen($rt))."$row[0]"; //create an array containing answer code, answer and fieldname(??) $alist[]=array("$row[0]", FlattenText($row[1]), $mfield); } //check "other" field. is it set? if ($qother == "Y") { $mfield=substr($rt, 1, strlen($rt))."other"; //create an array containing answer code, answer and fieldname(??) $alist[]=array($statlang->gT("Other"), $statlang->gT("Other"), $mfield); } } //S - Short Free Text //T - Long Free Text elseif ($firstletter == "T" || $firstletter == "S") //Short and long text { //search for key $fld = substr($rt, 1, strlen($rt)); $fielddata=$fieldmap[$fld]; //get SGQA IDs $qsid=$fielddata['sid']; $qgid=$fielddata['gid']; $qqid=$fielddata['qid']; list($qanswer, $qlid)=!empty($fielddata['aid']) ? explode("_", $fielddata['aid']) : array("", ""); //get SGQ data //list($qsid, $qgid, $qqid) = explode("X", substr($rt, 1, strlen($rt)), 3); //get question data $nquery = "SELECT title, type, question, other, parent_qid FROM ".db_table_name("questions")." WHERE parent_qid=0 AND qid='$qqid' AND language='{$language}'"; $nresult = db_execute_num($nquery) or safe_die("Couldn't get text question
$nquery
".$connect->ErrorMsg()); //loop through question data while ($nrow=$nresult->FetchRow()) { $qtitle=FlattenText($nrow[0]); $qtype=$nrow[1]; $qquestion=FlattenText($nrow[2]); $nlid=$nrow[4]; } $mfield=substr($rt, 1, strlen($rt)); //Text questions either have an answer, or they don't. There's no other way of quantising the results. // So, instead of building an array of predefined answers like we do with lists & other types, // we instead create two "types" of possible answer - either there is a response.. or there isn't. // This question type then can provide a % of the question answered in the summary. $alist[]=array("Answers", $statlang->gT("Answer"), $mfield); $alist[]=array("NoAnswer", $statlang->gT("No answer"), $mfield); } //Multiple short text elseif ($firstletter == "Q") { //get SGQ data list($qsid, $qgid, $qqid) = explode("X", substr($rt, 1, strlen($rt)), 3); //separating another ID $tmpqid=substr($qqid, 0, strlen($qqid)-1); //check if we have legid QIDs. if not create them by substringing while (!in_array ($tmpqid,$legitqids)) $tmpqid=substr($tmpqid, 0, strlen($tmpqid)-1); //length of QID $qidlength=strlen($tmpqid); //we somehow get the answer code (see SQL later) from the $qqid $qaid=substr($qqid, $qidlength, strlen($qqid)-$qidlength); //get some question data $nquery = "SELECT title, type, question, other FROM ".db_table_name("questions")." WHERE qid='".substr($qqid, 0, $qidlength)."' AND parent_qid=0 AND language='{$language}'"; $nresult = db_execute_num($nquery) or safe_die("Couldn't get text question
$nquery
".$connect->ErrorMsg()); //more substrings $count = substr($qqid, strlen($qqid)-1); //loop through question data while ($nrow=$nresult->FetchRow()) { $qtitle=FlattenText($nrow[0]).'-'.$count; $qtype=$nrow[1]; $qquestion=FlattenText($nrow[2]); } //get answers $qquery = "SELECT title as code, question as answer FROM ".db_table_name("questions")." WHERE parent_qid='".substr($qqid, 0, $qidlength)."' AND title='$qaid' AND language='{$language}' ORDER BY question_order"; $qresult=db_execute_num($qquery) or safe_die ("Couldn't get answer details (Array 5p Q)
$qquery
".$connect->ErrorMsg()); //loop through answer data while ($qrow=$qresult->FetchRow()) { //store each answer here $atext=FlattenText($qrow[1]); } //add this to the question title $qtitle .= " [$atext]"; //even more substrings... $mfield=substr($rt, 1, strlen($rt)); //Text questions either have an answer, or they don't. There's no other way of quantising the results. // So, instead of building an array of predefined answers like we do with lists & other types, // we instead create two "types" of possible answer - either there is a response.. or there isn't. // This question type then can provide a % of the question answered in the summary. $alist[]=array("Answers", $statlang->gT("Answer"), $mfield); $alist[]=array("NoAnswer", $statlang->gT("No answer"), $mfield); } //RANKING OPTION THEREFORE CONFUSING elseif ($firstletter == "R") { //getting the needed IDs somehow $lengthofnumeral=substr($rt, strpos($rt, "-")+1, 1); list($qsid, $qgid, $qqid) = explode("X", substr($rt, 1, strpos($rt, "-")-($lengthofnumeral+1)), 3); //get question data $nquery = "SELECT title, type, question FROM ".db_table_name("questions")." WHERE parent_qid=0 AND qid='$qqid' AND language='{$language}'"; $nresult = db_execute_num($nquery) or safe_die ("Couldn't get question
$nquery
".$connect->ErrorMsg()); //loop through question data while ($nrow=$nresult->FetchRow()) { $qtitle=FlattenText($nrow[0]). " [".substr($rt, strpos($rt, "-")-($lengthofnumeral), $lengthofnumeral)."]"; $qtype=$nrow[1]; $qquestion=FlattenText($nrow[2]). "[".$statlang->gT("Ranking")." ".substr($rt, strpos($rt, "-")-($lengthofnumeral), $lengthofnumeral)."]"; } //get answers $query="SELECT code, answer FROM ".db_table_name("answers")." WHERE qid='$qqid' AND scale_id=0 AND language='{$language}' ORDER BY sortorder, answer"; $result=db_execute_num($query) or safe_die("Couldn't get list of answers for multitype
$query
".$connect->ErrorMsg()); //loop through answers while ($row=$result->FetchRow()) { //create an array containing answer code, answer and fieldname(??) $mfield=substr($rt, 1, strpos($rt, "-")-1); $alist[]=array("$row[0]", FlattenText($row[1]), $mfield); } } //N = numerical input //K = multiple numerical input elseif ($firstletter == "N" || $firstletter == "K") //NUMERICAL TYPE { //Zero handling if (!isset($excludezeros)) //If this hasn't been set, set it to on as default: { $excludezeros=1; } //check last character, greater/less/equals don't need special treatment if (substr($rt, -1) == "G" || substr($rt, -1) == "L" || substr($rt, -1) == "=") { //DO NOTHING } else { //create SGQ identifier list($qsid, $qgid, $qqid) = explode("X", $rt, 3); //multiple numerical input if($firstletter == "K") { // This is a multiple numerical question so we need to strip of the answer id to find the question title $tmpqid=substr($qqid, 0, strlen($qqid)-1); //did we get a valid ID? while (!in_array ($tmpqid,$legitqids)) $tmpqid=substr($tmpqid, 0, strlen($tmpqid)-1); //check lenght of ID $qidlength=strlen($tmpqid); //get answer ID from qid $qaid=substr($qqid, $qidlength, strlen($qqid)-$qidlength); //get question details from DB $nquery = "SELECT title, type, question, qid, parent_qid FROM ".db_table_name("questions")." WHERE parent_qid=0 AND qid='".substr($qqid, 0, $qidlength)."' AND language='{$language}'"; $nresult = db_execute_num($nquery) or safe_die("Couldn't get text question
$nquery
".$connect->ErrorMsg()); } //probably question type "N" = numerical input else { //we can use the qqid without any editing $nquery = "SELECT title, type, question, qid, parent_qid FROM ".db_table_name("questions")." WHERE parent_qid=0 AND qid='$qqid' AND language='{$language}'"; $nresult = db_execute_num($nquery) or safe_die ("Couldn't get question
$nquery
".$connect->ErrorMsg()); } //loop through results while ($nrow=$nresult->FetchRow()) { $qtitle=FlattenText($nrow[0]); //clean up title $qtype=$nrow[1]; $qquestion=FlattenText($nrow[2]); $qiqid=$nrow[3]; $qlid=$nrow[4]; } //Get answer texts for multiple numerical if(substr($rt, 0, 1) == "K") { //get answer data $atext=$connect->GetOne("SELECT question FROM ".db_table_name("questions")." WHERE parent_qid='{$qiqid}' AND scale_id=0 AND title='{$qaid}' AND language='{$language}'"); //put single items in brackets at output $qtitle .= " [$atext]"; } //outputting switch($outputType) { case 'xls': $headXLS = array(); $tableXLS = array(); $footXLS = array(); $xlsTitle = sprintf($statlang->gT("Field summary for %s"),html_entity_decode($qtitle,ENT_QUOTES,'UTF-8')); $xlsDesc = html_entity_decode($qquestion,ENT_QUOTES,'UTF-8'); ++$xlsRow; ++$xlsRow; ++$xlsRow; $sheet->write($xlsRow, 0,$xlsTitle); ++$xlsRow; $sheet->write($xlsRow, 0,$xlsDesc); $headXLS[] = array($statlang->gT("Calculation"),$statlang->gT("Result")); ++$xlsRow; $sheet->write($xlsRow, 0,$statlang->gT("Calculation")); $sheet->write($xlsRow, 1,$statlang->gT("Result")); break; case 'pdf': $headPDF = array(); $tablePDF = array(); $footPDF = array(); $pdfTitle = sprintf($statlang->gT("Field summary for %s"),html_entity_decode($qtitle,ENT_QUOTES,'UTF-8')); $titleDesc = html_entity_decode($qquestion,ENT_QUOTES,'UTF-8'); $headPDF[] = array($statlang->gT("Calculation"),$statlang->gT("Result")); break; case 'html': $statisticsoutput .= "\n\n" ."\t\n" ."\t\n" ."\t\n\t\t\n" ."\t\t\n" ."\t\n"; break; default: break; } //this field is queried using mathematical functions $fieldname=substr($rt, 1, strlen($rt)); //special treatment for MS SQL databases if ($connect->databaseType == 'odbc_mssql' || $connect->databaseType == 'odbtp' || $connect->databaseType == 'mssql_n' || $connect->databaseType == 'mssqlnative') { //standard deviation $query = "SELECT STDEVP(".db_quote_id($fieldname)."*1) as stdev"; } //other databases (MySQL, Postgres) else { //standard deviation $query = "SELECT STDDEV(".db_quote_id($fieldname).") as stdev"; } //sum $query .= ", SUM(".db_quote_id($fieldname)."*1) as sum"; //average $query .= ", AVG(".db_quote_id($fieldname)."*1) as average"; //min $query .= ", MIN(".db_quote_id($fieldname)."*1) as minimum"; //max $query .= ", MAX(".db_quote_id($fieldname)."*1) as maximum"; //Only select responses where there is an actual number response, ignore nulls and empties (if these are included, they are treated as zeroes, and distort the deviation/mean calculations) //special treatment for MS SQL databases if ($connect->databaseType == 'odbc_mssql' || $connect->databaseType == 'odbtp' || $connect->databaseType == 'mssql_n' || $connect->databaseType == 'mssqlnative') { //no NULL/empty values please $query .= " FROM ".db_table_name("survey_$surveyid")." WHERE ".db_quote_id($fieldname)." IS NOT NULL"; if(!$excludezeros) { //NO ZERO VALUES $query .= " AND (".db_quote_id($fieldname)." <> 0)"; } } //other databases (MySQL, Postgres) else { //no NULL/empty values please $query .= " FROM ".db_table_name("survey_$surveyid")." WHERE ".db_quote_id($fieldname)." IS NOT NULL"; if(!$excludezeros) { //NO ZERO VALUES $query .= " AND (".db_quote_id($fieldname)." != 0)"; } } //filter incomplete answers if set if (incompleteAnsFilterstate() == "inc") {$query .= " AND submitdate is null";} elseif (incompleteAnsFilterstate() == "filter") {$query .= " AND submitdate is not null";} //$sql was set somewhere before if ($sql != "NULL") {$query .= " AND $sql";} //execute query $result=db_execute_assoc($query) or safe_die("Couldn't do maths testing
$query
".$connect->ErrorMsg()); //get calculated data while ($row=$result->FetchRow()) { //put translation of mean and calculated data into $showem array $showem[]=array($statlang->gT("Sum"), $row['sum']); $showem[]=array($statlang->gT("Standard deviation"), round($row['stdev'],2)); $showem[]=array($statlang->gT("Average"), round($row['average'],2)); $showem[]=array($statlang->gT("Minimum"), $row['minimum']); //Display the maximum and minimum figures after the quartiles for neatness $maximum=$row['maximum']; $minimum=$row['minimum']; } //CALCULATE QUARTILES //get data $query ="SELECT ".db_quote_id($fieldname)." FROM ".db_table_name("survey_$surveyid")." WHERE ".db_quote_id($fieldname)." IS NOT null"; //NO ZEROES if(!$excludezeros) { $query .= " AND ".db_quote_id($fieldname)." != 0"; } //filtering enabled? if (incompleteAnsFilterstate() == "inc") {$query .= " AND submitdate is null";} elseif (incompleteAnsFilterstate() == "filter") {$query .= " AND submitdate is not null";} //if $sql values have been passed to the statistics script from another script, incorporate them if ($sql != "NULL") {$query .= " AND $sql";} //execute query $result=$connect->Execute($query) or safe_die("Disaster during median calculation
$query
".$connect->ErrorMsg()); $querystarter="SELECT ".db_quote_id($fieldname)." FROM ".db_table_name("survey_$surveyid")." WHERE ".db_quote_id($fieldname)." IS NOT null"; //No Zeroes if(!$excludezeros) { $querystart .= " AND ".db_quote_id($fieldname)." != 0"; } //filtering enabled? if (incompleteAnsFilterstate() == "inc") {$querystarter .= " AND submitdate is null";} elseif (incompleteAnsFilterstate() == "filter") {$querystarter .= " AND submitdate is not null";} //if $sql values have been passed to the statistics script from another script, incorporate them if ($sql != "NULL") {$querystarter .= " AND $sql";} //we just count the number of records returned $medcount=$result->RecordCount(); //put the total number of records at the beginning of this array array_unshift($showem, array($statlang->gT("Count"), $medcount)); //no more comment from Mazi regarding the calculation // Calculating only makes sense with more than one result if ($medcount>1) { //1ST QUARTILE (Q1) $q1=(1/4)*($medcount+1); $q1b=(int)((1/4)*($medcount+1)); $q1c=$q1b-1; $q1diff=$q1-$q1b; $total=0; // fix if there are too few values to evaluate. if ($q1c<0) {$q1c=0;} if ($q1 != $q1b) { //ODD NUMBER $query = $querystarter . " ORDER BY ".db_quote_id($fieldname)."*1 "; $result=db_select_limit_assoc($query, 2, $q1c) or safe_die("1st Quartile query failed
".$connect->ErrorMsg()); while ($row=$result->FetchRow()) { if ($total == 0) {$total=$total-$row[$fieldname];} else {$total=$total+$row[$fieldname];} $lastnumber=$row[$fieldname]; } $q1total=$lastnumber-((1-$q1diff)*$total); if ($q1total < $minimum) {$q1total=$minimum;} $showem[]=array($statlang->gT("1st quartile (Q1)"), $q1total); } else { //EVEN NUMBER $query = $querystarter . " ORDER BY ".db_quote_id($fieldname)."*1 "; $result=db_select_limit_assoc($query,1, $q1c) or safe_die ("1st Quartile query failed
".$connect->ErrorMsg()); while ($row=$result->FetchRow()) { $showem[]=array($statlang->gT("1st quartile (Q1)"), $row[$fieldname]); } } $total=0; //MEDIAN (Q2) $median=(1/2)*($medcount+1); $medianb=(int)((1/2)*($medcount+1)); $medianc=$medianb-1; $mediandiff=$median-$medianb; if ($median != $medianb) { //remainder $query = $querystarter . " ORDER BY ".db_quote_id($fieldname)."*1 "; $result=db_select_limit_assoc($query,2, $medianc) or safe_die("What a complete mess with the remainder
$query
".$connect->ErrorMsg()); while ( $row=$result->FetchRow()) {$total=$total+$row[$fieldname]; } $showem[]=array($statlang->gT("2nd quartile (Median)"), $total/2); } else { //EVEN NUMBER $query = $querystarter . " ORDER BY ".db_quote_id($fieldname)."*1 "; $result=db_select_limit_assoc($query,1, $medianc-1) or safe_die("What a complete mess
$query
".$connect->ErrorMsg()); while ($row=$result->FetchRow()) { $showem[]=array($statlang->gT("Median value"), $row[$fieldname]); } } $total=0; //3RD QUARTILE (Q3) $q3=(3/4)*($medcount+1); $q3b=(int)((3/4)*($medcount+1)); $q3c=$q3b-1; $q3diff=$q3-$q3b; if ($q3 != $q3b) { $query = $querystarter . " ORDER BY ".db_quote_id($fieldname)."*1 "; $result = db_select_limit_assoc($query,2,$q3c) or safe_die("3rd Quartile query failed
".$connect->ErrorMsg()); while ($row=$result->FetchRow()) { if ($total == 0) {$total=$total-$row[$fieldname];} else {$total=$total+$row[$fieldname];} $lastnumber=$row[$fieldname]; } $q3total=$lastnumber-((1-$q3diff)*$total); if ($q3total < $maximum) {$q1total=$maximum;} $showem[]=array($statlang->gT("3rd quartile (Q3)"), $q3total); } else { $query = $querystarter . " ORDER BY ".db_quote_id($fieldname)."*1"; $result = db_select_limit_assoc($query,1, $q3c) or safe_die("3rd Quartile even query failed
".$connect->ErrorMsg()); while ($row=$result->FetchRow()) { $showem[]=array($statlang->gT("3rd quartile (Q3)"), $row[$fieldname]); } } $total=0; $showem[]=array($statlang->gT("Maximum"), $maximum); //output results foreach ($showem as $shw) { switch($outputType) { case 'xls': ++$xlsRow; $sheet->write($xlsRow, 0,html_entity_decode($shw[0],ENT_QUOTES,'UTF-8')); $sheet->write($xlsRow, 1,html_entity_decode($shw[1],ENT_QUOTES,'UTF-8')); $tableXLS[] = array($shw[0],$shw[1]); break; case 'pdf': $tablePDF[] = array(html_entity_decode($shw[0],ENT_QUOTES,'UTF-8'),html_entity_decode($shw[1],ENT_QUOTES,'UTF-8')); break; case 'html': $statisticsoutput .= "\t\n" ."\t\t\n" ."\t\t\n" ."\t\n"; break; default: break; } } switch($outputType) { case 'xls': ++$xlsRow; $sheet->write($xlsRow, 0,$statlang->gT("Null values are ignored in calculations")); ++$xlsRow; $sheet->write($xlsRow, 0,sprintf($statlang->gT("Q1 and Q3 calculated using %s"), $statlang->gT("minitab method"))); $footXLS[] = array($statlang->gT("Null values are ignored in calculations")); $footXLS[] = array(sprintf($statlang->gT("Q1 and Q3 calculated using %s"), $statlang->gT("minitab method"))); break; case 'pdf': $footPDF[] = array($statlang->gT("Null values are ignored in calculations")); $footPDF[] = array(sprintf($statlang->gT("Q1 and Q3 calculated using %s"), "".$statlang->gT("minitab method")."")); $pdf->addPage('P','A4'); $pdf->Bookmark($pdf->delete_html($qquestion), 1, 0); $pdf->titleintopdf($pdfTitle,$titleDesc); $pdf->headTable($headPDF, $tablePDF); $pdf->tablehead($footPDF); break; case 'html': //footer of question type "N" $statisticsoutput .= "\t\n" ."\t\t\n" ."\t\n
".sprintf($statlang->gT("Field summary for %s"),$qtitle).":" ."
$qquestion
" .$statlang->gT("Calculation")."" .$statlang->gT("Result")."
$shw[0]$shw[1]
\n" ."\t\t\t".$statlang->gT("Null values are ignored in calculations")."
\n" ."\t\t\t".sprintf($statlang->gT("Q1 and Q3 calculated using %s"), "".$statlang->gT("minitab method")."") ."
\n" ."\t\t
\n"; break; default: break; } //clean up unset($showem); } //end if (enough results?) //not enough (<1) results for calculation else { switch($outputType) { case 'xls': $tableXLS = array(); $tableXLS[] = array($statlang->gT("Not enough values for calculation")); ++$xlsRow; $sheet->write($xlsRow, 0, $statlang->gT("Not enough values for calculation")); break; case 'pdf': $tablePDF = array(); $tablePDF[] = array($statlang->gT("Not enough values for calculation")); $pdf->addPage('P','A4'); $pdf->Bookmark($pdf->delete_html($qquestion), 1, 0); $pdf->titleintopdf($pdfTitle,$titleDesc); $pdf->equalTable($tablePDF); break; case 'html': //output $statisticsoutput .= "\t\n" ."\t\t".$statlang->gT("Not enough values for calculation")."\n" ."\t\n
\n"; break; default: break; } unset($showem); } } //end else -> check last character, greater/less/equals don't need special treatment } //end else-if -> multiple numerical types //is there some "id", "datestamp" or "D" within the type? elseif (substr($rt, 0, 2) == "id" || substr($rt, 0, 9) == "datestamp" || ($firstletter == "D")) { /* * DON'T show anything for date questions * because there aren't any statistics implemented yet! * * See bug report #2539 and * feature request #2620 */ } // NICE SIMPLE SINGLE OPTION ANSWERS else { //search for key $fielddata=$fieldmap[$rt]; //print_r($fielddata); //get SGQA IDs $qsid=$fielddata['sid']; $qgid=$fielddata['gid']; $qqid=$fielddata['qid']; $qanswer=$fielddata['aid']; //question type $qtype=$fielddata['type']; //question string $qastring=$fielddata['question']; //question ID $rqid=$qqid; //get question data $nquery = "SELECT title, type, question, qid, parent_qid, other FROM ".db_table_name("questions")." WHERE qid='{$rqid}' AND parent_qid=0 and language='{$language}'"; $nresult = db_execute_num($nquery) or safe_die ("Couldn't get question
$nquery
".$connect->ErrorMsg()); //loop though question data while ($nrow=$nresult->FetchRow()) { $qtitle=FlattenText($nrow[0]); $qtype=$nrow[1]; $qquestion=FlattenText($nrow[2]); $qiqid=$nrow[3]; $qparentqid=$nrow[4]; $qother=$nrow[5]; } //check question types switch($qtype) { //Array of 5 point choices (several items to rank!) case "A": //get data $qquery = "SELECT title, question FROM ".db_table_name("questions")." WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order"; $qresult=db_execute_num($qquery) or safe_die ("Couldn't get answer details (Array 5p Q)
$qquery
".$connect->ErrorMsg()); //loop through results while ($qrow=$qresult->FetchRow()) { //5-point array for ($i=1; $i<=5; $i++) { //add data $alist[]=array("$i", "$i"); } //add counter $atext=FlattenText($qrow[1]); } //list IDs and answer codes in brackets $qquestion .= "
\n[".$atext."]"; $qtitle .= "($qanswer)"; break; //Array of 10 point choices //same as above just with 10 items case "B": $qquery = "SELECT title, question FROM ".db_table_name("questions")." WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order"; $qresult=db_execute_num($qquery) or safe_die ("Couldn't get answer details (Array 10p Q)
$qquery
".$connect->ErrorMsg()); while ($qrow=$qresult->FetchRow()) { for ($i=1; $i<=10; $i++) { $alist[]=array("$i", "$i"); } $atext=FlattenText($qrow[1]); } $qquestion .= "
\n[".$atext."]"; $qtitle .= "($qanswer)"; break; //Array of Yes/No/$statlang->gT("Uncertain") case "C": $qquery = "SELECT title, question FROM ".db_table_name("questions")." WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order"; $qresult=db_execute_num($qquery) or safe_die ("Couldn't get answer details
$qquery
".$connect->ErrorMsg()); //loop thorugh results while ($qrow=$qresult->FetchRow()) { //add results $alist[]=array("Y", $statlang->gT("Yes")); $alist[]=array("N", $statlang->gT("No")); $alist[]=array("U", $statlang->gT("Uncertain")); $atext=FlattenText($qrow[1]); } //output $qquestion .= "
\n[".$atext."]"; $qtitle .= "($qanswer)"; break; //Array of Yes/No/$statlang->gT("Uncertain") //same as above case "E": $qquery = "SELECT title, question FROM ".db_table_name("questions")." WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order"; $qresult=db_execute_num($qquery) or safe_die ("Couldn't get answer details
$qquery
".$connect->ErrorMsg()); while ($qrow=$qresult->FetchRow()) { $alist[]=array("I", $statlang->gT("Increase")); $alist[]=array("S", $statlang->gT("Same")); $alist[]=array("D", $statlang->gT("Decrease")); $atext=FlattenText($qrow[1]); } $qquestion .= "
\n[".$atext."]"; $qtitle .= "($qanswer)"; break; case ";": //Array (Multi Flexi) (Text) list($qacode, $licode)=explode("_", $qanswer); $qquery = "SELECT title, question FROM ".db_table_name("questions")." WHERE parent_qid='$qiqid' AND title='$qacode' AND language='{$language}' ORDER BY question_order"; //echo $qquery."
"; $qresult=db_execute_num($qquery) or die ("Couldn't get answer details
$qquery
".$connect->ErrorMsg()); while ($qrow=$qresult->FetchRow()) { $fquery = "SELECT * FROM ".db_table_name("answers")." WHERE qid='{$qiqid}' AND scale_id=0 AND code = '{$licode}' AND language='{$language}'ORDER BY sortorder, code"; $fresult = db_execute_assoc($fquery); while ($frow=$fresult->FetchRow()) { $alist[]=array($frow['code'], $frow['answer']); $ltext=$frow['answer']; } $atext=FlattenText($qrow[1]); } $qquestion .= "
\n[".$atext."] [".$ltext."]"; $qtitle .= "($qanswer)"; break; case ":": //Array (Multiple Flexi) (Numbers) $qidattributes=getQuestionAttributes($qiqid); if (trim($qidattributes['multiflexible_max'])!='') { $maxvalue=$qidattributes['multiflexible_max']; } else { $maxvalue=10; } if (trim($qidattributes['multiflexible_min'])!='') { $minvalue=$qidattributes['multiflexible_min']; } else { $minvalue=1; } if (trim($qidattributes['multiflexible_step'])!='') { $stepvalue=$qidattributes['multiflexible_step']; } else { $stepvalue=1; } if ($qidattributes['multiflexible_checkbox']!=0) { $minvalue=0; $maxvalue=1; $stepvalue=1; } for($i=$minvalue; $i<=$maxvalue; $i+=$stepvalue) { $alist[]=array($i, $i); } $qquestion .= "
\n[".$fielddata['subquestion1']."] [".$fielddata['subquestion2']."]"; list($myans, $mylabel)=explode("_", $qanswer); $qtitle .= "[$myans][$mylabel]"; break; case "F": //Array of Flexible case "H": //Array of Flexible by Column $qquery = "SELECT title, question FROM ".db_table_name("questions")." WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order"; $qresult=db_execute_num($qquery) or safe_die ("Couldn't get answer details
$qquery
".$connect->ErrorMsg()); //loop through answers while ($qrow=$qresult->FetchRow()) { //this question type uses its own labels $fquery = "SELECT * FROM ".db_table_name("answers")." WHERE qid='{$qiqid}' AND scale_id=0 AND language='{$language}'ORDER BY sortorder, code"; $fresult = db_execute_assoc($fquery); //add code and title to results for outputting them later while ($frow=$fresult->FetchRow()) { $alist[]=array($frow['code'], FlattenText($frow['answer'])); } //counter $atext=FlattenText($qrow[1]); } //output $qquestion .= "
\n[".$atext."]"; $qtitle .= "($qanswer)"; break; case "G": //Gender $alist[]=array("F", $statlang->gT("Female")); $alist[]=array("M", $statlang->gT("Male")); break; case "Y": //Yes\No $alist[]=array("Y", $statlang->gT("Yes")); $alist[]=array("N", $statlang->gT("No")); break; case "I": //Language // Using previously defined $surveylanguagecodes array of language codes foreach ($surveylanguagecodes as $availlang) { $alist[]=array($availlang, getLanguageNameFromCode($availlang,false)); } break; case "5": //5 Point (just 1 item to rank!) for ($i=1; $i<=5; $i++) { $alist[]=array("$i", "$i"); } break; case "1": //array (dual scale) $sSubquestionQuery = "SELECT question FROM ".db_table_name("questions")." WHERE parent_qid='$qiqid' AND title='$qanswer' AND language='{$language}' ORDER BY question_order"; $sSubquestion=FlattenText($connect->GetOne($sSubquestionQuery)); //get question attributes $qidattributes=getQuestionAttributes($qqid); //check last character -> label 1 if (substr($rt,-1,1) == 0) { //get label 1 $fquery = "SELECT * FROM ".db_table_name("answers")." WHERE qid='{$qqid}' AND scale_id=0 AND language='{$language}' ORDER BY sortorder, code"; //header available? if (trim($qidattributes['dualscale_headerA'])!='') { //output $labelheader= "[".$qidattributes['dualscale_headerA']."]"; } //no header else { $labelheader =''; } //output $labelno = "Label 1"; } //label 2 else { //get label 2 $fquery = "SELECT * FROM ".db_table_name("answers")." WHERE qid='{$qqid}' AND scale_id=1 AND language='{$language}' ORDER BY sortorder, code"; //header available? if (trim($qidattributes['dualscale_headerB'])!='') { //output $labelheader= "[".$qidattributes['dualscale_headerB']."]"; } //no header else { $labelheader =''; } //output $labelno = "Label 2"; } //get data $fresult = db_execute_assoc($fquery); //put label code and label title into array while ($frow=$fresult->FetchRow()) { $alist[]=array($frow['code'], FlattenText($frow['answer'])); } //adapt title and question $qtitle = $qtitle." [".$sSubquestion."][".$labelno."]"; $qquestion = $qastring .$labelheader; break; default: //default handling //get answer code and title $qquery = "SELECT code, answer FROM ".db_table_name("answers")." WHERE qid='$qqid' AND scale_id=0 AND language='{$language}' ORDER BY sortorder, answer"; $qresult = db_execute_num($qquery) or safe_die ("Couldn't get answers list
$qquery
".$connect->ErrorMsg()); //put answer code and title into array while ($qrow=$qresult->FetchRow()) { $alist[]=array("$qrow[0]", FlattenText($qrow[1])); } //handling for "other" field for list radio or list drowpdown if (($qtype == "L" || $qtype == "!") && $qother == "Y") { //add "other" $alist[]=array($statlang->gT("Other"),$statlang->gT("Other"),$fielddata['fieldname'].'other'); } } //end switch question type //moved because it's better to have "no answer" at the end of the list instead of the beginning //put data into array $alist[]=array("", $statlang->gT("No answer")); } //end else -> single option answers //foreach ($alist as $al) {$statisticsoutput .= "$al[0] - $al[1]
";} //debugging line //foreach ($fvalues as $fv) {$statisticsoutput .= "$fv | ";} //debugging line //2. Collect and Display results ####################################################################### if (isset($alist) && $alist) //Make sure there really is an answerlist, and if so: { // this will count the answers considered completed $TotalCompleted = 0; switch($outputType) { case 'xls': $xlsTitle = sprintf($statlang->gT("Field summary for %s"),html_entity_decode($qtitle,ENT_QUOTES,'UTF-8')); $xlsDesc = html_entity_decode($qquestion,ENT_QUOTES,'UTF-8'); ++$xlsRow; ++$xlsRow; ++$xlsRow; $sheet->write($xlsRow, 0,$xlsTitle); ++$xlsRow; $sheet->write($xlsRow, 0,$xlsDesc); $tableXLS = array(); $footXLS = array(); break; case 'pdf': $pdfTitle = $pdf->delete_html(sprintf($statlang->gT("Field summary for %s"),html_entity_decode($qtitle,ENT_QUOTES,'UTF-8'))); $titleDesc = $pdf->delete_html(html_entity_decode($qquestion,ENT_QUOTES,'UTF-8')); $pdf->addPage('P','A4'); $pdf->Bookmark($pdf->delete_html($qquestion), 1, 0); $pdf->titleintopdf($pdfTitle,$titleDesc); $tablePDF = array(); $footPDF = array(); break; case 'html': //output $statisticsoutput .= "\n" ."\t\n" ."\t\n" ."\t\n\t\t\n" ."\t\t\n" ."\t\t\n" ."\t\t\n" ."\t\n"; break; default: break; } $showheadline = false; } else { switch($outputType) { case 'xls': $headXLS = array(); $headXLS[] = array($statlang->gT("Answer"),$statlang->gT("Count"),$statlang->gT("Percentage")); ++$xlsRow; $sheet->write($xlsRow,0,$statlang->gT("Answer")); $sheet->write($xlsRow,1,$statlang->gT("Count")); $sheet->write($xlsRow,2,$statlang->gT("Percentage")); break; case 'pdf': $headPDF = array(); $headPDF[] = array($statlang->gT("Answer"),$statlang->gT("Count"),$statlang->gT("Percentage")); break; case 'html': //three columns $statisticsoutput .= "".$statlang->gT("Answer")."\n" ."\t\t\n" ."\t\t\n" ."\t\n"; break; default: break; } $showheadline = false; } } //text for answer column is always needed $fname="$al[1] ($al[0])"; //these question types get special treatment by $showaggregateddata if($qtype == "5" || $qtype == "A") { //put non-edited data in here because $row will be edited later $grawdata[]=$row[0]; $showaggregated_indice=count($grawdata) - 1; $showaggregated_indice_table[$showaggregated_indice]="aggregated"; $showaggregated_indice=-1; //keep in mind that we already added data (will be checked later) $justadded = true; //we need a counter because we want to sum up certain values //reset counter if 5 items have passed if(!isset($testcounter) || $testcounter >= 4) { $testcounter = 0; } else { $testcounter++; } //beside the known percentage value a new aggregated value should be shown //therefore this item is marked in a certain way if($testcounter == 0 ) //add 300 to original value { //HACK: add three times the total number of results to the value //This way we get a 300 + X percentage which can be checked later $row[0] += (3*$results); } //the third value should be shown twice later -> mark it if($testcounter == 2) //add 400 to original value { //HACK: add four times the total number of results to the value //This way there should be a 400 + X percentage which can be checked later $row[0] += (4*$results); } //the last value aggregates the data of item 4 + item 5 later if($testcounter == 4 ) //add 200 to original value { //HACK: add two times the total number of results to the value //This way there should be a 200 + X percentage which can be checked later $row[0] += (2*$results); } } //end if -> question type = "5"/"A" } //end if -> show aggregated data //handling what's left else { if(!isset($showheadline) || $showheadline != false) { switch($outputType) { case 'xls': $headXLS = array(); $headXLS[] = array($statlang->gT("Answer"),$statlang->gT("Count"),$statlang->gT("Percentage")); ++$xlsRow; $sheet->write($xlsRow,0,$statlang->gT("Answer")); $sheet->write($xlsRow,1,$statlang->gT("Count")); $sheet->write($xlsRow,2,$statlang->gT("Percentage")); break; case 'pdf': $headPDF = array(); $headPDF[] = array($statlang->gT("Answer"),$statlang->gT("Count"),$statlang->gT("Percentage")); break; case 'html': //three columns $statisticsoutput .= "".$statlang->gT("Answer")."\n" ."\t\t\n" ."\t\t\n" ."\t\n"; break; default: break; } $showheadline = false; } //answer text $fname="$al[1] ($al[0])"; } //are there some results to play with? if ($results > 0) { //calculate percentage $gdata[] = ($row[0]/$results)*100; } //no results else { //no data! $gdata[] = "N/A"; } //only add this if we don't handle question type "5"/"A" if(!isset($justadded)) { //put absolute data into array $grawdata[]=$row[0]; } else { //unset to handle "no answer" data correctly unset($justadded); } //put question title and code into array $label[]=$fname; //put only the code into the array $justcode[]=$al[0]; //edit labels and put them into antoher array $lbl[] = wordwrap(FlattenText("$al[1] ($row[0])"), 25, "\n"); // NMO 2009-03-24 $lblrtl[] = utf8_strrev(wordwrap(FlattenText("$al[1] )$row[0]("), 25, "\n")); // NMO 2009-03-24 } //end while -> loop through results } //end foreach -> loop through answer data //no filtering of incomplete answers and NO multiple option questions //if ((incompleteAnsFilterstate() != "filter") and ($qtype != "M") and ($qtype != "P")) //error_log("TIBO ".print_r($showaggregated_indice_table,true)); if (($qtype != "M") and ($qtype != "P")) { //is the checkbox "Don't consider NON completed responses (only works when Filter incomplete answers is Disable)" checked? //if (isset($_POST["noncompleted"]) and ($_POST["noncompleted"] == "on") && (isset($showaggregateddata) && $showaggregateddata == 0)) // TIBO: TODO WE MUST SKIP THE FOLLOWING SECTION FOR TYPE A and 5 when // showaggreagated data is set and set to 1 if (isset($_POST["noncompleted"]) and ($_POST["noncompleted"] == "on") ) { //counter $i=0; while (isset($gdata[$i])) { if (isset($showaggregated_indice_table[$i]) && $showaggregated_indice_table[$i]=="aggregated") { // do nothing, we don't rewrite aggregated results // or at least I don't know how !!! (lemeur) } else { //we want to have some "real" data here if ($gdata[$i] != "N/A") { //calculate percentage $gdata[$i] = ($grawdata[$i]/$TotalCompleted)*100; } } //increase counter $i++; } //end while (data available) } //end if -> noncompleted checked //noncompleted is NOT checked else { //calculate total number of incompleted records $TotalIncomplete = $results - $TotalCompleted; //output if ((incompleteAnsFilterstate() != "filter")) { $fname=$statlang->gT("Not completed or Not displayed"); } else { $fname=$statlang->gT("Not displayed"); } //we need some data if ($results > 0) { //calculate percentage $gdata[] = ($TotalIncomplete/$results)*100; } //no data :( else { $gdata[] = "N/A"; } //put data of incompleted records into array $grawdata[]=$TotalIncomplete; //put question title ("Not completed") into array $label[]= $fname; //put the code ("Not completed") into the array $justcode[]=$fname; //edit labels and put them into antoher array if ((incompleteAnsFilterstate() != "filter")) { $lbl[] = wordwrap(FlattenText($statlang->gT("Not completed or Not displayed")." ($TotalIncomplete)"), 20, "\n"); // NMO 2009-03-24 } else { $lbl[] = wordwrap(FlattenText($statlang->gT("Not displayed")." ($TotalIncomplete)"), 20, "\n"); // NMO 2009-03-24 } } //end else -> noncompleted NOT checked } //end if -> no filtering of incomplete answers and no multiple option questions //counter $i=0; //we need to know which item we are editing $itemcounter = 1; //array to store items 1 - 5 of question types "5" and "A" $stddevarray = array(); //loop through all available answers while (isset($gdata[$i])) { //repeat header (answer, count, ...) for each new question unset($showheadline); /* * there are 3 colums: * * 1 (50%) = answer (title and code in brackets) * 2 (25%) = count (absolute) * 3 (25%) = percentage */ $statisticsoutput .= "\t\n\t\t\n" //output absolute number of records ."\t\t"; //no data if ($gdata[$i] == "N/A") { switch($outputType) { case 'xls': $label[$i]=FlattenText($label[$i]); $tableXLS[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $gdata[$i]). "%"); ++$xlsRow; $sheet->write($xlsRow,0,$label[$i]); $sheet->write($xlsRow,1,$grawdata[$i]); $sheet->write($xlsRow,2,sprintf("%01.2f", $gdata[$i]). "%"); break; case 'pdf': $tablePDF[] = array(FlattenText($label[$i]),$grawdata[$i],sprintf("%01.2f", $gdata[$i]). "%", ""); break; case 'html': //output when having no data $statisticsoutput .= "\t\t"; } break; default: break; } } //data available else { //check if data should be aggregated if(isset($showaggregateddata) && $showaggregateddata == 1 && ($qtype == "5" || $qtype == "A")) { //mark that we have done soemthing special here $aggregated = true; //just calculate everything once. the data is there in the array if($itemcounter == 1) { //there are always 5 answers for($x = 0; $x < 5; $x++) { //put 5 items into array for further calculations array_push($stddevarray, $grawdata[$x]); } } //"no answer" & items 2 / 4 - nothing special to do here, just adjust output if($gdata[$i] <= 100) { if($itemcounter == 2 && $label[$i+4] == $statlang->gT("No answer")) { //prevent division by zero if(($results - $grawdata[$i+4]) > 0) { //re-calculate percentage $percentage = ($grawdata[$i] / ($results - $grawdata[$i+4])) * 100; } else { $percentage = 0; } } elseif($itemcounter == 4 && $label[$i+2] == $statlang->gT("No answer")) { //prevent division by zero if(($results - $grawdata[$i+2]) > 0) { //re-calculate percentage $percentage = ($grawdata[$i] / ($results - $grawdata[$i+2])) * 100; } else { $percentage = 0; } } else { $percentage = $gdata[$i]; } switch($outputType) { case 'xls': $label[$i]=FlattenText($label[$i]); $tableXLS[]= array($label[$i],$grawdata[$i],sprintf("%01.2f", $percentage)."%"); ++$xlsRow; $sheet->write($xlsRow,0,$label[$i]); $sheet->write($xlsRow,1,$grawdata[$i]); $sheet->write($xlsRow,2,sprintf("%01.2f", $percentage)."%"); break; case 'pdf': $label[$i]=FlattenText($label[$i]); $tablePDF[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $percentage)."%", ""); break; case 'html': //output $statisticsoutput .= "\t\t"; break; default: break; } } //item 3 - just show results twice //old: if($gdata[$i] >= 400) //trying to fix bug #2583: if($gdata[$i] >= 400 && $i != 0) { //remove "400" which was added before $gdata[$i] -= 400; if($itemcounter == 3 && $label[$i+3] == $statlang->gT("No answer")) { //prevent division by zero if(($results - $grawdata[$i+3]) > 0) { //re-calculate percentage $percentage = ($grawdata[$i] / ($results - $grawdata[$i+3])) * 100; } else { $percentage = 0; } } else { //get the original percentage $percentage = $gdata[$i]; } switch($outputType) { case 'xls': $label[$i]=FlattenText($label[$i]); $tableXLS[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $percentage)."%",sprintf("%01.2f", $percentage)."%"); ++$xlsRow; $sheet->write($xlsRow,0,$label[$i]); $sheet->write($xlsRow,1,$grawdata[$i]); $sheet->write($xlsRow,2,sprintf("%01.2f", $percentage)."%"); $sheet->write($xlsRow,3,sprintf("%01.2f", $percentage)."%"); break; case 'pdf': $label[$i]=FlattenText($label[$i]); $tablePDF[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $percentage)."%",sprintf("%01.2f", $percentage)."%"); break; case 'html': //output percentage $statisticsoutput .= "\t\t"; //output again (no real aggregation here) $statisticsoutput .= "\t\t\t\t"; break; default: break; } } //FIRST value -> add percentage of item 1 + item 2 //old: if($gdata[$i] >= 300 && $gdata[$i] < 400) //trying to fix bug #2583: if(($gdata[$i] >= 300 && $gdata[$i] < 400) || ($i == 0 && $gdata[$i] <= 400)) { //remove "300" which was added before $gdata[$i] -= 300; if($itemcounter == 1 && $label[$i+5] == $statlang->gT("No answer")) { //prevent division by zero if(($results - $grawdata[$i+5]) > 0) { //re-calculate percentage $percentage = ($grawdata[$i] / ($results - $grawdata[$i+5])) * 100; $percentage2 = ($grawdata[$i + 1] / ($results - $grawdata[$i+5])) * 100; } else { $percentage = 0; $percentage2 = 0; } } else { $percentage = $gdata[$i]; $percentage2 = $gdata[$i+1]; } //percentage of item 1 + item 2 $aggregatedgdata = $percentage + $percentage2; switch($outputType) { case 'xls': $label[$i]=FlattenText($label[$i]); $tableXLS[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $percentage)."%",sprintf("%01.2f", $aggregatedgdata)."%"); ++$xlsRow; $sheet->write($xlsRow,0,$label[$i]); $sheet->write($xlsRow,1,$grawdata[$i]); $sheet->write($xlsRow,2,sprintf("%01.2f", $percentage)."%"); $sheet->write($xlsRow,3,sprintf("%01.2f", $aggregatedgdata)."%"); break; case 'pdf': $label[$i]=FlattenText($label[$i]); $tablePDF[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $percentage)."%",sprintf("%01.2f", $aggregatedgdata)."%"); break; case 'html': //output percentage $statisticsoutput .= "\t\t"; //output aggregated data $statisticsoutput .= "\t\t\t\t"; break; default: break; } } //LAST value -> add item 4 + item 5 if($gdata[$i] > 100 && $gdata[$i] < 300) { //remove "200" which was added before $gdata[$i] -= 200; if($itemcounter == 5 && $label[$i+1] == $statlang->gT("No answer")) { //prevent division by zero if(($results - $grawdata[$i+1]) > 0) { //re-calculate percentage $percentage = ($grawdata[$i] / ($results - $grawdata[$i+1])) * 100; $percentage2 = ($grawdata[$i - 1] / ($results - $grawdata[$i+1])) * 100; } else { $percentage = 0; $percentage2 = 0; } } else { $percentage = $gdata[$i]; $percentage2 = $gdata[$i-1]; } //item 4 + item 5 $aggregatedgdata = $percentage + $percentage2; switch($outputType) { case 'xls': $label[$i]=FlattenText($label[$i]); $tableXLS[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $percentage)."%",sprintf("%01.2f", $aggregatedgdata)."%"); ++$xlsRow; $sheet->write($xlsRow,0,$label[$i]); $sheet->write($xlsRow,1,$grawdata[$i]); $sheet->write($xlsRow,2,sprintf("%01.2f", $percentage)."%"); $sheet->write($xlsRow,3,sprintf("%01.2f", $aggregatedgdata)."%"); break; case 'pdf': $label[$i]=FlattenText($label[$i]); $tablePDF[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $percentage)."%",sprintf("%01.2f", $aggregatedgdata)."%"); break; case 'html': //output percentage $statisticsoutput .= "\t\t"; //output aggregated data $statisticsoutput .= "\t\t\t\t"; break; default: break; } // create new row "sum" //calculate sum of items 1-5 $sumitems = $grawdata[$i] + $grawdata[$i-1] + $grawdata[$i-2] + $grawdata[$i-3] + $grawdata[$i-4]; //special treatment for zero values if($sumitems > 0) { $sumpercentage = "100.00"; } else { $sumpercentage = "0"; } //special treatment for zero values if($TotalCompleted > 0) { $casepercentage = "100.00"; } else { $casepercentage = "0"; } switch($outputType) { case 'xls': $footXLS[] = array($statlang->gT("Sum")." (".$statlang->gT("Answers").")",$sumitems,$sumpercentage."%",$sumpercentage."%"); $footXLS[] = array($statlang->gT("Number of cases"),$TotalCompleted,$casepercentage."%",""); ++$xlsRow; $sheet->write($xlsRow,0,$statlang->gT("Sum")." (".$statlang->gT("Answers").")"); $sheet->write($xlsRow,1,$sumitems); $sheet->write($xlsRow,2,$sumpercentage."%"); $sheet->write($xlsRow,3,$sumpercentage."%"); ++$xlsRow; $sheet->write($xlsRow,0,$statlang->gT("Number of cases")); $sheet->write($xlsRow,1,$TotalCompleted); $sheet->write($xlsRow,2,$casepercentage."%"); //$sheet->write($xlsRow,3,$sumpercentage."%"); break; case 'pdf': $footPDF[] = array($statlang->gT("Sum")." (".$statlang->gT("Answers").")",$sumitems,$sumpercentage."%",$sumpercentage."%"); $footPDF[] = array($statlang->gT("Number of cases"),$TotalCompleted,$casepercentage."%",""); break; case 'html': $statisticsoutput .= "\t\t \n\t\n"; $statisticsoutput .= ""; $statisticsoutput .= ""; $statisticsoutput .= ""; $statisticsoutput .= ""; $statisticsoutput .= "\t\t \n\t\n"; $statisticsoutput .= ""; //German: "Fallzahl" $statisticsoutput .= ""; $statisticsoutput .= ""; //there has to be a whitespace within the table cell to display correctly $statisticsoutput .= ""; break; default: break; } } } //end if -> show aggregated data //don't show aggregated data else { switch($outputType) { case 'xls': $label[$i]=FlattenText($label[$i]); $tableXLS[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $gdata[$i])."%", ""); ++$xlsRow; $sheet->write($xlsRow,0,$label[$i]); $sheet->write($xlsRow,1,$grawdata[$i]); $sheet->write($xlsRow,2,sprintf("%01.2f", $gdata[$i])."%"); //$sheet->write($xlsRow,3,$sumpercentage."%"); break; case 'pdf': $label[$i]=FlattenText($label[$i]); $tablePDF[] = array($label[$i],$grawdata[$i],sprintf("%01.2f", $gdata[$i])."%", ""); break; case 'html': //output percentage $statisticsoutput .= "\t\t\n\t\n"; break; default: break; } } } //end else -> $gdata[$i] != "N/A" //increase counter $i++; $itemcounter++; } //end while //only show additional values when this setting is enabled if(isset($showaggregateddata) && $showaggregateddata == 1 ) { //it's only useful to calculate standard deviation and arithmetic means for question types //5 = 5 Point Scale //A = Array (5 Point Choice) if($qtype == "5" || $qtype == "A") { $stddev = 0; $am = 0; //calculate arithmetic mean if(isset($sumitems) && $sumitems > 0) { //calculate and round results //there are always 5 items for($x = 0; $x < 5; $x++) { //create product of item * value $am += (($x+1) * $stddevarray[$x]); } //prevent division by zero if(isset($stddevarray) && array_sum($stddevarray) > 0) { $am = round($am / array_sum($stddevarray),2); } else { $am = 0; } //calculate standard deviation -> loop through all data /* * four steps to calculate the standard deviation * 1 = calculate difference between item and arithmetic mean and multiply with the number of elements * 2 = create sqaure value of difference * 3 = sum up square values * 4 = multiply result with 1 / (number of items) * 5 = get root */ for($j = 0; $j < 5; $j++) { //1 = calculate difference between item and arithmetic mean $diff = (($j+1) - $am); //2 = create square value of difference $squarevalue = square($diff); //3 = sum up square values and multiply them with the occurence //prevent divison by zero if($squarevalue != 0 && $stddevarray[$j] != 0) { $stddev += $squarevalue * $stddevarray[$j]; } } //4 = multiply result with 1 / (number of items (=5)) //There are two different formulas to calculate standard derivation //$stddev = $stddev / array_sum($stddevarray); //formula source: http://de.wikipedia.org/wiki/Standardabweichung //prevent division by zero if((array_sum($stddevarray)-1) != 0 && $stddev != 0) { $stddev = $stddev / (array_sum($stddevarray)-1); //formula source: http://de.wikipedia.org/wiki/Empirische_Varianz } else { $stddev = 0; } //5 = get root $stddev = sqrt($stddev); $stddev = round($stddev,2); } switch($outputType) { case 'xls': $tableXLS[] = array($statlang->gT("Arithmetic mean"),$am,'',''); $tableXLS[] = array($statlang->gT("Standard deviation"),$stddev,'',''); ++$xlsRow; $sheet->write($xlsRow,0,$statlang->gT("Arithmetic mean")); $sheet->write($xlsRow,1,$am); ++$xlsRow; $sheet->write($xlsRow,0,$statlang->gT("Standard deviation")); $sheet->write($xlsRow,1,$stddev); break; case 'pdf': $tablePDF[] = array($statlang->gT("Arithmetic mean"),$am,'',''); $tablePDF[] = array($statlang->gT("Standard deviation"),$stddev,'',''); break; case 'html': //calculate standard deviation $statisticsoutput .= ""; //German: "Fallzahl" $statisticsoutput .= ""; $statisticsoutput .= ""; //German: "Fallzahl" $statisticsoutput .= ""; break; default: break; } } } if($outputType=='pdf') //XXX TODO PDF { //$tablePDF = array(); $tablePDF = array_merge_recursive($tablePDF, $footPDF); $pdf->headTable($headPDF,$tablePDF); //$pdf->tableintopdf($tablePDF); // if(isset($footPDF)) // foreach($footPDF as $foot) // { // $footA = array($foot); // $pdf->tablehead($footA); // } } //-------------------------- PCHART OUTPUT ---------------------------- //PCHART has to be enabled and we need some data if ($usegraph==1 && array_sum($gdata)>0) { $graph = ""; $p1 = ""; // $statisticsoutput .= "
";
                    //                  $statisticsoutput .= "GDATA:\n";
                    //                  print_r($gdata);
                    //                  $statisticsoutput .= "GRAWDATA\n";
                    //                  print_r($grawdata);
                    //                  $statisticsoutput .= "LABEL\n";
                    //                  print_r($label);
                    //                  $statisticsoutput .= "JUSTCODE\n";
                    //                  print_r($justcode);
                    //                  $statisticsoutput .= "LBL\n";
                    //                  print_r($lbl);
                    //                  $statisticsoutput .= "
"; //First, lets delete any earlier graphs from the tmp directory //$gdata and $lbl are arrays built at the end of the last section //that contain the values, and labels for the data we are about //to send to pchart. $i = 0; foreach ($gdata as $data) { if ($data != 0){$i++;} } $totallines=$i; if ($totallines>15) { $gheight=320+(6.7*($totallines-15)); $fontsize=7; $legendtop=0.01; $setcentrey=0.5/(($gheight/320)); } else { $gheight=320; $fontsize=8; $legendtop=0.07; $setcentrey=0.5; } // Create bar chart for multiple options if ($qtype == "M" || $qtype == "P") { //new bar chart using data from array $grawdata which contains percentage $DataSet = new pData; $counter=0; $maxyvalue=0; foreach ($grawdata as $datapoint) { $DataSet->AddPoint(array($datapoint),"Serie$counter"); $DataSet->AddSerie("Serie$counter"); $counter++; if ($datapoint>$maxyvalue) $maxyvalue=$datapoint; } if ($maxyvalue<10) {++$maxyvalue;} $counter=0; foreach ($lbl as $label) { $DataSet->SetSerieName($label,"Serie$counter"); $counter++; } if ($MyCache->IsInCache("graph".$surveyid,$DataSet->GetData())) { $cachefilename=basename($MyCache->GetFileFromCache("graph".$surveyid,$DataSet->GetData())); } else { $graph = new pChart(1,1); $graph->setFontProperties($rootdir."/fonts/".$chartfontfile, $chartfontsize); $legendsize=$graph->getLegendBoxSize($DataSet->GetDataDescription()); if ($legendsize[1]<320) $gheight=420; else $gheight=$legendsize[1]+100; $graph = new pChart(690+$legendsize[0],$gheight); $graph->loadColorPalette($homedir.'/styles/'.$admintheme.'/limesurvey.pal'); $graph->setFontProperties($rootdir."/fonts/".$chartfontfile,$chartfontsize); $graph->setGraphArea(50,30,500,$gheight-60); $graph->drawFilledRoundedRectangle(7,7,523+$legendsize[0],$gheight-7,5,254,255,254); $graph->drawRoundedRectangle(5,5,525+$legendsize[0],$gheight-5,5,230,230,230); $graph->drawGraphArea(255,255,255,TRUE); $graph->drawScale($DataSet->GetData(),$DataSet->GetDataDescription(),SCALE_START0,150,150,150,TRUE,90,0,TRUE,5,false); $graph->drawGrid(4,TRUE,230,230,230,50); // Draw the 0 line $graph->setFontProperties($rootdir."/fonts/".$chartfontfile,$chartfontsize); $graph->drawTreshold(0,143,55,72,TRUE,TRUE); // Draw the bar graph $graph->drawBarGraph($DataSet->GetData(),$DataSet->GetDataDescription(),FALSE); //$Test->setLabel($DataSet->GetData(),$DataSet->GetDataDescription(),"Serie4","1","Important point!"); // Finish the graph $graph->setFontProperties($rootdir."/fonts/".$chartfontfile, $chartfontsize); $graph->drawLegend(510,30,$DataSet->GetDataDescription(),255,255,255); $MyCache->WriteToCache("graph".$surveyid,$DataSet->GetData(),$graph); $cachefilename=basename($MyCache->GetFileFromCache("graph".$surveyid,$DataSet->GetData())); } } //end if (bar chart) //Pie Chart else { // this block is to remove the items with value == 0 $i = 0; while (isset ($gdata[$i])) { if ($gdata[$i] == 0) { array_splice ($gdata, $i, 1); array_splice ($lbl, $i, 1); } else {$i++;} } $lblout=array(); if ($language=='ar') { $lblout=$lbl; //reset text order to original include_once($rootdir.'/classes/core/Arabic.php'); $Arabic = new Arabic('ArGlyphs'); foreach($lblout as $kkey => $kval){ if (preg_match("^[A-Za-z]^", $kval)) { //auto detect if english //eng //no reversing } else{ $kval = $Arabic->utf8Glyphs($kval,50,false); $lblout[$kkey] = $kval; } } } elseif (getLanguageRTL($language)) { $lblout=$lblrtl; } else { $lblout=$lbl; } //create new 3D pie chart if ($usegraph==1) { $DataSet = new pData; $DataSet->AddPoint($gdata,"Serie1"); $DataSet->AddPoint($lblout,"Serie2"); $DataSet->AddAllSeries(); $DataSet->SetAbsciseLabelSerie("Serie2"); if ($MyCache->IsInCache("graph".$surveyid,$DataSet->GetData())) { $cachefilename=basename($MyCache->GetFileFromCache("graph".$surveyid,$DataSet->GetData())); } else { $gheight=ceil($gheight); $graph = new pChart(690,$gheight); $graph->loadColorPalette($homedir.'/styles/'.$admintheme.'/limesurvey.pal'); $graph->drawFilledRoundedRectangle(7,7,687,$gheight-3,5,254,255,254); $graph->drawRoundedRectangle(5,5,689,$gheight-1,5,230,230,230); // Draw the pie chart $graph->setFontProperties($rootdir."/fonts/".$chartfontfile, $chartfontsize); $graph->drawPieGraph($DataSet->GetData(),$DataSet->GetDataDescription(),225,round($gheight/2),170,PIE_PERCENTAGE,TRUE,50,20,5); $graph->setFontProperties($rootdir."/fonts/".$chartfontfile,$chartfontsize); $graph->drawPieLegend(430,12,$DataSet->GetData(),$DataSet->GetDataDescription(),250,250,250); $MyCache->WriteToCache("graph".$surveyid,$DataSet->GetData(),$graph); $cachefilename=basename($MyCache->GetFileFromCache("graph".$surveyid,$DataSet->GetData())); } //print_r($DataSet->GetData()); echo "

"; } } //end else -> pie charts //introduce new counter if (!isset($ci)) {$ci=0;} //increase counter, start value -> 1 $ci++; switch($outputType) { case 'xls': /** * No Image for Excel... */ break; case 'pdf': $pdf->AddPage('P','A4'); $pdf->titleintopdf($pdfTitle,$titleDesc); $pdf->Image($tempdir."/".$cachefilename, 5, 70, 200, 200, '', $homeurl."/admin.php?sid=$surveyid", 'B', true, 150,'',false,false,0,true); break; case 'html': $statisticsoutput .= ""; break; default: break; } } //close table/output if($outputType=='html') $statisticsoutput .= "
" //headline .sprintf($statlang->gT("Field summary for %s"),$qtitle)."" ."
" //question title .$qquestion."
"; break; default: break; } echo ''; //loop thorugh the array which contains all answer data foreach ($alist as $al) { //picks out alist that come from the multiple list above if (isset($al[2]) && $al[2]) { //handling for "other" option if ($al[0] == $statlang->gT("Other")) { if($qtype=='!') { // It is better for single choice question types to filter on the number of '-oth-' entries, than to // just count the number of 'other' values - that way with failing Javascript the statistics don't get messed up $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE ".db_quote_id(substr($al[2],0,strlen($al[2])-5))."='-oth-'"; } else { //get data $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE "; $query .= ($connect->databaseType == "mysql")? db_quote_id($al[2])." != ''" : "NOT (".db_quote_id($al[2])." LIKE '')"; } } /* * text questions: * * U = huge free text * T = long free text * S = short free text * Q = multiple short text */ elseif ($qtype == "U" || $qtype == "T" || $qtype == "S" || $qtype == "Q" || $qtype == ";") { //free text answers if($al[0]=="Answers") { $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE "; $query .= ($connect->databaseType == "mysql")? db_quote_id($al[2])." != ''" : "NOT (".db_quote_id($al[2])." LIKE '')"; } //"no answer" handling elseif($al[0]=="NoAnswer") { // $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE (".db_quote_id($al[2])." IS NULL OR "; $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE ( "; $query .= ($connect->databaseType == "mysql")? db_quote_id($al[2])." = '')" : " (".db_quote_id($al[2])." LIKE ''))"; } } // all other question types else { $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE ".db_quote_id($al[2])." ="; //ranking question? if (substr($rt, 0, 1) == "R") { $query .= " '$al[0]'"; } else { $query .= " 'Y'"; } } } //end if -> alist set else { if ($al[0] != "") { //get more data if ($connect->databaseType == 'odbc_mssql' || $connect->databaseType == 'odbtp' || $connect->databaseType == 'mssql_n' || $connect->databaseType == 'mssqlnative') { // mssql cannot compare text blobs so we have to cast here $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE cast(".db_quote_id($rt)." as varchar)= '$al[0]'"; } else $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE ".db_quote_id($rt)." = '$al[0]'"; } else { // This is for the 'NoAnswer' case // We need to take into account several possibilities // * NoAnswer cause the participant clicked the NoAnswer radio // ==> in this case value is '' or ' ' // * NoAnswer in text field // ==> value is '' // * NoAnswer due to conditions, or a page not displayed // ==> value is NULL if ($connect->databaseType == 'odbc_mssql' || $connect->databaseType == 'odbtp' || $connect->databaseType == 'mssql_n' || $connect->databaseType == 'mssqlnative') { // mssql cannot compare text blobs so we have to cast here //$query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE (".db_quote_id($rt)." IS NULL " $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE ( " // . "OR cast(".db_quote_id($rt)." as varchar) = '' " . "cast(".db_quote_id($rt)." as varchar) = '' " . "OR cast(".db_quote_id($rt)." as varchar) = ' ' )"; } else // $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE (".db_quote_id($rt)." IS NULL " $query = "SELECT count(*) FROM ".db_table_name("survey_$surveyid")." WHERE ( " // . "OR ".db_quote_id($rt)." = '' " . " ".db_quote_id($rt)." = '' " . "OR ".db_quote_id($rt)." = ' ') "; } } //check filter option if (incompleteAnsFilterstate() == "inc") {$query .= " AND submitdate is null";} elseif (incompleteAnsFilterstate() == "filter") {$query .= " AND submitdate is not null";} //check for any "sql" that has been passed from another script if ($sql != "NULL") {$query .= " AND $sql";} //get data $result=db_execute_num($query) or safe_die ("Couldn't do count of values
$query
".$connect->ErrorMsg()); // $statisticsoutput .= "\n\n\n"; // this just extracts the data, after we present while ($row=$result->FetchRow()) { //increase counter $TotalCompleted += $row[0]; //"no answer" handling if ($al[0] === "") {$fname=$statlang->gT("No answer");} //"other" handling //"Answers" means that we show an option to list answer to "other" text field elseif ($al[0] === $statlang->gT("Other") || $al[0] === "Answers" || $qtype === "P") { if ($qtype == "P") $ColumnName_RM = $al[2]."comment"; else $ColumnName_RM = $al[2]; $fname="$al[1]"; if ($browse===true) $fname .= " "; } /* * text questions: * * U = huge free text * T = long free text * S = short free text * Q = multiple short text */ elseif ($qtype == "S" || $qtype == "U" || $qtype == "T" || $qtype == "Q") { $headPDF = array(); $headPDF[] = array($statlang->gT("Answer"),$statlang->gT("Count"),$statlang->gT("Percentage")); //show free text answers if ($al[0] == "Answers") { $fname= "$al[1]"; if ($browse===true) $fname .= " "; } elseif ($al[0] == "NoAnswer") { $fname= "$al[1]"; } } //check if aggregated results should be shown elseif (isset($showaggregateddata) && $showaggregateddata == 1) { if(!isset($showheadline) || $showheadline != false) { if($qtype == "5" || $qtype == "A") { switch($outputType) { case 'xls': $headXLS = array(); $headXLS[] = array($statlang->gT("Answer"),$statlang->gT("Count"),$statlang->gT("Percentage"),$statlang->gT("Sum")); ++$xlsRow; $sheet->write($xlsRow,0,$statlang->gT("Answer")); $sheet->write($xlsRow,1,$statlang->gT("Count")); $sheet->write($xlsRow,2,$statlang->gT("Percentage")); $sheet->write($xlsRow,3,$statlang->gT("Sum")); break; case 'pdf': $headPDF = array(); $headPDF[] = array($statlang->gT("Answer"),$statlang->gT("Count"),$statlang->gT("Percentage"),$statlang->gT("Sum")); break; case 'html': //four columns $statisticsoutput .= "".$statlang->gT("Answer")."
" ."".$statlang->gT("Count")."" ."".$statlang->gT("Percentage")."" ."".$statlang->gT("Sum")."
" ."".$statlang->gT("Count")."" ."".$statlang->gT("Percentage")."
" ."".$statlang->gT("Count")."" ."".$statlang->gT("Percentage")."
" . $label[$i] ."\n" ."\t\t" . $grawdata[$i] . "\n"; //percentage = 0 $statisticsoutput .= sprintf("%01.2f", $gdata[$i]) . "%"; $gdata[$i] = 0; //check if we have to adjust ouput due to $showaggregateddata setting if(isset($showaggregateddata) && $showaggregateddata == 1 && ($qtype == "5" || $qtype == "A")) { $statisticsoutput .= "\t\t"; //output percentage $statisticsoutput .= sprintf("%01.2f", $percentage) . "%"; //adjust output $statisticsoutput .= "\t\t"; $statisticsoutput .= sprintf("%01.2f", $percentage) . "%"; $statisticsoutput .= sprintf("%01.2f", $percentage)."%"; $statisticsoutput .= ""; $statisticsoutput .= sprintf("%01.2f", $percentage) . "%"; $statisticsoutput .= sprintf("%01.2f", $aggregatedgdata)."%"; $statisticsoutput .= ""; $statisticsoutput .= sprintf("%01.2f", $percentage) . "%"; $statisticsoutput .= sprintf("%01.2f", $aggregatedgdata)."%"; $statisticsoutput .= "
".$statlang->gT("Sum")." (".$statlang->gT("Answers").")".$sumitems."$sumpercentage%$sumpercentage%
".$statlang->gT("Number of cases")."".$TotalCompleted."$casepercentage% 
"; $statisticsoutput .= sprintf("%01.2f", $gdata[$i]) . "%"; $statisticsoutput .= "\t\t"; //end output per line. there has to be a whitespace within the table cell to display correctly $statisticsoutput .= "\t\t 
".$statlang->gT("Arithmetic mean")."  $am 
".$statlang->gT("Standard deviation")." $stddev 

\n"; } //end if -> collect and display results //delete data unset($gdata); unset($grawdata); unset($label); unset($lbl); unset($lblout); unset($justcode); unset ($alist); } // end foreach -> loop through all questions //output if($outputType=='html') $statisticsoutput .= "
 \n"; } //end if -> show summary results switch($outputType) { case 'xls': //$workbook-> $workbook->close(); if($pdfOutput=='F') return $tempdir.'/statistic-survey'.$surveyid.'.xls'; else return; break; case 'pdf': $pdf->lastPage(); if($pdfOutput=='F') { // This is only used by lsrc to send an E-Mail attachment, so it gives back the filename to send and delete afterwards $pdf->Output($tempdir."/".$statlang->gT('Survey').'_'.$surveyid."_".$surveyInfo['surveyls_title'].'.pdf', $pdfOutput); return $tempdir."/".$statlang->gT('Survey').'_'.$surveyid."_".$surveyInfo['surveyls_title'].'.pdf'; } else return $pdf->Output($statlang->gT('Survey').'_'.$surveyid."_".$surveyInfo['surveyls_title'].'.pdf', $pdfOutput); break; case 'html': return $statisticsoutput; break; default: return $statisticsoutput; break; } } ////XXXXXX***// //simple function to square a value function square($number) { if($number == 0) { $squarenumber = 0; } else { $squarenumber = $number * $number; } return $squarenumber; } ?>