<?php
	class cStatisticsPage extends cTeacherPage
	{
    	 
    function __construct( $smarty, $db, $action, $postParams, $getParams )
    {
      parent::__construct( $smarty, $db, $action, $postParams, $getParams );
      $this->menu=new cCourseTeacherMenu;
      if (isset($this->getParams["courseID"]) && ($this->getParams["courseID"]!=="")) 
      {
        $this->menu->GenerateMenu("&amp;courseID=".$this->getParams["courseID"]);
      }
      else
      {
        $this->menu->GenerateMenu("");
      }
      $menuItem=$this->menu->GetItemByLabel("štatistika");
      $menuItem->bSelected=true;
    }
		
    function Process()
    {
      return true;
    }
    
    function Draw()
    {
      if ($this->bLoggedInAsTeacher && ($this->IsTeacherToCourse())) 
      {
        if ($this->action==="showBySuccess")
        {
          if (isset($this->getParams["showWhat"]) && isset($this->getParams["statisticsDateFrom"]) &&
              isset($this->getParams["statisticsDateTo"]) && isset($this->getParams["sort"]))
          {
            $this->getParams["showWhat"]=mysql_real_escape_string($this->getParams["showWhat"]);
            $this->getParams["statisticsDateFrom"]=trim(mysql_real_escape_string($this->getParams["statisticsDateFrom"]));
            $this->getParams["statisticsDateTo"]=trim(mysql_real_escape_string($this->getParams["statisticsDateTo"]));
            $this->getParams["sort"]=mysql_real_escape_string($this->getParams["sort"]);
            if (IsDateType($this->getParams["statisticsDateFrom"]))
            {
              $_SESSION["statisticsDateFrom"]=$this->getParams["statisticsDateFrom"];
            }
            if (IsDateType($this->getParams["statisticsDateTo"]))
            {
              $_SESSION["statisticsDateTo"]=$this->getParams["statisticsDateTo"];
            }
            
            if ($this->getParams["showWhat"]==="answerSets")
            {
              //vyselektuje vsetky sady odpovedi z daneho predmetu
              $answerSets=array();
              $query = "SELECT tD.id AS id_domain, tQ.id AS id_question, tAS.id, GROUP_CONCAT(tA.text) AS text ".
                       "FROM tDomain AS tD, tQuestion AS tQ, tAnswerSet AS tAS, tAnswer AS tA ".
                       "WHERE tD.id_course=".$this->getParams["courseID"]." AND tQ.id_domain=tD.id AND ".
                       "tAS.id_question=tQ.id AND NOT tAS.obsolete AND tA.id_answerSet=tAS.id ".
                       "GROUP BY tAS.id;";
              $result = $this->db->QueryToArray($query);
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $answerSets[$value["id"]]["id_domain"]=$value["id_domain"];
                  $answerSets[$value["id"]]["id_question"]=$value["id_question"];
                  $answerSets[$value["id"]]["id"]=$value["id"];
                  $answerSets[$value["id"]]["text"]=$value["text"];
                  $answerSets[$value["id"]]["points"]=0;
                  $answerSets[$value["id"]]["count"]=0;
                }
              }
              
              //vyselektuje statisticke udaje k sadam odpovedi
              $query = "SELECT tAS.id, tTA.points ".
                       "FROM tDomain AS tD, tQuestion AS tQ, tAnswerSet AS tAS, tAnswer AS tA, ".
                       "tTestAnswerToAnswer AS tTATA, tTestAnswer AS tTA, tTest AS tT, tExam AS tE ".
                       "WHERE tD.id_course=".$this->getParams["courseID"]." AND tQ.id_domain=tD.id AND ".
                       "tAS.id_question=tQ.id AND NOT tAS.obsolete AND tA.id_answerSet=tAS.id AND tTATA.id_answer=tA.id AND ".
                       "tTATA.id_testAnswer=tTA.id AND tTA.id_test=tT.id AND tT.id_exam=tE.id AND ".
                       "tE.date>='".$_SESSION["statisticsDateFrom"]." 00:00:00' AND ".
                       "tE.date<='".$_SESSION["statisticsDateTo"]." 23:59:00' ".
                       "GROUP BY tTA.id;";
              $result = $this->db->QueryToArray($query);
              
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $answerSets[$value["id"]]["points"]+=$value["points"];
                  $answerSets[$value["id"]]["count"]++;
                }
              }
              foreach($answerSets as $key=>$value)
              {
                if ($value["count"]==0)
                {
                  $answerSets[$key]["avg"]=0;
                }
                else
                {
                  $answerSets[$key]["avg"]=intval(100*($value["points"]/$value["count"]))/100;
                }
              }
              if ($this->getParams["sort"]==="descending")
              {
                uasort($answerSets, "CmpAvgDescending");
              }
              else
              {
                uasort($answerSets, "CmpAvgAscending");
              }
              foreach ($answerSets as $key=>$value)
              {
                $answerSets[$key]["text"]=ShortenText($answerSets[$key]["text"]);
              }
              $this->smarty->assign("answerSets", $answerSets);
            }
            elseif ($this->getParams["showWhat"]==="questions")
            {
              //vyselektuje vsetky otazky z daneho predmetu
              $questions=array();
              $query = "SELECT tD.id AS id_domain, tQ.id, tQ.question ".
                       "FROM tDomain AS tD, tQuestion AS tQ ".
                       "WHERE tD.id_course=".$this->getParams["courseID"]." AND tQ.id_domain=tD.id AND ".
                       "NOT tQ.obsolete;";
              $result = $this->db->QueryToArray($query);
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $questions[$value["id"]]["id_domain"]=$value["id_domain"];
                  $questions[$value["id"]]["id"]=$value["id"];
                  $questions[$value["id"]]["question"]=$value["question"];
                  $questions[$value["id"]]["points"]=0;
                  $questions[$value["id"]]["count"]=0;
                }
              }
              
              //vyselektuje statisticke udaje k otazkam
              $query = "SELECT tQ.id, tTA.points ".
                       "FROM tDomain AS tD, tQuestion AS tQ, tAnswerSet AS tAS, tAnswer AS tA, ".
                       "tTestAnswerToAnswer AS tTATA, tTestAnswer AS tTA, tTest AS tT, tExam AS tE ".
                       "WHERE tD.id_course=".$this->getParams["courseID"]." AND tQ.id_domain=tD.id AND ".
                       "tAS.id_question=tQ.id AND NOT tQ.obsolete AND tA.id_answerSet=tAS.id AND tTATA.id_answer=tA.id AND ".
                       "tTATA.id_testAnswer=tTA.id AND tTA.id_test=tT.id AND tT.id_exam=tE.id AND ".
                       "tE.date>='".$_SESSION["statisticsDateFrom"]." 00:00:00' AND ".
                       "tE.date<='".$_SESSION["statisticsDateTo"]." 23:59:00' ".
                       "GROUP BY tTA.id;";
              $result = $this->db->QueryToArray($query);
              
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $questions[$value["id"]]["points"]+=$value["points"];
                  $questions[$value["id"]]["count"]++;
                }
              }
              foreach($questions as $key=>$value)
              {
                if ($value["count"]==0)
                {
                  $questions[$key]["avg"]=0;
                }
                else
                {
                  $questions[$key]["avg"]=intval(100*($value["points"]/$value["count"]))/100;
                }
              }
              if ($this->getParams["sort"]==="descending")
              {
                uasort($questions, "CmpAvgDescending");
              }
              else
              {
                uasort($questions, "CmpAvgAscending");
              }
              foreach ($questions as $key=>$value)
              {
                $questions[$key]["question"]=ShortenText($questions[$key]["question"]);
              }
              $this->smarty->assign("questions", $questions);
            }
            elseif ($this->getParams["showWhat"]==="domains")
            {
              //vyselektuje vsetky okruhy tem z daneho predmetu
              $domains=array();
              $query = "SELECT tD.id, tD.id_parent, tD.subject ".
                       "FROM tDomain AS tD ".
                       "WHERE tD.id_course=".$this->getParams["courseID"]." AND NOT tD.obsolete;";
              $result = $this->db->QueryToArray($query);
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $domains[$value["id"]]["id_parent"]=$value["id_parent"];
                  $domains[$value["id"]]["id"]=$value["id"];
                  $domains[$value["id"]]["subject"]=$value["subject"];
                  $domains[$value["id"]]["points"]=0;
                  $domains[$value["id"]]["count"]=0;
                }
              }
              
              //vyselektuje statisticke udaje k okruhom tem
              $query = "SELECT tD.id, tTA.points ".
                       "FROM tDomain AS tD, tQuestion AS tQ, tAnswerSet AS tAS, tAnswer AS tA, ".
                       "tTestAnswerToAnswer AS tTATA, tTestAnswer AS tTA, tTest AS tT, tExam AS tE ".
                       "WHERE tD.id_course=".$this->getParams["courseID"]." AND tQ.id_domain=tD.id AND ".
                       "tAS.id_question=tQ.id AND NOT tD.obsolete AND tA.id_answerSet=tAS.id AND tTATA.id_answer=tA.id AND ".
                       "tTATA.id_testAnswer=tTA.id AND tTA.id_test=tT.id AND tT.id_exam=tE.id AND ".
                       "tE.date>='".$_SESSION["statisticsDateFrom"]." 00:00:00' AND ".
                       "tE.date<='".$_SESSION["statisticsDateTo"]." 23:59:00' ".
                       "GROUP BY tTA.id;";
              $result = $this->db->QueryToArray($query);
              
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $domains[$value["id"]]["points"]+=$value["points"];
                  $domains[$value["id"]]["count"]++;
                  
                  //prirataj body aj rodicovskym okruhom tem
                  $id=$domains[$value["id"]]["id_parent"];
                  while ($id!=0)
                  {
                    $domains[$id]["points"]+=$value["points"];
                    $domains[$id]["count"]++;
                    $id=$domains[$id]["id_parent"];
                  }
                }
              }
              foreach($domains as $key=>$value)
              {
                if ($value["count"]==0)
                {
                  $domains[$key]["avg"]=0;
                }
                else
                {
                  $domains[$key]["avg"]=intval(100*($value["points"]/$value["count"]))/100;
                }
              }
              if ($this->getParams["sort"]==="descending")
              {
                uasort($domains, "CmpAvgDescending");
              }
              else
              {
                uasort($domains, "CmpAvgAscending");
              }
              foreach ($domains as $key=>$value)
              {
                $domains[$key]["subject"]=ShortenText($domains[$key]["subject"]);
              }
              $this->smarty->assign("domains", $domains);
            }
            elseif ($this->getParams["showWhat"]==="students")
            {
              //vyselektuje vsetkych studentov z daneho predmetu
              $students=array();
              $query = "SELECT tU.id, CONCAT(tU.namePrefix, ' ', tU.surname, ', ', tU.name, ' ', tU.nameSuffix) AS name ".
                       "FROM tStudents AS tS, tUser AS tU, tCourseToUser AS tCTU, tCourse AS tC ".
                       "WHERE tS.id=tU.id AND tCTU.id_user=tU.id AND tCTU.id_course=tC.id AND ".
                       "tC.id=".$this->getParams["courseID"].";";
              $result = $this->db->QueryToArray($query);
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $students[$value["id"]]["id"]=$value["id"];
                  $students[$value["id"]]["name"]=$value["name"];
                  $students[$value["id"]]["points"]=0;
                  $students[$value["id"]]["count"]=0;
                }
              }
              
              //vyselektuje statisticke udaje k studentom
              $query = "SELECT tT.id_user AS id, tT.points ".
                       "FROM tTest AS tT, tExam AS tE ".
                       "WHERE tT.id_exam=tE.id AND tE.id_course=".$this->getParams["courseID"]." AND ".
                       "tE.date>='".$_SESSION["statisticsDateFrom"]." 00:00:00' AND ".
                       "tE.date<='".$_SESSION["statisticsDateTo"]." 23:59:00';";
              $result = $this->db->QueryToArray($query);
              
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $students[$value["id"]]["points"]+=$value["points"];
                  $students[$value["id"]]["count"]++;
                }
              }
              foreach($students as $key=>$value)
              {
                if ($value["count"]==0)
                {
                  $students[$key]["avg"]=0;
                }
                else
                {
                  $students[$key]["avg"]=intval(100*($value["points"]/$value["count"]))/100;
                }
              }
              if ($this->getParams["sort"]==="descending")
              {
                uasort($students, "CmpAvgDescending");
              }
              else
              {
                uasort($students, "CmpAvgAscending");
              }
              $this->smarty->assign("students", $students);
            }
            elseif ($this->getParams["showWhat"]==="terms")
            {
              //vyselektuje vsetky terminy skusok z daneho predmetu
              $terms=array();
              $query = "SELECT tE.id, tE.date FROM tExam AS tE ".
                       "WHERE tE.id_course=".$this->getParams["courseID"].";";
              $result = $this->db->QueryToArray($query);
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $terms[$value["id"]]["id"]=$value["id"];
                  $terms[$value["id"]]["date"]=substr($value["date"],0,16);
                  $terms[$value["id"]]["points"]=0;
                  $terms[$value["id"]]["count"]=0;
                }
              }
              
              //vyselektuje statisticke udaje k terminom
              $query = "SELECT tE.id, tT.points ".
                       "FROM tTest AS tT, tExam AS tE ".
                       "WHERE tT.id_exam=tE.id AND tE.id_course=".$this->getParams["courseID"]." AND ".
                       "tE.date>='".$_SESSION["statisticsDateFrom"]." 00:00:00' AND ".
                       "tE.date<='".$_SESSION["statisticsDateTo"]." 23:59:00';";
              $result = $this->db->QueryToArray($query);
              
              if ($result!=0)
              {
                foreach($result as $key=>$value)
                {
                  $terms[$value["id"]]["points"]+=$value["points"];
                  $terms[$value["id"]]["count"]++;
                }
              }
              foreach($terms as $key=>$value)
              {
                if ($value["count"]==0)
                {
                  $terms[$key]["avg"]=0;
                }
                else
                {
                  $terms[$key]["avg"]=intval(100*($value["points"]/$value["count"]))/100;
                }
              }
              if ($this->getParams["sort"]==="descending")
              {
                uasort($terms, "CmpAvgDescending");
              }
              else
              {
                uasort($terms, "CmpAvgAscending");
              }
              $this->smarty->assign("terms", $terms);
            }
            else
            {
              $this->bAccessDenied=true;
            }
            $this->smarty->assign("showWhat", $this->getParams["showWhat"]);
            $this->smarty->assign("statisticsDateFrom", $_SESSION["statisticsDateFrom"]);
            $this->smarty->assign("statisticsDateTo", $_SESSION["statisticsDateTo"]);
            $this->smarty->assign("sort", $this->getParams["sort"]);
          }
          else
          {
            $this->bAccessDenied=true;
          }
          $this->smarty->assign("statisticsDateFrom", $_SESSION["statisticsDateFrom"]);
          $this->smarty->assign("statisticsDateTo", $_SESSION["statisticsDateTo"]);
        }
        elseif ($this->action==="showRepresentationOfMarks")
        {
          if (isset($this->getParams["examID"]) && ($this->getParams["examID"]!==""))
          {
            $query = "SELECT tE.date, tE.id FROM tExam AS tE WHERE tE.id_course=".
                     $this->getParams["courseID"]." ORDER BY tE.date DESC;";
            $result = $this->db->QueryToArray($query);
            if ( $result != 0 )
            {
              foreach($result as $key=>$value)
              {
                $result[$key]["date"]=substr($result[$key]["date"],0,16);
              }
              $this->smarty->assign("examList", $result);
              
            }
            
            if ($this->getParams["examID"]==="dontCare")
            {
              $query = "SELECT tT.mark, COUNT(*) AS count FROM tTest AS tT, tExam AS tE ".
                       "WHERE tT.id_exam=tE.id AND tE.id_course=".$this->getParams["courseID"]." ".
                       "GROUP BY tT.mark ORDER BY tT.mark ASC;";
              $result = $this->db->QueryToArray($query);
              if ( $result != 0 )
              {
                $marks=array("A", "B", "C", "D", "E", "Fx");
                $marksAndCount=array();
                foreach ($marks as $key=>$value)
                {
                  $b=false;
                  $marksAndCount[$value]["mark"]=$value;
                  foreach ($result as $key2=>$value2)
                  {
                    if ($value2["mark"]===$value)
                    {
                      $marksAndCount[$value]["count"]=$value2["count"];
                      $b=true;
                    }
                  }
                  if (!$b)
                  {
                    $marksAndCount[$value]["count"]=0;
                  }
                }
                $this->smarty->assign("marks", $marksAndCount);
              }
              $this->smarty->assign("examDate", "na všetkých");
            }
            else
            {
              if ($this->IsCourseToExam())
              {
                $query = "SELECT tE.date FROM tExam AS tE ".
                         "WHERE tE.id=".$this->getParams["examID"].";";
                $result = $this->db->QueryToRow($query);
                if ( $result != 0 )
                {
                  $this->smarty->assign("examDate", substr($result["date"],0,16));
                }
                
                $query = "SELECT tT.mark, COUNT(*) AS count FROM tTest AS tT, tExam AS tE ".
                         "WHERE tT.id_exam=tE.id AND tE.id_course=".$this->getParams["courseID"]." AND ".
                         "tE.id=".$this->getParams["examID"]." GROUP BY tT.mark ORDER BY tT.mark ASC;";
                $result = $this->db->QueryToArray($query);
                if ( $result != 0 )
                {
                  $marks=array("A", "B", "C", "D", "E", "Fx");
                  $marksAndCount=array();
                  foreach ($marks as $key=>$value)
                  {
                    $b=false;
                    $marksAndCount[$value]["mark"]=$value;
                    foreach ($result as $key2=>$value2)
                    {
                      if ($value2["mark"]===$value)
                      {
                        $marksAndCount[$value]["count"]=$value2["count"];
                        $b=true;
                      }
                    }
                    if (!$b)
                    {
                      $marksAndCount[$value]["count"]=0;
                    }
                  }
                  $this->smarty->assign("marks", $marksAndCount);
                }
              }
              else
              {
                $this->bAccessDenied=true;
              }
            }
            $this->smarty->assign("examID", $this->getParams["examID"]);
          }
          else
          {
            $this->bAccessDenied=true;
          }
        }
        else
        {
          $query = "SELECT tE.date, tE.id FROM tExam AS tE WHERE tE.id_course=".
                   $this->getParams["courseID"]." ORDER BY tE.date DESC;";
          $result = $this->db->QueryToArray($query);
          if ( $result != 0 )
          {
            foreach($result as $key=>$value)
            {
              $result[$key]["date"]=substr($result[$key]["date"],0,16);
            }
            $this->smarty->assign("examList", $result);
          }
          $this->smarty->assign("statisticsDateFrom", $_SESSION["statisticsDateFrom"]);
          $this->smarty->assign("statisticsDateTo", $_SESSION["statisticsDateTo"]);
        }
      }
      else
      {
        $this->bAccessDenied=true;
      }
      $this->smarty->assign("action", $this->action);
      $this->smarty->assign("course", $this->CourseName());
      $this->smarty->assign("courseID", $this->getParams["courseID"]);
      $this->smarty->assign("content","statistics.tpl");
      parent::Draw();
    }
	}
?>
