/**
* @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;
}
