<?php
class DesaTaniController extends BaseController
{
    const DIREKTORAT_PETERNAKAN = 'TERNAK';
    const DIREKTORAT_HOLTIKULTURA = 'HOLTIKULTURA';
    const DIREKTORAT_PERKEBUNAN = 'KEBUN';
    const DIREKTORAT_TANAMAN_PANGAN = 'PANGAN';
    const DIREKTORAT_SARANA_PRASARANA = 'SARANA';

    const direktorat = [
        self::DIREKTORAT_PETERNAKAN => 'Peterbakan dan Kesehatan Hewan',
        self::DIREKTORAT_HOLTIKULTURA => 'Holtikultura',
        self::DIREKTORAT_PERKEBUNAN => 'Perkebunan',
        self::DIREKTORAT_TANAMAN_PANGAN => 'Tanaman Pangan',
        self::DIREKTORAT_SARANA_PRASARANA => 'Sarana dan Prasana',
    ];

    //region DASHBOARD
    //region GENERAL
    public function alsitanPrapanen()
    {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'direktorat' => ['validators' => ['required']],
                'tahun' => ['validators' => ['digit']]
            ]
        );

        $whereTahun = '';

        $bind = [
            'direktorat' => array_key_exists(strtoupper($params['direktorat']), self::direktorat) ? self::direktorat[strtoupper($params['direktorat'])] : $params['direktorat'],
            'prapascapanen' => 'PRAPANEN',
        ];

        if(!empty($params['tahun'])) {
            $whereTahun = ' AND tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        $result = $this->rawQuery("
            SELECT
            	SUM(jumlah) as jumlah_alsintan, 
            	COUNT(DISTINCT(penerima_kelompok_tani)) FILTER (WHERE penerima_kelompok_tani IS NOT NULL AND penerima_kelompok_tani != '') as kelompok_penerima, 
            	COUNT(DISTINCT(desa)) FILTER (WHERE desa IS NOT NULL AND desa != '') as desa_penerima
            FROM
            	desa_tani
             	WHERE direktorat = :direktorat AND prapanen_pascapanen = :prapascapanen
             	$whereTahun
        ", $bind);

        return new SimpleResponse($result);
    }

    public function alsitanPascapanen()
    {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'direktorat' => ['validators' => ['required']],
                'tahun' => ['validators' => ['digit']]
            ]
        );

        $whereTahun = '';

        $bind = [
            'direktorat' => array_key_exists(strtoupper($params['direktorat']), self::direktorat) ? self::direktorat[strtoupper($params['direktorat'])] : $params['direktorat'],
            'prapascapanen' => 'PASCAPANEN'
        ];

        if(!empty($params['tahun'])) {
            $whereTahun = ' AND tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        $result = $this->rawQuery("
            SELECT
            	SUM(jumlah) as jumlah_alsintan, 
            	COUNT(DISTINCT(penerima_kelompok_tani)) FILTER (WHERE penerima_kelompok_tani IS NOT NULL AND penerima_kelompok_tani != '') as kelompok_penerima, 
            	COUNT(DISTINCT(desa)) FILTER (WHERE desa IS NOT NULL AND desa != '') as desa_penerima
            FROM
            	desa_tani
             	WHERE direktorat = :direktorat AND prapanen_pascapanen = :prapascapanen
             	$whereTahun
        ", $bind);

        return new SimpleResponse($result);
    }

    public function listTahun()
    {
        $result = array_column($this->rawQuery("
            SELECT
            	tahun
            FROM
            	desa_tani
            WHERE tahun IS NOT NULL
             	GROUP BY tahun
             	ORDER BY tahun ASC 
        ", []), 'tahun');

        if(empty($result)){
            $result = [
                date('Y')
            ];
        }

        return new SimpleResponse($result);
    }
    //endregion

    //region Sarana dan Prasarana
    public function sebaranAlsitanPerProvinsi() {
        $getParams = $this->request->get();
        $getParams['limit'] = $getParams['limit'] ?? 10;
        $getParams['offset'] = $getParams['offset'] ?? 0;
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']],
                'search' => ['validators' => []],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
            ]
        );

        $whereTahun = '';

        $bind = [
            'pra' => 'PRAPANEN',
            'pasca' => 'PASCAPANEN',
            'direktorat' => 'Sarana dan Prasana'
        ];

        if(!empty($params['tahun'])) {
            $whereTahun = ' AND tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        $whereSearch = '';

        if(!empty($params['search'])) {
            $whereSearch = ' AND LOWER(provinsi) LIKE :search';
            $bind['search'] = '%'.strtolower($params['search']).'%';
        }

        $limitOffset = "LIMIT ".$params['limit']." OFFSET ".$params['offset'];

        $result = $this->rawQuery("
            SELECT
            	provinsi,
            	COALESCE (SUM (jumlah) FILTER (WHERE prapanen_pascapanen = :pra), 0) AS jumlah_prapanen,
            	COALESCE (SUM (jumlah) FILTER (WHERE prapanen_pascapanen = :pasca), 0) AS jumlah_pascapanen,
	            (COALESCE (SUM (jumlah) FILTER (WHERE prapanen_pascapanen = :pra), 0) + COALESCE (SUM (jumlah) FILTER (WHERE prapanen_pascapanen = :pasca), 0)) AS jumlah_total
            	
            FROM
            	desa_tani 
            	WHERE direktorat = :direktorat
            	$whereTahun
            	$whereSearch
            GROUP BY
            	provinsi
            ORDER BY jumlah_total DESC
            $limitOffset
        ", $bind);

        $number = 1;
        foreach ($result as $key => $value) {
            $result[$key]['no'] = $number;
            $number++;
        }

        return new SimpleResponse($result);
    }

    public function jenisAlsitanPrapanen() {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']]
            ]
        );

        $whereTahun = '';

        $bind = [
            'pra' => 'PRAPANEN',
            'direktorat' => 'Sarana dan Prasana'
        ];

        if(!empty($params['tahun'])) {
            $whereTahun = ' AND tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        $result = $this->rawQuery("
            SELECT
            	jenis_alsin_kegiatan,
            	COALESCE (SUM (jumlah), 0) AS jumlah
            FROM
            	desa_tani 
            	WHERE direktorat = :direktorat AND prapanen_pascapanen = :pra
            	$whereTahun
            GROUP BY
            	jenis_alsin_kegiatan
        ", $bind);

        return new SimpleResponse($result);
    }

    public function jenisAlsitanPascapanen() {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']]
            ]
        );

        $whereTahun = '';

        $bind = [
            'pasca' => 'PASCAPANEN',
            'direktorat' => 'Sarana dan Prasana'
        ];

        if(!empty($params['tahun'])) {
            $whereTahun = ' AND tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        $result = $this->rawQuery("
            SELECT
            	jenis_alsin_kegiatan,
            	COALESCE (SUM (jumlah), 0) AS jumlah
            FROM
            	desa_tani 
            	WHERE direktorat = :direktorat AND prapanen_pascapanen = :pasca
            	$whereTahun
            GROUP BY
            	jenis_alsin_kegiatan
        ", $bind);

        return new SimpleResponse($result);
    }
    //endregion

    //region Holtikulutra
    public function holtikulturaPenerimaBantuan()
    {
        $data = $this->rawQuery("
            SELECT
            	COUNT(*) AS jumlah,
            	jenis_alsin_kegiatan,
            	tahun
            FROM
            	desa_tani
            	WHERE direktorat = :direktorat
            	GROUP BY
            	jenis_alsin_kegiatan, tahun
            	ORDER BY
            	jenis_alsin_kegiatan, tahun;
        ", [
            'direktorat' => 'Holtikultura'
        ]);

        $jenis = [];
        $tahun = [];

        foreach ($data as $item) {
            if(!in_array($item['jenis_alsin_kegiatan'], $jenis)) array_push($jenis, $item['jenis_alsin_kegiatan']);
            if(!in_array($item['tahun'], $tahun)) array_push($tahun, $item['tahun']);
        }

        $groupedData = [];
        foreach ($jenis as $item) {
            $groupedData[$item] = [];
        }

        foreach ($data as $item) {
            array_push($groupedData[$item['jenis_alsin_kegiatan']], $item);
        }

        foreach ($groupedData as $key => $item) {
            $groupedData[$key] = [];
            foreach ($item as $itemJumlah) {
                $groupedData[$key][$itemJumlah['tahun']] = $itemJumlah['jumlah'];
            }
        }

        $dataFinal = [];
        foreach ($tahun as $i) {
            $itemResult = [
                'name' => $i
            ];
            $iData = [];

            foreach ($jenis as $j) {
                if(array_key_exists($i, $groupedData[$j]))
                    array_push($iData, $groupedData[$j][$i] ?? 0);
            }
            $itemResult['data'] =  $iData;
            array_push($dataFinal, $itemResult);
        }

        $result = [
            'key' => $jenis,
            'value' => $dataFinal
        ];

        return new SimpleResponse($result);
    }

    public function holtikulturaDetailPenerimaBantuan()
    {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit', 'required']],
                'alsin' => ['validators' => []],
                'prapascapanen' => ['validators' => []]
            ]
        );

        $whereCustom = '';

        $bind = [
            'direktorat' => 'Holtikultura',
        ];

        if(!empty($params['tahun'])) {
            $whereCustom = ' AND tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        if(!empty($params['alsin'])) {
            $whereCustom .= ' AND jenis_alsin_kegiatan = :alsin';
            $bind['alsin'] = $params['alsin'];
        }

        if(!empty($params['prapascapanen'])) {
            $whereCustom .= ' AND prapanen_pascapanen = :prapascapanen';
            $bind['prapascapanen'] = $params['prapascapanen'];
        }

        $result = $this->rawQuery("
            SELECT
            	COUNT(komuditas) AS jumlah,
            	komuditas
            FROM
            	desa_tani
            	WHERE direktorat = :direktorat 
            	$whereCustom
            	GROUP BY
            	komuditas
        ", $bind);

        return new SimpleResponse($result);
    }

    public function holtikulturaPenerimaPascaProvinsiRodaTiga()
    {
        $getParams = $this->request->get();
        $getParams['limit'] = $getParams['limit'] ?? 10;
        $getParams['offset'] = $getParams['offset'] ?? 0;
        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
            ]
        );

        $limitOffset = "LIMIT ".$params['limit']." OFFSET ".$params['offset'];

        $topTen = $this->rawQuery("
            SELECT
            	COUNT(*) AS jumlah,
            	INITCAP(provinsi) as provinsi
            FROM
            	desa_tani
            	WHERE direktorat = :direktorat
            	AND jenis_alsin_kegiatan = :alsin
            	AND prapanen_pascapanen = :prapascapanen
            GROUP BY
            	INITCAP(provinsi)
			ORDER BY jumlah DESC 
			$limitOffset
        ", [
            'direktorat' => 'Holtikultura',
            'alsin' => 'Motor Roda Tiga',
            'prapascapanen' => 'PASCAPANEN',
        ]);

        if(empty($topTen)) {
            $data = [];
        }
        else {
            $provinsiTopTen = array_column($topTen, 'provinsi');
            $inArray = "('" . implode("','", $provinsiTopTen) . "')";
            $ordinality = " UNNEST(ARRAY['".implode("','", $provinsiTopTen)."']::text[]) WITH ORDINALITY t(provinsi, ord) ON INITCAP(dt.provinsi) = t.provinsi";

            $data = $this->rawQuery("
            SELECT
            	COUNT(*) AS jumlah,
            	INITCAP(dt.provinsi) as provinsi,
            	tahun
            FROM
            	desa_tani dt
            JOIN $ordinality
            	WHERE direktorat = :direktorat
            	AND jenis_alsin_kegiatan = :alsin
            	AND prapanen_pascapanen = :prapascapanen
            	AND INITCAP(dt.provinsi) IN $inArray
            GROUP BY	
            	INITCAP(dt.provinsi), tahun, t.ord
			ORDER BY t.ord
        ", [
                'direktorat' => 'Holtikultura',
                'alsin' => 'Motor Roda Tiga',
                'prapascapanen' => 'PASCAPANEN',
            ]);
        }

        $result = [];
        $provinsiIndex = [];
        $index = 0;
        foreach ($data as $item) {
            if(!array_key_exists($item['provinsi'], $provinsiIndex)) {
                $provinsiIndex[$item['provinsi']] = $index;

                $itemResult = [
                    'provinsi' => $item['provinsi'],
                    'no' => $index + 1,
                    'data_' . $item['tahun'] => $item['jumlah']
                ];
                array_push($result, $itemResult);

                $index++;
            }
            else {
                $result[$provinsiIndex[$item['provinsi']]]['data_' . $item['tahun']] = $item['jumlah'];
            }
        }

        return new SimpleResponse($result);
    }

    public function holtikulturaPenerimaManfaat()
    {
        $result = $this->rawQuery("
            SELECT
            	COUNT(*) AS jumlah,
            	tahun
            FROM
            	desa_tani
            	WHERE direktorat = :direktorat
            	AND jenis_alsin_kegiatan = :alsin
            GROUP BY
            	tahun
							ORDER BY tahun
        ", [
            'direktorat' => 'Holtikultura',
            'alsin' => 'Penerima Manfaat Sarana Pengelolahan',
        ]);

        return new SimpleResponse($result);
    }

    public function holtikulturaDetailPenerimaSaranaPengelolahan($tahun)
    {
        $result = $this->rawQuery("
            SELECT
            	COUNT(komuditas) AS jumlah,
				komuditas
            FROM
            	desa_tani
            	WHERE direktorat = :direktorat
            	AND jenis_alsin_kegiatan = :alsin
            	AND tahun = :tahun
            GROUP BY
            	komuditas
							ORDER BY komuditas
        ", [
            'direktorat' => 'Holtikultura',
            'alsin' => 'Penerima Manfaat Sarana Pengelolahan',
            'tahun' => $tahun,
        ]);

        return new SimpleResponse($result);
    }
    //endregion

    //region Peternakan dan Kesehatan Hewan
    public function ternakProgramSeribuDesaSapi()
    {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']]
            ]
        );

        $whereTahun = '';

        $bind = [
            'direktorat' => 'Peterbakan dan Kesehatan Hewan',
        ];

        if(!empty($params['tahun'])) {
            $whereTahun = ' AND tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        $data = $this->rawQuery("
            SELECT
            	COUNT(*) AS jumlah,
            	provinsi,
            	jenis_alsin_kegiatan
            FROM
            	desa_tani
            	WHERE direktorat = :direktorat
            	$whereTahun
            GROUP BY
            	provinsi, jenis_alsin_kegiatan
			ORDER BY provinsi, jenis_alsin_kegiatan
        ", $bind);

        //region custom array for chart
        /*$provinsi = [];
        $jenis = [];

        foreach ($data as $item) {
            if(!in_array($item['provinsi'], $provinsi)) array_push($provinsi, $item['provinsi']);
            if(!in_array($item['jenis_alsin_kegiatan'], $jenis)) array_push($jenis, $item['jenis_alsin_kegiatan']);
        }

        $groupedData = [];
        foreach ($provinsi as $item) {
            $groupedData[$item] = [];
        }

        foreach ($data as $item) {
            array_push($groupedData[$item['provinsi']], $item);
        }

        foreach ($groupedData as $key => $item) {
            $groupedData[$key] = [];
            foreach ($item as $itemJumlah) {
                $groupedData[$key][$itemJumlah['jenis_alsin_kegiatan']] = $itemJumlah['jumlah'];
            }
        }

        $dataFinal = [];
        foreach ($jenis as $i) {
            $itemResult = [
                'name' => $i
            ];
            $iData = [];

            foreach ($provinsi as $j) {
                array_push($iData, $groupedData[$j][$i] ?? 0);
            }
            $itemResult['data'] =  $iData;
            array_push($dataFinal, $itemResult);
        }

        $result = [
            'key' => $provinsi,
            'value' => $dataFinal
        ];*/
        //endregion

        //region custom array for table
        $result = [];
        $provinsiIndex = [];
        $index = 0;

        $pattern = "/[-!$%^&*()+|~=`{}\[\]:\";'<>?,.\/]/i";

        foreach ($data as $item) {
            if(!array_key_exists($item['provinsi'], $provinsiIndex)) {
                $provinsiIndex[$item['provinsi']] = $index;

                $itemResult = [
                    'provinsi' => $item['provinsi'],
                    'no' => $index + 1,
                    'data_' . str_replace(" ", "_", preg_replace($pattern, "", $item['jenis_alsin_kegiatan'])) => $item['jumlah']
                ];
                array_push($result, $itemResult);

                $index++;
            }
            else {
                $result[$provinsiIndex[$item['provinsi']]]['data_' . str_replace(" ", "_", preg_replace($pattern, "", $item['jenis_alsin_kegiatan']))] = $item['jumlah'];
            }
        }
        //endregion

        return new SimpleResponse($result);
    }
    //endregion

    //region Perkebunan
    public function alsitanPascapanenPerkebunan()
    {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']]
            ]
        );

        $whereTahun = '';

        $bind = [
            'direktorat' => 'Perkebunan',
            'prapascapanen' => 'PASCAPANEN',
        ];

        if(!empty($params['tahun'])) {
            $whereTahun = ' AND tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        $result = $this->rawQuery("
            SELECT
            	COUNT(*) AS jumlah,
            	provinsi
            FROM
            	desa_tani
            	WHERE direktorat = :direktorat 
            	AND prapanen_pascapanen = :prapascapanen
            	$whereTahun
            	GROUP BY
            	provinsi
        ", $bind);

        return new SimpleResponse($result);
    }
    //endregion

    //region Tanaman Pangan
    public function alsitanPascapanenTanamanPangan()
    {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']]
            ]
        );

        $whereTahun = '';

        $bind = [
            'direktorat' => 'Tanaman Pangan',
            'prapascapanen' => 'PASCAPANEN',
        ];

        if(!empty($params['tahun'])) {
            $whereTahun = ' AND tahun = :tahun';
            $bind['tahun'] = $params['tahun'];
        }

        $result = $this->rawQuery("
            SELECT
            	COUNT(*) AS jumlah,
            	provinsi
            FROM
            	desa_tani
            	WHERE direktorat = :direktorat 
            	AND prapanen_pascapanen = :prapascapanen
            	$whereTahun
            	GROUP BY
            	provinsi
        ", $bind);

        return new SimpleResponse($result);
    }
    //endregion
    //endregion

    //region DETAIL DESA
    public function detailJagaTani()
    {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'desa' => ['validators' => ['required']],
                'tahun' => ['validators' => ['digit']]
            ]
        );

        $whereTahun = '';

        $bind = [
            'desa' => '%' . strtolower($params['desa']) . '%'
        ];

        if(!empty($params['tahun'])) {
            $whereTahun = ' AND tahun = :tahun:';
            $bind['tahun'] = $params['tahun'];
        }

        $data = DesaTani::find([
            'conditions' => 'LOWER(desa) LIKE :desa:' . $whereTahun,
            'bind' => $bind
        ])->toArray();

        $result = [];
        foreach (self::direktorat as $item) {
            if($item == self::direktorat[self::DIREKTORAT_PETERNAKAN]) {
                $result[$item] = [
                ];
            }
            else {
                $result[$item] = [
                    'PRAPANEN' => [],
                    'PASCAPANEN' => [],
                    'OTHER' => []
                ];
            }
        }

        foreach ($data as $item) {
            if($item['direktorat'] == self::direktorat[self::DIREKTORAT_PETERNAKAN]) {
                if(!array_key_exists($item['jenis_alsin_kegiatan'], $result[$item['direktorat']])) {
                    $result[$item['direktorat']][$item['jenis_alsin_kegiatan']] = $item['jumlah'];
                }
                else {
                    $result[$item['direktorat']][$item['jenis_alsin_kegiatan']] += $item['jumlah'];
                }
            }
            else {
                if (empty($item['prapanen_pascapanen']) || $item['prapanen_pascapanen'] == null) {
                    array_push($result[$item['direktorat']]['OTHER'], $item);
                } else {
                    array_push($result[$item['direktorat']][$item['prapanen_pascapanen']], $item);
                }
            }
        }

        return new SimpleResponse($result);
    }
    //endregion

    //region ADMIN
    public function get()
    {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['provinsi'] = array_key_exists('provinsi', $params) ? $params['provinsi'] : "";
        $params['kabupaten'] = array_key_exists('kabupaten', $params) ? $params['kabupaten'] : "";

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $strWhere = "";
        $bind = [];
        if (strlen($params["provinsi"]) > 0) {
            $strWhere = "lower(provinsi) like :provinsi:";
            $bind["provinsi"] = "%" . strtolower($params["provinsi"]). "%";
        }

        if (strlen($params["kabupaten"]) > 0) {
            if (strlen($strWhere) > 0)
                $strWhere .= " AND ";
            $strWhere .= "lower(kabupaten) like :kabupaten:";
            $bind["kabupaten"] = "%" . strtolower($params["kabupaten"]) . "%";
        }

        $totalCount = DesaTani::count(
            [
                $strWhere,
                "bind" => $bind
            ]
        );

        $listDesa = DesaTani::find(
            [
                'conditions' => $strWhere,
                "bind" => $bind,
                "limit" => $params["limit"],
                "offset" => $params["offset"]
            ]
        );

        return new PaginatedResponse($listDesa, $totalCount, $params["limit"], $params["offset"]);
    }

    public function upload()
    {
        $this->checkIsAuthenticated();

        ini_set('max_execution_time', '1200');
        set_time_limit(1200);

        try {
            $document = $this->getAndValidateImportFile2(
                'document',
                ['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'],
                $this->request->getUploadedFiles()
            );

            $successCount = 0;
            $fails = [];

            if (!empty($document)) {
                $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($document->getTempName());
                $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
                $reader->setReadDataOnly(false);

                $spreadsheet = $reader->load($document->getTempName());
                $worksheet = $spreadsheet->getActiveSheet();

                $expectedHeaders = [
                    "DIREKTORAT",
                    "PROVINSI",
                    'KABUPATEN',
                    'KECAMATAN',
                    'DESA',
                    'PENERIMA / KELOMPOK TANI',
                    'NAMA KETUA',
                    'JENIS ALSIN / NAMA KEGIATAN',
                    'Merek / Tipe',
                    'JUMLAH (UNIT)',
                    'HARGA',
                    'KOMUDITAS',
                    'PRAPANEN_PASCAPANEN',
                    'TAHUN'

                ];

                $rows = [];
                $headers = null;
                $rowCount = 0;

                foreach ($worksheet->getRowIterator() as $row) {
                    $values = [];

                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,

                    foreach ($cellIterator as $cell) {
                        if ($rowCount > 0 && count($values) == 14) {
                            $values[] = $cell->getValue();
                        } else {
                            $values[] = str_replace(',', '', $cell->getFormattedValue());
                        }
                    }

                    if (count($values) < count($expectedHeaders)) {
                        break;
                    }

                    if (empty($values[0]) && empty($values[1])) {
                        break;
                    }

                    if ($rowCount == 0) {
                        $headers = $values;
                    } else {
                        $rows[] = $values;
                    }

                    $rowCount++;
                }

                if (empty($headers)) {
                    throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Judul kolom/headers tidak ditemukan');
                }

                if (count($headers) < count($expectedHeaders)) {
                    throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Jumlah kolom < ' . count($expectedHeaders));
                }

                if (count($headers) > count($expectedHeaders)) {
                    $headers = array_slice($headers, 0, count($expectedHeaders));
                }

                for ($i = 0; $i < count($expectedHeaders); $i++) {
                    if ($expectedHeaders[$i] != $headers[$i]) {
                        throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Nama kolom ke-$i tidak valid. Expected: " . $expectedHeaders[$i] . '. Got: ' . $headers[$i]);
                    }
                }

                $errors = [];

                $rules = [
                    'direktorat' => ['validators' => ['required']],
                    'provinsi' => ['validators' => ['required']],
                    'kabupaten' => ['validators' => []],
                    'kecamatan' => ['validators' => []],
                    'desa' => ['validators' => []],
                    'penerima_kelompok_tani' => ['validators' => []],
                    'nama_ketua' => ['validators' => []],
                    'jenis_alsin_kegiatan' => ['validators' => []],
                    'merek_tipe' => ['validators' => []],
                    'jumlah' => ['validators' => ['numeric']],
                    'harga' => ['validators' => ['numeric']],
                    'komuditas' => ['validators' => []],
                    'prapanen_pascapanen' => ['validators' => []],
                    'tahun' => ['validators' => ['digit']]
                ];


                foreach ($rows as $key => $row) {
                    $rowPayload = [
                        'direktorat' => trim($row[0]),
                        'provinsi' => trim($row[1]),
                        'kabupaten' => trim($row[2]),
                        'kecamatan' => trim($row[3]),
                        'desa' => trim($row[4]),
                        'penerima_kelompok_tani' => trim($row[5]),
                        'nama_ketua' => trim($row[6]),
                        'jenis_alsin_kegiatan' => trim($row[7]),
                        'merek_tipe' => trim($row[8]),
                        'jumlah' => trim($row[9]),
                        'harga' => empty($row[10]) ? null : trim(str_replace('.', '', str_replace(',', '', $row[10]))),
                        'komuditas' => trim($row[11]),
                        'prapanen_pascapanen' => trim($row[12]),
                        'tahun' => empty(trim($row[13])) ? null : trim($row[13])
                    ];

                    $validationResult = $this->baseValidate($rowPayload, $rules);

                    if (count($validationResult['errors']) > 0) {
                        $errors[] = [
                            'row_id' => $key + 1,
                            'value' => $rowPayload,
                            'errors' => $validationResult['errors']
                        ];
                    }
                }

                if (count($errors) > 0) {
                    throw new ValidationException($errors);
                }

                $masterProvinsi = MasterProvinsi::find([
                    'columns' => 'provinsi_code, lower(provinsi_name) AS provinsi_name',
                ])->toArray();
                $provinsiAsKeyVal = ArrayHelper::asKeyValue($masterProvinsi, 'provinsi_name', 'provinsi_code');
                $masterKabKota = MasterKotaKabupaten::find([
                    'columns' => 'kota_kabupaten_code, lower(kota_kabupaten_name) AS kota_kabupaten_name'
                ])->toArray();
                $kabKotaAsKeyVal = ArrayHelper::asKeyValue($masterKabKota, 'kota_kabupaten_name', 'kota_kabupaten_code');

                $this->db->begin();
                try {
                    foreach ($rows as $key => $row) {
                        $rowPayload = [
                            'direktorat' => trim($row[0]),
                            'code_provinsi' => array_key_exists(strtolower(trim($row[1])), $provinsiAsKeyVal) ? $provinsiAsKeyVal[strtolower(trim($row[1]))] : null,
                            'provinsi' => trim($row[1]),
                            'code_kabupaten' => array_key_exists(strtolower(trim($row[2])), $kabKotaAsKeyVal) ? $kabKotaAsKeyVal[strtolower(trim($row[2]))] : null,
                            'kabupaten' => trim($row[2]),
                            'kecamatan' => trim($row[3]),
                            'desa' => trim($row[4]),
                            'penerima_kelompok_tani' => trim($row[5]),
                            'nama_ketua' => trim($row[6]),
                            'jenis_alsin_kegiatan' => trim($row[7]),
                            'merek_tipe' => trim($row[8]),
                            'jumlah' => empty(trim($row[9])) ? 0 : trim($row[9]),
                            'harga' => empty($row[10]) ? null : trim(str_replace('.', '', str_replace(',', '', $row[10]))),
                            'komuditas' => trim($row[11]),
                            'prapanen_pascapanen' => trim($row[12]),
                            'tahun' => empty(trim($row[13])) ? null : trim($row[13])
                        ];

                        $model = new DesaTani();
                        $model->direktorat = $rowPayload['direktorat'];
                        $model->code_provinsi = $rowPayload['code_provinsi'];
                        $model->provinsi = $rowPayload['provinsi'];
                        $model->code_kabupaten = $rowPayload['code_kabupaten'];
                        $model->kabupaten = $rowPayload['kabupaten'];
                        $model->kecamatan = $rowPayload['kecamatan'];
                        $model->desa = $rowPayload['desa'];
                        $model->penerima_kelompok_tani = $rowPayload['penerima_kelompok_tani'];
                        $model->nama_ketua = $rowPayload['nama_ketua'];
                        $model->jenis_alsin_kegiatan = $rowPayload['jenis_alsin_kegiatan'];
                        $model->merek_tipe = $rowPayload['merek_tipe'];
                        $model->jumlah = $rowPayload['jumlah'];
                        $model->harga = $rowPayload['harga'];
                        $model->komuditas = $rowPayload['komuditas'];
                        $model->prapanen_pascapanen = $rowPayload['prapanen_pascapanen'];
                        $model->tahun = $rowPayload['tahun'];
                        $result = $model->create();
                        if (!$result) {
                            $errorMessage = '';

                            foreach ($model->getMessages() as $message) {
                                $errorMessage .= $message . ", ";
                            }

                            $fails[] = [
                                'row_id' => $key + 1,
                                'value' => $rowPayload,
                                'error_message' => $errorMessage
                            ];
                        } else {
                            $successCount++;
                        }

                    }
                    $this->db->commit();
                } catch (\Exception $e) {
                    $this->db->rollback();

                    throw $e;
                }


                return new CollectionPaginatedResponse([
                    'success_count' => $successCount,
                    'fails' => $fails
                ]);
            } else {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found or Not active");
            }
        }
        catch (Exception $e) {
            \Sentry\captureException($e);
            throw new Exception($e);
        }
    }

    public function delete($id)
    {
        $this->checkIsAuthenticated();

        $data = DesaTani::findFirst($id);

        if (empty($data)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id' . $id . 'tidak diketemukan');
        endif;

        $status = $data->delete();

        if (!$status) {
            $errors = $data->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) {
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            }

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$data]);
        }
    }

    public function deleteByDirektorat()
    {
        $this->checkIsAuthenticated();

        $postParams = $this->request->getJsonRawBody(true);
        $params = [];

        $params['direktorat'] = array_key_exists('direktorat', $postParams) ? $postParams['direktorat'] : null;

        $validatedParams = $this->validate(
            $params,
            [
                'direktorat' => ['validators' => ['required']]
            ]
        );

        $data = DesaTani::find([
            'conditions' => 'direktorat = ?0',
            'bind' => [
                self::direktorat[$validatedParams['direktorat']]
            ]
        ]);

        if (empty($data)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'data tidak diketemukan');
        endif;

        $this->db->begin();
        try {
            foreach ($data as $item) {
                $status = $item->delete();

                if (!$status) {
                    $errors = $data->getMessages();
                    $error_messages = [];

                    foreach ($errors as $key => $error) {
                        $error_messages[] = [
                            "field" => $error->getField(),
                            "message" => $error->getMessage()
                        ];
                    }

                    throw new ValidationException($error_messages);
                }
            }
            $this->db->commit();
            return new CollectionPaginatedResponse([$data]);
        }
        catch (Exception $e) {
            $this->db->rollback();
            throw new Exception($e);
        }
    }

    public function deleteAll()
    {
        $this->checkIsAuthenticated();

        $status = $this->rawQuery("
            TRUNCATE TABLE desa_tani RESTART IDENTITY;
        ");

        return new SimpleResponse("success");
    }

    public function listProvinsi() {
        $result = array_column(DesaTani::find([
            'columns' => 'DISTINCT(INITCAP(provinsi)) as provinsi',
            'group' => 'provinsi'
        ])->toArray(), 'provinsi');

        return new SimpleResponse($result);
    }

    public function listKabupaten() {
        $result = array_column(DesaTani::find([
            'columns' => 'DISTINCT(INITCAP(kabupaten)) as kabupaten',
            'group' => 'kabupaten'
        ])->toArray(), 'kabupaten');

        return new SimpleResponse($result);
    }

    public function listDirektorat() {
        return [
            [
                'key' => self::DIREKTORAT_PETERNAKAN,
                'value' => self::direktorat[self::DIREKTORAT_PETERNAKAN]
            ],
            [
                'key' => self::DIREKTORAT_HOLTIKULTURA,
                'value' => self::direktorat[self::DIREKTORAT_HOLTIKULTURA]
            ],
            [
                'key' => self::DIREKTORAT_PERKEBUNAN,
                'value' => self::direktorat[self::DIREKTORAT_PERKEBUNAN]
            ],
            [
                'key' => self::DIREKTORAT_TANAMAN_PANGAN,
                'value' => self::direktorat[self::DIREKTORAT_TANAMAN_PANGAN]
            ],
            [
                'key' => self::DIREKTORAT_SARANA_PRASARANA,
                'value' => self::direktorat[self::DIREKTORAT_SARANA_PRASARANA]
            ],
        ];
    }
    //endregion
}