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']; $this->atime[$i]['short_desc'] = $line['short_desc']; $this->atime[$i]['h1'] = 0; $this->atime[$i]['h2'] = $line['h2'] + $line['m2'] / 60; $this->atime[$i]['flag'] = -1; // признак, что задание началось ранее $this->atime[$i]['start'] = $line['starttime']; $this->atime[$i]['end'] = $line['endtime']; $i++; } $select->reset(); unset($select); unset($stmt); // задание закончилось позднее // либо задание еще длится // ********** query 3 ******************* $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))")); } $select->joinLeft(array('sd' => 'webacula_jobdesc'), 'Job.Name = sd.name_job'); $select->where("( \n (StartTime >= '{$date} 00:00:00') AND (StartTime <= '{$date} 23:59:59') \n ) AND\n\t\t\t\t( \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 )"); $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']; $this->atime[$i]['short_desc'] = $line['short_desc']; $this->atime[$i]['h1'] = $line['h1'] + $line['m1'] / 60; $this->atime[$i]['h2'] = 23.9; $this->atime[$i]['flag'] = 1; // признак, что задание окончилось позднее $this->atime[$i]['start'] = $line['starttime']; $this->atime[$i]['end'] = $line['endtime']; $i++; } $select->reset(); unset($select); unset($stmt); // задание началось ранее и закончилось позднее (очень длинное задание) // либо задание еще длится // ********** query 4 ******************* $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 \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 )"); $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']; $this->atime[$i]['short_desc'] = $line['short_desc']; $this->atime[$i]['h1'] = 0; $this->atime[$i]['h2'] = 23.9; $this->atime[$i]['flag'] = 2; // признак, что задание началось ранее и окончилось позднее (очень длинное задание) $this->atime[$i]['start'] = $line['starttime']; $this->atime[$i]['end'] = $line['endtime']; $i++; } $select->reset(); unset($select); unset($stmt); // do Bacula ACLs $this->atime = $this->bacula_acl->doBaculaAcl($this->atime, 'name', 'job'); } }
/** * 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')); /* column name in left table */ $jlkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId(); $uniqext = $jtable . '__joined_for__' . $jlkey; if (in_array($uniqext, $this->_alreadyJoined)) { $class = $property->getParameter('instanceof'); $jtable = $uniqext; continue; } /* pkey name in joined table */ $jpkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY; $join = sprintf("%s.%s = %s.%s", $parentTable, $jlkey, $uniqext, $jpkey); $this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array()); $this->_alreadyJoined[$jtable] = $uniqext; //$jtable; $jtable = $uniqext; $class = $property->getParameter('instanceof'); } } $property = $condition->getProperty(); if ($property instanceof Property\ObjectProperty) { // no join if object is stored in a different backend ! // @todo improve this part if (ObjectModel::getObjectBackend($property->getParameter('instanceof'))->getAlias() != $this->_uri->getAlias()) { $clauses = $condition->getClauses(); if ($clauses[0]['value'] != Condition::NO_VALUE) { $clauses[0]['operator'] = Condition::OPERATOR_ENDSWITH | Condition::OPERATOR_EQUAL; $condition->setClauses($clauses); } $field = $this->_mapper ? $this->_mapper->propertyToDatastoreName($this->_class, $property->getId()) : $property->getId(); } else { // which table to join with ? (in case of condition is last element of a recursion) $jtable2 = $jtable ? $jtable : $table; $jtable = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof')); $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $property->getId()) : $property->getId(); $field = $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($property->getParameter('instanceof')) : Backend::DEFAULT_PKEY; $uniqext = $jtable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { $join = sprintf("%s.%s = %s.%s", $jtable2, $leftkey, $uniqext, is_array($rightkey) ? $rightkey[0] : $rightkey); $this->_select->joinLeft($jtable . " AS {$uniqext}", $join, array()); $this->_alreadyJoined[$jtable] = $uniqext; } $jtable = $uniqext; } } else { if ($property instanceof Property\CollectionProperty) { // handling of conditions based on collection limited to withMembers() and withoutMembers() $leftkey = $property->getParameter('keyprop'); $field = $property->getId(); $subSelect = $this->_ressource->select(); $subseltbl = $this->_mapper ? $this->_mapper->getDatastore($property->getParameter('instanceof')) : $this->_getTableFromClass($property->getParameter('instanceof')); $subSelect->from($subseltbl, new \Zend_Db_Expr(sprintf("COUNT(%s)", $leftkey))); $join = sprintf("%s.%s = %s", $subseltbl, $leftkey, $pkey); $subSelect->where($join); $statement = $this->_buildConditionStatement(new \Zend_Db_Expr(sprintf("(%s)", $subSelect)), $condition->getClauses(), $conditionArray[1]); $this->_select->where($statement); continue; } else { $field = $property->getId(); if ($this->_mapper) { $field = $this->_mapper->propertyToDatastoreName($class, $field); } } } /* convert identifier tag to the valid primary key */ if ($field == ObjectUri::IDENTIFIER) { // @todo handle multiple keys from mapper $field = $table . '.'; $key = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; $field .= is_array($key) ? $key[0] : $key; } else { if ($jtable) { if (array_key_exists($jtable, $this->_alreadyJoined)) { $field = $this->_alreadyJoined[$jtable] . '.' . $field; } else { $tmp = $jtable . '.'; $tmp .= is_array($field) ? $field[0] : $field; $field = $tmp; } } else { if (array_key_exists($table, $this->_alreadyJoined)) { $field = $this->_alreadyJoined[$table] . '.' . $field; } else { $field = $table . '.' . $field; } } } if ($field instanceof Key) { $field = $table . '.' . $field->getName(); } // protect DateProperty() with setted timepart parameter from misuse if ($property instanceof DateProperty && $property->getParameter('timepart') == true) { $field = "DATE({$field})"; } $statement = $this->_buildConditionStatement($field, $condition->getClauses(), $conditionArray[1]); switch ($conditionArray[1]) { case Condition::MODE_OR: $this->_select->orWhere($statement); break; case Condition::MODE_AND: default: $this->_select->where($statement); break; } } // Adjust query based on returnCount if ($returnCount) { if (is_array($returnCount)) { if ($subOp) { } else { // return count on grouped columns foreach ($returnCount as $key => $property) { $fieldmodifier = null; if ($this->_mapper) { $class = $property->getParent() ? $property->getParent()->getId() : $collection->getDataObject()->getClass(); $field = $this->_mapper->propertyToDatastoreName($class, $property->getId()); } else { $field = $property->getId(); } if ($property instanceof ObjectProperty) { // join with $key if necessary if (strstr($key, '.') !== false) { $leftPart = substr($key, 0, strpos($key, '.')); $intermediateProp = $collection->getDataObject()->getProperty($leftPart); $fieldmodifier = $this->_join($intermediateProp, $table) . '.' . $field; } } // limit date grouping to date part, omitting possible hour part if ($property instanceof DateProperty) { $fieldmodifier = "DATE({$field})"; } $this->_select->group($fieldmodifier ? $fieldmodifier : $field); $this->_select->columns(array($field => $fieldmodifier ? $fieldmodifier : $field)); } } } else { $this->_select->reset('group'); } } else { $this->_select->limit($collection->getBoundaryBatch() != -1 ? $collection->getBoundaryBatch() : null, $collection->getBoundaryOffset()); /** * Sorting part */ foreach ($collection->getSortings() as $sorting) { $slUniqext = $slTable = null; // Specific cases first // @todo find a better way to sort on meta properties if ($sorting[0]->getId() == ObjectUri::IDENTIFIER || $sorting[0] instanceof MetaProperty) { $id = Backend::DEFAULT_PKEY; $this->_select->order(new \Zend_Db_Expr($table . '.' . $id . ' ' . $sorting[1])); continue; } else { if ($sorting[0] instanceof Property\CollectionProperty) { // handling of conditions based on collection limited to withMembers() and withoutMembers() $leftkey = $sorting[0]->getParameter('keyprop'); //$field = $property->getId(); $subSelect = $this->_ressource->select(); $subseltbl = $this->_mapper ? $this->_mapper->getDatastore($sorting[0]->getParameter('instanceof')) : $this->_getTableFromClass($sorting[0]->getParameter('instanceof')); $subSelect->from($subseltbl, new \Zend_Db_Expr(sprintf("COUNT(%s)", $leftkey))); $join = sprintf("%s.%s = %s", $subseltbl, $leftkey, $pkey); $subSelect->where($join); // $statement = $this->_buildConditionStatement(new \Zend_Db_Expr(sprintf("(%s)", $subSelect)), $condition->getClauses(), $conditionArray[1]); $this->_select->order(new \Zend_Db_Expr('(' . $subSelect->__toString() . ') ' . $sorting[1])); continue; } else { if ($sorting[0] instanceof Property\ObjectProperty) { // find which property to sort by if ($sorting[0]->getParameter('sorting')) { $sprops = array_keys($sorting[0]->getParameter('sorting')); } else { // try to sort with properties used to display value if (substr($sorting[0]->getParameter('display'), 0, 1) == '[') { // @todo extract elements of pattern to order from them ? $sprops = array('id'); } else { $sprops = explode(',', $sorting[0]->getParameter('display')); } } // sorting property belongs to a second-level join if ($sorting[0]->getParent()->getClass() != $collection->getClass()) { $leftkey = 'commande'; //$this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getParent()getId()) : $sorting[0]->getId(); $class = $sorting[0]->getParent()->getClass(); $stable = $this->_getTableFromClass($class); $sbackend = ObjectModel::getObjectBackend($class); // Property to sort from is in a different backend from current one if ($sbackend->getAlias() != $this->getAlias()) { // We presume that the current backend is allowed to connect to the remote one // Should we raise an exception instead ? $stable = $sbackend->getUri()->getDatabase() . '.' . $stable; } $field = $sorting[0]->getId(); $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; $uniqext = $stable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { if (is_array($rightkey)) { foreach ($rightkey as $rightkeyObj) { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName()); } } else { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey); } $this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array()); $this->_alreadyJoined[$stable] = $uniqext; } $slTable = $this->_getTableFromClass($sorting[0]->getParameter('instanceof')); $slUniqext = $uniqext; } $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($collection->getDataObject()->getClass(), $sorting[0]->getId()) : $sorting[0]->getId(); $class = $sorting[0]->getParameter('instanceof'); $stable = isset($slTable) ? $slTable : $this->_getTableFromClass($class); $sbackend = ObjectModel::getObjectBackend($class); // Property to sort from is in a different backend from current one if ($sbackend->getAlias() != $this->getAlias()) { // We presume that the current backend is allowed to connect to the remote one // Should we raise an exception instead ? $stable = $sbackend->getUri()->getDatabase() . '.' . $stable; } $field = $sorting[0]->getId(); $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($class) : Backend::DEFAULT_PKEY; $uniqext = $stable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { if (is_array($rightkey)) { foreach ($rightkey as $rightkeyObj) { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkeyObj->getName()); } } else { $join = sprintf("%s.%s = %s.%s", isset($slUniqext) ? $slUniqext : $table, $leftkey, $uniqext, $rightkey); } $this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array()); $this->_alreadyJoined[$stable] = $uniqext; } foreach ($sprops as $sprop) { if ($this->_mapper) { $sfield = $this->_mapper->propertyToDatastoreName($class, $sprop); } else { $sfield = $sprop; } $sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield; if (isset($sorting[2]) && !empty($sorting[2])) { $sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr); } $this->_select->order(new \Zend_Db_Expr($sortingExpr . ' ' . $sorting[1])); } continue; } } } // default sorting on a different table $class = $sorting[0]->getParent() ? $sorting[0]->getParent()->getClass() : $collection->getDataObject()->getClass(); $stable = $this->_getTableFromClass($class); if ($this->_mapper) { $sfield = $this->_mapper->propertyToDatastoreName($class, $sorting[0]->getId()); } else { $field = $sorting[0]; $sfield = $field->getId(); } // add a left join if the sorting field belongs to a table not yet part of the query if ($stable != $table) { // get the property id from the class name $tfield = isset($sorting[3]) ? $sorting[3] : $collection->getDataObject()->getObjectPropertyId($class); $leftkey = $this->_mapper ? $this->_mapper->propertyToDatastoreName($class, $tfield) : $tfield; $rightkey = $this->_mapper ? $this->_mapper->getPrimaryKey($field->getParameter('instanceof')) : Backend::DEFAULT_PKEY; $uniqext = $stable . '__joined_for__' . $leftkey; if (!in_array($uniqext, $this->_alreadyJoined)) { $join = sprintf("%s.%s = %s.%s", $table, $leftkey, $uniqext, $rightkey); $this->_select->joinLeft("{$stable} AS {$uniqext}", $join, array()); $this->_alreadyJoined[$stable] = $uniqext; } $sortingExpr = $this->_alreadyJoined[$stable] . '.' . $sfield; } else { $sortingExpr = $stable . '.' . $sfield; } if (isset($sorting[2]) && !empty($sorting[2])) { $sortingExpr = sprintf('%s(%s)', $sorting[2], $sortingExpr); } $this->_select->order(new \Zend_Db_Expr('TRIM(' . $sortingExpr . ') ' . $sorting[1])); } } $result = array(); $context = array('table' => $table); try { if (true && $returnCount == false) { $this->_select->columns($this->_getColumns($collection->getDataObject())); } $result = $this->_ressource->fetchAll($this->_select); } catch (\Zend_Db_Exception $e) { $context['error'] = $e->getMessage(); $this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context); return false; } $this->_setLastQuery($this->_select->__toString(), $this->_select->getPart('where'), $context); if ($returnCount !== false) { return is_array($returnCount) ? $result : $result[0][Backend::MAX_ROWS_IDENTIFIER]; } // convert array of primary keys to strings foreach ($result as $key => $val) { // $result[$key] = implode(Backend\Mapper::VALUES_SEPARATOR, $val); } /* prepare base of object uri */ $uri = new ObjectModel\ObjectUri(); $uri->setBackendUri($this->_uri); $uri->setClass($collection->getDataObject()->getClass()); $uri->setUrl($this->_database . '/' . $table . '/'); return $collection->populate($result, $uri); //return $this->_populateCollection($result, $collection, $uri); }
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)); } }