import com.haulmont.cuba.core.*

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

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

import com.groupstp.rtneo.entity.*;

import com.groupstp.rtneo.service.*;

import com.groupstp.rtneo.core.bean.tools.*;

import com.groupstp.rtneo.core.bean.calculation.*;

import java.text.SimpleDateFormat;

import com.haulmont.bali.util.ParamsMap;

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

/**

*

*/

DatePeriodTools periodTools = AppBeans.get(DatePeriodTools.class)

def df = new SimpleDateFormat("dd-MM-yyyy")

CommitContext cctx = new CommitContext();

Map<String, Object> instance = ParamsMap.of('df', df, 'cctx', cctx, 'periodTools', periodTools)

/** Исправление позиции где ее период меньше периода договора */

////Получаем позиции где ее период меньше периода договора

//List<KeyValueEntity> res = resDifferencePeriodCPandContract()

////Устанавливаем период позиций равный дате начала договора

//setCPPeriodAsContractFrom(res, instance)

////Вывод позиции где ее период меньше периода договора

//analizeDifferencePeriodCPandContract(res, df)

/** Исправление позиции где ее период меньше периода договора */

//List<KeyValueEntity> res = resDiscountAccrual()

//fixDiscountAccrual(res, instance)

/** Анализ и исправление арендаторов */

List<KeyValueEntity> res = resRenters()

analizeRentersSum(res, instance)

//_(res.size())

//Строка лога не корректных тарифов в начислениях

//res.each{_("pa - ${it.getValue('pa')}, ctNumber - ${it.getValue('ctNumber')}, aPeriod - ${it.getValue('aPeriod')}, aPrice - ${it.getValue('aPrice')}, correctPrice - ${it.getValue('correctPrice')}, aTotalSum - ${it.getValue('aTotalSum')}, aUpdate - ${it.getValue('aUpdate')}")}

_(cctx.getCommitInstances().size())

dataManager.commit(cctx)

/**

*Main resource data

*

*/

//Контрагенты у которых период позиции меньше чем дата договора

def resDifferencePeriodCPandContract(){

//Все

String q = 'select distinct cp.id, cp.contract.id, cp.contragent.personalAccount, cp.contract.number, cp.period, cp.contract.from, (select min(a.period) from rtneo$Accrual a where a.contractPosition = cp), cp.contract.accepted from rtneo$ContractPosition cp where cp.period < cp.contract.from order by cp.contragent.personalAccount, cp.contract.number'

//С количеством оплат

// String q = 'select distinct cp.id, cp.contract.id, cp.contragent.personalAccount, cp.contract.number, cp.period, cp.contract.from, (select min(a.period) from rtneo$Accrual a where a.contractPosition = cp), cp.contract.accepted, (select count(p) from rtneo$Payment p where p.inn = cp.contragent.inn) from rtneo$ContractPosition cp where cp.period < cp.contract.from order by cp.contragent.personalAccount, cp.contract.number'

// Только с оплатами

// String q = 'select distinct cp.id, cp.contract.id, cp.contragent.personalAccount, cp.contract.number, cp.period, cp.contract.from, (select min(a.period) from rtneo$Accrual a where a.contractPosition = cp), cp.contract.accepted from rtneo$Payment p join rtneo$ContractPosition cp on p.inn = cp.contragent.inn where cp.period < cp.contract.from order by cp.contragent.personalAccount, cp.contract.number'

return dataManager.loadValues(q)

.properties("cpId", "ctId", "pa", "ctNumber", "period", "ctFrom", "minAccrual", "cAccepted")

.list()

}

//Контрагенты у которых период начисления меньше чем дата договора

def resDifferencePeriodAccrualAndContract(){

//Все

String q = 'select a.contragent.personalAccount, a.id, a.period, a.contractPosition.contract.from from rtneo$Accrual a where a.period < a.contractPosition.contract.from order by a.contragent.personalAccount'

return dataManager.loadValues(q)

.properties("pa", "aId", "aPeriod", "ctFrom")

.list()

}

//Начисления где не корректный тариф//не верный запрос

def resDifferencePriceAccrual(){

//Все

// String q = 'select a.id, a.contragent.personalAccount, a.contractPosition.contract.number, a.documentNumber, a.period, a.price, (select t.tariff from rtneo$Tariff t where t.name = 1 and a.period between t.dateSince and t.dateTill)*1.2, a.totalSum from rtneo$Accrual a where a.period > a.contractPosition.contract.before and (select t.tariff from rtneo$Tariff t where t.name = 1 and a.period between t.dateSince and t.dateTill)*1.2 <> a.price order by a.contragent.personalAccount'

//updateTs

String q = 'select a.id, a.contragent.personalAccount, a.contractPosition.contract.number, a.documentNumber, a.period, a.price, (select t.tariff from rtneo$Tariff t where t.name = 1 and a.period between t.dateSince and t.dateTill)*1.2, a.totalSum, a.updatedBy from rtneo$Accrual a where a.period > a.contractPosition.contract.before and (select t.tariff from rtneo$Tariff t where t.name = 1 and a.period between t.dateSince and t.dateTill)*1.2 <> a.price order by a.contragent.personalAccount'

return dataManager.loadValues(q)

.properties("aId", "pa", "ctNumber", "anumber", "aPeriod", "aPrice", "correctPrice", "aTotalSum", "aUpdate")

.list()

}

//Начисления со скидкой

def resDiscountAccrual(){

String q = 'select distinct a.contragent.id from rtneo$Accrual a where a.totalSum <> a.totalSumBase and a.contractPosition.contract.accepted = true'

return dataManager.loadValues(q)

.properties("cId")

.maxResults(10)

.list()

}

//

def resRenters(){

def i=0

String q = 'select r.contragentRealEstate.id, r.contragentRealEstate.realEstate.cadastralNumber, r.contragentRealEstate.contragent.id, r.contragentRealEstate.contragent.personalAccount, rr.id, rr.contragent.id, rr.contragent.personalAccount, rr.validityFrom, rr.validityTo, (select max(c.before) from rtneo$Contract c where c.contragent.id = r.contragentRealEstate.contragent.id and c.accepted = true), (select max(c.before) from rtneo$Contract c where c.contragent.id = r.renterRecord.contragent.id and c.accepted = true) from rtneo$RealEstateRenter r left join r.renterRecord rr where r.payOwner = true order by r.contragentRealEstate.contragent.id'

// String q = 'select r.contragentRealEstate.id, r.contragentRealEstate.realEstate.cadastralNumber, r.contragentRealEstate.contragent.id, r.contragentRealEstate.contragent.personalAccount, rr.id, rr.contragent.id, rr.contragent.personalAccount, rr.validityFrom, rr.validityTo from rtneo$RealEstateRenter r left join r.renterRecord rr where r.contragentRealEstate.contragent.personalAccount = \'ААА3812525543\' and r.payOwner = true order by r.contragentRealEstate.contragent.id'

return dataManager.loadValues(q)

.properties("lCreId", "creCad", "lId", "lPA", "tCreId", "tId", "tPA", "vFrom", "vTo", "lPeriodMax", "tPeriodMax")

// .firstResult(i*1000)

// .maxResults(1000)

.list()

}

/**

*Second resource data

*

*/

def sresActualAccruals(){

Persistence persistence= AppBeans.get(Persistence.NAME)

Transaction tr = persistence.createTransaction()

EntityManager em = persistence.getEntityManager();

Query query = em.createNativeQuery("with\n" +

"periods as(\n" +

"SELECT \n" +

" distinct a.period as per\n" +

"FROM \n" +

" public.rtneo_contragent c, \n" +

" public.rtneo_contract ct, \n" +

" public.rtneo_contract_position cp, \n" +

" public.rtneo_accrual a\n" +

"WHERE \n" +

" ct.contragent_id = c.id AND\n" +

" cp.contract_id = ct.id AND\n" +

" a.contract_position_id = cp.id AND\n" +

" c.personal_account = 'ААА3812525543'\n" +

" and a.delete_ts isnull\n" +

" order by a.period),\n" +

"contracts as(\n" +

"select\n" +

"p.per as perct,\n" +

"(select ct.id\n" +

"from \n" +

"public.rtneo_contract ct join\n" +

"public.rtneo_contragent c on c.id = ct.contragent_id\n" +

"where\n" +

"c.personal_account = 'ААА3812525543'\n" +

"and ct.create_ts = \n" +

"(select\n" +

"max(ct.create_ts)\n" +

"from \n" +

"public.rtneo_contract ct join\n" +

"public.rtneo_contragent c on c.id = ct.contragent_id\n" +

"where\n" +

"c.personal_account = 'ААА3812525543'\n" +

" and ct.accepted = true\n" +

" and ct.delete_ts is null\n" +

"and p.per between ct.from_ and ct.before_)\n" +

") as contract\n" +

"from \n" +

"periods p)\n" +

"\n" +

"SELECT\n" +

"a.period, a.document_number\n" +

"FROM \n" +

" contracts ct,\n" +

" rtneo_accrual a left join\n" +

" public.rtneo_contract_position cp\n" +

" on a.contract_position_id = cp.id\n" +

"where\n" +

" a.period = ct.perct\n" +

" and ct.contract = cp.contract_id\n" +

" and a.delete_ts is null\n" +

"order by a.period\n" +

"limit 200");

//query.setParameter(1, "%Company%");

List list = query.getResultList();

for (Iterator it = list.iterator(); it.hasNext(); ) {

Object[] row = (Object[]) it.next();

def per = row[0];

def name = row[1];

_("${per} = ${name}")

}

}

/**

*Analize method

*

*/

void analizeDifferencePeriodCPandContract(List<KeyValueEntity> res, SimpleDateFormat df){

String prev = null

res.each{

// String str = "pa - ${it.getValue('pa')}, ctNumber - ${it.getValue('ctNumber')}, cpPeriod - ${df.format(it.getValue('period'))}, ctFrom - ${df.format(it.getValue('ctFrom'))}, minAccrual - ${it.getValue('minAccrual')}, cAccepted - ${it.getValue('cAccepted')}"

String str = "|${it.getValue('pa')}|${it.getValue('ctNumber')}|${df.format(it.getValue('period'))}|${df.format(it.getValue('ctFrom'))}|${it.getValue('minAccrual')}|${it.getValue('cAccepted')}|"

if(prev == null || !prev.equals(str)){

prev = str

_(str)

}

}

}

void analizeRentersSum(List<KeyValueEntity> res, Map<String, Object> i){

SimpleDateFormat df = i.df

// ("lCreId", "lId", "tCreId", "tId", "vFrom", "vTo")

BillService billService = AppBeans.get(BillService.NAME)

Date dStart = df.parse('01-01-2019')

Date dStop = df.parse('01-12-2020')

Calendar start = Calendar.getInstance()

Calendar end = Calendar.getInstance()

start.setTime(dStart)

end.setTime(dStop)

def prev = null

def prevRent = null

def prevAccRent = null

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

List<Accrual> accrualsFact = new ArrayList<>()

List<Accrual> accrualsRent = new ArrayList<>()

String caption = "|Контрагент|Арендатор|Договоров|Конец действия|Кад. номер"

while(start.getTime().compareTo(end.getTime()) <= 0){

caption += "|${df.format(start.getTime())}"

start.add(Calendar.MONTH, 1)

}

_(caption)

for(KeyValueEntity item : res){

start.setTime(dStart)

end.setTime(dStop)

start.set(Calendar.DAY_OF_MONTH, 1)

end.set(Calendar.DAY_OF_MONTH, 1)

accruals = getContragentBillsCRE(item.getValue('lId'), item.getValue('tCreId'))

accrualsRent = getContragentBillsCRE(item.getValue('tId'), item.getValue('tCreId'))

List<String> periods = new ArrayList<>()

HashMap<Date, Object> payLm = new HashMap<>()

HashMap<Date, Object> payTm = new HashMap<>()

while(start.getTime().compareTo(end.getTime()) <= 0){

def period = start.getTime()

payLm.putIfAbsent(period, null)

payTm.putIfAbsent(period, null)

//Месец нужно прибавить сразу, чтобы не зациклилось

start.add(Calendar.MONTH, 1)

if(!i.periodTools.isPeriodIncluded(period, item.getValue('vFrom'), item.getValue('vTo'))){

payTm[period] = ''

payLm[period] = ''

continue

}

Boolean isFact = dataManager.getCount(LoadContext.create(Contract.class).setQuery(

LoadContext.createQuery('select c from rtneo$ContractPosition c where c.contragent.id = :lId and c.contragentRealEstate.id = :lCreId and (c.isFactCalculation = true or c.isFactRecalculation = true) and c.contract.accepted = true and c.period <= :period')

.setParameter("lCreId", item.getValue('lCreId'))

.setParameter("lId", item.getValue('lId'))

.setParameter("period", period)

)) > 0

//Сумма для арендаторов записывается даже при факте арендодателя

def sumInRent = accrualsRent.stream()

.filter{e -> e.getPeriod().compareTo(period) == 0 && (e.getContractPosition().getContragentRealEstate() != null && e.getContractPosition().getContragentRealEstate().getId() == item.getValue('tCreId'))}

.map{p -> p.getTotalSum()}.reduce{u1, u2 -> u1.add(u2)}.orElse(null)

payTm[period] = sumInRent

if(isFact){

payLm[period] = "fact"

continue

}

// Boolean isRec = accrualsFact.stream().anyMatch{e -> e.getPeriod().compareTo(start.getTime()) == 0}

// Boolean isAcc = accruals.stream().anyMatch{e -> e.getPeriod().compareTo(start.getTime()) == 0 && (e.getContractPosition().getContragentRealEstate() != null && e.getContractPosition().getContragentRealEstate().getId() == item.getValue('tCreId'))}

def sumAcc = accruals.stream()

.filter{e -> e.getPeriod().compareTo(period) == 0 && (e.getContractPosition().getContragentRealEstate() != null && e.getContractPosition().getContragentRealEstate().getId() == item.getValue('tCreId'))}

.map{p -> p.getTotalSum()}.reduce{u1, u2 -> u1.add(u2)}.orElse(null)

if(sumAcc > 0){

payLm[period] = sumAcc

continue

}

if(sumAcc == 0){

payLm[period] = 0

continue

}

periods.add(period)

}

long countL = dataManager.getCount(LoadContext.create(Contract.class).setQuery(

LoadContext.createQuery('select c from rtneo$Contract c where c.contragent.id = :id and c.accepted = true').setParameter("id", item.getValue('lId'))

))

long countR = dataManager.getCount(LoadContext.create(Contract.class).setQuery(

LoadContext.createQuery('select c from rtneo$Contract c where c.contragent.id = :id and c.accepted = true').setParameter("id", item.getValue('tId'))

))

String lStart = "|${item.getValue('lPA')}|-------------|${countL}|${item.getValue('lPeriodMax')}|${item.getValue('creCad')}"

String rStart = "|-------------|${item.getValue('tPA')}|${countR}|${item.getValue('tPeriodMax')}|-------------"

String l = ''

String r = ''

start.setTime(dStart)

end.setTime(dStop)

Boolean isIncorrect = false

Date twoYear = df.parse('01-01-2020')

while(start.getTime().compareTo(end.getTime()) <= 0){

Boolean inReport = false

Date currPeriod = start.getTime()

start.add(Calendar.MONTH, 1)

// if(payLm[currPeriod] == null && payTm[currPeriod] == null)inReport = true

// if(payLm[currPeriod] == 0 && payTm[currPeriod] == null)inReport = true

// if(payLm[currPeriod].equals('fact') || payTm[currPeriod].equals('fact'))inReport = false

// if((payLm[currPeriod] instanceof BigDecimal && payLm[currPeriod] > 0) || (payTm[currPeriod] instanceof BigDecimal && payTm[currPeriod] > 0))inReport = false

// if((payLm[currPeriod] instanceof String && payLm[currPeriod].equals('')) && (payTm[currPeriod] instanceof String && payTm[currPeriod].equals('')))inReport = false

if(payLm[currPeriod].equals('fact') && (payTm[currPeriod] instanceof BigDecimal && payTm[currPeriod] > 0))inReport = true

if(start.getTime().compareTo(twoYear) >= 0)inReport = false

if(inReport)isIncorrect = true

l+='|' + payLm[currPeriod]

r+='|' + payTm[currPeriod]

}

if(isIncorrect){

String lINN = dataManager.loadValue('select c.inn from rtneo$Contragent c where c.personalAccount = :pa and (select count(p) from rtneo$Payment p where p.inn = c.inn) > 0', String.class)

.parameter("pa", item.getValue('lPA'))

.optional()

.orElse('')

String tINN = dataManager.loadValue('select c.inn from rtneo$Contragent c where c.personalAccount = :pa and (select count(p) from rtneo$Payment p where p.inn = c.inn) > 0', String.class)

.parameter("pa", item.getValue('tPA'))

.optional()

.orElse('')

if(!lINN.equals(''))_(lINN)

if(!tINN.equals(''))_(tINN)

// _(lStart+l)

// _(rStart+r)

}

}

}

/**

* Methods

*

*/

def prolongation(Date from, Date to, Contragent contragent, Date dateBill){

CalculationWorkerBean calculationWorker = AppBeans.get(CalculationWorkerBean.class);

Contract baseContract = dataManager.load(Contract.class)

.query('select c from rtneo$Contract c where c.mainContract is null and c.contragent.id=:contragent order by c.createTs desc')

.parameter("contragent", contragent.getId())

.view("contract-edit")

.optional()

.orElse(null);

if(baseContract == null)return false

Contract lastContract = getLastContract(contragent, baseContract);

if(lastContract == null)lastContract = baseContract;

return calculationWorker.calculateAccruals(from, to, contragent, dateBill, lastContract, false);

}

public Contract getLastContract(Contragent contragent, Contract baseContract){

return dataManager.load(Contract.class)

.query('select c from rtneo$Contract c where c.mainContract.id = :id and c.accepted = true and ( c.isFactRecalculation is null or not c.isFactRecalculation = true ) order by c.number desc')

.parameter("id", baseContract.getId())

.view("contract-edit")

.firstResult(0)

.maxResults(1)

.optional().orElse(null);

}

//Устанавливаем период позиций равный дате начала договора

void setCPPeriodAsContractFrom(List<KeyValueEntity> res, Map<String, Object> i){

Calendar cal = Calendar.getInstance();

def df = i.df

CommitContext cctx = i.cctx

for(KeyValueEntity item : res){

if(item.getValue('ctFrom').compareTo(item.getValue('period')) > 0){

_("PA - ${item.getValue('pa')}, Position - ${df.format(item.getValue('period'))}, Contract from - ${df.format(item.getValue('ctFrom'))}, Period accrual - ${item.getValue('minAccrual') == null ? null : df.format(item.getValue('minAccrual'))}")

ContractPosition cp = dataManager.load(ContractPosition.class).id(item.getValue('cpId')).view('_local').optional().orElse(null)

cal.setTime(item.getValue('ctFrom'))

cal.set(Calendar.DAY_OF_MONTH, 1)

cp.setPeriod(cal.getTime())

if(!item.getValue('minAccrual').equals(null) && item.getValue('period').compareTo(item.getValue('minAccrual')) < 0){

_('Можно обрезать')

i.cctx.addInstanceToCommit(cp)

}

if(item.getValue('minAccrual').equals(null)){

_("Можно обрезать, начислений нет ${item.getValue('ctNumber')}")

i.cctx.addInstanceToCommit(cp)

}

}

}

// dataManager.commit(cctx)

}

void fixDiscountAccrual(List<KeyValueEntity> res, Map<String, Object> i){

CommitContext cctx = i.cctx

BillService billService = AppBeans.get(BillService.NAME)

for(KeyValueEntity item : res){

List<Accrual> accruals = billService.getContragentBills(item.getValue('cId'))

for(Accrual accrual : accruals){

if(!accrual.getTotalSum().equals(accrual.getTotalSumBase())){

_("!!!!!${accrual.getTotalSum()} <> ${accrual.getTotalSumBase()}")

}

}

}

}

void disablePayOwner(UUID id, Map<String, Object> i){

def rr = dataManager.load(RealEstateRenter.class)

.query('select r from rtneo$RealEstateRenter r where r.renterRecord.id = :id')

.parameter("id", id)

.view("_local")

.optional()

.orElse(null)

rr.setPayOwner(false)

i.cctx.addInstanceToCommit(rr)

}

List<Accrual> getContragentBillsCRE(UUID contragentID,UUID creId)

{

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

.parameter("contragent", contragentID)

.view("_base")

.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.contractPosition.contragentRealEstate.id=:creId and a.contragent.id = :contragentID and (' + sqlQuery + ') order by a.period';

accruals = dataManager.load(Accrual.class)

.query(sqlQuery)

.parameter("contragentID", contragentID)

.parameter("creId", creId)

.view("accrual-full")

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