/**
* @param int $apartmentId
* @param string $startDate
* @param string $endDate
*
* @return array
*/
public function getMonthlyCost($apartmentId, $startDate, $endDate)
{
$expenses = $this->fetchAll(function (Select $select) use($apartmentId, $startDate, $endDate) {
$nestedWhere = new Where();
$nestedWhere->lessThanOrEqualTo('item.period_from', $endDate)->or->greaterThanOrEqualTo('item.period_to', $startDate);
$select->columns(['amount' => 'amount'])->join(['item' => DbTables::TBL_EXPENSE_ITEM], $this->getTable() . '.expense_item_id = item.id', ['period_from', 'period_to', 'date_created', 'is_refund'], Select::JOIN_LEFT)->join(['expense' => DbTables::TBL_EXPENSES], 'item.expense_id = expense.id', [], Select::JOIN_LEFT)->where->equalTo('expense.status', Ticket::STATUS_GRANTED)->equalTo($this->getTable() . '.cost_center_id', $apartmentId)->equalTo($this->getTable() . '.cost_center_type', ExpenseCosts::TYPE_APARTMENT)->addPredicate($nestedWhere);
});
$result = [];
foreach ($expenses as $expense) {
$monthStart = date("n", strtotime($expense['period_from']));
$yearStart = date("Y", strtotime($expense['period_from']));
$monthEnd = date("n", strtotime($expense['period_to']));
$yearEnd = date("Y", strtotime($expense['period_to']));
$startInMonths = $monthStart + $yearStart * 12;
$endInMonths = $monthEnd + $yearEnd * 12;
$sign = $expense['is_refund'] ? -1 : 1;
$expensePerMonth = $sign * $expense['amount'] / ($endInMonths - $startInMonths + 1);
for ($iterator = $startInMonths; $iterator <= $endInMonths; $iterator++) {
$tempMonth = $iterator % 12;
$tempMonth = $tempMonth ? $tempMonth : 12;
$currentYear = ($iterator - $tempMonth) / 12;
$currentMonth = date('M', mktime(0, 0, 0, $tempMonth, 1));
$key = $currentMonth . '_' . $currentYear;
if (isset($result[$key])) {
$result[$key] += $expensePerMonth;
} else {
$result[$key] = $expensePerMonth;
}
}
}
return $result;
}
/**
* @param $params
* @param $userId
* @return array
*/
public function getAllBudgets($params, $userId)
{
$this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
$where = new Where();
if (isset($params['name']) && $params['name']) {
$where->like($this->getTable() . '.name', $params['name'] . '%');
}
if (isset($params['status']) && $params['status']) {
$where->equalTo($this->getTable() . '.status', $params['status']);
}
if (isset($params['user']) && $params['user']) {
$where->equalTo($this->getTable() . '.user_id', $params['user']);
}
if (isset($params['period']) && $params['period']) {
$dateRange = Helper::refactorDateRange($params['period']);
$where->greaterThanOrEqualTo($this->getTable() . '.to', $dateRange['date_from']);
$where->lessThanOrEqualTo($this->getTable() . '.from', $dateRange['date_to']);
}
if (isset($params['frozen']) && $params['frozen'] >= 0) {
$where->equalTo($this->getTable() . '.frozen', $params['frozen']);
}
if (isset($params['archived']) && $params['archived'] >= 0) {
$where->equalTo($this->getTable() . '.archived', $params['archived']);
}
if ($userId) {
$where->equalTo($this->getTable() . '.user_id', $userId);
}
if (isset($params['department']) && $params['department'] >= 0) {
$where->equalTo($this->getTable() . '.department_id', $params['department']);
}
if (isset($params['country']) && $params['country'] >= 0) {
$where->equalTo($this->getTable() . '.country_id', $params['country']);
}
if (isset($params['global']) && $params['global'] >= 0) {
$where->equalTo($this->getTable() . '.is_global', $params['global']);
}
$offset = $params['iDisplayStart'];
$limit = $params['iDisplayLength'];
$sortCol = $params['iSortCol_0'];
$sortDir = $params['sSortDir_0'];
$result = $this->fetchAll(function (Select $select) use($offset, $limit, $sortCol, $sortDir, $where) {
$sortColumns = ['status', 'name', 'department_name', 'from', 'amount', 'balance', 'user_name'];
$select->columns(['id', 'name', 'from', 'to', 'amount', 'description', 'status', 'user_id', 'department_id', 'country_id', 'is_global', 'balance']);
$select->join(['users' => DbTables::TBL_BACKOFFICE_USERS], $this->getTable() . '.user_id = users.id', ['user_name' => new Expression('CONCAT(firstname, " ", lastname)')], Select::JOIN_LEFT);
$select->join(['teams' => DbTables::TBL_TEAMS], $this->getTable() . '.department_id = teams.id', ['department_name' => 'name'], Select::JOIN_LEFT);
$select->where($where);
$select->group($this->getTable() . '.id')->order($sortColumns[$sortCol] . ' ' . $sortDir)->offset((int) $offset)->limit((int) $limit);
$select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
});
$statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
$resultCount = $statement->execute();
$row = $resultCount->current();
$total = $row['total'];
return ['result' => $result, 'total' => $total];
}
/**
* @param $spotId
* @param $startDate
* @param $endDate
* @return \DDD\Domain\Parking\Spot\Inventory[]
*/
public function getSpotInventoryForRange($spotId, $startDate, $endDate)
{
$result = $this->fetchAll(function (Select $select) use($spotId, $startDate, $endDate) {
$where = new Where();
$where->equalTo($this->getTable() . '.spot_id', $spotId);
$where->greaterThanOrEqualTo($this->table . '.date', $startDate);
$where->lessThanOrEqualTo($this->table . '.date', $endDate);
$select->join(['spot' => DbTables::TBL_PARKING_SPOTS], $this->getTable() . '.spot_id = spot.id', ['price'], Select::JOIN_INNER)->where($where);
});
return $result;
}
/**
* @param int|bool $type
* @return \ArrayObject
*/
public function fetch($type = false)
{
return $this->fetchAll(function (Select $select) use($type) {
$where = new Where();
$where->lessThanOrEqualTo('send_time', new Expression('NOW()'));
if ($type) {
$where->equalTo('type', $type);
$select->join(['applicants' => DbTables::TBL_HR_APPLICANTS], $this->getTable() . '.entity_id = applicants.id', ['status', 'email', 'applicant_name' => new Expression('CONCAT(firstname, " ", lastname)')], Select::JOIN_INNER)->join(['jobs' => DbTables::TBL_HR_JOBS], 'jobs.id = applicants.job_id', ['position_title' => 'title'], Select::JOIN_INNER);
}
$select->where($where)->limit(EmailQueueService::MAX_SEND_COUNT);
});
}
public function fetchByDateRange(\DateTime $startDate = null, \DateTime $endDate = null)
{
$select = $this->tableGateway->getSql()->select();
$where = new WherePredicate();
if (!is_null($startDate)) {
$where->greaterThanOrEqualTo('releaseDate', $startDate->format(self::DATETIME_FORMAT));
}
if (!is_null($endDate)) {
$where->lessThanOrEqualTo('releaseDate', $endDate->format(self::DATETIME_FORMAT));
}
$select->where($where)->order('releaseDate DESC');
$results = $this->tableGateway->selectWith($select);
return $results->buffer();
}
/**
* Get rate availability for given range
* @access public
*
* @param int $rateID
* @param string $startDate
* @param string $endDate
* @return \DDD\Domain\Apartment\Rate\Select
* @author Tigran Petrosyan
*/
public function getRateInventoryForRange($rateID, $startDate, $endDate)
{
$result = $this->fetchAll(function (Select $select) use($rateID, $startDate, $endDate) {
$columns = array('id' => 'id', 'rate_id' => 'rate_id', 'date' => 'date', 'price' => 'price', 'availability' => 'availability', 'room_id' => 'room_id', 'apartment_id' => 'apartment_id', 'is_lock_price' => 'is_lock_price');
$select->join(array('prod_types' => DbTables::TBL_PRODUCT_TYPES), $this->getTable() . '.room_id = prod_types.id', array('cubilis_room_id' => 'cubilis_id'), 'LEFT');
$select->columns($columns);
$where = new Where();
$where->equalTo($this->table . '.rate_id', $rateID);
$where->greaterThanOrEqualTo($this->table . '.date', $startDate);
$where->lessThanOrEqualTo($this->table . '.date', $endDate);
$select->where($where);
});
return $result;
}
/**
* @param int $userId
* @param string $from
* @param string $to
* @return \DDD\Domain\User\Schedule\Inventory[]
*/
public function getUserScheduleInRange($userId, $from, $to)
{
$result = $this->fetchAll(function (Select $select) use($userId, $from, $to) {
$where = new Where();
if ($from) {
$where->greaterThanOrEqualTo('date', $from);
}
if ($to) {
$where->lessThanOrEqualTo('date', $to);
}
$where->equalTo($this->getTable() . '.user_id', $userId);
$select->join(['users' => DbTables::TBL_BACKOFFICE_USERS], $this->getTable() . '.user_id = users.id', ['user_firstname' => 'firstname', 'user_lastname' => 'lastname'], Select::JOIN_INNER)->join(['vacations' => DbTables::TBL_BACKOFFICE_USER_VACATIONS], new Expression($this->getTable() . '.user_id = vacations.user_id' . ' AND vacations.is_approved = 1' . ' AND vacations.from <= ' . $this->getTable() . '.`date`' . ' AND vacations.to >= ' . $this->getTable() . '.`date`'), ['vacation_type' => 'type'], Select::JOIN_LEFT)->where($where)->order([$this->getTable() . '.date' => 'ASC']);
});
return $result;
}
/**
* @param $apartmentIds
* @return \DDD\Domain\ApartmentGroup\ConciergeView
*/
public function getCurrentStays($apartmentIds, $todayWithTimeZone)
{
// Maximize GROUP_CONCAT limitations for housekeeping_comment
$this->adapter->query('SET SESSION group_concat_max_len = 100000;')->execute();
$columns = array('id', 'ki_page_hash', 'guest_first_name', 'guest_last_name', 'res_number', 'pax' => 'man_count', 'occupancy', 'date_to', 'arrival_status', 'guest_balance', 'housekeeping_comment' => new Expression("(\n SELECT\n GROUP_CONCAT(\n CONCAT('<blockquote class=\"comment-blockquote\">', '<p>', value, '</p><footer>', users.firstname, ' ', users.lastname, ', ', `timestamp`, ' (Amsterdam Time)', '</footer></blockquote>') SEPARATOR ''\n )\n FROM " . DbTables::TBL_ACTION_LOGS . "\n LEFT JOIN " . DbTables::TBL_BACKOFFICE_USERS . " AS users ON users.id = " . DbTables::TBL_ACTION_LOGS . ".user_id\n WHERE module_id = " . Logger::MODULE_BOOKING . " AND identity_id = " . $this->getTable() . ".`id` AND action_id = " . Logger::ACTION_HOUSEKEEPING_COMMENT . "\n )"), 'ki_page_status' => 'ki_page_status');
$datetime = new \DateTime($todayWithTimeZone);
$dateToday = $datetime->format('Y-m-d');
$datetime->modify('+1 day');
$dateTomorrow = $datetime->format('Y-m-d');
return $this->fetchAll(function (Select $select) use($apartmentIds, $columns, $dateToday, $dateTomorrow) {
$where = new Where();
$where->lessThanOrEqualTo($this->getTable() . '.date_from', $dateToday)->greaterThanOrEqualTo($this->getTable() . '.date_to', $dateTomorrow)->equalTo($this->getTable() . '.status', Booking::BOOKING_STATUS_BOOKED)->notEqualTo($this->getTable() . '.overbooking_status', BookingTicket::OVERBOOKING_STATUS_OVERBOOKED)->in($this->getTable() . '.apartment_id_assigned', $apartmentIds);
$select->columns($columns)->where($where)->join(['charge' => DbTables::TBL_CHARGE], new Expression($this->getTable() . '.id = charge.reservation_id AND charge.addons_type = 6 AND charge.status = 0'), ['parking' => 'id'], Select::JOIN_LEFT)->join(['tasks' => DbTables::TBL_TASK], new Expression($this->getTable() . '.id = tasks.res_id AND tasks.task_type = ' . Task::TYPE_KEYFOB . ' AND tasks.task_status != ' . Task::STATUS_VERIFIED), ['key_task' => 'id'], Select::JOIN_LEFT)->join(['apartment' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = apartment.id', ['unitNumber' => 'unit_number', 'acc_name' => 'name'], Select::JOIN_LEFT)->group($this->getTable() . '.res_number')->order(['guest_last_name ASC']);
});
}
/**
* @param int $userId
* @param string $from
* @param string $to
* @return \DDD\Domain\User\VacationRequest[]
*/
public function getUsersApprovedVacationsInRange($userId, $from = '', $to = '')
{
$result = $this->fetchAll(function (Select $select) use($userId, $from, $to) {
$where = new Where();
$where->equalTo($this->getTable() . '.user_id', $userId)->equalTo($this->getTable() . '.is_approved', 1);
if ($from) {
$where->greaterThanOrEqualTo($this->getTable() . '.from', $from);
}
if ($to) {
$where->lessThanOrEqualTo($this->getTable() . '.to', $to);
}
$select->columns(['id', 'from', 'to', 'total_number'])->where($where)->order([$this->getTable() . '.from ASC']);
});
return $result;
}
public function testFetchByDateRangeCanSearchUpToADate()
{
$resultSet = new ResultSet();
$record = new FeedModel();
$record->exchangeArray($this->_recordData);
$where = new Where();
$resultSet->initialize(array($record));
$endDate = new \DateTime('2000-01-01');
$mockSql = \Mockery::mock('Zend\\Db\\Sql\\Select');
$mockSql->shouldReceive('select')->andReturn($mockSql);
$mockSql->shouldReceive('where')->with(array($where->lessThanOrEqualTo('feedDate', $endDate->format(FeedTable::DATETIME_FORMAT))))->times(1)->andReturn($mockSql);
$mockSql->shouldReceive('order')->times(1)->with("feedDate DESC, feedTime DESC")->andReturn($resultSet);
$mockTableGateway = \Mockery::mock('Zend\\Db\\TableGateway\\TableGateway');
$mockTableGateway->shouldReceive('getSql')->andReturn($mockSql);
$mockTableGateway->shouldReceive('selectWith')->times(1)->with($mockSql)->andReturn($resultSet);
$mockFeedTable = new FeedTable($mockTableGateway);
$this->assertEquals($resultSet, $mockFeedTable->fetchByDateRange(null, $endDate));
}
public function getNextReservationsForApartment($apartmentId, $todayDate, $lastReservation, $dateTimeAfter2days = false)
{
$prototype = $this->resultSetPrototype->getArrayObjectPrototype();
$this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
$columns = array('id' => 'id', 'apartment_id_assigned' => 'apartment_id_assigned', 'res_number' => 'res_number', 'date_to' => 'date_to', 'date_from' => 'date_from', 'pin' => 'pin', 'pax' => 'man_count', 'outside_door_code' => 'outside_door_code', 'occupancy' => 'occupancy', 'timestamp' => 'timestamp', 'no_refresh' => 'no_refresh');
$result = $this->fetchAll(function (Select $select) use($apartmentId, $todayDate, $columns, $lastReservation, $dateTimeAfter2days) {
$select->columns($columns);
$select->join(['apartment_description' => DbTables::TBL_PRODUCT_DESCRIPTIONS], $this->getTable() . '.apartment_id_assigned = apartment_description.apartment_id', ['apartment_check_in_time' => 'check_in', 'apartment_check_out_time' => 'check_out'], Select::JOIN_LEFT);
$select->join(['task' => DbTables::TBL_TASK], $this->getTable() . '.id = task.res_id', ['task_status'], Select::JOIN_LEFT);
$where = new Where();
$where->equalTo($this->getTable() . '.apartment_id_assigned', $apartmentId)->equalTo($this->getTable() . '.status', BookingService::BOOKING_STATUS_BOOKED)->notEqualTo($this->getTable() . '.overbooking_status', BookingTicketService::OVERBOOKING_STATUS_OVERBOOKED)->greaterThanOrEqualTo($this->getTable() . '.date_from', $todayDate)->notEqualTo($this->getTable() . '.id', $lastReservation['id'])->greaterThanOrEqualTo($this->getTable() . '.date_from', $lastReservation['date_to']);
if ($dateTimeAfter2days !== false) {
$where->lessThanOrEqualTo($this->getTable() . '.date_from', $dateTimeAfter2days);
} else {
$select->limit(1);
}
$select->where($where);
$select->group([$this->getTable() . '.id']);
$select->order(array($this->getTable() . '.date_from ASC'));
});
$this->resultSetPrototype->setArrayObjectPrototype($prototype);
return $result;
}
/**
* @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;
}
/**
* @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);
}
}
}
public function constructWhereFromFilterParams($filterParams, $download = false)
{
/* @var $auth \Library\Authentication\BackofficeAuthenticationService */
$auth = $this->getServiceLocator()->get('library_backoffice_auth');
$where = new Where();
$table = DbTables::TBL_BOOKINGS;
if (isset($filterParams["res_number"]) && $filterParams["res_number"] != '') {
$where->like($table . '.res_number', '%' . $filterParams["res_number"] . '%');
}
if (isset($filterParams["rooms_count"])) {
$apartmentTableName = $download ? DbTables::TBL_APARTMENTS : 'apartments';
$roomsCount = $filterParams["rooms_count"] - 1;
if ($roomsCount >= 0) {
if ($roomsCount == 2) {
$where->greaterThanOrEqualTo($apartmentTableName . '.bedroom_count', $roomsCount);
} else {
$where->equalTo($apartmentTableName . '.bedroom_count', $roomsCount);
}
}
}
if (isset($filterParams["status"]) && $filterParams["status"]) {
if ($filterParams["status"] == Constants::NOT_BOOKED_STATUS) {
$where->notEqualTo($table . '.status', Booking::BOOKING_STATUS_BOOKED);
} else {
$where->equalTo($table . '.status', $filterParams["status"]);
}
}
if (isset($filterParams["arrival_status"]) && $filterParams['arrival_status'] != -1) {
$where->equalTo($table . '.arrival_status', $filterParams["arrival_status"]);
}
if (isset($filterParams["overbooking_status"]) && $filterParams["overbooking_status"] != -1) {
$where->equalTo($table . '.overbooking_status', $filterParams["overbooking_status"]);
}
if (isset($filterParams["apartel_id"]) && $filterParams["apartel_id"] != -2) {
$where->equalTo($table . '.apartel_id', $filterParams["apartel_id"]);
}
if (isset($filterParams["product_id"]) && isset($filterParams["product"]) && $filterParams["product_id"] != '' && $filterParams["product"] != '') {
$where->equalTo($table . '.apartment_id_origin', $filterParams["product_id"]);
}
if (isset($filterParams["assigned_product_id"]) && isset($filterParams["assigned_product"]) && $filterParams["assigned_product_id"] != '' && $filterParams["assigned_product"] != '') {
$where->equalTo($table . '.apartment_id_assigned', $filterParams["assigned_product_id"]);
}
if (isset($filterParams["booking_date"]) && $filterParams["booking_date"] != '') {
$dates = explode(' - ', $filterParams["booking_date"]);
$startDate = $dates[0];
$endDate = $dates[1];
$where->expression('DATE(' . $table . '.timestamp) >= \'' . $startDate . '\'', []);
$where->expression('DATE(' . $table . '.timestamp) <= \'' . $endDate . '\'', []);
}
if (isset($filterParams["arrival_date"]) && $filterParams["arrival_date"] != '') {
$dates = explode(' - ', $filterParams["arrival_date"]);
$startDate = $dates[0];
$endDate = $dates[1];
$where->lessThanOrEqualTo($table . ".date_from", $endDate);
$where->greaterThanOrEqualTo($table . ".date_from", $startDate);
}
if (isset($filterParams["departure_date"]) && $filterParams["departure_date"] != '') {
$dates = explode(' - ', $filterParams["departure_date"]);
$startDate = $dates[0];
$endDate = $dates[1];
$where->lessThanOrEqualTo($table . ".date_to", $endDate);
$where->greaterThanOrEqualTo($table . ".date_to", $startDate);
}
if (isset($filterParams["guest_first_name"]) && $filterParams["guest_first_name"] != '') {
$where->like($table . '.guest_first_name', '%' . $filterParams["guest_first_name"] . '%');
}
if (isset($filterParams["guest_last_name"]) && $filterParams["guest_last_name"] != '') {
$where->like($table . '.guest_last_name', '%' . $filterParams["guest_last_name"] . '%');
}
if (isset($filterParams["guest_phone"]) && $filterParams["guest_phone"] != '') {
$nestedWhere = new Predicate();
$nestedWhere->like($table . '.guest_phone', '%' . $filterParams["guest_phone"] . '%');
$nestedWhere->OR;
$nestedWhere->like($table . '.guest_travel_phone', '%' . $filterParams["guest_phone"] . '%');
$where->addPredicate($nestedWhere);
}
if (isset($filterParams["guest_email"]) && $filterParams["guest_email"] != '') {
$where->like($table . '.guest_email', '%' . $filterParams["guest_email"] . '%');
}
if (isset($filterParams["guest_secondary_email"]) && $filterParams["guest_secondary_email"] != '') {
$where->like($table . '.secondary_email', '%' . $filterParams["guest_secondary_email"] . '%');
}
if (isset($filterParams["guest_country_id"]) && $filterParams["guest_country_id"] != '' && $filterParams["guest_country"] != '') {
$where->equalTo($table . '.guest_country_id', $filterParams["guest_country_id"]);
}
if (isset($filterParams["apt_location_id"]) && $filterParams["apt_location_id"] != '') {
$nestedWhere = new Predicate();
$nestedWhere->equalTo($table . '.acc_country_id', $filterParams["apt_location_id"]);
$nestedWhere->OR;
$nestedWhere->equalTo($table . '.acc_city_id', $filterParams["apt_location_id"]);
$where->addPredicate($nestedWhere);
}
if (isset($filterParams["partner_id"]) && $filterParams["partner_id"] != '0') {
$where->equalTo($table . '.partner_id', $filterParams["partner_id"]);
}
if (isset($filterParams["partner_reference"]) && $filterParams["partner_reference"] != '') {
$where->like($table . '.partner_ref', '%' . $filterParams["partner_reference"] . '%');
}
if (isset($filterParams["payment_model"]) && $filterParams["payment_model"] != '0') {
$where->equalTo($table . '.model', $filterParams["payment_model"]);
}
if (!$auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING)) {
$where->expression($table . '.apartment_id_assigned NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
}
if (isset($filterParams["no_collection"]) && $filterParams["no_collection"] > 1) {
$where->equalTo($table . '.no_collection', $filterParams["no_collection"] == 2 ? 1 : 0);
}
if (isset($filterParams["channel_res_id"]) && $filterParams["channel_res_id"]) {
$where->equalTo($table . '.channel_res_id', $filterParams["channel_res_id"]);
}
return $where;
}
public function getTaskListForSearch($authID, $iDisplayStart = null, $iDisplayLength = null, $filterParams = array(), $sortCol = 0, $sortDir = 'DESC', $taskManger)
{
$where = new Where();
foreach ($filterParams as $key => $row) {
if (!is_array($row)) {
$filterParams[$key] = trim($row);
}
}
if ($filterParams["title"] != '') {
$where->like($this->getTable() . '.title', '%' . $filterParams["title"] . '%');
}
if ($filterParams["status"] > 0) {
$statusArray = [$filterParams["status"]];
if ($filterParams["status"] == TaskService::STATUS_ALL_OPEN) {
$statusArray = [TaskService::STATUS_NEW, TaskService::STATUS_VIEWED, TaskService::STATUS_BLOCKED, TaskService::STATUS_STARTED];
}
$where->in($this->getTable() . '.task_status', $statusArray);
}
if ($filterParams["priority"] > 0) {
$where->equalTo($this->getTable() . '.priority', $filterParams["priority"]);
}
if ($filterParams["type"] > 0) {
$where->equalTo($this->getTable() . '.task_type', $filterParams["type"]);
}
if ($filterParams["creator_id"] > 0) {
$where->equalTo('task_creators.user_id', (int) $filterParams["creator_id"]);
}
if ($filterParams["responsible_id"] > 0) {
$where->equalTo('task_responsibles.user_id', (int) $filterParams["responsible_id"]);
}
if ($filterParams["responsible_id"] < 0) {
$where->isNull('task_responsibles.user_id');
}
if ($filterParams["verifier_id"] > 0) {
$where->equalTo('task_verifiers.user_id', (int) $filterParams["verifier_id"]);
}
if ($filterParams["helper_id"] > 0) {
$where->equalTo('task_helpers.user_id', (int) $filterParams["helper_id"]);
}
if ($filterParams["follower_id"] > 0) {
$where->equalTo('task_followers.user_id', (int) $filterParams["follower_id"]);
}
if ($filterParams["property_id"] > 0 && $filterParams['property']) {
$where->equalTo($this->getTable() . '.property_id', $filterParams["property_id"]);
}
if ($filterParams["team_id"]) {
$where->equalTo($this->getTable() . '.team_id', $filterParams["team_id"]);
}
if (isset($filterParams['tags']) && !empty($filterParams['tags'])) {
$where->in('task_tag.tag_id', explode(',', $filterParams['tags']));
}
if ($filterParams["end_date"] != '') {
$dates = explode(' - ', $filterParams["end_date"]);
$rangeStart = $dates[0];
$rangeEnd = $dates[1];
$where->lessThanOrEqualTo($this->getTable() . ".end_date", $rangeEnd);
$where->greaterThanOrEqualTo($this->getTable() . ".end_date", $rangeStart);
}
if ($filterParams["creation_date"] != '') {
$dates = explode(' - ', $filterParams["creation_date"]);
$rangeStart = $dates[0] . ' 00:00';
$rangeEnd = $dates[1] . ' 23:59';
$where->lessThanOrEqualTo($this->getTable() . ".creation_date", $rangeEnd);
$where->greaterThanOrEqualTo($this->getTable() . ".creation_date", $rangeStart);
}
if ($filterParams["done_date"] != '') {
$dates = explode(' - ', $filterParams["done_date"]);
$rangeStart = $dates[0];
$rangeEnd = $dates[1];
$where->lessThanOrEqualTo($this->getTable() . ".done_date", $rangeEnd);
$where->greaterThanOrEqualTo($this->getTable() . ".done_date", $rangeStart);
}
$buildingId = false;
if ($filterParams["building_id"] > 0 && $filterParams['building']) {
$buildingId = $filterParams["building_id"];
}
$sortColumns = array('priority', 'task_status', 'start_date', 'end_date', 'title', 'apartment_name', 'responsible_name', 'verifier_name', 'task_type');
$result = $this->fetchAll(function (Select $select) use($sortColumns, $iDisplayStart, $iDisplayLength, $where, $sortCol, $sortDir, $authID, $taskManger, $buildingId) {
$select->join(['task_types' => DbTables::TBL_TASK_TYPE], $this->getTable() . '.task_type = task_types.id', ['task_type_name' => 'name'], Select::JOIN_INNER)->join(['task_creators' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_creators.task_id and task_creators.type=' . TaskService::STAFF_CREATOR), [], Select::JOIN_LEFT)->join(['users_creators' => DbTables::TBL_BACKOFFICE_USERS], 'task_creators.user_id = users_creators.id', ['creator_id' => 'id', 'creator_name' => new Expression("CONCAT(users_creators.firstname, ' ', users_creators.lastname)")], Select::JOIN_LEFT)->join(['task_responsibles' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_responsibles.task_id and task_responsibles.type=' . TaskService::STAFF_RESPONSIBLE), [], Select::JOIN_LEFT)->join(['users_responsibles' => DbTables::TBL_BACKOFFICE_USERS], 'task_responsibles.user_id = users_responsibles.id', ['responsible_id' => 'id', 'responsible_name' => new Expression("CONCAT(users_responsibles.firstname, ' ', users_responsibles.lastname)")], Select::JOIN_LEFT)->join(['task_verifiers' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_verifiers.task_id and task_verifiers.type=' . TaskService::STAFF_VERIFIER), [], Select::JOIN_LEFT)->join(['users_verifiers' => DbTables::TBL_BACKOFFICE_USERS], 'task_verifiers.user_id = users_verifiers.id', ['verifier_id' => 'id', 'verifier_name' => new Expression("CONCAT(users_verifiers.firstname, ' ', users_verifiers.lastname)")], Select::JOIN_LEFT)->join(['task_helpers' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_helpers.task_id and task_helpers.type=' . TaskService::STAFF_HELPER), [], Select::JOIN_LEFT)->join(['users_helpers' => DbTables::TBL_BACKOFFICE_USERS], 'task_helpers.user_id = users_helpers.id', ['helper_id' => 'id', 'helper_name' => new Expression("CONCAT(users_helpers.firstname, ' ', users_helpers.lastname)")], Select::JOIN_LEFT)->join(['task_followers' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = task_followers.task_id and task_followers.type=' . TaskService::STAFF_FOLLOWER), [], Select::JOIN_LEFT)->join(['task_team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.team_id = task_team_staff.team_id and task_team_staff.type NOT IN (' . TeamService::STAFF_CREATOR . ', ' . TeamService::STAFF_DIRECTOR . ')'), [], Select::JOIN_LEFT)->join(['task_following_team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.following_team_id = task_following_team_staff.team_id and task_following_team_staff.type NOT IN (' . TeamService::STAFF_CREATOR . ', ' . TeamService::STAFF_DIRECTOR . ')'), [], Select::JOIN_LEFT)->join(['users_followers' => DbTables::TBL_BACKOFFICE_USERS], 'task_followers.user_id = users_followers.id', ['follower_id' => 'id', 'follower_name' => new Expression("CONCAT(users_followers.firstname, ' ', users_followers.lastname)")], Select::JOIN_LEFT)->join(['apartment1' => DbTables::TBL_APARTMENTS], $this->getTable() . '.property_id = apartment1.id', ['apartment_name' => 'name', 'apartment_unit_number' => 'unit_number'], Select::JOIN_LEFT)->join(['subtask' => DbTables::TBL_TASK_SUBTASK], 'subtask.task_id = ' . $this->getTable() . '.id', ['subtask_description' => new Expression('GROUP_CONCAT(subtask.description)')], Select::JOIN_LEFT)->join(['task_tag' => DbTables::TBL_TASK_TAG], new Expression($this->getTable() . '.id = task_tag.task_id'), [], Select::JOIN_LEFT);
if ($buildingId) {
$select->join(['apartment' => DbTables::TBL_APARTMENTS], new Expression($this->getTable() . '.property_id = apartment.id AND apartment.building_id = ' . $buildingId), []);
}
if (!$taskManger) {
$where->expression('(users_creators.id = ' . $authID . ' OR users_responsibles.id = ' . $authID . ' OR task_verifiers.user_id = ' . $authID . ' OR users_helpers.id = ' . $authID . ' OR users_followers.id = ' . $authID . ' OR task_team_staff.user_id = ' . $authID . ' OR task_following_team_staff.user_id = ' . $authID . ')', []);
}
if ($where !== null) {
$select->where($where);
}
$select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
if ($iDisplayLength !== null && $iDisplayStart !== null) {
$select->limit((int) $iDisplayLength);
$select->offset((int) $iDisplayStart);
}
$select->group($this->getTable() . '.id');
$select->order($sortColumns[$sortCol] . ' ' . $sortDir);
});
$statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
$result2 = $statement->execute();
$row = $result2->current();
$total = $row['total'];
return ['result' => $result, 'count' => $total];
}
/**
* @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);
}
}
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));
}
/**
* Retrieves listings from table based on search form field info
* @param array $data = search data
* @return Zend\Db\Results $results
*/
public function search($data)
{
$select = new Select();
$select->from($this->tableName);
$where = new Where();
// key = form field; value = column name
foreach ($this->searchFormMappings as $key => $value) {
if ($data[$key]) {
if ($key == 'priceMin') {
$where->greaterThanOrEqualTo($value, $data[$key]);
} elseif ($key == 'priceMax') {
$where->lessThanOrEqualTo($value, $data[$key]);
} elseif ($key == 'category' && $data[$key] == '1') {
continue;
} else {
$where->like($value, '%' . $data[$key] . '%');
}
}
}
$select->where($where);
return $this->selectWith($select);
}
