<?php

/**
 * This is the model class for table "whitelisttable".
 *
 * The followings are the available columns in table 'whitelisttable':
 * @property string $ID
 * @property string $Descrizione
 * @property string $Transito
 * @property string $VehicleID
 * @property string $GateID
 * @property string $TimeID
 * @property string $UserID
 * @property string $AbbonamentoID
 * @property string $Speciale
 * @property string $epoch
 * @property string $Category
 * @property string $Note
 * @property string $list_code
 * @property boolean $enabled
 * @property boolean $to_be_confirmed
 * @property integer $insertid
 * @property Vehicle vehicle
 * @property VehicleOwner user
 * @property ValidityPeriod validityPeriod
 * @property PlateList plate_list
 * @property User submitted_by
 * @property string $type
 * @property string $action
 * @property string $GateID_type
 * @property integer $id_create_user
 * @property string $request_code
 * @property string $authorization_code
 */
class PlateAuthorization extends ActiveRecord {

    const TYPE_BLACKLIST = 0;
    const TYPE_WHITELIST = 1;
    const TYPE_DISABLE = - 1;
    const GATEID_TYPE_GATE = 'gate';
    const GATEID_TYPE_GROUP = 'group';
    const GATEID_TYPE_SET = 'set';
    const FIND_EXPIRING_STATUS_ALL = 'both';
    const FIND_EXPIRING_STATUS_EXPIRING = 'expiring';
    const FIND_EXPIRING_STATUS_VALID = 'valid';
    const FIND_EXPIRING_STATUS_EXPIRED = 'expired';
    const FIND_EXPIRING_STATUS_TOBECONFIRMED = 'tobeconfirmed';
    const FIND_EXPIRING_STATUS_CONFIRMED = 'confirmed';
    const FIND_EXPIRING_STATUS_ENABLED = 'enabled';
    const FIND_EXPIRING_STATUS_DISABLED = 'disabled';
    const FIND_EXPIRING_TIMERANGE_ALL = - 1;
    const FIND_EXPIRING_SORT_NONE = 'none';
    const FIND_EXPIRING_SORT_NAME_ASC = 'name_asc';
    const FIND_EXPIRING_SORT_NAME_DESC = 'name_desc';
    const FIND_EXPIRING_SORT_SURNAME_ASC = 'surname_asc';
    const FIND_EXPIRING_SORT_SURNAME_DESC = 'surname_desc';
    const FIND_EXPIRING_SORT_PLATE_ASC = 'plate_asc';
    const FIND_EXPIRING_SORT_PLATE_DESC = 'plate_desc';
    const FIND_EXPIRING_SORT_GATE_ASC = 'gate_asc';
    const FIND_EXPIRING_SORT_GATE_DESC = 'gate_desc';
    const FIND_EXPIRING_SORT_IDTIME_ASC = 'idtime_asc';
    const FIND_EXPIRING_SORT_IDTIME_DESC = 'idtime_desc';
    const FIND_EXPIRING_SORT_STARTTIME_ASC = 'start_time_asc';
    const FIND_EXPIRING_SORT_STARTTIME_DESC = 'start_time_desc';
    const FIND_EXPIRING_SORT_ENDTIME_ASC = 'end_time_asc';
    const FIND_EXPIRING_SORT_ENDTIME_DESC = 'end_time_desc';
    const FIND_EXPIRING_SORT_CREATETIME_ASC = 'create_time_asc';
    const FIND_EXPIRING_SORT_CREATETIME_DESC = 'create_time_desc';

    public $GateID = - 1;
    public $TimeID = - 1;

    /**
     * Returns the static model of the specified AR class.
     *
     * @param string $className
     *          active record class name.
     * @return PlateAuthorization 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 'whitelisttable';
    }

    /**
     *
     * @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(
                'Descrizione, Transito, Category, Note',
                'length',
                'max' => 250
            ),
            array(
                'VehicleID, GateID, TimeID, UserID, AbbonamentoID, Speciale',
                'length',
                'max' => 20
            ),
            array(
                'epoch',
                'safe'
            ),
            array(
                'list_code',
                'safe'
            ),
            array(
                'enabled',
                'safe'
            ),
            array(
                'type',
                'safe'
            ),
            array(
                'to_be_confirmed',
                'safe'
            ),
            array(
                'GateID_type',
                'safe'
            ),
            // The following rule is used by search().
            // Please remove those attributes that should not be searched.
            array(
                'ID, Descrizione, Transito, VehicleID, GateID, TimeID, UserID, AbbonamentoID, Speciale, epoch, Category, Note, vehiclePlate, listName',
                'safe',
                'on' => 'search'
            )
        );
    }

    /**
     *
     * @return array relational rules.
     */
    public function relations() {
        return array(
            'vehicle' => array(
                self::BELONGS_TO,
                'Vehicle',
                'VehicleID'
            ),
            'user' => array(
                self::BELONGS_TO,
                'VehicleOwner',
                'UserID'
            ),
            'validityPeriod' => array(
                self::BELONGS_TO,
                'ValidityPeriod',
                'AbbonamentoID'
            ),
            'plate_list' => array(
                self::BELONGS_TO,
                'PlateList',
                array(
                    'list_code' => 'code'
                )
            ),
            'submitted_by' => array(
                self::BELONGS_TO,
                'User',
                'insertid'
            ),
            'actions' => array(
                self::HAS_MANY,
                'PlateAuthorizationAction',
                'id_plate_authorization',
                'order' => 'epoch ASC'
            )
        );
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array();
    }

    /**
     *
     * @return array customized attribute labels (name=>label)
     */
    public function myAttributeLabels() {
        return array(
            'ID' => 'ID',
            'Descrizione' => t('Descrizione'),
            'Transito' => t('Transito'),
            'VehicleID' => t('Vehicle'),
            'GateID' => t('Gate'),
            'TimeID' => t('Time Schedule'),
            'UserID' => t('User'),
            'AbbonamentoID' => t('Abbonamento'),
            'Speciale' => t('Speciale'),
            'epoch' => t('Epoch'),
            'Category' => t('Category'),
            'Note' => t('Note'),
            'listName' => t('List Name'),
            'vehiclePlate' => t('Vehicle Plate'),
            'Targa' => t('Vehicle Plate'),
            'type' => t('Type'),
            'action' => t('Action'),
            'GateID_type' => t('GateID Type')
        );
    }

    public function getAuthorizationAccessType() {
        if ($this->validityPeriod == null)
            return self::TYPE_DISABLE;
        else if ($this->validityPeriod->Abilitato == 1)
            return self::TYPE_WHITELIST;
        else if ($this->validityPeriod->Abilitato == 0)
            return self::TYPE_BLACKLIST;

        return self::TYPE_DISABLE;
    }

    public function byVehicleId($id) {
        $this->getDbCriteria()
                ->mergeWith(array(
                    'condition' => 'VehicleID = :vehicle_id',
                    'params' => array(
                        ':vehicle_id' => $id
                    )
        ));

        return $this;
    }

    public function byVehicleOwnerId($id) {
        $this->getDbCriteria()
                ->mergeWith(array(
                    'condition' => 'UserID = :user_id',
                    'params' => array(
                        ':user_id' => $id
                    )
        ));

        return $this;
    }

    public function byListCode($code) {
        $this->getDbCriteria()
                ->mergeWith(array(
                    'condition' => 't.list_code = :lstcd',
                    'params' => array(
                        ':lstcd' => $code
                    )
        ));

        return $this;
    }

    public function byValidity($now = null) {
        $now = DateHelper::ensureObjectEx($now);
        $this->getDbCriteria()->join = "INNER JOIN abbonamentotable at ON at.ID=t.AbbonamentoID";
        $this->getDbCriteria()
                ->mergeWith(array(
                    'condition' => '(at.DataInizio <= :now AND :now < at.DataFine)',
                    'params' => array(
                        ':now' => $now
                    )
        ));

        return $this;
    }

    protected function afterSave() {
        $this->updatePlateListLastEvent();
        return parent::afterSave();
    }

    protected function afterDelete() {
        $this->updatePlateListLastEvent();
        return parent::afterDelete();
    }

    protected function updatePlateListLastEvent() {
        $list = $this->plate_list;
        $list->last_change = new DateTime ();
        return $list->save();
    }

    public function byDescription($what) {
        $this->getDbCriteria()
                ->addCondition("(Descrizione LIKE '%$what%')");

        return $this;
    }

    public function byValidityPeriodId($id) {
        $this->getDbCriteria()
                ->mergeWith(array(
                    'condition' => 'AbbonamentoID = :validity_period_id',
                    'params' => array(
                        ':validity_period_id' => $id
                    )
        ));

        return $this;
    }

    public static function deleteByListCode($list_code) {
        PlateAuthorization::model()->deleteAllByAttributes(array(
            'list_code' => $list_code
        ));
    }

    /**
     * element for searching with join
     */
    private $_vehiclePlate = null;

    /**
     *
     * @return string
     */
    public function getVehiclePlate() {
        if ($this->_vehiclePlate === null && $this->vehicle !== null) {
            $this->_vehiclePlate = $this->vehicle->Targa;
        }
        return $this->_vehiclePlate;
    }

    public function setVehiclePlate($value) {
        $this->_vehiclePlate = $value;
    }

    private $_listName = null;

    public function getListName() {
        if ($this->_listName === null && $this->plate_list !== null) {
            $this->_listName = $this->plate_list->name;
        }
        return $this->_listName;
    }

    public function setListName($value) {
        $this->_listName = $value;
    }

    /**
     * is a valid plate auth (does user/abbonamento/vehicle exist?)
     *
     * @return boolean
     */
    public function isValid() {
        return VehicleOwner::model()->count('id=:x', array(
                    ':x' => $this->UserID
                )) > 0 && ValidityPeriod::model()->count('id=:x', array(
                    ':x' => $this->AbbonamentoID
                )) > 0 && Vehicle::model()->count('id=:x', array(
                    ':x' => $this->VehicleID
                )) > 0;
    }

    /**
     * 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.
        ini_set('max_execution_time', 300); // 300 seconds = 5 minutes

        $criteria = new CDbCriteria ();

        $criteria->compare('t.ID', $this->ID, true);
        $criteria->compare('Descrizione', $this->Descrizione, true);
        $criteria->compare('t.Transito', $this->Transito, true);
        $criteria->compare('t.VehicleID', $this->VehicleID, true);
        $criteria->compare('t.GateID', $this->GateID, true);
        $criteria->compare('t.TimeID', $this->TimeID, true);
        $criteria->compare('t.UserID', $this->UserID, true);
        $criteria->compare('t.AbbonamentoID', $this->AbbonamentoID, true);
        $criteria->compare('t.Speciale', $this->Speciale, true);
        $criteria->compare('t.epoch', $this->epoch, true);
        $criteria->compare('t.Category', $this->Category, true);
        $criteria->compare('t.Note', $this->Note, true);
        $criteria->compare('vehicle.Targa', $this->vehiclePlate, true);
        $criteria->compare('plate_list.name', $this->listName, true);

        $criteria->addCondition('plate_list.visible=1');
        $criteria->addCondition('vehicle.privacy_level>0');

        $criteria->with = array(
            'vehicle' => array(
                'joinType' => 'INNER JOIN'
            ),
            'plate_list' => array(
                'joinType' => 'INNER JOIN'
            )
        );
        $criteria->together = true;
        /*
         * $sort = new CSort();
         * $sort->attributes = array(
         * 'defaultOrder'=>'t.create_time DESC',
         * 'content'=>array(
         * 'asc'=>'t.content',
         * 'desc'=>'t.content desc',
         * ),
         * 'status'=>array(
         * 'asc'=>'t.status',
         * 'desc'=>'t.status desc',
         * ),
         * 'author'=>array(
         * 'asc'=>'t.author',
         * 'desc'=>'t.author desc',
         * ),
         * 'postTitle'=>array(
         * 'asc'=>'post.title',
         * 'desc'=>'post.title desc',
         * ),
         * );
         */
        $dataProv = new CActiveDataProvider($this, array(
            'criteria' => $criteria
        ));
        // 'sort'=>$sort,

        $dataProv->setTotalItemCount(1000000000);

        return $dataProv;
    }

    public function searchForWizard() {
        $criteria = new CDbCriteria ();

        $criteria->compare('t.ID', $this->ID, true);
        $criteria->compare('Descrizione', $this->Descrizione, true);
        $criteria->compare('t.Transito', $this->Transito, true);
        $criteria->compare('t.VehicleID', $this->VehicleID);
        // $criteria->compare('t.GateID',$this->GateID,true);
        // $criteria->compare('t.TimeID',$this->TimeID,true);
        $criteria->compare('t.UserID', $this->UserID);
        $criteria->compare('t.AbbonamentoID', $this->AbbonamentoID);

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

        return $dataProv;
    }

    
    public static function applyfindExpiringOrder($cmd, $sort, $addJoin = false)
    {   
       
        
        $criteria_1 = new CDbCriteria();
        $criteria_2 = new CDbCriteria();
        $sel = preg_replace("/`/", '', $cmd->select); 
        $criteria_2->select = $sel;
        
        $column_field_name = array (
                array("ut.ID AS user_ID","ut.Nome AS Nome","ut.Cognome AS Cognome","ut.Note AS Note","ut.ID1 AS ID1","ut.ID2 AS ID2","ut.Categoria AS Categoria"
                    ,"ut.epoch AS epoch","ut.list_code AS list_code","ut.email AS email","ut.address AS address","ut.city AS city","ut.phone AS phone"
                    ,"ut.personal_id_code AS personal_id_code"),
                array("vt.ID as vehicletable_ID","vt.Descrizione as Descrizione","vt.Classe as Classe","vt.Abilitato as Abilitato","vt.AreaID as AreaID"
                    ,"vt.Targa as Targa","vt.Nationality as Nationality","vt.UserID as UserID","vt.ID1 as ID1","vt.ID2 as ID2"
                    ,"vt.ID3 as ID3","vt.ID4 as ID4","vt.epoch as epoch","vt.source as source","vt.privacy_level as privacy_level"
                    ,"vt.action as action","vt.list_code as list_code","vt.type as type","vt.category as category","vt.note as note"),
                array("gt.ID AS gate_ID","gt.Descrizione AS Descrizione","gt.Matricola AS Matricola","gt.ValidationMaxDelay AS ValidationMaxDelay"
                    ,"gt.Type AS Type","gt.Gate AS Gate","gt.Enable AS Enable","gt.LinkedGate AS LinkedGate","gt.Code AS Code","gt.epoch AS epoch"
                    ,"gt.AreaSourceID AS AreaSourceID","gt.AreaDestinationID AS AreaDestinationID","gt.Status AS Status","gt.LastLifeSignal AS LastLifeSignal"
                    ,"gt.Homologation AS Homologation","gt.LastEventID AS LastEventID","gt.LifeSignalTimeOut AS LifeSignalTimeOut","gt.LastEventTime AS LastEventTime"
                    ,"gt.GateType AS GateType","gt.id_group AS id_group","gt.speed_limit AS speed_limit","gt.last_exported_event_id AS last_exported_event_id"
                    ,"gt.last_exported_event_time AS last_exported_event_time","gt.last_exported_event_validation_time AS last_exported_event_validation_time"
                    ,"gt.latitude AS latitude","gt.longitude AS longitude","gt.altitude AS altitude","gt.short_name AS short_name"
                    ,"gt.live_url AS live_url","gt.work_mode AS work_mode","gt.immunity_from_s AS immunity_from_s","gt.immunity_to_s AS immunity_to_s"
                    ,"gt.device_model AS device_model","gt.function AS function","gt.mapx AS mapx","gt.mapy AS mapy","gt.last_section_event AS last_section_event"
                    ,"gt.orderIndex AS orderIndex","gt.master_gate AS master_gate","gt.life_signal_filter_id AS life_signal_filter_id"
                    ,"gt.life_signal_filter_type AS life_signal_filter_type","gt.yellow_time_set AS yellow_time_set"),
                array("tt.ID AS timetable_ID","tt.Descrizione AS Descrizione","tt.epoch AS epoch","tt.start_day AS start_day","tt.start_month AS start_month"
                    ,"tt.end_day AS end_day","tt.end_month AS end_month","tt.icon AS icon","tt.for_run AS for_run","tt.for_whitelisttable AS for_whitelisttable"
                    ,"tt.for_ztl AS for_ztl"),
                array("at.ID AS abbonamento_ID","at.Descrizione AS Descrizione","at.Tipo AS Tipo","at.DataInizio AS DataInizio","at.DataFine AS DataFine"
                    ,"at.Abilitato AS Abilitato","at.Credito AS Credito","at.Autorizzazione AS Autorizzazione","at.UserID AS UserID","at.epoch AS epoch"
                    ,"at.Codice AS Codice","at.list_code AS list_code")
                    );
        switch ($sort) {
            case PlateAuthorization::FIND_EXPIRING_SORT_NAME_ASC:
                $cmd->order(array(
                'ut.Nome ASC'
                    ));
                
                if($addJoin){
                    $criteria_1->select = $column_field_name[0];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('usertable ut', 'wlt.UserID=ut.ID');
                }
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_NAME_DESC:
                $cmd->order(array(
                'ut.Nome DESC'
                    ));
                
                if($addJoin){
                    $criteria_1->select = $column_field_name[0];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('usertable ut', 'wlt.UserID=ut.ID');
                }
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_SURNAME_ASC:
                $cmd->order(array(
                'ut.Cognome ASC'
                    ));
                
                if($addJoin){
                    $criteria_1->select = $column_field_name[0];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('usertable ut', 'wlt.UserID=ut.ID');
                }
                    break;
            case PlateAuthorization::FIND_EXPIRING_SORT_SURNAME_DESC:
                $cmd->order(array(
                'ut.Cognome DESC'
                    ));
                if($addJoin){
                    $criteria_1->select = $column_field_name[0];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('usertable ut', 'wlt.UserID=ut.ID');
                }
                    break;
            case PlateAuthorization::FIND_EXPIRING_SORT_PLATE_ASC:
                $cmd->order(array(
                'vt.Targa ASC'
                    ));
                if($addJoin){
                    $criteria_1->select = $column_field_name[1];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('vehicletable vt', 'wlt.VehicleID=vt.ID');
                }
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_PLATE_DESC:
                $cmd->order(array(
                'vt.Targa DESC'
                    ));
                if($addJoin)
                {
                    $criteria_1->select = $column_field_name[1];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('vehicletable vt', 'wlt.VehicleID=vt.ID');
                }
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_GATE_ASC:
                $cmd->order(array(
                'gt.Descrizione ASC'
                    ));
                if($addJoin)
                    {
                    $criteria_1->select = $column_field_name[2];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('gatetable gt', 'wlt.GateID=gt.ID AND wlt.GateID_type=\'' . PlateAuthorization::GATEID_TYPE_GATE . '\'');
                    }
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_GATE_DESC:
                $cmd->order(array(
                'gt.Descrizione DESC'
                    ));
                if($addJoin)
                    {
                    $criteria_1->select = $column_field_name[2];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('gatetable gt', 'wlt.GateID=gt.ID AND wlt.GateID_type=\'' . PlateAuthorization::GATEID_TYPE_GATE . '\'');
                    }
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_IDTIME_ASC:
                $cmd->order(array(
                'tt.Descrizione ASC'
                    ));
                if($addJoin)
                    {
                    $criteria_1->select = $column_field_name[3];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('timetable tt', 'wlt.TimeID=tt.ID');
                    }
                    
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_IDTIME_DESC:
                $cmd->order(array(
                'tt.Descrizione DESC'
                    ));
                if($addJoin)
                    {
                    $criteria_1->select = $column_field_name[3];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('timetable tt', 'wlt.TimeID=tt.ID');
                    }
                    
                
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_STARTTIME_ASC:
                $cmd->order(array(
                'at.DataInizio ASC'
                    ));
                
                if($addJoin)
                    {
                    $criteria_1->select = $column_field_name[4];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    
                    $cmd->leftJoin('abbonamentotable at', 'wlt.AbbonamentoID=at.ID');
                  
                    }
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_STARTTIME_DESC:
                $cmd->order(array(
                'at.DataInizio DESC'
                    ));
             
                if($addJoin)
                    {
                $criteria_1->select = $column_field_name[4];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('abbonamentotable at', 'wlt.AbbonamentoID=at.ID');
                    
                    
                    }
                
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_ENDTIME_ASC:
                $cmd->order(array(
                'at.DataFine ASC'
                    ));
                
                if($addJoin)
                {
                    $criteria_1->select = $column_field_name[4];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('abbonamentotable at', 'wlt.AbbonamentoID=at.ID');
                }
                
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_ENDTIME_DESC:
                $cmd->order(array(
                'at.DataFine DESC'
                    ));
                
                if($addJoin)
                    {
                    $criteria_1->select = $column_field_name[4];
                    $criteria_1->mergeWith($criteria_2);
                    $cmd->select = $criteria_1->select;
                    $cmd->leftJoin('abbonamentotable at', 'wlt.AbbonamentoID=at.ID');
               
                    }
                
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_CREATETIME_ASC:
                $cmd->order(array(
                'wlt.epoch ASC'
                    ));
                    
                break;
            case PlateAuthorization::FIND_EXPIRING_SORT_CREATETIME_DESC:
                $cmd->order(array(
                'wlt.epoch DESC'
                    ));
                break;
        }
        
         
        return $cmd;
    }
    /**
     *
     * @return CDbCommand the command to search the expirind auths (SmartPlateAuth objects)
     *        
     */
    public static function findExpiring($list_code, $search, $timeRange, $status, $sort, $category = null)
    {
        $cmd = self::getSmartPlateAuthCommand();
        
        $cmd = self::applyfindExpiringOrder($cmd, $sort);

        $whereConditions = array();
        $whereParameters = array();

        if ($list_code != - 1) {
            // single list mode
            $whereConditions [] = '(wlt.list_code=:lstcode)';
            $whereParameters = $whereParameters + array(
                ':lstcode' => $list_code
            );
        } else {
            // global mode
            $userLists = PlateList::getCurrentUserLists();
            $userListsCodes = array_convert($userLists, function ($pl) {
                return $pl->code;
            });
            $whereConditions [] = '(wlt.list_code IN (' . implode(",", $userListsCodes) . '))';
        }

        if (!user()->checkAccess('whitelist_visibility')) {
            $whereConditions [] = '(pl.mode<>:plmodew)';
            $whereParameters = $whereParameters + array(
                ':plmodew' => PlateList::MODE_WHITE_LIST
            );
        }

        if (!user()->checkAccess('blacklist_visibility')) {
            $whereConditions [] = '(pl.mode<>:plmodeb)';
            $whereParameters = $whereParameters + array(
                ':plmodeb' => PlateList::MODE_BLACK_LIST
            );
        }

        $datetimeNOW = new DateTime ();
        $datetimeNOWString = DateHelper::formatISO9075DateTime($datetimeNOW);

        switch ($status) {
            case self::FIND_EXPIRING_STATUS_DISABLED:
                $whereConditions [] = '(wlt.enabled<>1)';
                break;
            case self::FIND_EXPIRING_STATUS_ENABLED:
                $whereConditions [] = '(wlt.enabled=1)';
                break;

            case self::FIND_EXPIRING_STATUS_CONFIRMED:
                $whereConditions [] = '(wlt.to_be_confirmed<>1)';
                break;

            case self::FIND_EXPIRING_STATUS_TOBECONFIRMED:
                $whereConditions [] = '(wlt.to_be_confirmed=1)';
                break;

            case self::FIND_EXPIRING_STATUS_EXPIRED:
                $whereConditions [] = '(at.DataFine<:now)';
                $whereParameters = $whereParameters + array(
                    ':now' => $datetimeNOWString
                );
                break;

            case self::FIND_EXPIRING_STATUS_VALID:
                $whereConditions [] = '(at.DataInizio <= :now AND :now < at.DataFine)';
                $whereParameters = $whereParameters + array(
                    ':now' => $datetimeNOWString
                );
                break;

            case self::FIND_EXPIRING_STATUS_EXPIRING:
                $whereConditions [] = '(at.DataFine>=:now)';
                $whereParameters = $whereParameters + array(
                    ':now' => $datetimeNOWString
                );
                break;
        }

        if ($category != null) {
            $whereConditions [] = '(vt.category = :category)';
            $whereParameters = $whereParameters + array(
                ':category' => $category
            );
        }

        if ($timeRange != self::FIND_EXPIRING_TIMERANGE_ALL) {
            $inteval = date_interval_create_from_date_string("$timeRange days");

            if ($status == self::FIND_EXPIRING_STATUS_EXPIRED || $status == self::FIND_EXPIRING_STATUS_ALL) {
                $datetimeLB = new DateTime ();
                $datetimeLB->sub($inteval);
                $datetimeLBString = DateHelper::formatISO9075DateTime($datetimeLB);

                $whereConditions [] = '(at.DataFine>=:lowerbound)';
                $whereParameters = $whereParameters + array(
                    ':lowerbound' => $datetimeLBString
                );
            }

            if ($status == self::FIND_EXPIRING_STATUS_EXPIRING || $status == self::FIND_EXPIRING_STATUS_ALL) {
                $datetimeUB = new DateTime ();
                $datetimeUB->add($inteval);
                $datetimeUBString = DateHelper::formatISO9075DateTime($datetimeUB);

                $whereConditions [] = '(at.DataFine<=:upperbound)';
                $whereParameters = $whereParameters + array(
                    ':upperbound' => $datetimeUBString
                );
            }
        }

        if (!empty($search)) {
            $labelsearchgate = t("All gates");
            $labelsearchtime = t("Always");
            $plateAuthTypes = paramdeep('plateAuthorizations', 'types');
            if (!is_array($plateAuthTypes))
                $plateAuthTypes = array();
            $plateAuthTypes = array_keys(array_remove($plateAuthTypes, function ($key, $value, $search1) {
                        return !containsStr($value, $search1);
                    }, $search));
            foreach ($plateAuthTypes as $k => $v)
                $plateAuthTypes [$k] = "'$v'";

            $search = "$search%";

            $searchQuery = array();

            if ($list_code == - 1) { // global mode
                $searchQuery [] = "pl.name LIKE '$search'";
            }
            if (is_numeric($search)) {
                $searchQuery [] = "wlt.ID LIKE '$search'";
            }
            $searchQuery [] = "ut.Nome LIKE '$search'";
            $searchQuery [] = "ut.Cognome LIKE '$search'";
            $searchQuery [] = "vt.Targa LIKE '$search'";
            if (DateTime::createFromFormat('Y-m-d G:i:s', $search) !== FALSE) {
                // it's a date
                $searchQuery [] = "at.DataInizio LIKE '$search'";
                $searchQuery [] = "at.DataFine LIKE '$search'";
            }
            $searchQuery [] = "tt.Descrizione LIKE '$search'";
            if (stripos(strtolower($labelsearchtime), strtolower($search)) !== FALSE) {
                $searchQuery [] = "(wlt.TimeID=-1 AND '" . $labelsearchtime . "' LIKE '$search')";
                $searchQuery [] = "(wlt.GateID=-1 AND '" . $labelsearchgate . "' LIKE '$search')";
            }
            $searchQuery [] = "gt.Descrizione LIKE '$search'";
            $searchQuery [] = "gs.name LIKE '$search'";
            $searchQuery [] = "gp.name LIKE '$search'";
            $searchQuery [] = "ut.address LIKE '$search'";
            $searchQuery [] = "ut.city LIKE '$search'";
            if (count($plateAuthTypes) > 0) {
                $searchQuery [] = "wlt.type IN (" . implode(",", $plateAuthTypes) . ")";
            }
            $searchQuery = implode(" OR ", $searchQuery);

            $whereConditions [] = "($searchQuery)";
        }


        if (count($whereConditions) > 0)
            $cmd->where(implode(" AND ", $whereConditions), $whereParameters);

        return $cmd;
    }

    /**
     *
     * @return string[]
     */
    public static function getSmartPlateAuthSelect() {
        return array(
            'wlt.list_code as IDLista',
            'wlt.ID as IDPermesso',
            'at.ID as IDAbbonamento',
            'vt.ID as IDVeicolo',
            'ut.ID as IDUtente',
            'ut.Nome as Nome',
            'ut.Cognome as Cognome',
            'vt.Targa as Targa',
            'vt.Nationality as Nationality',
            'at.DataInizio as DataInizio',
            'at.DataFine as DataFine',
            'wlt.enabled as Enabled',
            'wlt.to_be_confirmed as ToBeConfirmed',
            'wlt.GateID as IDGate',
            'wlt.GateID_type as IDGate_type',
            'wlt.TimeID as IDTime',
            'wlt.epoch as Epoch',
            'gt.Descrizione as GateDescr',
            'tt.Descrizione as TimeDescr',
            'pl.name as Lista',
            'pl.mode as ModeLista',
            'wlt.Descrizione as DescrizionePermesso',
            'vt.Descrizione as DescrizioneVeicolo',
            'at.Descrizione as DescrizionePeriodo'
        );
    }

    /**
     *
     * @return CDbCommand
     */
    public static function getSmartPlateAuthCommand()
    {
        $cmd = Yii::app()->db->createCommand();

        $cmd->select(self::getSmartPlateAuthSelect())
                ->from('whitelisttable wlt')
                ->join('abbonamentotable at', 'wlt.AbbonamentoID=at.ID')
                ->join('vehicletable vt', 'wlt.VehicleID=vt.ID')
                ->join('usertable ut', 'wlt.UserID=ut.ID')
                ->join('plate_list pl', 'wlt.list_code=pl.Code')
                ->leftJoin('timetable tt', 'wlt.TimeID=tt.ID')
                ->leftJoin('gatetable gt', 'wlt.GateID=gt.ID AND wlt.GateID_type=\'' . PlateAuthorization::GATEID_TYPE_GATE . '\'')
                ->leftJoin('gate_set gs', 'wlt.GateID=gs.ID AND wlt.GateID_type=\'' . PlateAuthorization::GATEID_TYPE_SET . '\'')
                ->leftJoin('gate_group gp', 'wlt.GateID=gp.ID AND wlt.GateID_type=\'' . PlateAuthorization::GATEID_TYPE_GROUP . '\'');

        return $cmd;
    }

    const FIND_SMART_BY_PLATE_FILTERUSERLIST_NONE = false;
    const FIND_SMART_BY_PLATE_FILTERUSERLIST_WHITELIST = 'whitelist';
    const FIND_SMART_BY_PLATE_FILTERUSERLIST_BLACKLIST = 'blacklist';
    const FIND_SMART_BY_PLATE_FILTERUSERLIST_BOTH = 'both';

    /**
     *
     * @param string $plate          
     * @param string|false $filterUserList
     *          (SEE CONST PlateAuthorization ::FIND_SMART_BY_PLATE_FILTERUSERLIST_*)
     * @param integer $limit          
     * @param integer $offset          
     * @return SmartPlateAuth[]
     */
    public static function findSmartByPlate($plate, $nationality, $filterUserList = self::FIND_SMART_BY_PLATE_FILTERUSERLIST_NONE, $limit = -1, $offset = -1) {
        $cmd = self::getSmartPlateAuthCommand();


        // order
        $cmd->order(array(
            'at.DataFine DESC'
        ));

        // limit
        if ($limit > 0) {
            if ($offset > 0)
                $cmd->limit($limit, $offset);
            else
                $cmd->limit($limit);
        }

        // where
        $whereText = array();
        $whereParams = array();

        $whereText [] = "(vt.Targa=:plt)";
        $whereParams [':plt'] = $plate;

        $whereText [] = "(vt.Nationality=:nat or vt.Nationality = '' or vt.Nationality is null)";
        $whereParams [':nat'] = $nationality;

        if ($filterUserList != self::FIND_SMART_BY_PLATE_FILTERUSERLIST_NONE) {
            /** @var PlateList[] $lists */
            $lists = array();
            if ($filterUserList == self::FIND_SMART_BY_PLATE_FILTERUSERLIST_WHITELIST)
                $lists = PlateList::getCurrentUserWhiteLists();
            if ($filterUserList == self::FIND_SMART_BY_PLATE_FILTERUSERLIST_BLACKLIST)
                $lists = PlateList::getCurrentUserBlackLists();
            if ($filterUserList == self::FIND_SMART_BY_PLATE_FILTERUSERLIST_BOTH)
                $lists = PlateList::getCurrentUserLists();
            if (count($lists) <= 0)
                return array(); // bypass search

            $whereText [] = "(wlt.list_code IN (" . implode_ex(",", $lists, null, '$v->code') . "))";
        }

        if (count($whereText) > 0)
            $cmd->where(implode(" AND ", $whereText), $whereParams);

        // data
        $data = array();
        foreach ($cmd->queryAll() as $row)
            $data [] = PlateAuthorization::createPermessoFromRowStatic($row);

        return $data;
    }

    /**
     *
     * @return SmartPlateAuth
     */
    public function getPermesso($returnUrl = null) {
        return PlateAuthorization::createPermessoFromRowStatic(PlateAuthorization::findSmartObjectFromId($this->ID), $returnUrl);
    }

    /**
     *
     * @param Array $row          
     * @param string $returnUrl          
     * @return SmartPlateAuth
     */
    public static function createPermessoFromRowStatic($row, $returnUrl = null) {
        $permesso = new SmartPlateAuth($row);

        $permesso->ListaIsBlack = $permesso->ModeLista == PlateList::MODE_BLACK_LIST;
        $permesso->ListaIsWhite = $permesso->ModeLista == PlateList::MODE_WHITE_LIST;

        if ($returnUrl == null)
            if ($permesso->IDLista != null)
                $returnUrl = Yii::app()->createUrl('plateAuthorization/whiteListStatus', array(
                    'plateListId' => $permesso->IDLista
                ));
            else
                $returnUrl = Yii::app()->createUrl('plateAuthorization');

        $permesso->url_return = $returnUrl;

        $permesso->url_renew = Yii::app()->createUrl('plateAuthorization/renewView', array(
            'plateAuthId' => $permesso->IDPermesso,
            'returnUrl' => $returnUrl
        ));
        $permesso->url_activate = Yii::app()->createUrl('plateAuthorization/activate', array(
            'plateAuthId' => $permesso->IDPermesso,
            'returnUrl' => $returnUrl
        ));
        $permesso->url_revoke = Yii::app()->createUrl('plateAuthorization/revoke', array(
            'plateAuthId' => $permesso->IDPermesso,
            'returnUrl' => $returnUrl
        ));
        $permesso->url_confirm = Yii::app()->createUrl('plateAuthorization/confirm', array(
            'plateAuthId' => $permesso->IDPermesso,
            'returnUrl' => $returnUrl
        ));
        $permesso->url_delete = Yii::app()->createUrl('plateAuthorization/whiteListWizardDelete', array(
            'listId' => $permesso->IDLista,
            'objType' => 'auth',
            'objId' => $permesso->IDPermesso,
            'returnUrl' => $returnUrl
        ));
        $permesso->url_view = Yii::app()->createUrl('plateAuthorization/' . $permesso->IDPermesso);
        $permesso->url_print = Yii::app()->createUrl('plateAuthorization/print', array(
            'id' => $permesso->IDPermesso
        ));

        switch ($permesso->IDGate_type) {
            case self::GATEID_TYPE_GATE :
                $permesso->GateObj = Gate::model()->findByPk($permesso->IDGate);
                break;
            case self::GATEID_TYPE_GROUP :
                $permesso->GateGroupObj = GateGroup::model()->findByPk($permesso->IDGate);
                break;
            case self::GATEID_TYPE_SET :
                $permesso->GateSetObj = GateSet::model()->findByPk($permesso->IDGate);
                break;
        }
        $permesso->TimeObj = TimeSchedule::model()->findByPk($permesso->IDTime);

        $da = new DateTime ();
        $a = new DateTime($permesso->DataFine);
        $diff = $da->diff($a);
        $expired = ($diff->format('%R') == '-');
        $color = null;

        $permesso->aboutToExpire = false;

        $aboutToExpireTimeout = paramdeep('plateAuthorizations', 'aboutToExpireTimeout');

        if (is_bool($aboutToExpireTimeout)) {
            $permesso->aboutToExpire = $aboutToExpireTimeout;
        } else {
            if ($expired) {
                $permesso->aboutToExpire = true;
            } else {
                $aboutToExpireInterval = date_interval_create_from_date_string($aboutToExpireTimeout);
                $permesso->aboutToExpire = date_interval_compare($diff, $aboutToExpireInterval) < 0;
            }
        }

        if ($permesso->ToBeConfirmed == 1) {
            $status = t('To be confirmed') . ($expired ? ' (' . t('Expired') . ')' : '');
            $color = 'gray';
        } else if ($permesso->Enabled != 1) {
            $status = t('Disabled') . ($expired ? ' (' . t('Expired') . ')' : '');
            $color = 'gray';
        } else {
            $status = $expired ? t('Expired by') . ' ' : t('Valid for') . ' ';
            $color = $expired ? 'black' : 'green';

            if (!$expired) {
                $expirationTimeoutWarning = paramdeep('plateAuthorizations', 'expirationTimeoutWarning');
                $expirationIntervalWarning = date_interval_create_from_date_string($expirationTimeoutWarning);
                if (date_interval_compare($diff, $expirationIntervalWarning) < 0)
                    $color = 'orange';

                $expirationTimeoutCritical = paramdeep('plateAuthorizations', 'expirationTimeoutCritical');
                $expirationIntervalCritical = date_interval_create_from_date_string($expirationTimeoutCritical);
                if (date_interval_compare($diff, $expirationIntervalCritical) < 0)
                    $color = 'red';
            }

            if ($diff->y != 0)
                $status .= ' ' . $diff->format('%y ' . t_by_num($diff->y, 'year', 'years'));

            if ($diff->m != 0)
                $status .= ' ' . $diff->format('%m ' . t_by_num($diff->m, 'month', 'months'));

            if ($diff->d != 0)
                $status .= ' ' . $diff->format('%d ' . t_by_num($diff->d, 'day', 'days'));

            if ($diff->y == 0 && $diff->m == 0 && $diff->d == 0) {
                if ($diff->h != 0)
                    $status .= ' ' . $diff->format('%h ' . t_by_num($diff->h, 'hour', 'hours'));

                if ($diff->i != 0)
                    $status .= ' ' . $diff->format('%i ' . t_by_num($diff->i, 'minute', 'minutes'));

                if ($diff->s != 0)
                    $status .= ' ' . $diff->format('%s ' . t_by_num($diff->s, 'second', 'seconds'));
            }
        }


        $permesso->statusText = $status;
        $permesso->statusColor = $color;
        $permesso->expired = $expired;
        $permesso->elapsed = $diff;

        if ($permesso->IDGate == null || $permesso->IDGate <= 0)
            $permesso->GateDescr = t('Every gate');

        if ($permesso->IDTime == null || $permesso->IDTime <= 0)
            $permesso->TimeDescr = t('Always');

        return $permesso;
    }

    /**
     *
     * @return Array the single auths row got by id (SmartPlateAuth object)
     *        
     */
    public static function findSmartObjectFromId($id) {
        $cmd = self::getSmartPlateAuthCommand();

        $cmd->where('wlt.ID=:id', array(
            ':id' => $id
        ));

        $results = $cmd->queryAll();

        if (isset($results [0]))
            return $results [0];

        return null;
    }

    /**
     *
     * @return string
     */
    public function getSubmitter() {
        $smbtid = $this->insertid;
        if ($smbtid === null)
            return t('Unknown');
        if ($smbtid == 0)
            return t('Event Server');
        $sbmt = $this->submitted_by;
        if ($sbmt == null)
            return t('Unknown');
        return $sbmt->getFullName();
    }

}
