chart_views.php

<?php helper('form');?>

<?= $this->extend('layout') ?>
<?= $this->section('main') ?>

<div class="card">
  <div class="card-header">
    <h3>
      Statistik purchase mengikut negeri (hot spot) in map categorized by number n
      colour
    </h3>
  </div>
  <div class="card-body">
    <div style="">
      <div id="purchase-negeri-chart-container">FusionCharts XT will load here!</div>
    </div>
  </div>
</div>

<?= $this->endSection() ?>

<?= $this->section('js') ?>

<script
  type="text/javascript"
  src="https://cdn.fusioncharts.com/fusioncharts/latest/fusioncharts.js"
></script>
<!-- Include fusion theme -->
<script
  type="text/javascript"
  src="https://cdn.fusioncharts.com/fusioncharts/latest/themes/fusioncharts.theme.fusion.js"
></script>
<script type="text/javascript">

  // purchase by negeri chart

  const purchase_negeri_dataset = <?php echo json_encode($purchase_negeri_chart_data['dataset']); ?>;

  const purchase_negeri_data_source = {
    chart: {
      caption: "Statistik purchase mengikut negeri",
      subcaption: "(tahun semasa)",
      legendposition: "BOTTOM",
      entitytooltext: "$lname: <b>$datavalue </b>purchase",
      legendcaption: "Jumlah purchase bagi setiap negeri",
      entityfillhovercolor: "#FFCDD2",
      theme: "candy"
    },
    colorrange: {
      gradient: "0",
      color: [
        {
          maxvalue: "4",
          displayvalue: "2-4",
          code: "#EF9A9A"
        },
        {
          maxvalue: "6",
          displayvalue: "4-6",
          code: "#EF5350"
        },
        {
          maxvalue: "8",
          displayvalue: "6-8",
          code: "#E53935"
        },
        {
          maxvalue: "10",
          displayvalue: "8-10",
          code: "#C62828"
        },
        {
          maxvalue: "11",
          displayvalue: "No data available",
          code: "#FFEBEE"
        }
      ]
    },
    data: purchase_negeri_dataset,
  };

  // end purchase by negeri chart

  FusionCharts.ready(function () {

    var myChart = new FusionCharts({
      type: "maps/malaysia",
      renderAt: "purchase-negeri-chart-container",
      width: "100%",
      height: "200%",
      dataFormat: "json",
      dataSource: purchase_negeri_data_source
    }).render();

  });
</script>

<?= $this->endSection() ?>

Lookup.php

<?php

namespace App\Libraries;

class Lookup {

    // constant for MAP NEGERI ID

    // const MAP_JOHOR = "001";
    // const MAP_KEDAH = "002";
    // const MAP_KELANTAN = "003";
    // const MAP_MELAKA = "004";
    // const MAP_N9 = "005";
    // const MAP_PAHANG = "006";
    // const MAP_PERAK = "007";
    // const MAP_PERLIS = "008";
    // const MAP_PENANG = "009";
    // const MAP_SABAH = "010";
    // const MAP_SARAWAK = "011";
    // const MAP_SELANGOR = "012";
    // const MAP_TERENGGANU = "013";
    // const MAP_KL = "015";
    // const MAP_LABUAN = "016";
    // const MAP_PUTRAJAYA = "017";
}

Report.php

<?php
namespace App\Models;
use CodeIgniter\Model;

class Report extends Model {

    function purchase_by_negeri($start_date, $end_date) {

        $db = \Config\Database::connect();
        
        $sql = "
        SELECT
            negeri.*,
            COUNT(kes.id_lokasi_kes) AS negeri_count
        FROM
            negeri
            LEFT JOIN kes ON negeri.id_negeri = kes.id_lokasi_kes
                AND kes.cipta_pada >= '" .$start_date. "'
                AND kes.cipta_pada <= '" .$end_date. "'
            GROUP BY
                negeri.id_negeri
        ";

        $query = $db->query($sql);
        
        return $query->getResult();
    }
    
}

ReportController.php

private function getPurchaseByNegeriChartData() {

        $start_date = '2023-01-01';
        $end_date = '2023-12-31';

        $report_model = new Report();

        $purchase_negeri_data = $report_model->purchase_by_negeri($start_date, $end_date);


        $purchase_by_negeri = [];

        if (!empty($purchase_negeri_data)) {
            foreach ($purchase_negeri_data as $row) {
                $purchase_by_negeri[] = [
                    'id' => $row->kod_map,
                    'value' => $row->negeri_count,
                ];
            }
        }

        $chart_data = [];

        $chart_data[] = [
            'data' => $purchase_by_negeri
        ];

        $purchase_negeri_chart_data = [];

        $purchase_negeri_chart_data['dataset'] = $chart_data;

        return $purchase_negeri_chart_data;
    }