import java.util.*

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

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

import com.groupstp.rtneo.entity.*

import java.text.SimpleDateFormat

import com.groupstp.rtneo.service.BillService

BillService billService = AppBeans.get(BillService.NAME)

SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd")

SimpleDateFormat dateView = new SimpleDateFormat("dd-MM-yyyy")

String period1 = '2019-01-01'

String period2 = '2020-12-01'

String period2020 = '2020-01-01'

String paymentLimit = '2020-06-05'

Date paymentLimitDate = df1.parse(paymentLimit)

Calendar periodStart = Calendar.getInstance();

periodStart.setTime(df1.parse(period1));

Calendar periodEnd = Calendar.getInstance();

periodEnd.setTime(df1.parse(period2));

Calendar period20 = Calendar.getInstance();

period20.setTime(df1.parse(period2020));

def i = 1;

List<Contragent> contragents = dataManager

// .loadValues('select distinct c.id, c.inn, c.kpp, c.personalAccount from rtneo$Contragent c where not c.personalAccount is null and size(c.contracts) > 0 and (select count(p) from rtneo$Payment p where p.inn = c.inn)>0 order by c.inn')

.loadValues('select distinct c.id, c.name, c.inn, c.kpp, c.personalAccount from rtneo$Payment e join rtneo$Contragent c on e.inn = c.inn where not e.inn is null and not c.personalAccount is null and size(c.contracts) > 0 order by c.inn')

.properties("id", "name", "inn", "kpp", "pa")

.firstResult(i*1000)

.maxResults(1000)

.list();

def accrualsGroup = new TreeMap<>();

def contragentsGroup = new TreeMap<>();

String strCaption = "!Наименование!ЛС!ИНН!КПП"

Calendar beginYear = periodStart.clone()

while(!beginYear.after(periodEnd)){

strCaption += "!${dateView.format(beginYear.getTime())}!Оплачено"

beginYear.add(Calendar.MONTH, 1)

}

strCaption += "!Оплат за 2020 год!Последняя оплата"

_(strCaption)

for(def contragent : contragents){

def payments = dataManager.load(Payment.class)

.query('select p from rtneo$Payment p where p.inn = :inn and p.createTs <= :paymentLimitDate')

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

.parameter("paymentLimitDate", paymentLimitDate)

.view('_local')

.list();

if(payments.size() == 0) continue;

// List<Accrual> rentAccruals = getRentersAccruals(contragent.getValue('id'));

List<Accrual> rentAccruals = null

if(contragentsGroup[contragent.getValue('inn')] == null){

contragentsGroup[contragent.getValue('inn')] =[

'id' : contragent.getValue('id'),

'name' : contragent.getValue('name'),

'inn' : contragent.getValue('inn'),

'kpp' : contragent.getValue('kpp'),

'pa' : contragent.getValue('pa')

]

}

contragentsGroup[contragent.getValue('inn')]['payments'] = [:];

for(def payment : payments){

if(contragentsGroup[contragent.getValue('inn')]['payments'][dateView.format(payment.getDate())] == null){

contragentsGroup[contragent.getValue('inn')]['payments'][dateView.format(payment.getDate())] = [

'purpose' : payment.getPurpose(),

'sum' : payment.getSum()

]

}else{

contragentsGroup[contragent.getValue('inn')]['payments'][dateView.format(payment.getDate())]['sum'] += payment.getSum();

}

}

contragentsGroup[contragent.getValue('inn')]['accruals'] = [:];

accrualsGroup = [:];

List<Accrual> accruals = getContragentBillsLight(UUID.fromString(contragent.getValue('id').toString()))

// if(rentAccruals != null){

// accruals.addAll(rentAccruals)

// }

for(def accrual : accruals){

tariffNoNDS = accrual.getPrice()*5/6;

if(accrualsGroup[dateView.format(accrual.getPeriod())]==null){

accrualsGroup[dateView.format(accrual.getPeriod())] = [

'period' : dateView.format(accrual.getPeriod()),

'number' : accrual.getDocumentNumber(),

'tariff' : tariffNoNDS,

'amountBase' : getNN(accrual.getAmountBase()),

'totalSumBaseNoNds' : getNN(accrual.getAmountBase())*tariffNoNDS,

'totalSumBase' : getNN(accrual.getTotalSumBase()),

'totalSum' : getNN(accrual.getTotalSum()),

'done' : false

]

}else{

accrualsGroup[dateView.format(accrual.getPeriod())]['amountBase'] += getNN(accrual.getAmountBase())

accrualsGroup[dateView.format(accrual.getPeriod())]['totalSumBaseNoNds'] += getNN(accrual.getAmountBase())*tariffNoNDS

accrualsGroup[dateView.format(accrual.getPeriod())]['totalSumBase'] += getNN(accrual.getTotalSumBase())

accrualsGroup[dateView.format(accrual.getPeriod())]['totalSum'] += getNN(accrual.getTotalSum())

}

}

contragentsGroup[contragent.getValue('inn')]['accruals'] = accrualsGroup;

}

for(def contragentItem : contragentsGroup){

Map<String, Object> res = new HashMap<>();

res.put("name", contragentItem.getValue()["name"])

res.put("pa", contragentItem.getValue()["pa"])

res.put("inn", contragentItem.getValue()["inn"])

res.put("kpp", contragentItem.getValue()["kpp"])

Map<Date, Map<String, Object>> accAndPay = new HashMap<>()

BigDecimal pay2020 = BigDecimal.ZERO

for(def a : contragentItem.getValue()["accruals"]){

accAndPay.putIfAbsent(a.getValue()["period"], new HashMap<>())

accAndPay.get(a.getValue()["period"]).put("sum", a.getValue()["totalSum"])

BigDecimal payPeriod = BigDecimal.ZERO

for(def p : contragentItem.getValue()["payments"]){

Calendar periodAccrual = Calendar.getInstance()

periodAccrual.setTime(dateView.parse(a.getValue()["period"]))

periodAccrual.add(Calendar.MONTH, 1)

if(dateView.parse(p.getKey()).compareTo(dateView.parse(a.getValue()["period"])) >= 0 && dateView.parse(p.getKey()).compareTo(periodAccrual.getTime())<0){

payPeriod = payPeriod.add(p.getValue()["sum"])

}

}

accAndPay.get(a.getValue()["period"]).put("pay", payPeriod)

}

String str = "!${res.get('name')}!${res.get('pa')}!${res.get('inn')}!${res.get('kpp')}"

// Calendar beginYear = Calendar.newInstance()

// beginYear.setTime(df1.parse("2019-01-01"));

beginYear = periodStart.clone()

while(!beginYear.after(periodEnd)){

if(accAndPay.get(dateView.format(beginYear.getTime())) == null){

str += "!null!null"

}else{

str += "!${accAndPay.get(dateView.format(beginYear.getTime())).get('sum')}!${accAndPay.get(dateView.format(beginYear.getTime())).get('pay')}"

}

beginYear.add(Calendar.MONTH, 1)

}

for(def p : contragentItem.getValue()["payments"]){

if(dateView.parse(p.getKey()).compareTo(period20.getTime()) >= 0){

pay2020 = pay2020.add(p.getValue()["sum"])

}

}

str += "!${pay2020}"

def lastPay = dataManager.loadValue('select max(p.date) from rtneo$Payment p where p.inn = :inn', Date.class)

.parameter("inn" , res.get('inn'))

.optional()

.orElse(null)

str += "!${dateView.format(lastPay)}"

res.put("calculations", accAndPay)

res.put("pay2020", pay2020)

_(str)

}

public List<Accrual> getRentersAccruals(UUID id){

List<Accrual> rentAccruals = null

def isPayRent = false;

def res = dataManager.load(RealEstateRenter.class)

.query('select r from rtneo$RealEstateRenter r where r.contragentRealEstate.contragent.id = :id and r.payOwner = true')

.parameter("id", id)

.view("realEstateRenter-changeQueryControl")

.list();

if(res.size() != 0){

isPayRent = true

// log.debug("Contragent[${contragent.getValue('id')}] rent is ${res.size()}")

}

if(isPayRent){

rentAccruals = initBillsRentersTab(res)

}

return rentAccruals;

}

def initBillsRentersTab(List<RealEstateRenter> rentersRE) {

//Формируем запрос в accrualDs для получения начислений арендаторов для которых арендотатель выбрал оплату арендотаделем

String fullSql = '';

int actual = 0;

// List<RealEstateRenter> rentersRE = getRentersRE();

if (rentersRE.size() > 0) {

for (RealEstateRenter renterRE : rentersRE) {

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", renterRE.getContragent().getId())

.view("_local")

.list();

if (contracts.size() > 0) {

Contract lastContract = null;

String sqlQuery = '';

for (Contract contract : contracts) {

if (lastContract == null) {

lastContract = contract;

sqlQuery = 'cast(a.contractPosition.contract.id text)=\'' + contract.getId().toString() + '\' ';

} else {

if (contract.getFrom().before(lastContract.getFrom())) {

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

String date = sdf.format(lastContract.getFrom());

sqlQuery = sqlQuery + ' or (cast(a.contractPosition.contract.id text)=\'' + contract.getId().toString() + '\' and a.period < \'' + date + '\' )';

lastContract = contract;

}

}

}

if (sqlQuery != '') {

sqlQuery = '(cast(a.contragent.id text) = \'' + renterRE.getContragent().getId().toString() + '\' and cast(a.contractPosition.contragentRealEstate.id text)=\'' + renterRE.getRenterRecord().getId().toString() + '\' and (' + sqlQuery + ')) ';

if (actual == 0) {

fullSql = sqlQuery;

actual = 1;

} else fullSql = fullSql + ' or ' + sqlQuery;

}

}

}

}

if (actual == 0) {

fullSql = 'select e from rtneo$Accrual e where 1 = 0';

} else {

fullSql = 'select a from rtneo$Accrual a where ' + fullSql + 'order by a.period';

}

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

.query(fullSql)

.view("accrual-edit")

.list();

// for (int l = 0; l < accruals.size(); l++) {

// log.debug(accruals.get(l));

// }

return accruals;

}

def getNN(BigDecimal value) {

return value == null ? BigDecimal.ZERO : value;

}

public List<Accrual> getContragentBillsLight(UUID contragentID) {

String sqlQuery = 'select e from rtneo$Accrual e where 1=0';

List<Accrual> accruals = new ArrayList<Accrual>();

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

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

.parameter("contragent", contragentID)

.view("_local")

.list();

if (contracts.size() > 0) {

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

Contract lastContract = null;

for (Contract contract : contracts) {

if (lastContract == null) {

lastContract = contract;

sqlQuery = 'cast(a.contractPosition.contract.id text)=\'' + contract.getId().toString() + '\' ';

} else {

if (contract.getFrom().before(lastContract.getFrom())) {

sqlQuery = sqlQuery + ' or (cast(a.contractPosition.contract.id text)=\'' + contract.getId().toString() + '\' and a.period < \'' + sdf.format(lastContract.getFrom()) + '\' )';

lastContract = contract;

}

}

}

sqlQuery = 'select a from rtneo$Accrual a where a.contragent.id = :contragentID and (' + sqlQuery + ') order by a.period';

accruals = dataManager.load(Accrual.class)

.query(sqlQuery)

.parameter("contragentID", contragentID)

.view("_local")

.list();

}

return accruals;

}

/**

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

*/

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