Location: PHPKode > projects > Teacher Evaluation based on phpsurveyor > DONT USE/mod_evaluation/statistics.class.php
<?php 
require_once "evaluation.classes.php";

// SELECT *
//FROM teacher_summary group by idteacher order by expires desc
//
// should return the current survey of all teachers

class ModEvaluation_Statistics_Base{//class for common handling for the mysql db
	var $QuestionCodesQueryResult;
	var $lastSID;
	var $lastSurveyTableName;
	var $dbprefix;
	
	function ModEvaluation_Statistics_Base(){ //Constructor
		global $dbprefix;
		$this->dbprefix=$dbprefix;
	}
	
	function printTableHeader(&$result){
		echo "<br><table border=1><tr>";
		while($row=mysql_fetch_field($result)){
			print "<td> {$row->name} </td>";
		}
		echo "</tr>"; // while
	}
	
	function printTableContent(&$result){
		while ($row = mysql_fetch_assoc($result)){
			echo "<tr>";
			foreach($row as $col){
				print "<td> $col </td>";
			}
			echo "</tr>";
		}
		echo "</table>";
		unset($result);
	}
	function fastprintResult(&$result){
		$this->printTableHeader($result);
		$this->printTableContent($result);
	}
	function fastprintTable($sql){
		$result=mysql_query($sql);
		$this->printTableHeader($result);
		$this->printTableContent($result);
	}
	function resolveSIDtoTable($sid){
		$this->lastSurveyTableName= $this->dbprefix . "survey_" . $sid;
		return $this->lastSurveyTableName;
	}
	
	function getQuestionCodesQueryResult($sid=2){ //2 is the default survey template  returns
		$sql="select sid, gid, q.qid, code, answer, 
		concat(sid,'X',gid,'X',a.qid,code)as compiledID 
		from {$this->dbprefix}questions q, {$this->dbprefix}answers a 
		where q.qid=a.qid and q.sid=$sid 
		order by compiledID,sortorder asc";
		$result=mysql_query($sql);
		// DELETE _ OLD SOLUTION _ TOOOO COMPLICATED : $result = mysql_query("describe  {$this->lastSurveyTableName} '{$this->lastSID}X%'");
		$this->QuestionCodesQueryResult=&$result; //try pointer to speed up ..DANGER if pointer is deleted on function end..
		return $result;
	}	
}

class ModEvaluation_createStatistics extends ModEvaluation_Statistics_Base{
	/* This class works on the single survey tables (one per teacher) summarizes the results and 
	saves them in the teacher_summary table
	*/
	function ModEvaluation_createStatistics(){
		parent::ModEvaluation_Statistics_Base();
	}
	function resetSummaryTable(){		
		//if the number of questions / column names have changed the old summary table has to be deleted and saved a side
		$newtablename =date("d_m_y___H_m_s");
		mysql_query("Create table summary_teacher_old_$newtablename (PRIMARY KEY  (`expires`,`sid`,`idteacher`))  select * from teacher_summary"); 
		mysql_query("drop table teacher_summary");
	}
	
	function sqlStatisticsforOneTeacher(&$myTeacher){
		$myTeacher->databasefields['currentsid'];
	 	$result=parent::getQuestionCodesQueryResult($myTeacher->databasefields['currentsid']);
		$columns=""; //init
		 while($row =mysql_fetch_assoc($result)){
		 	$columns = $columns . "AVG({$row['compiledID']}) as {$row['code']},";
		 }
     	$columns = substr($columns, 0, -1); //delete last comma    
     	$sql = " Select t.idteacher, s.sid, expires,
				count(id) as totalvotes, 
				min(ts.datestamp)as fromdate, 
				max(ts.datestamp) as untildate,  
				%COLUMNS%  
				FROM  {$this->dbprefix}surveys s, {$myTeacher->surveytablename} ts, teacher t 
				where s.sid=t.currentsid and s.sid={$myTeacher->databasefields['currentsid']}  
				group by t.idteacher";  
     	$sql=str_replace('%COLUMNS%',$columns,$sql);	
		return $sql;
	}
	
	function updateStatisticsforOneTeacher(&$myTeacher) {
		$sql=& $this->sqlStatisticsforOneTeacher(&$myTeacher);
	 	 //echo "<hr><br>$sql<hr"; //DEBUG
		parent::fastprintTable($sql); //DEBUG
		$this->createTeacher_SummaryTableOnDemand($sql);
   	 	mysql_query("Replace into teacher_summary $sql"); //insert or update
		
		//REPORTING
		echo "<p>Teacher: {$myTeacher->databasefields['idteacher']} 
			{$myTeacher->databasefields['lastname']} 
			{$myTeacher->databasefields['firstname']} updated";
		if (mysql_affected_rows()!=1){
			me_error("Rows affected: " . mysql_affected_rows());
		}
		echo "</p>";
	}
	function createTeacher_SummaryTableOnDemand(&$sql){
		$sqlCreateTableIfNotExists="Create Table if not Exists teacher_summary 
								(PRIMARY KEY  (`expires`,`sid`,`idteacher`) )
								$sql";
		mysql_query($sqlCreateTableIfNotExists);
	}

	function updateAllTeachers(){
		$sql="Select min(idteacher) as min, max(idteacher) as max from teacher";
		$result=mysql_query($sql);
		$row=mysql_fetch_assoc($result);
		
		for($i=$row['min'];$i<=$row['max'];$i++){
			$myTeacher= new Teacher($i);
			$this->updateStatisticsforOneTeacher(&$myTeacher);
		}
	}

}


class ModEvaluation_getStatistics extends ModEvaluation_Statistics_Base{
	var $idteacher; //array(SID,SID2...) | all 		//either array containing the ids of the teachers or "all"
	var $timeframe; //current|all
	var $allTeachersAverage; //yes|no 				//show average of all teachers yes|no
	var $summarymode; // singleanswer|singlegroup|total
	var $teacherspergraph; //X			//after adding X functions to plot finish plot and create new
	var $showsingletimeperiods;  //yes|no  - if no then the timeperiods per teacher are averaged and only on line is added to the data plot
	
	//var $myTeachers; //array for teacher class instances
	var $data2plot; //this 2d Array is filled with the data to plot.
	var $questions; //the heading for the data plot	
	//var $entireDataResult; //all the data return by mysql
	var $dataOverallAverage; //the array containing the average of all the teachers in the result set
	var $entireDataArray; //all the data from mysql into a number and index array
	var $sql;
	var $sqlQuestionsOnly;
	var $sqlOverallAverage;
	var $compileSQLFlag;
	function ModEvaluation_getStatistics($idteacher="all", //CONSTRUCTOR
										 $timeframe="current", 
										 $summarymode="singleanswer", 
										 $teacherspergraph="1",
										 $allteachersaverage="yes",
										 $showsingletimeperiods="yes"){
		$this->idteacher=$idteacher;
		$this->timeframe=$timeframe;
		$this->summarymode=$summarymode;
		$this->teacherspergraph=$teacherspergraph;
		$this->allTeachersAverage=$allteachersaverage;
		$this->$showsingletimeperiods=$showsingletimeperiods;
		
		parent::ModEvaluation_Statistics_Base();
	}	
	
	function compileSQL(){
//		%COLUMNS%
//		%WHERECLAUSE%
//		%ORDERBY%
//		%GROUPBY%
//		%DISTINCT%

		$columns="ts.idteacher, CONCAT(t.lastname,\" \",t.firstname) as teachername, ts.sid, ts.expires, ts.totalvotes, ts.fromdate, ts.untildate ";
		$whereclause="WHERE ";
		$orderby="ORDER BY ";
		$groupby="GROUP BY ";
		$distinct=""; //needed for the all clause
		if($this->idteacher=="all"){
			$whereclause=$whereclause . " ts.idteacher=t.idteacher";
			$distinct="DISTINCT";
			$groupby=$groupby . " t.idteacher";
			$orderby="";
		}elseif(is_array($this->idteacher)){
			foreach($this->idteacher as $singleteacher){
				$whereclause=$whereclause . " (ts.idteacher=t.idteacher and idteacher={$singleteacher}) or ";
			}
			$whereclause=substr($whereclause,0,-3); //delete last or
		}else{
			me_error("idteacher is neither ALL nor ARRAY");
		}
		
		
		//get columns
		$result=parent::getQuestionCodesQueryResult();
		$questioncolumns=""; //init
		$overallaveragecolumns=""; //init
		while($row=mysql_fetch_assoc($result)){
			$answer=str_replace("%teachername%","",$row['answer']);
			$questioncolumns=$questioncolumns . " ,{$row['code']} as '{$answer}'";
			$overallaveragecolumns=$overallaveragecolumns . " ,AVG({$row['code']}) as '{$answer}'";
		}
		unset($result);	
			
		$this->sql=$this->concatSQLString($distinct,$columns . $questioncolumns,$whereclause,$groupby,$orderby);
		$questioncolumns=substr($questioncolumns,2); //cut the first to chars	(These are " ,"	
		$this->sqlQuestionsOnly=$this->concatSQLString($distinct,$questioncolumns,$whereclause,$groupby,$orderby);
		$this->sqlOverallAverage=$this->concatSQLString($distinct,$columns . $overallaveragecolumns,$whereclause,$groupby,$orderby);
		$this->compileSQLFlag=1;
		
	}
	function concatSQLString($distinct,$columns,$whereclause,$groupby,$orderby){
		return "SELECT $distinct $columns FROM teacher_summary ts , teacher t $whereclause  $groupby $orderby";
	}
// FILL DATA IN VARIABLES
	function fillEntireDataArray(){
		unset($this->entireDataArray);
		$result=mysql_query($this->sql);
		while($row=mysql_fetch_array($result)){
			$this->entireDataArray[]=$row;
		}
	}
	function fillDataPlot(){
		$result=mysql_query($this->sqlQuestionsOnly);
		unset($this->data2plot); //clear to prevent errors / wrong results
		while($row=mysql_fetch_row($result)){
			$this->data2plot[]=$row;
		}
		//parent::getQuestionCodesQueryResult(); //$get questioncodes table result use parent::QuestionCodesQueryResult to retrieve
	}
	function fillQuestions(){ //this funciton is different to - (the fieldnames are different, order could be different?) parent::getQuestionCodesQueryResult 
		$result=mysql_query($this->sqlQuestionsOnly);
		unset($this->questions);
		while($row=mysql_fetch_field($result)){
			$this->questions[]=$row->name ."\n test";
		}
	}
	function fillOverallAverage(){
		$result=mysql_query($this->sqlOverallAverage);
		unset($this->dataOverallAverage); //clear to prevent errors / wrong results
		while($row=mysql_fetch_row($result)){
			$this->dataOverallAverage[]=$row;
		}
	}
	
//-------------------------------------------
	function returnData2Plot($i){ //return data line one by one
		return $this->data2plot[$i];
	}
}
class ModEvaluation_visualizeStatistics extends ModEvaluation_getStatistics{
	var $jpgraphdir;
	var $graphcounter;
	var $numberofgraphs;
	var $currentline;
	function ModEvaluation_visualizeStatistics($jpgraphdir){ //CONSTRUCTOR
		define("TTF_DIR","C:/WINDOWS/FONTS/");
		parent::ModEvaluation_getStatistics(); //TODO ADD PARAMS
		$this->jpgraphdir=$jpgraphdir;
		require_once "{$this->jpgraphdir}/jpgraph.php";
		require_once "{$this->jpgraphdir}/jpgraph_line.php";

	}
	
	function newLinePlot(&$graph,$dataarray, $legend="Lehrer"){
		$p1 = new LinePlot($dataarray);
		//$p1[$i]->SetColor("navy"); always alternate color
		$p1->SetLegend($legend);
		$p1->value->show();
		$p1->value->SetFont(FF_ARIAL);
		$p1->mark->SetType(MARK_IMG_MBALL,'red');
		$graph->Add($p1);
	}
	function newGraph($title="Evaluierung"){
		$graph = new Graph(600,600);
		$graph->img->SetImgFormat("jpeg");
		$graph->SetMarginColor('white');
		$graph->SetScale("textint",1,5);
		$graph->SetFrame(false);
		$graph->SetMargin(30,50,30,30);
		$graph->img->SetMargin(200,50,20,200);
		$graph->title->Set($title);	
		$graph->legend->SetShadow('hide@address.com',5);
		$graph->legend->SetPos(0.01,0.01,'left','top');
		$graph->xaxis->SetTickLabels($this->questions); 
		$graph->xaxis->SetFont(FF_ARIAL);
		$graph->xaxis->SetLabelAngle(30);
		$graph->xaxis->SetLabelMargin(10);
		return $graph;
	}
	function finishGraph($graph){
		global $tempdir;
		global $tempurl;
		$filename="teacher{$this->graphcounter}.jpg";		
		$graph->Stroke($tempdir."/".$filename);	
		echo "<br><img src='{$tempurl}/{$filename}' border='1'><br>";
	}
	
	function plot2d($title){ //params as arrays
		//compiel sql has to be done first
		if ($this->compileSQLFlag!=1) {
			me_error( "call compileSQL() before calling plod2d");
			exit;   
		}
		parent::fillQuestions();
		parent::fillDataPlot();
		parent::fillEntireDataArray();
		$this->currentline=0;
		$count=count($this->data2plot);
		$this->numberofgraphs=ceil($count / $this->teacherspergraph);
		for($this->graphcounter=1;$this->graphcounter<=$this->numberofgraphs;$this->graphcounter++){
			$currentgraph=$this->newGraph($title);
			for($j=1;$j<=$this->teacherspergraph;$j++){
				$this->newLinePlot($currentgraph,$this->data2plot[$this->currentline],$this->entireDataArray[$this->currentline]['teachername']);
				$this->currentline++;
			}
			$this->finishGraph($currentgraph);	// Setup the graph
				//$graph->xaxis->SetTickLabels($gDateLocale->GetShortMonth());
		}
	}
	
	function table(){
		//parent::fastprintArray(parent::data2plot);
	}
} 
?>
Return current item: Teacher Evaluation based on phpsurveyor