<?php

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

class Nonmovingitems_model extends CI_Model {

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

  // GET NONMOVING ITEMS     HUSSAIN 02/03/2012
   function getnonmovingitems($datefrom)
{

$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 = 'id';
if (!$sord) $sord = 'asc';

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

//Total Returned Rows Count
$this->db->select('PRODUCTS.ID, PRODUCTS.NAME, SC.UNITS,UNITS.NAME AS UOM, PRODUCTS.PRICEBUY AS UNITPRICE,(SC.UNITS*PRODUCTS.PRICEBUY) AS TOTAL');
$this->db->from('PRODUCTS');
  $where = 'SC.UNITS > 0 AND SC.LOCATION = 0 AND 0 != (SELECT COUNT(*) FROM MATERIALS_UNITS WHERE MATERIALS_UNITS.MATERIAL = PRODUCTS.ID) AND 0 = (SELECT COUNT(*) FROM STOCKDIARY WHERE (STOCKDIARY.REASON = 1000 OR STOCKDIARY.REASON = -4) AND STOCKDIARY.LOCATION = 0 AND STOCKDIARY.PRODUCT = PRODUCTS.ID AND STOCKDIARY.DATENEW > "'. $datefrom.'")';
         $this->db->where($where);

$this->db->join('STOCKCURRENT AS SC', 'SC.PRODUCT = PRODUCTS.ID', 'LEFT');
$this->db->join('MATERIALS_UNITS AS MU', 'MU.MATERIAL = PRODUCTS.ID', 'LEFT');
$this->db->join('UNITS', 'UNITS.ID = MU.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."";


//Fetch data

$this->db->select('PRODUCTS.ID, PRODUCTS.NAME, SC.UNITS,UNITS.NAME AS UOM, PRODUCTS.PRICEBUY AS UNITPRICE,(SC.UNITS*PRODUCTS.PRICEBUY) AS TOTAL');

  
  $this->db->from('PRODUCTS');

   $where = 'SC.UNITS > 0 AND SC.LOCATION = 0 AND 0 != (SELECT COUNT(*) FROM MATERIALS_UNITS WHERE MATERIALS_UNITS.MATERIAL = PRODUCTS.ID) AND 0 = (SELECT COUNT(*) FROM STOCKDIARY WHERE (STOCKDIARY.REASON = 1000 OR STOCKDIARY.REASON = -4) AND STOCKDIARY.LOCATION = 0 AND STOCKDIARY.PRODUCT = PRODUCTS.ID AND STOCKDIARY.DATENEW > "'. $datefrom.'")';
         $this->db->where($where);

$this->db->join('STOCKCURRENT AS SC', 'SC.PRODUCT = PRODUCTS.ID', 'LEFT');
$this->db->join('MATERIALS_UNITS AS MU', 'MU.MATERIAL = PRODUCTS.ID', 'LEFT');
$this->db->join('UNITS', 'UNITS.ID = MU.UNIT', 'LEFT');

$this->db->order_by($sidx, $sord);
$this->db->limit($limit, $start);
$rows = $this->db->get()->result_array();

//Format data
$i=0;
foreach($rows as $row){
$data['rows'][$i]['id']=$i+1;
$data['rows'][$i]['cell']=array($row['ID'],$row['NAME'],$row['UNITS'],$row['UOM'],$row['UNITPRICE'],$row['TOTAL']);
$i++;
}
return json_encode($data);
}

  
}