<?php

   include_once("include/basiclist.inc");
/* *STEPS:
   *
   * //write sql + where clause
   * //set UL class
   * //overrite data types
   * //[set totals]
   * //load the rest of the data types
   * //set del messages
   * //display()
   * 20110804 - added dirived query over the union, changed union to union all - pj
   * 20160622 - Updated strDate to dtInvoiceDate - Christiaan
   **/

   //print_rr($this->rptVars);
   switch($this->rptVars[frClientType])
   {
      case "Clients":
         $Where .= " AND ClientID > 1";
         break;
      case "Free":
         $Where .= " AND ClientID = 1";
         break;
      case "Private":
         $Where .= " AND ClientID = -1";
         break;
   }
   if($this->rptVars[frClientID] > 0){
      $Where .= " AND ClientID = ". $this->rptVars[frClientID];
   }
   if($this->rptVars[frPracticeID] > 0){
      $Where .= " AND tblLicence.refPracticeID = ". $this->rptVars[frPracticeID];
   }
   if($this->rptVars[frProductID] > -1){
      $Where .= " AND ProductID = ". $this->rptVars[frProductID];
   }
   if($this->rptVars[frInvoice] != "")
      $Where .= " AND InvoiceID LIKE (". $xdb->qs("%". $this->rptVars[frInvoice] ."%") .")";
   if($this->rptVars[frDateIgnore] == "")
   {
      $Where .= " AND (strDate >= '". MakeDBDate($this->rptVars[frDateStart]) ."' AND strDate <='". MakeDBDate($this->rptVars[frDateEnd]) ."')";
   }

//write sql + where clause // USE comma space (, ) ONLY BETWEEN THE COLUMNS YOU SELECT
   //2 queries: 1rst is online, 2nd is offline transactions
   //20110804 - added dirived query over the union, changed union to union all - pj
   //20160622 - Updated strDate to dtInvoiceDate - Christiaan
   $sql = "SELECT `Invoice Number` as 'Invoice Number', TID, `Date`, `Client/Practice`, `Customer`, `Payment Method`, `Account Code`, `Product Code`, `Product`, SUM(`Quantity`) AS Quantity
            , SUM(`Unit Price`) AS 'Unit Price', SUM(`Sub Total`) AS 'Sub Total'
            , SUM(`Discount`) AS Discount, SUM(`VAT`) AS VAT, SUM(`Total`) AS Total
         FROM (

            SELECT RIGHT(concat('000000000',InvoiceID),10) AS 'Invoice Number', tblInvoice.refTransactionID AS 'TID', tblInvoice.dtInvoiceDate AS 'Date', strPractice AS 'Client/Practice', tblPractice.strContact AS 'Customer', tblInvoice.strPaymentMethod as 'Payment Method', left(tblProduct.strProductCode,7) AS 'Account Code', right(tblProduct.strProductCode,4) AS 'Product Code', tblProduct.strShortCode AS Product, Sum(tblLicence.intQuantum) AS Quantity, tblLicence.dblAmountExcl / tblLicence.intQuantum AS 'Unit Price', tblLicence.dblAmountExcl as 'Sub Total', ROUND(Sum((dblPrice * tblLicence.intQuantum) - tblLicence.dblAmountExcl),2) AS Discount, Sum(tblLicence.dblAmountIncl-tblLicence.dblAmountExcl) AS 'VAT', ROUND(Sum(tblLicence.dblAmountIncl),0) AS Total
            FROM (tblClient INNER JOIN ((tblLicence INNER JOIN tblInvoice ON tblLicence.refInvoiceID = tblInvoice.InvoiceID) INNER JOIN tblPractice ON tblLicence.refPracticeID = tblPractice.PracticeID) ON tblClient.ClientID = tblInvoice.refClientID) INNER JOIN tblProduct ON tblLicence.refProductID = tblProduct.ProductID
            WHERE tblInvoice.refClientID = -1 $Where
            GROUP BY tblInvoice.InvoiceID, tblInvoice.dtInvoiceDate, tblClient.strClient, tblPractice.strPractice, tblPractice.strContact, tblProduct.strProductCode, tblProduct.strProduct, tblProduct.dblPrice, tblInvoice.blnLocked
            HAVING tblInvoice.blnLocked = 1

            UNION ALL

            SELECT RIGHT(concat('000000000',InvoiceID),10) AS 'Invoice Number', 'n/a' AS 'TID', tblInvoice.dtInvoiceDate AS 'Date', strClient AS 'Client/Practice', '' AS 'Customer'
            , tblInvoice.strPaymentMethod as 'Payment Method', left(tblProduct.strProductCode,7) AS 'Account Code', right(tblProduct.strProductCode,4) AS 'Product Code'
            , tblProduct.strShortCode AS Product, Sum(tblLicence.intQuantum) AS Quantity, tblLicence.dblAmountExcl / tblLicence.intQuantum AS 'Unit Price', tblLicence.dblAmountExcl as 'Sub Total'
            , ROUND(Sum((dblPrice * tblLicence.intQuantum) - tblLicence.dblAmountExcl),2) AS Discount, Sum(tblLicence.dblAmountIncl-tblLicence.dblAmountExcl) AS 'VAT'
            , Sum(tblLicence.dblAmountIncl) AS Total
            FROM (tblClient INNER JOIN (tblLicence INNER JOIN tblInvoice ON tblLicence.refInvoiceID = tblInvoice.InvoiceID) ON tblClient.ClientID = tblInvoice.refClientID) INNER JOIN tblProduct ON tblLicence.refProductID = tblProduct.ProductID
            WHERE tblInvoice.refClientID <> -1 $Where
            GROUP BY tblInvoice.InvoiceID, tblInvoice.dtInvoiceDate, tblClient.strClient, tblProduct.strProductCode, tblProduct.strProduct, tblProduct.dblPrice, tblLicence.refPracticeID, tblInvoice.blnLocked
            HAVING tblInvoice.blnLocked = 1

         ) as tblInvoice
         GROUP BY 'Invoice Number', TID, `Date`, `Client/Practice`, `Customer`, `Payment Method`, `Account Code`, `Product Code`, `Product`
         ORDER BY 'Date', 'Invoice Number'";
            //


//set UL class
   $ulLedger = new UberList("Ledger Report - ". date("j F Y"),"", $sql, "rpt",0);

//overrite data types

   $ulLedger->Columns["Payment Method"]->Type =
   $ulLedger->Columns["Product"]->Type =
   $ulLedger->Columns["Account Code"]->Type =
   $ulLedger->Columns["Product Code"]->Type =
   $ulLedger->Columns["Customer"]->Type =
   $ulLedger->Columns["Client/Practice"]->Type = "varchar";
   $ulLedger->Columns[Date]->Type = "Date";

   $ulLedger->Columns[Quantity]->Type = "int";
   $ulLedger->Columns["Unit Price"]->Type = "double";
   $ulLedger->Columns["Sub Total"]->Type = "double";
   $ulLedger->Columns[Discount]->Type = "double";
   $ulLedger->Columns[VAT]->Type = "double";
   $ulLedger->Columns[Total]->Type = "double";

//[set totals]
   $ulLedger->TotalAllNumerics();

   $ulLedger->Columns["Invoice Number"]->Type = "int";

//load the rest of the data types
   $ulLedger->LoadDataTypes();

   //$ulLedger->Columns["Account/Product Code"]->html->align = "center";



   //print_rr($ulLedger->Columns);

   //incoming vars sortNew=tblClient.strClient&sortDir=DESC&sortCurrent=
//set del messages
   $ulLedger->DeleteMessage = "";
   $ulLedger->DeleteColumn = "";

//display()
   //print_rr($ulLedger);
   //$ulLedger->Debug = 1;
   echo $ulLedger->Display($AllowNew=0, $AllowSort=0, $ShowPK = 0,$AllowEdit=0, $AllowDelete=0, $AllowPaging=0, $AllowExport=1);

?>