import com.gridnine.xtrip.common.l10n.model.LocaleHelper
import com.gridnine.xtrip.common.model.ClientFop
import com.gridnine.xtrip.common.model.EntityReference
import com.gridnine.xtrip.common.model.booking.ProductStatus
import com.gridnine.xtrip.common.model.booking.TransportationType
import com.gridnine.xtrip.common.model.booking.air.Commission
import com.gridnine.xtrip.common.model.booking.air.ProductFop
import com.gridnine.xtrip.common.model.booking.air.Tax
import com.gridnine.xtrip.common.model.dict.ContractType
import com.gridnine.xtrip.common.model.dict.MCOCategory
import com.gridnine.xtrip.common.model.dict.ProductCategory
import com.gridnine.xtrip.common.model.entity.EntityStorage
import com.gridnine.xtrip.common.model.finance.BasicPaymentDocumentIndex
import com.gridnine.xtrip.common.model.finance.CreditBankTransferOrder
import com.gridnine.xtrip.common.model.finance.PaymentDocumentType
import com.gridnine.xtrip.common.model.helpers.*
import com.gridnine.xtrip.common.model.profile.Contract
import com.gridnine.xtrip.common.model.profile.Organization
import com.gridnine.xtrip.common.model.profile.Person
import com.gridnine.xtrip.common.model.support.reports.ReportCreditBankTransferOrderInfo
import com.gridnine.xtrip.common.model.system.BillingTransactionCategory
import com.gridnine.xtrip.common.model.system.BillingTransactionStatus
import com.gridnine.xtrip.common.model.system.PaymentType
import com.gridnine.xtrip.common.reports.model.AirTicketsTemplateReportSegment
import com.gridnine.xtrip.common.reports.model.AirTicketsTemplateReportSegmentTariff
import com.gridnine.xtrip.common.reports.model.AirTicketsTemplateReportTicket
import com.gridnine.xtrip.common.search.SearchCriterion
import com.gridnine.xtrip.common.search.SearchQuery
import com.gridnine.xtrip.common.util.MiscUtil

import java.text.SimpleDateFormat

if (parameters['isVatRegistries'] != parameters['vatRegistries']) {
    return
}
// Styles
createStyle(name: 'title', h_alignment: 'CENTER', v_alignment: 'CENTER')
createStyle(name: 'titleH1', fontHeight: 14, parent: 'title')
createStyle(name: 'titleH2', fontHeight: 9, parent: 'title')
createStyle(name: 'titleH3', fontHeight: 9, parent: 'title')
createStyle(name: 'header', h_alignment: 'CENTER', v_alignment: 'CENTER', fontHeight: 9)
createStyle(name: 'columnHeader', parent: 'header', warpText: true)
createStyle(name: 'rowHeader', parent: 'header')
createStyle(name: 'data', h_alignment: 'CENTER', v_alignment: 'CENTER', fontHeight: 9)
createStyle(name: 'dataText', parent: 'data')
createStyle(name: 'dataText8', parent: 'data', fontHeight: 8)
createStyle(name: 'dataDate', format: 'm/d/yy', parent: 'data')
createStyle(name: 'dataNumber', h_alignment: 'RIGHT', format: '#,##0.00', parent: 'data')
createStyle(name: 'wrap', wrapText: true)
createStyle(name: 'ahl', h_alignment: 'LEFT')
createStyle(name: 'ahc', h_alignment: 'CENTER')
createStyle(name: 'ahr', h_alignment: 'RIGHT')
createStyle(name: 'avt', v_alignment: 'TOP')
createStyle(name: 'avc', v_alignment: 'CENTER')
createStyle(name: 'avb', v_alignment: 'BOTTOM')
createStyle(name: 'aac', h_alignment: 'CENTER', v_alignment: 'CENTER')
createStyle(name: 'bold', fontBold: true)
createStyle(name: 'italic', fontItalic: true)
createStyle(name: 'bt', topBorder: 'THIN')
createStyle(name: 'bl', leftBorder: 'THIN')
createStyle(name: 'bb', bottomBorder: 'THIN')
createStyle(name: 'br', rightBorder: 'THIN')
createStyle(name: 'ba', topBorder: 'THIN', leftBorder: 'THIN', bottomBorder: 'THIN', rightBorder: 'THIN')
createStyle(name: 'green', foreground: 'LIGHT_GREEN')
createStyle(name: 'yellow', foreground: 'YELLOW')
createStyle(name: 'purple', foreground: 'CORAL')
createStyle(name: 'locked', locked: true)

// Properties
Date periodBeginParameter = parameters['key-report-params']?.periodBegin
Date periodEndParameter = parameters['key-report-params']?.periodEnd
EntityReference<Organization> supplierParameter = parameters['key-report-params']?.supplier
EntityReference<Organization> agencyParameter = parameters['key-report-params']?.agency
EntityReference<Organization> blankOwnerParameter = parameters['key-report-params']?.blankOwner

// Closures
def cellFill(Object value, String style, int x, int y) {
    if (value != null) {
        if (value instanceof Number) {
            number(value, style, x, y)
        } else if (value instanceof Date) {
            date(value, style, x, y)
        } else if (value instanceof Formula){
            formula(value.formula, style, x, y)
        } else {
            text(value, style, x, y)
        }
    } else {
        text('', style, x, y)
    }
}

def cellFill(Object value, String style) {
    if (value != null) {
        if (value instanceof Number) {
            number(value, style)
        } else if (value instanceof Date) {
            date(value, style)
        } else if (value instanceof Formula){
            formula(value.formula, style)
        } else {
            text(value, style)
        }
    } else {
        text('', style)
    }
}

boolean isRouteContainsCIS(final List<AirTicketsTemplateReportSegmentTariff> segmentTariffs) {
    if (segmentTariffs) {
        for (AirTicketsTemplateReportSegmentTariff st : segmentTariffs) {
            for (AirTicketsTemplateReportSegment seg : st.getSegments()) {
                if (AirProductHelper.isCISWithoutDomestic(seg.getDepartureLocation())
                        || AirProductHelper.isCISWithoutDomestic(seg.getArrivalLocation())) {
                    return true
                }
            }
        }
    }
    return false
}

boolean isRouteContainsDVFO(final List<AirTicketsTemplateReportSegmentTariff> segmentTariffs) {
    if (segmentTariffs) {
        for (AirTicketsTemplateReportSegmentTariff st : segmentTariffs) {
            for (AirTicketsTemplateReportSegment seg : st.getSegments()) {
                if (AirProductHelper.isDVFO(seg.getDepartureLocation())
                        || AirProductHelper.isDVFO(seg.getArrivalLocation())) {
                    return true
                }
            }
        }
    }
    return false
}

boolean isRouteContainsKaliningrad(final List<AirTicketsTemplateReportSegmentTariff> segmentTariffs) {
    if (segmentTariffs) {
        for (AirTicketsTemplateReportSegmentTariff st : segmentTariffs) {
            for (AirTicketsTemplateReportSegment seg : st.getSegments()) {
                if (AirProductHelper.isKaliningrad(seg.getDepartureLocation())
                        || AirProductHelper.isKaliningrad(seg.getArrivalLocation())) {
                    return true
                }
            }
        }
    }
    return false
}

boolean isRouteContainCrimea(final List<AirTicketsTemplateReportSegmentTariff> segmentTariffs) {
    if (segmentTariffs) {
        for (AirTicketsTemplateReportSegmentTariff st : segmentTariffs) {
            for (AirTicketsTemplateReportSegment seg : st.getSegments()) {
                if (AirProductHelper.isCrimea(seg.getDepartureLocation())
                        || AirProductHelper.isCrimea(seg.getArrivalLocation())) {
                    return true
                }
            }
        }
    }
    return false
}

def period = {
    def format = new SimpleDateFormat('dd.MM.yyyy')
    return String.format('%s - %s', periodBeginParameter != null ? format.format(periodBeginParameter) : '?', periodEndParameter != null ? format.format(periodEndParameter) : '?')
}

def agencyName = {
    Organization organization = EntityStorage.get().resolve(agencyParameter)?.entity
    return organization ? ProfileHelper.getFullName(organization, LocaleHelper.getLocale('ru', 'RU'), false) : 'Не указано'
}

def contractNumber = {
    List<EntityReference<Contract>> contracts = ProfileHelper.getContracts(supplierParameter, supplierParameter, agencyParameter, ContractType.VENDOR, periodBeginParameter, periodEndParameter)
    def contractCustomerInfo = contracts.size() > 0 ? ProfileHelper.getContractCustomerInfo(contracts.get(0), agencyParameter) : null
    return contractCustomerInfo ? contractCustomerInfo.number : '?'
}

def contractDate = {
    List<EntityReference<Contract>> contracts = ProfileHelper.getContracts(supplierParameter, supplierParameter, agencyParameter, ContractType.VENDOR, periodBeginParameter, periodEndParameter)
    def contractCustomerInfo = contracts.size() > 0 ? ProfileHelper.getContractCustomerInfo(contracts.get(0), agencyParameter) : null
    def format = new SimpleDateFormat('dd.MM.yyyy')
    return contractCustomerInfo != null && contractCustomerInfo.issueDate != null ? format.format(contractCustomerInfo.issueDate) : '?'
}

def directorName = {
    Person person = EntityStorage.get().resolve(requestedParameter('director'))?.entity
    PersonalLocalizableNameFormatter format = new PersonalLocalizableNameFormatter(PersonalLocalizableNameFormatter.SIMPLE)
    return person ? format.format(person, LocaleHelper.getLocale('ru', 'RU'), false) : '?'
}

def responsibleName = {
    Person person = EntityStorage.get().resolve(requestedParameter('responsible'))?.entity
    PersonalLocalizableNameFormatter format = new PersonalLocalizableNameFormatter(PersonalLocalizableNameFormatter.SIMPLE)
    return person ? format.format(person, LocaleHelper.getLocale('ru', 'RU'), false) : '?'
}

def accountantName = {
    Person person = EntityStorage.get().resolve(requestedParameter('accountant'))?.entity
    PersonalLocalizableNameFormatter format = new PersonalLocalizableNameFormatter(PersonalLocalizableNameFormatter.SIMPLE)
    return person ? format.format(person, LocaleHelper.getLocale('ru', 'RU'), false) : '?'
}

def ignoreZeroNumber = { BigDecimal value ->
    return value == null || value == BigDecimal.ZERO || value == 0 ? '' : value
}

def isMtd = { AirTicketsTemplateReportTicket ticket, boolean mtd ->
    for (ProductFop vendorFop : ticket.vendorFops) {
        if (vendorFop.type == PaymentType.CREDIT
                ||  vendorFop.type == PaymentType.MTD) {
            mtd = true
        }
    }
    return mtd
}

def isExchange = { ProductStatus status, BigDecimal num ->
    if (num == null){
        return BigDecimal.ZERO
    }
    return ProductStatus.EXCHANGE == status ? MiscUtil.negate(MiscUtil.abs(num)) : num
}

def getFee = { AirTicketsTemplateReportTicket ticket ->
    List<Tax> taxes = ticket.taxes
    BigDecimal fees = BigDecimal.ZERO
    if (taxes != null) {
        for (Tax tax : taxes) {
            if ((tax.code != null) && (tax.equivalentAmount != null)) {
                if (!tax.code.equalsIgnoreCase("YQ")) {
                    fees = MiscUtil.sum(fees, tax.equivalentAmount)
                }
            }
        }
    }
    return ProductStatus.EXCHANGE == ticket.status ? MiscUtil.negate(MiscUtil.abs(fees)) : fees
}

def getBtos = {
    EntityReference<Organization> blankOwner = blankOwnerParameter
    SearchQuery query = new SearchQuery()
    query
            .getCriteria()
            .getCriterions()
            .add(
            SearchCriterion.ge(
                    BasicPaymentDocumentIndex.Property.endOfVendorReportDecade
                            .name(), MiscUtil.clearTime(periodBeginParameter)))
    query
            .getCriteria()
            .getCriterions()
            .add(
            SearchCriterion.le(
                    BasicPaymentDocumentIndex.Property.endOfVendorReportDecade
                            .name(), MiscUtil.setDayEndTime(periodEndParameter)))
    query
            .getCriteria()
            .getCriterions()
            .add(
            SearchCriterion.eq(
                    BasicPaymentDocumentIndex.Property.documentType.name(),
                    PaymentDocumentType.CREDIT_BANK_TRANSFER_ORDER))
    query
            .getCriteria()
            .getCriterions()
            .add(
            SearchCriterion.eq(
                    BasicPaymentDocumentIndex.Property.payer.name(), blankOwner))
    query
            .getCriteria()
            .getCriterions()
            .add(
            SearchCriterion.eq(
                    BasicPaymentDocumentIndex.Property.organization.name(),
                    agencyParameter))
    query.getPreferredProperties().add(
            BasicPaymentDocumentIndex.Property.number.name())
    List<ReportCreditBankTransferOrderInfo> orders =
            new ArrayList<ReportCreditBankTransferOrderInfo>()
    for (BasicPaymentDocumentIndex idx : EntityStorage.get()
            .search(BasicPaymentDocumentIndex.class, query).getData()) {
        CreditBankTransferOrder order =
                (CreditBankTransferOrder) EntityStorage.get()
                        .resolve(idx.getSource()).getEntity()
        ReportCreditBankTransferOrderInfo orderInfo =
                new ReportCreditBankTransferOrderInfo()
        orderInfo
                .setAmount(order.getTotalAmount() == null ? BigDecimal.ZERO
                : MiscUtil.guarded(order.getTotalAmount().getValue()))
        orderInfo.setDate(order.getDate())
        orderInfo.setName(order.getName())
        orderInfo.setPaymentType(order.getVendorReportData()
                .getPaymentType())
        orderInfo.setEndOfDecade(order.getVendorReportData()
                .getEndOfDecade())
        orders.add(orderInfo)
    }
    return orders
}

def fillCommissions(AirTicketsTemplateReportTicket ticket){
    BigDecimal comission = BigDecimal.ZERO
    if (ticket.status != ProductStatus.VOID) {
        for (Commission aCommission : ticket.commissions) {
            comission = comission.add(aCommission.equivalentAmount ?: BigDecimal.ZERO)
        }
    }
    return comission
}

def isVPD(AirTicketsTemplateReportTicket ticket) {
    if (ticket?.clientFops) {
        for(ClientFop fop : ticket.clientFops) {
            if (fop?.type && (fop.type == PaymentType.MTD || fop.type == PaymentType.MTD_AGENCY)) {
                return true
            }
        }
    }
    return false
}

def sell = new CalcData()
def refund = new CalcData()

def calculate = { CalcData cd, AirTicketsTemplateReportTicket ticket ->
    if (TransportationType.INTERNATIONAL == ticket.transportationType
            || TransportationType.DOMESTIC == ticket.transportationType && isRouteContainsCIS(ticket.segmentTariffs)
            || TransportationType.DOMESTIC == ticket.transportationType && isRouteContainsDVFO(ticket.segmentTariffs)
            || TransportationType.DOMESTIC == ticket.transportationType && isRouteContainCrimea(ticket.segmentTariffs)
            || TransportationType.DOMESTIC == ticket.transportationType && isRouteContainsKaliningrad(ticket.segmentTariffs)) {

        if (isVPD(ticket)) {
            cd.totalVPDTariffVA = cd.totalVPDTariffVA.add(isExchange(ticket.status, ticket.equivalentFare))
        } else if ((MCOCategory.PENALTY == ticket.mcoCategory)
                || (MCOCategory.REBOOKING == ticket.mcoCategory)) {
            cd.totalPenaltyVA = cd.totalPenaltyVA.add(isExchange(ticket.status, ticket.equivalentFare))
        } else {
            cd.totalTariffVA = cd.totalTariffVA.add(isExchange(ticket.status, ticket.equivalentFare))
        }
        cd.totalFuelFeeVA = cd.totalFuelFeeVA.add(isExchange(ticket.status, ticket.fuelChargeAmount))
        if (ProductStatus.SELL == ticket.status
                && ProductStatus.EXCHANGE == ticket.previousProductStatus) {
            cd.totalFeeVA = cd.totalFeeVA.add(isExchange(ticket.status, ticket.penalty))
        }
        cd.totalFeeVA = cd.totalFeeVA.add(getFee(ticket))
        if (isVPD(ticket)) {
            cd.totalVPDVA = cd.totalVPDVA.add(fillCommissions(ticket))
        } else {
            cd.totalCommissionVA = cd.totalCommissionVA.add(fillCommissions(ticket))
        }

        if (ProductCategory.MCO == ticket.productCategory
            || ProductStatus.REFUND == ticket.status) {
            cd.totalPenaltyVA = cd.totalPenaltyVA.add(isExchange(ticket.status, ticket.penalty))
        }

    } else if (TransportationType.DOMESTIC == ticket.transportationType) {
        if (isVPD(ticket)) {
            cd.totalVPDTariffVP = cd.totalVPDTariffVP.add(isExchange(ticket.status, ticket.equivalentFare))
        } else if ((MCOCategory.PENALTY == ticket.mcoCategory)
                || (MCOCategory.REBOOKING == ticket.mcoCategory)) {
            cd.totalPenaltyVP = cd.totalPenaltyVP.add(isExchange(ticket.status, ticket.equivalentFare))
        } else {
            cd.totalTariffVP = cd.totalTariffVP.add(isExchange(ticket.status, ticket.equivalentFare))
        }
        cd.totalFuelFeeVP = cd.totalFuelFeeVP.add(isExchange(ticket.status, ticket.fuelChargeAmount))
        if (ProductStatus.SELL == ticket.status
                && ProductStatus.EXCHANGE == ticket.previousProductStatus) {
            cd.totalFeeVP = cd.totalFeeVP.add(isExchange(ticket.status, ticket.penalty))
        }
        cd.totalFeeVP = cd.totalFeeVP.add(getFee(ticket))
        if (isVPD(ticket)) {
            cd.totalVPDVP = cd.totalVPDVP.add(fillCommissions(ticket))
        } else {
            cd.totalCommissionVP = cd.totalCommissionVP.add(fillCommissions(ticket))
        }
        if (ProductCategory.MCO == ticket.productCategory
            || ProductStatus.REFUND == ticket.status) {
            cd.totalPenaltyVP = cd.totalPenaltyVP.add(isExchange(ticket.status, ticket.penalty))
        }
    }
}

def calculateMtd = { CalcData cd, AirTicketsTemplateReportTicket ticket ->
    if (TransportationType.INTERNATIONAL == ticket.transportationType
            ||  TransportationType.DOMESTIC == ticket.transportationType && isRouteContainsCIS(ticket.segmentTariffs)
            ||  TransportationType.DOMESTIC == ticket.transportationType && isRouteContainsDVFO(ticket.segmentTariffs)) {

        BigDecimal mtdValue = MiscUtil.sum(ticket.equivalentFare ?: BigDecimal.ZERO,
                getFee(ticket),
                ticket.fuelChargeAmount ?: BigDecimal.ZERO,
                ticket.penalty ?: BigDecimal.ZERO)

        cd.totalMtdValueVA = cd.totalMtdValueVA.add(isExchange(ticket.status, mtdValue))
        if (ticket.vendorCommissionValue != null) {
            cd.totalMtdCommissionVA = cd.totalMtdCommissionVA.add(isExchange(ticket.status, ticket.vendorCommissionValue))
        }
    } else if (TransportationType.DOMESTIC == ticket.transportationType) {
        BigDecimal mtdValue = MiscUtil.sum(ticket.equivalentFare,
                getFee(ticket),
                ticket.fuelChargeAmount ?: BigDecimal.ZERO,
                ticket.penalty ?: BigDecimal.ZERO)
        cd.totalMtdValueVP = cd.totalMtdValueVP.add(isExchange(ticket.status, mtdValue))
        if (ticket.vendorCommissionValue != null) {
            cd.totalMtdCommissionVP = cd.totalMtdCommissionVP.add(isExchange(ticket.status, ticket.vendorCommissionValue))
        }
    }
}

def doEstimate = {
    allTickets.each { AirTicketsTemplateReportTicket ticket ->
        mtd = isMtd (ticket, false)
        if (!mtd) {
            if (ProductStatus.SELL == ticket.status
            ||  ProductStatus.EXCHANGE == ticket.status) {
                // Sell
                calculate(sell, ticket)
            } else if (ProductStatus.REFUND == ticket.status) {
                // Refund
                calculate(refund, ticket)
            }
        } else {
            if (ProductStatus.SELL == ticket.status
            ||  ProductStatus.EXCHANGE == ticket.status) {
                calculateMtd(sell, ticket)
            } else if (ProductStatus.REFUND == ticket.status) {
                calculateMtd(refund, ticket)
            }
        }
    }
}

BigDecimal balanceStart =
        BillingTransactionHelper.calculateBillingTransactionsSum(ContractType.VENDOR, agencyParameter,
                supplierParameter, null, BillingTransactionCategory.DEBIT,
                BillingTransactionStatus.ACTIVE, null, periodBeginParameter)

def sumColumn = {
    return new Formula("SUM(${cellIndex(0, -2)}:${cellIndex(0, -1)})")
}

page{parameters['name']} {
    doEstimate()

    // Set fix width for amount of sheets
    fitWidth(1)

    // Set fix height for amount of sheets
    fitHeight(1)

    // Set portrait mode
    landscape(false)

    // Set narrow margins
    margin(0.25, 0.25, 0.25, 0.25)

    // Set scale
    scale(70)

    // Set preserve mode
    preserve(false)

// Report header
    7.times {columnWidth(15); nextColumn()}
    nextRow()
    cellFill('ПЕРЕВОЗЧИК: ОАО Авиакомпания"УРАЛЬСКИЕ АВИАЛИНИИ"', 'titleH1|ahc|bold', 6, 1)
    nextRow()
    cellFill('АГЕНТ: ' + agencyName(), 'titleH1|ahc|bold', 6, 1)
    nextRow()
    cellFill('Агентское соглашение о продаже авиаперевозок №' + contractNumber() + ' от ' + contractDate() , 'titleH1|ahc|bold', 6, 1)
    nextRow()
    cellFill('РАСЧЕТНОЕ ПИСЬМО', 'titleH1|bold|ahc', 6, 1); nextRow()
    cellFill('по продаже авиаперевозок', 'titleH1|bold|ahc', 6, 1); nextRow()
    cellFill( period(), 'titleH1|bold|ahc', 6, 1); nextRow()

// Table header
    cellFill('СТАТЬЯ', 'wrap|columnHeader|ba|italic', 1, 4)
    nextColumn()
    cellFill(null, 'wrap|columnHeader|ba|italic', 1, 4)
    nextColumn()
    cellFill('№ стр.', 'wrap|columnHeader|ba|italic', 1, 4)
    nextColumn()
    cellFill('НДС ДА, ' + DictHelper.getLocalCurrency(), 'wrap|columnHeader|ba|italic', 1, 4)
    nextColumn()
    cellFill('НДС НЕТ, ' + DictHelper.getLocalCurrency(), 'wrap|columnHeader|ba|italic', 1, 4)
    nextColumn()
    cellFill('Итого:', 'wrap|columnHeader|wrap|ba|italic', 1, 4)
    4.times { nextRow() }

// Table
    cellFill('Сальдо начальное', 'dataText|bold|ahl|yellow|ba', 2, 1); nextColumn()
    cellFill(null, 'yellow'); nextColumn()
    cellFill('1', 'dataText|ahl|yellow|ba')
    2.times { nextColumn(); cellFill(null, 'yellow|ba') }
    nextColumn(); cellFill(ignoreZeroNumber(balanceStart), 'yellow|bold|ba')
    nextRow()

    cellFill('Выручка по реестрам продажи', 'titleH2|ba|bold', 2, 1)
    2.times{ nextColumn() }
    cellFill('(наличные)', 'dataText|ahl|ba', 3, 1)
    3.times{ nextColumn(); cellFill(null, 'ba')}
    nextRow()
    cellFill('У6', 'dataText|ahr|ba'); nextColumn()
    cellFill('тариф', 'dataText|ahl|ba'); nextColumn()
    cellFill('2', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(sell.totalTariffVP), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(sell.totalTariffVA), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(null, 'ba'); nextColumn()
    cellFill('прочие сборы', 'dataText|ahl|ba'); nextColumn()
    cellFill('3', 'dataText|ahl|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(
            null, 'ba'); nextColumn()
    cellFill('топл сбор', 'dataText|ahl|ba'); nextColumn()
    cellFill('4', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(sell.totalFuelFeeVP), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(sell.totalFuelFeeVA), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(null, 'ba'); nextColumn()
    cellFill('аэр сбор', 'dataText|ahl|ba'); nextColumn()
    cellFill('5', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(sell.totalFeeVP), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(sell.totalFeeVA), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(null, 'ba'); nextColumn()
    cellFill('ВПД тариф', 'dataText|ahl|ba'); nextColumn()
    cellFill('6', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(sell.totalVPDTariffVP), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(sell.totalVPDTariffVA), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()

    cellFill('Сумма по реестрам возврата', 'titleH2|ba|bold', 2, 1)
    2.times{ nextColumn() }
    cellFill('(наличные)', 'dataText|ahl|ba', 3, 1)
    3.times{ nextColumn(); cellFill('', 'ba')}
    nextRow()
    cellFill('У6', 'dataText|ahr|ba'); nextColumn()
    cellFill('тариф', 'dataText|ahl|ba'); nextColumn()
    cellFill('7', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(refund.totalTariffVP), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(refund.totalTariffVA), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(null, 'ba'); nextColumn()
    cellFill('ВПД тариф', 'dataText|ahl|ba'); nextColumn()
    cellFill('8', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(refund.totalVPDTariffVP), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(refund.totalVPDTariffVA), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(null, 'ba'); nextColumn()
    cellFill('топл сбор', 'dataText|ahl|ba'); nextColumn()
    cellFill('9', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(refund.totalFuelFeeVP), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(refund.totalFuelFeeVA), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(null, 'ba'); nextColumn()
    cellFill('аэр сбор', 'dataText|ahl|ba'); nextColumn()
    cellFill('10', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(refund.totalFeeVP), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(refund.totalFeeVA), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(null, 'ba'); nextColumn()
    cellFill('штраф за возврат', 'dataText|ahl|ba'); nextColumn()
    cellFill('11', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sum(refund.totalPenaltyVP, sell.totalPenaltyVP)), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sum(refund.totalPenaltyVA, sell.totalPenaltyVA)), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('Сумма корректировки (3.1.1.1-548 от 27.09.18; 3.1.1.1-471 от 27.08.18)', 'dataText|ba|wrap', 2, 2)
    2.times{ nextColumn() }
    cellFill('12', 'dataText|ahl|ba', 1, 2); nextColumn()
    cellFill(null, 'dataNumber|ba', 1, 2); nextColumn()
    cellFill(null, 'dataNumber|ba', 1, 2); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba', 1, 2)
    2.times{ nextRow() }
    cellFill('Итого по форме оплаты НАЛ', 'titleH2|avc|ba|purple|bold', 2, 3)
    2.times{ nextColumn() }
    cellFill('п.2+3+4+5+6+11-7-8-9-10+12', 'dataText|avc|ba|purple|bold|wrap', 1, 3); nextColumn()
    2.times { cellFill(new Formula("SUM(${cellIndex(-13, 0)}:${cellIndex(-9, 0)}) - " +
            "SUM(${cellIndex(-7, 0)}:${cellIndex(-4, 0)}) + " +
            "SUM(${cellIndex(-3, 0)}:${cellIndex(-1, 0)})"), 'dataNumber|bold|ba|purple', 1, 3); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|bold|ba|purple', 1, 3)
    3.times { nextRow() }

    cellFill('Размер вознаграждения Агента', 'titleH2|ba|bold', 2, 1); nextColumn()
    4.times{ nextColumn(); cellFill(null, 'ba') }
    nextRow()
    cellFill('Наличные', 'dataText|ahr|ba', 2, 1); 2.times{ nextColumn() }
    cellFill('13', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sub(sell.totalCommissionVP, refund.totalCommissionVP)), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sub(sell.totalCommissionVA, refund.totalCommissionVA)), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('ВПД', 'dataText|ahr|ba', 2, 1); 2.times{ nextColumn() }
    cellFill('14', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sub(sell.totalVPDVP, refund.totalVPDVP)), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sub(sell.totalVPDVA, refund.totalVPDVA)), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('Дополнительное 2% по ВПД', 'dataText|ahr|ba', 2, 1); 2.times{ nextColumn() }
    cellFill('', 'dataText|ahl|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('Кредит ВПД', 'dataText|ahr|ba', 2, 1); 2.times{ nextColumn() }
    cellFill('15', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sub(sell.totalMtdCommissionVP, refund.totalMtdCommissionVP)), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sub(sell.totalMtdCommissionVA, refund.totalMtdCommissionVA)), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('Сумма по корректировки ', 'dataText|ahr|ba', 2, 1); 2.times{ nextColumn() }
    cellFill('16', 'dataText|ahl|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('Комисс по замечаниям', 'dataText|ahr|ba', 2, 1); 2.times{ nextColumn() }
    cellFill('17', 'dataText|ahl|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('Итого размер вознаграждения Агента', 'titleH2|ba|green|bold', 2, 1)
    2.times{ nextColumn() }
    cellFill('п.13+14+15+16+17', 'dataText|ba|green|bold'); nextColumn()
    2.times { cellFill(new Formula("SUM(${cellIndex(-6, 0)}:${cellIndex(-1, 0)})"), 'dataNumber|bold|ba|green'); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|bold|ba|green')
    nextRow()

    cellFill('Сумма по иным формам оплаты', 'titleH2|ba|bold', 2, 1); nextColumn()
    4.times{ nextColumn(); cellFill(null, 'ba') }
    nextRow()
    cellFill('Кредит ВПД', 'dataText|ahr|ba', 2, 1); 2.times { nextColumn() }
    cellFill('18', 'dataText|ahl|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sub(sell.totalMtdValueVP, refund.totalMtdValueVP)), 'dataNumber|ba'); nextColumn()
    cellFill(ignoreZeroNumber(MiscUtil.sub(sell.totalMtdValueVA, refund.totalMtdValueVA)), 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('РТА', 'dataText|ahr|ba', 2, 1); 2.times { nextColumn() }
    cellFill('19', 'dataText|ahl|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('Интернет-продажа', 'dataText|ahr|ba', 2, 1); 2.times { nextColumn() }
    cellFill('20', 'dataText|ahl|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(null, 'dataNumber|ba'); nextColumn()
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('Итоги по иным формам оплаты', 'titleH2|ba|green|bold', 2, 1); 2.times{ nextColumn() }
    cellFill('п.18+19+20', 'dataText|ba|green|bold'); nextColumn()
    2.times { cellFill(new Formula("SUM(${cellIndex(-3, 0)}:${cellIndex(-1, 0)})"), 'dataNumber|bold|ba|green'); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|bold|ba|green')
    nextRow()

    cellFill('Итого подлежит перечислению за вычетом комиссионного вознаграждения', 'titleH2|ba|yellow|wrap|bold', 2, 3)
    2.times{ nextColumn() }
    cellFill('21', 'dataText|ahl|ba|yellow', 1, 3); nextColumn()
    2.times { cellFill(new Formula("${cellIndex(-16, 0)}-${cellIndex(-6, 0)}"), 'dataNumber|bold|ba|yellow', 1, 3); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|bold|ba|yellow', 1, 3)
    3.times { nextRow() }

    cellFill('Учет перечислений:', 'dataText|ahc|ba', 2, 1); nextColumn()
    4.times{ nextColumn(); cellFill(null, 'ba') }
    nextRow()
//    for (ReportCreditBankTransferOrderInfo order : orders) {
//        cellFill(order.name, 'dataText8|ahr|ba', 2, 1); 2.times{ nextColumn() }
//        cellFill(null, 'dataText|ahl|ba'); nextColumn()
//        cellFill(ignoreZeroNumber(order.paymentType == VendorReportPaymentType.VVL ? order.amount : null), 'dataNumber|ba'); nextColumn()
//        cellFill(ignoreZeroNumber(order.paymentType == VendorReportPaymentType.MVL ? order.amount : null), 'dataNumber|ba'); nextColumn()
//        cellFill(sumColumn(), 'dataNumber|ba'); nextColumn()
//        nextRow()
//    }
    cellFill('п/п  №9964  от 17.10.2018', 'dataText8|ahr|ba', 2, 1); 2.times{ nextColumn() }
    3.times{ cellFill(null, 'ba'); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('п/п  №8815  от 17.09.2018', 'dataText8|ahr|ba', 2, 1); 2.times{ nextColumn() }
    3.times{ cellFill(null, 'ba'); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('п/п  №8967  от 20.09.2018', 'dataText8|ahr|ba', 2, 1); 2.times{ nextColumn() }
    3.times{ cellFill(null, 'ba'); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(null, 'dataText8|ahr|ba', 2, 1); 2.times{ nextColumn() }
    3.times{ cellFill(null, 'ba'); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill(null, 'dataText8|ahr|ba', 2, 1); 2.times{ nextColumn() }
    3.times{ cellFill(null, 'ba'); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|ba')
    nextRow()
    cellFill('Итого перечислено', 'titleH2|ba|bold', 2, 1); 2.times{ nextColumn() }
    cellFill('22', 'dataText|ahl|ba'); nextColumn()
    3.times { cellFill(new Formula("${cellIndex(-5, 0)}-${cellIndex(-1, 0)}"), 'dataNumber|ba'); nextColumn() }
    nextRow()
    cellFill('Сальдо конечное', 'dataText|bold|ahl|ba|yellow', 1, 2); nextColumn()
    cellFill(null, 'dataText|bold|ahl|ba|yellow', 1, 2); nextColumn()
    cellFill('п.1+21-22', 'dataText|bold|ahl|ba|yellow|bold', 1, 2)
    3.times {nextColumn(); cellFill(new Formula(
            "${cellIndex(-41, 0)} + ${cellIndex(-26, 0)} - ${cellIndex(-10, 0)} - ${cellIndex(-1, 0)}"),
            'dataNumber|bold|ba|yellow', 1, 2) }
    2.times { nextRow() }
    cellFill('Перечисление не в учетном периоде', 'titleH2|ba|bold', 2, 1); 2.times{ nextColumn() }
    4.times { cellFill(null, 'ba'); nextColumn() }
    nextRow()
    cellFill('п/п  №  от 22.06.2018', 'dataText8|ahr|ba', 2, 1); 2.times{ nextColumn() }
    4.times { cellFill(null, 'ba'); nextColumn() }
    nextRow()
    cellFill('К перечислению', 'titleH2|ba|bold|yellow', 2, 1); 2.times{ nextColumn() }
    cellFill(null, 'ba|yellow'); nextColumn()
    2.times { cellFill(Formula.getSumFormulaByRow("${cellIndex(-4, 0)}","${cellIndex(-1, 0)}"), 'dataNumber|ba|yellow'); nextColumn() }
    cellFill(sumColumn(), 'dataNumber|ba|yellow')
    nextRow()
    3.times { nextRow() }

// Footer
    cellFill('Руководитель:', 'dataText|ahr')
    2.times { nextColumn() }
    cellFill(directorName(), 'dataText|ahl')
    2.times { nextRow() }
    cellFill('Гл. бухгалтер:', 'dataText|ahr')
    2.times { nextColumn() }
    cellFill(responsibleName(), 'dataText|ahl')
    2.times { nextRow() }
    cellFill('Исполнитель:', 'dataText|ahr')
    2.times { nextColumn() }
    cellFill(accountantName(), 'dataText|ahl')
}

class Formula {
    String formula
    Formula(){}
    Formula(String formula) {
        this.formula = formula
    }

    static Formula getSumFormulaByRow (String start, String end) {
        return new Formula('SUM(' + start + ':' + end + ')')
    }
    static Formula getSumFormulaByCell(String... index) {
        def formula = ''
        for (int i=0; i<index.size(); i++) {
            if(i == 0) {
                formula += index[i]
            } else {
                formula += ',' + index[i]
            }
        }
        return new Formula('SUM(' + formula + ')')
    }
}

class CalcData {
    BigDecimal totalVPDVP = BigDecimal.ZERO
    BigDecimal totalVPDVA = BigDecimal.ZERO

    BigDecimal totalPenaltyVP = BigDecimal.ZERO
    BigDecimal totalPenaltyVA = BigDecimal.ZERO

    BigDecimal totalVPDTariffVP = BigDecimal.ZERO
    BigDecimal totalVPDTariffVA = BigDecimal.ZERO

    BigDecimal totalTariffVP = BigDecimal.ZERO
    BigDecimal totalTariffVA = BigDecimal.ZERO

    BigDecimal totalFuelFeeVP = BigDecimal.ZERO
    BigDecimal totalFuelFeeVA = BigDecimal.ZERO

    BigDecimal totalFeeVP = BigDecimal.ZERO
    BigDecimal totalFeeVA = BigDecimal.ZERO

    BigDecimal totalCommissionVP = BigDecimal.ZERO
    BigDecimal totalCommissionVA = BigDecimal.ZERO

    BigDecimal totalMtdValueVP = BigDecimal.ZERO
    BigDecimal totalMtdValueVA = BigDecimal.ZERO

    BigDecimal totalMtdCommissionVP = BigDecimal.ZERO
    BigDecimal totalMtdCommissionVA = BigDecimal.ZERO
}