<?php
session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
include("config.inc.php");
include("Functions_Sales.php");
//include("Functions_UserAccess.php");

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");


if(	$_SESSION['encodingmode'] == 'utf8' || $_SESSION['encodingmode'] == 'utf-8'){
	mysql_query("SET NAMES 'utf8'"); 
	mysql_query('SET CHARACTER SET utf8'); 
	mysql_set_charset('utf8', $connection);
}
	
$vTableName      = $_GET["tablename"];
$vKeyword        = $_GET["term"];
$vClientCode     = $_GET["pclient"];
$vCurrencyCode   = $_GET["pcurrency"];
$vCurrencyCodeB1 = $_GET["pcurrbase1"];
$vCurrencyCodeB2 = $_GET["pcurrbase2"];
$vDate           = $_GET["pdate"];
$vItemCode       = $_GET["pitem"];
$vWarehouseCode  = $_GET["pwarehouse"];
$vProjectCode    = $_GET["pproject"];
$vCostCentCode   = $_GET["pcostcent"];

$vDbTableName    = $_GET["dbtablename"];
$vDbFieldCode    = $_GET["dbfieldcode"];
$vDbFieldName    = $_GET["dbfieldname"];
$vDbFieldCurr    = $_GET["dbfieldcurr"];
$vInputVal 		 = $_GET["inputval"];
$vLedgerNo 		 = $_GET["pledger"];
$vjourDtRate     = $_GET["jourDtRate"];

if (!function_exists('ConvertCurrency')) {
	function ConvertCurrency($FromCurr, $ToCurr, $FromDate, $Amount, $connection)
	{	
		$query = "SELECT ifNull(convertcurfunc('".$FromCurr."', '".$ToCurr."', '".$FromDate."', 0, 0, '', '', ifNull($Amount,0)),0) From Dual ";
		$result = selectData($connection, $query);
		//var_dump($query); 
		return	$result[0];

	}
}
if (!function_exists('ConvertCurrencyRate')) {
	function ConvertCurrencyRate($FromCurr, $ToCurr, $FromDate, $Amount,$rate, $connection)
	{
		$querymodule=" Select ifNull(module_journalRate,0) As module_journalRate ".	
			" From Module";
		$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
		$rowmodule=mysql_fetch_array($resultmodule);
			$module_journalRate=$rowmodule[0];

		$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, Journal_DefaultCur ".
             " from setup ";
		$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
		while($rowsetup=mysql_fetch_array($resultsetup)){
			extract($rowsetup);
		}
		
		$query_curdt = "select curdt_rate from curdt where currency_code='$base2'
						and curdt_date = (select max(curdt_date) from curdt where curdt_date <='$FromDate'
						and Currency_Code = '$base2');";
		$res_curdt = mysql_query($query_curdt,$connection);
		$rw_curdt = mysql_fetch_row($res_curdt);	
		$bRate = $rw_curdt[0];
		if($base1 == $ToCurr) {
			$rate1 = 0;
			$rate2 = 0;
		} else if($base2 == $ToCurr) {
			$rate1 = $rate;
			$rate2 = 1;
		}
		$query = "SELECT ifNull(convertcurfunc('".$FromCurr."', '".$ToCurr."', '".$FromDate."',$rate1 ,$rate2, '', '', ifNull($Amount,0)),0) From Dual ";
		$result = selectData($connection, $query);
		//return $query; 
		return	$result[0];

	}
}
if (!function_exists('ConvertCurrencyRate2')) {
	function ConvertCurrencyRate2($FromCurr, $ToCurr, $FromDate, $Amount,$rate, $connection)
	{
		$querymodule=" Select ifNull(module_journalRate,0) As module_journalRate ".	
			" From Module";
		$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
		$rowmodule=mysql_fetch_array($resultmodule);
			$module_journalRate=$rowmodule[0];

		$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, Journal_DefaultCur ".
             " from setup ";
		$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
		while($rowsetup=mysql_fetch_array($resultsetup)){
			extract($rowsetup);
		}
		
		$query_curdt = "select curdt_rate from curdt where currency_code='$base1'
						and curdt_date = (select max(curdt_date) from curdt where curdt_date <='$FromDate'
						and Currency_Code = '$base1');";
		$res_curdt = mysql_query($query_curdt,$connection);
		$rw_curdt = mysql_fetch_row($res_curdt);	
		$bRate = $rw_curdt[0];
		if($base1 == $ToCurr) {
			$rate1 = $rate;
			$rate2 = 1;
		} else if($base2 == $ToCurr) {
			$rate1 = 0;
			$rate2 = 0;
		}
		$query = "SELECT ifNull(convertcurfunc('".$FromCurr."', '".$ToCurr."', '".$FromDate."',$rate1 ,$rate2, '', '', ifNull($Amount,0)),0) From Dual ";
		$result = selectData($connection, $query);
		//return 4; 
		return	$result[0];

	}
}
if (!function_exists('ConvertCurrencyRate3')) {
	function ConvertCurrencyRate3($FromCurr, $ToCurr, $FromDate, $Amount,$rate, $connection)
	{
		
		$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, Journal_DefaultCur ".
             " from setup ";
		$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
		while($rowsetup=mysql_fetch_array($resultsetup)){
			extract($rowsetup);
		}
		
		if($base2 == $ToCurr) {
			$rate1 = 0;
			$rate2 = 0;
		}
		else {
			$rate1 = $rate;
			$rate2 = 1;
		}
		$query = "SELECT ifNull(convertcurfunc('".$FromCurr."', '".$ToCurr."', '".$FromDate."',$rate1,$rate2, '', '', ifNull($Amount,0)),0) From Dual ";
		$result = selectData($connection, $query);
		//return $query; 
		return	$result[0];

	}
}
if (!function_exists('GetSQLValueString')) {
	function GetSQLValueString($theValue, $theType) 
	{
	  $theValue = trim($theValue);	
	  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

	  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

	  switch ($theType) {
		case "text":
		  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
		  break;    
		case "long":
		case "int":
		  $theValue = ($theValue != "") ? intval($theValue) : "Null";
		  break;
		case "double":
		  $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
		  break;
		case "date":
		  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
		  break;
	  }
	  return $theValue;
	}
}

if (!function_exists('Get_CostPrice')) {
	function Get_CostPrice($PItemCode, $PItemCurr, $PCostMethod, $PDate, $PCurrCode, $PCurBase1, $PCurBase2, $connection) {

		$ItemCost = 0;
				
		if ($PCostMethod == 1) {
			$query = "SELECT ifNull(GetLastValuesFunc('".$PCurBase1."', '".$PCurBase2."', '".$PItemCode."'),0) From Dual ";
			$result = selectData($connection, $query);
			//echo $query;
			$ItemCost = $result[0];
			$ItemCost = ConvertCurrency($PItemCurr, $PCurrCode, insertMySQLDate($PDate), $ItemCost, $connection);
		}
		else if ($PCostMethod != 1) {
			$query = "SELECT ifNull(MeanCostCur('".$PItemCode."', '".insertMySQLDate($PDate)."', 1, '".$PCurBase1."', '".$PCurrCode."'),0) From Dual ";
			$result = selectData($connection, $query);
			//echo $query;
			$ItemCost = $result[0];
		}

		return	$ItemCost;
	}
}


$return_arr = array();

//Read From Module Table
$querymodule=" Select ifNull(Module_Delivery,0) As Module_Delivery,  ".
			 " ifNull(Module_Project,0) As Module_Project, ifNull(Module_Costcenter,0) As Module_CostCenter, ".
			 " ifNull(Module_ProjectDt,0) As Module_ProjectDt, ifNull(Module_CostCenterDt,0) As Module_CostCenterDt ".
			 " From Module";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

$querylog=" select ifNull(login_belongstogrp,0) As FI_BelongsToGrp ".
          " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')  ";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

//Read From Setup Table							
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2 from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}

//Read From PurchaseSetup Table
$queryPurchaseSetup="Select PurchaseTTC From PurchaseSetup";
$resultPurchaseSetup=mysql_query($queryPurchaseSetup,$connection) or die(mysql_error());
while($rowPurchaseSetup=mysql_fetch_array($resultPurchaseSetup)){
	extract($rowPurchaseSetup);
}

//Read From SalesSetup Table
$querySalesSetup="Select SalesTTC From SalesSetup";
$resultSalesSetup=mysql_query($querySalesSetup,$connection) or die(mysql_error());
while($rowSalesSetup=mysql_fetch_array($resultSalesSetup)){
	extract($rowSalesSetup);
}

$querygroup=" select distinct group_code,group_name ".
			" from grouping where group_code in (select group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."') ".
			" order by group_code";
$resultgroup = mysql_query($querygroup,$connection) or die(mysql_error());
$groupexist   = mysql_num_rows($resultgroup);

$querywarehouse=" select distinct warehouse.warehouse_code, warehouse_name ".
				" from warehouse where warehouse_code in (select warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."') ".
				" order by warehouse.warehouse_code";
$resultwh = mysql_query($querywarehouse,$connection) or die(mysql_error());
$whexist   = mysql_num_rows($resultwh);

$queryledger= " select distinct ledger_number, ledger_include ".
              " from ".$_SESSION['dbLabel'].".ledgerstk  ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " order by ledger_number";
$ledgerresult = mysql_query($queryledger,$connection) or die(mysql_error());
$ledgerexist  = mysql_num_rows($ledgerresult);	
$rows=mysql_fetch_array($ledgerresult) ;
$ledgerinclude = $rows[1];

if ($vTableName == 'client') {
	
	$query=" select ledger.Ledger_number, ledger.Ledger_name, clients.Client_PaymentTerms, clients.Client_CreditLimit, clients.Client_VAT, clients.Salesmen_Code ".
		" from clients inner join ledger on clients.ledger_number = ledger.ledger_number ".
        " where 1=1  ";
		
		/*
if($ledgerinclude== 1 && $ledgerexist > 0) {
	$query = $query." And Exists ( select 1 from ".$_SESSION['dbLabel'].".ledgerstk ".
								"              where ledgerstk.user_account='".$_SESSION['useraccount']."' ".
								"              and (ledger_number is not null and ledger_number != '') ".
								"              and CLIENTS.LEDGER_NUMBER like concat(ledgerstk.ledger_number,'%') ".
								"            ) ";
}
if($ledgerinclude== 0 && $ledgerexist > 0){
	$query = $query." And Not Exists ( select 1 from ".$_SESSION['dbLabel'].".ledgerstk ".
								"                  where ledgerstk.user_account='".$_SESSION['useraccount']."' ".
								"          	       and (ledger_number is not null and ledger_number != '') ".
								"                  and CLIENTS.LEDGER_NUMBER like concat(ledgerstk.ledger_number,'%') ".
								"                ) ";	
}*/
		$query = $query." order by ledger.Ledger_number ";	 
	//echo $query;exit;	

}
else if ($vTableName == 'items') {
	
	
	//by rod  check cost center 
 
 
$querymodule=" Select ifNull(Module_CostCenterParent,0) As Module_CostCenterParent From Module";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
	
}



$querymastercostcenter=" select distinct Mastercostcenter from ".$_SESSION['dbLabel'].".loginstk ".
					   " where loginstk.user_account='".$_SESSION['useraccount']."' ".
					   " and (Mastercostcenter is not null and Mastercostcenter != '') ";

$costcentermasterresult  = mysql_query($querymastercostcenter,$connection) or die(mysql_error());
$costcentermasterexist   = mysql_num_rows($costcentermasterresult);



$query1=" select distinct costcent.costcent_code as costcent_code, costcent_name as costcent_name ".
		" from Costcent  ";
if ($costcentermasterexist > 0) {	
							$query1= $query1." inner join ( select distinct Mastercostcenter from ".$_SESSION['dbLabel'].".loginstk ".
														 "			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
														 "             and (Mastercostcenter is not null and Mastercostcenter != '') ".
														 "			 ) CstMasterQry on ifnull(Costcent.CostCent_Code ,concat(CstMasterQry.Mastercostcenter,'%')) like concat(CstMasterQry.Mastercostcenter,'%') ";
							}	

$query1=$query1." left join ".$_SESSION['dbLabel'].".loginstk on (($Module_CostCenterParent=1 and costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%')) or ($Module_CostCenterParent=0 and costcent.costcent_code = loginstk.costcent_code )) ".
 			   " where user_account='".$_SESSION['useraccount']."' ".
			   " and (loginstk.costcent_code is not null and loginstk.costcent_code != '') ".
			   " and ifnull(costcent.costcent_deactivate,0) = 0 ".
			    " ";
			   
$query1=$query1." and (costcent.Costcent_code  like"."('%".$PRef."%') or Costcent_name like"."('%".$PRef."%')) and ifnull(Costcent_Deactivate,0)=0 ".
		        " order by costcent.costcent_code";
				

 

$result1=mysql_query($query1,$connection) or die(mysql_error());
	 
 
//echo $query1;exit;

$costcentrows   = mysql_num_rows($result1);
if ($costcentrows != 0){
	
	$costcent_array = "";
	$i = 0;
	$costcent_array = "(";
	while($rows=mysql_fetch_array($result1)){
		
		 
		if($i==0)
		{
			$costcent_array = $costcent_array. "'" .$rows[0] ."'";
		}
		else
		{
				$costcent_array =$costcent_array. ",'" .$rows[0] ."'";
		}
		$i = $i +1;
		 
	}
	$costcent_array =$costcent_array. ")";
	
	//echo $costcent_array; echo "<br>";
	$filtercostcent = "and ( costcent_code in";
	$filtercostcent = $filtercostcent . $costcent_array;
	
	
	$filtercostcent.=" or costcent_code IS NULL   or costcent_code = '')";
	 
  //  echo $filtercostcent;exit;		
}


//by rod  end check cost center 

 
	$appendcostcenter = "";
	//  echo $_GET['filtercostinputexist'];exit;
	
	if (isset($_GET['filtercostinputexist']))
	{
		$appendcostcenter = " and ( costcent_code in ('"  .$_GET['filtercostinputexist']. "') or costcent_code IS NULL   or costcent_code = '')";
	 // second choice
	//  $appendcostcenter = " and   costcent_code in ('"  .$_GET['filtercostinputexist']. "')  ";
	}
	  if(!empty($costcent_array))
  {
	 $appendcostcenter =  $appendcostcenter   . "  " .$filtercostcent;
  }
  //echo $appendcostcenter;exit;
	
//end by rod 
	
	$query = "";
	$query = $query." Select Items.Item_Code as ac_code, Items.Item_Description  as ac_name ".
					" From Items ";

		
	/*if ($FilterExistWarehouse >	0)	{
		$query = $query." ".$QueryFilterWarehouse." on warehouse.warehouse_code like concat(QW.code,'%') " ;
	}
	if ($FilterExistGroup >	0) {
		$query=$query." ".$QueryFilterGroup. " on grouping.group_code like concat(QG.code,'%')";
	}*/	
							 
	$query = $query." Where Items.Item_Code like '%".trim(strtoupper($vKeyword))."%' and (ifNull(Items.item_active,0) = 1 and 1 = 1) ";
	if ($groupexist > 0)
	$query = $query. " and items.group_code In (select group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."') ";

	$query = $query." and item_active = 1";
	$query = $query.  $appendcostcenter;
	$query = $query." order by Items.Item_Code, Items.Item_Description  ";		
	$query = $query." limit 10 ";
 //	echo $query;	exit;

}

else if ($vTableName == 'clients') {
	
	$query = "";
	$query = $query." Select clients.ledger_number as ac_code, clients.ledger_name  as ac_name ".
					" From clients ";

		
	/*if ($FilterExistWarehouse >	0)	{
		$query = $query." ".$QueryFilterWarehouse." on warehouse.warehouse_code like concat(QW.code,'%') " ;
	}
	if ($FilterExistGroup >	0) {
		$query=$query." ".$QueryFilterGroup. " on grouping.group_code like concat(QG.code,'%')";
	}*/	
							 
	$query = $query." Where clients.ledger_number like '".$vKeyword."%' ";
	
	$query = $query." order by clients.ledger_number, clients.ledger_name  ";		
	$query = $query." limit 10 ";
//	echo $query;	

}
else if ($vTableName == 'warehouse') {

	$query = " select warehouse.warehouse_code as ac_code, warehouse.warehouse_name as ac_name from warehouse ";
	             
	if ($whexist >	0) {
		$query = $query." Inner join ( select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk ".
						"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
						"              and (warehouse_code is not null and warehouse_code != '') ".
						"			 ) Q1 on warehouse.Warehouse_Code = Q1.warehouse_code ";
	}
	
	$query = $query." where (warehouse.warehouse_code like '".$vKeyword."%' or warehouse.warehouse_name like '%".$vKeyword."%') ".
					//" And ifNull(Warehouse_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0) ".
					" order by warehouse.warehouse_code ";
	$query = $query." limit 10 ";			  
	
}
else if ($vTableName == 'ledgers') {
	
	$query = "";
	$query = $query." Select Ledger.Ledger_Number as ac_code, Ledger.Ledger_Name as ac_name ".
					" From Ledger ";
	
	$query = $query." where ledger.ledger_number like '".$vKeyword."%' ";
	$query = $query." and ifnull(ledger.ledger_grpdet,0) = 0 ";
	
	if($ledgerinclude== 1)
		$query = $query." And Exists ( select 1 from ".$_SESSION['dbLabel'].".ledgerstk ".
						" 		       where ledgerstk.user_account='".$_SESSION['useraccount']."' ".
						"		       and ledger.ledger_number like concat(ledgerstk.ledger_number,'%') ".
						"   		) ";

	if($ledgerinclude== 0)
		$query = $query." And Not Exists ( select 1 from ".$_SESSION['dbLabel'].".ledgerstk ".
						" 			       where ledgerstk.user_account='".$_SESSION['useraccount']."' ".
						"       		   and ledger.ledger_number like concat(ledgerstk.ledger_number,'%') ".
						"                ) ";
		
	if ($Module_BelongsToGroup == 1)			 
		$query = $query." And ifNull(ledger.Ledger_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0) ";
		
	$query = $query." And ((ifNull(ledger.Ledger_inactive,0) = 0 and 1 = 1) Or (ifNull(ledger.Ledger_inactive,0) = 1 and ifNull(Date_Format(ledger.Ledger_inactivedate,'%Y%m%d'), Date_Format(now(),'%Y%m%d')) >= Date_Format(now(),'%Y%m%d')) )  ";
	$query = $query." order by Ledger.Ledger_Number, Ledger.Ledger_Name  ";		
	$query = $query." limit 10 ";
	//echo $query;

}
else if (in_array($vTableName, array("project","costcenter","branch","machine","shipment"))) {

	$query = " Select Divisions.Divisions_ID as ac_id, Divisions.Divisions_Code as ac_code, Divisions.Divisions_Name as ac_name ".
	  	     " From Divisions Inner Join DivisionTypes on Divisions.DivisionTypes_Id = DivisionTypes.DivisionTypes_Id ";

	if($FilterExistDiv > 0){
		$Q = 'Q'.$vTableName;
		$query = $query." ".$QueryFilterDiv."  on (Divisions.Divisions_Code like concat($Q.code,'%')) ";
	}
	
	$query = $query." Where Divisions.Divisions_Code Like '".$vKeyword."%'  ".
					" And Upper(DivisionTypes_Name) = Upper('".$vTableName."') ".
					" Order By Divisions.Divisions_Code ";
	$query = $query." limit 10 ";
	
}
else if ($vTableName == 'getitem') {

 
	//echo "tt";
	//Read From Setup Table							
	$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2 from setup ";
	$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
	while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
	}
	
	$querymodule=" Select ifNull(module_sellingbyclient,0) as module_sellingbyclient, ifnull(module_chargesInTransfer,0) as module_chargesInTransfer From Module ";
	$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
	while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
	}


	$sub_query = "SELECT Client_SellingPrice, ifNull(Client_DiscountPer,0),currency_code FROM Clients WHERE Ledger_Number = '".$vClientCode."'";
	//echo "<br><br>".$sub_query;
	//exit;
	$sub_result = selectData($connection, $sub_query);
	$c_sp	   = $sub_result[0];
	$c_discper = $sub_result[1];
	$client_cur = $sub_result[2];
	
	if($module_sellingbyclient == 1)
	$sub_query = " Select if('".$client_cur."'='$base1',ifnull(sellingpriceb1,Item_SellingPrice1Base1),if('".$client_cur."'='$base2',ifnull(sellingpriceb2,Item_SellingPrice1Base2),Item_SellingPrice1))
									, if('".$client_cur."'='$base1',Item_SellingPrice2Base1,if('".$client_cur."'='$base2',Item_SellingPrice2Base2,Item_SellingPrice2))
									, if('".$client_cur."'='$base1',Item_SellingPrice3Base1,if('".$client_cur."'='$base2',Item_SellingPrice3Base2,Item_SellingPrice3))
									, if('".$client_cur."'='$base1',Item_SellingPrice4Base1,if('".$client_cur."'='$base2',Item_SellingPrice4Base2,Item_SellingPrice4))
									, if('".$client_cur."'='$base1',Item_SellingPrice5Base1,if('".$client_cur."'='$base2',Item_SellingPrice5Base2,Item_SellingPrice5))
									, if('".$client_cur."'='$base1',Item_SellingPrice6Base1,if('".$client_cur."'='$base2',Item_SellingPrice6Base2,Item_SellingPrice6)) ";
	else	{						
	
	// get price by cost from client
		$querySaleatCost=" select ifnull(Client_priceatcost , 0) ".
						" from clients where ledger_number = '$vClientCode'";
		$resultSC = mysql_query($querySaleatCost,$connection) or die(mysql_error());
		$SC  = mysql_fetch_array($resultSC);
		 
		if($SC[0] != 1){
			$sub_query = " Select if('".$client_cur."'='$base1',Item_SellingPrice1Base1,if('".$client_cur."'='$base2',Item_SellingPrice1Base2,Item_SellingPrice1))";
		}else{
			$sub_query = " Select if('".$client_cur."'='$base1', meancostcur(item_code,DATE_FORMAT('".insertMySQLDate($vDate)."','%Y-%m-%d'),1,'','') ,
			if('".$client_cur."'='$base2',meancostcur(item_code,DATE_FORMAT('".insertMySQLDate($vDate)."','%Y-%m-%d'),2,'','') ,
			meancostcur(item_code,DATE_FORMAT('".insertMySQLDate($vDate)."','%Y-%m-%d'),1,'','')))";
		}
	
	$sub_query .= "					, if('".$client_cur."'='$base1',Item_SellingPrice2Base1,if('".$client_cur."'='$base2',Item_SellingPrice2Base2,Item_SellingPrice2))
									, if('".$client_cur."'='$base1',Item_SellingPrice3Base1,if('".$client_cur."'='$base2',Item_SellingPrice3Base2,Item_SellingPrice3))
									, if('".$client_cur."'='$base1',Item_SellingPrice4Base1,if('".$client_cur."'='$base2',Item_SellingPrice4Base2,Item_SellingPrice4))
									, if('".$client_cur."'='$base1',Item_SellingPrice5Base1,if('".$client_cur."'='$base2',Item_SellingPrice5Base2,Item_SellingPrice5))
									, if('".$client_cur."'='$base1',Item_SellingPrice6Base1,if('".$client_cur."'='$base2',Item_SellingPrice6Base2,Item_SellingPrice6)) ";
	}	
	$sub_query =$sub_query." ,ifNull(Item_Promotion, 0), ifNull(Item_NonStock, 0), ifNull(item_zeroPrice, 0), Item_UnitSalesDesc, Item_UnitPurchaseDesc, ";
	if($module_sellingbyclient == 1) $sub_query =$sub_query." ifnull(sp.currency_code,items.Currency_Code), ";
	else $sub_query =$sub_query." items.Currency_Code, ";
		$sub_query =$sub_query." ifNull(Item_MinimumPrice, 0) As Item_MinimumPrice, ifNull(Item_Unit,1), ifNull(Item_Vat,0), ifNull(Item_Pack,0), ".
				 " ifNull(Item_Expire,0), ifNull(item_withserialno,0), Items.Project_Code, Items.CostCent_Code, ifNull(Item_CostMethod,0), Item_Description, ifNull(Item_Package,0) ".
				 " FROM items ";
	if($module_sellingbyclient == 1)
	$sub_query =$sub_query." left join sellingpricebyclient sp on sp.item_code = items.item_code and sp.client_code = '$vClientCode' ";			 
	$sub_query =$sub_query." WHERE items.Item_Code = ".GetSQLValueString(trim(strtoupper($vItemCode)),"text")." and ifnull(item_active,0) = 1 ";
	
	$sub_query = $sub_query . $appendcostcenter;
	
	//echo $sub_query;

	
 
	$sub_result = selectData($connection, $sub_query);
	$i_sp 		 = $sub_result[0];
	$i_promo     = $sub_result[6];
	$i_nonstock  = $sub_result[7];
	$i_zeroprice = $sub_result[8];
	$i_unit1     = $sub_result[9];
	$i_unit2 	 = $sub_result[10]; 
	$i_curr  	 = $sub_result[11];
	$i_minprice	 = $sub_result[12];
	$i_unitcoef	 = $sub_result[13];
	$i_vat		 = $sub_result[14];
	$i_pack		 = $sub_result[15];
	$i_itemexpire	  = $sub_result[16];
	$i_itemwithserial = $sub_result[17];
	$i_project 		  = $sub_result[18];
	$i_costcent 	  = $sub_result[19];
	$i_costmethod	  = $sub_result[20];
	$i_itemname		  = $sub_result[21];
	
	
	$i_itemunit		  = $sub_result[13];
	$i_itempackage		  = $sub_result[22];
	
		
	switch ($c_sp){
		case "II":
			$i_sp = $sub_result[1]; break;
		case "III":
			$i_sp = $sub_result[2]; break;
		case "IV":
			$i_sp = $sub_result[3]; break;
		case "V":
			$i_sp = $sub_result[4]; break;
		case "VI":
			$i_sp = $sub_result[5]; break;
	}
	
	if(($i_sp == 0 || $i_sp == '') && ($i_pack == 1)){
		$query = " SELECT Sum(if(ifNull(PackDt_CostPercentage,0) > 0, (ifNull(Item_SellingPrice1,0)*PackDt_Quantity), 0)) As Item_SellingPrice1, ".
				 " Sum(if(ifNull(PackDt_CostPercentage,0) > 0, (ifNull(Item_SellingPrice2,0)*PackDt_Quantity), 0)) As Item_SellingPrice2, ".
				 " Sum(if(ifNull(PackDt_CostPercentage,0) > 0, (ifNull(Item_SellingPrice3,0)*PackDt_Quantity), 0)) As Item_SellingPrice3, ".
				 " Sum(if(ifNull(PackDt_CostPercentage,0) > 0, (ifNull(Item_SellingPrice4,0)*PackDt_Quantity), 0)) As Item_SellingPrice4, ".
				 " Sum(if(ifNull(PackDt_CostPercentage,0) > 0, (ifNull(Item_SellingPrice5,0)*PackDt_Quantity), 0)) As Item_SellingPrice5, ".
				 " Sum(if(ifNull(PackDt_CostPercentage,0) > 0, (ifNull(Item_SellingPrice6,0)*PackDt_Quantity), 0)) As Item_SellingPrice6, ".
				 " 0 As Item_Promotion ".
				 " FROM items ".
				 " INNER JOIN packdt on packdt.item_codepackdt = items.item_code ".
				 " WHERE packdt.Item_Code = ".GetSQLValueString($vItemCode,"text")."";
		
		//echo "<br><br>".$query;
		$result = selectData($connection, $query);
		$i_sp = $result[0];
		$i_promo = $result[6];	
		
		switch ($c_sp){
			case "II":
				$i_sp = $result[1]; break;
			case "III":
				$i_sp = $result[2]; break;
			case "IV":
				$i_sp = $result[3]; break;
			case "V":
				$i_sp = $result[4]; break;
			case "VI":
				$i_sp = $result[5]; break;
		}
		
	} 
	$companyname = explode('_',$_SESSION['dbName']);
	$companyname = $companyname[0];
	$querySchema = "select concat(CompanyName,'_',Diryear)
				from ".$_SESSION['dbLabel'].".directories where  upper(CompanyName) = upper('$companyname');";	
	
	$query = "select price, pDate from ( ";
	$query1 = "select discountper, pDate from ( ";
	$resScehma = mysql_query($querySchema,$connection);
	$j = 0;
	//$query = " ";
	$querymain1 = " ";
	while( $rowSchema = mysql_fetch_row($resScehma)){

	$schemaname = $rowSchema[0];
	if($j != 0) {
		$querymain = $querymain." Union ";
		$querymain1 = $querymain1." Union ";
	}
	//Get The Last Selling Price For The Item
	if ($i_pack == 1){
		if ($Module_Delivery == 0) {
			 
			$querymain = $querymain." SELECT if($SalesTTC = 1, DeliveryDt_PriceTTC, DeliveryDt_Price) as price, Delivery_Date as pDate FROM DeliveryDt WHERE Client_Code = '".$vClientCode."' And Item_CodePacked = '".$vItemCode."' And ifNull(DeliveryDt_Price,0) > 0 ".
					 //" And (('".$lstWarehouse."' = '' And 1 = 1) Or ('".$lstWarehouse."' != '' And Warehouse_Code = '".$lstWarehouse."' )) ".
					 " Order By Delivery_Date Desc Limit 1 ";
					 
			$querymain1 = $querymain1." SELECT ifNull(DeliveryDt_DiscountPer,0) as discountper, Delivery_Date as pDate FROM DeliveryDt ".
					  " WHERE Client_Code = '".$vClientCode."' And Item_CodePacked = '".$vItemCode."' And ifNull(DeliveryDt_DiscountPer,0) > 0 ".
					  " Order By Delivery_Date Desc Limit 1 ";
		} else {
			$querymain = $querymain." SELECT if($SalesTTC = 1, SaleDt_PriceTTC, SaleDt_Price) as price, Sale_Date as pDate FROM SalesDt WHERE Client_Code = '".$vClientCode."' And Item_CodePacked = '".$vItemCode."' And ifNull(SaleDt_Price,0) > 0 ".
					 //" And (('".$lstWarehouse."' = '' And 1 = 1) Or ('".$lstWarehouse."' != '' And Warehouse_Code = '".$lstWarehouse."' )) ".
					 " Order By Sale_Date Desc Limit 1 ";
		
			$querymain1 = $querymain1." SELECT ifNull(SaleDt_DiscountPer,0) as discountper, Sale_Date as pDate  FROM SalesDt ".
					  " WHERE Client_Code = '".$vClientCode."' And Item_CodePacked = '".$vItemCode."'".
//					  " And ifNull(SaleDt_DiscountPer,0) > 0 ".
					  " Order By Sale_Date Desc Limit 1 ";
		}
	
	} else {
		if ($Module_Delivery == 0) {
			//$SalesTTC=0;
			$querymain = $querymain." SELECT if($SalesTTC = 1, DeliveryDt_PriceTTC, DeliveryDt_Price) as price, Delivery_Date as pDate FROM DeliveryDt WHERE Client_Code = '".$vClientCode."' And Item_code = '".$vItemCode."' And ifNull(DeliveryDt_Price,0) > 0 ".
					 //" And (('".$lstWarehouse."' = '' And 1 = 1) Or ('".$lstWarehouse."' != '' And Warehouse_Code = '".$lstWarehouse."' )) ".
					 " Order By Delivery_Date Desc Limit 1 ";
					 
			$querymain1 = $querymain1." SELECT ifNull(DeliveryDt_ITEMdISCOUNT,0) as discountper, Delivery_Date as pDate  FROM DeliveryDt ".
					  " WHERE Client_Code = '".$vClientCode."' And Item_code = '".$vItemCode."'".
//					  " And ifNull(DeliveryDt_DiscountPer,0) > 0 ".
					  " Order By Delivery_Date Desc Limit 1 ";
		} else {
			$querymain = $querymain." SELECT if($SalesTTC = 1, SaleDt_PriceTTC, SaleDt_Price) as price, Sale_Date as pDate FROM SalesDt WHERE Client_Code = '".$vClientCode."' And Item_code = '".$vItemCode."' And ifNull(SaleDt_Price,0) > 0 ".
					 //" And (('".$lstWarehouse."' = '' And 1 = 1) Or ('".$lstWarehouse."' != '' And Warehouse_Code = '".$lstWarehouse."' )) ".
					 " Order By Sale_Date Desc Limit 1 ";
					 
			$querymain1 = $querymain1." SELECT ifNull(SaleDt_DiscountPer,0) as discountper, Sale_Date as pDate  FROM SalesDt ".
					  " WHERE Client_Code = '".$vClientCode."' And Item_code = '".$vItemCode."'".
//					  " And ifNull(SaleDt_DiscountPer,0) > 0 ".
					  " Order By Sale_Date Desc Limit 1 ";
		}
	}
	$j++;
	}	
	$query = $query.$querymain;
	$query1 = $query1.$querymain1;
	
	$query= $query." ) as Q Order By pDate Desc Limit 1 ";
	$query1= $query1." ) as Q1 Order By pDate Desc Limit 1 ";
	
	
	//echo $query;
	//exit;
	$result = selectData($connection, $query);
	$i_lsp = $result[0];

	//$i_lsp =888;
	if(!isset($i_lsp)) $i_lsp = 0;
	
	$result1 = selectData($connection, $query1);
	$i_ldiscper = $result1[0];
	if(!isset($i_ldiscper)) $i_ldiscper = 0;
	
	//if($i_promo == 1) $i_sp = 0; 
	if($i_promo == 1 && $i_pack != 1) $i_sp = 0; 
	
	if($i_curr!= $base1 and $i_curr!= $base2){
	//Convert SellPrice To Invoice Currency
	$query = "SELECT ifNull(convertcurfunc('".$i_curr."', '".$vCurrencyCode."', '".insertMySQLDate($vDate)."', 0, 0, '', '', ifNull(".$i_sp.",0)),0) From Dual ";
	$result = selectData($connection, $query);
	$i_sp	= $result[0];
	if (empty($i_sp)) $i_sp = 0;
	}
	
	//Convert MinPrice To Invoice Currency
	$query = "SELECT ifNull(convertcurfunc('".$i_curr."', '".$vCurrencyCode."', '".insertMySQLDate($vDate)."', 0, 0, '', '', ifNull(".$i_minprice.",0)),0) From Dual ";
	$result = selectData($connection, $query);
	$i_minprice	= $result[0];
	if (empty($i_minprice)) $i_minprice = 0;
	
	
	$i_lpurp = 0;
	$i_cost = 0;
	if ($vClientCode == -1) {
		$query = " SELECT if($PurchaseTTC = 1, PurchaseDt_PriceTTCBase1, PurchaseDt_PriceBase1) FROM PurchasesDt WHERE Item_code = '".$vItemCode."' And ifNull(PurchaseDt_Price,0) > 0 ".
				 " Order By Purchase_Date Desc Limit 1 ";
		$result = selectData($connection, $query);
		$i_lpurp = $result[0];
		if(!isset($i_lpurp)) $i_lpurp = 0;
		
		//Convert Last Price To Invoice Currency
		$query = "SELECT ifNull(convertcurfunc('".$Base1."', '".$vCurrencyCode."', '".insertMySQLDate($vDate)."', 0, 0, '', '', ifNull(".$i_lpurp.",0)),0) From Dual ";
		$result = selectData($connection, $query);
		$i_lpurp	= $result[0];
		
		if (empty($i_lpurp)) $i_lpurp = 0;
	}	
	else if ($vClientCode == -2 || $vClientCode == -3 || $vClientCode == -4 || $vClientCode != "") {
		$i_cost = mround(Get_CostPrice($vItemCode, $i_curr, $i_costmethod, $vDate, $vCurrencyCode, $Base1, $Base2, $connection), $DecimalBase);
	}
			
	//$i_lsp=34;
	//$i_ldiscper=23343;
	$row_array['i_unit1'] 	  = $i_unit1;
	$row_array['i_unit2'] 	  = $i_unit2;
	$row_array['i_zeroprice'] = $i_zeroprice;
	$row_array['i_unitcoef']  = $i_unitcoef;
	$row_array['i_nonstock']  = $i_nonstock;
	
	$row_array['i_sp'] 		 = $i_sp;
	$row_array['i_lsp'] 	 = $i_lsp."-".$i_ldiscper;
	$row_array['i_minprice'] = $i_minprice;
	$row_array['c_discper']  = $c_discper;
	$row_array['i_vat']      = $i_vat;
	
	$row_array['i_itemexpire']     = $i_itemexpire;
	$row_array['i_itemwithserial'] = $i_itemwithserial;
	
	$row_array['i_lpurp'] 	 = $i_lpurp;
	
	$row_array['i_cost'] 	 = $i_cost;
	
	$row_array['i_itemname'] = $i_itemname;
	
	$row_array['i_itemunit'] = $i_itemunit;
	$row_array['i_itempackage'] = $i_itempackage;
	
	$row_array['i_project']  = '';
	$row_array['i_costcent'] = '';
	
	if ($Module_ProjectDt == 1)
		$row_array['i_project']  = $i_project;
	if ($Module_CostCenterDt == 1)	
		$row_array['i_costcent'] = $i_costcent;
	
			
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
}

else if ($vTableName == 'getprojcostcent') {
	$sub_query = " SELECT Items.Project_Code, Items.CostCent_Code FROM items WHERE Item_Code = ".GetSQLValueString($vItemCode,"text")."";
	//echo "<br><br>".$sub_query;
	$sub_result = selectData($connection, $sub_query);
	
	$i_project 		  = $sub_result[0];
	$i_costcent 	  = $sub_result[1];
	
	$row_array['i_project']  = $i_project;
	$row_array['i_costcent'] = $i_costcent;
			
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
	
}
else if ($vTableName == 'getitemlastprice') {
	//Get Last Purchase Price For an Item
	$i_lastprice = 0;
	$sub_query = " SELECT if($PurchaseTTC = 1, PurchaseDt_PriceTTCBase1, PurchaseDt_PriceBase1), ifNull(PurchaseDt_DiscountPer,0), Purchase_Date FROM PurchasesDt ".
				 " WHERE Item_code = '".$vItemCode."' And ifNull(PurchaseDt_Price,0) > 0 ".
				 " Order By Purchase_Date Desc Limit 1 ";
	$sub_result = selectData($connection, $sub_query);
	$i_lastprice = $sub_result[0];
	if(!isset($i_lastprice)) $i_lastprice = 0;
				
	$i_lastprice = ConvertCurrency($Base1, $vCurrencyCode, insertMySQLDate($vDate), $i_lastprice, $connection);
	if(!isset($i_lastprice)) $i_lastprice = 0;
				
	$i_lastdisc = $sub_result[1];
	$i_lastdate = $sub_result[2];
	
	
	$row_array['i_lastprice']  = $i_lastprice;
	$row_array['i_lastdisc']   = $i_lastdisc;
	$row_array['i_lastdate']   = $i_lastdate;
		
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;

}

else if ($vTableName == 'getitemcost') {
	
	$i_cost = 0;
	
	$sub_query = " SELECT Currency_Code, ifNull(Item_CostMethod,0) ".
				 " FROM items WHERE Item_Code = ".GetSQLValueString($vItemCode,"text")."";
		
	//echo "<br><br>".$sub_query;
	$sub_result = selectData($connection, $sub_query);
	$i_curr  	  = $sub_result[0];
	$i_costmethod = $sub_result[1];
	
	$i_cost = mround(Get_CostPrice($vItemCode, $i_curr, $i_costmethod, $vDate, $vCurrencyCode, $Base1, $Base2, $connection), $DecimalBase);
	if(!isset($i_cost)) $i_cost = 0;
				
	
	$row_array['i_cost']  = $i_cost;
	
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;

}

else if ($vTableName == 'getitemqty') {
	
		 



			
	$min_book_qty = 0;
	$min_available_qty = 9999999999;
	$min_exists_qty = 9999999999;
	$available_qty = 0;
	$exist_qty = 0;
				
	$vItemPackCode = '';
	$vItemPackQty  = 0;
				
				
	$sub_query = " SELECT ifNull(Item_Pack, 0), ifNull(item_zeroPrice, 0), Item_UnitSalesDesc, Item_UnitPurchaseDesc, ifNull(Item_Unit,1) ".
				 " FROM Items ".
				 " WHERE Items.Item_Code = ".GetSQLValueString($vItemCode,"text")."";
					
	//echo "<br><br>".$sub_query;
	
	
	
	$sub_result = selectData($connection, $sub_query);
	$i_pack      = $sub_result[0];
	$i_zeroprice = $sub_result[1];
	$i_unit1     = $sub_result[2];
	$i_unit2 	 = $sub_result[3]; 
	$i_unitcoef	 = $sub_result[4];
				
	if($i_pack == 1){
					
		$query = "SELECT Item_CodePackDt, Sum(PackDt_Quantity) FROM packdt WHERE Item_Code = '".$vItemCode."' Group By Item_CodePackDt ";
		$result = mysql_query($query, $connection);			
		//echo $query;
		while($cur = mysql_fetch_row($result)){
							
			$Qry_QtyExists = " SELECT ifnull(QtyExists('".$cur[0]."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0, 'SO', '', ''),0) As QtyExists ";
			$sub_result3 = selectData($connection, $Qry_QtyExists);
			$exist_qty = ($sub_result3[0] / $cur[1]); 
			if ($exist_qty < $min_exists_qty ){
				$min_exists_qty = $exist_qty;
				
				$vItemPackCode = $cur[0];
				$vItemPackQty  = $cur[1];
			}
		}
		
		$Qry_QtyBookTemp  = " SELECT ifnull(QtyBooking_Temp('".$vItemPackCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0),0)";
		$Qry_QtyAvailable = " SELECT ifnull(QtyExistsWSorder_Temp('".$vItemPackCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0, 'SO', '', '', 0),0) QtyExists ";
		//$Qry_QtyInTemp    = " Select ifNull(Sum(ifNull(SorderDt_Quantity,0)),0) FROM Temp_Sorderdt ".
			//			    " WHERE Item_code = '".$vItemPackCode."'  And  Warehouse_code = '".$vWarehouseCode."' And Sorder_ID = $PSorId And SorderDt_ID != ifNull('".$PSorDtId."',0) ";
		
		$Qry_QtyTransferInTemp = " Select ifNull(Sum(ifNull(TransferDt_Quantity,0)),0) FROM Temp_TransferDt ".
								 " WHERE Item_code = '".$vItemPackCode."'  And  TransferDt_WarehouseFrom = '".$vWarehouseCode."' ";					
								 
		$Qry_Unit2QtyExists = " SELECT ifnull(Unit2QtyExists('".$vItemPackCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0, 'SO', '', ''),0) As QtyExists ";
		//echo $Qry_Unit2QtyExists;						 
		
		//echo "<br><br>".$Qry_QtyBookTemp;
		//echo "<br><br>".$Qry_QtyInTemp;
		//echo "<br><br>".$Qry_QtyAvailable;
					
		$sub_result  = selectData($connection, $Qry_QtyBookTemp);
		$sub_result1 = selectData($connection, $Qry_QtyAvailable);
		//$sub_result2 = selectData($connection, $Qry_QtyInTemp);
		$sub_result3 = selectData($connection, $Qry_QtyTransferInTemp);
		$sub_result4 = selectData($connection, $Qry_Unit2QtyExists);
			
		if ($sub_result[0] < 0) {$sub_result[0] = 0;}
		if ($sub_result1[0] < 0){$sub_result1[0] = 0;}
		if ($sub_result2[0] < 0){$sub_result2[0] = 0;}
		if ($sub_result3[0] < 0){$sub_result3[0] = 0;}
		if ($sub_result4[0] < 0){$sub_result4[0] = 0;}
		
		$sub_result2[0] = 0;
		
		$booking_qty = (($sub_result[0] + $sub_result2[0] + $sub_result3[0]) / $vItemPackQty);
		$min_book_qty = $booking_qty;
			
		$available_qty = (($sub_result1[0] - $sub_result2[0] - $sub_result3[0]) / $vItemPackQty);
		$available_qty = abs($available_qty);
		$min_available_qty = $available_qty;

			
		$min_book_qty = ceil($min_book_qty);
		$min_available_qty = floor($min_exists_qty) - ceil($min_book_qty);
		$min_exists_qty = floor($min_exists_qty);
		$min_exists_unit2qty = floor($min_exists_unit2qty);

	} else {
		$Qry_QtyBookTemp = "SELECT ifnull(QtyBooking_Temp('".$vItemCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0),0) ";
		$result = selectData($connection, $Qry_QtyBookTemp);
		if ($result[0] < 0){$result[0] = 0;}
		$min_book_qty = $result[0];
						 
		$Qry_QtyExists = " SELECT ifnull(QtyExists('".$vItemCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0, 'SO', '', ''),0) As QtyExists ";				 
		$result = selectData($connection, $Qry_QtyExists);
		$min_exists_qty = $result[0];
		//echo $Qry_QtyExists;
		
		$Qry_Unit2QtyExists = " SELECT ifnull(Unit2QtyExists('".$vItemCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0, 'SO', '', ''),0) As QtyExists ";
		$result = selectData($connection, $Qry_Unit2QtyExists);
		$min_exists_unit2qty = $result[0];
		//echo $Qry_Unit2QtyExists;
		
		$Qry_QtyAvailable = " Select QtyExistsWSorder_Temp('".$vItemCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0, 'SO', '', '', 0) QtyExists From Dual ";
		$result = selectData($connection, $Qry_QtyAvailable);
		$min_available_qty = $result[0];

		/*$Qry_QtyInTemp = " Select ifNull(Sum(ifNull(SorderDt_Quantity,0)),0) FROM Temp_Sorderdt ".
		                 " WHERE Item_code = '".$vItemCode."'  And  Warehouse_code = '".$vWarehouseCode."' And Sorder_ID = $PSorId And SorderDt_ID != ifNull('".$PSorDtId."',0) ";
		$result = selectData($connection, $Qry_QtyInTemp);
		$QtyTemp_SO = $result[0];*/
		$QtyTemp_SO = 0;
		
		$Qry_QtyTransferInTemp = " Select ifNull(Sum(ifNull(TransferDt_Quantity,0)),0) FROM Temp_TransferDt ".
								 " WHERE Item_code = '".$vItemCode."'  And  TransferDt_WarehouseFrom = '".$vWarehouseCode."' ";
		$result = selectData($connection, $Qry_QtyTransferInTemp);						 
		$QtyTemp_TR = $result[0];
			
		$min_available_qty = $min_available_qty - $QtyTemp_SO - $QtyTemp_TR;
		$min_exists_qty = $min_exists_qty;
		$min_book_qty = $min_book_qty + $QtyTemp_SO + $QtyTemp_TR;

	}
				
			
	
	$row_array['i_bookqty']  = $min_book_qty;
	$row_array['i_existqty'] = $min_exists_qty;
	$row_array['i_availqty'] = $min_available_qty;
	$row_array['i_existunit2qty'] = $i_unit2.' '.$min_exists_unit2qty;
		
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
}	

else if ($vTableName == 'getitemqtybywh') {

	$min_book_qty = 0;
	$min_available_qty = 9999999999;
	$min_exists_qty = 9999999999;
	
	$sub_query = "SELECT Warehouse_Code From Warehouse Order By Warehouse_Code ";
	$sub_result = mysql_query($sub_query, $connection);
	//echo $sub_query;
	
	$Show = "<table align='center' border='0' cellpadding='0' cellspacing='0'>".
			"	<tr height='25px;'>
					<td width='100px;' style='text-align:center; font-size:11px;'>&nbsp;</td>
					<td width='100px;' style='text-align:center; font-size:11px;'>Existing Qty</td>
					<td width='100px;' style='text-align:center; font-size:11px;'>Booking Qty</td>
					<td width='100px;' style='text-align:center; font-size:11px;'>Real Qty</td>
				</tr>";
						
	while($sub_row = mysql_fetch_array($sub_result)){
		
		$vWarehouseCode = $sub_row[0];
		
		$Qry_QtyBookTemp = "SELECT ifnull(QtyBooking_Temp('".$vItemCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0),0) ";
		$result = selectData($connection, $Qry_QtyBookTemp);
		if ($result[0] < 0){$result[0] = 0;}
		$min_book_qty = $result[0];
						 
		$Qry_QtyExists = " SELECT ifnull(QtyExists('".$vItemCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0, 'SO', '', ''),0) As QtyExists ";				 
		$result = selectData($connection, $Qry_QtyExists);
		$min_exists_qty = $result[0];
		//echo $Qry_QtyExists;
		
		$Qry_QtyAvailable = " Select QtyExistsWSorder_Temp('".$vItemCode."', '".$vWarehouseCode."', '".MySQLDateNoTime($vDate)."', 0, 'SO', '', '', 0) QtyExists From Dual ";
		$result = selectData($connection, $Qry_QtyAvailable);
		$min_available_qty = $result[0];

		/*$Qry_QtyInTemp = " Select ifNull(Sum(ifNull(SorderDt_Quantity,0)),0) FROM Temp_Sorderdt ".
		                 " WHERE Item_code = '".$vItemCode."'  And  Warehouse_code = '".$vWarehouseCode."' And Sorder_ID = $PSorId And SorderDt_ID != ifNull('".$PSorDtId."',0) ";
		$result = selectData($connection, $Qry_QtyInTemp);
		$QtyTemp_SO = $result[0];*/
		$QtyTemp_SO = 0;
		
		$Qry_QtyTransferInTemp = " Select ifNull(Sum(ifNull(TransferDt_Quantity,0)),0) FROM Temp_TransferDt ".
								 " WHERE Item_code = '".$vItemCode."'  And  TransferDt_WarehouseFrom = '".$vWarehouseCode."' ";
		$result = selectData($connection, $Qry_QtyTransferInTemp);						 
		$QtyTemp_TR = $result[0];
			
		$min_available_qty = $min_available_qty - $QtyTemp_SO - $QtyTemp_TR;
		$min_exists_qty = $min_exists_qty;
		$min_book_qty = $min_book_qty + $QtyTemp_SO + $QtyTemp_TR;
		
		if ($min_exists_qty != 0) {
		
			$Show .= "	<tr height='25px;'>
							<td width='100px;' style='text-align:left; font-size:11px;'>".$vWarehouseCode."</td>
							<td width='100px;' style='text-align:right; font-size:11px;'>".$min_exists_qty."</td>
							<td width='100px;' style='text-align:right; font-size:11px;'>".$min_book_qty."</td>
							<td width='100px;' style='text-align:right; font-size:11px;'>".$min_available_qty."</td>
						</tr>";
		}				
	
	}
	
	$Show .= "</table>";
	echo $Show;
	
	exit;
}

else if ($vTableName == 'convertamt') {
	$vAmount   = $_GET["pamount"];
	$vjourDtRate = $_GET["jourDtRate"];
	$vjourDtRate2 = $_GET["jourDtRate2"];
	
	if($vjourDtRate == '') $vjourDtRate = 0;
	if($vjourDtRate2 == '') $vjourDtRate2 = 0;
	
	$querymodule=" Select ifNull(module_journalRate,0) As module_journalRate ".	
			" From Module";
	$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
	$rowmodule=mysql_fetch_array($resultmodule);
	$module_journalRate=$rowmodule[0];
			
	if(($vjourDtRate != 0  ) && $module_journalRate == 1){
	
	$vAmountB1 = ConvertCurrencyRate($vCurrencyCode, $vCurrencyCodeB1, insertMySQLDate($vDate), $vAmount,$vjourDtRate, $connection);
	$vAmountB2 = ConvertCurrencyRate($vCurrencyCode,$vCurrencyCodeB2, insertMySQLDate($vDate),$vAmount,$vjourDtRate, $connection);
	} 
	else if(( $vjourDtRate2 != 0 ) && $module_journalRate == 1){
	
	$vAmountB1 = ConvertCurrencyRate2($vCurrencyCode, $vCurrencyCodeB1, insertMySQLDate($vDate), $vAmount,$vjourDtRate2, $connection);
	$vAmountB2 = ConvertCurrencyRate2($vCurrencyCode,$vCurrencyCodeB2, insertMySQLDate($vDate),$vAmount,$vjourDtRate2, $connection);
	}
	else {
	$vAmountB1 = ConvertCurrency($vCurrencyCode, $vCurrencyCodeB1, insertMySQLDate($vDate), $vAmount, $connection);
	$vAmountB2 = ConvertCurrency($vCurrencyCode, $vCurrencyCodeB2, insertMySQLDate($vDate), $vAmount, $connection);
	}
	
	$row_array['amount1'] = $vAmountB1;
	$row_array['amount2'] = $vAmountB2;
		
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
}

else if ($vTableName == 'convertamt1') {

	$vAmount1  = $_GET["pamount"];
	$vjourDtRate = $_GET["jourDtRate"];
	$vjourDtRate2 = $_GET["jourDtRate2"];
	
	if($vjourDtRate == '') $vjourDtRate = 0;
	if($vjourDtRate2 == '') $vjourDtRate2 = 0;
	
	if(($vjourDtRate != 0) && $module_journalRate == 1){
	$vAmount = ConvertCurrencyRate($vCurrencyCodeB1, $vCurrencyCode, insertMySQLDate($vDate), $vAmount1,$vjourDtRate, $connection);
	
	}else if(($vjourDtRate2 != 0) && $module_journalRate == 1){
	$vAmount = ConvertCurrencyRate2($vCurrencyCodeB1, $vCurrencyCode, insertMySQLDate($vDate), $vAmount1,$vjourDtRate2, $connection);
	
	}
	else {
	$vAmount = ConvertCurrency($vCurrencyCodeB1, $vCurrencyCode, insertMySQLDate($vDate), $vAmount1, $connection);
	}
	$row_array['amount'] = $vAmount;
		
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
}
else if ($vTableName == 'convertamtrate') {
	$vAmount   		= $_GET["pamount"];
	$vRate	   		= $_GET["prate"];	
	//$vCurrencyCode	= $_GET["pcurrency"];	
	if(($vRate != 0  ) ){
	
	$vAmountB1 = ConvertCurrencyRate3($vCurrencyCode, $vCurrencyCodeB1, insertMySQLDate($vDate), $vAmount,$vRate, $connection);
	$vAmountB2 = ConvertCurrencyRate3($vCurrencyCode,$vCurrencyCodeB2, insertMySQLDate($vDate),$vAmount,$vRate, $connection);
	} 
		
	$row_array['amount1'] = $vAmountB1;
	$row_array['amount2'] = $vAmountB2;
		
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
}
else if ($vTableName == 'chkcodeexists') {
	
		 


	$sub_query = " SELECT Count(*) ".
				 " FROM ".$vDbTableName."  ".
				 " WHERE Upper(".$vDbFieldName.") = Upper('".str_replace('\\','\\\\',trim($vInputVal))."')";
				 
	if (strtoupper($vDbTableName) == 'LEDGER')			 
		$sub_query .= " and ifnull(ledger.ledger_grpdet,0) = 0 ";
		
	//echo "<br><br>".$sub_query;
	$sub_query = $sub_query ;
	//echo $sub_query;
	$sub_result = selectData($connection, $sub_query);

	$vExists = 0;
	if ($sub_result[0] >= 1) $vExists = 1;
	
	$row_array['exists'] = $vExists;
	//$row_array['text'] = $sub_query;
		
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;

}

else if ($vTableName == 'getname') {
	$query = "select base1 from setup;";
	$result = selectData($connection, $query);
	$base1 = $result[0];
	if ($vDbFieldCurr != "") {
		$sub_query = " SELECT ".$vDbFieldName.", ".$vDbFieldCurr." FROM ".$vDbTableName."  ".
					 " WHERE Upper(".$vDbFieldCode.") = Upper('".str_replace('\\','\\\\',$vInputVal)."')";
	} else {
		$sub_query = " SELECT ".$vDbFieldName." FROM ".$vDbTableName."  ".
					 " WHERE Upper(".$vDbFieldCode.") = Upper('".str_replace('\\','\\\\',$vInputVal)."')";
	}
				 
	//echo "<br><br>".$sub_query;
	$sub_result = selectData($connection, $sub_query);

	$row_array['name'] = $sub_result[0];//utf8_encode($sub_result[0]);//$sub_result[0];
	$row_array['curr'] = $sub_result[1];
	
	if($row_array['curr'] == '' && $base1 != '') $row_array['curr'] = $base1;
	//$row_array['text'] = $sub_query;
		
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;

}

else if ($vTableName == 'chkledgerbtns') {
	$PTransType   = $_GET["PTransType"];
	$PAccountType = $_GET["PAccountType"];
	
	$query_buttons = " Select Ledger_NumberFrom, Ledger_NumberTo, Ledger.Ledger_Name ".
					 " From JourTypeButton ".
					 " Left Join Ledger on Ledger.Ledger_Number = JourTypeButton.Ledger_NumberFrom ".
					 " Where JourType_code = '".$PTransType."' ".
					 " And JourTypeButton_Name = '".$PAccountType."' ".
					 " Order By JourTypeButton_Order ";
	$result_buttons = selectData($connection, $query_buttons);	
	$LedgerNumberFrom = $result_buttons[0];
	$LedgerNumberTo   = $result_buttons[1];
	$LedgerNameFrom   = $result_buttons[2];
	
	$vExistOnce = 0;
	if ($LedgerNumberFrom == $LedgerNumberTo) $vExistOnce = 1;
	
	$row_array = array();
	$row_array['existonce'] = $vExistOnce;
	$row_array['ledgerno']  = $LedgerNumberFrom;
	$row_array['ledgername']  = $LedgerNameFrom;
	//$row_array['text'] = $query_buttons;
	
	
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;

}

else if ($vTableName == 'getbalance') {

	$querybalance  = "SELECT IFNULL(SUM(if(journaldt_dbcd = 'D', journaldt_amount1, (-1)*journaldt_amount1)), 0) As Balance, 
						IFNULL(SUM(if(journaldt_dbcd = 'D', journaldt_amount2, (-1)*journaldt_amount2)), 0) As Balance2 
					FROM journaldt where ledger_number = '".$vLedgerNo."' and date_format(journaldt.journal_date,'%Y%m%d') <= date_format('".insertMySQLDate($vDate)."','%Y%m%d')";
	$resultbalance = selectData($connection, $querybalance);
	$CurrBalance   = $resultbalance[0];
	$CurrBalance2  = $resultbalance[1];

	$row_array['currbalance'] = number_format($CurrBalance, $DecimalBase)."  ".$Base1;
	$row_array['currbalance2'] = number_format($CurrBalance2, $DecimalBase)."  ".$Base2;
	
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
}

else if ($vTableName == 'checkinactiveledger') {
	$querycheck = "select ifnull(Ledger_inactive,0) from ledger where Ledger_number = '$vLedgerNo' 
				and Ledger_number in (select Ledger_number from Ledger) and ifNull(Date_Format(ledger.Ledger_inactivedate,'%Y%m%d'), Date_Format(now(),'%Y%m%d')) < date_format('".insertMySQLDate($vDate)."','%Y%m%d') "; 
	$result = selectData($connection, $querycheck);
	$Ledger_inactive   = $result[0];
	if($Ledger_inactive == '') $Ledger_inactive=0;
	$row_array['Ledger_inactive'] = $Ledger_inactive;
	
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
}

else if ($vTableName == 'getledgeramount') {
	$Type_dbcd = $_GET['Type_dbcd'];
	$PTransId  = $_GET['PTransId'];
	
	$sub_query = " Select TransDt.Trans_ID, ".
				 " Round(Sum(if(Transdt_dbcd='D', ifNull(Transdt_Amount ,0), 0)) - Sum(if(Transdt_dbcd='C', ifNull(Transdt_Amount ,0), 0)), ".$DecimalBase.") As TransDtAmount_D, ".
				 " Round(Sum(if(Transdt_dbcd='D', ifNull(Transdt_Amount1,0), 0)) - Sum(if(Transdt_dbcd='C', ifNull(Transdt_Amount1,0), 0)), ".$DecimalBase1.") As TransDtAmount1_D, ".
				 " Round(Sum(if(Transdt_dbcd='D', ifNull(Transdt_Amount2,0), 0)) - Sum(if(Transdt_dbcd='C', ifNull(Transdt_Amount2,0), 0)), ".$DecimalBase2.") As TransDtAmount2_D, ".
				 " Round(Sum(if(Transdt_dbcd='D', ifNull(Transdt_Amount ,0), 0)) - Sum(if(Transdt_dbcd='C', ifNull(Transdt_Amount ,0), 0)), 9) As TransDtAmount, ".
				 " Round(Sum(if(Transdt_dbcd='D', ifNull(Transdt_Amount1,0), 0)) - Sum(if(Transdt_dbcd='C', ifNull(Transdt_Amount1,0), 0)), 9) As TransDtAmount1, ".
				 " Round(Sum(if(Transdt_dbcd='D', ifNull(Transdt_Amount2,0), 0)) - Sum(if(Transdt_dbcd='C', ifNull(Transdt_Amount2,0), 0)), 9) As TransDtAmount2 ".
				 " From Temp_TransDt As TransDt ".
				 " Where TransDt.Trans_ID = '".$PTransId."' ".
				 " Group By TransDt.Trans_ID ";

	//echo $sub_query;
	$sub_result = selectData($connection, $sub_query);
			
	if ($Type_dbcd == 'D' && $sub_result[2] < 0) {
		
		$TransDtAmount    = mabsolute($sub_result[5]);
		$TransDtAmount    = ConvertCurrency($Base1, $pcurrency, insertMySQLDate($vDate), $TransDtAmount, $connection);
				
		$TransDtAmount1   = mabsolute($sub_result[5]);
		$TransDtAmount2   = mabsolute($sub_result[6]);
				
		$TransDtAmount_D  = mabsolute($sub_result[2]);	
		$TransDtAmount_D  = ConvertCurrency($Base1, $pcurrency, insertMySQLDate($vDate), $TransDtAmount_D, $connection);
				
		$TransDtAmount1_D = mabsolute($sub_result[2]);
		$TransDtAmount2_D = mabsolute($sub_result[3]);
	}
	else if ($Type_dbcd == 'C' && $sub_result[2] > 0) {
			
		$TransDtAmount    = mabsolute($sub_result[5]);
		$TransDtAmount    = ConvertCurrency($Base1, $pcurrency, insertMySQLDate($vDate), $TransDtAmount, $connection);
				
		$TransDtAmount1   = mabsolute($sub_result[5]);
		$TransDtAmount2   = mabsolute($sub_result[6]);
						
		$TransDtAmount_D  = mabsolute($sub_result[2]);	
		$TransDtAmount_D  = ConvertCurrency($Base1, $pcurrency, insertMySQLDate($vDate), $TransDtAmount_D, $connection);
				
		$TransDtAmount1_D = mabsolute($sub_result[2]);
		$TransDtAmount2_D = mabsolute($sub_result[3]);

	}

	$row_array['TransDtAmount']  = $TransDtAmount;
	$row_array['TransDtAmount1'] = $TransDtAmount1;
	$row_array['TransDtAmount2'] = $TransDtAmount2;
	$row_array['TransDtAmount_D']  = $TransDtAmount_D;
	$row_array['TransDtAmount1_D'] = $TransDtAmount1_D;
	$row_array['TransDtAmount2_D'] = $TransDtAmount2_D;
	
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
}
else if ($vTableName == 'checkLedgerCurrency') {
	$cur 		 = $_GET['pcurrency'];
	$ledger_code = $_GET['pLedgercode'];
	$querycheck = "select Currency_code from ledger where Ledger_number = '".$ledger_code."' "; 
	$rescheck = mysql_query($querycheck,$connection);
	$rwcheck = mysql_fetch_row($rescheck);
	if($rwcheck[0] != '' && $rwcheck[0] == $cur){
		$row_array['checkCur'] = 1;
		$row_array['cur'] = $cur;
	} else if($rwcheck[0] != '' && $rwcheck[0] != $cur){
		$row_array['checkCur'] = 0;
		$row_array['cur'] = $rwcheck[0];
	} else {
		$row_array['checkCur'] = 1;
		$row_array['cur'] = $cur;
	}
	array_push($return_arr,$row_array);
	echo json_encode($return_arr);
	exit;
}	
$result=mysql_query($query,$connection) or die(mysql_error($connection));	
if(mysql_num_rows($result))
{
	while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
		//extract($row);
		
		//$list .= $ac_code. "||" . $ac_id. "||" . $ac_name."\n";
		$row_array['value'] = $row['ac_code'];
        $row_array['id']    = $row['ac_code'];
        $row_array['code']  = $row['ac_code'];
		$row_array['name']  = $row['ac_name'];
         
        array_push($return_arr,$row_array);

	}

	// send all collected data to the client
	//$list = substr($list,0,-2);
	//echo $list;
	echo json_encode($return_arr);
	
}
else
	echo "";
	
	

?>