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;
}
/**
* 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 fetchAllByIdEmpleadoAndPeriodo($id_empleado, $inicio, $fin)
{
$sql = new Sql($this->adapter);
$select = $sql->select();
$select->from($this->table)->join('cat_asistencia', 'cat_asistencia.id = tab_asistencia.id_cat_asistencia', array('asistencia' => 'nombre'));
$where1 = new Where();
$where1->between('tab_asistencia.fecha', $inicio, $fin);
$select->where($where1);
$where2 = new Where();
$where2->equalTo('tab_asistencia.id_empleado', $id_empleado);
$select->where(array($where2));
$select->order(array('fecha'));
// echo $select->getSqlString();
$statement = $sql->prepareStatementForSqlObject($select);
$resultSet = $statement->execute();
$entities = array();
foreach ($resultSet as $row) {
$entity = new Entity\Asistencia(array('id' => $row["id"], 'id_empleado' => $row["id_empleado"], 'id_cat_asistencia' => $row["id_cat_asistencia"], 'fecha' => $row["fecha"], 'asistencia' => $row["asistencia"]));
$entities[] = $entity->toArray();
}
return $entities;
}
public function detachBranch($nodeMoveID, $options = null)
{
$moveInfo = $this->getNodeInfo(array('id' => $nodeMoveID));
$moveLeft = $moveInfo->left;
$moveRight = $moveInfo->right;
$totalNode = ($moveRight - $moveLeft + 1) / 2;
// ================================== Node on branch ==================================
if ($options == null) {
$data = array('left' => new Expression('`left` - ?', array($moveLeft)), 'right' => new Expression('`right` - ?', array($moveRight)));
$where = new Where();
$where->between('left', $moveInfo->left, $moveInfo->right);
$this->tableGateway->update($data, $where);
}
if ($options['task'] == 'remove-node') {
$where = new Where();
$where->between('left', $moveInfo->left, $moveInfo->right);
$this->tableGateway->delete($where);
}
// ================================== Node on tree (LEFT) ==================================
$data = array('left' => new Expression('`left` - ?', array($totalNode * 2)));
$where = new Where();
$where->greaterThan('left', $moveRight);
$this->tableGateway->update($data, $where);
// ================================== Node on tree (RIGHT) ==================================
$data = array('right' => new Expression('`right` - ?', array($totalNode * 2)));
$where = new Where();
$where->greaterThan('right', $moveInfo->right);
$this->tableGateway->update($data, $where);
return $totalNode;
}
/**
* Deletes a row from tree.
*
* @param int|array $where
* @param string $table
* @return int number of affected rows
*/
public function delete($where, $table = null)
{
if (is_array($where)) {
$pk = $where[$this->getPrimaryKey()];
} else {
$pk = (int) $where;
}
$row = $this->getPosition($pk);
$where = new Where();
$where->between(self::COLUMN_LEFT, $row->getLft(), $row->getRgt());
$result = parent::delete($where, $table);
if ($result) {
$this->updateTree($row->getRgt(), '-', $row->width());
}
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);
}
}
/**
* @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;
}
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);
}
}
}