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)

def i = 0

log.debug(new Date())

def contragents = dataManager.load(Contragent.class)

.query('select c from rtneo$Contragent c where c.type = 3 and (select count(cre) from rtneo$ContragentRealEstate cre where c.id = cre.contragent.id and (not cre.category.isLiving = true or cre.category.isLiving is null))>0 and (select count(ct) from rtneo$Contract ct where ct.contragent.id = c.id and ct.accepted = true)>1 and (select count(p) from rtneo$Payment p where p.inn = c.inn)>0')

.view("_local")

.list()

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

log.debug(new Date())

for(def contragent : contragents){

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

def sum = 0;

for(Accrual accrual : accruals){

sum += accrual.getTotalSum()

}

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;

}