<?php
//sleep(1000);
//LE - 22-5-17 #2
require_once("../../../../general/TaquaLB/Elements/Elements.php");
$configdir = "../../../../../../".$_POST['SubDir'];
require_once("../../../../general/config.php");
require("../../../../general/getinfo.php");
function Error($title,$text){
    Box("class=defaultTabText");
    Box("style=color:red");Icon("exclamation-triangle fa-3x");_Box();
    Box();echo $title;_Box();  
    Box();echo $text;_Box();  
  _Box();
  
  }
AllowUser("RUpload");
//require("../../../../epconfig/GenScript/PHP/getinfo.php");//hold basic functions to perform some common database operations or request

if($_POST['empty']){ 
     $header=array("*RegNo"=>"REGISTRATION NO.","*CA"=>"ASSESSMENT","*Exm"=>"EXAM","#Tot"=>"TOTAL","#Grd"=>"GRADE","Rmk"=>"REMARK");
     Box("class=ep-animate-opacity");
      SpreadSheet("rowselect=false,style=width:calc(100% - 12px);margin:auto;margin-top:6px;margin-bottom:6px,id=sprstupload,multiselect=false,cellfocus=,cellblur=Exams.ResultUpload.OnBlur,cellkeypress=Exams.ResultUpload.CellKeyPress,readonly=Tot;Grd,dynamiccolumn=true,dynamicrow=true,minrow=-1,dependables=CA:Grd~Tot;Exm:Grd~Tot,rowdelete=false",$header);
      _Box();
        exit(); 
}
$POST = array();
$POSTTXT = array();
$detarr = array("sestb","rstudstudy","rstudfac","rstuddept","rstudprog","rstudlvl","semest","rcourse");
foreach($detarr as $key){
    $pst = $_POST[$key];
     $expst = explode(":",$pst);
    $POST[$key] = $expst[0];
    $POSTTXT[$key] = rawurldecode($expst[1]);
}
extract($POST);
//["sestb","rstudstudy","rstudfac","rstuddept","rstudprog","rstudlvl","semest","rcourse"]
if(isset($sestb)){ //if result details send
  // print_r($_POST);
  $dump = array(); //hold all data to be loaded in spreadsheet
 // $filtertb = isset($filtertb)?(int)$filtertb:1; //get the filter value 22-5-17 #1

 //Get the approval status
 //************************************************* */
  $queryapprv = "SELECT * FROM resultapprove_tb WHERE ProgID = $rstudprog AND Ses = $sestb AND Lvl = $rstudlvl AND Sem = $semest AND CourseID = $rcourse AND StudyID = $rstudstudy LIMIT 1";
  $apprst = $dbo->RunQuery($queryapprv);
  $apprv = false;//hold the result approval status 
  $editable = true; //indicate if result will be editable
  if(is_array($apprst)){
      if($apprst[1] > 0){
          $apprstrw = $apprst[0]->fetch_array();
          if($apprstrw['Status'] == "TRUE"){
              $apprv = true;
          }
          if($apprstrw['Editable'] == "FALSE"){
            $editable = false;
        }
      }
  }
//************************************************* */


  //if to load from import operation
  //************************************************* */
  $importdata = [];
if(isset($_POST['import']) && (int)$_POST['import'] == 1 && $apprv == false && $editable == true){
  
require_once("../../../../epconfig/TaquaLB/Ajax/CGI/PHP/Excel/CLASSES/PHPExcel.php");
include "../Gen/loadexcel.php";
 $colregno = $_POST['colregno'];
    $colca = $_POST['colca'];
    $colexam = $_POST['colexam'];
    
   $excellarray = ExcelToArray("sprstupload_file",$colregno,[$colca,$colexam]);
  // var_dump($excellarray);
   //exit;
   //exit($excellarray);
if(is_array($excellarray)){
    $importdata = $excellarray;
}

}
//************************************************* */

$oldcourseid = 0;
$coursesarr = [$rcourse];
//get the old copy of this course if exist
$oldcourse = $dbo->SelectFirstRow("course_tb","","Former=".$rcourse);
if(is_array($oldcourse)){ //if old course exist
  $oldcourseid = (int)$oldcourse['CourseID'];
  $coursesarr[] = $oldcourseid;
}

//Load Result of all Registered Student
//************************************************* */
$query = "";
foreach($coursesarr as $indcourseid){
  $query .= $query != ""?" UNION ":"";
  //query to get all registered student for the course
  $query .= "SELECT c.RegNo,r.Rst,s.SurName, s.FirstName, s.OtherNames, r.ID as RstID,r.GroupID,r.RstInfo,'$indcourseid' as CourseID,s.StartSes FROM coursereg_tb c LEFT JOIN studentinfo_tb s ON c.RegNo = s.RegNo OR c.RegNo = s.JambNo LEFT JOIN result_tb r ON (c.RegNo = r.RegNo  AND c.Sem = r.Sem AND c.Lvl = r.Lvl AND c.SesID = r.SesID) WHERE (c.CoursesID LIKE '{$indcourseid}~%' OR c.CoursesID LIKE '%~{$indcourseid}'  OR c.CoursesID LIKE '%~{$indcourseid}~%') AND  c.SesID = {$sestb} AND c.Sem = {$semest}";
}



  $query .= " ORDER BY StartSes DESC, RegNo ASC, GroupID DESC";//c.Lvl = {$rstudlvl} AND
//echo $query;
//exit($query);
  $rst = $dbo->RunQuery($query);

  //preapare the spreadsheet header
  $header=array("*RegNo"=>"REGISTRATION NO.","*CA"=>"ASSESSMENT","*Exm"=>"EXAM","#Tot"=>"TOTAL","#Grd"=>"GRADE","-RstRegCID"=>"CourseID");//,"Rmk"=>"REMARK"
  $readonly = "Tot;Grd";
  $rowinsert = "true";

  //Get the school result setting
  $grdstr = $dbo -> SelectFirstRow("resultinfo_tb","","ID = (select GrdStrucID from school_tb limit 1)");
          $grdstr = is_array($grdstr)?$grdstr[1]:"";

          //Get All the Grading
          $schgrdstr = GetGradeDetAll();

          //Get the Grade base on the score (Score = -1 get for all range of score)
          //keep the grading structure (formated) in an hidden element for easy access on spreadsheet
          Box("id=gradeStruc,style=display:none");
          echo GetGrade(-1,$grdstr,$schgrdstr);
          _Box();
           //Hidden("gradeStruc",GetGrade(-1,$grdstr,$schgrdstr));
 // echo $query;
 $studreg = true; //hold if details from course reg or directly from result
  if(is_array($rst)){
       //if no student found based on course registration, get student from result_tb
      if($rst[1] < 1){
        $nruery = "";
foreach($coursesarr as $indcourseid){
  $nruery .= $nruery != ""?" UNION ":"";
$nruery .= "SELECT *,ID as RstID, '$indcourseid' as CourseID FROM result_tb WHERE Sem=$semest AND SesID=$sestb AND (Rst LIKE '%&{$indcourseid}=%' OR Rst LIKE '{$indcourseid}=%')";
}
           

           $nruery .= " ORDER BY RegNo ASC, GroupID DESC";
           $rst = $dbo->RunQuery($nruery);
           $studreg = false;
      }
     // if student found
      if($rst[1] > 0){
          
          $lstReg = "";
          $lca = ""; $lex = "";
          $RstArr = array();
          $RstIDs = array();
          //Loop through all student and form the result data for each student
          while($reg = $rst[0]->fetch_array()){
              //22-5-17 #2 check student class by filter to dertemin which set of student to load - stoped
              /*if($filtertb > 1){ //if the filter is not all
               //get student level
              // $currentstudLvl = StudLevel($reg["RegNo"]);
              // $rstudlvl
                if($filtertb == 2){
                    
                }
              }*/
              $rwID = $reg['RstID'];
              if(in_array($rwID,$RstIDs))continue;
              $RstIDs[] = $rwID;
              $rwID = "0"; //default rwID(ResultID) which indicate no course result found for the current student
              $ca = ""; $ex = ""; $tot = ""; $grd="";$studrst = "";
              if($studreg){ //if student details gotten via coursereg and studentinfo tb
                //concatinate the names
               $name = $reg[2]." ".$reg[3]." ".$reg[4];
              }

              $cid = $reg['CourseID'];
              $rcourse = $cid; //update the sent corse id to the student result course id for merged courses

              $grds="";

              //If student result is from uploaded excell file and it exist
              if(count($importdata) > 0 && isset($importdata[strtolower(trim($reg["RegNo"]))])){
                 $rstimp = $importdata[strtolower(trim($reg["RegNo"]))]; //get the student result ca and exam
                 $ca = (float)$rstimp[0]; //ca
                        $ex = (float)$rstimp[1]; //exam
                        $tot = $ca + $ex; //total
                        $grds = GetGrade($tot,$grdstr,$schgrdstr); //get the grade based on total score
                       $grds = $grds["Grade"]; //Get the score grade string
                       $rwID = $reg['RstID']; //the student rslt ID from result tb, if exist
              }elseif($reg["Rst"] != ""){ //if not from exel file and student has result
                //break down the result string to get the loaded course result
                  $rstarr = explode("&",$reg["Rst"]);
                  //exit(json_decode($reg['RstInfo'],true));
                  $rstinfo = (trim($reg['RstInfo']) == "" || trim($reg['RstInfo']) == "[]")?[]:json_decode($reg['RstInfo'],true);
                  
                  
                  foreach($rstarr as $rststr){
                    //result structure new
                    //CourseID=ca|Exam|GrdPoint|Grade|GrDescr|Pass(0 or 1)
                      $rstbr = explode("=",$rststr);
                      //check if the current student result is the selected course
                      if((int)$rstbr[0] == (int)$rcourse){
                        //get the ca and exam
                        $scorarr = explode("|",$rstbr[1]);
                        $ca = $scorarr[0];//ca
                        $ex = $scorarr[1]; //exam
                        $tot = $ca + $ex; //total
                        $rstgrd = isset($rstinfo[$rcourse]['GradeStruc'])?$rstinfo[$rcourse]['GradeStruc']:"";
                        if(trim($rstgrd) == ""){ //if grading details not exist
                          //get the grade
                          $grds = GetGrade($tot,$grdstr,$schgrdstr); 
                          
                        }else{
                          //exit($rstgrd);
                          $grds = GetGradeFromString($tot,$rstgrd);
                          //$grds = $scorarr[3];
                        }
                        $grds = $grds["Grade"];
                        //set the result ID, to be use to uniquelly identify the result during saving, cos if duplicate(updated result exist, the id will be the only identifying field)
                        break;
                      }
                  }
                  
              }
              if($studreg){//if student details gotten via coursereg and studentinfo tb
            $dump[] = array($reg["RegNo"],$name,$ca,$ex,$tot,$grds,$cid,"ID"=>$rwID);
              }else{ //if directely from result, student name not identified
               $dump[] = array($reg["RegNo"],$ca,$ex,$tot,$grds,$cid,"ID"=>$rwID);   
              }

              //Check if Updated Copy (if the last loaded student is same as the current student)
              if($lstReg == $reg["RegNo"]){ //if updated copy exist and ca/ex not the same
               //if($lca != $ca || $lex != $ex){
                 //check if the old score is not the current one (display as updated result)
                 //$RstArr - hold array of student result in this format - "RegNo"."|".$ca."|".$ex (use as lookup if student result is a duplicate)
               if(!in_array($reg["RegNo"]."|".$ca."|".$ex,$RstArr)){
                  $dump[count($dump) - 1]["logo"] = "#history";
                  $dump[count($dump) - 1]["info"] = "Outdated Copy";
                  $dump[count($dump) - 1]["disable"] = "true";
               }else{ //if duplcate
                   //remove from array i.e - duplicate result 
                   array_pop($dump);
               }
              }

              //check if a valid student
              $checkstud = $dbo->SelectFirstRow("studentinfo_tb","RegNo","RegNo='".$reg["RegNo"]."' OR JambNo='".$reg["RegNo"]."' LIMIT 1");
              
              if(!is_array($checkstud)){ //if student not exist
              
                $dump[count($dump) - 1]["logo"] = "#exclamation-triangle";
                  $dump[count($dump) - 1]["info"] = "Invalid Student";
                  $dump[count($dump) - 1]["disable"] = "true";
              }
             // if(!array())
              $lstReg = $reg["RegNo"]; //keep the regno as last loaded, to be use to check next student if updated result
              $RstArr[] = $reg["RegNo"]."|".$ca."|".$ex; //keep the student result to check duplicate result for the next student
              $lca = $ca; 
              $lex = $ex;
          }

         // Form the spreadsheet Header
         if($studreg){//if student details gotten via coursereg and studentinfo tb
          $header=array("*RegNo"=>"REGISTRATION NO.","*Name"=>"FULL NAME","*CA"=>"ASSESSMENT","*Exm"=>"EXAM","#Tot"=>"TOTAL","#Grd"=>"GRADE","-RstRegCID"=>"CourseID");//,"Rmk"=>"REMARK"
          //if result already approved disable entering and row insert
          $readonly = ($apprv || $editable == false)?"RegNo;Name;Tot;Grd;CA;Exm":"RegNo;Name;Tot;Grd";
           $rowinsert = "false";
         }else{
           $header = array("*RegNo"=>"REGISTRATION NO.","*CA"=>"ASSESSMENT","*Exm"=>"EXAM","#Tot"=>"TOTAL","*Grd"=>"GRADE","-RstRegCID"=>"CourseID");//,"Rmk"=>"REMARK"
           //if result already approved disable entering and row insert
           $readonly = ($apprv || $editable == false)?"RegNo;Tot;Grd;CA;Exm":"Tot;Grd";
//note - for result from result_tb only row insert is enabled if not approved
           $rowinsert = ($apprv || $editable == false)?"false":"true";
         }
           
      }else{ //if no student register for the course and no result exist
         $header = array("*RegNo"=>"REGISTRATION NO.","*CA"=>"ASSESSMENT","*Exm"=>"EXAM","*Tot"=>"TOTAL","*Grd"=>"GRADE","-RstRegCID"=>"CourseID");//,"Rmk"=>"REMARK"
           $readonly = ($apprv || $editable == false)?"RegNo;Tot;Grd;CA;Exm":"Tot;Grd";
           $rowinsert = ($apprv || $editable == false)?"false":"true"; 
      }
  }
 
  //Display the Spreadsheet
  Box("class=ep-animate-opacity,id=rstshinner");
	    SpreadSheet("rowselect=false,style=width:calc(100% - 12px);margin:auto;margin-top:0px;font-size:0.85em;margin-bottom:6px,id=sprstupload,multiselect=false,cellfocus=,cellblur=Exams.ResultUpload.OnBlur,cellkeypress=Exams.ResultUpload.CellKeyPress,disable=$readonly,dynamiccolumn=false,dynamicrow=$rowinsert,minrow=-1,dependables=CA:Grd~Tot;Exm:Grd~Tot,rowfilter=true,filtertitle=FILTER RESULT SHEET,filterstyle=width:calc(100% - 12px);margin:auto,rowdelete=false",$header,$dump);
        //"rstudstudy","rstudfac","rstuddept","rstudprog","rstudlvl","semest","rcourse"
//Display Result Details Bellow
   echo '<div style="margin-left:20px;color:#CCC;font-weight:bold">'.strtoupper($POSTTXT['sestb']." | ".$POSTTXT['rstudstudy']." | ".$POSTTXT['rstudfac']." | ".$POSTTXT['rstudprog']." | ".$POSTTXT['rstudlvl']." | ".$POSTTXT['semest']." SEMESTER").'</div>';

   //echo the import file type
   echo '<input type="file" id="sprstupload_file" name="sprstupload_file" onchange="Exams.ResultUpload.PerformImport(this)" style="position:absolute;display:none" accept=".xls,.xlsx" />';
_Box();
//keep parameters in hidden elemts
   Hidden("dispdet",$dbo->DataString($POSTTXT));
   Hidden("dispdetid",$dbo->DataString($POST));
   Hidden("dispdetappr",$apprv);
     //Hidden("dispdet",implode(";",$POSTTXT));
}

?>