jquery - Highcharts Datepicker -
01) have following sql statement generates output mentioned bellow.
select date(a1.date_time) date, a4.ph1_active_energy - a1.ph1_active_energy 'ph1', a4.ph2_active_energy - a1.ph2_active_energy 'ph2', a4.ph3_active_energy - a1.ph3_active_energy 'ph3' powerpro a1 join (select date(date_time) date, min(date_time) min powerpro group date(date_time) ) a2 on a1.date_time = a2.min join (select date(date_time) date, min(date_time) min powerpro group date(date_time) ) a3 on date(a1.date_time) = a3.date - interval 1 day join powerpro a4 on a4.date_time = a3.min date(a1.date_time) between date_sub(now(), interval 30 day) , now() order a1.date_time generated output
+------------+------+------+------+ | date | ph1 | ph2 | ph3 | +------------+------+------+------+ | 2014-11-26 | 38 | 11 | 20 | | 2014-11-27 | 20 | 5 | 12 | | 2014-11-28 | 15 | 31 | 28 | | 2014-11-29 | 30 | 37 | 30 | | 2014-11-30 | 15 | 7 | 14 | | 2014-12-04 | 11 | 15 | 29 | +------------+------+------+------+ 02) further, wish use following code select 2 dates datepicker , want if select 2014-11-27 date1 should automatically select 2014-11-29 date2 adding 2 days #datepicker1. after want select ph1, ph2 , ph3 values including in selected date range.
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.1/themes/base/jquery-ui.css" /> <link rel="stylesheet" href="/resources/demos/style.css" /> <script src="http://code.jquery.com/jquery-1.9.1.js"></script> <script src="http://code.jquery.com/ui/1.10.1/jquery-ui.js"></script> <script type="text/javascript" src="js/highstock.js" ></script> <script type="text/javascript" src="js/highcharts.js" ></script> <script type="text/javascript" src="js/themes/dark-green.js"></script> <script type="text/javascript" src="js/modules/exporting.js"></script> <script type="text/javascript"> var options; $(document).ready(function() { options = { chart: { renderto: 'container', type: 'column', zoomtype: 'x', }, title: { text: '' }, colors: [ '#fe2712', '#fefe33', '#0247fe' ], subtitle: { text: '' }, xaxis: { categories: [], labels: { align: 'center', x: -3, y: 20, formatter: function() { return highcharts.dateformat('%y-%m-%d', date.parse(this.value +' utc')); } } }, xaxis: { type: 'datetime' }, title: { text: 'energy - kwh', x: -20 //center }, xaxis: { datetimelabelformats: { day: '%a, %e of %b' } }, yaxis: { title: { text: '' } }, tooltip: { enabled: false, formatter: function() { return '<b>'+ this.series.name +'</b><br/>'+ this.x +': '+ this.y; } }, plotoptions: { column: { datalabels: { enabled: true }, enablemousetracking: false } }, plotoptions: { series: { bordercolor: '#303030' } }, legend: { layout: 'center ', align: 'right', verticalalign: 'top', x: -10, y: 100, borderwidth: 5 }, navigator: { enabled: true }, series: [{ type: 'column', name: '', data: [] }] } $.getjson("datae.php", function(json){ options.xaxis.categories = json[0]['data']; options.series[0] = json[1]; options.series[1] = json[2]; options.series[2] = json[3]; chart = new highcharts.chart(options); }); }); function refresh() { $.getjson("datae.php", function(json){ options.xaxis.categories = json[0]['data']; options.series[0] = json[1]; options.series[1] = json[2]; options.series[2] = json[3]; chart = new highcharts.chart(options); }); } <script type="text/javascript"> $(function () { $("#datepicker1, #datepicker2").datepicker({ dateformat: "yy-mm-dd", showon: "button", buttonimage: "calendar.gif", buttonimageonly: true, }); $("#datepicker1").datepicker("option", "onselect", function (datetext, inst) { var date1 = $.datepicker.parsedate(inst.settings.dateformat || $.datepicker._defaults.dateformat, datetext, inst.settings); var date2 = new date(date1.gettime()); date2.setdate(date2.getdate() + 2); $.getjson("datae.php?dateparam="+datetext, function(json){ options.xaxis.categories = json[0]['data']; options.series[0] = json[1]; options.series[1] = json[2]; options.series[2] = json[3]; chart = new highcharts.chart(options); }); }); }); </script> <p>select date1: <input type="text" id="datepicker1" /><br><input type="button" value="refresh" onclick="refresh();" /></p> <p>select date2: <input type="text" id="datepicker2" /><br><input type="button" value="refresh" onclick="refresh();" /></p> mysql ddl , mml (deleted data reduce no of records)
create table if not exists `powerpro1` ( `record_no` int(11) not null auto_increment, `date_time` datetime default null, `ph1_active_energy` float default null, `ph2_active_energy` float default null, `ph3_active_energy` float default null, `ph1_active_power` float default null, `ph2_active_power` float default null, `ph3_active_power` float default null, primary key (`record_no`) ) engine=innodb default charset=latin1 auto_increment=21 ; insert `powerpro1` (`record_no`, `date_time`, `ph1_active_energy`, `ph2_active_energy`, `ph3_active_energy`, `ph1_active_power`, `ph2_active_power`, `ph3_active_power`) values ('2013-02-01 00:00:00', 1606, 230, 234, null, null, null), ('2013-02-01 01:00:00', 1607, 235, 238, null, null, null), ( '2013-02-01 02:00:00', 1877, 232, 255, null, null, null), ( '2013-02-01 03:00:00', 1387, 255, 230, null, null, null), ( '2014-11-26 08:00:00', 122, 145, 140, null, null, null), ( '2014-11-27 09:00:00', 160, 156, 160, null, null, null), ( '2014-11-27 10:00:00', 125, 144, 112, null, null, null), ( '2014-11-28 11:00:00', 180, 161, 172, null, null, null), ( '2013-11-28 12:00:00', 1847, null, null, null, null, null), ( '2014-11-29 13:00:00', 195, 192, 200, null, null, null), ( '2014-11-29 14:00:00', 1815, null, null, null, null, null), ( '2014-11-30 15:00:00', 225, 229, 230, -22.2, 22, 24), ( '2014-11-30 16:00:00', 1820, null, null, 22, 30, 23), ( '2014-12-01 17:00:00', 240, 236, 244, 18, 16, -20), ( '2014-12-01 18:00:00', 240, 244, 238, 23, 30, 14), ( '2014-12-04 09:00:00', 222, 219, 211, 22, 20, 16), ( '2014-12-04 10:00:00', 1340, 2300, 2345, -22.2, 30, 26), ( '2014-12-04 21:00:00', 1200, 2220, 234, 22.2, -12, 11), ( '2014-12-05 22:00:00', 230, 400, 450, -22.2, 23, 6), ( '2014-12-05 10:08:10', 233, 234, 240, -44.44, 30.3, 6); and datae.php file follows:
<?php $con = mysql_connect("localhost","powerproadmin","powerpro123"); if (!$con) { die('could not connect: ' . mysql_error()); } mysql_select_db("powerpro", $con); if (isset($_get["dateparam"])) { //&& $_get["dateparam1"] $query = mysql_query("select date(a1.date_time) date, a4.ph1_active_energy - a1.ph1_active_energy 'ph1', a4.ph2_active_energy - a1.ph2_active_energy 'ph2', a4.ph3_active_energy - a1.ph3_active_energy 'ph3' powerpro1 a1 join (select date(date_time) date, min(date_time) min powerpro1 group date(date_time) ) a2 on a1.date_time = a2.min join (select date(date_time) date, min(date_time) min powerpro1 group date(date_time) ) a3 on date(a1.date_time) = a3.date - interval 1 day join powerpro1 a4 on a4.date_time = a3.min date(a1.date_time) '".$_get["dateparam"]."%' order a1.date_time");//and '".$_get["dateparam1"]."%' } else { $query = mysql_query("select date(a1.date_time) date, a4.ph1_active_energy - a1.ph1_active_energy 'ph1', a4.ph2_active_energy - a1.ph2_active_energy 'ph2', a4.ph3_active_energy - a1.ph3_active_energy 'ph3' powerpro1 a1 join (select date(date_time) date, min(date_time) min powerpro1 group date(date_time) ) a2 on a1.date_time = a2.min join (select date(date_time) date, min(date_time) min powerpro1 group date(date_time) ) a3 on date(a1.date_time) = a3.date - interval 1 day join powerpro1 a4 on a4.date_time = a3.min date(a1.date_time) between date_sub(now(), interval 30 day) , now() order a1.date_time"); } $category = array(); $category['name'] = 'date_time'; $series1 = array(); $series1['name'] = 'phase 1'; $series2 = array(); $series2['name'] = 'phase 2'; $series3 = array(); $series3['name'] = 'phase 3'; while($r = mysql_fetch_array($query)) { $category['data'][] = $r['date']; $series1['data'][] = $r['ph1']; $series2['data'][] = $r['ph2']; $series3['data'][] = $r['ph3']; } $result = array(); array_push($result,$category); array_push($result,$series1); array_push($result,$series2); array_push($result,$series3); print json_encode($result, json_numeric_check); mysql_close($con); ?> can me
Comments
Post a Comment