<?php
/*
ALTER TABLE `tblCPDPoints` DROP FOREIGN KEY `tblCPDPoints.strLearner`;
ALTER TABLE `tblCPDPoints` ADD CONSTRAINT `tblCPDPoints.strLearner` FOREIGN KEY ( `refLearnerID` ) REFERENCES `tblLearner` ( `LearnerID` ) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE `tblCPDPoints` DROP FOREIGN KEY `tblCPDPoints.strCourse`;
ALTER TABLE `tblCPDPoints` ADD CONSTRAINT `tblCPDPoints.strCourse` FOREIGN KEY ( `refEventID` ) REFERENCES `tblCourse` ( `CourseID` ) ON DELETE CASCADE ON UPDATE CASCADE ;

//20181106 - Course/CPD - ClassOfBusiness - pj [strCPDClassOfBusiness]

*/
include_once("_nemo.list.cls.php");

class CPDPoints extends NemoList
{
   private $ID = 0;

   public function __construct($DataKey)
   {
      $this->Filters[frIgnoreDate]->tag = "input";
      $this->Filters[frIgnoreDate]->html->value = "checked";
      $this->Filters[frIgnoreDate]->html->type = "checkbox";
      $this->Filters[frIgnoreDate]->html->onclick = "d('frStartDate').disabled=this.checked;d('frEndDate').disabled=this.checked;";

      $this->Filters[frStartDate]->tag = "input";
      $this->Filters[frStartDate]->html->value = date("Y-01-01");
      $this->Filters[frStartDate]->html->type = "text";
      $this->Filters[frStartDate]->html->class = "controlText controlNumeric datePicker";

      $this->Filters[frEndDate]->tag = "input";
      $this->Filters[frEndDate]->html->value = date("Y-m-d");
      $this->Filters[frEndDate]->html->type = "text";
      $this->Filters[frEndDate]->html->class = "controlText controlNumeric datePicker";

      $this->Filters[frSearch]->tag = "input";
      $this->Filters[frSearch]->html->value = "";
      $this->Filters[frSearch]->html->type = "text";
      $this->Filters[frSearch]->html->class = "controlText";

      $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";

      // $this->Filters[frCourseType]->tag = "select";
      // $this->Filters[frCourseType]->html->value = "0"; //defalt value = 0, -1 = '- Unassigned -'
      // $this->Filters[frCourseType]->html->class = "controlText";
      // $this->Filters[frCourseType]->sql = "
      //                   SELECT 0 AS ControlValue, '- All -' AS ControlText
      //                   UNION ALL
      //                   SELECT `refEventType` AS ControlValue, concat(refEventType,' (',count(refEventType),')') AS ControlText
      //                   FROM tblCPDPoints
      //                   GROUP BY refEventType
      //                   ORDER BY ControlText ASC";

      $this->Filters[frCourse]->tag = "select";
      $this->Filters[frCourse]->html->value = "0"; //defalt value = 0, -1 = '- Unassigned -'

      $this->Filters[frCourse]->html->class = "controlText";
      $this->Filters[frCourse]->sql = "
                        SELECT 0 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT `CourseID` AS ControlValue, strCourse AS ControlText
                        FROM tblCourse 
                        WHERE  blnWorkshop = 0 and blnWI = 0
                        GROUP BY CourseID
                        ORDER BY ControlText ASC";

      parent::__construct($DataKey);

   }

   public function getList($LearnerID = null, $CourseID = null)
   {
      global $SystemSettings;
      $Where = "";
      $LearnerWhere = "";

      if($LearnerID != null)
      {
         $LearnerWhere = "AND tblCPDPoints.refLearnerID = $LearnerID";
         $LearnerSelect =  "tblCPDPoints.refEventType AS Type, tblCourse.strCourseHTML AS Event,";
      }
      else if($CourseID != null)
      {
         $WIWhere = "AND tblCPDPoints.refEventID = $CourseID";
         $WISelect = "tblLearner.strLearner AS Learner,tblCPDPoints.strID as 'ID Number',tblCPDPoints.strEmail as 'Email',";
         $WIOrderBy = "tblLearner.strLearner,tblCPDPoints.strID,tblCPDPoints.strEmail,";
      }
      else
      {
         if($this->Filters[frIgnoreDate]->html->checked != "checked")
         {
            $Where .= " AND dtEventDate >= '". $this->Filters[frStartDate]->html->value ."' AND dtEventDate <= '". $this->Filters[frEndDate]->html->value ."'";
         }
         if($this->Filters[frSearch]->html->value != "")
         {
            $like = "LIKE(". $this->db->qs("%".$this->Filters[frSearch]->html->value."%") .")";
            $Where .= " AND (tblLearner.strLearner $like
               OR tblCPDPoints.strID $like
               OR tblCPDPoints.strEmail $like
               OR tblCourse.strCourse $like
               OR tblCourse.strFPIReferenceNumber $like
               OR tblCPDPoints.strFPIReferenceNumber $like
               OR tblCourse.refCPDCategory $like
               OR tblCourse.strCPDClassOfBusiness $like )"; //20181106 - Course/CPD - ClassOfBusiness - pj [strCPDClassOfBusiness]
         }
         // if($this->Filters[frCourseType]->html->value != "0")
         // {
         //    $Where .= " AND tblCPDPoints.refEventType = '". $this->Filters[frCourseType]->html->value."'";
         // }

         if($this->Filters[frCourse]->html->value != "0")
         {
            $Where .= " AND tblCPDPoints.refEventID = '". $this->Filters[frCourse]->html->value."'";
         }
         
         if($this->Filters[frStatus]->html->value != -1)
         {
            $Where .= " AND tblLearner.blnActive = ". $this->Filters[frStatus]->html->value;
         }

         $LearnerSelect =  "tblCPDPoints.refEventType AS Type, IFNULL(modModule.strModuleName,tblCourse.strCourseHTML) AS Event,sysUser.strUser AS 'Business Coach',";
         $WISelect = "tblLearner.strLearner AS Learner,tblCPDPoints.strID as 'ID Number',tblCPDPoints.strEmail as 'Email',";
         $WIOrderBy = "tblCPDPoints.dtEventDate DESC,tblLearner.strLearner,tblCPDPoints.strID,tblCPDPoints.strEmail,";
      }

      //20181106 - Course/CPD - ClassOfBusiness - pj [strCPDClassOfBusiness]
      $this->ListSQL("
         SELECT tblCPDPoints.ID,$WISelect tblCPDPoints.dtEventDate AS 'Date'
            , IF(IFNULL(tblCourse.strFPIReferenceNumber,'')=tblCPDPoints.strFPIReferenceNumber
               , CONCAT('<b class=textColour>', IFNULL(tblCourse.strFPIReferenceNumber,''), '</b>')
               , CONCAT('<b class=textRed>', tblCPDPoints.strFPIReferenceNumber, ' / ', IFNULL(tblCourse.strFPIReferenceNumber,'n/a'), '</b>')
            ) AS 'FPI Ref'
            , tblCPDPoints.refCPDCategory AS Category, tblCPDPoints.strCPDClassOfBusiness as 'Class of Business'
            , tblCPDPoints.dblPointsFPI AS 'FPI Points', tblCPDPoints.dblPointsFSB AS 'FSB Points',  $LearnerSelect tblCPDPoints.strLastUser AS 'Last User', tblCPDPoints.dtLastEdit AS 'Last Edit'
         FROM ((tblLearner RIGHT JOIN tblCPDPoints ON tblLearner.LearnerID = tblCPDPoints.refLearnerID) LEFT JOIN tblCourse ON tblCPDPoints.refEventID = tblCourse.CourseID LEFT JOIN modModule ON modModule.ModuleID = tblCPDPoints.refModuleID) LEFT JOIN sysUser ON sysUser.UserID = tblLearner.refBusinessCoachID
         WHERE 1=1 $LearnerWhere $WIWhere $Where
         ORDER BY $WIOrderBy tblCPDPoints.dtEventDate DESC, tblCPDPoints.refCPDCategory
         ", 0, "cpd.points.php");

      return $this->renderTable("CPD Points List");
   }

   public static function checkCPDPoints($LearnerID, $dblNewPoints, $strYear)
   {
      global $xdb,$SystemSettings;

      $row = $xdb->getRowSQL("SELECT Sum(tblCPDPoints.dblPointsFPI) AS dblPointsFPI
            FROM tblCPDPoints
            WHERE (((tblCPDPoints.refLearnerID)=$LearnerID) AND ((Left(dtEventDate,4))=$strYear))
          ",0);
      $dblPointsRemaining = number_format((float)$SystemSettings["MAX_CPD_POINTS"], 1) - number_format((float)$row->dblPointsFPI, 1);
      // echo $SystemSettings["MAX_CPD_POINTS"] . " - ";
      // echo $row->dblPointsFPI . " = " ;
      // echo $dblPointsRemaining;

      if($dblPointsRemaining - $dblNewPoints >= 0)
      {
         return $dblNewPoints;
      }
      else
      {
         return $dblPointsRemaining;
      }
   }

   public static function Save(&$ID)
   {
      global $xdb,$SystemSettings;

      $db = new NemoDatabase("tblCPDPoints", $ID, null, 0);
      // nemoDatabase method cant handle the field name ID, so we have to set the value of ID to 0 again. - Christiaan
      // if($ID == 0)
      // {
      //    $ID = 0;
      // }

      $db->SetValues($_POST);

      $date = substr($db->Fields[dtEventDate],0,4);
      $db->Fields[dblPointsFPI] = checkCPDPoints($db->Fields[refLearnerID], $db->Fields[dblPointsFPI], $date, $ID);//, $db->Fields[refEventID]


      //save
      $result = $db->Save(0,0);
      //print_rr( $result );

      if($db->Fields[dblPointsFPI] != $_POST[dblPointsFPI])
      {
         //only x of y points could be allocated
         $result->Error = 1;
         $result->Message = "Only ".(float) $db->Fields[dblPointsFPI]." of " .$SystemSettings["MAX_CPD_POINTS"]." points can be allocated for the year $date.";
      }
      if($ID == 0)
      {
         $ID = $result->ID;
      }

      //return msgs
      if($result->Error == 1)
         return $result->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 tblCPDPoints WHERE ID = ". $xdb->qs($key));
      }
         return "Records Deleted. ";
      }
   }
}
?>