PHP Zend_Db_Select::distinct方法代码示例

 function getById($jobid)
 {
     // do Bacula ACLs
     Zend_Loader::loadClass('Job');
     $table = new Job();
     if (!$table->isJobIdExists($jobid)) {
         return FALSE;
     }
     $select = new Zend_Db_Select($this->db);
     switch ($this->db_adapter) {
         case 'PDO_SQLITE':
             // bug http://framework.zend.com/issues/browse/ZF-884
             $select->distinct();
             $select->from(array('l' => 'Log'), array('logid' => 'LogId', 'jobid' => 'JobId', 'LogTime' => 'Time', 'logtext' => 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
             break;
         default:
             // mysql, postgresql
             $select->distinct();
             $select->from(array('l' => 'Log'), array('LogId', 'JobId', 'LogTime' => 'Time', 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
     }
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $stmt = $select->query();
     return $stmt->fetchAll();
 }

 public function init()
 {
     // Form
     $this->setMethod('GET')->setAction(Zend_Controller_Front::getInstance()->getRouter()->assemble(array()))->addAttribs(array('id' => 'filter_form', 'class' => 'global_form_box'));
     $this->clearDecorators()->addDecorator('FormElements')->addDecorator('Form')->addDecorator('HtmlTag', array('tag' => 'div', 'class' => 'search'))->addDecorator('HtmlTag2', array('tag' => 'div', 'class' => 'clear'));
     // Element: moduleName
     $jobTypesTable = Engine_Api::_()->getDbtable('jobTypes', 'core');
     $modulesTable = Engine_Api::_()->getDbtable('modules', 'core');
     $select = new Zend_Db_Select($jobTypesTable->getAdapter());
     $modules = $select->distinct()->from($jobTypesTable->info('name'), 'module')->joinLeft($modulesTable->info('name'), 'module=name', array('title'))->where($modulesTable->info('name') . '.enabled = ?', 1)->order('title')->query()->fetchAll();
     $multiOptions = array('' => '');
     foreach ($modules as $module) {
         if (!empty($module['title'])) {
             $multiOptions[$module['module']] = $module['title'];
         }
     }
     $this->addElement('Select', 'moduleName', array('label' => 'Module', 'multiOptions' => $multiOptions, 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: jobType
     $jobTypes = $jobTypesTable->select()->from($jobTypesTable, array('jobtype_id', 'title', 'type'))->order('title')->query()->fetchAll();
     $multiOptions = array('' => '');
     foreach ($jobTypes as $jobType) {
         $multiOptions[$jobType['jobtype_id']] = $jobType['title'];
     }
     $this->addElement('Select', 'jobtype_id', array('label' => 'Type', 'multiOptions' => $multiOptions, 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: state
     $this->addElement('Select', 'state', array('label' => 'State', 'multiOptions' => array('' => '', 'pending' => 'Pending', 'active' => 'Active', 'sleeping' => 'Sleeping', 'failed' => 'Failed', 'cancelled' => 'Cancelled', 'completed' => 'Completed', 'timeout' => 'Timed Out'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: order
     $this->addElement('Select', 'order', array('label' => 'Order', 'multiOptions' => array('job_id' => 'ID', 'jobtype_id' => 'Type', 'state' => 'State', 'progress' => 'Progress', 'creation_date' => 'Queued Date', 'started_date' => 'Started Date', 'completion_date' => 'Completed Date', 'priority' => 'Priority'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: direction
     $this->addElement('Select', 'direction', array('label' => 'Direction', 'multiOptions' => array('ASC' => 'A-Z', 'DESC' => 'Z-A'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: submit
     $this->addElement('Button', 'execute', array('label' => 'Filter', 'ignore' => true, 'type' => 'submit', 'decorators' => array('ViewHelper', array('HtmlTag', array('tag' => 'div', 'class' => 'buttons')), array('HtmlTag2', array('tag' => 'div')))));
 }

 /**
  * Get info Volumes with Status of media: Disabled, Error
  *
  */
 function getProblemVolumes($order = null)
 {
     $db = Zend_Registry::get('db_bacula');
     // make select from multiple tables
     $select = new Zend_Db_Select($db);
     $select->distinct();
     $select->from(array('m' => 'Media'), array("MediaId", 'PoolId', 'StorageId', 'VolumeName', 'VolStatus', 'VolBytes', 'MaxVolBytes', 'VolJobs', 'VolRetention', 'Recycle', 'Slot', 'InChanger', 'MediaType', 'FirstWritten', 'LastWritten'));
     $select->joinLeft(array('p' => 'Pool'), 'm.PoolId = p.PoolId', array('PoolName' => 'p.Name'));
     $select->where("VolStatus IN ('Error', 'Disabled')");
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $result = $select->query()->fetchAll(null, $order);
     // do Bacula ACLs
     return $this->bacula_acl->doBaculaAcl($result, 'poolname', 'pool');
 }

 private function _buildSelects()
 {
     $this->_executePlugins();
     if (is_null($this->_oSelect)) {
         $oAdapter = Zend_Db_Table_Abstract::getDefaultAdapter();
         $oSelect = new Zend_Db_Select($oAdapter);
         $oCountSelect = new Zend_Db_Select($oAdapter);
         $bCount = false;
         foreach ($this->_aSQL as $aSQL) {
             foreach ($aSQL as $sMethod => &$val) {
                 if (isset($val[self::METHOD_COUNT])) {
                     $bCount = true;
                     $count = array($val[self::METHOD_COUNT]);
                     unset($val[self::METHOD_COUNT]);
                 } else {
                     $count = null;
                 }
                 //select
                 call_user_func_array(array($oSelect, $sMethod), $val);
                 //counter
                 if ($sMethod != 'order' and $sMethod != 'limitPage') {
                     if ($sMethod == 'from') {
                         $valFrom = $val;
                         if ($count) {
                             $valFrom['fields'] = $count;
                             $oCountSelect->distinct(false);
                         }
                         //$valFrom['fields'] = array('COUNT(*)');
                         call_user_func_array(array($oCountSelect, $sMethod), $valFrom);
                     } else {
                         $valFrom = $val;
                         $valFrom['fields'] = array(array());
                         call_user_func_array(array($oCountSelect, $sMethod), $valFrom);
                     }
                 }
             }
         }
         if ($bCount) {
             $this->_oSelect = $oSelect;
             $this->_oSelectCount = $oCountSelect;
         } else {
             $oCountSelectSec = new Zend_Db_Select($oAdapter);
             $oCountSelectSec->from($oCountSelect, array(new Zend_Db_Expr('COUNT(*) AS ' . Zend_Paginator_Adapter_DbSelect::ROW_COUNT_COLUMN)));
             $this->_oSelect = $oSelect;
             $this->_oSelectCount = $oCountSelectSec;
         }
     }
 }

 public function init()
 {
     // Form
     $this->setMethod('GET')->setAction(Zend_Controller_Front::getInstance()->getRouter()->assemble(array()))->addAttribs(array('id' => 'filter_form', 'class' => 'global_form_box'));
     $this->clearDecorators()->addDecorator('FormElements')->addDecorator('Form')->addDecorator('HtmlTag', array('tag' => 'div', 'class' => 'search'))->addDecorator('HtmlTag2', array('tag' => 'div', 'class' => 'clear'));
     // Element: category
     $tasksTable = Engine_Api::_()->getDbtable('tasks', 'core');
     $select = new Zend_Db_Select($tasksTable->getAdapter());
     $categories = $select->distinct()->from($tasksTable->info('name'), 'category')->where('module IN(?)', (array) Engine_Api::_()->getDbtable('modules', 'core')->getEnabledModuleNames())->query()->fetchAll();
     $multiOptions = array('' => '');
     foreach ($categories as $category) {
         $multiOptions[$category['category']] = ucwords(str_replace('_', ' ', $category['category']));
     }
     $this->addElement('Select', 'category', array('label' => 'Category', 'multiOptions' => $multiOptions, 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: moduleName
     $modulesTable = Engine_Api::_()->getDbtable('modules', 'core');
     $select = new Zend_Db_Select($tasksTable->getAdapter());
     $modules = $select->distinct()->from($tasksTable->info('name'), 'module')->joinLeft($modulesTable->info('name'), 'module=name', array('title'))->where($modulesTable->info('name') . '.enabled = ?', 1)->query()->fetchAll();
     $multiOptions = array('' => '');
     foreach ($modules as $module) {
         if (!empty($module['title'])) {
             $multiOptions[$module['module']] = $module['title'];
         }
     }
     $this->addElement('Select', 'moduleName', array('label' => 'Module', 'multiOptions' => $multiOptions, 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: type
     $this->addElement('Select', 'type', array('label' => 'Type', 'multiOptions' => array('' => '', 'automatic' => 'Automatic', 'semi-automatic' => 'Semi-automatic', 'manual' => 'Manual', 'disabled' => 'Disabled'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: state
     $this->addElement('Select', 'state', array('label' => 'State', 'multiOptions' => array('' => '', 'active' => 'Active', 'sleeping' => 'Sleeping', 'ready' => 'Ready', 'dormant' => 'Dormant'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: order
     $this->addElement('Select', 'order', array('label' => 'Order', 'multiOptions' => array('task_id' => 'ID', 'title' => 'Name', 'timeout' => 'Timeout', 'type' => 'Type', 'state' => 'State', 'category' => 'Category', 'module' => 'Module', 'enabled' => 'Enabled', 'priority' => 'Priority'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: direction
     $this->addElement('Select', 'direction', array('label' => 'Direction', 'multiOptions' => array('ASC' => 'A-Z', 'DESC' => 'Z-A'), 'decorators' => array('ViewHelper', array('Label', array('tag' => null, 'placement' => 'PREPEND')), array('HtmlTag', array('tag' => 'div')))));
     // Element: submit
     $this->addElement('Button', 'execute', array('label' => 'Filter', 'ignore' => true, 'type' => 'submit', 'decorators' => array('ViewHelper', array('HtmlTag', array('tag' => 'div', 'class' => 'buttons')), array('HtmlTag2', array('tag' => 'div')))));
 }

 /**
  * Set select distinct
  *
  * @param   bool $flag
  *
  * @return  Varien_Data_Collection_Db
  */
 public function distinct($flag)
 {
     $this->_select->distinct($flag);
     return $this;
 }

 public function getNumberOfTaxes()
 {
     $tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
     $select = new Zend_Db_Select($this->_db);
     $select->distinct(true);
     $select->from($tbl_prefix . 'tax', array('tax_id'));
     $select->joinInner($tbl_prefix . 'invoice_item_tax', $tbl_prefix . "invoice_item_tax.tax_id = " . $tbl_prefix . "tax.tax_id", NULL);
     $select->joinInner($tbl_prefix . "invoice_items", $tbl_prefix . "invoice_items.id = " . $tbl_prefix . "invoice_item_tax.invoice_item_id", NULL);
     $select->where($tbl_prefix . "invoice_items.invoice_id=?", $this->_id);
     $select->group(array($tbl_prefix . "tax.tax_id"));
     $result = $this->_db->fetchAll($select);
     return count($result);
 }

 /**
  * LogBook full text search
  *
  */
 function findLogBookByText($id_text, $sort_order)
 {
     if (!isset($id_text)) {
         return;
     }
     $id_text = trim($id_text);
     $db = Zend_Db_Table::getAdapter('db_bacula');
     $select = new Zend_Db_Select($db);
     switch ($this->db_adapter) {
         case 'PDO_MYSQL':
             $select->distinct();
             $select->from(array('l' => 'webacula_logbook'), array('logId', 'logDateCreate', 'logDateLast', 'logTxt', 'logTypeId', 'logIsDel'));
             $select->joinLeft(array('t' => 'webacula_logtype'), 'l.logTypeId = t.typeId', array('typeId', 'typeDesc'));
             $select->where(' MATCH(logTxt) AGAINST ("' . $id_text . '" WITH QUERY EXPANSION)');
             break;
         case 'PDO_PGSQL':
             $select->distinct();
             $select->from(array('l' => 'webacula_logbook'), array('logId', 'logDateCreate', 'logDateLast', 'logTxt', 'logTypeId', 'logIsDel'));
             $select->joinLeft(array('t' => 'webacula_logtype'), 'l.logTypeId = t.typeId', array('typeId', 'typeDesc'));
             $str = preg_replace('/\\s+/', ' & ', $id_text);
             $select->where(" to_tsvector(logtxt) @@ to_tsquery(" . $db->quote($str) . ")");
             break;
         case 'PDO_SQLITE':
             // see also http://www.sqlite.org/cvstrac/wiki?p=FtsOne "FTS1 module is available in SQLite version 3.3.8 and later
             $select->distinct();
             $select->from(array('l' => 'webacula_logbook'), array('logid' => 'logId', 'logdatecreate' => 'logDateCreate', 'logdatelast' => 'logDateLast', 'logtxt' => 'logTxt', 'logtypeid' => 'logTypeId', 'logisdel' => 'logIsDel'));
             $select->joinLeft(array('t' => 'webacula_logtype'), 'l.logTypeId = t.typeId', array('typeid' => 'typeId', 'typedesc' => 'typeDesc'));
             $select->where(' logTxt LIKE  "%' . $id_text . '%"');
             break;
     }
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $result = $select->query();
     return $result;
 }

 /**
  * Put data from DB to 2D array
  *
  * @param integer $y - year - YYYY
  * @param integer $m - month
  * @param integer $d - day
  * @return array 2D
  */
 public function getDataTimeline($date)
 {
     if (!empty($date)) {
         $db = Zend_Db_Table::getDefaultAdapter();
         // ********** query 1 *******************
         $select = new Zend_Db_Select($db);
         $select->distinct();
         switch ($this->db_adapter) {
             case 'PDO_MYSQL':
                 // http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
                 // %H - Hour (00..23)
                 // %i - Minutes, numeric (00..59)
                 $select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "DATE_FORMAT(StartTime, '%H')", 'm1' => "DATE_FORMAT(StartTime, '%i')", 'h2' => "DATE_FORMAT(EndTime, '%H')", 'm2' => "DATE_FORMAT(EndTime, '%i')"));
                 break;
             case 'PDO_PGSQL':
                 // PostgreSQL
                 // http://www.postgresql.org/docs/8.0/static/functions-formatting.html
                 // HH24 - hour of day (00-23)
                 // MI   - minute (00-59)
                 $select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "to_char(StartTime, 'HH24')", 'm1' => "to_char(StartTime, 'MI')", 'h2' => "to_char(EndTime, 'HH24')", 'm2' => "to_char(EndTime, 'MI')"));
                 break;
             case 'PDO_SQLITE':
                 // SQLite3 Documentation
                 // http://sqlite.org/lang_datefunc.html
                 // %H - Hour (00 .. 23)
                 // %M - Minute (00 .. 59)
                 // bug http://framework.zend.com/issues/browse/ZF-884
                 $select->from('Job', array('jobid' => 'JobId', 'name' => 'Name', 'starttime' => 'StartTime', 'endtime' => 'EndTime', 'joberrors' => 'JobErrors', 'jobstatus' => 'JobStatus', 'h1' => "(strftime('%H',StartTime))", 'm1' => "(strftime('%M',StartTime))", 'h2' => "(strftime('%H',EndTime))", 'm2' => "(strftime('%M',EndTime))"));
                 break;
         }
         $select->joinLeft(array('sd' => 'webacula_jobdesc'), 'Job.Name = sd.name_job');
         $select->where("(StartTime >= '{$date} 00:00:00') AND (StartTime <= '{$date} 23:59:59') AND\n                (EndTime <= '{$date} 23:59:59')");
         $select->order('JobId');
         //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
         $stmt = $select->query();
         $result = $stmt->fetchAll();
         // забиваем результат в массив
         $i = 0;
         foreach ($result as $line) {
             $this->atime[$i]['jobid'] = $line['jobid'];
             $this->atime[$i]['name'] = $line['name'];
             $this->atime[$i]['short_desc'] = $line['short_desc'];
             $this->atime[$i]['h1'] = $line['h1'] + $line['m1'] / 60;
             $this->atime[$i]['h2'] = $line['h2'] + $line['m2'] / 60;
             $this->atime[$i]['flag'] = 0;
             // признак, что задание уложилось в сутки
             $this->atime[$i]['start'] = $line['starttime'];
             $this->atime[$i]['end'] = $line['endtime'];
             $i++;
         }
         $select->reset();
         unset($select);
         unset($stmt);
         // задания, старт или окончание которых лежат за пределами указанных суток
         // задание началось ранее
         // либо задание еще длится
         // ********** query 2 *******************
         $select = new Zend_Db_Select($db);
         $select->distinct();
         switch ($this->db_adapter) {
             case 'PDO_MYSQL':
                 // http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
                 // %H - Hour (00..23)
                 // %i - Minutes, numeric (00..59)
                 $select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "DATE_FORMAT(StartTime, '%H')", 'm1' => "DATE_FORMAT(StartTime, '%i')", 'h2' => "DATE_FORMAT(EndTime, '%H')", 'm2' => "DATE_FORMAT(EndTime, '%i')"));
                 break;
             case 'PDO_PGSQL':
                 // PostgreSQL
                 // http://www.postgresql.org/docs/8.0/static/functions-formatting.html
                 // HH24 - hour of day (00-23)
                 // MI   - minute (00-59)
                 $select->from('Job', array('JobId', 'Name', 'StartTime', 'EndTime', 'JobErrors', 'JobStatus', 'h1' => "to_char(StartTime, 'HH24')", 'm1' => "to_char(StartTime, 'MI')", 'h2' => "to_char(EndTime, 'HH24')", 'm2' => "to_char(EndTime, 'MI')"));
                 break;
             case 'PDO_SQLITE':
                 // SQLite3 Documentation
                 // http://sqlite.org/lang_datefunc.html
                 // %H - Hour (00 .. 23)
                 // %M - Minute (00 .. 59)
                 // bug http://framework.zend.com/issues/browse/ZF-884
                 $select->from('Job', array('jobid' => 'JobId', 'name' => 'Name', 'starttime' => 'StartTime', 'endtime' => 'EndTime', 'joberrors' => 'JobErrors', 'jobstatus' => 'JobStatus', 'h1' => "(strftime('%H',StartTime))", 'm1' => "(strftime('%M',StartTime))", 'h2' => "(strftime('%H',EndTime))", 'm2' => "(strftime('%M',EndTime))"));
                 break;
         }
         $select->joinLeft(array('sd' => 'webacula_jobdesc'), 'Job.Name = sd.name_job');
         $select->where("( \n                    (EndTime > '{$date} 00:00:00') AND \n                    ( \n                        (EndTime <= '{$date} 23:59:59') OR \n                        ( \n                            (EndTime IS NULL) AND \n                            (Job.jobstatus IN ('R', 'B', 'A', 'F', 'S', 'm', 'M', 's', 'j', 'c', 'd', 't', 'p', 'i', 'a', 'l', 'L') ) \n                        ) \n                    ) \n                )\n                AND\n                (StartTime < '{$date} 00:00:00')");
         $select->order('JobId');
         //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
         $stmt = $select->query();
         $result = $stmt->fetchAll();
         // забиваем результат в массив
         foreach ($result as $line) {
             $this->atime[$i]['jobid'] = $line['jobid'];
             $this->atime[$i]['name'] = $line['name'];
//.........这里部分代码省略.........

 /**
  * Find File(s) by Path/Name file
  *
  * @param $path     with trailing slash
  * @param $namefile
  * @param $client
  * @param $limit
  * @param $type_search      [ordinary | like | regexp]
  * @return rows
  */
 function getByFileName($path, $namefile, $client, $limit, $type_search)
 {
     if (isset($namefile, $client)) {
         $select = new Zend_Db_Select($this->db);
         $select->distinct();
         $select->limit($limit);
         switch ($this->db_adapter) {
             case 'PDO_MYSQL':
                 $select->from(array('j' => 'Job'), array('JobId', 'Type', 'JobName' => 'Name', 'Level', 'ClientId', 'StartTime' => "DATE_FORMAT(j.StartTime, '%y-%b-%d %H:%i')", 'EndTime' => "DATE_FORMAT(j.EndTime,   '%y-%b-%d %H:%i')", 'VolSessionId', 'VolSessionTime', 'JobFiles', 'JobBytes', 'JobErrors', 'Reviewed', 'PoolId', 'FileSetId', 'PurgedFiles', 'JobStatus', 'DurationTime' => 'TIMEDIFF(EndTime, StartTime)'));
                 $select->joinLeft('File', 'j.JobId = File.JobId', array('File.JobId', 'File.FileId'));
                 $select->joinLeft('Filename', 'File.FilenameId = Filename.FilenameId', array('FileName' => 'Filename.Name'));
                 $select->joinLeft('Path', 'File.PathId = Path.PathId', array('Path' => 'Path.Path'));
                 $select->joinLeft('Status', 'j.JobStatus = Status.JobStatus', array('JobStatusLong' => 'Status.JobStatusLong'));
                 $select->joinLeft('Client', 'j.ClientId = Client.ClientId', array('ClientName' => 'Client.Name'));
                 $select->joinLeft('Pool', 'j.PoolId = Pool.PoolId', array('PoolName' => 'Pool.Name'));
                 $select->joinLeft('FileSet', 'j.FileSetId = FileSet.FileSetId', array('FileSet' => 'FileSet.FileSet'));
                 $select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
                 break;
             case 'PDO_PGSQL':
                 // PostgreSQL
                 // http://www.postgresql.org/docs/8.0/static/functions-datetime.html
                 $select->from(array('j' => 'Job'), array('JobId', 'Type', 'JobName' => 'Name', 'Level', 'ClientId', 'StartTime', 'EndTime', 'VolSessionId', 'VolSessionTime', 'JobFiles', 'JobBytes', 'JobErrors', 'Reviewed', 'PoolId', 'FileSetId', 'PurgedFiles', 'JobStatus', 'DurationTime' => '(EndTime - StartTime)'));
                 $select->joinLeft('File', 'j.JobId = File.JobId', array('File.JobId', 'File.FileId'));
                 $select->joinLeft('Filename', 'File.FilenameId = Filename.FilenameId', array('FileName' => 'Filename.Name'));
                 $select->joinLeft('Path', 'File.PathId = Path.PathId', array('Path' => 'Path.Path'));
                 $select->joinLeft('Status', 'j.JobStatus = Status.JobStatus', array('JobStatusLong' => 'Status.JobStatusLong'));
                 $select->joinLeft('Client', 'j.ClientId = Client.ClientId', array('ClientName' => 'Client.Name'));
                 $select->joinLeft('Pool', 'j.PoolId = Pool.PoolId', array('PoolName' => 'Pool.Name'));
                 $select->joinLeft('FileSet', 'j.FileSetId = FileSet.FileSetId', array('FileSet' => 'FileSet.FileSet'));
                 $select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
                 break;
             case 'PDO_SQLITE':
                 // SQLite3 Documentation
                 // http://sqlite.org/lang_datefunc.html
                 // workaround of bug http://framework.zend.com/issues/browse/ZF-884
                 $select->from(array('j' => 'Job'), array('jobid' => 'JobId', 'type' => 'Type', 'JobName' => 'Name', 'level' => 'Level', 'clientid' => 'ClientId', 'starttime' => 'StartTime', 'endtime' => 'EndTime', 'volsessionid' => 'VolSessionId', 'volsessiontime' => 'VolSessionTime', 'jobfiles' => 'JobFiles', 'jobbytes' => 'JobBytes', 'joberrors' => 'JobErrors', 'reviewed' => 'Reviewed', 'poolid' => 'PoolId', 'filesetid' => 'FileSetId', 'purgedfiles' => 'PurgedFiles', 'jobstatus' => 'JobStatus', 'DurationTime' => "(strftime('%H:%M:%S',strftime('%s',EndTime) - strftime('%s',StartTime),'unixepoch'))"));
                 $select->joinLeft('File', 'j.JobId = File.JobId', array('File.JobId', 'File.FileId'));
                 $select->joinLeft('Filename', 'File.FilenameId = Filename.FilenameId', array('FileName' => 'Filename.Name'));
                 $select->joinLeft('Path', 'File.PathId = Path.PathId', array('path' => 'Path.Path'));
                 $select->joinLeft('Status', 'j.JobStatus = Status.JobStatus', array('jobstatuslong' => 'Status.JobStatusLong'));
                 $select->joinLeft('Client', 'j.ClientId = Client.ClientId', array('clientname' => 'Client.Name'));
                 $select->joinLeft('Pool', 'j.PoolId = Pool.PoolId', array('poolname' => 'Pool.Name'));
                 $select->joinLeft('FileSet', 'j.FileSetId = FileSet.FileSetId', array('fileset' => 'FileSet.FileSet'));
                 $select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
                 break;
         }
         // terminated jobs
         $select->where("j.JobStatus IN ('T', 'E', 'e', 'f', 'A', 'W')");
         if (!empty($path)) {
             $select->where($this->myMakeWhere('Path.Path', $path, $type_search));
         }
         $select->where($this->myMakeWhere('Filename.Name', $namefile, $type_search));
         if (!empty($client)) {
             $select->where($this->db->quoteInto("Client.Name = ?", $client));
         }
         $select->order(array("StartTime"));
         //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     }
     $stmt = $select->query();
     // do Bacula ACLs
     return $this->bacula_acl->doBaculaAcl($stmt->fetchAll(), 'jobname', 'job');
 }

 /**
  * Returns an array of objects queried from the given t41_Object_Collection instance parameters
  * 
  * The given collection is populated if it comes empty of members.
  * 
  * In any other case, this method doesn't directly populate the collection. This action is under the responsability of 
  * the caller. For example, the t41_Object_Collection::find() method takes care of it.
  * 
  * @param t41\ObjectModel\Collection $collection
  * @param boolean|array $returnCount true = counting, array = stats on listed properties
  * @param string $subOp complex operation like SUM or AVG
  * @return array
  */
 public function find(ObjectModel\Collection $collection, $returnCount = false, $subOp = null)
 {
     $this->_class = $class = $collection->getDataObject()->getClass();
     $table = $this->_getTableFromClass($class);
     if (!$table) {
         throw new Exception('MISSING_DBTABLE_PARAM');
     }
     // primary key is either part of the mapper configuration or 'id'
     $pkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : \t41\Backend::DEFAULT_PKEY;
     if (is_array($pkey)) {
         $composite = array();
         /* @var $obj t41\Backend\Key */
         foreach ($pkey as $obj) {
             $composite[] = sprintf('TRIM(%s)', $table . '.' . $obj->getName());
             $composite[] = Backend\Mapper::VALUES_SEPARATOR;
         }
         $pkey = sprintf("CONCAT(%s) AS %s", implode(',', $composite), Backend::DEFAULT_PKEY);
     } else {
         $pkey = $table . '.' . $pkey;
     }
     $this->_connect();
     /* @var $select \Zend_Db_Select */
     $this->_select = $this->_ressource->select();
     // detect if query is of stat-kind
     if ($returnCount) {
         switch ($subOp) {
             case ObjectModel::CALC_SUM:
                 $expressions = array();
                 foreach ($returnCount as $propKey => $property) {
                     $prop = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $propKey) : $propKey;
                     $expressions[] = sprintf('SUM(%s.%s)', $table, $prop);
                 }
                 $subOpExpr = implode('+', $expressions);
                 break;
             case ObjectModel::CALC_AVG:
                 $subOpExpr = sprintf('AVG(%s)', $returnCount);
                 break;
             default:
                 $subOpExpr = 'COUNT(*)';
                 break;
         }
         $this->_select->from($table, new \Zend_Db_Expr($subOpExpr . " AS " . \t41\Backend::MAX_ROWS_IDENTIFIER));
     } else {
         $this->_select->distinct();
         $this->_select->from($table, $pkey);
     }
     $this->_alreadyJoined = array();
     /* @var $condition t41\Backend\Condition */
     foreach ($collection->getConditions() as $conditionArray) {
         // combo conditions
         if ($conditionArray[0] instanceof Condition\Combo) {
             $statement = array();
             foreach ($conditionArray[0]->getConditions() as $condition) {
                 $statement[] = $this->_parseCondition($condition[0], $this->_select, $table);
             }
             $statement = implode(' OR ', $statement);
             switch ($conditionArray[1]) {
                 case Condition::MODE_OR:
                     $this->_select->orWhere($statement);
                     break;
                 case Condition::MODE_AND:
                 default:
                     $this->_select->where($statement);
                     break;
             }
             continue;
         }
         // optional table where the column may be
         $jtable = '';
         // condition object is in the first key
         $condition = $conditionArray[0];
         /* does condition contain another condition object ? */
         if ($condition->isRecursive()) {
             while ($condition->isRecursive()) {
                 $property = $condition->getProperty();
                 $parent = $property->getParent() ? $property->getParent()->getId() : $table;
                 $condition = $condition->getCondition();
                 if ($jtable) {
                     $parentTable = $jtable;
                 } else {
                     if ($parent) {
                         $parentTable = $this->_mapper ? $this->_mapper->getDatastore($parent) : $parent;
                     } else {
                         $parentTable = $table;
                     }
                 }
                 $jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof'));
//.........这里部分代码省略.........

	protected function _synchronizeStatisticData() {
		$db = $this -> getDb();

		// update favorite_count for videos
		$db -> query("UPDATE `engine4_video_videos` AS `videos` " . "SET `favorite_count` = " . "(SELECT COUNT(*) from `engine4_video_favorites` AS `favorites` WHERE `favorites`.video_id = `videos`.video_id)");

		// update video_count for playlists
		$db -> query("UPDATE `engine4_video_playlists` AS `playlists` " . "SET `video_count` = " . "(SELECT COUNT(*) from `engine4_video_playlistassoc` AS `playlistassoc` WHERE `playlists`.playlist_id = `playlistassoc`.playlist_id)");

		// remove all statistic data about a video
		$db -> query("DELETE FROM `engine4_video_signatures`");

		// Check if it's already been placed
		$select = new Zend_Db_Select($db);
		$select -> distinct() -> from('engine4_video_videos', 'owner_id');
		foreach ($select->query()->fetchAll() as $userId) {
			$selectCountVideo = new Zend_Db_Select($db);
			$selectCountVideo -> from('engine4_video_videos', 'count(*)') -> where('owner_id = ?', $userId['owner_id']);
			$query = $selectCountVideo -> query();
			$videoCount = $query -> fetchColumn();
			$db -> insert('engine4_video_signatures', array('user_id' => $userId['owner_id'], 'creation_date' => date('Y-m-d H:i:s'), 'modified_date' => date('Y-m-d H:i:s'), 'video_count' => $videoCount));
		}
	}