<?php

class KipSummary2017 extends \Phalcon\Mvc\Model
{

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

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

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

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

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

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

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

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

    /**
     * Initialize method for model.
     */
    public function initialize()
    {
        $this->setSchema("jaga");
        $this->setSource("kip_summary_2017");
        $this->belongsTo('kecamatan_code', '\MasterKecamatan', 'kecamatan_code', ['alias' => 'MasterKecamatan']);
        $this->belongsTo('jenjang_id', '\Jenjang', 'id', ['alias' => 'Jenjang']);
    }

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

    /**
     * Allows to query a set of records that match the specified conditions
     *
     * @param mixed $parameters
     * @return KipSummary2017[]|KipSummary2017|\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 KipSummary2017|\Phalcon\Mvc\Model\ResultInterface
     */
    public static function findFirst($parameters = null)
    {
        return parent::findFirst($parameters);
    }

    public function summary_jenjang(){
        return $this->modelsManager->createBuilder()
        ->columns(
            array(
                'Jenjang.nama_jenjang',
                'sum(total_bantuan) as total_bantuan',
                'sum(total_sekolah) as total_sekolah',
                'sum(total_nominal) as total_nominal'
            )
        )
        ->from('jaga.KipSummary2017')
        ->join('Jenjang', 'KipSummary2017.jenjang_id=Jenjang.id')
        ->groupBy('Jenjang.nama_jenjang')
        ->getQuery()
        ->execute();
    }

    public function summary_nasional($jenjang){
        return $this->modelsManager->createBuilder()
        ->columns(
            array(
                'MasterProvinsi.provinsi_code as pro',
                'MasterProvinsi.provinsi_name as Provinsi',
                'sum(total_bantuan) as total_bantuan',
                'sum(total_sekolah) as total_sekolah',
                'sum(total_nominal) as total_nominal'
            )
        )
        ->from(array('KipSummary2017' => 'jaga.KipSummary2017'))
        ->join('MasterKecamatan', 'KipSummary2017.kecamatan_code = MasterKecamatan.kecamatan_code')
        ->join('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code = MasterKecamatan.kota_kabupaten_code')
        ->join('MasterProvinsi', 'MasterProvinsi.provinsi_code = MasterKotaKabupaten.provinsi_code')
        ->where('KipSummary2017.jenjang_id = :jenjang:')
        ->groupBy(['pro', 'Provinsi'])
		->orderBy('pro')
        ->getQuery()
        ->execute(array('jenjang' => $jenjang));
    }

    public function summary_provinsi($jenjang, $provinsi_id){                    
        return $this->modelsManager->createBuilder()
        ->columns(
            array(
                'MasterKotaKabupaten.kota_kabupaten_code as kab',
                'MasterKotaKabupaten.kota_kabupaten_name as Kabupaten',
                'sum(total_bantuan) as total_bantuan',
                'sum(total_sekolah) as total_sekolah',
                'sum(total_nominal) as total_nominal'
            )
        )
        ->from(array('KipSummary2017' => 'jaga.KipSummary2017'))
        ->join('MasterKecamatan', 'KipSummary2017.kecamatan_code = MasterKecamatan.kecamatan_code')
        ->join('MasterKotaKabupaten', 'MasterKotaKabupaten.kota_kabupaten_code = MasterKecamatan.kota_kabupaten_code')
        ->join('MasterProvinsi', 'MasterProvinsi.provinsi_code = MasterKotaKabupaten.provinsi_code')
        ->where('KipSummary2017.jenjang_id = :jenjang: and MasterKotaKabupaten.provinsi_code = :pro:')
        ->groupBy(['kab', 'Kabupaten'])
		->orderBy('kab')
        ->getQuery()
        ->execute(array('jenjang' => $jenjang, 'pro' => $provinsi_id));
    }	
}