<?php
/*
//practice
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblLicence` DROP FOREIGN KEY `tblLicence.strPractice`;
ALTER TABLE `tblLicence` ADD CONSTRAINT `tblLicence.strPractice` FOREIGN KEY (`refPracticeID`) REFERENCES `tblPractice` (`PracticeID`) ON UPDATE CASCADE ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS = 1;


//client
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblLicence` DROP FOREIGN KEY `tblLicence.strClient`;
ALTER TABLE `tblLicence` ADD CONSTRAINT `tblLicence.strClient` FOREIGN KEY (`refClientID`) REFERENCES `tblClient` (`ClientID`) ON UPDATE CASCADE ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS = 1;


//course
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblLicence` DROP FOREIGN KEY `tblLicence.strCourse`;
ALTER TABLE `tblLicence` ADD CONSTRAINT `tblLicence.strCourse` FOREIGN KEY (`refCourseID`) REFERENCES `tblCourse` (`CourseID`) ON UPDATE CASCADE ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS = 1;


//product
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblLicence` DROP FOREIGN KEY `tblLicence.strProduct`;
ALTER TABLE `tblLicence` ADD CONSTRAINT `tblLicence.strProduct` FOREIGN KEY (`refProductID`) REFERENCES `tblProduct` (`ProductID`) ON UPDATE CASCADE ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS = 1;


//learner
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblLicence` DROP FOREIGN KEY `tblLicence.strLearner`;
ALTER TABLE `tblLicence` ADD CONSTRAINT `tblLicence.strLearner` FOREIGN KEY (`refLearnerID`) REFERENCES `tblLearner` (`LearnerID`) ON UPDATE CASCADE ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS = 1;


//invoice
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblLicence` DROP FOREIGN KEY `tblLicence.strInvoiceNumber`;
ALTER TABLE `tblLicence` ADD CONSTRAINT `tblLicence.strInvoiceNumber` FOREIGN KEY (`refInvoiceID`) REFERENCES `tblInvoice` (`InvoiceID`) ON UPDATE CASCADE ON DELETE CASCADE;
SET FOREIGN_KEY_CHECKS = 1;

//20161025 - FOREIGN KEYS - added foreign keys for ddl - neils
//20161025 - PH2 - refLearner, and refPracticeID need to be NULL if their value is 0. refClientID is -1 by default - christiaan
//20161108 - fixed sql for tblLicenceJoined - pj
*/

include_once("_nemo.list.cls.php");

class Licence 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[frClient]->tag = "select";
      $this->Filters[frClient]->html->value = "-99"; //defalt value = 0, -1 = '- Unassigned -'
      $this->Filters[frClient]->html->class = "controlText";
      $this->Filters[frClient]->sql = "
                        SELECT -99 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT `ClientID` AS ControlValue, strClient AS ControlText
                        FROM tblClient
                        ORDER BY ControlText ASC";

      $this->Filters[frLearner]->tag = "select";
      $this->Filters[frLearner]->html->value = "-99"; //defalt value = 0, -1 = '- Unassigned -'
      $this->Filters[frLearner]->html->class = "controlText";
      $this->Filters[frLearner]->sql = "
                        SELECT -99 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT `LearnerID` AS ControlValue, strLearner AS ControlText
                        FROM tblLearner
                        ORDER BY ControlText ASC";

      $this->Filters[frProduct]->tag = "select";
      $this->Filters[frProduct]->html->value = "-99"; //defalt value = 0, -1 = '- Unassigned -'
      $this->Filters[frProduct]->html->class = "controlText";
      $this->Filters[frProduct]->sql = "
                        SELECT -99 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT `ProductID` AS ControlValue, strProduct AS ControlText
                        FROM tblProduct
                        ORDER BY ControlText ASC";

      $this->Filters[frPractice]->tag = "select";
      $this->Filters[frPractice]->html->value = "-99"; //defalt value = 0, -1 = '- Unassigned -'
      $this->Filters[frPractice]->html->class = "controlText";
      $this->Filters[frPractice]->sql = "
                        SELECT -99 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT `PracticeID` AS ControlValue, strPractice AS ControlText
                        FROM tblPractice
                        ORDER BY ControlText ASC";


      $this->Filters[frStatus]->tag = "select";
      $this->Filters[frStatus]->html->value = "- All -";
      $this->Filters[frStatus]->html->class = "controlText";
      $this->Filters[frStatus]->sql = "
                        SELECT '- All -' AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT 'In Processing' AS ControlValue, 'In Processing' AS ControlText
                        UNION ALL
                        SELECT 'Active' AS ControlValue, 'Active' AS ControlText
                        UNION ALL
                        SELECT 'Dormant' AS ControlValue, 'Dormant' AS ControlText
                        UNION ALL
                        SELECT 'Expired' AS ControlValue, 'Expired' AS ControlText
                        ORDER BY ControlText ASC";

      // $this->Filters[frDateIssued]->tag = "input";
      // $this->Filters[frDateIssued]->html->value = date("Y-m-01");
      // $this->Filters[frDateIssued]->html->type = "text";
      // $this->Filters[frDateIssued]->html->class = "controlText controlNumeric datePicker";

      // $this->Filters[frExpiryDate]->tag = "input";
      // $this->Filters[frExpiryDate]->html->value = date("Y-m-01");
      // $this->Filters[frExpiryDate]->html->type = "text";
      // $this->Filters[frExpiryDate]->html->class = "controlText controlNumeric datePicker";

      // print_rr($_POST);

      // $this->Filters[frIgnoreDateIssued]->tag = "input";
      // $this->Filters[frIgnoreDateIssued]->html->value = "on";
      // $this->Filters[frIgnoreDateIssued]->html->value = qs($_POST[frIgnoreDateIssued])=="on"?"on":"";
      // if($this->Filters[frIgnoreDateIssued]->html->value == "on")
      // {
      //    $this->Filters[frIgnoreDateIssued]->html->checked = "checked";
      // }
      // $this->Filters[frIgnoreDateIssued]->html->type = "checkbox";
      // $this->Filters[frIgnoreDateIssued]->html->onclick = "d('frDateIssued').disabled=this.checked;";


      // $this->Filters[frIgnoreExpiryDate]->tag = "input";
      // $this->Filters[frIgnoreExpiryDate]->html->value = "on";
      // if()
      // $this->Filters[frIgnoreExpiryDate]->html->value = qs($_POST[frIgnoreExpiryDate])=="on"?"on":"";

      // if($this->Filters[frIgnoreExpiryDate]->html->value == "on")
      // {
      //    $this->Filters[frIgnoreExpiryDate]->html->checked = "checked";
      // }
      // $this->Filters[frIgnoreExpiryDate]->html->type = "checkbox";
      // $this->Filters[frIgnoreExpiryDate]->html->onclick = "d('frExpiryDate').disabled=this.checked;";


      parent::__construct($DataKey);


   }

   public function getList($frInvoiceID = 0, $frClientID = 0, $frPracticeID = 0) //, $blnUseCheckbox = 0, $blnHideLearner = 0, $blnHideClient = 0, $blnHidePractice = 0)
   {
      global $SystemSettings;

      // print_rr($this->Filters[frIgnoreDateIssued]);
      // print_rr($this->Filters[frIgnoreExpiryDate]);

      $sqlClient = ", strClient as 'Client'";
      $sqlPractice = ", strPractice as 'Practice'";
      $sqlLearner = ", strLearner as 'Learner'";
      $sqlInvoiceNumber = ", strInvoiceNumber as 'Invoice Number'";

      if($frInvoiceID != 0)
      {
         $Where = "AND tblLicence.refInvoiceID = $frInvoiceID";
         $sqlClient =
         // $sqlPractice = "";
         $sqlInvoiceNumber = "";
      }
      elseif($frClientID != 0)
      {
         $Where = "AND tblLicence.refClientID = $frClientID";
         $sqlClient = "";
      }
      elseif($frPracticeID != 0)
      {
         $Where = "AND tblLicence.refPracticeID = $frPracticeID";
         $sqlPractice = "";
         $sqlClient = "";
      }
      else{

      // if($blnUseCheckbox == 1)
      // {
      //    // <input id="chkSelect" type="checkbox" value="checked" name="chkSelect[4844]">
      //    $strSelectable = ",concat('<input id=chkSelect type=checkbox value=checked name=chkSelect[', ID , '] >') AS 'Select' ";
      // }

         if($this->Filters[frSearch]->html->value != "")
         {
            $like = "LIKE(". $this->db->qs("%".$this->Filters[frSearch]->html->value."%") .")";
            $Where .= " AND (tblLicence.ID $like OR tblLicence.LicenceID $like OR strLearner $like OR strClient $like OR strPractice $like)";
         }
         if($this->Filters[frClient]->html->value != -99)
         {
            $Where .= " AND tblLicence.refClientID = ". $this->Filters[frClient]->html->value;
         }

         if($this->Filters[frLearner]->html->value != -99)
         {
            $Where .= " AND tblLicence.refLearnerID = ". $this->Filters[frLearner]->html->value;
         }

         if($this->Filters[frProduct]->html->value != -99)
         {
            $Where .= " AND tblLicence.refProductID = ". $this->Filters[frProduct]->html->value;
         }

         if($this->Filters[frPractice]->html->value != -99)
         {
            $Where .= " AND tblLicence.refPracticeID = ". $this->Filters[frPractice]->html->value;
         }

         // if(!isset($this->Filters[frIgnoreDateIssued]->html->checked))
         // {
         //    $Where .= " AND tblLicence.dtDateIssued = '". $this->Filters[frIgnoreDateIssued]->html->value ."'";
         // }

         // if(!isset($this->Filters[frIgnoreExpiryDate]->html->checked))
         // {
         //    $Where .= " AND tblLicence.dtDateExpiry = '". $this->Filters[frIgnoreExpiryDate]->html->value ."'";
         // }

         if($this->Filters[frStatus]->html->value != "- All -")
         {
            $Where .= " AND tblLicence.strStatus = '". $this->Filters[frStatus]->html->value."'";
         }
      }

      //added mod 1> 0 to check if there is a remainder so that the number format does noth ave decimals on whole numbers
      //20161108 - fixed sql for tblLicenceJoined - pj
      $this->ListSQL("
         SELECT ID, tblLicenceJoined.LicenceID as LicenceID $sqlInvoiceNumber $sqlLearner $sqlClient $sqlPractice, tblLicenceJoined.strLicenceDescription as 'Description' ,  strStatus as Status
         , if(dblAmountExcl mod 1 > 0 , FORMAT(dblAmountExcl,2) , FORMAT(dblAmountExcl,0) ) as `Amount Excl`
         , if(dblAmountIncl mod 1 > 0 , FORMAT(dblAmountIncl,2) , FORMAT(dblAmountIncl,0) ) as `Amount Incl`
         , dtDateIssued as `Date Issued`, dtDateExpiry as `Expiry Date`, strLastUser AS 'Last User', dtLastEdit AS 'Last Edit' $strSelectable
         FROM
         (
            SELECT ID, LicenceID , strProduct as 'strLicenceDescription' , strInvoiceNumber, strLearner, strClient, strPractice , strStatus, tblLicence.dblAmountExcl, tblLicence.dblAmountIncl, dtDateIssued , dtDateExpiry , tblLicence.strLastUser, tblLicence.dtLastEdit
            FROM tblProduct INNER JOIN (tblInvoice RIGHT JOIN (tblCourse RIGHT JOIN (tblLearner RIGHT JOIN (tblClient RIGHT JOIN (tblPractice RIGHT JOIN tblLicence ON tblPractice.PracticeID = tblLicence.refPracticeID) ON tblClient.ClientID = tblLicence.refClientID) ON tblLearner.LearnerID = tblLicence.refLearnerID) ON tblCourse.CourseID = tblLicence.refCourseID) ON tblInvoice.InvoiceID = tblLicence.refInvoiceID) ON tblProduct.ProductID = tblLicence.refProductID
            WHERE 1=1 $Where
            UNION ALL
            SELECT ID, LicenceID, concat(tblCourse.strCourse ,' (' , tblCourse.dtStartDate ,' - ' ,tblCourse.dtEndDate , ')') as 'strLicenceDescription',strInvoiceNumber ,strLearner, null as strClient, null as strPractice , strStatus, tblLicence.dblAmountExcl , tblLicence.dblAmountIncl, dtDateIssued , dtDateExpiry , tblLicence.strLastUser, tblLicence.dtLastEdit
            FROM tblInvoice RIGHT JOIN (tblCourse RIGHT JOIN (tblLearner RIGHT JOIN (tblClient RIGHT JOIN (tblPractice RIGHT JOIN tblLicence ON tblPractice.PracticeID = tblLicence.refPracticeID) ON tblClient.ClientID = tblLicence.refClientID) ON tblLearner.LearnerID = tblLicence.refLearnerID) ON tblCourse.CourseID = tblLicence.refCourseID) ON tblInvoice.InvoiceID = tblLicence.refInvoiceID
            WHERE 1=1 $Where
         ) as tblLicenceJoined
         GROUP BY ID
         ORDER BY dtDateIssued, LicenceID

         ", 0, "licence.php", "");

      $this->Columns["Amount Excl"]->html->align= "right";
      $this->Columns["Amount Incl"]->html->align= "right";

      return $this->renderTable("Licence List");
   }

   // public function getLicenceSubList()
   // {
   //    global $SystemSettings;
   //    if($frInvoiceID > 0)
   //    {
   //       $where = "AND refInvoiceID = $frInvoiceID";
   //    }

   //    if($blnUseCheckbox == 1)
   //    {
   //       // <input id="chkSelect" type="checkbox" value="checked" name="chkSelect[4844]">
   //       $strSelectable = ",concat('<input id=chkSelect type=checkbox value=checked name=chkSelect[', ID , '] >') AS 'Select' ";
   //    }

   //    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[frClient]->html->value != 0)
   //    {
   //       $Where .= " AND tblLicence.refClientID = ". $this->Filters[frClient]->html->value;
   //    }
   //    if($this->Filters[frStatus]->html->value != -1)
   //    {
   //       $Where .= " AND tblCourse.blnActive = ". $this->Filters[frStatus]->html->value;
   //    }

   //    $this->ListSQL("
   //       SELECT ID,tblLicenceJoined.LicenceID as `Licence ID`, tblLicenceJoined.strLicenceDescription as 'Description' , strStatus as Status, dblAmountExcl as `Amount Excl`, dblAmountIncl as `Amount Incl`, dtDateIssued as `Date Issued`, dtDateExpiry as `Expiry Date`, strLastUser AS 'Last User', dtLastEdit AS 'Last Edit' $strSelectable
   //       FROM
   //       (
   //          SELECT ID, LicenceID , strProduct as 'strLicenceDescription' , strStatus, dblAmountExcl, dblAmountIncl, dtDateIssued , dtDateExpiry , tblLicence.strLastUser, tblLicence.dtLastEdit
   //          FROM tblLicence INNER JOIN tblProduct on refProductID = ProductID
   //          WHERE 1=1 $where
   //          UNION ALL
   //          SELECT ID, LicenceID, concat(tblCourse.strCourse ,' (' , tblCourse.dtStartDate ,' - ' ,tblCourse.dtEndDate , ')') as 'strLicenceDescription' , strStatus, dblAmountExcl , dblAmountIncl, dtDateIssued , dtDateExpiry , tblLicence.strLastUser, tblLicence.dtLastEdit
   //          FROM tblLicence INNER JOIN tblCourse on refCourseID = CourseID
   //          WHERE 1=1 $where
   //       ) as tblLicenceJoined
   //       ORDER BY dtDateIssued, LicenceID

   //       ");

   //    return $this->renderTable("Licence List");
   // }


   public static function Save(&$LicenceID)
   {
      $db = new NemoDatabase("tblLicence", $LicenceID, null, 0);

      $db->SetValues($_POST);

      //20161025 - PH2 - refLearner, and refPracticeID need to be NULL if their value is 0. refClientID is -1 by default - christiaan
      if($db->Fields[refClientID] == 0)
      {
         $ldb->Fields[refClientID] = "-1";
      }
      if($db->Fields[refPracticeID] == 0)
      {
         $ldb->Fields[refPracticeID] = "NULL";
      }
      if($db->Fields[refLearnerID] == 0)
      {
         $ldb->Fields[refLearnerID] = "NULL";
      }

      $db->Fields[strLastUser] = $_SESSION['USER']->USERNAME;
      //$db->Fields[dtLastEdit] = date("YmdHi");
      $result = $db->Save();
      //print_rr($db->Fields);
      if($LicenceID == 0) $LicenceID = $db->ID[LicenceID];

      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 tblLicence WHERE LicenceID = ". $xdb->qs($key));
         }
         return "Records Deleted.";
      }
   }
}
?>