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

class Common_model extends CI_Model{
        
    public function __construct()
    {
        parent::__construct();
		$this->tblprefix = 'tbl_';
    }

	function delcomm($rsrcid, $tablename)
	{
		if($rsrcid!="" && $tablename!="")
		{
			$datanew = array("fld_del" => "1", "fld_updatedon" => date("Y-m-d H:i:s"));
			//$this->db->set('fld_del', '1',false);
			$this->db->where('fld_id',$rsrcid);
			$this->db->update($this->tblprefix.$tablename, $datanew);
			return true;
		}
		else
		{
			return false;
		}
	}
	function delinfocomm($rsrcid, $tablename)
	{
		if($rsrcid!="" && $tablename!="")
		{
			$this->db->where("fld_id",$rsrcid);
			$this->db->delete($this->tblprefix.$tablename);
			return true;
		}
		else
		{
			return false;
		}
	}   

	function liststaff($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$type = $this->input->get("type");
		$tasttype = $this->uri->segment("1");
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("*");
		}

		$this->db->where("fld_clientid", $this->clientid);
		$this->db->where("fld_type", "staff");
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("fld_name", $keyword);
			$this->db->or_like("fld_email", $keyword);
			$this->db->or_like("fld_mobile", $keyword);	
			$this->db->group_end();
		}
		$this->db->where('fld_del', '0');
		if($listtype != "count")
		{
			$this->db->order_by('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("tbl_users");
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}
	
	function listdata($tablename, $listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$type = $this->input->get("type");
		$tasttype = $this->uri->segment("1");
		if($tablename=="users")
		{
			if($tasttype=='account')
			{
				
				$outletid = decodeValue($this->uri->segment("3"));
			}
		}
		elseif($tablename=="customers" || $tablename=="billing")
		{			
			if($tasttype=='account')
			{
				$outletid = decodeValue($this->input->get("outletid"));				
			}			
		}
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("*");
		}

		if($tablename=="users")
		{			
			if($tasttype=='account')
			{
				$this->db->where("fld_outletid", $outletid);
				$this->db->where("fld_clientid", $this->clientid);
			}			
		}
		elseif($tablename=="vendors" || $tablename=="zone"  || $tablename=="warehouse"    || $tablename=="billing")
		{
			if($tasttype=='account')
			{
				if($outletid>0)
				{
					$this->db->where("fld_outletid", $outletid);
				}				
			}
			elseif($tasttype=='outlet')
			{
				$this->db->where("fld_outletid", $this->outletid);
			}

			$this->db->where("fld_clientid", $this->clientid);
		}
		
		if($keyword!="")
		{
			$this->db->group_start();
			if($tablename=="vendors" || $tablename=="users" || $tablename=="customers")
			{
				$this->db->like("fld_name", $keyword);
				$this->db->or_like("fld_email", $keyword);
				$this->db->or_like("fld_mobile", $keyword);	
				if($tablename=="vendors")
				{
					$this->db->or_like("fld_address", $keyword);
					$this->db->or_like("fld_cpname", $keyword);
					$this->db->or_like("fld_cpemail", $keyword);
					$this->db->or_like("fld_cpmobile", $keyword);
				}
				elseif($tablename=="users")
				{
					$this->db->or_like("fld_username", $keyword);
				}				
			}
			else
			{
				$this->db->like("fld_name", $keyword);
			}
			$this->db->group_end();
		}
		$this->db->where('fld_del', '0');
		if($listtype != "count")
		{
			if($tablename=="category" || $tablename=="brand" || $tablename=="zone" || $tablename=="warehouse" || $tablename=="vendors" || $tablename=="customers")
			{
				$this->db->order_by('fld_name', 'asc');					
			}
			else
			{
				$this->db->order_by('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($this->tblprefix.$tablename);
		//echo $this->db->last_query();-
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}
	function listcustomerdata($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		
			$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');
			}	
		$tasttype = $this->uri->segment("1");
		if($tasttype=='account')
		{
			$outletid = decodeValue($this->input->get("outletid"));				
		}
		$billerid = decodeValue($this->input->get("billerid"));
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("CI.*,sum(BI.fld_netamount) as totalnetamount,sum(BI.fld_discount) as totaldiscount");
		}
		
		$this->db->from("tbl_customers CI");		
		$this->db->join("tbl_billing BI", "CI.fld_id=BI.fld_customerid",'left');
		$this->db->where("BI.fld_status", 'completed');
		if($tasttype=='account')
		{
			if($outletid>0)
			{
				$this->db->where("CI.fld_outletid", $outletid);
			}
				
		}
		if($billerid>0)
		{
			$this->db->where("CI.fld_userid", $billerid);
		}
		
		$this->db->where("CI.fld_clientid", $this->clientid);
		if($fromdate!="" && $todate!="")
		{		
			$this->db->where("(DATE_FORMAT(CI.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(CI.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
		}
		elseif($fromdate!="")
		{			

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

			$this->db->where("DATE_FORMAT(CI.fld_datetime,'%Y-%m-%d')='".$todate."'");
		}
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("CI.fld_name", $keyword);
			$this->db->or_like("CI.fld_mobile", $keyword);
			$this->db->or_like("CI.fld_email", $keyword);			
			$this->db->or_like("CI.fld_givenname", $keyword);
			$this->db->or_like("CI.fld_givenemail", $keyword);
			$this->db->or_like("CI.fld_givenmobile", $keyword);
			$this->db->group_end();
		}
		$this->db->where('CI.fld_del', '0');
		$this->db->group_by('BI.fld_customerid');
		if($listtype != "count")
		{
			$this->db->order_by('CI.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 listjoindata($tablename, $listtype, $params = array())
	{
		$tasttype = $this->uri->segment("1");
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");	
		$catid = decodeValue($this->input->get("catid"));	
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			if($tablename=="gifts")
			{
				$this->db->select("T1.*, T2.fld_name as productname");
			}
			else
			{
				$this->db->select("T1.*");
			}
		}
		$this->db->from($this->tblprefix.$tablename." T1");
		if($tablename=="gifts")
		{	
			$this->db->join("tbl_products T2", "T2.fld_id=T1.fld_productid", "left");
			if($tasttype=='outlet')
			{
				$this->db->where("fld_outletid", $this->outletid);
				$this->db->where("fld_clientid", $this->clientid);			
			}			
		}
		if($keyword!="")
		{
			$this->db->group_start();
			if($tablename=="gifts")
			{
				$this->db->like("T1.fld_fromvalue", $keyword);
				$this->db->or_like("T1.fld_tovalue", $keyword);
				$this->db->or_like("T2.fld_name", $keyword);
			}
			
			$this->db->group_end();
		}
		$this->db->where('T1.fld_del', '0');
		if($listtype != "count")
		{
			if($tablename=="gifts")
			{
				$this->db->order_by('T1.fld_name', 'asc');
			}
			else
			{
				$this->db->order_by('T1.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 $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}
	function listproductdata($listtype, $params = array(),$price,$stock,$category,$brand,$salebydays)
	{
		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 = trim($this->input->get("fromprice"));
		$toprice = trim($this->input->get("toprice"));
		$tasktype = $this->uri->segment("1");
		if($tasktype=='outlet')
		{
			$outletid = $this->outletid;
		}
		else
		{
			$outletid = trim($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)
			{
				//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)
						{
							$this->db->select("*");
							$this->db->where("fld_productid", $prodid);
							$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)
							{
								$rowminqty = $resultsminqty->row();
								$arr[$prodid]["minqtyid"] = $rowminqty->fld_id;
								$arr[$prodid]["minqty"] = $rowminqty->fld_minqty;
								//echo $this->db->last_query();
							}
							else
							{
								//$arr[$prodid]["minqtyid"] = $rowminqty->fld_id;
								$arr[$prodid]["minqty"] = 0;
							}

						}
						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;
							
							### 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_clientid',$this->clientid);
							if($outletid>0)
							{
								$this->db->where('TI2.fld_outletid',$outletid);
							}
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$wincomingqty = $row2->wincomingqty;
							$arr[$prodid]["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);
							if($outletid>0)
							{
								$this->db->where('TI5.fld_outletid',$outletid);
							}
							$this->db->group_by('TII5.fld_productid');
							$query5 = $this->db->get();
							$row5 = $query5->row();
							$boutgoing = $row5->boutgoing;
							$arr[$prodid]["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_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;
							$arr[$prodid]["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->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->group_by('TII4.fld_productid');
							$query4 = $this->db->get();
							//echo $this->db->last_query();
							$row4 = $query4->row();
							$ooutgoingqty = $row4->ooutgoingqty;
							$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++)
									{
										
										$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("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("TII5.fld_productid",$prodid);
										$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();
										$row7 = $query7->row();
										if($row7->totalqty>0)
										{
											//$saleamount = $row7->saleamount;
											$totalqty = $row7->totalqty;
											//$arr[$prodid][$sdayscnt[$i].'dayssale'] = $saleamount;
											$arr[$prodid][$sdayscnt[$i].'dayssale'] = round($totalqty);
										}
										else
										{
											$arr[$prodid][$sdayscnt[$i].'dayssale'] = 0;
										}
										
										//echo "<br> " . $this->db->last_query();
									}
								}
							}
						}
					}
					
				}
				return $arr;
			}
		}
	}
	function listpricedata($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$zoneid = decodeValue($this->input->get("zoneid"));
		$catid = decodeValue($this->input->get("catid"));
		$productid = decodeValue($this->input->get("productid"));
		$fromprice = trim($this->input->get("fromprice"));
		$toprice = trim($this->input->get("toprice"));
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("PRI.*,PI.fld_name as productname,ZI.fld_name as zonename,CI.fld_name as catname");
		}
		$this->db->from("tbl_price PRI");
		$this->db->join("tbl_zone ZI", "PRI.fld_zoneid=ZI.fld_id",'left');
		$this->db->join("tbl_products PI", "PI.fld_id=PRI.fld_productid",'left');
		$this->db->join("tbl_category CI", "PI.fld_catid=CI.fld_id",'left');
		if($catid>0)
		{
			
			$this->db->where('PI.fld_catid',$catid);
		}
		if($zoneid>0)
		{
			$this->db->where('PRI.fld_zoneid',$zoneid);
		}

		if($productid>0)
		{
			$this->db->where('PRI.fld_productid',$productid);
		}
		if($fromprice>0 && $fromprice>0)
		{
			$this->db->where("(PRI.fld_price>=".$fromprice." and PRI.fld_price<=".$toprice.")");
		}
		elseif($fromprice>0)
		{
			$this->db->where('PRI.fld_price',$fromprice);
		}
		elseif($toprice>0)
		{				
			$this->db->where('PRI.fld_price',$toprice);
		}
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("PI.fld_name", $keyword);
			$this->db->or_like("ZI.fld_name", $keyword);
			$this->db->or_like("PRI.fld_mrp", $keyword);
			$this->db->or_like("PRI.fld_price", $keyword);
			$this->db->or_like("PRI.fld_price3bottles", $keyword);
			$this->db->or_like("PRI.fld_price6bottles", $keyword);
			$this->db->or_like("PRI.fld_price9bottles", $keyword);
			$this->db->or_like("PRI.fld_price12bottles", $keyword);
			$this->db->or_like("PRI.fld_price24bottles", $keyword);
	
			$this->db->group_end();
		}
		$this->db->where('PRI.fld_clientid',$this->clientid);	
		$this->db->where('PRI.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)
			{
				return $results->result();
			}
		}
	}

	function listbillingdata($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
			if($this->uri->segment("1")=='outlet')
			{
				$outletid = $this->outletid;
			}
			else
			{
				$outletid = decodeValue($this->input->get("outletid"));
			}
			$billerid = decodeValue($this->input->get("billerid"));
			$tasktype = $this->uri->segment("3");		
			$statustype = trim($this->input->get("status"));
			$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
		{
			if($this->uri->segment("1")=='account')
			{
				$this->db->select("TI.*,OI.fld_name as outletname");
			}
			else
			{
				$this->db->select("TI.*");			
			}
		}
		$this->db->from("tbl_billing TI");
		if($this->uri->segment("1")=='account')
		{
			$this->db->join("tbl_outlets OI", "OI.fld_id=TI.fld_outletid");
		}

		if($billerid>0)
		{
			$this->db->where('TI.fld_userid',$billerid);
		}		
		if($outletid>0)
		{
			$this->db->where('TI.fld_outletid',$outletid);
		}

		if($tasktype=="cnclrqts")
		{
			$this->db->where("(TI.fld_status='pending')");
		}
		else
		{
			if($statustype!="")
			{
				if($statustype=='discount')
				{
					$this->db->where('TI.fld_discount>0');
				}
				else
				{
					$this->db->where('TI.fld_status',$statustype);
				}
			}
		}
		if($tasktype!="cnclrqts")
		{
		
			if($fromdate!="" && $todate!="")
			{		
				$this->db->where("(DATE_FORMAT(TI.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(TI.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
			}
			elseif($fromdate!="")
			{			

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

				$this->db->where("DATE_FORMAT(TI.fld_datetime,'%Y-%m-%d')='".$todate."'");
			}
		}

		
		$this->db->where("TI.fld_clientid", $this->clientid);
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("TI.fld_billno", $keyword);
			$this->db->or_like("TI.fld_mobile", $keyword);
			$this->db->or_like("TI.fld_name", $keyword);
			$this->db->or_like("TI.fld_netamount", $keyword);
			$this->db->or_like("TI.fld_givenname", $keyword);
			$this->db->or_like("TI.fld_givenemail", $keyword);
			$this->db->or_like("TI.fld_givenmobile", $keyword);
			$this->db->or_like("TI.fld_discount", $keyword);
			$this->db->or_like("TI.fld_giftname", $keyword);
			$this->db->group_end();
		}
		$this->db->where('TI.fld_del', '0');
		if($listtype != "count")
		{
			if($tasktype=="cnclrqts")
			{
				$this->db->order_by('TI.fld_cancellationdate', 'desc');
			}
			else
			{
				$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 $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result_array();
			}
		}
	}
	function listordersdata($listtype, $params = array(),$fldname,$fldid)
	{
			if($listtype=="") $listtype = "count";
		
			$keyword = $this->input->get("search");
			$tasktype = $this->uri->segment("1");
			$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
			{
				if($fldname=='fld_productid' || $fldname=='fld_catid' || $fldname=='fld_brandid')
				{
					$this->db->select("T1.*,T2.fld_qty,T2.fld_price,T3.fld_name as productname");
				}
				elseif($fldname=='fld_userid')
				{
					$this->db->select("T1.*,T2.fld_name as outletname,T4.fld_name as billername");
				}
				elseif($fldname=='fld_outletid')
				{
					$this->db->select("T1.*,T2.fld_name as outletname,T4.fld_name as billername");
				}
				else
				{
					$this->db->select("T1.*");
				}
			}
			$this->db->from("tbl_billing T1");
			if($fldname=='fld_customerid')
			{
				$this->db->where('T1.'.$fldname,$fldid);
			}
			elseif($fldname=='fld_userid')
			{
					$this->db->join("tbl_outlets T2", "T2.fld_id=T1.fld_outletid");
					$this->db->join("tbl_users T4", "T4.fld_id=T1.fld_userid");
					if($fldid>0)
					{	
						$this->db->where('T1.'.$fldname,$fldid);
					}
					if($tasktype=='outlet')
					{
						$this->db->where('T1.fld_outletid',$this->outletid);
					}
				
			}
			elseif($fldname=='fld_outletid')
			{
				$this->db->join("tbl_outlets T2", "T2.fld_id=T1.fld_outletid");
				$this->db->join("tbl_users T4", "T4.fld_id=T1.fld_userid");
				$this->db->where('T1.'.$fldname,$fldid);
				
			}
			elseif($fldname=='fld_productid' || $fldname=='fld_catid' || $fldname=='fld_brandid')
			{
				$this->db->join("tbl_billinginfo T2", "T2.fld_billingid=T1.fld_id");		
				$this->db->join("tbl_products T3", "T2.fld_productid=T3.fld_id");
				if($fldname=='fld_productid')
				{
					$this->db->where('T2.'.$fldname,$fldid);
				}
				elseif($fldname=='fld_catid')
				{
					$this->db->where('T3.'.$fldname,$fldid);
				}
				elseif($fldname=='fld_brandid')
				{
					$this->db->where('T3.'.$fldname,$fldid);
				}
			}
			if($this->router->fetch_method()=="report")
			{
				if($fromdate!="" && $todate!="")
				{			
					//$this->db->where('T1.fld_datetime BETWEEN "'.$fromdate. '" and "'.$todate.'"');
					$this->db->where('DATE_FORMAT(T1.fld_datetime,"%Y-%m-%d") BETWEEN "'.$fromdate. '" and "'.$todate.'"');
				}
				elseif($fromdate!="")
				{			

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

					$this->db->where("DATE_FORMAT(T1.fld_datetime,'%Y-%m-%d')='".$todate."'");
				}
				else
				{
					$fromdate = date('Y-m-d');
					//$this->db->where("DATE_FORMAT(T1.fld_datetime,'%Y-%m-%d')='".$fromdate."'");
				}
				$this->db->where('T1.fld_status="completed"');
			}

			if($tasktype=='outlet')
			{
				$this->db->where('T1.fld_outletid',$this->outletid);		
			}
			elseif($tasktype=='account' && $outletid>0)
			{
				$this->db->where('T1.fld_outletid',$outletid);		
			}
			$this->db->where("T1.fld_clientid", $this->clientid);
			if($keyword!="")
			{
				$this->db->group_start();
				$this->db->like("T1.fld_billno", $keyword);	
				$this->db->or_like("T1.fld_netamount", $keyword);
				$this->db->or_like("T1.fld_mobile", $keyword);				
				$this->db->or_like("T1.fld_name", $keyword);				
				$this->db->or_like("T1.fld_givenname", $keyword);				
				$this->db->or_like("T1.fld_givenemail", $keyword);				
				$this->db->or_like("T1.fld_givenmobile", $keyword);				
				$this->db->or_like("T1.fld_discount", $keyword);				
				$this->db->or_like("T1.fld_remarks", $keyword);			
				$this->db->or_like("T1.fld_giftname", $keyword);	
				if($fldname=='fld_productid' || $fldname=='fld_catid' || $fldname=='fld_brandid')
				{				
					$this->db->or_like("T2.fld_qty", $keyword);
				}
				

				$this->db->group_end();
			}
			$this->db->where('T1.fld_del', '0');
			if($listtype != "count")
			{
				$this->db->order_by('T1.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 $this->db->last_query();
			//exit;
			if($listtype == "count")
			{
				return $results->row()->totalrecs;
			}
			else
			{
				if($results->num_rows()>0)
				{
					return $results->result();
				}
			}
		
	}
	function listallordersdata($listtype, $params = array(),$fldname,$fldid)
	{
			if($listtype=="") $listtype = "count";
		
			$keyword = $this->input->get("search");
			$price = $this->input->get("price");
			$tasktype = $this->uri->segment("1");
			if($tasktype=='outlet')
			{
				$outletid = $this->outletid;

			}
			else
			{
				$outletid = decodeValue($this->input->get("outletid"));
				
			}
			$billerid = decodeValue($this->input->get("billerid"));
			$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
			{
				if($fldname=='fld_gprodid')
				{
					$this->db->select("T1.*,T1.fld_gqty as qty,T3.fld_name as productname");
				}
				else
				{
					$this->db->select("T1.*,T2.fld_qty as qty,T2.fld_price,T3.fld_name as productname");
				}
				
			}
			$this->db->from("tbl_billing T1");
			if($fldname!='fld_gprodid')
			{
				$this->db->join("tbl_billinginfo T2", "T2.fld_billingid=T1.fld_id");
				$this->db->join("tbl_products T3", "T2.fld_productid=T3.fld_id");
			}
			else
			{
				$this->db->join("tbl_products T3", "T1.fld_gprodid=T3.fld_id");
			}
			
			if($fldname=='fld_catid')
			{
				$this->db->where('T3.'.$fldname,$fldid);
			}
			elseif($fldname=='fld_brandid')
			{
				$this->db->where('T3.'.$fldname,$fldid);
			}
			elseif($fldname=='fld_productid')
			{
				$this->db->where('T2.'.$fldname,$fldid);
				if($price!="")
				{
					$this->db->where('T2.fld_price',$price);
				}
			}
			elseif($fldname=='fld_gprodid')
			{
				$this->db->where('T1.'.$fldname,$fldid);

			}
			elseif($fldname=='fld_type')
			{
				$this->db->where('T3.'.$fldname,$fldid);
			}
			elseif($fldname=='fld_customerid')
			{
				$this->db->where('T1.'.$fldname,$fldid);
			}


			if($outletid>0)
			{
				$this->db->where('T1.fld_outletid',$outletid);		
			}
			if($billerid>0)
			{
				$this->db->where('T1.fld_userid',$billerid);		
			}
			$this->db->where("T1.fld_status!='cancelled'");
			$this->db->where("T1.fld_clientid", $this->clientid);

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

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

				$this->db->where("DATE_FORMAT(T1.fld_datetime,'%Y-%m-%d')='".$todate."'");
			}
			if($keyword!="")
			{
				$this->db->group_start();
				$this->db->like("T1.fld_billno", $keyword);	
				$this->db->or_like("T1.fld_netamount", $keyword);
				$this->db->or_like("T1.fld_mobile", $keyword);				
				$this->db->or_like("T1.fld_name", $keyword);				
				$this->db->or_like("T1.fld_givenname", $keyword);				
				$this->db->or_like("T1.fld_givenemail", $keyword);				
				$this->db->or_like("T1.fld_givenmobile", $keyword);				
				$this->db->or_like("T1.fld_discount", $keyword);				
				$this->db->or_like("T1.fld_remarks", $keyword);			
				$this->db->or_like("T1.fld_giftname", $keyword);	
				if($fldname=='fld_gprodid')
				{
					$this->db->or_like("T1.fld_gqty", $keyword);
				}
				else
				{
					$this->db->or_like("T2.fld_qty", $keyword);
				}				
				
				

				$this->db->group_end();
			}
			$this->db->where('T1.fld_del', '0');
			if($listtype != "count")
			{
				$this->db->order_by('T1.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 $this->db->last_query();
			//exit;
			if($listtype == "count")
			{
				return $results->row()->totalrecs;
			}
			else
			{
				if($results->num_rows()>0)
				{
					return $results->result();
				}
			}
		
	}
	function listallstdata($listtype, $params = array(),$fldname,$fldid)
	{
			if($listtype=="") $listtype = "count";
		
			$keyword = $this->input->get("search");
			$tasktype = $this->uri->segment("1");
			if($tasktype=='outlet')
			{
				$outletid = $this->outletid;

			}
			else
			{
				$outletid = decodeValue($this->input->get("outletid"));
				
			}
			$warehouseid = decodeValue($this->input->get("warehouseid"));
			$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("T1.*,T2.fld_qty as qty,T3.fld_name as productname,T4.fld_name as warehousename,T5.fld_name as outletname");				
			}
			$this->db->from("tbl_transaction T1");
			$this->db->join("tbl_transactioninfo T2", "T2.fld_transactionid=T1.fld_id");
			$this->db->join("tbl_products T3", "T2.fld_productid=T3.fld_id");
			$this->db->join("tbl_warehouse T4", "T4.fld_id=T1.fld_warehouseid");
			$this->db->join("tbl_outlets T5", "T5.fld_id=T1.fld_outletid");
			
			if($fldname=='fld_catid')
			{
				$this->db->where('T3.'.$fldname,$fldid);
			}
			elseif($fldname=='fld_brandid')
			{
				$this->db->where('T3.'.$fldname,$fldid);
			}
			elseif($fldname=='fld_productid')
			{
				$this->db->where('T2.'.$fldname,$fldid);
				if($price!="")
				{
					$this->db->where('T2.fld_price',$price);
				}
			}
			elseif($fldname=='fld_type')
			{
				$this->db->where('T3.'.$fldname,$fldid);
			}
			if($warehouseid>0)
			{
				$this->db->where('T1.fld_warehouseid',$warehouseid);		
			}

			if($outletid>0)
			{
				$this->db->where('T1.fld_outletid',$outletid);		
			}

			$this->db->where("(T1.fld_status='completed' or T1.fld_status='delivered')");
			$this->db->where("T1.fld_type", 'stockout');
			$this->db->where("T1.fld_clientid", $this->clientid);
			$this->db->where('T1.fld_del', '0');
			if($fromdate!="" && $todate!="")
			{		
				$this->db->where("(DATE_FORMAT(T1.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(T1.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
			}
			elseif($fromdate!="")
			{			

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

				$this->db->where("DATE_FORMAT(T1.fld_datetime,'%Y-%m-%d')='".$todate."'");
			}
			if($keyword!="")
			{
				$this->db->group_start();
				$this->db->like("T1.fld_refno", $keyword);
				$this->db->or_like("T1.fld_remarks", $keyword);
				$this->db->or_like("T2.fld_qty", $keyword);
				$this->db->group_end();
			}

			if($listtype != "count")
			{
				$this->db->order_by('T1.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 $this->db->last_query();
			//exit;
			if($listtype == "count")
			{
				return $results->row()->totalrecs;
			}
			else
			{
				if($results->num_rows()>0)
				{
					return $results->result();
				}
			}
		
	}
	function listordertotalreport($listtype, $params = array())
	{
			if($listtype=="") $listtype = "count";
		
			$keyword = $this->input->get("search");
			$tasktype = $this->uri->segment("1");
			$tasktype3 = $this->uri->segment("3");

			$billerid = decodeValue($this->input->get("billerid"));
			$outletid = decodeValue($this->input->get("outletid"));

			$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
			{
				if($tasktype=='account')
				{
					if($tasktype3=='sale')
					{
						$this->db->select("T1.fld_outletid,COUNT(T1.fld_id) as totalinvocies,sum(T1.fld_netamount) as totalamt,T2.fld_name as outletname");
					}
					elseif($tasktype3=='billersale')
					{
						$this->db->select("T1.fld_userid,COUNT(T1.fld_id) as totalinvocies,sum(T1.fld_netamount) as totalamt,T2.fld_name as billername");
					}
					
				}
				elseif($tasktype=='outlet')
				{
					$this->db->select("T1.fld_userid,COUNT(T1.fld_id) as totalinvocies,sum(T1.fld_netamount) as totalamt,T2.fld_name as billername");
				}
				
			}
			$this->db->from("tbl_billing T1");
			if($tasktype=='account')
			{
				if($tasktype3=='sale')
				{
					$this->db->join("tbl_outlets T2", "T2.fld_id=T1.fld_outletid");
				}
				elseif($tasktype3=='billersale')
				{
					$this->db->join("tbl_users T2", "T2.fld_id=T1.fld_userid");	
				}
					
			}
			elseif($tasktype=='outlet')
			{
				$this->db->join("tbl_users T2", "T2.fld_id=T1.fld_userid");	
			}
			$this->db->where('T1.fld_del', '0');
			if($fromdate!="" && $todate!="")
			{			
				//$this->db->where('T1.fld_datetime BETWEEN "'.$fromdate. '" and "'.$todate.'"');
				$this->db->where('DATE_FORMAT(T1.fld_datetime,"%Y-%m-%d") BETWEEN "'.$fromdate. '" and "'.$todate.'"');
			}
			elseif($fromdate!="")
			{			

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

				$this->db->where("DATE_FORMAT(T1.fld_datetime,'%Y-%m-%d')='".$todate."'");
			}

			$this->db->where('T1.fld_status','completed');
			if($billerid>0)
			{
				$this->db->where('T1.fld_userid',$billerid);		
			}
			if($tasktype=='outlet')
			{
				$this->db->where('T1.fld_outletid',$this->outletid);		
			}
			elseif($tasktype=='account' && $outletid>0)
			{
				$this->db->where('T1.fld_outletid',$outletid);		
			}
			$this->db->where("T1.fld_clientid", $this->clientid);
			if($keyword!="")
			{
				$this->db->group_start();
				$this->db->like("T2.fld_name", $keyword);
				$this->db->group_end();
			}
			if($tasktype=='outlet')
			{
				$this->db->group_by("T1.fld_userid, T2.fld_name");
			}
			elseif($tasktype=='account')
			{
				if($tasktype3=='sale')
				{
					$this->db->group_by("T1.fld_outletid, T2.fld_name");
				}
				elseif($tasktype3=='billersale')
				{
					$this->db->group_by("T1.fld_userid, T2.fld_name");
				}
				
			}
			
			$this->db->where('T1.fld_del', '0');
			if($listtype != "count")
			{
					
				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 listdayoffreport($listtype, $params = array())
	{
			if($listtype=="") $listtype = "count";
		
			$keyword = $this->input->get("search");
			$tasktype = $this->uri->segment("1");
			$tasktype3 = $this->uri->segment("3");

			$billerid = decodeValue($this->input->get("billerid"));
			if($tasktype=='outlet')
			{
				$outletid = $this->outletid;		
			}
			else
			{
				$outletid = decodeValue($this->input->get("outletid"));
			}
			if($outletid=="") $outletid = 0;
			//if($outletid>0)
			//{
				$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
				{
					if($tasktype=='account')
					{
						$this->db->select("T1.*,T2.fld_name as billername,T3.fld_name as outletname");					
					}
					elseif($tasktype=='outlet')
					{
						$this->db->select("T1.*,T2.fld_name as billername");
					}
					
				}
				$this->db->from("tbl_dayoff T1");
				if($tasktype=='account')
				{
					$this->db->join("tbl_users T2", "T2.fld_id=T1.fld_userid");	
					$this->db->join("tbl_outlets T3", "T3.fld_id=T2.fld_outletid");
					
						
				}
				elseif($tasktype=='outlet')
				{
					$this->db->join("tbl_users T2", "T2.fld_id=T1.fld_userid");	
				}
				
				if($fromdate!="" && $todate!="")
				{			
					
					$this->db->where('DATE_FORMAT(T1.fld_datetime,"%Y-%m-%d") BETWEEN "'.$fromdate. '" and "'.$todate.'"');
				}
				elseif($fromdate!="")
				{			

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

					$this->db->where("DATE_FORMAT(T1.fld_datetime,'%Y-%m-%d')='".$todate."'");
				}

				
				if($billerid>0)
				{
					$this->db->where('T1.fld_userid',$billerid);		
				}
				if($outletid>0)
				{
					$this->db->where('T2.fld_outletid',$outletid);
				}
				$this->db->where("T2.fld_clientid", $this->clientid);
				$this->db->where('T1.fld_del', '0');
				if($keyword!="")
				{
					$this->db->group_start();
					$this->db->like("T1.fld_amount", $keyword);
					$this->db->or_like("T1.fld_cash", $keyword);
					$this->db->or_like("T1.fld_cc", $keyword);
					$this->db->or_like("T1.fld_upi", $keyword);
					$this->db->group_end();
				}

				
				
				if($listtype != "count")
				{
					$this->db->order_by('T1.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 $this->db->last_query();
				//exit;
				if($listtype == "count")
				{
					return $results->row()->totalrecs;
				}
				else
				{
					if($results->num_rows()>0)
					{
						return $results->result();
					}
				}
			//}
		
	}
	function listdemanddata($listtype, $params = array(),$stocktype)
	{
		if($listtype=="") $listtype = "count";
		$tasktype = $this->uri->segment("1");
		$keyword = $this->input->get("search");		
		$warehouseid = decodeValue($this->input->get("warehouseid"));
		$outletid = decodeValue($this->input->get("outletid"));
		$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
		{
			if($stocktype=='w2a')
			{
				$this->db->select("TI.*,WI.fld_name as warehousename");
			}
			elseif($stocktype=='o2w')
			{
				$this->db->select("TI.*,WI.fld_name as warehousename,OI.fld_name as outletname");
			}		
		}

		$this->db->from("tbl_demand TI");		
		$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
		if($stocktype=='o2w')
		{			
			$this->db->join("tbl_outlets OI", "OI.fld_id=TI.fld_outletid",'left');
		}
		
		$this->db->where('TI.fld_type',$stocktype);

		if($tasktype=='warehouse')
		{
			$this->db->where('TI.fld_warehouseid',$this->warehouseid);
			if($outletid>0)
			{
				$this->db->where('TI.fld_outletid',$outletid);
			}
		}
		elseif($tasktype=='outlet')
		{
			$this->db->where('TI.fld_outletid',$this->outletid);
			if($warehouseid>0)
			{
				$this->db->where('TI.fld_warehouseid',$warehouseid);
			}
		}
		else
		{
			if($outletid>0)
			{
				$this->db->where('TI.fld_outletid',$outletid);
			}
			if($warehouseid>0)
			{
				$this->db->where('TI.fld_warehouseid',$warehouseid);
			}
		}

		$this->db->where("TI.fld_clientid", $this->clientid);
		$this->db->where("TI.fld_del", 0);

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

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

			$this->db->where("DATE_FORMAT(TI.fld_datetime,'%Y-%m-%d')='".$todate."'");
		}
		$this->db->where('TI.fld_del', '0');
		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_status", $keyword);
			
			$this->db->group_end();
		}

		if($listtype != "count")
		{
			$this->db->order_by('TI.fld_id', '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 managedata($data, $tablename, $rsrcid)
	{
		$returnvalue = 0; // default for update when info. not available
		if($rsrcid!="" && $rsrcid>0) // for update
		{
			$this->db->select("*");
			$this->db->where("fld_id", $rsrcid);
			$results = $this->db->get($this->tblprefix.$tablename);
			if($results->num_rows()>0) // update if found
			{
				$this->db->where('fld_id',$rsrcid);
				$this->db->update($this->tblprefix.$tablename, $data);
				$returnvalue = $rsrcid;
			}
		}
		else // for insert
		{
				$this->db->select("*");
				if($tablename=="zone" || $tablename=="products" || $tablename=="vendors"  || $tablename=="warehouse"   || $tablename=="outlets")
				{
					$this->db->where("fld_name", $data["fld_name"]);
					if($tablename=="vendors")
					{
						//$this->db->where("fld_email", $data["fld_email"]);
					}
					if($tablename=="zone" || $tablename=="vendors"  || $tablename=="warehouse"  || $tablename=="outlets")
					{
						$this->db->where("fld_clientid", $data["fld_clientid"]);
					}
				}
				elseif($tablename=="users")
				{
					$this->db->where("fld_username", $data["fld_username"]);
				}
				elseif($tablename=="customers")
				{
					$this->db->where("fld_mobile", $data["fld_mobile"]);
					$this->db->where("fld_outletid", $data["fld_outletid"]);
					$this->db->where("fld_clientid", $data["fld_clientid"]);
				}
				elseif($tablename=="price")
				{
					$this->db->where("fld_clientid", $data["fld_clientid"]);
					$this->db->where("fld_productid", $data["fld_productid"]);
					$this->db->where("fld_zoneid", $data["fld_zoneid"]);
					$this->db->where("fld_del", 0);
				}
				elseif($tablename=="gifts")
				{
					$this->db->where("fld_clientid", $data["fld_clientid"]);
					$this->db->where("fld_outletid", $data["fld_outletid"]);
					$this->db->where("fld_fromvalue", $data["fld_fromvalue"]);
					$this->db->where("fld_tovalue", $data["fld_tovalue"]);
					$this->db->where("fld_del", 0);
				}
				else
				{
					$this->db->where("fld_name", $data["fld_name"]);
				}				
				$results = $this->db->get($this->tblprefix.$tablename);

				if($results->num_rows()>0) // already exists
				{
					$returnvalue = -1;
				}
				else
				{
					$this->db->insert($this->tblprefix.$tablename, $data);
					$returnvalue = $this->db->insert_id();
				}
			
		}
		return $returnvalue;
	}
	
	function insertdata($tablename, $data)
	{
		$this->db->insert($this->tblprefix.$tablename, $data);
		$returnvalue = $this->db->insert_id();
		return $returnvalue;
	}
	
	
	function managestaff($data, $rsrcid)
	{
		$returnvalue = 0; // default for update when info. not available
		if($rsrcid!="" && $rsrcid>0) // for update
		{
			$this->db->select("*");
			$this->db->where("fld_id", $rsrcid);
			$results = $this->db->get("tbl_users");
			if($results->num_rows()>0) // update if found
			{
				$this->db->where('fld_id',$rsrcid);
				$this->db->update("tbl_users", $data);
				$returnvalue = $rsrcid;
			}
		}
		else // for insert
		{
			$this->db->select("*");
			$this->db->where("fld_username", $data["fld_username"]);
			$this->db->group_start();
			$this->db->where("fld_type", "admin");
			$this->db->or_where("fld_type", "staff");
			$this->db->group_end();
			$results = $this->db->get("tbl_users");
			if($results->num_rows()>0) // already exists
			{
				$row = $results->row();
				if($row->fld_del==1) // if exists but deleted then create new
				{
					$data["fld_del"] = 0;
					$this->db->where('fld_id',$row->fld_id);
					$this->db->update("tbl_users", $data);
					$returnvalue = $row->fld_id;
				}
				else
				{
					$returnvalue = -1;
				}
			}
			else
			{
				$this->db->insert("tbl_users", $data);
				$returnvalue = $this->db->insert_id();
			}
		}
		return $returnvalue;
	}
	
	function updatedata($tablename, $rsrcid, $data)
	{
		$this->db->where('fld_id',$rsrcid);
		$this->db->update($this->tblprefix.$tablename, $data);
		return true;
		//echo $this->db->last_query();
	}

	function getinfo($rsrcid, $tablename)
	{		
		if($rsrcid>0 && $tablename!="")
		{
			if($tablename=='transaction')
			{
				$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");
				$this->db->join("tbl_vendors VI", "TI.fld_vendorid=VI.fld_id");
				$this->db->where("TI.fld_id", $rsrcid);
				$results = $this->db->get();
			}
			elseif($tablename=='demand')
			{
				$this->db->select("TI.*,VI.fld_name as outletname,WI.fld_name as warehousename");
				$this->db->from("tbl_demand TI");			
				$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
				$this->db->join("tbl_outlets VI", "TI.fld_outletid=VI.fld_id",'left');
				$this->db->where("TI.fld_id", $rsrcid);
				$results = $this->db->get();
			}
			elseif($tablename=='products')
			{
				$this->db->select("TI.*,CI.fld_name as catname,CI.fld_type as cattype");
				$this->db->from("tbl_products TI");			
				$this->db->join("tbl_category CI", "TI.fld_catid=CI.fld_id", "left");
				$this->db->where("TI.fld_id", $rsrcid);
				$results = $this->db->get();
			}
			else
			{
				$this->db->select("*");
				$this->db->where("fld_id", $rsrcid);
				$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 gettransinfolist($transactionid)
	{
		$this->db->select("POI.*, ITI.fld_name");		
		$this->db->from("tbl_transactioninfo POI");
		$this->db->join("tbl_products ITI", "ITI.fld_id=POI.fld_productid",'left');

		$this->db->where("POI.fld_transactionid", $transactionid);
		$this->db->order_by('ITI.fld_name', 'asc');
		$query = $this->db->get();
        //echo $this->db->last_query();
        if($query -> num_rows() > 0)
        {            
            return $query->result();
        }
		else
		{
			return false;
		}
	}
	function getbillinginfolist($transactionid)
	{
		$this->db->select("POI.*, ITI.fld_name");		
		$this->db->from("tbl_billinginfo POI");
		$this->db->join("tbl_products ITI", "ITI.fld_id=POI.fld_productid",'left');

		$this->db->where("POI.fld_billingid", $transactionid);
		$this->db->order_by('POI.fld_id', 'asc');
		$query = $this->db->get();
        //echo $this->db->last_query();
        if($query -> num_rows() > 0)
        {            
            return $query->result();
        }
		else
		{
			return false;
		}
	}
	
	function getdemandinfo($rsrcid,$type)
	{		
		$datainfo = array();
		$tasktype = $this->uri->segment("1");
		if($rsrcid>0 && ($type=='w2a' || $type=='o2w'))
		{
			if($tasktype=='o2w')
			{
				$this->db->select("T1.*,T2.fld_name as warehousename,T3.fld_name as outletname");
			}
			elseif($tasktype=='w2a')
			{
				$this->db->select("T1.*,T2.fld_name as warehousename");
			}
			$this->db->from("tbl_demand T1");
			$this->db->join("tbl_warehouse T2", "T1.fld_warehouseid=T2.fld_id", "left");
			if($tasktype=='o2w')
			{				
				$this->db->join("tbl_outlets T3", "T1.fld_outletid=T3.fld_id",'left');
			}
			
			$this->db->where("T1.fld_id", $rsrcid);
			
			//echo $this->db->last_query();
			$result = $this->db->get();
			if($result->num_rows()>0)
			{
				$row = $result->row();
				$datainfo["main"] = $row;
				
				$this->db->select("TII.*");
				$this->db->from("tbl_demandinfo TII");
				$this->db->where("TII.fld_demandid", $rsrcid);
				$result2 = $this->db->get();
				//secho $this->db->last_query();
				if($result2->num_rows()>0)
				{
					foreach($result2->result() as $row2)
					{
						$datainfo["info"][] = $row2;					
					}
					return $datainfo;
				}
			}
			else
			{
				return false;
			}
		}
		else
		{
			return false;
		}
	}
	function getdemandinfolist($transactionid)
	{
		$this->db->select("POI.*, ITI.fld_name");		
		$this->db->from("tbl_demandinfo POI");
		$this->db->join("tbl_products ITI", "ITI.fld_id=POI.fld_productid",'left');

		$this->db->where("POI.fld_demandid", $transactionid);
		$this->db->order_by('ITI.fld_name', 'asc');
		$query = $this->db->get();
        //echo $this->db->last_query();
        if($query -> num_rows() > 0)
        {            
            return $query->result();
        }
		else
		{
			return false;
		}
	}
	function exportdemandinfolist($transactionid)
	{
		$this->db->select("ITI.fld_name,POI.fld_qty");		
		$this->db->from("tbl_demandinfo POI");
		$this->db->join("tbl_products ITI", "ITI.fld_id=POI.fld_productid",'left');

		$this->db->where("POI.fld_demandid", $transactionid);
		$this->db->order_by('ITI.fld_name', 'asc');
		$query = $this->db->get();
        //echo $this->db->last_query();
        if($query -> num_rows() > 0)
        {            
            return $query->result_array();
        }
		else
		{
			return false;
		}
	}
	function getlist($tablename, $fields,$orderbyfldname)
	{
		if($fields=="") $fields = "*";
		if($tablename!="")
		{
			$this->db->select("$fields");
			if($tablename=="vendors" || $tablename=="zone"  || $tablename=="warehouse"|| $tablename=="outlets"   || $tablename=="customers")
			{	
				$this->db->where("fld_clientid", $this->clientid);
				
			}
			$this->db->where('fld_del', '0');
			if($orderbyfldname!="")
			{
				$this->db->order_by($orderbyfldname, 'asc');
			}
			$results = $this->db->get($this->tblprefix.$tablename);

			if($results->num_rows()>0)
			{			
				return $results->result();
			}
			else
			{
				return false;
			}
		}
	}
	
	function getproducttypes()
	{
		$this->db->distinct();
		$this->db->select("fld_type");
		$this->db->where("fld_type!=", "");
		$results = $this->db->get("tbl_products");
		if($results->num_rows()>0)
		{			
			return $results->result();
		}
		else
		{
			return false;
		}
	}
	
	function getlistbywhere($tablename, $fields,$orderbyfldname,$where)
	{
		
		if($fields=="") $fields = "*";
		if($tablename!="")
		{
			$this->db->select("$fields");
			if($where!="")
			{
				$this->db->where("$where");
			}
			$this->db->where('fld_del', '0');
			if($orderbyfldname!="")
			{
				$this->db->order_by($orderbyfldname);
			}
			$results = $this->db->get($this->tblprefix.$tablename);
			//echo $this->db->last_query(); //exit;
			if($results->num_rows()>0)
			{			
				return $results->result();
			}
			else
			{
				return false;
			}
		}
	}
	function getprodlistbycattype($type)
	{		
			$this->db->select("tbl_products.*");
			$this->db->from('tbl_products');
			$this->db->join("tbl_category", "tbl_category.fld_id=tbl_products.fld_catid");
			if($type=='normal')
			{
				$this->db->where("tbl_category.fld_type='normal'");
			}
			elseif($type=='gift')
			{
				$this->db->where("tbl_category.fld_type='gift'");
				
			}
			$this->db->where('tbl_products.fld_del', '0');
			$this->db->order_by('tbl_products.fld_name');
			$results = $this->db->get();
			//echo $this->db->last_query(); //exit;
			if($results->num_rows()>0)
			{			
				return $results->result();
			}
			else
			{
				return false;
			}
		
	}
	
	function listoutletsstock($listtype, $params = array(),$outletid)
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");		
		//$outletid = decodeValue($this->input->get("outletid"));
		$type = $this->input->get("type");
		
		$pid = decodeValue($this->input->get("pid"));
		$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,TI.fld_productid");
		}		
		$this->db->from("tbl_products PI");
		$this->db->join("tbl_transactioninfo TI", "TI.fld_productid=PI.fld_id");		
		$this->db->join("tbl_transaction T", "TI.fld_transactionid=T.fld_id");
		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($pid>0)
		{
			$this->db->where('PI.fld_id',$pid);
		}
		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();
		}
		$this->db->where('T.fld_del', '0');
		$this->db->where('T.fld_outletid',$outletid);
		$this->db->where('T.fld_clientid',$this->clientid);
		$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', '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;
						$this->db->select("fld_mrp");
						$this->db->where("fld_productid",$prodid);
						$ppquery = $this->db->get('tbl_price');
						$pprow = $ppquery->row();
						$mrp = $pprow->fld_mrp;

						$arr[$prodid] = array(
							"id" => $prodid,
							"name" => $row->fld_name,
							"previousstock" => 0,
							"incoming" => 0,
							"outgoing" => 0,
							"instock" => 0,
							"stocktransfer" => 0,
							"soldqty" => 0,
							"mrp" => $mrp,							
							"totprice" => 0,
							"totinvoces" => 0,
							"totspecialdiscount" => 0,
							"minstockvalue" => $row->fld_minstockvalue,
						);		
						if($arr[$prodid])
						{
							### totinvoces
							$this->db->select("count(BI.fld_billno) as totinvoces, SUM(BI.fld_discount) as totspecialdiscount");
							$this->db->from("tbl_billing BI");
							$this->db->where("BI.fld_status!='cancelled'");
							if($outletid>0)
							{
								$this->db->where('BI.fld_outletid',$outletid);
							}
							$this->db->where('BI.fld_clientid',$this->clientid);
							if($fromdate!="" && $todate!="")
							{		
								$this->db->where("(DATE_FORMAT(BI.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(BI.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
							}
							elseif($fromdate!="")
							{			

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

								$this->db->where("DATE_FORMAT(BI.fld_datetime,'%Y-%m-%d')='".$todate."'");
							}
							$this->db->where("BI.fld_del","0");
							$query7 = $this->db->get();			
							$row7 = $query7->row();
							//echo $this->db->last_query(); exit;

							$arr[$prodid]["totinvoces"] += $row7->totinvoces;
							$arr[$prodid]["totspecialdiscount"] += $row7->totspecialdiscount;

							### previous stock from date stocktransfer
							$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->group_start();
							$this->db->where("TI2.fld_type", "stockout");
							$this->db->or_where("TI2.fld_type", "o2o");
							$this->db->group_end();
							$this->db->where("TI2.fld_del", "0");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_outletid',$outletid);
							$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;
							
							/*$this->db->select("SUM(TII2.fld_qty) as pincoming2");
							$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='o2o')");
							$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->where("DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d')<'".$fromdate."'");
							$this->db->group_by('TII2.fld_productid');
							$piquery2 = $this->db->get();
							$pirow2 = $piquery2->row();
							$pincoming += $pirow2->pincoming2;*/

							
							$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->group_start();
							$this->db->where("TI2.fld_type", "o2o");
							$this->db->or_where("TI2.fld_type", "o2w");
							$this->db->group_end();
							$this->db->where("TI2.fld_del", "0");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_fromoutletid',$outletid);
							$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;

							$this->db->select("SUM(TII2.fld_qty) as poutgoing2");
							$this->db->from("tbl_billinginfo TII2");
							$this->db->join("tbl_billing TI2", "TII2.fld_billingid=TI2.fld_id");
							$this->db->where("TII2.fld_productid",$prodid);
							$this->db->where("(TI2.fld_status!='cancelled')");							
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where("TI2.fld_del","0");
							$this->db->where('TI2.fld_outletid',$outletid);
							$this->db->where("DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d')<'".$fromdate."'");							
							$this->db->group_by('TII2.fld_productid');
							$poquery2 = $this->db->get();
							$porow2 = $poquery2->row();
							$poutgoing += $porow2->poutgoing2;

							/*$this->db->select("SUM(TII2.fld_gqty) as poutgoing3");
							$this->db->from("tbl_billing TII2");
							$this->db->where("TII2.fld_gprodid=",$prodid);
							$this->db->where("(TII2.fld_status!='cancelled')");							
							$this->db->where('TII2.fld_clientid',$this->clientid);
							if($outletid>0)
							{
								$this->db->where('TII2.fld_outletid',$outletid);
							}
							$this->db->where("DATE_FORMAT(TII2.fld_datetime,'%Y-%m-%d')<'".$fromdate."'");							
							$this->db->group_by('TII2.fld_gprodid');
							$poquery3 = $this->db->get();
							$porow3 = $poquery3->row();
							$poutgoing += $porow3->poutgoing3;*/
							
							$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='stockout')");
							$this->db->where("TI2.fld_del", "0");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_outletid',$outletid);
							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 out from billing outgoing
							
							$this->db->select("SUM(TII5.fld_qty) as boutgoing,SUM(TII5.fld_qty*TII5.fld_price) as totprice");
							$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_del","0");
							$this->db->where('TI5.fld_outletid',$outletid);
							if($fromdate!="" && $todate!="")
							{		
								$this->db->where("(DATE_FORMAT(TI5.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(TI5.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
							}
							elseif($fromdate!="")
							{			

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

								$this->db->where("DATE_FORMAT(TI5.fld_datetime,'%Y-%m-%d')='".$todate."'");
							}
							$this->db->group_by('TII5.fld_productid');
							$query5 = $this->db->get();
							//echo $this->db->last_query();
							$row5 = $query5->row();
							$boutgoing = $row5->boutgoing;
							$arr[$prodid]["outgoing"] += $boutgoing;
							$arr[$prodid]["soldqty"] += $row5->boutgoing;
							$arr[$prodid]["totprice"] += $row5->totprice;
							
							### stock from billing outgoing
							$this->db->select("SUM(BI.fld_gqty) as goutgoing");
							$this->db->from("tbl_billing BI");
							$this->db->where("BI.fld_gprodid=",$prodid);
							$this->db->where("BI.fld_status!='cancelled'");
							$this->db->where("BI.fld_del","0");
							if($outletid>0)
							{
								$this->db->where('BI.fld_outletid',$outletid);
							}
							$this->db->where('BI.fld_clientid',$this->clientid);
							if($fromdate!="" && $todate!="")
							{		
								$this->db->where("(DATE_FORMAT(BI.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(BI.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
							}
							elseif($fromdate!="")
							{			

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

								$this->db->where("DATE_FORMAT(BI.fld_datetime,'%Y-%m-%d')='".$todate."'");
							}
							$query6 = $this->db->get();			
							$row6 = $query6->row();
							//echo $this->db->last_query();
							$goutgoing = $row6->goutgoing;
							$arr[$prodid]["outgoing"] += $goutgoing;
							//$arr[$prodid]["soldqty"] += $row6->goutgoing;

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

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

									$this->db->where("DATE_FORMAT(TI3.fld_datetime,'%Y-%m-%d')='".$todate."'");
								}
								$this->db->group_by('TII3.fld_productid');
								$query3 = $this->db->get();
								//echo $this->db->last_query();
								$row3 = $query3->row();
								$oincomingqty = $row3->oincomingqty;
								$arr[$prodid]["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_del", "0");
								$this->db->where('TI4.fld_clientid',$this->clientid);
								$this->db->where('TI4.fld_fromoutletid',$outletid);
								if($fromdate!="" && $todate!="")
								{		
									$this->db->where("(DATE_FORMAT(TI4.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(TI4.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
								}
								elseif($fromdate!="")
								{			

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

									$this->db->where("DATE_FORMAT(TI4.fld_datetime,'%Y-%m-%d')='".$todate."'");
								}
								$this->db->group_by('TII4.fld_productid');
								$query4 = $this->db->get();
								//echo $this->db->last_query();
								$row4 = $query4->row();
								$ooutgoingqty = $row4->ooutgoingqty;
								$arr[$prodid]["outgoing"] += $ooutgoingqty;
								$arr[$prodid]["stocktransfer"] = $row4->ooutgoingqty;
							}
							$arr[$prodid]["instock"] = $previousstock +($arr[$prodid]["incoming"]-$arr[$prodid]["outgoing"]);
						}
					}
				}
			}
			return $arr;
		}
	}
	
	function listoutletsstocktypewise($listtype, $params = array(),$outletid)
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");		
		//$outletid = decodeValue($this->input->get("outletid"));
		$type = $this->input->get("type");
		
		$pid = decodeValue($this->input->get("pid"));
		$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_type,TI.fld_productid");
		}		
		$this->db->from("tbl_products PI");
		$this->db->join("tbl_transactioninfo TI", "TI.fld_productid=PI.fld_id");		
		$this->db->join("tbl_transaction T", "TI.fld_transactionid=T.fld_id");
		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($pid>0)
		{
			$this->db->where('PI.fld_id',$pid);
		}
		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();
		}
		$this->db->where('T.fld_del', '0');
		$this->db->where('T.fld_outletid',$outletid);
		$this->db->where('T.fld_clientid',$this->clientid);
		$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', '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)
					{
						$prodtype = $row->fld_type;
						$prodid = $row->fld_productid;
						$this->db->select("fld_mrp");
						$this->db->where("fld_productid",$prodid);
						$ppquery = $this->db->get('tbl_price');
						$pprow = $ppquery->row();
						$mrp = $pprow->fld_mrp;

						$arr[$prodtype][$prodid] = array(
							"id" => $prodid,
							"name" => $row->fld_name,
							"previousstock" => 0,
							"incoming" => 0,
							"outgoing" => 0,
							"instock" => 0,
							"stocktransfer" => 0,
							"soldqty" => 0,
							"mrp" => $mrp,							
							"totprice" => 0,
							"totinvoces" => 0,
							"totspecialdiscount" => 0,
							"minstockvalue" => $row->fld_minstockvalue,
						);		
						if($arr[$prodtype][$prodid])
						{
							### totinvoces
							$this->db->select("count(BI.fld_billno) as totinvoces, SUM(BI.fld_discount) as totspecialdiscount");
							$this->db->from("tbl_billing BI");
							$this->db->where("BI.fld_status!='cancelled'");
							if($outletid>0)
							{
								$this->db->where('BI.fld_outletid',$outletid);
							}
							$this->db->where('BI.fld_clientid',$this->clientid);
							if($fromdate!="" && $todate!="")
							{		
								$this->db->where("(BI.fld_billdate >= '".$fromdate."' and BI.fld_billdate <= '".$todate."')");
							}
							elseif($fromdate!="")
							{
								$this->db->where("BI.fld_billdate", $fromdate);
							}
							elseif($todate!="")
							{
								$this->db->where("BI.fld_billdate", $todate);
							}
							$this->db->where("BI.fld_del","0");
							$query7 = $this->db->get();			
							$row7 = $query7->row();
							//echo $this->db->last_query(); exit;

							$arr[$prodtype][$prodid]["totinvoces"] += $row7->totinvoces;
							$arr[$prodtype][$prodid]["totspecialdiscount"] += $row7->totspecialdiscount;

							### previous stock from date stocktransfer
							$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->group_start();
							$this->db->where("TI2.fld_type", "stockout");
							$this->db->or_where("TI2.fld_type", "o2o");
							$this->db->group_end();
							$this->db->where("TI2.fld_del", "0");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_outletid',$outletid);
							$this->db->where("TI2.fld_refdate<'".$fromdate."'");
							$this->db->group_by('TII2.fld_productid');
							$piquery = $this->db->get();
							$pirow = $piquery->row();
							$pincoming = $pirow->pincoming;
							
							/*$this->db->select("SUM(TII2.fld_qty) as pincoming2");
							$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='o2o')");
							$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->where("DATE_FORMAT(TI2.fld_datetime,'%Y-%m-%d')<'".$fromdate."'");
							$this->db->group_by('TII2.fld_productid');
							$piquery2 = $this->db->get();
							$pirow2 = $piquery2->row();
							$pincoming += $pirow2->pincoming2;*/

							
							$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->group_start();
							$this->db->where("TI2.fld_type", "o2o");
							$this->db->or_where("TI2.fld_type", "o2w");
							$this->db->group_end();
							$this->db->where("TI2.fld_del", "0");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_fromoutletid',$outletid);
							$this->db->where("TI2.fld_refdate<'".$fromdate."'");							
							$this->db->group_by('TII2.fld_productid');
							$poquery = $this->db->get();
							$porow = $poquery->row();
							$poutgoing = $porow->poutgoing;

							$this->db->select("SUM(TII2.fld_qty) as poutgoing2");
							$this->db->from("tbl_billinginfo TII2");
							$this->db->join("tbl_billing TI2", "TII2.fld_billingid=TI2.fld_id");
							$this->db->where("TII2.fld_productid",$prodid);
							$this->db->where("(TI2.fld_status!='cancelled')");							
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where("TI2.fld_del","0");
							$this->db->where('TI2.fld_outletid',$outletid);
							$this->db->where("TI2.fld_billdate<'".$fromdate."'");							
							$this->db->group_by('TII2.fld_productid');
							$poquery2 = $this->db->get();
							$porow2 = $poquery2->row();
							$poutgoing += $porow2->poutgoing2;

							/*$this->db->select("SUM(TII2.fld_gqty) as poutgoing3");
							$this->db->from("tbl_billing TII2");
							$this->db->where("TII2.fld_gprodid=",$prodid);
							$this->db->where("(TII2.fld_status!='cancelled')");							
							$this->db->where('TII2.fld_clientid',$this->clientid);
							if($outletid>0)
							{
								$this->db->where('TII2.fld_outletid',$outletid);
							}
							$this->db->where("DATE_FORMAT(TII2.fld_datetime,'%Y-%m-%d')<'".$fromdate."'");							
							$this->db->group_by('TII2.fld_gprodid');
							$poquery3 = $this->db->get();
							$porow3 = $poquery3->row();
							$poutgoing += $porow3->poutgoing3;*/
							
							$previousstock =  ($pincoming-$poutgoing);
							$arr[$prodtype][$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='stockout')");
							$this->db->where("TI2.fld_del", "0");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_outletid',$outletid);
							if($fromdate!="" && $todate!="")
							{		
								$this->db->where("(TI2.fld_refdate >= '".$fromdate."' and TI2.fld_refdate <= '".$todate."')");
							}
							elseif($fromdate!="")
							{
								$this->db->where("TI2.fld_refdate", $fromdate);
							}
							elseif($todate!="")
							{
								$this->db->where("TI2.fld_refdate", $todate);
							}
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$wincomingqty = $row2->wincomingqty;
							$arr[$prodtype][$prodid]["incoming"] += $wincomingqty;
							

							### stock out from billing outgoing
							
							$this->db->select("SUM(TII5.fld_qty) as boutgoing,SUM(TII5.fld_qty*TII5.fld_price) as totprice");
							$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_del","0");
							$this->db->where('TI5.fld_outletid',$outletid);
							if($fromdate!="" && $todate!="")
							{		
								$this->db->where("(TI5.fld_billdate >= '".$fromdate."' and TI5.fld_billdate <= '".$todate."')");
							}
							elseif($fromdate!="")
							{			

								$this->db->where("TI5.fld_billdate", $fromdate);
							}
							elseif($todate!="")
							{
								$this->db->where("TI5.fld_billdate", $todate);
							}
							$this->db->group_by('TII5.fld_productid');
							$query5 = $this->db->get();
							//echo $this->db->last_query();
							$row5 = $query5->row();
							$boutgoing = $row5->boutgoing;
							$arr[$prodtype][$prodid]["outgoing"] += $boutgoing;
							$arr[$prodtype][$prodid]["soldqty"] += $row5->boutgoing;
							$arr[$prodtype][$prodid]["totprice"] += $row5->totprice;
							
							### stock from billing outgoing
							$this->db->select("SUM(BI.fld_gqty) as goutgoing");
							$this->db->from("tbl_billing BI");
							$this->db->where("BI.fld_gprodid=",$prodid);
							$this->db->where("BI.fld_status!='cancelled'");
							$this->db->where("BI.fld_del","0");
							if($outletid>0)
							{
								$this->db->where('BI.fld_outletid',$outletid);
							}
							$this->db->where('BI.fld_clientid',$this->clientid);
							if($fromdate!="" && $todate!="")
							{		
								$this->db->where("(BI.fld_billdate >= '".$fromdate."' and BI.fld_billdate <= '".$todate."')");
							}
							elseif($fromdate!="")
							{
								$this->db->where("BI.fld_billdate", $fromdate);
							}
							elseif($todate!="")
							{
								$this->db->where("BI.fld_billdate", $todate);
							}
							$query6 = $this->db->get();			
							$row6 = $query6->row();
							//echo $this->db->last_query();
							$goutgoing = $row6->goutgoing;
							$arr[$prodtype][$prodid]["outgoing"] += $goutgoing;
							//$arr[$prodtype][$prodid]["soldqty"] += $row6->goutgoing;

							if($outletid>0)
							{
								### 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);
								if($fromdate!="" && $todate!="")
								{		
									$this->db->where("(TI3.fld_refdate >= '".$fromdate."' and TI3.fld_refdate <= '".$todate."')");
								}
								elseif($fromdate!="")
								{
									$this->db->where("TI3.fld_refdate", $fromdate);
								}
								elseif($todate!="")
								{
									$this->db->where("TI3.fld_refdate", $todate);
								}
								$this->db->group_by('TII3.fld_productid');
								$query3 = $this->db->get();
								//echo $this->db->last_query();
								$row3 = $query3->row();
								$oincomingqty = $row3->oincomingqty;
								$arr[$prodtype][$prodid]["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_del", "0");
								$this->db->where('TI4.fld_clientid',$this->clientid);
								$this->db->where('TI4.fld_fromoutletid',$outletid);
								if($fromdate!="" && $todate!="")
								{		
									$this->db->where("(TI4.fld_refdate >= '".$fromdate."' and TI4.fld_refdate <= '".$todate."')");
								}
								elseif($fromdate!="")
								{
									$this->db->where("TI4.fld_refdate", $fromdate);
								}
								elseif($todate!="")
								{			

									$this->db->where("TI4.fld_refdate", $todate);
								}
								$this->db->group_by('TII4.fld_productid');
								$query4 = $this->db->get();
								//echo $this->db->last_query();
								$row4 = $query4->row();
								$ooutgoingqty = $row4->ooutgoingqty;
								$arr[$prodtype][$prodid]["outgoing"] += $ooutgoingqty;
								$arr[$prodtype][$prodid]["stocktransfer"] = $row4->ooutgoingqty;
							}
							$arr[$prodtype][$prodid]["instock"] = $previousstock +($arr[$prodtype][$prodid]["incoming"]-$arr[$prodtype][$prodid]["outgoing"]);
						}
					}
				}
			}
			return $arr;
		}
	}
	


	function getstocktransferinfo($rsrcid,$type)
	{		
		
		if($rsrcid>0 && ($type=='warehouse' || $type=='outlet' || $type=='o2w'))
		{
			if($type=='warehouse')
			{
				$this->db->select("T1.*,T2.fld_name as warehousename,T3.fld_name as outletname");
			}
			elseif($type=='outlet')
			{
				$this->db->select("T1.*,T3.fld_name as outletname");
			}
			elseif($type=='o2w')
			{
				$this->db->select("T1.*,T2.fld_name as warehousename,T3.fld_name as outletname");
			}
			$this->db->from("tbl_transaction T1");
			if($type=='warehouse')
			{
				$this->db->join("tbl_warehouse T2", "T1.fld_warehouseid=T2.fld_id", "left");
				$this->db->join("tbl_outlets T3", "T1.fld_outletid=T3.fld_id",'left');
			}
			elseif($type=='outlet')
			{
				$this->db->join("tbl_outlets T2", "T1.fld_fromoutletid=T2.fld_id",'left');
				$this->db->join("tbl_outlets T3", "T1.fld_outletid=T3.fld_id",'left');
			}
			elseif($type=='o2w')
			{
				$this->db->join("tbl_warehouse T2", "T1.fld_warehouseid=T2.fld_id", "left");
				$this->db->join("tbl_outlets T3", "T1.fld_fromoutletid=T3.fld_id",'left');
				
			}
			
			$this->db->where("T1.fld_id", $rsrcid);
			$results = $this->db->get();
			//echo $this->db->last_query();
			if($results->num_rows()>0)
			{
				$row = $results->row();
				return $row;
			}
			else
			{
				return false;
			}
		}
		else
		{
			return false;
		}
	}
	
	function getstockreceivedinfo($rsrcid)
	{		
		if($rsrcid>0 )
		{
			$this->db->select("TI.*,VI.fld_name as outletname,VI2.fld_name as tooutletname,WI.fld_name as warehousename");
			$this->db->from("tbl_transaction TI");			
			$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
			$this->db->join("tbl_outlets VI", "TI.fld_fromoutletid=VI.fld_id",'left');
			$this->db->join("tbl_outlets VI2", "TI.fld_outletid=VI2.fld_id",'left');
			$this->db->where("TI.fld_id", $rsrcid);
			$results = $this->db->get();
			//echo $this->db->last_query();
			if($results->num_rows()>0)
			{
				$row = $results->row();
				return $row;
			}
			else
			{
				return false;
			}
		}
		else
		{
			return false;
		}
	}

	function getdayoffinfo($rsrcid)
	{		
		if($rsrcid>0 )
		{
			$this->db->select("TI.*,VI.fld_name as outletname,WI.fld_name as billername");
			$this->db->from("tbl_dayoff TI");			
			$this->db->join("tbl_users WI", "TI.fld_userid=WI.fld_id", "left");
			$this->db->join("tbl_outlets VI", "WI.fld_outletid=VI.fld_id",'left');
			$this->db->where("TI.fld_id", $rsrcid);
			$results = $this->db->get();
			//echo $this->db->last_query();
			if($results->num_rows()>0)
			{
				$row = $results->row();
				return $row;
			}
			else
			{
				return false;
			}
		}
		else
		{
			return false;
		}
	}

	function getwarehousestocklist($warehouseid)
	{
		$arrstockincoming = array();
		$arrstockoutgoing = array();
		### stock 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='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();
		if($query2->num_rows()>0)
		{
			foreach($query2->result() as $row2)
			{
				$arrstockincoming[$row2->fld_productid] = $row2->wincomingqty;
			}
		}

		### stock 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->where("(TI4.fld_type='stockout')");
		$this->db->where("TI4.fld_clientid",$this->clientid);
		$this->db->where("TI4.fld_warehouseid",$warehouseid);
		$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)
			{
				$arrstockoutgoing[$row4->fld_productid] = $row4->ooutgoingqty;
			}
		}


		$this->db->select("P.fld_name,TI.fld_productid,P.fld_noofbottlesincase");
		$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->where("(T.fld_status='completed' or T.fld_status='delivered')");
		$this->db->group_by('TI.fld_productid,P.fld_name,P.fld_noofbottlesincase');

		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		
		$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(
						"productid" => $prodid,
						"name" => $row->fld_name,
						"noofbottlesincase" => $row->fld_noofbottlesincase,
						"incoming" => 0,
						"outgoing" => 0,
						"instock" => 0
					);
					if($arr[$prodid])
					{
						$wincomingqty = 0;
						if($arrstockincoming[$prodid]) $wincomingqty = $arrstockincoming[$prodid];
						$arr[$prodid]["incoming"] += $wincomingqty;

						$ooutgoingqty = 0;
						if($arrstockoutgoing[$prodid]) $ooutgoingqty = $arrstockoutgoing[$prodid];
						$arr[$prodid]["outgoing"] += $ooutgoingqty;

						$arr[$prodid]["instock"] = $arr[$prodid]["incoming"]-$arr[$prodid]["outgoing"];
					}
				}
			}
		}
		return $arr;
	}

	function getoutletstocklist($outletid)
	{

		$this->db->select("P.fld_name,TI.fld_productid,P.fld_noofbottlesincase");
		$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_outletid',$outletid);
		$this->db->where('T.fld_clientid',$this->clientid);
		$this->db->where("(T.fld_status='completed' or T.fld_status='delivered')");
		$this->db->where("T.fld_del","0");
		$this->db->group_by('TI.fld_productid,P.fld_name,P.fld_noofbottlesincase');
		$this->db->order_by("P.fld_name", "asc");

		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		
			$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(
							"productid" => $prodid,
							"name" => $row->fld_name,
							"noofbottlesincase" => $row->fld_noofbottlesincase,
							"incoming" => 0,
							"outgoing" => 0,
							"instock" => 0,
							"price1bottles" => 0,
							"price3bottles" => 0,
							"price6bottles" => 0,
							"price9bottles" => 0,
							"price12bottles" => 0,
							"price24bottles" => 0
						);	


						if($arr[$prodid])
						{

							### Price

							if($outletid>0)
							{
								$this->db->select("PP.fld_price, PP.fld_price3bottles, PP.fld_price6bottles, PP.fld_price9bottles, PP.fld_price12bottles,PP.fld_price24bottles");
								$this->db->from("tbl_price PP");
								$this->db->join("tbl_outlets OI", "OI.fld_zoneid=PP.fld_zoneid");
								$this->db->where("PP.fld_productid",$prodid);
								$this->db->where('OI.fld_id',$this->outletid);
								$this->db->where('PP.fld_clientid',$this->clientid);								
								$ppquery = $this->db->get();
								$pprow = $ppquery->row();
								if($pprow->fld_price>0)
								{
									$arr[$prodid]["price1bottles"] = $pprow->fld_price;
								}
								if($pprow->fld_price3bottles>0)
								{
									$arr[$prodid]["price3bottles"] = $pprow->fld_price3bottles;
								}
								if($pprow->fld_price6bottles>0)
								{
									$arr[$prodid]["price6bottles"] = $pprow->fld_price6bottles;
								}
								if($pprow->fld_price9bottles>0)
								{
									$arr[$prodid]["price9bottles"] = $pprow->fld_price9bottles;
								}
								if($pprow->fld_price12bottles>0)
								{
									$arr[$prodid]["price12bottles"] = $pprow->fld_price12bottles;
								}
								if($pprow->fld_price24bottles>0)
								{
									$arr[$prodid]["price24bottles"] = $pprow->fld_price24bottles;
								}
								
							}
							### 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_clientid',$this->clientid);
							$this->db->where("TI2.fld_del","0");
							if($outletid>0)
							{
								$this->db->where('TI2.fld_outletid',$outletid);
							}
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$wincomingqty = $row2->wincomingqty;
							$arr[$prodid]["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_del","0");
							if($outletid>0)
							{
								$this->db->where('TI5.fld_outletid',$outletid);
							}
							$this->db->group_by('TII5.fld_productid');
							$query5 = $this->db->get();
							$row5 = $query5->row();
							$boutgoing = $row5->boutgoing;
							$arr[$prodid]["outgoing"] += $boutgoing;
							
							### stock from billing outgoing
							$this->db->select("SUM(BI.fld_gqty) as goutgoing");
							$this->db->from("tbl_billing BI");
							$this->db->where("BI.fld_gprodid=",$prodid);
							$this->db->where("BI.fld_status!='cancelled'");
							if($outletid>0)
							{
								$this->db->where('BI.fld_outletid',$outletid);
							}
							$this->db->where('BI.fld_clientid',$this->clientid);
							$this->db->where("BI.fld_del","0");
							$query6 = $this->db->get();			
							$row6 = $query6->row();
							$goutgoing = $row6->goutgoing;
							$arr[$prodid]["outgoing"] += $goutgoing;

							if($outletid>0)
							{
								### 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_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();
								//echo $this->db->last_query();
								$row3 = $query3->row();
								$oincomingqty = $row3->oincomingqty;
								$arr[$prodid]["incoming"] += $oincomingqty;

								### stock from outlet incoming
								$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_del","0");
								$this->db->group_by('TII4.fld_productid');
								$query4 = $this->db->get();
								//echo $this->db->last_query();
								$row4 = $query4->row();
								$ooutgoingqty = $row4->ooutgoingqty;
								$arr[$prodid]["outgoing"] += $ooutgoingqty;

							}

							$arr[$prodid]["instock"] = $arr[$prodid]["incoming"]-$arr[$prodid]["outgoing"];

						}						
						

					
					}


					//predata($arr);

				}
					
			}
			return $arr;
		
	}

	function exportStockList($warehouseid="")
	{
		if($listtype=="") $listtype = "count";
		
		$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');
		}	
		
		$this->db->select("TI.fld_productid  as ID, P.fld_name as ProductName,SUM( Case When T.fld_type= 'stockin'  then TI.fld_qty end) as Incoming,SUM( Case When T.fld_type= 'stockout'  then TI.fld_qty end) as Outgoing, abs(SUM( Case When T.fld_type= 'stockin'  then TI.fld_qty end)-SUM( Case When T.fld_type= 'stockout'  then TI.fld_qty end)) as Instock");		
		$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");

		if($warehouseid>0)
		{
			$this->db->where('T.fld_warehouseid',$warehouseid);
		}
		
		$this->db->where('T.fld_clientid',$this->clientid);
		$this->db->where("(T.fld_status='completed' or T.fld_status='delivered')");
		if($fromdate!="" && $todate!="")
		{		
			$this->db->where("(DATE_FORMAT(T.fld_datetime,'%Y-%m-%d') >= '".$fromdate."' and DATE_FORMAT(T.fld_datetime,'%Y-%m-%d') <= '".$todate."')");
		}
		elseif($fromdate!="")
		{			

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

			$this->db->where("DATE_FORMAT(T.fld_datetime,'%Y-%m-%d')='".$todate."'");
		}
		$this->db->group_by('TI.fld_productid,P.fld_name');

		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		return $results->result_array();
	}
	
	function exportOutletStockList($warehouseid="")
	{
		
		$this->db->select("TI.fld_productid  as ID, P.fld_name as ProductName,SUM( Case When T.fld_type= 'stockin'  then TI.fld_qty end) as Incoming,SUM( Case When T.fld_type= 'stockout'  then TI.fld_qty end) as Outgoing, abs(SUM( Case When T.fld_type= 'stockin'  then TI.fld_qty end)-SUM( Case When T.fld_type= 'stockout'  then TI.fld_qty end)) as Instock");		
		$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");

		if($warehouseid>0)
		{
			$this->db->where('T.fld_warehouseid',$warehouseid);
		}
		
		$this->db->where('T.fld_clientid',$this->clientid);
		$this->db->where("(T.fld_status='completed' or T.fld_status='delivered')");
		$this->db->group_by('TI.fld_productid,P.fld_name');

		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		return $results->result_array();
	}
	function getoutletproductlist($outletid)
	{

		$this->db->select("P.fld_name,TI.fld_productid,P.fld_noofbottlesincase,P.fld_barcode");
		$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_outletid',$outletid);
		$this->db->where('T.fld_clientid',$this->clientid);
		
		$this->db->where("(T.fld_status='completed' or T.fld_status='delivered')");
		$this->db->group_by('TI.fld_productid,P.fld_name,P.fld_noofbottlesincase,P.fld_barcode');
		$this->db->where('P.fld_del', '0');
		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
		
			$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;
						### Price							
								$this->db->select("PP.fld_price, PP.fld_price3bottles, PP.fld_price6bottles, PP.fld_price9bottles, PP.fld_price12bottles,PP.fld_price24bottles");
								$this->db->from("tbl_price PP");
								$this->db->join("tbl_outlets OI", "OI.fld_zoneid=PP.fld_zoneid");
								$this->db->where("PP.fld_productid",$prodid);
								$this->db->where('OI.fld_id',$this->outletid);
								$this->db->where('PP.fld_clientid',$this->clientid);								
								$ppquery = $this->db->get();
								$pprow = $ppquery->row();
								if($pprow->fld_price>0 && $pprow->fld_price3bottles>0 && $pprow->fld_price6bottles>0 && $pprow->fld_price9bottles>0 && $pprow->fld_price12bottles>0 && $pprow->fld_price24bottles>0)
								{									
										$arr[$prodid] = array(
										"productid" => $prodid,
										"name" => $row->fld_name,				
										"noofbottlesincase" => $row->fld_noofbottlesincase,
										"incoming" => 0,
										"outgoing" => 0,
										"instock" => 0,
										"price1bottles" => 0,
										"price3bottles" => 0,
										"price6bottles" => 0,
										"price9bottles" => 0,
										"price12bottles" => 0,
										"price24bottles" => 0,
										"barcode" => $row->fld_barcode,
										"minstockvalue" => $row->fld_minstockvalue
									);
									if($pprow->fld_price>0)
									{
										$arr[$prodid]["price1bottles"] = $pprow->fld_price;
									}
									if($pprow->fld_price3bottles>0)
									{
										$arr[$prodid]["price3bottles"] = $pprow->fld_price3bottles;
									}
									if($pprow->fld_price6bottles>0)
									{
										$arr[$prodid]["price6bottles"] = $pprow->fld_price6bottles;
									}
									if($pprow->fld_price9bottles>0)
									{
										$arr[$prodid]["price9bottles"] = $pprow->fld_price9bottles;
									}
									if($pprow->fld_price12bottles>0)
									{
										$arr[$prodid]["price12bottles"] = $pprow->fld_price12bottles;
									}
									if($pprow->fld_price24bottles>0)
									{
										$arr[$prodid]["price24bottles"] = $pprow->fld_price24bottles;
									}

									### 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_clientid',$this->clientid);
									if($outletid>0)
									{
										$this->db->where('TI2.fld_outletid',$outletid);
									}
									$this->db->group_by('TII2.fld_productid');
									$query2 = $this->db->get();
									$row2 = $query2->row();
									$wincomingqty = $row2->wincomingqty;
									$arr[$prodid]["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);
									if($outletid>0)
									{
										$this->db->where('TI5.fld_outletid',$outletid);
									}
									$this->db->group_by('TII5.fld_productid');
									$query5 = $this->db->get();
									$row5 = $query5->row();
									$boutgoing = $row5->boutgoing;
									$arr[$prodid]["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_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;
									$arr[$prodid]["incoming"] += $oincomingqty;

									### stock from outlet incoming
									$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')");
									$this->db->where('TI4.fld_clientid',$this->clientid);
									$this->db->where('TI4.fld_fromoutletid',$outletid);
									$this->db->group_by('TII4.fld_productid');
									$query4 = $this->db->get();
									//echo $this->db->last_query();
									$row4 = $query4->row();
									$ooutgoingqty = $row4->ooutgoingqty;
									$arr[$prodid]["outgoing"] += $ooutgoingqty;

								

									$arr[$prodid]["instock"] = abs($arr[$prodid]["incoming"]-$arr[$prodid]["outgoing"]);
								}					
						

					
					}


					//predata($arr);

				}
					
			}
			return $arr;
		
	}
	function getproductinfo($value,$outletid)
	{

		$this->db->select("P.fld_name,P.fld_id ,P.fld_noofbottlesincase,fld_barcode");
		$this->db->from("tbl_products P");		
		$this->db->where('P.fld_id',$value);
		$this->db->or_where('P.fld_barcode',$value);
		$results = $this->db->get();
		//echo $this->db->last_query();
		//exit;
			if($results->num_rows()>0 && $outletid>0)
			{
				
								$row = $results->row();	
								$prodid = $row->fld_id;
								### Price							
								$this->db->select("PP.fld_price, PP.fld_price3bottles, PP.fld_price6bottles, PP.fld_price9bottles, PP.fld_price12bottles,PP.fld_price24bottles");
								$this->db->from("tbl_price PP");
								$this->db->join("tbl_outlets OI", "OI.fld_zoneid=PP.fld_zoneid");
								$this->db->where("PP.fld_productid",$prodid);
								$this->db->where('OI.fld_id',$this->outletid);
								$this->db->where('PP.fld_clientid',$this->clientid);								
								$ppquery = $this->db->get();
								$pprow = $ppquery->row();
								if($pprow->fld_price>0 && $pprow->fld_price3bottles>0 && $pprow->fld_price6bottles>0 && $pprow->fld_price9bottles>0 && $pprow->fld_price12bottles>0 && $pprow->fld_price24bottles>0)
								{
										
										$arr = array(
										"productid" => $prodid,
										"name" => $row->fld_name,				
										"noofbottlesincase" => $row->fld_noofbottlesincase,
										"incoming" => 0,
										"outgoing" => 0,
										"instock" => 0,
										"price1bottles" => 0,
										"price3bottles" => 0,
										"price6bottles" => 0,
										"price9bottles" => 0,
										"price12bottles" => 0,
										"price24bottles" => 0,
										"barcode" => $row->fld_barcode
									);
									if($pprow->fld_price>0)
									{
										$arr["price1bottles"] = $pprow->fld_price;
									}
									if($pprow->fld_price3bottles>0)
									{
										$arr["price3bottles"] = $pprow->fld_price3bottles;
									}
									if($pprow->fld_price6bottles>0)
									{
										$arr["price6bottles"] = $pprow->fld_price6bottles;
									}
									if($pprow->fld_price9bottles>0)
									{
										$arr["price9bottles"] = $pprow->fld_price9bottles;
									}
									if($pprow->fld_price12bottles>0)
									{
										$arr["price12bottles"] = $pprow->fld_price12bottles;
									}
									if($pprow->fld_price24bottles>0)
									{
										$arr["price24bottles"] = $pprow->fld_price24bottles;
									}

									### 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_clientid',$this->clientid);
									if($outletid>0)
									{
										$this->db->where('TI2.fld_outletid',$outletid);
									}
									$this->db->group_by('TII2.fld_productid');
									$query2 = $this->db->get();
									$row2 = $query2->row();
									$wincomingqty = $row2->wincomingqty;
									$arr["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);
									if($outletid>0)
									{
										$this->db->where('TI5.fld_outletid',$outletid);
									}
									$this->db->group_by('TII5.fld_productid');
									$query5 = $this->db->get();
									$row5 = $query5->row();
									$boutgoing = $row5->boutgoing;
									$arr["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_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;
									$arr["incoming"] += $oincomingqty;

									### stock from outlet incoming
									$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')");
									$this->db->where('TI4.fld_clientid',$this->clientid);
									$this->db->where('TI4.fld_fromoutletid',$outletid);
									$this->db->group_by('TII4.fld_productid');
									$query4 = $this->db->get();
									//echo $this->db->last_query();
									$row4 = $query4->row();
									$ooutgoingqty = $row4->ooutgoingqty;
									$arr["outgoing"] += $ooutgoingqty;

								

									$arr["instock"] = abs($arr["incoming"]-$arr["outgoing"]);
								}
							
					
			}
			return $arr;
		
	}
	function getdayoffsale($outletid,$userid)
	{	
		$currdate = date('Y-m-d');
		$totalamount  =0;
		
		$this->db->select('SUM(fld_netamount) as totalamount');
		$this->db->where('fld_outletid',$outletid);
		$this->db->where('fld_userid',$userid);
		$this->db->where("DATE_FORMAT(fld_datetime,'%Y-%m-%d') ", $currdate);
		$this->db->where("(fld_status='completed')");
		$query = $this->db->get('tbl_billing');
       // echo $this->db->last_query();
        if($query -> num_rows() > 0)
        {
			$row = $query -> row();
			$totalamount  = $row->totalamount;

			## dayoff records
			$this->db->select('SUM(fld_amount) as dayofftotamt');			
			$this->db->where('fld_userid',$userid);
			$this->db->where("DATE_FORMAT(fld_datetime,'%Y-%m-%d') ", $currdate);
			
			$query2 = $this->db->get('tbl_dayoff');
		   //echo $this->db->last_query();
			if($query2 -> num_rows() > 0)
			{
				$row2 = $query2 -> row();
				$dayofftotamt  = $row2->dayofftotamt;
				$totalamount  = abs($totalamount-$dayofftotamt);
			}
		}
		if($totalamount=="")
		{
			$totalamount = 0;
		}
		return $totalamount;
	}
	
	function getproductids4zone($zoneid)
	{
		$this->db->distinct();
		$this->db->select("fld_zoneid, fld_productid");
		$this->db->where("fld_zoneid", $zoneid);
		$result = $this->db->get("tbl_price");
		if($result->num_rows()>0) return $result->result();
	}
	
	function exportdata($outletid)
	{
		if($outletid>0)
		{
			$arrproducts = array();
			
			## get outlet price
			$arrproductprice = array();
			$this->db->select("PRI.*, OI.fld_name as outletname, ZI.fld_name as zonename");
			$this->db->from("tbl_price PRI");
			$this->db->join("tbl_zone ZI", "PRI.fld_zoneid=ZI.fld_id");
			$this->db->join("tbl_outlets OI", "PRI.fld_zoneid=OI.fld_zoneid");
			$this->db->where("OI.fld_id", $outletid);
			$this->db->where("PRI.fld_del", "0");
			$result = $this->db->get();
			if($result->num_rows()>0)
			{
				while ($row = $result->unbuffered_row())
				{
					$arrproductprice[$row->fld_productid] = array(
						"fld_mrp" => $row->fld_mrp,
						"fld_price" => $row->fld_price,
						"fld_price3bottles" => $row->fld_price3bottles,
						"fld_price6bottles" => $row->fld_price6bottles,
						"fld_price9bottles" => $row->fld_price9bottles,
						"fld_price12bottles" => $row->fld_price12bottles,
						"fld_price24bottles" => $row->fld_price24bottles,
						"outletname" => $row->outletname
					);
				}
			}
			
			## get incoming stock to outlet
			$arrstockincoming = array();
			$this->db->select("TII.fld_productid, sum(TII.fld_qty) as incomingstock");
			$this->db->from("tbl_transactioninfo TII");
			$this->db->join("tbl_transaction TI", "TII.fld_transactionid=TI.fld_id");
			$this->db->group_start();
			$this->db->where("TI.fld_type", "stockout");
			$this->db->or_where("TI.fld_type", "o2o");
			$this->db->group_end();
			$this->db->where("TI.fld_outletid", $outletid);
			$this->db->where("TI.fld_del", "0");
			$this->db->group_start();
			$this->db->where("TI.fld_status", "completed");
			$this->db->or_where("TI.fld_status", "delivered");
			$this->db->group_end();
			$this->db->group_by("TII.fld_productid");
			$result02 = $this->db->get();
			if($result02->num_rows()>0)
			{
				while ($row02 = $result02->unbuffered_row())
				{
					$arrstockincoming[$row02->fld_productid] = $row02->incomingstock;
				}
			}
			
			## get outgoingn stock from outlet to another outlet and warehouse
			$arrstockoutgoing = array();
			$this->db->select("TII.fld_productid, sum(TII.fld_qty) as outgoingstock");
			$this->db->from("tbl_transactioninfo TII");
			$this->db->join("tbl_transaction TI", "TII.fld_transactionid=TI.fld_id");
			$this->db->group_start();
			$this->db->where("TI.fld_type", "o2w");
			$this->db->or_where("TI.fld_type", "o2o");
			$this->db->group_end();
			$this->db->where("TI.fld_fromoutletid", $outletid);
			$this->db->where("TI.fld_del", "0");
			$this->db->group_start();
			$this->db->where("TI.fld_status", "completed");
			$this->db->or_where("TI.fld_status", "delivered");
			$this->db->group_end();
			$this->db->group_by("TII.fld_productid");
			$result03 = $this->db->get();
			if($result03->num_rows()>0)
			{
				while ($row03 = $result03->unbuffered_row())
				{
					$arrstockoutgoing[$row03->fld_productid] = $row03->outgoingstock;
				}
			}
			
			## get outlet billed stock
			$arrbilledstock = array();
			$this->db->select("BII.fld_productid, sum(BII.fld_qty) as billedstock");
			$this->db->from("tbl_billinginfo BII");
			$this->db->join("tbl_billing BI", "BII.fld_billingid=BI.fld_id");
			$this->db->where("BI.fld_outletid", $outletid);
			$this->db->where("BI.fld_del", "0");
			$this->db->where("BI.fld_status", "completed");
			$this->db->group_by("BII.fld_productid");
			$result04 = $this->db->get();
			if($result04->num_rows()>0)
			{
				while ($row04 = $result04->unbuffered_row())
				{
					$arrbilledstock[$row04->fld_productid] = $row04->billedstock;
				}
			}
			
			## get all products
			$this->db->select("PI.fld_id, PI.fld_name, PI.fld_type, PI.fld_barcode, PI.fld_noofbottlesincase, PI.fld_shortdesc, CI.fld_name as categoryname, BI.fld_name as brandname");
			$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");
			$this->db->where("PI.fld_del", "0");
			$result05 = $this->db->get();
			if($result05->num_rows()>0)
			{
				while ($row05 = $result05->unbuffered_row())
				{
					$arrproducts[$row05->fld_id] = array(
						"Name" => $row05->fld_name,
						"Desc" => $row05->fld_shortdesc,
						"Type" => $row05->fld_type,
						"Brand" => $row05->brandname,
						"Category" => $row05->categoryname,
						"Barcode1" => $row05->fld_barcode,
						"Stock" => 0,
						"Number of Bottles in Case" => $row05->fld_noofbottlesincase,
						"MRP" => 0,
						"1 Bottle Price" => 0,
						"3 Bottle Price" => 0,
						"6 Bottle Price" => 0,
						"9 Bottle Price" => 0,
						"12 Bottle Price" => 0,
						"24 Bottle Price" => 0,
						"Outlet Name" => ""
					);
					
					## update price if exists
					if($arrproductprice[$row05->fld_id])
					{
						$arrproducts[$row05->fld_id]["MRP"] = $arrproductprice[$row05->fld_id]["fld_mrp"];
						$arrproducts[$row05->fld_id]["1 Bottle Price"] = $arrproductprice[$row05->fld_id]["fld_price"];
						$arrproducts[$row05->fld_id]["3 Bottle Price"] = $arrproductprice[$row05->fld_id]["fld_price3bottles"];
						$arrproducts[$row05->fld_id]["6 Bottle Price"] = $arrproductprice[$row05->fld_id]["fld_price6bottles"];
						$arrproducts[$row05->fld_id]["9 Bottle Price"] = $arrproductprice[$row05->fld_id]["fld_price9bottles"];
						$arrproducts[$row05->fld_id]["12 Bottle Price"] = $arrproductprice[$row05->fld_id]["fld_price12bottles"];
						$arrproducts[$row05->fld_id]["24 Bottle Price"] = $arrproductprice[$row05->fld_id]["fld_price24bottles"];
						$arrproducts[$row05->fld_id]["Outlet Name"] = $arrproductprice[$row05->fld_id]["outletname"];
					}
					
					## update stock
					
					
					
					$incomingstockqty = 0;
					$outgoingstockqty = 0;
					$billedstockqty = 0;
					$stockqty = 0;
					if($arrstockincoming[$row05->fld_id]) $incomingstockqty = $arrstockincoming[$row05->fld_id];
					if($arrstockoutgoing[$row05->fld_id]) $outgoingstockqty = $arrstockoutgoing[$row05->fld_id];
					if($arrbilledstock[$row05->fld_id]) $billedstockqty = $arrbilledstock[$row05->fld_id];
					$stockqty = $incomingstockqty - $outgoingstockqty - $billedstockqty; 
					$arrproducts[$row05->fld_id]["Stock"] = $stockqty;
				}
			}
		}
		return $arrproducts;
	}
	
	function getitemledger($resourcetype, $params)
	{
		$productid = 0;
		$warehouseid = 0;
		$fromdate = "";
		$todate = "";
		$outletid = "";
		if($params["productid"]) $productid = decodeValue($params["productid"]);
		if($params["warehouseid"]) $warehouseid = decodeValue($params["warehouseid"]);
		if($params["outletid"]) $outletid = decodeValue($params["outletid"]);
		if($params["fromdate"]) $fromdate = $params["fromdate"];
		if($params["todate"]) $todate = $params["todate"];
		
		$arrdata = array();
		if($resourcetype=="admin")
		{
			$productid = 0;
			if($params["productid"]) $productid = decodeValue($params["productid"]);
			$fromdate = "";
			$todate = "";
			if($params["fromdate"]) $fromdate = $params["fromdate"];
			if($params["todate"]) $todate = $params["todate"];
			if($productid>0)
			{
				$balance = 0;
				if($fromdate!="" && $todate!="")
				{
					$fromdate = date("Y-m-d", strtotime($fromdate));
					$todate = date("Y-m-d", strtotime($todate));
					$this->db->select("TI2.fld_refno, TI2.fld_refdate, TI2.fld_type, TII2.fld_qty, VI.fld_name as vendorname, WI.fld_name as warehousename, TI2.fld_del, TI2.fld_datetime as createdon");
					$this->db->from("tbl_transactioninfo TII2");
					$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
					$this->db->join("tbl_vendors VI", "TI2.fld_vendorid=VI.fld_id", "left");
					$this->db->join("tbl_warehouse WI", "TI2.fld_warehouseid=WI.fld_id", "left");
					$this->db->where("TII2.fld_productid",$productid);
					$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
					$this->db->where("TI2.fld_del", "0");
					$this->db->group_start();
					$this->db->where("TI2.fld_type", "stockin");
					$this->db->or_where("TI2.fld_type", "stockout");
					$this->db->group_end();
					$this->db->where("TI2.fld_refdate<", $fromdate);
					$result02 = $this->db->get();
					if($result02->num_rows()>0)
					{
						$received = 0;
						$delivered = 0;
						foreach($result02->result() as $row02)
						{
							if($row02->fld_type=="stockin")
							{
								$received += $row02->fld_qty;
							}
							else
							{
								$delivered += $row02->fld_qty;
							}
						}
						if($received>0 || $delivered>0)
						{
							$balance += $received - $delivered;
							$newfromdate = date("Y-m-d", (strtotime($fromdate)-85400));
							$arrdata[] = array(
								"refno" => "",
								"refdate" => date("d-M-Y", strtotime($newfromdate)),
								"type" => "stockin",
								"particular" => "Stock as on " . date("d-M-Y", strtotime($newfromdate)),
								"qty" => 0,
								"receivedqty" => 0,
								"deliveredqty" => 0,
								"delstatus" => "0",
								"balance" => $balance
							);
						}
					}
				}
				
				$this->db->select("TI2.fld_refno, TI2.fld_refdate, TI2.fld_type, TII2.fld_qty, VI.fld_name as vendorname, WI.fld_name as warehousename, TI2.fld_del, TI2.fld_datetime as createdon");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->join("tbl_vendors VI", "TI2.fld_vendorid=VI.fld_id", "left");
				$this->db->join("tbl_warehouse WI", "TI2.fld_warehouseid=WI.fld_id", "left");
				$this->db->where("TII2.fld_productid",$productid);
				$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
				$this->db->where("TI2.fld_del", "0");
				$this->db->group_start();
				$this->db->where("TI2.fld_type", "stockin");
				$this->db->or_where("TI2.fld_type", "stockout");
				$this->db->group_end();
				if($fromdate!="" && $todate!="")
				{
					$this->db->group_start();
					$this->db->where("TI2.fld_refdate>=", $fromdate);
					$this->db->where("TI2.fld_refdate<=", $todate);
					$this->db->group_end();
				}				
				$this->db->order_by("TI2.fld_datetime", "ASC");
				$result = $this->db->get();
				if($result->num_rows()>0)
				{
					foreach($result->result() as $row)
					{
						$particular = "";
						$receivedqty = 0;
						$deliveredqty = 0;
						if($row->fld_type=="stockin")
						{
							$particular = "By " . $row->vendorname;
							$receivedqty = $row->fld_qty;
						}
						else
						{
							$particular = "To " . $row->warehousename;
							$deliveredqty = $row->fld_qty;
						}
						if($row->fld_del==0) $balance += $receivedqty - $deliveredqty;
						
						$arrdata[] = array(
							"refno" => $row->fld_refno,
							"refdate" => date("d-M-Y", strtotime($row->fld_refdate)),
							"type" => $row->fld_type,
							"particular" => $particular,
							"qty" => $row->fld_qty,
							"receivedqty" => $receivedqty,
							"deliveredqty" => $deliveredqty,
							"delstatus" => $row->fld_del,
							"balance" => $balance
						);
					}
				}
			}
		}
		elseif($resourcetype=="warehouse")
		{
			if($productid>0 && $warehouseid>0)
			{
				$balance = 0;
				if($fromdate!="" && $todate!="")
				{
					$fromdate = date("Y-m-d", strtotime($fromdate));
					$todate = date("Y-m-d", strtotime($todate));
					$this->db->select("TI2.fld_refno, TI2.fld_refdate, TI2.fld_type, TII2.fld_qty, VI.fld_name as vendorname, WI.fld_name as warehousename, TI2.fld_del, TI2.fld_datetime as createdon");
					$this->db->from("tbl_transactioninfo TII2");
					$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
					$this->db->join("tbl_vendors VI", "TI2.fld_vendorid=VI.fld_id", "left");
					$this->db->join("tbl_warehouse WI", "TI2.fld_warehouseid=WI.fld_id", "left");
					$this->db->where("TII2.fld_productid",$productid);
					$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
					$this->db->where("TI2.fld_del", "0");
					$this->db->where("TI2.fld_warehouseid", $warehouseid);
					$this->db->group_start();
					$this->db->where("TI2.fld_type", "stockin");
					$this->db->or_where("TI2.fld_type", "stockout");
					$this->db->or_where("TI2.fld_type", "o2w");
					$this->db->group_end();
					$this->db->where("TI2.fld_refdate<", $fromdate);
					$result02 = $this->db->get();
					if($result02->num_rows()>0)
					{
						$received = 0;
						$delivered = 0;
						foreach($result02->result() as $row02)
						{
							if($row02->fld_type=="stockin" || $row02->fld_type=="o2w")
							{
								$received += $row02->fld_qty;
							}
							else
							{
								$delivered += $row02->fld_qty;
							}
						}
						if($received>0 || $delivered>0)
						{
							$balance += $received - $delivered;
							$newfromdate = date("Y-m-d", (strtotime($fromdate)-85400));
							$arrdata[] = array(
								"refno" => "",
								"refdate" => date("d-M-Y", strtotime($newfromdate)),
								"type" => "stockin",
								"particular" => "Stock as on " . date("d-M-Y", strtotime($newfromdate)),
								"qty" => 0,
								"receivedqty" => 0,
								"deliveredqty" => 0,
								"delstatus" => "0",
								"balance" => $balance
							);
						}
					}
				}
				
				$this->db->select("TI2.fld_refno, TI2.fld_refdate, TI2.fld_type, TII2.fld_qty, VI.fld_name as vendorname, WI.fld_name as warehousename, TI2.fld_del, TI2.fld_datetime as createdon, OI.fld_name as outletname");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->join("tbl_vendors VI", "TI2.fld_vendorid=VI.fld_id", "left");
				$this->db->join("tbl_warehouse WI", "TI2.fld_warehouseid=WI.fld_id", "left");
				$this->db->join("tbl_outlets OI", "TI2.fld_fromoutletid=OI.fld_id", "left");
				$this->db->where("TII2.fld_productid",$productid);
				$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
				$this->db->where("TI2.fld_del", "0");
				$this->db->where("TI2.fld_warehouseid", $warehouseid);
				$this->db->group_start();
				$this->db->where("TI2.fld_type", "stockin");
				$this->db->or_where("TI2.fld_type", "stockout");
				$this->db->or_where("TI2.fld_type", "o2w");
				$this->db->group_end();
				if($fromdate!="" && $todate!="")
				{
					$this->db->group_start();
					$this->db->where("TI2.fld_refdate>=", $fromdate);
					$this->db->where("TI2.fld_refdate<=", $todate);
					$this->db->group_end();
				}				
				$this->db->order_by("TI2.fld_datetime", "ASC");
				$result = $this->db->get();
				if($result->num_rows()>0)
				{
					foreach($result->result() as $row)
					{
						$particular = "";
						$receivedqty = 0;
						$deliveredqty = 0;
						if($row->fld_type=="stockin" || $row->fld_type=="o2w")
						{
							$name2show = $row->vendorname;
							if($row->fld_type=="o2w") $name2show = $row->outletname;
							$particular = "By " . $name2show;
							$receivedqty = $row->fld_qty;
						}
						else
						{
							$particular = "To " . $row->warehousename;
							$deliveredqty = $row->fld_qty;
						}
						if($row->fld_del==0) $balance += $receivedqty - $deliveredqty;
						
						$type2show = $row->fld_type;
						if($type2show=="o2w") $type2show = "stockin";
						
						$arrdata[] = array(
							"refno" => $row->fld_refno,
							"refdate" => date("d-M-Y", strtotime($row->fld_refdate)),
							"type" => $type2show,
							"particular" => $particular,
							"qty" => $row->fld_qty,
							"receivedqty" => $receivedqty,
							"deliveredqty" => $deliveredqty,
							"delstatus" => $row->fld_del,
							"balance" => $balance
						);
					}
				}
			}
		}
		elseif($resourcetype=="outlet")
		{
			if($productid>0 && $outletid>0)
			{
				$balance = 0;
				if($fromdate!="" && $todate!="")
				{
					$fromdate = date("Y-m-d", strtotime($fromdate));
					$todate = date("Y-m-d", strtotime($todate));
					
					# received from warehouse and other outlet
					$this->db->select("sum(TII2.fld_qty) as qtyreceivedfromware");
					$this->db->from("tbl_transactioninfo TII2");
					$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
					$this->db->where("TII2.fld_productid",$productid);
					$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
					$this->db->where("TI2.fld_del", "0");
					if($warehouseid>0) $this->db->where("TI2.fld_warehouseid", $warehouseid);
					$this->db->where("TI2.fld_outletid", $outletid);
					$this->db->group_start();
					$this->db->or_where("TI2.fld_type", "stockout");
					$this->db->or_where("TI2.fld_type", "o2o");
					$this->db->group_end();
					$this->db->where("TI2.fld_refdate<", $fromdate);
					$result02 = $this->db->get();
					$qtyreceivedfromware = $result02->row()->qtyreceivedfromware;
					
					# transferred to other outlet and warehouse
					$this->db->select("sum(TII2.fld_qty) as qtytransferredtownoutlet");
					$this->db->from("tbl_transactioninfo TII2");
					$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
					$this->db->where("TII2.fld_productid",$productid);
					$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
					$this->db->where("TI2.fld_del", "0");
					if($warehouseid>0) $this->db->where("TI2.fld_warehouseid", $warehouseid);
					$this->db->where("TI2.fld_fromoutletid", $outletid);
					$this->db->group_start();
					$this->db->or_where("TI2.fld_type", "o2w");
					$this->db->or_where("TI2.fld_type", "o2o");
					$this->db->group_end();
					$this->db->where("TI2.fld_refdate<", $fromdate);
					$result02 = $this->db->get();
					$qtytransferredtownoutlet = $result02->row()->qtytransferredtownoutlet;
					
					# delivered in billing
					$this->db->select("sum(BII.fld_qty) as qtybilled");
					$this->db->from("tbl_billinginfo BII");
					$this->db->join("tbl_billing BI", "BII.fld_billingid=BI.fld_id");
					$this->db->where("BII.fld_productid",$productid);
					$this->db->where("BI.fld_status", "completed");
					$this->db->where("BI.fld_del", "0");
					$this->db->where("BI.fld_outletid", $outletid);
					$this->db->where("BI.fld_billdate<", $fromdate);
					$result02 = $this->db->get();
					$qtybilled = $result02->row()->qtybilled;
					
					$balance = $qtyreceivedfromware - $qtytransferredtownoutlet - $qtybilled;
					if($balance>0)
					{
						$balance += $received - $delivered;
						$newfromdate = date("Y-m-d", (strtotime($fromdate)-85400));
						$arrdata[] = array(
							"refno" => "",
							"refdate" => date("d-M-Y", strtotime($newfromdate)),
							"type" => "stockin",
							"particular" => "Stock as on " . date("d-M-Y", strtotime($newfromdate)),
							"qty" => 0,
							"receivedqty" => 0,
							"deliveredqty" => 0,
							"delstatus" => "0",
							"balance" => $balance
						);
					}
				}
				
				
				# received from warehouse and other outlet and vice versa
				$this->db->select("TI2.fld_refno, TI2.fld_refdate, TI2.fld_type, TI2.fld_outletid, TI2.fld_fromoutletid, TII2.fld_qty, WI.fld_name as warehousename, TI2.fld_del, TI2.fld_datetime as createdon, OI.fld_name as fromoutletname, OI2.fld_name as tooutletname");
				$this->db->from("tbl_transactioninfo TII2");
				$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
				$this->db->join("tbl_warehouse WI", "TI2.fld_warehouseid=WI.fld_id", "left");
				$this->db->join("tbl_outlets OI", "TI2.fld_fromoutletid=OI.fld_id", "left");
				$this->db->join("tbl_outlets OI2", "TI2.fld_outletid=OI2.fld_id", "left");
				$this->db->where("TII2.fld_productid",$productid);
				$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
				$this->db->where("TI2.fld_del", "0");
				if($warehouseid>0) $this->db->where("TI2.fld_warehouseid", $warehouseid);
				$this->db->group_start();
				$this->db->where("TI2.fld_outletid", $outletid);
				$this->db->or_where("TI2.fld_fromoutletid", $outletid);
				$this->db->group_end();
				$this->db->group_start();
				$this->db->or_where("TI2.fld_type", "stockout");
				$this->db->or_where("TI2.fld_type", "o2o");
				$this->db->or_where("TI2.fld_type", "o2w");
				$this->db->group_end();
				if($fromdate!="" && $todate!="")
				{
					$this->db->group_start();
					$this->db->where("TI2.fld_refdate>=", $fromdate);
					$this->db->where("TI2.fld_refdate<=", $todate);
					$this->db->group_end();
				}
				$this->db->order_by("TI2.fld_refdate", "ASC");
				$result02 = $this->db->get();
				if($result02->num_rows()>0)
				{
					foreach($result02->result() as $row)
					{
						$particular = "";
						$receivedqty = 0;
						$deliveredqty = 0;
						$name2show = "";
						
						$type2show = $row->fld_type;
						
						if($row->fld_type=="stockout")
						{
							$name2show = $row->warehousename;
							$particular = "By " . $name2show;
							$receivedqty = $row->fld_qty;
							$type2show = "stockin";
						}
						elseif($row->fld_type=="o2o")
						{
							if($row->fld_outletid==$outletid) // received
							{
								$name2show = $row->fromoutletname;
								$particular = "By " . $name2show;
								$receivedqty = $row->fld_qty;
								$type2show = "stockin";
							}
							if($row->fld_fromoutletid==$outletid) // delivered/transferred
							{
								$name2show = $row->tooutletname;
								$particular = "To " . $name2show;
								$deliveredqty = $row->fld_qty;
								$type2show = "stockout33";
							}
						}
						elseif($row->fld_type=="o2w")
						{
							$particular = "To " . $row->warehousename;
							$deliveredqty = $row->fld_qty;
							$type2show = "stockout";
						}
						
						$balance += $receivedqty - $deliveredqty;

						$arrdata[] = array(
							"refno" => $row->fld_refno,
							"refdate" => date("d-M-Y", strtotime($row->fld_refdate)),
							"type" => $type2show,
							"particular" => $particular,
							"qty" => $row->fld_qty,
							"receivedqty" => $receivedqty,
							"deliveredqty" => $deliveredqty,
							"delstatus" => $row->fld_del,
							"balance" => $balance
						);
					}
				}
				
				# delivered in billing
				$this->db->select("BI.fld_billno, BI.fld_billdate, BII.fld_qty");
				$this->db->from("tbl_billinginfo BII");
				$this->db->join("tbl_billing BI", "BII.fld_billingid=BI.fld_id");
				$this->db->where("BII.fld_productid",$productid);
				$this->db->where("BI.fld_status", "completed");
				$this->db->where("BI.fld_del", "0");
				$this->db->where("BI.fld_outletid", $outletid);
				if($fromdate!="" && $todate!="")
				{
					$this->db->group_start();
					$this->db->where("BI.fld_billdate>=", $fromdate);
					$this->db->where("BI.fld_billdate<=", $todate);
					$this->db->group_end();
				}
				$result02 = $this->db->get();
				if($result02->num_rows()>0)
				{
					foreach($result02->result() as $row)
					{
						$balance = $balance - $row->fld_qty;
						
						$arrdata[] = array(
							"refno" => $row->fld_billno,
							"refdate" => date("d-M-Y", strtotime($row->fld_billdate)),
							"type" => "stockout",
							"particular" => "Billing",
							"qty" => $row->fld_qty,
							"receivedqty" => "0",
							"deliveredqty" => $row->fld_qty,
							"delstatus" => "0",
							"balance" => $balance
						);
					}
				}
			}
		}
		//predata($arrdata); exit;
		return $arrdata;
	}

	function checktpnumber($type, $transtype, $typeid, $refno)
	{
		$response = "notexists";
		$this->db->select("count(*) as totalrecs");
		$this->db->where("fld_refno", $refno);
		$this->db->where("fld_type", $transtype);
		if($type=="warehouse")
		{
			$this->db->where("fld_warehouseid", $typeid);
		}
		$query = $this->db->get("tbl_transaction");
        //echo $this->db->last_query();
        $totalrecs = $query->row()->totalrecs;
        if($totalrecs>0)
		{
			$response = "exists";
		}
		return $response;
	}
	
	
}
?>
