<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('memory_limit', '512M');
date_default_timezone_set('Europe/Madrid');

function mssql_escape($str)
{
    if(get_magic_quotes_gpc())
    {
        $str= stripslashes($str);
    }
    return str_replace("'", "''", $str);
}
$serverName = "localhost";
$connectionInfo = array( "Database"=>"ICF", "UID"=>"DMR", "PWD"=>"DMR");
$connectionInfoMaster = array( "Database"=>"Master", "UID"=>"DMR", "PWD"=>"DMR");

$connNavision=false;
$connNavisionMaster=false;
$retries=0;
while(!$connNavisionMaster && $retries<3){
	//$connNavision = sqlsrv_connect( $serverName, $connectionInfo);
	$connNavisionMaster = sqlsrv_connect( $serverName, $connectionInfoMaster);
	$retries++;
}
	if( $connNavisionMaster ) {
		// echo "Conexion establecida con Navision.\r\n";
	}else{
		// echo "Conexion no se pudo establecer con Navision.\r\n";
		//	 die( print_r( sqlsrv_errors(), true));
		die(json_encode(["result"=>-1,"mensaje"=>"Error, no se han podido liberar licencias en Navision"]));
	}
	
	$sql = "SELECT COUNT(*) actives FROM sys.dm_exec_sessions WHERE database_id  = db_id('ICF') AND program_name='Microsoft Dynamics NAV Classic client';";
	
	$stmt = sqlsrv_query( $connNavisionMaster, $sql );
	if( $stmt === false) {die(json_encode(["result"=>-1,"mensaje"=>"Error, no se han podido liberar licencias en Navision"]));	}
	if(!($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))) { die(json_encode(["result"=>-1,"mensaje"=>"Error, no se han podido liberar licencias en Navision"])); }
	$actives=$row['actives'];
	$killcount=$actives-26;
	if($killcount<=0) die(json_encode(["result"=>0,"mensaje"=>"Licencia liberada en Navision"]));
	$sql = "SELECT TOP ".$killcount." * FROM sys.dm_exec_sessions WHERE database_id  = db_id('ICF') AND status='sleeping' AND login_name NOT IN ('SRV2K8\Administrador') AND program_name='Microsoft Dynamics NAV Classic client' AND login_name NOT IN ('JRZ', 'HLL', 'JCMS', 'CCM', 'CAJA', 'MOS1', 'MOS2', 'MOS3') ORDER BY last_request_end_time ASC;";
	$stmt = sqlsrv_query( $connNavisionMaster, $sql );
	if( $stmt === false) {
		die(json_encode(["result"=>-1,"mensaje"=>"Error, no se han podido liberar licencias en Navision"]));
	}
		
	$kill=0;
	while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
		$sql = "KILL ".$row['session_id'].";";
		$stmt_temp = sqlsrv_query( $connNavisionMaster, $sql );
		$kill++;
	}
	if($kill>0) die(json_encode(["result"=>$kill, "mensaje"=>"Licencia liberada en Navision"]));
	
	die(json_encode(["result"=>-1,"mensaje"=>"Error, no se han podido liberar licencias en Navision"]));

?>
