public function getHistory($sTableName, $row_id)
{
$oWhere = new \Zend\Db\Sql\Where();
$oWhere->equalTo('table_name', $sTableName);
$oWhere->equalTo('row_id', $row_id);
$rowset = $this->select(function (Select $select) use($oWhere) {
$select->where($oWhere);
});
return $rowset->toArray();
}
/**
* Retourne les particpants d'un raid.
* @param type $iIdRaid
*/
function getParticipantRaid($iIdRaid)
{
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$oQuery = $sql->select();
$oQuery->from(array('rp' => 'raid_personnage'))->join(array('r' => 'raids'), 'r.idRaid=rp.idRaid', array(), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('p' => 'personnages'), 'p.idPersonnage=rp.idPersonnage', array('personnage_nom' => 'nom', 'personnage_royaume' => 'royaume', 'ilvl'), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('c' => 'classes'), 'c.idClasses=p.idClasses', array('classe_nom' => 'nom', 'classe_couleur' => 'couleur'), \Zend\Db\Sql\Select::JOIN_INNER)->join(array('rac' => 'race'), 'rac.idRace=p.idRace', array('race_nom' => 'nom'), \Zend\Db\Sql\Select::JOIN_INNER);
$where = new \Zend\Db\Sql\Where();
$where->equalTo("rp.idRaid", $iIdRaid);
$oQuery->where($where);
$oQuery->order('p.nom');
$aAllParticpantTmp = $this->fetchAllArray($oQuery);
foreach ($aAllParticpantTmp as $aValue) {
$aValue['roster'] = 0;
$aValue['apply'] = null;
$aValue['abs'] = false;
$aAllParticpant[$aValue['idPersonnage']] = $aValue;
}
$oQuery->join(array('rhp' => 'roster_has_personnage'), 'rhp.idRoster = r.idRosterTmp AND rhp.idPersonnage = rp.idPersonnage', array('apply' => 'isApply'), \Zend\Db\Sql\Select::JOIN_INNER);
$aMembreRosterTmp = $this->fetchAllArray($oQuery);
foreach ($aMembreRosterTmp as $aValue) {
$aMembreRoster[$aValue['idPersonnage']] = $aValue;
}
if (isset($aMembreRoster)) {
$aParticipantRoster = array_intersect_key($aAllParticpant, $aMembreRoster);
foreach ($aParticipantRoster as $key => $value) {
$aAllParticpant[$key]['roster'] = 1;
$aAllParticpant[$key]['apply'] = $aMembreRoster[$key]['apply'];
$aAllParticpant[$key]['abs'] = false;
}
}
return $aAllParticpant;
}
public function addCategory($parentCategoryId = null, $categoryId, $siteId = 1)
{
$where = new \Zend\Db\Sql\Where();
$where->equalTo('website_id', $siteId)->equalTo('category_id', $categoryId);
if (null === $parentCategoryId) {
$where->isNull('parent_category_id');
} else {
$where->equalTo('parent_category_id', $parentCategoryId);
}
$table = 'catalog_category_website';
$row = array('category_id' => $categoryId, 'parent_category_id' => $parentCategoryId, 'website_id' => $siteId);
$select = $this->getSelect($table)->where($where);
$result = $this->selectOne($select);
if (false === $result) {
$this->insert($row, $table);
}
}
/**
* {@inheritDoc}
*/
public function loadStream($sourceFQCN, $sourceId, $version = null)
{
$tableGateway = $this->getTablegateway($sourceFQCN);
$sql = $tableGateway->getSql();
$where = new \Zend\Db\Sql\Where();
$where->equalTo('sourceId', $sourceId);
if (!is_null($version)) {
$where->AND->greaterThanOrEqualTo('sourceVersion', $version);
}
$select = $sql->select()->where($where)->order('sourceVersion');
$eventsData = $tableGateway->selectWith($select);
$events = array();
foreach ($eventsData as $eventData) {
$eventClass = $eventData->eventClass;
$payload = $this->getSerializer()->deserialize($eventData->payload, 'array', 'json');
$event = new $eventClass($payload, $eventData->eventId, (int) $eventData->timestamp, (double) $eventData->eventVersion);
$event->setSourceVersion((int) $eventData->sourceVersion);
$event->setSourceId($sourceId);
$events[] = $event;
}
return $events;
}
public function getUsersByEmail($email, $id = 0)
{
$where = new \Zend\Db\Sql\Where();
$where->notEqualTo('id', (int) $id);
$where->equalTo('email', $email);
$rowset = $this->select($where);
$row = $rowset->current();
if (!$row) {
return false;
}
return $row;
}
/**
* @param array $where ['equalTo' => ['type', 'single'], 'in' => ['id', [1, 2]], ]
* @param null $limit
* @param int $offset
* @param string $order
* @return null|\Zend\Db\ResultSet\ResultSetInterface
*/
public function getItemsWhere($where = [], $limit = null, $offset = 0, $order = 'id asc')
{
$select = $this->getSelect()->from($this->getTable());
if ($where) {
$whereObj = new \Zend\Db\Sql\Where();
foreach ($where as $key => $params) {
if (in_array($key, $this->fields)) {
if (is_array($params)) {
if ($params) {
$whereObj->in($key, $params);
} else {
// в случае когда передано $service->getItemsWhere(['id => []]);
return [];
}
} else {
$whereObj->equalTo($key, $params);
}
} else {
$allowedMethods = ['equalTo', 'notEqualTo', 'lessThan', 'greaterThan', 'lessThanOrEqualTo', 'greaterThanOrEqualTo', 'like', 'notLike', 'expression', 'literal', 'isNull', 'isNotNull', 'in', 'notIn', 'between'];
if (!in_array($key, $allowedMethods)) {
throw new \Exception("method '" . $key . "' not allowed in where.");
}
call_user_func_array([$whereObj, $key], $params);
}
}
$select->where($whereObj);
}
if ($limit !== null) {
$select->limit($limit);
}
if ($offset) {
$select->offset($offset);
}
$select->order($order);
return $this->tableGateway->selectWith($select);
}
/**
* Retourne le select query deja configuré par l'adapter et le nom de la table pour le frontend
*
* @return Zend\Db\Sql\Select
*/
public function getBaseQueryFrontend($iIdRoster)
{
try {
$sql = new \Zend\Db\Sql\Sql($this->getAdapter());
$query = $sql->select();
$query->from($this->table)->columns(array('*'));
$where = new \Zend\Db\Sql\Where();
$where->equalTo("idRosterTmp", $iIdRoster);
$query->where($where);
$query->order('date DESC');
return $query;
} catch (\Exception $exc) {
throw new DatabaseException(4000, 4, $this->_getServiceLocator(), $iIdRoster, $exc);
}
}
<?php
/** @var $adapter Zend\Db\Adapter\Adapter */
$adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php');
refresh_data($adapter);
$where = new Zend\Db\Sql\Where();
$where->equalTo('id', 1)->OR->equalTo('id', 2);
$where->OR->NEST->like('name', 'Ralph%')->OR->greaterThanOrEqualTo('age', 30)->AND->lessThanOrEqualTo('age', 50)->UNNEST->literal('foo = ?', 'bar');
$target = <<<EOS
SELECT "foo".* FROM "foo" WHERE "id" = '1' OR "id" = '2' OR ("name" LIKE 'Ralph%' OR "age" >= '30' AND "age" <= '50') AND foo = 'bar'
EOS;
$select = new Zend\Db\Sql\Select('foo');
$select->where($where);
assert_example_works($target == $select->getSqlString());
/**
* Update package assignments
*
* Sets a new package on existing assignments. Updated assignments have
* their status reset to "not notified" and their options (force, schedule,
* post cmd) removed.
*
* @param integer $oldPackageId package to be replaced
* @param integer $newPackageId new package
* @param bool $deployNonnotified Update assignments with status 'not notified'
* @param bool $deploySuccess Update assignments with status 'success'
* @param bool $deployNotified Update assignments with status 'notified'
* @param bool $deployError Update assignments with status 'error'
* @param bool $deployGroups Update assignments for groups
* @throws RuntimeException if an error occurs
*/
public function updateAssignments($oldPackageId, $newPackageId, $deployNonnotified, $deploySuccess, $deployNotified, $deployError, $deployGroups)
{
if (!($deployNonnotified or $deploySuccess or $deployNotified or $deployError or $deployGroups)) {
return;
// nothing to do
}
$clientConfig = $this->_serviceManager->get('Database\\Table\\ClientConfig');
$groupInfo = $this->_serviceManager->get('Database\\Table\\GroupInfo');
$where = new \Zend\Db\Sql\Where();
$where->equalTo('ivalue', $oldPackageId);
// Additional filters are only necessary if not all conditions are set
if (!($deployNonnotified and $deploySuccess and $deployNotified and $deployError and $deployGroups)) {
$groups = $groupInfo->getSql()->select()->columns(array('hardware_id'));
$filters = new \Zend\Db\Sql\Where(null, \Zend\Db\Sql\Where::COMBINED_BY_OR);
if ($deployNonnotified) {
$filters->isNull('tvalue')->and->notIn('hardware_id', $groups);
}
if ($deploySuccess) {
$filters->equalTo('tvalue', \Model\Package\Assignment::SUCCESS);
}
if ($deployNotified) {
$filters->equalTo('tvalue', \Model\Package\Assignment::NOTIFIED);
}
if ($deployError) {
$filters->like('tvalue', \Model\Package\Assignment::ERROR_PREFIX . '%');
}
if ($deployGroups) {
$filters->in('hardware_id', $groups);
}
$where->addPredicate($filters);
}
$now = $this->_serviceManager->get('Library\\Now')->format(\Model\Package\Assignment::DATEFORMAT);
try {
// Remove DOWNLOAD_* options from updated assignments
$subquery = $clientConfig->getSql()->select()->columns(array('hardware_id'))->where(array('name' => 'DOWNLOAD', $where));
$delete = new \Zend\Db\Sql\Where();
$delete->equalTo('ivalue', $oldPackageId)->in('hardware_id', $subquery)->notEqualTo('name', 'DOWNLOAD_SWITCH')->like('name', 'DOWNLOAD_%');
$clientConfig->delete($delete);
// Update package ID and reset status
$clientConfig->update(array('ivalue' => $newPackageId, 'tvalue' => \Model\Package\Assignment::NOT_NOTIFIED, 'comments' => $now), array('name' => 'DOWNLOAD', $where));
} catch (\Exception $e) {
throw new RuntimeException($e->getMessage(), (int) $e->getCode(), $e);
}
}
public function getUserTreeMenus($role_id, $parent_id = 0)
{
$adapter = $this->adapter;
$result = array();
$select = new Select();
$select->from($this->table);
// $select->columns(array(new Expression('DISTINCT(menus.id) as id'), 'name', 'type', 'url', 'target'));
$select->join('resources', 'resources.id = menus.resource_id', array('resource_name' => 'name'), 'left');
$select->join('role_resources', 'menus.resource_id = role_resources.resource_id', array('role_id' => 'role_id'), 'left');
$where = new \Zend\Db\Sql\Where();
if ($role_id != 1) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Expression("(role_resources.role_id = {$role_id} OR menus.type = 1)"));
}
$where->equalTo('parent_id', $parent_id);
$where->equalTo('status', 1);
if (!empty($where)) {
$select->where($where);
}
$select->order('order' . ' ' . 'ASC');
$sql = new Sql($adapter);
$statement = $sql->getSqlStringForSqlObject($select);
//echo $statement;
// die();
$resultSet = $adapter->query($statement, $adapter::QUERY_MODE_EXECUTE);
$resultSet->buffer();
if (!empty($resultSet)) {
$exist_ids = array();
foreach ($resultSet as $key => $data) {
if (!in_array($data->id, $exist_ids)) {
$exist_ids[] = $data->id;
$result[$key]['data'] = $data;
$result[$key]['child'] = $this->getUserTreeMenus($role_id, $data->id);
}
}
}
return $result;
}
/**
* Retourne les loots du raid.
* @param type $iIdRaid
*/
public function getLootRaid($iIdRaid)
{
$oQuery = $this->getQueryBaseLoot();
$oQuery->order('item_date');
// $oQuery->join(array('rhp' => 'roster_has_personnage'), 'rhp.idRoster = r.idRosterTmp AND rhp.idPersonnage = ipr.idPersonnage', array(), \Zend\Db\Sql\Select::JOIN_INNER);
$predicate = new \Zend\Db\Sql\Where();
$predicate->equalTo("r.idRaid", $iIdRaid);
$oQuery->where($predicate);
$aLoots = $this->fetchAllArray($oQuery);
foreach ($aLoots as $key => $loot) {
switch ($loot['valeur']) {
//spé
case 0:
$aLoots[$key]['spe'] = 'spé 1';
break;
//spé
//spé
case 1:
$aLoots[$key]['spe'] = 'spé 2';
break;
//spé
//spé
case 2:
$aLoots[$key]['spe'] = 'spé 3';
break;
//spé
//spé
case 3:
$aLoots[$key]['spe'] = 'spé 4';
break;
default:
$aLoots[$key]['spe'] = 'spé 1';
break;
}
}
return $aLoots;
}
/**
* index Action
* Receive the search params
* Build the search query
* Generate the search result as a list
* @return type
* Author: Tahmina Khatoon
*/
public function indexAction()
{
$searchform = new MenusSearchForm();
/**
* Set Resource List
*/
$options = $this->getResourcesTable()->dropdownResources();
$searchform->get('resource_id')->setOptions(array('value_options' => $options));
/**
* Set Parent Menu List
*/
$menuoptions = $this->dropdownMenus();
$searchform->get('parent_id')->setOptions(array('value_options' => $menuoptions));
$searchform->get('submit')->setValue('Search');
$select = new Select();
$order_by = $this->params()->fromRoute('order_by') ? $this->params()->fromRoute('order_by') : 'order';
$order = $this->params()->fromRoute('order') ? $this->params()->fromRoute('order') : Select::ORDER_DESCENDING;
$page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1;
$item_per_page = $this->params()->fromRoute('item_per_page') ? (int) $this->params()->fromRoute('item_per_page') : 10;
$page_range = $this->params()->fromRoute('page_range') ? (int) $this->params()->fromRoute('page_range') : 7;
$select->order($order_by . ' ' . $order);
$search_by = $this->params()->fromRoute('search_by') ? $this->params()->fromRoute('search_by') : '';
$select->join('resources', 'resources.id = menus.resource_id', array('resource_name' => 'name'), 'left');
$select->join(array('parents' => 'menus'), 'parents.id = menus.parent_id', array('parent_name' => 'name'), 'left');
$where = new \Zend\Db\Sql\Where();
$formdata = array();
if (!empty($search_by)) {
$formdata = (array) json_decode($search_by);
if (!empty($formdata['name'])) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Like('menus.name', '%' . $formdata['name'] . '%'));
}
if (!empty($formdata['type'])) {
$where->equalTo('menus.type', $formdata['type']);
}
if (!empty($formdata['resource_id'])) {
$where->equalTo('menus.resource_id', $formdata['resource_id']);
}
if (!empty($formdata['url'])) {
$where->addPredicate(new \Zend\Db\Sql\Predicate\Like('menus.url', '%' . $formdata['url'] . '%'));
}
if (!empty($formdata['target'])) {
$where->equalTo('menus.target', $formdata['target']);
}
if (!empty($formdata['parent_id'])) {
$where->equalTo('menus.parent_id', $formdata['parent_id']);
}
if (!empty($formdata['status'])) {
$where->equalTo('menus.status', $formdata['status']);
}
}
if (!empty($where)) {
$select->where($where);
}
$paginator = $this->getMenusTable()->fetchAll($select, true);
$paginator->setCurrentPageNumber($page)->setItemCountPerPage($item_per_page)->setPageRange($page_range);
$totalRecord = $paginator->getTotalItemCount();
$currentPage = $paginator->getCurrentPageNumber();
$totalPage = $paginator->count();
$searchform->setData($formdata);
$this->vm->setVariables(array('flashMessages' => $this->flashMessenger()->getMessages(), 'search_by' => $search_by, 'order_by' => $order_by, 'order' => $order, 'page' => $page, 'item_per_page' => $item_per_page, 'paginator' => $paginator, 'pageAction' => 'menus/index', 'form' => $searchform, 'totalRecord' => $totalRecord, 'currentPage' => $currentPage, 'totalPage' => $totalPage));
return $this->vm;
}
/**
* Update package assignments
*
* Sets a new package on existing assignments. Updated assignments have
* their status reset to "pending" and their options (force, schedule,
* post cmd) removed.
*
* @param integer $oldPackageId package to be replaced
* @param integer $newPackageId new package
* @param bool $deployPending Update assignments with status 'pending'
* @param bool $deployRunning Update assignments with status 'running'
* @param bool $deploySuccess Update assignments with status 'success'
* @param bool $deployError Update assignments with status 'error'
* @param bool $deployGroups Update assignments for groups
* @throws RuntimeException if an error occurs
*/
public function updateAssignments($oldPackageId, $newPackageId, $deployPending, $deployRunning, $deploySuccess, $deployError, $deployGroups)
{
if (!($deployPending or $deployRunning or $deploySuccess or $deployError or $deployGroups)) {
return;
// nothing to do
}
$clientConfig = $this->_serviceManager->get('Database\\Table\\ClientConfig');
$groupInfo = $this->_serviceManager->get('Database\\Table\\GroupInfo');
$where = new \Zend\Db\Sql\Where();
$where->equalTo('ivalue', $oldPackageId);
$where->equalTo('name', 'DOWNLOAD');
// Additional filters are only necessary if not all conditions are set
if (!($deployPending and $deployRunning and $deploySuccess and $deployError and $deployGroups)) {
$groups = $groupInfo->getSql()->select()->columns(array('hardware_id'));
$filters = new \Zend\Db\Sql\Where(null, \Zend\Db\Sql\Where::COMBINED_BY_OR);
if ($deployPending) {
$filters->isNull('tvalue')->and->notIn('hardware_id', $groups);
}
if ($deployRunning) {
$filters->equalTo('tvalue', \Model\Package\Assignment::RUNNING);
}
if ($deploySuccess) {
$filters->equalTo('tvalue', \Model\Package\Assignment::SUCCESS);
}
if ($deployError) {
$filters->like('tvalue', \Model\Package\Assignment::ERROR_PREFIX . '%');
}
if ($deployGroups) {
$filters->in('hardware_id', $groups);
}
$where->addPredicate($filters);
}
$now = $this->_serviceManager->get('Library\\Now')->format(\Model\Package\Assignment::DATEFORMAT);
try {
// Remove DOWNLOAD_* options from updated assignments
$subquery = $clientConfig->getSql()->select()->columns(array('hardware_id'))->where($where);
// @codeCoverageIgnoreStart
if ($clientConfig->getAdapter()->getPlatform()->getName() == 'MySQL') {
// MySQL does not allow subquery on the same table for DELETE
// statements. Fetch result as a list instead.
$subquery = array_column($clientConfig->selectWith($subquery)->toArray(), 'hardware_id');
}
// @codeCoverageIgnoreEnd
if ($subquery) {
// $subquery is either an SQL statement or a non-empty array.
$delete = new \Zend\Db\Sql\Where();
$delete->equalTo('ivalue', $oldPackageId)->notEqualTo('name', 'DOWNLOAD_SWITCH')->in('hardware_id', $subquery)->like('name', 'DOWNLOAD_%');
$clientConfig->delete($delete);
}
// Update package ID and reset status
$clientConfig->update(array('ivalue' => $newPackageId, 'tvalue' => \Model\Package\Assignment::PENDING, 'comments' => $now), $where);
} catch (\Exception $e) {
throw new RuntimeException($e->getMessage(), (int) $e->getCode(), $e);
}
}
