<?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 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 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');
		
		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");		
		$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($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");
			}
			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");
		}
		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>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);
		$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!="")
		{
			$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
				);
				
				## 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");
				$result02 = $this->db->get();
				if($result02->num_rows()>0)
				{
					while ($row02 = $result02->unbuffered_row())
					{
						$arrparams["warehouseid"] = $row02->fld_id;
						## get warehouse stock
						$arrwarehousestock = $this->getitemstockwarehousewise($arrparams);
						$instockqtyinbottles = $arrwarehousestock[$productid]["instock"];
						//$instockqtyincases = $instockqtyinbottles;
						$instockqtyincases = round(($instockqtyinbottles / $bottlesincase ), 2);
						$arritemstock[] = array(
							"name" => $row02->fld_name,
							"type" => "warehouse",
							"incoming" => $arrwarehousestock[$productid]["incoming"],
							"outgoing" => $arrwarehousestock[$productid]["outgoing"],
							"instockinbottles" => $instockqtyinbottles,
							"instockincases" => $instockqtyincases
						);
					}
				}

				## 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");
				$result02 = $this->db->get();
				if($result02->num_rows()>0)
				{
					while ($row02 = $result02->unbuffered_row())
					{
						$arrparams["outletid"] = $row02->fld_id;
						## get warehouse stock
						$arroutletstock = $this->getitemstockoutletwise($arrparams);
						$instockqtyinbottles = $arroutletstock[$productid]["instock"];
						//$instockqtyincases = $instockqtyinbottles;
						$instockqtyincases = round(($instockqtyinbottles / $bottlesincase ), 2);
						$arritemstock[] = array(
							"name" => $row02->fld_name,
							"type" => "outlet",
							"incoming" => $arroutletstock[$productid]["incoming"],
							"outgoing" => $arroutletstock[$productid]["outgoing"],
							"instockinbottles" => $instockqtyinbottles,
							"instockincases" => $instockqtyincases
						);
					}
				}
				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"));
		$type = $this->input->get("type");
		$catid = decodeValue($this->input->get("catid"));
		$brandid = decodeValue($this->input->get("brandid"));
		$fromdt = trim($this->input->get("fromdate"));
		$todt = trim($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
		{
			$this->db->select("PI.fld_name,PI.fld_minstockvalue,TI.fld_productid");
		}		
		$this->db->from("tbl_products PI");
		$this->db->join("tbl_transactioninfo TI", "TI.fld_productid=PI.fld_id",'left');		
		$this->db->join("tbl_transaction T", "TI.fld_transactionid=T.fld_id",'left');
		if($catid>0)
		{
			$this->db->join("tbl_category CI", "CI.fld_id=PI.fld_catid",'left');
		}
		if($brandid>0)
		{
			$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);
		}
		if($warehouseid>0)
		{
			$this->db->where('T.fld_warehouseid',$warehouseid);
		}
		
		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('T.fld_clientid',$this->clientid);
		$this->db->where('T.fld_del', '0');
		$this->db->where("(T.fld_status='completed' or T.fld_status='delivered')");
		$this->db->group_by('TI.fld_productid,PI.fld_name');
		if($listtype != "count")
		{
			$this->db->order_by('PI.fld_name,PI.fld_minstockvalue,PI.fld_catid', '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;
			return $results->num_rows();
		}
		else
		{
			$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_productid;
						$arr[$prodid] = array(
							"id" => $prodid,
							"name" => $row->fld_name,
							"previousstock" => 0,
							"incoming" => 0,
							"outgoing" => 0,
							"instock" => 0,
							"minstockvalue" => $row->fld_minstockvalue,
						);	
						if($arr[$prodid])
						{
							### previous stock from date
							$this->db->select("SUM(TII2.fld_qty) as pincoming");
							$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->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("DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d')<'".$fromdate."'");
							$this->db->group_by('TII2.fld_productid');
							$piquery = $this->db->get();
							$pirow = $piquery->row();
							$pincoming = $pirow->pincoming;
							//echo $this->db->last_query(); exit;
							
							$this->db->select("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("TII2.fld_productid=",$prodid);
							$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
							$this->db->where("(TI2.fld_type='stockout')");
							$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("DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d')<'".$fromdate."'");							
							$this->db->group_by('TII2.fld_productid');
							$poquery = $this->db->get();
							$porow = $poquery->row();
							$poutgoing = $porow->poutgoing;
							//echo $this->db->last_query(); exit;
							$previousstock =  ($pincoming-$poutgoing);
							$arr[$prodid]["previousstock"] = $previousstock;

							### 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);
							$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");
							if($warehouseid>0)
							{
								$this->db->where('TI2.fld_warehouseid',$warehouseid);
							}
							if($fromdate!="" && $todate!="")
							{		
								$this->db->where("(DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
							}
							elseif($fromdate!="")
							{			

								$this->db->where("DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d')='".$fromdate."'");
							}
							elseif($todate!="")
							{			

								$this->db->where("DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d')='".$todate."'");
							}
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$wincomingqty = $row2->wincomingqty;
							$arr[$prodid]["incoming"] += $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("TII2.fld_productid=",$prodid);
							$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
							$this->db->where("(TI2.fld_type='stockout')");
							$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($fromdate!="" && $todate!="")
							{		
								$this->db->where("(DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
							}
							elseif($fromdate!="")
							{			

								$this->db->where("DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d')='".$fromdate."'");
							}
							elseif($todate!="")
							{			

								$this->db->where("DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d')='".$todate."'");
							}
							$this->db->group_by('TII2.fld_productid');
							$query3 = $this->db->get();
							$row3 = $query3->row();
							$boutgoing = $row3->boutgoing;
							$arr[$prodid]["outgoing"] += $boutgoing;
							$arr[$prodid]["instock"] = $previousstock + ($arr[$prodid]["incoming"]-$arr[$prodid]["outgoing"]);
						}
					}
					//predata($arr); exit;
				}
			}
			return $arr;
		}
	}
	 
	function managestock($posteddata,$stocktype)
	{
		$returnvalue = 0;		
		$rsrcid = $posteddata["rsrcid"];
		$vendorid = $posteddata["vendorid"];
		$outletid = $posteddata["outletid"];
		$warehouseid = $posteddata["warehouseid"];
		$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;
		}
		
		$data = array(
			"fld_clientid" => $this->clientid,
			"fld_type" => $stocktype,
			"fld_vendorid" => $vendorid,
			"fld_warehouseid" => $warehouseid,
			"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)
						{
							$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");
		}
		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');
		}
		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);						
							$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_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);						
							$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_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);						
							$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);
							$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);
							$this->db->where("(TI3.fld_status='completed' or TI3.fld_status='delivered')");
							$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;
		}
	}
}
?>
