/**
* Construct Where object from query parameters
*
* @param array $filterParams
* @param bool $testApartments
* @return Where
*/
public function constructWhereFromFilterParams($filterParams, $testApartments = true)
{
$auth = $this->getServiceLocator()->get('library_backoffice_auth');
$hasDevTestRole = $auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING);
$where = new Where();
$table = DbTables::TBL_APARTMENTS;
$productStatusGroups = Objects::getProductStatusGroups();
if (!$testApartments || !$hasDevTestRole) {
$where->expression($table . '.id NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
}
if (isset($filterParams["status"]) && $filterParams["status"] != '0') {
$statusGroup = $productStatusGroups[$filterParams["status"]];
$where->in($table . ".status", $statusGroup);
}
if (isset($filterParams["building_id"]) && $filterParams["building_id"] != '0') {
$where->expression($table . '.id IN (SELECT `apartment_id` FROM ' . DbTables::TBL_APARTMENT_GROUP_ITEMS . ' JOIN ' . DbTables::TBL_APARTMENT_GROUPS . ' ON `apartment_group_id` = ' . DbTables::TBL_APARTMENT_GROUPS . '.id WHERE ' . DbTables::TBL_APARTMENT_GROUPS . '.id = ' . $filterParams['building_id'] . ' ) ', []);
}
if (isset($filterParams["address"]) && $filterParams["address"] != '') {
$addressQuery = $filterParams["address"];
$nestedWhere = new Where();
$nestedWhere->like($table . '.name', '%' . $addressQuery . '%')->or->like('det1.name', '%' . $addressQuery . '%')->or->like('det2.name', '%' . $addressQuery . '%')->or->like($table . '.address', '%' . $addressQuery . '%')->or->like($table . '.unit_number', '%' . $addressQuery . '%')->or->like($table . '.postal_code', '%' . $addressQuery . '%');
$where->addPredicate($nestedWhere);
}
if (isset($filterParams['createdDate']) && $filterParams['createdDate'] !== '') {
$createdDate = explode(' - ', $filterParams['createdDate']);
$where->between($table . '.create_date', $createdDate['0'], $createdDate['1']);
}
return $where;
}
public function getListForSearch($iDisplayStart, $iDisplayLength, $filterParams, $sSearch, $sortCol = 0, $sortDir = 'DESC')
{
$where = new Where();
if ($filterParams["category"] > 0) {
$where->equalTo($this->getTable() . '.category_id', $filterParams["category"]);
}
if (FALSE !== strpos($filterParams["location"], '_')) {
$locationsArray = explode('_', $filterParams["location"]);
$locationId = $locationsArray[1];
$where->equalTo($this->getTable() . '.location_entity_id', $locationId);
}
switch ((int) $filterParams["runningOut"]) {
case AssetService::RUNNING_OUT_YES:
$where->expression($this->getTable() . '.quantity <= thresholds.threshold', []);
break;
case AssetService::RUNNING_OUT_NO:
$where->expression($this->getTable() . '.quantity > thresholds.threshold', []);
break;
case AssetService::RUNNING_OUT_NOT_SET:
$where->isNull(' thresholds.threshold');
break;
}
if (strlen($sSearch)) {
$where->nest->like('storages.name', "%" . $sSearch . "%")->or->like('categories.name', "%" . $sSearch . "%")->unnest;
}
$sortColumns = ['category_name', 'location_name', 'quantity', 'running_out', 'threshold'];
$result = $this->fetchAll(function (Select $select) use($filterParams, $sortColumns, $iDisplayStart, $iDisplayLength, $where, $sSearch, $sortCol, $sortDir) {
$select->join(['categories' => DbTables::TBL_ASSET_CATEGORIES], $this->getTable() . '.category_id = categories.id', ['category_name' => 'name'], Select::JOIN_INNER)->join(['storages' => DbTables::TBL_WM_STORAGE], $this->getTable() . '.location_entity_id = storages.id', ['location_name' => 'name'], Select::JOIN_INNER)->join(['thresholds' => DbTables::TBL_WM_THRESHOLD], $this->getTable() . '.category_id = thresholds.asset_category_id AND ' . $this->getTable() . '.location_entity_id = thresholds.storage_id', ['threshold' => 'threshold'], Select::JOIN_LEFT);
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->order($sortColumns[$sortCol] . ' ' . $sortDir);
});
$return['result'] = $result;
$statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
$result2 = $statement->execute();
$row = $result2->current();
$return['count'] = $row['total'];
return $return;
}
/**
* @param array $roles
* @return \Zend\Db\ResultSet\ResultSet
*/
public function getRouteData(array $roles)
{
$results = $this->select(function (Select $select) use($roles) {
$where = new Where();
if (!empty($roles)) {
$where->in('rp.roleId', $roles);
} else {
$where->expression(' 1 = ?', 0);
}
$select->join(array('rp' => 'tbl_route_permission'), 'tbl_route.routeId = rp.routeId', array('routeId'), Select::JOIN_INNER)->where($where)->quantifier(Select::QUANTIFIER_DISTINCT);
});
return $results;
}
/**
* Get the list of weekly hours for the given Employee ID
*
* @param $id Week #
* */
public function getList($id = false)
{
$shifts_gateway = Shifts::factory($this->getServiceLocator());
$employee_id = $this->params()->fromRoute('user_id');
// aggregate hours worked each week by the given user
$result = $shifts_gateway->select(function ($select) use($employee_id, $id) {
$select->columns(array('employee_id', 'week_id' => new Expression("WEEK(start_time)"), 'start_of_week' => new Expression("DATE_FORMAT(\r\n\t\t\t\t\t\tDATE_ADD(start_time, INTERVAL(1 - DAYOFWEEK(start_time)) + 1 DAY),\r\n\t\t\t\t\t\t'%a, %d %b %Y'\r\n\t\t\t\t\t)"), 'end_of_week' => new Expression("DATE_FORMAT(\r\n\t\t\t\t\t\tDATE_ADD(start_time, INTERVAL(7 - DAYOFWEEK(start_time)) + 1 DAY),\r\n\t\t\t\t\t\t'%a, %d %b %Y'\r\n\t\t\t\t\t)"), 'hours' => new Expression("SUM(TIMESTAMPDIFF(HOUR, start_time, end_time))")));
$where = new Where();
$where->equalTo('employee_id', $employee_id);
if ($id !== false) {
$where->expression("WEEK(start_time) = ?", array($id));
}
$select->where($where);
$select->group(array(new Expression("WEEK(start_time)")));
});
return $result;
}
/**
* Returns an collection of items for a page.
* @param int $offset Page offset
* @param int $limit
* @return array
* @internal param int $itemCountPerPage Number of items per page
*/
public function getItems($offset, $limit)
{
$options = $this->_options;
$items = $this->select(function (Select $select) use($offset, $limit, $options) {
$where = new Where();
$select->offset($offset)->limit($limit);
$select->order('date_time DESC');
$select->order('event_logger_id DESC');
if (isset($this->_options["eventData"]) && $this->_options["eventData"] != '') {
$where->expression($this->_sql_search_expression, "%" . $this->_options["eventData"] . "%");
}
$select->where($where);
});
if ($items->count() > 0) {
$this->result("", false);
}
return $items;
}
/**
* @param array $params
* @return Where
* @throws \Exception
*/
private function constructWhereForTransactionSearch($params)
{
$where = new Where();
$transactionTable = DbTables::TBL_EXPENSE_TRANSACTIONS;
if (empty($params['transactionIdList'])) {
if (empty($params['poId'])) {
throw new \Exception('Purchase order id is invalid.');
}
$where->equalTo("{$transactionTable}.expense_id", $params['poId']);
if (!empty($params['transactionId'])) {
$where->equalTo("{$transactionTable}.id", $params['transactionId']);
}
if (!empty($params['accountFrom'])) {
$where->equalTo("{$transactionTable}.money_account_id", $params['accountFrom']);
}
if (!empty($params['accountTo'])) {
$where->equalTo("{$transactionTable}.account_to_id", $params['accountTo']);
}
if (!empty($params['transactionDate'])) {
$where->equalTo("{$transactionTable}.transaction_date", date('Y-m-d', strtotime($params['transactionDate'])));
}
if (!empty($params['creationDate'])) {
$where->expression("date({$transactionTable}.creation_date) = ?", [date('Y-m-d', strtotime($params['creationDate']))]);
}
if (!empty($params['amount'])) {
$where->expression("abs({$transactionTable}.amount) = ?", $params['amount']);
}
} else {
$where->in("{$transactionTable}.id", $params['transactionIdList']);
}
return $where;
}
public function getMatchingOrdersForConsumableAsset($categoryId, $locationEntityType, $locationEntityId, $quantity, $shipmentStatus, $checkOrderExist = 0)
{
$prototype = $this->getEntity();
$this->setEntity(new \ArrayObject());
$result = $this->fetchAll(function (Select $select) use($categoryId, $locationEntityId, $locationEntityType, $quantity, $shipmentStatus, $checkOrderExist) {
$where = new Where();
$where->expression('quantity - received_quantity > 0', []);
if (!$checkOrderExist) {
if (!$shipmentStatus) {
$where->expression('quantity - received_quantity < ?', $quantity);
} else {
$where->expression('quantity - received_quantity = ?', $quantity);
}
$select->columns(['id', 'status_shipping', 'title', 'quantity', 'quantity_type', 'remaining_quantity' => new Expression('quantity - received_quantity')])->order(new Expression('remaining_quantity ASC'));
}
$where->equalTo('asset_category_id', $categoryId)->equalTo('target_id', $locationEntityId)->equalTo('target_type', $locationEntityType)->notIn('status_shipping', [OrderService::STATUS_RECEIVED, OrderService::STATUS_CANCELED, OrderService::STATUS_REFUNDED]);
$select->where($where);
});
$this->setEntity($prototype);
return $result;
}
public function getActiveReservationStartingFromDate($dateStarting, $dateTill)
{
$prototype = $this->resultSetPrototype->getArrayObjectPrototype();
$this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
$columns = array('id', 'apartment_id_assigned', 'date_to');
$res = $this->fetchAll(function (Select $select) use($dateStarting, $dateTill, $columns) {
$select->join(['tasks' => DbTables::TBL_TASK], new Expression($this->getTable() . '.id = tasks.res_id AND `tasks`.`task_type`=3'), [], Select::JOIN_LEFT);
$where = new Where();
$where->expression('DATE(' . $this->getTable() . '.date_from) >= \'' . $dateStarting . '\'' . ' AND ' . 'DATE(' . $this->getTable() . '.date_from) <= \'' . $dateTill . '\'', [])->equalTo($this->getTable() . '.status', BookingService::BOOKING_STATUS_BOOKED)->notEqualTo($this->getTable() . '.arrival_status', ReservationTicketService::BOOKING_ARRIVAL_STATUS_NO_SHOW)->isNull('tasks.id');
$select->columns($columns)->where($where);
});
$this->resultSetPrototype->setArrayObjectPrototype($prototype);
return $res;
}
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 null $parentId
* @param array $roles
* @return array
*/
public function getMenuList($parentId = null, array $roles)
{
$results = $this->select(function (Select $select) use($parentId, $roles) {
try {
$where = new Where();
if (empty($parentId)) {
$where->isNull('parentId');
} else {
$where->equalTo('parentId', $parentId);
}
if (!empty($roles)) {
$where->in('mp.roleId', $roles);
} else {
$where->expression(' 1 = ?', 0);
}
$select->join(array('mp' => 'tbl_menu_permission'), 'tbl_menu.menuId = mp.menuId', array('menuId'), Select::JOIN_INNER)->where($where)->quantifier(Select::QUANTIFIER_DISTINCT)->order(array('priority ASC'));
} catch (\Exception $ex) {
throw $ex;
}
});
$menus = array();
foreach ($results as $menu) {
$nav = array();
$nav['id'] = $menu->getMenuId();
$pages = $this->getMenuList($menu->getMenuId(), $roles);
$caret = '';
if (!empty($pages)) {
$nav['pages'] = $pages;
}
$icon = '';
if ($menu->getIcon()) {
$icon = '<span class="' . $menu->getIcon() . '"></span> ';
}
$nav['title'] = $menu->getDescription();
$nav['label'] = $icon . htmlspecialchars($menu->getTitle()) . $caret;
$nav['order'] = $menu->getPriority();
$nav['rel'] = array('divider' => $menu->getHasDivider());
if ($menu->getUrlType() == 'R') {
$nav['route'] = $menu->getUrls();
} else {
$nav['uri'] = $menu->getUrls();
}
$menus[] = $nav;
}
return $menus;
}
public function constructWhereFromFilterParams($filterParams)
{
$where = new Where();
$table = DbTables::TBL_BACKOFFICE_USERS;
if (isset($filterParams["group"]) && $filterParams["group"] != '0') {
$where->expression($filterParams["group"] . ' IN (SELECT `group_id` FROM ' . DbTables::TBL_BACKOFFICE_USER_GROUPS . ' WHERE `user_id` = ' . $table . '.id ) ', []);
}
if (isset($filterParams["ud"]) && $filterParams["ud"] != '0') {
$where->expression($filterParams["ud"] . ' IN (SELECT `dashboard_id` FROM ' . DbTables::TBL_BACKOFFICE_USER_DASHBOARDS . ' WHERE `user_id` = ' . $table . '.id ) ', []);
}
if (isset($filterParams["team"]) && $filterParams["team"] != '0') {
$where->expression($filterParams["team"] . ' IN (SELECT `team_id` FROM ' . DbTables::TBL_TEAM_STAFF . ' WHERE `user_id` = ' . $table . '.id AND `type` NOT IN (' . TeamService::STAFF_CREATOR . ', ' . TeamService::STAFF_DIRECTOR . ')) ', []);
}
if (isset($filterParams['city']) && $filterParams['city'] > 0) {
$where->EqualTo('city_id', $filterParams['city']);
}
if (isset($filterParams["system-user-status"])) {
if ($filterParams["system-user-status"] == 1) {
$where->and->EqualTo('system', 0);
} elseif ($filterParams["system-user-status"] == 2) {
$where->and->EqualTo('system', 1);
}
}
if (isset($filterParams["external-user-status"])) {
if ($filterParams["external-user-status"] == 1) {
$where->and->EqualTo('external', 0);
} elseif ($filterParams["external-user-status"] == 2) {
$where->and->EqualTo('external', 1);
}
}
if (isset($filterParams["active-user-status"])) {
if ($filterParams["active-user-status"] == 1) {
$where->and->EqualTo('disabled', 0);
} elseif ($filterParams["active-user-status"] == 2) {
$where->and->EqualTo('disabled', 1);
}
}
if (isset($filterParams["sSearch"]) && $filterParams["sSearch"] != '') {
$nestedWhere = new \Zend\Db\Sql\Predicate\Predicate();
$nestedWhere->like($table . '.firstname', '%' . $filterParams["sSearch"] . '%');
$nestedWhere->OR;
$nestedWhere->like($table . '.lastname', '%' . $filterParams["sSearch"] . '%');
$nestedWhere->OR;
$nestedWhere->like($table . '.position', '%' . $filterParams["sSearch"] . '%');
$nestedWhere->OR;
$nestedWhere->like('details' . '.name', '%' . $filterParams["sSearch"] . '%');
$nestedWhere->OR;
$nestedWhere->like('teams' . '.name', '%' . $filterParams["sSearch"] . '%');
$where->addPredicate($nestedWhere);
}
return $where;
}
public function getAttributeId($attribute)
{
$where = new Where();
$where->expression('LOWER(attribute_name) LIKE ?', $attribute);
$resultSet = $this->tableGateways['attribute']->select($where);
if (!$resultSet || $resultSet->count() == 0) {
throw new \Exception("Could not find attribute {$attribute}");
} else {
$row = $resultSet->current();
return $row->id;
}
return false;
}
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;
}
/**
* @param $rateId
* @param $dateFrom
* @param $dateTo
* @param $weekDays
* @param int $isChanged
* @param int $isLockPrice
* @return Where
*/
private function getRateWhereForInventoryRange($rateId, $dateFrom, $dateTo, $weekDays, $isChanged = 0, $isLockPrice = 0)
{
$where = new Where();
$where->equalTo(DbTables::TBL_APARTMENT_INVENTORY . '.rate_id', $rateId)->greaterThanOrEqualTo(DbTables::TBL_APARTMENT_INVENTORY . '.date', $dateFrom)->lessThanOrEqualTo(DbTables::TBL_APARTMENT_INVENTORY . '.date', $dateTo);
if (!is_null($weekDays)) {
$where->expression("weekday(" . DbTables::TBL_APARTMENT_INVENTORY . ".date) in ({$weekDays})", []);
}
if (!is_null($isChanged)) {
$where->equalTo(DbTables::TBL_APARTMENT_INVENTORY . '.is_changed', $isChanged);
}
if (!is_null($isLockPrice)) {
$where->equalTo(DbTables::TBL_APARTMENT_INVENTORY . '.is_lock_price', $isLockPrice);
}
return $where;
}
/**
*
* @param array $filterParams
* @param bool $testApartments
* @return Where
*/
public function constructWhereFromFilterParams($filterParams, $securityLevels = [])
{
/* @var $auth \Library\Authentication\BackofficeAuthenticationService */
$auth = $this->getServiceLocator()->get('library_backoffice_auth');
$hasDevTestRole = $auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING);
$documentsTable = DbTables::TBL_DOCUMENTS;
$where = new Where();
if (!$hasDevTestRole) {
$where->expression('apartment.id NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
}
if (isset($filterParams["validation-range"]) && $filterParams["validation-range"] != '') {
$tempDatesArray = explode(' - ', $filterParams['validation-range']);
$validFrom = $tempDatesArray[0];
$validTo = $tempDatesArray[1];
$where->expression('DATE(' . $documentsTable . '.valid_from) >= DATE("' . $validFrom . '") ' . 'AND DATE(' . $documentsTable . '.valid_to) <= DATE("' . $validTo . '") ', []);
}
if (isset($filterParams['createdDate']) && $filterParams['createdDate'] !== '') {
$createdDate = explode(' - ', $filterParams['createdDate']);
$where->between($documentsTable . '.created_date', $createdDate['0'] . ' 00:00:00', $createdDate['1'] . ' 23:59:59');
}
if (!empty($filterParams['supplier_id']) && $filterParams['supplier_id'] != '78') {
$where->equalTo($documentsTable . '.supplier_id', $filterParams['supplier_id']);
}
if (!empty($filterParams['document_type'])) {
$where->equalTo($documentsTable . '.type_id', $filterParams['document_type']);
}
if (isset($filterParams['legal_entity_id']) && $filterParams['legal_entity_id'] != 0) {
$where->equalTo($documentsTable . '.legal_entity_id', $filterParams['legal_entity_id']);
}
if (isset($filterParams['signatory_id']) && $filterParams['signatory_id'] != 0) {
$where->equalTo($documentsTable . '.signatory_id', $filterParams['signatory_id']);
}
if (!empty($filterParams['author_id'])) {
$where->equalTo($documentsTable . '.created_by', $filterParams['author_id']);
}
if (!empty($filterParams['account_number'])) {
$where->like($documentsTable . '.account_number', '%' . $filterParams['account_number'] . '%');
}
if (!empty($filterParams['entity_id'])) {
$where->equalTo($documentsTable . '.entity_id', $filterParams['entity_id']);
}
if (!empty($filterParams['entity_type'])) {
$where->equalTo($documentsTable . '.entity_type', $filterParams['entity_type']);
}
if (!empty($filterParams['account_holder'])) {
$where->like($documentsTable . '.account_holder', '%' . $filterParams['account_holder'] . '%');
}
if (!empty($filterParams['has_attachment'])) {
switch ($filterParams['has_attachment']) {
case 1:
$where->isNotNull($documentsTable . '.attachment')->notEqualTo($documentsTable . '.attachment', '');
break;
case 2:
$where->NEST->isNull($documentsTable . '.attachment')->OR->equalTo($documentsTable . '.attachment', '')->UNNEST;
break;
}
}
if (isset($filterParams['has_url']) && !empty($filterParams['has_url'])) {
switch ($filterParams['has_url']) {
case 1:
$where->notEqualTo($documentsTable . '.url', '');
break;
case 2:
$where->equalTo($documentsTable . '.url', '');
break;
}
}
$hasSecurityAccess = $auth->hasRole(Roles::ROLE_DOCUMENTS_MANAGEMENT_GLOBAL);
if (isset($securityLevels[0]) && !$hasSecurityAccess) {
$where->in($documentsTable . '.security_level', $securityLevels);
}
return $where;
}
/**
* @param array $filterParams
* @return Where
*/
protected function constructWhereFromFilterParams($filterParams)
{
$where = new Where();
if (!empty($filterParams['apartment_groups'])) {
$where->equalTo('apartment_groups_items.apartment_group_id', $filterParams['apartment_groups']);
}
if (!empty($filterParams['tags'])) {
$where->in('review_category_rel.apartment_review_category_id', explode(',', $filterParams['tags'][0]));
}
if (!empty($filterParams['arrival_date_range'])) {
$dateArray = explode(' - ', $filterParams['arrival_date_range']);
if (isset($dateArray[1])) {
$dateFrom = $dateArray[0];
$dateTo = $dateArray[1];
$where->expression('DATE(reservations.date_from) >= DATE("' . $dateFrom . '") AND ' . 'DATE(reservations.date_from) <= DATE("' . $dateTo . '")', []);
}
}
if (!empty($filterParams['departure_date_range'])) {
$dateArray = explode(' - ', $filterParams['departure_date_range']);
if (isset($dateArray[1])) {
$dateFrom = $dateArray[0];
$dateTo = $dateArray[1];
$where->expression('DATE(reservations.date_to) >= DATE("' . $dateFrom . '") AND ' . 'DATE(reservations.date_to) <= DATE("' . $dateTo . '")', []);
}
}
if (!empty($filterParams['stay_length_from'])) {
$where->expression('DATEDIFF(reservations.date_to, reservations.date_from) >= ' . $filterParams['stay_length_from'], []);
}
if (!empty($filterParams['stay_length_to'])) {
$where->expression('DATEDIFF(reservations.date_to, reservations.date_from) <= ' . $filterParams['stay_length_to'], []);
}
if (!empty($filterParams['score_filter']) && is_array($filterParams['score_filter']) && count($filterParams['score_filter']) != 5) {
$where->in(DbTables::TBL_PRODUCT_REVIEWS . '.score', $filterParams['score_filter']);
}
return $where;
}
/**
* @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;
}
