public function getUsersAndAbove(bool $paginated, $name = '', $roles = []) { $select = new Select('account'); $where = new Where(); if ($roles) { $sub = $where->nest(); for ($i = 0; $i < count($roles); $i++) { $sub->equalTo('role', $roles[$i]); if ($i < count($roles) - 1) { $sub->or; } } $sub->unnest(); } else { $where->greaterThan('role', '0'); $where->lessThan('role', '32'); } if ($name) { $where->like('name', '%' . $name . '%'); } $select->where($where)->order('name ASC'); if ($paginated) { $resultSetPrototype = new ResultSet(); $resultSetPrototype->setArrayObjectPrototype(new Account()); $paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype); return new Paginator($paginatorAdapter); } return $this->tableGateway->select($select); }
/** * Creates a new square manager object. * * Preloads all available squares from the database. * * @param SquareTable $squareTable * @param SquareMetaTable $squareMetaTable * @param string $locale */ public function __construct(SquareTable $squareTable, SquareMetaTable $squareMetaTable, $locale) { $this->squareTable = $squareTable; $this->squareMetaTable = $squareMetaTable; $select = $squareTable->getSql()->select(); $select->order('priority ASC'); $resultSet = $squareTable->selectWith($select); $this->squares = SquareFactory::fromResultSet($resultSet); /* Load square meta data */ if ($this->squares) { $sids = array(); foreach ($this->squares as $square) { $sids[] = $square->need('sid'); } reset($this->squares); $metaWhere = new Where(); $metaWhere->in('sid', $sids); $metaWhere->and; $metaWhereNested = $metaWhere->nest(); $metaWhereNested->isNull('locale'); $metaWhereNested->or; $metaWhereNested->equalTo('locale', $locale); $metaWhereNested->unnest(); $metaSelect = $this->squareMetaTable->getSql()->select(); $metaSelect->where($metaWhere); $metaSelect->order('locale ASC'); $metaResultSet = $this->squareMetaTable->selectWith($metaSelect); SquareFactory::fromMetaResultSet($this->squares, $metaResultSet); /* Prepare active squares */ $this->activeSquares = $this->getAllVisible(); } }
/** * Gets the entity meta select predicate. * * @param string $id * @param array $eids * @return Select */ protected function getByMetaSelect($id, array $eids) { $where = new Where(); $where->in($id, $eids); $where->and; $nestedWhere = $where->nest(); $nestedWhere->equalTo('locale', $this->locale); $nestedWhere->or; $nestedWhere->isNull('locale'); $nestedWhere->unnest(); $metaSelect = $this->entityMetaTable->getSql()->select(); $metaSelect->where($where); $metaSelect->order('locale ASC'); return $metaSelect; }
/** * Get the list of shifts for the given Manager ID. If a start_time and/or end_time is provided, will restrict the list of shifts to only shifts within those datetime * * @param $id Shift ID * */ public function getList($id = false) { $shifts_gateway = Shifts::factory($this->getServiceLocator()); $manager_id = $this->params()->fromRoute('manager_id'); // shifts within this start/end time $start_time = $this->params()->fromQuery('start_time'); $end_time = $this->params()->fromQuery('end_time'); if (!empty($start_time)) { $start_time = strtotime($start_time); if ($start_time === false || $start_time == 0) { return $this->responseError(400, 'invalid start time'); } $start_time = date('Y-m-d H:i:s', $start_time); } if (!empty($end_time)) { $end_time = strtotime($end_time); if ($end_time === false || $end_time == 0) { return $this->responseError(400, 'invalid end time'); } $end_time = date('Y-m-d H:i:s', $end_time); } // select all shifts for the given manager $result = $shifts_gateway->select(function ($select) use($manager_id, $start_time, $end_time, $shifts_gateway) { $where = new Where(); $where->equalTo('manager_id', $manager_id); $where2 = $where->nest(); if (!empty($start_time)) { $nest = $where2->or->nest(); $nest->lessThanOrEqualTo('start_time', $start_time); $nest->greaterThan('end_time', $start_time); } if (!empty($end_time)) { $nest = $where2->or->nest(); $nest->lessThanOrEqualTo('start_time', $end_time); $nest->greaterThan('end_time', $end_time); } $select->where($where); //echo $shifts_gateway->getSql()->getSqlStringForSqlObject($select); }); return $result; }
public function getTimeline() { $select = new Select(); $select->from(array('fl' => 'fg_flicks')); $select->columns(array('*')); $select->join(array('fr' => 'fg_friends'), 'fl.owner = fr.user_one OR fl.owner = fr.user_two', array()); $select->join(array('u' => 'fg_users'), 'fl.owner = u.id', array("nickname")); $where = new Where(); $or = $where->nest(); $or->equalTo('fr.user_one', $this->user_id); $or->OR->equalTo('fr.user_two', $this->user_id); $or->unnest(); $where->AND->equalTo('state', 1); $where->AND->notEqualTo('fl.owner', $this->user_id); $select->where($where); $select->order(array('id DESC')); $select->group("id"); $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select); $resultSet = $statement->execute(); // var_dump($resultSet->current()); // exit; return $resultSet; }
/** * @param bool|int $managerId * @param bool $active * @return \DDD\Domain\User\User[]|array[]|\ArrayObject */ public function getPeopleList($managerId = false, $active = true, $withoutExternalUsers = true, $countryId = false) { return $this->fetchAll(function (Select $select) use($managerId, $active, $withoutExternalUsers, $countryId) { $select->columns(['id', 'firstname', 'lastname', 'manager_id', 'email']); $where = new Where(); $where->equalTo('system', 0); if ($withoutExternalUsers) { $where->equalTo('external', 0); } if ($countryId) { $where->equalTo('country_id', $countryId); } if ($active || $managerId) { $nest = $where->nest(); } if ($active) { $nest->equalTo('disabled', 0); } if ($managerId) { $nest->or->equalTo('id', $managerId); } $select->where($where); $select->order('firstname ASC'); }); }
public function getUserInfo($id) { // Get the Contact by email or userId to find the contactId // Instantiate the contact controller $ContactController = $this->getServiceLocator()->get('ContactController'); $ContactController->setValidateOAuth(false); // Get the contact with the user_id associated to the Token $where = new Where(); $where->nest()->equalTo(CONTACT_EMAIL, $id)->or->equalTo(CONTACT_USERID, $id); $contact = $ContactController->get($where); if ($contact->getVariable('status') == STATUS_FAILED) { throw new CommonException(array('messageId' => 'globals.query.record_not_found', 'parms' => array('id' => $id), EXCEPTION_ERRORKEY => 404, 'code' => 404)); } $this->setValidateOAuth(false); $result = parent::get($contact->getVariable($ContactController->getTable()->getPk())); $this->setValidateOAuth(true); // TODO - Validate only one record found return $result; }
/** * Gets all reservations within the specified datetime interval. * * Reservations are ordered by date and start time. * * @param DateTime $dateTimeStart * @param DateTime $dateTimeEnd * @param int $limit * @param int $offset * @param boolean $loadMeta * @return array */ public function getInRange(DateTime $dateTimeStart, DateTime $dateTimeEnd, $limit = null, $offset = null, $loadMeta = true) { if ($dateTimeStart->format('Y-m-d') == $dateTimeEnd->format('Y-m-d')) { return $this->getByRange($dateTimeStart, $dateTimeEnd, $dateTimeStart->format('H:i'), $dateTimeEnd->format('H:i'), $limit, $offset, $loadMeta); } $where = new Where(); $nested = $where->nest(); $nested->equalTo('date', $dateTimeStart->format('Y-m-d')); $nested->greaterThan('time_end', $dateTimeStart->format('H:i')); $nested->unnest(); $where->or; $nested = $where->nest(); $nested->greaterThan('date', $dateTimeStart->format('Y-m-d')); $nested->lessThan('date', $dateTimeEnd->format('Y-m-d')); $nested->unnest(); $where->or; $nested = $where->nest(); $nested->equalTo('date', $dateTimeEnd->format('Y-m-d')); $nested->lessThan('time_start', $dateTimeEnd->format('H:i')); $nested->unnest(); return $this->getBy($where, 'date, time_start ASC', $limit, $offset, $loadMeta); }
public function getFriends() { $select = new Select(); $select->from(array('f' => 'fg_friends')); $select->columns(array('*')); $where = new Where(); $or = $where->nest(); $or->equalTo('user_one', $this->user_id); $or->OR->equalTo('user_two', $this->user_id); $or->unnest(); $where->AND->equalTo('state', 1); $select->where($where); $select->join(array('u1' => 'fg_users'), 'f.user_one = u1.id', array('u1_nickname' => 'nickname', 'u1_id' => 'id')); $select->join(array('u2' => 'fg_users'), 'f.user_two = u2.id', array('u2_nickname' => 'nickname', 'u2_id' => 'id')); $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select); $resultSet = $statement->execute(); $friends = array(); foreach ($resultSet as $result) { $friend = new \stdClass(); if ($result["u1_id"] != $this->user_id) { $friend->{"id"} = $result["u1_id"]; $friend->{"nickname"} = $result["u1_nickname"]; } else { $friend->{"id"} = $result["u2_id"]; $friend->{"nickname"} = $result["u2_nickname"]; } $friends[] = $friend; } return $friends; }
/** * @param $filterParams * @return Where */ protected function constructWhereFromFilterParams($filterParams) { $where = new Where(); if (isset($filterParams['usage']) && $filterParams['usage'] != '') { $where->equalTo('lock_types.' . $filterParams['usage'], 1); } if (isset($filterParams['type_id']) && $filterParams['type_id'] != '' && (int) $filterParams['type_id'] != 0) { $where->equalTo(DbTables::TBL_LOCKS . '.type_id', (int) $filterParams['type_id']); } if (isset($filterParams['sSearch']) && !empty($filterParams['sSearch'])) { $searchQuery = trim(strip_tags($filterParams['sSearch'])); if (!empty($searchQuery)) { $where->nest()->like(DbTables::TBL_LOCKS . '.name', '%' . $searchQuery . '%')->or->like(DbTables::TBL_LOCKS . '.description', '%' . $searchQuery . '%')->unnest(); } } return $where; }