javascript - 如何在堆叠条形图中为每个日期范围显示多个堆叠列

Question

本质上，目的是比较我的团队中每个成员每周执行的任务类别。

我想在轴下方添加周数而不是 1.0 、 2.0 和 3.0 。

我得到了错误的结果，因为它只显示 1 表示优秀，而且所有第 1 周、第 2 周、第 3 周、第 4 周

我选择的开始日期是 2014 年 1 月 12 日，结束日期是 2014 年 12 月 31 日，所以我希望有 1 个表示差，2 个表示好，3 个表示优秀。

这是我的PHP代码

<?php>
$result = mysqli_query($con,"SELECT * FROM `employees` WHERE  `Date` BETWEEN  '" . $_POST     
['start'] . "' AND  '" . $_POST ['end'] . "' ") or die ("Error: ".mysqli_error($con));

$Levels = 0;
        $Levelscounter=0;
        $countergood=0;
        $counterbad=0;
        while($row = mysqli_fetch_array($result))
         {
            $answer = $row['level'];


                 $bad = 'bad';
                 $good='good';
                 $excellent='excellent';
                  if ($answer == $bad)

             {
                $counterbad++;
             }
             if($answer == $good)
             {
               $countergood++;
             }
             if($answer == $excellent)
             {
               $counterexcellent++;
             }
             $Levelscounter;
                        }
             mysqli_close($con); 
        ?>

这是我的 JavaScript 代码:

       <script type="text/javascript">
         (function($) {
          var series = [{
          data: [[ 1,<?php echo $counterbad; ?>] ],
               valueLabels: {
               show: true,
               valign: 'middle'
                }     ,
         label: "Low"
         },
         {
       data: [[1,<?php echo $countergood; ?>]], 
           valueLabels: {
    show: true,
    valign: 'middle'
}     ,

    label: "Medium"
   }, {
    data: [[1,<?php echo $counterexcellent; ?>]],
     valueLabels: {
    show: true,
    valign: 'middle'
   }     ,
    label: "High"
   }];

    var options = {
    xaxis: {
        minTickSize: 1
    },
    series: {
        bars: {
            show: true,
            barWidth: .8,
            align: "center"
        },
        stack: true
    }
   };

  $.plot("#placeholder", series, options);
  })(jQuery);
 </script>

我得到了堆叠图表中显示的级别，从我选择的开始和结束日期开始，它工作正常，但我可以将此结果显示为每周结果。我想知道我需要添加什么？有什么想法吗？PHP 中是否有一个函数可以解决这个问题？

谢谢。

更新中

正如您在每个系列的图像编号中看到的那样，与差、好、优秀相关。红色代表坏，蓝色代表好，黄色代表优秀。

Answer 1

添加weeks/textual列下方的数据，您必须将库的类别文件 jquery.flot.categories.min.js 添加到您的 javascript Assets 中。

如果我理解正确的话，你希望图表看起来像这样

Javascript

你需要将这些文件添加到

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="jquery.flot.min.js"></script>
<script src="jquery.flot.categories.min.js"></script>
<script src="jquery.flot.stack.min.js"></script>

并初始化库我们将在这段代码之后讨论$output

<div id="placeholder" style="width:818px;height:413px" ></div>
<script type="text/javascript">
$(function() {
    var series = [<?php echo $output; ?>];
       $.plot("#placeholder", series, {
        series: {
            stack:true,
            lines:{fill:true,show:false,steps:false},
            bars: {
                show: true,
                barWidth: 0.8,
                align: "middle",
            },
        },
        xaxis: {
            mode: "categories",
            minTickSize: 1
        }
       });
});

PHP

首先您需要查询数据库以找到指定日期之间的日期，得到结果后您必须将每周的数据排序到一个数组中

例如 第一周 => 'good','good','bad','bad', 'week two' => 等等...

之后你可以使用array_count_values()计算出现的次数并构建图表列。

我使用函数简化了代码，让您更轻松

<?php
$con = mysqli_connect("localhost", 'root','','your db');

function getChartData($con, $startDate, $endDate){

    $startDate = date("Y-m-d H:i:s", strtotime($startDate));
    $endDate = date("Y-m-d H:i:s", strtotime($endDate));

    $query = "SELECT * FROM `employees` WHERE `date` BETWEEN '$startDate' AND '$endDate'";

    $result = mysqli_query($con, $query) or die ("Error: ".mysqli_error($con));

    // a multidimenional array containing each week with it's
    $weeksData = [];

    // Group each week with it's data 
    while($row = mysqli_fetch_array($result)){
        $weekNumber = date("W", strtotime($row['date']));
        if(isset($weeksData[$weekNumber]))
        {
            $weeksData[$weekNumber][] = $row['level'];
        }
        $weeksData[$weekNumber][] = $row['level'];
    }

    // reset array indexes and sort the array
    sort($weeksData);

    $data = array();

    // using array_count_values to count the number of (good, bad and excellent)
    foreach ($weeksData as $key => $week) {
        $data[$key] = array_count_values($week);
    }

    // return all the weeks with number of (good, bad and excellent) occurences 
    return $data;
}

// build the javascript object {data:['week num', occuerences]}
function buildColumn($data,$label, $numberOfWeeks)
{
    $data = array_column($data,strtolower($label));
    $balance = $numberOfWeeks - count($data);
    if($balance !=0){ 
        for($i=1;$i<=$balance;$i++) { 
            $data[] = 1; 
        } 
    }

    $string = '{data: [';
    foreach ($data as $key => $value) {
        $weekNumber = $key+1;
        $string .= '["Week '.$weekNumber.'",'.$value.'],';
    }
    $string = rtrim($string, ',');
    $string .= "],valueLabels: {show: true,valign: 'middle'},label: '$label'}";
    return $string;
}

function getNumberofWeeks($startDate, $endDate){
      $weeks = array();
      $period = new DatePeriod(new DateTime($startDate),
        DateInterval::createFromDateString('+1 day'),new DateTime($endDate) 
      );
      foreach ( $period as $dt ) {
        $weeks[] = $dt->format( 'W' );
      }
      return count(array_unique($weeks));
}  

现在你可以像这样轻松地使用这些函数了

$numberOfWeeks = getNumberofWeeks($_POST['start'],$_POST['end']);

// get data of the last number of weeks
$chartData = getChartData($con, $_POST['start'],$_POST['end']);
// bulding columns data for each occurence
$badColumn = buildColumn($chartData,'Bad', $numberOfWeeks);
$goodColumn = buildColumn($chartData,'Good', $numberOfWeeks);
$excellentColumn = buildColumn($chartData,'Excellent', $numberOfWeeks);

// output {data: ...}, {data: ...},{data:....}
$output = "$excellentColumn , $goodColumn , $badColumn";  

完整的工作示例

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    <script src="jquery.flot.min.js"></script>
    <script src="jquery.flot.categories.min.js"></script>
    <script src="jquery.flot.stack.min.js"></script>
</head>
<body>
<?php
$con = mysqli_connect("localhost", 'root','','your db');

function getChartData($con, $startDate, $endDate){

    $startDate = date("Y-m-d H:i:s", strtotime($startDate));
    $endDate = date("Y-m-d H:i:s", strtotime($endDate));

    $query = "SELECT * FROM `employees` WHERE `date` BETWEEN '$startDate' AND '$endDate'";

    $result = mysqli_query($con, $query) or die ("Error: ".mysqli_error($con));

    // a multidimenional array containing each week with it's
    $weeksData = [];

    // Group each week with it's data 
    while($row = mysqli_fetch_array($result)){
        $weekNumber = date("W", strtotime($row['date']));
        if(isset($weeksData[$weekNumber]))
        {
            $weeksData[$weekNumber][] = $row['level'];
        }
        $weeksData[$weekNumber][] = $row['level'];
    }

    // reset array indexes and sort the array
    sort($weeksData);

    $data = array();

    // using array_count_values to count the number of (good, bad and excellent)
    foreach ($weeksData as $key => $week) {
        $data[$key] = array_count_values($week);
    }

    // return all the weeks with number of (good, bad and excellent) occurences 
    return $data;
}

// build the javascript object {data:['week num', occuerences]}
function buildColumn($data,$label, $numberOfWeeks)
{
    $data = array_column($data,strtolower($label));
    $balance = $numberOfWeeks - count($data);
    if($balance !=0){ 
        for($i=1;$i<=$balance;$i++) { 
            $data[] = 1; 
        } 
    }

    $string = '{data: [';
    foreach ($data as $key => $value) {
        $weekNumber = $key+1;
        $string .= '["Week '.$weekNumber.'",'.$value.'],';
    }
    $string = rtrim($string, ',');
    $string .= "],valueLabels: {show: true,valign: 'middle'},label: '$label'}";
    return $string;
}

function getNumberofWeeks($startDate, $endDate){
      $weeks = array();
      $period = new DatePeriod(new DateTime($startDate),
        DateInterval::createFromDateString('+1 day'),new DateTime($endDate) 
      );
      foreach ( $period as $dt ) {
        $weeks[] = $dt->format( 'W' );
      }
      return count(array_unique($weeks));
}
// the number of weeks that you want to display in the chart
$numberOfWeeks = getNumberofWeeks($_POST['start'],$_POST['end']);

// get data of the last number of weeks
$chartData = getChartData($con, $_POST['start'],$_POST['end']);
// bulding columns data for each occurence
$badColumn = buildColumn($chartData,'Bad', $numberOfWeeks);
$goodColumn = buildColumn($chartData,'Good', $numberOfWeeks);
$excellentColumn = buildColumn($chartData,'Excellent', $numberOfWeeks);

// output {data: ...}, {data: ...},{data:....}
$output = "$excellentColumn , $goodColumn , $badColumn";

?>
<div id="placeholder" style="width:818px;height:413px" ></div>
<script type="text/javascript">
$(function() {
    var series = [<?php echo $output; ?>];
       $.plot("#placeholder", series, {
        series: {
            stack:true,
            lines:{fill:true,show:false,steps:false},
            bars: {
                show: true,
                barWidth: 0.8,
                align: "middle",
            },
        },
        xaxis: {
            mode: "categories",
            minTickSize: 1
        }
       });
});
  </script>
 </body>
</html> 

编辑

只需替换这两个函数，使其兼容dd/mm/yyyy

 function getChartData($con, $startDate, $endDate){
    $startDate = explode('/', $startDate);
    $startDate = $startDate[1] . '/' . $startDate[0] . '/' . $startDate[2];

    $endDate = explode('/', $endDate);
    $endDate = $endDate[1] . '/' . $endDate[0] . '/' . $endDate[2];

    $startDate = date("Y-m-d H:i:s", strtotime($startDate));
    $endDate = date("Y-m-d H:i:s", strtotime($endDate));

    $query = "SELECT * FROM `employees` WHERE `date` BETWEEN '$startDate' AND '$endDate'";

    $result = mysqli_query($con, $query) or die ("Error: ".mysqli_error($con));

    // a multidimenional array containing each week with it's
    $weeksData = [];

    // Group each week with it's data 
    while($row = mysqli_fetch_array($result)){
        $weekNumber = date("W", strtotime($row['date']));
        if(isset($weeksData[$weekNumber]))
        {
            $weeksData[$weekNumber][] = $row['level'];
        }
        $weeksData[$weekNumber][] = $row['level'];
    }

    // reset array indexes and sort the array
    sort($weeksData);

    $data = array();

    // using array_count_values to count the number of (good, bad and excellent)
    foreach ($weeksData as $key => $week) {
        $data[$key] = array_count_values($week);
    }

    // return all the weeks with number of (good, bad and excellent) occurences 
    return $data;
}

和

   function getNumberofWeeks($startDate, $endDate){
    $startDate = explode('/', $startDate);
    $startDate = $startDate[1] . '/' . $startDate[0] . '/' . $startDate[2];

    $endDate = explode('/', $endDate);
    $endDate = $endDate[1] . '/' . $endDate[0] . '/' . $endDate[2];
    $diff = strtotime($startDate, 0) - strtotime($endDate, 0);

    return str_replace('-','', (int)floor($diff / 604800));
}