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

class suppliersreport_model extends CI_Model {

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



 function getsupplierdiarydet($datefrom,$dateto, $suppliers,$material,$reason)
     {
      //$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 = "PRODUCTS";
         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("SUPPLIERS.NAME AS SUPPLIER,PRODUCTS.NAME AS PRODUCT,STOCKDIARY.DATENEW,STOCKDIARY.UNITS")->from($table_name);
               $this->db->join('STOCKDIARY','PRODUCTS.ID = STOCKDIARY.PRODUCT','LEFT');
               $this->db->join('SUPPLIERS','STOCKDIARY.SUPPLIER = SUPPLIERS.ID','LEFT');
               $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
               $this->db->join('UNITS','UNITS.ID = MATERIALS_UNITS.UNIT','LEFT');
                   

        $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 = "PRODUCTS";
           
         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($suppliers)
        {
            $this->db->like('SUPPLIERS.NAME',$suppliers);
        }
         if($material)
        {
            $this->db->like('PRODUCTS.NAME',$material);
        }
         if($reason)
        {
            $this->db->like('STOCKDIARY.REASON',$reason);
        }
// $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("SUPPLIERS.NAME AS SUPPLIER,PRODUCTS.NAME AS PRODUCT,STOCKDIARY.DATENEW,STOCKDIARY.UNITS,STOCKDIARY.PRICE,(STOCKDIARY.UNITS*STOCKDIARY.PRICE) AS TOTAL")->from($table_name);

             $this->db->join('STOCKDIARY','PRODUCTS.ID = STOCKDIARY.PRODUCT','LEFT');
             // $this->db->join('TICKETS','RECEIPTS.ID=TICKETS.ID AND TICKETS.TICKETTYPE=0 ','LEFT');
              $this->db->join('CATEGORIES','PRODUCTS.CATEGORY = CATEGORIES.ID','LEFT');
               $this->db->join('SUPPLIERS','STOCKDIARY.SUPPLIER = SUPPLIERS.ID','LEFT');
                 $this->db->join('MATERIALS_UNITS','MATERIALS_UNITS.MATERIAL = PRODUCTS.ID','LEFT');
                   $this->db->join('UNITS','UNITS.ID = MATERIALS_UNITS.UNIT','LEFT');

                 //  $where="'CATEGORIES.PARENTID'='-1'";
                   $this->db->where("CATEGORIES.PARENTID",'-1');
                   // $this->db->where("CATEGORIES.ID",'-1');
                   
                    // $this->db->group_by('PRODUCTS.ID');
                    //   $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['SUPPLIER'],$row['DATENEW'],$row['PRODUCT'],$row['UNITS'],$row['PRICE'],$row['TOTAL']);
            $i++;       
            }
            return json_encode($data);
    
     }



   
}




