/** * @return array country */ public function getCountriesAndCitiesWithSums() { $adapter = $this->cityCodesTable->getTableGateway()->getAdapter(); $sql = new Sql($adapter); $select = $sql->select(); $where = new Where(); $where->isNotNull('l.price'); $select->from(array('c' => $this->cityCodesTable->getTableName()))->columns(array('ISO2'))->join(array('l' => $this->listingsTable->getTableName()), 'c.ISO2 = l.country', array('total' => new Expression('SUM(price)')))->where($where)->group('c.ISO2'); //echo $select->getSqlString($this->listingsTable->getAdapter()->getPlatform()); //exit; $list = $adapter->query($select->getSqlString($adapter->getPlatform()), Adapter::QUERY_MODE_EXECUTE); $output = array(); foreach ($list as $row) { $output[$row->ISO2] = array('sum' => $row->total, 'cities' => $this->cityCodesTable->getListByCountry($row->ISO2)); } return $output; }
/** * * @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; }