<?php
   include_once("_nemo.list.cls.php");

   $rpt = new NemoList(null);

   $rpt->isSelectable = 0;
   $rpt->isSortable = 0;

   $strFilename = "Invoice_Report_". date("Ymd");
   $Where = "";

   if($page->Filters[frIgnoreDates]->html->checked != "checked")
   {
      $Where .= " AND (tblInvoice.dtInvoiceDate BETWEEN '". $page->Filters[frStartDate]->html->value ."' AND '". $page->Filters[frEndDate]->html->value ."')";
      $strCaption = $page->Filters[frStartDate]->html->value ." to ". $page->Filters[frEndDate]->html->value;
   }
   else
   {
      $strCaption = "";
   }

   if($page->Filters[frPaymentMethod]->html->value != -1)
   {
      $Where .= " AND tblInvoice.strPaymentMethod = '". $page->Filters[frPaymentMethod]->html->value ."'";
   }

   if($page->Filters[frCourseType]->html->value != 0)
   {
      $Where .= " AND tblCourse.refCourseTypeID = '". $page->Filters[frCourseType]->html->value ."'";
   }

   if($page->Filters[frFinal]->html->value != -1)
   {
      $Where .= " AND tblInvoice.blnLocked = '". $page->Filters[frFinal]->html->value ."'";
   }

      $rpt->ListSQL("SELECT CONCAT('<a href=invoice.php?Action=Edit&InvoiceID=',tblInvoice.InvoiceID,' target=_blank >',tblInvoice.strInvoiceNumber,'</a>') AS 'Invoice / Order Number'
      , tblInvoice.strPaymentMethod AS 'Payment Method'
      , CASE WHEN tblInvoice.blnLocked = 1 THEN '<b class=textColour>Yes</b>' ELSE '<b class=textRed>No</b>' END AS 'Final'
      , tblInvoice.dtInvoiceDate AS 'Invoice Date', derLLC.strLearner AS 'Name', derLLC.strCompany AS 'Company', derLLC.strVatNumber AS 'VAT Number'
      , derLLC.strAddress1 AS 'Address 1', derLLC.strAddress2 AS 'Address 2', derLLC.strAddress3 AS 'Address 3'
      , derLLC.strCode AS 'Code', derLLC.strCourse AS 'Course Name', tblRSVP.strStatus AS 'Atendance Status'
      , Count(derLLC.LicenceID) AS 'Licences'
      , Sum(derLLC.dblAmountExcl) AS 'Amount (Excl)', Sum(derLLC.dblAmountIncl - derLLC.dblAmountExcl) AS 'VAT', Sum(derLLC.dblAmountIncl) AS 'Amount'
      , tblInvoice.TransactionID, tblInvoice.strLastUser as 'Last User', tblInvoice.dtLastEdit as 'Last Edit'
      FROM (
            (SELECT tblLearner.LearnerID, tblLearner.strLearner, tblLearner.strCompany, tblLearner.strEmail, tblLearner.strVatNumber, tblLearner.strAddress1, tblLearner.strAddress2, tblLearner.strAddress3, tblLearner.strCode, tblLicence.refInvoiceID, tblLicence.LicenceID, tblLicence.dblAmountExcl, tblLicence.dblAmountIncl,tblCourse.CourseID, tblCourse.strCourse 
            FROM (tblLearner INNER JOIN tblLicence ON tblLearner.LearnerID = tblLicence.refLearnerID) INNER JOIN tblCourse ON tblLicence.refCourseID = tblCourse.CourseID 
            )  AS derLLC 
         INNER JOIN tblInvoice ON derLLC.refInvoiceID = tblInvoice.InvoiceID) LEFT JOIN tblRSVP ON (derLLC.strEmail = tblRSVP.strEmail) AND (derLLC.CourseID = tblRSVP.refCourseID)
      
      WHERE 1=1 AND tblInvoice.blnDeleted = 0 $Where
      GROUP BY tblInvoice.strInvoiceNumber, tblInvoice.strPaymentMethod, Final, tblInvoice.dtInvoiceDate, derLLC.strLearner, derLLC.strCompany, derLLC.strVatNumber, derLLC.strAddress1, derLLC.strAddress2, derLLC.strAddress3, derLLC.strCode, derLLC.strCourse, tblRSVP.strStatus, tblInvoice.TransactionID
      ORDER BY tblInvoice.InvoiceID DESC, Amount DESC",0);

   // $rpt->ListSQL("SELECT CONCAT('<a href=invoice.php?Action=Edit&InvoiceID=',tblInvoice.InvoiceID,' target=_blank >',tblInvoice.strInvoiceNumber,'</a>') AS 'Invoice / Order Number'
   //          ,tblInvoice.strPaymentMethod AS 'Payment Method'
   //          , CASE WHEN tblInvoice.blnLocked = 1 THEN '<b class=textColour>Yes</b>' ELSE '<b class=textRed>No</b>' END AS 'Final'
   //          , tblInvoice.dtInvoiceDate AS 'Invoice Date', tblLearner.strLearner AS 'Name', tblLearner.strCompany AS 'Company', tblLearner.strVatNumber AS 'VAT Number'
   //          , tblLearner.strAddress1 AS 'Address 1', tblLearner.strAddress2 AS 'Address 2', tblLearner.strAddress3 AS 'Address 3'
   //          , tblLearner.strCode AS 'Code', tblCourse.strCourse AS 'Course Name', tblCourseType.strCourseType AS 'Course Type', '-' AS 'Attended'
   //          , Count(tblLicence.LicenceID) AS 'Licences' 
   //          , Sum(tblLicence.dblAmountExcl) AS 'Amount (Excl)', ROUND(Sum(tblLicence.dblAmountIncl-tblLicence.dblAmountExcl),2) AS 'VAT', Sum(tblLicence.dblAmountIncl) AS 'Amount'
   //          , tblInvoice.TransactionID
   //          FROM tblCourseType INNER JOIN (tblInvoice INNER JOIN (tblLearner INNER JOIN (tblLicence INNER JOIN tblCourse ON tblLicence.refCourseID = tblCourse.CourseID) ON tblLearner.LearnerID = tblLicence.refLearnerID) ON tblInvoice.InvoiceID = tblLicence.refInvoiceID) ON tblCourseType.CourseTypeID = tblCourse.refCourseTypeID
   //          WHERE 1=1 AND tblInvoice.blnDeleted = 0 $Where
   //          GROUP BY tblInvoice.strInvoiceNumber, tblInvoice.strPaymentMethod, Final, tblInvoice.dtInvoiceDate, tblLearner.strLearner, tblLearner.strCompany, tblLearner.strVatNumber, tblLearner.strAddress1, tblLearner.strAddress2, tblLearner.strAddress3, tblLearner.strCode, tblCourse.strCourse, tblCourseType.strCourseType, '-', tblInvoice.TransactionID 
   //          ORDER BY tblInvoice.InvoiceID DESC, Amount DESC",0);

   // $totalAmount =
   // $totalVat =
   // $totalAmountExcl = 0;
   // foreach($rpt->Data as $key=>$data)
   // {
   //    $totalAmountExcl += $data["Amount (Excl)"];
   //    $totalVat += $data["Vat"];
   //    $totalAmount += $data["Amount"];
   //    //echo $BR."$key: ".$totalAmountExcl;
   // }

   // $idxTotal = count($rpt->Data);
   // $rpt->Data[$idxTotal]["Name"] = "<b>Total</b>";
   // $rpt->Data[$idxTotal]["Amount (Excl)"] = $totalAmountExcl;
   // $rpt->Data[$idxTotal]["Vat"] = $totalVat;
   // $rpt->Data[$idxTotal]["Amount"] = $totalAmount;

   // $rpt->Columns["Amount (Excl)"]->html->align =
   // $rpt->Columns["Vat"]->html->align =
   // $rpt->Columns["Amount"]->html->align = "right";
   // $rpt->Columns["Amount (Excl)"]->html->nowrap = 1;
   // $rpt->Columns["Vat"]->html->nowrap = 1;
   // $rpt->Columns["Amount"]->html->nowrap =1;

   //unset($rpt->Columns["intOrder"]);
   $Output = $rpt->renderTable("Invoice Report $strCaption");

?>