<?php
require_once("../../../../general/TaquaLB/Elements/Elements.php");
$configdir = "../../../../../../".$_POST['SubDir'];
require_once("../../../../general/config.php");
require("../../../../general/getinfo.php");
AllowUser("MngCourse");
//require("../../../../epconfig/GenScript/PHP/getinfo.php");

//function to form duplicate update querys
$duplupdquery = '';
function PushUpdateQuery($oldID,$NewID){
  global $duplupdquery;
  
  //1. Student Course Reg Update
  $q = "UPDATE coursereg_tb SET CoursesID = CONCAT('$NewID',SUBSTR(CoursesID,CHAR_LENGTH('$oldID')+1)) WHERE CoursesID LIKE '$oldID~%';";
  $q .= "UPDATE coursereg_tb SET CoursesID = REPLACE(CoursesID,'~$oldID~','~$NewID~') WHERE CoursesID LIKE '%~$oldID~%';";
  $q .= "UPDATE coursereg_tb SET CoursesID = CONCAT(SUBSTR(CoursesID,1,CHAR_LENGTH(CoursesID) - CHAR_LENGTH('$oldID')),'$NewID') WHERE CoursesID LIKE '%~$oldID';";

//2. Result
$q .= "UPDATE result_tb SET Rst = CONCAT('$NewID',SUBSTR(Rst,CHAR_LENGTH('$oldID')+1)) WHERE Rst LIKE '$oldID=%';";
$q .= "UPDATE result_tb SET Rst = REPLACE(Rst,'&$oldID=','&$NewID=') WHERE Rst LIKE '%&$oldID=%';";
$q .= "UPDATE result_tb SET RstInfo = REPLACE(RstInfo,'\"$oldID\":','\"$NewID\":') WHERE RstInfo LIKE '%\"$oldID\":%';";
$q .= "UPDATE result_tb SET Rept = REPLACE(Rept,':$oldID:',':$NewID:') WHERE Rept LIKE '%:$oldID:%';";
$q .= "UPDATE result_tb SET Outst = REPLACE(Outst,':$oldID:',':$NewID:') WHERE Outst LIKE '%:$oldID:%';";

//1. Staff Assigned Course Update
$q .= "UPDATE staff_tb SET Courses = CONCAT('$NewID',SUBSTR(Courses,CHAR_LENGTH('$oldID')+1)) WHERE Courses LIKE '$oldID~%';";
$q .= "UPDATE staff_tb SET Courses = REPLACE(Courses,'~$oldID~','~$NewID~') WHERE Courses LIKE '%~$oldID~%';";
$q .= "UPDATE staff_tb SET Courses = CONCAT(SUBSTR(Courses,1,CHAR_LENGTH(Courses) - CHAR_LENGTH('$oldID')),'$NewID') WHERE Courses LIKE '%~$oldID';";

$duplupdquery .= $q;
}

extract($_POST);
//exit($courses);
$courses = $dbo->DataArray($courses);
$det = json_decode($cdet,true);
$maxdata = (int)$courses['MaxDataRow'];
//get the course control details
$courseCntr = COURSE();
$Duplicate = $courseCntr['DuplicateCourse'] == "TRUE"?true:false;
//get the details 
$query = "";
$CousreCodes = [];
$CousreCHs = [];
$ResponseDuplicate = []; //for insert operation
$UpdateDuplicate = []; //for insert update operation (use to display popup)
$UpdateDuplicateTb = '';
$queryArray = [];
//loadtype

$UpdateDuplicateTb .= __Table("rowselect=true,style=width:calc(100% - 10px);text-align:left;margin:10px auto,id=dupltb,multiselect=false,data-type=table,rowalt=true");

$UpdateDuplicateTb .= __THeader(array("#","OLD","NEW"),"");
//exit('aa');
$cnt = 1;
$SesID = CurrentSesID();
$Info = [];
//loop through all courses
for($s=1;$s<=$maxdata;$s++){
    //get individual course details
    $CCode = $dbo->SqlSafe(rawurldecode($courses[$s.'_1']));
    $CTitle = $dbo->SqlSafe(rawurldecode($courses[$s.'_2']));
    $CH = (int)$dbo->SqlSafe(rawurldecode($courses[$s.'_3']));
   
    $StartSes = (int)$dbo->SqlSafe(rawurldecode($courses[$s.'_4']));
    $EndSes = (int)$dbo->SqlSafe(rawurldecode($courses[$s.'_5']));
    $Status = (int)$dbo->SqlSafe(rawurldecode($courses[$s.'_6']));
    $Elective = (int)$dbo->SqlSafe(rawurldecode($courses[$s.'_7']));
    $GroupID = (int)$dbo->SqlSafe(rawurldecode($courses[$s.'_8']));
    $NewcourseID = (int)$dbo->SqlSafe(rawurldecode($courses[$s.'_9']));
    $Deleted = rawurldecode($courses[$s.'_deleted']);
    //check if id exist (meaning course already exist and update operation is to be done)
    if(isset($courses[$s.'_10']) && (int)$courses[$s.'_10'] > 0){
        $courseID = (int)rawurldecode($courses[$s.'_10']);
        //if no other data sent - meaning delete operation
        if((trim($CCode) == "" && trim($CTitle) == "" && $CH == 0 && $StartSes == 0 && $Status == 0 && $GroupID == 0) || $Deleted == "true"){
            $queryArray[$s] = "DELETE FROM course_tb WHERE CourseID = $courseID";
        }else if(trim($CCode) != "" && trim($CTitle) != "" && $CH > 0){
            $GroupID = $GroupID==0?1:$GroupID; //if group id not set use the default
            //check if new course id can be set (only if the old course as expired)
            if($NewcourseID > 0 && ($EndSes >= $SesID || $EndSes == 0)){ //meaning the old course has not expired
                $NewcourseID = 0;
               $Info[]="New Course Not Assigned to $CCode. Course Not Expired Yet";
            }
            //$EndSes
            $queryArray[$s] = "UPDATE course_tb SET CourseCode = '$CCode', Title='$CTitle', CH=$CH, StartSesID=$StartSes,EndSesID=$EndSes, CourseStatus=$Status, GroupID=$GroupID, Elective=$Elective, Former=$NewcourseID WHERE CourseID = $courseID"; 
            
            $exist = array_search(trim(strtolower(str_replace(" ","",$CCode))),$CousreCodes);
            if($exist !== FALSE && $Duplicate == false && $CousreCHs[$exist][0] == (int)$CH){ //if duplicated
              //delete the duplicate course
              $queryArray[$exist] = "DELETE FROM course_tb WHERE CourseID = {$CousreCHs[$exist][1]}";
              $UpdateDuplicate[] = [$CousreCodes[$exist],$CousreCHs[$exist][0],$CousreCHs[$exist][1]]; //hod duplicate courses details
              
              $UpdateDuplicateTb .= __TRecord(array($cnt,strtoupper($CousreCHs[$exist][1].":".$CousreCHs[$exist][2]." ({$CousreCHs[$exist][0]})"),strtoupper($courseID.":".$CCode." ({$CH})")),"style=text-align:left");
              PushUpdateQuery($CousreCHs[$exist][1],$courseID);
              $cnt++;
            }
            $CousreCodes[$s] = trim(strtolower(str_replace(" ","",$CCode)));
            $CousreCHs[$s] = [(int)$CH,$courseID,$CCode];
        }
        

    }else{
        //cstudy=2&coursefac=5&coursedept=48&courseprog=49&courselvl=1&coursesemest=1
        $exist = array_search(trim(strtolower(str_replace(" ","",$CCode))),$CousreCodes);
        if($exist !== FALSE && $Duplicate == false && $CousreCHs[$exist][0] == (int)$CH){ //if duplicated
            $ResponseDuplicate[] = $CCode;
        }else{
            //if($CH == 0)exit("#Credit Hour Field is REQUIRED and must be greater than zero(0)");
            //Check if valid record supplied
            if(trim($CCode) != "" && trim($CTitle) != "" && $CH > 0){
                $GroupID = $GroupID==0?1:$GroupID; //if group id not set use the default
            //check if new course id can be set (only if the old course as expired)
            if($NewcourseID > 0 && ($EndSes >= $SesID || $EndSes == 0)){ //meaning the old course has not expired
                $NewcourseID = 0;
            $Info[]="New Course Not Assigned to $CCode. Course Not Expired Yet";
            }
            $queryArray[$s] = "INSERT course_tb SET CourseCode = '$CCode', Title='$CTitle', CH=$CH, StartSesID=$StartSes,EndSesID=$EndSes, CourseStatus=$Status, Former=$NewcourseID, Lvl = {$det['courselvl']}, DeptID = {$det['courseprog']}, Sem ={$det['coursesemest']}, StudyID ={$det['cstudy']}, GroupID= $GroupID , Elective=$Elective"; 
           $CousreCodes[$s] = trim(strtolower(str_replace(" ","",$CCode)));
        $CousreCHs[$s] = (int)$CH;
            }
        }
    
    }
}
$UpdateDuplicateTb .= ___Table();
if((int)$checkdupl == 1 && $Duplicate == FALSE && count($UpdateDuplicate) > 0){
    $rec = '<div class="rptbx" style="height:auto;width:100%"><div class="rptrow">
    <div style="width:80%" class="rptitem">'.count($UpdateDuplicate).' Duplicate<small>(s)</small></div>
    <div style="width:20%;text-align:center;cursor:pointer" onclick="_(\'dupltbcont\').ToggleShowHide()" class="altColor2Hover rptitem" title="View Details"><i class="fa fa-ellipsis-h"></i></div>
    <div style="clear:both"></div>
</div>
<div id="dupltbcont" style="display:none">
    ';
    echo "~".$rec;
    echo $UpdateDuplicateTb."</div>";
    
    echo"</div>";

    Note();
     echo 'All occurence of the Old course in Student/Staff Records <b style="font-weight:bold">(Course Registration, Result and Course Assigned)</b> will be updated to the equivalent New Course. <br/> You can Manage Duplicate Courses Settings in the <a href="javascript:OptionBox.Close();Page.OpenByTabName(\'csetting\')">Course Settings Module</a>';
    _Note();

    exit();
}
//

if(count($queryArray) > 0){
    $query = implode(";",$queryArray).";".$duplupdquery;
    if($loadtype == "import_1"){ //if it is from import and must replace existing one
        $delq = "UPDATE course_tb SET CourseStatus = 1 WHERE  Lvl = {$det['courselvl']} AND DeptID = {$det['courseprog']} AND Sem ={$det['coursesemest']} AND  StudyID ={$det['cstudy']};";
        $query = $delq.$query;
    }
    //exit($query);
    $dump = $dbo->Connection->multi_query($query);
    if(!$dump){
      echo "#Server Error: ".$dbo->Connection->error;
    }else{
        do {
            /* store first result set */
                            if ($result = $dbo->Connection->store_result()) {
                                /*while ($row = $result->fetch_row()) {
                                    printf("%s\n", $row[0]);
                                }*/
                                $result->free();
                            }
                            /* print divider */
                            if ($dbo->Connection->more_results()) {
                                //printf("-----------------\n");
                            }
                        } while ($dbo->Connection->next_result());
       echo "*Courses Saved";
       if(count($ResponseDuplicate) > 0){ //if ignored duplate courses exit
        echo '<div>Duplicate Courses '.strtoupper(implode(', ',$ResponseDuplicate)).' Ignored</div>';
       }
if(count($Info) > 0){
    echo '<div>'.implode('</div><div>',$Info).'</div>';
}
    }
   }else{
     echo "No Course Found";
   }
?>