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 java.text.SimpleDateFormat;
import com.haulmont.bali.util.ParamsMap;
import com.haulmont.cuba.core.global.PersistenceHelper;
/**
*
*/
def df = new SimpleDateFormat("dd-MM-yyyy")
CommitContext cctx = new CommitContext();
Map<String, Object> instance = ParamsMap.of('df', df, 'cctx', cctx)
/** Исправление позиции где ее период меньше периода договора */
////Получаем позиции где ее период меньше периода договора
//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 from rtneo$RealEstateRenter r left join r.renterRecord rr where r.payOwner = true order by r.contragentRealEstate.contragent.id'
return dataManager.loadValues(q)
.properties("lCreId", "creCad", "lId", "lPA", "tCreId", "tId", "tPA", "vFrom", "vTo")
.firstResult(i*100)
.maxResults(100)
.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 analizeRenters(List<KeyValueEntity> res, Map<String, Object> i){
SimpleDateFormat df = i.df
// ("lCreId", "lId", "tCreId", "tId", "vFrom", "vTo")
//select a from rtneo$Accrual a where a.contragent.id = '001231b4-3b88-1441-7cd8-42643af28383' and a.contractPosition.contract.accepted = true and (a.contractPosition.contragentRealEstate.contragent.id = '151a9f03-aca9-c233-7268-746c5fd65962' or a.contractPosition.contract.isFactRecalculation = true and a.contractPosition.contragentRealEstate.id = '06a06953-a5e8-2ad0-5cc9-9d732712949b')
BillService billService = AppBeans.get(BillService.NAME)
Calendar start = Calendar.getInstance()
Calendar end = Calendar.getInstance()
def prev = null
def prevRent = null
List<Accrual> accruals = new ArrayList<>()
List<Accrual> accrualsRec = new ArrayList<>()
for(KeyValueEntity item : res){
start.setTime(item.getValue('vFrom') == null ? df.parse('01-01-2019') : item.getValue('vFrom'))
end.setTime(item.getValue('vTo') == null ? df.parse('01-12-2020') : item.getValue('vTo'))
start.set(Calendar.DAY_OF_MONTH, 1)
end.set(Calendar.DAY_OF_MONTH, 1)
if(prev == null || !prev.equals(item.getValue('lId'))){
prev = item.getValue('lId')
accruals = billService.getContragentBills(item.getValue('lId'))
// accruals = dataManager.load(Accrual.class)
// .queru('select a from rtneo$Accrual a where a.contragent.id = :lId and a.contractPosition.contract.accepted = true and '
// +'(a.contractPosition.contragentRealEstate.contragent.id = :tCreId or a.contractPosition.contract.isFactRecalculation = true and a.contractPosition.contragentRealEstate.id = :lCreId)')
// .parameter("lId")
// .parameter("tCreId")
// .parameter("lCreId")
// .view("_local")
// .list()
}
//Не проверяет на факте ли сам объект
accrualsRec = dataManager.load(Accrual.class)
.query('select a from rtneo$Accrual a where a.contragent.id = :lId and a.contractPosition.contract.accepted = true and (a.contractPosition.contract.isFactRecalculation = true or a.contractPosition.contract.isFactCalculation = true) and a.contractPosition.contragentRealEstate.id = :lCreId')
.parameter("lId", item.getValue('lId'))
.parameter("lCreId", item.getValue('lCreId'))
.view("_local")
.list()
List<String> periods = new ArrayList<>()
while(start.getTime().compareTo(end.getTime()) <= 0){
def period = df.format(start.getTime())
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", start.getTime())
)) > 0
Boolean isRec = accrualsRec.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'))}
//Месец нужно прибавить сразу, чтобы не зациклилось
start.add(Calendar.MONTH, 1)
periods.add(period)
if(isFact){
// _("IsFact|${item.getValue('lPA')}|${item.getValue('tPA')}|${item.getValue('creCad')}|${period}")
continue
}
if(isRec){
continue
}
if(isAcc){
continue
}
}
if(periods.size() > 0){
long count
// if(prevRent == null || !prevRent.equals(item.getValue('tId'))){
// prevRent = item.getValue('tId')
// count = 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'))
// ))
// }
//
// if(count <= 1){
// disablePayOwner(item.getValue('tCreId'), i)
// _("|${item.getValue('lPA')}|${item.getValue('tPA')}|${count}|${item.getValue('creCad')}|${periods.each{it}}")
//// _("No match: Contragent - ${item.getValue('lPA')}, Renter - ${item.getValue('tPA')}, period - ${periods.each{it}}")
// }
_("|${item.getValue('lPA')}|${item.getValue('tPA')}|${item.getValue('creCad')}|${periods.each{it}}")
// _("No match: Contragent - ${item.getValue('lPA')}, Renter - ${item.getValue('tPA')}, period - ${periods.each{it}}")
}
}
}
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)
Calendar start = Calendar.getInstance()
Calendar end = Calendar.getInstance()
def prev = null
def prevRent = null
def prevAccRent = null
List<Accrual> accruals = new ArrayList<>()
List<Accrual> accrualsFact = new ArrayList<>()
List<Accrual> accrualsRent = new ArrayList<>()
start.setTime(df.parse('01-01-2019'))
end.setTime(df.parse('01-12-2020'))
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(item.getValue('vFrom') == null ? df.parse('01-01-2019') : item.getValue('vFrom'))
end.setTime(item.getValue('vTo') == null ? df.parse('01-12-2020') : item.getValue('vTo'))
start.set(Calendar.DAY_OF_MONTH, 1)
end.set(Calendar.DAY_OF_MONTH, 1)
if(prev == null || !prev.equals(item.getValue('lId'))){
prev = item.getValue('lId')
accruals = billService.getContragentBills(item.getValue('lId'))
}
if(prevAccRent == null || !prevAccRent.equals(item.getValue('tId'))){
prevAccRent = item.getValue('tId')
accrualsRent = billService.getContragentBills(item.getValue('tId'))
}
//Не проверяет на факте ли сам объект
// accrualsFact = dataManager.load(Accrual.class)
// .query('select a from rtneo$Accrual a where a.contragent.id = :lId and a.contractPosition.contract.accepted = true and (a.contractPosition.contract.isFactRecalculation = true or a.contractPosition.contract.isFactCalculation = true) and a.contractPosition.contragentRealEstate.id = :lCreId')
// .parameter("lId", item.getValue('lId'))
// .parameter("lCreId", item.getValue('lCreId'))
// .view("_local")
// .list()
List<String> periods = new ArrayList<>()
List<String> payL = new ArrayList<>()
List<String> payT = new ArrayList<>()
while(start.getTime().compareTo(end.getTime()) <= 0){
def period = df.format(start.getTime())
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", start.getTime())
)) > 0
// 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 sumInRent = accrualsRent.stream()
.filter{e -> e.getPeriod().compareTo(start.getTime()) == 0 && (e.getContractPosition().getContragentRealEstate() != null && e.getContractPosition().getContragentRealEstate().getId() == item.getValue('tCreId'))}
.map{p -> p.getTotalSum()}.reduce{u1, u2 -> u1.add(u2)}.orElse(null)
payT.add(sumInRent)
def sumAcc = accruals.stream()
.filter{e -> e.getPeriod().compareTo(start.getTime()) == 0 && (e.getContractPosition().getContragentRealEstate() != null && e.getContractPosition().getContragentRealEstate().getId() == item.getValue('tCreId'))}
.map{p -> p.getTotalSum()}.reduce{u1, u2 -> u1.add(u2)}.orElse(null)
//Месец нужно прибавить сразу, чтобы не зациклилось
start.add(Calendar.MONTH, 1)
if(isFact){
// _("IsFact|${item.getValue('lPA')}|${item.getValue('tPA')}|${item.getValue('creCad')}|${period}")
payL.add("fact")
continue
}
// if(isRec){
// payL.add("!Recfact")
// continue
// }
if(sumAcc > 0){
payL.add(sumAcc)
continue
}
if(sumAcc == 0){
payL.add(0)
continue
}
payL.add("null")
periods.add(period)
}
if(periods.size() > 0){
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 l = "|${item.getValue('lPA')}|${item.getValue('tPA')}|${countL}|${item.getValue('creCad')}"
payL.each{l+='|'+it}
String r = "|-------------|-------------|${countR}|-------------"
payT.each{r+='|'+it}
_(l)
_(r)
// _("|${item.getValue('lPA')}|${item.getValue('tPA')}|${countL}|${item.getValue('creCad')}|${payL.each{'|'+it.toString()}}")
// _("|-------------|-------------|${countR}|-------------|${payT.each{'|'+it.toString()}}")
}
}
}
/**
* Methods
*
*/
//Устанавливаем период позиций равный дате начала договора
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)
}
/**
* Логирование
*/
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)}
