<commit_before><?php

/*
|--------------------------------------------------------------------------
| Application Routes
|--------------------------------------------------------------------------
|
| Here is where you can register all of the routes for an application.
| It's a breeze. Simply tell Laravel the URIs it should respond to
| and give it the controller to call when that URI is requested.
|
 */

Route::get('/', 'WelcomeController@index');

Route::get('home', 'HomeController@index');

Route::controllers([
	'auth' => 'Auth\AuthController',
	'password' => '<PASSWORD>',
]);

Route::get('noms', function () {

	if (Input::get('tamPagina') && Input::get('pagina')) {
		return json_encode(DB::select(DB::raw("
			WITH nomReciente AS (SELECT clavenomnorm, max(fecha) AS fecha FROM notasnom  WHERE etiqueta= 'NOM' GROUP BY clavenomnorm),
			notasNOMRecientes AS (SELECT * from nomreciente NATURAL JOIN notasnom)
			SELECT fecha,clavenomnorm,trim(both '-' from (regexp_matches(clavenomnorm,'NOM(?:[^a-z0-9])(\d[a-z0-9\/]*[^a-z0-9])?([a-z][a-z0-9\/]*(?:[^a-z0-9](?:[a-z][a-z0-9\/]*[^a-z0-9]?)?)?)?(\d[a-z0-9\/]*[^a-z0-9])?','gi'))[2]) as comite, titulo from vigencianoms NATURAL LEFT JOIN notasnomrecientes;
			")));
	} else {
		return json_encode(DB::select(DB::raw("
			WITH nomReciente AS (SELECT clavenomnorm, max(fecha) AS fecha FROM notasnom  WHERE etiqueta= 'NOM' GROUP BY clavenomnorm),
			notasNOMRecientes AS (SELECT * from nomreciente NATURAL JOIN notasnom)
			SELECT fecha,clavenomnorm,trim(both '-' from (regexp_matches(clavenomnorm,'NOM(?:[^a-z0-9])(\d[a-z0-9\/]*[^a-z0-9])?([a-z][a-z0-9\/]*(?:[^a-z0-9](?:[a-z][a-z0-9\/]*[^a-z0-9]?)?)?)?(\d[a-z0-9\/]*[^a-z0-9])?','gi'))[2]) as comite, titulo from vigencianoms NATURAL LEFT JOIN notasnomrecientes ;
			")));
	}


});
Route::get('noms/{clave} ', function ($clave) {
	$clave = urldecode($clave);

	$historial = DB::select(DB::raw("
		WITH nomReciente AS (SELECT clavenomnorm, max(fecha) AS fecha FROM notasnom  WHERE etiqueta= 'NOM' GROUP BY clavenomnorm),
		notasNOMRecientes AS (SELECT * from nomreciente NATURAL JOIN notasnom)

		SELECT fecha,clavenomnorm,trim(both '-' from (regexp_matches(clavenomnorm,'NOM(?:[^a-z0-9])(\d[a-z0-9\/]*[^a-z0-9])?([a-z][a-z0-9\/]*(?:[^a-z0-9](?:[a-z][a-z0-9\/]*[^a-z0-9]?)?)?)?(\d[a-z0-9\/]*[^a-z0-9])?','gi'))[2]) as comite, titulo from vigencianoms NATURAL LEFT JOIN notasnomrecientes where clavenomnorm like :clavenomnorm;
		"), array('clavenomnorm' => '%' . substr($clave, 3, -4) . '%'));
/*

		$ramayProducto = DB::select(DB::raw("
		Select rama, producto from vigencianoms where clavenomnorm like :clavenomnorm limit 1;
		"), array('clavenomnorm' => '%' . substr($clave, 3, -4) . '%'));

		$result = new stdClass;
		foreach ($ramayProducto as $row){
			$result->rama = $row->rama;
			$result->producto = $row->producto;
		}

		$result->historial = $historial;
		*/

		return json_encode($historial);

})->where('clave', '(.*)');

Route::get('nom/{clave}', function ($clave) {
	$clave = urldecode($clave);

	$historial = DB::select(DB::raw("SELECT  fecha,cod_nota, clavenomnorm, etiqueta, entity2char(titulo), urlnota AS url
FROM notasnom where clavenomnorm like :clavenomnorm ORDER BY fecha ASC;"),
		array('clavenomnorm' => '%' . substr($clave, 3, -4) . '%'));

		$ramayProducto = DB::select(DB::raw("
		Select rama, producto from vigencianoms where clavenomnorm like :clavenomnorm limit 1;
		"), array('clavenomnorm' => '%' . substr($clave, 3, -4) . '%'));

		$result = new stdClass;
		foreach ($ramayProducto as $row){
			$result->rama = $row->rama;
			$result->producto = $row->producto;
		}

		$result->historial = $historial;

		return json_encode($result);


})->where('clave', '(.*)');

/** Consultas relacionadas a la dependencia **/

Route::get('dependencia/{dependencia?}', function ($dependencia = null) {
	if ($dependencia == null) {
		$sqlQuery = "SELECT DISTINCT secretaria AS dependencia from comite";
	} else {
		$sqlQuery = "WITH detalleDependencia AS (SELECT secretaria AS dependencia, comite, descripcion_comite, reseña_comite from comite WHERE lower(secretaria)=lower('$dependencia')),

		nomReciente AS (SELECT clavenomnorm, max(fecha) AS fecha FROM notasnom  WHERE etiqueta= 'NOM' GROUP BY clavenomnorm),
		notasNOMRecientes AS (SELECT * from nomreciente NATURAL JOIN notasnom),
<commit_msg>Refs #69 @0.1h Dependencia y comite<commit_after><?php

/*
|--------------------------------------------------------------------------
| Application Routes
|--------------------------------------------------------------------------
|
| Here is where you can register all of the routes for an application.
| It's a breeze. Simply tell Laravel the URIs it should respond to
| and give it the controller to call when that URI is requested.
|
 */

Route::get('/', 'WelcomeController@index');

Route::get('home', 'HomeController@index');

Route::controllers([
	'auth' => 'Auth\AuthController',
	'password' => '<PASSWORD>',
]);

Route::get('noms', function () {

	if (Input::get('tamPagina') && Input::get('pagina')) {
		return json_encode(DB::select(DB::raw("
			WITH nomReciente AS (SELECT clavenomnorm, max(fecha) AS fecha FROM notasnom  WHERE etiqueta= 'NOM' GROUP BY clavenomnorm),
			notasNOMRecientes AS (SELECT * from nomreciente NATURAL JOIN notasnom)
			SELECT fecha,clavenomnorm,trim(both '-' from (regexp_matches(clavenomnorm,'NOM(?:[^a-z0-9])(\d[a-z0-9\/]*[^a-z0-9])?([a-z][a-z0-9\/]*(?:[^a-z0-9](?:[a-z][a-z0-9\/]*[^a-z0-9]?)?)?)?(\d[a-z0-9\/]*[^a-z0-9])?','gi'))[2]) as comite, titulo from vigencianoms NATURAL LEFT JOIN notasnomrecientes;
			")));
	} else {
		return json_encode(DB::select(DB::raw("
			WITH nomReciente AS (SELECT clavenomnorm, max(fecha) AS fecha FROM notasnom  WHERE etiqueta= 'NOM' GROUP BY clavenomnorm),
			notasNOMRecientes AS (SELECT * from nomreciente NATURAL JOIN notasnom)
			SELECT fecha,clavenomnorm,trim(both '-' from (regexp_matches(clavenomnorm,'NOM(?:[^a-z0-9])(\d[a-z0-9\/]*[^a-z0-9])?([a-z][a-z0-9\/]*(?:[^a-z0-9](?:[a-z][a-z0-9\/]*[^a-z0-9]?)?)?)?(\d[a-z0-9\/]*[^a-z0-9])?','gi'))[2]) as comite, titulo from vigencianoms NATURAL LEFT JOIN notasnomrecientes ;
			")));
	}


});
Route::get('noms/{clave} ', function ($clave) {
	$clave = urldecode($clave);

	$historial = DB::select(DB::raw("
		WITH nomReciente AS (SELECT clavenomnorm, max(fecha) AS fecha FROM notasnom  WHERE etiqueta= 'NOM' GROUP BY clavenomnorm),
		notasNOMRecientes AS (SELECT * from nomreciente NATURAL JOIN notasnom)

		SELECT fecha,clavenomnorm,trim(both '-' from (regexp_matches(clavenomnorm,'NOM(?:[^a-z0-9])(\d[a-z0-9\/]*[^a-z0-9])?([a-z][a-z0-9\/]*(?:[^a-z0-9](?:[a-z][a-z0-9\/]*[^a-z0-9]?)?)?)?(\d[a-z0-9\/]*[^a-z0-9])?','gi'))[2]) as comite, titulo from vigencianoms NATURAL LEFT JOIN notasnomrecientes where clavenomnorm like :clavenomnorm;
		"), array('clavenomnorm' => '%' . substr($clave, 3, -4) . '%'));
/*

		$ramayProducto = DB::select(DB::raw("
		Select rama, producto from vigencianoms where clavenomnorm like :clavenomnorm limit 1;
		"), array('clavenomnorm' => '%' . substr($clave, 3, -4) . '%'));

		$result = new stdClass;
		foreach ($ramayProducto as $row){
			$result->rama = $row->rama;
			$result->producto = $row->producto;
		}

		$result->historial = $historial;
		*/

		return json_encode($historial);

})->where('clave', '(.*)');

Route::get('nom/{clave}', function ($clave) {
	$clave = urldecode($clave);

	$historial = DB::select(DB::raw("SELECT  fecha,cod_nota, clavenomnorm, etiqueta, entity2char(titulo), urlnota AS url
FROM notasnom where clavenomnorm like :clavenomnorm ORDER BY fecha ASC;"),
		array('clavenomnorm' => '%' . substr($clave, 3, -4) . '%'));

		$ramayProducto = DB::select(DB::raw("
		Select rama, producto from vigencianoms where clavenomnorm like :clavenomnorm limit 1;
		"), array('clavenomnorm' => '%' . substr($clave, 3, -4) . '%'));

		$result = new stdClass;
		foreach ($ramayProducto as $row){
			$result->rama = $row->rama;
			$result->producto = $row->producto;
		}

		$result->historial = $historial;

		$comite = DB::select(DB::raw("SELECT  secretaria, nombre_secretaria, comite, descripcion_comite from comite WHERE :clavenomnorm like ('%'||comite||'%')ORDER BY secretaria, comite ASC;"),
		array('clavenomnorm' => '%' . substr($clave, 3, -4) . '%'));

		$result->comite = $comite;


		return json_encode($result);


})->where('clave', '(.*)');

/** Consultas relacionadas a la dependencia **/

Route::get('dependencia/{dependencia?}', function ($dependencia = null) {
	if ($dependencia == null) {
		$sqlQuery = "SELECT DISTINCT secretaria AS dependencia from comite";
	} else {
		$sqlQuery = "WITH detalleDependencia AS (SELECT secretaria AS dependencia, comite, descripcion_comite, reseña_comite from comite WHERE lower(secretaria)=lower('$dependencia')),

		nomReciente AS (SELECT clavenomnorm, max(fecha) AS fecha FROM notasnom  WHERE etiqueta= 'NOM' GROUP BY clavenomnorm),
		notasNOMRecientes AS (SELECT * from nomreciente NATURAL JOIN notasnom),

{'fraction_non_alphanumeric': 0.2273945077026122, 'fraction_numerical': 0.015851752623353428, 'mean_word_length': 4.172632794457275, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3682525', 'n_tokens_mistral': 3562, 'n_tokens_neox': 3318, 'n_words': 703}