<?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']=="GetDashboardStatistics")
	{
		$data=array();
		$sql="select count(*) as 'Items' from inventory where [group]=0 and  productBaseType='Item'";
		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Items']=$row['Items'];
		}
		$sql="select count(*) as 'ItemCategories' from inventory where [group]=1 and  productBaseType='Item'";
		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['ItemCategories']=$row['ItemCategories'];
		}
		$sql="select count(*) as 'Customers' from Accounts where   NatureID=14 and active=1";
		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Customers']=$row['Customers'];
		}
				$sql="select count(*) as 'Suppliers' from Accounts where   NatureID=15 and active=1";
		$result=odbc_exec($connection,$sql);
		while($row=odbc_fetch_array($result))
		{
			$data['Suppliers']=$row['Suppliers'];
		}
	$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 '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'];
		}
		//
		echo json_encode($data);
	}
		$sql="";
	
	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'];
		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  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);
   	}//end action view 
   	if($_GET['action']=="optionview")
	{

	//print_r($_GET);
	$TextBoxName=$_GET['TextBoxName'];
	if(isset($_GET['CategoryID']) && $_GET['CategoryID']!="" && $_GET['CategoryID']>0)
	$searchbox=" and CategoryID=".$_GET['CategoryID'];	
	else
	$searchbox="";	
$data=array();
 $sql="select  
MasterValueID,MasterValue  FROM MASTER_VALUES   where active=1 and  TextBoxName='$TextBoxName' $searchbox";
$filename='cache/'.md5($sql).".txt";
       if (filemtime($filename) < time()-1*3600) 
       {
	$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;
	

  }
  @unlink($filename);
            file_put_contents($filename, serialize(json_encode($data)));
            $data=json_encode($data);
}
else
{
$data=unserialize(file_get_contents($filename));
}
 // print_r($data);
   echo ($data);
   	
	/*
	//print_r($_GET);
	$TextBoxName=$_GET['TextBoxName'];
	if(isset($_GET['CategoryID']) && $_GET['CategoryID']!="" && $_GET['CategoryID']>0)
	$searchbox=" and CategoryID=".$_GET['CategoryID'];	
	else
	$searchbox="";	
$data=array();
 $sql="select  
MasterValueID,MasterValue  FROM MASTER_VALUES   where active=1 and  TextBoxName='$TextBoxName' $searchbox";
	$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);
   echo json_encode($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;
		$CategoryName=str_replace("_"," ",$_POST->CategoryName);
		$CategoryID=$_POST->CategoryID;
		$MasterValue=str_replace("_"," ",$_POST->MasterValue);
		$Code=$_POST->CategoryID;
		// 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 
$MasterValueID=getmaxId("MASTER_VALUES","MasterValueID",$connection)+1;
  	 $sql="insert into MASTER_VALUES (MasterValueID,[MasterValue],[TextBoxName],[CategoryName],CategoryID,Active,Code) values($MasterValueID,'".$MasterValue."','".$TextBoxName."','".$CategoryName."',".$CategoryID.",1,'".$Code."')";

		}
		odbc_exec($connection, $sql)or die(odbc_error().$sql);
		$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);
		$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*3600) 
       {
    $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;

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

	/*
		$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";
    $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]=urlencode($row[$key]);
    	  		$row[$key]=htmlspecialchars($value);
    	}
      $data []=$row;

    }
    echo json_encode($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);
            file_put_contents($filename, serialize(json_encode($data)));
$data=json_encode($data);
	}
	else
	{
$data=unserialize(file_get_contents($filename));
	}

    return ($data);

  
  /*
    $data=array();
    if($where!="")$where=" where ".$where;else $where="";
       $sql="select $id,$name from $table $where";
    $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]=urlencode($row[$key]);
    	    $row[$key]=htmlentities($row[$key]);
    	}
      $data []=$row;

    }

    return json_encode($data);

  */}
?>