<?php
/*
ALTER TABLE `tblInvoice` DROP FOREIGN KEY `tblInvoice.strLearner`;
ALTER TABLE `tblInvoice` ADD CONSTRAINT `tblInvoice.strLearner` FOREIGN KEY ( `refLearnerID` ) REFERENCES `tblLearner` ( `LearnerID` ) ON UPDATE CASCADE;

ALTER TABLE `tblInvoice` DROP FOREIGN KEY `tblInvoice.strClient`;
ALTER TABLE `tblInvoice` ADD CONSTRAINT `tblInvoice.strClient` FOREIGN KEY ( `refClientID` ) REFERENCES `tblClient` ( `ClientID` ) ON UPDATE CASCADE;

ALTER TABLE `tblInvoice` DROP FOREIGN KEY `tblInvoice.strPractice`;
ALTER TABLE `tblInvoice` ADD CONSTRAINT `tblInvoice.strPractice` FOREIGN KEY ( `refPracticeID` ) REFERENCES `tblPractice` ( `PracticeID` ) ON UPDATE CASCADE;

//added these - neils
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblInvoice` DROP FOREIGN KEY `tblInvoice.strClient`;
ALTER TABLE `tblInvoice` ADD CONSTRAINT `tblInvoice.strClient` FOREIGN KEY (`refClientID`) REFERENCES `tblClient` (`ClientID`) ON UPDATE CASCADE;

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `tblInvoice` DROP FOREIGN KEY `tblInvoice.strPractice`;
ALTER TABLE `tblInvoice` ADD CONSTRAINT `tblInvoice.strPractice` FOREIGN KEY (`refPracticeID`) REFERENCES `tblPractice` (`PracticeID`) ON UPDATE CASCADE;

//20161110 - PH2 Extra - Invoice/Order process merged - Christiaan
//20180322 - Update VAT to use SystemSettings[VAT] and change VAT from 14% to 15% for 20180401 countrywide VAT increase - CL
//20180327 -add condition to choose between using learner or practice record when filling in the pdf recipient details - CL
//20190704 - This query returns more than one and creates cronjob for the first result record which may not always be the newly registered course and ends up sending wrong email - Rachel
*/

include_once("_nemo.list.cls.php");
include_once("includes/invoicePDF.cls.php");

class Invoice extends NemoList
{
   private $ID = 0;

   public function __construct($DataKey)
   {

      $this->Filters[frSearch]->tag = "input";
      $this->Filters[frSearch]->html->value = "";
      $this->Filters[frSearch]->html->class = "controlText";

      // $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 tblCourse.blnActive = 1
      //                   GROUP BY CourseID, strCourse
      //                   ORDER BY ControlText ASC";

      $this->Filters[frPaymentMethod]->tag = "select";
      $this->Filters[frPaymentMethod]->html->value = "-1";
      $this->Filters[frPaymentMethod]->html->class = "controlText";
      $this->Filters[frPaymentMethod]->sql = "SELECT -1 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT 'Credit Card' AS ControlValue, 'Credit Card' AS ControlText
                        UNION ALL
                        SELECT 'EFT' AS ControlValue, 'EFT' AS ControlText
                        UNION ALL
                        SELECT 'Internal' AS ControlValue, 'Internal' AS ControlText
                        UNION ALL
                        SELECT 'Voucher' AS ControlValue, 'Voucher' AS ControlText
                        ORDER BY ControlText ASC";

      $this->Filters[frLocked]->tag = "select";
      $this->Filters[frLocked]->html->value = "-1";
      $this->Filters[frLocked]->html->class = "controlText";
      $this->Filters[frLocked]->sql = "SELECT -1 AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT 1 AS ControlValue, 'Locked' AS ControlText
                        UNION ALL
                        SELECT 0 AS ControlValue, 'Unlocked' AS ControlText
                        ORDER BY ControlText ASC";

      $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-m-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->class = "controlText";

      parent::__construct($DataKey);
   }


   // protected function ToolBar()
   // {
   //    return "
   //    <div id='divToolBar' style='min-height: 90px;'>
   //       <div style='float:left;'>
   //          <table class='tblBlank'>
   //             <tr>
   //                <td><img src='images/extraimages/header/icon-48-module.png' height='44'></img></td>
   //                <td style='padding: 16px;'><span class='textHeading'>". $this->ToolBar->Label ."</span></td>
   //                <td style='position: relative; border: 0px dashed orange; padding: 0px;'>". $this->ToolBar->Block . $this->renderFilters() ."</td>
   //             </tr>
   //          </table>
   //       </div>
   //       <div class='divButtons'>
   //          ". $this->renderToolbarButtons() ."
   //       </div>
   //    </div>
   //    ";
   // }


   public function getList()
   {
      global $xdb, $SystemSettings;

      $Where = "";

      if($this->Filters[frSearch]->html->value != "")
      {
         $like = "LIKE (". $xdb->qs("%". $this->Filters[frSearch]->html->value ."%") .")";
         $Where .= " AND (tblInvoice.strInvoiceNumber $like
                           OR tblLearner.strLearner $like
                           OR tblInvoice.dblAmountIncl $like
                           OR tblInvoice.dblAmountExcl $like) ";
      }

      // if($this->Filters[frCourse]->html->value != 0)
      // {
      //    $Where .= " AND tblLicence.refCourseID = '" . $this->Filters[frCourse]->html->value . "' ";
      // }

      if($this->Filters[frPaymentMethod]->html->value != -1)
      {
         $Where .= " AND tblInvoice.strPaymentMethod = '" . $this->Filters[frPaymentMethod]->html->value . "' ";
      }

      if($this->Filters[frLocked]->html->value != -1)
      {
         $Where .= " AND tblInvoice.blnLocked = '" . $this->Filters[frLocked]->html->value . "' ";
      }

      if($this->Filters[frIgnoreDate]->html->checked != "checked")
      {
         $Where .= " AND dtInvoiceDate >= '". $this->Filters[frStartDate]->html->value ."' AND dtInvoiceDate <= '". $this->Filters[frEndDate]->html->value ."'";
      }

      // $this->ListSQL("
      //    SELECT tblInvoice.InvoiceID, tblInvoice.strInvoiceNumber AS 'Invoice Number', tblInvoice.dtInvoiceDate AS `Invoice Date`, tblCourse.strCourse as 'Course'
      //    , tblLearner.strLearner as Learner, tblInvoice.strPaymentMethod as 'Payment Method', tblInvoice.blnLocked as Locked, tblInvoice.dblAmountIncl as `Amount Incl`
      //    , tblInvoice.strLastUser AS 'Last User', tblInvoice.dtLastEdit AS 'Last Edit'
      //    FROM tblCourse RIGHT JOIN (tblLicence RIGHT JOIN (tblInvoice INNER JOIN tblLearner ON tblInvoice.refLearnerID = tblLearner.LearnerID) ON tblLicence.refLearnerID = tblLearner.LearnerID) ON tblCourse.CourseID = tblLicence.refCourseID
      //    WHERE blnDeleted = 0 $Where
      //    ORDER BY tblInvoice.InvoiceID DESC, tblInvoice.dtInvoiceDate",1);

      $this->ListSQL("
         SELECT tblInvoice.InvoiceID, tblInvoice.strInvoiceNumber AS 'Invoice Number', tblInvoice.dtInvoiceDate AS `Invoice Date`
         , CONCAT(tblLearner.strLearner, ' / ',if(tblInvoice.refPracticeID IS NULL,'N/A',tblPractice.strPractice)) as 'Learner / Practice'
         , tblInvoice.strPaymentMethod as 'Payment Method', blnLocked as Locked, dblAmountIncl as `Amount Incl`
         , tblInvoice.strLastUser AS 'Last User', tblInvoice.dtLastEdit AS 'Last Edit'
         FROM tblInvoice
            LEFT JOIN tblLearner ON tblLearner.LearnerID = tblInvoice.refLearnerID
            LEFT JOIN tblPractice ON tblPractice.PracticeID = tblInvoice.refPracticeID
         WHERE blnDeleted = 0 $Where
         ORDER BY tblInvoice.InvoiceID DESC, tblInvoice.dtInvoiceDate",0);

      foreach ($this->Data as $intRowNumber => $rowListItem)
      {
         $this->Data[$intRowNumber]["Amount Incl"] = number_format( $rowListItem["Amount Incl"], 0, ".", ",");
      }

      $this->Columns["Amount Incl"]->html->align = "right";
      //$this->

      return $this->renderTable("Invoice List");
   }

   public static function generateAddlicencePanel()
   {
      global $SystemSettings, $xdb;
      $imgRequired = $SystemSettings["imgRequired"];

      // $practiceDDL = "<select class='controlText' tag='select' value=''  id='PracticeID' name='PracticeID''>";
      // $practiceDDL .= $xdb->ListOptions("
      //    SELECT -1 AS Value, '-  Select  -' AS Text
      //       UNION
      //    SELECT PracticeID AS Value, strPractice as Text
      //    FROM tblPractice
      //    WHERE blnActive = 1
      //    ORDER BY Text", 0);
      // $practiceDDL .= "</select>";

      // $clientDDL = "<select class='controlText' tag='select' value='' id='ClientID' name='ClientID'>";
      // $clientDDL .= $xdb->ListOptions("
      //          SELECT -1 AS Value, '-  Select  -' AS Text
      //             UNION
      //          SELECT ClientID AS Value, strClient as Text
      //          FROM tblClient
      //          WHERE blnActive = 1
      //          ORDER BY Text", 0);
      //  $clientDDL .= "</select>";

      //LicenceTypeID could be the course or productID

      //Start Product ddl section
      $licenceTypeDDL .=  "<optgroup label='Product' >";

      $rst = $xdb->doQuery("SELECT ProductID as Value, strProduct as Text
         FROM tblProduct
         WHERE blnActive = 1 AND blnRequiresLicence = 1 ORDER BY intOrder",0);

      while($row = $xdb->fetch_object($rst))
      {
         $licenceTypeDDL .=  "<option value='".$row->Value."' type='Tools'>".$row->Text."</option>";
      }

      //Start Course ddl section
      $licenceTypeDDL .=  "</optgroup>
                           <optgroup label='Course' >";

      $rst = $xdb->doQuery("Select tblCourse.CourseID AS Value , concat(tblCourse.strCourse ,' (' ,tblCourse.dtStartDate ,' - ' ,tblCourse.dtEndDate, ')') AS Text
           FROM tblCourseVenue RIGHT JOIN (tblVoucher RIGHT JOIN ((
            (SELECT refCourseTypeID, CourseID
            FROM tblCourse
            WHERE blnActive = 1
            GROUP BY refCourseTypeID, CourseID
            )  AS tblCourses INNER JOIN tblCourse ON tblCourses.CourseID = tblCourse.CourseID) INNER JOIN tblCourseType ON tblCourses.refCourseTypeID = tblCourseType.CourseTypeID) ON tblVoucher.refCourseTypeID = tblCourseType.CourseTypeID) ON tblCourseVenue.refCourseID = tblCourse.CourseID
         WHERE tblCourse.blnActive = 1 AND (dtEndDate >= '". date("Y-m-d", strtotime("last Sunday")) ."' OR tblCourseType.strCourseTypeCode = 'OMW') AND tblCourse.blnWorkshop = 0
            AND tblCourse.strCourseCode <> '2016/TEA/2'
         GROUP BY tblCourses.CourseID
         ORDER BY tblCourse.strCourse, tblCourse.dtStartDate");

      while($row = $xdb->fetch_object($rst))
      {
         $licenceTypeDDL .=  "<option value='".$row->Value."' type='Course'>".$row->Text."</option>";
      }
      //Start Workshop ddl section
      $licenceTypeDDL .=  "</optgroup>
                           <optgroup label='Workshop' >";

      $rst = $xdb->doQuery("Select tblCourse.CourseID AS Value , concat(tblCourse.strCourse ,' (' ,tblCourse.dtStartDate ,' - ' ,tblCourse.dtEndDate, ')') AS Text
        FROM tblVoucher RIGHT JOIN ((tblRSVP RIGHT JOIN (tblCourseVenue RIGHT JOIN tblCourse ON tblCourseVenue.refCourseID = tblCourse.CourseID) ON tblRSVP.refCourseID = tblCourse.CourseID) INNER JOIN tblCourseType ON tblCourse.refCourseTypeID = tblCourseType.CourseTypeID) ON tblVoucher.strVoucher = tblRSVP.strVoucher
         WHERE tblCourse.blnActive=1 AND tblCourse.blnWorkshop = 1 AND tblCourse.blnRSVPOnly = 0 AND tblCourse.blnSpecialCodeOnly = 0 AND tblCourse.dtStartDate > '".date('Y-m-d')."'
         GROUP BY tblCourse.CourseID
         ORDER BY tblCourse.strCourse, tblCourse.strCourseHTML, tblCourse.dtStartDate",0);

      while($row = $xdb->fetch_object($rst))
      {
         $licenceTypeDDL .=  "<option value='".$row->Value."' type='Course'>".$row->Text."</option>";
      }
      $licenceTypeDDL .= "</optgroup>";

      //20180322 - Update VAT to use SystemSettings[VAT] and change VAT from 14% to 15% for 20180401 countrywide VAT increase - CL
      return "<div id='divAddLicence' style='display:none;'>
               <table class='dora-DetailsTable' width='100%' cellspacing='1' cellpadding='2' border='0'>
                     <caption>
                        Add Licences
                     </caption>
                     <tr>
                        <td class='tdDetailsLeft' align='right'>
                           <label for=''>Licence Type :</label> $imgRequired
                        </td>
                        <td class='tdDetailsRight' align='left'>
                           <select class='controlText' tag='select' value='' id='LicenceTypeID' name='LicenceTypeID' onChange='jsUpdatelicenceType();'>
                           <option value =-1>- Select -</option>
                           $licenceTypeDDL
                           </select>
                           <input type='hidden' id='strLicenceType' name='strLicenceType' value=''>
                        </td>
                     </tr>
                      <tr>
                        <td class='tdDetailsLeft' align='right'>
                           <label for=''>Quantity :</label> $imgRequired
                        </td>
                        <td class='tdDetailsRight' align='left'>
                           <input type='text' class='controlText controlNumeric' tag='input' value='1' id='intLicenceQty' name='intLicenceQty'>
                        </td>
                     </tr>
                     <tr>
                        <td class='tdDetailsLeft' align='right'>
                           <label for=''>Vat :</label> $imgRequired
                        </td>
                        <td class='tdDetailsRight' align='left'>
                           VAT ".($SystemSettings[VAT] * 100)."%<input type='radio' tag='input' id='blnVatAmount' name='radVat' checked value='1'>
                           No VAT<input type='radio' tag='input' id='blnNoVat' name='radVat' value='0'>
                        </td>
                     </tr>
                     <tr>
                        <td colspan='2' style='padding: 8px 4px;' align='right'>
                           <input type='submit' name='Action' id='Action' class='controlButton' value=\"Add Licence\" onClick='return (jsNemoValidateSave() && addLicence())' >
                        </td>
                     </tr>
                  </table>

            </div>"
            .js("
                  function jsShowAddLicenceDiv()
                  {
                     $('#divAddLicence').show('fast');
                     $('#btnNew').parent().attr('style', 'display:none');
                  }
                  function jsUpdatelicenceType()
                  {
                     var LicenceTypeID = $('#LicenceTypeID').val();
                     var strLicenceType = $('#LicenceTypeID option:selected').attr('type');
                     //var strLicenceType = $('#LicenceTypeID').attr('type');

                     $('#strLicenceType').val(strLicenceType);
                  }
               ");
   }

   public static function Addlicence($LicenceID = 0)
   {
      global $SystemSettings, $xdb, $DT;
      //print_rr($_POST);
      //Validation

      $InvoiceID = $_POST[InvoiceID];
      $LearnerID = $_POST[refLearnerID];
      $strLicenceType = $_POST[strLicenceType];
      $refPracticeID = $_POST[refPracticeID];
      $refClientID = $_POST[refClientID];
      $LicenceTypeID = $_POST[LicenceTypeID];
      $intLicenceQty = $_POST[intLicenceQty];
      $dblVAT = $_POST[radVat] == 1 ?  $SystemSettings["VAT"] : 0;

      $strMsg = "";

      //if the quantity doesn't exist, or is zero, or is not a number
      if(!isset($intLicenceQty) || ($intLicenceQty*1) == 0 || !is_numeric($intLicenceQty))
      {
         $strMsg .= "quantity incorrect, ";
      }

      if($strMsg != "")
      {
         $strMsg = substr_replace($strMsg ,"",-2);
         return "Please resolve the following issues before trying again: ".$strMsg.". ";
      }

      //Used a loop here as the quantity of licences is a field editable by the user. Used mainly for css -Christiaan
      $x = 0;
      while($x < $intLicenceQty)//start loop
      {
         $db = new NemoDatabase("tblLicence", $LicenceID, null, 0);

         //IMPORTANT NOTE: $_POST[LicenceTypeID] has either the CourseID or the ProductID. -Christiaan
         switch ($strLicenceType)
         {
            case "Tools":
               $rowProduct = $xdb->getRowSQL("SELECT * FROM tblProduct WHERE ProductID = ". $xdb->qs($LicenceTypeID), 0);
               $strProductCode = $rowProduct->strShortCode;
               $dblCost = $rowProduct->dblPrice;

               $db->Fields[refProductID] = $LicenceTypeID;
               $db->Fields[refCourseID] = "NULL";
            break;

            case "Course":
               $rowCourse = $xdb->getRowSQL("SELECT tblCourse.*, tblCourseType.dblCost, tblCourseType.strCourseTypeCode
                  FROM tblCourse INNER JOIN tblCourseType ON tblCourse.refCourseTypeID = tblCourseType.CourseTypeID
                  WHERE CourseID = ". $xdb->qs($LicenceTypeID),0);
               $strProductCode = $rowCourse->strCourseTypeCode;
               $dblCost = $rowCourse->dblCost;

               $db->Fields[refProductID] = "NULL";
               $db->Fields[refCourseID] = $LicenceTypeID;
            break;
         }

         $rowClient = $xdb->getRowSQL("SELECT * FROM tblClient WHERE ClientID = ". $xdb->qs($refClientID),0);
         $strClientCode = $rowClient->strShortCode;

         $db->Fields[LicenceID] = $strProductCode . $strClientCode . Obfuscate(); ///$LicenceTypeID is the CourseTypeID, not the CourseID - must be set on learner redemption
         $db->Fields[refLearnerID] = $LearnerID;
         $db->Fields[refInvoiceID] = $InvoiceID;
         $db->Fields[refPracticeID] = $refPracticeID;
         $db->Fields[refClientID] = $refClientID;
         $db->Fields[strStatus] = "Dormant";
         $db->Fields[dblAmountExcl] = round($dblCost,2);
         $db->Fields[dblAmountIncl] = round($dblCost + ($dblCost * $dblVAT) ,2);
         $db->Fields[dtDateIssued] = $DT;
         $db->Fields[dtDateActivated] = "NULL";
         $db->Fields[dtDateExpiry] = "NULL"; //$db->Fields[dtDateExpiry] = date("Y-m-d", strtotime("+ 1 year - 1 day")); dtDateExpiry gets set in FinalizeInvoice()
         $db->Fields[strLastUser] = $_SESSION['USER']->USERNAME;

         if($db->Fields[refLearnerID] == 0)
         {
            $db->Fields[refLearnerID] = "NULL";
         }
         if($db->Fields[refPracticeID] == 0)
         {
            $db->Fields[refPracticeID] = "NULL";
         }
         if($db->Fields[refClientID] == 0)
         {
            $db->Fields[refClientID] = "NULL"; //pj wants ClientID to be null and not -1
         }
         if($db->Fields[refCourseID] == 0)
         {
            $db->Fields[refCourseID] = "NULL";
         }

         if($LicenceID == 0)
         {
            $db->Fields[strFirstUser] = $_SESSION['USER']->USERNAME;
            $db->Fields[dtFirstEdit] = $DT;
         }
         //print_rr($db);

         $result = $db->Save(0, 0);
         //print_rr($db->Fields);

         $x++;
      }//end loop


      //Update Invoice with licence total
      $row = $xdb->getRowSQL("SELECT SUM(dblAmountExcl) As dblAmountExcl, SUM(dblAmountIncl) As dblAmountIncl FROM tblLicence WHERE refInvoiceID = ". $xdb->qs($InvoiceID), 0);
      if($row)
      {
         $xdb->doQuery("UPDATE tblInvoice SET dblAmountExcl = ROUND($row->dblAmountExcl,4), dblAmountIncl = ROUND($row->dblAmountIncl,4) WHERE InvoiceID = ". $xdb->qs($InvoiceID), 0);
      }

      if($result->Error == 1)
         return $result->Message;
      else
         return "Licences Added. ";
   }


   public static function DeleteLicences($chkSelect, $InvoiceID)
   {

//FIX: WHERE are you getting $InvoiceID from?!
      global $xdb;
      //print_rr($chkSelect);
      if(count($chkSelect) > 0){
         foreach($chkSelect as $key => $value)
         {
            $xdb->doQuery("DELETE FROM tblLicence WHERE ID = ". $xdb->qs($key));
         }

         //Update Invoice with licence total
         $row = $xdb->getRowSQL("SELECT SUM(dblAmountExcl) As dblAmountExcl, SUM(dblAmountIncl) As dblAmountIncl FROM tblLicence WHERE refInvoiceID = '". $InvoiceID."'");
         if($row)
         {
            $xdb->doQuery("UPDATE tblInvoice SET dblAmountExcl = ROUND($row->dblAmountExcl,4), dblAmountIncl = ROUND($row->dblAmountIncl,4) WHERE InvoiceID = '". $InvoiceID."'");
         }

         return "Records Deleted. ";
      }


   }

   public static function Save(&$InvoiceID)
   {
      global $xdb;

      $db = new NemoDatabase("tblInvoice", $InvoiceID, null, 0);

//FIX: not sure this is going to work - twhat happens when i save a locked invoice? securitygroup.lnSpecial allows me to do that
      //unset($_POST[blnLocked]);

      $db->SetValues($_POST);
      $db->Fields[strLastUser] = $_SESSION['USER']->USERNAME;

      if($db->Fields[refLearnerID] == 0)
      {
         $db->Fields[refLearnerID] = "NULL";// pj wants this to be NULL and not -1 - christiaan
      }
      if($db->Fields[refPracticeID] == 0)
      {
         $db->Fields[refPracticeID] = "NULL";
      }
      if($db->Fields[refClientID] == 0)
      {
         $db->Fields[refClientID] = "NULL";
      }

      //20161110 - PH2 Extra - Invoice/Order process merged - Christiaan
      if($InvoiceID != 0)
      {
        $db->Fields["strInvoiceNumber"] = "KM".str_pad($InvoiceID, 8, "0", STR_PAD_LEFT);
      }
      else
      {
        $db->Fields["strInvoiceNumber"] = "KM".str_pad(Obfuscate(), 8, "0", STR_PAD_LEFT);
      }

//print_rr($db->Fields);
      $result = $db->Save(0, 0);

      if($InvoiceID == 0) $InvoiceID = $db->ID[InvoiceID];

      //Update Invoice with licence total
      $row = $xdb->getRowSQL("SELECT SUM(dblAmountExcl) As dblAmountExcl, SUM(dblAmountIncl) As dblAmountIncl FROM tblLicence WHERE refInvoiceID = ". $xdb->qs($InvoiceID), 0);
      if($row)
      {
         if($row->dblAmountExcl == "")
         {
            $row->dblAmountExcl = 0;
         }
         if($row->dblAmountIncl == "")
         {
            $row->dblAmountIncl = 0;
         }
         $xdb->doQuery("UPDATE tblInvoice SET dblAmountExcl = ROUND($row->dblAmountExcl,4), dblAmountIncl = ROUND($row->dblAmountIncl,4) WHERE InvoiceID = ". $xdb->qs($InvoiceID), 0);
      }

      if($result->Error == 1)
         return $result->Message;
      else
         return "Details Saved. ";
   }

   public static function Finalize(&$InvoiceID)
   {
      global $xdb, $DT;

      //ini
      $blnUsePracticeDetails = 0;

      //process
      if(!FinalizeInvoice($InvoiceID))
      {
         return $result->Message;
      }
      else
      {
         //Create Invoice PDF
         $rowInvoice = $xdb->getRowSQL("
            SELECT tblInvoice.InvoiceID, tblLearner.*
            FROM tblInvoice INNER JOIN tblLearner ON tblLearner.LearnerID = tblInvoice.refLearnerID
            WHERE InvoiceID = ". $xdb->qs($InvoiceID),0);
         //20180327 -add condition to choose between using learner or practice record when filling in the pdf recipient details - CL
         if($xdb->Rows == 0)
         {
            $blnUsePracticeDetails = 1;
         }
         
         $pdf = new PDF_Invoice($InvoiceID, $blnUsePracticeDetails);

         $filepath = $pdf->getFilePath();
         $filename = $pdf->getFileName();

         $strCronFields[InvoiceID] = $rowInvoice->InvoiceID;
         $strCronFields[filepath] = $filepath;
         $strCronFields[filename] = $filename;
         $strCronFields[TransactionID] = $_GET[Reference];
         $strCronFields[strEmail] = $rowInvoice->strEmail;
         $strCronFields[strFullName] = $rowInvoice->strDisplayName;

         $rstCourse = $xdb->doQuery("SELECT tblCourse.*
            FROM (tblLicence INNER JOIN tblInvoice ON tblLicence.refInvoiceID = tblInvoice.InvoiceID) INNER JOIN tblCourse ON tblLicence.refCourseID = tblCourse.CourseID
            WHERE tblInvoice.InvoiceID = ". $xdb->qs($InvoiceID));
/*         $rowCourse = $xdb->getRowSQL("SELECT tblCourse.*
            FROM (tblLicence INNER JOIN tblInvoice ON tblLicence.refInvoiceID = tblInvoice.InvoiceID) INNER JOIN tblCourse ON tblLicence.refCourseID = tblCourse.CourseID
            WHERE tblInvoice.InvoiceID = ". $xdb->qs($InvoiceID));

         $strCronFields[Course] = $rowCourse->strCourse;
         $strCronFields[StartDate] = date("j F Y", strtotime($rowCourse->dtStartDate));*/
         //print_rr($strCronFields); die;
         //20190704 - This query returns more than one and creates cronjob for the first result record which may not always be the newly registered course and ends up sending wrong email - Rachel
         //Create a separate cronjob for Exec Assistant Course since the course has different email signatures
         $blnExecutiveAssistant = false;
         $blnNonExecutive = false;
         $dblNonExecCost = 0;

         while($rowCourse = $xdb->fetch_object($rstCourse))
         {
            if($rowCourse->refCourseTypeID == 6)
            {
               $blnExecutiveAssistant = true;
               $strEACourse = $rowCourse->strCourse;
               $dtEAStartDate = $rowCourse->dtStartDate;

            }else{
               $blnNonExecutive = true;
               $dblNonExecCost += $rowCourse->dblCost;
               $strCourse = $rowCourse->strCourse;
               $dtStartDate = $rowCourse->dtStartDate;
            }
         }

        if($blnExecutiveAssistant == true){

            $strCronFields[Course] = str_replace("'","", $strEACourse);
            $strCronFields[StartDate] = date("j F Y", strtotime($dtEAStartDate));
            $strSerializedFields = serialize($strCronFields);

            $xdb->doQuery("INSERT INTO tblCronJob (strStatus, strJobType, strSerializedFields, strFirstUser, dtFirstEdit, strLastUser) VALUES
                     ('ToDo', 'Invoice', '" . $strSerializedFields . "', '".$_SESSION[USER]->USERNAME."', '$DT', '".$_SESSION[USER]->USERNAME."')");

         }

         if($blnNonExecutive == true && $dblNonExecCost > 0)
         {

            $strCronFields[Course] = str_replace("'","", $strCourse);
            $strCronFields[StartDate] = date("j F Y", strtotime($dtStartDate));
            $strSerializedFields = serialize($strCronFields);

            $xdb->doQuery("INSERT INTO tblCronJob (strStatus, strJobType, strSerializedFields, strFirstUser, dtFirstEdit, strLastUser) VALUES
                     ('ToDo', 'Invoice', '" . $strSerializedFields . "', '".$_SESSION[USER]->USERNAME."', '$DT', '".$_SESSION[USER]->USERNAME."')");

         }

         // $strCronFields[Course] = $rowCourse->strCourse;
         // $strCronFields[StartDate] = date("j F Y", strtotime($rowCourse->dtStartDate));


         // $strSerializedFields = serialize($strCronFields);
         // $xdb->doQuery("INSERT INTO tblCronJob (strStatus, strJobType, strSerializedFields, strFirstUser, dtFirstEdit, strLastUser) VALUES
         //                   ('ToDo', 'Invoice', '" . $strSerializedFields . "', '".$_SESSION[USER]->USERNAME."', '$DT', '".$_SESSION[USER]->USERNAME."')");

         return "Invoice finalized. ";
      }

   }
   
   public static function Delete($chkSelect)
   {
      global $xdb;
      //print_rr($chkSelect);
      if(count($chkSelect) > 0){
      foreach($chkSelect as $key => $value)
      {
         //$xdb->doQuery("DELETE FROM tblInvoice WHERE InvoiceID = ". $xdb->qs($key));
         $xdb->doQuery("UPDATE tblInvoice SET blnDeleted = 1, strLastUser = 'InvoiceList.Delete()::".$_SESSION[USER]->USERNAME."' WHERE InvoiceID = ". $xdb->qs($key));
      }
         return "Records Deleted. ";
      }
   }
}
?>