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=1
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
def c = 0
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((payTm[currPeriod] instanceof BigDecimal && payTm[currPeriod] == 0 && currPeriod.compareTo(twoYear) >=0) && (payLm[currPeriod] == null && currPeriod.compareTo(twoYear) >=0))inReport = true
if(item.getValue('tPeriodMax') != null && item.getValue('tPeriodMax').compareTo(twoYear) >= 0)inReport = false
if(inReport)isIncorrect = true
l+='|' + payLm[currPeriod]
r+='|' + payTm[currPeriod]
}
if(isIncorrect){
List<Accrual> pAccruals = prolongation(twoYear, end.getTime(), dataManager.load(Contragent.class).id(item.getValue('tId')).view('contragent-calculations').optional().orElse(null), twoYear)
_(lStart+l)
_(rStart+r)
pAccruals.each{_(it)}
}
}
}
/**
* 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)}
