/**
* Initialize payment data.
* replaces method getPayment($id)
*/
protected function _initData()
{
$tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
$select = new Zend_Db_Select($this->_db);
$select->from($tbl_prefix . 'payment');
$select->joinInner($tbl_prefix . 'invoices', $tbl_prefix . "payment.ac_inv_id = " . $tbl_prefix . "invoices.id", NULL);
$select->joinInner($tbl_prefix . 'customers', $tbl_prefix . "invoices.customer_id = " . $tbl_prefix . "customers.id", array('customer_id' => $tbl_prefix . "customers.id", 'customer' => $tbl_prefix . "customers.name"));
$select->joinInner($tbl_prefix . 'biller', $tbl_prefix . "invoices.biller_id = " . $tbl_prefix . "biller.id", array('biller_id' => $tbl_prefix . "biller.id", 'biller' => $tbl_prefix . "biller.name"));
$select->where($tbl_prefix . 'payment.id=?', $this->_id);
$this->_data = $this->_db->fetchRow($select);
$this->_data['date'] = siLocal::date($payment['ac_date']);
}
/**
* Get the total paid amount by this customer.
* Replaces: calc_customer_paid()
* Enter description here ...
*/
public function getPaidAmount()
{
$tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
$select = new Zend_Db_Select($this->_db);
$select->from($tbl_prefix . "payment", array('amount' => new Zend_Db_Expr("COALESCE(SUM(" . $tbl_prefix . "payment.ac_amount), 0)")));
$select->joinInner($tbl_prefix . "invoices", $tbl_prefix . "payment.ac_inv_id=" . $tbl_prefix . "invoices.id", NULL);
$select->where($tbl_prefix . "invoices.customer_id=?", $this->_id);
return $this->_db->fetchOne($select);
}
/**
* Join Inner
*
* Joins a table, without selecting any columns by default and allowing MySQL table alias syntax
*
* @access public
* @param array|string|Zend_Db_Expr $name
* @param string $cond
* @param array|string $cols
* @param string $schema
* @return $this - Chainable.
*/
public function joinInner($name, $cond, $cols = array(), $schema = null)
{
$this->_joinHistory[] = array('function' => 'joinInner', 'args' => func_get_args());
if (is_string($name) && strpos($name, ' ')) {
list($table, $alias) = explode(' ', $name);
$name = array($alias => $table);
}
$this->_isJoined = true;
$this->_select->joinInner($name, $cond, $cols, $schema);
$this->_count = false;
return $this;
}
protected function _addExtTablesToSelect(Zend_Db_Select &$select)
{
foreach ($this->_extTables as $tableName => $info) {
$select->joinInner($tableName, $info['joinCondition'], $info['fields']);
}
}
/**
* Get payments for this invoice.
*/
public function getPayments()
{
$tbl_prefix = SimpleInvoices_Db_Table_Abstract::getTablePrefix();
$select = new Zend_Db_Select($this->_db);
$select->from($tbl_prefix . 'payment');
$select->joinInner($tbl_prefix . 'invoices', $tbl_prefix . 'payment.ac_inv_id=' . $tbl_prefix . 'invoices.id', NULL);
$select->joinInner($tbl_prefix . 'customers', $tbl_prefix . 'customers.id=' . $tbl_prefix . 'invoices.customer_id', array('cname' => $tbl_prefix . 'customers.name'));
$select->joinInner($tbl_prefix . 'biller', $tbl_prefix . 'biller.id=' . $tbl_prefix . 'invoices.biller_id', array('bname' => $tbl_prefix . 'biller.name'));
$select->where($tbl_prefix . 'payment.ac_inv_id=?', $this->_id);
$select->order($tbl_prefix . 'payment.id DESC');
return $this->_db->fetchAll($select);
}
/**
* Join the specified groupscatalog index table to the passed select instance
*
* @param Zend_Db_Select $select
* @param string $table The groupscatalog index table
* @param int $groupId
* @param int $storeId
* @param string $entityField The entity table column where the product or category id is stored
* @return void
*/
protected function _addGroupsCatalogFilterToSelect(Zend_Db_Select $select, $table, $groupId, $storeId, $entityField = 'e.entity_id')
{
// NOTE to self:
// Using joinTable() seems to trigger an exception for some users that I can't reproduce (so far).
// It is related to the flat catalog (Mage_Catalog_Model_Resource_Category_Flat_Collection missing
// joinTable()). Using getSelect()->joinInner() to work around this issue.
/*
$collection->joinTable(
$helper->getIndexTableByEntityType($entityType), // table
"catalog_entity_id=entity_id", // primary bind
array('group_id' => 'group_id', 'store_id' => 'store_id'), // alias to field mappings for the bind cond.
array( // additional bind conditions (see mappings above)
'group_id' => $groupId,
'store_id' => $collection->getStoreId(),
),
'inner' // join type
);
*/
// Avoid double joins for the wishlist collection.
// They clone and clear() the collection each time, but the joins on the
// collections select objects persist. This is more reliable then setting
// a flag on the collection object.
foreach ($select->getPart(Zend_Db_Select::FROM) as $joinedTable) {
if ($joinedTable['tableName'] == $table) {
// filter join already applied
return;
}
}
$select->joinInner($table, "{$table}.catalog_entity_id={$entityField} AND " . $this->_getReadAdapter()->quoteInto("{$table}.group_id=? AND ", $groupId) . $this->_getReadAdapter()->quoteInto("{$table}.store_id=?", $storeId), array());
}
/**
* Build the advanced search.
*
* @param Zend_Db_Select $select
* @param array $simpleTerms
*/
protected function _advancedSearch($select, $terms)
{
$db = $this->getDb();
$advancedIndex = 0;
foreach ($terms as $v) {
// Do not search on blank rows.
if (empty($v['element_id']) || empty($v['type'])) {
continue;
}
$value = isset($v['terms']) ? $v['terms'] : null;
$type = $v['type'];
$elementId = (int) $v['element_id'];
$alias = "_advanced_{$advancedIndex}";
$inner = true;
$extraJoinCondition = '';
// Determine what the WHERE clause should look like.
switch ($type) {
case 'contains':
$predicate = "LIKE " . $db->quote('%' . $value . '%');
break;
case 'is exactly':
$predicate = ' = ' . $db->quote($value);
break;
case 'does not contain':
$extraJoinCondition = "AND {$alias}.text LIKE " . $db->quote('%' . $value . '%');
case 'is empty':
$inner = false;
$predicate = "IS NULL";
break;
case 'is not empty':
$predicate = "IS NOT NULL";
break;
default:
throw new Omeka_Record_Exception(__('Invalid search type given!'));
}
// Note that $elementId was earlier forced to int, so manual quoting
// is unnecessary here
$joinCondition = "{$alias}.record_id = items.id AND {$alias}.record_type = 'Item' AND {$alias}.element_id = {$elementId}";
if ($extraJoinCondition) {
$joinCondition .= ' ' . $extraJoinCondition;
}
if ($inner) {
$select->joinInner(array($alias => $db->ElementText), $joinCondition, array());
} else {
$select->joinLeft(array($alias => $db->ElementText), $joinCondition, array());
}
$select->where("{$alias}.text {$predicate}");
$advancedIndex++;
}
}
/**
* WARNING: Only columns copying supported.
* @param Lib_Model_Db_Mysql $model
* @param string|null $thisKeyCol may contain table prefix or not
* @param string|null $thatKeyCol may contain table prefix or not
* @param string $conditions
* @return $this
*/
public function joinFrom($model, $thisKeyCol, $thatKeyCol = null, $conditions = '')
{
if ($thatKeyCol === null) {
debug_assert($thisKeyCol !== null);
$thatKeyCol = $model->getPrimaryKey();
} elseif ($thisKeyCol === null) {
debug_assert($thatKeyCol !== null);
$thisKeyCol = $this->getPrimaryKey();
}
$this->prefixColumn($model, $thatKeyCol);
$this->prefixColumn($this, $thisKeyCol);
$conditions = str_replace('{that}', $model->getAlias(), $conditions);
$conditions = str_replace('{this}', $this->getAlias(), $conditions);
$this->mapPartWhere($this->addAliasToConditionDg());
$model->mapPartWhere($model->addAliasToConditionDg());
$thisFrom = $this->_select->getPart(Zend_Db_Select::FROM);
$modelColumns = array_chain($model->_select->getPart(Zend_Db_Select::COLUMNS), array_group_dg(array_get_dg(return_dg(0))), array_map_val_dg(array_chain_dg(array_map_val_dg(function ($descriptor) {
return null === $descriptor[2] ? $descriptor[1] : [$descriptor[2] => $descriptor[1]];
}), function ($columns) {
$outArray = [];
array_map_val($columns, function ($column) use(&$outArray) {
if (is_array($column)) {
array_map_val($column, function ($column, $alias) use(&$outArray) {
$outArray[$alias] = $column;
});
} else {
$outArray[] = $column;
}
});
return $outArray;
})));
array_each($model->_select->getPart(Zend_Db_Select::FROM), function ($descriptor, $alias) use($modelColumns, $thisFrom, $model, $thisKeyCol, $thatKeyCol, $conditions) {
debug_enforce(!array_key_exists($alias, $thisFrom), "Alias `{$alias}` already used for table `{$descriptor['tableName']}`");
switch ($descriptor['joinType']) {
case Zend_Db_Select::FROM:
$this->_select->joinLeft([$model->getAlias() => $model->getTable()], "{$thisKeyCol}={$thatKeyCol} " . $conditions, array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::INNER_JOIN:
$this->_select->joinInner([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::LEFT_JOIN:
$this->_select->joinLeft([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::RIGHT_JOIN:
$this->_select->joinRight([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::FULL_JOIN:
$this->_select->joinFull([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::CROSS_JOIN:
$this->_select->joinCross([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
case Zend_Db_Select::NATURAL_JOIN:
$this->_select->joinNatural([$alias => $descriptor['tableName']], $descriptor['joinCondition'], array_key_exists($alias, $modelColumns) ? $modelColumns[$alias] : [], $descriptor['schema']);
break;
default:
debug_assert(false, "Unknown join type " . var_dump_human_compact($descriptor['joinType']));
break;
}
});
$this->settingsJoin($model);
return $this;
}
/**
* Get Select object (Zend_Db_Select) for sorting table records
*
* @param Zend_Db_Select $select
* @param string $order
*
* @return Zend_Db_Select
*/
public static function GetSelectForSort($select, $options)
{
$aliasTable = '';
$order = $options['order'];
//--------------------------
$arrOrder = explode('.', $order);
// Если в параметре сортировки не задан псевдоним таблицы
// то определим его, и если нужно присоединим,
// соответствующую таблицу
if (count($arrOrder) == 1) {
$joinTableForSort = $options['joinTableForSort'];
if ($joinTableForSort) {
// Определим какие таблицы уже присоединены
$fromTables = $select->getPart(Zend_Db_Select::FROM);
foreach ($fromTables as $alias => $joinParams) {
// Если таблица -> $joinTableForSort уже присоединена
// то получим ее псевдоним
if ($joinParams['tableName'] == $joinTableForSort) {
$aliasTable = $alias;
}
}
if ($aliasTable) {
$order = $aliasTable . '.' . $order;
} else {
// Получим поле сортировки
$arrOrder = explode(' ', trim($order));
$field = $arrOrder[0];
// Присоединим таблицу
$joinTable = $joinTableForSort;
switch ($joinTable) {
case 'blog_posts_profile':
$select->joinInner(array('p_profile' => $joinTable), 'p_profile.post_id = p.id', array())->where('p_profile.profile_key = ?', $field);
$order = 'p_profile.profile_value ' . $arrOrder[1];
break;
case 'blog_posts_images':
$select->joinInner(array('img' => $joinTable), 'img.post_id = p.id', array());
$order = 'img.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_audio':
$select->joinInner(array('au' => $joinTable), 'au.post_id = p.id', array());
$order = 'au.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_video':
$select->joinInner(array('v' => $joinTable), 'v.post_id = p.id', array());
$order = 'v.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_lacations':
$select->joinInner(array('l' => $joinTable), 'l.post_id = p.id', array());
$order = 'l.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_tags':
$select->joinInner(array('t' => $joinTable), 't.post_id = p.id', array());
$order = 't.' . $field . ' ' . $arrOrder[1];
break;
case 'blog_posts_comments':
$select->joinInner(array('c' => $joinTable), 'c.post_id = p.id', array());
$order = 'c.' . $field . ' ' . $arrOrder[1];
break;
case 'users':
$select->joinInner(array('u' => $joinTable), 'u.id = p.user_id', array());
$order = 'u.' . $field . ' ' . $arrOrder[1];
break;
case 'users_profile':
$select->joinInner(array('u_profile' => $joinTable), 'u_profile.user_id = p.user_id', array())->where('u_profile.profile_key = ?', $field);
$order = 'u_profile.profile_value ' . $arrOrder[1];
break;
default:
$order = 'p.' . $field . ' ' . $arrOrder[1];
break;
}
}
} else {
$order = 'p.' . $order;
}
}
$select->order($order);
return $select;
}
function getLastJobRun($numjob)
{
$select = new Zend_Db_Select($this->db);
$select->distinct();
$select->limit($numjob);
switch ($this->db_adapter) {
case 'PDO_MYSQL':
$select->from(array('j' => 'Job'), array('JobId', 'Type', 'JobName' => 'Name', 'Level', 'ClientId', 'sortStartTime' => 'StartTime', 'StartTime', 'EndTime', 'VolSessionId', 'VolSessionTime', 'JobFiles', 'JobBytes', 'JobErrors', 'Reviewed', 'PoolId', 'FileSetId', 'PurgedFiles', 'JobStatus', 'DurationTime' => 'TIMEDIFF(EndTime, StartTime)'));
$select->joinLeft(array('c' => 'Client'), 'j.ClientId = c.ClientId', array('ClientName' => 'Name'));
$select->joinLeft(array('p' => 'Pool'), 'j.PoolId = p.PoolId', array('PoolName' => 'Name'));
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
$select->joinInner(array('s' => 'Status'), "j.JobStatus = s.JobStatus", array('JobStatusLong'));
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', 'sortStartTime' => 'StartTime', 'StartTime', 'EndTime', 'VolSessionId', 'VolSessionTime', 'JobFiles', 'JobBytes', 'JobErrors', 'Reviewed', 'PoolId', 'FileSetId', 'PurgedFiles', 'JobStatus', 'DurationTime' => '(EndTime - StartTime)'));
$select->joinLeft(array('c' => 'Client'), 'j.ClientId = c.ClientId', array('ClientName' => 'Name'));
$select->joinLeft(array('p' => 'Pool'), 'j.PoolId = p.PoolId', array('PoolName' => 'Name'));
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
$select->joinInner(array('s' => 'Status'), "j.JobStatus = s.JobStatus", array('JobStatusLong'));
break;
case 'PDO_SQLITE':
// SQLite3 Documentation
// http://sqlite.org/lang_datefunc.html
// 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', 'sortStartTime' => 'StartTime', '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(array('c' => 'Client'), 'j.ClientId = c.ClientId', array('ClientName' => 'Name'));
$select->joinLeft(array('p' => 'Pool'), 'j.PoolId = p.PoolId', array('PoolName' => 'Name'));
$select->joinLeft(array('sd' => 'webacula_jobdesc'), 'j.Name = sd.name_job');
$select->joinInner(array('s' => 'Status'), "j.JobStatus = s.JobStatus", array('jobstatuslong' => 'JobStatusLong'));
break;
}
$select->where("j.JobStatus IN ('T', 'E', 'e', 'f', 'A', 'W', 'D')");
$select->where("j.Type = 'B'");
$select->order(array("sortStartTime DESC"));
//$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // !!!debug
$stmt = $select->query();
// do Bacula ACLs
return $this->bacula_acl->doBaculaAcl($stmt->fetchAll(), 'jobname', 'job');
}
/**
* inner Join
*
* @param array|String $name
* @param unknown_type $cond
* @return WeFlex_Db_Model
*/
public function joinInner($name, $cond)
{
$this->_selector->joinInner($name, $cond);
return $this;
}
/**
* Helper to specify new rules in queries for Omeka releases < 2.5.
*
* @see Table_Item::_advancedSearch()
*
* @param Zend_Db_Select $select
* @param array $simpleTerms
* @return void
*/
private function _addRulesOldOmeka($select, $rulesOmekaNew)
{
if (empty($rulesOmekaNew)) {
return;
}
$db = $this->_db;
$advancedIndex = 0;
foreach ($rulesOmekaNew as $v) {
// Do not search on blank rows.
if (empty($v['element_id']) || empty($v['type'])) {
continue;
}
$value = isset($v['terms']) ? $v['terms'] : null;
$type = $v['type'];
$elementId = (int) $v['element_id'];
$alias = "_advanced_old_{$advancedIndex}";
$inner = true;
$extraJoinCondition = '';
// Determine what the WHERE clause should look like.
switch ($type) {
case 'starts with':
$predicate = "LIKE " . $db->quote($value . '%');
break;
case 'ends with':
$predicate = "LIKE " . $db->quote('%' . $value);
break;
case 'is not exactly':
$predicate = ' != ' . $db->quote($value);
break;
case 'matches':
if (!strlen($value)) {
continue 2;
}
$predicate = 'REGEXP ' . $db->quote($value);
break;
case 'does not match':
if (!strlen($value)) {
continue 2;
}
$predicate = 'NOT REGEXP ' . $db->quote($value);
break;
default:
throw new Omeka_Record_Exception(__('Invalid search type given!'));
}
// Note that $elementId was earlier forced to int, so manual quoting
// is unnecessary here
$joinCondition = "{$alias}.record_id = items.id AND {$alias}.record_type = 'Item' AND {$alias}.element_id = {$elementId}";
if ($extraJoinCondition) {
$joinCondition .= ' ' . $extraJoinCondition;
}
if ($inner) {
$select->joinInner(array($alias => $db->ElementText), $joinCondition, array());
} else {
$select->joinLeft(array($alias => $db->ElementText), $joinCondition, array());
}
$select->where("{$alias}.text {$predicate}");
$advancedIndex++;
}
}
/**
* Get Select object (Zend_Db_Select) for sorting table records
*
* @param Zend_Db_Select $select
* @param array $options
*
* @return Zend_Db_Select
*/
public static function GetSelectForSort($select, $options)
{
$aliasTable = '';
$order = $options['order'];
//--------------------------
$arrOrder = explode('.', $order);
// Если в параметре сортировки не задан псевдоним таблицы
// то определим его, и если нужно присоединим,
// соответствующую таблицу
if (count($arrOrder) == 1) {
$joinTableForSort = $options['joinTableForSort'];
if ($joinTableForSort) {
// Определим какие таблицы уже присоединены
$fromTables = $select->getPart(Zend_Db_Select::FROM);
foreach ($fromTables as $alias => $joinParams) {
// Если таблица -> $joinTableForSort уже присоединена
// то получим ее псевдоним
if ($joinParams['tableName'] == $joinTableForSort) {
$aliasTable = $alias;
}
}
if ($aliasTable) {
$order = $aliasTable . '.' . $order;
} else {
// Получим поле сортировки
$arrOrder = explode(' ', trim($order));
$field = $arrOrder[0];
// Присоединим таблицу
$joinTable = $joinTableForSort;
switch ($joinTable) {
case 'blog_info_profile':
$select->joinInner(array('i_profile' => $joinTable), 'i_profile.info_id = i.id', array())->where('i_profile.profile_key = ?', $field);
$order = 'i_profile.profile_value ' . $arrOrder[1];
break;
default:
$order = 'i.' . $field . ' ' . $arrOrder[1];
break;
}
}
} else {
$order = 'i.' . $order;
}
}
$select->order($order);
return $select;
}
