<?php

class paket extends CActiveRecord
{
	public $efisiensi;
	public $prosentase;

	public function prosentase()
	{
	$users = Yii::app()->db->createCommand()
    	->select('*')
    	->from('paket')
    	->queryAll();
	}

	public function tableName()
	{
		return 'paket';
	}

	public function rules()
	{
		return array(
			array('id_tahun, id_bulan, id_skpd, kode_lelang, nama_paket, pagu_paket, nilai_penawaran, nama_pemenang, id_asal_kota, id_status, id_sbr_dana, id_tayang', 'required'),
			array('id_tahun, id_bulan, id_asal_kota, id_status, id_sbr_dana, id_tayang', 'numerical', 'integerOnly'=>true),
			array('id_skpd', 'length', 'max'=>50),
			array('kode_lelang, nilai_hps', 'length', 'max'=>100),
			array('nama_paket', 'length', 'max'=>3500),
			array('url_tayang', 'length', 'max'=>500),
			array('pagu_paket, nilai_penawaran', 'length', 'max'=>250),
			array('nama_pemenang', 'length', 'max'=>350),
			array('code', 'length', 'max'=>1),
			array('id_paket, id_tahun, id_bulan, id_skpd, kode_lelang, nama_paket, url_tayang, pagu_paket, nilai_hps, nilai_penawaran, nama_pemenang, id_asal_kota, id_status, id_sbr_dana, id_tayang, code', 'safe', 'on'=>'search'),
		);
	}

	public function relations()
	{
		return array(
			'bulan' => array(self::BELONGS_TO, 'bulan','id_bulan'),
			'tahun' => array(self::BELONGS_TO, 'tahun','id_tahun'),
			'skpd' => array(self::BELONGS_TO, 'skpd','id_skpd'),
			'sumberdana' => array(self::BELONGS_TO, 'sumberdana','id_sbr_dana'),
			'asalkota' => array(self::BELONGS_TO, 'asalkota','id_asal_kota'),
			'jenisbelanja' => array(self::BELONGS_TO, 'jenisbelanja','id_jb'),
			'jenispenyedia' => array(self::BELONGS_TO, 'jenispenyedia','id_jp'),
			'status' => array(self::BELONGS_TO, 'status','id_status'),
			'pstatus' => array(self::BELONGS_TO, 'pstatus','id_p_status'),
			'tayang' => array(self::BELONGS_TO, 'tayang','id_tayang'),
		);
	}

	public function attributeLabels()
	{
		return array(
			'id_paket' => 'ID Paket',
			'id_tahun' => 'Tahun',
			'id_bulan' => 'Bulan',
			'id_skpd' => 'Nama SKPD',
			'kode_lelang' => 'Kode Lelang',
			'nama_paket' => 'Nama Paket',
			'url_tayang' => 'Link URL',
			'pagu_paket' => 'Pagu Paket',
			'nilai_hps' => 'Nilai HPS',
			'nilai_penawaran' => 'Nilai Penawaran',
			'nama_pemenang' => 'Nama Pemenang',
			'id_asal_kota' => 'Asal Pemenang',
			'id_status' => 'Status',
			'id_sbr_dana' => 'Sumber Dana',
			'id_tayang' => 'Status Tayang',
			'code' => 'Code',
		);
	}

	public function search()
	{
// SELECT `tahun`.`nama_tahun` as `tahun`,   sum(pagu_paket) AS t_pagu, sum(nilai_penawaran) AS t_tawar,   sum(IF( nilai_penawaran >0, (pagu_paket - nilai_penawaran), 0 )) AS selisih,  ( sum(IF( nilai_penawaran >0, (pagu_paket - nilai_penawaran), 0 )) / sum( pagu_paket ) *100 ) AS t_pros, COUNT( nama_paket ) AS jml_paket  FROM `paket`  LEFT JOIN `tahun` ON `paket`.`id_tahun` = `tahun`.`id_tahun`  GROUP BY `paket`.`id_tahun`

		$criteria=new CDbCriteria;
		$criteria->select='t.*,IF( nilai_penawaran >0, (pagu_paket - nilai_penawaran), 0 ) AS efisiensi';
		$criteria->select='t.*,IF( nilai_penawaran >0, (pagu_paket - nilai_penawaran), 0 ) / pagu_paket * 100 AS prosentase';
		$criteria->compare('id_paket',$this->id_paket);
		$criteria->compare('id_tahun',$this->id_tahun);
		$criteria->compare('id_bulan',$this->id_bulan);
		$criteria->compare('id_skpd',$this->id_skpd,true);
		$criteria->compare('kode_lelang',$this->kode_lelang,true);
		$criteria->compare('nama_paket',$this->nama_paket,true);
		$criteria->compare('url_tayang',$this->url_tayang,true);
		$criteria->compare('pagu_paket',$this->pagu_paket,true);
		$criteria->compare('nilai_hps',$this->nilai_hps,true);
		$criteria->compare('nilai_penawaran',$this->nilai_penawaran,true);
		$criteria->compare('nama_pemenang',$this->nama_pemenang,true);
		$criteria->compare('id_asal_kota',$this->id_asal_kota);
		$criteria->compare('id_status',$this->id_status);
		$criteria->compare('id_sbr_dana',$this->id_sbr_dana);
		$criteria->compare('id_tayang',$this->id_tayang);
		$criteria->compare('code',$this->code,true);

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
			'sort'=>array(
            		'defaultOrder'=>'id_paket DESC'),
			        'pagination' => array(
             			'pageSize' => 50,
    	    		),
		));
	}

	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}
}