select distinct c.personalAccount, c.name, case when c.legalAddress is null then (case when c.actualAddress is null then c.registrationAddress else c.actualAddress end) else c.legalAddress end, '', 0 from rtneo$Contragent c join c.realEstates r where (c.type = 1 or c.type = 3) and c.personalAccount is not null and c.name is not null and not(c.legalAddress is null and c.actualAddress is null and c.registrationAddress is null) and (r.category.isLiving is null or r.category.isLiving = false)

def list = dataManager.loadValues('select distinct c.personalAccount, c.name, case when c.legalAddress is null then (case when c.actualAddress is null then c.registrationAddress else c.actualAddress end) else c.legalAddress end, \'\', 0 from rtneo$Contragent c join c.realEstates r where (c.type = 1 or c.type = 3) and c.personalAccount is not null and c.name is not null and not(c.legalAddress is null and c.actualAddress is null and c.registrationAddress is null) and (r.category.isLiving is null or r.category.isLiving = false)')

.properties("personalAccount", "name", "address", "empty", "sum")

// .maxResults(1000)

.list()

_(list.size())

list.each{_("${it.getValue('personalAccount')}|${it.getValue('name')}|${it.getValue('address')}|${it.getValue('empty')}|${it.getValue('sum')}".replaceAll("\\p{Cntrl}", ""))}

/**

* Логирование

*/

import com.haulmont.cuba.core.app.serialization.EntitySerializationAPI;

import com.groupstp.rtneo.util.JsonUtil;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

private _(Object obj, String... options){

if(obj == null){log("LOG.ERROR: Object is null!!!");return}

if(options.size() == 0){log(obj)}

for(def option : options){

if(option.equals("str")){log(obj)}

if(option.equals("for")){obj.each({_(it)})}

if(option.equals("json")){

EntitySerializationAPI entitySerializationAPI = AppBeans.get(EntitySerializationAPI.NAME)

try{log(entitySerializationAPI.toJson(obj))}

catch(Exception e){log("LOG.ERROR: JSON entity serialization failed")}

}

if(option.equals("objJson")){

JsonUtil jsonUtil = AppBeans.get(JsonUtil.NAME)

try{log.(jsonUtil.toJson(obj))}

catch(Exception e){log("LOG.ERROR: JSON object serialization failed")}

}

if(option.equals("date")){

DateFormat df = new SimpleDateFormat("dd.MM.yyyy");

try{log(df.format(obj))}

catch(Exception e){log("LOG.ERROR: Failed date format")}

}

}

}

private log(Object obj){log.debug(obj)}