import com.groupstp.rtneo.entity.*

import com.haulmont.cuba.core.global.*

import com.groupstp.rtneo.service.BillService

import java.text.SimpleDateFormat

DataManager dataManager = AppBeans.get(DataManager.NAME)

BillService service = AppBeans.get(BillService.NAME)

def i = 0

log.debug(new Date())

def contragents = dataManager.load(Contragent.class)

.query('select c from rtneo$Contragent c where c.category.id = \'22381c77-e556-953b-f83a-5069b89cf072\'')

.view("_local")

// .maxResults(10)

.list()

log.debug("Contragents size ${contragents.size()}")

log.debug(new Date())

for(def contragent : contragents){

List<Bill> bills = dataManager.load(Bill.class)

.query('select b from rtneo$Bill b where b.contragent.id = :contragentId and b.relevance = true and b.period <= \'2020-12-01\' order by b.period')

.parameter("contragentId", contragent.getId())

.view("_local")

.list()

// List<Accrual> accruals = getContragentBills(contragent.getId())

def sum = 0;

for(Bill bill : bills){

sum += bill.getSum()

}

def payments = dataManager.load(Payment.class)

.query('select p from rtneo$Payment p where p.inn = :inn')

.parameter("inn", contragent.getInn())

.view("_local")

.list()

def pay = 0;

for(Payment payment : payments){

pay += payment.getSum()

}

log.debug("!${contragent.getName()}!${contragent.getInn()}!${contragent.getPersonalAccount()}!${sum}!${pay}!${sum-pay}")

}

//log.debug(new Date())

List<Accrual> getContragentBills(UUID contragentID) {

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.contragent.id = :contragentID and (' + sqlQuery + ') order by a.period';

accruals = dataManager.load(Accrual.class)

.query(sqlQuery)

.parameter("contragentID", contragentID)

.view("_local")

.list();

}

return accruals;

}