<?php

/**
* This is the model class for table "users".
*
* The followings are the available columns in table 'users':
* @property integer $id_user
* @property string $username
* @property string $password
* @property string $email
* @property string $fullname
* @property string $location
* @property string $website
* @property string $
* @property string
* @property string
* @property string $phone
* @property string $
* @property string $birth
* @property integer $gender
* @property string $facebook
* @property string $twitter
* @property string
* @property string $image
* @property string $background
* @property string $
* @property integer $verified
* @property integer $status
* @property integer $level
* @property integer $division
* @property string $create_time
* @property string $visit_time
* @property string $ipaddress
* @property integer $active
* @property integer $
*/
class Users extends CActiveRecord
{
	public $repeat_password;
	/**
	* Returns the static model of the specified AR class.
	* @param string $className active record class name.
	* @return Users the static model class
	*/
	public static function model($className = __CLASS__)
	{
		return parent::model($className);
	}

	/**
	* @return string the associated database table name
	*/
	public function tableName()
	{
		return 'user';
	}

	/**
	* @return array validation rules for model attributes.
	*/
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			// array('password, repeat_password', 'required', 'on' => 'create'),
			array('username, password, division, level', 'required', 'on' => 'add'),
			array('username, fullname, email, level, phone', 'required', 'on' => 'update'),
			array('username, password, repeat_password, email', 'required', 'on' => 'register'),
			array('gender, status, level, division, active, type, type_member', 'numerical', 'integerOnly' => true),
			array('username, fullname, ipaddress, phone, birth', 'length', 'max' => 32),
			array('password, email', 'length', 'max' => 256),
			array('image, description', 'length', 'max' => 128),

			array('level', 'required', 'on' => 'level'),
			array('active', 'required', 'on' => 'log'),

			// Validation Username
			array('username', 'match', 'pattern' => '/^[A-Za-z0-9_]+$/u', 'message' => 'Username only fill number and alphabet.'),
			array('username', 'filter', 'filter' => 'strtolower'),
			array('password', 'length', 'max' => 64, 'min' => 6),
			array('username', 'unique', 'on' => 'add'),
			array('username', 'unique', 'on' => 'create, register'),
			array('email', 'unique', 'on' => 'create, register'),
			array('email', 'email', 'on' => 'create, register, update'),

			// Validation Avatar and Cover Required
			array('image', 'required', 'on' => 'changeImage'),

			//Validation Password Repeat
			array('repeat_password, password', 'required', 'on' => 'changePassword, resetPassword, register'),
			array('repeat_password', 'length', 'max' => 64, 'min' => 6),
			// array('password', 'compare', 'compareAttribute' => 'repeat_password', 'on' => 'changePassword, resetPassword, create, register'),

			//Validation Image
			array(
				'image', 'file', 'allowEmpty' => false, 'types' => 'jpg, gif, png', 'on' => 'changeImage',
				// 'message' => 'Upload Valid Image!',  // Error message
				// 'wrongType'=>'File type is Invalid',
				// 'minSize'=>10,// 1MB
				// 'maxSize'=>1024,
				// 'maxFiles'=>2,
				// 'tooLarge'=>'File Size Too Large',//Error Message
				// 'tooSmall'=>'File Size Too Small',//Error Message
				// 'tooMany'=>'Too Many Files Uploaded',//Error Message
			),

			// The following rule is used by search().
			// Please remove those attributes that should not be searched.
			array('id_user, username, password, email, fullname, birth, gender, image, status, level, division, create_time, visit_time, type_member, type', 'safe', 'on' => 'search'),
		);
	}

	/**
	* @return array relational rules.
	*/
	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
			'Department' => array(self::BELONGS_TO, 'Department', 'division'),
		);
	}

	/**
	* @return array customized attribute labels (name=>label)
	*/
	public function attributeLabels()
	{
		return array(
			'code' => 'NIK e-KTP',
			'id_user' => 'User ID',
			'create_time' => 'Tanggal Daftar',
			'update_time' => 'Tanggal Update',
			'visit_time' => 'Terakhir Login',
			'fullname' => 'Nama Lengkap',
			'gender' => 'Jenis Kelamin',
			'birth' => 'Tanggal Lahir',
			'email' => 'Email',
			'username' => 'Username',
			'password' => 'Password',
			'description' => 'Jenjang',
			'level' => 'Level',
			'division' => 'Unit Kerja',
			'image' => 'Upload Surat Pernyataan (format pdf)',
			'ipaddress' => 'IP Address',
			'active' => 'Active',
			'status' => 'Status Akun',
			'type' => 'Jenis Kepesertaan',
			'type_member' => 'Kategori Kepesertaan',
			'phone' => 'Kontak',
		);
	}

	/**
	* Retrieves a list of models based on the current search/filter conditions.
	* @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
	*/
	public function search()
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria = new CDbCriteria;
		$criteria->compare('id_user', $this->id_user);
		$criteria->compare('create_time', $this->create_time, true);
		$criteria->compare('update_time', $this->update_time, true);
		$criteria->compare('visit_time', $this->visit_time, true);
		$criteria->compare('fullname', $this->fullname, true);
		$criteria->compare('gender', $this->gender, true);
		$criteria->compare('birth', $this->birth, true);
		$criteria->compare('email', $this->email, true);
		$criteria->compare('username', $this->username, true);
		$criteria->compare('password', $this->password, true);
		$criteria->compare('level', $this->level);
		$criteria->compare('division', $this->division);
		$criteria->compare('image', $this->image, true);
		$criteria->compare('ipaddress', $this->ipaddress, true);
		$criteria->compare('active', $this->active);
		$criteria->compare('status', $this->status);


		return new CActiveDataProvider($this, array(
			'criteria' => $criteria,
		));
	}

	public function activity($UserID)
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria = new CDbCriteria;

		$criteria->compare('id_user', $UserID);
		$criteria->compare('create_time', $this->create_time, true);
		$criteria->compare('update_time', $this->update_time, true);
		$criteria->compare('visit_time', $this->visit_time, true);
		$criteria->compare('fullname', $this->fullname, true);
		$criteria->compare('gender', $this->gender, true);
		$criteria->compare('birth', $this->birth, true);
		$criteria->compare('email', $this->email, true);
		$criteria->compare('username', $this->username, true);
		$criteria->compare('password', $this->password, true);
		$criteria->compare('level', $this->level);
		$criteria->compare('division', $this->division);
		$criteria->compare('image', $this->image, true);
		$criteria->compare('ipaddress', $this->ipaddress, true);
		$criteria->compare('active', $this->active);
		$criteria->compare('status', $this->status);


		return new CActiveDataProvider($this, array(
			'criteria' => $criteria,
		));
	}


	public function team()
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria = new CDbCriteria;
		$criteria->compare('id_user', $this->id_user);
		$criteria->compare('create_time', $this->create_time, true);
		$criteria->compare('update_time', $this->update_time, true);
		$criteria->compare('visit_time', $this->visit_time, true);
		$criteria->compare('fullname', $this->fullname, true);
		$criteria->compare('gender', $this->gender, true);
		$criteria->compare('birth', $this->birth, true);
		$criteria->compare('email', $this->email, true);
		$criteria->compare('username', $this->username, true);
		$criteria->compare('password', $this->password, true);
		$criteria->compare('level', $this->level);
		$criteria->compare('division', $this->division);
		$criteria->compare('image', $this->image, true);
		$criteria->compare('ipaddress', $this->ipaddress, true);
		$criteria->compare('active', $this->active);
		$criteria->compare('status', $this->status);
		$criteria->compare('type_member', $this->type_member);
		$criteria->compare('type', $this->type);
		$criteria->addSearchCondition('level', 3, true, 'AND');
		$criteria->addCondition('fullname is NOT NULL');
		$criteria->order = 'id_user DESC';


		return new CActiveDataProvider($this, array(
			'criteria' => $criteria,
		));
	}


	public static function getSumMember()
	{
		$sql = "SELECT COUNT(id_user) as totalMember FROM Users";
		$command = Yii::app()->db_ppid->createCommand($sql);

		return $command->queryAll();
	}

	protected function beforeSave()
	{
		$this->birth = date('Y-m-d', strtotime($this->birth));
		$this->create_time = date('Y-m-d h:i:s', strtotime($this->create_time));
		return TRUE;
	}

	protected function afterFind()
	{
		// $this->birth = date('d-m-Y', strtotime($this->birth));
		$this->create_time = date('d F Y', strtotime($this->create_time));
		return TRUE;
	}

	public function level($data)
	{
		if ($data == 1) {
			return "Administrator";
		} elseif ($data == 2) {
			return "Penilai";
		} elseif ($data == 3) {
			return "Peserta";
		} else {
			return "-";
		}
	}

	public function status($a)
	{
		if ($a == 0)
			return "Belum di Aktivasi";
		else if ($a == 1)
			return "Sudah di Aktivasi";
		else if ($a == 2)
			return "Pendaftaran di Reject";
		else
			return "-";
	}

	public function active($a)
	{
		if ($a == 0)
			return "Offline";
		else
			return "Online";
	}

	public function type($a)
	{
		if ($a == 1)
			return "Perseorangan";
		else
			return "Kelompok";
	}

	public function typeMember($a)
	{
		if ($a == 1)
			return "Analis Kebijakan";
		else
			return "Non Analis Kebijakan";
	}

	public static function getActiveUsers()
	{
		$sql = "SELECT id_user as id, username, image, active, visit_time FROM users where active=1 order by visit_time DESC limit 10";
		$command = Yii::app()->db_ppid->createCommand($sql);
		return $command->queryAll();
	}

	public static function showFullname($UserID)
	{
		$model = Users::model()->findByPk($UserID);
		return $model->fullname;
	}

	public static function showAvatar($UserID)
	{
		$model = Users::model()->findByPk($UserID);
		return $model->image;
	}


	public static function showType($UserID)
	{
		$model = Users::model()->findByPk($UserID);
		return Users::model()->type($model->type);
	}


	public static function showTypeMember($UserID)
	{
		$model = Users::model()->findByPk($UserID);
		return Users::model()->typeMember($model->type_member);
	}

	public function thumbAvatar($imagefile)
	{
		$image = new EasyImage(Yii::getPathOfAlias('webroot') . '/image/avatar/' . $imagefile);
		$image->scaleAndCrop(128, 128, EasyImage::RESIZE_AUTO);
		$image->save(Yii::getPathOfAlias('webroot') . '/image/avatar/' . $imagefile);
	}

	public function thumbCover($imagefile)
	{
		$image = new EasyImage(Yii::getPathOfAlias('webroot') . '/image//' . $imagefile);
		// $mark = new EasyImage(Yii::getPathOfAlias('webroot').'/image/favicon/favicon.png');
		$image->scaleAndCrop(1400, 512, EasyImage::RESIZE_AUTO);
		// $image->watermark($mark, 20, 20);
		$image->render('gif', 60);
		$image->save(Yii::getPathOfAlias('webroot') . '/image//' . $imagefile);
	}

	public function seo($title)
	{
		return preg_replace('/[^a-z0-9_-]/i', '', strtolower(str_replace(' ', '-', trim($title))));
	}

	public function getDistrict()
	{
		$users = Users::model()->findByPk($this->id_user);
		$data = Department::model()->findByPk($users->division);
		return $users->username . " - " . $data->name;
	}

	public function findDivision($id)
	{
		$data = Department::model()->findByPk($id);
		return $data->name;
	}

	public static function getMember()
	{
		$sql = "SELECT id_user FROM users WHERE level=2";
		$command = YII::app()->db_ppid->createCommand($sql);
		return $command->queryAll();
	}

	public function getFullnameByDepartment($id)
	{
		$data = self::model()->findByAttributes(array('division' => $id));
		if ($data === null) {
			return "-";
		} else {
			return $data->fullname;
		}
	}

	public function getLastLogin($id)
	{
		$data = self::model()->findByAttributes(array('division' => $id));
		if ($data === null) {
			return "-";
		} else {
			return $data->visit_time;
		}
	}

	public static function getDepartment($UserID)
	{
		$model = Users::model()->findByPk($UserID);
		return $model->description;
	}

	public static function getExpiredNotification()
	{
		$sql = "SELECT ( reject_time + INTERVAL 2 DAY ) AS expired_date, now( ) as now_date, DATEDIFF( ( reject_time + INTERVAL 2 DAY ), now( ) ) AS status_expired, status as status_register, fullname, email, id_user FROM user WHERE status = 2 AND  ( reject_time + INTERVAL 2 DAY ) <= now( )";
		$command = Yii::app()->db->createCommand($sql);
		return $command->queryAll();
	}

	public static function getExpiredNotificationMy($id)
	{
		$sql = "SELECT ( reject_time + INTERVAL 2 DAY ) AS expired_date FROM user WHERE status = 2 AND id_user=" . $id;
		$command = Yii::app()->db->createCommand($sql);
		return date('d F Y h:i', strtotime($command->queryScalar()));
	}

	public static function getReportByDate($type,$chart,$date='')
	{
		if($chart==1){
			$sql = "SELECT count(id_user) as totals, date(create_time) as dates FROM user WHERE type=".$type." AND level=3 AND fullname is NOT NULL GROUP BY date(create_time)";
			$command = Yii::app()->db->createCommand($sql);
			return $command->queryAll();
		}elseif($chart==2){
			$sql = "SELECT count(id_user) as totals FROM user WHERE type=".$type." AND level=3 AND fullname is NOT NULL";
			$command = Yii::app()->db->createCommand($sql);
			return $command->queryScalar();
		}elseif($chart==3){
			$sql = "SELECT count(id_user) as totals FROM user WHERE status=".$type." AND level=3 AND fullname is NOT NULL";
			$command = Yii::app()->db->createCommand($sql);
			return $command->queryScalar();
		}elseif($chart==4){
			$sql = "SELECT count(id_user) as totals FROM user WHERE level=3 AND fullname is NOT NULL";
			$command = Yii::app()->db->createCommand($sql);
			return $command->queryScalar();
		}elseif($chart==5){
			$sql = "SELECT count(a.id_user) as totals FROM user as a LEFT JOIN paper as b ON a.id_user=b.researchers_id WHERE a.level=3 AND b.paper_file is NULL AND a.fullname is NOT NULL";
			$command = Yii::app()->db->createCommand($sql);
			return $command->queryScalar();
		}elseif($chart==6){
			$sql = "SELECT count(id_user) as totals FROM user WHERE date(create_time)='".$date."' AND type=".$type." AND level=3 AND fullname is NOT NULL";
			$command = Yii::app()->db->createCommand($sql);
			return $command->queryScalar();
		}
	}


}
