<?php
include_once('database_connection.php');
if(isset($_GET['action']))
{

if($_GET['action']=="ItemDetail_GetItems")
  		{
$MainDocumentType=$_GET['MainDocumentType'];
$SubDocumentType=$_GET['SubDocumentType'];//InvoiceType

//print_r($_REQUEST);
		if(isset($_GET['ID']))
		{
			$ID=$_GET['ID'];
/*$sql="select Name as ItemName,Under as ProductCategoryID,Barcode,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,ShowInPurchases,ShowInSales,IsFixedAsset,ShowInStock,IsNonStockAble,Details,LastModificationTimeStamp,cast(VATPercent as numeric) as VATPercent,AdditionalInfo2,AdditionalInfo1 from inventory where ID=$ID";// offset $start rows fetch next $limit rows only";*/

		}
		else
		{
		$start=0;
		$limit=10;
		if(isset($_GET['ItemName']))
		{
		$Item=$_GET['ItemName'];
		$ItemName="i.Name like '%".$Item."%' and ";
		}
		else
		{
	$ItemName="";
		}
		if(isset($_GET['InvoiceDate']) && trim($_GET['InvoiceDate'])!="")
		{
		$ItemInvoiceDate=$_GET['InvoiceDate'];
		$InvoiceDate="InvoiceDate ='".$ItemInvoiceDate."' and ";
		}
		else
		{
		$InvoiceDate="";
		}
		if(isset($_GET['InvoiceID']) && trim($_GET['InvoiceID'])!="")
		{
		$ItemInvoiceID=$_GET['InvoiceID'];
		$InvoiceID="InvoiceID =".$ItemInvoiceID." and ";
		}
		else
		{
		$InvoiceID="";
		}
		if(isset($_GET['AddtionalInfo5']) && trim($_GET['AddtionalInfo5'])!="")
		{
		$ItemAddtionalInfo5=$_GET['AddtionalInfo5'];
		$AddtionalInfo5="i.AddtionalInfo5 like '%".$ItemAddtionalInfo5."%' and ";
		}
		else
		{
		$AddtionalInfo5="";
		}
		if(isset($_GET['DeliveryDate']) && trim($_GET['DeliveryDate'])!="")
		{
		$ItemDeliveryDate=$_GET['DeliveryDate'];
		$DeliveryDate="DeliveryDate ='".$ItemDeliveryDate."' and ";
		}
		else
		{
		$DeliveryDate="";
		}
		if(isset($_GET['DepartmentID']) && trim($_GET['DepartmentID'])!="")
		{
		$ItemDepartmentID=$_GET['DepartmentID'];
		$DepartmentID="ShipperID =".$ItemDepartmentID." and ";
		}
		else
		{
		$DepartmentID="";
		}
		if(isset($_GET['SupplierID']) && trim($_GET['SupplierID'])!="")
		{
		$ItemSupplierID=$_GET['SupplierID'];
		$SupplierID="SupplierID =".$ItemSupplierID." and ";
		}
		else
		{
		$SupplierID="";
		}
		if($MainDocumentType==1)
		{
			$Rate="cast(i.StandardPrice2 as numeric)";
		}
		else
		{
			$Rate="d.UnitPrice";
		}
		$searchbox=$ItemName."".$InvoiceDate."".$InvoiceID."".$AddtionalInfo5."".$DeliveryDate."".$DepartmentID."".$SupplierID;

	/*	 $sql="select   ROW_NUMBER() OVER (
      PARTITION BY [CompanyID]
      ORDER BY [CompanyID]
   ) row_num,i.Name as ItemName,i.Under as ProductCategoryName,i.Barcode as Barcode,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.Model as Size,c.Name as CompanyShortForm,'' as col_Action,ID from inventory as i inner join godowns as c on c.GodownID=i.CompanyID where  ".$searchbox." i.[group]=0 and   ProductBaseType='".$ProductBaseType."'  order by CompanyID";*/
   // offset $start rows fetch next $limit rows only";
     $sql="    select ROW_NUMBER() OVER (
      PARTITION BY ItemID
      ORDER BY ItemID
   ) row_num,d.TransactionID as ReferenceTransactionID,d.TransactionID as TransactionID,i.Name as ItemName,ItemID,stockQty as ApproveQty,QTY as Qty,ReceivedQty,(isnull(stockQty,QTY)-isnull(ReceivedQty,0)) as BalanceQty,i.KeyField as KeyField,narration,i.Make as ItemColor,i.Model as ItemSize,c.Name as ItemCategory,i.UnitID as  ItemUnit,i.make as ItemBrand,
   i.Barcode as Barcode,d.StockQty as StockQty,d.ReceivedQty as ReceivedQty,$Rate as Rate,d.Amount as Amount,d.DiscountAmount as Discount,
   d.VATPercent as VAT,d.Amount as Amount,d.VATAmount as VATAmount,d.DiscountAmount as DiscountAmount,d.Narration as Narrattion,d.Attr1 as  Specification,i.AdditionalInfo2 PartNumber
   from invoicedetail as d  
   left outer join inventory as i on d.ItemID=i.ID
      left outer join inventory as c on i.Under=c.ID
   where (Qty-ReceivedQty)>0 and  invoiceid in(select InvoiceID from invoicemaster where ".$searchbox." invoicetype=".$MainDocumentType.")";
		}

   $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)) {
    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']=='ItemDetail_GetDocuments')
  		{

$MainDocumentType=$_GET['MainDocumentType'];
$SubDocumentType=$_GET['MainDocumentType'];//InvoiceType

		if(isset($_GET['ID']))
		{
			$ID=$_GET['ID'];
/*$sql="select Name as ItemName,Under as ProductCategoryID,Barcode,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,ShowInPurchases,ShowInSales,IsFixedAsset,ShowInStock,IsNonStockAble,Details,LastModificationTimeStamp,cast(VATPercent as numeric) as VATPercent,AdditionalInfo2,AdditionalInfo1 from inventory where ID=$ID";// offset $start rows fetch next $limit rows only";*/

		}
		else
		{
		$start=0;
		$limit=10;
		if(isset($_GET['ItemName']))
		{
		$Item=$_GET['ItemName'];
		$ItemName="i.Name like '%".$Item."%' and ";
		}
		else
		{
	$ItemName="";
		}
		if(isset($_GET['InvoiceDate']) && trim($_GET['InvoiceDate'])!="")
		{
		$ItemInvoiceDate=$_GET['InvoiceDate'];
		$InvoiceDate="InvoiceDate ='".$ItemInvoiceDate."' and ";
		}
		else
		{
		$InvoiceDate="";
		}
		if(isset($_GET['Under']) && trim($_GET['Under'])!="")
		{
		$ItemUnder=$_GET['Under'];
		$Under="i.Under =".$ItemUnder." and ";
		}
		else
		{
		$Under="";
		}
		if(isset($_GET['AddtionalInfo5']) && trim($_GET['AddtionalInfo5'])!="")
		{
		$ItemAddtionalInfo5=$_GET['AddtionalInfo5'];
		$AddtionalInfo5="i.AddtionalInfo5 like '%".$ItemAddtionalInfo5."%' and ";
		}
		else
		{
		$AddtionalInfo5="";
		}
		if(isset($_GET['DeliveryDate']) && trim($_GET['DeliveryDate'])!="")
		{
		$ItemDeliveryDate=$_GET['DeliveryDate'];
		$DeliveryDate="DeliveryDate ='".$ItemDeliveryDate."' and ";
		}
		else
		{
		$DeliveryDate="";
		}
		if(isset($_GET['SupplierID']) && trim($_GET['SupplierID'])!="" && $MainDocumentType>1)
		{//
		$ItemSupplierID=$_GET['SupplierID'];
		$SupplierID="SupplierID =".$ItemSupplierID." and ";
		}
		else
		{
		$SupplierID="";
		}
		if(isset($_GET['DepartmentID']) && trim($_GET['DepartmentID'])!="" )
		{//
		$ItemDepartmentID=$_GET['DepartmentID'];
		$DepartmentID="ShipperID =".$ItemDepartmentID." and ";
		}
		else
		{
		$DepartmentID="";
		}		
	if(isset($_GET['CompanyID']) && trim($_GET['CompanyID'])!="" && $MainDocumentType>=1)
		{//
		$ItemCompanyID=$_GET['CompanyID'];
		$CompanyID="GodownID =".$ItemCompanyID." and ";
		}
		else
		{
		$CompanyID="";
		}
		if($MainDocumentType==1)
		{
			$InvoiceStatus=" InvoiceStatus<=2 and ";
		}
		else if($MainDocumentType==2 || $MainDocumentType==100)
		{
			$InvoiceStatus=" InvoiceStatus<=2 and ";
		}
		else
		{
			$InvoiceStatus="";
		}
		$searchbox=$ItemName."".$InvoiceDate."".$Under."".$AddtionalInfo5."".$DeliveryDate."".$SupplierID."".$InvoiceStatus.$CompanyID.$DepartmentID;

	/*	 $sql="select   ROW_NUMBER() OVER (
      PARTITION BY [CompanyID]
      ORDER BY [CompanyID]
   ) row_num,i.Name as ItemName,i.Under as ProductCategoryName,i.Barcode as Barcode,i.AdditionalInfo2 as Code,i.Make as Brand,i.Rating as Color,i.Model as Size,c.Name as CompanyShortForm,'' as col_Action,ID from inventory as i inner join godowns as c on c.GodownID=i.CompanyID where  ".$searchbox." i.[group]=0 and   ProductBaseType='".$ProductBaseType."'  order by CompanyID";*/
   // offset $start rows fetch next $limit rows only";
    $sql="    select InvoiceID,KeyField,narration,InvoiceStatus  from invoiceMaster 
   where $searchbox  invoicetype=".$MainDocumentType;
		}

   $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)) {
    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']=="getJobCardService")
  		{

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

			if(isset($_GET['KeyField']) && $_GET['KeyField']!="" &&  $_GET['KeyField']!="undefined")
			$KeyField=" KeyField='".$_GET['KeyField']."' and " ;
			else
			$KeyField="";
				
		$searchbox=$InvoiceID;//$KeyField;

$sql="select TransactionID,i.Name as ItemName,InvoiceDetail.ItemID as ItemID from InvoiceDetail inner join Inventory as i on InvoiceDetail.ItemID=i.ID where $searchbox i.active=1 and InvoiceDetail.ItemID not in (select MasterValueID from InvoiceMaster_JobDetail where InvoiceID=".$_GET['InvoiceID'].")";
   $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)) {
  	$ItemID=$row['ItemID'];
  	$ProductBaseType=getnamesingle("Inventory","ProductBaseType","ID",$ItemID,$connection);
   		if($ProductBaseType=="Package")
   		{
   			$sql1="select MaterialID,Qty from Inventory_Ingredients where ItemID=$ItemID and MaterialID not in (select MasterValueID from InvoiceMaster_JobDetail where InvoiceID=".$_GET['InvoiceID'].")";
   				$result1=odbc_exec($connection, $sql1) or die(odbc_error().$sql1);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  		while ($row1 = odbc_fetch_array($result1)) {
  				
  	$MaterialName=getnamesingle("Inventory","Name","ID",$row1['MaterialID'],$connection);
  				  	$row1['TransactionID']=$row['TransactionID'];
  				  	$row1['ItemID']=$row1['MaterialID'];
  				  	$row1['ItemName']=$MaterialName." Qty:".$row1['Qty'];
  				  		    $data []=$row1;

		  		}

   		}
   		else
   		{
    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']=="getJobCardItem")
  		{
  			if(isset($_GET['InvoiceID']) && $_GET['InvoiceID']!="")
  			{
			$InvoiceID=" InvoiceID=".$_GET['InvoiceID']." and ";
  			}
  			else
  			{
  				$InvoiceID="";
  			}

			if(isset($_GET['KeyField']) && $_GET['KeyField']!="" &&  $_GET['KeyField']!="undefined")
			$KeyField=" KeyField='".$_GET['KeyField']."' and " ;
			else
			$KeyField="";
				
		$searchbox=$InvoiceID;//$KeyField;

$sql="select TransactionID,i.Name as ItemName,InvoiceDetail.ItemID as ItemID from InvoiceDetail inner join Inventory as i on InvoiceDetail.ItemID=i.ID where $searchbox i.active=1 ";
   $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)) {
  	$ItemID=$row['ItemID'];
  	$ProductBaseType=getnamesingle("Inventory","ProductBaseType","ID",$ItemID,$connection);
   		if($ProductBaseType=="Package")
   		{
   			$sql1="select MaterialID,Qty from Inventory_Ingredients where ItemID=$ItemID";
   				$result1=odbc_exec($connection, $sql1) or die(odbc_error().$sql1);
	//'.$row["CategoryID"].'
	//$limit=425;//
	//echo  odbc_num_rows($result);
  		while ($row1 = odbc_fetch_array($result1)) {
  				
  				  	$MaterialName=getnamesingle("Inventory","Name","ID",$row1['MaterialID'],$connection);
  				  	$row1['TransactionID']=$row['TransactionID'];
  				  	$row1['ItemID']=$row1['MaterialID'];
  				  	$row1['ItemName']=$MaterialName." Qty:".$row1['Qty'];
  				  		    $data []=$row1;

		  		}

   		}
   		else
   		{
    foreach ($row as $key => $value) {
        	# code...
        	$row[$key]=utf8_encode($value);
        }

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


  		}

}//end action if 
?>