<?php
include_once('database_connection.php');


if(isset($_GET['action']))
{
	if($_GET['action']=="CheckShopStock")
	{
		$ItemID=$_GET['ItemID'];
		$GodownID=$_GET['GodownID'];
		echo checkStockShop($ItemID,$GodownID,$connection);
	}
	if($_GET['action']=="StockSummaryNew")
	{
		


		$data=array();

		if(isset($_GET['CompanyID']) && $_GET['CompanyID']>0 && $_GET['CompanyID']!="undefined")
		{
			$CompanyID="godownid=".$_GET['CompanyID']." and ";
		}
		else
		{
			$CompanyID='';
		}

		if(isset($_GET['item']) && $_GET['item']>0 && $_GET['item']!="undefined")
		{
			$item="ITEMID=".$_GET['item']." and ";
		}
		else
		{
			$item='';
		}

		if((isset($_GET['startdate']) && $_GET['startdate']!="" && $_GET['startdate']!="undefined" ) && (isset($_GET['enddate']) && $_GET['enddate']!="" && $_GET['enddate']!="undefined" ))
		{
			$date="TransDate between '".$_GET['startdate']."' and '".$_GET['enddate']."' and ";
		}
		else
		{
			$date='';
		}	
		$searchbox=$CompanyID."".$item."".$date;
		 if($date=="")//
		  $sql="
	select *,isnull([0],0) as Opening,[1],isnull([2],0) as recstock,isnull([3],0) as purchaseRet,isnull([4],0) as purchase,isnull([5],0) as Sale,isnull([6],0) as StoreIssue,isnull([7],0) as DepartISSUE,[8],[9],[10],[12],isnull([16],0) as StoreRet,isnull([17],0) as DepartRet,isnull([25],0) as  SalesRet,isnull([55],0) as Cutting,isnull([56],0) as Finish,[57],[58],isnull([59],0) as Sale2,[60],[61],[100] from (
select sum(Qty) as Qty,InvoiceTypeID,(Select Name from inventory as inv  where inv.ID=(Select Under from Inventory as i where i.id=ItemID)) as CategoryName,(Select Name from inventory as inv1  where inv1.ID=ItemID) as ItemName ,(Select StandardPrice3 from inventory as inv2  where inv2.ID=ItemID) as costprice,(Select StandardPrice2 from inventory as inv2  where inv2.ID=ItemID) as saleprice,(Select Barcode from inventory as inv3  where inv3.ID=ItemID) as barcode,(Select KeyField from inventory as inv4  where inv4.ID=ItemID) as code,ItemID from StockLedger where $searchbox 1=1 group by InvoiceTypeID,ItemID)  as t
PIVOT(
    Sum(Qty) 
    FOR InvoiceTypeID IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[12],[16],[17],[25],[55],[56],[57],[58],[59],[60],[61],[100])) AS pivot_table";
    	else
	 $sql="select pivot_table.*,i.ID as ItemID1,i.Barcode as barcode ,i.Name as ItemName,i.StandardPrice1 as costprice,i.StandardPrice2 as saleprice,
	(isnull([0],0)) as Opening,[1],
	isnull([2],0) as recstock,isnull([3],0) as purchaseRet,isnull([4],0) as purchase,isnull([5],0) as Sale,isnull([6],0) as StoreIssue,isnull([7],0) as DepartISSUE,[8],
	[9],[10],[12],isnull([16],0) as StoreRet,isnull([17],0) as DepartRet,isnull([25],0) as SalesRet,isnull([55],0) as Cutting,isnull([56],0) as Finish,[57],
	[58],isnull([59],0) as Sale2,[60],[61],[100] from ( select sum(Qty) as Qty,InvoiceTypeID,TransDate,
	(Select Name from inventory as inv where inv.ID=(Select Under from Inventory as i where i.id=ItemID)) as CategoryName,
	(Select KeyField from inventory as inv4 where inv4.ID=ItemID) as code,ItemID from StockLedger
	where $searchbox  1=1 group by InvoiceTypeID,ItemID,TransDate) as t PIVOT( Sum(Qty) 
	FOR InvoiceTypeID IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[12],[16],[17],[25],[55],[56],[57],[58],[59],[60],[61],[100])) AS pivot_table
	right join Inventory as i  on i.ID=ItemID where degree>=2 and [group]=0";    		
    		//

		 /*$sql="   select ITEMID,Item,Category,partnumber,specs,saleprice,barcode,costprice,isNull((select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=2 and    godownid=parent.godownid),0 ) as recstock,(select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=4 and  godownid=parent.godownid) as purchase
   ,isnull((select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=3 and    godownid=parent.godownid),0) as purchaseRet,
   isnull((select sum(qty) from stocksummary where ItemID=parent.ItemID and (InvoiceType=5 or Invoicetype=8) and godownid=parent.godownid),0) as Sale,
   isNull((select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=7  and  godownid=parent.godownid),0) as DepartISSUE,isNull(
   (select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=6  and godownid=parent.godownid),0) as StoreIssue,  company,
isNull((select sum(qty) from InventoryOpeningBalance where ID=parent.ItemID  and godownid=parent.godownid),0) as Opening ,godownid ,isNull(
   (select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=17  and godownid=parent.godownid),0) as DepartRet,isNull(
   (select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=16  and godownid=parent.godownid),0) as StoreRet,0 as SalesRet
   from StockSummary as parent
   where $searchbox 1=1
   group by ITEMID,Item,Category,partnumber,specs,saleprice,barcode,costprice,godownid,company";*/
   	$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
		while($row=odbc_fetch_array($result))
		{

		foreach($row as $key=>$value)
			$row[$key]= utf8_encode($value);	

$Opening='';	
		$ItemID=isset($row['ItemID1'])?$row['ItemID1']:0;
		if($ItemID>0)
		{
		$where=" ItemID=".$ItemID." and TransDate<'".$_GET['startdate']."'";
$Opening=getnamewhere("StockLedger","sum(qty) as Qty",$where,$connection);		

$Opening=json_decode($Opening);
}

		if(isset($Opening->Qty))
		{
			$Open=$Opening->Qty;
		}
		else
		{
			$Open=0;
		}
		$row['Opening']=($row['Opening']+$Open);
			$data[]=$row;	
		}
		//$data['description']=$sum;
	//	print_r($data);

		//Code for opening 

		echo json_encode($data);

	
	}

	if($_GET['action']=="stocksummary")
	{
		$data=array();

		if(isset($_GET['CompanyID']) && $_GET['CompanyID']>0)
		{
			$CompanyID="godownid=".$_GET['CompanyID']." and ";
		}
		else
		{
			$CompanyID='';
		}

		if(isset($_GET['item']) && $_GET['item']>0)
		{
			$item="parent.ITEMID=".$_GET['item']." and ";
		}
		else
		{
			$item='';
		}

		if((isset($_GET['startdate']) && $_GET['startdate']!="") && (isset($_GET['enddate']) && $_GET['enddate']!=""))
		{
			$date="InvoiceDate between '".$_GET['startdate']."' and '".$_GET['enddate']."' and ";
		}
		else
		{
			$date='';
		}	
		$searchbox=$CompanyID."".$item."".$date;
		 $sql="   select ITEMID,Item,Category,partnumber,specs,saleprice,barcode,costprice,isNull((select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=2 and    godownid=parent.godownid),0 ) as recstock,(select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=4 and  godownid=parent.godownid) as purchase
   ,isnull((select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=3 and    godownid=parent.godownid),0) as purchaseRet,
   isnull((select sum(qty) from stocksummary where ItemID=parent.ItemID and (InvoiceType=5 or Invoicetype=8) and godownid=parent.godownid),0) as Sale,
   isNull((select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=7  and  godownid=parent.godownid),0) as DepartISSUE,isNull(
   (select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=6  and godownid=parent.godownid),0) as StoreIssue,  company,
isNull((select sum(qty) from InventoryOpeningBalance where ID=parent.ItemID  and godownid=parent.godownid),0) as Opening ,godownid ,isNull(
   (select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=17  and godownid=parent.godownid),0) as DepartRet,isNull(
   (select sum(qty) from stocksummary where ItemID=parent.ItemID and InvoiceType=16  and godownid=parent.godownid),0) as StoreRet,0 as SalesRet
   from StockSummary as parent
   where $searchbox 1=1
   group by ITEMID,Item,Category,partnumber,specs,saleprice,barcode,costprice,godownid,company";
   	$result=odbc_exec($connection, $sql);
		while($row=odbc_fetch_array($result))
		{

		foreach($row as $key=>$value)
			$row[$key]= utf8_encode($value);	

		$data[]=$row;	
		}
		//$data['description']=$sum;
	//	print_r($data);
		echo json_encode($data);

	}


}



?>