js/tbl_zoom_plot.js

   1 /* vim: set expandtab sw=4 ts=4 sts=4: */
   2 /**
   3  ** @fileoverview JavaScript functions used on tbl_select.php
   4  **
   5  ** @requires    jQuery
   6  ** @requires    js/functions.js
   7  **/
   8
   9
  10 /**
  11  **  Display Help/Info
  12  **/
  13 function displayHelp() {
  14     var msgbox = PMA_ajaxShowMessage(PMA_messages['strDisplayHelp'],10000);
  15     msgbox.click(function() {
  16         PMA_ajaxRemoveMessage(msgbox);
  17     });
  18 }
  19
  20 /**
  21  ** Extend the array object for max function
  22  ** @param array
  23  **/
  24 Array.max = function (array) {
  25     return Math.max.apply( Math, array );
  26 }
  27
  28 /**
  29  ** Extend the array object for min function
  30  ** @param array
  31  **/
  32 Array.min = function (array) {
  33     return Math.min.apply( Math, array );
  34 }
  35
  36 /**
  37  ** Checks if a string contains only numeric value
  38  ** @param n: String (to be checked)
  39  **/
  40 function isNumeric(n) {
  41     return !isNaN(parseFloat(n)) && isFinite(n);
  42 }
  43
  44 /**
  45  ** Checks if an object is empty
  46  ** @param n: Object (to be checked)
  47  **/
  48 function isEmpty(obj) {
  49     var name;
  50     for (name in obj) {
  51         return false;
  52     }
  53     return true;
  54 }
  55
  56 /**
  57  ** Converts a timestamp into the format of its field type
  58  ** @param val  Integer Timestamp
  59  ** @param type String  Field type(datetime/timestamp/time/date)
  60  **/
  61 function getDate(val,type) {
  62     if(type.toString().search(/datetime/i) != -1 || type.toString().search(/timestamp/i) != -1) {
  63         return Highcharts.dateFormat('%Y-%m-%e %H:%M:%S', val)
  64     }
  65     else if(type.toString().search(/time/i) != -1) {
  66         return Highcharts.dateFormat('%H:%M:%S', val)
  67     }
  68     else if (type.toString().search(/date/i) != -1) {
  69         return Highcharts.dateFormat('%Y-%m-%e', val)
  70     }
  71 }
  72
  73 /**
  74  ** Converts a date/time into timestamp
  75  ** @param val  String Date
  76  ** @param type Sring  Field type(datetime/timestamp/time/date)
  77  **/
  78 function getTimeStamp(val,type) {
  79     if(type.toString().search(/datetime/i) != -1 || type.toString().search(/timestamp/i) != -1) {
  80         return getDateFromFormat(val,'yyyy-MM-dd HH:mm:ss', val)
  81     }
  82     else if(type.toString().search(/time/i) != -1) {
  83         return Highcharts.dateFormat('HH:mm:ss', val)
  84     }
  85     else if (type.toString().search(/date/i) != -1) {
  86         return Highcharts.dateFormat('yyyy-mm-dd', val)
  87     }
  88 }
  89
  90 /**
  91  ** Classifies the field type into numeric,timeseries or text
  92  ** @param field: field type (as in database structure)
  93  **/
  94 function getType(field) {
  95         if(field.toString().search(/int/i) != -1 || field.toString().search(/decimal/i) != -1 || field.toString().search(/year/i) != -1)
  96             return 'numeric';
  97         else if(field.toString().search(/time/i) != -1 || field.toString().search(/date/i) != -1)
  98             return 'time';
  99         else
 100             return 'text';
 101 }
 102 /**
 103  ** Converts a categorical array into numeric array
 104  ** @param array categorical values array
 105  **/
 106 function getCord(arr) {
 107     var newCord = new Array();
 108     var original = $.extend(true,[],arr);
 109     var arr = jQuery.unique(arr).sort();
 110     $.each(original, function(index,value) {
 111         newCord.push(jQuery.inArray(value,arr));
 112     });
 113     return [newCord,arr,original];
 114 }
 115
 116 /**
 117  ** Scrolls the view to the display section
 118  **/
 119 function scrollToChart() {
 120    var x = $('#dataDisplay').offset().top - 100; // 100 provides buffer in viewport
 121    $('html,body').animate({scrollTop: x}, 500);
 122 }
 123
 124 $(document).ready(function() {
 125
 126    /**
 127     ** Set a parameter for all Ajax queries made on this page.  Don't let the
 128     ** web server serve cached pages
 129     **/
 130     $.ajaxSetup({
 131         cache: 'false'
 132     });
 133
 134     var cursorMode = ($("input[name='mode']:checked").val() == 'edit') ? 'crosshair' : 'pointer';
 135     var currentChart = null;
 136     var currentData = null;
 137     var xLabel = $('#tableid_0').val();
 138     var yLabel = $('#tableid_1').val();
 139     var xType = $('#types_0').val();
 140     var yType = $('#types_1').val();
 141     var dataLabel = $('#dataLabel').val();
 142
 143     // Get query result
 144     var data = jQuery.parseJSON($('#querydata').html());
 145
 146     /**
 147      ** Input form submit on field change
 148      **/
 149     $('#tableid_0').change(function() {
 150           $('#zoom_search_form').submit();
 151     })
 152
 153     $('#tableid_1').change(function() {
 154           $('#zoom_search_form').submit();
 155     })
 156
 157     $('#tableid_2').change(function() {
 158           $('#zoom_search_form').submit();
 159     })
 160
 161     $('#tableid_3').change(function() {
 162           $('#zoom_search_form').submit();
 163     })
 164
 165     /**
 166      * Input form validation
 167      **/
 168     $('#inputFormSubmitId').click(function() {
 169         if ($('#tableid_0').get(0).selectedIndex == 0 || $('#tableid_1').get(0).selectedIndex == 0)
 170             PMA_ajaxShowMessage(PMA_messages['strInputNull']);
 171         else if (xLabel == yLabel)
 172             PMA_ajaxShowMessage(PMA_messages['strSameInputs']);
 173     });
 174
 175     /**
 176       ** Prepare a div containing a link, otherwise it's incorrectly displayed
 177       ** after a couple of clicks
 178       **/
 179     $('<div id="togglesearchformdiv"><a id="togglesearchformlink"></a></div>')
 180     .insertAfter('#zoom_search_form')
 181     // don't show it until we have results on-screen
 182     .hide();
 183
 184     $('#togglesearchformlink')
 185         .html(PMA_messages['strShowSearchCriteria'])
 186         .bind('click', function() {
 187             var $link = $(this);
 188             $('#zoom_search_form').slideToggle();
 189             if ($link.text() == PMA_messages['strHideSearchCriteria']) {
 190                 $link.text(PMA_messages['strShowSearchCriteria']);
 191             } else {
 192                 $link.text(PMA_messages['strHideSearchCriteria']);
 193             }
 194              // avoid default click action
 195             return false;
 196          });
 197
 198     /**
 199      ** Set dialog properties for the data display form
 200      **/
 201     $("#dataDisplay").dialog({
 202         autoOpen: false,
 203         title: 'Data point content',
 204         modal: false, //false otherwise other dialogues like timepicker may not function properly
 205         height: $('#dataDisplay').height() + 80,
 206         width: $('#dataDisplay').width() + 80
 207     });
 208
 209     /*
 210      * Handle submit of zoom_display_form
 211      */
 212
 213     $("#submitForm").click(function(event) {
 214
 215         //Prevent default submission of form
 216         event.preventDefault();
 217
 218         //Find changed values by comparing form values with selectedRow Object
 219         var newValues = new Array();//Stores the values changed from original
 220         var it = 4;
 221         var xChange = false;
 222         var yChange = false;
 223         for (key in selectedRow) {
 224             if (key != 'where_clause'){
 225                 var oldVal = selectedRow[key];
 226                 var newVal = ($('#fields_null_id_' + it).attr('checked')) ? null : $('#fieldID_' + it).val();
 227                 if (oldVal != newVal){
 228                     selectedRow[key] = newVal;
 229                     newValues[key] = newVal;
 230                     if(key == xLabel) {
 231                         xChange = true;
 232                         data[currentData][xLabel] = newVal;
 233                     }
 234                     else if(key == yLabel) {
 235                         yChange = true;
 236                         data[currentData][yLabel] = newVal;
 237                     }
 238                 }
 239             }
 240             it++
 241         }//End data update
 242
 243         //Update the chart series and replot
 244         if (xChange || yChange) {
 245             var newSeries = new Array();
 246             newSeries[0] = new Object();
 247             newSeries[0].marker = {
 248                 symbol: 'circle'
 249             };
 250             //Logic similar to plot generation, replot only if xAxis changes or yAxis changes. Code includes a lot of checks so as to replot only when necessary
 251             if(xChange) {
 252                 xCord[currentData] = selectedRow[xLabel];
 253                 if(xType == 'numeric') {
 254                     currentChart.series[0].data[currentData].update({ x : selectedRow[xLabel] });
 255                     currentChart.xAxis[0].setExtremes(Array.min(xCord) - 6,Array.max(xCord) + 6);
 256                 }
 257                 else if(xType == 'time') {
 258                     currentChart.series[0].data[currentData].update({ x : getTimeStamp(selectedRow[xLabel],$('#types_0').val())});
 259                 }
 260                 else {
 261                     var tempX = getCord(xCord);
 262                     var tempY = getCord(yCord);
 263                     var i = 0;
 264                     newSeries[0].data = new Array();
 265                     xCord = tempX[2];
 266                     yCord = tempY[2];
 267
 268                     $.each(data,function(key,value) {
 269                         if(yType != 'text')
 270                             newSeries[0].data.push({ name: value[dataLabel], x: tempX[0][i], y: value[yLabel], marker: {fillColor: colorCodes[i % 8]} , id: i } );
 271                         else
 272                             newSeries[0].data.push({ name: value[dataLabel], x: tempX[0][i], y: tempY[0][i], marker: {fillColor: colorCodes[i % 8]} , id: i } );
 273                         i++;
 274                     });
 275                     currentSettings.xAxis.labels = { formatter : function() {
 276                         if(tempX[1][this.value] && tempX[1][this.value].length > 10)
 277                             return tempX[1][this.value].substring(0,10)
 278                         else
 279                             return tempX[1][this.value];
 280                         }
 281                     }
 282                     currentSettings.series = newSeries;
 283                     currentChart = PMA_createChart(currentSettings);
 284                 }
 285
 286             }
 287             if(yChange) {
 288
 289                 yCord[currentData] = selectedRow[yLabel];
 290                 if(yType == 'numeric') {
 291                     currentChart.series[0].data[currentData].update({ y : selectedRow[yLabel] });
 292                     currentChart.yAxis[0].setExtremes(Array.min(yCord) - 6,Array.max(yCord) + 6);
 293                 }
 294                 else if(yType =='time') {
 295                     currentChart.series[0].data[currentData].update({ y : getTimeStamp(selectedRow[yLabel],$('#types_1').val())});
 296                 }
 297                 else {
 298                     var tempX = getCord(xCord);
 299                     var tempY = getCord(yCord);
 300                     var i = 0;
 301                     newSeries[0].data = new Array();
 302                     xCord = tempX[2];
 303                     yCord = tempY[2];
 304
 305                     $.each(data,function(key,value) {
 306                         if(xType != 'text' )
 307                             newSeries[0].data.push({ name: value[dataLabel], x: value[xLabel], y: tempY[0][i], marker: {fillColor: colorCodes[i % 8]} , id: i } );
 308                         else
 309                             newSeries[0].data.push({ name: value[dataLabel], x: tempX[0][i], y: tempY[0][i], marker: {fillColor: colorCodes[i % 8]} , id: i } );
 310                         i++;
 311                     });
 312                     currentSettings.yAxis.labels = { formatter : function() {
 313                         if(tempY[1][this.value] && tempY[1][this.value].length > 10)
 314                             return tempY[1][this.value].substring(0,10)
 315                         else
 316                             return tempY[1][this.value];
 317                         }
 318                     }
 319                     currentSettings.series = newSeries;
 320                     currentChart = PMA_createChart(currentSettings);
 321                 }
 322             }
 323             currentChart.series[0].data[currentData].select();
 324         }
 325         //End plot update
 326
 327         //Generate SQL query for update
 328         if (!isEmpty(newValues)) {
 329             var sql_query = 'UPDATE `' + window.parent.table + '` SET ';
 330             for (key in newValues) {
 331                 if(key != 'where_clause') {
 332                     sql_query += '`' + key + '`=' ;
 333                     var value = newValues[key];
 334                     if(!isNumeric(value) && value != null)
 335                         sql_query += '\'' + value + '\' ,';
 336                     else
 337                         sql_query += value + ' ,';
 338                 }
 339             }
 340             sql_query = sql_query.substring(0, sql_query.length - 1);
 341             sql_query += ' WHERE ' + PMA_urldecode(data[currentData]['where_clause']);
 342
 343             //Post SQL query to sql.php
 344             $.post('sql.php', {
 345                 'token' : window.parent.token,
 346                 'db' : window.parent.db,
 347                 'ajax_request' : true,
 348                 'sql_query' : sql_query,
 349                 'inline_edit' : false
 350                 }, function(data) {
 351                     if(data.success == true) {
 352                         $('#sqlqueryresults').html(data.sql_query);
 353                         $("#sqlqueryresults").trigger('appendAnchor');
 354                     }
 355                     else
 356                         PMA_ajaxShowMessage(data.error);
 357             })//End $.post
 358         }//End database update
 359         $("#dataDisplay").dialog("close");
 360     });//End submit handler
 361
 362     /*
 363      * Generate plot using Highcharts
 364      */
 365
 366     if (data != null) {
 367         $('#zoom_search_form')
 368          .slideToggle()
 369          .hide();
 370         $('#togglesearchformlink')
 371          .text(PMA_messages['strShowSearchCriteria'])
 372         $('#togglesearchformdiv').show();
 373         var selectedRow;
 374         var columnNames = new Array();
 375         var colorCodes = ['#FF0000','#00FFFF','#0000FF','#0000A0','#FF0080','#800080','#FFFF00','#00FF00','#FF00FF'];
 376         var series = new Array();
 377         var xCord = new Array();
 378         var yCord = new Array();
 379         var xCat = new Array();
 380         var yCat = new Array();
 381         var tempX, tempY;
 382         var it = 0;
 383
 384         // Set the basic plot settings
 385         var currentSettings = {
 386             chart: {
 387                 renderTo: 'querychart',
 388                 type: 'scatter',
 389                 zoomType: 'xy',
 390                 width:$('#resizer').width() -3,
 391                 height:$('#resizer').height()-20
 392             },
 393             credits: {
 394                 enabled: false
 395             },
 396             exporting: { enabled: false },
 397             label: { text: $('#dataLabel').val() },
 398             plotOptions: {
 399                 series: {
 400                     allowPointSelect: true,
 401                     cursor: 'pointer',
 402                     showInLegend: false,
 403                     dataLabels: {
 404                         enabled: false,
 405                     },
 406                     point: {
 407                         events: {
 408                             click: function() {
 409                                 var id = this.id;
 410                                 var fid = 4;
 411                                 currentData = id;
 412                                 // Make AJAX request to tbl_zoom_select.php for getting the complete row info
 413                                 var post_params = {
 414                                     'ajax_request' : true,
 415                                     'get_data_row' : true,
 416                                     'db' : window.parent.db,
 417                                     'table' : window.parent.table,
 418                                     'where_clause' : data[id]['where_clause'],
 419                                     'token' : window.parent.token,
 420                                 }
 421                                 $.post('tbl_zoom_select.php', post_params, function(data) {
 422                                     // Row is contained in data.row_info, now fill the displayResultForm with row values
 423                                     for ( key in data.row_info) {
 424                                         if (data.row_info[key] == null)
 425                                             $('#fields_null_id_' + fid).attr('checked', true);
 426                                         else
 427                                             $('#fieldID_' + fid).val(data.row_info[key]);
 428                                         fid++;
 429                                      }
 430                                      selectedRow = new Object();
 431                                      selectedRow = data.row_info;
 432                                 });
 433
 434                                 $("#dataDisplay").dialog("open");
 435                             },
 436                         }
 437                     }
 438                 }
 439             },
 440             tooltip: {
 441                 formatter: function() {
 442                     return this.point.name;
 443                 }
 444             },
 445             title: { text: 'Query Results' },
 446             xAxis: {
 447                 title: { text: $('#tableid_0').val() },
 448             },
 449             yAxis: {
 450                 title: { text: $('#tableid_1').val() },
 451             },
 452         }
 453
 454         $('#resizer').resizable({
 455             resize: function() {
 456                 currentChart.setSize(
 457                     this.offsetWidth -3,
 458                     this.offsetHeight -20,
 459                     false
 460                 );
 461             }
 462         });
 463
 464         // Classify types as either numeric,time,text
 465         xType = getType(xType);
 466         yType = getType(yType);
 467
 468         //Set the axis type based on the field
 469         currentSettings.xAxis.type = (xType == 'time') ? 'datetime' : 'linear';
 470         currentSettings.yAxis.type = (yType == 'time') ? 'datetime' : 'linear';
 471
 472         // Formulate series data for plot
 473         series[0] = new Object();
 474         series[0].data = new Array();
 475         series[0].marker = {
 476             symbol: 'circle'
 477         };
 478         if (xType != 'text' && yType != 'text') {
 479             $.each(data,function(key,value) {
 480                 var xVal = (xType == 'numeric') ? value[xLabel] : getTimeStamp(value[xLabel],$('#types_0').val());
 481                 var yVal = (yType == 'numeric') ? value[yLabel] : getTimeStamp(value[yLabel],$('#types_1').val());
 482                 series[0].data.push({ name: value[dataLabel], x: xVal, y: yVal, marker: {fillColor: colorCodes[it % 8]} , id: it } );
 483                 xCord.push(value[xLabel]);
 484                 yCord.push(value[yLabel]);
 485                 it++;
 486             });
 487             if(xType == 'numeric') {
 488                 currentSettings.xAxis.max = Array.max(xCord) + 6
 489                 currentSettings.xAxis.min = Array.min(xCord) - 6
 490             }
 491             else {
 492                 currentSettings.xAxis.labels = { formatter : function() {
 493                     return getDate(this.value, $('#types_0').val());
 494                 }}
 495             }
 496             if(yType == 'numeric') {
 497                 currentSettings.yAxis.max = Array.max(yCord) + 6
 498                 currentSettings.yAxis.min = Array.min(yCord) - 6
 499             }
 500             else {
 501                 currentSettings.yAxis.labels = { formatter : function() {
 502                     return getDate(this.value, $('#types_1').val());
 503                 }}
 504             }
 505
 506         }
 507
 508         else if (xType =='text' && yType !='text') {
 509             $.each(data,function(key,value) {
 510                 xCord.push(value[xLabel]);
 511                 yCord.push(value[yLabel]);
 512             });
 513
 514             tempX = getCord(xCord);
 515             $.each(data,function(key,value) {
 516                 var yVal = (yType == 'numeric') ? value[yLabel] : getTimeStamp(value[yLabel],$('#types_1').val());
 517                 series[0].data.push({ name: value[dataLabel], x: tempX[0][it], y: yVal, marker: {fillColor: colorCodes[it % 8]} , id: it } );
 518                 it++;
 519             });
 520
 521             currentSettings.xAxis.labels = { formatter : function() {
 522                     if(tempX[1][this.value] && tempX[1][this.value].length > 10)
 523                         return tempX[1][this.value].substring(0,10)
 524                     else
 525                         return tempX[1][this.value];
 526                 }
 527             }
 528             if(yType == 'numeric') {
 529                 currentSettings.yAxis.max = Array.max(yCord) + 6
 530                 currentSettings.yAxis.min = Array.min(yCord) - 6
 531             }
 532             else {
 533                 currentSettings.yAxis.labels = { formatter : function() {
 534                     return getDate(this.value, $('#types_1').val());
 535                 }}
 536             }
 537             xCord = tempX[2];
 538         }
 539
 540         else if (xType !='text' && yType =='text') {
 541             $.each(data,function(key,value) {
 542                 xCord.push(value[xLabel]);
 543                 yCord.push(value[yLabel]);
 544             });
 545             tempY = getCord(yCord);
 546             $.each(data,function(key,value) {
 547                 var xVal = (xType == 'numeric') ? value[xLabel] : getTimeStamp(value[xLabel],$('#types_0').val());
 548                 series[0].data.push({ name: value[dataLabel], y: tempY[0][it], x: xVal, marker: {fillColor: colorCodes[it % 8]} , id: it } );
 549                 it++;
 550             });
 551             if(xType == 'numeric') {
 552                 currentSettings.xAxis.max = Array.max(xCord) + 6
 553                 currentSettings.xAxis.min = Array.min(xCord) - 6
 554             }
 555             else {
 556                 currentSettings.xAxis.labels = { formatter : function() {
 557                     return getDate(this.value, $('#types_0').val());
 558                 }}
 559             }
 560             currentSettings.yAxis.labels = { formatter : function() {
 561                     if(tempY[1][this.value] && tempY[1][this.value].length > 10)
 562                         return tempY[1][this.value].substring(0,10)
 563                     else
 564                         return tempY[1][this.value];
 565                 }
 566             }
 567             yCord = tempY[2];
 568         }
 569
 570         else if (xType =='text' && yType =='text') {
 571             $.each(data,function(key,value) {
 572                 xCord.push(value[xLabel]);
 573                 yCord.push(value[yLabel]);
 574             });
 575             tempX = getCord(xCord);
 576             tempY = getCord(yCord);
 577             $.each(data,function(key,value) {
 578                 series[0].data.push({ name: value[dataLabel], x: tempX[0][it], y: tempY[0][it], marker: {fillColor: colorCodes[it % 8]} , id: it } );
 579                 it++;
 580             });
 581             currentSettings.xAxis.labels = { formatter : function() {
 582                     if(tempX[1][this.value] && tempX[1][this.value].length > 10)
 583                         return tempX[1][this.value].substring(0,10)
 584                     else
 585                         return tempX[1][this.value];
 586                 }
 587             }
 588             currentSettings.yAxis.labels = { formatter : function() {
 589                     if(tempY[1][this.value] && tempY[1][this.value].length > 10)
 590                         return tempY[1][this.value].substring(0,10)
 591                     else
 592                         return tempY[1][this.value];
 593                 }
 594             }
 595             xCord = tempX[2];
 596             yCord = tempY[2];
 597
 598         }
 599
 600         currentSettings.series = series;
 601         currentChart = PMA_createChart(currentSettings);
 602         scrollToChart();
 603     }
 604 });