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

class customersreport_model extends CI_Model {

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



 function getcustomersdet($datefrom,$dateto, $customers,$receipt)
     {
      //$datefrom='2012-08-21';
      //$dateto='2012-08-07';
        $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 = 'DATENEW ';
        if (!$sord)
            $sord = 'asc';

        if (!$page)
            $page = 1;
        if (!$limit)
            $limit = 25;
        //Total Returned Rows Count
       $table_name = "RECEIPTS";
         if ($datefrom && $dateto) {
            $this->db->where("DATE(DATENEW) >=", $datefrom);
            $this->db->where("DATE(DATENEW) <=", $dateto."+1 day");
        } else if ($datefrom) {
            $this->db->where("DATE(DATENEW)", $datefrom);
        }

               $this->db->select("RECEIPTS.DATENEW,TICKETS.TICKETID,CUSTOMERS.NAME,PRODUCTS.NAME,TICKETLINES.UNITS,SUM(TICKETLINES.PRICE),CUSTOMERS.CURDEBT")->from($table_name);
               $this->db->join('TICKETS','TICKETS.ID= RECEIPTS.ID','LEFT');
               $this->db->join('TICKETLINES','TICKETS.ID=TICKETLINES.TICKET','LEFT');
               $this->db->join('PRODUCTS','PRODUCTS.ID=TICKETLINES.PRODUCT','LEFT');
               $this->db->join('CUSTOMERS','CUSTOMERS.ID=TICKETS.CUSTOMER','LEFT');

                 $this->db->where("TICKETS.CUSTOMER !=", '');
                     $this->db->group_by('TICKETS.TICKETID');

                  //    $this->db->order_by($sidx, $sord);
        $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."";

         $table_name = "RECEIPTS";

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

       if($customers)
        {
            $this->db->like('CUSTOMERS.NAME',$customers);
        }
         if($receipt)
        {
            $this->db->like('TICKETS.TICKETID',$receipt);
        }
        
// $this->db->select("SUPPLIERS.NAME AS SUPPLIER,PRODUCTS.NAME AS PRODUCT,STOCKDIARY.DATENEW,STOCKDIARY.UNITS,UNITS.NAME AS UOM,STOCKDIARY.PRICE,(STOCKDIARY.UNITS * STOCKDIARY.PRICE) AS TOTAL")->from($table_name);
                 $this->db->select("RECEIPTS.DATENEW,TICKETS.TICKETID,CUSTOMERS.NAME AS CUSTOMER,PRODUCTS.NAME AS PRODUCT,TICKETLINES.UNITS,SUM(TICKETLINES.PRICE) AS PRICE,CUSTOMERS.CURDEBT,CUSTOMERS.MAXDEBT")->from($table_name);
               $this->db->join('TICKETS','TICKETS.ID= RECEIPTS.ID','LEFT');
               $this->db->join('TICKETLINES','TICKETS.ID=TICKETLINES.TICKET','LEFT');
               $this->db->join('PRODUCTS','PRODUCTS.ID=TICKETLINES.PRODUCT','LEFT');
               $this->db->join('CUSTOMERS','CUSTOMERS.ID=TICKETS.CUSTOMER','LEFT');


                     $this->db->where("TICKETS.CUSTOMER !=", '');
                     $this->db->group_by('TICKETS.TICKETID');

                    //  $this->db->order_by($sidx, $sord);
                        // $this->db->order_by('DATE(DATENEW)');
                        //  $this->db->group_by('DATE(DATENEW)');
                            // return $this->db->get()-> result();
                              $rows = $this->db->get()->result_array();
        $i=0;
    //var_dump($data);
        foreach ($rows as $row) {
           $data['rows'][$i]['id'] = $i + 1;
           $data['rows'][$i]['cell'] = array($row['DATENEW'],$row['TICKETID'],$row['CUSTOMER'],$row['PRICE'],$row['CURDEBT'],$row['MAXDEBT']);
            $i++;
            }
            return json_encode($data);

     }




}




