import java.util.*
import com.haulmont.cuba.core.global.*
import com.haulmont.cuba.core.entity.*;
import com.groupstp.rtneo.entity.*
import com.haulmont.cuba.core.entity.FileDescriptor
import com.haulmont.cuba.core.app.FileStorageAPI
import java.io.InputStream
import java.io.InputStreamReader
import java.io.ByteArrayOutputStream
import org.apache.commons.io.IOUtils
import java.nio.charset.StandardCharsets;
import com.opencsv.CSVReader;
import com.groupstp.rtneo.service.BillService
import java.text.SimpleDateFormat
import org.apache.commons.collections.CollectionUtils
import java.util.stream.Collectors;
import com.groupstp.rtneo.core.bean.tools.*
long st = System.currentTimeMillis();
def df1 = new SimpleDateFormat("yyyy-MM-dd")
def dateView = new SimpleDateFormat("dd-MM-yyyy")
def period1 = '2019-01-01'
def period2 = '2019-12-01'
def paymentLimit = '2020-07-27'
def contractLimit = '2020-07-27'
//def prolongationLimit1 = df1.parse('2020-07-08')
//def prolongationLimit2 = df1.parse('2020-07-09')
FileStorageAPI fileStorageAPI=AppBeans.get(FileStorageAPI.NAME);
BillService billService = AppBeans.get(BillService.NAME)
DatePeriodTools datePeriodTools = AppBeans.get(DatePeriodTools.class)
//Контрагенты из файла
//String id='52513a70-421d-4d9b-bee3-1575f58d7e0a'
//List<String> listInn = inToFile(id)
//
String idEx='35749000-97f9-deb3-0183-518eab9cfbc4'
List<String> listExInn = inToFile(idEx)
//List<String> listExInn = new ArrayList<>()
//
//List<String> unique = new ArrayList<>()
//
//for(String item : listInn){
// if(listExInn.stream().anyMatch{e -> e.equals(item)})continue
// unique.add(item)
//}
def tariffs = dataManager.loadValues('select t.tariff*1.2, t.dateSince, t.dateTill from rtneo$Tariff t where t.name = 1')
.properties("tariff", "from", "to")
.list()
Calendar periodEnd = Calendar.getInstance();
periodEnd.setTime(df1.parse(period2));
Date paymentLimitDate = df1.parse(paymentLimit)
Calendar beginYear = Calendar.getInstance();
beginYear.setTime(df1.parse("2019-01-01"));
def i = 0;
//ААА3801011440
//ААА3801012412
//Ф380101342472
long stCLoad = System.currentTimeMillis();
def contragents = dataManager
.loadValues('select distinct c.id, 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 e.createTs <= :paymentLimitDate and not c.personalAccount is null and size(c.contracts) > 0 order by c.inn')
// .loadValues('select distinct c.id, 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 e.createTs <= :paymentLimitDate and c.personalAccount = \'Ф381900407640\' order by c.inn')
.properties("id", "inn", "kpp", "pa")
.firstResult(i*2000)
.maxResults(2000)
.parameter("paymentLimitDate", paymentLimitDate)
// .parameter("exclude", listExInn)
.list();
_(">> Contragents load = ${System.currentTimeMillis()-stCLoad}")
def accrualsGroup = new TreeMap<>();
def contragentsGroup = new TreeMap<>();
//Коллекция исключенных объектов
Map<String, String> excluded = new HashMap<>()
//Формируем структуру данных
for(def contragent : contragents){
//Исключаем контрагентов с филиалами
if(contragents.stream().filter{e->e.getValue('inn') == contragent.getValue('inn')}.count() > 1){
excluded.putIfAbsent(contragent.getValue('inn'), "Filial")
continue;
}
if(listExInn.stream().anyMatch{e -> e.equals(contragent.getValue('inn'))}){
excluded.putIfAbsent(contragent.getValue('inn'), "exclude")
continue;
}
//------------------------------------------------
//Исключаем контрагентов у кого есть арендаторы за которых он платит, не из БТ и нет перерасчета по факту за 2019 год
def isRent = dataManager.loadValue('select count(r) from rtneo$RealEstateRenter r where r.contragentRealEstate.contragent.id = :contragent and r.payOwner = true and (select count(c) from rtneo$Contract c where c.contragent.id = r.contragentRealEstate.contragent.id and (c.loadedFromBigTrio = true or (c.accepted = true and c.isFactRecalculation = true))) = 0', Integer.class)
.parameter("contragent", contragent.getValue('id'))
.optional().orElse(0)
// if(isRent > 0){
// excluded.putIfAbsent(contragent.getValue('inn'), "Rent")
// continue
// }
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;
if(contragentsGroup[contragent.getValue('inn')] == null){
contragentsGroup[contragent.getValue('inn')] =[
'id' : contragent.getValue('id'),
'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 = [:];
beginYear.setTime(df1.parse("2019-01-01"));
Boolean failTariff = false
Boolean actualProlongation = false
Boolean factAccrual = false
Boolean zeroAccrual = false
Boolean minusAccrual = false
long stGetAccruals = System.currentTimeMillis();
while(!beginYear.after(periodEnd)){
List<Accrual> accruals = getBillsOnPeriod(UUID.fromString(contragent.getValue('id').toString()), beginYear.getTime())
def tariff = tariffs.stream().filter{e-> datePeriodTools.isPeriodIncluded(beginYear.getTime(), e.getValue('from'), e.getValue('to'))}.findFirst().orElse(null)
BigDecimal roundingTarif = new BigDecimal(tariff.getValue('tariff')).setScale(5, BigDecimal.ROUND_HALF_UP)
for(def accrual : accruals){
// if(!accrual.getPrice().equals(roundingTarif)){
// failTariff = true
// }
if(accrual.getPrice().equals(new BigDecimal("594.52800"))){
failTariff = true
}
//
// if(accrual.getComment().equals("По факту")){
// factAccrual = true
// continue
// }
// if(accrual.getCreateTs().compareTo(prolongationLimit1) == 1 && accrual.getCreateTs().compareTo(prolongationLimit2) == -1 && accrual.getCreatedBy().equals("a.kotvinskiy@groupstp.ru")){
// actualProlongation = true
// }
// BigDecimal sum = accruals.stream()
// .map{a -> a.getTotalSum()}
// .reduce{u1, u2 -> u1.add(u2)}
// .orElse(0);
// if(sum == 0){
// zeroAccrual = true
// }
// if(sum < 0){
// minusAccrual = true
// }
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()),
'nds' : getNN(accrual.getNds()),
'residue' : 0,
'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())
}
}
beginYear.add(Calendar.MONTH, 1)
}
_(">> Accruals load = ${System.currentTimeMillis()-stGetAccruals}")
if(failTariff){
excluded.putIfAbsent(contragent.getValue('inn'), "Fail tariff 594.52800")
continue
}
// if(factAccrual){
// excluded.putIfAbsent(contragent.getValue('inn'), "Faact accrual")
// continue
// }
//
// if(actualProlongation){
// excluded.putIfAbsent(contragent.getValue('inn'), "Actual prolongation")
// continue
// }
// if(zeroAccrual){
// excluded.putIfAbsent(contragent.getValue('inn'), "Zero accrual")
// continue
// }
// if(minusAccrual){
// excluded.putIfAbsent(contragent.getValue('inn'), "Minus accrual")
// continue
// }
accrualsGroup.each{
if(!(it.getValue()['totalSum'].compareTo(it.getValue()['totalSumBase']) == 0)){
BigDecimal noNdsSum = it.getValue()['totalSum']/(100+it.getValue()['nds'])*100
it.getValue().put('amountBase', noNdsSum/it.getValue()['tariff'])
// excluded.putIfAbsent(contragent.getValue('inn'), "discount")
}
}
contragentsGroup[contragent.getValue('inn')]['accruals'] = accrualsGroup;
}
//Выводим исключенные
_("-----------Excluded------------")
excluded.each{_(it)}
_("--------------End--------------")
//Форматируем данные для вывода
def res = [:]
for(def contr : contragentsGroup){
def sumPay = 0
def residue = 0
contr.getValue()['payments'].each{p -> sumPay += p.getValue()['sum']}
res[contr.getValue()['inn']] = [
'id' : contr.getValue()['id'],
'inn' : contr.getValue()['inn'],
'kpp' : contr.getValue()['kpp'],
'pa' : contr.getValue()['pa'],
'sumPayments' : sumPay,
'accruals': [:]
]
// def accrual = []
for(def accrualFor : contr.getValue()['accruals']){
if(sumPay > accrualFor.getValue()['totalSum']){
accrualFor.getValue()['done'] = true
}else{
accrualFor.getValue()['done'] = false
}
if(sumPay >= 0){
residue = sumPay
accrualFor.getValue()['residue'] = residue
}
sumPay -= accrualFor.getValue()['totalSum']
res[contr.getValue()['inn']]['accruals'][accrualFor.getValue()['period']]=accrualFor
}
}
Calendar endMonth = Calendar.getInstance();
endMonth.setTime(df1.parse(period1));
List<String> totalsNullAccrualContract = new ArrayList<>()
HashMap<String, List<Object>> zeroMap = new HashMap<>()
List<Object> zeroList = new ArrayList<>()
List<Object> excledeOutList = new ArrayList<>()
_("!Наименование услуги!Дата договора!Основной договор!Номер УПД!Дата УПД!Дата корр.!ИНН!КПП!ЛС!Объем!Цена без НДС!Сумма без НДС!НДС!Цена с НДС!Оплачен период!Остаток на начало периода")
for(def itemRes : res){
if(excluded.keySet().stream().anyMatch{e -> e.equals(itemRes.getValue()['inn'])})continue
List<Contract> contractList = dataManager.load(Contract.class)
.query('select c from rtneo$Contract c where c.contragent.id = :contragent and c.accepted is not null and c.accepted = true and c.from < :contractLimit order by c.createTs')
.parameter("contragent", itemRes.getValue()['id'])
.parameter("contractLimit", df1.parse(contractLimit))
.view("contract-browse")
.list()
Boolean keyOutput = false
def excledeOut = false
HashMap<String, Object> excludeMap = new HashMap<>()
for(def item : itemRes.getValue()['accruals']){
if(dateView.parse(item.getKey()).compareTo(df1.parse(period1))<0)continue
// _(item)
def total = item.getValue().getValue()
if(dateView.parse(total['period'].toString()).compareTo(dateView.parse('01-01-2020')) == 0 && total['residue'] <= 0 && !total['residue'])excledeOut = true
endMonth.setTime(dateView.parse(total['period'].toString()));
endMonth.set(Calendar.DAY_OF_MONTH, endMonth.getActualMaximum(Calendar.DAY_OF_MONTH))
def month = getTextPeriod(dateView.parse(total['period']))
//Выводим один не оплаченный месяц
if(total['done']!=true && !keyOutput){
keyOutput = true
}else if(total['done']!=true && keyOutput){
continue
}
if(excledeOut){
excludeMap.put("inn", itemRes.getValue()['inn'])
excludeMap.put("kpp", itemRes.getValue()['kpp'])
// excledeOutList.add("!Услуга Регионального оператора по обращению с ТКО за ${month}!!!!${dateView.format(endMonth.getTime())}!!${itemRes.getValue()['inn']}!${itemRes.getValue()['kpp']}!${itemRes.getValue()['pa']}!!!!!!${total['done']==true?'да':'нет'}!${total['residue']}")
}else{
log.debug("!Услуга Регионального оператора по обращению с ТКО за ${month}!!!!${dateView.format(endMonth.getTime())}!!${itemRes.getValue()['inn']}!${itemRes.getValue()['kpp']}!${itemRes.getValue()['pa']}!!!!!!${total['done']==true?'да':'нет'}!${total['residue']}")
}
List<HashMap> itemsList = new ArrayList<>()
for(Contract contract : contractList){
KeyValueEntity totalAccrualContract = dataManager.loadValues('select a.documentNumber, max(a.period), sum(a.amountBase), sum(a.amountBase)*max(a.price), max(a.price), sum(a.ndsSum), sum(a.totalSum), sum(a.totalSumBase) from rtneo$Accrual a where a.contractPosition.contract.id = :contract and a.period = :period group by a.documentNumber')
.properties("number", "period", "amount", "totalSumWithNDS", "price", "totalNDS", "totalSum", "totalSumBase")
.parameter("contract", contract.getId())
.parameter("period",dateView.parse(total['period']))
.optional()
.orElse(null)
// if(totalAccrualContract == null){
// totalsNullAccrualContract.add(itemRes.getValue()['inn'])
// continue
// }
String mainContractNumber = contract.getMainContract() != null ? contract.getMainContract().getNumber() : ''
HashMap<String, Object> mapResults = new HashMap<>()
if(totalAccrualContract == null){
// _("!${contract.getNumber()}!No accruals in to period")
}else{
mapResults.put("cnumber", contract.getNumber())
mapResults.put("cdate", contract.getMainContract()!=null ? '' : dateView.format(contract.getDate()))
mapResults.put("cmnumber", mainContractNumber)
mapResults.put("number", totalAccrualContract.getValue('number'))
mapResults.put("period", dateView.format(endMonth.getTime()))
mapResults.put("dateCor", contract.getMainContract()!=null?dateView.format(contract.getDate()):'')
mapResults.put("amount", totalAccrualContract.getValue('amount'))
mapResults.put("price", totalAccrualContract.getValue('price')*5/6)
def totalNDS = totalAccrualContract.getValue('totalNDS') == null ? BigDecimal.ZERO : totalAccrualContract.getValue('totalNDS')
mapResults.put("totalSumNoNDS", totalAccrualContract.getValue('totalSum') - totalNDS)
mapResults.put("totalNDS", totalNDS)
mapResults.put("totalSum", totalAccrualContract.getValue('totalSum'))
if(!(totalAccrualContract.getValue('totalSum').compareTo(totalAccrualContract.getValue('totalSumBase')) == 0)){
mapResults.put('amount', totalAccrualContract.getValue('totalSum')/totalAccrualContract.getValue('price'))
}
// accrualsGroup.each{
// if(!(it.getValue()['totalSum'].compareTo(it.getValue()['totalSumBase']) == 0)){
// BigDecimal noNdsSum = it.getValue()['totalSum']/(100+it.getValue()['nds'])*100
// it.getValue().put('amountBase', noNdsSum/it.getValue()['tariff'])
// // excluded.putIfAbsent(contragent.getValue('inn'), "discount")
// }
// }
itemsList.add(mapResults)
}
}
Boolean print1Contract = false
Boolean print2Contract = false
if(itemsList.size()>=1){
if(!itemsList.get(0).get('cdate').equals('')){
print1Contract = true
}else{
if(itemsList.size() == 1)print1Contract = true
}
if(itemsList.size()>1){
//для 20го года Сделать условие!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// if(!itemsList.get(0).get('cdate').equals('') && dateView.parse(itemsList.get(0).get('cdate')).compareTo(dateView.parse('01-01-2020')) < 0)print1Contract = false
if(!itemsList.get(0).get('cdate').equals('') && !itemsList.get(itemsList.size()-1).get('cdate').equals('')){
print1Contract = false
}
// if(itemsList.get(0).get('cdate').equals('') && itemsList.get(itemsList.size()-1).get('cdate').equals(''))itemsList.get(itemsList.size()-1)['dateCor'] = ''
if(itemsList.get(0).get('totalSum').equals(itemsList.get(itemsList.size()-1).get('totalSum')))print1Contract = false
print2Contract = true
}
}
if(excledeOut){
if(print1Contract){
excludeMap.put("number", itemsList.get(0).get("number"))
excledeOutList.add(excludeMap)
}
if(print2Contract){
def excludeMapC = new HashMap<>(excludeMap)
excludeMapC.put("number", itemsList.get(itemsList.size()-1).get("number"))
excledeOutList.add(excludeMapC)
}
// if(print1Contract)excledeOutList.add(itemsList.get(0))
// if(print2Contract)excledeOutList.add(itemsList.get(itemsList.size()-1))
}else{
if(print1Contract)printItemFromResultMap(itemsList.get(0))
if(print2Contract)printItemFromResultMap(itemsList.get(itemsList.size()-1))
}
}
}
_('****************************************************************************')
excledeOutList.each{
if(it instanceof HashMap){
_("|${it.get('inn')}|${it.get('kpp')}|${it.get('number')}")
}else{
_(it)
}
}
_(">> Script time = ${System.currentTimeMillis()-st}")
//HashSet(totalsNullAccrualContract).each{_(it)}
return true;
//Не используется
class itemResultComparator implements Comparator<HashMap<String, Object>>{
public int compare(HashMap<String, Object> a, HashMap<String, Object> b){
if(((String)a.get("cnumber")).equals((String)a.get("cnumber")))return 0;
if(((String)a.get("cnumber")).lenght() > 3 && ((String)b.get("cnumber")).lenght()<3)return 1;
if(((String)a.get("cnumber")).lenght() < 3 && ((String)b.get("cnumber")).lenght()>3)return -1;
return ((String)a.get("cnumber")).compareTo((String)b.get("cnumber"));
}
}
void printItemFromResultMap(HashMap<String, Object> current){
_("!${current.get('cnumber')}!${current.get('cdate')}!${current.get('cmnumber')}!${current.get('number')}!${current.get('period')}!${current.get('dateCor')}!!!!${current.get('amount')}!${current.get('price')}!${current.get('totalSumNoNDS')}!${current.get('totalNDS')}!${current.get('totalSum')}")
}
def getTextPeriod(Date date) {
def result=''
if (date != null) {
def monthesRP = ['январь','февраль','март','апрель','май','июнь','июль','август','сентябрь','октябрь','ноябрь','декабрь']
Calendar cal = Calendar.getInstance()
cal.setTime(date);
result+=monthesRP[cal.get(Calendar.MONTH)]
result+=" "+cal.get(Calendar.YEAR).toString()+" г."
}
return result
}
def getNN(BigDecimal value) {
return value == null ? BigDecimal.ZERO : value;
}
import com.haulmont.cuba.core.global.ViewRepository;
import com.haulmont.cuba.core.global.View;
public List<Accrual> getBillsOnPeriod(UUID contragentID, Date period) {
ViewRepository viewRepository = AppBeans.get(ViewRepository.NAME)
View acView = viewRepository.getView(Accrual.class, "_local");
acView.addProperty("contractPosition", viewRepository.getView(ContractPosition.class, "_local")
.addProperty("contragentRealEstate", viewRepository.getView(ContragentRealEstate.class, "_local")))
.addProperty("createTs")
.addProperty("createdBy");
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("_local")
.list();
if (contracts.size() == 0) {
contracts = dataManager.load(Contract.class)
.query('select e from rtneo$Contract e where e.contragent.id = :contragent order by e.createTs')
.parameter("contragent", contragentID)
.view("_local")
.list();
}
if (contracts.size() > 0) {
int i = 0;
Contract contract = contracts.get(i);
if (contract.getFrom() == null) return null;
while (contract.getFrom() != null & period.before(contract.getFrom())) {
i = i + 1;
if (i == contracts.size()) break;
contract = contracts.get(i);
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
if (contract.getFrom() != null) {
List<Accrual> accruals = dataManager.load(Accrual.class)
.query('select e from rtneo$Accrual e where e.contractPosition.contract.id=:contractId and e.period=:period')
.parameter("contractId", contract.getId())
.parameter("period", period)
.view(acView)
.list();
return accruals;
}
}
return null;
}
List<String> inToFile(String id){
FileDescriptor fd = dataManager.load(FileDescriptor.class)
.id(UuidProvider.fromString(id))
.view(View.MINIMAL)
.optional().orElse(null);
if(fd == null){throw new RuntimeException("fileNotFound")}
FileStorageAPI fileStorageAPI=AppBeans.get(FileStorageAPI.NAME);
InputStream inputStream=fileStorageAPI.openStream(fd)
CSVReader reader = new CSVReader(new InputStreamReader(inputStream, "UTF-8"));
return reader.readAll().stream()
.map{e->e[0]}.collect(Collectors.toList())
}
/**
* Логирование
*/
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)}
