<?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  ***/
$objs['brands'] = array();
$query = mysql_query("SELECT id, name from brands");
while($r = mysql_fetch_array($query)) { 
    $objs['brands'][] = array(
        "id"    =>      utf8_encode($r["id"]),
        "name"  =>      utf8_encode($r["name"]),
    );
}
/***    Fin de Marcas   ***/



/***    Cadenas ***/
$objs['chains'] = array();
$query = mysql_query("SELECT DISTINCT * FROM chains ORDER BY chains.name;");
while($r = mysql_fetch_array($query)) { 
    $objs['chains'] = array(
        "id"    =>      utf8_encode($r["id"]),
        "name"  =>      utf8_encode($r["name"]),
    );
}
/***    Fin de Cadenas  ***/




/***    Clusters    ***/
$objs['clusters'] = array();
$query = mysql_query("SELECT DISTINCT * FROM clusters ORDER BY clusters.name;");
while($r = mysql_fetch_array($query)) { 
    $objs['clusters'][] = array(
        "id"    =>      utf8_encode($r["id"]),
        "name"  =>      utf8_encode($r["name"]),
    );
}
/***    Fin de Clusters ***/




/***    Justificaciones ***/
$objs['justifications'] = array();
$query = mysql_query("SELECT DISTINCT * FROM justifications WHERE deleted_at IS NULL AND code NOT IN (8,9,10,12) ORDER BY justifications.name;");
while($r = mysql_fetch_array($query)) { 
    $objs['justifications'][] = array(
        "id"    =>      utf8_encode($r["code"]),
        "name"  =>      utf8_encode($r["name"]),
    );
}
/***    Fin de Justificaciones  ***/




/***    Exhibidores de PDVs ***/
$objs['exhibitor_nodes'] = array();
$query = mysql_query("
        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
        group by node_id, exhibitor_id
        order by updated_at desc, created_at desc
        ;");

while($r = mysql_fetch_array($query)) {
    $objs['exhibitor_nodes'][] = array(
        "node_id"       =>      utf8_encode($r["node_id"]),
        "exhibitor_id"  =>      utf8_encode($r["exhibitor_id"]),
        "name"          =>      utf8_encode($r["name"]),
        "state_id"      =>      utf8_encode($r["state_id"]),
        "value"         =>      utf8_encode($r["value"]),
        "updated_by"    =>      utf8_encode($r["updated_by"]),
        "updated_at"    =>      utf8_encode($r["updated_at"]),
        "created_at"    =>      utf8_encode($r["created_at"])
    );
}
/***    Fin de Exhibidores de PDVs  ***/




/***    Exhibidores Estados ***/
$objs['exhibitor_states'] = array();
$query = mysql_query("SELECT * from exhibitor_states;");
while($r = mysql_fetch_array($query)) { 
    $arr = array();
    foreach($r as $k => $obj) {
        $arr[$k] = utf8_encode($obj);
    }
    $objs['exhibitor_states'][] = $arr;
}
/***    Fin de Exhibidores Estados  ***/    




/***    Exhibidores ***/
$objs['exhibitors'] = array();
$query = mysql_query("SELECT id, name, channel_id, created_at from exhibitors where deleted_at is null;");
while($r = mysql_fetch_array($query)) {
    $objs['exhibitors'][] = array(
        "id"    =>  $r["id"],
        "name"  =>  utf8_encode($r["name"]),
        "channel_id"    =>  $r["channel_id"],
        "created_at"    =>  $r["created_at"]
    );
}
/***    Fin de Exhibidores  ***/


$supervisorRepoIDs = array(3191, 3202, 3203, 3204, 3205, 3206, 3207, 3214, 3215, 3216, 3217, 
    3218, 3219, 3220, 3221, 3222, 3223, 3224, 3272);
$isSupervisorRepo = in_array($user_id, $supervisorRepoIDs);


/***    Formularios ***/
$objs['forms'] = array();
$query = mysql_query("
        SELECT DISTINCT
            forms.id,
            forms.name,
            forms.type_id,
            forms.mandatory,
            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
            " . (!$isSupervisorRepo ? 
                "AND date_format(form_planification.date, '%Y-%m-%d') = CURDATE()"
                : "AND date_format(form_planification.date, '%Y-%m-%d') BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()"
            ) . "
        ORDER BY name ASC");
while($r = mysql_fetch_array($query)) {
    $objs['forms'][] = array(
        "id"        =>      utf8_encode($r["id"]),
        "name"      =>      utf8_encode($r["name"]),
        "type_id"   =>      utf8_encode($r["type_id"]),
        "mandatory" =>      utf8_encode($r["mandatory"]),
        "ordering"  =>      utf8_encode($r["ordering"])
    );
}
/***    Fin de Formularios  ***/



/***    Valores de Formularios  ***/
$objs['custom_values'] = array();
$query = mysql_query("
        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");
while($r = mysql_fetch_array($query)) {
    $objs['custom_values'][] = array(
        "id"        =>      utf8_encode($r["id"]),
        "name"      =>      utf8_encode($r["name"]),
        "form_id"   =>      utf8_encode($r["form_id"]),
        "forms_tags_id"   =>      utf8_encode($r["forms_tags_id"])
    );
}
/***    Fin de Valores de Formularios   ***/




/***    Tags de Formularios ***/
$objs['forms_tags'] = array();
$query = mysql_query("SELECT DISTINCT
            forms_tags.id as id,
            forms.id as form_id,
            tags.id AS tag_id,
            forms_tags.max_value as maxval,
            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
            " . (!$isSupervisorRepo ? 
                "AND date_format(form_planification.date, '%Y-%m-%d') = CURDATE()"
                : "AND date_format(form_planification.date, '%Y-%m-%d') BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()"
            ) . "
        GROUP BY forms.id, tags.id
        ORDER BY `forms_tags`.`ordering` ASC;");
while($r = mysql_fetch_array($query)) {
    $objs['forms_tags'][] = array(
        "id"            =>      utf8_encode($r["id"]),
        "form_id"       =>      utf8_encode($r["form_id"]),
        "tag_id"        =>      utf8_encode($r["tag_id"]),
        "maxval"        =>      utf8_encode($r["maxval"]),
        "minval"        =>      utf8_encode($r["minval"]),
        "type_id"       =>      utf8_encode($r["type_id"]),
        "ordering"      =>      utf8_encode($r["ordering"])
    );
}
/***    Fin de Tags de Formularios  ***/




/***    Puntos de Venta ***/
$objs['nodes'] = array();
$query = mysql_query("
        SELECT distinct
            nodes.id as id,
            nodes.address as direccion,
            cities.name as localidad,
            $route_id as id_ruta,
            nodes.lat,
            nodes.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') = curdate()
                    and checkouts.node_id = nodes.id
                    and checkouts.route_id = $route_id " .
                    ($user_id > 0 ? " 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,
            nodes.cluster_id,
            nodes.branches,
            nodes.origin,
            nodes.boxes,
            nodes.location,
            nodes.m2_salon,
            nodes.m2_salon_category,
            nodes.m2_deposit,
            nodes.modules,
            nodes.has_sunblind,
            nodes.brand_sunblind,
            nodes.has_awning,
            nodes.brand_awning,
            nodes.has_paint,
            nodes.brand_paint,
            nodes.fractionated_product,
            nodes.closed_bag,
            nodes.small_sizes,
            nodes.veterinary,
            nodes.delivery,
            nodes.pets,
            nodes.pet_washing,
            nodes.petblock,
            nodes.carrousel,
            nodes.eukanuba_stand,
            nodes.iams_stand,
            nodes.sachets_stand,
            nodes.ct_stand,
            nodes.ct_exhibitor,
            nodes.pouch_optimum_exhibitor,
            nodes.pie_optimum_exhibitor,
            nodes.optimum_pallet,
            nodes.optimum_rack,
            nodes.champ_bucket,
            nodes.ct_exhibitor_pie,

            route_nodes.from_hour as planning_date
        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
        where route_nodes.route_id = $route_id and nodes.deleted_at is null
        having completed = 0
        order by planning_date, nodes.address asc;");
while($r = mysql_fetch_array($query)) { 
    $objs['nodes'][] = array(
        "id"                        =>      utf8_encode($r["id"]),
        "direccion"                 =>      utf8_encode($r["direccion"]),
        "localidad"                 =>      utf8_encode($r["localidad"]),
        "id_ruta"                   =>      utf8_encode($r["id_ruta"]),
        "lat"                       =>      utf8_encode($r["lat"]),
        "lng"                       =>      utf8_encode($r["lng"]),
        "id_canal"                  =>      utf8_encode($r["id_canal"]),
        "id_cadena"                 =>      utf8_encode($r["id_cadena"]),
        "cadena"                    =>      utf8_encode($r["cadena"]),
        "nombre"                    =>      utf8_encode($r["nombre"]),
        "completed"                 =>      utf8_encode($r["completed"]),
        "manager"                   =>      utf8_encode($r["manager"]),
        "comercial_name"            =>      utf8_encode($r["comercial_name"]),
        "phone"                     =>      utf8_encode($r["phone"]),
        "mail"                      =>      utf8_encode($r["mail"]),
        "supplier_id"               =>      utf8_encode($r["supplier_id"]),
        "cluster_id"                =>      utf8_encode($r["cluster_id"]),
        "branches"                  =>      utf8_encode($r["branches"]),
        "origin"                    =>      utf8_encode($r["origin"]),
        "boxes"                     =>      utf8_encode($r["boxes"]),
        "location"                  =>      utf8_encode($r["location"]),
        "m2_salon"                  =>      utf8_encode($r["m2_salon"]),
        "m2_salon_category"         =>      utf8_encode($r["m2_salon_category"]),
        "m2_deposit"                =>      utf8_encode($r["m2_deposit"]),
        "modules"                   =>      utf8_encode($r["modules"]),
        "has_sunblind"              =>      utf8_encode($r["has_sunblind"]),
        "brand_sunblind"            =>      utf8_encode($r["brand_sunblind"]),
        "has_awning"                =>      utf8_encode($r["has_awning"]),
        "brand_awning"              =>      utf8_encode($r["brand_awning"]),
        "has_paint"                 =>      utf8_encode($r["has_paint"]),
        "brand_paint"               =>      utf8_encode($r["brand_paint"]),
        "fractionated_product"      =>      utf8_encode($r["fractionated_product"]),
        "closed_bag"                =>      utf8_encode($r["closed_bag"]),
        "small_sizes"               =>      utf8_encode($r["small_sizes"]),
        "veterinary"                =>      utf8_encode($r["veterinary"]),
        "delivery"                  =>      utf8_encode($r["delivery"]),
        "pets"                      =>      utf8_encode($r["pets"]),
        "pet_washing"               =>      utf8_encode($r["pet_washing"]),
        "petblock"                  =>      utf8_encode($r["petblock"]),
        "carrousel"                 =>      utf8_encode($r["carrousel"]),
        "eukanuba_stand"            =>      utf8_encode($r["eukanuba_stand"]),
        "iams_stand"                =>      utf8_encode($r["iams_stand"]),
        "sachets_stand"             =>      utf8_encode($r["sachets_stand"]),
        "ct_stand"                  =>      utf8_encode($r["ct_stand"]),
        "ct_exhibitor"              =>      utf8_encode($r["ct_exhibitor"]),
        "pouch_optimum_exhibitor"   =>      utf8_encode($r["pouch_optimum_exhibitor"]),
        "pie_optimum_exhibitor"     =>      utf8_encode($r["pie_optimum_exhibitor"]),
        "optimum_pallet"            =>      utf8_encode($r["optimum_pallet"]),
        "optimum_rack"              =>      utf8_encode($r["optimum_rack"]),
        "champ_bucket"              =>      utf8_encode($r["champ_bucket"]),
        "ct_exhibitor_pie"              =>      utf8_encode($r["ct_exhibitor_pie"]),

        "planning_date"             =>      float2hour($r["planning_date"])
    );
}
/***    Fin de Puntos de Venta  ***/




/***    Formularios de Puntos de Venta  ***/
$objs['nodes_forms'] = array();
$query = mysql_query("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 nodes ON (nodes.id = node_forms.node_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 nodes.deleted_at is null
            " . (!$isSupervisorRepo ? 
                "AND date_format(form_planification.date, '%Y-%m-%d') = CURDATE()"
                : "AND date_format(form_planification.date, '%Y-%m-%d') BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE()"
            ) . "
        ORDER BY `forms`.`ordering` ASC;");
while($obj = mysql_fetch_array($query)) {   
    $arr = array();
    
    if($obj["condition_id"] == 1) {
        $arr = fillObj($arr, $obj);
    } elseif($obj["condition_id"] == 2) {
        if(!$obj["last_visited_at"] || strtotime($obj["last_visited_at"]) < strtotime("last Monday") || date('N') == 1) {
            $arr = fillObj($arr, $obj);
        }
    } elseif($obj["condition_id"] == 3) {
        if(date('d') < 15) {
            if(!$obj["last_visited_at"] || (strtotime($obj["last_visited_at"]) < strtotime(date('Y-m-01 00:00:00')))) {
                $arr = fillObj($arr, $obj);
            }
        } else {
            if(!$obj["last_visited_at"] || (strtotime($obj["last_visited_at"]) < strtotime(date('Y-m-15 00:00:00')))) {
                $arr = fillObj($arr, $obj);
            }
        }
    } elseif($obj["condition_id"] == 4) {
        if(!$obj["last_visited_at"] || (strtotime($obj["last_visited_at"]) < strtotime(date('Y-m-01 00:00:00')))) {
            $arr = fillObj($arr, $obj);
        }
    } elseif($obj["condition_id"] == 5) {
        if(!$obj["last_visited_at"]) {
            $arr = fillObj($arr, $obj);
        }
    } elseif($obj["condition_id"] == 6) {

        $queryPeriod = mysql_query(
            "SELECT date_from, date_to 
            FROM periods 
            WHERE NOW() BETWEEN date_from AND date_to"
        );

        $period = mysql_fetch_array($queryPeriod);
        
        if (!$obj['last_visited_at'] || strtotime($obj['last_visited_at']) < strtotime($period['date_from']))
            $arr = fillObj($arr, $obj);

    }
    if(!empty($arr)) $objs['nodes_forms'][] = $arr;
}
function fillObj($arr, $obj) 
{
    $arr['node_id'] = utf8_encode($obj['node_id']);
    $arr['form_id'] = utf8_encode($obj['form_id']);
    
    return $arr;
}
/***    Fin de Formularios de Puntos de Venta   ***/




/***    Inactivos   ***/
$objs['tags_inactives'] = array();
$query = mysql_query("
    SELECT DISTINCT node_inactive_tags.node_id, node_inactive_tags.tag_id
    FROM node_inactive_tags
    INNER JOIN route_nodes ON route_nodes.node_id = node_inactive_tags.node_id
    WHERE route_nodes.route_id = $route_id");
while($r = mysql_fetch_array($query)) {
    $objs['tags_inactives'][] = array(
        "node_id"       =>      utf8_encode($r["node_id"]),
        "tag_id"        =>      utf8_encode($r["tag_id"]),
    );
}
/***    Fin de Inactivos    ***/




/***    Objetivos   ***/
$objs['nodes_targets'] = array();
$query = mysql_query("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");
while($r = mysql_fetch_array($query)) {
        $objs['nodes_targets'][] = array(
        "id"        =>      utf8_encode($r["id"]),
        "name"      =>      utf8_encode($r["name"]),
        "node_id"   =>      utf8_encode($r["node_id"]),
    );
}
/***    Fin de Objetivos    ***/




/***    Usuarios de Puntos de venta ***/
$objs['nodes_users'] = array();

//cambio de funcion debido a la lentitud de la funcion distinct
//se deja de todas formas de bkp la funcion comentada

/*
$query = mysql_query("
        SELECT distinct
            n.id as node_id,
            u.id as user_id,
            concat(u.name, ' ', if(u.lastname is not null, u.lastname, '')) as fullname,
            u.phone as phone,
            ur.id as rol_id,
            ur.name as rol
        from users u
        inner join route_users ru on ru.user_id = u.id
        inner join route_nodes rn on rn.route_id = ru.route_id
        inner join nodes n on n.id = rn.node_id
        inner join user_rols ur on ur.id = u.rol_id

        where rn.node_id in (
            select distinct nodes.id as node_id from nodes inner join route_nodes on nodes.id = route_nodes.node_id where route_nodes.route_id = $route_id
            );
    ");
    */

$query = mysql_query("
        SELECT DISTINCT
            n.id AS node_id,
            u.id AS user_id,
            CONCAT(u.name, ' ', IFNULL(u.lastname, '')) AS fullname,
            u.phone,
            ur.id AS rol_id,
            ur.name AS rol
        FROM users u
        INNER JOIN user_rols ur ON ur.id = u.rol_id
        INNER JOIN route_users ru ON ru.user_id = u.id
        INNER JOIN route_nodes rn ON rn.route_id = ru.route_id
        INNER JOIN nodes n ON n.id = rn.node_id
        INNER JOIN route_nodes filter_rn ON filter_rn.node_id = n.id AND filter_rn.route_id = $route_id;
    ");

while($r = mysql_fetch_array($query)) {
    $objs['nodes_users'][] = array(
        "node_id"       =>      utf8_encode($r["node_id"]),
        "user_id"       =>      utf8_encode($r["user_id"]),
        "fullname"      =>      utf8_encode($r["fullname"]),
        "phone"         =>      utf8_encode($r["phone"]),
        "rol_id"        =>      utf8_encode($r["rol_id"]),
        "rol"           =>      utf8_encode($r["rol"]),
    );
}
/***    Fin de Usuarios de Puntos de Venta  ***/




/***    Proveedores ***/
$objs['suppliers'] = array();
$query = mysql_query("SELECT * from suppliers");
while($r = mysql_fetch_array($query)) { 
    $objs['suppliers'][] = array(
        "id"        =>      utf8_encode($r["id"]),
        "name"      =>      utf8_encode($r["name"]),
        "mail"      =>      utf8_encode($r["mail"]),
    );
}
/***    Fin de Proveedores  ***/




/***    Proveedores de Tags ***/
$objs['suppliers_tags'] = array();
$query = mysql_query("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");
while($r = mysql_fetch_array($query)) { 
    $objs['suppliers_tags'][] = array(
        "supplier_id"   =>      utf8_encode($r["supplier_id"]),
        "tag_id"        =>      utf8_encode($r["tag_id"]),
        "name"          =>      utf8_encode($r["name"]),
    );
}
/***    Fin de Proveedores de Tags  ***/




/***    Tags    ***/
$objs['tags'] = array();
$query = mysql_query("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;");

while($r = mysql_fetch_array($query)) {
    $objs['tags'][] = array(
        "tag_id"        =>      utf8_encode($r["tag_id"]),
        "tag"           =>      utf8_encode($r["tag"]),
        "subcategory"   =>      utf8_encode($r["subcategory"]),
        "category"      =>      utf8_encode($r["category"])
    );
}
/***    Fin de Tags ***/
    

/***    Resultado del procesamiento codificado a JSON   ***/
echo json_encode($objs);