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

DataManager dataManager = AppBeans.get(DataManager.NAME)

FileStorageAPI fileStorageAPI=AppBeans.get(FileStorageAPI.NAME);

BillService billService = AppBeans.get(BillService.NAME)

//String id='6129c48c-fda9-7225-e770-4e6e9d76037b'

//

//

//FileDescriptor fd = dataManager.load(FileDescriptor.class)

// .id(UuidProvider.fromString(id))

// .view(View.MINIMAL)

// .optional()

// .orElse(null);

// if (fd == null) {

// throw new RuntimeException(getMessage("BigTrioContractImporterBean.fileNotFound"));

// }

//InputStream inputStream=fileStorageAPI.openStream(fd)

//CSVReader reader = new CSVReader(new InputStreamReader(inputStream, "UTF-8"));

//List<String> listInn = [];

//for(row in reader)

//{

// listInn.add(row[0])

//}

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

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

def period1 = '2019-01-01'

def period2 = '2019-06-01'

def paymentLimit = '2020-05-31'

def contractLimit = '2019-12-30'

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;

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 c.type = 3 and not e.inn is null and not c.personalAccount is null and size(c.contracts) > 0 order by c.inn')

.properties("id", "inn", "kpp", "pa")

.firstResult(i*100)

.maxResults(100)

.list();

//def contragents = dataManager.loadValues('select c.id, c.inn, c.kpp from rtneo$Contragent c where c.inn = \'3808105761\' order by c.id')

// .properties("id", "inn", "kpp")

// .list();

//List<Accrual> accruals = null

def accrualsGroup = new TreeMap<>();

def contragentsGroup = new TreeMap<>();

for(def contragent : contragents){

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;

List<Accrual> rentAccruals = null

def isPayRent = false;

def res = dataManager.load(RealEstateRenter.class)

.query('select r from rtneo$RealEstateRenter r where r.contragentRealEstate.contragent.id = :id and r.payOwner = true')

.parameter("id", contragent.getValue('id'))

.view("realEstateRenter-changeQueryControl")

.list();

if(res.size() != 0){

isPayRent = true

// log.debug("Contragent[${contragent.getValue('id')}] rent is ${res.size()}")

}

if(isPayRent){

rentAccruals = initBillsRentersTab(res)

}

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"));

while(!beginYear.after(periodEnd)){

List<Accrual> accruals = billService.getBillsOnPeriod(UUID.fromString(contragent.getValue('id').toString()), beginYear.getTime())

if(rentAccruals != null){

// log.debug("Before rent ${accruals}")

for(Accrual rentAccrual : rentAccruals){

// log.debug("rentAccrual.getPeriod() - ${rentAccrual.getPeriod()}, periodStart - ${periodStart.getTime()}")

if(rentAccrual.getPeriod() == beginYear.getTime()){

accruals.add(rentAccrual)

}

}

// log.debug("After rent ${accruals}")

}

for(def accrual : accruals){

tariffNoNDS = accrual.getPrice()*5/6;

if(accrualsGroup[dateView.format(accrual.getPeriod())]==null){

KeyValueEntity mainContractNumber = dataManager.loadValues('select CASE WHEN not a.contractPosition.contract.mainContract is null THEN b.number ELSE a.contractPosition.contract.number END, CASE WHEN not a.contractPosition.contract.mainContract is null THEN b.date ELSE a.contractPosition.contract.date END from rtneo$Accrual a left outer join a.contractPosition.contract.mainContract b where a.id = :id')

.properties('number', 'date')

.parameter('id', accrual.getId())

.optional()

.orElse(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()),

'done' : false,

'mainContractNumber' : mainContractNumber.getValue('number'),

'mainContractDate' : mainContractNumber.getValue('date')

]

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

}

contragentsGroup[contragent.getValue('inn')]['accruals'] = accrualsGroup;

}

def res = [:]

for(def contr : contragentsGroup){

def sumPay = 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']){

sumPay -= accrualFor.getValue()['totalSum']

if(!(sumPay<0))accrualFor.getValue()['done'] = true else accrualFor.getValue()['done'] = false

res[contr.getValue()['inn']]['accruals'][accrualFor.getValue()['period']]=accrualFor

}

}

Calendar endMonth = Calendar.getInstance();

endMonth.setTime(df1.parse(period1));

_("!Наименование услуги!Дата договора!Основной договор!Номер УПД!Дата УПД!Дата корр.!ИНН!КПП!ЛС!Объем!Цена без НДС!Сумма без НДС!НДС!Цена с НДС!Оплачен период")

for(def itemRes : res){

def prev

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

for(def item : itemRes.getValue()['accruals']){

if(dateView.parse(item.getKey()).compareTo(df1.parse(period1))<0)continue

def total = item.getValue().getValue()

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

log.debug("!Услуга Регионального оператора по обращению с ТКО за ${month}!${total['mainContractDate']}!${total['mainContractNumber']}!!${dateView.format(endMonth.getTime())}!!${itemRes.getValue()['inn']}!${itemRes.getValue()['kpp']}!${itemRes.getValue()['pa']}!!!!!!${total['done']==true?'да':'нет'}")

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

.parameter("contract", contract.getId())

.parameter("period",dateView.parse(total['period']))

.optional()

.orElse(null)

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", 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')/6*5)

mapResults.put("totalSumNoNDS", totalAccrualContract.getValue('totalSum') - totalAccrualContract.getValue('totalNDS'))

mapResults.put("totalNDS", totalAccrualContract.getValue('totalNDS'))

mapResults.put("totalSum", totalAccrualContract.getValue('totalSum'))

itemsList.add(mapResults)

}

}

if(itemsList.size()>=1){

printItemFromResultMap(itemsList.get(0))

if(itemsList.size()>1){

printItemFromResultMap(itemsList.get(itemsList.size()-1))

}

}

// ListIterator<HashMap> iter = itemsList.listIterator()

// while(iter.hasNext()){

// HashMap<String, Object> current = iter.next()

// if(iter.hasNext()){

// HashMap<String, Object> next = iter.next()

// if(!((BigDecimal)current.get("totalSum")).equals((BigDecimal)next.get("totalSum")) && !((BigDecimal)current.get("amount")).equals((BigDecimal)next.get("amount"))){

// printItemFromResultMap(current)

// }

// iter.previous()

// }else{

// printItemFromResultMap(current)

// }

// }

}

}

return res;

//Не используется

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('number')}!${current.get('period')}!${current.get('dateCor')}!!!!${current.get('amount')}!${current.get('price')}!${current.get('totalSumNoNDS')}!${current.get('totalNDS')}!${current.get('totalSum')}")

// _("!${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;

}

def initBillsRentersTab(List<RealEstateRenter> rentersRE) {

//Формируем запрос в accrualDs для получения начислений арендаторов для которых арендотатель выбрал оплату арендотаделем

String fullSql = '';

int actual = 0;

// List<RealEstateRenter> rentersRE = getRentersRE();

if (rentersRE.size() > 0) {

for (RealEstateRenter renterRE : rentersRE) {

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", renterRE.getContragent().getId())

.view("_local")

.list();

if (contracts.size() > 0) {

Contract lastContract = null;

String sqlQuery = '';

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())) {

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

String date = sdf.format(lastContract.getFrom());

sqlQuery = sqlQuery + ' or (cast(a.contractPosition.contract.id text)=\'' + contract.getId().toString() + '\' and a.period < \'' + date + '\' )';

lastContract = contract;

}

}

}

if (sqlQuery != '') {

sqlQuery = '(cast(a.contragent.id text) = \'' + renterRE.getContragent().getId().toString() + '\' and cast(a.contractPosition.contragentRealEstate.id text)=\'' + renterRE.getRenterRecord().getId().toString() + '\' and (' + sqlQuery + ')) ';

if (actual == 0) {

fullSql = sqlQuery;

actual = 1;

} else fullSql = fullSql + ' or ' + sqlQuery;

}

}

}

}

if (actual == 0) {

fullSql = 'select e from rtneo$Accrual e where 1 = 0';

} else {

fullSql = 'select a from rtneo$Accrual a where ' + fullSql + 'order by a.period';

}

List<Accrual> accruals = dataManager.load(Accrual.class)

.query(fullSql)

.view("accrual-edit")

.list();

// for (int l = 0; l < accruals.size(); l++) {

// log.debug(accruals.get(l));

// }

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