how to query according to the value that is GET from selected date.

sashavalentina · April 27, 2021

I'm creating a line graph using ChartJs. I wanted to display my data according to the month selected by the user. For example, if the user select "2021-03" all the order that is made in the month of March will be plotted in the graph. It works well for the current month, but it does not work for the selected month.

I use Ajax to get the data but i do not know why my graph does not shown when i click on my `loadchart();` button. Can anyone enlighten me how can i do it? Because i do not know what mistake did i made here. Any kind of explanation will be appreciated. Thanks!

This is the where i place my `loadchart()` button and also the javascript to plot the graph.

  <div class="col-md-12 form-group">
            <div class="card" style="box-shadow: 0 0 8px rgb(0,0,0,0.3)">
                <div class="row">
                   <div class="col col-lg-1"></div>
                  <div class="col-2 my-auto">
                    <input type="month" id="month" class="form-control" value="<?php echo date('Y-m'); ?>">
                  </div>

                   <div class="col-2 my-auto">
                    <button type="button" class="btn btn-info" onclick="loadchart();">
                      <i class="nc-icon nc-zoom-split"></i>
                    </button>
                  </div>
                </div>
                <div id="loadchart">
              <h6 class="text-muted text-center p-3"><i class="fa fa-line-chart"></i> Daily Gross Sales (Current Month)</h6>
              <div  class="card-body">
                <canvas id="attChart"></canvas>
              </div>
            </div>
            </div>
          </div>

        </div>
      </div>
      
    </div>
  </div>
  
  <!-- Chart JS -->
  <script src="./assets/js/plugins/chartjs.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
  <?php
  $days = array();
  $gross_sales = array();
  $average_sales = array();

  $type = CAL_GREGORIAN;
  $month = date('n'); // Month ID, 1 through to 12.
  $year = date('Y'); // Year in 4 digit 2009 format.
  $day_count = cal_days_in_month($type, $month, $year); // Get the amount of days

  //loop through all days
  for ($i = 1; $i <= $day_count; $i++) {
    $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH(NOW()) AND YEAR(order_datetime) = YEAR(NOW()) AND order_status = 5 ";
    $query = $conn->query($sql);
    $total = $query->num_rows;
    $row = $query->fetch_assoc();

      
    if($row['purchase_price'] != 0)
    {      
      $gross_sales[] = $row['purchase_price'];
    }
      
    else if ($row['purchase_price'] == 0 && $i <= date('d')) {
      $gross_sales[] = 0;
    }

    $average_sales[] =  $row['purchase_price'] / $day_count;

    $day =  $i.'/'.$month; //format date
    array_push($days, $day);
  }

  $days = json_encode($days);
  $daily_gross_sales = json_encode($gross_sales);
  $average_gross_sales =  json_encode($average_sales);

?>
  <script>
    const colors = {
      colorcode: {
        fill: '#51cbce',
        stroke: '#51cbce',
      },
    
    };
    var ctx2 = document.getElementById("attChart").getContext("2d");
    const attChart = new Chart(ctx2, {
      type: 'line',
      data: {
        labels: <?php echo $days; ?>,
        
        //labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
          label: "Gross Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor: colors.colorcode.stroke,
          borderColor: colors.colorcode.stroke,
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $daily_gross_sales; ?>,
          showLine: true
        },
        {
          label: "Average Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor:   '#FF0000',
          borderColor: ' #FF0000 ',
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $average_gross_sales; ?>,
          showLine: true
        }

        ]
      },

      options: {
        responsive: true,
        
        // Can't just just `stacked: true` like the docs say
        scales: {
          yAxes: [{
            stacked: false,
            gridLines: {
              display: true,
              color: "#dee2e6"
            },
            ticks: {
              stepSize: 100000,
              callback: function(tick) {
                return 'RM'+tick.toString();
              }
            }
          }],
          
          xAxes: [{
            stacked: false,
            gridLines: {
              display: false
            },
          }]
          
        },
        animation: {
          duration: 3000,
        },
        
        legend:{
          display: false,
        }
      }
    });
  </script>

This is my AJAX method

   <script type="text/javascript">
    function loadchart()
    {
     $('#spin1').show();
     var month= $('#month').val();
     //alert(date);
      var data = {     // create object
        month: month,
        
      }
      $.ajax({
        method:"GET",
        data:data,
        url:"includes/loadchart.php",
        success:function(data)
        {
            $('#loadchart').html(data);
        }
      });

    }
  </script>

This is where i use to load the graph whenever a month is selected. (loadchart.php)

<?php
include '../session.php';
if(isset($_GET['month']))
{
	$months = $_GET['month'];
?>

 <h6 class="text-muted text-center p-3"><i class="fa fa-line-chart"></i> Daily Gross Sales (<?php echo $months ?>)</h6>
              <div id="loadchart" class="card-body">
                <canvas id="attChart"></canvas>
              </div>
            </div>
          </div>

        </div>
      </div>
      
    </div>
  </div>
  
  <!-- Chart JS -->
  <script src="./assets/js/plugins/chartjs.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

  <?php
  $days = array();
  $gross_sales = array();
  $average_sales = array();

  $type = CAL_GREGORIAN; // Gregorian (localized) calendar
  $month = date('n'); // Month ID, 1 through to 12.
  $year = date('Y'); // Year in 4 digit 2009 format.
 
  $day_count = cal_days_in_month($type, $month, $year); // Get the amount of days

  //loop through all days
  for ($i = 1; $i <= $day_count; $i++) {
      $sql = "SELECT *, SUM(purchase_price) as purchase_price FROM ordered_items WHERE DAY(order_datetime) = '$i' AND MONTH(order_datetime) = MONTH('".$months."') AND YEAR(order_datetime) = YEAR('".$months."') AND order_status = 5 ";
    $query = $conn->query($sql);
    $total = $query->num_rows;
    $row = $query->fetch_assoc();

      
    if($row['purchase_price'] != 0)
    {      
      $gross_sales[] = $row['purchase_price'];
    }
      
    else if ($row['purchase_price'] == 0 && $i <= date('d')) {
      $gross_sales[] = 0;
    }

    $average_sales[] =  $row['purchase_price'] / $day_count;

    $day =  $i.'/'.$month; //format date
    array_push($days, $day);
  }

  $days = json_encode($days);
  $daily_gross_sales = json_encode($gross_sales);
  $average_gross_sales =  json_encode($average_sales);

?>
  <script>
    const colors = {
      colorcode: {
        fill: '#51cbce',
        stroke: '#51cbce',
      },
    
    };
    var ctx2 = document.getElementById("attChart").getContext("2d");
    const attChart = new Chart(ctx2, {
      type: 'line',
      data: {
        labels: <?php echo $days; ?>,
        
        //labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
          label: "Gross Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor: colors.colorcode.stroke,
          borderColor: colors.colorcode.stroke,
          pointHighlightStroke: colors.colorcode.stroke,
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $daily_gross_sales; ?>,
          showLine: true
        },
        {
          label: "Average Sales: RM",
          fill: true,
          //backgroundColor: colors.colorcode.fill,
          pointBackgroundColor:   '#FF0000',
          borderColor: ' #FF0000 ',
          pointHighlightStroke: '#FF0000',
          borderCapStyle: 'dot',
          pointRadius: 5,
          pointHoverRadius: 5,
          pointStyle: 'dot',
          data: <?php echo $average_gross_sales; ?>,
          showLine: true
        }

        ]
      },

      options: {
        responsive: true,
        
        // Can't just just `stacked: true` like the docs say
        scales: {
          yAxes: [{
            stacked: false,
            gridLines: {
              display: true,
              color: "#dee2e6"
            },
            ticks: {
              stepSize: 100000,
              callback: function(tick) {
                return 'RM'+tick.toString();
              }
            }
          }],
          
          xAxes: [{
            stacked: false,
            gridLines: {
              display: false
            },
          }]
          
        },
        animation: {
          duration: 3000,
        },
        
        legend:{
          display: false,
        }
      }
    });
  </script>
  
<?php	
}
?>

Edited April 27, 2021 by sashavalentina

Sign In

how to query according to the value that is GET from selected date.

Recommended Posts

sashavalentina

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information