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