clqms-server/app/Controllers/Reports.php

<?php

namespace App\Controllers;
use DateTime;
class Reports extends BaseController {

	private function getProductList() {
        $curl = curl_init();

        curl_setopt_array($curl, array(
            CURLOPT_URL => 'https://services-summit.my.id/api/getProductSites',
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
            CURLOPT_CUSTOMREQUEST => 'GET',
        ));

        $response = curl_exec($curl);
        curl_close($curl);
        $data = json_decode($response, true);
		
		return $data;
    }

	public function rangeData () {

		$startdate = $this->request->getGet('startdate');
		$enddate = $this->request->getGet('enddate');

		if ($startdate && $enddate) {
			$db = \Config\Database::connect();

			// $sql = "SELECT 
			// 			MIN(YEAR(ResultDateTime)) AS FirstYear,
			// 			MAX(YEAR(ResultDateTime)) AS LastYear
			// 		FROM patres";
			// $query = $db->query($sql);
			// $results = $query->getResultArray();
			$yearRange = range(date('Y', strtotime($startdate)), date('Y', strtotime($enddate)));

			$query = $db->query("
				SELECT EquipmentID, YEAR(ResultDateTime) AS Tahun, COUNT(*) AS patresCount
				FROM patres 
				WHERE ResultDateTime BETWEEN '$startdate 00:00:01' AND '$enddate 23:59:59'
				GROUP BY YEAR(ResultDateTime), EquipmentID 
				ORDER BY EquipmentID, tahun
			");
			$rows = $query->getResultArray();

			$equipments = [
				['EquipmentID' => '6006100619', 'sitename' => 'RS Permata Cibubur'],
				['EquipmentID' => '6005840519', 'sitename' => 'RS dr. Oen Kandang Sapi Solo'],
				['EquipmentID' => '6008850621', 'sitename' => 'RS Universitas Sebelas Maret'],
				['EquipmentID' => '6010610522', 'sitename' => 'RSUD Muntilan'],
				['EquipmentID' => '6011310722', 'sitename' => 'National Hospital Surabaya'],
				['EquipmentID' => '6011320722', 'sitename' => 'RS Mardi Rahayu Kudus'],
				['EquipmentID' => '6002771117', 'sitename' => 'UPTD Laboratorium Kesehatan Kota Magelang'],
				['EquipmentID' => '6014341023', 'sitename' => 'RSUD Sukoharjo'],
				['EquipmentID' => '6015090124', 'sitename' => 'Persada Hospital'],
				['EquipmentID' => '6015560324', 'sitename' => 'ScanMe Labs Jakarta Kelapa Gading'],
				['EquipmentID' => '6007621020', 'sitename' => 'UPT Labkesmas Kabupaten Magelang'],
				['EquipmentID' => '6016560724', 'sitename' => 'RS Kasih Ibu Surakarta'],
				['EquipmentID' => '6016850924', 'sitename' => 'Laboratorium Klinik Budi Sehat']
			];

			// Mapping EquipmentID => sitename
			$siteNames = [];
			foreach ($equipments as $eq) {
				$siteNames[(int)$eq['EquipmentID']] = $eq['sitename'];
			}

			// Buat struktur pivot
			$pivot = [];
			foreach ($rows as $row) {
				$eid   = (int) $row['EquipmentID'];
				$tahun = (int) $row['Tahun'];
				$count = (int) $row['patresCount'];

				if (!isset($pivot[$eid])) {
					foreach ($yearRange as $y) {
						$pivot[$eid][$y] = 0;
					}
				}

				$pivot[$eid][$tahun] = $count;
			}
		
			// Buat struktur Growth/Presentase
			$growth = [];
			foreach ($pivot as $eid => $data) {
				foreach ($yearRange as $i => $currYear) {
					if ($i === 0) continue;

					$prevYear = $yearRange[$i - 1];
					$prev = $data[$prevYear] ?? 0;
					$curr = $data[$currYear] ?? 0;

					$key = "$prevYear-$currYear";

					if (!isset($growth[$eid])) {
						$growth[$eid] = [];
					}

					if ($prev == 0) {
						$growth[$eid][$key] = '-';
					} else {
						$percent = (($curr - $prev) / $prev) * 100;
						$growth[$eid][$key] = round($percent, 1) . '%';
					}
				}
			}

			return view('report_all_range', [
				'yearRange' => $yearRange,
				'pivot'     => $pivot,
				'growth'    => $growth,
				'siteNames' => $siteNames
			]);

		} else {
			return view('report_all_range');
		}

	}

	public function spesificData() {

		$first_month = (int)$this->request->getGet('first_month');
		$last_month = (int)$this->request->getGet('last_month');

		if ($first_month && $last_month) {

			$db = \Config\Database::connect();

			$sql = "SELECT 
						MIN(YEAR(ResultDateTime)) AS FirstYear,
						MAX(YEAR(ResultDateTime)) AS LastYear
					FROM patres";
			$query = $db->query($sql);
			$results = $query->getResultArray();
			$yearRange = range($results[0]['FirstYear'], $results[0]['LastYear']);

			$query = $db->query("
				SELECT EquipmentID, YEAR(ResultDateTime) AS Tahun, COUNT(*) AS patresCount
				FROM patres
				WHERE MONTH(ResultDateTime) BETWEEN $first_month AND $last_month
				GROUP BY EquipmentID, YEAR(ResultDateTime)
				ORDER BY EquipmentID, Tahun
			");

			$rows = $query->getResultArray();

			$equipments = [
				['EquipmentID' => '6006100619', 'sitename' => 'RS Permata Cibubur'],
				['EquipmentID' => '6005840519', 'sitename' => 'RS dr. Oen Kandang Sapi Solo'],
				['EquipmentID' => '6008850621', 'sitename' => 'RS Universitas Sebelas Maret'],
				['EquipmentID' => '6010610522', 'sitename' => 'RSUD Muntilan'],
				['EquipmentID' => '6011310722', 'sitename' => 'National Hospital Surabaya'],
				['EquipmentID' => '6011320722', 'sitename' => 'RS Mardi Rahayu Kudus'],
				['EquipmentID' => '6002771117', 'sitename' => 'UPTD Laboratorium Kesehatan Kota Magelang'],
				['EquipmentID' => '6014341023', 'sitename' => 'RSUD Sukoharjo'],
				['EquipmentID' => '6015090124', 'sitename' => 'Persada Hospital'],
				['EquipmentID' => '6015560324', 'sitename' => 'ScanMe Labs Jakarta Kelapa Gading'],
				['EquipmentID' => '6007621020', 'sitename' => 'UPT Labkesmas Kabupaten Magelang'],
				['EquipmentID' => '6016560724', 'sitename' => 'RS Kasih Ibu Surakarta'],
				['EquipmentID' => '6016850924', 'sitename' => 'Laboratorium Klinik Budi Sehat']
			];

			// Mapping EquipmentID => sitename
			$siteNames = [];
			foreach ($equipments as $eq) {
				$siteNames[(int)$eq['EquipmentID']] = $eq['sitename'];
			}

			// Buat struktur pivot
			$pivot = [];
			foreach ($rows as $row) {
				$eid   = (int) $row['EquipmentID'];
				$tahun = (int) $row['Tahun'];
				$count = (int) $row['patresCount'];

				if (!isset($pivot[$eid])) {
					foreach ($yearRange as $y) {
						$pivot[$eid][$y] = 0;
					}
				}

				$pivot[$eid][$tahun] = $count;
			}
		
			// Buat struktur Growth/Presentase
			$growth = [];
			foreach ($pivot as $eid => $data) {
				foreach ($yearRange as $i => $currYear) {
					if ($i === 0) continue;

					$prevYear = $yearRange[$i - 1];
					$prev = $data[$prevYear] ?? 0;
					$curr = $data[$currYear] ?? 0;

					$key = "$prevYear-$currYear";

					if (!isset($growth[$eid])) {
						$growth[$eid] = [];
					}

					if ($prev == 0) {
						$growth[$eid][$key] = '-';
					} else {
						$percent = (($curr - $prev) / $prev) * 100;
						$growth[$eid][$key] = round($percent, 1) . '%';
					}
				}
			}

			// $first_month = 7;
			$name_first_month = DateTime::createFromFormat('!m', $first_month)->format('F');
			$name_last_month = DateTime::createFromFormat('!m', $last_month)->format('F');

			return view('report_spesific_range', [
				'yearRange' => $yearRange,
				'pivot'     => $pivot,
				'growth'    => $growth,
				'siteNames' => $siteNames,
				'name_first_month' => $name_first_month,
				'name_last_month' => $name_last_month
			]);

		} else {
			return view('report_spesific_range');
		}

		
    }
	
}