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); }
/** * Gets all events within the specified datetime interval. * * Events are ordered by start date and 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) { $where = new Where(); $where->greaterThan('datetime_end', $dateTimeStart->format('Y-m-d H:i:s')); $where->and; $where->lessThan('datetime_start', $dateTimeEnd->format('Y-m-d H:i:s')); return $this->getBy($where, 'datetime_start ASC', $limit, $offset, $loadMeta); }
public function getAvailableSpotsForApartment($apartmentId, $dateFrom, $dateTo) { $prototype = $this->resultSetPrototype->getArrayObjectPrototype(); $this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject()); $result = $this->fetchAll(function (Select $select) use($apartmentId, $dateFrom, $dateTo) { $where = new Where(); $where->lessThan('parking_inventory.date', $dateTo)->greaterThanOrEqualTo('parking_inventory.date', $dateFrom)->equalTo('parking_inventory.availability', 1)->equalTo($this->getTable() . '.apartment_id', $apartmentId); $select->columns([])->join(['apartments' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id = apartments.id', [], Select::JOIN_LEFT)->join(['parking_building' => DbTables::TBL_BUILDING_LOTS], 'apartments.building_section_id = parking_building.building_section_id', [], Select::JOIN_LEFT)->join(['spots' => DbTables::TBL_PARKING_SPOTS], 'parking_building.lot_id = spots.lot_id', ['spot_id' => 'id', 'spot_unit' => 'unit', 'price'], Select::JOIN_LEFT)->join(['lots' => DbTables::TBL_PARKING_LOTS], 'parking_building.lot_id = lots.id', ['lot_name' => 'name'], Select::JOIN_LEFT)->join(['parking_inventory' => DbTables::TBL_PARKING_INVENTORY], 'parking_inventory.spot_id = spots.id', [], Select::JOIN_INNER)->where($where)->group('spots.id')->having('count(parking_inventory.id) = (DATEDIFF("' . $dateTo . '", "' . $dateFrom . '"))'); }); $this->resultSetPrototype->setArrayObjectPrototype($prototype); return $result; }
/** * Garbage Collection * Only delete sessions that have expired. * * @param int $maxlifetime * @return true */ public function gc($maxlifetime) { $platform = $this->tableGateway->getAdapter()->getPlatform(); $where = new Where(); $where->lessThan($this->options->getModifiedColumn(), new Expression('(' . time() . ' - ' . $platform->quoteIdentifier($this->options->getLifetimeColumn()) . ')')); $rows = $this->tableGateway->select($where); $ids = []; /* @var \UthandoSessionManager\Model\Session $row */ foreach ($rows as $row) { $ids[] = $row->{$this->options->getIdColumn()}; } if (count($ids) > 0) { $where = new Where(); $result = (bool) $this->tableGateway->delete($where->in($this->options->getIdColumn(), $ids)); } else { $result = false; } return $result; }
/** * @param DatagridFilter $filter * @throws \Exception */ public function applyFilter(DatagridFilter $filter) { $select = $this->getSelect(); $adapter = $this->getSql()->getAdapter(); $qi = function ($name) use($adapter) { return $adapter->getPlatform()->quoteIdentifier($name); }; $col = $filter->getColumn(); if (!$col instanceof Column\Select) { throw new \Exception('This column cannot be filtered: ' . $col->getUniqueId()); } $colString = $col->getSelectPart1(); if ($col->getSelectPart2() != '') { $colString .= '.' . $col->getSelectPart2(); } if ($col instanceof Column\Select && $col->hasFilterSelectExpression()) { $colString = sprintf($col->getFilterSelectExpression(), $colString); } $values = $filter->getValues(); $wheres = []; foreach ($values as $value) { $where = new Where(); switch ($filter->getOperator()) { case DatagridFilter::LIKE: $wheres[] = $where->like($colString, '%' . $value . '%'); break; case DatagridFilter::LIKE_LEFT: $wheres[] = $where->like($colString, '%' . $value); break; case DatagridFilter::LIKE_RIGHT: $wheres[] = $where->like($colString, $value . '%'); break; case DatagridFilter::NOT_LIKE: $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value . '%']); break; case DatagridFilter::NOT_LIKE_LEFT: $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value]); break; case DatagridFilter::NOT_LIKE_RIGHT: $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', [$value . '%']); break; case DatagridFilter::EQUAL: $wheres[] = $where->equalTo($colString, $value); break; case DatagridFilter::NOT_EQUAL: $wheres[] = $where->notEqualTo($colString, $value); break; case DatagridFilter::GREATER_EQUAL: $wheres[] = $where->greaterThanOrEqualTo($colString, $value); break; case DatagridFilter::GREATER: $wheres[] = $where->greaterThan($colString, $value); break; case DatagridFilter::LESS_EQUAL: $wheres[] = $where->lessThanOrEqualTo($colString, $value); break; case DatagridFilter::LESS: $wheres[] = $where->lessThan($colString, $value); break; case DatagridFilter::BETWEEN: $wheres[] = $where->between($colString, $values[0], $values[1]); break 2; default: throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator()); break; } } if (!empty($wheres)) { $set = new PredicateSet($wheres, PredicateSet::OP_OR); $select->where->andPredicate($set); } }
/** * * @return int */ public function removeDateExpiredRows() { $where = new Where(); $where->lessThan($this->getTable() . '.date', date('Y-m-d')); return $this->delete($where); }
/** * @param $apartmentGroupId * @param $from * @param $to * @param $roomCount * @param $roomType * @return ResultSet */ public function getApartmentGroupOverbookingsForDateRange($apartmentGroupId, $from, $to, $roomCount, $roomType) { $to = date('Y-m-j', strtotime($to . ' +1 days')); $prototype = $this->resultSetPrototype->getArrayObjectPrototype(); $this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject()); $result = $this->fetchAll(function (Select $select) use($apartmentGroupId, $from, $to, $roomCount, $roomType) { $where = new Where(); $where->lessThan($this->getTable() . '.date_from', $to)->greaterThan($this->getTable() . '.date_to', $from)->equalTo($this->getTable() . '.status', BookingService::BOOKING_STATUS_BOOKED)->equalTo($this->getTable() . '.overbooking_status', ReservationTicketService::OVERBOOKING_STATUS_OVERBOOKED); if ($roomCount != -1) { $where->equalTo('A.bedroom_count', $roomCount); } $select->columns(['channel_res_id', 'res_number', 'ki_viewed', 'date_from', 'date_to', 'apartel_id', 'apartment_id' => 'apartment_id_assigned', 'occupancy', 'guest_balance', 'is_locked' => 'locked', 'res_length' => new Expression('datediff(date_to, date_from)'), 'draw_length' => new Expression('datediff(least(date_to, "' . $to . '"), greatest(date_from, "' . $from . '"))'), 'draw_start' => new Expression('datediff(greatest(date_from, "' . $from . '"), "' . $from . '")'), 'res_start' => new Expression('datediff(date_from, "' . $from . '")')])->join(['AGI' => DbTables::TBL_APARTMENT_GROUP_ITEMS], new Expression($this->getTable() . '.apartment_id_assigned = AGI.apartment_id AND AGI.apartment_group_id = ' . $apartmentGroupId), [], Select::JOIN_INNER)->join(['A' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = A.id', ['apartment_name' => 'name'], Select::JOIN_INNER); if ($roomType > 0) { $select->join(['rel_apartment_room_type' => DbTables::TBL_APARTEL_REL_TYPE_APARTMENT], new Expression($this->getTable() . '.apartment_id_assigned = rel_apartment_room_type.apartment_id AND rel_apartment_room_type.apartel_type_id = ' . $roomType), [], Select::JOIN_INNER); } $select->where($where); }); $this->resultSetPrototype->setArrayObjectPrototype($prototype); return $result; }
public function getmanagernames($userRoleId) { $sql = new Sql($this->getAdapter()); $select = $sql->select()->from('company_roles')->where(['id' => $userRoleId, 'is_active' => 1, 'is_delete' => 0]); $roleDetail = $sql->prepareStatementForSqlObject($select)->execute()->current(); $where = new Where(); $sql = new Sql($this->getAdapter()); $select = $sql->select()->from(['ul' => 'userlist'])->columns(['id', 'username'])->join(['cr' => 'company_roles'], 'cr.id=ul.role_id', 'role_name'); $where->lessThan('cr.seniority', $roleDetail['seniority']); $select->where($where); $select->where(['ul.is_active' => 1, 'ul.is_delete' => 0, 'ul.comp_id' => $this->loggedInUserDetails->comp_id, 'cr.is_active' => 1, 'cr.is_delete' => 0])->order('cr.seniority'); $result = $sql->prepareStatementForSqlObject($select)->execute(); $dataArray = array(); if (count($result)) { foreach ($result as $managers) { $tempArr = []; $tempArr['Id'] = $managers['id']; $tempArr['Name'] = $managers['username'] . ' (' . $managers['role_name'] . ')'; $dataArray[] = $tempArr; } } // echo '<pre>';print_r($dataArray);exit; return json_encode($dataArray); }
/** * @param DatagridFilter $filter * @throws \InvalidArgumentException */ public function applyFilter(DatagridFilter $filter) { $select = $this->getSelect(); $adapter = $this->getSql()->getAdapter(); $qi = function ($name) use($adapter) { return $adapter->getPlatform()->quoteIdentifier($name); }; $column = $filter->getColumn(); $colString = $column->getSelectPart1(); if ($column->getSelectPart2() != '') { $colString .= '.' . $column->getSelectPart2(); } if ($column instanceof Column\Select && $column->hasFilterSelectExpression()) { $colString = sprintf($column->getFilterSelectExpression(), $colString); } $values = $filter->getValues(); $filterSelectOptions = $column->getFilterSelectOptions(); $wheres = array(); if ($filter->getColumn()->getType() instanceof Column\Type\DateTime && $filter->getColumn()->getType()->isDaterangePickerEnabled() === true) { $where = new Where(); $wheres[] = $where->between($colString, $values[0], $values[1]); if (count($wheres) > 0) { $set = new PredicateSet($wheres, PredicateSet::OP_AND); $select->where->andPredicate($set); } } else { foreach ($values as $value) { $where = new Where(); switch ($filter->getOperator()) { case DatagridFilter::LIKE: $wheres[] = $where->like($colString, '%' . $value . '%'); break; case DatagridFilter::LIKE_LEFT: $wheres[] = $where->like($colString, '%' . $value); break; case DatagridFilter::LIKE_RIGHT: $wheres[] = $where->like($colString, $value . '%'); break; case DatagridFilter::NOT_LIKE: $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value . '%')); break; case DatagridFilter::NOT_LIKE_LEFT: $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value)); break; case DatagridFilter::NOT_LIKE_RIGHT: $wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array($value . '%')); break; case DatagridFilter::EQUAL: $wheres[] = $where->equalTo($colString, $value); break; case DatagridFilter::NOT_EQUAL: $wheres[] = $where->notEqualTo($colString, $value); break; case DatagridFilter::GREATER_EQUAL: $wheres[] = $where->greaterThanOrEqualTo($colString, $value); break; case DatagridFilter::GREATER: $wheres[] = $where->greaterThan($colString, $value); break; case DatagridFilter::LESS_EQUAL: $wheres[] = $where->lessThanOrEqualTo($colString, $value); break; case DatagridFilter::LESS: $wheres[] = $where->lessThan($colString, $value); break; case DatagridFilter::BETWEEN: $wheres[] = $where->between($colString, $values[0], $values[1]); break; case DatagridFilter::IN: $wheres[] = $where->in($colString, (array) $value); break; case DatagridFilter::NOT_IN: $wheres[] = $where->notin($colString, (array) $value); break; default: throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator()); break; } } if (count($wheres) > 0) { $set = new PredicateSet($wheres, PredicateSet::OP_OR); $select->where->andPredicate($set); } } }
/** * @param int $rateId * @param string $date * * @return int */ public function deleteAvailabilities($rateId, $date) { $where = new Where(); $where->lessThan('date', $date)->and->equalTo('rate_id', $rateId); return $this->delete($where); }