//Площади не жилых объектов с группировкой по категориям

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 (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 (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

}

}

//Площади не жилых объектов с группировкой по категориям в Иркутске и Ангарске

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

}

}

//Площадь жилых помещеший

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

c.category.isLiving = true)

//С учетом доли

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), c.share, '+

'(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", "share", "rentArea")

.list()

def res = [:]

for(def item : val){

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

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

"area": getNN(item.getValue("area"))*getNNShare(item.getValue("share")),

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

]

}else{

res[item.getValue("category")]['area'] +=getNN(item.getValue("area"))*getNNShare(item.getValue("share"))

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 \'%г% Ангарск%\' 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")),

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

]

}else{

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

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

}

}

for(def item : res){

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

}

return res

def getNN(def item){

if(item == null){

return BigDecimal.ZERO

}else{

return item

}

}

def getNNShare(def item){

if(item == null | item == BigDecimal.ZERO){

return BigDecimal.ONE

}else{

return item

}

}