
<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class salestotalregister_model extends CI_Model {

public function __construct() {
parent::__construct();
}


  

function getsalestotalregister($datefrom, $dateto) {
            $page = $this->input->post('page');
        $limit = $this->input->post('rows'); // get how many rows we want to have into the grid
        $sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
        $sord = $this->input->post('sord'); // get the direction
//Default values
        if (!$sidx)
            $sidx = 'BILLDATE';
        if (!$sord)
            $sord = 'desc';

        if (!$page)
            $page = 1;
        if (!$limit)
            $limit = 25;

 $table_name = "RECEIPTS";
 if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select("DISTINCT DATE(RECEIPTS.DATENEW) AS BillDate")->from($table_name);
              // $this->db->join('TAXLINES','TAXLINES.RECEIPT=RECEIPTS.ID ','LEFT' );

//               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
//               $this->db->order_by("DATE(RECEIPTS.DATENEW)");
 $count = $this->db->count_all_results();

//Total Pages
        if ($count > 0 && $limit > 0) {
            $total_pages = ceil($count / $limit);
        } else {
            $total_pages = 0;
        }

//Check requested page
        if ($page > $total_pages)
            $page = $total_pages;

//Set start record number
        $start = $limit * $page - $limit;

//Format data for jqGrid output
        $data['page'] = "" . $page . "";
        $data['total'] = "" . $total_pages . "";
        $data['records'] = "" . $count . "";
         

          if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

        
           $this->db->select("DATE(RECEIPTS.DATENEW) AS BillDate,MIN(TICKETS.TICKETID) AS ReceiptNo,MAX(TICKETS.TICKETID)  AS ReceiptNoTo, SUM(TAXLINES.BASE) AS TOTAL1, SUM(TAXLINES.AMOUNT)  AS TAX1 ")->from($table_name);
          

               $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');
               $this->db->join('TICKETLINES','TICKETS.ID=TICKETLINES.TICKET','LEFT');
               $this->db->join('TAXLINES','TAXLINES.RECEIPT=RECEIPTS.ID','LEFT');
//    $this->db->where("TICKETS.TICKETTYPE=0");

              $this->db->group_by("DATE(RECEIPTS.DATENEW)");
              $this->db->order_by("DATE(RECEIPTS.DATENEW)");

         $rows = $this->db->get()->result_array();


   //************Second query FOR TOTAL SALE*********

        

          if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select("DATE(RECEIPTS.DATENEW) AS BillDate,  SUM(TAXLINES.BASE)  AS Total  ")->from($table_name);
               $this->db->join('TAXLINES','TAXLINES.RECEIPT=RECEIPTS.ID ','LEFT' );
               $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');
               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
               $this->db->order_by("DATE(RECEIPTS.DATENEW)");

            $rows1 = $this->db->get()->result_array();

  //************Second query FOR NON TAXABLE SALE*********

         

          if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select("DATE(RECEIPTS.DATENEW) AS BillDate,  SUM(TAXLINES.BASE)  AS NonTaxable  ")->from($table_name);
               $this->db->join('TAXLINES','TAXLINES.RECEIPT=RECEIPTS.ID AND TAXLINES.AMOUNT=0','LEFT' );
               $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');
               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
               $this->db->order_by("DATE(RECEIPTS.DATENEW)");

            $rows2 = $this->db->get()->result_array();


  //************3rd query FOR TAXABLE SALE*********

         

          if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select("DATE(RECEIPTS.DATENEW) AS BillDate,  SUM(TAXLINES.BASE)  AS Taxable,SUM(TAXLINES.AMOUNT)  AS Taxonly  ")->from($table_name);
               $this->db->join('TAXLINES','TAXLINES.RECEIPT=RECEIPTS.ID AND TAXLINES.AMOUNT>0','LEFT' );
               $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');
               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
               $this->db->order_by("DATE(RECEIPTS.DATENEW)");

           $rows3 = $this->db->get()->result_array();

//************4TH query FOR TAX*********

         

          if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select("DATE(RECEIPTS.DATENEW) AS BillDate,  SUM(TAXLINES.AMOUNT)  AS Tax  ")->from($table_name);
               $this->db->join('TAXLINES','TAXLINES.RECEIPT=RECEIPTS.ID ','LEFT' );
               $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');
               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
               $this->db->order_by("DATE(RECEIPTS.DATENEW)");

            $rows4 = $this->db->get()->result_array();

           

// DEBIT SALE 

               if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select(" DATE(RECEIPTS.DATENEW) AS BillDate, SUM(PAYMENTS.TOTAL)  AS debtTotal  ")->from($table_name);
                $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');
               $this->db->join('PAYMENTS','PAYMENTS.RECEIPT = RECEIPTS.ID AND PAYMENTS.PAYMENT="debt"','LEFT');

               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
               $this->db->order_by("DATE(RECEIPTS.DATENEW)");

            $rows5 = $this->db->get()->result_array();
////--------------------------------------------------------
            // CASH SALE

               if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select(" DATE(RECEIPTS.DATENEW) AS BillDate, SUM(PAYMENTS.TOTAL)  AS cashTotal  ")->from($table_name);

               $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');
               $this->db->join('PAYMENTS','PAYMENTS.RECEIPT = RECEIPTS.ID AND PAYMENTS.PAYMENT="cash"','LEFT');

               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
               $this->db->order_by("DATE(RECEIPTS.DATENEW)");

            $rows6 = $this->db->get()->result_array();
////--------------------------------------------------------

//************7th query FOR  5%TAX  SALE*********



          if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select("DATE(RECEIPTS.DATENEW) AS BillDate,  SUM(TAXLINES.BASE)  AS 5Taxsale ,SUM(TAXLINES.AMOUNT)  AS fiveTax, SUM(TAXLINES.AMOUNT)/100  AS fiveVat ")->from($table_name);
//               $this->db->join('TAXLINES','TAXLINES.RECEIPT=RECEIPTS.ID AND TAXLINES.AMOUNT>0','LEFT' );
//
//               $this->db->join('TAXES','TAXLINES.TAXID=TAXES.ID AND TAXES.RATE="0.04"','LEFT' );
               $this->db->join('TAXLINES  JOIN TAXES  ON TAXES.ID = TAXLINES.TAXID ',' TAXLINES.RECEIPT = RECEIPTS.ID  AND   TAXES.RATE = "0.050"','LEFT');

               $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');

               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
               $this->db->order_by("DATE(RECEIPTS.DATENEW)");

           $rows7 = $this->db->get()->result_array();

           //************8th query FOR  13.5%TAX  SALE*********



          if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select("DATE(RECEIPTS.DATENEW) AS BillDate,  SUM(TAXLINES.BASE)  AS thirtyTaxsale ,SUM(TAXLINES.AMOUNT)  AS thirtyTax ,SUM(TAXLINES.AMOUNT)/100  AS thiryVat")->from($table_name);
               $this->db->join('TAXLINES  JOIN TAXES  ON TAXES.ID = TAXLINES.TAXID ',' TAXLINES.RECEIPT = RECEIPTS.ID  AND   TAXES.RATE = "0.135"','LEFT');

               $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');

               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
               $this->db->order_by("DATE(RECEIPTS.DATENEW)");

           $rows8 = $this->db->get()->result_array();
              //************8th query FOR  20%TAX  SALE*********



          if ($datefrom && $dateto) {
            $this->db->where("DATE(RECEIPTS.DATENEW) >=", $datefrom);
            $this->db->where("DATE(RECEIPTS.DATENEW) <=", $dateto);
        } else if ($datefrom) {
            $this->db->where("DATE(RECEIPTS.DATENEW)=", $datefrom);
        }

               $this->db->select("DATE(RECEIPTS.DATENEW) AS BillDate,  SUM(TAXLINES.BASE)  AS TwentyTaxsale ,SUM(TAXLINES.AMOUNT)  AS TwentyTax,SUM(TAXLINES.AMOUNT)/100  AS twentyVat ")->from($table_name);
               $this->db->join('TAXLINES  JOIN TAXES  ON TAXES.ID = TAXLINES.TAXID ',' TAXLINES.RECEIPT = RECEIPTS.ID  AND   TAXES.RATE = "0.20"','LEFT');

               $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');

               $this->db->group_by("DATE(RECEIPTS.DATENEW)");
               $this->db->order_by("DATE(RECEIPTS.DATENEW)");

           $rows9 = $this->db->get()->result_array();
//echo $this->db->last_query();


        
        $i = 0;
        foreach ($rows as $row) {
            $data['rows'][$i]['id'] = $i + 1;
            $data['rows'][$i]['cell'] = array($row['BillDate'], $row['ReceiptNo'],  $row['ReceiptNoTo'],$rows1[$i]['Total']+$rows4[$i]['Tax'],$rows2[$i]['NonTaxable'],$rows3[$i]['Taxable'],$rows7[$i]['5Taxsale'],$rows8[$i]['thirtyTaxsale'],$rows9[$i]['TwentyTaxsale'],$rows7[$i]['fiveTax'],$rows8[$i]['thirtyTax'],$rows9[$i]['TwentyTax'],$rows4[$i]['Tax'],$rows5[$i]['debtTotal'],$rows6[$i]['cashTotal']);
            $i++;
        }
        return json_encode($data);

    }

    
}





////
//SELECT taxcustcategories.NAME AS CUSTCATEGORY, CUSTOMERS.NAME AS CUSTOMER ,CUSTOMERS.MAXDEBT AS MAXIMUM ,CUSTOMERS.CURDEBT FROM (`TICKETS`)
//LEFT JOIN `RECEIPTS` ON `RECEIPTS`.`ID`=`TICKETS`.`ID`
//LEFT JOIN `CUSTOMERS` ON `CUSTOMERS`.`ID`=`TICKETS`.`CUSTOMER`
//LEFT JOIN `taxcustcategories` ON `taxcustcategories`.`ID`=`CUSTOMERS`.`taxcategory`
//where `TICKETS`.`CUSTOMER` !='null'
//*****************************
//SELECT DATE(RECEIPTS.DATENEW) AS BillDate, SUM(PAYMENTS.TOTAL) AS cashTotal FROM (`RECEIPTS`)
//LEFT JOIN `PAYMENTS` ON `PAYMENTS`.`RECEIPT` = `RECEIPTS`.`ID`
//LEFT JOIN `TICKETS` ON `RECEIPTS`.`ID`=`TICKETS`.`ID`
//LEFT JOIN `TICKETLINES` ON `TICKETLINES`.`TICKET`=`TICKETS`.`ID`
//
//LEFT JOIN `PRODUCTS` ON `PRODUCTS`.`ID`=`TICKETLINES`.`PRODUCT`
//LEFT JOIN `TAXES` ON `TAXES`.`CATEGORY`=`PRODUCTS`.`TAXCAT`
//
//GROUP BY DATE(RECEIPTS.DATENEW)
//*********************************

//SELECT DATE(RECEIPTS.DATENEW) AS BillDate, SUM(PAYMENTS.TOTAL) AS cashTotal FROM (`RECEIPTS`)
//LEFT JOIN `TAXLINES` ON `TAXLINES`.`RECEIPT`=`RECEIPTS`.`ID`
//LEFT JOIN `TICKETS` ON `RECEIPTS`.`ID`=`TICKETS`.`ID` AND TICKETS.TICKETTYPE=0
//LEFT JOIN `PAYMENTS` ON `PAYMENTS`.`RECEIPT` = `TAXLINES`.`RECEIPT` AND PAYMENTS.PAYMENT='debt'
//
//GROUP BY DATE(RECEIPTS.DATENEW)
//******************************

//SELECT DATE(RECEIPTS.DATENEW) AS BillDate, SUM(PAYMENTS.TOTAL) AS cashTotal FROM (`RECEIPTS`)
//LEFT JOIN `PAYMENTS` ON `PAYMENTS`.`RECEIPT` = `RECEIPTS`.`ID`
//LEFT JOIN `TICKETS` ON `RECEIPTS`.`ID`=`TICKETS`.`ID`
//LEFT JOIN `TICKETLINES` ON `TICKETLINES`.`TICKET`=`TICKETS`.`ID`
//
//LEFT JOIN `PRODUCTS` ON `PRODUCTS`.`ID`=`TICKETLINES`.`PRODUCT`
//LEFT JOIN `TAXES` ON `TAXES`.`CATEGORY`=`PRODUCTS`.`TAXCAT`AND TAXES.RATE=0
//
//GROUP BY DATE(RECEIPTS.DATENEW)