<?php
include_once('database_connection.php');
if(isset($_GET['action']))
{
 // get category 
 	if($_GET['action']=="GetProductCategory")
 	{
 		$CategoryName="";
 		$UnderName="";
 			$ProductBaseType=$_GET['ProductBaseType'];
 		if(isset($_GET['CategoryName'])  || isset($_GET['UnderName']))
		{
		if(isset($_GET['CategoryName']))
		$CategoryName="a.Name like '".$_GET['CategoryName']."%'";
		if(isset($_GET['UnderName']))
			$UnderName="b.Name like '".$_GET['UnderName']."%'";
		if($UnderName=="" && $CategoryName=="")
		{
			$sql="select  ROW_NUMBER() OVER (
     
      ORDER BY [Under]
   ) row_num,a.Name as  CategoryName,
   (select Name from Inventory as b where b.id=a.under)
    as UnderName,SortOrder,'' as col_Action,a.Under,a.ID as CategoryID,a.LastModificationTimeStamp,BarCodePrefix,BarCodeSuffix from 
	inventory as a where [group]=1 and active=1   and ProductBaseType='".$ProductBaseType."'";
		  /* $sql="select   ROW_NUMBER() OVER (
     
      ORDER BY b.[Under]
   ) row_num,a.Name as  CategoryName,b.Name as UnderName,b.SortOrder,'' as col_Action,a.Under,a.ID as CategoryID,a.LastModificationTimeStamp FROM [Inventory] as a,[Inventory] as b

  where a.degree=2 and a.ProductBaseType='".$ProductBaseType."' and b.ID=a.under";*/
	
		}
		 else
		 {
		 	if($UnderName!="" && $CategoryName!="")
		 	{
		 		$searchbox="(".$CategoryName." OR ".$UnderName.")";
		 	}
		 	else if($UnderName!="" && $CategoryName=="")
		 	{
		 		$searchbox=$UnderName;
		 	}
		 	else if($UnderName=="" && $CategoryName!="")
		 	{
		 		$searchbox=$CategoryName;
		 	}
		 	else
		 	{
		 		$searchbox="";
		 	}
    $sql="select  ROW_NUMBER() OVER (
     
      ORDER BY [Under]
   ) row_num,a.Name as  CategoryName,
   (select Name from Inventory as b where b.id=a.under)
    as UnderName,SortOrder,'' as col_Action,a.Under,a.ID as CategoryID,a.LastModificationTimeStamp,BarCodePrefix,BarCodeSuffix from 
	inventory as a where [group]=1  and active=1  and ProductBaseType='".$ProductBaseType."'  and ".$searchbox."";

		 }
		}//end if search top isset($_GET['CategoryName']
		else
		{
			  $sql="select  ROW_NUMBER() OVER (
     
      ORDER BY [Under]
   ) row_num,a.Name as  CategoryName,
   (select Name from Inventory as b where b.id=a.under)
    as UnderName,SortOrder,'' as col_Action,a.Under,a.ID as CategoryID,a.LastModificationTimeStamp,BarCodePrefix,BarCodeSuffix from 
	inventory as a where [group]=1 and active=1    and ProductBaseType='".$ProductBaseType."' ";

		}
$data=array();
//echo $sql;
	$result=odbc_exec($connection, $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["CategoryID"].');"></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["CategoryID"].');"></i>';
        $row['CategoryName']=htmlspecialchars($row['CategoryName']);
    $data []=$row;
  }
  //print_r($data);
  $d=array();
  $count=0;
  /*foreach($data as $key=>$value)
  {
   $d[]= json_decode($data[$count]);
   $count++;
  /* if($count==$limit)break;*/
   //}*/
  // base64_encode(data)
  // mysqli_escape_string()
   echo json_encode($data);
 	}//end getproductcategory if 
   	if($_GET['action']=="optionview")
	{
	//print_r($_GET);
	$ProductBaseType=$_GET['ProductBaseType'];
$data=array();
 $sql="select  
ID as CategoryID,Name as CategoryName  FROM Inventory   where ProductBaseType='$ProductBaseType' and [group]=1 and active=1";
	$result=odbc_exec($connection, $sql);
  while ($row = odbc_fetch_array($result)) {
        $row['CategoryName']=htmlspecialchars($row['CategoryName']);
    $data []=$row;

  }
 // print_r($data);
   echo json_encode($data);
   	}//end action view 

	if($_GET['action']=="InsertProductCategory")
	{
		$Under=0;
		$Name="";
		$SortOrder=0;
	$data=array_keys($_POST);
		$d=json_decode($data[0]);
		$_POST=$d;
		//print_r($_POST);
		if(isset($_POST->CategoryID) && $_POST->CategoryID!="")
		{

		$CategoryID=$_POST->CategoryID;
		}
			else
		{		
			$CategoryID=0;
		}
		$Name=isset($_POST->Name)?$_POST->Name:0;
		$ProductBaseType=isset($_POST->ProductBaseType)?$_POST->ProductBaseType:'';
		$SortOrder=(isset($_POST->SortOrder) && $_POST->SortOrder>0)?$_POST->SortOrder:0;
		$Under=(isset($_POST->Under))?$_POST->Under:0;
		$BarCodePrefix=(isset($_POST->BarCodePrefix))?$_POST->BarCodePrefix:0;
		$BarCodeSuffix=(isset($_POST->BarCodeSuffix))?$_POST->BarCodeSuffix:0;
		
		$CategoryName='';
		$degree=1;
		// check if the record already exits 
		 $sql="select max(id) as maxid from Inventory";
		$resultcount=odbc_exec($connection, $sql);
		while($rowcount=odbc_fetch_array($resultcount))
		$countid=$rowcount['maxid']+1;
		//get the degree 
		 $sqld="select degree from Inventory where id=".$Under;

		$resultd=odbc_exec($connection, $sqld);
		while($rowd=odbc_fetch_array($resultd))
		$degree=$rowd['degree']+1;	


		 $sql="select * from Inventory where ID=".$CategoryID;
		$result=odbc_exec($connection, $sql);
		if(odbc_num_rows($result)>0)
		{
			// update query
			$Name=str_replace("_"," ",$Name);
		  $sql="update Inventory set datamode=0,SortOrder=".$SortOrder.",Name='".$Name."',Under=".$Under.",BarCodePrefix='$BarCodePrefix',BarCodeSuffix='$BarCodeSuffix',AdditionalInfo4='0',SequenceTypeID=0,LastBarCodeNo=0 where ID=".$CategoryID;
		  odbc_exec($connection, $sql);
		  generateItemH($degree,$CategoryID,$connection);
		  $res=array('status'=>"ok",'description'=>'Data Updated');
		}
		else
		{
			// insert query 
$Name=str_replace("_"," ",$Name);
  		  $sql="insert into Inventory (ID,DiscountType,HasSupplierWarranty,IsAssembly,HasBatches,HasExpiry,
CommisionType,Isbarcodemanual,MaxPriceLevel,[SortOrder],[Name],[Under],Active,ProductBaseType,Degree,[Group],datamode,BarCodePrefix,BarCodeSuffix,AdditionalInfo4,SequenceTypeID,LastBarCodeNo) values(".$countid.",0,0,0,0,0,0,0,0,".$SortOrder.",'".$Name."',".$Under.",1,'".$ProductBaseType."',".$degree.",1,0,'".$BarCodePrefix."','".$BarCodeSuffix."',0,0,0)";
odbc_exec($connection, $sql);
//if($Under>1)$Degree=2;else $degree=1;
generateItemH($degree,$countid,$connection);
$Prefix=getnamesingle("TablesMaster","Prefix","TableID","69",$connection);				
$LastID=getnamesingle("TablesMaster","LastID","TableID","69",$connection)+1;				
$PLastID=str_pad($LastID,7,"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);
$res=array('status'=>"ok",'description'=>'Data Saved');
		}
		
		
		echo json_encode($res);//implode(",",$res);
	}//end if insert data
	if($_GET['action']=="DeleteProductCategory")
	{
		$CategoryID=$_GET['ID'];
		$sql="update Inventory set active=0,datamode=0 where ID=".$CategoryID;
	odbc_exec($connection, $sql);
		$res=array('status'=>"ok",'description'=>'Data Deleted Sucessfully');
		echo json_encode($res);//implode(",",$res);
	}

}//end if GET action 
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);
	}
	elseif($degree==1)
	{
		$updatesql="update inventory set Hierarchy='{'+convert(varchar,ID)+'}' from Inventory where ID=$COAID";
odbc_exec($connection,$updatesql);
	}
}
?>