public function getTranslationListForSearch($filterParams = [])
{
$where = new Where();
$hasPageQuery = false;
if ((int) $filterParams["id_translation"] > 0) {
$where->equalTo($this->getTable() . '.id', $filterParams["id_translation"]);
} else {
if ($filterParams["srch_txt"] != '') {
$where->like($this->getTable() . '.en_html_clean', '%' . strip_tags(trim($filterParams["srch_txt"])) . '%');
}
if ($filterParams['category'] == 1 && isset($filterParams['un_type'][0])) {
$pages = explode(',', $filterParams['un_type'][0]);
$where->in('pr.page_id', $pages);
}
if ($filterParams["description"] != '') {
$where->like($this->getTable() . '.description', '%' . strip_tags(trim($filterParams["description"])) . '%');
}
}
$columns = array('id' => 'id', 'content' => 'en', 'description' => 'description', 'page_name' => new Expression("GROUP_CONCAT(p.name SEPARATOR ', ')"));
$sortColumns = ['id', 'en'];
$result = $this->fetchAll(function (Select $select) use($columns, $sortColumns, $where) {
$select->columns($columns);
$select->join(['pr' => DbTables::TBL_UN_TEXTLINE_PAGE_REL], $this->getTable() . '.id = pr.textline_id', []);
$select->join(['p' => DbTables::TBL_PAGES], 'p.id = pr.page_id', []);
if ($where !== null) {
$select->where($where);
}
$select->group($this->getTable() . '.id');
$select->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
});
$statement = $this->adapter->query('SELECT FOUND_ROWS() as count');
$result2 = $statement->execute();
$row = $result2->current();
return ['result' => $result, 'count' => $row['count']];
}
/**
* 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 search($str)
{
$select = new Select('video');
$spec = new Where();
$spec->like('title', '% ' . $str . ' %');
$select->where($spec);
$select->where->OR->like('title', $str . ' %');
$select->where->OR->like('title', '% ' . $str);
//$select->where(array('title'=>$str));
//$select->where(array('catelog'=>$title,'description'=>$description));
$select->order('id ASC');
/*
// create a new result set based on the Album entity
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Search());
// create a new pagination adapter object
$paginatorAdapter = new DbSelect(
// our configured select object
$select,
// the adapter to run it against
$this->tableGateway->getAdapter(),
// the result set to hydrate
$resultSetPrototype
);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
*/
return $this->tableGateway->selectWith($select);
}
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);
}
public function fetchAll_user($user)
{
$user_new = $user;
$where = new Where();
$where->like('id_uzytkownika', $user_new);
$resultSet = $this->tableGateway->select(array($where));
return $resultSet;
}
/**
* @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];
}
public function fetch($filter = null)
{
$select = new Select();
$select->from('contact_company')->order('name');
if ($filter !== null) {
$where = new Where();
$where->like('name', '%' . $filter . '%')->OR->like('display_name', '%' . $filter . '%');
return $this->select($select->where($where));
} else {
return $this->select($select);
}
}
/**
* @param string $filename
* @param string $match_pattern
* @return Paginator
*/
public function matchingFilename($filename, $match_pattern = self::MATCH_CONTAINS)
{
$select = $this->getTableGateway()->getSql()->select();
$select->join(['file' => 'commit_file_status'], 'commit.commit_id = file.commit_id', []);
$where = new Where();
$where->like('file.name', sprintf($match_pattern, $filename));
$select->where($where);
$selectAdapter = new DbSelect($select, $this->adapter, $this->getTableGateway()->getResultSetPrototype());
$paginator = new CommitPaginator($selectAdapter);
$paginator->setCommitFileStatusMapper($this->getCommitFileStatusMapper());
$paginator->setRepositoryMapper($this->getRepositoryMapper());
return $paginator;
}
public function getSerieTitre($titre)
{
$titre = (string) $titre;
$where = new Where();
$where->like("titre", "%" . $titre . "%");
$rowset = $this->tableGateway->select($where);
$row = $rowset->current();
if ($row) {
return $row;
} else {
throw new \Exception("Could not find serie {$titre}");
}
}
public function search(array $params)
{
$where = new Where();
if (isset($params['product_type_id'])) {
$where->equalTo('product_type_id', $params['product_type_id']);
}
if (isset($params['query']) && trim($params['query'])) {
$where->like('name', "%{$params['query']}%");
} else {
//todo: make this less hacky
return array();
}
$select = $this->getSelect()->where($where);
return $this->selectManyModels($select);
}
public function fetchByDirector($director)
{
$select = $this->tableGateway->getSql()->select();
$where = new WherePredicate();
$select->join('tbldirectors', $this->tableGateway->getTable() . '.directorId', array('firstName', 'lastName', 'dateOfBirth', 'nationality'), \Zend\Db\Sql\Select::JOIN_LEFT);
if (is_int($director)) {
$where->equalTo($this->tableGateway->getTable() . '.directorId', $director);
}
if (is_string($director)) {
$where->like('firstName', $director . '%')->or->like('lastName', $director . '%');
}
$select->where($where)->order('releaseDate DESC');
$results = $this->tableGateway->selectWith($select);
return $results->buffer();
}
/**
* @param $params
* @return array
*/
public function getAllEspms($params)
{
$this->setEntity(new \ArrayObject());
$where = new Where();
if (isset($params['transaction_account']) && $params['transaction_account']) {
$where->equalTo($this->getTable() . '.transaction_account_id', $params['transaction_account']);
}
if (isset($params['account']) && $params['account']) {
$where->equalTo($this->getTable() . '.external_account_id', $params['account']);
}
if (isset($params['type']) && $params['type']) {
$where->equalTo($this->getTable() . '.type', $params['type']);
}
if (isset($params['status']) && $params['status']) {
$where->equalTo($this->getTable() . '.status', $params['status']);
}
if (isset($params['reason']) && $params['reason']) {
$where->like($this->getTable() . '.reason', '%' . $params['reason'] . '%');
}
if (isset($params['amount']) && $params['amount']) {
$where->equalTo($this->getTable() . '.amount', $params['amount']);
}
if (isset($params['is_archived']) && $params['is_archived'] != 2) {
$where->equalTo($this->getTable() . '.is_archived', $params['is_archived']);
}
$offset = $params['iDisplayStart'];
$limit = $params['iDisplayLength'];
$sortCol = $params['iSortCol_0'];
$sortDir = $params['sSortDir_0'];
$this->setEntity(new \ArrayObject());
$result = $this->fetchAll(function (Select $select) use($offset, $limit, $sortCol, $sortDir, $where) {
$sortColumns = ['transaction_account_id', 'external_account_id', 'type', 'status', 'amount', 'creator_id'];
$select->columns(['id', 'transaction_account_id', 'external_account_id', 'type', 'status', 'amount', 'creator_id', 'supplier_name' => new Expression("ifnull(\n ifnull(b_p.partner_name, s.name),\n concat(b_u.firstname, ' ', b_u.lastname)\n )")]);
$select->join(['users' => DbTables::TBL_BACKOFFICE_USERS], $this->getTable() . '.creator_id = users.id', ['creator' => new Expression('CONCAT(users.firstname, " ", users.lastname)')], Select::JOIN_LEFT)->join(['external_account' => DbTables::TBL_EXTERNAL_ACCOUNT], $this->getTable() . '.external_account_id = external_account.id', ['external_account_name' => 'name'], Select::JOIN_LEFT)->join(['t_a' => DbTables::TBL_TRANSACTION_ACCOUNTS], $this->getTable() . '.transaction_account_id = t_a.id', ['transaction_type' => 'type'], Select::JOIN_LEFT)->join(['b_u' => DbTables::TBL_BACKOFFICE_USERS], new Expression('t_a.holder_id = b_u.id AND t_a.type=' . Account::TYPE_PEOPLE), [], Select::JOIN_LEFT)->join(['s' => DbTables::TBL_SUPPLIERS], new Expression('t_a.holder_id = s.id AND t_a.type=' . Account::TYPE_SUPPLIER), [], Select::JOIN_LEFT)->join(['b_p' => DbTables::TBL_BOOKING_PARTNERS], new Expression('t_a.holder_id = b_p.gid AND t_a.type=' . Account::TYPE_PARTNER), [], Select::JOIN_LEFT)->join(['currency' => DbTables::TBL_CURRENCY], $this->getTable() . '.currency_id = currency.id', ['currency_code' => 'code'], 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];
}
public function getPlayerByName($name)
{
$adapter = $this->tableGateway->getAdapter();
$where = new Where();
$sql = new Sql($adapter);
$select = $sql->select();
$select->from('player');
$select->join('haircolor', 'haircolor.id = player.hairColorId ', array('haircolor' => 'name'), $type = self::JOIN_LEFT);
$select->join('skincolor', 'skincolor.id = player.skincolorId ', array('skincolor' => 'name'), $type = self::JOIN_LEFT);
// $select->columns(array('name'));
$where->like('player.name', '%' . $name . '%');
$select->where($where);
$statement = $sql->prepareStatementForSqlObject($select);
$rowset = $statement->execute();
$row = $rowset->current();
//if (!$row) {
// throw new \Exception("Could not find row $id");
//}
return $row;
}
/**
* Get by params
*
* @param array $params
* @return array|\ArrayObject|null
*/
public function getExternalAccountsByParams($params = [])
{
$result = $this->fetchAll(function (Select $select) use($params) {
$where = new Where();
$where->equalTo('transaction_account_id', $params['transactionAccountID']);
if (isset($params['status']) && $params['status'] > 0) {
$where->equalTo('status', $params['status']);
}
if (!empty($params['sort'])) {
foreach ($params['sort'] as $col => $dir) {
$select->order($col . ' ' . $dir);
}
}
if (!empty($params['search'])) {
$where->like('name', '%' . $params['search'] . '%');
}
$select->where($where)->order('id DESC');
});
return $result;
}
public function searchByNickname($name)
{
/*
SELECT id, nickname, MAX(state) 'friendship' FROM fg_users u
LEFT JOIN fg_friends f ON u.id = f.user_one OR u.id = f.user_two
WHERE UCASE(nickname) LIKE "%LI%"
GROUP BY u.id*/
$select = new Select();
// $subSelect = new Select();
$select->from(array('u' => 'fg_users'))->columns(array('id', 'nickname'));
// $select->from(array('u' => 'fg_users'), array('MAX(state)', 'UCASE(nickname)') );
// $select->columns(array('id' => 'id', 'nickname' => 'nickname', 'friendship' => 'friendship'));
// $select->join(array('f' => 'fg_friends'), 'u.id = f.user_one OR u.id = f.user_two', array(), 'inner');
$where = new Where();
$where->like(new Expression('UCASE(nickname)'), '%' . strtoupper($name) . '%');
$where->AND->notEqualTo('id', $this->user_id);
$select->where($where);
// $select->group('id');
$statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select);
$resultSet = $statement->execute();
return $resultSet;
}
/**
* @param int $reservationId
* @param int $status 1 for active ones, 2 for inactive, 0 for all
* @return int
*/
public function getParkingLotsCountForDatatable($search, $status = 1)
{
$prototype = $this->resultSetPrototype->getArrayObjectPrototype();
$this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
$result = $this->fetchOne(function (Select $select) use($search, $status) {
$like = $search['value'];
$where = new Where();
if ($status == 1) {
$where->equalTo('active', 1);
} else {
if ($status == 2) {
$where->equalTo('active', 0);
}
}
$where->like('name', '%' . $like . '%');
$select->columns(['count' => new Expression('count(*)')])->where($where);
});
$count = 0;
if ($result) {
$count = $result['count'];
}
$this->resultSetPrototype->setArrayObjectPrototype($prototype);
return $count;
}
/**
* @param $params
* @return Where
* @throws \Exception
*/
private function constructWhereForItemSearchForDatatable($params)
{
$where = new Where();
$itemTable = DbTables::TBL_EXPENSE_ITEM;
if (!empty($params['item-search-supplier'])) {
$where->equalTo("{$itemTable}.account_id", $params['item-search-supplier']);
}
if (!empty($params['creator_id'])) {
$where->equalTo("{$itemTable}.creator_id", $params['creator_id']);
}
if (!empty($params['item-search-creation-date'])) {
list($creationFrom, $creationTo) = explode(' - ', $params['item-search-creation-date']);
$creationFrom = date(Constants::DATABASE_DATE_FORMAT, strtotime($creationFrom));
$creationTo = date(Constants::DATABASE_DATE_FORMAT, strtotime($creationTo));
$where->expression("date({$itemTable}.date_created) BETWEEN ? AND ?", [$creationFrom, $creationTo]);
}
if (!empty($params['item-search-reference'])) {
$where->like("{$itemTable}.account_reference", "%{$params['item-search-reference']}%");
}
if ($params['item-search-amount'] !== '') {
$where->equalTo("{$itemTable}.amount", $params['item-search-amount']);
}
if (!empty($params['item-search-period'])) {
list($periodFrom, $periodTo) = explode(' - ', $params['item-search-period']);
$periodFrom = date(Constants::DATABASE_DATE_FORMAT, strtotime($periodFrom));
$periodTo = date(Constants::DATABASE_DATE_FORMAT, strtotime($periodTo));
$whereP = new Where();
$whereP->between("{$itemTable}.period_from", $periodFrom, $periodTo);
$whereP->or;
$whereP->between("{$itemTable}.period_to", $periodFrom, $periodTo);
$where->andPredicate($whereP);
}
if (!empty($params['item-search-category'])) {
list($categoryId, $categoryType) = explode('_', $params['item-search-category']);
// Conventional: 1 - category, 2 - sub category
if ($categoryType == 1) {
$where->equalTo("sub_category.category_id", $categoryId);
$where->equalTo("sub_category.category_id", $categoryId);
} elseif ($categoryType == 2) {
$where->equalTo("{$itemTable}.sub_category_id", $categoryId);
} else {
throw new \Exception('Invalid category type.');
}
}
if (!empty($params['item-search-cost-center'])) {
list($costCenterType, $costCenterId) = explode('_', $params['item-search-cost-center']);
// Conventional: 1 - apartment, 2 - office section
if (in_array($costCenterType, [1, 2])) {
$where->equalTo("cost.cost_center_id", $costCenterId);
$where->equalTo("cost.cost_center_type", $costCenterType);
} else {
throw new \Exception('Invalid cost center type.');
}
}
return $where;
}
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 getDatosOtro($otro)
{
//$datos = $this->select(array('rut'=>$rut));
$datos = $this->select(function (Select $select) use($otro) {
$where = new Where();
$where->like('dni', $otro . "%");
$select->where($where);
});
$recorre = $datos->toArray();
return $recorre;
}
/**
* @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 $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;
}
public function changeSubtaskOccupancy($taskId, $occupancy)
{
$where = new Where();
$where->like('description', '%Occupancy:%')->equalTo('task_id', $taskId);
$this->save(['description' => 'Occupancy: ' . $occupancy], $where);
}
/**
* Fetch all or a subset of resources
*
* @param array $params
* @return ApiProblem|mixed
*/
public function fetchAll($params = array())
{
$where = new Where();
if (isset($params['nome']) && $params['nome'] != '') {
$likeSpec = new Where();
$likeSpec->like('nome', '%' . $params['nome'] . '%');
$where->addPredicate($likeSpec);
$likeSpec = new Where();
$likeSpec->like('sobrenome', '%' . $params['nome'] . '%');
$where->orPredicate($likeSpec);
}
$sort = null;
if (in_array($params['sort'], array_keys((new ClienteEntity())->getArrayCopy()))) {
$sort = $params['sort'];
}
$dbTableGatewayAdapter = new DbTableGateway($this->tableGateway, $where, $sort);
return new ClienteCollection($dbTableGatewayAdapter);
}
/**
* @param int $resId
* @param int $start
* @param int $length
* @param array $order
* @param array $search
* @param int $status
* @return \DDD\Domain\Task\Task[]
*/
public function getTasksOnReservationForDatatable($resId, $start, $length, $order, $search, $status)
{
$result = $this->fetchAll(function (Select $select) use($resId, $start, $length, $order, $search, $status) {
$like = $search['value'];
$where = new Where();
if ($status == 1) {
$where->notIn('task_status', [TaskService::STATUS_CANCEL, TaskService::STATUS_VERIFIED, TaskService::STATUS_DONE]);
} else {
if ($status == 2) {
$where->in('task_status', [TaskService::STATUS_CANCEL, TaskService::STATUS_VERIFIED, TaskService::STATUS_DONE]);
}
}
$where->equalTo('reservations.id', $resId);
$columns = ['priority', 'title', 'task_status', 'start_date', 'end_date', 'id', 'task_type'];
$orderColumns = ['priority', 'task_status', 'start_date', 'end_date', 'title', 'task_type', 'creator_name', 'responsible_name'];
$nestedWhere = new Where();
$nestedWhere->like('title', '%' . $like . '%')->or->like($this->getTable() . '.start_date', '%' . $like . '%')->or->like($this->getTable() . '.end_date', '%' . $like . '%')->or->like('creator_users.firstname', '%' . $like . '%')->or->like('creator_users.lastname', '%' . $like . '%');
$where->addPredicate($nestedWhere);
$orderList = [];
foreach ($order as $entity) {
$orderList[] = $orderColumns[$entity['column']] . ' ' . $entity['dir'];
}
$select->columns($columns)->join(['reservations' => DbTables::TBL_BOOKINGS], $this->getTable() . '.res_id = reservations.id', [], Select::JOIN_INNER)->join(['task_types' => DbTables::TBL_TASK_TYPE], $this->getTable() . '.task_type = task_types.id', ['task_type_name' => 'name'], Select::JOIN_INNER)->join(['creators' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = creators.task_id AND creators.type = ' . TaskService::STAFF_CREATOR), [], Select::JOIN_INNER)->join(['creator_users' => DbTables::TBL_BACKOFFICE_USERS], 'creators.user_id = creator_users.id', ['creator_id' => 'id', 'creator_name' => new Expression('CONCAT(creator_users.firstname, " ", creator_users.lastname)')], Select::JOIN_INNER)->join(['responsibles' => DbTables::TBL_TASK_STAFF], new Expression($this->getTable() . '.id = responsibles.task_id AND responsibles.type = ' . TaskService::STAFF_RESPONSIBLE), [], Select::JOIN_LEFT)->join(['responsible_users' => DbTables::TBL_BACKOFFICE_USERS], 'responsibles.user_id = responsible_users.id', ['responsible_id' => 'id', 'responsible_name' => new Expression('CONCAT(responsible_users.firstname, " ", responsible_users.lastname)')], Select::JOIN_LEFT)->where($where)->order($orderList)->group($this->getTable() . '.id')->offset((int) $start)->limit((int) $length)->quantifier(new Expression('SQL_CALC_FOUND_ROWS'));
});
$statement = $this->adapter->query('SELECT FOUND_ROWS() as total');
$result2 = $statement->execute();
$row = $result2->current();
$total = $row['total'];
return ['result' => $result, 'total' => $total];
}
/**
* 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);
}
/**
*
* @param string $cityName
*/
public function getCityLike($cityName)
{
$where = new Where();
$where->like('city', $cityName . '%');
$select = new Select();
$select->from($this->getTableName())->columns(array('world_city_area_code_id', 'city', 'state_province', 'ISO2'))->where($where);
// format in the form of an array
$list = $this->getTableGateway()->selectWith($select);
$output = array();
foreach ($list as $row) {
$output[] = $row->city . ' ' . $row->state_province . ' ' . $row->ISO2;
}
return $output;
}
/**
* @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 search($str)
{
$select = new Select('video');
//echo $str;
//function getwhere(Where $where,$str) {
// $where->like('title', '%in%');
// }
// $spec=getwhere($str);
$spec = new Where();
$spec->like('title', '%' . $str . '%');
// var_dump($spec);
$select->where($spec);
//$select->where(array('title'=>$str));
//$select->where(array('catelog'=>$title,'description'=>$description));
$select->order('id ASC');
// create a new result set based on the Album entity
$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Main());
// create a new pagination adapter object
$paginatorAdapter = new DbSelect($select, $this->tableGateway->getAdapter(), $resultSetPrototype);
$paginator = new Paginator($paginatorAdapter);
return $paginator;
}
/**
* @param string $query
* @param stdClass $user
* @param int $limit
* @return \DDD\Domain\Booking\FrontierCard[]
*/
public function getFrontierCardList($query, $user, $limit)
{
$this->resultSetPrototype->setArrayObjectPrototype(new \DDD\Domain\Booking\FrontierCard());
// Execute for search purposes only
if (!$query) {
return false;
}
return $this->select(function (Select $select) use($query, $user, $limit) {
$columns = ['id', 'res_number', 'guest_first_name', 'guest_last_name'];
$where = new Where();
$nestedWhere = new Where();
$nestedWhere->like($this->getTable() . '.guest_first_name', '%' . $query . '%')->or->like($this->getTable() . '.guest_last_name', '%' . $query . '%')->or->equalTo($this->getTable() . '.res_number', $query);
$where->greaterThanOrEqualTo($this->getTable() . '.date_from', new Expression('DATE_SUB(CURDATE(),INTERVAL 30 DAY)'))->lessThanOrEqualTo($this->getTable() . '.date_to', new Expression('DATE_ADD(CURDATE(),INTERVAL 30 DAY)'))->addPredicate($nestedWhere);
$select->columns($columns)->join(['apartments' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = apartments.id', ['apartment_assigned' => 'name'], Select::JOIN_INNER)->join(['group_item' => DbTables::TBL_APARTMENT_GROUP_ITEMS], $this->getTable() . '.apartment_id_assigned = group_item.apartment_id', [])->join(['cda' => DbTables::TBL_CONCIERGE_DASHBOARD_ACCESS], new Expression('group_item.apartment_group_id = cda.apartment_group_id AND cda.user_id = ' . $user->id), [])->where($where);
if ($limit) {
$select->limit($limit);
}
});
}
/**
* @param string $query
* @param stdClass $user
* @param int $limit
* @return \DDD\Domain\Apartment\FrontierCard[]
*/
public function getFrontierCardList($query, $user, $limit)
{
$this->resultSetPrototype->setArrayObjectPrototype(new \DDD\Domain\Apartment\FrontierCard());
// Execute for search purposes only
if (!$query) {
return false;
}
return $this->select(function (Select $select) use($query, $user, $limit) {
$columns = ['id', 'name', 'unit_number'];
$where = new Where();
$nestedWhere = new Where();
$nestedWhere->like($this->getTable() . '.name', '%' . $query . '%')->or->like($this->getTable() . '.address', '%' . $query . '%')->or->equalTo($this->getTable() . '.unit_number', $query);
$where->addPredicate($nestedWhere)->in($this->table . '.status', [Objects::PRODUCT_STATUS_LIVEANDSELLIG, Objects::PRODUCT_STATUS_SELLINGNOTSEARCHABLE]);
$select->columns($columns)->join(['group_item' => DbTables::TBL_APARTMENT_GROUP_ITEMS], $this->getTable() . '.id = group_item.apartment_id', [])->join(['cda' => DbTables::TBL_CONCIERGE_DASHBOARD_ACCESS], new Expression('group_item.apartment_group_id = cda.apartment_group_id AND cda.user_id = ' . $user->id), [])->join(['buildings' => DbTables::TBL_APARTMENT_GROUPS], $this->getTable() . '.building_id = buildings.id', ['building' => 'name'], Select::JOIN_LEFT)->where($where);
if ($limit) {
$select->limit($limit);
}
});
}
