高图表日期选择器

Question

>01( 我有以下 SQL 语句生成输出，如下面提到的。

SELECT DATE(a1.date_time) AS date, 
        a4.ph1_active_energy - a1.ph1_active_energy AS 'ph1',
        a4.ph2_active_energy - a1.ph2_active_energy AS 'ph2',
        a4.ph3_active_energy - a1.ph3_active_energy AS 'ph3'
    FROM powerpro a1
    JOIN (SELECT DATE(date_time) date, MIN(date_time) AS min 
            FROM powerpro GROUP BY DATE(date_time)
           ) a2 ON a1.date_time = a2.min 
    JOIN (SELECT DATE(date_time) date, MIN(date_time) AS min 
            FROM powerpro GROUP BY DATE(date_time)
           ) a3 ON DATE(a1.date_time) = a3.date - INTERVAL 1 DAY 
    JOIN powerpro a4
            ON a4.date_time = a3.min
    WHERE  DATE(a1.date_time) BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()  ORDER BY a1.date_time 

生成的输出

+------------+------+------+------+
|   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(此外，我希望使用以下代码从日期选择器中选择两个日期，并希望如果我选择2014-11-27作为Date1，那么它应该自动选择2014-11-29作为Date2，方法是向 #datepicker1 添加2天。之后，我想选择ph1，ph2和ph3值，包括在所选日期范围内。

    <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(删除了一些数据以减少记录数(

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 INTO `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);

和datae.php文件如下：

<?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) AS date, 
    a4.ph1_active_energy - a1.ph1_active_energy AS 'ph1',
    a4.ph2_active_energy - a1.ph2_active_energy AS 'ph2',
    a4.ph3_active_energy - a1.ph3_active_energy AS 'ph3'
FROM powerpro1 a1
JOIN (SELECT DATE(date_time) date, MIN(date_time) AS min 
        FROM powerpro1 GROUP BY DATE(date_time)
       ) a2 ON a1.date_time = a2.min 
JOIN (SELECT DATE(date_time) date, MIN(date_time) AS min 
        FROM powerpro1 GROUP BY DATE(date_time)
       ) a3 ON DATE(a1.date_time) = a3.date - INTERVAL 1 DAY 
JOIN powerpro1 a4
        ON a4.date_time = a3.min
WHERE DATE(a1.date_time) LIKE '".$_GET["dateParam"]."%' ORDER BY a1.date_time");//AND '".$_GET["dateParam1"]."%' 
} else {
    $query = mysql_query("SELECT DATE(a1.date_time) AS date, 
    a4.ph1_active_energy - a1.ph1_active_energy AS 'ph1',
    a4.ph2_active_energy - a1.ph2_active_energy AS 'ph2',
    a4.ph3_active_energy - a1.ph3_active_energy AS 'ph3'
FROM powerpro1 a1
JOIN (SELECT DATE(date_time) date, MIN(date_time) AS min 
        FROM powerpro1 GROUP BY DATE(date_time)
       ) a2 ON a1.date_time = a2.min 
JOIN (SELECT DATE(date_time) date, MIN(date_time) AS min 
        FROM powerpro1 GROUP BY DATE(date_time)
       ) a3 ON DATE(a1.date_time) = a3.date - INTERVAL 1 DAY 
JOIN powerpro1 a4
        ON a4.date_time = a3.min
WHERE  DATE(a1.date_time) BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()  ORDER BY 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);
?> 

谁能帮我

Answer 1

带有日期选择器的 LARAVEL 高图表 选择日期：

例：

<script>
    $(document).ready(function() {
        var startDatePicker = $('#startDatePicker').val();
        var endDatePicker = $('#endDatePicker').val();
        metabolicScoreAjax(startDatePicker, endDatePicker)
    });
    function metabolicScoreAjax(startDatePicker, endDatePicker) {
        $.ajaxSetup({
            headers: {
                "X-CSRF-TOKEN": $('meta[name="csrf-token"]').attr("content"),
            },
        });
        $.ajax({
            type: 'POST',
            data: {
                "selectedDate": "",
                "from": startDatePicker,
                "to": endDatePicker
            },
            url: base_url_metabolic,
            cache: false,
            // contentType: false,
            // processData: false,
            dataType: "JSON",
            success: function(response) {
                console.log(response);
                ScoreChart(response.data);
            },
            error: function() {
                ScoreChart(response.data);
            }
        });
    }
    function metabolicScoreChart(data) {
        var series = [{
                name: "Average Metabolic Score",
                data: [],
                marker: {
                    symbol: 'circle',
                    fillColor: '#62CE55'
                },
                dataLabels: {
                    enabled: true,
                    format: '{point.y}'
                },
                name: " Metabolic Score",
                legend: {
                    symbol: 'circle',
                },
                color: "#62CE55"
            },
            {
                name: "Metabolic Score",
                data: [],
                marker: {
                    symbol: 'circle',
                    fillColor: "#FF6B48"
                },
                type: 'spline',
                dataLabels: {
                    enabled: true,
                    format: '{point.y}'
                },
                name: "Cumulative Avg. Score",
                legend: {
                    symbol: 'circle',
                },
                color: "#FF6B48",
            }
        ];
        data.forEach(function(dataEl) {
            series[0].data.push([
                new Date(dataEl.date).getTime(),
                Number(dataEl.max)
            ]);
            series[1].data.push([
                new Date(dataEl.date).getTime(),
                Number(dataEl.average)
            ]);
        });
        Highcharts.chart('metabolicScore', {
            chart: {
                type: 'areaspline',
                scrollablePlotArea: {
                    minWidth: 3000,
                    scrollPositionX: 1,
                    opacity: 10
                }
            },
            title: {
                text: '1. Metabolic Score',
                align: "left",
                margin: 30
            },
            legend: {
                align: 'left',
                verticalAlign: 'top',
                layout: 'horizontal',
                padding: 3,
                itemMarginBottom: 20,
                ltr: true,
            },
            xAxis: {
                type: "category",
                startOnTick: true,
                endOnTick: true,
                labels: {
                    formatter: function() {
                        return Highcharts.dateFormat('%e %b, %Y', this.value);
                    }
                },
                tickInterval: 24 * 3600 * 1000,
            },
            yAxis: {
                title: {
                    text: 'Metabolic Score'
                },
                tickLength: 0,
                allowDecimals: true,
                labels: {
                    format: '{value}'
                },
                gridLineDashStyle: 'longdash'
            },
            tooltip: {
                borderRadius: 5,
                useHTML: true,
                formatter: function() {
                    return '<div class="tooltip_box"><div><span class="seriesHeading">' + Highcharts
                        .dateFormat('%e %b, %Y', this.x) +
                        '</span> </div> <span class="seriesName">' + this.series.name +
                        ': </span><span class="seriesPoint" style="color:' + this.series.color + '; ">' +
                        this
                        .y + '</span></div>';
                }
            },
            plotOptions: {
                areaspline: {
                    fillOpacity: 0.1
                }
            },
            series: series,
            credits: {
                enabled: false
            },
            navigation: {
                buttonOptions: {
                    theme: {
                        style: {
                            fontSize: '20px',
                            color: '#888',
                            item: "-20px"
                        },
                        states: {
                            hover: {
                                style: {
                                    color: '#000'
                                }
                            }
                        }
                    },
                    useHTML: true
                }
            },
            exporting: {
                buttons: {
                    contextButton: {
                        enabled: false
                    },
                    exportButton: {
                        text: '<i class="fa fa-download"></i>',
                        // Use only the download related menu items from the default
                        // context button
                        menuItems: [
                            'downloadXLS',
                            'downloadCSV',
                        ]
                    }
                }
            }
        });
    }
</script>