<?php
/*
ALTER TABLE `tblCourseType` DROP FOREIGN KEY `tblCourseType.strUserCategory`;
ALTER TABLE `tblCourseType` ADD CONSTRAINT `tblCourseType.strUserCategory` FOREIGN KEY (`refUserCategoryID`) REFERENCES `tblUserCategory` (`UserCategoryID`) ON UPDATE CASCADE;
 *
 */

include_once("_nemo.list.cls.php");

class CourseType extends NemoList
{
   private $ID = 0;

   public function __construct($DataKey="")
   {
      //filters
      $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);
   }

   public function getList()
   {
      global $SystemSettings;

      $dt = date("Y-m-d");
      $Where = "";

      if($this->Filters[frStatus]->html->value != -1)
      {
         $Where .= " AND tblCourseType.blnActive = ". $this->Filters[frStatus]->html->value;
      }

      $this->ListSQL("
         SELECT tblCourseType.CourseTypeID, tblCourseType.strCourseTypeHTML as 'Course Type', tblCourseType.strCourseTypeCode as 'Code', COUNT(CourseID) AS 'No Courses', (SELECT MIN(dtStartDate) FROM tblCourse WHERE refCourseTypeID = CourseTypeID AND dtStartDate >= '$dt') AS 'Next Course Date',tblCourseType.blnActive AS Active, tblCourseType.strLastUser AS 'Last User', tblCourseType.dtLastEdit AS 'Last Edit'
         FROM tblCourseType LEFT JOIN tblCourse ON CourseTypeID = refCourseTypeID
         WHERE 1=1 $Where
         GROUP BY CourseTypeID, strCourseTypeHTML
         ORDER BY tblCourseType.strCourseType Asc",0,"course.type.php");
      return $this->renderTable("Course Type List");
   }

   public static function Save(&$CourseTypeID)
   {
      global $xdb, $SystemSettings, $TR, $SP, $HR, $BR, $DT;
      $db = new NemoDatabase("tblCourseType", $CourseTypeID, null, 0);

      $db->SetValues($_POST);

      $db->Fields[strLastUser] = $_SESSION['USER']->USERNAME;

      $result = $db->Save();

      if($CourseTypeID == 0) $CourseTypeID = $db->ID[CourseTypeID];

      if($result->Error == 1)
         return $result->Message;
      else
         return "Details Saved. ";
   }

   //HDP 21/11/2016 - Added CTUC
   public static function SaveUserCategory(&$CourseTypeID)
   {
      global $xdb, $SystemSettings, $TR, $SP, $HR, $BR, $DT;

      //Delete all records from tblCourseTypeUserCategory where refCourseTypeID=$CourseTypeID and then resave only selected
      if($CourseTypeID != 0) {
         $xdb->doQuery("DELETE tblCourseTypeUserCategory.* FROM tblCourseTypeUserCategory WHERE refCourseTypeID = $CourseTypeID");

         if($_POST[blnCTUCSelected])
         {
            foreach($_POST[blnCTUCSelected] as $ID => $Value)
            {
               $xdb->doQuery("INSERT INTO tblCourseTypeUserCategory (refCourseTypeID, refUserCategoryID)
                              VALUES ($CourseTypeID,$ID)");         
            }
         } 
      }
   }

   public static function Delete($chkSelect)
   {
      global $xdb;
      //print_rr($chkSelect);
      if(count($chkSelect) > 0){
      foreach($chkSelect as $key => $value)
      {
         $row = $xdb->getRowSQL("SELECT COUNT(CourseID) AS intCount FROM tblCourse WHERE refCourseTypeID = ". $xdb->qs($key));
         if($row->intCount > 0){
            $xdb->doQuery("UPDATE tblCourseType SET blnActive = 0 WHERE CourseTypeID = ". $xdb->qs($key));
            return "This Course Type has child entities and has been made INACTIVE";
         }else{
            $xdb->doQuery("DELETE FROM tblCourseType WHERE CourseTypeID = ". $xdb->qs($key));
            return "Records Deleted.";
         }
      }
      }
      return "No Records Selected.";
   }

   //HDP - 21/11/2016 - Added CTUC
   public function getListCTUC($CourseTypeID=null)
   {
      global $SystemSettings, $xdb;

      // $Where = "";
      // if($CourseTypeID != null)
      // {
      //    $Where = " AND tblCourse.refCourseTypeID = '$CourseTypeID'";
      // }else{
      //    if($this->Filters[frSearch]->html->value != "")
      //    {
      //       $like = "LIKE(". $this->db->qs("%".$this->Filters[frSearch]->html->value."%") .")";
      //       $Where .= " AND (tblCourse.CourseID $like OR tblCourse.strCourse $like OR tblCourse.strCourseCode $like )";
      //    }

      //    if($this->Filters[frStatus]->html->value != -1)
      //    {
      //       $Where .= " AND vieWealthIntelligence.blnActive = ". $this->Filters[frStatus]->html->value;
      //    }
      // }


      $this->isSelectable = 0;
      $sql = "
            SELECT UserCategoryID, strUserCategory AS 'User Category', SUM(blnCTUCSelected) AS `Select` FROM (

            SELECT UserCategoryID, strUserCategory, 0 AS blnCTUCSelected
            FROM tblUserCategory 
            WHERE blnActive = 1

            UNION ALL

            SELECT UserCategoryID, strUserCategory, 1 AS blnCTUCSelected 
            FROM tblUserCategory
               INNER JOIN tblCourseTypeUserCategory on tblUserCategory.UserCategoryID = tblCourseTypeUserCategory.refUserCategoryID
            WHERE refCourseTypeID=". $xdb->qs($CourseTypeID) ."
                
            ) AS tCTUC
            GROUP BY UserCategoryID, strUserCategory
            ORDER BY strUserCategory
            ";

      $this->ListSQL($sql, 0);

      foreach ($this->Data as $i => $row)
      {
         $this->Data[$i]["Select"] = "<input type=checkbox name=blnCTUCSelected[". $row["UserCategoryID"] ."] id=blnCTUCSelected value=1 ". ($row["Select"] == 0 ? "" : "checked") ." />";         
      }

      $this->Columns["Select"]->html->width = "5% ";
      $this->Columns["Select"]->html->align = "center";
      unset($this->Columns["UserCategoryID"]);

      return $this->renderTable("Applicable User Categories");
   }   
}
?>