<?php

namespace app\models;

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

/**
 * class untuk logic query peta kecamatan
 */

class Peta extends Model
{
  /**
   * return json
   */
  function getPetaKabupaten()
  {
    // //\Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
    // $connection = \Yii::$app->db;
    // //$model = $connection->createCommand('SELECT *, public.ST_AsGeoJSON(wkb_geometry) as geojson FROM "' . $table_name . '"');
    // $model = $connection->createCommand('SELECT row_to_json(fc)
    //     FROM (SELECT \'FeatureCollection\' AS type, 
    //         (SELECT crs FROM 
    //             (SELECT \'name\' AS type, 
    //                  (SELECT properties 
    //                     FROM (SELECT \'EPSG:32751\' AS name) AS properties)
    //         ) AS crs),
    //           array_to_json(array_agg(f)) As features
    //        FROM (SELECT \'Feature\' As type
    //         , public.ST_AsGeoJSON(lg.wkb_geometry, 4)::json As geometry
    //         , row_to_json((SELECT l FROM (SELECT ogc_fid,d_kd_kec,d_nm_kec) As l
    //           )) As propertiesTable
    //        FROM public."7404" As lg ) As f ) As fc;');

    // $kabupaten = $model->queryOne();

    // return $kabupaten;
    $query = new Query();
    $results = $query->select([
      'ogc_fid',
      'd_kd_kec',
      'd_nm_kec',
      'ST_AsGeoJSON(ST_Transform(wkb_geometry, 4326)) as geometry',
      'ST_AsGeoJSON(wkb_geometry) as original_geometry'
    ])
      ->from('public."7404"')
      ->all();

    // Transform PostGIS results to GeoJSON Feature Collection
    $features = [];
    foreach ($results as $row) {
      $geometry = json_decode($row['geometry'], true);

      // Ensure we're dealing with a MultiPolygon
      if ($geometry['type'] === 'Polygon') {
        // Convert single Polygon to MultiPolygon
        $geometry = [
          'type' => 'MultiPolygon',
          'coordinates' => [$geometry['coordinates']]
        ];
      }

      $features[] = [
        'type' => 'Feature',
        'properties' => [
          'id' => $row['ogc_fid'],
          'd_kd_kec' => $row['d_kd_kec'],
          'd_nm_kec' => $row['d_nm_kec'],
          'original_srid' => 32751,
          'converted_srid' => 4326
        ],
        'geometry' => $geometry
      ];
    }

    $featureCollection = [
      'type' => 'FeatureCollection',
      'features' => $features
    ];

    return $featureCollection;
  }

  /**
   * return json
   */
  function getPetaJalanKabupaten()
  {
    //\Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
    $connection = \Yii::$app->db;
    //$model = $connection->createCommand('SELECT *, public.ST_AsGeoJSON(wkb_geometry) as geojson FROM "' . $table_name . '"');
    $model = $connection->createCommand('SELECT row_to_json(fc)
        FROM (SELECT \'FeatureCollection\' AS type, 
            (SELECT crs FROM 
                (SELECT \'name\' AS type, 
                     (SELECT properties 
                        FROM (SELECT \'EPSG:32751\' AS name) AS properties)
            ) AS crs),
              array_to_json(array_agg(f)) As features
           FROM (SELECT \'Feature\' As type
            , public.ST_AsGeoJSON(lg.geom, 4)::json As geometry
            , row_to_json((SELECT l FROM (SELECT id,jalan_mimika) As l
              )) As propertiesTable
           FROM public."9109jl" As lg ) As f ) As fc;');

    $jalanKabupaten = $model->queryOne();

    return $jalanKabupaten;
  }

  /**
   * return json
   */
  function getPetaSungaiKabupaten()
  {
    //\Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
    $connection = \Yii::$app->db;
    //$model = $connection->createCommand('SELECT *, public.ST_AsGeoJSON(wkb_geometry) as geojson FROM "' . $table_name . '"');
    $model = $connection->createCommand('SELECT row_to_json(fc)
        FROM (SELECT \'FeatureCollection\' AS type, 
            (SELECT crs FROM 
                (SELECT \'name\' AS type, 
                     (SELECT properties 
                        FROM (SELECT \'EPSG:32751\' AS name) AS properties)
            ) AS crs),
              array_to_json(array_agg(f)) As features
           FROM (SELECT \'Feature\' As type
            , public.ST_AsGeoJSON(lg.geom, 4)::json As geometry
            , row_to_json((SELECT l FROM (SELECT id,sungai) As l
              )) As propertiesTable
           FROM public."9109sg" As lg ) As f ) As fc;');

    $sungaiKabupaten = $model->queryOne();

    return $sungaiKabupaten;
  }

  /**
   * return json
   */
  function getPetaSungaiPolyKabupaten()
  {
    //\Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
    $connection = \Yii::$app->db;
    //$model = $connection->createCommand('SELECT *, public.ST_AsGeoJSON(wkb_geometry) as geojson FROM "' . $table_name . '"');
    $model = $connection->createCommand('SELECT row_to_json(fc)
        FROM (SELECT \'FeatureCollection\' AS type, 
            (SELECT crs FROM 
                (SELECT \'name\' AS type, 
                     (SELECT properties 
                        FROM (SELECT \'EPSG:32751\' AS name) AS properties)
            ) AS crs),
              array_to_json(array_agg(f)) As features
           FROM (SELECT \'Feature\' As type
            , public.ST_AsGeoJSON(lg.geom, 4)::json As geometry
            , row_to_json((SELECT l FROM (SELECT id,sungai) As l
              )) As propertiesTable
           FROM public."9109sgp" As lg ) As f ) As fc;');

    $sungaiPolyKabupaten = $model->queryOne();

    return $sungaiPolyKabupaten;
  }

  /**
   * return json
   */
  function getPetaKecamatan($nop)
  {
    try {
      $connection = \Yii::$app->db;

      $model = $connection->createCommand('SELECT row_to_json(fc)
            FROM (SELECT \'FeatureCollection\' AS type, 
                (SELECT crs FROM 
                    (SELECT \'name\' AS type, 
                        (SELECT properties 
                            FROM (SELECT \'EPSG:32751\' AS name) AS properties)
                ) AS crs),
                array_to_json(array_agg(f)) As features
            FROM (SELECT \'Feature\' As type
                , public.ST_AsGeoJSON(lg.wkb_geometry, 4)::json As geometry
                , row_to_json((SELECT l FROM (SELECT ogc_fid,d_kd_kel,d_nm_kel) As l
                )) As propertiesTable
            FROM public."' . $nop . '" As lg ) As f ) As fc;');

      $kecamatan = $model->queryOne();

      return $kecamatan;
    } catch (\yii\base\Exception $e) {
      return $kecamatan = null;
    }
  }

  /**
   * return json
   */
  function getPetaKelurahan($nop)
  {
    try {
      $connection = \Yii::$app->db;

      $model = $connection->createCommand(
        'SELECT json_build_object(
                \'type\', \'FeatureCollection\',
                \'crs\',  json_build_object(
                    \'type\',      \'name\', 
                    \'properties\', json_build_object(
                        \'name\', \'EPSG:32751\')),
                        \'features\', json_agg(
                    json_build_object(
                        \'type\',       \'Feature\',
                        \'id\',         ogc_fid,
                        \'geometry\',   ST_AsGeoJSON(wkb_geometry)::json,
                        \'properties\', json_build_object(
                            \'id\', ogc_fid,
                            \'nop\', d_nop
                        )
                    )
                )
            )
            from public."' . $nop . '"'
      );

      $kelurahan = $model->queryOne();
      return $kelurahan;
    } catch (\yii\base\Exception $e) {
      return $kelurahan = null;
    }
  }

  /**
   * return json
   */
  function getPetaBlok($nop)
  {
    try {
      $connection = \Yii::$app->db;

      $model = $connection->createCommand('SELECT row_to_json(fc)
            FROM (SELECT \'FeatureCollection\' AS type, 
                (SELECT crs FROM 
                    (SELECT \'name\' AS type, 
                        (SELECT properties 
                            FROM (SELECT \'EPSG:32751\' AS name) AS properties)
                ) AS crs),
                array_to_json(array_agg(f)) As features
            FROM (SELECT \'Feature\' As type
                , public.ST_AsGeoJSON(lg.wkb_geometry, 4)::json As geometry
                , row_to_json((SELECT l FROM (SELECT ogc_fid,d_blok,nm_blok) As l
                )) As propertiesTable
            FROM public."' . $nop . 'bl" As lg ) As f ) As fc;');

      $blok = $model->queryOne();

      return $blok;
    } catch (\yii\base\Exception $e) {
      return $blok = null;
    }
  }

  /**
   * return json
   */
  function getPetaBangunan($nop)
  {
    try {
      $connection = \Yii::$app->db;

      $model = $connection->createCommand('SELECT row_to_json(fc)
            FROM (SELECT \'FeatureCollection\' AS type, 
                (SELECT crs FROM 
                    (SELECT \'name\' AS type, 
                        (SELECT properties 
                            FROM (SELECT \'EPSG:32751\' AS name) AS properties)
                ) AS crs),
                array_to_json(array_agg(f)) As features
            FROM (SELECT \'Feature\' As type
                , public.ST_AsGeoJSON(lg.wkb_geometry, 4)::json As geometry
                , row_to_json((SELECT l FROM (SELECT *) As l
                )) As propertiesTable
            FROM public."' . $nop . 'bg" As lg ) As f ) As fc;');

      $bangunan = $model->queryOne();

      return $bangunan;
    } catch (\yii\base\Exception $e) {
      return $bangunan = null;
    }
  }

  /**
   * get jalan
   * @param string $nop
   * @return json
   */
  function getJalan($nop)
  {
    try {
      $connection = \Yii::$app->db;

      $model = $connection->createCommand('SELECT row_to_json(fc)
            FROM (SELECT \'FeatureCollection\' AS type, 
                (SELECT crs FROM 
                    (SELECT \'name\' AS type, 
                        (SELECT properties 
                            FROM (SELECT \'EPSG:32751\' AS name) AS properties)
                ) AS crs),
                array_to_json(array_agg(f)) As features
            FROM (SELECT \'Feature\' As type
                , public.ST_AsGeoJSON(lg.geom, 4)::json As geometry
                , row_to_json((SELECT l FROM (SELECT id) As l
                )) As propertiesTable
            FROM public."' . $nop . 'jl" As lg ) As f ) As fc;');

      $jalan = $model->queryOne();

      return $jalan;
    } catch (\yii\base\Exception $e) {
      return $jalan = null;
    }
  }

  /**
   * get sungai
   * @param string $nop
   * @return json
   */
  function getSungai($nop)
  {
    try {
      $connection = \Yii::$app->db;

      $model = $connection->createCommand('SELECT row_to_json(fc)
            FROM (SELECT \'FeatureCollection\' AS type, 
                (SELECT crs FROM 
                    (SELECT \'name\' AS type, 
                        (SELECT properties 
                            FROM (SELECT \'EPSG:32751\' AS name) AS properties)
                ) AS crs),
                array_to_json(array_agg(f)) As features
            FROM (SELECT \'Feature\' As type
                , public.ST_AsGeoJSON(lg.geom, 4)::json As geometry
                , row_to_json((SELECT l FROM (SELECT id) As l
                )) As propertiesTable
            FROM public."' . $nop . 'sg" As lg ) As f ) As fc;');

      $sungai = $model->queryOne();

      return $sungai;
    } catch (\yii\base\Exception $e) {
      return $sungai = null;
    }
  }

  public function getInfoBangunan2($nop)
  {
    $kd_propinsi    = substr($nop, 0, 2);
    $kd_dati2       = substr($nop, 2, 2);
    $kd_kecamatan   = substr($nop, 4, 3);
    $kd_kelurahan   = substr($nop, 7, 3);
    $kd_blok        = substr($nop, 10, 3);
    $no_urut        = substr($nop, 13, 4);
    $kd_jns_op      = substr($nop, 17, 1);

    $connection = \Yii::$app->db;
    $model = $connection->createCommand("select d.nm_wp, a.thn_dibangun_bng, b.nm_jpb, a.thn_renovasi_bng, a.luas_bng, a.jml_lantai_bng,
        a.kd_propinsi||'.'||a.kd_dati2||'.'||a.kd_kecamatan||'.'||a.kd_kelurahan||'.'||a.kd_blok||'.'||a.no_urut||'.'||a.kd_jns_op as nop,
        case a.kondisi_bng when '1' then 'SANGAT BAIK'
                        when '2' then 'BAIK'
                        when '3' then 'SEDANG'
                        when '4' then 'JELEK'
        end as kondisi_bng,
        case a.jns_konstruksi_bng when '1' then 'BAJA'
                                when '2' then 'BETON'
                                when '3' then 'BATU BATA'
                                when '4' then 'KAYU'
        end as jns_konstruksi_bng,
        case a.jns_atap_bng when '1' then 'DECRABON/BETON/GTG GLAZUR'
                            when '2' then 'GTG BETON/ALUMUNIUM'
                            when '3' then 'GTG BIASA/SIRAP'
                            when '4' then 'ASBES'
            else 'SENG'
        end as jns_atap_bng,
        case a.kd_dinding when '1' then 'KACA/ALUMUNIUM'
                        when '2' then 'BETON'
                        when '3' then 'BATU BATA/CONBLOK'
                        when '4' then 'KAYU'
                        when '5' then 'SENG'
            else 'TIDAK ADA'
        end as kd_dinding,
        case a.kd_lantai when '1' then 'MARMER'
                        when '2' then 'KERAMIK'
                        when '3' then 'TERASO'
                        when '4' then 'UBIN PC/PAPAN'
            else 'SEMEN'
        end as kd_lantai,
        case a.kd_langit_langit when '1' then 'AKUSTIK/JATI'
                                when '2' then 'TRIPLEK/ASBES BAMBU'
            else 'TIDAK ADA'
        end as kd_langit_langit
        from pbb.dat_op_bangunan a
        inner join pbb.ref_jpb b on a.kd_jpb = b.kd_jpb
        left join pbb.dat_objek_pajak c on a.kd_propinsi||a.kd_dati2||a.kd_kecamatan||a.kd_kelurahan||a.kd_blok||a.no_urut||a.kd_jns_op  = c.kd_propinsi||c.kd_dati2||c.kd_kecamatan||c.kd_kelurahan||c.kd_blok||c.no_urut||c.kd_jns_op 
        left join pbb.dat_subjek_pajak d on c.subjek_pajak_id = d.subjek_pajak_id
        where
        a.kd_propinsi = '$kd_propinsi'
                            AND a.kd_dati2 = '$kd_dati2'
                            AND a.kd_kecamatan = '$kd_kecamatan'
                            AND a.kd_kelurahan = '$kd_kelurahan'
                            AND a.kd_blok = '$kd_blok'
                            AND a.no_urut = '$no_urut'
                            AND a.kd_jns_op = '$kd_jns_op'");

    $result = $model->queryOne();

    return json_encode($result, true);
  }

  public function getInfoBangunan($nop)
  {
    try {
      $kd_propinsi    = substr($nop, 0, 2);
      $kd_dati2       = substr($nop, 2, 2);
      $kd_kecamatan   = substr($nop, 4, 3);
      $kd_kelurahan   = substr($nop, 7, 3);
      $kd_blok        = substr($nop, 10, 3);
      $no_urut        = substr($nop, 13, 4);
      $kd_jns_op      = substr($nop, 17, 1);
      $objek_pajak    = [
        "alamat_op" => "",
        "nm_wp" => "",
        "status_wp" => "",
        "pekerjaan" => "",
        "npwp" => "",
        "alamat_wp" => "",
        "luas_bumi" => "",
        "kd_znt" => "",
        "jns_bumi" => "",
        "nop" => ""
      ];
      $first = "";

      $connection = \Yii::$app->db;
      $model = $connection->createCommand("select trim(a.JALAN_OP)||' '||trim(a.BLOK_KAV_NO_OP)||' RW/RT '||TRIM(a.RW_OP)||'/'||TRIM(a.RT_OP) as alamat_op,
          nm_wp,
          (case when a.KD_STATUS_WP = '1' then 'PEMILIK'
                when a.KD_STATUS_WP = '2' then 'PENYEWA'
                when a.KD_STATUS_WP = '3' then 'PENGELOLA'
                when a.KD_STATUS_WP = '4' then 'PEMAKAI'
                else 'SENGKETA' end)  as status_wp,
          (case when c.STATUS_PEKERJAAN_WP = '1' then 'PNS'
            when c.STATUS_PEKERJAAN_WP = '2' then 'ABRI'
            when c.STATUS_PEKERJAAN_WP = '3' then 'PENSIUNAN'
            when c.STATUS_PEKERJAAN_WP = '4' then 'BADAN'
            else 'LAINNYA' end)  as pekerjaan,
          c.NPWP, trim(c.JALAN_WP)||' '||trim(c.BLOK_KAV_NO_WP)||' RW/RT '||trim(c.RW_WP)||'/'||trim(c.RT_WP) as alm_wp,
          b.luas_bumi, b.KD_ZNT,
          (case when b.JNS_BUMI = '1' then 'TANAH + BANGUNAN'
            when b.JNS_BUMI = '2' then 'KAVLING SIAP BANGUN'
            when b.JNS_BUMI = '3' then 'TANAH KOSONG'
            else 'FASILITAS UMUM' end)  as jns_bumi,
            a.kd_propinsi||a.kd_dati2||a.kd_kecamatan||a.kd_kelurahan||a.kd_blok||a.no_urut||a.kd_jns_op as nop
          from pbb.dat_objek_pajak a
          INNER JOIN pbb.dat_op_bumi b on a.kd_propinsi = b.kd_propinsi And a.kd_dati2 = b.kd_dati2 And a.kd_kecamatan = b.kd_kecamatan
          and a.kd_kelurahan = b.kd_kelurahan  and a.kd_blok = b.kd_blok and a.no_urut = b.no_urut and a.kd_jns_op = b.kd_jns_op
          INNER JOIN pbb.dat_subjek_pajak c on a.subjek_pajak_id = c.subjek_pajak_id
          where
          a.kd_propinsi = '$kd_propinsi'
                              AND a.kd_dati2 = '$kd_dati2'
                              AND a.kd_kecamatan = '$kd_kecamatan'
                              AND a.kd_kelurahan = '$kd_kelurahan'
                              AND a.kd_blok = '$kd_blok'
                              AND a.no_urut = '$no_urut'
                              AND a.kd_jns_op = '$kd_jns_op'");

      $resultObjekPajak = $model->queryAll();

      foreach ($resultObjekPajak as $row) {
        $objek_pajak = array(
          "alamat_op" => $row['alamat_op'] ? $row['alamat_op'] : '-',
          "nm_wp" => $row['nm_wp'] ? $row['nm_wp'] : '-',
          "status_wp" => $row['status_wp'] ? $row['status_wp'] : '-',
          "pekerjaan" => $row['pekerjaan'] ? $row['pekerjaan'] : '-',
          "npwp" => $row['npwp'] ? $row['npwp'] : '-',
          "alamat_wp" => $row['alm_wp'] ? $row['alm_wp'] : '-',
          "luas_bumi" => $row['luas_bumi'] ? number_format($row['luas_bumi']) : '-',
          "kd_znt" => $row['kd_znt'] ? $row['kd_znt'] : '',
          "jns_bumi" => $row['jns_bumi'] ? $row['jns_bumi'] : '',
          "nop" => $row['nop'] ? $row['nop'] : ''
        );
        //echo json_encode($data);
      }

      $datOpBangungan = $connection->createCommand("
              select * from pbb.dat_op_bangunan where 
              kd_propinsi = '$kd_propinsi'
                              AND kd_dati2 = '$kd_dati2'
                              AND kd_kecamatan = '$kd_kecamatan'
                              AND kd_kelurahan = '$kd_kelurahan'
                              AND kd_blok = '$kd_blok'
                              AND no_urut = '$no_urut'
                              AND kd_jns_op = '$kd_jns_op'");

      $resultOpBangunan = $datOpBangungan->queryAll();


      $json = array('data' => [], 'objek_pajak' => isset($objek_pajak) ? $objek_pajak : [], 'dat_nir' => [], 'jumlah_bangunan' => count($resultOpBangunan), 'sppt' => []);

      $thnPajakSppts = $connection->createCommand("
          select distinct thn_pajak_sppt
              from pbb.sppt
          where 
          kd_propinsi = '$kd_propinsi'
                              AND kd_dati2 = '$kd_dati2'
                              AND kd_kecamatan = '$kd_kecamatan'
                              AND kd_kelurahan = '$kd_kelurahan'
                              AND kd_blok = '$kd_blok'
                              AND no_urut = '$no_urut'
                              AND kd_jns_op = '$kd_jns_op'
                              order by thn_pajak_sppt desc");

      $tahunPajak = $thnPajakSppts->queryAll();

      foreach ($tahunPajak as $i => $thnPajakSppt) {
        //echo json_encode(array("thn_pajak_sppt",$thnPajakSppt->thn_pajak_sppt));
        $propertiesThnPajakSppt = array(
          'thn_pajak_sppt' => $thnPajakSppt['thn_pajak_sppt'] ? $thnPajakSppt['thn_pajak_sppt'] : '-',
          //'objek_pajak'    => $objek_pajak
        );
        if ($i == 0) {
          $first = $propertiesThnPajakSppt['thn_pajak_sppt'];
        }

        array_push($json['data'], $propertiesThnPajakSppt);
      }

      $tahun = null;
      if ($tahun != null) {
        $tahun1 = $tahun;
      } else {
        $tahun1 = $first;
      }
      $datNir = $connection->createCommand("
              select kd_znt, nir 
                  from pbb.dat_nir
              where 
              kd_propinsi = '$kd_propinsi'
                              AND kd_dati2 = '$kd_dati2'
                              AND kd_kecamatan = '$kd_kecamatan'
                              AND kd_kelurahan = '$kd_kelurahan'
                              AND kd_znt = '$objek_pajak[kd_znt]'
                              AND thn_nir_znt = '$tahun1'");

      $resultDatNir = $datNir->queryAll();

      foreach ($resultDatNir as $datNir) {
        $propertiesNir = array(
          'kd_znt_nir' => $datNir['kd_znt'] ? $datNir['kd_znt'] : '-',
          'nir' => $datNir['nir'] ? $datNir['nir'] : '-'
        );
        array_push($json['dat_nir'], $propertiesNir);
      }

      $modelSppt = $connection->createCommand("
              select nm_wp_sppt, njop_bumi_sppt, njop_bng_sppt, pbb_yg_harus_dibayar_sppt
              from pbb.sppt
              where
              kd_propinsi = '$kd_propinsi'
                                  AND kd_dati2 = '$kd_dati2'
                                  AND kd_kecamatan = '$kd_kecamatan'
                                  AND kd_kelurahan = '$kd_kelurahan'
                                  AND kd_blok = '$kd_blok'
                                  AND no_urut = '$no_urut'
                                  AND kd_jns_op = '$kd_jns_op'
                                  AND thn_pajak_sppt = '$tahun1'");

      $sppts = $modelSppt->queryAll();

      foreach ($sppts as $sppt) {
        $propertiesSppt = array(
          'nm_wp_sppt' => $sppt['nm_wp_sppt'] ? $sppt['nm_wp_sppt'] : '-',
          'njop_bumi' => $sppt['njop_bumi_sppt'] ? number_format($sppt['njop_bumi_sppt']) : '-',
          'njop_bng' => $sppt['njop_bng_sppt'] ? number_format($sppt['njop_bng_sppt']) : '-',
          'pbb' => $sppt['pbb_yg_harus_dibayar_sppt'] ? number_format($sppt['pbb_yg_harus_dibayar_sppt']) : '-'
        );
        array_push($json['sppt'], $propertiesSppt);
      }
      if ($json["data"] == []) {
        return json_encode(['error' => 'data tidak ditemukan']);
      }
      return json_encode($json, true);
    } catch (\yii\base\Exception $e) {
      return json_encode(['error' => 'data tidak ditemukan', 'message' => $e->getMessage()]);
    }
  }
}