<?php

namespace app\models;

use Yii;
use yii\base\Model;
use yii\db\Query;

/**
 * class untuk informasi sidebar
 */

class Info extends Model
{
    /**
     * @return mixed
     */
    function getJumlahKecamatan($where = '')
    {
        $connection = Yii::$app->db;

        $model = $connection->createCommand("
            select count(*) as jumlah_kecamatan from pbb.ref_kecamatan where kd_propinsi||kd_dati2 = '7404'
        ");

        $jumlahKecamatan = $model->queryOne();

        return $jumlahKecamatan;
    }

    /**
     * @return mixed
     */
    function getNamaKecamatan($where)
    {
        $connection = Yii::$app->db;

        $model = $connection->createCommand("
            select nm_kecamatan from pbb.ref_kecamatan where kd_propinsi||kd_dati2||kd_kecamatan = '$where'
        ");

        $namaKecamatan = $model->queryOne();

        return $namaKecamatan;
    }

    /**
     * @return mixed
     */
    function getJumlahKelurahan($where = '')
    {
        $connection = Yii::$app->db;
        if ($where != '') $where = " and kd_kecamatan = '" . substr($where, -3) . "'";

        $model = $connection->createCommand("
            select count(*) as jumlah_kelurahan from pbb.ref_kelurahan where kd_propinsi||kd_dati2 = '7404' $where
        ");

        $jumlahKelurahan = $model->queryOne();

        return $jumlahKelurahan;
    }

    /**
     * @return mixed
     */
    function getNamaKelurahan($where)
    {
        $connection = Yii::$app->db;

        $model = $connection->createCommand("
            select nm_kelurahan from pbb.ref_kelurahan where kd_propinsi||kd_dati2||kd_kecamatan||kd_kelurahan = '$where'
        ");

        $namaKelurahan = $model->queryOne();

        return $namaKelurahan;
    }

    /**
     * @return mixed
     */
    function getJumlahObjekPajak($where = '', $jenis = '')
    {
        $connection = Yii::$app->db;
        if ($where != '') {
            $where = " and kd_kecamatan = '" . substr($where, -3) . "'";
        }

        $model = $connection->createCommand("
            select count(*) as jumlah_objek_pajak from pbb.dat_objek_pajak where kd_propinsi||kd_dati2 = '7404' $where
        ");

        $jumlahObjekPajak = $model->queryOne();

        return $jumlahObjekPajak;
    }

    /**
     * @return mixed
     */
    function getJumlahObjekPajakKelurahan($where = '')
    {
        $connection = Yii::$app->db;
        if ($where != '') {
            $where = " kd_propinsi||kd_dati2||kd_kecamatan||kd_kelurahan = '$where' ";
        }

        $model = $connection->createCommand("
            select count(*) as jumlah_objek_pajak from pbb.dat_objek_pajak where $where
        ");

        $jumlahObjekPajak = $model->queryOne();

        return $jumlahObjekPajak;
    }

    /**
     * @return mixed
     */
    function getJumlahSppt($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') $where = " and kd_kecamatan = '" . substr($where, -3) . "' ";

        $model = $connection->createCommand("
            select count(*) as jumlah_sppt from pbb.sppt where kd_propinsi||kd_dati2 = '7404' $where and thn_pajak_sppt = (SELECT MAX(thn_pajak_sppt) FROM pbb.sppt where kd_propinsi||kd_dati2 = '7404' $where)
        ");

        $jumlahSppt = $model->queryOne();

        return $jumlahSppt;
    }

    /**
     * @return mixed
     */
    function getJumlahSpptKelurahan($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') $where = " kd_propinsi||kd_dati2||kd_kecamatan||kd_kelurahan = '$where' ";

        $model = $connection->createCommand("
            select count(*) as jumlah_sppt from pbb.sppt where $where and thn_pajak_sppt = (SELECT MAX(thn_pajak_sppt) FROM pbb.sppt where $where)
        ");

        $jumlahSppt = $model->queryOne();

        return $jumlahSppt;
    }

    /**
     * @return mixed
     */
    function getJumlahPokokPbb($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') $where = " and kd_kecamatan = '" . substr($where, -3) . "' ";

        $model = $connection->createCommand("
            select sum(pbb_yg_harus_dibayar_sppt) as pokok_pbb from pbb.sppt where kd_propinsi||kd_dati2 = '7404' $where and thn_pajak_sppt = (SELECT MAX(thn_pajak_sppt) FROM pbb.sppt where kd_propinsi||kd_dati2 = '7404' $where)
        ");

        $pokokPbb = $model->queryOne();

        return $pokokPbb;
    }

    /**
     * @return mixed
     */
    function getJumlahPokokPbbKelurahan($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') $where = " kd_propinsi||kd_dati2||kd_kecamatan||kd_kelurahan = '$where' ";

        $model = $connection->createCommand("
            select sum(pbb_yg_harus_dibayar_sppt) as pokok_pbb from pbb.sppt where $where and thn_pajak_sppt = (SELECT MAX(thn_pajak_sppt) FROM pbb.sppt where $where)
        ");

        $pokokPbb = $model->queryOne();

        return $pokokPbb;
    }

    /**
     * @return mixed
     */
    function getRealisasiPbb($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') $where = " and kd_kecamatan = '" . substr($where, -3) . "' ";

        $model = $connection->createCommand("
            select sum(jml_sppt_yg_dibayar) as realisasi_pbb from pbb.pembayaran_sppt where kd_propinsi||kd_dati2 = '7404' $where and thn_pajak_sppt = (SELECT MAX(thn_pajak_sppt) FROM pbb.sppt where kd_propinsi||kd_dati2 = '7404' $where)
        ");

        $realisasiPbb = $model->queryOne();

        return $realisasiPbb;
    }

    /**
     * @return mixed
     */
    function getRealisasiPbbKelurahan($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') $where = " kd_propinsi||kd_dati2||kd_kecamatan||kd_kelurahan = '$where' ";

        $model = $connection->createCommand("
            select sum(jml_sppt_yg_dibayar) as realisasi_pbb from pbb.pembayaran_sppt where $where and thn_pajak_sppt = (SELECT MAX(thn_pajak_sppt) FROM pbb.sppt where $where)
        ");

        $realisasiPbb = $model->queryOne();

        return $realisasiPbb;
    }

    /**
     * @return mixed
     */
    function getZntTertinggi($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') $where = " and sppt.kd_kecamatan = '" . substr($where, -3) . "' ";

        $model = $connection->createCommand("
            select max(datnir.nir::numeric*1000) as znt_tertinggi 
            from pbb.sppt sppt join pbb.dat_nir datnir on (sppt.kd_propinsi = datnir.kd_propinsi and sppt.kd_dati2 = datnir.kd_dati2 and sppt.kd_kecamatan = datnir.kd_kecamatan and sppt.kd_kelurahan = datnir.kd_kelurahan)
            where sppt.kd_propinsi||sppt.kd_dati2 = '7404' $where and datnir.thn_nir_znt = (SELECT MAX(thn_nir_znt) FROM pbb.dat_nir where kd_propinsi||kd_dati2 = '7404')
        ");

        $zntTertinggi = $model->queryOne();

        return $zntTertinggi;
    }

    /**
     * @return mixed
     */
    function getZntTertinggiKelurahan($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') {
            $where1 = " sppt.kd_propinsi||sppt.kd_dati2||sppt.kd_kecamatan||sppt.kd_kelurahan = '$where' ";
            $where2 = " kd_propinsi||kd_dati2||kd_kecamatan||kd_kelurahan = '$where' ";
        }

        $model = $connection->createCommand("
            select max(datnir.nir::numeric*1000) as znt_tertinggi 
            from pbb.sppt sppt join pbb.dat_nir datnir on (sppt.kd_propinsi = datnir.kd_propinsi and sppt.kd_dati2 = datnir.kd_dati2 and sppt.kd_kecamatan = datnir.kd_kecamatan and sppt.kd_kelurahan = datnir.kd_kelurahan)
            where $where1 and datnir.thn_nir_znt = (SELECT MAX(thn_nir_znt) FROM pbb.dat_nir where $where2)
        ");

        $zntTertinggi = $model->queryOne();

        return $zntTertinggi;
    }

    /**
     * @return mixed
     */
    function getZntTerendah($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') $where = " and sppt.kd_kecamatan = '" . substr($where, -3) . "' ";

        $model = $connection->createCommand("
            select min(datnir.nir::numeric *1000) as znt_terendah 
            from pbb.sppt sppt join pbb.dat_nir datnir on (sppt.kd_propinsi = datnir.kd_propinsi and sppt.kd_dati2 = datnir.kd_dati2 and sppt.kd_kecamatan = datnir.kd_kecamatan and sppt.kd_kelurahan = datnir.kd_kelurahan)
            where sppt.kd_propinsi||sppt.kd_dati2 = '7404' $where and datnir.thn_nir_znt = (SELECT MAX(thn_nir_znt) FROM pbb.dat_nir where kd_propinsi||kd_dati2 = '7404')
        ");

        $zntTerendah = $model->queryOne();

        return $zntTerendah;
    }

    /**
     * @return mixed
     */
    function getZntTerendahKelurahan($where = '')
    {
        $connection = Yii::$app->db;

        if ($where != '') {
            $where1 = " sppt.kd_propinsi||sppt.kd_dati2||sppt.kd_kecamatan||sppt.kd_kelurahan = '$where' ";
            $where2 = " kd_propinsi||kd_dati2||kd_kecamatan||kd_kelurahan = '$where' ";
        }

        $model = $connection->createCommand("
            select min(datnir.nir::numeric *1000) as znt_terendah 
            from pbb.sppt sppt join pbb.dat_nir datnir on (sppt.kd_propinsi = datnir.kd_propinsi and sppt.kd_dati2 = datnir.kd_dati2 and sppt.kd_kecamatan = datnir.kd_kecamatan and sppt.kd_kelurahan = datnir.kd_kelurahan)
            where $where1 and datnir.thn_nir_znt = (SELECT MAX(thn_nir_znt) FROM pbb.dat_nir where $where2)
        ");

        $zntTerendah = $model->queryOne();

        return $zntTerendah;
    }
}