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

class Account_model extends CI_Model{
        
    public function __construct()
    {
        parent::__construct();
    }
    
    function checklogin()
    {
		$username = $this->input->post("username");
		$userpass = $this->input->post("userpass");
		if($username!="" && $userpass!="")
		{
			$this->db->select("*");
			$this->db->where("fld_username", $username);
			$this->db->where("fld_userpass", md5($userpass));
			$this->db->where("fld_status", "active");
			$this->db->group_start();
			$this->db->where("fld_type", "admin");
			$this->db->or_where("fld_type", "staff");
			$this->db->group_end();
			$this->db->where('fld_del', '0');
			$result = $this->db->get("tbl_users");
			//echo $this->db->last_query();
			if($result->num_rows()>0)
			{
				$row = $result->row();
	
				$this->db->select("fld_name,fld_company");
				$this->db->where("fld_id", $row->fld_clientid);
				$this->db->where('fld_del', '0');
				$result2 = $this->db->get("tbl_clients");				
				//echo $this->db->last_query();
				if($result2->num_rows()>0)
				{
					$userrights = $row->fld_access;
					if($row->fld_type=="admin") $userrights = "all";
					$userright2show = explode(",", $userrights);
					$row2 = $result2->row();						
					$data = array(
						'sessCompanyname' => $company,
						'sessCLIENTID' => $row->fld_clientid,					
						'sessID' => $row->fld_id,
						'sessCompanyname' => $row2->fld_company,
						'sessName' => $row->fld_name,
						'sessUserName' => $row->fld_username,
						'sessType' => 'admin',
						'sessEmail' => $row->fld_email,
						'sessRights' => $userright2show,
						'sessLoggedInStatus' => true
						);
					//predata($data); exit;
					$this->session->set_userdata('clinetLoggedIn', $data);
					return true;
				}
				else
				{
					return false;
				}
			}
			else
			{
				return false;
			}			
		}
		
	}
	
	function updatedata($table, $data, $rsrcid)
	{
		$this->db->where('fld_id',$rsrcid);
		$this->db->update($table, $data);
	}

	function manageuserpass($data, $currpass)
	{
		$returnvalue = "failed";
		$userid = $this->lguserinfo["sessID"];
		$this->db->select("*");
		$this->db->where("fld_id", $userid);
		$this->db->where('fld_del', '0');
		$results = $this->db->get("tbl_users");
		if($results->num_rows()>0) // update if found
		{
			$row = $results->row();
			if($row->fld_userpass==md5($currpass))
			{
				$this->db->where('fld_id',$userid);
				$this->db->update("tbl_users", $data);
				$returnvalue = "success";
			}
			else
			{
				$returnvalue = "notmatched";
			}
		}
		return $returnvalue;
	}

	function listalloutlets($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$zoneid = decodeValue($this->input->get("zoneid"));
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("OI.*,ZI.fld_name as zonename");
		}
		$this->db->from("tbl_outlets OI");
		$this->db->join("tbl_zone ZI", "OI.fld_zoneid=ZI.fld_id",'left');

		if($zoneid>0)
		{
			$this->db->where('OI.fld_zoneid',$zoneid);
		}
		$this->db->where('OI.fld_clientid',$this->clientid);		
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("OI.fld_invoiceprefix", $keyword);
			$this->db->or_like("OI.fld_address", $keyword);
			$this->db->or_like("OI.fld_managername", $keyword);
			$this->db->or_like("OI.fld_manageremail", $keyword);
			$this->db->or_like("OI.fld_managermobile", $keyword);
			
			$this->db->group_end();
		}

		if($listtype != "count")
		{
			$this->db->order_by('OI.fld_name', 'asc');		
			if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit'],$params['start']);
			}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit']);
			}
		}
		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}
	
	function listoutletsdata($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$zoneid = decodeValue($this->input->get("zoneid"));
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("OI.*,ZI.fld_name as zonename");
		}
		$this->db->from("tbl_outlets OI");
		$this->db->join("tbl_zone ZI", "OI.fld_zoneid=ZI.fld_id",'left');

		if($zoneid>0)
		{
			$this->db->where('OI.fld_zoneid',$zoneid);
		}
		$this->db->where('OI.fld_clientid',$this->clientid);
		$this->db->where('OI.fld_del', '0');
		$this->db->where('OI.fld_show', 'yes');
		
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("OI.fld_invoiceprefix", $keyword);
			$this->db->or_like("OI.fld_address", $keyword);
			$this->db->or_like("OI.fld_managername", $keyword);
			$this->db->or_like("OI.fld_manageremail", $keyword);
			$this->db->or_like("OI.fld_managermobile", $keyword);
			
			$this->db->group_end();
		}

		if($listtype != "count")
		{
			$this->db->order_by('OI.fld_name', 'asc');		
			if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit'],$params['start']);
			}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit']);
			}
		}
		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}
	function liststockdata($listtype, $params,$stocktype)
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");		
		$vendorid = $this->input->get("vendorid");
		$warehouseid = $this->input->get("warehouseid");
		$outletid = $this->input->get("outletid");
		$clientid = $this->input->get("clientid");
		$fromdt = $this->input->get("fromdate");
		$todt = $this->input->get("todate");
		if($fromdt!="")
		{
			 $fromdate = convertdateFormat($fromdt,'Y-m-d');
		}
		if($todt!="")
		{
			$todate = convertdateFormat($todt,'Y-m-d');
		}

		if($fromdt=="" && $todt=="")
		{
			$fromdate = date('Y-m-d');
			$todate = date('Y-m-d');
		}
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			if($stocktype=='stockout')
			{
				$this->db->select("TI.*,WI.fld_name as warehousename,OI.fld_name as ouletname");
			}
			elseif($stocktype=='o2o')
			{
				$this->db->select("TI.*, OI.fld_name as ouletname, OI02.fld_name as fromouletname");
			}
			elseif($stocktype=='o2w')
			{
				$this->db->select("TI.*, OI.fld_name as ouletname, WI.fld_name as warehousename");
			}
			elseif($stocktype=='w2w')
			{
				$this->db->select("TI.*, WI.fld_name as warehousename, WI02.fld_name as towarehousename");
			}
			elseif($stocktype=='wsale')
			{
				$this->db->select("TI.*, WI.fld_name as warehousename, BCI.fld_name as clientname");
			}
			else
			{
				$this->db->select("TI.*,VI.fld_name as vendorname,WI.fld_name as warehousename");
			}			
		}

		$this->db->from("tbl_transaction TI");		
		if($stocktype=='stockout')
		{
			$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
			$this->db->join("tbl_outlets OI", "OI.fld_id=TI.fld_outletid",'left');
		}
		elseif($stocktype=='o2o')
		{			
			$this->db->join("tbl_outlets OI", "TI.fld_outletid=OI.fld_id");
			$this->db->join("tbl_outlets OI02", "TI.fld_fromoutletid=OI02.fld_id");
		}
		elseif($stocktype=='o2w')
		{			
			$this->db->join("tbl_outlets OI", "TI.fld_fromoutletid=OI.fld_id");
			$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
		}
		elseif($stocktype=='w2w')
		{
			$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
			$this->db->join("tbl_warehouse WI02", "TI.fld_towarehouseid=WI02.fld_id", "left");
		}
		elseif($stocktype=='wsale')
		{
			$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
			$this->db->join("tbl_saleclients BCI", "TI.fld_vendorid=BCI.fld_id", "left");
		}
		else
		{
			$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
			$this->db->join("tbl_vendors VI", "TI.fld_vendorid=VI.fld_id");
		}
		$this->db->where('TI.fld_type',$stocktype);

		if($vendorid!="")
		{
			$vendorid = decodeValue($vendorid);
			$this->db->where('TI.fld_vendorid',$vendorid);
		}
		if($clientid!="")
		{
			$this->db->where('TI.fld_vendorid',$clientid);
		}
		if($warehouseid>0)
		{
			if($stocktype=='w2w')
			{
				$this->db->group_start();
				$this->db->where('TI.fld_warehouseid',$warehouseid);
				$this->db->or_where('TI.fld_towarehouseid',$warehouseid);
				$this->db->group_end();
			}
			elseif($stocktype=='o2w')
			{
				$this->db->where('TI.fld_warehouseid',$warehouseid);
			}
			else
			{
				$this->db->where('TI.fld_warehouseid',$warehouseid);
			}
		}
		if($outletid>0)
		{
			if($stocktype=='o2o')
			{
				$this->db->group_start();
				$this->db->where('TI.fld_outletid',$outletid);
				$this->db->or_where('TI.fld_fromoutletid',$outletid);
				$this->db->group_end();
			}
			elseif($stocktype=='o2w')
			{
				$this->db->where('TI.fld_fromoutletid',$outletid);
			}
			else
			{
				$this->db->where('TI.fld_outletid',$outletid);
			}
		}
		$this->db->where("TI.fld_clientid", $this->clientid);
		$this->db->where("TI.fld_del", 0);
		$this->db->where("(TI.fld_refdate >= '".$fromdate."' and TI.fld_refdate <= '".$todate."')");

		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("TI.fld_refno", $keyword);
			$this->db->or_like("TI.fld_refdate", $keyword);
			$this->db->or_like("TI.fld_amount", $keyword);
			$this->db->group_end();
		}
		if($listtype != "count")
		{
			$this->db->order_by('TI.fld_id', 'desc');		
			if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit'],$params['start']);
			}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit']);
			}
		}
		$results = $this->db->get();
		//echo "<br>" . $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}

	function getitemstockwarehousewise($params)
	{
		$warehouseid = $params["warehouseid"];
		$minstockvalue = $params["minstockvalue"];
		$bottlesincase = $params["bottlesincase"];
		$productid = $params["productid"];

		$arr = array();
		$prodid = $productid;
		$arr[$prodid] = array(
			"id" => $prodid,
			"incoming" => 0,
			"outgoing" => 0,
			"instock" => 0
		);	
		if($arr[$prodid])
		{
			### stock incoming via purchase or outlet
			$this->db->select("SUM(TII2.fld_qty) as wincomingqty");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("(TI2.fld_type='stockin' or TI2.fld_type='o2w')");
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			$this->db->where("TII2.fld_productid", $prodid);
			$this->db->where('TI2.fld_warehouseid',$warehouseid);
			$query2 = $this->db->get();
			$row2 = $query2->row();
			$incomingqty = $row2->wincomingqty;
			
			### stock incoming from other warehouse
			$this->db->select("SUM(TII2.fld_qty) as wincomingqty");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_type", "w2w");
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			$this->db->where("TII2.fld_productid",$prodid);
			$this->db->where("TI2.fld_towarehouseid",$warehouseid);
			$query02 = $this->db->get();
			$row02 = $query02->row();
			$wincomingqty = $row02->wincomingqty;

			### stock from warehouse outgoing
			$this->db->select("SUM(TII2.fld_qty) as boutgoing");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->group_start();
			$this->db->where("TI2.fld_type", "stockout");
			$this->db->or_where("TI2.fld_type", "wsale");
			$this->db->or_where("TI2.fld_type", "w2w");
			$this->db->group_end();
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			$this->db->where("TII2.fld_productid", $prodid);
			$this->db->where('TI2.fld_warehouseid',$warehouseid);
			$query3 = $this->db->get();
			$row3 = $query3->row();
			$boutgoing = $row3->boutgoing;
			
			$arr[$prodid]["incoming"] = $incomingqty + $wincomingqty;
			$arr[$prodid]["outgoing"] = $boutgoing;
			$arr[$prodid]["instock"] = $incomingqty + $boutgoing;
		}
		return $arr;
	}
	
	function getitemstockoutletwise($params)
	{
		$outletid = $params["outletid"];
		$minstockvalue = $params["minstockvalue"];
		$bottlesincase = $params["bottlesincase"];
		$productid = $params["productid"];

		$arr = array();
		$prodid = $productid;
		$arr[$prodid] = array(
			"id" => $prodid,
			"incoming" => 0,
			"outgoing" => 0,
			"instock" => 0
		);	
		if($arr[$prodid])
		{
			### stock from warehouse incoming
			$this->db->select("SUM(TII2.fld_qty) as wincomingqty");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("(TI2.fld_type='stockout')");
			$this->db->where("TI2.fld_del", "0");
			$this->db->where("TII2.fld_productid", $prodid);
			$this->db->where('TI2.fld_clientid',$this->clientid);
			$this->db->where('TI2.fld_outletid',$outletid);
			$query2 = $this->db->get();
			$wincomingqty = $query2->row()->wincomingqty;
			
			### stock out from billing outgoing
			$this->db->select("SUM(TII5.fld_qty) as boutgoing");
			$this->db->from("tbl_billinginfo TII5");
			$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
			$this->db->where("TI5.fld_status!=",'cancelled');
			$this->db->where('TI5.fld_clientid',$this->clientid);
			$this->db->where("TI5.fld_del","0");
			$this->db->where("TII5.fld_productid", $prodid);
			$this->db->where('TI5.fld_outletid',$outletid);
			$query5 = $this->db->get();
			$row5 = $query5->row();
			$boutgoing = $row5->boutgoing;
			
			### gift stock from billing outgoing
			$this->db->select("SUM(BI.fld_gqty) as goutgoing");
			$this->db->from("tbl_billing BI");
			$this->db->where("BI.fld_status!='cancelled'");
			$this->db->where("BI.fld_del","0");
			$this->db->where('BI.fld_gprodid',$prodid);
			$this->db->where('BI.fld_outletid',$outletid);
			$this->db->where('BI.fld_clientid',$this->clientid);
			$query6 = $this->db->get();
			$row6 = $query6->row();
			$goutgoing = $row6->goutgoing;
					
			### stock from outlet incoming
			$this->db->select("SUM(TII3.fld_qty) as oincomingqty");
			$this->db->from("tbl_transactioninfo TII3");
			$this->db->join("tbl_transaction TI3", "TII3.fld_transactionid=TI3.fld_id");
			$this->db->where("(TI3.fld_status='completed' or TI3.fld_status='delivered')");
			$this->db->where("(TI3.fld_type='o2o')");
			$this->db->where("TII3.fld_productid", $prodid);
			$this->db->where("TI3.fld_del", "0");
			$this->db->where('TI3.fld_clientid',$this->clientid);
			$this->db->where('TI3.fld_outletid',$outletid);
			$query3 = $this->db->get();
			$row3 = $query3->row();
			$oincomingqty = $row3->oincomingqty;
			
			### stock from outlet outgoing
			$this->db->select("SUM(TII4.fld_qty) as ooutgoingqty");
			$this->db->from("tbl_transactioninfo TII4");
			$this->db->join("tbl_transaction TI4", "TII4.fld_transactionid=TI4.fld_id");
			$this->db->where("(TI4.fld_status='completed' or TI4.fld_status='delivered')");
			$this->db->where("(TI4.fld_type='o2o' or TI4.fld_type='o2w')");
			$this->db->where("TII4.fld_productid", $prodid);
			$this->db->where("TI4.fld_del", "0");
			$this->db->where('TI4.fld_clientid',$this->clientid);
			$this->db->where('TI4.fld_fromoutletid',$outletid);
			$query4 = $this->db->get();
			$row4 = $query4->row();
			$ooutgoingqty = $row4->ooutgoingqty;
			
			$totalincoming = $wincomingqty + $oincomingqty;
			$totaloutgoing = $boutgoing + $goutgoing + $ooutgoingqty;
			$instockinbottles = $totalincoming - $totaloutgoing;
			
			$arr[$prodid]["incoming"] = $totalincoming;
			$arr[$prodid]["outgoing"] = $totaloutgoing;
			$arr[$prodid]["instock"] = $instockinbottles;
		}
		return $arr;
	}
	
	function getitemstock($params)
	{
		$arritemstock = array();
		$productid = 0;
		if($params["productid"]) $productid = decodeValue($params["productid"]);
		if($productid>0)
		{
			$this->db->select("fld_name,fld_minstockvalue, fld_noofbottlesincase");
			$this->db->where("fld_id", $productid);
			$result = $this->db->get("tbl_products");
			if($result->num_rows()>0)
			{
				$row = $result->row();
				$name = $row->fld_name;
				$minstockvalue = $row->fld_minstockvalue;
				$bottlesincase = $row->fld_noofbottlesincase;
				
				$arrparams = array(
					"productid" => $productid,
					"minstockvalue" => $minstockvalue,
					"bottlesincase" => $bottlesincase
				);
				
				$arrwarehouseincomingviapurchasenoutlet = array();
				$arrwarehouseincomingfromotherwarehouse = array();
				$arrwarehouseoutgoing = array();
				
				$prodid = $productid;
				
				## get warehouse stock
				### stock incoming via purchase or outlet
				$this->db->select("TI2.fld_warehouseid, SUM(TII2.fld_qty) as wincomingqty");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
				$this->db->where("TI2.fld_status", "completed");
				$this->db->group_start();
				$this->db->where("TI2.fld_type", "stockin");
				$this->db->or_where("TI2.fld_type", "o2w");
				$this->db->group_end();
				$this->db->where("TI2.fld_clientid",$this->clientid);
				$this->db->where("TI2.fld_del","0");
				$this->db->where("TII2.fld_productid", $prodid);
				$this->db->group_by("TI2.fld_warehouseid");
				$query2 = $this->db->get();
				//echo "<br>" . $this->db->last_query() . " => " . $query2->num_rows();
				if($query2->num_rows()>0)
				{
					foreach($query2->result() as $row2)
					{
						$arrwarehouseincomingviapurchasenoutlet[$row2->fld_warehouseid] = $row2->wincomingqty;
					}
				}
				//predata($arrwarehouseincomingviapurchasenoutlet);
				
				### stock incoming from other warehouse
				$this->db->select("TI2.fld_towarehouseid, SUM(TII2.fld_qty) as wincomingqty");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
				$this->db->where("TI2.fld_status", "completed");
				$this->db->where("TI2.fld_type", "w2w");
				$this->db->where("TI2.fld_clientid",$this->clientid);
				$this->db->where("TI2.fld_del","0");
				$this->db->where("TII2.fld_productid",$prodid);
				$this->db->group_by("TI2.fld_towarehouseid");
				$query02 = $this->db->get();
				//echo "<br>" . $this->db->last_query() . " => " . $query02->num_rows();
				if($query02->num_rows()>0)
				{
					foreach($query02->result() as $row02)
					{
						$arrwarehouseincomingfromotherwarehouse[$row02->fld_towarehouseid] = $row02->wincomingqty;
					}
				}
				//predata($arrwarehouseincomingfromotherwarehouse);

				### stock from warehouse outgoing
				$this->db->select("TI2.fld_warehouseid, SUM(TII2.fld_qty) as boutgoing");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
				$this->db->group_start();
				$this->db->where("TI2.fld_type", "stockout");
				$this->db->or_where("TI2.fld_type", "wsale");
				$this->db->or_where("TI2.fld_type", "w2w");
				$this->db->group_end();
				$this->db->where("TI2.fld_clientid",$this->clientid);
				$this->db->where("TI2.fld_del","0");
				$this->db->where("TII2.fld_productid", $prodid);
				$this->db->group_by("TI2.fld_warehouseid");
				$query3 = $this->db->get();
				//echo "<br>" . $this->db->last_query() . " => " . $query2->num_rows();
				if($query3->num_rows()>0)
				{
					foreach($query3->result() as $row3)
					{
						$arrwarehouseoutgoing[$row3->fld_warehouseid] = $row3->boutgoing;
					}
				}
				//predata($arrwarehouseoutgoing);
				//exit;
				## end of warehouse stock
				
				$arroutletincomingfromwarehouse = array();
				$arroutletoutgoingbilling = array();
				$arroutletoutgoinggiftsbilling = array();
				$arroutletincomingfromoutlet = array();
				$arroutletoutgoingtowarehouse = array();
				
				## get outlet stock
				### stock from warehouse incoming
				$this->db->select("TI2.fld_outletid, SUM(TII2.fld_qty) as wincomingqty");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
				$this->db->where("TI2.fld_status", "completed");
				$this->db->where("(TI2.fld_type='stockout')");
				$this->db->where("TI2.fld_del", "0");
				$this->db->where("TII2.fld_productid", $prodid);
				$this->db->where('TI2.fld_clientid',$this->clientid);
				$this->db->group_by("TI2.fld_outletid");
				$query2 = $this->db->get();
				//echo "<br>" . $this->db->last_query();
				if($query2->num_rows()>0)
				{
					foreach($query2->result() as $row2)
					{
						$arroutletincomingfromwarehouse[$row2->fld_outletid] = $row2->wincomingqty;
					}
				}
				//predata($arroutletincomingfromwarehouse);
				
				### stock out from billing outgoing
				$this->db->select("TI5.fld_outletid, SUM(TII5.fld_qty) as boutgoing");
				$this->db->from("tbl_billinginfo TII5");
				$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
				$this->db->where("TI5.fld_status!=",'cancelled');
				$this->db->where('TI5.fld_clientid',$this->clientid);
				$this->db->where("TI5.fld_del","0");
				$this->db->where("TII5.fld_productid", $prodid);
				$this->db->group_by("TI5.fld_outletid");
				$query5 = $this->db->get();
				//echo "<br>" . $this->db->last_query();
				if($query5->num_rows()>0)
				{
					foreach($query5->result() as $row5)
					{
						$arroutletoutgoingbilling[$row5->fld_outletid] = $row5->boutgoing;
					}
				}
				//predata($arroutletoutgoingbilling);
				
				### gift stock from billing outgoing
				$this->db->select("BI.fld_outletid, SUM(BI.fld_gqty) as goutgoing");
				$this->db->from("tbl_billing BI");
				$this->db->where("BI.fld_status!='cancelled'");
				$this->db->where("BI.fld_del","0");
				$this->db->where('BI.fld_gprodid',$prodid);
				$this->db->where('BI.fld_clientid',$this->clientid);
				$this->db->group_by("BI.fld_outletid");
				$query6 = $this->db->get();
				//echo "<br>" . $this->db->last_query();
				if($query6->num_rows()>0)
				{
					foreach($query6->result() as $row6)
					{
						$arroutletoutgoinggiftsbilling[$row6->fld_outletid] = $row6->goutgoing;
					}
				}
				//predata($arroutletoutgoinggiftsbilling);
						
				### stock from other outlet incoming
				$this->db->select("TI3.fld_outletid, SUM(TII3.fld_qty) as oincomingqty");
				$this->db->from("tbl_transactioninfo TII3");
				$this->db->join("tbl_transaction TI3", "TII3.fld_transactionid=TI3.fld_id");
				//9-aug $this->db->where("(TI3.fld_status='completed' or TI3.fld_status='delivered')");
				$this->db->where("TI3.fld_status", "completed");
				$this->db->where("(TI3.fld_type='o2o')");
				$this->db->where("TII3.fld_productid", $prodid);
				$this->db->where("TI3.fld_del", "0");
				$this->db->where('TI3.fld_clientid',$this->clientid);
				$this->db->group_by("TI3.fld_outletid");
				$query3 = $this->db->get();
				//echo "<br>" . $this->db->last_query();
				if($query3->num_rows()>0)
				{
					foreach($query3->result() as $row3)
					{
						$arroutletincomingfromoutlet[$row3->fld_outletid] = $row3->oincomingqty;
					}
				}
				//predata($arroutletincomingfromoutlet);
				
				### stock from outlet outgoing to warehouse
				$this->db->select("TI4.fld_fromoutletid, SUM(TII4.fld_qty) as ooutgoingqty");
				$this->db->from("tbl_transactioninfo TII4");
				$this->db->join("tbl_transaction TI4", "TII4.fld_transactionid=TI4.fld_id");
				$this->db->where("(TI4.fld_status='completed' or TI4.fld_status='delivered')");
				$this->db->where("(TI4.fld_type='o2o' or TI4.fld_type='o2w')");
				$this->db->where("TII4.fld_productid", $prodid);
				$this->db->where("TI4.fld_del", "0");
				$this->db->where('TI4.fld_clientid',$this->clientid);
				$this->db->group_by("TI4.fld_fromoutletid");
				$query4 = $this->db->get();
				//echo "<br>" . $this->db->last_query();
				if($query4->num_rows()>0)
				{
					foreach($query4->result() as $row4)
					{
						$arroutletoutgoingtowarehouse[$row4->fld_fromoutletid] = $row4->ooutgoingqty;
					}
				}
				//predata($arroutletoutgoingtowarehouse);
				## end of outlet stock
				
				
				## get stock by warehouse list
				$this->db->distinct();
				$this->db->select("WI.fld_id, WI.fld_name");
				$this->db->from("tbl_warehouse WI");
				//$this->db->join("tbl_transaction TI", "WI.fld_id=TI.fld_warehouseid", "left");
				//$this->db->join("tbl_transaction TI02", "WI.fld_id=TI02.fld_towarehouseid", "left");
				$this->db->where("WI.fld_show", "yes");
				$result02 = $this->db->get();
				//echo "<br>" . $this->db->last_query() . " => " . $result02->num_rows();
				if($result02->num_rows()>0)
				{
					while ($row02 = $result02->unbuffered_row())
					{
						$warehouseid = $row02->fld_id;
						$arrparams["warehouseid"] = $row02->fld_id;
						
						$warehouseincoming = 0;
						if($arrwarehouseincomingviapurchasenoutlet[$warehouseid]) $warehouseincoming += $arrwarehouseincomingviapurchasenoutlet[$warehouseid];
						if($arrwarehouseincomingfromotherwarehouse[$warehouseid]) $warehouseincoming += $arrwarehouseincomingfromotherwarehouse[$warehouseid];
						
						$warehouseoutgoing = 0;
						if($arrwarehouseoutgoing[$warehouseid]) $warehouseoutgoing += $arrwarehouseoutgoing[$warehouseid];
												
						$warehouseinstockqtyinbottles = $warehouseincoming - $warehouseoutgoing;
						$warehouseinstockqtyincases = round(($warehouseinstockqtyinbottles / $bottlesincase ), 2);
						$arritemstock[] = array(
							"id" => $row02->fld_id,
							"name" => $row02->fld_name,
							"type" => "warehouse",
							"incoming" => $warehouseincoming,
							"outgoing" => $warehouseoutgoing,
							"instockinbottles" => $warehouseinstockqtyinbottles,
							"instockincases" => $warehouseinstockqtyincases
						);
					}
				}

				## get stock by outlet list
				$this->db->distinct();
				$this->db->select("OI.fld_id, OI.fld_name");
				$this->db->from("tbl_outlets OI");
				//$this->db->join("tbl_transaction TI", "OI.fld_id=TI.fld_outletid", "left");
				//$this->db->join("tbl_transaction TI02", "OI.fld_id=TI02.fld_fromoutletid", "left");
				$this->db->where("OI.fld_show", "yes");
				$result02 = $this->db->get();
				if($result02->num_rows()>0)
				{
					while ($row02 = $result02->unbuffered_row())
					{
						$outletid = $row02->fld_id;
						$arrparams["outletid"] = $row02->fld_id;
						
						$outletincoming = 0;
						if($arroutletincomingfromwarehouse[$outletid]) $outletincoming += $arroutletincomingfromwarehouse[$outletid];
						if($arroutletincomingfromoutlet[$outletid]) $outletincoming += $arroutletincomingfromoutlet[$outletid];
						
						$outletoutgoing = 0;
						if($arroutletoutgoingbilling[$outletid]) $outletoutgoing += $arroutletoutgoingbilling[$outletid];
						if($arroutletoutgoingtowarehouse[$outletid]) $outletoutgoing += $arroutletoutgoingtowarehouse[$outletid];
						if($arroutletoutgoinggiftsbilling[$outletid]) $outletoutgoing += $arroutletoutgoinggiftsbilling[$outletid];

						$instockqtyinbottles = $outletincoming - $outletoutgoing;
						$instockqtyincases = round(($instockqtyinbottles / $bottlesincase ), 2);
						$arritemstock[] = array(
							"id" => $row02->fld_id,
							"name" => $row02->fld_name,
							"type" => "outlet",
							"incoming" => $outletincoming,
							"outgoing" => $outletoutgoing,
							"instockinbottles" => $instockqtyinbottles,
							"instockincases" => $instockqtyincases
						);
					}
				}
				return $arritemstock;
			}
			else
			{
				return false;
			}
		}
		else
		{
			return false;
		}
	}
	
	function getprojecteditemstock($params)
	{
		$arrdata = array();
		$noofdaystocalculate = $params["prevnoofdays"];
		$noofdays = $params["noofdays"];
		$outletids = $params["outletids"];
		if($noofdaystocalculate=="") $noofdaystocalculate = 10;
		$arritemstock = array();
		$productid = 0;
		if($params["productid"]) $productid = decodeValue($params["productid"]);
		if(count(array_filter($outletids))>0)
		{
			$this->db->select("fld_id, fld_name,fld_minstockvalue, fld_noofbottlesincase");
			$this->db->where("fld_del", "0");
			if($productid>0) $this->db->where("fld_id", $productid);
			$this->db->order_by("fld_name", "asc");
			$result = $this->db->get("tbl_products");
			if($result->num_rows()>0)
			{
				## get stock sold
				$this->db->select("BII.fld_productid, BI.fld_outletid, sum(BII.fld_qty) as totalqty, (sum(BII.fld_qty)/$noofdaystocalculate) as averagesingledayqty");
				$this->db->from("tbl_billinginfo BII");
				$this->db->join("tbl_billing BI", "BII.fld_billingid=BI.fld_id");
				$this->db->where("BI.fld_billdate >= CURRENT_DATE() - INTERVAL $noofdaystocalculate DAY");
				$this->db->where("BI.fld_billdate <= CURRENT_DATE()");
				if($productid>0) $this->db->where("BII.fld_productid", $productid);
				$this->db->group_by("BII.fld_productid, BI.fld_outletid");
				$result02 = $this->db->get();
				if($result02->num_rows()>0)
				{
					foreach($result02->result() as $row02)
					{
						$projectedqty = $row02->averagesingledayqty * $noofdays;
						$arrdata["outlets"][$row02->fld_outletid][$row02->fld_productid]["totalqty"] = $row02->totalqty;
						$arrdata["outlets"][$row02->fld_outletid][$row02->fld_productid]["averagesingledayqty"] = $row02->averagesingledayqty;
						$arrdata["outlets"][$row02->fld_outletid][$row02->fld_productid]["projectedqty"] = $projectedqty;
					}
				}
				## end of stock sold
				
				## get stock of each outlet
				$arrproductids = array();
				
				### stock from warehouse incoming
				$this->db->select("TI2.fld_outletid, TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->where("TI2.fld_status", "completed");
				$this->db->where("(TI2.fld_type='stockout')");
				$this->db->where("TI2.fld_del", "0");
				$this->db->where("TI2.fld_clientid",$this->clientid);
				$this->db->where_in("TI2.fld_outletid",$outletids);
				if($productid>0) $this->db->where("TII2.fld_productid", $productid);
				$this->db->group_by("TI2.fld_outletid, TII2.fld_productid");
				$query2 = $this->db->get();
				if($query2->num_rows()>0)
				{
					foreach($query2->result() as $row2)
					{
						$arrdata["outletstock"]["incoming1"][$row2->fld_outletid][$row2->fld_productid] = $row2->wincomingqty;
					}
				}
				
				### stock out from billing outgoing
				$this->db->select("TI5.fld_outletid, TII5.fld_productid, SUM(TII5.fld_qty) as boutgoing");
				$this->db->from("tbl_billinginfo TII5");
				$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
				$this->db->where("TI5.fld_status!=","cancelled");
				$this->db->where("TI5.fld_del","0");
				$this->db->where("TI5.fld_clientid",$this->clientid);
				$this->db->where_in("TI5.fld_outletid",$outletids);
				if($productid>0) $this->db->where("TII5.fld_productid", $productid);
				$this->db->group_by("TI5.fld_outletid, TII5.fld_productid");
				$query5 = $this->db->get();
				if($query5->num_rows()>0)
				{
					foreach($query5->result() as $row5)
					{
						
						$arrdata["outletstock"]["outgoing1"][$row5->fld_outletid][$row5->fld_productid] = $row5->boutgoing;
					}
				}
				
				### gift stock from billing outgoing
				$this->db->select("BI.fld_outletid, BI.fld_gprodid, SUM(BI.fld_gqty) as goutgoing");
				$this->db->from("tbl_billing BI");
				$this->db->where("BI.fld_status!='cancelled'");
				$this->db->where("BI.fld_del","0");
				$this->db->where("BI.fld_clientid",$this->clientid);
				$this->db->where_in("BI.fld_outletid",$outletids);
				if($productid>0) $this->db->where("BI.fld_gprodid", $productid);
				$this->db->group_by("BI.fld_outletid, BI.fld_gprodid");
				$query6 = $this->db->get();
				if($query6->num_rows()>0)
				{
					foreach($query6->result() as $row6)
					{
						$arrdata["outletstock"]["outgoing2"][$row6->fld_outletid][$row6->fld_gprodid] = $row6->goutgoing;
					}
				}
						
				### stock from outlet incoming
				$this->db->select("TI3.fld_outletid, TII3.fld_productid, SUM(TII3.fld_qty) as oincomingqty");
				$this->db->from("tbl_transactioninfo TII3");
				$this->db->join("tbl_transaction TI3", "TII3.fld_transactionid=TI3.fld_id");
				$this->db->where("TI3.fld_status", "completed");
				$this->db->where("(TI3.fld_type='o2o')");
				$this->db->where("TI3.fld_del", "0");
				$this->db->where("TI3.fld_clientid",$this->clientid);
				$this->db->where_in("TI3.fld_outletid", $outletids);
				if($productid>0) $this->db->where("TII3.fld_productid", $productid);
				$this->db->group_by("TI3.fld_outletid, TII3.fld_productid");
				$query3 = $this->db->get();
				if($query3->num_rows()>0)
				{
					foreach($query3->result() as $row3)
					{
						$arrdata["outletstock"]["incoming2"][$row3->fld_outletid][$row3->fld_productid] = $row3->oincomingqty;
					}
				}
				
				### stock from outlet outgoing
				$this->db->select("TI4.fld_fromoutletid, TII4.fld_productid, SUM(TII4.fld_qty) as ooutgoingqty");
				$this->db->from("tbl_transactioninfo TII4");
				$this->db->join("tbl_transaction TI4", "TII4.fld_transactionid=TI4.fld_id");
				$this->db->where("(TI4.fld_status='completed' or TI4.fld_status='delivered')");
				$this->db->where("(TI4.fld_type='o2o' or TI4.fld_type='o2w')");
				$this->db->where("TI4.fld_del", "0");
				$this->db->where("TI4.fld_clientid",$this->clientid);
				$this->db->where_in("TI4.fld_fromoutletid",$outletids);
				if($productid>0) $this->db->where("TII4.fld_productid", $productid);
				$this->db->group_by("TI4.fld_fromoutletid, TII4.fld_productid");
				$query4 = $this->db->get();
				if($query4->num_rows()>0)
				{
					foreach($query4->result() as $row4)
					{
						$arrdata["outletstock"]["outgoing3"][$row4->fld_fromoutletid][$row4->fld_productid] = $row4->ooutgoingqty;
					}
				}
				## end of get data from other tabls
				## end of stock of each outlet
				
				## get products
				foreach($result->result() as $row)
				{
					$arrdata["products"][] = array("fld_id" => $row->fld_id, "fld_name" => $row->fld_name);
				}
				## end of products
			}
		}
		return $arrdata;
	}
	
	function getitemsoldqty($params)
	{
		$productid = $params["productid"];
		if($productid=="") $productid = 0;
		$outletid = $params["outletid"];
		if($outletid=="") $outletid = 0;
		$fromdate = $params["fromdate"];
		$todate = $params["todate"];
		if($fromdate!="" && $todate!="")
		{
			$fromdate = date("Y-m-d", strtotime($fromdate));
			$todate = date("Y-m-d", strtotime($todate));
		}
		$arritemstock = array();
		if($productid>0)
		{
			$this->db->select("fld_name,fld_minstockvalue, fld_noofbottlesincase");
			$this->db->where("fld_id", $productid);
			$result = $this->db->get("tbl_products");
			if($result->num_rows()>0)
			{
				$row = $result->row();
				$name = $row->fld_name;
				$minstockvalue = $row->fld_minstockvalue;
				$bottlesincase = $row->fld_noofbottlesincase;
				
				$arrparams = array(
					"productid" => $productid,
					"minstockvalue" => $minstockvalue,
					"bottlesincase" => $bottlesincase
				);
				
				$arrwarehouseincomingviapurchasenoutlet = array();
				$arrwarehouseincomingfromotherwarehouse = array();
				$arrwarehouseoutgoing = array();
				
				$prodid = $productid;
				
				$arroutletincomingfromwarehouse = array();
				$arroutletoutgoingbilling = array();
				$arroutletoutgoinggiftsbilling = array();
				$arroutletincomingfromoutlet = array();
				$arroutletoutgoingtowarehouse = array();

				### stock out from billing outgoing
				$this->db->select("TI5.fld_outletid, SUM(TII5.fld_qty) as boutgoing");
				$this->db->from("tbl_billinginfo TII5");
				$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
				$this->db->where("TI5.fld_status!=",'cancelled');
				$this->db->where('TI5.fld_clientid',$this->clientid);
				if($outletid>0) $this->db->where("TI5.fld_outletid", $outletid);
				if($fromdate!="" && $todate!="") $this->db->where("(TI5.fld_billdate >= '".$fromdate."' and TI5.fld_billdate <= '".$todate."')");
				$this->db->where("TI5.fld_del","0");
				$this->db->where("TII5.fld_productid", $prodid);
				$this->db->group_by("TI5.fld_outletid");
				$query5 = $this->db->get();
				//echo "<br>" . $this->db->last_query();
				if($query5->num_rows()>0)
				{
					foreach($query5->result() as $row5)
					{
						$arroutletoutgoingbilling[$row5->fld_outletid] = $row5->boutgoing;
					}
				}
								
				### gift stock from billing outgoing
				$this->db->select("BI.fld_outletid, SUM(BI.fld_gqty) as goutgoing");
				$this->db->from("tbl_billing BI");
				$this->db->where("BI.fld_status!='cancelled'");
				$this->db->where("BI.fld_del","0");
				$this->db->where('BI.fld_gprodid',$prodid);
				$this->db->where('BI.fld_clientid',$this->clientid);
				if($outletid>0) $this->db->where("BI.fld_outletid", $outletid);
				if($fromdate!="" && $todate!="") $this->db->where("(BI.fld_billdate >= '".$fromdate."' and BI.fld_billdate <= '".$todate."')");
				$this->db->group_by("BI.fld_outletid");
				$query6 = $this->db->get();
				//echo "<br>" . $this->db->last_query();
				if($query6->num_rows()>0)
				{
					foreach($query6->result() as $row6)
					{
						$arroutletoutgoinggiftsbilling[$row6->fld_outletid] = $row6->goutgoing;
					}
				}
				
				## get stock by outlet list
				$this->db->distinct();
				$this->db->select("OI.fld_id, OI.fld_name");
				$this->db->from("tbl_outlets OI");
				$this->db->join("tbl_billing BI", "OI.fld_id=BI.fld_outletid");
				if($outletid>0) $this->db->where("OI.fld_id", $outletid);
				$this->db->order_by("OI.fld_name");
				$this->db->where("OI.fld_show", "yes");
				$result02 = $this->db->get();
				//echo "<br>" . $this->db->last_query();
				if($result02->num_rows()>0)
				{
					while ($row02 = $result02->unbuffered_row())
					{
						$outletid = $row02->fld_id;
						
						$outletoutgoing = 0;
						if($arroutletoutgoingbilling[$outletid]) $outletoutgoing += $arroutletoutgoingbilling[$outletid];
						if($arroutletoutgoinggiftsbilling[$outletid]) $outletoutgoing += $arroutletoutgoinggiftsbilling[$outletid];

						$outletoutgoingcases = round(($outletoutgoing / $bottlesincase ), 2);
						$arritemstock[] = array(
							"id" => $row02->fld_id,
							"name" => $row02->fld_name,
							"outgoing" => $outletoutgoing,
							"outgoingcases" => $outletoutgoingcases
						);
					}
				}
				return $arritemstock;
			}
			else
			{
				return false;
			}
		}
		else
		{
			return false;
		}
	}
	
	function liststockstatusdata($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$warehouseid = decodeValue($this->input->get("warehouseid"));
		if($warehouseid=="") $warehouseid = 0;

		$type = $this->input->get("type");
		$catid = decodeValue($this->input->get("catid"));
		$brandid = decodeValue($this->input->get("brandid"));
		$fromdt = $this->input->get("fromdate");
		if($fromdt=="") $fromdt = date("Y-m-d");
		$todt = $this->input->get("todate");
		if($todt=="") $todt = date("Y-m-d");
		if($fromdt!="")
		{
			 $fromdate = convertdateFormat($fromdt,'Y-m-d');
		}
		if($todt!="")
		{
			$todate = convertdateFormat($todt,'Y-m-d');
		}

		if($fromdt=="" && $todt=="")
		{
			$fromdate = date('Y-m-d');
			$todate = date('Y-m-d');
		}
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->distinct();
			$this->db->select("PI.fld_id, PI.fld_name,PI.fld_noofbottlesincase,PI.fld_minstockvalue");
		}		
		$this->db->from("tbl_products PI");
		if($type!="")
		{
			$this->db->where('PI.fld_type',$type);
		}
		if($catid>0)
		{
			$this->db->where('PI.fld_catid',$catid);
		}
		if($brandid>0)
		{
			$this->db->where('PI.fld_brandid',$brandid);
		}
		
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("PI.fld_name", $keyword);
			$this->db->or_like("PI.fld_barcode", $keyword);
			$this->db->or_like("PI.fld_barcode02", $keyword);
			$this->db->or_like("PI.fld_barcode03", $keyword);
			$this->db->or_like("PI.fld_barcode04", $keyword);
			$this->db->or_like("PI.fld_noofbottlesincase", $keyword);
			$this->db->group_end();
		}		
		
		if($listtype != "count")
		{
			$this->db->order_by('PI.fld_name', 'asc');		
			if(is_array($params))
			{
				if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
					$this->db->limit($params['limit'],$params['start']);
				}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
					$this->db->limit($params['limit']);
				}
			}
		}
		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
			//return $results->num_rows();
		}
		else
		{
			## others
			$arrprevincoming = array();
			$arrprevoutgoing = array();
			$arrprevincomingfromwarehouse = array();
			$arrcurrincoming = array();
			$arrcurroutgoing = array();
			$arrcurrincomingfromwarehouse = array();
			
			### previous stock incoming via purchase or from outlet from date
			$this->db->select("TII2.fld_productid, SUM(TII2.fld_qty) as pincoming");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_status", "completed");
			$this->db->where("(TI2.fld_type='stockin' or TI2.fld_type='o2w')");
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			if($warehouseid>0) $this->db->where('TI2.fld_warehouseid',$warehouseid);
			$this->db->where("TI2.fld_refdate<'".$fromdate."'");
			$this->db->group_by('TII2.fld_productid');
			$piquery = $this->db->get();
			if($piquery->num_rows()>0)
			{
				foreach($piquery->result() as $pirow)
				{
					$arrprevincoming[$pirow->fld_productid] = $pirow->pincoming;
				}
			}
			
			### previous stock incoming other warehouse
			$this->db->select("TII2.fld_productid, SUM(TII2.fld_qty) as pincoming");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_status", "completed");
			$this->db->where("TI2.fld_type", "w2w");
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			if($warehouseid>0) $this->db->where("TI2.fld_towarehouseid",$warehouseid);
			$this->db->where("TI2.fld_refdate<'".$fromdate."'");
			$this->db->group_by("TII2.fld_productid");
			$piquery02 = $this->db->get();
			if($piquery02->num_rows()>0)
			{
				foreach($piquery02->result() as $pirow02)
				{
					$arrprevincomingfromwarehouse[$pirow02->fld_productid] = $pirow02->pincoming;
				}
			}
			
			### previous stock outgoing from date
			$this->db->select("TII2.fld_productid, SUM(TII2.fld_qty) as poutgoing");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->group_start();
			$this->db->where("TI2.fld_type", "stockout");
			$this->db->or_where("TI2.fld_type", "wsale");
			$this->db->or_where("TI2.fld_type", "w2w");
			$this->db->group_end();
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			if($warehouseid>0) $this->db->where('TI2.fld_warehouseid',$warehouseid);
			$this->db->where("TI2.fld_refdate<'".$fromdate."'");							
			$this->db->group_by('TII2.fld_productid');
			$poquery = $this->db->get();
			if($poquery->num_rows()>0)
			{
				foreach($poquery->result() as $porow)
				{
					$arrprevoutgoing[$porow->fld_productid] = $porow->poutgoing;
				}
			}
			
			### stock incoming via purchase or outlet
			$this->db->select("TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_status", "completed");
			$this->db->where("(TI2.fld_type='stockin' or TI2.fld_type='o2w')");
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			if($warehouseid>0) $this->db->where('TI2.fld_warehouseid',$warehouseid);
			if($outletid>0)
			{
				$this->db->where('TI2.fld_outletid',$outletid);
			}
			$this->db->where("(TI2.fld_refdate >= '".$fromdate."' and TI2.fld_refdate <= '".$todate."')");
			$this->db->group_by('TII2.fld_productid');
			$query2 = $this->db->get();
			if($query2->num_rows()>0)
			{
				foreach($query2->result() as $row2)
				{
					$arrcurrincoming[$row2->fld_productid] = $row2->wincomingqty;
				}
			}
			
			### stock incoming from other warehouse
			$this->db->select("TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_status", "completed");
			$this->db->where("TI2.fld_type", "w2w");
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			if($warehouseid>0) $this->db->where("TI2.fld_towarehouseid",$warehouseid);
			if($outletid>0)
			{
				$this->db->where("TI2.fld_outletid",$outletid);
			}
			$this->db->where("(TI2.fld_refdate >= '".$fromdate."' and TI2.fld_refdate <= '".$todate."')");
			$this->db->group_by("TII2.fld_productid");
			$query02 = $this->db->get();
			if($query02->num_rows()>0)
			{
				foreach($query02->result() as $row02)
				{
					$arrcurrincomingfromwarehouse[$row02->fld_productid] = $row02->wincomingqty;
				}
			}
			
			### stock from warehouse outgoing
			$this->db->select("TII2.fld_productid, SUM(TII2.fld_qty) as boutgoing");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->group_start();
			$this->db->where("TI2.fld_type", "stockout");
			$this->db->or_where("TI2.fld_type", "wsale");
			$this->db->or_where("TI2.fld_type", "w2w");
			$this->db->group_end();
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			if($warehouseid>0) $this->db->where('TI2.fld_warehouseid',$warehouseid);
			if($outletid>0)
			{
				$this->db->where('TI2.fld_outletid',$outletid);
			}
			$this->db->where("(TI2.fld_refdate >= '".$fromdate."' and TI2.fld_refdate <= '".$todate."')");
			$this->db->group_by('TII2.fld_productid');
			$query3 = $this->db->get();
			$row3 = $query3->row();
			$boutgoing = $row3->boutgoing;
			if($query3->num_rows()>0)
			{
				foreach($query3->result() as $row3)
				{
					$arrcurroutgoing[$row3->fld_productid] = $row3->boutgoing;
				}
			}
			
			$arr = array();
			if($results->num_rows()>0)
			{
				//return $results->result();
				$resultsinfo = $results->result();
				
				if(is_array($resultsinfo) && count(array_filter($resultsinfo))>0)
				{					
					foreach($resultsinfo as $row)
					{
						$prodid = $row->fld_id;
						$arr[$prodid] = array(
							"id" => $prodid,
							"name" => $row->fld_name,
							"previousstock" => 0,
							"incoming" => 0,
							"outgoing" => 0,
							"instock" => 0,
							"instockcases" => 0,
							"bottlesincase" => $row->fld_noofbottlesincase,
							"minstockvalue" => $row->fld_minstockvalue,
						);	
						if($arr[$prodid])
						{
							### previous stock incoming from purchase or outlet
							$pincoming = 0;
							if($arrprevincoming[$prodid]) $pincoming = $arrprevincoming[$prodid];
							if($arrprevincomingfromwarehouse[$prodid]) $pincoming += $arrprevincomingfromwarehouse[$prodid];

							## previous stock outgoing to outlets
							$poutgoing = 0;
							if($arrprevoutgoing[$prodid]) $poutgoing = $arrprevoutgoing[$prodid];

							$previousstock =  ($pincoming-$poutgoing);
							$arr[$prodid]["previousstock"] = $previousstock;

							### stock incoming
							$wincomingqty = 0;
							if($arrcurrincoming[$prodid]) $wincomingqty = $arrcurrincoming[$prodid];
							if($arrcurrincomingfromwarehouse[$prodid]) $wincomingqty += $arrcurrincomingfromwarehouse[$prodid];
							$arr[$prodid]["incoming"] += $wincomingqty;

							### stock from warehouse outgoing
							$boutgoing = 0;
							if($arrcurroutgoing[$prodid]) $boutgoing = $arrcurroutgoing[$prodid];
							$arr[$prodid]["outgoing"] += $boutgoing;
							
							$instockbottles = $previousstock + ($arr[$prodid]["incoming"]-$arr[$prodid]["outgoing"]);
							if($arr[$prodid]["bottlesincase"]>0)
							{
								$instockcases = $instockbottles / $arr[$prodid]["bottlesincase"];
							}
							else
							{
								$instockcases = $instockbottles;
							}
							
							$arr[$prodid]["instock"] = $instockbottles;
							$arr[$prodid]["instockcases"] = round($instockcases, 2);
						}
					}
					//predata($arr); exit;
				}
			}
			return $arr;
		}
	}
	
	function listnetstockstatusdata($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$type = $this->input->get("type");
		$catid = decodeValue($this->input->get("catid"));
		$brandid = decodeValue($this->input->get("brandid"));
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->distinct();
			$this->db->select("PI.fld_id, PI.fld_name,PI.fld_noofbottlesincase,PI.fld_minstockvalue");
		}		
		$this->db->from("tbl_products PI");
		if($type!="")
		{
			$this->db->where('PI.fld_type',$type);
		}
		if($catid>0)
		{
			$this->db->where('PI.fld_catid',$catid);
		}
		if($brandid>0)
		{
			$this->db->where('PI.fld_brandid',$brandid);
		}
		
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("PI.fld_name", $keyword);
			$this->db->or_like("PI.fld_barcode", $keyword);
			$this->db->or_like("PI.fld_barcode02", $keyword);
			$this->db->or_like("PI.fld_barcode03", $keyword);
			$this->db->or_like("PI.fld_barcode04", $keyword);
			$this->db->or_like("PI.fld_noofbottlesincase", $keyword);
			$this->db->group_end();
		}		
		
		if($listtype != "count")
		{
			$this->db->order_by('PI.fld_name', 'asc');		
			if(is_array($params))
			{
				if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
					$this->db->limit($params['limit'],$params['start']);
				}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
					$this->db->limit($params['limit']);
				}
			}
		}
		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			//return $results->row()->totalrecs;
			return $results->row()->totalrecs;
		}
		else
		{
			## others
			$arrcurrincoming = array();
			$arrcurroutgoing = array();
			$arrbillingoutgoing = array();
			$arrbillingoutgoinggifts = array();
			
			### stock incoming via purchase
			$this->db->select("TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_type", "stockin");
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			$this->db->group_by('TII2.fld_productid');
			$query2 = $this->db->get();
			if($query2->num_rows()>0)
			{
				foreach($query2->result() as $row2)
				{
					$arrcurrincoming[$row2->fld_productid] = $row2->wincomingqty;
				}
			}
			
			### stock from warehouse outgoing
			$this->db->select("TII2.fld_productid, SUM(TII2.fld_qty) as boutgoing");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_type", "wsale");
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			$this->db->group_by('TII2.fld_productid');
			$query3 = $this->db->get();
			if($query3->num_rows()>0)
			{
				foreach($query3->result() as $row3)
				{
					$arrcurroutgoing[$row3->fld_productid] = $row3->boutgoing;
				}
			}
			
			### stock out from billing outgoing
			$this->db->select("TII5.fld_productid, SUM(TII5.fld_qty) as boutgoing");
			$this->db->from("tbl_billinginfo TII5");
			$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
			$this->db->where("TI5.fld_status!=",'cancelled');
			$this->db->where('TI5.fld_clientid',$this->clientid);
			$this->db->where("TI5.fld_del","0");
			$this->db->group_by('TII5.fld_productid');
			$query5 = $this->db->get();
			if($query5->num_rows()>0)
			{
				foreach($query5->result() as $row5)
				{
					$arrbillingoutgoing[$row5->fld_productid] = $row5->boutgoing;
				}
			}
			
			### gift stock from billing outgoing
			$this->db->select("BI.fld_gprodid, SUM(BI.fld_gqty) as goutgoing");
			$this->db->from("tbl_billing BI");
			$this->db->where("BI.fld_status!='cancelled'");
			$this->db->where("BI.fld_del","0");
			$this->db->where('BI.fld_clientid',$this->clientid);
			$this->db->group_by('BI.fld_gprodid');
			$query6 = $this->db->get();
			if($query6->num_rows()>0)
			{
				foreach($query6->result() as $row6)
				{
					$arrbillingoutgoinggifts[$row6->fld_gprodid] = $row6->goutgoing;
				}
			}
			
			$arr = array();
			if($results->num_rows()>0)
			{
				//return $results->result();
				$resultsinfo = $results->result();
				
				if(is_array($resultsinfo) && count(array_filter($resultsinfo))>0)
				{					
					foreach($resultsinfo as $row)
					{
						$prodid = $row->fld_id;
						$arr[$prodid] = array(
							"id" => $prodid,
							"name" => $row->fld_name,
							"incoming" => 0,
							"outgoing" => 0,
							"instock" => 0,
							"instockcases" => 0,
							"bottlesincase" => $row->fld_noofbottlesincase,
							"minstockvalue" => $row->fld_minstockvalue,
						);	
						if($arr[$prodid])
						{
							### stock incoming
							$wincomingqty = 0;
							if($arrcurrincoming[$prodid]) $wincomingqty = $arrcurrincoming[$prodid];
							$arr[$prodid]["incoming"] += $wincomingqty;

							### stock from warehouse outgoing
							$boutgoing = 0;
							if($arrcurroutgoing[$prodid]) $boutgoing = $arrcurroutgoing[$prodid];
							if($arrbillingoutgoing[$prodid]) $boutgoing += $arrbillingoutgoing[$prodid];
							if($arrbillingoutgoinggifts[$prodid]) $boutgoing += $arrbillingoutgoinggifts[$prodid];
							$arr[$prodid]["outgoing"] += $boutgoing;
							
							$instockbottles = $arr[$prodid]["incoming"] - $arr[$prodid]["outgoing"];
							if($arr[$prodid]["bottlesincase"]>0)
							{
								$instockcases = $instockbottles / $arr[$prodid]["bottlesincase"];
							}
							else
							{
								$instockcases = $instockbottles;
							}
							
							$arr[$prodid]["instock"] = $instockbottles;
							$arr[$prodid]["instockcases"] = round($instockcases, 2);
						}
					}
					//predata($arr); exit;
				}
			}
			return $arr;
		}
	}
	
	function getlistbyids($table, $arrids)
	{
		$this->db->distinct();
		$this->db->select("*");
		$this->db->where_in("fld_id", $arrids);
		$result = $this->db->get($table);
		if($result->num_rows()>0) return $result->result();
	}
	
	function listaveragesaledata()
	{
		$arrdata = array();
		$keyword = $this->input->get("search");
		$fromdt = $this->input->get("fromdate");
		$todt = $this->input->get("todate");
		$outletids = $this->input->get("outletids");
		$warehouseids = $this->input->get("warehouseids");
		
		if(count(array_filter($outletids))>0 || count(array_filter($warehouseids))>0)
		{
			$arrproductids = array();
			$arroutletsale = array();
			$arrwarehousesale = array();
			## get outlets sale
			if(is_array($outletids) && count(array_filter($outletids))>0)
			{
				$this->db->select("TI5.fld_outletid, TII5.fld_productid, SUM(TII5.fld_qty) as qty, SUM(TII5.fld_qty*TII5.fld_price) as subtotal");
				$this->db->from("tbl_billing TI5");
				$this->db->join("tbl_billinginfo TII5", "TI5.fld_id=TII5.fld_billingid");
				$this->db->where_in("TI5.fld_outletid", $outletids);
				$this->db->where("TI5.fld_status", "completed");
				$this->db->where("TI5.fld_del","0");
				if($fromdt!="" && $todt!="")
				{
					$fromdate = convertdateFormat($fromdt,'Y-m-d');
					$todate = convertdateFormat($todt,'Y-m-d');
					$this->db->where("(TI5.fld_billdate >= '".$fromdate."' and TI5.fld_billdate <= '".$todate."')");
				}
				$this->db->group_by('TI5.fld_outletid, TII5.fld_productid');
				$this->db->having("SUM(TII5.fld_qty)>0");
				$query5 = $this->db->get();
				//echo $this->db->last_query();
				if($query5->num_rows()>0)
				{
					foreach($query5->result() as $row5)
					{
						$arroutletsale[$row5->fld_outletid]["name"] = $row5->outeltname;
						$arroutletsale[$row5->fld_outletid][$row5->fld_productid]["qty"] = $row5->qty;
						$arroutletsale[$row5->fld_outletid][$row5->fld_productid]["subtotal"] = $row5->subtotal;

						$arrproductids[$row5->fld_productid] = $row5->fld_productid;
					}
					$arrdata["outlets"] = $arroutletsale;
				}
				
				## get outlet stock in hand
				### stock from warehouse incoming
				$this->db->select("TI2.fld_outletid, TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->where("TI2.fld_status", "completed");
				$this->db->where("(TI2.fld_type='stockout')");
				$this->db->where("TI2.fld_del", "0");
				$this->db->where("TI2.fld_clientid",$this->clientid);
				$this->db->where_in("TI2.fld_outletid",$outletids);
				if($productid>0) $this->db->where("TII2.fld_productid", $productid);
				$this->db->group_by("TI2.fld_outletid, TII2.fld_productid");
				$query2 = $this->db->get();
				if($query2->num_rows()>0)
				{
					foreach($query2->result() as $row2)
					{
						$arrdata["outletstock"]["incoming1"][$row2->fld_outletid][$row2->fld_productid] = $row2->wincomingqty;
					}
				}
				
				### stock out from billing outgoing
				$this->db->select("TI5.fld_outletid, TII5.fld_productid, SUM(TII5.fld_qty) as boutgoing");
				$this->db->from("tbl_billinginfo TII5");
				$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
				$this->db->where("TI5.fld_status!=","cancelled");
				$this->db->where("TI5.fld_del","0");
				$this->db->where("TI5.fld_clientid",$this->clientid);
				$this->db->where_in("TI5.fld_outletid",$outletids);
				if($productid>0) $this->db->where("TII5.fld_productid", $productid);
				$this->db->group_by("TI5.fld_outletid, TII5.fld_productid");
				$query5 = $this->db->get();
				if($query5->num_rows()>0)
				{
					foreach($query5->result() as $row5)
					{
						
						$arrdata["outletstock"]["outgoing1"][$row5->fld_outletid][$row5->fld_productid] = $row5->boutgoing;
					}
				}
				
				### gift stock from billing outgoing
				$this->db->select("BI.fld_outletid, BI.fld_gprodid, SUM(BI.fld_gqty) as goutgoing");
				$this->db->from("tbl_billing BI");
				$this->db->where("BI.fld_status!='cancelled'");
				$this->db->where("BI.fld_del","0");
				$this->db->where("BI.fld_clientid",$this->clientid);
				$this->db->where_in("BI.fld_outletid",$outletids);
				if($productid>0) $this->db->where("BI.fld_gprodid", $productid);
				$this->db->group_by("BI.fld_outletid, BI.fld_gprodid");
				$query6 = $this->db->get();
				if($query6->num_rows()>0)
				{
					foreach($query6->result() as $row6)
					{
						$arrdata["outletstock"]["outgoing2"][$row6->fld_outletid][$row6->fld_gprodid] = $row6->goutgoing;
					}
				}
						
				### stock from outlet incoming
				$this->db->select("TI3.fld_outletid, TII3.fld_productid, SUM(TII3.fld_qty) as oincomingqty");
				$this->db->from("tbl_transactioninfo TII3");
				$this->db->join("tbl_transaction TI3", "TII3.fld_transactionid=TI3.fld_id");
				$this->db->where("TI3.fld_status", "completed");
				$this->db->where("(TI3.fld_type='o2o')");
				$this->db->where("TI3.fld_del", "0");
				$this->db->where("TI3.fld_clientid",$this->clientid);
				$this->db->where_in("TI3.fld_outletid", $outletids);
				if($productid>0) $this->db->where("TII3.fld_productid", $productid);
				$this->db->group_by("TI3.fld_outletid, TII3.fld_productid");
				$query3 = $this->db->get();
				if($query3->num_rows()>0)
				{
					foreach($query3->result() as $row3)
					{
						$arrdata["outletstock"]["incoming2"][$row3->fld_outletid][$row3->fld_productid] = $row3->oincomingqty;
					}
				}
				
				### stock from outlet outgoing
				$this->db->select("TI4.fld_fromoutletid, TII4.fld_productid, SUM(TII4.fld_qty) as ooutgoingqty");
				$this->db->from("tbl_transactioninfo TII4");
				$this->db->join("tbl_transaction TI4", "TII4.fld_transactionid=TI4.fld_id");
				$this->db->where("(TI4.fld_status='completed' or TI4.fld_status='delivered')");
				$this->db->where("(TI4.fld_type='o2o' or TI4.fld_type='o2w')");
				$this->db->where("TI4.fld_del", "0");
				$this->db->where("TI4.fld_clientid",$this->clientid);
				$this->db->where_in("TI4.fld_fromoutletid",$outletids);
				if($productid>0) $this->db->where("TII4.fld_productid", $productid);
				$this->db->group_by("TI4.fld_fromoutletid, TII4.fld_productid");
				$query4 = $this->db->get();
				if($query4->num_rows()>0)
				{
					foreach($query4->result() as $row4)
					{
						$arrdata["outletstock"]["outgoing3"][$row4->fld_fromoutletid][$row4->fld_productid] = $row4->ooutgoingqty;
					}
				}
				## end of get data from other tabls				
				## end of outlet stock in hand
			}
			
			## get warehouse sale
			if(is_array($warehouseids) && count(array_filter($warehouseids))>0)
			{
				$this->db->select("TI.fld_warehouseid, TII.fld_productid, SUM(TII.fld_qty) as qty, SUM(TII.fld_qty*TII.fld_price) as subtotal");
				$this->db->from("tbl_transaction TI");
				$this->db->join("tbl_transactioninfo TII", "TI.fld_id=TII.fld_transactionid");
				$this->db->where_in("TI.fld_warehouseid", $warehouseids);
				$this->db->where("TI.fld_status", "completed");
				$this->db->where("TI.fld_del","0");
				if($fromdt!="" && $todt!="")
				{
					$fromdate = convertdateFormat($fromdt,'Y-m-d');
					$todate = convertdateFormat($todt,'Y-m-d');
					$this->db->where("(TI.fld_refdate >= '".$fromdate."' and TI.fld_refdate <= '".$todate."')");
				}
				$this->db->group_by('TI.fld_warehouseid, TII.fld_productid');
				$this->db->having("SUM(TII.fld_qty)>0");
				$query5 = $this->db->get();
				//echo "<br>" . $this->db->last_query();
				if($query5->num_rows()>0)
				{
					foreach($query5->result() as $row5)
					{
						$arrwarehousesale[$row5->fld_warehouseid]["name"] = $row5->warehouse;
						$arrwarehousesale[$row5->fld_warehouseid][$row5->fld_productid]["qty"] = $row5->qty;
						$arrwarehousesale[$row5->fld_warehouseid][$row5->fld_productid]["subtotal"] = $row5->subtotal;

						$arrproductids[$row5->fld_productid] = $row5->fld_productid;
					}
					$arrdata["warehouses"] = $arrwarehousesale;
				}
				
				## get warehouse stock in hand
				### stock incoming via purchase or outlet
				$this->db->select("TI2.fld_warehouseid, TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->where("TI2.fld_status", "completed");
				$this->db->where("(TI2.fld_type='stockin' or TI2.fld_type='o2w')");
				$this->db->where("TI2.fld_clientid",$this->clientid);
				$this->db->where("TI2.fld_del","0");
				$this->db->where_in("TI2.fld_warehouseid",$warehouseids);
				$this->db->group_by("TI2.fld_warehouseid, TII2.fld_productid");
				$query2 = $this->db->get();
				if($query2->num_rows()>0)
				{
					foreach($query2->result() as $row2)
					{
						$arrdata["warehousedata"]["incoming1"][$row2->fld_warehouseid][$row2->fld_productid] = $row2->wincomingqty;
					}
				}
				
				### stock incoming from other warehouse
				$this->db->select("TI2.fld_towarehouseid, TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->where("TI2.fld_status", "completed");
				$this->db->where("TI2.fld_type", "w2w");
				$this->db->where("TI2.fld_clientid",$this->clientid);
				$this->db->where("TI2.fld_del","0");
				$this->db->where_in("TI2.fld_towarehouseid",$warehouseids);
				$this->db->group_by("TI2.fld_towarehouseid, TII2.fld_productid");
				$query02 = $this->db->get();
				if($query02->num_rows()>0)
				{
					foreach($query02->result() as $row02)
					{
						$arrdata["warehousedata"]["incoming2"][$row02->fld_towarehouseid][$row02->fld_productid] = $row02->wincomingqty;
					}
				}
				
				### stock from warehouse outgoing
				$this->db->select("TI2.fld_warehouseid, TII2.fld_productid, SUM(TII2.fld_qty) as boutgoing");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
				$this->db->group_start();
				$this->db->where("TI2.fld_type", "stockout");
				$this->db->or_where("TI2.fld_type", "wsale");
				$this->db->or_where("TI2.fld_type", "w2w");
				$this->db->group_end();
				$this->db->where("TI2.fld_clientid",$this->clientid);
				$this->db->where("TI2.fld_del","0");
				$this->db->where_in("TI2.fld_warehouseid", $warehouseids);
				$this->db->group_by("TI2.fld_warehouseid, TII2.fld_productid");
				$query3 = $this->db->get();
				$row3 = $query3->row();
				$boutgoing = $row3->boutgoing;
				if($query3->num_rows()>0)
				{
					foreach($query3->result() as $row3)
					{
						$arrdata["warehousedata"]["outgoing1"][$row3->fld_warehouseid][$row3->fld_productid] = $row3->boutgoing;
					}
				}				
				## end of warehouse stock in hand
			}
			
			## get products
			if(count(array_filter($arrproductids))>0)
			{
				$this->db->distinct();
				$this->db->select("fld_id, fld_name, fld_noofbottlesincase, fld_minstockvalue");
				$this->db->where_in("fld_id", $arrproductids);
				$this->db->where_in("fld_del", "0");
				if($keyword!="")
				{
					$this->db->group_start();
					$this->db->like("fld_name", $keyword);
					$this->db->or_like("fld_barcode", $keyword);
					$this->db->or_like("fld_barcode02", $keyword);
					$this->db->or_like("fld_barcode03", $keyword);
					$this->db->or_like("fld_barcode04", $keyword);
					$this->db->or_like("fld_noofbottlesincase", $keyword);
					$this->db->group_end();
				}
				$this->db->order_by("fld_name", "asc");
				$results = $this->db->get("tbl_products");
				if($results->num_rows()>0)
				{
					$arrdata["products"] = $results->result_array();
					return $arrdata;					
				}
			}
		}
	}

	function liststocksdata()
	{
		$warehouseids = $this->input->post("warehouseids");
		$outletids = $this->input->post("outletids");
				
		$arritemstock = array();
		
		## get warehouse stock
		$arrwarehouseincomingviapurchasenoutlet = array();
		$arrwarehouseincomingfromotherwarehouse = array();
		$arrwarehouseoutgoing = array();
		$arrwarehouseids = array();
		$arrwarehouses = array();
		if(is_array($warehouseids))
		{
			### stock incoming via purchase or outlet
			$this->db->select("TI2.fld_warehouseid, TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_status", "completed");
			$this->db->group_start();
			$this->db->where("TI2.fld_type", "stockin");
			$this->db->or_where("TI2.fld_type", "o2w");
			$this->db->group_end();
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			$this->db->where_in("TI2.fld_warehouseid",$warehouseids);
			$this->db->group_by("TI2.fld_warehouseid, TII2.fld_productid");
			$query2 = $this->db->get();
			//echo "<br>" . $this->db->last_query() . " => " . $query2->num_rows();
			if($query2->num_rows()>0)
			{
				foreach($query2->result() as $row2)
				{
					if(!$arrwarehouseids[$row2->fld_warehouseid]) $arrwarehouseids[$row2->fld_warehouseid] = $row2->fld_warehouseid;
					$arrwarehouseincomingviapurchasenoutlet[$row2->fld_warehouseid][$row2->fld_productid] = $row2->wincomingqty;
				}
			}
			//predata($arrwarehouseincomingviapurchasenoutlet);
			
			### stock incoming from other warehouse
			$this->db->select("TI2.fld_towarehouseid, TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_status", "completed");
			$this->db->where("TI2.fld_type", "w2w");
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			$this->db->where_in("TI2.fld_towarehouseid",$warehouseids);
			$this->db->group_by("TI2.fld_towarehouseid, TII2.fld_productid");
			$query02 = $this->db->get();
			//echo "<br>" . $this->db->last_query() . " => " . $query2->num_rows();
			if($query02->num_rows()>0)
			{
				foreach($query02->result() as $row02)
				{
					if(!$arrwarehouseids[$row02->fld_towarehouseid]) $arrwarehouseids[$row02->fld_towarehouseid] = $row02->fld_towarehouseid;
					$arrwarehouseincomingfromotherwarehouse[$row02->fld_towarehouseid][$row02->fld_productid] = $row02->wincomingqty;
				}
			}
			//predata($arrwarehouseincomingfromotherwarehouse);

			### stock from warehouse outgoing
			$this->db->select("TI2.fld_warehouseid, TII2.fld_productid, SUM(TII2.fld_qty) as boutgoing");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->group_start();
			$this->db->where("TI2.fld_type", "stockout");
			$this->db->or_where("TI2.fld_type", "wsale");
			$this->db->or_where("TI2.fld_type", "w2w");
			$this->db->group_end();
			$this->db->where("TI2.fld_clientid",$this->clientid);
			$this->db->where("TI2.fld_del","0");
			$this->db->where_in("TI2.fld_warehouseid",$warehouseids);
			$this->db->group_by("TI2.fld_warehouseid, TII2.fld_productid");
			$query3 = $this->db->get();
			//echo "<br>" . $this->db->last_query() . " => " . $query2->num_rows();
			if($query3->num_rows()>0)
			{
				foreach($query3->result() as $row3)
				{
					if(!$arrwarehouseids[$row3->fld_warehouseid]) $arrwarehouseids[$row3->fld_warehouseid] = $row3->fld_warehouseid;
					$arrwarehouseoutgoing[$row3->fld_warehouseid][$row3->fld_productid] = $row3->boutgoing;
				}
			}
			//predata($arrwarehouseoutgoing);

			$this->db->distinct();
			$this->db->select("WI.fld_id, WI.fld_name");
			$this->db->from("tbl_warehouse WI");
			$this->db->join("tbl_transaction TI", "WI.fld_id=TI.fld_warehouseid", "left");
			$this->db->join("tbl_transaction TI02", "WI.fld_id=TI02.fld_towarehouseid", "left");
			$this->db->where_in("WI.fld_id", $warehouseids);
			$result02 = $this->db->get();
			//echo "<br>" . $this->db->last_query() . " => " . $result02->num_rows();
			if($result02->num_rows()>0)
			{
				while ($row02 = $result02->unbuffered_row())
				{
					$warehouseid = $row02->fld_id;
					$arrwarehouses[$warehouseid] = $row02->fld_name;
				}
			}		
			## end of warehouse stock
		}
		
		## get outlet stock
		$arroutletids = array();
		$arroutletincomingfromwarehouse = array();
		$arroutletoutgoingbilling = array();
		$arroutletoutgoinggiftsbilling = array();
		$arroutletincomingfromoutlet = array();
		$arroutletoutgoingtowarehouse = array();
		$arroutlets = array();
		if(is_array($outletids))
		{
			### stock from warehouse incoming
			$this->db->select("TI2.fld_outletid, TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
			$this->db->from("tbl_transactioninfo TII2");
			$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
			//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
			$this->db->where("TI2.fld_status", "completed");
			$this->db->where("(TI2.fld_type='stockout')");
			$this->db->where("TI2.fld_del", "0");
			$this->db->where('TI2.fld_clientid',$this->clientid);
			$this->db->where_in("TI2.fld_outletid", $outletids);
			$this->db->group_by("TI2.fld_outletid, TII2.fld_productid");
			$query2 = $this->db->get();
			//echo "<br>" . $this->db->last_query();
			if($query2->num_rows()>0)
			{
				foreach($query2->result() as $row2)
				{
					if(!$arroutletids[$row2->fld_outletid]) $arroutletids[$row2->fld_outletid] = $row2->fld_outletid;
					$arroutletincomingfromwarehouse[$row2->fld_outletid][$row2->fld_productid] = $row2->wincomingqty;
				}
			}
			//echo "warehouse incoming";
			//predata($arroutletincomingfromwarehouse);
			
			### stock out from billing outgoing
			$this->db->select("TI5.fld_outletid, TII5.fld_productid, SUM(TII5.fld_qty) as boutgoing");
			$this->db->from("tbl_billinginfo TII5");
			$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
			$this->db->where("TI5.fld_status!=",'cancelled');
			$this->db->where('TI5.fld_clientid',$this->clientid);
			$this->db->where("TI5.fld_del","0");
			$this->db->where_in("TI5.fld_outletid", $outletids);
			$this->db->group_by("TI5.fld_outletid, TII5.fld_productid");
			$query5 = $this->db->get();
			//echo "<br>" . $this->db->last_query();
			if($query5->num_rows()>0)
			{
				foreach($query5->result() as $row5)
				{
					if(!$arroutletids[$row5->fld_outletid]) $arroutletids[$row5->fld_outletid] = $row5->fld_outletid;
					$arroutletoutgoingbilling[$row5->fld_outletid][$row5->fld_productid] = $row5->boutgoing;
				}
			}
			//echo "out via billing";
			//predata($arroutletoutgoingbilling);
			
			### gift stock from billing outgoing
			$this->db->select("BI.fld_outletid, BI.fld_gprodid, SUM(BI.fld_gqty) as goutgoing");
			$this->db->from("tbl_billing BI");
			$this->db->where("BI.fld_status!='cancelled'");
			$this->db->where("BI.fld_del","0");
			$this->db->where('BI.fld_clientid',$this->clientid);
			$this->db->where_in("BI.fld_outletid", $outletids);
			$this->db->group_by("BI.fld_outletid, BI.fld_gprodid");
			$query6 = $this->db->get();
			//echo "<br>" . $this->db->last_query();
			if($query6->num_rows()>0)
			{
				foreach($query6->result() as $row6)
				{
					if(!$arroutletids[$row6->fld_outletid]) $arroutletids[$row6->fld_outletid] = $row6->fld_outletid;
					$arroutletoutgoinggiftsbilling[$row6->fld_outletid][$row6->fld_gprodid] = $row6->goutgoing;
				}
			}
			//echo "out via gifts";
			//predata($arroutletoutgoinggiftsbilling);
					
			### stock from other outlet incoming
			$this->db->select("TI3.fld_outletid, TII3.fld_productid, SUM(TII3.fld_qty) as oincomingqty");
			$this->db->from("tbl_transactioninfo TII3");
			$this->db->join("tbl_transaction TI3", "TII3.fld_transactionid=TI3.fld_id");
			//9-aug $this->db->where("(TI3.fld_status='completed' or TI3.fld_status='delivered')");
			$this->db->where("TI3.fld_status", "completed");
			$this->db->where("(TI3.fld_type='o2o')");
			$this->db->where("TI3.fld_del", "0");
			$this->db->where('TI3.fld_clientid',$this->clientid);
			$this->db->where_in("TI3.fld_outletid", $outletids);
			$this->db->group_by("TI3.fld_outletid, TII3.fld_productid");
			$query3 = $this->db->get();
			//echo "<br>" . $this->db->last_query();
			if($query3->num_rows()>0)
			{
				foreach($query3->result() as $row3)
				{
					if(!$arroutletids[$row3->fld_outletid]) $arroutletids[$row3->fld_outletid] = $row3->fld_outletid;
					$arroutletincomingfromoutlet[$row3->fld_outletid][$row3->fld_productid] = $row3->oincomingqty;
				}
			}
			//echo "incoming from other outlet";
			//predata($arroutletincomingfromoutlet);
			
			### stock from outlet outgoing to warehouse
			$this->db->select("TI4.fld_fromoutletid, TII4.fld_productid, SUM(TII4.fld_qty) as ooutgoingqty");
			$this->db->from("tbl_transactioninfo TII4");
			$this->db->join("tbl_transaction TI4", "TII4.fld_transactionid=TI4.fld_id");
			$this->db->where("(TI4.fld_status='completed' or TI4.fld_status='delivered')");
			$this->db->where("(TI4.fld_type='o2o' or TI4.fld_type='o2w')");
			$this->db->where("TI4.fld_del", "0");
			$this->db->where('TI4.fld_clientid',$this->clientid);
			$this->db->where_in("TI4.fld_fromoutletid", $outletids);
			$this->db->group_by("TI4.fld_fromoutletid, TII4.fld_productid");
			$query4 = $this->db->get();
			//echo "<br>" . $this->db->last_query();
			if($query4->num_rows()>0)
			{
				foreach($query4->result() as $row4)
				{
					if(!$arroutletids[$row4->fld_fromoutletid]) $arroutletids[$row4->fld_fromoutletid] = $row4->fld_fromoutletid;
					$arroutletoutgoingtowarehouse[$row4->fld_fromoutletid][$row4->fld_productid] = $row4->ooutgoingqty;
				}
			}
			//echo "outgoing to warehouse and other outlet";
			//predata($arroutletoutgoingtowarehouse);
			## end of outlet stock
			
			## get stock by outlet list
			$this->db->distinct();
			$this->db->select("OI.fld_id, OI.fld_name");
			$this->db->from("tbl_outlets OI");
			$this->db->join("tbl_transaction TI", "OI.fld_id=TI.fld_outletid", "left");
			$this->db->join("tbl_transaction TI02", "OI.fld_id=TI02.fld_fromoutletid", "left");
			$this->db->where_in("OI.fld_id", $outletids);
			$this->db->order_by("OI.fld_name", "asc");
			$result02 = $this->db->get();
			if($result02->num_rows()>0)
			{
				while ($row02 = $result02->unbuffered_row())
				{
					$outletid = $row02->fld_id;
					$arroutlets[$outletid] = $row02->fld_name;
				}
			}
		}

		$arrstockdata = array();
		$this->db->select("fld_id, fld_name,fld_minstockvalue, fld_noofbottlesincase");
		$this->db->where("fld_status", "active");
		$this->db->where("fld_del", "0");
		$this->db->order_by("fld_name", "asc");
		$result = $this->db->get("tbl_products");
		if($result->num_rows()>0)
		{
			$arrproduct = array("Product Name", "Total Qty.");
			$arrheading = array_merge($arrproduct, $arrwarehouses, $arroutlets);
			
			$filename = "ALLStockStatus-". date("YmdHis") . ".csv";
			ob_end_clean();
			// Set headers for file name, MIME type, and caching
			header("Content-type: application/csv");
			header("Content-Disposition: attachment; filename=\"".$filename.".csv\"");
			header("Pragma: no-cache");
			header("Expires: 0");
			$fh = fopen('php://output', 'w');
			
			fputcsv($fh, $arrheading);
			
			while ($row = $result->unbuffered_row())
			{
				$totalquantity = 0;
				$ix++;
				$prodid = $row->fld_id;
				$name = $row->fld_name;
				$minstockvalue = $row->fld_minstockvalue;
				$bottlesincase = $row->fld_noofbottlesincase;
				
				$arrproductrow = array();
				$arrproductrow[0] = $name;
				$arrproductrow[] = 0;
				$arrtempwarehouses = $arrwarehouses;
				foreach($arrtempwarehouses as $warehouseid=>$kvalue)
				{
					$warehouseincoming = 0;
					if($arrwarehouseincomingviapurchasenoutlet[$warehouseid][$prodid])
					{
						$warehouseincoming += $arrwarehouseincomingviapurchasenoutlet[$warehouseid][$prodid];
					}
					if($arrwarehouseincomingfromotherwarehouse[$warehouseid][$prodid])
					{
						$warehouseincoming += $arrwarehouseincomingfromotherwarehouse[$warehouseid][$prodid];
					}
					$warehouseoutgoing = 0;
					if($arrwarehouseoutgoing[$warehouseid][$prodid])
					{
						 $warehouseoutgoing += $arrwarehouseoutgoing[$warehouseid][$prodid];
					}
					$warehouseinstockqtyinbottles = $warehouseincoming - $warehouseoutgoing;
					//$warehouseinstockqtyincases = round(($warehouseinstockqtyinbottles / $bottlesincase ), 2);
					//$arrproductrow[$warehouseid] = $warehouseinstockqtyinbottles;
					$arrproductrow[] = $warehouseinstockqtyinbottles;
					$totalquantity += $warehouseinstockqtyinbottles;
				}
				
				$arrtempoutlets = $arroutlets;
				foreach($arrtempoutlets as $outletid=>$outletname)
				{
					$outletincoming = 0;
					if($arroutletincomingfromwarehouse[$outletid][$prodid]) $outletincoming += $arroutletincomingfromwarehouse[$outletid][$prodid];
					if($arroutletincomingfromoutlet[$outletid][$prodid]) $outletincoming += $arroutletincomingfromoutlet[$outletid][$prodid];
					
					$outletoutgoing = 0;
					if($arroutletoutgoingbilling[$outletid][$prodid]) $outletoutgoing += $arroutletoutgoingbilling[$outletid][$prodid];
					if($arroutletoutgoingtowarehouse[$outletid][$prodid]) $outletoutgoing += $arroutletoutgoingtowarehouse[$outletid][$prodid];
					if($arroutletoutgoinggiftsbilling[$outletid][$prodid]) $outletoutgoing += $arroutletoutgoinggiftsbilling[$outletid][$prodid];
					$instockqtyinbottles = $outletincoming - $outletoutgoing;
					//$instockqtyincases = round(($instockqtyinbottles / $bottlesincase ), 2);
					//$arrproductrow[$outletid] = $warehouseinstockqtyinbottles;
					$arrproductrow[] = $instockqtyinbottles;
					$totalquantity += $instockqtyinbottles;
				}
				$arrproductrow[1] = $totalquantity;
				fputcsv($fh, $arrproductrow);
			}
			fclose($fh);
		}
	}
		 
	function managestock($posteddata,$stocktype)
	{
		$returnvalue = 0;		
		$rsrcid = $posteddata["rsrcid"];
		$vendorid = $posteddata["vendorid"];
		$outletid = $posteddata["outletid"];
		$warehouseid = $posteddata["warehouseid"];
		$towarehouseid = $posteddata["towarehouseid"];
		$refno = $posteddata["refno"];
		$refdate = $posteddata["refdate"];		
		$transactioninfoids = $posteddata["transactioninfoids"];		
		$productid = $posteddata["productid"];
		$qty_case = $this->input->post("qty_case");
		$qty_bottle = $this->input->post("qty_bottle");
		$price_case = $this->input->post("price_case");
		$price_bottle = $this->input->post("price_bottle");
		$subtotal = $this->input->post("subtotal");
		$vatamount = $this->input->post("vatamount");
		$tscamount = $this->input->post("tscamount");
		$discount = $this->input->post("discount");
		$adjustment = $this->input->post("adjustment");
		$remarks = $this->input->post("remarks");
		$productttotalamount = $posteddata["productttotalamount"];
		$producttotalrows = $posteddata["producttotalrows"];
		
		if($stocktype=='stockin')
		{
			$outletid =0;
			$status ='completed';
		}
		else
		{
			$productttotalamount=0;
			$vendorid = 0;
			$status ='delivered';
			$vatamount = 0;
			$tscamount = 0;
			$discount = 0;
			$adjustment = 0;
		}
		if($towarehouseid=="") $towarehouseid = 0;
		
		$data = array(
			"fld_clientid" => $this->clientid,
			"fld_type" => $stocktype,
			"fld_vendorid" => $vendorid,
			"fld_warehouseid" => $warehouseid,
			"fld_towarehouseid" => $towarehouseid,
			"fld_outletid" => $outletid,
			"fld_userid" => $this->userid,
			"fld_refno" => $refno,
			"fld_refdate" => $refdate,
			"fld_remarks" => $remarks,	
			"fld_amount" => $productttotalamount,	
			"fld_vatamount" => $vatamount,	
			"fld_tscamount" => $tscamount,	
			"fld_discount" => $discount,	
			"fld_adjustment" => $adjustment,	
			"fld_updatedby" => $this->lguserinfo["sessID"],
			"fld_updatedon" => date("Y-m-d H:i:s"),			
			"fld_status" => $status
		);
		//predata($data); exit;

		$executefurther = 1;
		// check already exists invoice no. for purchase
		if($stocktype=='stockin')
		{ 
			$this->db->select("fld_id");
			$this->db->where("fld_refno", $data["fld_refno"]);
			$this->db->where("fld_clientid", $data["fld_clientid"]);
			$this->db->where("fld_vendorid", $data["fld_vendorid"]);	
			$this->db->where("fld_del", 0);		
			if($rsrcid>0) // check
			{
				$this->db->where("fld_id!=$rsrcid");
			}
			$results = $this->db->get('tbl_transaction');
			if($results->num_rows()>0) // already exists
			{
				$returnvalue = -1;
				$executefurther = 0;
			}
		}
		if($executefurther>0)
		{
			if($rsrcid=="" || $rsrcid==0) // for new
			{
				$data["fld_datetime"] = date("Y-m-d H:i:s");
				$this->db->insert("tbl_transaction", $data);
				$returnvalue = $this->db->insert_id();
				if($stocktype!='stockin')
				{
					$data11 = array("fld_refno" => $returnvalue, "fld_refdate" => date("Y-m-d"));
					$this->db->where("fld_id", $returnvalue);
					$this->db->update("tbl_transaction", $data11);
				}
			}
			else // for existings
			{
				$this->db->where("fld_id",$rsrcid);
				$this->db->update("tbl_transaction", $data);
				$returnvalue = $rsrcid;

			}
			if($returnvalue>0) // add/update info. table
			{	
				if($producttotalrows>0)
				{
					## delete existing data to 0 for qty
					$dataold = array(
						"fld_qty" => "0",
						"fld_qty_case" => "0",
						"fld_qty_bottle" => "0"
					);
					$this->db->where("fld_transactionid", $returnvalue);
					$this->db->update("tbl_transactioninfo", $dataold);
				
					for($i=0;$i<$producttotalrows;$i++)
					{
						$transactioninfoid = $transactioninfoids[$i];
						$prodid = $productid[$i];
						$nprice_case = $price_case[$i];
						$nprice_bottle = $price_bottle[$i];
						$nsubtotal= $subtotal[$i];
						
						if($stocktype=='stockout')
						{
							$prodids = explode("_",$productid[$i]);
							$prodid = $prodids[0];
							$nprice_case = 0;
							$nprice_bottle = 0;
							$nsubtotal= 0;
						}
						$noofbottlesincase = 0;
						$this->db->select("fld_noofbottlesincase");
						$this->db->where("fld_id", $prodid);
						$results = $this->db->get("tbl_products");
						if($results->num_rows()>0) // update if found
						{
							$row = $results->row();
							$noofbottlesincase =$row->fld_noofbottlesincase;
						}
						$qty = $qty_bottle[$i]+($qty_case[$i]*$noofbottlesincase); 
						
						## store stock data
						if($prodid>0 && $qty>=0)
						{
							if($nprice_case=="") $nprice_case = 0;
							if($nprice_bottle=="") $nprice_bottle = 0;
							if($nsubtotal=="") $nsubtotal = 0;
							$databkinfo = array(
								"fld_transactionid" => $returnvalue,
								"fld_productid" => $prodid,
								"fld_qty_case" => $qty_case[$i],
								"fld_qty_bottle" => $qty_bottle[$i],
								"fld_qty" => $qty,
								"fld_price_case" => $nprice_case,
								"fld_price_bottle" => $nprice_bottle,
								"fld_subtotal" => $nsubtotal
								
							);
							
							## check for data exists for update or insert
							$this->db->select("count(*) as recfound");
							$this->db->where("fld_productid", $prodid);
							$this->db->where("fld_transactionid", $returnvalue);
							$result55 = $this->db->get("tbl_transactioninfo");
							$recfound = $result55->row()->recfound;
							if($recfound>0)
							{
								$this->db->where("fld_productid", $prodid);
								$this->db->where("fld_transactionid", $returnvalue);
								$this->db->update("tbl_transactioninfo", $databkinfo);
							}
							else
							{
								$this->db->insert("tbl_transactioninfo", $databkinfo);
							}
						}
					}
				}						
			}
		}
		return $returnvalue;
	}

	function managestockoutlet($posteddata,$stocktype)
	{
		$returnvalue = 0;
		$rsrcid = $posteddata["rsrcid"];
		$fromoutletid = $posteddata["fromoutletid"];
		$outletid = $posteddata["tooutletid"];
		$warehouseid = $posteddata["warehouseid"];
		if($stocktype=='o2o')
		{
			$warehouseid =0;
		}
		elseif($stocktype=='o2w')
		{
			$outletid =0;
		}
		$refno = $posteddata["refno"];
		$refdate = $posteddata["refdate"];
		
		$transactioninfoids = $posteddata["transactioninfoids"];
		
		$productid = $posteddata["productid"];
		$qty_case = $this->input->post("qty_case");
		$qty_bottle = $this->input->post("qty_bottle");
		$price_case = $this->input->post("price_case");
		$price_bottle = $this->input->post("price_bottle");
		$subtotal = $this->input->post("subtotal");
		$productttotalamount = $posteddata["productttotalamount"];
		$producttotalrows = $posteddata["producttotalrows"];
		//predata($_REQUEST); exit;
		$data = array(
			"fld_clientid" => $this->clientid,
			"fld_type" => $stocktype,
			"fld_vendorid" => 0,
			"fld_warehouseid" => $warehouseid,
			"fld_outletid" => $outletid,
			"fld_userid" => $this->userid,
			"fld_fromoutletid" => $fromoutletid,
			"fld_refno" => $refno,
			"fld_refdate" => $refdate,			
			"fld_amount" => 0,	
			"fld_vatamount" => 0,	
			"fld_tscamount" => 0,	
			"fld_discount" => 0,	
			"fld_adjustment" => 0,
			"fld_updatedby" => $this->lguserinfo["sessID"],
			"fld_updatedon" => date("Y-m-d H:i:s"),			
			"fld_status" => 'delivered'
		);

		// cehck already exits chalaln no. 
		$this->db->select("fld_id");
		$this->db->where("fld_refno", $data["fld_refno"]);
		$this->db->where("fld_clientid", $data["fld_clientid"]);
		$this->db->where("fld_fromoutletid", $data["fld_fromoutletid"]);
		$this->db->where("fld_type", $data["fld_type"]);		
		$this->db->where("fld_del", 0);		
		if($rsrcid>0) // check
		{
			$this->db->where("fld_id!=$rsrcid");
		}
		$results = $this->db->get('tbl_transaction');
		//echo $this->db->last_query();
		if($results->num_rows()>0) // already exists
		{
			$returnvalue = -1;
		}
		else
		{

			if($rsrcid=="" || $rsrcid==0) // for new
			{
				$data["fld_datetime"] = date("Y-m-d H:i:s");
				$this->db->insert("tbl_transaction", $data);
				$returnvalue = $this->db->insert_id();
				$data11 = array("fld_refno" => $returnvalue, "fld_refdate" => date("Y-m-d"));
				$this->db->where("fld_id", $returnvalue);
				$this->db->update("tbl_transaction", $data11);
			}
			else // for existings
			{
				$this->db->where("fld_id",$rsrcid);
				$this->db->update("tbl_transaction", $data);
				$returnvalue = $rsrcid;

			}
			if($returnvalue>0) // add/update info. table
			{	
				if($producttotalrows>0)
				{
					for($i=0;$i<$producttotalrows;$i++)
					{
						$transactioninfoid = $transactioninfoids[$i];
						$prodid = $productid[$i];
						$price_case = 0;
						$price_bottle = 0;
						$subtotal= 0;
						$noofbottlesincase = 0;
						$this->db->select("fld_noofbottlesincase");
						$this->db->where("fld_id", $prodid);
						$results = $this->db->get("tbl_products");
						if($results->num_rows()>0) // update if found
						{
							$row = $results->row();
							$noofbottlesincase =$row->fld_noofbottlesincase;
						}
						$qty = $qty_bottle[$i]+($qty_case[$i]*$noofbottlesincase); 
						if($prodid>0 && $qty>=0)
						{
							$databkinfo = array(
								"fld_transactionid" => $returnvalue,
								"fld_productid" => $prodid,
								"fld_qty_case" => $qty_case[$i],
								"fld_qty_bottle" => $qty_bottle[$i],
								"fld_qty" => $qty,
								"fld_price_case" => $price_case,
								"fld_price_bottle" => $price_bottle,
								"fld_subtotal" => $subtotal
								
							);
							
							if($transactioninfoid=="" || $transactioninfoid==0)
							{
								$this->db->insert("tbl_transactioninfo", $databkinfo);
							}
							else
							{
								$this->db->where("fld_id", $transactioninfoid);
								$this->db->update("tbl_transactioninfo", $databkinfo);
							}
						}
						
		
					}
				}
			}
		}
		
		return $returnvalue;
	}

	function getstockinfo($transactionid, $stocktype)
	{
		$stockinfo = array();
		
		if($stocktype=='stockout')
		{
			$this->db->select("TI.*,WI.fld_name as warehousename,OI.fld_name as ouletname");
		}
		elseif($stocktype=='o2o')
		{
			$this->db->select("TI.*, OI.fld_name as ouletname, OI02.fld_name as fromouletname");
		}
		elseif($stocktype=='o2w')
		{
			$this->db->select("TI.*, OI.fld_name as ouletname, WI.fld_name as warehousename");
		}
		elseif($stocktype=='w2w')
		{
			$this->db->select("TI.*, WI.fld_name as warehousename, WI02.fld_name as towarehousename");
		}
		else
		{
			$this->db->select("TI.*,VI.fld_name as vendorname,WI.fld_name as warehousename");
		}

		$this->db->from("tbl_transaction TI");
		
		$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
		if($stocktype=='stockout')
		{
			$this->db->join("tbl_outlets OI", "OI.fld_id=TI.fld_outletid",'left');
		}
		elseif($stocktype=='o2o')
		{
			$this->db->join("tbl_outlets OI", "TI.fld_outletid=OI.fld_id",'left');
			$this->db->join("tbl_outlets OI02", "TI.fld_fromoutletid=OI02.fld_id",'left');
		}
		elseif($stocktype=='o2w')
		{
			$this->db->join("tbl_outlets OI", "TI.fld_fromoutletid=OI.fld_id",'left');
		}
		elseif($stocktype=='w2w')
		{
			$this->db->join("tbl_warehouse WI02", "TI.fld_towarehouseid=WI02.fld_id", "left");
		}
		else
		{
			$this->db->join("tbl_vendors VI", "TI.fld_vendorid=VI.fld_id");
		}
		$this->db->where("TI.fld_id", $transactionid);
		$this->db->where("TI.fld_clientid", $this->clientid);
		$this->db->where('TI.fld_del', '0');
		$result = $this->db->get();
		if($result->num_rows()>0)
		{
			$row = $result->row();
			$stockinfo["main"] = $row;
			
			$this->db->select("TII.*, PI.fld_name as productname");
			$this->db->from("tbl_transactioninfo TII");
			$this->db->join("tbl_products PI", "TII.fld_productid=PI.fld_id");
			$this->db->where("TII.fld_transactionid", $transactionid);
			$result2 = $this->db->get();
			if($result2->num_rows()>0)
			{
				foreach($result2->result() as $row2)
				{
					$stockinfo["info"][] = $row2;					
				}
			}
		}
		return $stockinfo;
	}
	
	function getwarehousestockinfo($warehouseid)
	{
		$this->db->select("P.fld_name,P.fld_name,TI.fld_productid,SUM( Case When T.fld_type= 'stockout' and fld_warehouseid='".$warehouseid."' then TI.fld_qty end) as outgoing,
				SUM( Case When T.fld_type= 'stockin' and fld_warehouseid='".$warehouseid."' then TI.fld_qty end) as incoming");		
		
		$this->db->from("tbl_products P");
		$this->db->join("tbl_transactioninfo TI", "TI.fld_productid=P.fld_id");		
		$this->db->join("tbl_transaction T", "TI.fld_transactionid=T.fld_id");
		$this->db->where("T.fld_warehouseid", $warehouseid);
		$this->db->where("T.fld_clientid", $this->clientid);
		$this->db->where('T.fld_del', '0');
		$this->db->group_by('TI.fld_productid,P.fld_name');
		$results = $this->db->get();		
		return $results->result();

		
	}

	function checkProductStock($warehouseid,$productids,$qty_case,$qty_bottle,$rsrcid)
	{
		$arr = array();
		$producttotalrows = count($productids);
		if($producttotalrows>0)
		{
			$arractproducts = array();
			for($i=0;$i<$producttotalrows;$i++)
			{
				if($arractproducts[$productids[$i]]) // if exists then add
				{
					$arractproducts[$productids[$i]]["cases"] += $qty_case[$i];
					$arractproducts[$productids[$i]]["bottles"] += $qty_bottle[$i];
				}
				else
				{
					$arractproducts[$productids[$i]]["cases"] = $qty_case[$i];
					$arractproducts[$productids[$i]]["bottles"] = $qty_bottle[$i];
				}
			}
			//for($i=0;$i<$producttotalrows;$i++)
			$i = -1;
			foreach($arractproducts as $vkey=>$kvalue)
			{
				$prodids = explode("_",$vkey);
				$prodid = $prodids[0];
				$this->db->select("fld_noofbottlesincase");
				$this->db->where("fld_id", $prodid);
				$results = $this->db->get("tbl_products");
				if($results->num_rows()>0) // update if found
				{
					$row = $results->row();
					$noofbottlesincase =$row->fld_noofbottlesincase;
				}
				$qty = $kvalue["bottles"]+($kvalue["cases"]*$noofbottlesincase);
						
				$this->db->select("fld_name");
				$this->db->where('fld_id',$prodid);
				$this->db->where('fld_status','active');
				$results = $this->db->get('tbl_products');				
				if($results -> num_rows() > 0)
				{
					$resultsinfo = $results->result();				
					if(is_array($resultsinfo) && count(array_filter($resultsinfo))>0)
					{					
						foreach($resultsinfo as $row)
						{		
							$incoming=0;
							$outgoing=0;
							$instock=0;
							### stock from warehouse incoming
							$this->db->select("SUM(TII2.fld_qty) as wincomingqty");
							$this->db->from("tbl_transactioninfo TII2");
							$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
							$this->db->where("TII2.fld_productid=",$prodid);						
							//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
							$this->db->where("TI2.fld_status", "completed");
							$this->db->where("(TI2.fld_type='stockin' or TI2.fld_type='o2w')");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_warehouseid',$warehouseid);
							$this->db->where("TI2.fld_del", "0");
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$wincomingqty = $row2->wincomingqty;
							$incoming += $wincomingqty;
							
							### stock received from other warehouse
							$this->db->select("SUM(TII2.fld_qty) as wincomingqty02");
							$this->db->from("tbl_transactioninfo TII2");
							$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
							$this->db->where("TII2.fld_productid=",$prodid);						
							//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
							$this->db->where("TI2.fld_status", "completed");
							$this->db->where("(TI2.fld_type='w2w')");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_towarehouseid',$warehouseid);
							$this->db->where("TI2.fld_del", "0");
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$wincomingqty02 = $row2->wincomingqty02;
							$incoming += $wincomingqty02;

							### stock from warehouse outgoing
							$this->db->select("SUM(TII2.fld_qty) as ooutgoingqty");
							$this->db->from("tbl_transactioninfo TII2");
							$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
							$this->db->where("TII2.fld_productid=",$prodid);						
							$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
							$this->db->group_start();
							$this->db->where("TI2.fld_type", "stockout");
							$this->db->or_where("TI2.fld_type", "w2w");
							$this->db->group_end();
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_warehouseid',$warehouseid);
							$this->db->where('TI2.fld_id!='.$rsrcid.'');
							$this->db->where("TI2.fld_del", "0");
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$ooutgoingqty = $row2->ooutgoingqty;
							$outgoing += $ooutgoingqty;	
							

							$instock = ($incoming-$outgoing);
							$stockval = ($instock-$qty);
							//echo $instock . " : " . $incoming . " : " . $outgoing . " => " . $stockval;
							//exit;
							if($stockval<0)// minus stocks
							{
								$i++;
								$arr[$i]['id'] = $prodid;
								$arr[$i]['name'] = $row->fld_name;
								$arr[$i]['statusval'] = $stockval;
							}
							
						}
					}
				
				}				
			}
			return $arr;
		}
		
		return $productinfo;
	}

	function checkProductStockInOutlet($outletid,$productids,$qty_case,$qty_bottle,$rsrcid)
	{
		$arr = array();
		$producttotalrows = count($productids);
		if($producttotalrows>0)
		{
			$arractproducts = array();
			for($i=0;$i<$producttotalrows;$i++)
			{
				if($arractproducts[$productids[$i]]) // if exists then add
				{
					$arractproducts[$productids[$i]]["cases"] += $qty_case[$i];
					$arractproducts[$productids[$i]]["bottles"] += $qty_bottle[$i];
				}
				else
				{
					$arractproducts[$productids[$i]]["cases"] = $qty_case[$i];
					$arractproducts[$productids[$i]]["bottles"] = $qty_bottle[$i];
				}
			}
			//for($i=0;$i<$producttotalrows;$i++)
			$i = -1;
			foreach($arractproducts as $vkey=>$kvalue)
			{
				$prodids = explode("_",$vkey);
				$prodid = $prodids[0];
				$this->db->select("fld_noofbottlesincase");
				$this->db->where("fld_id", $prodid);
				$results = $this->db->get("tbl_products");
				if($results->num_rows()>0) // update if found
				{
					$row = $results->row();
					$noofbottlesincase =$row->fld_noofbottlesincase;
				}
				$qty = $kvalue["bottles"]+($kvalue["cases"]*$noofbottlesincase);
				
				$this->db->select("fld_name");
				$this->db->where('fld_id',$prodid);
				$this->db->where('fld_status','active');
				$results = $this->db->get('tbl_products');
				//echo $this->db->last_query();
				if($results -> num_rows() > 0)
				{
					$resultsinfo = $results->result();				
					if(is_array($resultsinfo) && count(array_filter($resultsinfo))>0)
					{					
						foreach($resultsinfo as $row)
						{		
							$incoming=0;
							$outgoing=0;
							$instock=0;
							### stock from warehouse incoming
							$this->db->select("SUM(TII2.fld_qty) as wincomingqty");
							$this->db->from("tbl_transactioninfo TII2");
							$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
							$this->db->where("TII2.fld_productid=",$prodid);						
							//9-aug $this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
							$this->db->where("TI2.fld_status", "completed");
							$this->db->where("(TI2.fld_type='stockout')");
							$this->db->where("TI2.fld_del", "0");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_outletid',$outletid);
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$wincomingqty = $row2->wincomingqty;
							$incoming += $wincomingqty;
							

							### stock out from billing outgoing
							$this->db->select("SUM(TII5.fld_qty) as boutgoing");
							$this->db->from("tbl_billinginfo TII5");
							$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
							$this->db->where("TII5.fld_productid=",$prodid);
							$this->db->where("TI5.fld_status!=",'cancelled');
							$this->db->where('TI5.fld_clientid',$this->clientid);
							$this->db->where('TI5.fld_outletid',$outletid);
							$this->db->group_by('TII5.fld_productid');
							$query5 = $this->db->get();
							$row5 = $query5->row();
							$boutgoing = $row5->boutgoing;
							$outgoing += $boutgoing;

					
							### stock from outlet incoming
							$this->db->select("SUM(TII3.fld_qty) as oincomingqty");
							$this->db->from("tbl_transactioninfo TII3");
							$this->db->join("tbl_transaction TI3", "TII3.fld_transactionid=TI3.fld_id");
							$this->db->where("TII3.fld_productid=",$prodid);
							//9-aug $this->db->where("(TI3.fld_status='completed' or TI3.fld_status='delivered')");
							$this->db->where("TI3.fld_status", "completed");
							$this->db->where("(TI3.fld_type='o2o')");
							$this->db->where("TI3.fld_del", "0");
							$this->db->where('TI3.fld_clientid',$this->clientid);
							$this->db->where('TI3.fld_outletid',$outletid);
							$this->db->group_by('TII3.fld_productid');
							$query3 = $this->db->get();
							//echo $this->db->last_query();
							$row3 = $query3->row();
							$oincomingqty = $row3->oincomingqty;
							$incoming += $oincomingqty;

							### stock from outlet outgoing
							$this->db->select("SUM(TII4.fld_qty) as ooutgoingqty");
							$this->db->from("tbl_transactioninfo TII4");
							$this->db->join("tbl_transaction TI4", "TII4.fld_transactionid=TI4.fld_id");
							$this->db->where("TII4.fld_productid=",$prodid);
							$this->db->where("(TI4.fld_status='completed' or TI4.fld_status='delivered')");
							$this->db->where("(TI4.fld_type='o2o' or TI4.fld_type='o2w')");
							$this->db->where('TI4.fld_clientid',$this->clientid);
							$this->db->where('TI4.fld_fromoutletid',$outletid);
							$this->db->where('TI4.fld_id!='.$rsrcid.'');
							$this->db->where("TI4.fld_del", "0");
							$this->db->group_by('TII4.fld_productid');
							$query4 = $this->db->get();
							//echo $this->db->last_query();
							$row4 = $query4->row();
							$ooutgoingqty = $row4->ooutgoingqty;
							$outgoing += $ooutgoingqty;

							$instock = $incoming-$outgoing;
							
							$stockval = ($instock-$qty);

							if($stockval<0)// minus stocks
							{
								$i++;
								$arr[$i]['id'] = $prodid;
								$arr[$i]['name'] = $row->fld_name;
								$arr[$i]['statusval'] = $stockval;

							}
							
						}
					}
				
				}				
			}
			return $arr;
		}
		
		return $productinfo;
	}	

	function getwhuserinfo($warehouseid, $tablename)
	{		
		if($warehouseid>0 && $tablename!="")
		{
			$this->db->select("*");
			$this->db->where("fld_warehouseid", $warehouseid);
			$results = $this->db->get($this->tblprefix.$tablename);
			//echo $this->db->last_query();
			if($results->num_rows()>0)
			{
				$row = $results->row();
				return $row;
			}
			else
			{
				return false;
			}
		}
		else
		{
			return false;
		}
	}
	
	function listproductsOLD($listtype, $params)
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$type = $this->input->get("type");
		$catid = decodeValue($this->input->get("catid"));
		$brandid = decodeValue($this->input->get("brandid"));
		$fromprice = $this->input->get("fromprice");
		$toprice = $this->input->get("toprice");
		$tasktype = $this->uri->segment("1");
		if($tasktype=='outlet')
		{
			$outletid = $this->outletid;
		}
		else
		{
			$outletid = $this->input->get("outletid");
		}
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$addcatparmeter ="";
			if($category=='category')
			{
				$addcatparmeter = ',CI.fld_name as catname';
			}
			if($brand=='brand')
			{
				$addcatparmeter .= ',BI.fld_name as brandname';
			}
			if($outletid>0)
			{
				$this->db->select("PI.*,PP.fld_price, PP.fld_price3bottles, PP.fld_price6bottles, PP.fld_price9bottles, PP.fld_price12bottles,PP.fld_price24bottles $addcatparmeter");
			}
			else
			{
				$this->db->select("PI.* $addcatparmeter");
			}
			
		}
		$this->db->from("tbl_products PI");		
		if($outletid>0)
		{
			$this->db->join("tbl_price PP", "PP.fld_productid=PI.fld_id");
			$this->db->join("tbl_outlets OI", "OI.fld_zoneid=PP.fld_zoneid");
			
			$this->db->where('OI.fld_id',$outletid);
			$this->db->where('PP.fld_clientid',$this->clientid);
			if($fromprice>0 && $fromprice>0)
			{
				$this->db->where("(PP.fld_price>=".$fromprice." and PP.fld_price<=".$toprice.")");
			}
			elseif($fromprice>0)
			{
				$this->db->where('PP.fld_price',$fromprice);
			}
			elseif($toprice>0)
			{				
				$this->db->where('PP.fld_price',$toprice);
			}
		}							

		if($category=='category')
		{
			$this->db->join("tbl_category CI", "CI.fld_id=PI.fld_catid",'left');
		}
		if($brand=='brand')
		{
			$this->db->join("tbl_brand BI", "BI.fld_id=PI.fld_brandid",'left');
		}
		if($type!="")
		{
			$this->db->where('PI.fld_type',$type);
		}
		if($catid>0)
		{
			$this->db->where('PI.fld_catid',$catid);
		}
		if($brandid>0)
		{
			$this->db->where('PI.fld_brandid',$brandid);
		}
		$this->db->where('PI.fld_del', '0');
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("PI.fld_name", $keyword);
			$this->db->or_like("PI.fld_barcode", $keyword);
			$this->db->or_like("PI.fld_barcode02", $keyword);
			$this->db->or_like("PI.fld_barcode03", $keyword);
			$this->db->or_like("PI.fld_barcode04", $keyword);
			$this->db->or_like("PI.fld_noofbottlesincase", $keyword);
			$this->db->group_end();
		}
		$this->db->where('PI.fld_del', '0');
		if($listtype != "count")
		{
			$this->db->order_by('PI.fld_name', 'asc');		
			if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit'],$params['start']);
			}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit']);
			}
		}
		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				## get data from other tables
				$arrminqty = array();
				$arrwarehouseincoming = array();
				$arrbillingoutgoing = array();
				$arroutletincoming = array();
				$arroutletoutgoing = array();
				$arrsaledays = array();
				
				$this->db->select("fld_id, fld_productid, fld_minqty");
				$this->db->where("fld_outletid", $outletid);
				$this->db->where('fld_clientid',$this->clientid);
				$resultsminqty = $this->db->get('tbl_prodminqty');
				//echo $this->db->last_query();
				if($resultsminqty->num_rows()>0)
				{
					foreach($resultsminqty->result() as $rowminqty)
					{
						$arrminqty[$rowminqty->fld_productid]["minqtyid"] = $rowminqty->fld_id;
						$arrminqty[$rowminqty->fld_productid]["minqty"] = $rowminqty->fld_minqty;
					}
				}
				
				### stock from warehouse incoming
				$this->db->select("TII2.fld_productid, SUM(TII2.fld_qty) as wincomingqty");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
				$this->db->where("(TI2.fld_type='stockout')");
				$this->db->where("TI2.fld_del", "0");
				$this->db->where('TI2.fld_clientid',$this->clientid);
				if($outletid>0)
				{
					$this->db->where('TI2.fld_outletid',$outletid);
				}
				$this->db->group_by('TII2.fld_productid');
				$query2 = $this->db->get();
				if($query2->num_rows()>0)
				{
					foreach($query2->result() as $row2)
					{
						$arrwarehouseincoming[$row2->fld_productid] = $row2->wincomingqty;
					}
				}
				
				### stock out from billing outgoing
				$this->db->select("TII5.fld_productid, SUM(TII5.fld_qty) as boutgoing");
				$this->db->from("tbl_billinginfo TII5");
				$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
				$this->db->where("TI5.fld_status!=",'cancelled');
				$this->db->where('TI5.fld_clientid',$this->clientid);
				if($outletid>0)
				{
					$this->db->where('TI5.fld_outletid',$outletid);
				}
				$this->db->group_by('TII5.fld_productid');
				$query5 = $this->db->get();
				if($query5->num_rows()>0)
				{
					foreach($query5->result() as $row5)
					{
						$arrbillingoutgoing[$row5->fld_productid] = $row5->boutgoing;
					}
				}
				
				### stock from outlet incoming
				$this->db->select("TII3.fld_productid, SUM(TII3.fld_qty) as oincomingqty");
				$this->db->from("tbl_transactioninfo TII3");
				$this->db->join("tbl_transaction TI3", "TII3.fld_transactionid=TI3.fld_id");
				$this->db->where("(TI3.fld_status='completed' or TI3.fld_status='delivered')");
				$this->db->where("(TI3.fld_type='o2o')");
				$this->db->where('TI3.fld_clientid',$this->clientid);
				$this->db->where('TI3.fld_outletid',$outletid);
				$this->db->where("TI3.fld_del", "0");
				$this->db->group_by('TII3.fld_productid');
				$query3 = $this->db->get();
				if($query3->num_rows()>0)
				{
					foreach($query3->result() as $row3)
					{
						$arroutletincoming[$row3->fld_productid] = $row3->oincomingqty;
					}
				}
				
				### stock from outlet outgoing
				$this->db->select("TII4.fld_productid, SUM(TII4.fld_qty) as ooutgoingqty");
				$this->db->from("tbl_transactioninfo TII4");
				$this->db->join("tbl_transaction TI4", "TII4.fld_transactionid=TI4.fld_id");
				$this->db->where("(TI4.fld_status='completed' or TI4.fld_status='delivered')");
				$this->db->group_start();
				$this->db->where("(TI4.fld_type='o2o')");
				$this->db->or_where("(TI4.fld_type='o2w')");
				$this->db->group_end();
				$this->db->where('TI4.fld_clientid',$this->clientid);
				$this->db->where('TI4.fld_fromoutletid',$outletid);
				$this->db->where("TI4.fld_del", "0");
				$this->db->group_by('TII4.fld_productid');
				$query4 = $this->db->get();
				if($query4->num_rows()>0)
				{
					foreach($query4->result() as $row4)
					{
						$arroutletoutgoing[$row4->fld_productid] = $row4->ooutgoingqty;
					}
				}
				
				
				if($salebydays!="")
				{
					$totalqty = 0;
					$saleamount = 0;
					$previousstockdate  ="";
					$saleedate="";
					$sdayscnt = explode(",",$salebydays);
					if(count($sdayscnt)>0)
					{
						for($i=0;$i<count($sdayscnt);$i++)
						{
							
							$salesdate = date('Y-m-d', strtotime('-'.$sdayscnt[$i].' day'));
							$saleedate = date('Y-m-d');
							### stock out from billing outgoing
							//$this->db->select("SUM(TII5.fld_price) as saleamount");
							$this->db->select("TII5.fld_productid, SUM(TII5.fld_qty) as totalqty");
							$this->db->from("tbl_billinginfo TII5");
							$this->db->join("tbl_billing TI5", "TII5.fld_billingid=TI5.fld_id");
							$this->db->where("TI5.fld_status!=",'cancelled');
							$this->db->where('TI5.fld_clientid',$this->clientid);
							if($outletid>0)
							{
								$this->db->where('TI5.fld_outletid',$outletid);
							}
							if($salesdate!="" && $saleedate!="")
							{		
								$this->db->where("(DATE_FORMAT(TI5.fld_datetime,'%Y-%m-%d') >= '".$salesdate."' and DATE_FORMAT(TI5.fld_datetime,'%Y-%m-%d') <= '".$saleedate."')");
							}
							$this->db->group_by('TII5.fld_productid');
							$query7 = $this->db->get();
							if($query7->num_rows()>0)
							{
								foreach($query7 as $row7)
								{
									$totalqty = 0;
									if($row7->totalqty>0) $totalqty = round($row7->totalqty);
									$arrsaledays[$row7->fld_productid][$sdayscnt[$i].'dayssale'] = $totalqty;
								}
							}
						}
					}
				}
				## end of get data from other tables
				
				//return $results->result();
				$resultsinfo = $results->result();
				$arr = array();
				if(is_array($resultsinfo) && count(array_filter($resultsinfo))>0)
				{					
					foreach($resultsinfo as $row)
					{
						$prodid = $row->fld_id;

						$arr[$prodid] = array(
							"id" => $prodid,
							"type" => $row->fld_type,
							"name" => $row->fld_name,
							"barcode" => $row->fld_barcode,
							"barcode02" => $row->fld_barcode02,
							"barcode03" => $row->fld_barcode03,
							"barcode04" => $row->fld_barcode04,
							"noofbottlesincase" => $row->fld_noofbottlesincase,
							"status" => $row->fld_status,	
							"catid" => $row->fld_catid,							
							"brandid" => $row->fld_brandid,
							"minstockvalue" => $row->fld_minstockvalue
						);
						if($outletid>0)
						{
							$minqty = 0;
							if($arrminqty[$prodid])
							{
								$minqty = $arrminqty[$prodid]["minqty"];
								$arr[$prodid]["minqtyid"] = $arrminqty[$prodid]["minqtyid"];
							}
							$arr[$prodid]["minqty"] = $minqty;

						}
						if($category=='category')
						{
							$arr[$prodid]["catname"] = $row->catname;
						}
						if($brand=='brand')
						{
							$arr[$prodid]["brandname"] = $row->brandname;
						}
												
						if($price=='price')
						{		
								### Price
								$arr[$prodid]["price1"] = 0;
								$arr[$prodid]["price3"] = 0;
								$arr[$prodid]["price6"] = 0;
								$arr[$prodid]["price9"] = 0;
								$arr[$prodid]["price12"] = 0;
								$arr[$prodid]["price24"] = 0;

								if($row->fld_price>0)
								{
									$arr[$prodid]["price1"] = round($row->fld_price,2);
								}
								if($row->fld_price3bottles>0)
								{
									$arr[$prodid]["price3"] = round($row->fld_price3bottles,2);
								}
								if($row->fld_price6bottles>0)
								{
									$arr[$prodid]["price6"] = round($row->fld_price6bottles,2);
								}
								if($row->fld_price9bottles>0)
								{
									$arr[$prodid]["price9"] = round($row->fld_price9bottles,2);
								}
								if($row->fld_price12bottles>0)
								{
									$arr[$prodid]["price12"] = round($row->fld_price12bottles,2);
								}
								if($row->fld_price24bottles>0)
								{
									$arr[$prodid]["price24"] = round($row->fld_price24bottles,2);
								}
							
						}
						if($stock=='stock')
						{
							$arr[$prodid]["incoming"] = 0;
							$arr[$prodid]["outgoing"] = 0;
							$arr[$prodid]["instock"] = 0;

							$wincomingqty = 0;
							if($arrwarehouseincoming[$prodid]) $wincomingqty = $arrwarehouseincoming[$prodid];
							$arr[$prodid]["incoming"] += $wincomingqty;

							$boutgoing = 0;
							if($arrbillingoutgoing[$prodid]) $boutgoing = $arrbillingoutgoing[$prodid];
							$arr[$prodid]["outgoing"] += $boutgoing;
					
							$oincomingqty = 0;
							if($arroutletincoming[$prodid]) $oincomingqty = $arroutletincoming[$prodid];
							$arr[$prodid]["incoming"] += $oincomingqty;
							
							$ooutgoingqty = 0;
							if($arroutletoutgoing[$prodid]) $ooutgoingqty = $arroutletoutgoing[$prodid];
							$arr[$prodid]["outgoing"] += $ooutgoingqty;
							
							$arr[$prodid]["instock"] = abs($arr[$prodid]["incoming"]-$arr[$prodid]["outgoing"]);
							if($salebydays!="")
							{
								
								$totalqty = 0;
								$saleamount = 0;
								$previousstockdate  ="";
								$saleedate="";
								$sdayscnt = explode(",",$salebydays);
								if(count($sdayscnt)>0)
								{
									for($i=0;$i<count($sdayscnt);$i++)
									{
										$arr[$prodid][$sdayscnt[$i].'dayssale'] = 0;
										if($arrsaledays[$prodid][$sdayscnt[$i].'dayssale']) $arr[$prodid][$sdayscnt[$i].'dayssale'] = $arrsaledays[$prodid][$sdayscnt[$i].'dayssale'];
									}
								}
							}
						}
					}
					
				}
				return $arr;
			}
		}
	}

	function listproducts($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$zoneid = decodeValue($this->input->get("zoneid"));
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("PI.*, BI.fld_name as brandname, CI.fld_name as categoryname");
		}
		$this->db->from("tbl_products PI");
		$this->db->join("tbl_category CI", "PI.fld_catid=CI.fld_id", "left");
		$this->db->join("tbl_brand BI", "PI.fld_brandid=BI.fld_id", "left");		
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("PI.fld_name", $keyword);
			$this->db->or_like("BI.fld_name", $keyword);
			$this->db->or_like("CI.fld_name", $keyword);
			$this->db->or_like("PI.fld_type", $keyword);
			$this->db->group_end();
		}

		if($listtype != "count")
		{
			$this->db->order_by('PI.fld_name', 'asc');		
			if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit'],$params['start']);
			}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit']);
			}
		}
		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}	

	function listtransfers($listtype, $params)
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");		
		$prodname = $this->input->get("prodname");		
		$warehouseid = $this->input->get("warehouseid");
		$outletid = $this->input->get("outletid");
		$fromdt = $this->input->get("fromdate");
		$todt = $this->input->get("todate");
		if($fromdt!="")
		{
			 $fromdate = convertdateFormat($fromdt,'Y-m-d');
		}
		if($todt!="")
		{
			$todate = convertdateFormat($todt,'Y-m-d');
		}
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("TI.*,WI.fld_name as warehousename, OI.fld_name as ouletname, OI02.fld_name as fromouletname,VI.fld_name as vendorname,WI02.fld_name as towarehousename");
		}

		$this->db->from("tbl_transaction TI");		
		$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
		$this->db->join("tbl_warehouse WI02", "TI.fld_towarehouseid=WI02.fld_id", "left");
		$this->db->join("tbl_outlets OI", "OI.fld_id=TI.fld_outletid", "left");
		$this->db->join("tbl_outlets OI02", "TI.fld_fromoutletid=OI02.fld_id", "left");
		$this->db->join("tbl_vendors VI", "TI.fld_vendorid=VI.fld_id", "left");
		if($prodname!="")
		{
			$this->db->join("tbl_transactioninfo TII", "TI.fld_id=TII.fld_transactionid");
			$this->db->join("tbl_products PI", "TII.fld_productid=PI.fld_id");
		}
		if($vendorid>0)
		{
			$this->db->where('TI.fld_vendorid',$vendorid);
		}
		if($warehouseid>0)
		{
			$this->db->where('TI.fld_warehouseid',$warehouseid);
		}
		if($outletid>0)
		{
			if($stocktype=='o2o')
			{
				$this->db->group_start();
				$this->db->where('TI.fld_outletid',$outletid);
				$this->db->or_where('TI.fld_fromoutletid',$outletid);
				$this->db->group_end();
			}
			else
			{
				$this->db->where('TI.fld_outletid',$outletid);
			}
		}
		$this->db->where("TI.fld_clientid", $this->clientid);
		if($fromdt!="" && $todt!="") $this->db->where("(TI.fld_refdate >= '".$fromdate."' and TI.fld_refdate <= '".$todate."')");
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("TI.fld_id", $keyword);
			$this->db->or_like("TI.fld_refno", $keyword);
			$this->db->or_like("TI.fld_refdate", $keyword);
			$this->db->or_like("TI.fld_status", $keyword);
			$this->db->or_like("TI.fld_amount", $keyword);
			$this->db->group_end();
		}
		if($prodname!="")
		{
			$this->db->like("PI.fld_name", $prodname);
		}
		if($listtype != "count")
		{
			$this->db->order_by('TI.fld_id', 'desc');		
			if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit'],$params['start']);
			}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit']);
			}
		}
		$results = $this->db->get();
		//echo "<br>" . $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}
	
	function listhistory($listtype, $params)
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");		
		$prodname = $this->input->get("prodname");		
		$warehouseid = $this->input->get("warehouseid");
		$outletid = $this->input->get("outletid");
		$fromdt = $this->input->get("fromdate");
		$todt = $this->input->get("todate");
		if($fromdt!="")
		{
			 $fromdate = convertdateFormat($fromdt,'Y-m-d');
		}
		if($todt!="")
		{
			$todate = convertdateFormat($todt,'Y-m-d');
		}
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("TI.*, UI.fld_name as performedby");
		}

		$this->db->from("tbl_logs TI");
		$this->db->join("tbl_users UI", "TI.fld_userid=UI.fld_id");
		if($fromdt!="" && $todt!="") $this->db->where("(TI.fld_datetime >= '".$fromdate."' and TI.fld_datetime <= '".$todate."')");
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->or_like("UI.fld_name", $keyword);
			$this->db->or_like("TI.fld_task", $keyword);
			$this->db->or_like("TI.fld_remarks", $keyword);
			$this->db->group_end();
		}
		if($listtype != "count")
		{
			$this->db->order_by('TI.fld_id', 'desc');		
			if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit'],$params['start']);
			}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
				$this->db->limit($params['limit']);
			}
		}
		$results = $this->db->get();
		//echo "<br>" . $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}	
	
	function getpendingwork()
	{
		$arrdata = array();
		
		## get bills for cancellation
		$this->db->select("BI.fld_id, BI.fld_billno, BI.fld_billdate, BI.fld_netamount, BI.fld_cancellation_reason, OI.fld_name as outletname");
		$this->db->from("tbl_billing BI");
		$this->db->join("tbl_outlets OI", "BI.fld_outletid=OI.fld_id");
		$this->db->where("BI.fld_status", "pending");
		$this->db->where("BI.fld_del", "0");
		$result = $this->db->get();
		//echo $this->db->last_query() . " => " . $result->num_rows();
		if($result->num_rows()>0)
		{
			foreach($result->result_array() as $row)
			{
				$arrdata["cancellationrequests"][] = $row;
			}
		}
		
		## get delivered acknowledgements
		$this->db->select("TI.fld_id, TI.fld_type, TI.fld_refno, TI.fld_refdate, VI.fld_name as vendorname, WI.fld_name as fromwarehousename, WI02.fld_name as towarehousename, OI.fld_name as fromoutletname, OI02.fld_name as tooutletname");
		$this->db->from("tbl_transaction TI");
		$this->db->join("tbl_vendors VI", "TI.fld_vendorid=VI.fld_id", "left");
		$this->db->join("tbl_outlets OI", "TI.fld_fromoutletid=OI.fld_id", "left");
		$this->db->join("tbl_outlets OI02", "TI.fld_outletid=OI02.fld_id", "left");
		$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
		$this->db->join("tbl_warehouse WI02", "TI.fld_towarehouseid=WI02.fld_id", "left");
		$this->db->where("TI.fld_status", "delivered");
		$this->db->where("TI.fld_del", "0");
		$result02 = $this->db->get();
		if($result02->num_rows()>0)
		{
			foreach($result02->result_array() as $row)
			{
				$arrdata["acknowledgements"][] = $row;
			}
		}
		
		## get products not priced
		$sql = "select distinct PI.fld_id, PI.fld_name, PI.fld_type from tbl_products PI WHERE PI.fld_id NOT IN(select distinct fld_productid from tbl_price PPI) AND PI.fld_del='0'";
		$result03 = $this->db->query($sql);
		if($result03->num_rows()>0)
		{
			foreach($result03->result_array() as $row)
			{
				$arrdata["productsmissingprice"][] = $row;
			}
		}
		return $arrdata;
	}
	
	function exportpendingworkmissitems($arroutletids)
	{
		$this->db->select("fld_id, fld_name, fld_zoneid");
		$this->db->where_in("fld_id", $arroutletids);
		$this->db->where("fld_show", "yes");
		$this->db->where("fld_status", "active");
		$results = $this->db->get("tbl_outlets");
		if($results->num_rows()>0)
		{
			$arrdata = array();
			while ($row = $results->unbuffered_row())
			{
				$outletid = $row->fld_id;
				$outletname = $row->fld_name;
				$zoneid = $row->fld_zoneid;
				
				## get missing items
				$sql = "select PI.fld_name, PI.fld_type, PPI.fld_zoneid from tbl_products PI LEFT JOIN tbl_price PPI ON PI.fld_id=PPI.fld_productid AND PPI.fld_zoneid='$zoneid' WHERE PI.fld_del='0' AND PPI.fld_zoneid is NULL order by PI.fld_name";
				$result03 = $this->db->query($sql);
				if($result03->num_rows()>0)
				{
					while ($row03 = $result03->unbuffered_row())
					{
						$arrdata[] = array(
							"Outlet" => $outletname,
							"Item Name" => $row03->fld_name,
							"Item Type" => $row03->fld_type
						);
					}
				}
			}
			return $arrdata;
		}
	}

	function exportcustdata()
	{
		$this->db->distinct();
		$this->db->select("fld_name as 'Customer Name', fld_mobile as 'Customer Mobile'");
		$this->db->where("fld_mobile!=","");
		//$this->db->order_by("fld_name", "asc");
		$results = $this->db->get("tbl_customers");
		if($results->num_rows()>0)
		{
			return $results->result_array();
		}
	}
	
	function exportitemswithmissingprice($exporttype)
	{
		$sql = "select distinct PI.fld_name as 'Item Name', PI.fld_type as 'Item Type' from tbl_products PI WHERE PI.fld_id NOT IN(select distinct fld_productid from tbl_price PPI) AND PI.fld_del='0' order by PI.fld_name";
		$result03 = $this->db->query($sql);
		if($result03->num_rows()>0)
		{
			return $result03->result_array();
		}			
	}
	
	
	function getoutletpendingwork($outletid)
	{
		$todate = $this->input->get("todate");
		if($todate!="") $todate = date("Y-m-d", strtotime($todate));
		$arrdata = array();
		
		## get bills for cancellation
		$this->db->select("count(*) as totalrecs");
		$this->db->where("fld_status", "pending");
		$this->db->where("fld_outletid", $outletid);
		$this->db->where("fld_del", "0");
		$this->db->where("fld_billdate<=", $todate);
		$result = $this->db->get("tbl_billing");
		//echo $this->db->last_query() . " => " . $result->num_rows();
		$arrdata["cancellationrequests"] = $result->row()->totalrecs;

		## get delivered acknowledgements
		$this->db->select("count(*) as totalrecs");
		$this->db->where("fld_status", "delivered");
		$this->db->where("fld_outletid", $outletid);
		$this->db->where("fld_del", "0");
		$this->db->where("fld_refdate<=", $todate);
		$result02 = $this->db->get("tbl_transaction");
		//echo $this->db->last_query() . " => " . $result02->num_rows();
		$arrdata["acknowledgements"] = $result02->row()->totalrecs;
		return $arrdata;
	}
		
}
?>
