public function getTranslationListForSearch($filterParams = array())
{
$columns = array('id' => 'id', 'type' => 'entity_type', 'content' => 'en', 'entity_name' => new Expression("\n CASE\n WHEN (apartel.id IS NOT NULL) THEN apartel_group.name\n WHEN (building.id IS NOT NULL) THEN building.name\n WHEN (apartment.id IS NOT NULL) THEN apartment.name\n WHEN (office.id IS NOT NULL) THEN office.name\n WHEN (parking.id IS NOT NULL) THEN parking.name\n WHEN (building_section.id IS NOT NULL) THEN CONCAT(building_for_section.name, ' ', building_section.name)\n END\n "));
$sortColumns = ['id'];
$result = $this->fetchAll(function (Select $select) use($columns, $sortColumns, $filterParams) {
$select->columns($columns);
$select->join(['apartment' => DbTables::TBL_APARTMENTS], new Expression($this->getTable() . '.entity_id = apartment.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$APARTMENT_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['apartel' => DbTables::TBL_APARTELS], new Expression($this->getTable() . '.entity_id = apartel.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$APARTEL_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['apartel_group' => DbTables::TBL_APARTMENT_GROUPS], 'apartel.apartment_group_id = apartel_group.id', [], $select::JOIN_LEFT)->join(['building' => DbTables::TBL_APARTMENT_GROUPS], new Expression($this->getTable() . '.entity_id = building.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$BUILDING_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['building_section' => DbTables::TBL_BUILDING_SECTIONS], new Expression($this->getTable() . '.entity_id = building_section.id AND ' . $this->getTable() . '.entity_type =' . Translation::PRODUCT_TEXTLINE_TYPE_BUILDING_SECTION_APARTMENT_ENTRY), [], $select::JOIN_LEFT)->join(['building_for_section' => DbTables::TBL_APARTMENT_GROUPS], 'building_section.building_id = building_for_section.id', [], $select::JOIN_LEFT)->join(['office' => DbTables::TBL_OFFICES], new Expression($this->getTable() . '.entity_id = office.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$OFFICE_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT)->join(['parking' => DbTables::TBL_PARKING_LOTS], new Expression($this->getTable() . '.entity_id = parking.id AND ' . $this->getTable() . '.entity_type IN (' . implode(', ', Translation::$PARKING_TEXTLINE_TYPES) . ')'), [], $select::JOIN_LEFT);
$where = new Where();
$textSearch = '%' . strip_tags(trim($filterParams["srch_txt"])) . '%';
if ((int) $filterParams["id_translation"] > 0) {
$where->equalTo($this->getTable() . '.entity_id', $filterParams["id_translation"]);
} else {
if ($filterParams["srch_txt"] != '') {
$where->NEST->like('apartment.name', $textSearch)->or->NEST->like('apartel_group.name', $textSearch)->or->NEST->like('building.name', $textSearch)->or->NEST->like('office.name', $textSearch)->or->like('parking.name', $textSearch)->or->like($this->getTable() . '.en_html_clean', $textSearch)->UNNEST;
}
}
if ((int) $filterParams['product_type']) {
$where->equalTo($this->getTable() . '.type', (int) $filterParams['product_type']);
}
$select->where($where);
$select->group($this->getTable() . '.id');
$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 removeSerie($userId, $serieId)
{
$where = new Where();
$where->equalTo('user_id', $userId);
$where->equalTo('sid', $serieId);
return parent::delete($where, $this->tableName);
}
public function getChatMesByGetter($getter)
{
$where = new Where();
$where->equalTo('getter', $getter);
$where->equalTo('isread', 1);
$rowset = $this->tableGateway->select($where);
return iterator_to_array($rowset);
}
/**
* @param int $moduleId
* @param int $identityId
* @param int $actionId
*
* @return \ArrayObject|\ArrayObject[]
*/
public function getByTicket($moduleId, $identityId, $actionId = null)
{
return $this->fetchAll(function (Select $select) use($moduleId, $identityId, $actionId) {
$where = new Where();
$where->equalTo($this->getTable() . '.module_id', $moduleId)->equalTo($this->getTable() . '.identity_id', $identityId);
if (!is_null($actionId)) {
$where->equalTo($this->getTable() . '.action_id', $actionId);
}
$select->join(['users' => DbTables::TBL_BACKOFFICE_USERS], $this->getTable() . '.user_id = users.id', ['user_name' => new Expression('CONCAT(firstname, " ", lastname)'), 'is_system' => 'system'], Select::JOIN_LEFT)->where($where)->order([$this->getTable() . '.timestamp DESC', $this->getTable() . '.id DESC']);
});
}
public function getPolaFormularza($iIdForm)
{
$iIdForm = intval($iIdForm);
$where = new Where();
$where->equalTo('id_formularza', $iIdForm);
$where->equalTo('status', 1);
$rowset = $this->select(function (Select $select) use($where) {
$select->where($where);
$select->order('pozycjonowanie ASC');
});
return $rowset->toArray();
}
public function findCourses($free, $disability, $child_care, $lat, $lng)
{
$sql = new Sql($this->tableGateway->getAdapter());
$select = $sql->select();
$select->from(array("co" => "course"))->join(array("cc" => "course_centre"), "co.id = cc.course_id")->join(array("ce" => "centre"), "ce.id = cc.centre_id", array("centre_id" => "id", "centreName" => "name", "location" => new Expression("AsWKT(location)"), "post_code", "address", "buses", "tube", "accebility", "accebility_condition", "other_information"));
//especificar las columnas que queremos en el resultados de las consultas.
$select->columns(array("id", "courseName" => "name", "class_type", "levels", "who_join", "how_join", "when_join", "how_long", "cost_free", "cost_condition", "times", "documentation_required", "contact_phone", "contact_email", "contact_person", "child_care", "child_condition", "organization_id", "other_information"));
$where = new Where();
//TODO terminar de poner prefijos a las tablas
if (isset($free)) {
if ($free == 'Yes') {
$where->in("co.cost_free", array("y", "c"));
} else {
if ($free == 'No') {
$where->equalTo("co.cost_free", "n");
}
}
}
if (isset($disability)) {
if ($disability == 'Yes') {
$where->in("ce.accebility", array("y", "c"));
} else {
if ($disability == 'No') {
$where->equalTo("ce.accebility", "n");
}
}
}
if (isset($child_care)) {
if ($child_care == 'Yes') {
$where->in("co.child_care", array("y", "c"));
} else {
if ($child_care == 'No') {
$where->equalTo("co.child_care", "n");
}
}
}
//hacemos busquedas en 3km a la redonda, se puede convertir en un parametro
$R = 6371;
//radio de la tierra en km
if (isset($lat) && isset($lng)) {
$maxLat = $lat + rad2deg(1.5 / $R);
$minLat = $lat - rad2deg(1.5 / $R);
$maxLng = $lng + rad2deg(1.5 / $R / cos(deg2rad($lat)));
$minLng = $lng - rad2deg(1.5 / $R / cos(deg2rad($lat)));
$where->between(new Expression("X(ce.location)"), $minLat, $maxLat);
$where->between(new Expression("y(ce.location)"), $minLng, $maxLng);
}
//TODO terminar con el resto de los filtros de busqueda
$select->where($where);
$statement = $sql->prepareStatementForSqlObject($select);
$rowset = $statement->execute();
return $rowset;
}
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']];
}
public function fillInventory($userId, $startDate, $officeId)
{
/** @var \DDD\Dao\User\Schedule\Inventory $inventoryDao */
$inventoryDao = $this->getServiceLocator()->get('dao_user_schedule_inventory');
$deleteWhere = new Where();
$deleteWhere->equalTo('user_id', $userId)->greaterThanOrEqualTo('date', date('Y-m-d', strtotime($startDate)))->notEqualTo('is_changed', 1);
$inventoryDao->delete($deleteWhere);
$scheduleData = $this->getUserSchedule($userId);
$scheduleArr = [];
if ($scheduleData) {
foreach ($scheduleData as $daySchedule) {
$scheduleArr[$daySchedule->getDay()] = $daySchedule;
}
}
// Fill up to 3 months ahead
$endDate = date('Y-m-d', strtotime('+92 days'));
$dateIterator = date('Y-m-d', strtotime($startDate));
$insertArray = [];
while ($dateIterator < $endDate) {
/** @var \DDD\Domain\User\Schedule\Schedule $daySchedule */
if (count($scheduleArr)) {
for ($scheduleIndex = 1; $scheduleIndex <= count($scheduleArr); $scheduleIndex++) {
$daySchedule = $scheduleArr[$scheduleIndex];
array_push($insertArray, ['user_id' => $userId, 'office_id' => $officeId, 'date' => $dateIterator, 'availability' => $daySchedule->isActive(), 'time_from1' => $daySchedule->isActive() ? $daySchedule->getTimeFrom1() : '', 'time_to1' => $daySchedule->isActive() ? $daySchedule->getTimeTo1() : '', 'time_from2' => $daySchedule->isActive() ? $daySchedule->getTimeFrom2() : '', 'time_to2' => $daySchedule->isActive() ? $daySchedule->getTimeTo2() : '']);
$dateIterator = date('Y-m-d', strtotime($dateIterator . '+1 days'));
}
// The case when user has no schedule scheme. Fill the inventory with 0 availability
} else {
array_push($insertArray, ['user_id' => $userId, 'office_id' => $officeId, 'date' => $dateIterator, 'availability' => 0, 'time_from1' => '', 'time_to1' => '', 'time_from2' => '', 'time_to2' => '']);
$dateIterator = date('Y-m-d', strtotime($dateIterator . '+1 days'));
}
}
$inventoryDao->multiInsert($insertArray, true);
}
public function isUnitUniqueInLot($params)
{
$prototype = $this->resultSetPrototype->getArrayObjectPrototype();
$this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
$result = $this->fetchOne(function (Select $select) use($params) {
$where = new Where();
if ($params['id']) {
$where->notEqualTo($this->getTable() . '.id', $params['id']);
}
$where->equalTo($this->getTable() . '.unit', $params['unit']);
$where->equalTo($this->getTable() . '.lot_id', $params['lot_id']);
$select->columns(['id'])->where($where);
});
$this->resultSetPrototype->setArrayObjectPrototype($prototype);
return $result;
}
/**
* @param array $typeList
* @param bool|true $onlyActive
* @return \DDD\Domain\Warehouse\Category\Category[]
*/
public function getCategoriesByTypeList($typeList = [], $onlyActive = true, $selectedId = 0, $returnArray = false)
{
$entity = $this->getEntity();
$this->setEntity(new \DDD\Domain\Warehouse\Category\Category());
if ($returnArray) {
$this->setEntity(new \ArrayObject());
}
$result = $this->fetchAll(function (Select $select) use($typeList, $onlyActive, $selectedId) {
$select->columns(['id', 'name', 'type' => 'type_id', 'inactive']);
$nestedWhere = new Where();
$where = new Where();
if (!empty($typeList)) {
$nestedWhere->in('type_id', $typeList);
}
if ($onlyActive) {
$nestedWhere->equalTo('inactive', AssetsCategoryService::CATEGORY_STATUS_ACTIVE);
}
if ($selectedId) {
$where->equalTo('id', $selectedId)->orPredicate($nestedWhere);
} else {
$where = $nestedWhere;
}
$select->where($where)->order('type_id');
});
$this->setEntity($entity);
return $result;
}
public function getBlackList($data)
{
$result = $this->fetchAll(function (Select $select) use($data) {
$select->join(['reservation' => DbTables::TBL_BOOKINGS], $this->getTable() . '.reservation_id = reservation.id', ['res_number'], $select::JOIN_LEFT);
$where = new Where();
$where->equalTo('hash', '');
if (!empty($data['email'])) {
$where->or->equalTo('hash', $data['email']);
}
if (!empty($data['fullNamePhone'])) {
$where->or->equalTo('hash', $data['fullNamePhone']);
}
if (!empty($data['fullNameAddress'])) {
$where->or->equalTo('hash', $data['fullNameAddress']);
}
if (!empty($data['fullName'])) {
$where->or->equalTo('hash', $data['fullName']);
}
if (!empty($data['phone'])) {
$where->or->equalTo('hash', $data['phone']);
}
$select->where($where);
});
return $result;
}
public function userLogin($email, $pass)
{
$sql = new Sql($this->getAdaptor());
$select = $sql->select('user');
$criteria = new Where();
$criteria->equalTo('password', md5($pass));
$criteria->equalTo('email', $email);
$criteria->OR->equalTo('username', $email);
$select->where($criteria);
$statment = $sql->prepareStatementForSqlObject($select);
$result = $statment->execute();
if ($result instanceof ResultInterface && $result->isQueryResult() && $result->getAffectedRows()) {
return array('isValidUser' => true, 'user' => $result->current());
}
throw new \InvalidArgumentException("Member with given email :{$email} not found.");
}
public function fetchUserTimeline($user)
{
$select = $this->getSql()->select();
$where = new Where();
$where->equalTo('username', $user);
$select->where($where)->order('timestamp DESC');
return new Paginator(new DbSelect($select, $this->adapter, $this->resultSetPrototype));
}
/**
* @param $transactionId
* @return array|\ArrayObject|null
*/
public function getTransferByTransaction($transactionId)
{
return $this->fetchOne(function (Select $select) use($transactionId) {
$where = new Where();
$where->equalTo('money_transaction_id_1', $transactionId)->or->equalTo('money_transaction_id_2', $transactionId);
$select->where($where)->columns(['id', 'money_transaction_id_1', 'money_transaction_id_2', 'amount_from', 'amount_to'])->join(['ta1' => DbTables::TBL_TRANSACTION_ACCOUNTS], new Expression($this->getTable() . '.account_id_from = ta1.id AND ta1.type = ' . Account::TYPE_MONEY_ACCOUNT), [], Select::JOIN_LEFT)->join(['ma1' => DbTables::TBL_MONEY_ACCOUNT], 'ta1.holder_id = ma1.id', ['money_account_id_from' => 'id', 'account_currency_from' => 'currency_id'], Select::JOIN_LEFT)->join(['ta2' => DbTables::TBL_TRANSACTION_ACCOUNTS], new Expression($this->getTable() . '.account_id_to = ta2.id AND ta2.type = ' . Account::TYPE_MONEY_ACCOUNT), [], Select::JOIN_LEFT)->join(['ma2' => DbTables::TBL_MONEY_ACCOUNT], 'ta2.holder_id = ma2.id', ['money_account_id_to' => 'id', 'account_currency_to' => 'currency_id'], Select::JOIN_LEFT);
});
}
/**
*
* @param string $search
* @return mixed
*/
public function getDocumento($search = '')
{
$select = $this->sql->select()->from(array('t1' => 'bdc_documento'))->join(array('t2' => 'bdc_tipo_documento'), 't1.tipd_id=t2.tipd_id', array('*'));
$where = new Where();
$where->equalTo('doc_numero', $search);
$select->where($where);
return $this->fetchRow($select);
}
public function getSkuIdByName($sku)
{
return $this->fetchOne(function (Select $select) use($sku) {
$where = new Where();
$where->equalTo('name', $sku);
$select->columns(['id', 'asset_category_id'])->where($where);
});
}
public function findByContactId($id)
{
$sql = new Select();
$sql->from(array('cc' => 'contact_companies'))->join(array('c' => 'contact_company'), 'c.company_id = cc.company_id');
$where = new Where();
$where->equalTo('cc.contact_id', $id);
return $this->select($sql->where($where));
}
/**
* @param $taskId
* @return \DDD\Domain\Task\Staff[]
*/
public function getTaskStaff($taskId)
{
return $this->fetchAll(function (Select $select) use($taskId) {
$where = new Where();
$where->equalTo('task_id', $taskId)->notEqualTo('type', TaskService::STAFF_CREATOR);
$select->join(['users' => DbTables::TBL_BACKOFFICE_USERS], $this->getTable() . '.user_id = users.id', ['id', 'name' => new Expression("CONCAT(users.firstname, ' ', users.lastname)"), 'avatar'])->where($where)->order('type', 'ASC');
});
}
/**
* @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 getApartmentDates($apartmentId)
{
return $this->fetchOne(function (Select $select) use($apartmentId) {
$columns = ['create_date', 'disable_date', 'edit_date'];
$where = new Where();
$where->equalTo($this->table . '.id', $apartmentId);
$select->where($where)->columns($columns);
});
}
public function deleteRemovedPostAddresses($owner, array $arrId)
{
$where = new Where();
$where->equalTo('PA_Owner', $owner);
if (!empty($arrId)) {
$where->notIn('PA_Id', $arrId);
}
$this->delete($where);
}
public function deleteRemovedRows($owner, array $arrId)
{
$where = new Where();
$where->equalTo('IR_InvoiceId', $owner);
if (!empty($arrId)) {
$where->notIn('IR_Id', $arrId);
}
$this->delete($where);
}
public function deleteRemovedPhoneNumbers($owner, array $arrId)
{
$where = new Where();
$where->equalTo('PN_Owner', $owner);
if (!empty($arrId)) {
$where->notIn('PN_Id', $arrId);
}
$this->delete($where);
}
/**
* @param $currency
* @return array|\ArrayObject|null
*/
public function getLastCurrency($currency)
{
$results = $this->select(function (Select $select) use($currency) {
$where = new Where();
$where->equalTo('code', $currency)->AND->equalTo('status', 'A');
$select->where($where)->order('entryDate DESC')->limit(1);
});
return $results->current();
}
/**
* @param array $accList
* @param string $date
*
* @return \DDD\Domain\ApartmentGroup\ConciergeView[]|\ArrayObject
*/
public function getCheckoutByDay($accList, $date)
{
$columns = array('id', 'ki_page_hash', 'guest_first_name', 'guest_last_name', 'res_number', 'pax' => 'man_count', 'occupancy', 'date_to', 'guest_email', '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', 'provide_cc_page_status');
return $this->fetchAll(function (Select $select) use($accList, $date, $columns) {
$where = new Where();
$where->equalTo($this->getTable() . '.date_to', $date)->equalTo($this->getTable() . '.status', Booking::BOOKING_STATUS_BOOKED)->notEqualTo($this->getTable() . '.arrival_status', ReservationTicketService::BOOKING_ARRIVAL_STATUS_NO_SHOW)->notEqualTo($this->getTable() . '.overbooking_status', BookingTicket::OVERBOOKING_STATUS_OVERBOOKED)->in($this->getTable() . '.apartment_id_assigned', $accList);
$select->columns($columns)->join(['apartment' => DbTables::TBL_APARTMENTS], $this->getTable() . '.apartment_id_assigned = apartment.id', ['unitNumber' => 'unit_number', 'acc_name' => 'name'], Select::JOIN_LEFT)->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)->where($where)->order([$this->getTable() . '.arrival_status ASC', $this->getTable() . '.guest_last_name ASC'])->group($this->getTable() . '.id');
});
}
public function getVideos($apartmentId)
{
$result = $this->fetchOne(function (Select $select) use($apartmentId) {
$select->columns(['id', 'apartment_id', 'video', 'key_entry_video']);
$where = new Where();
$where->equalTo($this->table . '.apartment_id', $apartmentId);
$select->where($where);
});
return $result;
}
public function getListingById($id = 1)
{
$select = new Select();
$select->from($this->tableName);
$where = new Where();
$where->equalTo('listings_id', $id);
$select->where($where);
$select->limit(1);
return $this->selectWith($select)->current();
}
public function updateCronLastRunDate($date)
{
if ($this->validateDate($date)) {
$cronVacationsLastRunDate = $this->getCronLastRunDate();
$where = new Where();
$where->equalTo('id', $cronVacationsLastRunDate['id']);
return $this->save(['cron_vacation_last_run_date' => $date], $where);
}
return FALSE;
}
/**
* @param array $params
* @return array|\ArrayObject|null
*/
public function getSalarySchemesByParams($params = [])
{
$result = $this->fetchAll(function (Select $select) use($params) {
$where = new Where();
if (isset($params['status']) && $params['status'] > 0) {
$where->equalTo($this->getTable() . '.status', $params['status']);
} else {
$where->notEqualTo($this->getTable() . '.status', \DDD\Service\User\SalaryScheme::SALARY_SCHEME_STATUS_ARCHIVED);
}
if (!empty($params['sort'])) {
foreach ($params['sort'] as $col => $dir) {
$select->order($col . ' ' . $dir);
}
}
$where->equalTo('transactionAccount.id', $params['transactionAccountId']);
$select->join(['externalAccount' => DbTables::TBL_EXTERNAL_ACCOUNT], $this->getTable() . '.external_account_id = externalAccount.id', [])->join(['transactionAccount' => DbTables::TBL_TRANSACTION_ACCOUNTS], 'externalAccount.transaction_account_id = transactionAccount.id', [])->where($where);
});
return $result;
}
public function deleteCountryWithSearch($search)
{
$where = new Where();
$where->equalTo('code', $search);
$where->OR->equalTo('alpha2', $search);
$where->OR->equalTo('alpha3', $search);
$rowset = $this->tableGateway->delete(function ($delete) use($where) {
$delete->where($where);
});
}
