<?php

include_once "lib/common.php";

header('Content-Type: application/json');

/***	Parametros	***/
 
$user_id = $_REQUEST["user_id"];
$route_id = $_REQUEST["route_id"];
$rol_id = $_REQUEST["rol_id"];



/***	Resultado de datos generales	***/
$objs = array();

/***	Marcas	***/

$q = "SELECT DISTINCT * FROM brands ORDER BY brands.name;";
$objs = getDataFromQuery($objs, "brands", $q);

/***	Fin de Marcas	***/


/***	Clusters	***/

$q = "SELECT DISTINCT * FROM clusters ORDER BY name;";
$objs = getDataFromQuery($objs, "clusters", $q);

/***	Fin de Clusters	***/


/***	Justificaciones	***/
$objs['justifications'] = array();
$query = mysql_query("SELECT DISTINCT * FROM justifications WHERE deleted_at IS NULL AND id NOT IN (3, 4, 5, 7) ORDER BY justifications.name;");
while($r = mysql_fetch_array($query)) {	
	$objs['justifications'][] = array(
		"id"	=>		utf8_encode($r["id"]),
		"name"	=>		utf8_encode($r["name"]),
	);
}
/***	Fin de Justificaciones	***/


/***	Exhibidores de PDVs	***/

$q = "
		select exhibitor_nodes.*, exhibitors.name from exhibitor_nodes
		inner join exhibitors on exhibitors.id = exhibitor_nodes.exhibitor_id
		inner join nodes on nodes.id = exhibitor_nodes.node_id
		inner join route_nodes on route_nodes.node_id = nodes.id
		inner join routes on routes.id = route_nodes.route_id
		where routes.id = $route_id
			and exhibitor_nodes.deleted_at is null
		group by exhibitor_nodes.node_id, exhibitor_nodes.exhibitor_id, exhibitor_nodes.state_id
		order by exhibitor_nodes.updated_at desc;
	";
$objs = getDataFromQuery($objs, "exhibitor_nodes", $q);

/***	Fin de Exhibidores de PDVs	***/


/***	Exhibidores Estados	***/

$q = "select * from exhibitor_states where id in (1,2);";
$objs = getDataFromQuery($objs, "exhibitor_states", $q);

/***	Fin de Exhibidores Estados	***/	


/***	Exhibidores	***/

$q = "select * from exhibitors where deleted_at is null;";
$objs = getDataFromQuery($objs, "exhibitors", $q);

/***	Fin de Exhibidores	***/


/***	Formularios	***/

$q = "SELECT DISTINCT
			forms.id,
			forms.name,
			forms.type_id,
			forms.ordering

		FROM forms
		
		INNER JOIN node_forms ON (node_forms.form_id = forms.id)
		INNER JOIN route_nodes ON (route_nodes.node_id = node_forms.node_id)
		INNER JOIN form_rols ON (form_rols.form_id = forms.id)
		INNER JOIN form_planification ON (form_planification.form_id = forms.id)

		WHERE deleted_at is null
			AND route_nodes.route_id = $route_id
			AND form_rols.rol_id = $rol_id
			AND date_format(form_planification.date, '%Y-%m-%d') = date_format(NOW(), '%Y-%m-%d')

		ORDER BY name ASC";
$objs = getDataFromQuery($objs, "forms", $q);

/***	Fin de Formularios	***/


/***	Valores de Formularios	***/

$q = "select distinct fcv.* from form_custom_values fcv
		inner join forms f on f.id = fcv.form_id
		inner join node_forms nf on nf.form_id = f.id
		inner join route_nodes rn on rn.node_id = nf.node_id
		where rn.route_id = $route_id";
$objs = getDataFromQuery($objs, "custom_values", $q);

/***	Fin de Valores de Formularios	***/


/***	Tags de Formularios	***/

$q = "SELECT DISTINCT
			forms_tags.id as form_tag_id,
			forms.id as form_id,
			tags.id AS tag_id,
			if(forms_tags.max_value is null, 0, forms_tags.max_value) as maxval,
			if(forms_tags.min_value is null, 0, forms_tags.min_value) as minval,
			forms_tags.type_id as type_id,
			forms_tags.ordering as ordering
		FROM `forms`
		INNER JOIN `forms_tags` ON (`forms_tags`.`form_id` = `forms`.`id`)
		INNER JOIN `tags` ON (`tags`.`id` = `forms_tags`.`tag_id`)
		INNER JOIN `subcategories` ON (`subcategories`.`id` = `tags`.`subcategory_id`)
		INNER JOIN `categories` ON (`categories`.`id` = `subcategories`.`category_id`)
		INNER JOIN node_forms ON (node_forms.form_id = forms.id)
		INNER JOIN route_nodes ON (route_nodes.node_id = node_forms.node_id)
		INNER JOIN form_rols ON (form_rols.form_id = forms.id)
		INNER JOIN form_planification ON (form_planification.form_id = forms.id)
		WHERE `forms`.`deleted_at` is null 
			and `tags`.`deleted_at` is null 
			and `categories`.`deleted_at` is null 
			and `subcategories`.`deleted_at` is null 
			and route_nodes.route_id = $route_id
			and form_rols.rol_id = $rol_id
			and date_format(form_planification.date, '%Y-%m-%d') = date_format(NOW(), '%Y-%m-%d')
		ORDER BY `forms`.`ordering` ASC;";
$objs = getDataFromQuery($objs, "forms_tags", $q);

/***	Fin de Tags de Formularios	***/


/***	Puntos de Venta	***/

$q = "
		select distinct
			nodes.id as id,
			nodes.address as direccion,
			cities.name as localidad,
			$route_id as id_ruta,
			nodes.lat as lat,
			nodes.lng as lng,
			nodes.channel_id as id_canal,
			nodes.chain_id as id_cadena,
			chains.name as cadena,
			nodes.name as nombre,
			(
				select count(*) 
				from checkouts
				where date_format(checkouts.visited_at, '%Y-%m-%d') = date_format(NOW(), '%Y-%m-%d')
					and checkouts.node_id = nodes.id
					and checkouts.user_id = $user_id
			) as completed,
			nodes.manager as manager,
			nodes.comercial_name,
			nodes.phone as phone,
			nodes.mail as mail,
			nodes.supplier_id as id_supplier,
			nodes.cluster_id as id_cluster,
			nodes.sellers as sellers,
			(select concat('(', date_format(ch.visited_at, '%d-%m-%Y %h:%i:%s'), ') ', u.name, ' ', u.lastname) from checkouts ch inner join users u on u.id = ch.user_id inner join nodes n on n.id = ch.node_id where u.rol_id = 4 and  n.id = nodes.id order by ch.visited_at desc limit 1) as merch_last_visit,
			(select concat('(', date_format(ch.visited_at, '%d-%m-%Y %h:%i:%s'), ') ', u.name, ' ', u.lastname) from checkouts ch inner join users u on u.id = ch.user_id inner join nodes n on n.id = ch.node_id where u.rol_id = 3 and  n.id = nodes.id order by ch.visited_at desc limit 1) as tl_last_visit,
			(select concat('(', date_format(ch.visited_at, '%d-%m-%Y %h:%i:%s'), ') ', u.name, ' ', u.lastname) from checkouts ch inner join users u on u.id = ch.user_id inner join nodes n on n.id = ch.node_id where u.rol_id = 6 and  n.id = nodes.id order by ch.visited_at desc limit 1) as opv_last_visit
		from nodes
		inner join cities on cities.id = nodes.city_id
		inner join chains on chains.id = nodes.chain_id
		inner join route_nodes on nodes.id = route_nodes.node_id
		inner join route_users ru on route_nodes.route_id = ru.route_id
		where route_nodes.route_id = $route_id
			and ru.user_id = $user_id
		having completed = 0
		order by nodes.name asc;
	";
$objs = getDataFromQuery($objs, "nodes", $q);

/***	Fin de Puntos de Venta	***/


/***	Formularios de Puntos de Venta	***/

$objs['nodes_forms'] = array();
$q = "SELECT DISTINCT
			node_forms.form_id,
			node_forms.node_id,
			forms.condition_id,
			(
				SELECT ch.visited_at 
				FROM checkouts ch
				INNER JOIN checkout_data cd ON cd.checkout_id = ch.id
				WHERE ch.node_id = node_forms.node_id
					AND ch.user_id = $user_id
					AND cd.form_id = forms.id
				ORDER BY ch.visited_at DESC
				LIMIT 1
			) as last_visited_at
		FROM `forms`
		INNER JOIN `forms_tags` ON (`forms_tags`.`form_id` = `forms`.`id`)
		INNER JOIN `tags` ON (`tags`.`id` = `forms_tags`.`tag_id`)
		INNER JOIN `subcategories` ON (`subcategories`.`id` = `tags`.`subcategory_id`)
		INNER JOIN `categories` ON (`categories`.`id` = `subcategories`.`category_id`)
		INNER JOIN node_forms ON (node_forms.form_id = forms.id)
		INNER JOIN route_nodes ON (route_nodes.node_id = node_forms.node_id)
		INNER JOIN form_rols ON (form_rols.form_id = forms.id)
		INNER JOIN form_planification ON (form_planification.form_id = forms.id)
		WHERE `forms`.`deleted_at` is null 
			and `tags`.`deleted_at` is null 
			and `categories`.`deleted_at` is null 
			and `subcategories`.`deleted_at` is null 
			and route_nodes.route_id = $route_id
			and form_rols.rol_id = $rol_id
			and date_format(form_planification.date, '%Y-%m-%d') = date_format(NOW(), '%Y-%m-%d')
		ORDER BY `forms`.`ordering` ASC;";
$objs = getNodesFormsFromQuery($objs, $q);
//$objs = getDataFromQuery($objs, "nodes_forms", $q);

/***	Fin de Formularios de Puntos de Venta	***/


/***	Inactivos	***/

$q = "select node_inactive_tags.node_id, node_inactive_tags.tag_id from node_inactive_tags 
		inner join nodes on nodes.id = node_inactive_tags.node_id
		inner join route_nodes on route_nodes.node_id = nodes.id
		inner join routes on routes.id = route_nodes.route_id
		where routes.id = $route_id";
$objs = getDataFromQuery($objs, "tags_inactives", $q);

/***	Fin de Inactivos	***/


/***	Objetivos	***/

$q = "select distinct
			node_targets.id,
			node_targets.name,
			node_targets.node_id
		from node_targets
		inner join nodes on nodes.id = node_targets.node_id
		inner join route_nodes on route_nodes.node_id = nodes.id
		inner join routes on routes.id = route_nodes.route_id
		where routes.id = $route_id and node_targets.completed is null";
$objs = getDataFromQuery($objs, "nodes_targets", $q);

/***	Fin de Objetivos	***/


/***	Proveedores	***/

$q = "select * from suppliers";
$objs = getDataFromQuery($objs, "suppliers", $q);

/***	Fin de Proveedores	***/


/***	Proveedores de Tags	***/

$q = "select 
			s.id as supplier_id,
			t.id as tag_id,
			s.name as name
		from suppliers_tags st
		inner join tags t on t.id = st.tag_id
		inner join suppliers s on s.id = st.supplier_id";
$objs = getDataFromQuery($objs, "suppliers_tags", $q);

/***	Fin de Proveedores de Tags	***/


/***	Tags	***/

$q = "SELECT DISTINCT
			tags.id as tag_id,
			tags.name as tag,
			subcategories.name as subcategory,
			categories.name as category
		FROM `forms`
		INNER JOIN `forms_tags` ON (`forms_tags`.`form_id` = `forms`.`id`)
		INNER JOIN `tags` ON (`tags`.`id` = `forms_tags`.`tag_id`)
		INNER JOIN `subcategories` ON (`subcategories`.`id` = `tags`.`subcategory_id`)
		INNER JOIN `categories` ON (`categories`.`id` = `subcategories`.`category_id`)
		INNER JOIN node_forms ON (node_forms.form_id = forms.id)
		INNER JOIN route_nodes ON (route_nodes.node_id = node_forms.node_id)
		INNER JOIN form_rols ON (form_rols.form_id = forms.id)
		INNER JOIN form_planification ON (form_planification.form_id = forms.id)
		WHERE `forms`.`deleted_at` is null 
			and `tags`.`deleted_at` is null 
			and `categories`.`deleted_at` is null 
			and `subcategories`.`deleted_at` is null 
			and route_nodes.route_id = $route_id
			and form_rols.rol_id = $rol_id
			and date_format(form_planification.date, '%Y-%m-%d') = date_format(NOW(), '%Y-%m-%d')
		ORDER BY `forms`.`ordering` ASC;";
$objs = getDataFromQuery($objs, "tags", $q);

/***	Fin de Tags	***/
	

/***	Resultado del procesamiento codificado a JSON	***/
echo json_encode($objs);