<?php
/*

ALTER TABLE `tblLearner` DROP FOREIGN KEY `tblLearner.strRegion`;
ALTER TABLE `tblLearner` ADD CONSTRAINT `tblLearner.strRegion` FOREIGN KEY ( `refRegionID` ) REFERENCES `tblRegion` ( `RegionID` ) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE `tblLearner` DROP FOREIGN KEY `tblLearner.strCountry`;
ALTER TABLE `tblLearner` ADD CONSTRAINT `tblLearner.strCountry` FOREIGN KEY ( `refCountryID` ) REFERENCES `tblCountry` ( `CountryID` ) ON DELETE CASCADE ON UPDATE CASCADE ;

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblLearner` DROP FOREIGN KEY `tblLearner.strPractice`;
ALTER TABLE `tblLearner` ADD CONSTRAINT `tblLearner.strPractice` FOREIGN KEY (`refPracticeID`) REFERENCES `tblPractice` (`PracticeID`) ON UPDATE CASCADE;

ALTER TABLE `tblLearner` drop FOREIGN KEY `tblLearner.strUserType`;
ALTER TABLE `tblLearner` ADD CONSTRAINT `tblLearner.strUserType` FOREIGN KEY (`refUserTypeID`) REFERENCES `tblUserType` (`UserTypeID`) ON UPDATE CASCADE;

 *
 * 20110829 - keeps selected entities in the list after re-filter when in Add mode - pj
 * 20110831 - removed Project Mark, renamed Project to Portfolio - pj

 * 20161024 - added foreign key for practice ddl- neils
 * 20161025 - ph2 - default practice to null on new learner- christiaan
 * 20161025 - added sublist function getPracticeLearnerList() for leaner list in practice details view - neils
 * 20170323 - Modify list include UserCategory and add tblUserCategory to joins- CL
 //20170403 - removed tblLearner.strUserType - pj
 //20180314 - User login for Webadmin Administrators via redirect- CL //added column to list query
 // 20181203 - PHP7 Conversion non-static method cannot be called statically - Rachel
 //20190314 - Added Organisation to 'Export Learners' excel - Rachel

 */

include_once("_nemo.list.cls.php");

class Learner extends NemoList
{
   private $ID = 0;

   public function __construct($DataKey)
   {
      $this->Filters[frSearch]->tag = "input";
      $this->Filters[frSearch]->html->value = "";
      $this->Filters[frSearch]->html->type = "text";
      $this->Filters[frSearch]->html->class = "controlText";

      $this->Filters[frRegion]->tag = "select";
      $this->Filters[frRegion]->html->value = "0"; //defalt value = 0, -1 = '- Unassigned -'
      $this->Filters[frRegion]->html->class = "controlText";
      $this->Filters[frRegion]->sql = "SELECT 0 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT `RegionID` AS ControlValue, concat(strRegion,' (',count(refRegionID),')') AS ControlText
                        FROM tblRegion INNER JOIN tblLearner ON tblRegion.RegionID = tblLearner.refRegionID
                        WHERE tblRegion.blnActive = 1
                        GROUP BY RegionID, strRegion
                        ORDER BY ControlText ASC";

      $this->Filters[frStatus]->tag = "select";
      $this->Filters[frStatus]->html->value = "-1";
      $this->Filters[frStatus]->html->class = "controlText";
      $this->Filters[frStatus]->sql = "SELECT -1 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT 1 AS ControlValue, 'Active' AS ControlText
                        UNION ALL
                        SELECT 0 AS ControlValue, 'Inactive' AS ControlText

                        ORDER BY ControlText ASC";

      parent::__construct($DataKey);
   }

   //20161025 - added sublist function getPracticeLearnerList() for leaner list in practice details view - neils
   //20180314 - User login for Webadmin Administrators via redirect- CL //added column to list query
   public function getList($PracticeID=0)
   {
      global $SystemSettings, $xdb;
      //print_rr($_POST);
      //ini
      $Where = "";
      //sql where

      if($PracticeID != 0)
      {
         $Where .= " AND refPracticeID = ". $xdb->qs($PracticeID);
      }else{

         if($this->Filters[frSearch]->html->value != "")
         {
            $like = "LIKE(". $this->db->qs("%".$this->Filters[frSearch]->html->value."%") .")";
            $Where .= " AND (tblLearner.LearnerID $like OR tblLearner.strLearner $like OR tblLearner.strEmail $like OR tblLearner.strPhone $like)";
         }
         if($this->Filters[frRegion]->html->value != 0)
         {
            $Where .= " AND tblRegion.RegionID = ". $this->Filters[frRegion]->html->value;
         }
         if($this->Filters[frStatus]->html->value != -1)
         {
            $Where .= " AND tblLearner.blnActive = ". $this->Filters[frStatus]->html->value;
         }
      }

      //20170323 - Modify list include UserCategory and add tblUserCategory to joins- CL
      //20180314 - User login for Webadmin Administrators via redirect- CL //added column to list query

      $cID = $xdb->getRowSQL("SELECT tblLearner.LearnerID FROM sysUser
         INNER JOIN tblLearner ON sysUser.strEmail = tblLearner.strEmail
         WHERE UserID = ".$_SESSION['USER']->ID, 0);

      $this->ListSQL("
         SELECT tblLearner.LearnerID, tblLearner.strLearner AS Learner, CASE WHEN refRegionID < 0 THEN tblCountry.strCountry ELSE tblRegion.strRegion END AS 'Region/Country'
            , concat(tblUserType.strUserType,' - ',tblUserCategory.strUserCategory) AS 'User Category - User Type', tblLearner.strPhone AS Phone, tblLearner.strEmail AS Email, tblLearner.dtRegistered as 'Date Registered', tblLearner.blnActive AS Active, tblLearner.strLastUser AS 'Last User', tblLearner.dtLastEdit AS 'Last Edit' 
            , concat(\"<a target='blank_' href='./learner.php?Action=RedirectWebadminToLearner&LearnerID=\", tblLearner.LearnerID, \"&CoachID=".$cID->LearnerID."'>Login</a>\") as 'Login'
         FROM tblUserCategory INNER JOIN (tblUserType INNER JOIN (tblCountry INNER JOIN (tblRegion INNER JOIN tblLearner ON tblRegion.RegionID = tblLearner.refRegionID) ON tblCountry.CountryID = tblLearner.refCountryID) ON tblUserType.UserTypeID = tblLearner.refUserTypeID) ON tblUserCategory.UserCategoryID = tblUserType.refUserCategoryID
         WHERE 1=1 $Where
         ORDER BY tblLearner.strLearner",0,"learner.php");

      return $this->renderTable("Learner List");
   }

   public function getCourseLearnerList($CourseID, $output="HTML")
   {
      global $xdb;
      //ini

      $this->isPageable =
      $this->isSortable = 0;


      $UserID = $_SESSION["USER"]->ID;

      $rowBusinessCoach = $xdb->getRowSQL("SELECT * FROM sysUser WHERE UserID = '$UserID'",0);
      $WHERE_LEARNER_COURSE = "";

      if($rowBusinessCoach->blnPracticeConsultant == 1 && $CourseID == 612){
         $WHERE_LEARNERCOURSE .= " AND refBusinessCoachID = '$UserID' ";
      }

      if($_REQUEST[blnAllCourses] != 1){
         $WHERE_COURSE = "AND CourseID=$CourseID";
         $WHERE_LEARNERCOURSE .= "AND tblLicence.refCourseID=$CourseID";
      }
      //where
      //$this->ListSQL("
      //   SELECT tblLearner.LearnerID, tblLearner.strLearner AS Learner, CASE WHEN refRegionID < 0 THEN tblCountry.strCountry ELSE tblRegion.strRegion END AS 'Region/Country', tblLearner.strPhone AS Phone, tblLearner.strEmail AS Email, tblLearner.strLastUser AS 'Last User', tblLearner.dtLastEdit AS 'Last Edit'
      //
      //   FROM tblLicence INNER JOIN ((tblLearner INNER JOIN tblRegion ON tblLearner.refRegionID = tblRegion.RegionID) INNER JOIN tblCountry ON tblLearner.refCountryID = tblCountry.CountryID) ON tblLicence.refLearnerID = tblLearner.LearnerID
      //   WHERE tblLicence.refCourseID = ". $this->db->qs($CourseID) ."
      //   GROUP BY tblLearner.LearnerID, tblLearner.strLearner
      //   ORDER BY tblLearner.strLearner",0,"learner.php"); //, concat('<input type=checkbox value=\"',tblLearner.strEmail,'\" id=chkSelect name=\"chkSelect[',LearnerID,']\" >')  as 'Select'

      //get all Course Assignments
      $rst = $xdb->doQuery("
         SELECT modAssignment.AssignmentID, modAssignment.intMarks, modAssignment.strType, intPassRate, CourseID
         FROM tblCourse INNER JOIN (modAssignment INNER JOIN modModule ON modAssignment.refModuleID = modModule.ModuleID) ON refCourseID = CourseID
         WHERE modAssignment.strType IN ('Assignment','Upload') AND modModule.blnActive = 1 AND modAssignment.blnActive = 1 $WHERE_COURSE
         ",0);// AND CourseID=$CourseID

      while($row = $xdb->fetch())
      {
         $arrAssi[$row->CourseID][$row->AssignmentID] = $row;
         //$dblAssiMarks += $row->intMarks;
         $dblAssiMarks[$row->CourseID] += $row->intMarks;
         $intPassRate = $row->intPassRate;
      }//print_rr($arrAssi[113]);

      /*
      foreach($arrAssi as $CID => $arr){
         //echo "<br>";
         $strAssignmentIDs .= "'". @implode("','", array_keys($arr)) ."'";
         //echo implode("<br>", array_keys($arr));
      }
      //$strAssignmentIDs = "'". @implode("','", array_keys($arrAssi)) ."'";


      //get all Course Assessments
      $rst = $xdb->doQuery("
         SELECT modAssessment.*, modModule.refCourseID
         FROM modAssessment INNER JOIN modModule ON ModuleID = refModuleID
         WHERE modAssessment.refAssignmentID In ($strAssignmentIDs) AND dtAssessment <> '0000-00-00' AND refLearnerID = 1048
         ORDER BY modAssessment.refLearnerID, modAssessment.AssessmentID",1);*/
      $rst = $xdb->doQuery("
         SELECT modAssessment.*, modModule.refCourseID
         FROM (tblCourse INNER JOIN (modAssignment INNER JOIN modModule ON modAssignment.refModuleID = modModule.ModuleID) ON tblCourse.CourseID = modModule.refCourseID) INNER JOIN modAssessment ON modAssignment.AssignmentID = modAssessment.refAssignmentID
         WHERE modAssignment.strType IN ('Assignment','Upload') AND modModule.blnActive = 1 AND modAssignment.blnActive = 1 AND dtAssessment <> '0000-00-00' $WHERE_COURSE
         ORDER BY modAssessment.refLearnerID, modAssessment.AssessmentID",0);
      while($row = $xdb->fetch())
      {//print_rr($row);
         $arrAsse[$row->refCourseID][$row->refLearnerID][$row->refAssignmentID] = $row;
      }//print_rr($arrAsse[113]); die;


      //vd($strAssignmentIDs);

      if($output == "EXCEL")
      {//20170403 - removed tblLearner.strUserType - pj
         //20190314 - Added Organisation to 'Export Learners' excel - Rachel
         // $sqlExtra = ", strName as Name, strSurname as Surname, strPhone as Phone, txtAddress AS Address, tblRegion.strRegion as Region, dtDateIssued AS 'Date Course Registered', tblCourse.strCourse as Course";
         
         if($CourseID == 613){
            $sqlExtra = ", strName as Name, strSurname as Surname, strPhone as Phone, txtAddress AS Address, tblRegion.strRegion as Region, strCompany AS 'Organisation', dtDateIssued AS 'Date Course Registered', tblCourse.strCourse as Course";
         }else{
            $sqlExtra = ", strName as Name, strSurname as Surname, strPhone as Phone, txtAddress AS Address, tblRegion.strRegion as Region, strCompany AS 'Organisation', dtDateIssued AS 'Date Course Registered', tblCourse.strCourse as Course, '' AS 'Has a No Answer'";
         }
      }
      //get learners

       $this->ListSQL("
         SELECT tblCourse.CourseID, tblCourseLearner.LearnerID, tblLearner.strLearner AS Learner, tblLearner.strEmail AS Email $sqlExtra , 0 AS Assignments, 0 AS 'Marks', 0 AS '% Achieved', '' AS 'Has a No Answer'
         FROM (tblRegion INNER JOIN ((SELECT tblLearner.LearnerID, tblLicence.refCourseID as CourseID, Min(tblLicence.dtDateIssued) as dtDateIssued
            FROM tblLicence INNER JOIN tblLearner ON tblLicence.refLearnerID = tblLearner.LearnerID
            WHERE tblLicence.strStatus='Active' $WHERE_LEARNERCOURSE
            GROUP BY tblLearner.LearnerID, tblLicence.refCourseID
         )  AS tblCourseLearner INNER JOIN tblLearner ON tblCourseLearner.LearnerID = tblLearner.LearnerID) ON tblRegion.RegionID = tblLearner.refRegionID) INNER JOIN tblCourse ON tblCourseLearner.CourseID = tblCourse.CourseID

         ORDER BY tblLearner.strLearner;
         ", 0,"learner.php"); //, tblCourse.strCourse,


      if(count($this->Data) > 0){
      foreach($this->Data as $idxRow => &$row)
      {//print_rr($row);

         $dblAsseMarks = 0;

         if(count($arrAssi[$row[CourseID]]) > 0)
         {

               //print_rr($arrAsse[$row[LearnerID]]);
            if(is_array($arrAsse[$row[CourseID]][$row[LearnerID]]))
            {

               //print_rr($row);
               foreach($arrAsse[$row[CourseID]][$row[LearnerID]] as $AssignmentID => &$rowAsse)
               {//print_rr($rowAsse->dblAssignmentMark);


                  if(!$rowAsse->dblAssignmentMark) $rowAsse->dblAssignmentMark = $arrAssi[$row[CourseID]][$rowAsse->refAssignmentID]->intMarks * $rowAsse->percAssignmentMark / 100;
                  $dblAsseMarks += round($rowAsse->dblAssignmentMark,1);

                  //delete later
                  //print_rr($rowAsse->percAssignmentMark);
                  //$dblAsseMarks = $xdb->Rows;

                  //post
                  if($rowAsse->percAssignmentMark < $intPassRate)
                     unset($arrAsse[$row[CourseID]][$row[LearnerID]][$AssignmentID]);

               }
            }
            //print_rr($arrAsse[$row[LearnerID]]);
            $row["Assignments"] = count($arrAsse[$row[CourseID]][$row[LearnerID]]) ." of ". count($arrAssi[$row[CourseID]]);
            $row["Marks"] = @round($dblAsseMarks,0) ."/". $dblAssiMarks[$row[CourseID]];
            $row["% Achieved"] = @round($dblAsseMarks / $dblAssiMarks[$row[CourseID]] *100,0) ." %";
            $row[percMark] = @$dblAsseMarks / @$dblAssiMarks[$row[CourseID]];
         }else{
            $row["Assignments"] =
            $row["Marks"] =
            $row["% Achieved"] = "-";
            $row[percMark] = null;
         }
         //print_rr($row); //die;

         //20130125 - added formatting for pass rate - pj
         if($row[percMark] === null || $row[percMark] === 0)
         {
            
            $row["Has a No Answer"] = "N/A";
            $row["% Achieved"] = "<span style='color: grey;'>".$row["% Achieved"]."</span>";
         }elseif($row[percMark] >= ($intPassRate/100)){
            $row["Has a No Answer"] = "<span style='color: green;'>No</span>";
            $row["% Achieved"] = "<span style='color: green;'>".$row["% Achieved"]."</span>";
         }else{
            $row["Has a No Answer"] = "<span style='color: red;'>Yes</span>";
            $row["% Achieved"] = "<span style='color: red;'>".$row["% Achieved"]."</span>";
         }

         unset($row[percMark]);
         unset($row[CourseID]);
         //print_rr($row);
         //die;
      }}
//print_rr($this->Columns);
      unset($this->Columns[CourseID]);
      if($CourseID == 613){
         unset($this->Columns["% Achieved"]);
         unset($this->Columns["Marks"]);
         unset($this->Columns["Assignments"]);

      }else{
         unset($this->Columns["Has a No Answer"]);

      }

      return $this->renderTable("Learner List");
   }


   public function GetAssessments($SelectionCriteria)
   {
      //20110831 - removed Project Mark, renamed Project to Portfolio - pj
      global $xdb, $TR, $BR, $SP;
      //INI
      $required = $this->SystemSettings[imgRequired];
      $arrChecked = array(0 => "", 1 => "checked");
      $arrStatus = array("" => "n/a", "C" => "C", "NYC" => "<span class='textColour'>NYC</span>");
      $strToday = date("'Y-m-d'");
      $intRow = 0;

      if($this->isSelectable == 1)
      {
         $thSelect = "<th rowspan=2 class='linkbutton2 textColour' onclick='jsToggleSelect();' >Select</th>";
      }

      $thHeadings = "
         <tr>
            <th rowspan=2>#</th>
            <th rowspan=2>Learner</th>
            <th rowspan=2>ID</th>
            <th rowspan=2>Assessed</th>
            <th rowspan=2>Date</th>
            <th colspan=2>% Marks</th>
            <th colspan=4 style=''>Compentency</th>
            <th colspan=3>Moderation</th>
            <th colspan=2>Inseta</th>
            <th colspan=2>NLRD</th>
            <th colspan=3>Certificate</th>
            <th rowspan=2>Notes</th>
            <th>Last User</th>
            $thSelect
         </tr>
         <tr>
            <!-- <th>Project</th> --!>
            <th>Research</th>
            <th>Test</th>
            <th>Portfolio</th>
            <th>Research</th>
            <th>Test</th>
            <th class='textColour' style='padding:2px;'>Overall</th>
            <th>Yes/No</th>
            <th>Date</th>
            <th>Mod #</th>
            <th>Yes/No</th>
            <th>Date</th>
            <th>Uploaded</th>
            <th>Date</th>
            <th>Yes/No</th>
            <th>Number</th>
            <th>Date</th>
            <th>Last Edit</th>
         </tr>
      ";

      $sqlStatus = "SELECT '' AS ControlValue, '' AS ControlText
                     UNION ALL
                     SELECT 'C' AS ControlValue, 'C' AS ControlText
                     UNION ALL
                     SELECT 'NYC' AS ControlValue, 'NYC' AS ControlText

                     ORDER BY ControlText ASC";
      //LOAD
      $rstAss = $xdb->doQuery("SELECT tblLearner.strLearner, tblLearner.strID, tblAssessment.*
                     FROM (tblAssessment LEFT JOIN tblModerationEvent ON tblAssessment.refEventID = tblModerationEvent.refEventID) INNER JOIN tblLearner ON tblAssessment.refLearnerID = tblLearner.LearnerID
                     WHERE $SelectionCriteria $Where
                     GROUP BY AssessmentID, tblLearner.strLearner, tblLearner.strID
                     ORDER BY tblLearner.strLearner");
      $intAss = $xdb->Rows;
      while($rowAss = $xdb->fetch_object($rstAss))
      {
         //ini
         $intRow ++;
         $ddlStatusOverall =
         $ddlStatusProject =
         $ddlStatusResearch =
         $ddlStatusTest = "";

         if($rowAss->dtAssessment == "0000-00-00") $rowAss->dtAssessment = "";
         if($rowAss->dtReAssessment == "0000-00-00") $rowAss->dtReAssessment = "";
         if($rowAss->dtModerated == "0000-00-00") $rowAss->dtModerated = "";
         if($rowAss->dtInseta == "0000-00-00") $rowAss->dtInseta = "";
         if($rowAss->dtNLRD == "0000-00-00") $rowAss->dtNLRD = "";
         if($rowAss->dtCertificate == "0000-00-00") $rowAss->dtCertificate = "";

         //load
         $AssID = $rowAss->AssessmentID;

         if($rowAss->blnAssessment != 1)
            $disabled = "disabled";
         else
            $disabled = "";


         //ddl
         //$ddlStatusOverall = "<select name='strStatusOverall[$AssID]' id='strStatusOverall[$AssID]' >". $xdb->ListOptions($sqlStatus, $rowAss->strStatusOverall) ."</select>";
         $ddlStatusOverall = "<b>". $arrStatus[$rowAss->strStatusOverall] ."</b>";
         $ddlStatusProject = "<select name='strStatusProject[$AssID]' id='strStatusProject[$AssID]' $disabled>". $xdb->ListOptions($sqlStatus, $rowAss->strStatusProject) ."</select>";
         $ddlStatusResearch = "<select name='strStatusResearch[$AssID]' id='strStatusResearch[$AssID]' $disabled>". $xdb->ListOptions($sqlStatus, $rowAss->strStatusResearch) ."</select>";
         $ddlStatusTest = "<select name='strStatusTest[$AssID]' id='strStatusTest[$AssID]' $disabled>". $xdb->ListOptions($sqlStatus, $rowAss->strStatusTest) ."</select>";

         //chk
         $chkAssessment = "<input type='checkbox' name='chkAssessment[$AssID]' id='chkAssessment[$AssID]' value='checked' ". $arrChecked[$rowAss->blnAssessment] ." onClick=\"enableControls($AssID, this.checked); setDate('dtAssessed[$AssID]', this.checked, 1);\" />";
         $chkModerated = "<input type='checkbox' name='chkModerated[$AssID]' id='chkModerated[$AssID]' ". $arrChecked[$rowAss->blnModerated] ." value='checked' $disabled onclick=\"setDate('dtModerated[$AssID]', this.checked, 0);\" />";
         $chkModeratedGroup = "<input type='checkbox' name='chkModeratedGroup[$AssID]' id='chkModeratedGroup[$AssID]' value='checked' ". $arrChecked[$rowAss->blnModeratedGroup] ." $disabled/>";
         $chkInseta = "<input type='checkbox' name='chkInseta[$AssID]' id='chkInseta[$AssID]' value='checked' ". $arrChecked[$rowAss->blnInseta] ." $disabled onclick=\"setDate('dtInseta[$AssID]', this.checked, 0);\" />";
         $chkInsetaGroup = "<input type='checkbox' name='chkInsetaGroup[$AssID]' id='chkInsetaGroup[$AssID]' value='checked' ". $arrChecked[$rowAss->blnInsetaGroup] ." $disabled/>";
         $chkNLRD = "<input type='checkbox' name='chkNLRD[$AssID]' id='chkNLRD[$AssID]' value='checked' ". $arrChecked[$rowAss->blnNLRD] ." $disabled onclick=\"setDate('dtNLRD[$AssID]', this.checked, 0);\" />";
         $chkCertificate = "<input type='checkbox' name='chkCertificate[$AssID]' id='chkCertificate[$AssID]' value='checked' ". $arrChecked[$rowAss->blnCertificate] ." $disabled onclick=\"setDate('dtCertificate[$AssID]', this.checked, 0);\" />";

         //dt
         //NOTE: dtAssessed else it conflicts with tblEvent.dtAssessment
         $dtAssessment = "<input name='dtAssessed[$AssID]' id='dtAssessed[$AssID]' value=\"$rowAss->dtAssessment\" type='text' class='controlText controlNumeric controlDateTime datePicker' $disabled />"; // readonly='readonly'
         $dtReAssessment = "<input name='dtReAssessment[$AssID]' id='dtReAssessment[$AssID]' value=\"$rowAss->dtReAssessment\" type='text' class='controlText controlNumeric controlDateTime datePicker' $disabled />";
         $dtModerated = "<input name='dtModerated[$AssID]' id='dtModerated[$AssID]' value=\"$rowAss->dtModerated\" type='text' class='controlText controlNumeric controlDateTime datePicker' $disabled />";
         $dtInseta = "<input name='dtInseta[$AssID]' id='dtInseta[$AssID]' value=\"$rowAss->dtInseta\" type='text' class='controlText controlNumeric controlDateTime datePicker' $disabled />";
         $dtNLRD = "<input name='dtNLRD[$AssID]' id='dtNLRD[$AssID]' value=\"$rowAss->dtNLRD\" type='text' class='controlText controlNumeric controlDateTime datePicker' $disabled />";
         $dtCertificate = "<input name='dtCertificate[$AssID]' id='dtCertificate[$AssID]' value=\"$rowAss->dtCertificate\" type='text' class='controlText controlNumeric controlDateTime datePicker' $disabled />";

         //dbl
         //$dblMark1Project = "<input type=text name='dblMark1Project[$AssID]' id='dblMark1Project[$AssID]' value=\"$rowAss->dblMark1Project\" class='controlText controlNumeric controlNarrow' onblur='removeAlpha(this);' $disabled />";
         $dblMark1Research = "<input type=text name='dblMark1Research[$AssID]' id='dblMark1Research[$AssID]' value=\"$rowAss->dblMark1Research\" class='controlText controlNumeric controlNarrow' onblur='removeAlpha(this);' $disabled />";
         $dblMark1Test = "<input type=text name='dblMark1Test[$AssID]' id='dblMark1Test[$AssID]' value=\"$rowAss->dblMark1Test\" class='controlText controlNumeric controlNarrow' onblur='removeAlpha(this);' $disabled />";

         //$dblMark2Project = "<input type=text name='dblMark2Project[$AssID]' id='dblMark2Project[$AssID]' value=\"$rowAss->dblMark2Project\" class='controlText controlNumeric controlNarrow' onblur='removeAlpha(this);' $disabled />";
         $dblMark2Research = "<input type=text name='dblMark2Research[$AssID]' id='dblMark2Research[$AssID]' value=\"$rowAss->dblMark2Research\" class='controlText controlNumeric controlNarrow' onblur='removeAlpha(this);' $disabled />";
         $dblMark2Test = "<input type=text name='dblMark2Test[$AssID]' id='dblMark2Test[$AssID]' value=\"$rowAss->dblMark2Test\" class='controlText controlNumeric controlNarrow' onblur='removeAlpha(this);' $disabled />";

         //str
         $strModFileNumber = "<input type=text name='strAModerationFileNumber[$AssID]' id='strModerationFileNumber[$AssID]' value=\"$rowAss->strModerationFileNumber\" class='controlText' style='width:100px;' $disabled/>";
         $strCertificateNumber = "<input type=text name='strCertificateNumber[$AssID]' id='strCertificateNumber[$AssID]' value=\"$rowAss->strCertificateNumber\" class='controlText' style='width:100px;' $disabled/>";
         $txtNotes = "<textarea name='txtANotes[$AssID]' id='txtANotes[$AssID]' class='controlText' $disabled>$rowAss->txtNotes</textarea>";

         if($this->isSelectable == 1)
         {
            $tdSelect = "<td align='center' rowspan='2'><input type=checkbox name='chkSelect[$AssID]' id='chkSelect' value='checked' /></td>";
         }

         /*
            <td align='left'>$dblMark1Project</td>
            <td align='center' rowspan='2'>$chkModerated</td>
            <td align='center' rowspan='2'>$chkInseta</td>

         */


         $trList .="
         <tr comment='main row'>
            <td align='right' rowspan='2' width='1%'>$intRow</td>
            <td align='left' rowspan='2'>$rowAss->strLearner</td>
            <td align='left' rowspan='1'>$rowAss->strID</td>
            <td align='center'>$chkAssessment</td>
            <td align='left'>$dtAssessment</td>
            <td align='left'>$dblMark1Research</td>
            <td align='left'>$dblMark1Test</td>
            <td align='left' rowspan='2'>$ddlStatusProject</td>
            <td align='left' rowspan='2'>$ddlStatusResearch</td>
            <td align='left' rowspan='2'>$ddlStatusTest</td>
            <td align='center' rowspan='2'>$ddlStatusOverall</td>
            <td align='center' rowspan='2'>$chkModeratedGroup</td>
            <td align='left' rowspan='2'>$dtModerated</td>
            <td align='left' rowspan='2'>$strModFileNumber</td>
            <td align='center' rowspan='2'>$chkInsetaGroup</td>
            <td align='left' rowspan='2'>$dtInseta</td>
            <td align='center' rowspan='2'>$chkNLRD</td>
            <td align='left' rowspan='2'>$dtNLRD</td>
            <td align='center' rowspan='2'>$chkCertificate</td>
            <td align='left' rowspan='2'>$strCertificateNumber</td>
            <td align='center' rowspan='2'>$dtCertificate</td>
            <td align='left' rowspan='2'>$txtNotes</td>
            <td align='center' rowspan='2'>$rowAss->strLastUser $rowAss->dtLastEdit</td>
            $tdSelect
         </tr>
         <tr comment='reAss row'>
            <td align='right' colspan='2' rowspan='1' class='textComment'>Reassessment Date:</td>
            <td align='left'>$dtReAssessment</td>
       <!-- <td align='left'>$dblMark2Project</td> --!>
            <td align='left'>$dblMark2Research</td>
            <td align='left'>$dblMark2Test</td>
         </tr>";

      }
      //,'chkModerated','chkInseta','dblMark1Project','dblMark2Project'
      $output = js("
         arrControls = new Array('strStatusProject','strStatusResearch','strStatusTest'
         ,'chkModeratedGroup','chkInsetaGroup','chkNLRD','chkCertificate'
         ,'dtAssessed','dtReAssessment','dtModerated','dtInseta','dtNLRD','dtCertificate'
         ,'dblMark1Research','dblMark1Test','dblMark2Research','dblMark2Test'
         ,'strModerationFileNumber','strCertificateNumber','txtANotes');
      function enableControls(AssID, blnEnabled)
      {
         for(var i in arrControls)
         {
            d(arrControls[i] +'['+ AssID +']').disabled = !blnEnabled;
         }
      }
      function setDate(id, blnChecked, blnAssessmentDate)
      {
         var dt = d(id);
         if(blnChecked == true &&  dt.value == '')  dt.value = $strToday;

         if(blnChecked == true &&  blnAssessmentDate == 1){
            dt.value = d('dtAssessment').value;
         }
      }

      ")."
      <table cellspacing=1 cellpadding=0 border=0 width=100% class='tblNemoList'>
         <caption>Learner Assessments</caption>
         $thHeadings
         $trList
      </table>
      ";

      if($intAss > 0)
         return $output;
      else
         return "<div id='divMessage'><div class='divWarning'>There are no Learners associated with this Event yet!</div></div>";

   }

   public static function SaveAssessment()
   {//print_rr($_POST);
      //20110831 - removed Project Mark, renamed Project to Portfolio - pj

      if(is_array($_POST[txtANotes]))
      {
         //ini
         $txtNotes = $_POST[txtANotes];
         $chkAssessment = $_POST[chkAssessment];
         $dtAssessed = $_POST[dtAssessed];
         $strStatusOverall = $_POST[strStatusOverall];
         $strStatusProject = $_POST[strStatusProject];
         $strStatusResearch = $_POST[strStatusResearch];
         $strStatusTest = $_POST[strStatusTest];
         //$dblMark1Project = $_POST[dblMark1Project];
         $dblMark1Research = $_POST[dblMark1Research];
         $dblMark1Test = $_POST[dblMark1Test];
         $chkModerated = $_POST[chkModerated];
         $chkModeratedGroup = $_POST[chkModeratedGroup];
         $dtInseta = $_POST[dtInseta];
         $strModerationFileNumber = $_POST[strAModerationFileNumber];
         $dtModerated = $_POST[dtModerated];
         $chkInseta = $_POST[chkInseta];
         $chkInsetaGroup = $_POST[chkInsetaGroup];
         $dtInseta = $_POST[dtInseta];
         $chkNLRD = $_POST[chkNLRD];
         $dtNLRD = $_POST[dtNLRD];
         $chkCertificate = $_POST[chkCertificate];
         $strCertificateNumber = $_POST[strCertificateNumber];
         $dtCertificate = $_POST[dtCertificate];
         $dtReAssessment = $_POST[dtReAssessment];
         //$dblMark2Project = $_POST[dblMark2Project];
         $dblMark2Research = $_POST[dblMark2Research];
         $dblMark2Test = $_POST[dblMark2Test];

         foreach($txtNotes as $AssessmentID => $value)
         {
            $db = new NemoDatabase("tblAssessment", $AssessmentID, null, 0);

            //statuses
            $db->Fields[strStatusProject] = $strStatusProject[$AssessmentID];
            $db->Fields[strStatusResearch] = $strStatusResearch[$AssessmentID];
            $db->Fields[strStatusTest] = $strStatusTest[$AssessmentID];
            if($db->Fields[strStatusProject] == "C" && $db->Fields[strStatusResearch] == "C" && $db->Fields[strStatusTest] == "C")
               $db->Fields[strStatusOverall] = "C";
            else
               $db->Fields[strStatusOverall] = "NYC";

            //dbl
            //BR: if Mark 2 > Mark 1 then use 2 else 1
            /*
            if($dblMark2Project[$AssessmentID] > $dblMark1Project[$AssessmentID])
               $db->Fields[dblMarkProject] = $dblMark2Project[$AssessmentID];
            else
               $db->Fields[dblMarkProject] = $dblMark1Project[$AssessmentID];
            */
            if($dblMark2Research[$AssessmentID] > $dblMark1Research[$AssessmentID])
               $db->Fields[dblMarkResearch] = $dblMark2Research[$AssessmentID];
            else
               $db->Fields[dblMarkResearch] = $dblMark1Research[$AssessmentID];

            if($dblMark2Test[$AssessmentID] > $dblMark1Test[$AssessmentID])
               $db->Fields[dblMarkTest] = $dblMark2Test[$AssessmentID];
            else
               $db->Fields[dblMarkTest] = $dblMark1Test[$AssessmentID];

            //$db->Fields[dblMark1Project] = $dblMark1Project[$AssessmentID];
            $db->Fields[dblMark1Research] = $dblMark1Research[$AssessmentID];
            $db->Fields[dblMark1Test] = $dblMark1Test[$AssessmentID];

            //$db->Fields[dblMark2Project] = $dblMark2Project[$AssessmentID];
            $db->Fields[dblMark2Research] = $dblMark2Research[$AssessmentID];
            $db->Fields[dblMark2Test] = $dblMark2Test[$AssessmentID];

            //bln = ($_POST['blnActive'] != "" ? "1" : "0");
            $db->Fields[blnAssessment] = ($chkAssessment[$AssessmentID] != "" ? 1 : 0);
            $db->Fields[blnModerated] = ($chkModerated[$AssessmentID] != "" ? 1 : 0);
            $db->Fields[blnModeratedGroup] = ($chkModeratedGroup[$AssessmentID] != "" ? 1 : 0);
            $db->Fields[blnInseta] = ($chkInseta[$AssessmentID] != "" ? 1 : 0);
            $db->Fields[blnInsetaGroup] = ($chkInsetaGroup[$AssessmentID] != "" ? 1 : 0);
            $db->Fields[blnNLRD] = ($chkNLRD[$AssessmentID] != "" ? 1 : 0);
            $db->Fields[blnCertificate] = ($chkCertificate[$AssessmentID] != "" ? 1 : 0);

            //dt
            $db->Fields[dtAssessment] = $dtAssessed[$AssessmentID]; //note dtAssessment <= dtAssessed
            $db->Fields[dtReAssessment] = $dtReAssessment[$AssessmentID];
            $db->Fields[dtModerated] = $dtModerated[$AssessmentID];
            $db->Fields[dtInseta] = $dtInseta[$AssessmentID];
            $db->Fields[dtNLRD] = $dtNLRD[$AssessmentID];
            $db->Fields[dtCertificate] = $dtCertificate[$AssessmentID];

            //str
            $db->Fields[strModerationFileNumber] = $strModerationFileNumber[$AssessmentID];
            $db->Fields[strCertificateNumber] = $strCertificateNumber[$AssessmentID];
            $db->Fields[txtNotes] = $txtNotes[$AssessmentID];
            $db->Fields[strLastUser] = ($_SESSION['CoachName'] ? $_SESSION['CoachName'] : $_SESSION['USER']->USERNAME);

            $result = $db->Save();
            $intSave ++;
            $db->doQuery("UPDATE tblAssessment
                     SET dtAssessment = CASE WHEN dtAssessment = '0000-00-00' THEN NULL ELSE dtAssessment END,
                         dtReAssessment = CASE WHEN dtReAssessment = '0000-00-00' THEN NULL ELSE dtReAssessment END,
                         dtModerated = CASE WHEN dtModerated = '0000-00-00' THEN NULL ELSE dtModerated END,
                         dtInseta = CASE WHEN dtInseta = '0000-00-00' THEN NULL ELSE dtInseta END,
                         dtNLRD = CASE WHEN dtNLRD = '0000-00-00' THEN NULL ELSE dtNLRD END,
                         dtCertificate = CASE WHEN dtCertificate = '0000-00-00' THEN NULL ELSE dtCertificate END
                     WHERE AssessmentID = $AssessmentID"); //post save clean: the db.Save() doesn't allow for nulls
         }
      }

      return "$intSave Learner Assessments saved.";
   }

   public static function AddAssessment($chkSelect, $EventID)
   {
      global $xdb;

      if(count($chkSelect) > 0){
         foreach($chkSelect as $key => $value)
         {
            $xdb->doQuery("INSERT IGNORE INTO tblAssessment(refEventID, refLearnerID) VALUES(". $xdb->qs($EventID) .",". $xdb->qs($key) .")");
         }
         return false;
      }else{
         return "No Learners selected.";
      }
   }

   public static function RemoveAssessment($chkSelect)
   {
      global $xdb;

      if(count($chkSelect) > 0){
         foreach($chkSelect as $key => $value)
         {
            $xdb->doQuery("DELETE FROM tblAssessment WHERE AssessmentID = ". $xdb->qs($key));
         }
         return "Assessment Deleted.";
      }else{
         return "No Assessments selected.";
      }
   }

   public static function AddNewAMLLearner($strCourseCode)
   {
      global $xdb;

      print_rr($strCourseCode);

   }

   public static function Save(&$LearnerID)
   {
      global $SystemSettings;
      $ldb = new NemoDatabase("tblLearner", $LearnerID, null, 0);
//print_rr($_FILES);


      $ldb->SetValues($_POST);

      if($_POST["refBusinessCoachID"] == 0){
         $ldb->Fields[refBusinessCoachID] = "NULL";
      }

      $ldb->Fields[strLearner] = $ldb->Fields[strSurname] .", ". $ldb->Fields[strName] ." (". $ldb->Fields[strDisplayName] .")";
      $ldb->Fields[txtAddress] = str_replace(chr(13).chr(13), chr(13), $ldb->Fields[strAddress1] . chr(13) . $ldb->Fields[strAddress2] . chr(13) . $ldb->Fields[strAddress3] . chr(13) . $ldb->Fields[strCode]);
      if($ldb->Fields[refCountryID] != 1)
         $ldb->Fields[refRegionID] = -2;

      list($day,$month) = explode("/", $_POST[dtDOB]);
      //echo date("Y")."/$month/$day";
      $ldb->Fields[dtDOB] = date("Y-m-d", strtotime(date("Y")."/$month/$day"));
      $ldb->Fields[strPasswordMD5] = md5($ldb->Fields[strPassword]);

      if($ldb->Fields[refPracticeID] == 0)
      {
         //20161025 - ph2 - default practice to null on new learner- christiaan
         $ldb->Fields[refPracticeID] = "NULL";
      }

      $ldb->Fields[strLastUser] = ($_SESSION['CoachName'] ? $_SESSION['CoachName'] : $_SESSION['USER']->USERNAME);

      $result1 = $ldb->Save();

      //print_rr($ldb->Fields);
      if($LearnerID == 0) $LearnerID = $ldb->ID[LearnerID];

      //print_rr($_FILES['Profile:PicturePath']);
      //print_rr($_REQUEST);
      //print_rr($_SESSION);
      //print_rr($_FILES);

      $ldb = new NemoDatabase("tblLearner", $LearnerID, null, 0);

      if($_FILES['Profile:PicturePath']['name'] != "" && $LearnerID != 0)
      {//current: cel_a/wa/_ need to go to cel_a/training/profilepictures/
         chmod($_FILES['Profile:PicturePath']['tmp_name'] , 0777);
         $strFileName = str_pad($LearnerID, 5,"0", STR_PAD_LEFT) ."_". $_FILES["Profile:PicturePath"]["name"];

         $strPath = $SystemSettings[ProfileImageDirAdmin]; //$strPath = "../../celestis_academy/training/profliepictures/";

         $ldb->Fields["Profile:PicturePath"] = $strFileName;

         move_uploaded_file($_FILES['Profile:PicturePath']['tmp_name'], $strPath . $strFileName);

      }
      if($ldb->Fields[refPracticeID] == 0)
      {
         //20161025 - ph2 - default practice to null on new learner- christiaan
         $ldb->Fields[refPracticeID] = "NULL";
      }

      if($_POST["refBusinessCoachID"] == 0){
         $ldb->Fields[refBusinessCoachID] = "NULL";
      }


      $result2 = $ldb->Save();

      if($result1->Error == 1 || $result2->Error == 1)
         return $result1->Message.$result2->Message;
      else
         return "Details Saved. ";
   }

   public static function Delete($chkSelect)
   {
      global $xdb;
      //print_rr($chkSelect);
      if(count($chkSelect) > 0){
      foreach($chkSelect as $key => $value)
      {
         $xdb->doQuery("DELETE FROM tblLearner WHERE LearnerID = ". $xdb->qs($key));
      }
         return "Records Deleted.";
      }
   }

   public static function getCourseResults($LearnerID, $CourseID)
   {
      global $xdb, $BR, $DT;

      $rst = $xdb->doQuery("
         SELECT modModule.ModuleID, modModule.strModuleName, modModule.dtReleaseDate, modAssignment.AssignmentID, modAssignment.strAssignment, modAssignment.strType, modAssignment.dtDueDate, tblLearnerMilestone.UniqueID, tblLearnerMilestone.refModuleID, tblLearnerMilestone.refLearnerID, tblLearnerMilestone.strStatus
         , Left(tblLearnerMilestone.dtLastEdit,10) AS dtMilestone, ROUND(tblLearnerMilestone.percAssignmentMark,0) AS percAssignmentMark, tblLearnerMilestone.dtAssessment, ROUND(tblClassMark.avgClassMark,0) AS avgClassMark
         , tblLearnerMilestone.intMarks, tblLearnerMilestone.dblAssignmentMark
         FROM (
            (
               SELECT modAssignment.refModuleID as ModuleID, Avg(modAssessment.percAssignmentMark) AS avgClassMark
               FROM modAssessment INNER JOIN modAssignment ON modAssessment.refAssignmentID = modAssignment.AssignmentID
               WHERE modAssignment.strType<>'Quiz' AND modAssignment.strType<>'Feedback Survey' AND NOT modAssessment.percAssignmentMark is NULL
               GROUP BY modAssignment.refModuleID
            )  AS tblClassMark
         RIGHT JOIN (modAssignment INNER JOIN modModule ON modAssignment.refModuleID = modModule.ModuleID) ON tblClassMark.ModuleID = modModule.ModuleID) LEFT JOIN
            (
               SELECT modMilestone.UniqueID, modMilestone.refModuleID, modMilestone.refLearnerID, modMilestone.strType, modMilestone.strStatus
               , modMilestone.dtLastEdit, modAssessment.percAssignmentMark, modAssessment.dtAssessment, modAssignment.intMarks
               , Round(percAssignmentMark*intMarks/100) AS dblAssignmentMark
               FROM ((modMilestone LEFT JOIN modAssessment ON (modMilestone.refLearnerID = modAssessment.refLearnerID) AND (modMilestone.UniqueID = modAssessment.refAssignmentID)) INNER JOIN modAssignment ON modMilestone.UniqueID = modAssignment.AssignmentID) INNER JOIN modModule ON modAssignment.refModuleID = modModule.ModuleID
               WHERE modMilestone.refLearnerID='$LearnerID' AND modMilestone.strType In ('Assignment','Assessment') AND modModule.refCourseID IN ($CourseID)
            )  AS tblLearnerMilestone ON modAssignment.AssignmentID = tblLearnerMilestone.UniqueID

         WHERE modAssignment.strType <>'Quiz' AND modAssignment.strType <> 'Feedback Survey' AND modAssignment.blnActive=1 AND modModule.refCourseID IN (". $CourseID .")
         ORDER BY modAssignment.dtDueDate, modModule.dtReleaseDate, modModule.intModuleNumber",0);//modAssignment.strType<>'Quiz' AND //AND modMilestone.strStatus='Completed'

      while($row = $xdb->fetch())
      {//print_rr($row);
         $i++;

         $dblOverallMarks += $row->intMarks;
         $dblOverallAssignmentMarks += $row->dblAssignmentMark;
         $avgOverallClassMarks += $row->avgClassMark;


         // if(!$row->dtDueDate > 0)
         //    $row->dtDueDate = "n/a";

         // if($row->dtAssessment > 0){}
         // else
         //    $row->dtAssessment = "-";


         if($row->avgClassMark != "")
            $row->avgClassMark = number_format($row->avgClassMark,0)." %";
         else
            $row->avgClassMark = "-";

         if(date("Y-m-d") > $row->dtDueDate)
         {
            if($row->dtAssessment > 0){
               $row->percAssignmentMark = number_format($row->percAssignmentMark,0)." %";
            }
            else{
               $row->percAssignmentMark = "-";
            }
         }else{
            $row->percAssignmentMark = "-";
         }


         //elseif(date("Y-m-d") > $row->dtDueDate && $row->dtAssessment > 0){
         //   $row->percAssignmentMark = "0 %";
         //}



         $strType = $row->strType;
         if($row->strType == "Upload")
            $strType = "Assignment";
         else
            $strType = "Quiz";

         $list .= "
               <tr style='background-color: white;''>
                  <td align='right'>$i</td>
                  <td >$row->strAssignment</td>
                  <td >$strType</td>
                  <td align='center' nowrap>$row->dtDueDate</td>
                  <td align='right'>$row->percAssignmentMark</td>
                  <td align='right'>$row->avgClassMark</td>
               </tr>";

      }//eoWhile

      //overall tr
      if($dblOverallMarks != 0){
         $percOverallAssignmentMark = round($dblOverallAssignmentMarks/$dblOverallMarks *100) ." %";
         $avgOverallClassMark = round($avgOverallClassMarks / $i )." %";
      }
      else{
         $percOverallAssignmentMark = "n/a";
         $avgOverallClassMark = "n/a";
      }

      $list .= "
               <tr>
                  <td align='right'></td>
                  <td colspan='3'><b>Overall Results</b></td>
                  <td align='right'>$percOverallAssignmentMark</td>
                  <td align='right'>$avgOverallClassMark</td>
               </tr>";

      $list = "

         <table border='0' cellpadding='2' cellspacing='1' width='100%' style='border: 1px solid #AAAAAA; background-color: #AAAAAA; font-family: arial; font-size: 14px;'>
            <thead>
               <tr style='background-color: #666666; color: white;'><td colspan='6' align='center'><b>Schedule of Results</b></td></tr>
               <tr style='background-color: #AAAAAA;'>
                  <th ><b>#</b></th>
                  <th ><b>Assignment</b></th>
                  <th ><b>Type</b></th>
                  <th ><b>Due Date</b></th>
                  <th ><b>Results</b></th>
                  <th ><b>Average *</b></th>
               </tr>
            </thead>
            <tbody>
               $list
            </tbody>
         </table>$BR
         * This is the class average, based on the number of assignments submitted
      ";

      return $list;
   }

   public static function RemoveLearnersFromCourse($chkSelect, $CourseID)
   {
      global $xdb;

      if(count($chkSelect) > 0)
      {
         foreach($chkSelect as $key => $value)
         {
            $xdb->doQuery("UPDATE tblLicence SET strStatus='Revoked' WHERE refLearnerID = ". $xdb->qs($key)." AND refCourseID = ". $xdb->qs($CourseID));
         }
         return "Learners removed. ";
      }
      else
      {
         return "No Learners selected. ";
      }

   }

   /*public static function getLearnerCourseList($LearnerID)
   {
      global $xdb;
      $i=0;

       $output = "
                  <table width='100%' cellspacing='1' cellpadding='2' border='0'>
                     <span class='textComment'></span>
                        <caption> Learner Course List </caption>
                        <tr style='background-color: rgb(255, 255, 255);'>
                           <th></th>
                           <th width='1%' align = 'center'>#</th>
                           <th class='textColour' align = 'center'>Course</th>
                           <th class='textColour' align = 'center'>Course Code</th>
                           <th class='textColour' align = 'center'>Status</th>
                           <th class='textColour' align = 'center'>Start Date</th>
                           <th class='textColour' align = 'center'>End Date</th>
                           <th class='textColour' align = 'center'>Date Issued</th>
                           <th class='textColour' align = 'center'>Date Activated</th>
                           <th class='textColour' align = 'center'>Date Expiry</th>
                           <th class='textColour' align = 'center'>Active</th>
                        </tr>";

      $sql = $xdb->doQuery("SELECT tblLicence.refLearnerID, tblCourse.strCourse, tblCourse.strCourseCode, tblLicence.strStatus, tblCourse.dtStartDate, tblCourse.dtEndDate,
                              tblLicence.dtDateIssued, tblLicence.dtDateActivated, tblLicence.dtDateExpiry, tblCourse.blnActive
                           FROM (tblLearner INNER JOIN tblLicence ON tblLearner.LearnerID = tblLicence.refLearnerID) INNER JOIN tblCourse ON tblLicence.refCourseID = tblCourse.CourseID
                           WHERE tblLicence.refLearnerID = $LearnerID");

      while($row = $xdb->fetch_object($sql))
      {
         $i++;
         //print_rr($row);
         $output .= "
                     <tr>
                        <td></td>
                        <td >$i</td>
                        <td >$row->strCourse</td>
                        <td >$row->strCourse Code</td>
                        <td >$row->strStatus</td>
                        <td >$row->dtStartDate</td>
                        <td >$row->dtEndDate</td>
                        <td >$row->dtDateIssued</td>
                        <td >$row->dtDateActivated</td>
                        <td >$row->dtDateExpiry</td>
                        <td >$row->blnActive == 1 ? 'yes' : 'No'</td>
                     </tr>";
      }

      $output .= " </table>";

      return $output;
   }*/

    //20180314 - User login for Webadmin Administrators via redirect- CL
   // 20181203 - PHP7 Conversion non-static method cannot be called statically - Rachel
   // function RedirectWebadminToLearner($LearnerID)
   public static function RedirectWebadminToLearner($LearnerID, $CoachID = 0)
   {
      global $xdb,$SystemSettings;
      $BASE_URL = str_replace("webadmin/","",$SystemSettings[BASE_URL]);

      $sql = "SELECT tblLearner.*, tblUserType.refUserCategoryID FROM tblLearner INNER JOIN tblUserType ON tblLearner.refUserTypeID = tblUserType.UserTypeID 
      WHERE LearnerID = ".$xdb->qs($LearnerID);
      if($CoachID)
      {
         $rowtmp = $xdb->getRowSQL("SELECT strName, strSurname, strEmail FROM tblLearner WHERE LearnerID = ".$xdb->qs($CoachID));
         $name = "";
         if(strlen($rowtmp->strName.' '.$rowtmp->strSurname) > 20)
         {
            $name .= $rowtmp->strName[0];
         }
         else
         {
            $name .= $rowtmp->strName;
         }
         $name .= ' '.$rowtmp->strSurname;
         $_SESSION['CoachName'] = $name;
         $_SESSION['CoachEmail'] = $rowtmp->strEmail;
      }
      else
      {
         unset($_SESSION['CoachName']);
      }
      $row = $xdb->getRowSQL($sql);
      //20161121 - Ph3 - Session changed to include User Type/Category - christiaan
      $_SESSION[LEARNER]->ID = $row->LearnerID;
      $_SESSION[LEARNER]->USERTYPEID = $row->refUserTypeID;
      $_SESSION[LEARNER]->USERCATEGORYID = $row->refUserCategoryID;
      $_SESSION[LEARNER]->USERNAME = $row->strLearner;
      $_SESSION[LEARNER]->DISPLAYNAME = $row->strDisplayName;
      $_SESSION[LEARNER]->EMAIL = $row->strEmail;
      $_SESSION[LEARNER]->ACTIVE = $row->blnActive;
      $_SESSION[LEARNER]->REGIONID = $row->refRegionID;
      $_SESSION[LEARNER]->TYPOGRAPHY = $row->strTypography;
      $profilePic = "Profile:PicturePath"; //2013-09-12 workaround for having the database value correctly called with the object -Christiaan
      $_SESSION[LEARNER]->PICTUREPATH = $row->$profilePic;

      tblLoginInsert("Login successful [Learner][A:$row->blnActive][P:$blnRegPurcLink][ADM]");
      windowLocation($BASE_URL."training/index.php?System=Overview");
   }   

   //208180314 - Update all Learner Passwords to hashed passwords - CL //takes about 7 minutes to complete
   function updateAllLearnerPasswordsFromMD5ToHash()
   {
      include_once("./_nemo.password.cls.php");
      //ini
      global $xdb;
      $np = new NemoPassword();

      //process
      $rst = $xdb->doQuery("SELECT * FROM tblLearner");
      while($row = $xdb->fetch($rst))
      {
         $np->createPassword($row->xstrPassword);
         $np->savePassword("Learner", $row->LearnerID, date("Y-m-d"), 0);
      }

      return "completed";
   }
}
?>