<?php
error_reporting(0);
//require_once("../../../../epconfig/TaquaLB/Elements/Elements.php"); 
//require_once("../../../../general/TaquaLB/Elements/Elements.php");
$configdir = "../../../../../../".urldecode($_POST['SubDir']);
require_once("../../../../general/config.php");
require("../../../../general/getinfo.php");

ini_set('display_errors', FALSE);
ini_set('display_startup_errors', FALSE);
date_default_timezone_set('Africa/Lagos');

// $udet = AllowUser("RSheet");
if (PHP_SAPI == 'cli')
	die('This module should only be run from a Web Browser');

/** Include PHPExcel */
require_once $configdir.$dbo->Config['Require'].'Excel/CLASSES/PHPExcel.php';
extract($_POST);


$scorearr = ["*SC1"=>"ASSESSMENT","*SC2"=>"EXAM"];
$cnter = 1;
$rstScoreStruc = json_decode(urldecode($rstScoreStruc),true);
$rstScoreStrucMax = json_decode(urldecode($rstScoreStrucMax),true);
if(!is_null($rstScoreStruc) && count($rstScoreStruc) > 0){
   foreach($rstScoreStruc as $scr){
    $scorearr["*SC".$cnter] = $scr;
    $cnter++;
   }
}

//exit(json_encode($scorearr));
  //form data strings
  $discolDS = $dbo->DataArray($discol);
  $sdatastrDS = $dbo->DataArray($sdatastr);
  $rstparamDS = $dbo->DataArray($rstparam);

  //var fields = ["sestb","rstudstudy","rstudfac","rstuddept","rstudprog","rstudlvl","semest"];
  $Lvl = rawurldecode($rstparamDS["rstudlvl"]);
  $Sem = rawurldecode($rstparamDS["semest"]);
  $Ses = rawurldecode($rstparamDS["sestb"]);
  $progid = rawurldecode($rstparamDS["rstudprog"]);
  $courseID = rawurldecode($rstparamDS["rcourse"]);//
  $RstInfoID = rawurldecode($rstparamDS["RstInfoID"]);
  $classid = rawurldecode($rstparamDS["classid"]);

  $studyID = rawurldecode($rstparamDS["rstudstudy"]);
//hold errors
$errors = [];
  //Get the Result Course Details
  $courseDet = $dbo->SelectFirstRow("course_tb c, coursegroup_tb cg","c.*,cg.AllowFail","c.CourseID=$courseID AND c.GroupID = cg.ID LIMIT 1");
  if(!is_array($courseDet)){
    $errors[]="Invalid Course";
  }



  //get grading structure
  $grdstr = $dbo -> SelectFirstRow("resultinfo_tb","","ID = ".$RstInfoID);
  //$grdstr = $dbo -> SelectFirstRow("resultinfo_tb","","ID = (select GrdStrucID from school_tb limit 1)");
$classPassStr = $grdstr['ClassOfPass'];
$gradingStr = $grdstr['Grading'];

$grdstr = is_array($grdstr)?$grdstr[1]:"";
$schgrdstr = GetGradeDetAll($RstInfoID);
$classPassDetAll = GetClassPassDetAll($RstInfoID);

$grdarr = [];
$bigcond = "";
$grbrk = explode("&",$gradingStr);
$totcon = 0;
//grade field letter
 $grdletter = PHPExcel_Cell::stringFromColumnIndex(3+count($scorearr));
//$grdletter = "H";

foreach($grbrk as $indgrbrk){
   $grdbrk2 = explode("=",$indgrbrk);
   if(count($grdbrk2) == 2){ //if valid
      $range = $grdbrk2[0];
      $grdid = $grdbrk2[1];
      $gradchar = $schgrdstr[$grdid]['Grade'];
      //check if range value
      $rabgval = explode("-",$range);
      $rngchk = count($rabgval);
      
      if($rngchk == 2){ //if range
        $cond = "AND($grdletter?>=".$rabgval[0].", $grdletter?<=".$rabgval[1].")";
      }else{
        $cond = "$grdletter?".$range;  
      }
      $bigcond .= "IF(".$cond . ',"'.$gradchar.'",';
      $totcon++;
   }
}
  
if($totcon > 0){
    $bigcond .= '""'.str_repeat(")",$totcon); 
}



// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$type = (int)$_POST['updatetype'];
// Set document properties
$objPHPExcel->getProperties()->setCreator("eduporta")
							 ->setLastModifiedBy($udet['UserName'])
							 ->setTitle($courseDet['CourseCode']."_".$Lvl."_".$Sem."_".$Ses)
							 ->setSubject("Result Sheet - ".$courseDet['CourseCode']."_".$Lvl."_".$Sem."_".$Ses)
                             ->setDescription($courseDet['CourseCode']." | ".$courseDet['CourseTitle']." | ".$Lvl." | ".$Sem." | ".$Ses)
							 ->setKeywords($courseDet['CourseCode']." ".$courseDet['CourseTitle']." ".$Lvl." ".$Sem." ".$Ses)
							 ->setCategory("Result Sheet");

                             $totrw = (int)$sdatastrDS["MaxDataRow"]; //total datarow sent
  //$update string;
  //$query = "";
  $fcoursID = $courseID;
  $CH = (int)$courseDet['CH'];
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', "S/N")
            ->setCellValue('B1', "REGISTRATION NO.")
            ->setCellValue('C1', 'FULL NAME');
    //$scorearr
    $ind = 3;
    foreach($scorearr as $FN){
      $leter = PHPExcel_Cell::stringFromColumnIndex($ind);
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($leter.'1', $FN);
      $ind++;
    }
            /* ->setCellValue('D1', 'CA')
            ->setCellValue('E1', 'EXAM') */

$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue(PHPExcel_Cell::stringFromColumnIndex($ind).'1', 'TOTAL')
            ->setCellValue(PHPExcel_Cell::stringFromColumnIndex($ind + 1).'1', 'GRADE');
$jsondata = [];
$cnt = 1;
  //loop through each row
  for($rw=1; $rw <= $totrw; $rw++){
      $RegNo = rawurldecode($sdatastrDS[$rw."_".$discolDS['RegNo']]);
      $Name = rawurldecode($sdatastrDS[$rw."_".$discolDS['Name']]);
      $Readonly = rawurldecode($sdatastrDS[$rw."_readonly"]);
      $Disable = rawurldecode($sdatastrDS[$rw."_disable"]);
      $scores = [];
      //Get the student Current CA and Exam
      foreach($scorearr as $SCID => $FN){
        $SCID = ltrim($SCID,"*");
        $scores[$SCID] = (float)rawurldecode($sdatastrDS[$rw."_".$discolDS[$SCID]]);;
      }
    /*  $CA = rawurldecode($sdatastrDS[$rw."_".$discolDS['CA']]);
     $Exm = rawurldecode($sdatastrDS[$rw."_".$discolDS['Exm']]); */
     $RstCourseID = (int)rawurldecode($sdatastrDS[$rw."_".$discolDS['RstRegCID']]);
     $courseID = $RstCourseID > 0?$RstCourseID:$fcoursID;
     //exit($RstCourseID);
     //if(trim($RegNo) == "" || $Readonly == "true" || $Disable == "true" || (trim($CA) == "" && trim($Exm) == "") ){ //if no regno proceed to next row or result is outdated
      if(trim($RegNo) == "" || $Readonly == "true" || $Disable == "true" ){ //if no regno proceed to next row or result is outdated
        continue;
      }
      //$proccnumfailed = true; //proccess result based on allowable number of failed
      //$couseExistInRept = false;
//AK17/NAS/BIO/032
      //$bigcond

$disrw = $cnt + 1;
//$CA = (float)$CA; $Exm = (float)$Exm;
//$tot = $CA + $Exm;
$tot = 0;
$grds = GetGrade($tot,$grdstr,$schgrdstr);
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$disrw, $cnt)
            ->setCellValue('B'.$disrw, $RegNo)
            ->setCellValue('C'.$disrw, $Name);

            $ind = 3; $totformular = [];
    foreach($scores as $IndScr){
      $leter = PHPExcel_Cell::stringFromColumnIndex($ind);
      $objPHPExcel->setActiveSheetIndex(0)->setCellValue($leter.$disrw, $IndScr);
      $totformular[] = $leter.$disrw;
      $tot += $IndScr;
      $ind++;
    }
            /* ->setCellValue('D'.$disrw, $CA)
            ->setCellValue('E'.$disrw, $Exm) */

$objPHPExcel->setActiveSheetIndex(0)            
            ->setCellValue(PHPExcel_Cell::stringFromColumnIndex($ind).$disrw, "=".implode("+",$totformular))
            ->setCellValue(PHPExcel_Cell::stringFromColumnIndex($ind + 1).$disrw, "=".str_replace("?",$disrw,$bigcond));

            $jsondata[] = array_merge(["SN"=>$rw,"RegNo"=>$RegNo,"Name"=>$Name],$scores,["Tot"=>$tot,"Grade"=>$grds['Grade']]);
      //->setCellValue('G'.$disrw, $grds['Grade']);
            $cnt++;
     
     //

     //Form The result string
     
    //$tot = (float)$CA + (float)$Exm;

  }

  //disable cells
  $sheet = $objPHPExcel->getActiveSheet();
  if(trim($pasw) != ""){
    // $objPHPExcel->getSecurity()->setLockWindows(true);
    // $objPHPExcel->getSecurity()->setLockStructure(true);
    // $objPHPExcel->getSecurity()->setWorkbookPassword($pasw);
      $sheet->getProtection()->setPassword($pasw);
      $sheet->getProtection()->setSheet(true);    // Needs to be set to true in order to
  }

  //set auto size and borders
/**autosize*/
//for ($col = 'A'; $col != 'P'; $col++) {
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
//}


/** Borders for all data */
   $objPHPExcel->getActiveSheet()->getStyle(
    'A1:' . 
    $objPHPExcel->getActiveSheet()->getHighestColumn() . 
    $objPHPExcel->getActiveSheet()->getHighestRow()
)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

  $objPHPExcel->getActiveSheet()->getStyle('A1:C'.$cnt)->getFill()
  ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
  ->getStartColor()->setARGB('FFF0F0F0');
  $objPHPExcel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex(3+count($scores)).'1:'.PHPExcel_Cell::stringFromColumnIndex(3+count($scores)+1).$cnt)->getFill()
  ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
  ->getStartColor()->setARGB('FFF0F0F0');
  $objPHPExcel->getActiveSheet()->getStyle('D1:'.PHPExcel_Cell::stringFromColumnIndex(3+count($scores)-1).'1')->getFill()
  ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
  ->getStartColor()->setARGB('FFF0F0F0');
  //$objPHPExcel->getActiveSheet()->getStyle('B2')->getFill()->getStartColor()->setARGB('FFFF0000');

 /*  $sheet->getStyle('D1:E'.$cnt)
->getProtection()
->setLocked(
    PHPExcel_Style_Protection::PROTECTION_UNPROTECTED
); */


// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($courseDet['CourseTitle']." (".$courseDet['CourseCode'].")");


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

if($type == 0){ //.xlsx
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$courseDet['CourseCode']."_".$Lvl."_".$Sem."_".$Ses.'.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
}else if($type == 1){
    // Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$courseDet['CourseCode']."_".$Lvl."_".$Sem."_".$Ses.'.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
}else if($type == 2){
    header('Content-Type: application/csv');
header('Content-Disposition: attachment;filename="'.$courseDet['CourseCode']."_".$Lvl."_".$Sem."_".$Ses.'.csv"');
header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV')->setDelimiter(',')
    ->setEnclosure('"')
    ->setLineEnding("\r\n")
    ->setSheetIndex(0);
   // ->save(str_replace('.php', '.csv', __FILE__));
}else if($type == 3){ //json
    header('Content-Type: application/json; charset=utf-8');
    header('Content-Disposition: attachment;filename="'.$courseDet['CourseCode']."_".$Lvl."_".$Sem."_".$Ses.'.json"');
    header('Cache-Control: max-age=0');
    //header('Content-type: application/json; charset=utf-8');
//$arr = array ('a'=>1,'b'=>2,'c'=>3,'d'=>4,'e'=>5);
exit(json_encode($jsondata));
}else{ //html
    header('Content-Type: application/html');
    header('Content-Disposition: attachment;filename="'.$courseDet['CourseCode']."_".$Lvl."_".$Sem."_".$Ses.'.html"');
    header('Cache-Control: max-age=0');
    
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
}

$objWriter->save('php://output');
exit;
?>