<?php

class Kip2017 extends \Phalcon\Mvc\Model
{

    /**
     *
     * @var integer
     */
    public $id;

    /**
     *
     * @var string
     */
    public $jenjang;

    /**
     *
     * @var string
     */
    public $peserta_didik_id;

    /**
     *
     * @var string
     */
    public $pro;

    /**
     *
     * @var string
     */
    public $provinsi;

    /**
     *
     * @var string
     */
    public $kab;

    /**
     *
     * @var string
     */
    public $kabupaten;

    /**
     *
     * @var string
     */
    public $kec;

    /**
     *
     * @var string
     */
    public $kecamatan;

    /**
     *
     * @var string
     */
    public $sekolah_id;

    /**
     *
     * @var string
     */
    public $npsn;

    /**
     *
     * @var string
     */
    public $nomenklatur;

    /**
     *
     * @var integer
     */
    public $kelas;

    /**
     *
     * @var string
     */
    public $rombel;

    /**
     *
     * @var string
     */
    public $nama_pd;

    /**
     *
     * @var string
     */
    public $nama_ibu_kandung;

    /**
     *
     * @var string
     */
    public $nama_ayah;

    /**
     *
     * @var string
     */
    public $tanggal_lahir;

    /**
     *
     * @var string
     */
    public $tempat_lahir;

    /**
     *
     * @var string
     */
    public $nisn;

    /**
     *
     * @var string
     */
    public $jenis_kelamin;

    /**
     *
     * @var double
     */
    public $nominal;

    /**
     *
     * @var string
     */
    public $tabunganku;

    /**
     *
     * @var integer
     */
    public $tahap_id;

    /**
     *
     * @var string
     */
    public $tahap_keterangan;

    /**
     *
     * @var string
     */
    public $nomor_sk;

    /**
     *
     * @var string
     */
    public $tanggal_sk;

    /**
     *
     * @var string
     */
    public $nama_uker;

    /**
     *
     * @var string
     */
    public $nama_rekening;

    /**
     *
     * @var string
     */
    public $tanggal_cair;

    /**
     *
     * @var string
     */
    public $branch_code;

    /**
     *
     * @var integer
     */
    public $sts_sk;

    /**
     *
     * @var string
     */
    public $pengusul;

    /**
     *
     * @var string
     */
    public $no_kip;

    /**
     *
     * @var double
     */
    public $tahun_sk;

    /**
     *
     * @var string
     */
    public $virtual_acc;

    /**
     *
     * @var string
     */
    public $no_rekening;

    /**
     *
     * @var double
     */
    public $tahun_cetak;

    /**
     *
     * @var string
     */
    public $kategori;

    /**
     *
     * @var string
     */
    public $bentuk;

    /**
     *
     * @var string
     */
    public $no_KKS;

    /**
     *
     * @var string
     */
    public $no_KPS;

    /**
     *
     * @var string
     */
    public $nama_kartu;

    /**
     *
     * @var string
     */
    public $id_pembuka_rekening;

    /**
     *
     * @var string
     */
    public $nomor_rekening;

    /**
     *
     * @var integer
     */
    public $publish;

    /**
     *
     * @var string
     */
    public $last_update;

    /**
     * Initialize method for model.
     */
    public function initialize()
    {
        $this->setSchema("jaga");
        $this->setSource("kip_2017");
    }

    /**
     * Returns table name mapped in the model.
     *
     * @return string
     */
    public function getSource()
    {
        return 'kip_2017';
    }

    /**
     * Allows to query a set of records that match the specified conditions
     *
     * @param mixed $parameters
     * @return Kip2017[]|Kip2017|\Phalcon\Mvc\Model\ResultSetInterface
     */
    public static function find($parameters = null)
    {
        return parent::find($parameters);
    }

    /**
     * Allows to query the first record that match the specified conditions
     *
     * @param mixed $parameters
     * @return Kip2017|\Phalcon\Mvc\Model\ResultInterface
     */
    public static function findFirst($parameters = null)
    {
        return parent::findFirst($parameters);
    }

    public function summary_kota_kabupaten($jenjang, $kota_kabupaten_id, $nomenklatur, $limit, $offset)
	{		
        return $this->modelsManager->createBuilder()
        ->columns(
            array(
                'Sekolah.npsn',
                'Sekolah.nama as nomenklatur',
                'count(*) as total_bantuan',
                'count(distinct(kelas)) as total_kelas',
                'sum(nominal) as total_nominal'
            )
        )
        ->from('jaga.Kip2017')
        ->join('Sekolah', 'Sekolah.npsn=Kip2017.npsn')
        ->join('MasterKecamatan', 'Sekolah.kecamatan_code = MasterKecamatan.kecamatan_code')
        ->join('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code = MasterKecamatan.kota_kabupaten_code')
        ->where('Sekolah.jenjang_id = :jenjang: and MasterKotaKabupaten.kota_kabupaten_code = :kab: and lower(Sekolah.nama) like :nomenklatur:')
        ->groupBy(['Sekolah.npsn', 'Sekolah.nama'])
        ->orderBy('Sekolah.npsn')
        ->limit($limit)
        ->offset($offset)
        ->getQuery()
        ->execute(array('jenjang' => $jenjang, 'kab' => $kota_kabupaten_id, 'nomenklatur' => '%'.strtolower($nomenklatur).'%'));
    }
    
    public function summary_kota_kabupaten_count($jenjang, $kota_kabupaten_id, $nomenklatur)
	{
		return $this->modelsManager->createBuilder()
            ->columns(
                array(
                    'Sekolah.npsn',
                    'Sekolah.nama'
                )
            )
            ->from('jaga.Kip2017')
            ->join('Sekolah', 'Sekolah.npsn=Kip2017.npsn')
            ->join('MasterKecamatan', 'Sekolah.kecamatan_code = MasterKecamatan.kecamatan_code')
            ->join('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code = MasterKecamatan.kota_kabupaten_code')
            ->where('Sekolah.jenjang_id = :jenjang: and MasterKotaKabupaten.kota_kabupaten_code = :kab: and lower(Sekolah.nama) like :nomenklatur:')
            ->groupBy(['Sekolah.npsn', 'Sekolah.nama'])
            ->getQuery()
            ->execute(array('jenjang' => $jenjang, 'kab' => $kota_kabupaten_id, 'nomenklatur' => '%'.strtolower($nomenklatur).'%'));
    }
    
    public function sekolah_detail($npsn){
        return $this->modelsManager->createBuilder()
        ->columns(
            array(
                'npsn',
                'nomenklatur',
                'kelas',
                'count(*) as jumlah_siswa',
                'SUM(nominal) as nominal'
            )
        )
        ->from('jaga.Kip2017')
        ->where('npsn = :npsn:')
        ->groupBy(['npsn', 'nomenklatur', 'kelas'])
        ->getQuery()
        ->execute(array('npsn' => $npsn));
    }
    
    public function detail($nisn)
	{
		return $this->modelsManager->createBuilder()
            ->columns(
                array(
                    'jenjang', 
                    'nisn', 
                    'nama_pd',
                    'npsn',
                    'nomenklatur',
                    'kelas',
                    'rombel',
                    'tanggal_lahir', 
                    'jenis_kelamin', 
                    'tahap_keterangan', 
                    'nomor_sk', 
                    'nama_uker', 
                    'nama_rekening', 
                    'tanggal_cair', 
                    'pengusul'
                )
            )
            ->from('jaga.Kip2017')
            ->where('nisn = :nisn:')
            ->getQuery()
            ->execute(array('nisn' => $nisn));
	}

}