select count(distinct c.contragent) from rtneo$Contract c join rtneo$ContragentRealEstate cre where

c.id = cre.contragent.id and c.accepted = true and c.createTs between '2020-01-01' and '2020-06-30' and c.acceptanceDate between '2020-01-01' and '2020-06-30' and

c.contragent.type = 1 and lower(cre.realEstate.address) like lower('%%Байкальск%%')

import com.haulmont.cuba.core.global.*;

import com.groupstp.rtneo.entity.*

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import com.haulmont.cuba.core.global.ViewRepository;

import com.haulmont.cuba.core.global.View;

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

Calendar calStart = Calendar.getInstance();

Calendar calEnd = Calendar.getInstance();

calEnd.setTime(df.parse('01.06.2020'))

def contragents = dataManager.loadValues('select distinct c.contragent.id, c.contragent.inn from rtneo$Contract c, rtneo$ContragentRealEstate cre '+

'where c.contragent.id = cre.contragent.id and c.accepted = true '+

'and c.createTs between \'2020-01-01\' and \'2020-06-30\' and c.acceptanceDate between \'2020-01-01\' and \'2020-06-30\' '+

'and c.contragent.type = 2 and (lower(cre.realEstate.address) like lower(\'%%г Байкальск%%\') or lower(cre.realEstate.address) like lower(\'%%г. Байкальск%%\'))')

.properties("id","inn")

.list()

def paySum = BigDecimal.ZERO

def accrualSum = BigDecimal.ZERO

for(def contragent : contragents){

paySum += dataManager.loadValue('select sum(p.sum) from rtneo$Payment p where p.inn = :inn and p.date between \'2020-01-01\' and \'2020-06-30\'', BigDecimal.class)

.parameter("inn", contragent.getValue('inn'))

.optional()

.orElse(BigDecimal.ZERO)

calStart.setTime(df.parse('01.01.2020'))

while(calStart.getTime().compareTo(calEnd.getTime()) <= 0){

accrualSum += getBillsOnPeriod(contragent.getValue('id'), calStart.getTime()).stream().map{e->e.getTotalSum()}.reduce{a,b->a.add(b)}.orElse(BigDecimal.ZERO)

calStart.add(Calendar.MONTH, 1)

}

}

_(contragents.size())

_(paySum)

_(accrualSum)

import com.haulmont.cuba.core.global.ViewRepository;

import com.haulmont.cuba.core.global.View;

public List<Accrual> getBillsOnPeriod(UUID contragentID, Date period) {

ViewRepository viewRepository = AppBeans.get(ViewRepository.NAME)

View acView = viewRepository.getView(Accrual.class, "_local");

acView.addProperty("contractPosition", viewRepository.getView(ContractPosition.class, "_local")

.addProperty("contragentRealEstate", viewRepository.getView(ContragentRealEstate.class, "_local")))

.addProperty("createTs")

.addProperty("createdBy");

List<Contract> contracts = dataManager.load(Contract.class)

.query('select e from rtneo$Contract e where e.contragent.id = :contragent and e.accepted=true order by e.createTs desc')

.parameter("contragent", contragentID)

.view("_local")

.list();

if (contracts.size() == 0) {

contracts = dataManager.load(Contract.class)

.query('select e from rtneo$Contract e where e.contragent.id = :contragent order by e.createTs')

.parameter("contragent", contragentID)

.view("_local")

.list();

}

if (contracts.size() > 0) {

int i = 0;

Contract contract = contracts.get(i);

if (contract.getFrom() == null) return null;

while (contract.getFrom() != null & period.before(contract.getFrom())) {

i = i + 1;

if (i == contracts.size()) break;

contract = contracts.get(i);

}

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

if (contract.getFrom() != null) {

List<Accrual> accruals = dataManager.load(Accrual.class)

.query('select e from rtneo$Accrual e where e.contractPosition.contract.id=:contractId and e.period=:period')

.parameter("contractId", contract.getId())

.parameter("period", period)

.view(acView)

.list();

return accruals;

}

}

return null;

}

/**

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

*/

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)}