<?php include_once('database_connection.php');
if(isset($_GET['action']))
{
if($_GET['action']=="InsertCustomerPrice")
{
	//print_r($_POST);
$ID=getMaxId("Inventory_Details","COSTID",$connection)+1;
$StartDate=Date("Y-m-d");
$EndDate=Date("Y-m-d");
$ItemID=$_POST['ItemID'];
$GodownID=$_POST['GodownID'];
$CustomerID=$_POST['CustomerID'];
$Price=$_POST['Price'];
odbc_autocommit($connection,false);
$delsql="delete from Inventory_Details where CustomerID=$CustomerID and ItemID=$ItemID";
		odbc_exec($connection, $delsql) or die($delsql);

	$sql="insert into Inventory_Details (COSTID,StartDate,EndDate,StatusActive,ItemID,CustomerID,TypeID,StandardPrice2,GodownID,Active) values ($ID,'$StartDate','$EndDate',1,$ItemID,$CustomerID,1,$Price,$GodownID,1)";
		odbc_exec($connection, $sql) or die($sql);
		    if (!odbc_error())
   {
      odbc_commit($connection);
      $res=array('status'=>"ok",'description'=>'Data Saved Sucessfully');
  }
else
{
      odbc_rollback($connection);
      $res=array('status'=>"Error",'description'=>'Record  Not Updated');

}
//		$res=array('status'=>"ok",'description'=>'Data Saved Sucessfully');
		echo json_encode($res);//implode(",",$res);		
}
			if($_GET['action']=="GetProducts_Customer")
		{
$data=array();
$ProductBaseType='ITEM';
		if(isset($_GET['ID']))
		{
			$ID=$_GET['ID'];
$sql="select Name as ItemName,Under as ProductCategoryID,AdditionalInfo2 as Code,Make as Brand,Rating as Color,Model as Size,CompanyID as CompanyID,'' as col_Action,ID ,ID AS ItemID,UnitID,Attr1,AdditionalInfo3,AdditionalInfo5,Barcode,cast(StandardPrice3 as numeric(7,2)) as CostPrice,cast(StandardPrice2 as numeric(7,2)) as WholeSalePrice,cast(StandardPrice1 as numeric(7,2)) as RetailPrice,cast(MinPriceLevel as numeric(7,2)) as MinPriceLevel,cast(CommisionPercent as numeric(7,2)) as CommisionPercent,cast(CommisionAmount as numeric(7,2)) as CommisionAmount,cast(MinQtyLevel as numeric(7,2)) as MinQtyLevel,cast(MaxQtyLevel as numeric(7,2)) as MaxQtyLevel,KeyField,ShowInSales as ShowInSales,ShowInStock as ShowInStock,ShowInPurchases as ShowInPurchases,IsFixedAsset as IsFixedAsset,Details,LastModificationTimeStamp,cast(VATPercent as numeric(7,2)) as VATPercent,AdditionalInfo2,AdditionalInfo1,KeyField from inventory where ID=$ID";// offset $start rows fetch next $limit rows only";
		}
		else
		{
		$start=0;
		$limit=20;
				if(isset($_GET['ItemName']) && $_GET['ItemName']!="")
		$ItemName="i.Name like '%".$_GET['ItemName']."%' and ";
		else
		$ItemName="";

		if(isset($_GET['CompanyID']) && $_GET['CompanyID']!="")
		$CompanyID=" i.CompanyID=".$_GET['CompanyID']." and ";
		else
		$CompanyID="";

		if(isset($_GET['CustomerID']) && $_GET['CustomerID']!="")
		{
			$CustomerID=" IND.CustomerID=".$_GET['CustomerID']." and ";
			$CustomerID1=" CustomerID=".$_GET['CustomerID']." ";
		}
		else
		{
		$CustomerID="";
		$CustomerID1="";
			}

		if(isset($_GET['Under']) && $_GET['Under']!="")
		$Under="i.Under=".$_GET['Under']." and ";
		else
		$Under="";
		//	
		if(isset($_GET['Code']) && $_GET['Code']!="")
		$Code="i.AdditionalInfo2 like '%".$_GET['Code']."%' and ";
		else
		$Code="";

		if(isset($_GET['Size']) && $_GET['Size']!="" && $_GET['Size']>0)
		$Size="i.Size like '%".$_GET['Size']."%' and ";
		else
		$Size="";

		if(isset($_GET['AdditionalInfo5']) && $_GET['AdditionalInfo5']!="" && $_GET['AdditionalInfo5']>0)
		$AdditionalInfo5="i.AdditionalInfo5 like '%".$_GET['AdditionalInfo5']."%' and ";
		else
		$AdditionalInfo5="";
		//i.AdditionalInfo5	
		$searchbox=$ItemName."".$Under."".$Code."".$Size."".$AdditionalInfo5."".$CompanyID.$CustomerID;
		if(isset($_GET['CustomerID']) && $_GET['CustomerID']>0)
		{
		 $sql="  select   ROW_NUMBER() OVER (
      ORDER BY i.[ID]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,(select Name from inventory as io where io.ID=inv.Under) as MasterCategory,i.under,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.AdditionalInfo1 as Part,i.Model as Size,(select Qty from InventoryOpeningBalance where ID=i.ID) as Opening,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,'' as col_Action,i.ID,i.ID as ItemID,i.ShowInSales as ShowInSales,i.ShowInStock as ShowInStock,i.ShowInPurchases as ShowInPurchases,i.IsFixedAsset as IsFixedAsset,convert(numeric(9,2),i.StandardPrice2) as WholeSalePrice,(select TOP 1 convert(numeric(9,2),IND.StandardPrice2)  from Inventory_Details where i.ID=Inventory_Details.ITEMID and $CustomerID1) as CustomerPrice,i.Barcode as Barcode from  inventory as i
   inner join Inventory as inv on i.Under=Inv.ID
   inner  join Godowns as g  on i.CompanyID=g.godownid
   inner join Inventory_Details as IND  on i.ID=IND.ITEMID
    where $searchbox i.[group]=0 and i.active=1 and   i.ProductBaseType='".$ProductBaseType."'  
    union 
     select   110*ROW_NUMBER() OVER (
      ORDER BY i.[ID]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,(select Name from inventory as io where io.ID=inv.Under) as MasterCategory,i.under,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.AdditionalInfo1 as Part,i.Model as Size,(select Qty from InventoryOpeningBalance where ID=i.ID) as Opening,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,'' as col_Action,i.ID,i.ID as ItemID,i.ShowInSales as ShowInSales,i.ShowInStock as ShowInStock,i.ShowInPurchases as ShowInPurchases,i.IsFixedAsset as IsFixedAsset,convert(numeric(9,2),i.StandardPrice2) as WholeSalePrice,0 as CustomerPrice,i.Barcode as Barcode from  inventory as i
   inner join Inventory as inv on i.Under=Inv.ID
   left outer join Godowns as g  on i.CompanyID=g.godownid
       where i.[group]=0 and i.active=1 and   i.ProductBaseType='".$ProductBaseType."' 
    and i.ShowInSales=1 and (i.ID not in(select ITEMID FRom Inventory_Details where $CustomerID1) and i.under not in (52,2) and inv.under not in (2,52)) order by i.ID
";// offset $start rows fetch next $limit rows only";
	//--left outer join Inventory_Details as IND  on i.ID=IND.ITEMID	

   $data=array();
//echo $sql;
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  while ($row = odbc_fetch_array($result)) {
        $row['col_Action']='&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,'.$row["ID"].');"></i>';
        foreach ($row as $key => $value) {
        	# code...
        	$row[$key]=utf8_encode($value);
        }
      //  $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","AdditionalInfo1","GodownID",$row['CompanyShortForm'],$connection);
      //  $row['Code']=htmlentities($row['Code']);
//code here 
  /*      $sql1="  SELECT isnull([0],0) as Opening,isnull([3],0) as PurchaseRet,isnull([2],0) as Purchase,isnull([5],0) as Sales,isnull([6],0) as TransferIN,isnull([66],0) as TransferOut,
   isnull([55],0) as Adjustment,([0]+[3]+[2]+[5]+[6]+[66]+[55]) as Balance,* FROM  (
    SELECT       Department,GodownID,    isnull(qty,0) as qty,ItemID,case when qty<0 and InvoiceTypeID=6 then 66 else InvoiceTypeID end as InvoiceTypeID 
    FROM     viewstockledger p 
        ) t PIVOT(
    sum(qty) 
    FOR InvoiceTypeID IN (
        [0],[3],[2], 
        [5], 
        [6],
		[66], 
        [7], 
        [55]
        )
) AS pivot_table where ItemID=".$row['ItemID'];

$result1=odbc_exec($connection, $sql1) or die(odbc_error().$sql1);
while ($row1 = odbc_fetch_array($result1)) {
	$row['Balance'][]=$row1;
	//$row['MasterCategory']=floatval($row['MasterCategory'])+floatval($row1['StockBalance']);
//	$row['MasterCategory']=number_format($row['MasterCategory'],2);
}*/
//$row['Balance']=
    $data []=$row;
  		}//end while
  	}//end if customerid
  }//end else
  		//print_r($data);
  		echo json_encode($data); 

	
		}
	if($_GET['action']=="StockReportOrder")
	{


	//i.ProductBaseType='".$ProductBaseType."'
	   $data=array();
	   $ProductID="";
	   if(isset($_GET['ProductBaseType']) && $_GET['ProductBaseType']!="" && $_GET['ProductBaseType']!="undefined")
	   {
	   	$ProductBaseType="inv.ProductBaseType='".$_GET['ProductBaseType']."' and ";
	   }
	   else
	   {
	   	$ProductBaseType="";
	   }
	   if(isset($_GET['ProductID']) && $_GET['ProductID']!="" && $_GET['ProductID']!="undefined")
	   {
	   	//$ProductID="(inv.ID=".$_GET['ProductID']." or inv.under=".$_GET['ProductID'].") and ";
	   	$ProductID=" inv.Name like '%".$_GET['ProductID']."%' and ";
	   	
	   }
	   else
	   {
	   	$ProductID="";
	   }
	    if(isset($_GET['Brand']) && $_GET['Brand']!="" && $_GET['Brand']!="undefined")
	   {
	   	//$Brand="(inv.ID=".$_GET['Brand']." or inv.under=".$_GET['Brand'].") and ";
	   	$Brand=" inv.Make like '%".$_GET['Brand']."%' and ";
	   	
	   }
	   else
	   {
	   	$Brand="";
	   }
	    if(isset($_GET['Barcode']) && $_GET['Barcode']!="" && $_GET['Barcode']!="undefined")
	   {
	   	//$Barcode="(inv.ID=".$_GET['Barcode']." or inv.under=".$_GET['Barcode'].") and ";
	   	$Barcode=" inv.Barcode like '%".$_GET['Barcode']."%' and ";
	   	
	   }
	   else
	   {
	   	$Barcode="";
	   }
	   if(isset($_GET['Category']) && $_GET['Category']!="" && $_GET['Category']!="undefined")
	   {
	   	//$Category="(inv.ID=".$_GET['Category']." or inv.under=".$_GET['Category'].") and ";
	   	$Category=" inv.Under=".$_GET['Category']." and ";
	   	
	   }
	   else
	   {
	   	$Category="";
	   }
	   if(isset($_GET['CompanyID']) && $_GET['CompanyID']!="" && $_GET['CompanyID']!="undefined")
	   {
	   	$CompanyID=" st.GodownID=".$_GET['CompanyID']." and ";
	   }
	   else
	   {
	   	$CompanyID="";
	   }

	   $searchbox=$ProductBaseType."".$ProductID.$CompanyID.$Brand.$Barcode.$Category;
	/*$sql="  select   i.under as under,ROW_NUMBER() OVER (
      PARTITION BY i.[under]
      ORDER BY i.[under]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,i.degree as level,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.Model as Size,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,i.ProductBaseType as ProductBaseType,i.ID from inventory as i
   left outer join Inventory as inv on i.Under=Inv.ID
   inner join Godowns as g  on i.CompanyID=g.godownid where $searchbox 1=1
      order by i.[group],i.under
";*/
   $sql=" select * from (
   select sum(qty) as Balance,ItemID,ProductBaseType,StandardPrice1 as Price, inv.Name as ItemName,inv.Make as Brand,g.Name as CompanyName,(select Name from Inventory as i where id=inv.under) as ProductCategoryName,inv.under,st.GodownID as GodownID from stockledger as st 
   inner join inventory as inv on inv.id=st.itemid  left outer join Godowns as g on g.GodownID=st.GodownID  where $searchbox st.active=1 and inv.[group]=0 
   group by inv.Under,ItemID,inv.Name,inv.Make,StandardPrice1,ProductBaseType,st.GodownID,g.Name) as u order by under";

	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  	while ($row = odbc_fetch_array($result)) 
  	{
  		foreach ($row as $key => $value) {
  			$row[$key]=utf8_encode($value);
  		}
  	    $data []=$row;
  	}
  	  		echo json_encode($data); 


	}
if($_GET['action']=="BranchWiseStockPosition")
{

	//i.ProductBaseType='".$ProductBaseType."'
	   $data=array();
	   $ProductID="";
	   if(isset($_GET['ProductBaseType']) && $_GET['ProductBaseType']!="" && $_GET['ProductBaseType']!="undefined")
	   {
	   	$ProductBaseType="inv.ProductBaseType='".$_GET['ProductBaseType']."' and ";
	   }
	   else
	   {
	   	$ProductBaseType="";
	   }
	   if(isset($_GET['ProductID']) && $_GET['ProductID']!="" && $_GET['ProductID']!="undefined")
	   {
	   	$ProductID="(inv.ID=".$_GET['ProductID']." or inv.under=".$_GET['ProductID'].") and ";
	   	
	   	
	   }
	   else
	   {
	   	$ProductID="";
	   }
	   if(isset($_GET['CompanyID']) && $_GET['CompanyID']!="" && $_GET['CompanyID']!="undefined")
	   {
	   	$CompanyID=" st.GodownID=".$_GET['CompanyID']." and ";
	   }
	   else
	   {
	   	$CompanyID="";
	   }

	   $searchbox=$ProductBaseType."".$ProductID.$CompanyID;
	/*$sql="  select   i.under as under,ROW_NUMBER() OVER (
      PARTITION BY i.[under]
      ORDER BY i.[under]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,i.degree as level,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.Model as Size,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,i.ProductBaseType as ProductBaseType,i.ID from inventory as i
   left outer join Inventory as inv on i.Under=Inv.ID
   inner join Godowns as g  on i.CompanyID=g.godownid where $searchbox 1=1
      order by i.[group],i.under
";*/
   $sql=" select * from (
   select sum(qty) as Balance,ItemID,ProductBaseType,StandardPrice1 as Price, inv.Name as ItemName,g.Name as CompanyName,(select Name from Inventory as i where id=inv.under) as ProductCategoryName,inv.under,st.GodownID as GodownID from stockledger as st 
   inner join inventory as inv on inv.id=st.itemid  left outer join Godowns as g on g.GodownID=st.GodownID  where $searchbox st.active=1 and inv.[group]=0 
   group by inv.Under,ItemID,inv.Name,StandardPrice1,ProductBaseType,st.GodownID,g.Name) as u order by under";

	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  	while ($row = odbc_fetch_array($result)) 
  	{
  		foreach ($row as $key => $value) {
  			$row[$key]=utf8_encode($value);
  		}
  	    $data []=$row;
  	}
  	  		echo json_encode($data); 

}
if($_GET['action']=="CurrentStockPosition")
{
	//i.ProductBaseType='".$ProductBaseType."'
	   $data=array();
	   $ProductID="";
	   if(isset($_GET['ProductBaseType']) && $_GET['ProductBaseType']!="" && $_GET['ProductBaseType']!="undefined")
	   {
	   	$ProductBaseType="inv.ProductBaseType='".$_GET['ProductBaseType']."' and ";
	   }
	   else
	   {
	   	$ProductBaseType="";
	   }
	   if(isset($_GET['ProductID']) && $_GET['ProductID']!="" && $_GET['ProductID']!="undefined")
	   {
	   	$ProductID="(inv.ID=".$_GET['ProductID']." or inv.under=".$_GET['ProductID'].") and ";
	   }
	   else
	   {
	   	$ProductID="";
	   }
	   if(isset($_GET['CompanyID']) && $_GET['CompanyID']!="" && $_GET['CompanyID']!="undefined")
	   {
	   	$CompanyID=" st.GodownID=".$_GET['CompanyID']." and ";
	   }
	   else
	   {
	   	$CompanyID="";
	   }

	   $searchbox=$ProductBaseType."".$ProductID.$CompanyID;
	/*$sql="  select   i.under as under,ROW_NUMBER() OVER (
      PARTITION BY i.[under]
      ORDER BY i.[under]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,i.degree as level,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.Model as Size,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,i.ProductBaseType as ProductBaseType,i.ID from inventory as i
   left outer join Inventory as inv on i.Under=Inv.ID
   inner join Godowns as g  on i.CompanyID=g.godownid where $searchbox 1=1
      order by i.[group],i.under
";*/
  $sql=" select * from (
   select sum(qty) as Balance,ItemID,ProductBaseType,StandardPrice1 as Price, Name as ItemName,(select Name from Inventory as i where id=inv.under) as ProductCategoryName,inv.under from stockledger as st 
   inner join inventory as inv on inv.id=st.itemid where $searchbox st.active=1 
   group by inv.Under,ItemID,Name,StandardPrice1,ProductBaseType) as u order by under";

	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  	while ($row = odbc_fetch_array($result)) 
  	{
  		foreach ($row as $key => $value) {
  			$row[$key]=utf8_encode($value);
  		}
  	    $data []=$row;
  	}
  	  		echo json_encode($data); 
}	
if($_GET['action']=="ListProductsApp")
{

	//i.ProductBaseType='".$ProductBaseType."'
	   $data=array();
	   $ProductID="";
	   if(isset($_GET['ProductBaseType']) && $_GET['ProductBaseType']!="")
	   {
	   	$ProductBaseType="i.ProductBaseType='".$_GET['ProductBaseType']."' and ";
	   }
	   else
	   {
	   	$ProductBaseType="";
	   }
	   if(isset($_GET['CategoryID']) && $_GET['CategoryID']!="")
	   {
	   	$ProductID="i.under=".$_GET['CategoryID']." and ";
	   }
	   else
	   {
	   	$ProductID="";
	   }

	   $searchbox=$ProductBaseType."".$ProductID;
	   $sql="select * from ( 
                    select Id, Name, Barcode, isnull(Under, 0)as Under,[Group] As IsGroup, Active,('".utf8_encode(SITEURL)."images/'+(case when PictureLocation='' then 'nopic.jpg' else PictureLocation end )) as ImageUrl,convert(numeric(9,2),DefaultQty) as DefaultQty,
                    isnull(AdditionalInfo5, 0) DealPrice,convert(numeric(9,2),StandardPrice2) as Retail, isnull(AppItemType, 0) as AppItemType from inventory
                    where id in(select under from inventory where[group] = 0 and active = 1 )
                    union all
                    select Id, Name, Barcode, isnull(Under, 0)as Under,[Group] As IsGroup, Active, ('".utf8_encode(SITEURL)."images/'+(case when PictureLocation='' then 'nopic.jpg' else PictureLocation end )) as ImageUrl,convert(numeric(9,2),DefaultQty) as DefaultQty,
                    isnull(AdditionalInfo5, 0) DealPrice,convert(numeric(9,2),StandardPrice2) as Retail, isnull(AppItemType, 0) as AppItemType from inventory
                    where [group] = 0)u order by id";
	/*$sql="  select   i.under as under,ROW_NUMBER() OVER (
      PARTITION BY i.[under]
      ORDER BY i.[under]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,i.degree as level,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.Model as Size,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,i.ProductBaseType as ProductBaseType,i.ID from inventory as i
   left outer join Inventory as inv on i.Under=Inv.ID
   inner join Godowns as g  on i.CompanyID=g.godownid where $searchbox 1=1
      order by i.[group],i.under
";*/
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  	while ($row = odbc_fetch_array($result)) 
  	{
/*  		foreach ($row as $key => $value) {
  			$row[$key]=utf8_encode($value);
  		}*/
  		if($row['IsGroup']==1)$row['IsGroup']=true;
  		else $row['IsGroup']=false;
  		if($row['Active']==1)$row['Active']=true;
  		else $row['Active']=false;
  		
  		$row['DefaultQty']=floatval($row['DefaultQty']);
  		$row['Retail']=floatval($row['Retail']);
  		
  	    $data []=$row;
  	}
  	  		echo json_encode($data); 

}

$ProductBaseType=$_GET['ProductBaseType'];
if($_GET['action']=="ListProducts")
{
	//i.ProductBaseType='".$ProductBaseType."'
	   $data=array();
	   $ProductID="";
	   if(isset($_GET['ProductBaseType']) && $_GET['ProductBaseType']!="")
	   {
	   	$ProductBaseType=" i.ProductBaseType='".$_GET['ProductBaseType']."' and ";
	   }
	   else
	   {
	   	$ProductBaseType="";
	   }
	   if(isset($_GET['ProductID']) && $_GET['ProductID']!="")
	   {
	   	$ProductID=" i.under=".$_GET['ProductID']." and ";
	   }
	   else
	   {
	   	$ProductID="";
	   }

	   $searchbox=$ProductBaseType."".$ProductID;
	$sql="  select   i.under as under,ROW_NUMBER() OVER (
      PARTITION BY i.[under]
      ORDER BY i.[under]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,i.degree as level,i.AdditionalInfo2 as Code,i.Barcode as Barcode,i.Make as Brand,i.Rating as Color,i.Model as Size,convert(Numeric,(case when  isNumeric(i.StandardPrice2)=0 then 0 else i.StandardPrice2 end )) as Price,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,i.ProductBaseType as ProductBaseType,i.ID from inventory as i
   left outer join Inventory as inv on i.Under=Inv.ID
   inner join Godowns as g  on isnull(i.CompanyID,1)=isnull(g.godownid,1) where $searchbox i.Active=1 and i.[group]=0
      order by i.[group],i.under";
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  	while ($row = odbc_fetch_array($result)) 
  	{
  		foreach ($row as $key => $value) {
  			$row[$key]=utf8_encode($value);
  		}
  	    $data []=$row;
  	}
  	  		echo json_encode($data); 
}
if($_GET['action']=="UpdateOpeningStock")
{
	//print_r($_POST);
//	return;
	odbc_autocommit($connection,false);
	$ItemID=$_POST['ItemID'];
	$Qty=$_POST['Qty'];
$GodownID=getnamesingle("Inventory","CompanyID","ID",$ItemID,$connection);
$UnitPrice=getnamesingle("Inventory","StandardPrice3","ID",$ItemID,$connection);
	$Value=$Qty*$UnitPrice;
	/*$sql="select * from inventory where id=".$ItemID;
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	if(odbc_num_rows($result)>0)
	{
		//update 
	}
  	else
  	{*/
  		//insert 
  	//}
  		
  		$PK_IOB=getMaxId("InventoryOpeningBalance","PK_IOB",$connection)+1;
  		$sql="delete from InventoryOpeningBalance where ID=".$ItemID;
  		odbc_exec($connection, $sql) or die(odbc_error().$sql);
  		$sqlinsert="insert into InventoryOpeningBalance ([UnitPrice]
      ,[Value]
      ,[GodownID]
      ,[PeriodID]
      ,[ID]
      ,[UnitID]
      ,[Qty]
      ,[PK_IOB]
      ,[Packing]
      ,[UnitSize]
      ,[BatchLotNo]
      ,[ExpiryDate]) values ($UnitPrice,$Value,$GodownID,1,$ItemID,1,$Qty,$PK_IOB,'',1,'','".Date("Y-m-d")."')";
      odbc_exec($connection, $sqlinsert) or die(odbc_error().$sqlinsert);
$Attr1='';
$Attr2='';
$Attr3='';
$Attr4='';
$Attr5='';
$Attr6='';
$Attr7='';
$Attr8='';
$Attr9='';
$Attr10='';
$Attr11='';
$Attr12='';

$ReferenceItemID=$ItemID;
//$Qty=$Value;
$InvoiceType=0;
$InvoiceID=0;
$ReferenceInvoiceID=0;
$Reference='';
$Remarks='';
$GodownID=1;
$ProjectID=1;
$BranchID=1;
$COAID=0;
$Active=1;
$ContainerNo='Open';
$UserID=1;
$Rate=$UnitPrice;
$CounterID=1;
$VoucherID=0;
$TransDate=Date("Y-m-d H:i:s");
$ExpiryDate=Date("Y-m-d");
$SupplierID=0;
$DepartmentID=0;
$ItemJson='';

$sqldelete="delete from stockledger where itemid=$ItemID and InvoiceTypeID=0";
odbc_exec($connection, $sqldelete) or die(odbc_error().$sqldelete);
$ID=getmaxId("stockledger","ID",$connection)+1;

$sql="INSERT INTO [StockLedger]
           ([ID]
           ,[ItemID]
           ,[ReferenceItemID]
           ,[Qty]
           ,[InvoiceTypeID]
           ,[InvoiceID]
           ,[ReferenceInvoiceID]
           ,[Reference]
           ,[Remarks]
           ,[GodownID]
           ,[ProjectID]
           ,[BranchID]
           ,[COAID]
           ,[Active]
           ,[ContainerNo]
           ,[UserID]
           ,[Rate]
           ,[CounterID]
           ,[VoucherID]
           ,[TransDate]
           ,[ExpiryDate]
           ,[SupplierID]
           ,[DepartmentID]
           ,[ItemJson]
           ,Attr1
           ,Attr2
           ,Attr3
           ,Attr4
           ,Attr5
           ,Attr6
           ,Attr7
           ,Attr8
           ,Attr9
           ,Attr10
           )
     VALUES ($ID,
           $ItemID,
           $ReferenceItemID,
           $Qty,
           $InvoiceType,
           $InvoiceID,
           $ReferenceInvoiceID,
           '$Reference',
           '$Remarks',
           $GodownID,
           $ProjectID,
           $BranchID,
           $COAID,
           $Active,
           '$ContainerNo',
           $UserID,
           $Rate,
           $CounterID,
           $VoucherID,
           '$TransDate',
           '$ExpiryDate',
           $SupplierID,
           $DepartmentID,
           '$ItemJson'
           ,'".$Attr1."'
           ,'".$Attr2."'
           ,'".$Attr3."'
           ,'".$Attr4."'
           ,'".$Attr5."'
           ,'".$Attr6."'
           ,'".$Attr7."'
           ,'".$Attr8."'
           ,'".$Attr9."'
           ,'".$Attr10."'
         )"; 
                     odbc_exec($connection, $sql) or die(odbc_error().$sql);
		if (!odbc_error())
 		  {
    	  odbc_commit($connection);
$res=array('status'=>"ok",'description'=>'Data Saved Sucessfully');  			}
		else
		{
      odbc_rollback($connection);
      $res=array('status'=>"Error",'description'=>'Record  Not Inserted');

		}
		echo json_encode($res);
}
if($_GET['action']=='getBarcode')
{
	
$data=array();
			if(isset($_GET['ID']))
		{
			$ID=$_GET['ID'];
			$sql="select * from [barcodeitems]  where invoiceID=$ID";
			$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
			while($row=odbc_fetch_array($result))
			{
			 foreach ($row as $key => $value) {
			
        	# code...
        	$row[$key]=utf8_encode($value);
        									  }

			$data[]=$row;
			}
		}
		echo json_encode($data); 	
}

if($_GET['action']=="GetProducts2")
		{

		if(isset($_GET['ID']))
		{
			$ID=$_GET['ID'];
  $sql="select Attr18,Name as ItemName,(select Name from Inventory as inv where inv.id=inventory.Under) as ProductCategoryID,AdditionalInfo2 as Code,Make as Brand,Rating as Color,Model as Size,CompanyID as CompanyID,'' as col_Action,ID,UnitID,Attr1,AdditionalInfo3,AdditionalInfo5,Barcode,cast(StandardPrice3 as numeric) as CostPrice,cast(StandardPrice2 as numeric) as WholeSalePrice,cast(StandardPrice1 as numeric) as RetailPrice,cast(MinPriceLevel as numeric) as MinPriceLevel,cast(CommisionPercent as numeric) as CommisionPercent,cast(CommisionAmount as numeric) as CommisionAmount,cast(MinQtyLevel as numeric) as MinQtyLevel,cast(MaxQtyLevel as numeric) as MaxQtyLevel,KeyField,ShowInSales as ShowInSales,0 as ShowInStock,ShowInPurchases as ShowInPurchases,0 as  IsFixedAsset,Details,'' as LastModificationTimeStamp,cast(VATPercent as numeric) as VATPercent,AdditionalInfo2,AdditionalInfo1,KeyField,PictureLocation from inventory where (barcode='$ID' or ID='$ID')";// offset $start rows fetch next $limit rows only";
		}
		else if (isset($_GET['Barcode']))
		{
			$Barcode=$_GET['Barcode'];
			$sql="select ROW_NUMBER() OVER (
      ORDER BY mitem.[Keyfield])  as row_num,Inv.Name as ItemName,smi.Code as Code,smi.SalesManName as EmployeeName,mitem.* from POS_MemberItem as mitem inner join SalesManInformation as smi on mitem.MemberID=smi.SalesManID
      inner join inventory as inv on mitem.ItemID=inv.ID where mitem.Barcode like '%".$Barcode."%'";
		}
		else
		{
		$start=0;
		$limit=20;
				if(isset($_GET['ItemName']) && $_GET['ItemName']!="")
		$ItemName="i.Name like '%".$_GET['ItemName']."%' and ";
		else
		$ItemName="";

		if(isset($_GET['CompanyID']) && $_GET['CompanyID']!="")
		$CompanyID=" i.CompanyID=".$_GET['CompanyID']." and ";
		else
		$CompanyID="";

		if(isset($_GET['Under']) && $_GET['Under']!="")
		$Under="i.Under=".$_GET['Under']." and ";
		else
		$Under="";
		//	
		if(isset($_GET['Code']) && $_GET['Code']!="")
		$Code="i.AdditionalInfo2 like '%".$_GET['Code']."%' and ";
		else
		$Code="";

		if(isset($_GET['Size']) && $_GET['Size']!="" && $_GET['Size']>0)
		$Size="i.Size like '%".$_GET['Size']."%' and ";
		else
		$Size="";

		if(isset($_GET['AdditionalInfo5']) && $_GET['AdditionalInfo5']!="" && $_GET['AdditionalInfo5']>0)
		$AdditionalInfo5="i.AdditionalInfo5 like '%".$_GET['AdditionalInfo5']."%' and ";
		else
		$AdditionalInfo5="";
		//i.AdditionalInfo5	
		$searchbox=$ItemName."".$Under."".$Code."".$Size."".$AdditionalInfo5."".$CompanyID;
		$sql="  select   ROW_NUMBER() OVER (
      ORDER BY i.[ID]
   ) row_num,Attr18,i.Name as ItemName,inv.Name as ProductCategoryName,i.under,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.AdditionalInfo1 as Part,i.Model as Size,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,'' as col_Action,i.ID,i.ShowInSales as ShowInSales,i.ShowInStock as ShowInStock,i.ShowInPurchases as ShowInPurchases,i.IsFixedAsset as IsFixedAsset from  inventory as i
   inner join Inventory as inv on i.Under=Inv.ID
   inner join Godowns as g  on i.CompanyID=g.godownid
    where $searchbox i.[group]=0 and i.active=1 and   i.ProductBaseType='".$ProductBaseType."' order by i.CompanyID
";// offset $start rows fetch next $limit rows only";
		}

   $data=array();
//echo $sql;
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  while ($row = odbc_fetch_array($result)) {
        $row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this,'.$row["ID"].');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,'.$row["ID"].');"></i>';
        foreach ($row as $key => $value) {
        	# code...
        	$row[$key]=utf8_encode($value);
        }
      //  $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","AdditionalInfo1","GodownID",$row['CompanyShortForm'],$connection);
      //  $row['Code']=htmlentities($row['Code']);

    $data []=$row;
  		}//end while
  		//print_r($data);
  		echo json_encode($data); 

	
		}
		//end code for product2
	if($_GET['action']=="GetProducts")
	{

		if(isset($_GET['ID']))
		{
			$ID=$_GET['ID'];
$sql="select Name as ItemName,Under as ProductCategoryID,AdditionalInfo2 as Code,Make as Brand,ItemType as Color,Model as Size,CompanyID as CompanyID,'' as col_Action,ID ,ID AS ItemID,UnitID,Attr1,AdditionalInfo3,AdditionalInfo5,Barcode,cast(StandardPrice3 as numeric(7,2)) as CostPrice,cast(StandardPrice2 as numeric(7,2)) as WholeSalePrice,cast(StandardPrice1 as numeric(7,2)) as RetailPrice,cast(MinPriceLevel as numeric(7,2)) as MinPriceLevel,cast(CommisionPercent as numeric(7,2)) as CommisionPercent,cast(CommisionAmount as numeric(7,2)) as CommisionAmount,cast(MinQtyLevel as numeric(7,2)) as MinQtyLevel,cast(MaxQtyLevel as numeric(7,2)) as MaxQtyLevel,KeyField,ShowInSales as ShowInSales,ShowInStock as ShowInStock,ShowInPurchases as ShowInPurchases,IsFixedAsset as IsFixedAsset,Details,LastModificationTimeStamp,cast(VATPercent as numeric(7,2)) as VATPercent,AdditionalInfo2,AdditionalInfo1,KeyField from inventory where ID=$ID";// offset $start rows fetch next $limit rows only";
		}
		else
		{
		$start=0;
		$limit=20;
				if(isset($_GET['ItemName']) && $_GET['ItemName']!="")
		$ItemName="i.Name like '%".$_GET['ItemName']."%' and ";
		else
		$ItemName="";

		if(isset($_GET['CompanyID']) && $_GET['CompanyID']!="" && $_GET['CompanyID']>0)
$CompanyID="(select Qty from InventoryOpeningBalance where  ID=i.ID and  GodownID=".$_GET['CompanyID']." )  ";
		else
$CompanyID="( select sum(qty) from stockledger where ItemID=i.ID and InvoiceTypeID=0 )";

		if(isset($_GET['Under']) && $_GET['Under']!="")
		$Under="i.Under=".$_GET['Under']." and ";
		else
		$Under="";
		//	
		if(isset($_GET['Code']) && $_GET['Code']!="")
		$Code="i.AdditionalInfo2 like '%".$_GET['Code']."%' and ";
		else
		$Code="";

		if(isset($_GET['Size']) && $_GET['Size']!="" && $_GET['Size']>0)
		$Size="i.Size like '%".$_GET['Size']."%' and ";
		else
		$Size="";

		if(isset($_GET['AdditionalInfo5']) && $_GET['AdditionalInfo5']!="" && $_GET['AdditionalInfo5']>0)
		$AdditionalInfo5="i.AdditionalInfo5 like '%".$_GET['AdditionalInfo5']."%' and ";
		else
		$AdditionalInfo5="";
		//i.AdditionalInfo5	
		$searchbox=$ItemName."".$Under."".$Code."".$Size."".$AdditionalInfo5;
		$sql="  select   ROW_NUMBER() OVER (
      ORDER BY i.[ID]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,(select Name from inventory as io where io.ID=inv.Under) as MasterCategory,i.under,i.AdditionalInfo2 as Code,i.Make as Brand,i.ItemType as Color,i.AdditionalInfo1 as Part,i.Model as Size,$CompanyID as Opening,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,'' as col_Action,i.ID,i.ID as ItemID,i.ShowInSales as ShowInSales,i.ShowInStock as ShowInStock,i.ShowInPurchases as ShowInPurchases,i.IsFixedAsset as IsFixedAsset,convert(numeric(9,0),i.StandardPrice2) as WholeSalePrice,i.Barcode as Barcode,convert(numeric(9,0),i.MinQtyLevel) as MinQtyLevel,(select Top 1 Name from Godowns as g1 where g1.GodownID=(case when i.Attr1='' then 0 else isnull(i.Attr1,0) end )) as Location  from  inventory as i
   inner join Inventory as inv on i.Under=Inv.ID
   left outer join Godowns as g  on i.CompanyID=g.godownid
    where $searchbox i.[group]=0 and i.active=1 and   i.ProductBaseType='".$ProductBaseType."' order by i.ID
";// offset $start rows fetch next $limit rows only";
		}

   $data=array();
//echo $sql;
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  while ($row = odbc_fetch_array($result)) {
        $row['col_Action']='&nbsp;&nbsp;&nbsp;  <i class="fa fa-print fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:doBarcodePrint(this,'.$row["ID"].','.$row["ID"].',0,0);"></i>&nbsp;<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this,'.$row["ID"].');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,'.$row["ID"].');"></i>';
        foreach ($row as $key => $value) {
        	# code...
        	$row[$key]=utf8_encode($value);
        }
      //  $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","AdditionalInfo1","GodownID",$row['CompanyShortForm'],$connection);
      //  $row['Code']=htmlentities($row['Code']);
//code here 
        $sql1="  SELECT isnull([0],0) as Opening,isnull([3],0) as PurchaseRet,isnull([2],0) as Purchase,isnull([5],0) as Sales,isnull([6],0) as TransferIN,isnull([66],0) as TransferOut,
   isnull([55],0) as Adjustment,([0]+[3]+[2]+[5]+[6]+[66]+[55]) as Balance,* FROM  (
    SELECT       Department,GodownID,    isnull(qty,0) as qty,ItemID,case when qty<0 and InvoiceTypeID=6 then 66 else InvoiceTypeID end as InvoiceTypeID 
    FROM     viewstockledger p 
        ) t PIVOT(
    sum(qty) 
    FOR InvoiceTypeID IN (
        [0],[3],[2], 
        [5], 
        [6],
		[66], 
        [7], 
        [55]
        )
) AS pivot_table where ItemID=".$row['ItemID'];

/*$result1=odbc_exec($connection, $sql1) or die(odbc_error().$sql1);
while ($row1 = odbc_fetch_array($result1)) {
	$row['Balance'][]=$row1;
	//$row['MasterCategory']=floatval($row['MasterCategory'])+floatval($row1['StockBalance']);
//	$row['MasterCategory']=number_format($row['MasterCategory'],2);
}*/
    $data []=$row;
  		}//end while
  		//print_r($data);
  		echo json_encode($data); 

	
	}//end if GETPRODUCTS
	if($_GET['action']=="searchProducts")
	{
	//	print_r($_POST);
//		print_r($_GET);
		$searchbox="";
$data=array();
		if(isset($_GET['ItemName']) && $_GET['ItemName']!="")
		$ItemName="i.Name like '%".$_GET['ItemName']."%' and ";
		else
		$ItemName="";

		if(isset($_GET['CompanyID']) && $_GET['CompanyID']!="")
		$CompanyID=" i.CompanyID =".$_GET['CompanyID']." and ";
		else
		$CompanyID="";

		if(isset($_GET['Under']) && $_GET['Under']!="")
		$Under="i.Under=".$_GET['Under']." and ";
		else
		$Under="";
		//	
		if(isset($_GET['Code']) && $_GET['Code']!="")
		$Code="i.AdditionalInfo2 like '%".$_GET['Code']."%' and ";
		else
		$Code="";

		if(isset($_GET['Size']) && $_GET['Size']!="")
		$Size="i.Size like '%".$_GET['Size']."%' and ";
		else
		$Size="";

		if(isset($_GET['AdditionalInfo5']) && $_GET['AdditionalInfo5']!="")
		$AdditionalInfo5="i.AdditionalInfo5 like '%".$_GET['AdditionalInfo5']."%' and ";
		else
		$AdditionalInfo5="";
		//i.AdditionalInfo5	
		$searchbox=$ItemName."".$Under."".$Code."".$Size."".$AdditionalInfo5."";

	/*	$search=array(
    'i.Name' =>(isset($_GET['ItemName']))?$_GET['ItemName']:"",
   	'i.CompanyID' =>(isset($_GET['CompanyID']))?$_GET['CompanyID']:"",
   	'i.Under' =>(isset($_GET['Under']))?$_GET['Under']:"",
   	'i.Attr2' =>(isset($_GET['Code']))?$_GET['Code']:"",
    'i.Size' =>(isset($_GET['Size']))?$_GET['Size']:"",
   'i.AdditionalInfo5' =>(isset($_GET['AdditionalInfo5']))?$_GET['AdditionalInfo5']:"");
   foreach($search as $key =>$value)
   {
   	$values="";
   	if(getDataType($connection,"inventory",str_replace("i.","",$key))==1)
					{
						if($value!="")
						$values="like '%".$value."%'";
					}
					else
					{
						if($value!="")
					$values ="=".$value;	
					}
		if($values!="")			
   		$searchbox .=" and $key $values";
   	
   }*/

    	 $sql="select   ROW_NUMBER() OVER (
      ORDER BY i.[CompanyID]
   ) row_num,i.Name as ItemName,inv.Name as ProductCategoryName,i.under,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.Model as Size,
   g.AdditionalInfo1 as CompanyShortForm,i.CompanyID as CompanyId ,'' as col_Action,i.ID as ID from inventory as i
   left outer join Inventory as inv on i.Under=Inv.ID
   left outer join Godowns as g  on i.CompanyID=g.godownid
    where i.[group]=0 and i.active=1 and i.ProductBaseType='ITEM' ".$searchbox."  order by 		CompanyID";
	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  while ($row = odbc_fetch_array($result)) {
        $row['col_Action']='<i class="fa fa-pencil fa-lg" style="text-align: left;color: blue;cursor:pointer;" onclick="javascript:editMaster(this,'.$row["ID"].');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteMaster(this,'.$row["ID"].');"></i>';
      //  $row['ItemName']=htmlspecialchars($row['ItemName']);
      //  $row['ProductCategoryName']=getnamesingle("inventory","Name","Under",$row['ProductCategoryName'],$connection);
      // $row['CompanyShortForm']=getnamesingle("Godowns","AdditionalInfo1","GodownID",$row['CompanyShortForm'],$connection);
      //  $row['Code']=htmlentities($row['Code']);
        	foreach ($row as $key => $value) {
        		# code...
        		$row[$key]=utf8_encode($value);
        	}
    $data []=$row;
  		}//end while
  		//print_r($data);
  		echo json_encode($data); 
	}

	if($_GET['action']=="DeleteProductSetup")
	{
		   odbc_autocommit($connection, FALSE);

		$ID=$_GET['ID'];
		$sql="select * from stockledger where ItemID=".$ID;
		$result=odbc_exec($connection,$sql);
		if(odbc_num_rows($result)>0)
		{
$res=array('status'=>"Error",'description'=>'Record  Not Deleted, Please Delete Transaction Records to Proceed it');
		}
		else
		{	
		$sql="update inventory set Active=0,datamode=0 where ID=".$ID;
			odbc_exec($connection, $sql);
		
		if (!odbc_error())
 		  {
    	  odbc_commit($connection);
$res=array('status'=>"ok",'description'=>'Data Deleted Sucessfully');  			}
		else
		{
      odbc_rollback($connection);
      $res=array('status'=>"Error",'description'=>'Record  Not Deleted');

		}
		}
		
		echo json_encode($res);//implode(",",$res);		
	}
}
		if(isset($_GET['action']) && $_GET['action']=="InsertItemInformation")
		{
			   odbc_autocommit($connection, FALSE);

			$fields=array();
			$fields=array_keys($_POST);
		//	print_r($_POST);
//return;
		//	print_r( $_FILES);
		//	singlegetname()
/*			$data= '['.$fields[0].'""}]';
			$fields=json_decode($data);
			//print_r($fields[0]);
			$d=$fields[0];*/
			if(isset($_POST['ID']))
			$ID=$_POST['ID'];
			else
			$ID=0;
	/*	$_POST['Barcode']=$_POST['ProductCode'];
		$_POST['Brand']='';
*/
		/*	if(isset($d->ID))
			$ID=$d->ID;
			else
				$ID=0;
		*/	/* $Under = $d->Under;
                    $UnitID = $d->$UnitID;
                    $Rating = $d->(color).attr('$Color');
                    $Model = $d->(size).attr('$Size');
                    $Name = txtProductName.val();
                    $AdditionalInfo1 = txtCode.val();
                    $Barcode =  txtBarcode.val();
                    $Make =  txtBrand.val();
                    $StandardPrice3 = txtCost.val();
                    $StandardPrice2 = txtRetail.val();
                    $StandardPrice1 = txtWholeSale.val();
                    $MinPriceLevel = txtMinPrice.val();
                    $CommissionPercent = txtComissionPercent.val();
                    $CommissionAmount = txtComissionAmount.val();
                    $MinQtyLevel = txtMinQty.val();
                    $MaxQtyLevel = txtMaxQty.val();
                    $AdditionalInfo2 = txtAdditionalInfo2.val();
                    $AdditionalInfo3 = $(country).attr('$Country');                    
                    $AdditionalInfo5 = $(supplier).attr('$COAID');
                    $CompanyID = $(company).attr('$GodownID');
                    $Attr1 = $(application).attr('$Application');
                    $Details = txtDetails.val();
                    $InitiateDate = txtInitiateDate.val();
                    $VATPercent = txtVATPercent.val();
                    $picture =   $('#picture').val();
                    $IsNonStockAble = IsNonStockAble;
                    $ID = txtID;
                    $LastModificationTimeStamp = txtLastModificationTimeStamp;   
                    Data.SideBarID=SideBarID; 
                    Data.ProductBaseType=ProductBaseType;*/
                    $fieldsname="";
                    $values="";
                    if($ID=="")
                    $ID=0;

                     $sql="select * from inventory where ID=".$ID;
                    $result=odbc_exec($connection,$sql);
                 	if(odbc_num_rows($result)>0)
                 	{
					//update code here
                 		$updatedata="";
                 		$ppic=$_FILES['picture']['name'];
	if($ppic!="")
		{
			$newfile=rand()*1200;
			$ext=strrchr($ppic,".");
			$newpic=$newfile.$ext;
			save($_FILES['picture'],'',"images/");	
			rename("images/".$ppic,"images/".$newpic);
			$_POST['PictureLocation']=$newpic;
		}
                 				foreach ($_POST as $key => $value) 
                 				{

   if($key!="SideBarID" &&  $key!="VehicleTypeDetail" && $key!="ID" && $key!="InitiateDate" && $key!="LastModificationTimeStamp")
									{

										if(getDataType($connection,"inventory",$key)==1)
										{

											$values="'".str_replace("_"," ",$value)."',";
										}
										else
										{
						if($value=="")$value="0";
						if($value=="on")$value="1";
						if($value=="option1")$value="0";					
										$values ="".$value.",";	
										}
										

									$updatedata.=$key."=".$values;
									}
				
                 				}
                 				$updatedata=substr($updatedata,0,strlen($updatedata)-1);
                 		  $sql="update inventory set $updatedata where ID=".$ID;
                 		 $sqlupdate="update inventory set datamode=0 where ID=".$ID;
                 odbc_exec($connection, $sqlupdate) or die(odbc_error().$sqlupdate);

                 	}// end query if for update query    
                    else
                    {

$ppic=$_FILES['picture']['name'];
	if($ppic!="")
		{
			$newfile=rand()*1200;
			$ext=strrchr($ppic,".");
			$newpic=$newfile.$ext;
			save($_FILES['picture'],'',"images/");	
			rename("images/".$ppic,"images/".$newpic);
			$_POST['PictureLocation']=$newpic;
		}
                    //code for insert 
			foreach ($_POST as $key => $value) {
				# code...
				if($key!="SideBarID" && $key!="Barcode" &&  $key!="VehicleTypeDetail" && $key!="ID")
				{
				$fieldsname.=$key.",";

					if(getDataType($connection,"inventory",$key)==1)
					{

						$values.="'".str_replace("_"," ",$value)."',";
					}
					else
					{
						if($value=="")$value="0";
						if($value=="on")$value="1";
						if($value=="option1")$value="0";						
					$values .="".$value.",";	
					}
				}
				
			}
			if(isset($_POST['Barcode']) && $_POST['Barcode']!="")
			{
$KeyField=$_POST['Barcode'];
$IsBarcodeManual=1;
			}
			else{
					# code...
$IsBarcodeManual=0;				
$Prefix=getnamesingle("TablesMaster","Prefix","TableID","69",$connection);				
$LastID=getnamesingle("TablesMaster","LastID","TableID","69",$connection)+1;			
$SequenceCode=getnamesingle("TablesMaster","SequenceCode","TableID","69",$connection);	
$PadNum=strlen($SequenceCode);
if($PadNum=="" || $PadNum==null || $PadNum<3)
{
$PadNum=6;
}
$PLastID=str_pad($LastID,$PadNum,"0",STR_PAD_LEFT);
$PKey=$Prefix.$PLastID;
$sqlupdate="update TablesMaster set LastID=$LastID where TableID=69";
	odbc_exec($connection, $sqlupdate) or die(odbc_error().$sqlupdate);
$Under=$_POST['Under'];

$BarcodeSequence=getnamesingle("Inventory","BarcodeSequence","ID",$Under,$connection);
$BarcodeSequence=str_replace("#",'', $BarcodeSequence);
if($BarcodeSequence=="")$BarcodeSequence="2021";

$KeyField=generatecode("Inventory","count(*)","ProductBaseType='ITEM'",$connection)+1;
           	$KeyField2=str_pad($KeyField,5,"0",STR_PAD_LEFT);
           	$KeyField=$BarcodeSequence.$KeyField2;

				}	
			$ID=getMaxId("inventory","ID",$connection)+1;
$values=str_replace("NaN-NaN-NaN",date("Y-m-d"),$values);
			$fieldsname=substr($fieldsname,0,strlen($fieldsname)-1);
			$values=substr($values,0,strlen($values)-1);
$sql="insert into inventory (ID,DiscountType,HasSupplierWarranty,IsAssembly,HasBatches,HasExpiry,
CommisionType,Isbarcodemanual,MaxPriceLevel,ProductBaseType,Degree,AdditionalInfo4,KeyField,Barcode,[group],active,defaultqty,$fieldsname) values('$ID',0,0,0,0,0,0,$IsBarcodeManual,0,'ITEM',2,'0','$PKey','$KeyField',0,1,1,$values)";
				}//end else for insert query 
			//	echo $sql;
			odbc_exec($connection, $sql) or die(odbc_error().$sql);

$Hierarchy=generateItemH(2,$ID,$connection);			
		    if (!odbc_error())
   {
      odbc_commit($connection);
      $res=array('status'=>"ok",'description'=>'Data Saved Sucessfully');
  }
else
{
      odbc_rollback($connection);
      $res=array('status'=>"Error",'description'=>'Record  Not Updated');

}
//		$res=array('status'=>"ok",'description'=>'Data Saved Sucessfully');
		echo json_encode($res);//implode(",",$res);		
		}
function getDataType($conn,$table,$field)
{
	$data=array();
   $sql="select data_type from information_schema.columns where table_name='$table' and column_name='$field'";
$result=odbc_exec($conn,$sql);
if(odbc_num_rows($result)>0)
{
  $data=odbc_fetch_array($result);

//print_r($data);
if($data['data_type']=="varchar" || $data['data_type']=="nvarchar" || $data['data_type']=="time" || $data['data_type']=="datetime" || $data['data_type']=="date" || $data['data_type']=="smalldatetime" || $data['data_type']=="time")
{
		return true;
} 
else 
	{
		return false;
	}
}
else
{
	return false;
} //echo json_encode($data);// print_r($data); 

}
function generateItemH($degree,$COAID,$connection)
{
	$updatesql="";
	if($degree==5)
	{
$updatesql="update accounts set Hierarchy='{'+(select convert(varchar,Under) from accounts as acc3 where acc3.COAID=(select Under from accounts as acc2 where acc2.COAID=(select Under from accounts as  acc1 where acc1.COAID=accounts.under)))+'},{'+(select convert(varchar,Under) from accounts as acc2 where acc2.COAID=(select Under from accounts as  acc1 where acc1.COAID=accounts.under))+'},{'+
(select convert(varchar,Under) from accounts as  acc1 where acc1.COAID=accounts.under)+'},{'+convert(varchar,Under)+
'},{'+convert(varchar,COAID)+'}' from accounts where COAID=$COAID";
	}
	elseif ($degree==4) {
		# code...
$updatesql="update accounts set Hierarchy='{'+(select convert(varchar,Under) from accounts as acc2 where acc2.COAID=(select Under from accounts as  acc1 where acc1.COAID=accounts.under))+'},{'+
(select convert(varchar,Under) from accounts as  acc1 where acc1.COAID=accounts.under)+'},{'+convert(varchar,Under)+
'},{'+convert(varchar,COAID)+'}' from accounts where COAID=$COAID";	
	}
	elseif ($degree==3) {
		# code...
$updatesql="update accounts set Hierarchy='{'+(select convert(varchar,Under) from accounts as  acc1 where acc1.COAID=accounts.under)+'},{'+convert(varchar,Under)+'},{'+convert(varchar,COAID)+'}' from accounts where COAID=$COAID";		
	}
	elseif($degree==2)
	{
		$updatesql="update inventory set Hierarchy='{'+convert(varchar,Under)+'},{'+convert(varchar,ID)+'}' from Inventory where ID=$COAID";
odbc_exec($connection,$updatesql);
	}
}
?>