<?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);
}
}
?>