<?php

class StuntingController extends BaseController
{
    public function table()
    {
        $params = $this->request->get();
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;

        $params = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required']],
                'kodewilayah' => ['validators' => ['required']],
                'kodesubwilayah' => [],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $kodePemda = null;

        if ($params['kodesubwilayah'] != null && !empty($params['kodesubwilayah']))
            $kodePemda = $params['kodesubwilayah'];
        else if ($params['kodewilayah'] != null && !empty($params['kodewilayah']))
            $kodePemda = $params['kodewilayah'];

        $q = "
            SELECT *
            FROM jaga.view_stunting
            WHERE tahun = :tahun AND kodepemda = :kodepemda
            ORDER BY regexp_replace(standarfungsi, E'\\\\D', '', 'g')::integer ASC, standarfungsi ASC
            LIMIT :thelimit OFFSET :theoffset
            
        ";

        $data = $this->rawQuery($q, [
            'tahun' => $params['tahun'],
            'kodepemda' => $kodePemda,
            'thelimit' => $params['limit'],
            'theoffset' => $params['offset']
        ]);

        $total_record = $this->rawQuery("
            SELECT COUNT(*) FROM view_stunting 
            WHERE tahun = :tahun AND kodepemda = :kodepemda
        ", [
            'tahun' => $params['tahun'],
            'kodepemda' => $kodePemda
        ])[0]['count'];

        return new PaginatedResponse($data, $total_record, $params['limit'], $params['offset']);
    }

    public function totalAnggaran()
    {
        $params = $this->request->get();

        $params = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required']],
                'kodewilayah' => ['validators' => ['required']],
                'kodesubwilayah' => [],
            ]
        );

        $kodePemda = null;

        if ($params['kodesubwilayah'] != null && !empty($params['kodesubwilayah']))
            $kodePemda = $params['kodesubwilayah'];
        else if ($params['kodewilayah'] != null && !empty($params['kodewilayah']))
            $kodePemda = $params['kodewilayah'];

        $q = "
            SELECT 
	            COUNT(id_anggaran) AS count_item_anggaran, 
	            COUNT(ids_realisasi) AS count_item_realisasi,
	            SUM(nilai_anggaran) AS total_nilai_anggaran, 
	            SUM(nilai_realisasi) AS total_nilai_realisasi,  
	            CASE WHEN SUM(nilai_anggaran) = 0 THEN null ELSE ROUND(SUM(nilai_realisasi)/SUM(nilai_anggaran)*100,2) END AS total_realisasi_thd_anggaran
	            FROM view_stunting 
	            WHERE kodepemda = :kodepemda 
	            AND tahun = :tahun
        ";

        $result = [
            'count_item_anggaran' => 0,
            'count_item_realisasi' => 0,
            'total_nilai_anggaran' => null,
            'total_nilai_realisasi' => null,
            'total_realisasi_thd_anggaran' => null
        ];

        $data = $this->rawQuery($q, [
            'tahun' => $params['tahun'],
            'kodepemda' => $kodePemda,
        ]);
        if(count($data) > 0)
            $result = $data[0];

        return new SimpleResponse($result);
    }

    public function anggaranStandarFungsi()
    {
        $params = $this->request->get();

        $params = $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required']],
                'kodewilayah' => ['validators' => ['required']],
                'kodesubwilayah' => [],
            ]
        );

        $kodePemda = null;

        if ($params['kodesubwilayah'] != null && !empty($params['kodesubwilayah']))
            $kodePemda = $params['kodesubwilayah'];
        else if ($params['kodewilayah'] != null && !empty($params['kodewilayah']))
            $kodePemda = $params['kodewilayah'];

        $q = "
            SELECT 
	            COUNT(id_anggaran) AS count_item_anggaran,
	            COUNT(ids_realisasi) AS count_item_realisasi,
	            standarfungsi,
	            SUM(nilai_anggaran) AS total_nilai_anggaran, 
	            SUM(nilai_realisasi) AS total_nilai_realisasi,  
	            CASE WHEN SUM(nilai_anggaran) = 0 THEN null ELSE ROUND(SUM(nilai_realisasi)/SUM(nilai_anggaran)*100,2) END AS total_realisasi_thd_anggaran
	            FROM view_stunting 
	            WHERE kodepemda = :kodepemda 
	            AND tahun = :tahun
	            GROUP BY standarfungsi
	            ORDER BY regexp_replace(standarfungsi, E'\\\\D', '', 'g')::integer ASC, standarfungsi ASC
        ";

        $result = $this->rawQuery($q, [
            'tahun' => $params['tahun'],
            'kodepemda' => $kodePemda,
        ]);

        return new SimpleResponse($result);
    }
}