// Imports

import com.gridnine.xtrip.common.l10n.model.LocaleHelper
import com.gridnine.xtrip.common.model.booking.OperationBatch
import com.gridnine.xtrip.common.model.booking.ProductStatus
import com.gridnine.xtrip.common.model.booking.TransportationType
import com.gridnine.xtrip.common.model.dict.CommunicationType
import com.gridnine.xtrip.common.model.dict.ContractType
import com.gridnine.xtrip.common.model.dict.MCOCategory
import com.gridnine.xtrip.common.model.dict.ManagerType
import com.gridnine.xtrip.common.model.entity.EntityStorage
import com.gridnine.xtrip.common.model.helpers.AirProductTaxHelper
import com.gridnine.xtrip.common.model.helpers.PersonalLocalizableNameFormatter
import com.gridnine.xtrip.common.model.helpers.ProfileHelper
import com.gridnine.xtrip.common.model.helpers.SystemHelper
import com.gridnine.xtrip.common.model.profile.Communication
import com.gridnine.xtrip.common.model.profile.Organization
import com.gridnine.xtrip.common.model.profile.PersonEmploymentIndex
import com.gridnine.xtrip.common.model.system.MetadataKey
import com.gridnine.xtrip.common.model.system.PaymentType
import com.gridnine.xtrip.common.reports.model.AirTicketsTemplateReportTicket
import com.gridnine.xtrip.common.reports.model.AirTicketsTemplateReportTicketType
import com.gridnine.xtrip.common.search.SearchCriterion
import com.gridnine.xtrip.common.search.SearchQuery
import com.gridnine.xtrip.common.util.MiscUtil

import java.math.RoundingMode
import java.text.SimpleDateFormat

// Styles
createStyle(name: 'title', h_alignment: 'CENTER', v_alignment: 'CENTER', fontFamily: "TimesNewRoman")
createStyle(name: 'titleH1', fontHeight: 12, parent: 'title')
createStyle(name: 'titleH2', fontHeight: 10, parent: 'title')
createStyle(name: 'titleH3', fontHeight: 10, parent: 'title')
createStyle(name: 'header', h_alignment: 'CENTER', v_alignment: 'CENTER', fontHeight: 10, fontFamily: "TimesNewRoman")
createStyle(name: 'columnHeader', parent: 'header')
createStyle(name: 'rowHeader', parent: 'header')
createStyle(name: 'data', h_alignment: 'CENTER', v_alignment: 'CENTER', fontHeight: 10, fontFamily: "TimesNewRoman")
createStyle(name: 'dataText', parent: 'data')
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: 'THICK')
createStyle(name: 'bl', leftBorder: 'THICK')
createStyle(name: 'bb', bottomBorder: 'THICK')
createStyle(name: 'br', rightBorder: 'THICK')
createStyle(name: 'ba', topBorder: 'THICK', leftBorder: 'THICK', bottomBorder: 'THICK', rightBorder: 'THICK')
createStyle(name: 'grey25', foreground: 'GREY_25_PERCENT')
createStyle(name: 'locked', locked: true)

// Properties
def periodBeginParameter = parameters['key-report-params']?.periodBegin
def periodEndParameter = parameters['key-report-params']?.periodEnd
def supplierParameter = parameters['key-report-params']?.supplier
def agencyParameter = parameters['key-report-params']?.agency

// Closures
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 = {

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

def agencyCode = {

    def organization = EntityStorage.get().resolve(agencyParameter)?.entity
    return organization ? SystemHelper.findMetadataAsString(organization.getMetadata(), MetadataKey.KEY_HZ_AGENCY_CODE) : ''
}

def agencyPhone = {
    String phone = ""
    Organization organization = (Organization) EntityStorage.get().resolve(agencyParameter)?.entity
    if (organization) {
        Communication phoneComm = organization.getCommunications().find { c ->
            c.getType() == CommunicationType.WORK_PHONE
        }
        if (phoneComm) {
            if (phoneComm.getCountryCode()) {
                phone = phone.concat(phoneComm.getCountryCode())
            }
            if (phoneComm.getCityCode()) {
                phone = phone.concat(phoneComm.getCityCode())
            }
            if (phoneComm.getSense()) {
                phone = phone.concat(phoneComm.getSense())
            }
        }
    }
    return phone
}

def director = {
    String director = null
    if (agencyParameter) {
        SearchQuery query = new SearchQuery()
        query.getCriteria().getCriterions().add(SearchCriterion.eq(
                PersonEmploymentIndex.Property.organization.name(), agencyParameter))
        query.getCriteria().getCriterions().add(SearchCriterion.or(
                SearchCriterion.eq(
                        PersonEmploymentIndex.Property.workStartDate.name(), null),
                SearchCriterion.le(
                        PersonEmploymentIndex.Property.workStartDate.name(), new Date())
        ))
        query.getCriteria().getCriterions().add(SearchCriterion.or(
                SearchCriterion.eq(
                        PersonEmploymentIndex.Property.workEndDate.name(), null),
                SearchCriterion.ge(
                        PersonEmploymentIndex.Property.workEndDate.name(), new Date())
        ))
        List<PersonEmploymentIndex> indices =
                EntityStorage.get().search(PersonEmploymentIndex.class, query).getData()
        if (!indices.isEmpty()) {
            PersonEmploymentIndex dir = indices.find { pei ->
                pei.getPosition() != null && pei.getPosition().getCode() == ManagerType.DIRECTOR.name()
            }
            if (dir) {
                director = dir.getSource().getCaption()
            }
        }
    }
    return director
}

def accountant = {
    String accountant = null
    if (agencyParameter) {
        SearchQuery query = new SearchQuery()
        query.getCriteria().getCriterions().add(SearchCriterion.eq(
                PersonEmploymentIndex.Property.organization.name(), agencyParameter))
        query.getCriteria().getCriterions().add(SearchCriterion.or(
                SearchCriterion.eq(
                        PersonEmploymentIndex.Property.workStartDate.name(), null),
                SearchCriterion.le(
                        PersonEmploymentIndex.Property.workStartDate.name(), new Date())
        ))
        query.getCriteria().getCriterions().add(SearchCriterion.or(
                SearchCriterion.eq(
                        PersonEmploymentIndex.Property.workEndDate.name(), null),
                SearchCriterion.ge(
                        PersonEmploymentIndex.Property.workEndDate.name(), new Date())
        ))
        List<PersonEmploymentIndex> indices =
                EntityStorage.get().search(PersonEmploymentIndex.class, query).getData()
        if (!indices.isEmpty()) {
            PersonEmploymentIndex acc = indices.find { pei ->
                pei.getPosition() != null && pei.getPosition().getCode() == ManagerType.CHIEF_ACCOUNTANT.name()
            }
            if (acc) {
                accountant = acc.getSource().getCaption()
            }
        }
    }
    return accountant
}

def agencyVatPayer = {

    def organization = EntityStorage.get().resolve(agencyParameter)?.entity
    return organization ? organization.isVatPayer()?.booleanValue() : false
}

def contractNumber = {

    def 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 = {

    def 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 = {

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

def accountantName = {

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

def responsibleName = {

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

def directorPhone = {

    def person = EntityStorage.get().resolve(requestedParameter('director'))?.entity
    def communication = person ? ProfileHelper.filterPersonCommunications(person.getCommunications(), CommunicationType.WORK_PHONE, agencyParameter) : null
    return communication ? ProfileHelper.buildFullPhoneNumber(communication) : '?'
}

def responsiblePhone = {

    def person = EntityStorage.get().resolve(requestedParameter('responsible'))?.entity
    def communication = person ? ProfileHelper.filterPersonCommunications(person.getCommunications(), CommunicationType.WORK_PHONE, agencyParameter) : null
    return communication ? ProfileHelper.buildFullPhoneNumber(communication) : '?'
}

def date = {
    SimpleDateFormat format = new SimpleDateFormat("\"dd\" MMMM yyyy год", LocaleHelper.RU_LOCALE)
    return format.format(new Date())
}

page{'Расчетное письмо'}{

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

    def counts = [:]

    def tariffs = [:]
    def taxesOther = [:]
    def taxesCn = [:]
    def taxesBp = [:]
    def taxesSw = [:]
    def penalties = [:]
    def commissions = [:]
    def baggageTariffs = [:]
    def baggageCommissions = [:]

    counts['sell-vvl'] = 0
    counts['sell-mvl'] = 0
    counts['refund-vvl'] = 0
    counts['refund-mvl'] = 0
    counts['exchange-vvl'] = 0
    counts['exchange-mvl'] = 0

    tariffs['sell'] = [:]
    tariffs['refund'] = [:]
    tariffs['exchange'] = [:]

    tariffs['sell']['mvl'] = BigDecimal.ZERO
    tariffs['refund']['mvl'] = BigDecimal.ZERO
    tariffs['exchange']['mvl'] = BigDecimal.ZERO

    tariffs['sell']['vvl'] = BigDecimal.ZERO
    tariffs['refund']['vvl'] = BigDecimal.ZERO
    tariffs['exchange']['vvl'] = BigDecimal.ZERO

    taxesOther['sell'] = [:]
    taxesOther['refund'] = [:]
    taxesOther['exchange'] = [:]

    taxesOther['sell']['mvl'] = BigDecimal.ZERO
    taxesOther['refund']['mvl'] = BigDecimal.ZERO
    taxesOther['exchange']['mvl'] = BigDecimal.ZERO

    taxesOther['sell']['vvl'] = BigDecimal.ZERO
    taxesOther['refund']['vvl'] = BigDecimal.ZERO
    taxesOther['exchange']['vvl'] = BigDecimal.ZERO

    taxesCn['sell'] = [:]
    taxesCn['refund'] = [:]
    taxesCn['exchange'] = [:]

    taxesCn['sell']['mvl'] = BigDecimal.ZERO
    taxesCn['refund']['mvl'] = BigDecimal.ZERO
    taxesCn['exchange']['mvl'] = BigDecimal.ZERO

    taxesCn['sell']['vvl'] = BigDecimal.ZERO
    taxesCn['refund']['vvl'] = BigDecimal.ZERO
    taxesCn['exchange']['vvl'] = BigDecimal.ZERO

    taxesBp['sell'] = [:]
    taxesBp['refund'] = [:]
    taxesBp['exchange'] = [:]

    taxesBp['sell']['mvl'] = BigDecimal.ZERO
    taxesBp['refund']['mvl'] = BigDecimal.ZERO
    taxesBp['exchange']['mvl'] = BigDecimal.ZERO

    taxesBp['sell']['vvl'] = BigDecimal.ZERO
    taxesBp['refund']['vvl'] = BigDecimal.ZERO
    taxesBp['exchange']['vvl'] = BigDecimal.ZERO

    taxesSw['sell'] = [:]
    taxesSw['refund'] = [:]
    taxesSw['exchange'] = [:]

    taxesSw['sell']['mvl'] = BigDecimal.ZERO
    taxesSw['refund']['mvl'] = BigDecimal.ZERO
    taxesSw['exchange']['mvl'] = BigDecimal.ZERO

    taxesSw['sell']['vvl'] = BigDecimal.ZERO
    taxesSw['refund']['vvl'] = BigDecimal.ZERO
    taxesSw['exchange']['vvl'] = BigDecimal.ZERO

    penalties['sell'] = [:]
    penalties['refund'] = [:]
    penalties['exchange'] = [:]

    penalties['sell']['mvl'] = BigDecimal.ZERO
    penalties['refund']['mvl'] = BigDecimal.ZERO
    penalties['exchange']['mvl'] = BigDecimal.ZERO

    penalties['sell']['vvl'] = BigDecimal.ZERO
    penalties['refund']['vvl'] = BigDecimal.ZERO
    penalties['exchange']['vvl'] = BigDecimal.ZERO

    commissions['sell'] = [:]
    commissions['refund'] = [:]
    commissions['exchange'] = [:]

    commissions['sell']['mvl'] = BigDecimal.ZERO
    commissions['refund']['mvl'] = BigDecimal.ZERO
    commissions['exchange']['mvl'] = BigDecimal.ZERO

    commissions['sell']['vvl'] = BigDecimal.ZERO
    commissions['refund']['vvl'] = BigDecimal.ZERO
    commissions['exchange']['vvl'] = BigDecimal.ZERO

    baggageTariffs['sell'] = [:]
    baggageTariffs['refund'] = [:]
    baggageTariffs['exchange'] = [:]

    baggageTariffs['sell']['mvl'] = BigDecimal.ZERO
    baggageTariffs['refund']['mvl'] = BigDecimal.ZERO
    baggageTariffs['exchange']['mvl'] = BigDecimal.ZERO

    baggageTariffs['sell']['vvl'] = BigDecimal.ZERO
    baggageTariffs['refund']['vvl'] = BigDecimal.ZERO
    baggageTariffs['exchange']['vvl'] = BigDecimal.ZERO

    baggageCommissions['sell'] = [:]
    baggageCommissions['refund'] = [:]
    baggageCommissions['exchange'] = [:]

    baggageCommissions['sell']['mvl'] = BigDecimal.ZERO
    baggageCommissions['refund']['mvl'] = BigDecimal.ZERO
    baggageCommissions['exchange']['mvl'] = BigDecimal.ZERO

    baggageCommissions['sell']['vvl'] = BigDecimal.ZERO
    baggageCommissions['refund']['vvl'] = BigDecimal.ZERO
    baggageCommissions['exchange']['vvl'] = BigDecimal.ZERO

    // Initialization
    tickets { AirTicketsTemplateReportTicket ticket ->
        if (ticket.type != AirTicketsTemplateReportTicketType.PRODUCT) {
            return
        }

        def transportationBatch = null
        if (ticket.transportationType == TransportationType.INTERNATIONAL) {
            transportationBatch = 'mvl'
        } else if(ticket.transportationType == TransportationType.DOMESTIC) {
            transportationBatch = 'vvl'
        }
        if (!transportationBatch) {
            return
        }

        def operationBatch = null
        if (ticket.operationBatch == OperationBatch.SELL) {
            operationBatch = 'sell'
        } else if(ticket.operationBatch == OperationBatch.REFUND) {
            operationBatch = 'refund'
        } else if(ticket.operationBatch == OperationBatch.EXCHANGE) {
            operationBatch = 'exchange'
        }
        if (!operationBatch) {
            return
        }

        if (ticket.mcoCategory == MCOCategory.EXCESS_LUGGAGE) {
            baggageTariffs[operationBatch][transportationBatch] = MiscUtil.sum(baggageTariffs[operationBatch][transportationBatch], ticket.price)
            baggageCommissions[operationBatch][transportationBatch] = MiscUtil.sum(baggageCommissions[operationBatch][transportationBatch], ticket.vendorCommissionValue)

            return
        }

        if (ticket.mcoCategory == MCOCategory.REBOOKING) {
            BigDecimal rebookingPenalty = null
            if (ticket.status == ProductStatus.SELL) {
                rebookingPenalty = ticket.equivalentFare
            } else if (ticket.status == ProductStatus.REFUND) {
                rebookingPenalty = MiscUtil(ticket.equivalentFare)
            }
            penalties['exchange'][transportationBatch] = MiscUtil.sum(penalties['exchange'][transportationBatch], rebookingPenalty)

            counts['exchange-' + transportationBatch] = counts['exchange-' + transportationBatch] + 1

            return
        }

        counts[operationBatch + '-' + transportationBatch] = counts[operationBatch + '-' + transportationBatch] + 1

        tariffs[operationBatch][transportationBatch] = MiscUtil.sum(tariffs[operationBatch][transportationBatch], ticket.equivalentFare)
        taxesOther[operationBatch][transportationBatch] = MiscUtil.sum(taxesOther[operationBatch][transportationBatch], AirProductTaxHelper.getEquivalentTaxesAmountByCodes(ticket.taxes, false, 'CN', 'BP', 'SW'))
        taxesCn[operationBatch][transportationBatch] = MiscUtil.sum(taxesCn[operationBatch][transportationBatch], AirProductTaxHelper.getEquivalentTaxesAmountByCodes(ticket.taxes, true, 'CN'))
        taxesBp[operationBatch][transportationBatch] = MiscUtil.sum(taxesBp[operationBatch][transportationBatch], AirProductTaxHelper.getEquivalentTaxesAmountByCodes(ticket.taxes, true, 'BP'))
        taxesSw[operationBatch][transportationBatch] = MiscUtil.sum(taxesSw[operationBatch][transportationBatch], AirProductTaxHelper.getEquivalentTaxesAmountByCodes(ticket.taxes, true, 'SW'))
        penalties[operationBatch][transportationBatch] = MiscUtil.sum(penalties[operationBatch][transportationBatch], ticket.penalty)
        commissions[operationBatch][transportationBatch] = MiscUtil.sum(commissions[operationBatch][transportationBatch], ticket.vendorCommissionValue)
    }

// Report header
    rowHeight(17)
    text('РАСЧЕТНОЕ ПИСЬМО', 'titleH1|bold', 6, 1)
    2.times { nextRow() }
    text('Код перевозчика: 598', 'titleH3|ahl', 2, 1)
    3.times { nextColumn() }
    text('Наименование агента: ' + agencyName(), 'titleH3|ahl', 3, 1)
    nextRow()
    text('Отчетный период ' + period(), 'titleH3|ahl', 2, 1)
    3.times { nextColumn() }
    text('Код агента: ' + (agencyCode() ? agencyCode() : ''), 'titleH3|ahl', 3, 1)
    nextRow()
    text('Номер отчетного периода: 1', 'titleH3|ahl', 2, 1)
    3.times { nextColumn() }
    text('Телефон: ' + agencyPhone(), 'titleH3|ahl', 3, 1)
    nextRow()
    text('Код валюты RUB', 'titleH3|ahl', 2, 1)
    3.times { nextColumn() }
    text('Регулярные перевозки', 'titleH3|ahl', 3, 1)
    3.times { nextRow() }

// Table header
    rowHeight(50)
    text('', 'columnHeader|wrap|ba', 1, 1)
    nextColumn()
    text('', 'columnHeader|wrap|bt|bb|bl', 1, 1)
    nextColumn()
    text('', 'columnHeader|wrap|bt|bb|br', 1, 1)
    nextColumn()
    text('Кол-во документов', 'columnHeader|wrap|ba', 1, 1)
    nextColumn()
    text('Причитается А/к "Аврора" ДЕБЕТ', 'columnHeader|wrap|ba', 1, 1)
    nextColumn()
    text('Причитается Агенту КРЕДИТ', 'columnHeader|wrap|ba', 1, 1)
    nextRow()

// Table
    text('1', 'rowHeader|wrap|ba')
    nextColumn()
    text('ДЕПОЗИТ', 'rowHeader|wrap|ahl|bt|bb|bl', 1, 1)
    nextColumn()
    text('', 'dataText|bt|bb|br')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    text('2', 'rowHeader|wrap|ba')
    nextColumn()
    text('БАНКОВСКАЯ ГАРАНТИЯ', 'rowHeader|wrap|ahl|bt|bb|bl', 1, 1)
    nextColumn()
    text('', 'dataText|bt|bb|br')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    text('3', 'rowHeader|wrap|ba')
    nextColumn()
    text('САЛЬДО НА НАЧАЛО ПЕРИОДА', 'rowHeader|wrap|ahl|bt|bb|bl', 1, 1)
    nextColumn()
    text('', 'dataText|bt|bb|br')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

// Sell
    rowHeight(17)
    String countSellVvl = String.valueOf(counts['sell-vvl'])
    text('4', 'rowHeader|wrap|ba', 1, 1)
    nextColumn()
    text('ПРОДАЖА ПЕРЕВОЗОК', 'rowHeader|wrap|ahl|ba', 1, 1)
    nextColumn()
    text('Тариф ВВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countSellVvl, 'dataText|ba', 1, 1)
    nextColumn()
    number(tariffs['sell']['vvl'], 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Такса ВВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countSellVvl, 'dataText|ba', 1, 1)
    nextColumn()
    number(taxesOther['sell']['vvl'], 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    String countSellMvl = String.valueOf(counts['sell-mvl'])
    text('Тариф МВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countSellMvl, 'dataText|ba', 1, 1)
    nextColumn()
    number(tariffs['sell']['mvl'], 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Таксы МВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countSellMvl, 'dataText|ba', 1, 1)
    nextColumn()
    number(taxesOther['sell']['mvl'], 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    String countSell = String.valueOf(counts['sell-vvl'] + counts['sell-mvl'])
    text('КПБ (EBT)', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countSell, 'dataText|ba', 1, 1)
    nextColumn()
    number(MiscUtil.sum(baggageTariffs['sell']['mvl'], baggageTariffs['sell']['vvl']), 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Агент. ком.', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countSell, 'dataText|ba', 1, 1)
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    number(MiscUtil.sum(commissions['sell']['mvl'], commissions['sell']['vvl']), 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Агент. ком. (EBT)', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countSell, 'dataText|ba', 1, 1)
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    number(MiscUtil.sum(baggageCommissions['sell']['mvl'], baggageCommissions['sell']['vvl']), 'dataNumber|ba')
    nextRow()

// Exchange
    String countExchangeVvl = String.valueOf(counts['exchange-vvl'])
    text('5', 'rowHeader|wrap|ba', 1, 1)
    nextColumn()
    text('ОБМЕН ДОКУМЕНТОВ', 'rowHeader|wrap|ahl|ba', 1, 1)
    nextColumn()
    text('Тариф ВВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countExchangeVvl, 'dataText|ba', 1, 1)
    nextColumn()
    number(MiscUtil.sum(tariffs['exchange']['vvl'], baggageTariffs['exchange']['vvl']), 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Такса ВВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countExchangeVvl, 'dataText|ba', 1, 1)
    nextColumn()
    number(taxesOther['exchange']['vvl'], 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    String countExchangeMvl = String.valueOf(counts['exchange-mvl'])
    text('Тариф МВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countExchangeMvl, 'dataText|ba', 1, 1)
    nextColumn()
    number(MiscUtil.sum(tariffs['exchange']['mvl'], baggageTariffs['exchange']['mvl']), 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Такса МВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countExchangeMvl, 'dataText|ba', 1, 1)
    nextColumn()
    number(taxesOther['exchange']['mvl'], 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    String countExchange = String.valueOf(counts['exchange-vvl'] + counts['exchange-mvl'])
    text('Штраф', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countExchange, 'dataText|ba', 1, 1)
    nextColumn()
    number(MiscUtil.sum(penalties['exchange']['mvl'], penalties['exchange']['vvl']), 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Агент. ком.', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countExchange, 'dataText|ba', 1, 1)
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    number(MiscUtil.sum(commissions['exchange']['mvl'], commissions['exchange']['vvl'], baggageCommissions['exchange']['mvl'], baggageCommissions['exchange']['vvl']), 'dataNumber|ba')
    nextRow()

// Refund
    String countRefundVvl = String.valueOf(counts['refund-vvl'])
    text('6', 'rowHeader|wrap|ba', 1, 1)
    nextColumn()
    text('ВОЗВРАТ', 'rowHeader|wrap|ahl|ba', 1, 1)
    nextColumn()
    text('Тариф ВВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countRefundVvl, 'dataText|ba', 1, 1)
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    number(tariffs['refund']['vvl'], 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Такса ВВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countRefundVvl, 'dataText|ba', 1, 1)
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    number(taxesOther['refund']['vvl'], 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    String countRefundMvl = String.valueOf(counts['refund-mvl'])
    text('Тариф МВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countRefundMvl, 'dataText|ba', 1, 1)
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    number(tariffs['refund']['mvl'], 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Такса МВЛ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countRefundMvl, 'dataText|ba', 1, 1)
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    number(taxesOther['refund']['mvl'], 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    String countRefund = String.valueOf(counts['refund-vvl'] + counts['refund-mvl'])
    text('Штраф', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countRefund, 'dataText|ba', 1, 1)
    nextColumn()
    number(MiscUtil.sum(penalties['refund']['mvl'], penalties['refund']['vvl']), 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Агент. ком.', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text(countRefund, 'dataText|ba', 1, 1)
    nextColumn()
    number(MiscUtil.sum(commissions['refund']['mvl'], commissions['refund']['vvl'], baggageCommissions['refund']['mvl'], baggageCommissions['refund']['vvl']), 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

// Third party airlines
    rowHeight(50)
    text('7', 'rowHeader|wrap|ba', 1, 1)
    nextColumn()
    text('СПЕЦИАЛЬНЫЕ УСЛОВИЯ ПО АГЕНТСКОМУ СОГЛАШЕНИЮ', 'rowHeader|wrap|ahl|ba', 1, 1)
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    rowHeight(17)
    3.times {
        6.times {
            text('', 'dataText|ba')
            nextColumn()
        }
        nextRow()
    }

// Agency memo
    rowHeight(40)
    text('8', 'rowHeader|wrap|ba', 1, 1)
    nextColumn()
    text('СУММА ПО ПРЕТЕНЗИОННЫМ УВЕДОМЛЕНИЯМ', 'rowHeader|wrap|ahl|ba', 1, 1)
    nextColumn()
    text('Тариф', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    rowHeight(17)
    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Такса', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Штраф', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times {
        text('', 'dataText|ba')
        nextColumn()
    }
    text('Штраф по АДМ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    2.times { nextColumn() }
    text('Агент. ком.', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    text('9', 'rowHeader|wrap|ba', 1, 1)
    nextColumn()
    text('ВСЕГО ЗА ОТЧЕТНЫЙ ПЕРИОД', 'rowHeader|wrap|ahl|ba', 2, 1)
    2.times { nextColumn() }
    text('', 'dataText|ba')
    nextColumn()
    formula("${cellIndex(-28, 0)}+${cellIndex(-27, 0)}+${cellIndex(-26, 0)}+${cellIndex(-25, 0)}+${cellIndex(-24, 0)}+${cellIndex(-21, 0)}+${cellIndex(-19, 0)}+${cellIndex(-18, 0)}+${cellIndex(-17, 0)}+${cellIndex(-11, 0)}+${cellIndex(-10, 0)}", 'dataNumber|ba')
    nextColumn()
    formula("${cellIndex(-23, 0)}+${cellIndex(-22, 0)}+${cellIndex(-16, 0)}+${cellIndex(-15, 0)}+${cellIndex(-14, 0)}+${cellIndex(-13, 0)}+${cellIndex(-12, 0)}+${cellIndex(-11, 0)}+${cellIndex(-10, 0)}", 'dataNumber|ba')
    nextRow()

    text('', 'dataText|ba')
    nextColumn()
    text('в т.ч. агент. ком.', 'rowHeader|wrap|ahr|ba', 2, 1)
    2.times { nextColumn() }
    text('', 'dataText|ba')
    nextColumn()
    formula("${cellIndex(-11, 0)}", 'dataNumber|ba')
    nextColumn()
    formula("${cellIndex(-24, 0)}+${cellIndex(-23, 0)}+${cellIndex(-17, 0)}+${cellIndex(-11, 0)}", 'dataNumber|ba')
    nextRow()

    text('10', 'rowHeader|wrap|ba')
    nextColumn()
    text('ИТОГО К ПЕРЕЧИСЛЕНИЮ', 'rowHeader|wrap|ahl|bold|ba', 2, 1)
    2.times { nextColumn() }
    text('', 'dataText|bold|ba')
    nextColumn()
    formula("${cellIndex(-2, 0)}-${cellIndex(-2, 1)}", 'dataNumber|bold|ba')
    nextColumn()
    text('', 'dataNumber|bold|ba')
    nextRow()

    text('11', 'rowHeader|wrap|ba')
    nextColumn()
    text('ВЗАИМОРАСЧЕТЫ', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text('', 'rowHeader|wrap|ahl|ba')
    nextColumn()
    text('', 'dataText|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|ba')
    nextRow()

    text('12', 'rowHeader|wrap|ba')
    nextColumn()
    text('ПЕРЕЧИСЛЕНО ЗА ОТЧЕТНЫЙ ПЕРИОД', 'rowHeader|wrap|ahl|bold|ba', 2, 1)
    2.times { nextColumn() }
    text('', 'dataText|bold|ba')
    nextColumn()
    text('', 'dataNumber|bold|ba')
    nextColumn()
    text('', 'dataNumber|bold|ba')
    nextRow()

    5.times {
        6.times {
            text('', 'dataText|ba')
            nextColumn()
        }
        nextRow()
    }


    text('13', 'rowHeader|wrap|ba')
    nextColumn()
    text('ВСЕГО ПЕРЕЧИСЛЕННО за отчетный период', 'rowHeader|wrap|ahl|bold|ba', 2, 1)
    2.times { nextColumn() }
    text('', 'dataText|bold|ba')
    nextColumn()
    text('', 'dataNumber|bold|ba')
    nextColumn()
    text('', 'dataNumber|bold|ba')
    nextRow()

    text('14', 'rowHeader|wrap|ba')
    nextColumn()
    text('САЛЬДО НА КОНЕЦ ПЕРИОДА', 'rowHeader|wrap|ahl|bold|ba', 2, 1)
    2.times { nextColumn() }
    text('', 'dataText|bold|ba')
    nextColumn()
    formula("${cellIndex(-9, 0)}+${cellIndex(-40, 0)}-${cellIndex(-1, 0)}", 'dataNumber|ba')
    nextColumn()
    text('', 'dataNumber|bold|ba')
    2.times { nextRow() }

    nextColumn()
    text('Руководитель', 'titleH3|ahl')
    nextColumn()
    text('/' + (directorName() ? directorName() : director() ? director() : '______________') + '/', 'titleH3', 3, 1)
    2.times { nextRow() }

    nextColumn()
    text('Главный бухгалтер', 'titleH3|ahl')
    nextColumn()
    text('/' + (accountantName() ? accountantName() : accountant() ? accountant() : '______________') + '/', 'titleH3', 3, 1)
    2.times { nextRow() }

    nextColumn()
    text('Дата составления отчёта', 'titleH3|ahl')
    nextColumn()
    text(date(), 'titleH3', 3, 1)
    2.times { nextRow() }

    nextColumn()
    text('Исполнитель   ' + (responsibleName() ? responsibleName() : '______________') + ' тел: ' + agencyPhone(), 'titleH3|ahl')
    2.times { nextRow() }
    nextColumn()
    text('АО "Авиакомпания "АВРОРА"', 'titleH3|ahl')
    nextRow()
    nextColumn()
    text('Бухгалтер', 'titleH3|ahl')

    nextRow()
    columnWidth(10); nextColumn()
    columnWidth(30); nextColumn()
    columnWidth(20); nextColumn()
    columnWidth(10); nextColumn()
    columnWidth(14); nextColumn()
    columnWidth(16)
}
