Codeigniter : Create Chart Report by date, week, month, year

Codeigniter : Create Chart Report by date, week, month, year

controllers Report.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Report extends CI_Controller
{

	public function __construct()
	{
		parent::__construct();
		$this->load->library('pagination');
		$this->load->model('ChartModel');
		$this->load->model('report_model');

	}


//report by date
	public function index()
	{
		$config = array();
		$config['base_url'] = base_url('report/index');
		$config['total_rows'] = $this->report_model->record_count($this->input->get('keyword'));
		$config['per_page'] = $this->input->get('keyword') == NULL ? 14 : 999;
		$config['uri_segment'] = 3;
		$choice = $config['total_rows'] / $config['per_page'];
		$config['num_links'] = round($choice);

		$this->pagination->initialize($config);

		$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
		$data['results'] = $this->report_model->fetch_report($config['per_page'], $page, $this->input->get('keyword'));
		$data['link'] = $this->pagination->create_links();
		$data['total_rows'] = $config['total_rows'];
		$this->load->view('template/backheader');
		$this->load->view('report/mainpage', $data);
		
		//$this->load->view('chartcontroller/line_chart');
		//$this->load->view('line_chart', $data);
		$this->load->view('template/backfooter');
	}



	public function r_y()
	{
		$config = array();
		$config['base_url'] = base_url('report/index');
		$config['total_rows'] = $this->report_model->record_count($this->input->get('keyword'));
		$config['per_page'] = $this->input->get('keyword') == NULL ? 14 : 999;
		$config['uri_segment'] = 3;
		$choice = $config['total_rows'] / $config['per_page'];
		$config['num_links'] = round($choice);

		$this->pagination->initialize($config);

		$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
		$data['r_y'] = $this->report_model->fetch_report_by_year($config['per_page'], $page, $this->input->get('keyword'));
		$data['link'] = $this->pagination->create_links();
		$data['total_rows'] = $config['total_rows'];
		$this->load->view('template/backheader');
		$this->load->view('report/mainpage_b_y', $data);
		
		//$this->load->view('chartcontroller/line_chart');
		//$this->load->view('line_chart', $data);
		$this->load->view('template/backfooter');
	}

public function r_m()
	{
		$config = array();
		$config['base_url'] = base_url('report/index');
		$config['total_rows'] = $this->report_model->record_count($this->input->get('keyword'));
		$config['per_page'] = $this->input->get('keyword') == NULL ? 14 : 999;
		$config['uri_segment'] = 3;
		$choice = $config['total_rows'] / $config['per_page'];
		$config['num_links'] = round($choice);

		$this->pagination->initialize($config);

		$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
		$data['r_m'] = $this->report_model->fetch_report_by_mo($config['per_page'], $page, $this->input->get('keyword'));
		$data['link'] = $this->pagination->create_links();
		$data['total_rows'] = $config['total_rows'];
		$this->load->view('template/backheader');
		$this->load->view('report/mainpage_b_m', $data);
		
		//$this->load->view('chartcontroller/line_chart');
		//$this->load->view('line_chart', $data);
		$this->load->view('template/backfooter');
	}

	public function r_w()
	{
		$config = array();
		$config['base_url'] = base_url('report/index');
		$config['total_rows'] = $this->report_model->record_count($this->input->get('keyword'));
		$config['per_page'] = $this->input->get('keyword') == NULL ? 14 : 999;
		$config['uri_segment'] = 3;
		$choice = $config['total_rows'] / $config['per_page'];
		$config['num_links'] = round($choice);

		$this->pagination->initialize($config);

		$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
		$data['r_w'] = $this->report_model->fetch_report_by_we($config['per_page'], $page, $this->input->get('keyword'));
		$data['link'] = $this->pagination->create_links();
		$data['total_rows'] = $config['total_rows'];
		$this->load->view('template/backheader');
		$this->load->view('report/mainpage_b_w', $data);
		
		//$this->load->view('chartcontroller/line_chart');
		//$this->load->view('line_chart', $data);
		$this->load->view('template/backfooter');
	}

	}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Report extends CI_Controller

{

public function __construct()

{

parent::__construct();

$this->load->library('pagination');

$this->load->model('ChartModel');

$this->load->model('report_model');

}

//report by date

public function index()

{

$config = array();

$config['base_url'] = base_url('report/index');

$config['total_rows'] = $this->report_model->record_count($this->input->get('keyword'));

$config['per_page'] = $this->input->get('keyword') == NULL ? 14 : 999;

$config['uri_segment'] = 3;

$choice = $config['total_rows'] / $config['per_page'];

$config['num_links'] = round($choice);

$this->pagination->initialize($config);

$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;

$data['results'] = $this->report_model->fetch_report($config['per_page'], $page, $this->input->get('keyword'));

$data['link'] = $this->pagination->create_links();

$data['total_rows'] = $config['total_rows'];

$this->load->view('template/backheader');

$this->load->view('report/mainpage', $data);

//$this->load->view('chartcontroller/line_chart');

//$this->load->view('line_chart', $data);

$this->load->view('template/backfooter');

}

public function r_y()

{

$config = array();

$config['base_url'] = base_url('report/index');

$config['total_rows'] = $this->report_model->record_count($this->input->get('keyword'));

$config['per_page'] = $this->input->get('keyword') == NULL ? 14 : 999;

$config['uri_segment'] = 3;

$choice = $config['total_rows'] / $config['per_page'];

$config['num_links'] = round($choice);

$this->pagination->initialize($config);

$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;

$data['r_y'] = $this->report_model->fetch_report_by_year($config['per_page'], $page, $this->input->get('keyword'));

$data['link'] = $this->pagination->create_links();

$data['total_rows'] = $config['total_rows'];

$this->load->view('template/backheader');

$this->load->view('report/mainpage_b_y', $data);

//$this->load->view('chartcontroller/line_chart');

//$this->load->view('line_chart', $data);

$this->load->view('template/backfooter');

}

public function r_m()

{

$config = array();

$config['base_url'] = base_url('report/index');

$config['total_rows'] = $this->report_model->record_count($this->input->get('keyword'));

$config['per_page'] = $this->input->get('keyword') == NULL ? 14 : 999;

$config['uri_segment'] = 3;

$choice = $config['total_rows'] / $config['per_page'];

$config['num_links'] = round($choice);

$this->pagination->initialize($config);

$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;

$data['r_m'] = $this->report_model->fetch_report_by_mo($config['per_page'], $page, $this->input->get('keyword'));

$data['link'] = $this->pagination->create_links();

$data['total_rows'] = $config['total_rows'];

$this->load->view('template/backheader');

$this->load->view('report/mainpage_b_m', $data);

//$this->load->view('chartcontroller/line_chart');

//$this->load->view('line_chart', $data);

$this->load->view('template/backfooter');

}

public function r_w()

{

$config = array();

$config['base_url'] = base_url('report/index');

$config['total_rows'] = $this->report_model->record_count($this->input->get('keyword'));

$config['per_page'] = $this->input->get('keyword') == NULL ? 14 : 999;

$config['uri_segment'] = 3;

$choice = $config['total_rows'] / $config['per_page'];

$config['num_links'] = round($choice);

$this->pagination->initialize($config);

$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;

$data['r_w'] = $this->report_model->fetch_report_by_we($config['per_page'], $page, $this->input->get('keyword'));

$data['link'] = $this->pagination->create_links();

$data['total_rows'] = $config['total_rows'];

$this->load->view('template/backheader');

$this->load->view('report/mainpage_b_w', $data);

//$this->load->view('chartcontroller/line_chart');

//$this->load->view('line_chart', $data);

$this->load->view('template/backfooter');

}

models Report_model.php

<?php

class Report_model extends CI_Model
{

	public function __construct()
	{
		parent::__construct();
	}


	public function fetch_report($value='')
	{
		$sql ="SELECT SUM(deposits) as total, `date` FROM booksdetail GROUP BY `date` ORDER BY `date` ASC";


		$rs = $this->db->query($sql)->result();
		return $rs;
	}

	
public function fetch_report_by_year($value='')
	{
		$sql ="SELECT SUM(deposits) as total, `date` FROM booksdetail GROUP BY DATE_FORMAT(`date`, '%Y%')";


		$rs = $this->db->query($sql)->result();
		return $rs;
	}

	public function fetch_report_by_mo($value='')
	{
		$sql ="SELECT SUM(deposits) as total, `date` FROM booksdetail GROUP BY DATE_FORMAT(`date`, '%m%')";


		$rs = $this->db->query($sql)->result();
		return $rs;
	}


	public function fetch_report_by_we($value='')
	{
	$sql ="SELECT SUM(deposits) as total, `date` FROM booksdetail GROUP BY WEEKOFYEAR(`date`)";


		$rs = $this->db->query($sql)->result();
		return $rs;
	}



	public function record_count($keyword)
	{
		//$this->db->like('date',$keyword);
		//SELECT SUM(deposits) as total FROM booksdetail GROUP BY `date`
		$this->db->from('booksdetail');
		return $this->db->count_all_results();
	}
}

<?php

class Report_model extends CI_Model

{

public function __construct()

{

parent::__construct();

}

public function fetch_report($value='')

{

$sql ="SELECT SUM(deposits) as total, `date` FROM booksdetail GROUP BY `date` ORDER BY `date` ASC";

$rs = $this->db->query($sql)->result();

return $rs;

}

public function fetch_report_by_year($value='')

{

$sql ="SELECT SUM(deposits) as total, `date` FROM booksdetail GROUP BY DATE_FORMAT(`date`, '%Y%')";

$rs = $this->db->query($sql)->result();

return $rs;

}

public function fetch_report_by_mo($value='')

{

$sql ="SELECT SUM(deposits) as total, `date` FROM booksdetail GROUP BY DATE_FORMAT(`date`, '%m%')";

$rs = $this->db->query($sql)->result();

return $rs;

}

public function fetch_report_by_we($value='')

{

$sql ="SELECT SUM(deposits) as total, `date` FROM booksdetail GROUP BY WEEKOFYEAR(`date`)";

$rs = $this->db->query($sql)->result();

return $rs;

}

public function record_count($keyword)

{

//$this->db->like('date',$keyword);

//SELECT SUM(deposits) as total FROM booksdetail GROUP BY `date`

$this->db->from('booksdetail');

return $this->db->count_all_results();

}

views report by day mainpage.php

<div class="content-wrapper">
    <!-- Content Header (Page header) -->
    <section class="content-header">
        <h1>
            รายงานยอดขายรายวัน
        </h1>
        <ol class="breadcrumb">
            <li><a href="<?php echo  base_url('report'); ?>"><i class="fa fa-dashboard"></i> หน้าแรก</a></li>
            <li class="active">-</li>
        </ol>
    </section>
    <!-- Top menu -->
    <!-- Main content -->
    <section class="content">
        <!-- Your Page Content Here -->
        <div class="box">
            <div class="box-body">
                <div id="example1_wrapper" class="dataTables_wrapper form-inline dt-bootstrap">
                    <div class="row">
                        <div class="col-sm-12">
                          
                         <br>
                            <table id="example1" class="table table-breported table-striped dataTable" role="grid" aria-describedby="example1_info">
                                <thead>
                                    <tr role="row">
                                    <th class="sorting" tabindex="0" rowspan="1" colspan="1">
                                    วันที่ 
                                        </th>
                                        <th class="sorting" tabindex="0" rowspan="1" colspan="1">ยอด/วัน 
                                        </th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php 

                                       $total_sum=0;

                                    if(!empty($results)){ foreach ($results as $data) { ?>
                                        <tr role="row">
                                           <td>
                                             <?php echo date('d/m/Y', strtotime($data->date)); ?>
                                             </td>
                                            <td>
                                             <b> 
                                             <?php echo number_format($data->total,2); ?> บาท 
                                             </b>
                                             </td>

                                             <?php  

                                                    
                                            $total_sum+=$data->total;

                                             ?>
                                           
                                        </tr>
      

                            <?php   } } ?>
                                    <font color="red">
                                   รวมยอดขายทั้งหมด <b>  <?php echo number_format($total_sum,2);?>  </b> บาท 
                                   </font> 

                                   <hr>
                                   <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.6.0/Chart.bundle.js"></script>


                                </tbody>

                            </table>

 <?php 
$beforyear = date('Y')-1; 
$thisyear = date('Y');
$nextyear = date('Y')+1;

 ?>                           
<hr>
<p align="center">
<canvas id="myChart" width="800px" height="200px"></canvas>
<script>
var ctx = document.getElementById("myChart").getContext('2d');
var myChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: [
        <?php 
        $i=0;
        foreach ($results as $r) {
          if($i>0){echo ',';}
           //echo "'".$r->date."'";
           echo "'".date('d/m/Y',strtotime($r->date))."'";
          $i++;
        } ?>
        ],
        datasets: [{
            label: 'รายงานยอดขายรายวัน',
            data: [
            <?php 
            $i=0;
            foreach ($results as $r) {
              if($i>0){echo ',';}
              echo "'".$r->total."'";
              $i++;
            } ?>
            ],
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
                'rgba(153, 102, 255, 0.2)',
                'rgba(255, 159, 64, 0.2)'
            ],
            borderColor: [
                'rgba(255,99,132,1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(153, 102, 255, 1)',
                'rgba(255, 159, 64, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero:true
                }
            }]
        }
    }
});
</script>  
</p> 

        </div>
    </section><!-- /.content -->
</div><!-- /.content-wrapper -->

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

<h1>

รายงานยอดขายรายวัน

</h1>

</ol>

</section>

<br>

<thead>

วันที่

</th>

<th class="sorting" tabindex="0" rowspan="1" colspan="1">ยอด/วัน

</th>

</tr>

</thead>

<tbody>

<?php

$total_sum=0;

if(!empty($results)){ foreach ($results as $data) { ?>

<td>

<?php echo date('d/m/Y', strtotime($data->date)); ?>

</td>

<td>

<b>

<?php echo number_format($data->total,2); ?> บาท

</b>

</td>

<?php

$total_sum+=$data->total;

</tr>

<?php } } ?>

รวมยอดขายทั้งหมด <b> <?php echo number_format($total_sum,2);?> </b> บาท

</font>

<hr>

</tbody>

</table>

<?php

$beforyear = date('Y')-1;

$thisyear = date('Y');

$nextyear = date('Y')+1;

<hr>

var ctx = document.getElementById("myChart").getContext('2d');

var myChart = new Chart(ctx, {

type: 'bar',

data: {

labels: [

<?php

$i=0;

foreach ($results as $r) {

if($i>0){echo ',';}

//echo "'".$r->date."'";

echo "'".date('d/m/Y',strtotime($r->date))."'";

$i++;

} ?>

datasets: [{

label: 'รายงานยอดขายรายวัน',

data: [

<?php

$i=0;

foreach ($results as $r) {

if($i>0){echo ',';}

echo "'".$r->total."'";

$i++;

} ?>

backgroundColor: [

'rgba(255, 99, 132, 0.2)',

'rgba(54, 162, 235, 0.2)',

'rgba(255, 206, 86, 0.2)',

'rgba(75, 192, 192, 0.2)',

'rgba(153, 102, 255, 0.2)',

'rgba(255, 159, 64, 0.2)'

borderColor: [

'rgba(255,99,132,1)',

'rgba(54, 162, 235, 1)',

'rgba(255, 206, 86, 1)',

'rgba(75, 192, 192, 1)',

'rgba(153, 102, 255, 1)',

'rgba(255, 159, 64, 1)'

borderWidth: 1

}]

options: {

scales: {

yAxes: [{

ticks: {

beginAtZero:true

}

}]

}

});

</script>

</p>

</div>

</section>

</div>

views report by weekly mainpage_b_w.php

<div class="content-wrapper">
    <!-- Content Header (Page header) -->
    <section class="content-header">
        <h1>
            รายงานยอดขายรายสัปดาห์
        </h1>
        <ol class="breadcrumb">
            <li><a href="<?php echo  base_url('report'); ?>"><i class="fa fa-dashboard"></i> หน้าแรก</a></li>
            <li class="active">-</li>
        </ol>
    </section>
    <!-- Top menu -->
    <!-- Main content -->
    <section class="content">
        <!-- Your Page Content Here -->
        <div class="box">
            <div class="box-body">
                <div id="example1_wrapper" class="dataTables_wrapper form-inline dt-bootstrap">
                    <div class="row">
                        <div class="col-sm-12">
                          
                         <br>
                            <table id="example1" class="table table-breported table-striped dataTable" role="grid" aria-describedby="example1_info">
                                <thead>
                                    <tr role="row">
                                    <th class="sorting" tabindex="0" rowspan="1" colspan="1">
                                  สัปดาห์ 
                                        </th>
                                        <th class="sorting" tabindex="0" rowspan="1" colspan="1">ยอดรวม 
                                        </th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php 

                                       //$data1 = array();
                                       $total_sum=0;

                                    if(!empty($r_w)){ foreach ($r_w as $data) { ?>
                                        <tr role="row">
                                           <td>
                                             <?php echo date('d/m/Y', strtotime($data->date)); ?>
                                             </td>
                                            <td>
                                             <b> 
                                             <?php echo number_format($data->total,2); ?> บาท 
                                             </b>
                                             </td>

                                             <?php  
        
                                            $total_sum+=$data->total;

                                             ?>
                                           
                                        </tr>

 <?php 
                          } } ?>
                                    <font color="red">
                                   รวมยอดขายทั้งหมด <b>  <?php echo number_format($total_sum,2);?>  </b> บาท 
                                   </font> 

                                   <hr>
                                   <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.6.0/Chart.bundle.js"></script>


                                </tbody>

                            </table>

 <?php 
 
$beforyear = date('Y')-1; 
$thisyear = date('Y');
$nextyear = date('Y')+1;



 ?>                           
<hr>
<p align="center">




<canvas id="myChart" width="800px" height="200px"></canvas>
<script>
var ctx = document.getElementById("myChart").getContext('2d');
var myChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: [
        <?php 
        $i=0;
        foreach ($r_w as $r) {
          if($i>0){echo ',';}
           //echo "'".$r->date."'";
           echo "'".date('d/m/Y',strtotime($r->date))."'";
          $i++;
        } ?>


        ],
        datasets: [{
            label: 'รายงานยอดขายรายสัปดาห์',
            data: [
              <?php 
            $i=0;
            foreach ($r_w as $r) {
              if($i>0){echo ',';}
              echo "'".$r->total."'";
              $i++;
            } ?>

            ],
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
                'rgba(153, 102, 255, 0.2)',
                'rgba(255, 159, 64, 0.2)'
            ],
            borderColor: [
                'rgba(255,99,132,1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(153, 102, 255, 1)',
                'rgba(255, 159, 64, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero:true
                }
            }]
        }
    }
});
</script>  
</p> 

        </div>
    </section><!-- /.content -->
</div><!-- /.content-wrapper -->

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

<h1>

รายงานยอดขายรายสัปดาห์

</h1>

</ol>

</section>

<br>

<thead>

สัปดาห์

</th>

<th class="sorting" tabindex="0" rowspan="1" colspan="1">ยอดรวม

</th>

</tr>

</thead>

<tbody>

<?php

//$data1 = array();

$total_sum=0;

if(!empty($r_w)){ foreach ($r_w as $data) { ?>

<td>

<?php echo date('d/m/Y', strtotime($data->date)); ?>

</td>

<td>

<b>

<?php echo number_format($data->total,2); ?> บาท

</b>

</td>

<?php

$total_sum+=$data->total;

</tr>

<?php

} } ?>

รวมยอดขายทั้งหมด <b> <?php echo number_format($total_sum,2);?> </b> บาท

</font>

<hr>

</tbody>

</table>

<?php

$beforyear = date('Y')-1;

$thisyear = date('Y');

$nextyear = date('Y')+1;

<hr>

var ctx = document.getElementById("myChart").getContext('2d');

var myChart = new Chart(ctx, {

type: 'bar',

data: {

labels: [

<?php

$i=0;

foreach ($r_w as $r) {

if($i>0){echo ',';}

//echo "'".$r->date."'";

echo "'".date('d/m/Y',strtotime($r->date))."'";

$i++;

} ?>

datasets: [{

label: 'รายงานยอดขายรายสัปดาห์',

data: [

<?php

$i=0;

foreach ($r_w as $r) {

if($i>0){echo ',';}

echo "'".$r->total."'";

$i++;

} ?>

backgroundColor: [

'rgba(255, 99, 132, 0.2)',

'rgba(54, 162, 235, 0.2)',

'rgba(255, 206, 86, 0.2)',

'rgba(75, 192, 192, 0.2)',

'rgba(153, 102, 255, 0.2)',

'rgba(255, 159, 64, 0.2)'

borderColor: [

'rgba(255,99,132,1)',

'rgba(54, 162, 235, 1)',

'rgba(255, 206, 86, 1)',

'rgba(75, 192, 192, 1)',

'rgba(153, 102, 255, 1)',

'rgba(255, 159, 64, 1)'

borderWidth: 1

}]

options: {

scales: {

yAxes: [{

ticks: {

beginAtZero:true

}

}]

}

});

</script>

</p>

</div>

</section>

</div>

views report by monthly mainpage_b_m.php

<div class="content-wrapper">
    <!-- Content Header (Page header) -->
    <section class="content-header">
        <h1>
            รายงานยอดขายรายเดือน
        </h1>
        <ol class="breadcrumb">
            <li><a href="<?php echo  base_url('report'); ?>"><i class="fa fa-dashboard"></i> หน้าแรก</a></li>
            <li class="active">-</li>
        </ol>
    </section>
    <!-- Top menu -->
    <?php // echo $this->session->flashdata('msginfo'); ?>
    <!-- Main content -->
    <section class="content">
        <!-- Your Page Content Here -->
        <div class="box">
            <div class="box-body">
                <div id="example1_wrapper" class="dataTables_wrapper form-inline dt-bootstrap">
                    <div class="row">
                        <div class="col-sm-12">
                          
                         <br>
                            <table id="example1" class="table table-breported table-striped dataTable" role="grid" aria-describedby="example1_info">
                                <thead>
                                    <tr role="row">
                                    <th class="sorting" tabindex="0" rowspan="1" colspan="1">
                                  เดือน 
                                        </th>
                                        <th class="sorting" tabindex="0" rowspan="1" colspan="1">ยอดรวม 
                                        </th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php 

                                       $total_sum=0;

                                    if(!empty($r_m)){ foreach ($r_m as $data) { ?>
                                        <tr role="row">
                                           <td>
                                             <?php echo date('m/Y', strtotime($data->date)); ?>
                                             </td>
                                            <td>
                                             <b> 
                                             <?php echo number_format($data->total,2); ?> บาท 
                                             </b>
                                             </td>

                                             <?php  

                                                    
                                            $total_sum+=$data->total;

                                             ?>
                                           
                                        </tr>
      
 <?php 

                                    } } ?>
                                    <font color="red">
                                   รวมยอดขายทั้งหมด <b>  <?php echo number_format($total_sum,2);?>  </b> บาท 
                                   </font> 

                                   <hr>
                                   <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.6.0/Chart.bundle.js"></script>


                                </tbody>

                            </table>

 <?php 
 
$beforyear = date('Y')-1; 
$thisyear = date('Y');
$nextyear = date('Y')+1;



 ?>                           
<hr>
<p align="center">
<canvas id="myChart" width="800px" height="200px"></canvas>
<script>
var ctx = document.getElementById("myChart").getContext('2d');
var myChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: [
<?php 
        $i=0;
        foreach ($r_m as $r) {
          if($i>0){echo ',';}
           //echo "'".$r->date."'";
           echo "'".date('m/Y',strtotime($r->date))."'";
          $i++;
        } ?>

        ],
        datasets: [{
            label: 'รายงานยอดขายรายเดือน',
            data: [
          <?php 
            $i=0;
            foreach ($r_m as $r) {
              if($i>0){echo ',';}
              echo "'".$r->total."'";
              $i++;
            } ?>
            ],
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
                'rgba(153, 102, 255, 0.2)',
                'rgba(255, 159, 64, 0.2)'
            ],
            borderColor: [
                'rgba(255,99,132,1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(153, 102, 255, 1)',
                'rgba(255, 159, 64, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero:true
                }
            }]
        }
    }
});
</script>  
</p> 
        </div>
    </section><!-- /.content -->
</div><!-- /.content-wrapper -->

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

<h1>

รายงานยอดขายรายเดือน

</h1>

</ol>

</section>

<?php // echo $this->session->flashdata('msginfo'); ?>

<br>

<thead>

เดือน

</th>

<th class="sorting" tabindex="0" rowspan="1" colspan="1">ยอดรวม

</th>

</tr>

</thead>

<tbody>

<?php

$total_sum=0;

if(!empty($r_m)){ foreach ($r_m as $data) { ?>

<td>

<?php echo date('m/Y', strtotime($data->date)); ?>

</td>

<td>

<b>

<?php echo number_format($data->total,2); ?> บาท

</b>

</td>

<?php

$total_sum+=$data->total;

</tr>

<?php

} } ?>

รวมยอดขายทั้งหมด <b> <?php echo number_format($total_sum,2);?> </b> บาท

</font>

<hr>

</tbody>

</table>

<?php

$beforyear = date('Y')-1;

$thisyear = date('Y');

$nextyear = date('Y')+1;

<hr>

var ctx = document.getElementById("myChart").getContext('2d');

var myChart = new Chart(ctx, {

type: 'bar',

data: {

labels: [

<?php

$i=0;

foreach ($r_m as $r) {

if($i>0){echo ',';}

//echo "'".$r->date."'";

echo "'".date('m/Y',strtotime($r->date))."'";

$i++;

} ?>

datasets: [{

label: 'รายงานยอดขายรายเดือน',

data: [

<?php

$i=0;

foreach ($r_m as $r) {

if($i>0){echo ',';}

echo "'".$r->total."'";

$i++;

} ?>

backgroundColor: [

'rgba(255, 99, 132, 0.2)',

'rgba(54, 162, 235, 0.2)',

'rgba(255, 206, 86, 0.2)',

'rgba(75, 192, 192, 0.2)',

'rgba(153, 102, 255, 0.2)',

'rgba(255, 159, 64, 0.2)'

borderColor: [

'rgba(255,99,132,1)',

'rgba(54, 162, 235, 1)',

'rgba(255, 206, 86, 1)',

'rgba(75, 192, 192, 1)',

'rgba(153, 102, 255, 1)',

'rgba(255, 159, 64, 1)'

borderWidth: 1

}]

options: {

scales: {

yAxes: [{

ticks: {

beginAtZero:true

}

}]

}

});

</script>

</p>

</div>

</section>

</div>

views report by yearly mainpage_b_y.php

<div class="content-wrapper">
    <!-- Content Header (Page header) -->
    <section class="content-header">
        <h1>
            รายงานยอดขายรายปี
        </h1>
        <ol class="breadcrumb">
            <li><a href="<?php echo  base_url('report'); ?>"><i class="fa fa-dashboard"></i> หน้าแรก</a></li>
            <li class="active"></li>
        </ol>
    </section>
    <!-- Top menu -->
    <!-- Main content -->
    <section class="content">
        <!-- Your Page Content Here -->
        <div class="box">
            <div class="box-body">
                <div id="example1_wrapper" class="dataTables_wrapper form-inline dt-bootstrap">
                    <div class="row">
                        <div class="col-sm-12">
                          
                         <br>
                            <table id="example1" class="table table-breported table-striped dataTable" role="grid" aria-describedby="example1_info">
                                <thead>
                                    <tr role="row">
                                    <th class="sorting" tabindex="0" rowspan="1" colspan="1">
                                    ปี 
                                        </th>
                                        <th class="sorting" tabindex="0" rowspan="1" colspan="1">ยอดรวม 
                                        </th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php 

                                       $total_sum=0;

                                    if(!empty($r_y)){ foreach ($r_y as $data) { ?>
                                        <tr role="row">
                                           <td>
                                             <?php echo date('Y', strtotime($data->date)); ?>
                                             </td>
                                            <td>
                                             <b> 
                                             <?php echo number_format($data->total,2); ?> บาท 
                                             </b>
                                             </td>

                                             <?php  

                                                    
                                            $total_sum+=$data->total;

                                             ?>
                                           
                                        </tr>
 
 <?php 
                                    } } ?>
                                    <font color="red">
                                   รวมยอดขายทั้งหมด <b>  <?php echo number_format($total_sum,2);?>  </b> บาท 
                                   </font> 

                                   <hr>
                                   <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.6.0/Chart.bundle.js"></script>


                                </tbody>

                            </table>

 <?php 
 
$beforyear = date('Y')-1; 
$thisyear = date('Y');
$nextyear = date('Y')+1;



 ?>                           
<hr>
<p align="center">
<canvas id="myChart" width="800px" height="200px"></canvas>
<script>
var ctx = document.getElementById("myChart").getContext('2d');
var myChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: [<?php 
        $i=0;
        foreach ($r_y as $r) {
          if($i>0){echo ',';}
          echo "'".date('Y',strtotime($r->date))."'";
          $i++;
        } ?>],
        datasets: [{
            label: 'รายงานยอดขายรายปี',
            data: [
<?php 
            $i=0;
            foreach ($r_y as $r) {
              if($i>0){echo ',';}
              echo "'".$r->total."'";
              $i++;
            } ?>
            ],
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
                'rgba(153, 102, 255, 0.2)',
                'rgba(255, 159, 64, 0.2)'
            ],
            borderColor: [
                'rgba(255,99,132,1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(153, 102, 255, 1)',
                'rgba(255, 159, 64, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero:true
                }
            }]
        }
    }
});
</script>  
</p> 

        </div>
    </section><!-- /.content -->
</div><!-- /.content-wrapper -->

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

<h1>

รายงานยอดขายรายปี

</h1>

</ol>

</section>

<br>

<thead>

ปี

</th>

<th class="sorting" tabindex="0" rowspan="1" colspan="1">ยอดรวม

</th>

</tr>

</thead>

<tbody>

<?php

$total_sum=0;

if(!empty($r_y)){ foreach ($r_y as $data) { ?>

<td>

<?php echo date('Y', strtotime($data->date)); ?>

</td>

<td>

<b>

<?php echo number_format($data->total,2); ?> บาท

</b>

</td>

<?php

$total_sum+=$data->total;

</tr>

<?php

} } ?>

รวมยอดขายทั้งหมด <b> <?php echo number_format($total_sum,2);?> </b> บาท

</font>

<hr>

</tbody>

</table>

<?php

$beforyear = date('Y')-1;

$thisyear = date('Y');

$nextyear = date('Y')+1;

<hr>

var ctx = document.getElementById("myChart").getContext('2d');

var myChart = new Chart(ctx, {

type: 'bar',

data: {

labels: [<?php

$i=0;

foreach ($r_y as $r) {

if($i>0){echo ',';}

echo "'".date('Y',strtotime($r->date))."'";

$i++;

} ?>],

datasets: [{

label: 'รายงานยอดขายรายปี',

data: [

<?php

$i=0;

foreach ($r_y as $r) {

if($i>0){echo ',';}

echo "'".$r->total."'";

$i++;

} ?>

backgroundColor: [

'rgba(255, 99, 132, 0.2)',

'rgba(54, 162, 235, 0.2)',

'rgba(255, 206, 86, 0.2)',

'rgba(75, 192, 192, 0.2)',

'rgba(153, 102, 255, 0.2)',

'rgba(255, 159, 64, 0.2)'

borderColor: [

'rgba(255,99,132,1)',

'rgba(54, 162, 235, 1)',

'rgba(255, 206, 86, 1)',

'rgba(75, 192, 192, 1)',

'rgba(153, 102, 255, 1)',

'rgba(255, 159, 64, 1)'

borderWidth: 1

}]

options: {

scales: {

yAxes: [{

ticks: {

beginAtZero:true

}

}]

}

});

</script>

</p>

</div>

</section>

</div>

the table

CREATE TABLE `booksdetail` (
  `books_id` int(11) NOT NULL COMMENT 'รหัสการจอง',
  `deposits` int(11) DEFAULT NULL COMMENT 'ค่ามัดจำ',
  `user_id` int(4) DEFAULT NULL COMMENT 'รหัสสมาชิก',
  `date` date DEFAULT NULL COMMENT 'วันที่จอง',
  `time` time DEFAULT NULL COMMENT 'เวลาที่จอง',
  `tel` varchar(20) DEFAULT NULL,
  `img_id` int(10) DEFAULT NULL COMMENT 'รหัสรูปภาพ',
  `status` int(1) DEFAULT NULL COMMENT 'สถานะมาไม่มา',
  `pay_id` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT 'รหัสเลขที่จ่ายเงิน',
  `pay_date` date DEFAULT NULL COMMENT 'วันที่จ่ายเงิน',
  `pay_time` time DEFAULT NULL COMMENT 'เวลาที่จ่ายเงิน'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `booksdetail`
--

INSERT INTO `booksdetail` (`books_id`, `deposits`, `user_id`, `date`, `time`, `tel`, `img_id`, `status`, `pay_id`, `pay_date`, `pay_time`) VALUES
(1, 22, 14, '2018-06-13', '11:30:30', '22', 2, 1, '1', NULL, NULL),
(3, 33, 14, '2016-06-13', '12:00:00', '3333', 1, 1, '1', NULL, NULL),
(4, 111, 14, '2017-06-30', '11:00:00', '11111', 3, 0, '1', NULL, NULL),
(5, 90, 14, '2017-07-15', '12:22:00', '343434', 2, 0, '1', NULL, NULL),
(6, 200, 14, '2018-01-05', '12:22:00', '200', 2, 0, '1', NULL, NULL),
(7, 200, 1, '2017-06-16', '13:00:00', '45455', 1, 1, '1', NULL, NULL),
(8, 200, 14, '2017-06-07', '22:02:00', '7667857865786587', 1, 1, '1', NULL, NULL),
(9, 400, 14, '2017-06-29', '11:11:00', '34343434', 10, 1, '1', NULL, NULL),
(10, 9999, 14, '2017-06-16', '11:11:00', '9999', 1, 1, '1', NULL, NULL),
(11, 200, 14, '2017-06-27', '22:20:00', '0000', 2, 1, '1', NULL, NULL),
(12, 4444, 14, '2017-07-03', '12:12:00', '444', 2, 1, '1', NULL, NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `booksdetail`
--
ALTER TABLE `booksdetail`
  ADD PRIMARY KEY (`books_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `booksdetail`
--
ALTER TABLE `booksdetail`
  MODIFY `books_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'รหัสการจอง', AUTO_INCREMENT=13;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

CREATE TABLE `booksdetail` (

`books_id` int(11) NOT NULL COMMENT 'รหัสการจอง',

`deposits` int(11) DEFAULT NULL COMMENT 'ค่ามัดจำ',

`user_id` int(4) DEFAULT NULL COMMENT 'รหัสสมาชิก',

`date` date DEFAULT NULL COMMENT 'วันที่จอง',

`time` time DEFAULT NULL COMMENT 'เวลาที่จอง',

`tel` varchar(20) DEFAULT NULL,

`img_id` int(10) DEFAULT NULL COMMENT 'รหัสรูปภาพ',

`status` int(1) DEFAULT NULL COMMENT 'สถานะมาไม่มา',

`pay_id` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT 'รหัสเลขที่จ่ายเงิน',

`pay_date` date DEFAULT NULL COMMENT 'วันที่จ่ายเงิน',

`pay_time` time DEFAULT NULL COMMENT 'เวลาที่จ่ายเงิน'

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `booksdetail`

INSERT INTO `booksdetail` (`books_id`, `deposits`, `user_id`, `date`, `time`, `tel`, `img_id`, `status`, `pay_id`, `pay_date`, `pay_time`) VALUES

(1, 22, 14, '2018-06-13', '11:30:30', '22', 2, 1, '1', NULL, NULL),

(3, 33, 14, '2016-06-13', '12:00:00', '3333', 1, 1, '1', NULL, NULL),

(4, 111, 14, '2017-06-30', '11:00:00', '11111', 3, 0, '1', NULL, NULL),

(5, 90, 14, '2017-07-15', '12:22:00', '343434', 2, 0, '1', NULL, NULL),

(6, 200, 14, '2018-01-05', '12:22:00', '200', 2, 0, '1', NULL, NULL),

(7, 200, 1, '2017-06-16', '13:00:00', '45455', 1, 1, '1', NULL, NULL),

(8, 200, 14, '2017-06-07', '22:02:00', '7667857865786587', 1, 1, '1', NULL, NULL),

(9, 400, 14, '2017-06-29', '11:11:00', '34343434', 10, 1, '1', NULL, NULL),

(10, 9999, 14, '2017-06-16', '11:11:00', '9999', 1, 1, '1', NULL, NULL),

(11, 200, 14, '2017-06-27', '22:20:00', '0000', 2, 1, '1', NULL, NULL),

(12, 4444, 14, '2017-07-03', '12:12:00', '444', 2, 1, '1', NULL, NULL);

-- Indexes for dumped tables

-- Indexes for table `booksdetail`

ALTER TABLE `booksdetail`

ADD PRIMARY KEY (`books_id`);

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `booksdetail`

ALTER TABLE `booksdetail`

MODIFY `books_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'รหัสการจอง', AUTO_INCREMENT=13;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

result images

devbanban

Codeigniter : Create Chart Report by date, week, month, year

Byพิศิษฐ์ บวรเลิศสุธี

Related

Related Post

สอนทำระบบแจ้งซ่อมออนไลน์ ฟรี, Helpdesk System

ระบบฐานข้อมูลวิจัย คลังข้อมูลวิจัย ฐานข้อมูลวิทยานิพนธ์ Research Corpus Web Application

แจกโค้ด ระบบสายตรงผู้บริหาร PHP

You missed

ระบบบริหารบุคลากร ระบบ HR + ระบบบันทึกเวลาเข้างาน

ฟรีคลิปสอนทำเว็บไซต์ PHP PDO MySQL AdminLTE3 2024

ระบบหอพัก , อพาร์ทเม้นท์ รายวัน รายเดือน ราคาเบาๆ เอาไปต่อยอดได้ 2024

แจกฟรี To-do list Ui ออกแบบจาก Bootstrap v5.3

Byพิศิษฐ์ บวรเลิศสุธี

Share this:

Related

Related Post

You missed