<?php include_once('database_connection.php');
if(!(isset($_SESSION['userid'])))
{
   $res=array('status'=>"Error",'description'=>'Session Expired , Please Login  First ');
   echo json_encode($res);
   return;
}
else
{
  $Userid=$_SESSION['userid'];
}
if(isset($_GET['action']))
{
	if($_GET['action']=="getCategorySize")
	{
		$data=array();
		$CategoryID=$_GET['CategoryID'];
		$sql="select MasterValueID,MasterValue,CategoryName from MASTER_VALUES where CategoryID=".$CategoryID;
		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data[]=$row;
		}
		echo json_encode($data);
	}
	if($_GET['action']=="maincategory")
  {

    if(isset($_GET['Under']))
    $Under="Under=".$_GET['Under']." and";
    else
    $Under="";
    /*if(isset($_GET['CompanyID']))
    $CompanyID="CompanyID=".$_GET['CompanyID']." and";
    else
    $CompanyID="";*/
    //$Size=$_GET['Size'];
    $ProductBaseType=$_GET['BaseType'];
    $condition=$_GET['condition'];
    if(isset($_GET['term']))
    {
      $term=" and name like '%".$_GET['term']."%'";
    }
    else
    {
      $term="";
    }
    $where=$Under.""." ProductBaseType='".$ProductBaseType."' and ".$condition;
    $table=$_GET['TextBoxName'];
    $fieldname=$_GET['fieldname'];
    $fieldid=$_GET['fieldid'];
    echo getSelect2List2($table,$fieldid,$fieldname,$where,$connection);
  
  }

	if($_GET['action']=="GetDashboardStatistics")
	{
		$data=array();
		$StartDate=Date("Y-m-d");
		$EndDate=$StartDate;
		$sql="select count(*) as 'Items' from inventory where [group]=0 and  productBaseType='Property'";
$filename='cache/'.md5($sql).".txt";
       if (filemtime($filename) < time()-1*30) 
       {		
		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Items']=$row['Items'];
		}
		$sql="select count(*) as TotalCheckOut from InvoiceDetail where invoiceid in (select InvoiceID from InvoiceMaster where  InvoiceType=5 and Duedate=convert(date,'$StartDate') ) and (itemid in (select ID from inventory where    ProductBaseType='Property' and active=1)) ";

		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['TotalCheckOut']=$row['TotalCheckOut'];
		}
		$sql="select (select count(*) from inventory where [group]=0 and active=1 and ProductBaseType='Property') as TotalRooms,count(qty) as Reserved from InvoiceDetail where ((InvoiceDate>=convert(date,'$StartDate') and  Duedate<=convert(date,'$EndDate')) or (DueDate>=convert(date,'$StartDate') and InvoiceDate<=convert(date,'$EndDate'))) and (itemid in (select ID from inventory where     ProductBaseType='Property'))";

		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Reserved']=$row['Reserved'];
			$data['TotalRooms']=$row['TotalRooms'];
			$data['RoomsAvailable']=($row['TotalRooms']-$row['Reserved']);
		}
				$sql="select count(qty) as TotalCheckIn from InvoiceDetail where invoiceid in (select InvoiceID from InvoiceMaster where  InvoiceType=5 and InvoiceDate=convert(date,'$StartDate') ) and (itemid in (select ID from inventory where    ProductBaseType='Property' and active=1))";

		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['TotalCheckIn']=$row['TotalCheckIn'];
		}
	$sql="select count(*) as 'Users' from UserAccounts where   active=1";

		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Users1']=$row['Users'];
		}
		$sql="select count(*) as Walkin from InvoiceMaster where  InvoiceType=5 and active=1 and ((InvoiceDate>=convert(date,'$StartDate') and  Duedate<=convert(date,'$EndDate')) or (DueDate>=convert(date,'$StartDate') and InvoiceDate<=convert(date,'$EndDate'))) and SupplierID=1899";
		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Walkin']=$row['Walkin'];
		}

	$sql="select count(*) as 'Employees' from SalesManInformation where   active=1";

		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Employees']=$row['Employees'];
		}
		$sql="select count(*) as 'Locations' from godowns  where degree=1 and  active=1";

		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Locations']=$row['Locations'];
		}
				$sql="select count(*) as 'Departments' from godowns  where degree=2 and  active=1";

		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Departments']=$row['Departments'];
		}
		//
		@unlink($filename);
		$data=json_encode($data);
            file_put_contents($filename, serialize($data));

		}
	else
	{
		$data=unserialize(file_get_contents($filename));	

	}
		echo ($data);
	}
		
	
	if($_GET['action']=="viewaccounts")
	{

	//print_r($_GET);
	$TextBoxName=$_GET['TextBoxName'];
		if(isset($_GET['searchtext']))
		{
		$searchbox=$_GET['searchtext'];
		if($searchbox=="")
		{
		 $sql="select   ROW_NUMBER() OVER (
      PARTITION BY [TextBoxName]
      ORDER BY [TextBoxName]
   ) row_num,
MasterValue,CategoryName,MasterValueID,CategoryID,Code  FROM MASTER_VALUES   where TextBoxName='$TextBoxName' and active=1";
	
		}
		 else
		 {
			 $sql="select   ROW_NUMBER() OVER (
      PARTITION BY [TextBoxName]
      ORDER BY [TextBoxName]
   ) row_num,
MasterValue,CategoryName,MasterValueID  FROM MASTER_VALUES   where TextBoxName='$TextBoxName' and active=1 and MasterValue like '".$searchbox."%'";

		 }
		}
		else
		{
			 $sql="select   ROW_NUMBER() OVER (
      PARTITION BY [TextBoxName]
      ORDER BY [TextBoxName]
   ) row_num,
MasterValue,CategoryName,MasterValueID  FROM MASTER_VALUES   where TextBoxName='$TextBoxName' and active=1";

		}
$data=array();
	$result=odbc_exec($connection, $sql);
	//'.$row["MasterValueId"].'
  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:editRow(this,'.$row["MasterValueID"].');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteRow(this,'.$row["MasterValueID"].');"></i>';
    $data []=$row;
  }
 // print_r($data);
   echo json_encode($data);
   	
	}
	if($_GET['action']=="view")
	{
	//print_r($_GET);
	 $TextBoxName=$_GET['TextBoxName'];
	// echo "name of file=".$TextBoxName+'_cache.txt';
/*	if($TextBoxName!="")
	$filename="view_".$TextBoxName.'_cache.txt';	
	else
	$filename='view_cache.txt';	
	if (filemtime($filename) < time()-1*1) 
	{*/
		if(isset($_GET['searchtext']))
		{
			$searchbox=$_GET['searchtext'];
			
		$searchbox=$_GET['searchtext'];
		if($searchbox=="")
		{
		 $sql="select   ROW_NUMBER() OVER (
      PARTITION BY [TextBoxName]
      ORDER BY [TextBoxName]
   ) row_num,
MasterValue,CategoryName,MasterValueID,CategoryID  FROM MASTER_VALUES   where TextBoxName='$TextBoxName' and active=1";
	
		}
		 else
		 {
			 $sql="select   ROW_NUMBER() OVER (
      PARTITION BY [TextBoxName]
      ORDER BY [TextBoxName]
   ) row_num,
MasterValue,CategoryName,MasterValueID  FROM MASTER_VALUES   where TextBoxName='$TextBoxName' and active=1 and MasterValue like '".$searchbox."%'";

		 }
		}
		else
		{
			 $sql="select   ROW_NUMBER() OVER (
      PARTITION BY [TextBoxName]
      ORDER BY [TextBoxName]
   ) row_num,
MasterValue,CategoryName,MasterValueID,  FROM MASTER_VALUES   where TextBoxName='$TextBoxName' and active=1";

		}
$data=array();
 $filename='cache/'.md5($sql).".txt";
       if (filemtime($filename) < time()-1*1) 
       {
	$result=odbc_exec($connection, $sql);
	//'.$row["MasterValueId"].'
  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:editRow(this,'.$row["MasterValueID"].');"></i>&nbsp;&nbsp;&nbsp;  <i class="fa fa-trash-o fa-lg" style="text-align: right;color: red;cursor:pointer;" onclick="javascript:deleteRow(this,'.$row["MasterValueID"].');"></i>';
    $data []=$row;

  }
  @unlink($filename);
  $data=json_encode($data);
            file_put_contents($filename, serialize($data));

}
else
{
		$data=unserialize(file_get_contents($filename));	

}
 // print_r($data);
   echo ($data);
   	}//end action view 
   	if($_GET['action']=="optionview")
	{
	//print_r($_GET);
	$TextBoxName=$_GET['TextBoxName'];
	//$filename="optionview_".trim($TextBoxName).'_cache.txt';

	
	if(isset($_GET['CategoryID']) && $_GET['CategoryID']!="" && $_GET['CategoryID']>0)
	$searchbox=" and CategoryID=".$_GET['CategoryID'];	
	else
	$searchbox="";	
$data=array();
 $sql="select  ROW_NUMBER() OVER (
      PARTITION BY [TextBoxName]
      ORDER BY [TextBoxName]
   ) row_num,
MasterValueID,MasterValue,CategoryName,CategoryID,'' as col_Action  FROM MASTER_VALUES   where active=1 and  TextBoxName='$TextBoxName' $searchbox";
 $filename='cache/'.md5($sql).".txt";
       if (filemtime($filename) < time()-1*1) 
       {
	$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);
  		$row[$key]=htmlspecialchars($value);
  	}
    $data []=$row;

  }
 // print_r($data);
  @unlink($filename);
  $data=json_encode($data);
          file_put_contents($filename, serialize($data));

	}
	else
	{
	$data=unserialize(file_get_contents($filename));	
	}
   echo ($data);
   	}//end action view 
	if($_GET['action']=="InsertMasterValue")
	{

		/*$data=json_encode($_POST);
		$_POST=$data;
*/		$data=array_keys($_POST);
		$d=json_decode($data[0]);
		//print_r($d);
		$_POST=$d;
		if(isset($_POST->MasterValueID) && $_POST->MasterValueID!="")
		{

		$MasterValueID=$_POST->MasterValueID;
		}
			else
		{		
			$MasterValueID=0;
		}
		$TextBoxName=str_replace("_"," ",$_POST->TextBoxName);
		
		$MasterValue=$_POST->MasterValue;
		if(isset($_POST->CategoryName) && $_POST->CategoryName!="")
		$CategoryName=str_replace("_"," ",$_POST->CategoryName);
			else
				$CategoryName='';

		if(isset($_POST->CategoryID))
		$CategoryID=$_POST->CategoryID;
		else
		$CategoryID=0;

		$MasterValue=str_replace("_"," ",$_POST->MasterValue);
		$Code=isset($_POST->CategoryID)?$_POST->CategoryID:0;
		// check if the record already exits 
		 $sql="select * from MASTER_VALUES where MasterValueID=".$MasterValueID;
		$result=odbc_exec($connection, $sql) or die(odbc_error().$sql);
		if(odbc_num_rows($result)>0)
		{
			// update query
		   $sql="update MASTER_VALUES set MasterValue='".$MasterValue."',CategoryName='$CategoryName',CategoryID=$CategoryID,Code='".$Code."' where MasterValueID=".$MasterValueID;
		}
		else
		{
			// insert query 

  	 $sql="insert into MASTER_VALUES ([MasterValue],[TextBoxName],[CategoryName],CategoryID,Active,Code) values('".$MasterValue."','".$TextBoxName."','".$CategoryName."',".$CategoryID.",1,'".$Code."')";

		}
		odbc_exec($connection, $sql)or die(odbc_error().$sql);
		$filename="optionview_".trim($TextBoxName).'_cache.txt';
		@unlink($filename);
		$filename="view_".trim($TextBoxName).'_cache.txt';
		@unlink($filename);


		$res=array('status'=>"ok",'description'=>'Data Saved');
		echo json_encode($res);//implode(",",$res);
	}// end action insertmastervalue 
	// code for delete 
	if($_GET['action']=="DeleteMasterValue")
	{
	//	print_r($_GET);
		//	$data=array_keys($_POST);
		//$d=json_decode($data[0]);
		//print_r($d);
		//$_POST=$d;
		$TextBoxName=$_GET['TextBoxName'];
		$MasterValueID=$_GET['MasterValueID'];
		$sqldelete="update MASTER_VALUES set active=0 where MasterValueID=".$MasterValueID." and TextBoxName='$TextBoxName'";
			odbc_exec($connection, $sqldelete);
					$filename="optionview_".trim($TextBoxName).'_cache.txt';
		@unlink($filename);
		$filename="view_".trim($TextBoxName).'_cache.txt';
		@unlink($filename);

		$res=array('status'=>"ok",'description'=>'Data Deleted Successfully');
		echo json_encode($res);//implode(",",$res);
	}//end delete MAster Value
   	if($_GET['action']=="optionselect")
	{
		$table=$_GET['TextBoxName'];
		$id=$_GET['fieldid'];
		$name=$_GET['fieldname'];
		$where=$_GET['condition'];
		echo getSelect2List($table,$id,$name,$where,$connection);
	}
	if($_GET['action']=="viewlist")
	{
		$table=$_GET['TextBoxName'];
		$fields=$_GET['fields'];
		//$name=$_GET['fieldname'];
		$where=$_GET['condition'];
 $data=array();
    if($where!="")$where=" where ".$where;else $where="";
     $sql="select $fields from $table $where";
       $filename='cache/'.md5($sql).".txt";
       if (filemtime($filename) < time()-1*1) 
	{
    $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(str_replace("-"," ",$value));
    	  		//$row[$key]=htmlspecialchars($value);
    	}
      $data []=$row;

    }
     @unlink($filename);
     $data=json_encode($data);
          file_put_contents($filename, serialize($data));
	}
	else
	{
					$data=unserialize(file_get_contents($filename));	

	}
    echo ($data);
	}	

}//end if action
function getSelect2List($table,$id,$name,$where='1=1',$connection)
  {
    $data=array();
    if($where!="")$where=" where ".$where;else $where="";
       $sql="select $id,$name from $table $where";
       $filename='cache/'.md5($sql).".txt";
       if (filemtime($filename) < time()-1*1) 
	{
    $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);
    		$row[$key]=htmlspecialchars($value);
    	  //  $row[$key]=htmlentities($value);
    	}
      $data []=$row;

    }
     @unlink($filename);
          $data=json_encode($data);
          file_put_contents($filename, serialize($data));
	}
	else
	{
			$data=unserialize(file_get_contents($filename));	

	}

    return $data;

  }
?>