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

class Warehouse_model extends CI_Model{
        
    public function __construct()
    {
        parent::__construct();
    }
    
    function checklogin()
    {
		$username = $this->input->post("username");
		$userpass = $this->input->post("userpass");
		if($username!="" && $userpass!="")
		{
			$this->db->select("*");
			$this->db->where("fld_username", $username);
			$this->db->where("fld_userpass", md5($userpass));
			$this->db->where("fld_status", "active");
			$this->db->where("fld_type", "managerwh");
			$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_warehouseid>0)
					{
						$this->db->select("fld_name");
						$this->db->where("fld_id", $row->fld_warehouseid);
						$result3 = $this->db->get("tbl_warehouse");				
						//echo $this->db->last_query();
						if($result3->num_rows()>0)
						{
							$row3 = $result3->row();
							$warehousename = $row3->fld_name;

							$data = array(
								'sessCompanyname' => $company,
								'sessCLIENTID' => $row->fld_clientid,
								'sessWHID' => $row->fld_warehouseid,
								'sessID' => $row->fld_id,
								'sessCompanyname' => $company,
								'sessWHName' => $warehousename,
								'sessName' => $row->fld_name,
								'sessUserName' => $row->fld_username,
								'sessType' => 'warehouse',
								'sessEmail' => $row->fld_email,
								'sessLoggedInStatus' => true
								);
							//predata($data); exit;
							$this->session->set_userdata('whLoggedIn', $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 listoutletsdata($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		$zoneid = decodeValue($this->input->get("zoneid"));
		if($listtype == "count")
		{
			$this->db->select("count(*) as totalrecs");
		}
		else
		{
			$this->db->select("OI.*,ZI.fld_name as zonename");
		}
		$this->db->from("tbl_outlets OI");
		$this->db->join("tbl_zone ZI", "OI.fld_zoneid=ZI.fld_id",'left');

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

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

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

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

		if($vendorid>0)
		{
			$this->db->where('TI.fld_vendorid',$vendorid);
		}
		if($clientid>0)
		{
			$this->db->where('TI.fld_vendorid',$clientid);
		}
		$this->db->group_start();
		$this->db->where('TI.fld_warehouseid',$this->warehouseid);
		$this->db->or_where('TI.fld_towarehouseid',$this->warehouseid);
		$this->db->group_end();
		if($outletid>0)
		{
			if($stocktype=='o2w')
			{
				$this->db->where('TI.fld_fromoutletid',$outletid);
			}
			else
			{
				$this->db->where('TI.fld_outletid',$outletid);
			}
		}
		$this->db->where("TI.fld_clientid", $this->clientid);
		$this->db->where("TI.fld_del", 0);

		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', '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 liststockstatusdata($listtype, $params = array())
	{
		if($listtype=="") $listtype = "count";
		
		$keyword = $this->input->get("search");
		//$warehouseid = decodeValue($this->input->get("warehouseid"));
		$warehouseid = $this->warehouseid;

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

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

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

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

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

							### stock from warehouse outgoing
							$boutgoing = 0;
							if($arrcurroutgoing[$prodid]) $boutgoing = $arrcurroutgoing[$prodid];
							$arr[$prodid]["outgoing"] += $boutgoing;
							
							$instockbottles = $previousstock + ($arr[$prodid]["incoming"]-$arr[$prodid]["outgoing"]);
							if($arr[$prodid]["bottlesincase"]>0)
							{
								$instockcases = $instockbottles / $arr[$prodid]["bottlesincase"];
							}
							else
							{
								$instockcases = $instockbottles;
							}
							
							$arr[$prodid]["instock"] = $instockbottles;
							$arr[$prodid]["instockcases"] = round($instockcases, 2);
						}
					}
					//predata($arr); exit;
				}
			}
			return $arr;
		}
	} 
	 
	function managestock($posteddata,$stocktype)
	{
		$returnvalue = 0;		
		$rsrcid = $posteddata["rsrcid"];
		$vendorid = $posteddata["vendorid"];
		$outletid = $posteddata["outletid"];
		if($outletid=="") $outletid = 0;
		$towarehouseid = $posteddata["towarehouseid"];
		if($towarehouseid=="") $towarehouseid = 0;
		$warehouseid = $this->warehouseid;
		$refno = $posteddata["refno"];
		$refdate = $posteddata["refdate"];		
		$transactioninfoids = $posteddata["transactioninfoids"];		
		$productid = $posteddata["productid"];
		$qty_case = $this->input->post("qty_case");
		$qty_bottle = $this->input->post("qty_bottle");
		$price_case = $this->input->post("price_case");
		$price_bottle = $this->input->post("price_bottle");
		$subtotal = $this->input->post("subtotal");
		$vatamount = $this->input->post("vatamount");
		$tscamount = $this->input->post("tscamount");
		$discount = $this->input->post("discount");
		$adjustment = $this->input->post("adjustment");
		$remarks = $this->input->post("remarks");
		$productttotalamount = $posteddata["productttotalamount"];
		$producttotalrows = $posteddata["producttotalrows"];
		
		if($stocktype=='stockin')
		{
			$outletid =0;
			$status ='completed';
		}
		else
		{
			$productttotalamount=0;
			$vendorid = 0;
			$status ='delivered';
			$vatamount = 0;
			$tscamount = 0;
			$discount = 0;
			$adjustment = 0;
			
		}
		$data = array(
			"fld_clientid" => $this->clientid,
			"fld_type" => $stocktype,
			"fld_vendorid" => $vendorid,
			"fld_warehouseid" => $warehouseid,
			"fld_towarehouseid" => $towarehouseid,
			"fld_outletid" => $outletid,
			"fld_userid" => $this->userid,
			"fld_refno" => $refno,
			"fld_refdate" => $refdate,
			"fld_remarks" => $remarks,	
			"fld_amount" => $productttotalamount,	
			"fld_vatamount" => $vatamount,	
			"fld_tscamount" => $tscamount,	
			"fld_discount" => $discount,	
			"fld_adjustment" => $adjustment,	
			"fld_updatedby" => $this->lguserinfo["sessID"],
			"fld_updatedon" => date("Y-m-d H:i:s"),			
			"fld_status" => $status
		);
		//predata($data); exit;

		$executefurther = 1;
		// check already exists invoice no. for purchase
		if($stocktype=='stockin')
		{ 
			$this->db->select("fld_id");
			$this->db->where("fld_refno", $data["fld_refno"]);
			$this->db->where("fld_clientid", $data["fld_clientid"]);
			$this->db->where("fld_vendorid", $data["fld_vendorid"]);	
			$this->db->where("fld_del", 0);		
			if($rsrcid>0) // check
			{
				$this->db->where("fld_id!=$rsrcid");
			}
			$results = $this->db->get('tbl_transaction');
			if($results->num_rows()>0) // already exists
			{
				$returnvalue = -1;
				$executefurther = 0;
			}
		}
		if($executefurther>0)
		{
			if($rsrcid=="" || $rsrcid==0) // for new
			{
				$data["fld_datetime"] = date("Y-m-d H:i:s");
				$this->db->insert("tbl_transaction", $data);
				$returnvalue = $this->db->insert_id();
				if($stocktype!='stockin')
				{
					if($refno=="" && $refdate=="")
					{
						$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];
						$nprice_case = $price_case[$i];
						$nprice_bottle = $price_bottle[$i];
						$nsubtotal= $subtotal[$i];
						
						if($stocktype=='stockout' || $stocktype=='w2w')
						{
							$prodids = explode("_",$productid[$i]);
							$prodid = $prodids[0];
							$nprice_case = 0;
							$nprice_bottle = 0;
							$nsubtotal= 0;
						}
						$noofbottlesincase = 0;
						$this->db->select("fld_noofbottlesincase");
						$this->db->where("fld_id", $prodid);
						$results = $this->db->get("tbl_products");
						if($results->num_rows()>0) // update if found
						{
							$row = $results->row();
							$noofbottlesincase =$row->fld_noofbottlesincase;
						}
						$qty = $qty_bottle[$i]+($qty_case[$i]*$noofbottlesincase); 
						
						## store stock data
						if($prodid>0 && $qty>=0)
						{
							$databkinfo = array(
								"fld_transactionid" => $returnvalue,
								"fld_productid" => $prodid,
								"fld_qty_case" => $qty_case[$i],
								"fld_qty_bottle" => $qty_bottle[$i],
								"fld_qty" => $qty,
								"fld_price_case" => $nprice_case,
								"fld_price_bottle" => $nprice_bottle,
								"fld_subtotal" => $nsubtotal
								
							);
							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 managebilling($posteddata,$stocktype)
	{
		$returnvalue = 0;		
		$rsrcid = $posteddata["rsrcid"];
		$vendorid = $posteddata["vendorid"];
		$clientid = $posteddata["clientid"];
		$warehouseid = $this->warehouseid;
		$refno = $posteddata["refno"];
		$refdate = $posteddata["refdate"];		
		$transactioninfoids = $posteddata["transactioninfoids"];		
		$productid = $posteddata["productid"];
		$qty_case = $this->input->post("qty_case");
		$qty_bottle = $this->input->post("qty_bottle");
		$price_case = $this->input->post("price_case");
		$price_bottle = $this->input->post("price_bottle");
		$subtotal = $this->input->post("subtotal");
		$producttsubtotalamount = $this->input->post("producttsubtotalamount");
		$vatamount = $this->input->post("vatamount");
		$tscamount = $this->input->post("tscamount");
		$discount = $this->input->post("discount");
		$adjustment = $this->input->post("adjustment");
		$remarks = $this->input->post("remarks");
		$productttotalamount = $posteddata["productttotalamount"];
		$producttotalrows = $posteddata["producttotalrows"];
		
		$status ='completed';

		$data = array(
			"fld_clientid" => $this->clientid,
			"fld_type" => $stocktype,
			"fld_vendorid" => $vendorid,
			"fld_warehouseid" => $warehouseid,
			"fld_vendorid" => $clientid,
			"fld_userid" => $this->userid,
			"fld_refno" => $refno,
			"fld_refdate" => $refdate,
			"fld_remarks" => $remarks,	
			"fld_subtotal" => $producttsubtotalamount,	
			"fld_amount" => $productttotalamount,	
			"fld_vatamount" => $vatamount,	
			"fld_tscamount" => $tscamount,	
			"fld_discount" => $discount,	
			"fld_adjustment" => $adjustment,	
			"fld_updatedby" => $this->lguserinfo["sessID"],
			"fld_updatedon" => date("Y-m-d H:i:s"),			
			"fld_status" => $status
		);
		//predata($data); exit;

		$executefurther = 1;
		// check already exists invoice no. for purchase
		$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_type", $data["fld_type"]);
		$this->db->where("fld_warehouseid", $data["fld_warehouseid"]);
		$this->db->where("fld_vendorid", $data["fld_vendorid"]); // here it is for clientid	
		$this->db->where("fld_del", 0);		
		if($rsrcid>0) // check
		{
			$this->db->where("fld_id!=$rsrcid");
		}
		$results = $this->db->get('tbl_transaction');
		if($results->num_rows()>0) // already exists
		{
			$returnvalue = -1;
			$executefurther = 0;
		}
		if($executefurther>0)
		{
			if($rsrcid=="" || $rsrcid==0) // for new
			{
				$data["fld_datetime"] = date("Y-m-d H:i:s");
				$this->db->insert("tbl_transaction", $data);
				$returnvalue = $this->db->insert_id();
			}
			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];
						$nprice_case = $price_case[$i];
						$nprice_bottle = $price_bottle[$i];
						$nsubtotal= $subtotal[$i];
						
						$nprice_case = 0;
						$noofbottlesincase = 0;
						$this->db->select("fld_noofbottlesincase");
						$this->db->where("fld_id", $prodid);
						$results = $this->db->get("tbl_products");
						if($results->num_rows()>0) // update if found
						{
							$row = $results->row();
							$noofbottlesincase =$row->fld_noofbottlesincase;
						}
						$qty = $qty_bottle[$i]+($qty_case[$i]*$noofbottlesincase); 
						
						## store stock data
						if($prodid>0 && $qty>=0)
						{
							$databkinfo = array(
								"fld_transactionid" => $returnvalue,
								"fld_productid" => $prodid,
								"fld_qty_case" => $qty_case[$i],
								"fld_qty_bottle" => $qty_bottle[$i],
								"fld_qty" => $qty,
								"fld_price_case" => $nprice_case,
								"fld_price_bottle" => $nprice_bottle,
								"fld_subtotal" => $nsubtotal
								
							);
							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"];
		$warehouseid = $this->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('W'.$warehouseid."-");			  
		}
		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_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_warehouseid", $warehouseid);
		$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_warehouseid"] = $warehouseid;
					$data["fld_outletid"] = 0;
					$data["fld_userid"] = $this->userid;
					$data["fld_status"] = 'open';
					$data["fld_datetime"] = date("Y-m-d H:i:s");

					$this->db->insert("tbl_demand", $data);
					$returnvalue = $this->db->insert_id();
				}
				else // for existings
				{

					$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=='stockout')
		{
			$this->db->select("TI.*,WI.fld_name as warehousename,OI.fld_name as ouletname");
		}
		elseif($stocktype=='w2w')
		{
			$this->db->select("TI.*,WI02.fld_name as towarehousename,WI.fld_name as warehousename");
		}
		
		elseif($stocktype=='wsale')
		{
			$this->db->select("TI.*,CI.fld_name as clientname,WI.fld_name as warehousename");
		}
		else
		{
			$this->db->select("TI.*,VI.fld_name as vendorname,WI.fld_name as warehousename");
		}

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

		$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 getwarehousestockinfo($warehouseid)
	{
		$this->db->select("P.fld_name,P.fld_name,TI.fld_productid,SUM( Case When T.fld_type= 'stockout' and fld_warehouseid='".$warehouseid."' then TI.fld_qty end) as outgoing,
				SUM( Case When T.fld_type= 'stockin' and fld_warehouseid='".$warehouseid."' then TI.fld_qty end) as incoming");		
		
		$this->db->from("tbl_products P");
		$this->db->join("tbl_transactioninfo TI", "TI.fld_productid=P.fld_id");		
		$this->db->join("tbl_transaction T", "TI.fld_transactionid=T.fld_id");
		$this->db->where("T.fld_warehouseid", $warehouseid);
		$this->db->where("T.fld_clientid", $this->clientid);
		$this->db->group_by('TI.fld_productid,P.fld_name');
		$results = $this->db->get();		
		return $results->result();

		
	}

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

							### stock received from other warehouse
							$this->db->select("SUM(TII2.fld_qty) as wincomingqty02");
							$this->db->from("tbl_transactioninfo TII2");
							$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
							$this->db->where("TII2.fld_productid=",$prodid);						
							$this->db->where("(TI2.fld_status='completed')");
							$this->db->where("(TI2.fld_type='w2w')");
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_towarehouseid',$warehouseid);
							$this->db->where("TI2.fld_del", "0");
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$wincomingqty02 = $row2->wincomingqty02;
							$incoming += $wincomingqty02;

							### stock from warehouse outgoing
							$this->db->select("SUM(TII2.fld_qty) as ooutgoingqty");
							$this->db->from("tbl_transactioninfo TII2");
							$this->db->join("tbl_transaction TI2", "TII2.fld_transactionid=TI2.fld_id");
							$this->db->where("TII2.fld_productid=",$prodid);						
							$this->db->where("(TI2.fld_status='completed' or TI2.fld_status='delivered')");
							$this->db->group_start();
							$this->db->where("TI2.fld_type", "stockout");
							$this->db->or_where("TI2.fld_type", "w2w");
							$this->db->group_end();
							$this->db->where('TI2.fld_clientid',$this->clientid);
							$this->db->where('TI2.fld_warehouseid',$warehouseid);
							$this->db->where('TI2.fld_id!='.$rsrcid.'');
							$this->db->where("TI2.fld_del", "0");
							$this->db->group_by('TII2.fld_productid');
							$query2 = $this->db->get();
							$row2 = $query2->row();
							$ooutgoingqty = $row2->ooutgoingqty;
							$outgoing += $ooutgoingqty;							
							$instock = ($incoming-$outgoing);							
							$stockval = ($instock-$qty);
							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 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 getwarehouselist($warehouseid)
	{
		$this->db->select("fld_id, fld_name");
		$this->db->where("fld_clientid", $this->clientid);
		$this->db->where("fld_id!=", $warehouseid);
		$this->db->where("fld_del", "0");
		$this->db->order_by("fld_name", "asc");
		$results = $this->db->get("tbl_warehouse");
		if($results->num_rows()>0)
		{			
			return $results->result();
		}
		else
		{
			return false;
		}
	}	

}
?>
