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);
}
/**
* @return \DDD\Domain\Warehouse\Assets\Consumable []
*/
public function getAssetsAwaitingApproval()
{
$result = $this->fetchAll(function (Select $select) {
$where = new Where();
$where->greaterThan($this->getTable() . '.quantity_change', 0);
$select->columns(['id', 'category_id', 'location_entity_id', 'location_entity_type', 'location_name' => new Expression(" (CASE " . $this->getTable() . ".location_entity_type " . "WHEN " . AssetService::ENTITY_TYPE_APARTMENT . " THEN apartments.name " . "WHEN " . AssetService::ENTITY_TYPE_OFFICE . " THEN offices.name " . "WHEN " . AssetService::ENTITY_TYPE_BUILDING . " THEN apartment_groups.name " . "WHEN " . AssetService::ENTITY_TYPE_STORAGE . " THEN storages.name " . " END)"), 'quantity_change', 'shipment_status'])->join(['cons' => DbTables::TBL_ASSETS_CONSUMABLE], $this->getTable() . '.category_id = cons.category_id', ['asset_id' => 'id'], Select::JOIN_INNER)->join(['categories' => DbTables::TBL_ASSET_CATEGORIES], $this->getTable() . '.category_id = categories.id', ['category_name' => 'name'], Select::JOIN_INNER)->join(['users' => DbTables::TBL_BACKOFFICE_USERS], 'cons.last_updated_by_id = users.id', ['firstname_last_updated' => 'firstname', 'lastname_last_updated' => 'lastname'], Select::JOIN_LEFT)->join(['apartments' => DbTables::TBL_APARTMENTS], new Expression($this->getTable() . '.location_entity_id = apartments.id AND ' . $this->getTable() . '.location_entity_type = ' . AssetService::ENTITY_TYPE_APARTMENT), [], Select::JOIN_LEFT)->join(['offices' => DbTables::TBL_OFFICES], new Expression($this->getTable() . '.location_entity_id = offices.id AND ' . $this->getTable() . '.location_entity_type = ' . AssetService::ENTITY_TYPE_OFFICE), [], Select::JOIN_LEFT)->join(['apartment_groups' => DbTables::TBL_APARTMENT_GROUPS], new Expression($this->getTable() . '.location_entity_id = apartment_groups.id AND ' . $this->getTable() . '.location_entity_type = ' . AssetService::ENTITY_TYPE_BUILDING), [], Select::JOIN_LEFT)->join(['storages' => DbTables::TBL_WM_STORAGE], new Expression($this->getTable() . '.location_entity_id = storages.id AND ' . $this->getTable() . '.location_entity_type = ' . AssetService::ENTITY_TYPE_STORAGE), [], Select::JOIN_LEFT)->where($where);
});
return $result;
}
public function moveAfter($nodeMoveID, $nodeSelectionID)
{
// ========================= Detach branch =========================
$totalNode = $this->detachBranch($nodeMoveID);
$nodeSelectionInfo = $this->getNodeInfo(array('id' => $nodeSelectionID));
$nodeMoveInfo = $this->getNodeInfo(array('id' => $nodeMoveID));
// ========================= Node on tree (LEFT) ========================= data + where -
$data = array('left' => new Expression('`left` + ?', array($totalNode * 2)));
$where = new Where();
$where->greaterThan('left', $nodeSelectionInfo->right);
$where->greaterThan('right', 0);
$this->tableGateway->update($data, $where);
// ========================= Node on tree (RIGHT) ========================= data + where -
$data = array('right' => new Expression('`right` + ?', array($totalNode * 2)));
$where = new Where();
$where->greaterThan('right', $nodeSelectionInfo->right);
$this->tableGateway->update($data, $where);
// ========================= Node on branch (LEVEL) ========================= data - where +
$where = new Where();
$where->lessThanOrEqualTo('right', 0);
$data = array('level' => new Expression('`level` + ?', array($nodeSelectionInfo->level - $nodeMoveInfo->level)));
$this->tableGateway->update($data, $where);
// ========================= Node on branch (LEFT) ========================= data - where +
$data = array('left' => new Expression('`left` + ?', array($nodeSelectionInfo->right + 1)));
$this->tableGateway->update($data, $where);
// ========================= Node on branch (RIGHT) ========================= data - where +
$data = array('right' => new Expression('`right` + ?', array($nodeSelectionInfo->right + $totalNode * 2)));
$this->tableGateway->update($data, $where);
// ========================= Node move (PARENT) ========================= data + where +
$data = array('parent' => $nodeSelectionInfo->parent);
$this->tableGateway->update($data, array('id' => $nodeMoveInfo->id));
}
/**
* Updates left and right values of tree
*
* @param $lft_rgt
* @param string $operator
* @param int $offset
* @return array
* @internal param int $left_rgt
*/
protected function updateTree($lft_rgt, $operator, $offset)
{
$lft = new Where();
$rgt = new Where();
$lftUpdate = $this->update([self::COLUMN_LEFT => new Expression(self::COLUMN_LEFT . $operator . $offset)], $lft->greaterThan(self::COLUMN_LEFT, $lft_rgt));
$rgtUpdate = $this->update([self::COLUMN_RIGHT => new Expression(self::COLUMN_RIGHT . $operator . $offset)], $rgt->greaterThan(self::COLUMN_RIGHT, $lft_rgt));
return [$lftUpdate, $rgtUpdate];
}
/**
* @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
*
* @author Tigran Petrosyan
*/
public function getNotChargedApartelReservationsCount()
{
$this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
$result = $this->fetchOne(function (Select $select) {
$select->join(['apartment_groups' => DbTables::TBL_APARTMENT_GROUPS], $this->getTable() . '.apartel_id = apartment_groups.id', []);
$select->join(['charges' => DbTables::TBL_CHARGE], new Expression($this->getTable() . '.id = charges.reservation_id AND charges.status=0'), [], Select::JOIN_LEFT);
$where = new Where();
$where->greaterThan($this->getTable() . '.apartel_id', 0);
$where->equalTo($this->getTable() . '.status', \DDD\Service\Booking::BOOKING_STATUS_BOOKED);
$where->isNull('charges.id');
$where->notEqualTo($this->getTable() . '.check_charged', 1);
$where->expression($this->getTable() . '.apartment_id_assigned NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
$where->greaterThanOrEqualTo($this->getTable() . '.date_from', date('Y-m-d'));
$where->expression('(' . $this->getTable() . '.is_refundable = 2 or ' . $this->getTable() . '.refundable_before_hours >= TIMESTAMPDIFF(HOUR,NOW(),date_from))', []);
$select->columns(['count' => new Expression('COUNT(*)')]);
$select->where($where);
});
return $result['count'];
}
/**
* @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 array $data
* @return array
*/
public function getPayToPartnerReservations(array $data)
{
/**
* @var \DDD\Dao\Booking\Booking $bookingDao
*/
$bookingDao = $this->getServiceLocator()->get('dao_booking_booking');
$bookingDao->setEntity(new \DDD\Domain\Booking\BookingTableRow());
$where = new Where();
if (isset($data['res_number'])) {
$where->equalTo(DbTables::TBL_BOOKINGS . '.res_number', $data['res_number']);
} else {
$partnerId = $data['partner'];
$dateFrom = $data['date_from'];
$dateTo = $data['date_to'];
$dist = $data['dist'];
$where->equalTo(DbTables::TBL_BOOKINGS . '.partner_id', $partnerId);
$where->greaterThanOrEqualTo(DbTables::TBL_BOOKINGS . '.date_to', date('Y-m-d', strtotime($dateFrom)));
$where->lessThanOrEqualTo(DbTables::TBL_BOOKINGS . '.date_to', date('Y-m-d', strtotime($dateTo)));
$where->greaterThan(DbTables::TBL_BOOKINGS . '.partner_balance', 0);
$where->equalTo(DbTables::TBL_BOOKINGS . '.partner_settled', 0);
$where->equalTo(DbTables::TBL_BOOKINGS . '.payment_settled', 1);
$where->expression(DbTables::TBL_BOOKINGS . '.apartment_id_assigned not in (?, ?)', [Constants::TEST_APARTMENT_1, Constants::TEST_APARTMENT_2]);
if (is_array($dist) && count($dist)) {
$apartmentIdList = [];
$apartelIdList = [];
$fiscalList = [];
foreach ($dist as $distItem) {
list($entityType, $entityId) = explode('_', $distItem);
// 1 for apartment, 2 for apartel (as a convention)
if ($entityType == Distribution::TYPE_APARTMENT) {
array_push($apartmentIdList, $entityId);
} elseif ($entityType == Distribution::TYPE_APARTEL) {
array_push($apartelIdList, $entityId);
} else {
array_push($fiscalList, $entityId);
}
}
if (!count($apartmentIdList) && !count($apartelIdList) && !count($fiscalList)) {
throw new \RuntimeException('Entity not selected.');
}
$wrapperPredicate = new Predicate();
$checkAddOr = false;
// apartment
if (count($apartmentIdList)) {
$predicate = new Predicate();
$predicate->in(DbTables::TBL_BOOKINGS . '.apartment_id_origin', $apartmentIdList)->lessThan(DbTables::TBL_BOOKINGS . '.apartel_id', 1);
$wrapperPredicate->addPredicate($predicate);
$checkAddOr = true;
}
// apartel
if (count($apartelIdList)) {
if ($checkAddOr) {
$wrapperPredicate->or;
}
$checkAddOr = true;
$wrapperPredicate->in(DbTables::TBL_BOOKINGS . '.apartel_id', $apartelIdList);
}
// fiscal
if (count($fiscalList)) {
if ($checkAddOr) {
$wrapperPredicate->or;
}
$wrapperPredicate->in('fiscal.id', $fiscalList);
}
$where->addPredicate($wrapperPredicate);
}
}
$reservations = $bookingDao->getPayToPartnerReservationsByFilter($where);
$reservationList = [];
if ($reservations->count()) {
foreach ($reservations as $reservation) {
array_push($reservationList, ['id' => $reservation->getReservationId(), 'res_number' => $reservation->getReservationNumber(), 'status' => $reservation->getStatus(), 'apartel_id' => $reservation->getApartelId(), 'booking_date' => $reservation->getBookingDate(), 'departure_date' => $reservation->getDepartureDate(), 'apartment_id' => $reservation->getApartmentId(), 'apartment_name' => $reservation->getApartmentName(), 'partner_id' => $reservation->getPartnerId(), 'partner_name' => $reservation->getPartnerName(), 'guest_balance' => $reservation->getGuestBalance(), 'partner_balance' => $reservation->getPartnerBalance(), 'symbol' => $reservation->getSymbol(), 'currency_id' => $reservation->getCurrencyId()]);
}
}
return $reservationList;
}
