{"id":4067,"date":"2017-04-29T22:18:23","date_gmt":"2017-04-30T02:18:23","guid":{"rendered":"http:\/\/blog-j.marcano.net.ve\/?p=4067"},"modified":"2018-09-18T10:47:08","modified_gmt":"2018-09-18T14:47:08","slug":"crear-tabla-json-para-timeline-de-google-charts","status":"publish","type":"post","link":"https:\/\/blog-j.marcano.net.ve\/index.php\/2017\/04\/29\/crear-tabla-json-para-timeline-de-google-charts\/","title":{"rendered":"Crear tabla JSON para Timeline de google charts"},"content":{"rendered":"

En la p\u00e1gina de google charts se explica claramente e formato que debe tener la tabla de datos JSON en el caso de un PieChart<\/a>, en el ejemplo el fragmento getData.php no hace m\u00e1s que leer la data de un archivo de texto con el formato requerido, en una aplicaci\u00f3n real debemos generar este formato JSON din\u00e1micamente en PHP ya que necesitamos que la data del chart que empleamos var\u00ede, ya sea en funcion de las actualizaciones de la base de datos o de las consultas a esta. Es decir,necesitamos escribir c\u00f3digo PHP que en funcion de nuestras necesidades genere la tabla JSON que a fin de cuentas no es m\u00e1s que texto con cierto formato.<\/p>\n

Para el caso particular del timeline del art\u00edculo sobre el raspberry pi 3 y el sensor PIR<\/a> la base de datos original cuenta con 3 columnas b\u00e1sicas, no indicar\u00e9 como crear las columnas, es harina de otro costal, en el caso del ejemplo cada columna cumple la siguiente funci\u00f3n:<\/p>\n

    \n
  1. Un columna ID: No es m\u00e1s que un n\u00famero de referencia. En este caso tom\u00e9 el ID como llave primaria.<\/li>\n
  2. Una columna indica la Fecha de iniciodel evento de detecci\u00f3n del sensor. El formato del dato es DATETIME<\/a> (hora y fecha combinados).<\/li>\n
  3. Una tercera columna indica fecha de finalizaci\u00f3n del evento de detecci\u00f3n con el mismo formato DATETIME.<\/li>\n<\/ol>\n

    No entrar\u00e9 en detalles sobre el c\u00f3digo del raspi y como escribe en la base de datos, tal vez para otro post, . Lo cierto es que con esta tabla de la base de datos se proceder\u00e1 a graficar usando google charts y para ello necesitamos el JSON.<\/p>\n

    Veamos la tabla (desde MySQL workbench<\/a>):<\/p>\n

    <\/a>

    Tabla de la base de datos (sensor pir)<\/p><\/div>\n

    Luego hacemos un QUERY super especial a la tabla llamada PIR que dice as\u00ed:<\/p>\n

    SELECT Id, Fecha, Fecha_2, timediff(Fecha_2,Fecha) AS calculated \r\nFROM pir WHERE Fecha_2 IS NOT NULL and(Fecha > DATE_SUB(now(),INTERVAL 30 DAY))\r\nORDER BY Id DESC<\/pre>\n
    Traduciendo al estilo open english:<\/p>\n
    Seleciona las columnas: Id,Fecha(inicio del evento), la Fecha_2(fin del evento) y la columna nueva timediff calculada de la tabla PIR donde la fecha_2 es no nula y la en un intervalo de los \u00faltimos 30 d\u00edas, ordenado la tabla por los ID en forma descendente.<\/p>\n
    Ok, revisemos con mayor cuidado:<\/p>\n
    La parte:<\/p>\n
    SELECT Id, Fecha, Fecha_2, timediff(Fecha_2,Fecha)<\/pre>\n
    Lo que hace esta consulta es arrojar una tabla con las columnas: Id, Fecha (inicio del evento) y Fecha_2 (fin del evento), lo util aqu\u00ed es que crearemos con las herramientas de MySQL una cuarta columna con la funci\u00f3n timediff(Fecha_2,Fecha), y que nos regresa la diferencia de tiempo entre los eventos, as\u00ed podemos conocer cuanto tiempo dura el evento. Realmente esta columna adicional no se emplea para construir la tabla JSON as\u00ed que es opcional y meramente informativa.<\/p>\n
    AS calculated FROM pir WHERE Fecha_2 IS NOT NULL<\/pre>\n
    Nos indica que la nueva tabla no incluir\u00e1 filas donde la Fecha_2 sea no nula ya que bajo ciertas circunstancias, durante el apagado o encendido repentino del raspberry PI puede darse esta situaci\u00f3n y Google charts no ser\u00e1 capaz de realizar la gr\u00e1fica si el dato es nulo, por ello se ignoran estas filas que contengan eventos incompletos.<\/p>\n
    and(Fecha > DATE_SUB(now(),INTERVAL 30 DAY))ORDER BY Id DESC<\/pre>\n
    Los eventos de la nueva tabla ser\u00e1n de los \u00faltimos 30 d\u00edas y las filas se ordenaran por el ID en forma descendente. Ello es conveniente ya que no interesa ver en el timeline de google charts los eventos m\u00e1s recientes primeros (no los m\u00e1s antiguos).<\/p>\n
    Luego de esta super consulta resulta:<\/p>\n
    <\/a>
    Tabla resultante del QUERY<\/p><\/div>\n
    Y bueno lleg\u00f3 la hora de la verdad, he aqu\u00ed como crear la tabla JSON para el timeline, adelanto que el resultado se puede ver aqu\u00ed<\/a>.<\/p>\n
    El archivo que llamamos get_json.php dice as\u00ed:<\/p>\n
    <?php\r\n\t\r\nrequire_once(\"mysqlli_connect.php\"); \/\/ se llama al script \"mysqlli_connect.php\" \r\n\/\/que realiza la conexi\u00c3\u00b3n mediante mysqli connect, \r\n\/\/en este caso el Query o consulta se encuentra all\u00c3\u00ad.\r\ninclude 'mysqlli_connect.php';\r\n$table = array();\r\n$table['cols'] = array(\r\n        array('id' => 'Role', 'type' => 'string'),\r\n        array('id' => 'Name', 'type' => 'string'),\r\n        array('id' => 'Start', 'type' => 'date'),\r\n        array('id' => 'End', 'type' => 'date'));\r\n\r\n$rows = array();\r\nwhile($r = mysqli_fetch_assoc($query)) {\r\n\t\r\npreg_match('\/(\\d{4})-(\\d{2})-(\\d{2})\\s(\\d{2}):(\\d{2}):(\\d{2})\/', $r['Fecha'], $match);\r\n    \r\n    $year = (int) $match[1];\r\n    $month = (int) $match[2]-1;\/\/convert to zero-index to match javascript's dates\r\n    $day = (int) $match[3];\r\n    $hours = (int) $match[4];\r\n    $minutes = (int) $match[5];\r\n    $seconds = (int) $match[6];\r\n\t\r\npreg_match('\/(\\d{4})-(\\d{2})-(\\d{2})\\s(\\d{2}):(\\d{2}):(\\d{2})\/', $r['Fecha_2'], $match);\r\n\r\n    $year_2 = (int) $match[1];\r\n    $month_2 = (int) $match[2]-1;\/\/convert to zero-index to match javascript's dates\r\n    $day_2 = (int) $match[3];\r\n    $hours_2 = (int) $match[4];\r\n    $minutes_2 = (int) $match[5];\r\n    $seconds_2 = (int) $match[6];\r\n\t\t\r\n    $temp = array();\r\n\t$temp[] = array('v' => date('m\/d\/Y',strtotime($r['Fecha'])));\r\n\t$temp[] = array('v' => 'Detecci\u00c3\u00b3n # '.$r['Id'].'');\r\n\t$temp[] = array('v' => \"Date(0,0,0,$hours,$minutes,$seconds)\"); \r\n\r\n\/\/ Se ignora la fecha \r\n\/\/dia\/mes\/ano y se rellena con ceros ya que cada dia se colocara como una nueva fila.\r\n\t\r\n        if($hours>$hours_2){$hours_2=24;$minutes_2=00;} \r\n\r\n\/\/ evita que el Timeline chart genere un error\r\n\/\/ cuando se pasa de las 12:XX a las 00:XX a media noche, \r\n\/\/ya que la hora de \"Start\" debe ser menor que la de \"End\" para el timeline.\r\n\t\r\n        $temp[] = array('v' => \"Date(0,0,0,$hours_2,$minutes_2,$seconds_2)\");\r\n        $rows[] = array('c' => $temp);\t\r\n}\r\n$table['rows'] = $rows;\r\n\r\n$jsonTable = json_encode($table);\r\n\r\nheader('Content-type: application\/json');\r\n\r\necho $jsonTable;\r\n?><\/pre>\n
    el archivo mySQLi es solo otra secci\u00f3n de c\u00f3digo que realiza la conexi\u00f3n a la base de datos, as\u00ed. Recordemos que utiliza MySQLli <\/a>(extensi\u00f3n del PHP para conectarse a bases de datos MySQL). Existen cantidad de ejemplos en la red. Recordemos que este se ejecuta ANTES que el segmento de c\u00f3digo anterior.<\/p>\n
    <?php\r\n$db_host = 'localhost'; \/\/ Nombre del host, como la base \r\nde datos est\u00e1 en el mismo raspberry ponemos localhost\r\n$db_user = 'root'; \/\/ el nombre del usuario\r\n$db_pass = 'PASSWORD'; \/\/ Password\r\n$db_name = 'NOMBRE_DE_LA_BASE_DE_DATOS'; \/\/ Database Name\r\n\r\n$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);\r\nif (!$conn) {\r\n\tdie ('Failed to connect to MySQL: ' . mysqli_connect_error());\t\r\n}\r\n$sql = 'SELECT Id, Fecha, Fecha_2, timediff(Fecha_2,Fecha) AS \r\ncalculated FROM pir WHERE Fecha_2 IS NOT NULL \r\nand(Fecha > DATE_SUB(now(),INTERVAL 30 DAY))ORDER BY Id DESC';\r\n$query = mysqli_query($conn, $sql);\r\n\r\nif (!$query) \r\n{\r\n\tdie ('SQL Error: ' . mysqli_error($conn));\r\n}\r\n\r\n?><\/pre>\n
    N\u00f3tese que all\u00ed se encuentra el QUERY del que hablamos al inicio del art\u00edculo, la secci\u00f3n del query podr\u00eda colocarse en otro archivo y modificarse  dinamicamente , ya esto lo haremos en otra ocasi\u00f3n. <\/p>\n
    A continuaci\u00f3n la p\u00e1gina index o la p\u00e1gina que llamaremos para realizar el gr\u00e1fico (el inicio de todo y que llama a los segmentos de c\u00f3digo anteriores, es igual al que sale en la p\u00e1gina de google<\/a>, solo que las secciones que hacen referencia al piechart, ha sido susituidas por Timeline. En el caso de nuestro server se llama main_5.php<\/p>\n
    <html>\r\n  <head>\r\n    <!--Load the AJAX API-->\r\n<script type=\"text\/javascript\" src=\"https:\/\/www.gstatic.com\/charts\/loader.js\"><\/script>\r\n<script type=\"text\/javascript\" src=\"\/\/ajax.googleapis.com\/ajax\/libs\/jquery\/1.10.2\/jquery.min.js\"><\/script>\r\n<script type=\"text\/javascript\">\r\n    \r\n    \/\/ Load the Visualization API and the piechart package.\r\n    google.charts.load('current', {'packages':['timeline']});\r\n      \r\n    \/\/ Set a callback to run when the Google Visualization API is loaded.\r\n    google.charts.setOnLoadCallback(drawChart);\r\n\t\r\n\tvar options = {\r\n      timeline: { singleColor: '#4d4dff' },\r\n    };\r\n      \r\n    function drawChart() {\r\n      var jsonData = $.ajax({\r\n          url: \"get_json.php\",\r\n          dataType: \"json\",\r\n          async: false\r\n          }).responseText;\r\n          \r\n      \/\/ Create our data table out of JSON data loaded from server.\r\n      var data = new google.visualization.DataTable(jsonData);\r\n\r\n      \/\/ Instantiate and draw our chart, passing in some options.\r\n      var chart = new google.visualization.Timeline(document.getElementById('chart_div'));\r\n      chart.draw(data,options);\r\n    }\r\n\r\n    <\/script>\r\n  <\/head>\r\n\r\n  <body>\r\n    <!--Div that will hold the pie chart-->\r\n    <div id=\"chart_div\" style=\"height: 600px;\"><\/div>\r\n  <\/body>\r\n<\/html>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
    En la p\u00e1gina de google charts se explica claramente e formato que debe tener la tabla de datos JSON en el caso de un PieChart, en el ejemplo el fragmento getData.php no hace m\u00e1s que leer la data de un archivo de texto con el formato requerido, en una aplicaci\u00f3n real debemos generar este formato JSON […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[18,5],"tags":[151,154,155,152,67,141,146,145,149,147,142,150,153,140],"_links":{"self":[{"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/posts\/4067"}],"collection":[{"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/comments?post=4067"}],"version-history":[{"count":17,"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/posts\/4067\/revisions"}],"predecessor-version":[{"id":4268,"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/posts\/4067\/revisions\/4268"}],"wp:attachment":[{"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/media?parent=4067"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/categories?post=4067"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog-j.marcano.net.ve\/index.php\/wp-json\/wp\/v2\/tags?post=4067"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}