<?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
 */
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 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;
  }

  /**
   *
   * @return CDbCommand the command to search the expirind auths (SmartPlateAuth objects)
   *        
   */
  public static function findExpiring($list_code, $search, $timeRange, $status, $sort)
  {
    $cmd = self::getSmartPlateAuthCommand ();
    
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_NAME_ASC)
    {
      $cmd->order ( array (
          'ut.Nome ASC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_NAME_DESC)
    {
      $cmd->order ( array (
          'ut.Nome DESC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_SURNAME_ASC)
    {
      $cmd->order ( array (
          'ut.Cognome ASC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_SURNAME_DESC)
    {
      $cmd->order ( array (
          'ut.Cognome DESC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_PLATE_ASC)
    {
      $cmd->order ( array (
          'vt.Targa ASC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_PLATE_DESC)
    {
      $cmd->order ( array (
          'vt.Targa DESC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_GATE_ASC)
    {
      $cmd->order ( array (
          'gt.Descrizione ASC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_GATE_DESC)
    {
      $cmd->order ( array (
          'gt.Descrizione DESC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_IDTIME_ASC)
    {
      $cmd->order ( array (
          'tt.Descrizione ASC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_IDTIME_DESC)
    {
      $cmd->order ( array (
          'tt.Descrizione DESC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_STARTTIME_ASC)
    {
      $cmd->order ( array (
          'at.DataInizio ASC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_STARTTIME_DESC)
    {
      $cmd->order ( array (
          'at.DataInizio DESC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_ENDTIME_ASC)
    {
      $cmd->order ( array (
          'at.DataFine ASC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_ENDTIME_DESC)
    {
      $cmd->order ( array (
          'at.DataFine DESC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_CREATETIME_ASC)
    {
      $cmd->order ( array (
          'wlt.epoch ASC' 
      ) );
    }
    if ($sort == PlateAuthorization::FIND_EXPIRING_SORT_CREATETIME_DESC)
    {
      $cmd->order ( array (
          'wlt.epoch DESC' 
      ) );
    }
    
    $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 );
    
    if ($status == self::FIND_EXPIRING_STATUS_DISABLED)
    {
      $whereConditions [] = '(wlt.enabled<>1)';
    }
    
    if ($status == self::FIND_EXPIRING_STATUS_ENABLED)
    {
      $whereConditions [] = '(wlt.enabled=1)';
    }
    
    if ($status == self::FIND_EXPIRING_STATUS_CONFIRMED)
    {
      $whereConditions [] = '(wlt.to_be_confirmed<>1)';
    }
    
    if ($status == self::FIND_EXPIRING_STATUS_TOBECONFIRMED)
    {
      $whereConditions [] = '(wlt.to_be_confirmed=1)';
    }
    
    if ($status == self::FIND_EXPIRING_STATUS_EXPIRED)
    {
      $whereConditions [] = '(at.DataFine<:now)';
      $whereParameters = $whereParameters + array (
          ':now' => $datetimeNOWString 
      );
    }
    
    if ($status == self::FIND_EXPIRING_STATUS_VALID)
    {
      $whereConditions [] = '(at.DataInizio <= :now AND :now < at.DataFine)';
      $whereParameters = $whereParameters + array (
          ':now' => $datetimeNOWString 
      );
    }
    
    if ($status == self::FIND_EXPIRING_STATUS_EXPIRING)
    {
      $whereConditions [] = '(at.DataFine>=:now)';
      $whereParameters = $whereParameters + array (
          ':now' => $datetimeNOWString 
      );
    }
    
    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'";
      $searchQuery [] = "wlt.ID LIKE '$search'";
      $searchQuery [] = "ut.Nome LIKE '$search'";
      $searchQuery [] = "ut.Cognome LIKE '$search'";
      $searchQuery [] = "vt.Targa LIKE '$search'";
      $searchQuery [] = "at.DataInizio LIKE '$search'";
      $searchQuery [] = "at.DataFine LIKE '$search'";
      $searchQuery [] = "tt.Descrizione LIKE '$search'";
      $searchQuery [] = "(wlt.TimeID=-1 AND '" . $labelsearchtime . "' LIKE '$search')";
      $searchQuery [] = "gt.Descrizione LIKE '$search'";
      $searchQuery [] = "gs.name LIKE '$search'";
      $searchQuery [] = "gp.name LIKE '$search'";
      $searchQuery [] = "(wlt.GateID=-1 AND '" . $labelsearchgate . "' 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 ( $row ["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 ();
  }
}