MyTetra Share
Делитесь знаниями!
Выгрузка УПД
Время создания: 14.08.2020 12:08
Раздел: INFO - JOB - rtneo - Работа над задачами - Исправление начислений
Запись: wwwlir/Tetra/master/base/1597378106mt1tqfv53s/text.html на raw.githubusercontent.com

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

 
MyTetra Share v.0.55
Яндекс индекс цитирования