public function getRowBySerialNumber($serialNumber, $assetId)
{
return $this->fetchOne(function (Select $select) use($serialNumber, $assetId) {
$where = new Where();
$where->equalTo('serial_number', $serialNumber);
if (FALSE !== $assetId) {
$where->notEqualTo('id', $assetId);
}
$select->columns(['id'])->where($where);
});
}
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;
}
public function getRowByCategoryLocationIdLocationEntity($categoryId, $locationEntityType, $locationEntityId, $assetId, $getInfo = false)
{
return $this->fetchOne(function (Select $select) use($categoryId, $locationEntityType, $locationEntityId, $assetId, $getInfo) {
$where = new Where();
$where->equalTo('category_id', $categoryId);
$where->equalTo('location_entity_type', $locationEntityType);
$where->equalTo('location_entity_id', $locationEntityId);
if ($assetId) {
$where->notEqualTo($this->getTable() . '.id', $assetId);
}
if (!$getInfo) {
$select->columns(['id']);
}
$select->where($where);
});
}
/**
* @param bool $status
*
* @return \DDD\Domain\Booking\Statuses[]|\ArrayObject
*/
public function getAllList($status = false)
{
$result = $this->fetchAll(function (Select $select) use($status) {
$where = new Where();
$where->equalTo('visible', 1);
if ($status) {
// If is canceled can't be changed to booked
if ($status != 1) {
$where->notEqualTo('id', 1);
}
// Smart select (active ones + selected one)
$where->or->equalTo('id', $status);
}
$select->where($where);
$select->columns(array('id', 'name', 'visible'))->order('id');
});
return $result->buffer();
}
/**
* @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;
}
/**
* @param $usage
* @param bool $deactivatedIncluded
* @return \DDD\Domain\ApartmentGroup\ForSelect[]
* @throws \Exception
*/
public function getApartmentGroupsByUsage($usage, $deactivatedIncluded = false, $hasNotDevAccess)
{
/**
* @var ApartmentGroupDAO $apartmentGroupDao
*/
$apartmentGroupDao = $this->getServiceLocator()->get('dao_apartment_group_apartment_group');
switch ($usage) {
case self::APARTMENT_GROUP_USAGE_APARTEL:
$usageField = 'usage_apartel';
break;
case self::APARTMENT_GROUP_USAGE_BUILDING:
$usageField = 'usage_building';
break;
case self::APARTMENT_GROUP_USAGE_CONCIERGE:
$usageField = 'usage_concierge_dashboard';
break;
case self::APARTMENT_GROUP_USAGE_COST_CENTER:
$usageField = 'usage_cost_center';
break;
case self::APARTMENT_GROUP_USAGE_PERFORMANCE:
$usageField = 'usage_performance_group';
break;
default:
throw new \Exception('Wrong apartment group usage passed');
}
$where = new Where();
$where->equalTo($usageField, 1);
if (!$deactivatedIncluded) {
$where->equalTo('active', 1);
}
if ($hasNotDevAccess) {
$where->notEqualTo('id', 1);
}
$apartmentGroupDao->getResultSetPrototype()->setArrayObjectPrototype(new ForSelect());
/**
* @var ForSelect[] $apartmentGroups
*/
$apartmentGroups = $apartmentGroupDao->fetchAll($where, ['id', 'name', 'usage_apartel']);
return $apartmentGroups;
}
/**
* Get the list of employees working with the given Employee ID
*
* @param $id Employee ID
* */
public function getList($id = false)
{
$users_gateway = Users::factory($this->getServiceLocator());
$employee_id = $this->params()->fromRoute('user_id');
$table = $users_gateway->getTable();
// select all users who works during the same time period
$result = $users_gateway->select(function ($select) use($employee_id, $id, $table) {
// the shifts other employees work
$select->join(array('S' => 'shifts'), "{$table}.id = S.employee_id", array(), Select::JOIN_INNER);
// the shifts the current employee work
$select->join(array('S2' => 'shifts'), new Expression("(S.start_time >= S2.start_time\n\t\t\t\t\t\t\t\t\t\t\tAND S.start_time < S2.end_time)\n\t\t\t\t\t\t\t\t\t\tOR (S.end_time >= S2.start_time\n\t\t\t\t\t\t\t\t\t\t\tAND S.end_time < S2.end_time)"), array(), Select::JOIN_INNER);
$where = new Where();
// we do not want to know about our own shifts
$where->notEqualTo("S.employee_id", $employee_id);
// we want to use only our shifts as a comparison
$where->equalTo('S2.employee_id', $employee_id);
if ($id !== false) {
$where->equalTo("{$table}.id", $id);
}
$select->where($where);
});
return $result;
}
/**
*
* @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 DatagridFilter $filter
* @throws \Exception
*/
public function applyFilter(DatagridFilter $filter)
{
$select = $this->getSelect();
$adapter = $this->getSql()->getAdapter();
$qi = function ($name) use($adapter) {
return $adapter->getPlatform()->quoteIdentifier($name);
};
$col = $filter->getColumn();
if (!$col instanceof Column\Select) {
throw new \Exception('This column cannot be filtered: ' . $col->getUniqueId());
}
$colString = $col->getSelectPart1();
if ($col->getSelectPart2() != '') {
$colString .= '.' . $col->getSelectPart2();
}
if ($col instanceof Column\Select && $col->hasFilterSelectExpression()) {
$colString = sprintf($col->getFilterSelectExpression(), $colString);
}
$values = $filter->getValues();
$wheres = [];
foreach ($values as $value) {
$where = new Where();
switch ($filter->getOperator()) {
case DatagridFilter::LIKE:
$wheres[] = $where->like($colString, '%' . $value . '%');
break;
case DatagridFilter::LIKE_LEFT:
$wheres[] = $where->like($colString, '%' . $value);
break;
case DatagridFilter::LIKE_RIGHT:
$wheres[] = $where->like($colString, $value . '%');
break;
case DatagridFilter::NOT_LIKE:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value . '%']);
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', ['%' . $value]);
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', [$value . '%']);
break;
case DatagridFilter::EQUAL:
$wheres[] = $where->equalTo($colString, $value);
break;
case DatagridFilter::NOT_EQUAL:
$wheres[] = $where->notEqualTo($colString, $value);
break;
case DatagridFilter::GREATER_EQUAL:
$wheres[] = $where->greaterThanOrEqualTo($colString, $value);
break;
case DatagridFilter::GREATER:
$wheres[] = $where->greaterThan($colString, $value);
break;
case DatagridFilter::LESS_EQUAL:
$wheres[] = $where->lessThanOrEqualTo($colString, $value);
break;
case DatagridFilter::LESS:
$wheres[] = $where->lessThan($colString, $value);
break;
case DatagridFilter::BETWEEN:
$wheres[] = $where->between($colString, $values[0], $values[1]);
break 2;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
if (!empty($wheres)) {
$set = new PredicateSet($wheres, PredicateSet::OP_OR);
$select->where->andPredicate($set);
}
}
/**
* @return int
*
* @author Tigran Petrosyan
*/
public function getNotChargedApartelReservationsCount()
{
$this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
$result = $this->fetchOne(function (Select $select) {
$select->join(['apartment_groups' => DbTables::TBL_APARTMENT_GROUPS], $this->getTable() . '.apartel_id = apartment_groups.id', []);
$select->join(['charges' => DbTables::TBL_CHARGE], new Expression($this->getTable() . '.id = charges.reservation_id AND charges.status=0'), [], Select::JOIN_LEFT);
$where = new Where();
$where->greaterThan($this->getTable() . '.apartel_id', 0);
$where->equalTo($this->getTable() . '.status', \DDD\Service\Booking::BOOKING_STATUS_BOOKED);
$where->isNull('charges.id');
$where->notEqualTo($this->getTable() . '.check_charged', 1);
$where->expression($this->getTable() . '.apartment_id_assigned NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
$where->greaterThanOrEqualTo($this->getTable() . '.date_from', date('Y-m-d'));
$where->expression('(' . $this->getTable() . '.is_refundable = 2 or ' . $this->getTable() . '.refundable_before_hours >= TIMESTAMPDIFF(HOUR,NOW(),date_from))', []);
$select->columns(['count' => new Expression('COUNT(*)')]);
$select->where($where);
});
return $result['count'];
}
private function prepareTransactionStatement($data)
{
$statement = new Where();
// Basic configuration
$types = [];
foreach ($data['transaction_type'] as $type => $typeValue) {
if ((int) $typeValue == 1) {
array_push($types, $type);
}
}
if (count($types)) {
$statement->in('type', $types);
}
// Transaction specific
if (!empty($data['transaction_date'])) {
list($dateFrom, $dateTo) = explode(' - ', $data['transaction_date']);
$statement->between(DbTables::TBL_CHARGE_TRANSACTION . '.date', $dateFrom, date('Y-m-d', strtotime('+1 day', strtotime($dateTo))));
}
// Reservation specific
if ($data['status']) {
if ($data['status'] == 111) {
// Canceled
$statement->notEqualTo(DbTables::TBL_BOOKINGS . '.status', Booking::BOOKING_STATUS_BOOKED);
} else {
$statement->equalTo(DbTables::TBL_BOOKINGS . '.status', $data['status']);
}
}
if ($data['payment_model']) {
$statement->equalTo(DbTables::TBL_BOOKINGS . '.model', $data['payment_model']);
}
if ($data['partner_id']) {
$statement->equalTo(DbTables::TBL_BOOKINGS . '.partner_id', $data['partner_id']);
}
if ($data['no_collection'] != -1) {
$statement->equalTo(DbTables::TBL_BOOKINGS . '.no_collection', $data['no_collection']);
}
if (!empty($data['booking_date'])) {
list($dateFrom, $dateTo) = explode(' - ', $data['booking_date']);
$statement->between(DbTables::TBL_BOOKINGS . '.timestamp', $dateFrom, date('Y-m-d', strtotime('+1 day', strtotime($dateTo))));
}
if (!empty($data['arrival_date'])) {
list($dateFrom, $dateTo) = explode(' - ', $data['arrival_date']);
$statement->between(DbTables::TBL_BOOKINGS . '.date_from', $dateFrom, $dateTo);
}
if (!empty($data['departure_date'])) {
list($dateFrom, $dateTo) = explode(' - ', $data['departure_date']);
$statement->between(DbTables::TBL_BOOKINGS . '.date_to', $dateFrom, $dateTo);
}
if (!empty($data['product_id'])) {
$statement->equalTo(DbTables::TBL_BOOKINGS . '.apartment_id_origin', $data['product_id']);
}
if (!empty($data['assigned_product_id'])) {
$statement->equalTo(DbTables::TBL_BOOKINGS . '.apartment_id_assigned', $data['assigned_product_id']);
}
if ($data['city']) {
$statement->equalTo(DbTables::TBL_BOOKINGS . '.acc_city_id', $data['city']);
}
if ($data['psp']) {
$statement->equalTo(DbTables::TBL_CHARGE_TRANSACTION . '.psp_id', $data['psp']);
}
// Apartment Group specific
if ($data['group']) {
$statement->equalTo(DbTables::TBL_APARTMENT_GROUP_ITEMS . '.apartment_group_id', $data['group']);
}
// Transaction Status
if ($data['transaction_status']) {
$statement->equalTo(DbTables::TBL_CHARGE_TRANSACTION . '.status', $data['transaction_status']);
}
return $statement;
}
/**
* @param DatagridFilter $filter
* @throws \InvalidArgumentException
*/
public function applyFilter(DatagridFilter $filter)
{
$select = $this->getSelect();
$adapter = $this->getSql()->getAdapter();
$qi = function ($name) use($adapter) {
return $adapter->getPlatform()->quoteIdentifier($name);
};
$column = $filter->getColumn();
$colString = $column->getSelectPart1();
if ($column->getSelectPart2() != '') {
$colString .= '.' . $column->getSelectPart2();
}
if ($column instanceof Column\Select && $column->hasFilterSelectExpression()) {
$colString = sprintf($column->getFilterSelectExpression(), $colString);
}
$values = $filter->getValues();
$filterSelectOptions = $column->getFilterSelectOptions();
$wheres = array();
if ($filter->getColumn()->getType() instanceof Column\Type\DateTime && $filter->getColumn()->getType()->isDaterangePickerEnabled() === true) {
$where = new Where();
$wheres[] = $where->between($colString, $values[0], $values[1]);
if (count($wheres) > 0) {
$set = new PredicateSet($wheres, PredicateSet::OP_AND);
$select->where->andPredicate($set);
}
} else {
foreach ($values as $value) {
$where = new Where();
switch ($filter->getOperator()) {
case DatagridFilter::LIKE:
$wheres[] = $where->like($colString, '%' . $value . '%');
break;
case DatagridFilter::LIKE_LEFT:
$wheres[] = $where->like($colString, '%' . $value);
break;
case DatagridFilter::LIKE_RIGHT:
$wheres[] = $where->like($colString, $value . '%');
break;
case DatagridFilter::NOT_LIKE:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value . '%'));
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value));
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = $where->literal($qi($colString) . 'NOT LIKE ?', array($value . '%'));
break;
case DatagridFilter::EQUAL:
$wheres[] = $where->equalTo($colString, $value);
break;
case DatagridFilter::NOT_EQUAL:
$wheres[] = $where->notEqualTo($colString, $value);
break;
case DatagridFilter::GREATER_EQUAL:
$wheres[] = $where->greaterThanOrEqualTo($colString, $value);
break;
case DatagridFilter::GREATER:
$wheres[] = $where->greaterThan($colString, $value);
break;
case DatagridFilter::LESS_EQUAL:
$wheres[] = $where->lessThanOrEqualTo($colString, $value);
break;
case DatagridFilter::LESS:
$wheres[] = $where->lessThan($colString, $value);
break;
case DatagridFilter::BETWEEN:
$wheres[] = $where->between($colString, $values[0], $values[1]);
break;
case DatagridFilter::IN:
$wheres[] = $where->in($colString, (array) $value);
break;
case DatagridFilter::NOT_IN:
$wheres[] = $where->notin($colString, (array) $value);
break;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
if (count($wheres) > 0) {
$set = new PredicateSet($wheres, PredicateSet::OP_OR);
$select->where->andPredicate($set);
}
}
}
public function constructWhereFromFilterParams($filterParams, $download = false)
{
/* @var $auth \Library\Authentication\BackofficeAuthenticationService */
$auth = $this->getServiceLocator()->get('library_backoffice_auth');
$where = new Where();
$table = DbTables::TBL_BOOKINGS;
if (isset($filterParams["res_number"]) && $filterParams["res_number"] != '') {
$where->like($table . '.res_number', '%' . $filterParams["res_number"] . '%');
}
if (isset($filterParams["rooms_count"])) {
$apartmentTableName = $download ? DbTables::TBL_APARTMENTS : 'apartments';
$roomsCount = $filterParams["rooms_count"] - 1;
if ($roomsCount >= 0) {
if ($roomsCount == 2) {
$where->greaterThanOrEqualTo($apartmentTableName . '.bedroom_count', $roomsCount);
} else {
$where->equalTo($apartmentTableName . '.bedroom_count', $roomsCount);
}
}
}
if (isset($filterParams["status"]) && $filterParams["status"]) {
if ($filterParams["status"] == Constants::NOT_BOOKED_STATUS) {
$where->notEqualTo($table . '.status', Booking::BOOKING_STATUS_BOOKED);
} else {
$where->equalTo($table . '.status', $filterParams["status"]);
}
}
if (isset($filterParams["arrival_status"]) && $filterParams['arrival_status'] != -1) {
$where->equalTo($table . '.arrival_status', $filterParams["arrival_status"]);
}
if (isset($filterParams["overbooking_status"]) && $filterParams["overbooking_status"] != -1) {
$where->equalTo($table . '.overbooking_status', $filterParams["overbooking_status"]);
}
if (isset($filterParams["apartel_id"]) && $filterParams["apartel_id"] != -2) {
$where->equalTo($table . '.apartel_id', $filterParams["apartel_id"]);
}
if (isset($filterParams["product_id"]) && isset($filterParams["product"]) && $filterParams["product_id"] != '' && $filterParams["product"] != '') {
$where->equalTo($table . '.apartment_id_origin', $filterParams["product_id"]);
}
if (isset($filterParams["assigned_product_id"]) && isset($filterParams["assigned_product"]) && $filterParams["assigned_product_id"] != '' && $filterParams["assigned_product"] != '') {
$where->equalTo($table . '.apartment_id_assigned', $filterParams["assigned_product_id"]);
}
if (isset($filterParams["booking_date"]) && $filterParams["booking_date"] != '') {
$dates = explode(' - ', $filterParams["booking_date"]);
$startDate = $dates[0];
$endDate = $dates[1];
$where->expression('DATE(' . $table . '.timestamp) >= \'' . $startDate . '\'', []);
$where->expression('DATE(' . $table . '.timestamp) <= \'' . $endDate . '\'', []);
}
if (isset($filterParams["arrival_date"]) && $filterParams["arrival_date"] != '') {
$dates = explode(' - ', $filterParams["arrival_date"]);
$startDate = $dates[0];
$endDate = $dates[1];
$where->lessThanOrEqualTo($table . ".date_from", $endDate);
$where->greaterThanOrEqualTo($table . ".date_from", $startDate);
}
if (isset($filterParams["departure_date"]) && $filterParams["departure_date"] != '') {
$dates = explode(' - ', $filterParams["departure_date"]);
$startDate = $dates[0];
$endDate = $dates[1];
$where->lessThanOrEqualTo($table . ".date_to", $endDate);
$where->greaterThanOrEqualTo($table . ".date_to", $startDate);
}
if (isset($filterParams["guest_first_name"]) && $filterParams["guest_first_name"] != '') {
$where->like($table . '.guest_first_name', '%' . $filterParams["guest_first_name"] . '%');
}
if (isset($filterParams["guest_last_name"]) && $filterParams["guest_last_name"] != '') {
$where->like($table . '.guest_last_name', '%' . $filterParams["guest_last_name"] . '%');
}
if (isset($filterParams["guest_phone"]) && $filterParams["guest_phone"] != '') {
$nestedWhere = new Predicate();
$nestedWhere->like($table . '.guest_phone', '%' . $filterParams["guest_phone"] . '%');
$nestedWhere->OR;
$nestedWhere->like($table . '.guest_travel_phone', '%' . $filterParams["guest_phone"] . '%');
$where->addPredicate($nestedWhere);
}
if (isset($filterParams["guest_email"]) && $filterParams["guest_email"] != '') {
$where->like($table . '.guest_email', '%' . $filterParams["guest_email"] . '%');
}
if (isset($filterParams["guest_secondary_email"]) && $filterParams["guest_secondary_email"] != '') {
$where->like($table . '.secondary_email', '%' . $filterParams["guest_secondary_email"] . '%');
}
if (isset($filterParams["guest_country_id"]) && $filterParams["guest_country_id"] != '' && $filterParams["guest_country"] != '') {
$where->equalTo($table . '.guest_country_id', $filterParams["guest_country_id"]);
}
if (isset($filterParams["apt_location_id"]) && $filterParams["apt_location_id"] != '') {
$nestedWhere = new Predicate();
$nestedWhere->equalTo($table . '.acc_country_id', $filterParams["apt_location_id"]);
$nestedWhere->OR;
$nestedWhere->equalTo($table . '.acc_city_id', $filterParams["apt_location_id"]);
$where->addPredicate($nestedWhere);
}
if (isset($filterParams["partner_id"]) && $filterParams["partner_id"] != '0') {
$where->equalTo($table . '.partner_id', $filterParams["partner_id"]);
}
if (isset($filterParams["partner_reference"]) && $filterParams["partner_reference"] != '') {
$where->like($table . '.partner_ref', '%' . $filterParams["partner_reference"] . '%');
}
if (isset($filterParams["payment_model"]) && $filterParams["payment_model"] != '0') {
$where->equalTo($table . '.model', $filterParams["payment_model"]);
}
if (!$auth->hasRole(Roles::ROLE_DEVELOPMENT_TESTING)) {
$where->expression($table . '.apartment_id_assigned NOT IN(' . Constants::TEST_APARTMENT_1 . ', ' . Constants::TEST_APARTMENT_2 . ')', []);
}
if (isset($filterParams["no_collection"]) && $filterParams["no_collection"] > 1) {
$where->equalTo($table . '.no_collection', $filterParams["no_collection"] == 2 ? 1 : 0);
}
if (isset($filterParams["channel_res_id"]) && $filterParams["channel_res_id"]) {
$where->equalTo($table . '.channel_res_id', $filterParams["channel_res_id"]);
}
return $where;
}
public function getUDListCount($userId, $type)
{
$this->resultSetPrototype->setArrayObjectPrototype(new \ArrayObject());
$result = $this->fetchOne(function (Select $select) use($userId, $type) {
$where = new Where();
$where->lessThanOrEqualTo($this->getTable() . '.start_date', date('Y-m-d H:i:s', strtotime('+3 day')));
$where->notIn('task_status', [TaskService::STATUS_CANCEL, TaskService::STATUS_VERIFIED]);
$expression = false;
switch ($type) {
case 'team':
$select->join(['team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.team_id = team_staff.team_id
AND team_staff.user_id = ' . $userId . '
AND team_staff.type IN (' . TeamService::STAFF_MANAGER . ', ' . TeamService::STAFF_OFFICER . ')'), [], Select::JOIN_INNER)->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);
$where->notEqualTo('is_hk', TaskService::TASK_IS_HOUSEKEEPING);
$expression = 'task_responsibles.id IS NULL';
break;
case 'team_assigned':
$select->join(['team_staff' => DbTables::TBL_TEAM_STAFF], new Expression($this->getTable() . '.team_id = team_staff.team_id
AND team_staff.user_id = ' . $userId . '
AND team_staff.type IN (' . TeamService::STAFF_MANAGER . ', ' . TeamService::STAFF_OFFICER . ', ' . TeamService::STAFF_MEMBER . ')'), [], Select::JOIN_INNER)->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);
$expression = 'task_responsibles.user_id = ' . User::ANY_TEAM_MEMBER_USER_ID;
break;
case 'created':
$select->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);
$expression = 'task_creators.user_id = ' . $userId;
break;
case 'verifying':
$select->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);
$expression = 'task_verifiers.user_id = ' . $userId . ' AND task_status = ' . TaskService::STATUS_DONE;
break;
case 'doing':
$select->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(['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);
$expression = '(task_responsibles.user_id = ' . $userId . ' OR task_helpers.user_id = ' . $userId . ') AND task_status <> ' . TaskService::STATUS_DONE;
break;
case 'following':
$select->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);
$select->join(['following_team_staff' => DbTables::TBL_TEAM_STAFF], new Expression('task_followers.user_id = ' . $userId . ' OR (' . $this->getTable() . '.following_team_id = following_team_staff.team_id
AND following_team_staff.user_id = ' . $userId . '
AND following_team_staff.type IN (' . TeamService::STAFF_MANAGER . ', ' . TeamService::STAFF_OFFICER . ', ' . TeamService::STAFF_MEMBER . ')
AND is_hk = 0)'), [], Select::JOIN_INNER);
$expression = 'task_status != ' . TaskService::STATUS_DONE;
break;
}
if ($expression) {
$where->expression($expression, []);
}
$select->columns(['count' => new Expression('COUNT(DISTINCT ' . $this->getTable() . '.id' . ')')])->where($where);
});
return $result['count'];
}
public function teamSave($data, $id, $global, $isDirector)
{
$apartmentDao = $this->getServiceLocator()->get('dao_accommodation_accommodations');
$auth = $this->getServiceLocator()->get('library_backoffice_auth');
$data = (array) $data;
$teamData = ['usage_department' => $data['usage_department'], 'usage_notifiable' => $data['usage_notifiable'], 'usage_frontier' => $data['usage_frontier'], 'usage_taskable' => $data['usage_taskable'], 'usage_security' => $data['usage_security'], 'usage_hiring' => $data['usage_hiring'], 'usage_storage' => $data['usage_storage'], 'extra_inspection' => isset($data['extra_inspection']) ? $data['extra_inspection'] : 0, 'timezone' => $data['timezone']];
if ($global) {
$teamData['name'] = $data['name'];
$teamData['description'] = $data['description'];
}
if ($id > 0) {
$teamData['modified_date'] = date('Y-m-d');
$this->getTeamDao()->save($teamData, ['id' => (int) $id]);
} else {
$teamData['created_date'] = date('Y-m-d');
$teamData['modified_date'] = null;
$id = $this->getTeamDao()->save($teamData);
$this->getTeamStaffDao()->insert(['type' => self::STAFF_CREATOR, 'team_id' => $id, 'user_id' => $auth->getIdentity()->id]);
}
$diffTimezone = [];
if ($id > 0) {
$teamInfo = $this->getTeamDao()->fetchOne(['id' => $id]);
// Remove duplication if user is set as both manager and member && its not frontier team
if ($teamInfo && $teamInfo->isFrontier() != self::IS_FRONTIER_TEAM) {
if (isset($data['members'])) {
$managers = $data['managers'];
$members = $data['members'];
foreach ($managers as $manager) {
if (array_search($manager, $members) !== false) {
$key = array_search($manager, $members);
unset($data['members'][$key]);
}
}
}
}
// Delete all staff and save again to avoid complicated select, check, insert & delete actions
$staffDeleteCondition = new Where();
$staffDeleteCondition->equalTo('team_id', $id);
// Do not delete creator
$staffDeleteCondition->notEqualTo('type', self::STAFF_CREATOR);
// Change (delete + insert) director only if global
if (!$global) {
$staffDeleteCondition->notEqualTo('type', self::STAFF_DIRECTOR);
}
// Change (delete + insert) managers only if global or director
if (!$global && !$isDirector) {
$staffDeleteCondition->notEqualTo('type', self::STAFF_MANAGER);
}
$this->getTeamStaffDao()->delete($staffDeleteCondition);
// Insert director, if global
if ($global && $data['director']) {
$this->getTeamStaffDao()->insert(['type' => self::STAFF_DIRECTOR, 'team_id' => $id, 'user_id' => $data['director']]);
}
// Insert members passed by form
if (!empty($data['members'])) {
foreach ($data['members'] as $member) {
$this->getTeamStaffDao()->insert(['type' => self::STAFF_MEMBER, 'team_id' => $id, 'user_id' => $member]);
}
}
// Insert officers passed by form
if (!empty($data['officers'])) {
foreach ($data['officers'] as $officer) {
$this->_dao_team_staff->insert(['type' => self::STAFF_OFFICER, 'team_id' => $id, 'user_id' => $officer]);
}
}
// Insert managers passed by form
if (!empty($data['managers'])) {
foreach ($data['managers'] as $manager) {
$this->_dao_team_staff->insert(['type' => self::STAFF_MANAGER, 'team_id' => $id, 'user_id' => $manager]);
}
}
// Delete all apartments and buildings to avoid complexity of selecting existing, comparison, deletion and addition
if ($global || $isDirector) {
$apartmentsDeleteCondition = new Where();
$apartmentsDeleteCondition->equalTo('team_id', $id);
$this->getTeamFrontierApartmentsDao()->delete($apartmentsDeleteCondition);
$this->getTeamFrontierBuildingsDao()->delete($apartmentsDeleteCondition);
// Insert apartments passed by form
if (!empty($data['frontier_apartments'])) {
foreach ($data['frontier_apartments'] as $frontierApartment) {
$isDuplicate = $this->getTeamFrontierApartmentsDao()->checkDuplicateApartment($id, $frontierApartment);
if (!$isDuplicate) {
$this->getTeamFrontierApartmentsDao()->insert(['team_id' => $id, 'apartment_id' => $frontierApartment]);
$apartmentTimezone = $apartmentDao->getApartmentTimezone($frontierApartment);
if ($data['timezone'] !== $apartmentTimezone['timezone']) {
array_push($diffTimezone, $apartmentTimezone['name']);
}
}
}
}
// Insert buildings passed by form
if (!empty($data['frontier_buildings'])) {
foreach ($data['frontier_buildings'] as $frontierBuilding) {
$this->getTeamFrontierBuildingsDao()->insert(['team_id' => $id, 'building_id' => $frontierBuilding]);
}
}
}
return ['id' => $id, 'diffTimezone' => implode(',', $diffTimezone)];
}
}
