Home / Questions / 446354
Accepted
Miguel
Asked: 2022-04-23 09:13:35 +0800 CST 2022-04-23 09:13:35 +0800 CST

Bug with dates in chart.js

  • 772

Good afternoon, I'm iterating the records of a database in charts.js charts . I take a created field that is called fecha_contactosince it is a database of clients of a company and they may want to add clients with a contact date different from that of created_at.

I manage it as follows:

$result = [];
$fecha = new DateTime("now");
$fecha->modify('first day');
$fecha->modify('-11 months');
  for ($i = 0; $i < 12; $i++) {
   $result[$fecha->format('Y-M')] = 0;
     $fecha->modify('+1 month');
  }

$data = Cliente::select(DB::raw('count(*) as total, fecha_contacto'))->where('marca', 0)->whereIn('tipo_cliente', [0, 3])->groupBy('fecha_contacto')->get();

  foreach($data as $t) {
    $fechas = date('Y-M', strtotime($t->fecha_contacto));
    if (isset($result[$fechas]) === true) {
     $result[$fechas] += $t->total;
    } else {
     $result[$fechas] = $t->total;
   }
  }

return view('clientesPotenciales')->with(["result" => $result]);

Until today I thought it worked fine, but today they have inserted clients who contacted them in 2017 and now it puts January 2017 in front of April 2021 in the graphs. I attached a photo to make it clearer.

enter image description here

What am I doing wrong? In theory, I'm pulling records for the last 12 months, right?

This is what I receive for php at a glance: Actually I send three different queries, but I put only one since they are the same but with different parameters.

array:13 [▼
  "2020-May" => 0
  "2020-Jun" => 22
  "2020-Jul" => 12
  "2020-Aug" => 14
  "2020-Sep" => 19
  "2020-Oct" => 15
  "2020-Nov" => 15
  "2020-Dec" => 4
  "2021-Jan" => 13
  "2021-Feb" => 9
  "2021-Mar" => 1
  "2021-Apr" => 2
  "2017-Jan" => 1
]

As you can see it puts me "2017-Jan" => 1in last position, I don't understand why.

Then in the view:

<div class="card" style="padding-bottom: 0px;padding-top: 0px;">
   <canvas id="clientes"></canvas>
</div>

And the JavaScript:

<script>
        var ctx = document.getElementById('clientes').getContext('2d');
        var clientes = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: {!! json_encode(array_keys($result)) !!},
            datasets: [{
                label: 'Depildiodo',
                data: {!! json_encode(array_values($result)) !!},
                backgroundColor: "rgb(85 51 255 / 48%)",
                borderColor: "#53f",
                borderWidth: 1,
                hoverBackgroundColor: "#53f",
                hoverBorderColor: "#53f",
                fill: false,
                pointBorderWidth: 1
            }]
        },
        plugins: [{
            beforeInit: function(chart, options) {
              chart.legend.afterFit = function() {
                this.height = this.height + 30;
              };
            }
        }],
        options: {
            responsive: true,
            maintainAspectRatio: false,
            scales: {
                yAxes: [
                    {
                    ticks: {
                        fontColor: "#adb0b5",
                        beginAtZero: true,
                        maxTicksLimit: 4,
                        padding: 20
                    },
                    gridLines: {
                        display: true,
                        color: "rgb(173 176 181 / 14%)"
                      }
                    }
                ],
                xAxes: [
                    {
                    ticks: {
                        padding: 20,
                        fontColor: "#adb0b5",
                    },
                    gridLines: {
                        display: true,
                        color: "rgb(173 176 181 / 14%)"
                        }
                    }
                ]
            },
            tooltips: {
                callbacks: {
                    labelColor: function(tooltipItem, chart) {
                        return {
                            borderColor: '#ccc',
                            backgroundColor: '#ccc'
                        };
                    },
                    labelTextColor: function(tooltipItem, chart) {
                        return '#fff';
                    }
                }
            },
            legend: {
                display: true,
                    
                labels: {
                    fontColor: '#a1aab2',
                    padding: 20,
                }
            }
            
        }
    });
  </script>
php

1 Answers

  1. Best Answer

    With the following code you are just creating an array of dates, but you are not actually filtering the query.

    $result = [];
$fecha = new DateTime("now");
$fecha->modify('first day');
$fecha->modify('-11 months');
  for ($i = 0; $i < 12; $i++) {
   $result[$fecha->format('Y-M')] = 0;
     $fecha->modify('+1 month');
  }

    Depending on the data type of the column, fecha_contactoyou must set the values ​​to filter:

    • Format for type date: Y-m-d,
    • Format for type datetime:Y-m-d H:i:s

    For type dateyou only need the date, it is used tto get the last day of the month, without having to modify:

    $fecha_inicio = (new DateTime('now'))->modify('first day')->modify('-11 months')->format('Y-m-d');
$fecha_fin = (new DateTime('now'))->format('Y-m-t');

    For type datetimeyou must consider the first moment of the day for the start date and the last for the end date:

    $fecha_inicio = (new DateTime('now'))->modify('first day')->modify('-11 months')->format('Y-m-d') . ' 00:00:00';
$fecha_fin = (new DateTime('now'))->format('Y-m-t') . ' 23:59:59';

    Then you add the filter to the query with ->whereBetween() :

    $data = Cliente::select(DB::raw('count(*) as total, fecha_contacto'))
    ->where('marca', 0)
    ->whereIn('tipo_cliente', [0, 3])
    ->whereBetween('fecha_contacto', [$fecha_inicio, $fecha_fin])
    ->groupBy('fecha_contacto')->get();

  foreach($data as $t) {
    $fechas = date('Y-M', strtotime($t->fecha_contacto));
    if (isset($result[$fechas]) === true) {
       $result[$fechas] += $t->total;
    } else {
       // Esto no debería pasar, porque se creó el arreglo solo con las fechas a mostrar
       $result[$fechas] = $t->total;
   }
  }

return view('clientesPotenciales')->with(["result" => $result]);

    It's not strictly necessary, but you could sort the results by date.

    • 4