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;
}