Выбираем кол-во объектов и арендуемую площадь всех действующих арендаторов на текущую дату

select count(rr), sum(rr.renterRecord.areaInRent) from rtneo$RealEstateRenter rr where

(rr.renterRecord.validityFrom is null or rr.renterRecord.validityFrom < CURRENT_DATE)

or(rr.renterRecord.validityTo is null or rr.renterRecord.validityTo > CURRENT_DATE)

Арендуемые обекты контрагента без RealEstateRenter

select count(c) from rtneo$ContragentRealEstate c where c.ownType = 1 and not c.id in (select rr.renterRecord.id from rtneo$RealEstateRenter rr)

Объекты контрагента в собственности и не сдаются

select count(c) from rtneo$ContragentRealEstate c where not c.ownType = 1 and not c.id in (select rr.contragentRealEstate.id from rtneo$RealEstateRenter rr)

Работает. Запросом получает сданную на текущий момент площадь объекта контрагента

select c.id, sum(c.realEstate.area), (

select sum(rr.areaInRent) from rtneo$RealEstateRenter rr where

rr.contragentRealEstate.id = c.id

and (rr.renterRecord.validityFrom is null or rr.renterRecord.validityFrom < CURRENT_DATE)

and(rr.renterRecord.validityTo is null or rr.renterRecord.validityTo > CURRENT_DATE)

) from rtneo$ContragentRealEstate c where c.id = '4f51c467-34d8-25ec-25c6-6628fb01cf57' group by c.id

import com.groupstp.rtneo.entity.*

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

DataManager dataManager = AppBeans.get(DataManager.NAME);

def q = 'select c.category.name, c.id, sum(c.realEstate.area), (select sum(rr.areaInRent) from rtneo$RealEstateRenter rr where '+

'rr.contragentRealEstate.id = c.id '+

'and (rr.renterRecord.validityFrom is null or rr.renterRecord.validityFrom < CURRENT_DATE) '+

'and(rr.renterRecord.validityTo is null or rr.renterRecord.validityTo > CURRENT_DATE)'+

') from rtneo$ContragentRealEstate c where c.realEstate.address like \'%г% Ангарск%\' group by c.category.name, c.id'

//def renters = dataManager.load(RealEstateRenter.class)

// .query(q)

// .view("_local")

// .list()

//

//def cres = dataManager.load(ContragentRealEstate.class)

// .query(q)

// .view("_local")

// .list()

//

//def res = dataManager.load(RealEstate.class)

// .query(q)

// .view("_local")

// .list()

def res = [:]

def val = dataManager.loadValues(q)

.properties("name", "id", "sum")

.list()

for(def item : val)log.debug(item.getValue('sum'))

Вся жилая площадь

51515292.87

Жилая площадь Иркутск, Ангарск

3765874.26

select sum(r.area) from rtneo$RealEstate r join rtneo$ContragentRealEstate cre where r in (select distinct c.realEstate from rtneo$ContragentRealEstate c where

(c.realEstate.address like '%г% Иркутск%' or c.realEstate.address like '%г% Ангарск%')

and (not c.category.isLiving = true or c.category.isLiving is null))

select c.id, c.category.name, sum(c.realEstate.area),

(select sum(rr.areaInRent) from rtneo$RealEstateRenter rr where

rr.contragentRealEstate.id = c.id

and (rr.renterRecord.validityFrom is null or rr.renterRecord.validityFrom < CURRENT_DATE)

and(rr.renterRecord.validityTo is null or rr.renterRecord.validityTo > CURRENT_DATE))

from rtneo$ContragentRealEstate c where

not c.ownType = 1

and (c.realEstate.address like '%г% Иркутск%' or c.realEstate.address like '%г% Ангарск%')

and (not c.category.isLiving = true or c.category.isLiving is null)

group by c.id, c.category.name

Готовый отбор по Ангарск+Иркутск. Площади без аренды

select c.id, c.category.name, sum(c.realEstate.area),

(select sum(rr.areaInRent) from rtneo$RealEstateRenter rr where

rr.contragentRealEstate.id = c.id

and (rr.renterRecord.validityFrom is null or rr.renterRecord.validityFrom < CURRENT_DATE)

and(rr.renterRecord.validityTo is null or rr.renterRecord.validityTo > CURRENT_DATE))

from rtneo$ContragentRealEstate c where

not c.ownType = 1

and (c.realEstate.address like '%г% Иркутск%' or c.realEstate.address like '%г% Ангарск%')

and (not c.category.isLiving = true or c.category.isLiving is null)

group by c.id, c.category.name

Готовый скрипт вывода площади для собственников

======================================================================

import com.groupstp.rtneo.entity.*

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

DataManager dataManager = AppBeans.get(DataManager.NAME);

def q = 'select c.id, c.category.name, sum(c.realEstate.area), '+

'(select sum(rr.areaInRent) from rtneo$RealEstateRenter rr where '+

'rr.contragentRealEstate.id = c.id '+

'and (rr.renterRecord.validityFrom is null or rr.renterRecord.validityFrom < CURRENT_DATE) '+

'and(rr.renterRecord.validityTo is null or rr.renterRecord.validityTo > CURRENT_DATE)) '+

'from rtneo$ContragentRealEstate c where '+

'not c.ownType = 1 '+

'and (c.realEstate.address like \'%г% Иркутск%\' or c.realEstate.address like \'%г% Ангарск%\') '+

'and (not c.category.isLiving = true or c.category.isLiving is null) '+

'group by c.id, c.category.name'

def val = dataManager.loadValues(q)

.properties("id", "category", "area", "rentArea")

.list()

def res = [:]

for(def item : val){

if(res[item.getValue("category")] == null){

res[item.getValue("category")] = [

"area": getNN(item.getValue("area"))-getNN(item.getValue("rentArea")),

"rentArea": getNN(item.getValue("rentArea"))

]

}else{

res[item.getValue("category")]['area'] +=getNN(item.getValue("area"))-getNN(item.getValue("rentArea"))

res[item.getValue("category")]['rentArea'] +=getNN(item.getValue("rentArea"))

}

}

def q2 = 'select '+

'c.id, '+

'c.category.name, '+

'c.areaInRent, '+

'(select sum(rr.areaInRent) from rtneo$RealEstateRenter rr '+

'where rr.contragentRealEstate.id = c.id '+

'and (rr.renterRecord.validityFrom is null or rr.renterRecord.validityFrom < CURRENT_DATE) '+

'and(rr.renterRecord.validityTo is null or rr.renterRecord.validityTo > CURRENT_DATE)) '+

'from rtneo$ContragentRealEstate c '+

'where c.ownType = 1 '+

'and (c.realEstate.address like \'%г% Иркутск%\' or c.realEstate.address like \'%г% Ангарск%\') '+

'and (not c.category.isLiving = true or c.category.isLiving is null) '+

'group by c.id, c.category.name'

def val2 = dataManager.loadValues(q2)

.properties("id", "category", "area", "rentArea")

.list()

for(def item : val2){

if(res[item.getValue("category")] == null){

res[item.getValue("category")] = [

"area": getNN(item.getValue("area"))-getNN(item.getValue("rentArea")),

"rentArea": getNN(item.getValue("rentArea"))

]

}else{

res[item.getValue("category")]['area'] +=getNN(item.getValue("area"))-getNN(item.getValue("rentArea"))

res[item.getValue("category")]['rentArea'] +=getNN(item.getValue("rentArea"))

}

}

return res

def getNN(def item){

if(item == null){

return BigDecimal.ZERO

}else{

return item

}

}

Готовый скрипт вывода площади для собственников и арендаторов

=========================================================================

import com.groupstp.rtneo.entity.*

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

DataManager dataManager = AppBeans.get(DataManager.NAME);

def q = 'select c.id, c.category.name, sum(c.realEstate.area), '+

'(select sum(rr.areaInRent) from rtneo$RealEstateRenter rr where '+

'rr.contragentRealEstate.id = c.id '+

'and (rr.renterRecord.validityFrom is null or rr.renterRecord.validityFrom < CURRENT_DATE) '+

'and(rr.renterRecord.validityTo is null or rr.renterRecord.validityTo > CURRENT_DATE)) '+

'from rtneo$ContragentRealEstate c where '+

'not c.ownType = 1 '+

'and (c.realEstate.address like \'%г% Иркутск%\' or c.realEstate.address like \'%г% Ангарск%\' or c.realEstate.address like \'%город Ангарск%\' or c.realEstate.address like \'%город Иркутск%\') '+

'and (not c.category.isLiving = true or c.category.isLiving is null) '+

'group by c.id, c.category.name'

def val = dataManager.loadValues(q)

.properties("id", "category", "area", "rentArea")

.list()

def res = [:]

for(def item : val){

if(res[item.getValue("category")] == null){

res[item.getValue("category")] = [

"area": getNN(item.getValue("area"))-getNN(item.getValue("rentArea"))]

}else{

res[item.getValue("category")]['area'] +=getNN(item.getValue("area"))-getNN(item.getValue("rentArea"))

}

}

def q2 = 'select '+

'c.id, '+

'c.category.name, '+

'c.areaInRent, '+

'(select sum(rr.areaInRent) from rtneo$RealEstateRenter rr '+

'where rr.contragentRealEstate.id = c.id '+

'and (rr.renterRecord.validityFrom is null or rr.renterRecord.validityFrom < CURRENT_DATE) '+

'and(rr.renterRecord.validityTo is null or rr.renterRecord.validityTo > CURRENT_DATE)) '+

'from rtneo$ContragentRealEstate c '+

'where c.ownType = 1 '+

'and (c.realEstate.address like \'%г% Иркутск%\' or c.realEstate.address like \'%г% Ангарск%\' or c.realEstate.address like \'%город Ангарск%\' or c.realEstate.address like \'%город Иркутск%\') '+

'and (not c.category.isLiving = true or c.category.isLiving is null) '+

'group by c.id, c.category.name'

def val2 = dataManager.loadValues(q2)

.properties("id", "category", "area", "rentArea")

.list()

for(def item : val2){

if(res[item.getValue("category")] == null){

res[item.getValue("category")] = [

"area": getNN(item.getValue("area"))-getNN(item.getValue("rentArea"))]

}else{

res[item.getValue("category")]['area'] +=getNN(item.getValue("area"))-getNN(item.getValue("rentArea"))

}

}

for(def item : res){

log.debug("!${item.getKey()}!${item.getValue()['area']}")

}

return res

def getNN(def item){

if(item == null){

return BigDecimal.ZERO

}else{

return item

}

}