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

class Outlet_model extends CI_Model{
        
    public function __construct()
    {
        parent::__construct();
    }
    
    function checklogin()
    {
		$username = $this->input->post("username");
		$userpass = $this->input->post("userpass");
		if($username!="" && $userpass!="")
		{
			$this->db->select("*");
			$this->db->where("fld_username", $username);
			$this->db->where("fld_userpass", md5($userpass));
			$this->db->where("fld_outletid>", 0);
			$this->db->where("fld_status", "active");
			$this->db->where("fld_type='manager'");
			$this->db->where('fld_del', '0');
			$result = $this->db->get("tbl_users");
			//echo $this->db->last_query();
			if($result->num_rows()>0)
			{
				$row = $result->row();	
				$this->db->select("fld_name,fld_company");
				$this->db->where("fld_id", $row->fld_clientid);
				$result2 = $this->db->get("tbl_clients");				
				//echo $this->db->last_query();
				if($result2->num_rows()>0)
				{
					$row2 = $result2->row();
					$company = $row2->fld_company;

					if($row->fld_outletid>0)
					{
						$this->db->select("fld_name");
						$this->db->where("fld_id", $row->fld_outletid);
						$result3 = $this->db->get("tbl_outlets");				
						//echo $this->db->last_query();
						if($result3->num_rows()>0)
						{
							$row3 = $result3->row();
							$outletname = $row3->fld_name;

							$data = array(								
								'sessCLIENTID' => $row->fld_clientid,
								'sessOUTLETID' => $row->fld_outletid,
								'sessID' => $row->fld_id,
								'sessCompanyname' => $company,
								'sessOTLName' => $outletname,
								'sessName' => $row->fld_name,
								'sessUserName' => $row->fld_username,
								'sessType' => 'manager',
								'sessEmail' => $row->fld_email,
								'sessLoggedInStatus' => true
								);
							//predata($data); exit;
							$this->session->set_userdata('outlettLoggedIn', $data);
							return true;
						}
						else
						{
							return false;
						}
					}
					else
					{
						return false;
					}
				}
				else
				{
					return false;
				}
				
			}
			else
			{
				return false;
			}			
		}
		
	}

	function manageuserpass($data, $currpass)
	{
		$returnvalue = "failed";
		$userid = $this->lguserinfo["sessID"];
		$this->db->select("*");
		$this->db->where("fld_id", $userid);
		$results = $this->db->get("tbl_users");
		if($results->num_rows()>0) // update if found
		{
			$row = $results->row();
			if($row->fld_userpass==md5($currpass))
			{
				$this->db->where('fld_id',$userid);
				$this->db->update("tbl_users", $data);
				$returnvalue = "success";
			}
			else
			{
				$returnvalue = "notmatched";
			}
		}
		return $returnvalue;
	}
	
	function acknowledgestock($rsrcid)
	{
		$this->db->select("fld_id");
		$this->db->where("fld_id", $rsrcid);
		$results = $this->db->get('tbl_transaction');
		//echo $this->db->last_query();
		if($results->num_rows()>0)
		{
			$data = array(						
			"fld_status" => 'completed',
			"fld_receivedby" => $this->userid,
			"fld_receivedon" => date('Y-m-d'),
			"fld_updatedby" => $this->lguserinfo["sessID"],
			"fld_updatedon" => date("Y-m-d H:i:s")	
			);

			$this->db->where('fld_id',$rsrcid);
			$this->db->update('tbl_transaction', $data);
			//echo $this->db->last_query();

			return true;
		}
		else
		{
			return false;
		}		
	}

	function liststockdata($listtype, $params,$stocktype)
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");

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

		if($fromdt=="" && $todt=="")
		{
			$fromdate = date('Y-m-d');
			$todate = date('Y-m-d');
		}

		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			if($stocktype=='o2o')
			{
				$this->db->select("TI.*,OI.fld_name as toouletname");
			}
			elseif($stocktype=='o2w')
			{
				$this->db->select("TI.*,WI.fld_name as warehousename");
			}
			else
			{
				$this->db->select("TI.*");
			}			
		}
		$this->db->from("tbl_transaction TI");		
		if($stocktype=='o2o')
		{
			$this->db->join("tbl_outlets OI", "OI.fld_id=TI.fld_outletid");
		}
		if($stocktype=='o2w')
		{
			$this->db->join("tbl_warehouse WI", "WI.fld_id=TI.fld_warehouseid");
		}
		elseif($stocktype=='o2c')
		{
			$this->db->join("tbl_customers CI", "CI.fld_id=TI.fld_customerid");
		}
		$this->db->where('TI.fld_type',$stocktype);
		if($customerid>0)
		{
			$this->db->where('TI.fld_customerid',$customerid);
		}

		if($outletid>0)
		{
			$this->db->where('TI.fld_outletid',$outletid);
		}
		$this->db->where('TI.fld_fromoutletid',$this->outletid);
		$this->db->where("TI.fld_clientid", $this->clientid);

		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_amount", $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 "<br><br>" . $this->db->last_query();
		//exit;
		if($listtype == "count")
		{
			return $results->row()->totalrecs;
		}
		else
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	}
	
	function liststockreceiveddata($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$vendorid = $this->input->get("vendorid");
		$warehouseid = $this->input->get("warehouseid");
		$outletid = $this->input->get("outletid");
		$fromdt = ($this->input->get("fromdate"));
		$todt = ($this->input->get("todate"));
		if($fromdt!="")
		{
			 $fromdate = convertdateFormat($fromdt,'Y-m-d');
		}
		if($todt!="")
		{
			$todate = convertdateFormat($todt,'Y-m-d');
		}

		if($fromdt=="" && $todt=="")
		{
			$fromdate = date('Y-m-d');
			$todate = date('Y-m-d');
		}
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("TI.*,WI.fld_name as warehousename,OI.fld_name as fromouletname");
			
		}


		$this->db->from("tbl_transaction TI");
		
		$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id",'left');
		$this->db->join("tbl_outlets OI", "OI.fld_id=TI.fld_fromoutletid",'left');
			

		if($warehouseid>0)
		{
			$this->db->where('TI.fld_warehouseid',$warehouseid);
		}
		elseif($outletid>0)
		{
			$this->db->where('TI.fld_fromoutletid',$outletid);
		}
		$this->db->where('TI.fld_outletid',$this->outletid);
		$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."'");
		}
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("TI.fld_refno", $keyword);
			$this->db->or_like("TI.fld_refdate", $keyword);
			//$this->db->or_like("TI.fld_amount", $keyword);
			$this->db->group_end();
		}

		if($listtype != "count")
		{
			$this->db->order_by('TI.fld_id', '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 liststocktransferdata($listtype, $params,$stocktype)
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");

		$outletid = $this->input->get("outletid");
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("TI.*,OI.fld_name as ouletname");			
		}


		$this->db->from("tbl_transaction TI");		
		$this->db->join("tbl_outlets OI", "OI.fld_id=TI.fld_outletid",'left');
		$this->db->where('TI.fld_type',$stocktype);

		if($outletid>0)
		{
			$this->db->where('TI.fld_outletid',$outletid);
		}
		$this->db->where('TI.fld_fromoutletid',$this->outletid);
		$this->db->where('TI.fld_clientid',$this->clientid);
		if($keyword!="")
		{
			$this->db->group_start();
			$this->db->like("TI.fld_refno", $keyword);
			$this->db->or_like("TI.fld_refdate", $keyword);
			$this->db->or_like("TI.fld_amount", $keyword);
			$this->db->group_end();
		}

		if($listtype != "count")
		{
			$this->db->order_by('TI.fld_id', '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 liststockstatusdata($listtype, $params,$stocktype)
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$vendorid = $this->input->get("vendorid");
		$warehouseid = $this->input->get("warehouseid");
		$outletid = $this->input->get("outletid");
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("P.fld_name,TI.fld_productid,SUM( Case When (T.fld_type= 'stockout' or  T.fld_type= 'o2o') and T.fld_outletid='".$this->outletid."'  then TI.fld_qty end) as incoming,
				SUM( Case When T.fld_type= 'o2o' and T.fld_fromoutletid='".$this->outletid."' then TI.fld_qty end) as outgoing");
		}
		
		$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_outletid',$this->outletid);
		$this->db->where('T.fld_clientid',$this->clientid);
		$this->db->where("T.fld_del","0");
		$this->db->group_by('TI.fld_productid,P.fld_name');
		if($listtype != "count")
		{
			$this->db->order_by('P.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
		{
			if($results->num_rows()>0)
			{
				return $results->result();
			}
		}
	} 
	function managestock($posteddata,$stocktype)
	{
		$returnvalue = 0;
		
		$rsrcid = $posteddata["rsrcid"];
		$outletid = decodeValue($posteddata["outletid"]);
		$warehouseid = decodeValue($posteddata["warehouseid"]);
		if($stocktype=='o2o')
		{
			$warehouseid =0;
		}
		elseif($stocktype=='o2w')
		{
			$outletid =0;
		}
		$refno = $posteddata["refno"];
		$refdate = $posteddata["refdate"];
		
		$transactioninfoids = $posteddata["transactioninfoids"];
		
		$productid = $posteddata["productid"];
		$qty_case = $this->input->post("qty_case");
		$qty_bottle = $this->input->post("qty_bottle");
		$price_case = $this->input->post("price_case");
		$price_bottle = $this->input->post("price_bottle");
		$subtotal = $this->input->post("subtotal");
		$productttotalamount = $posteddata["productttotalamount"];
		$producttotalrows = $posteddata["producttotalrows"];
		//predata($_REQUEST); exit;
		$data = array(
			"fld_clientid" => $this->clientid,
			"fld_type" => $stocktype,
			"fld_vendorid" => 0,
			"fld_warehouseid" => $warehouseid,
			"fld_outletid" => $outletid,
			"fld_userid" => $this->userid,
			"fld_fromoutletid" => $this->outletid,
			"fld_refno" => $refno,
			"fld_refdate" => $refdate,			
			"fld_amount" => 0,	
			"fld_vatamount" => 0,	
			"fld_tscamount" => 0,	
			"fld_discount" => 0,	
			"fld_adjustment" => 0,
			"fld_updatedby" => $this->lguserinfo["sessID"],
			"fld_updatedon" => date("Y-m-d H:i:s"),			
			"fld_status" => 'delivered'
		);
		//predata($data);
		// cehck already exits chalaln no. 
		$this->db->select("fld_id");
		$this->db->where("fld_refno", $data["fld_refno"]);
		$this->db->where("fld_clientid", $data["fld_clientid"]);
		$this->db->where("fld_fromoutletid", $data["fld_fromoutletid"]);
		$this->db->where("fld_type", $data["fld_type"]);		
		$this->db->where("fld_del", 0);		
		if($rsrcid>0) // check
		{
			$this->db->where("fld_id!=$rsrcid");
		}
		$results = $this->db->get('tbl_transaction');
		//echo $this->db->last_query();
		if($results->num_rows()>0) // already exists
		{
			$returnvalue = -1;
		}
		else
		{

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

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

	function managedemind($posteddata,$stocktype)
	{
		$returnvalue = 0;		
		$rsrcid = $posteddata["rsrcid"];
		$outletid = $this->outletid;
		$warehouseid = $posteddata["warehouseid"];
		$refno = $posteddata["refno"];
		$refdate = $posteddata["refdate"];		
		$transactioninfoids = $posteddata["transactioninfoids"];		
		$productid = $posteddata["productid"];
		$qty_case = $this->input->post("qty_case");
		$qty_bottle = $this->input->post("qty_bottle");
		$remarks = $this->input->post("remarks");
		$productttotalamount = $posteddata["productttotalamount"];
		$producttotalrows = $posteddata["producttotalrows"];
		if($rsrcid=="" || $rsrcid==0) // for new
		{
			$refno = createDemandRefno('O'.$outletid."-");			  
		}
		else
		{
			$this->db->select("fld_refno");
			$this->db->where("fld_id", $rsrcid);
			$results = $this->db->get("tbl_demand");
			if($results->num_rows()>0) // update if found
			{
				$row = $results->row();
				$refno =$row->fld_refno;
			}			
		}
		
		$data = array(		
			"fld_warehouseid" => $warehouseid,
			"fld_refdate" => $refdate,
			"fld_remarks" => $remarks,	
			"fld_updatedby" => $this->lguserinfo["sessID"],
			"fld_updatedon" => date("Y-m-d H:i:s")		
			
		);
		//predata($data); exit;

		// cehck already exits ref no. 
		$this->db->select("fld_id");
		$this->db->where("fld_refno", $refno);
		$this->db->where("fld_clientid", $this->clientid);
		$this->db->where("fld_outletid", $outletid);
		$this->db->where("fld_type", $stocktype);	
		$this->db->where("fld_del", 0);		
		if($rsrcid>0) // check
		{
			$this->db->where("fld_id!=$rsrcid");
		}
		$results = $this->db->get('tbl_demand');
		if($results->num_rows()>0) // already exists
		{
			$returnvalue = -1;
		}
		else
		{
				if($rsrcid=="" || $rsrcid==0) // for new
				{
					$data["fld_refno"] = $refno;
					$data["fld_type"] = $stocktype;
					$data["fld_clientid"] = $this->clientid;
					
					$data["fld_outletid"] = $outletid;
					$data["fld_userid"] = $this->userid;
					$data["fld_status"] = 'open';
					$data["fld_datetime"] = date("Y-m-d H:i:s");
					$data["fld_updatedby"]= $this->lguserinfo["sessID"];
					$data["fld_updatedon"] = date("Y-m-d H:i:s");
					$this->db->insert("tbl_demand", $data);
					$returnvalue = $this->db->insert_id();
				}
				else // for existings
				{
					$data["fld_updatedby"]= $this->lguserinfo["sessID"];
					$data["fld_updatedon"] = date("Y-m-d H:i:s");
					$this->db->where("fld_id",$rsrcid);
					$this->db->update("tbl_demand", $data);
					$returnvalue = $rsrcid;

				}
				if($returnvalue>0) // add/update info. table
				{	
					
					if($producttotalrows>0)
					{
						for($i=0;$i<$producttotalrows;$i++)
						{
							$transactioninfoid = $transactioninfoids[$i];
							$prodid = $productid[$i];
							$noofbottlesincase = 0;
							$this->db->select("fld_noofbottlesincase");
							$this->db->where("fld_id", $prodid);
							$results = $this->db->get("tbl_products");
							if($results->num_rows()>0) // update if found
							{
								$row = $results->row();
								$noofbottlesincase =$row->fld_noofbottlesincase;
							}
							$qty = $qty_bottle[$i]+($qty_case[$i]*$noofbottlesincase); 
							
							## store stock data
							if($prodid>0 && $qty>0)
							{
								$databkinfo = array(
									"fld_demandid" => $returnvalue,
									"fld_productid" => $prodid,
									"fld_qty_case" => $qty_case[$i],
									"fld_qty_bottle" => $qty_bottle[$i],
									"fld_qty" => $qty,
									
								);
								if($transactioninfoid=="" || $transactioninfoid==0)
								{
									$this->db->insert("tbl_demandinfo", $databkinfo);
								}
								else
								{
									$this->db->where("fld_id", $transactioninfoid);
									$this->db->update("tbl_demandinfo", $databkinfo);
								}
							}
							
			
						}
						
					}						
				}
			
		}
			
		return $returnvalue;
	}
		
	function getstockinfo($transactionid, $stocktype)
	{
		$stockinfo = array();
		
		if($stocktype=='o2w')
		{
			$this->db->select("TI.*,WI.fld_name as warehousename");
		}
		else
		{
			$this->db->select("TI.*,OI.fld_name as ouletname");
		}

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

		$this->db->where("TI.fld_id", $transactionid);
		$result = $this->db->get();
		if($result->num_rows()>0)
		{
			$row = $result->row();
			$stockinfo["main"] = $row;
			
			$this->db->select("TII.*");
			$this->db->from("tbl_transactioninfo TII");
			$this->db->where("TII.fld_transactionid", $transactionid);
			$result2 = $this->db->get();
			if($result2->num_rows()>0)
			{
				foreach($result2->result() as $row2)
				{
					$stockinfo["info"][] = $row2;					
				}
			}
		}
		return $stockinfo;
	}
	function getstockinfo_old($transactionid, $stocktype)
	{
		$stockinfo = array();
		
		if($stocktype=='stockout')
		{
			$this->db->select("TI.*,WI.fld_name as warehousename,OI.fld_name as ouletname");
		}
		else
		{
			$this->db->select("TI.*,VI.fld_name as vendorname,WI.fld_name as warehousename");
		}

		$this->db->from("tbl_transaction TI");
		
		$this->db->join("tbl_warehouse WI", "TI.fld_warehouseid=WI.fld_id", "left");
		if($stocktype=='stockout')
		{
			$this->db->join("tbl_outlets OI", "OI.fld_id=TI.fld_outletid",'left');
		}
		else
		{
			$this->db->join("tbl_vendors VI", "TI.fld_vendorid=VI.fld_id");
		}
		$this->db->where("TI.fld_id", $transactionid);
		$result = $this->db->get();
		if($result->num_rows()>0)
		{
			$row = $result->row();
			$stockinfo["main"] = $row;
			
			$this->db->select("TII.*");
			$this->db->from("tbl_transactioninfo TII");
			$this->db->where("TII.fld_transactionid", $transactionid);
			$result2 = $this->db->get();
			if($result2->num_rows()>0)
			{
				foreach($result2->result() as $row2)
				{
					$stockinfo["info"][] = $row2;					
				}
			}
		}
		return $stockinfo;
	}
	function getbillinginfo($transactionid)
	{
		$stockinfo = array();
		
		$this->db->select("TI.*");

		$this->db->from("tbl_billing TI");		
		$this->db->where("TI.fld_id", $transactionid);
		$result = $this->db->get();
		if($result->num_rows()>0)
		{
			$row = $result->row();
			$stockinfo["main"] = $row;
			
			$this->db->select("TII.*");
			$this->db->from("tbl_billinginfo TII");
			$this->db->where("TII.fld_billingid", $transactionid);
			$result2 = $this->db->get();
			if($result2->num_rows()>0)
			{
				foreach($result2->result() as $row2)
				{
					$stockinfo["info"][] = $row2;					
				}
			}
		}
		return $stockinfo;
	}	
	function getoutletstockinfo($outletid)
	{
		$this->db->select("P.fld_name,TI.fld_productid,SUM( Case When (T.fld_type= 'stockout' or T.fld_type= 'o2o') and fld_outletid='".$outletid."' then TI.fld_qty end) as incoming,SUM( Case When T.fld_type= 'o2o' and fld_fromoutletid='".$outletid."' then TI.fld_qty end) as outgoing");
		$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_del", "0");
		$this->db->group_by('TI.fld_productid,P.fld_name');
		$results = $this->db->get();
		//echo $this->db->last_query();//exit;
		return $results->result();
	}

	function oldgetoutletstockinfo2($outletid)
	{
		//,PP.fld_price,PP.fld_price3bottles,PP.fld_price6bottles,PP.fld_price9bottles,PP.fld_price12bottles
		$this->db->select("P.fld_name,TI.fld_productid,PP.fld_price,PP.fld_price3bottles,PP.fld_price6bottles,PP.fld_price9bottles,PP.fld_price12bottles,SUM( Case When (T.fld_type= 'stockout' or T.fld_type= 'o2o') and T.fld_outletid='".$outletid."' and T.fld_clientid='".$this->clientid."' then TI.fld_qty end) as incoming,SUM( Case When T.fld_type= 'o2o' and T.fld_fromoutletid='".$outletid."' and T.fld_clientid='".$this->clientid."' then TI.fld_qty end) as outgoing ,SUM( Case When B.fld_outletid='".$outletid."' and T.fld_clientid='".$this->clientid."' then BI.fld_qty end) as outgoing2");
		$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->join("tbl_outlets ZI", "ZI.fld_id=T.fld_outletid");
		$this->db->join("tbl_price PP", "PP.fld_productid=P.fld_id and PP.fld_zoneid=ZI.fld_zoneid");
		$this->db->join("tbl_billinginfo BI", "BI.fld_productid=P.fld_id",'left');		
		$this->db->join("tbl_billing B", "BI.fld_billingid=B.fld_id",'left');
		$this->db->where('PP.fld_price>0');
		$this->db->where('PP.fld_price3bottles>0');
		$this->db->where('PP.fld_price6bottles>0');
		$this->db->where('PP.fld_price9bottles>0');
		$this->db->where('PP.fld_price12bottles>0');
		$this->db->where("T.fld_del", "0");
		//$this->db->where('T.fld_outletid',$outletid);
		//$this->db->where('T.fld_clientid',$this->clientid);
		$this->db->group_by('P.fld_name,TI.fld_productid,PP.fld_price,PP.fld_price3bottles,PP.fld_price6bottles,PP.fld_price9bottles,PP.fld_price12bottles');
		$results = $this->db->get();
		echo $this->db->last_query();exit;
		return $results->result(); 
	}


	function getoutletstockinfo2($outletid)
	{
		//,PP.fld_price,PP.fld_price3bottles,PP.fld_price6bottles,PP.fld_price9bottles,PP.fld_price12bottles
		$this->db->select("P.fld_name,TI.fld_productid,PP.fld_price,PP.fld_price3bottles,PP.fld_price6bottles,PP.fld_price9bottles,PP.fld_price12bottles,SUM( Case When (T.fld_type= 'stockout' or T.fld_type= 'o2o') and T.fld_outletid='".$outletid."' and T.fld_clientid='".$this->clientid."' then TI.fld_qty end) as incoming,SUM( Case When T.fld_type= 'o2o' and T.fld_fromoutletid='".$outletid."' and T.fld_clientid='".$this->clientid."' then TI.fld_qty end) as outgoing ,SUM( Case When B.fld_outletid='".$outletid."' and T.fld_clientid='".$this->clientid."' then BI.fld_qty end) as outgoing2");
		$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->join("tbl_outlets ZI", "ZI.fld_id=T.fld_outletid");
		$this->db->join("tbl_price PP", "PP.fld_productid=P.fld_id and PP.fld_zoneid=ZI.fld_zoneid");
		$this->db->join("tbl_billinginfo BI", "BI.fld_productid=P.fld_id",'left');		
		$this->db->join("tbl_billing B", "BI.fld_billingid=B.fld_id",'left');
		$this->db->where('PP.fld_price>0');
		$this->db->where('PP.fld_price3bottles>0');
		$this->db->where('PP.fld_price6bottles>0');
		$this->db->where('PP.fld_price9bottles>0');
		$this->db->where('PP.fld_price12bottles>0');
		$this->db->where("T.fld_del", "0");
		//$this->db->where('T.fld_outletid',$outletid);
		//$this->db->where('T.fld_clientid',$this->clientid);
		$this->db->group_by('P.fld_name,TI.fld_productid,PP.fld_price,PP.fld_price3bottles,PP.fld_price6bottles,PP.fld_price9bottles,PP.fld_price12bottles');
		$results = $this->db->get();
		echo $this->db->last_query();exit;
		return $results->result(); 
	}
	function getotheroutletlist()
	{
		$this->db->select("fld_name, fld_id");
		$this->db->where('fld_status', 'active');
		$this->db->where('fld_show', 'yes');
		$this->db->where('fld_clientid',$this->clientid);
		$this->db->where('fld_id!=', $this->outletid);
		$this->db->order_by('fld_name', 'asc');
		$results = $this->db->get('tbl_outlets');
		if($results->num_rows()>0)
		{			
			return $results->result();
		}
		else
		{
			return false;
		}
	}



	function getproductlist()
	{		
			$this->db->select("P.fld_name,TI.fld_productid,T.fld_id as transactionid,PP.*");
			$this->db->from("tbl_products P");			
			$this->db->join("tbl_transactioninfo TI", "TI.fld_productid=P.fld_id");
			$this->db->join("tbl_transaction T", "T.fld_id=TI.fld_transactionid");
			$this->db->join("tbl_price PP", "PP.fld_productid=P.fld_id");
			$this->db->where("T.fld_clientid", $this->clientid);
			$this->db->where("T.fld_outletid", $this->outletid);
			$this->db->where("(T.fld_type='stockout' or T.fld_type='o2o')");
			//$this->db->or_where("T.fld_type", 'o2o');
			$this->db->group_by('P.fld_name,TI.fld_productid');
			$results = $this->db->get(); 
			//echo $this->db->last_query(); exit;
			if($results->num_rows()>0)
			{
				$row = $results->result();
				return $row;
			}
			else
			{
				return false;
			}
	}

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

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

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

							### stock from outlet 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;
							$outgoing += $ooutgoingqty;

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

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

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


	function getlastsynceddata($outletid, $synctype, $diffinseconds)
	{
		$response = "failed";
		$todate = date("Y-m-d H:i:s");
		
		$this->db->select("fld_updatedon");
		$this->db->where("fld_type", $synctype);
		$this->db->where("fld_outletid", $outletid);
		$this->db->order_by("fld_updatedon", "desc");
		$this->db->limit(1);
		$result = $this->db->get("tbl_updatehistory");
		//echo $this->db->last_query() . " => " . $result->num_rows();
		if($result->num_rows()>0)
		{
			$row = $result->row();
			$lastsynceddatetime = $row->fld_updatedon;
			$diff = strtotime($todate) - strtotime($lastsynceddatetime);
			//echo "<br>" . $todate . " => " . $lastsynceddatetime . " = " . $diff;
			if($diff <= $diffinseconds && $diff>=0) $response = "success";
		}
		return $response;
	}
}
?>
