/**
 * config/setup-ibecorp/entity/templates/passengersReport.groovy *
 */


import com.gridnine.xtrip.common.l10n.model.L10nStringHelper
import com.gridnine.xtrip.common.l10n.model.LocaleHelper
import com.gridnine.xtrip.common.model.EntityContainer
import com.gridnine.xtrip.common.model.EntityReference
import com.gridnine.xtrip.common.model.corteos.CorteosPersonProfileData
import com.gridnine.xtrip.common.model.dict.CodeSystem
import com.gridnine.xtrip.common.model.dict.CommunicationType
import com.gridnine.xtrip.common.model.dict.CostCodeCategory
import com.gridnine.xtrip.common.model.dict.DictionaryCache
import com.gridnine.xtrip.common.model.dict.DictionaryReference
import com.gridnine.xtrip.common.model.dict.Gender
import com.gridnine.xtrip.common.model.dict.ProfileGroupReference;
import com.gridnine.xtrip.common.model.entity.EntityStorage
import com.gridnine.xtrip.common.model.helpers.DictHelper
import com.gridnine.xtrip.common.model.helpers.GeneralProductHelper
import com.gridnine.xtrip.common.model.helpers.ProfileHelper
import com.gridnine.xtrip.common.model.profile.*
import com.gridnine.xtrip.common.util.EnumUtil
import com.gridnine.xtrip.common.util.TextUtil
import com.gridnine.xtrip.server.gds.corteos.CorteosHelper
import com.gridnine.xtrip.server.parsers2.person.ExcelColumns
import com.gridnine.xtrip.server.parsers2.person.PersonHelper
import com.gridnine.xtrip.common.util.MiscUtil
import groovy.transform.Field

import java.util.Map.Entry
import java.util.stream.Collectors

createStyle(name: "title", h_alignment: "CENTER", v_alignment: "CENTER", wrapText: true)
createStyle(name: "date", format: 'm/d/yy')
//createStyle(name: 'textData', h_alignment: 'CENTER', v_alignment: 'CENTER', leftBorder: 'THIN', rightBorder: 'THIN', topBorder: 'THIN', bottomBorder: 'THIN')
//createStyle(name: 'numberData', parent: 'textData', h_alignment: 'RIGHT')
//createStyle(name: 'wrappedText', parent: 'textData', wrapText: true)

page { "Пассажиры" } {
    PersonData data = prepareData()
    printHead(data)
    printData(data)
    setAutoWidth(data)
}

@Field Map<Gender, String> genderMap = [(Gender.MALE): 'мужской', (Gender.FEMALE): 'женский']
@Field List<EntityReference<Organization>> clients

PersonData prepareData() {
    PersonData result = new PersonData()
    int ind = 1

    clients = parameters['CLIENT']
    Map<EntityReference<Organization>, List<Person>> personMap = [:].withDefault {[]}
    Map<EntityReference<Organization>, Set<DictionaryReference<CostCodeCategory>>> costCodeCategoryMap = new TreeMap<>(new Comparator<EntityReference<Organization>>() {
       public int compare(EntityReference<Organization> o1, EntityReference<Organization> o2) {
            if (clients.contains(o1) && !clients.contains(o2)) {
                return -1
            }
            if (!clients.contains(o1) && clients.contains(o2)) {
                return 1
            }
            if (clients.contains(o1) && clients.contains(o2)) {
                return clients.indexOf(o1).compareTo(clients.indexOf(o2))
            }
            if (o1 != null && o2 != null) {
                if (o1.getCaption() != null && o2.getCaption() != null) {
                    return o1.getCaption().compareTo(o2.getCaption())
                }
                if (o1.getCaption() != null && o2.getCaption() == null) {
                    return -1
                }
                if (o1.getCaption() == null && o2.getCaption() != null) {
                    return 1
                }
                return 0
            }
            if (o1 != null && o2 == null) {
                return -1
            }
            if (o1 == null && o2 != null) {
                return 1
            }
            return 0
       }
    })

    allTickets.each { PersonIndex idx ->
        Person person = EntityStorage.get().resolve(idx.source)?.entity
        def personOrganization = ProfileHelper.getOrganizationForPerson(person)
        if (!personOrganization.isEmpty()) {
            personMap.get(personOrganization.get(0)).add(person)
        }
        person.getCostCodes().each {PersonCostCode costCode ->
            Set<DictionaryReference<CostCodeCategory>> categories = costCodeCategoryMap.get(costCode.getOrganization())
            if (categories == null) {
                categories = new TreeSet(new Comparator<DictionaryReference<CostCodeCategory>>() {
                    public int compare(DictionaryReference<CostCodeCategory> o1, DictionaryReference<CostCodeCategory> o2) {
                        Organization organization = EntityStorage.get().resolve(costCode.getOrganization())?.entity
                        if (organization) {
                            List<DictionaryReference<CostCodeCategory>> orgCategories =
                                organization.getCostCodeProperties().stream()
                                    .map({item -> item.getCategory()})
                                    .filter({item -> item != null})
                                    .collect(Collectors.toList())
                            if (orgCategories.contains(o1) && !orgCategories.contains(o2)) {
                                return -1
                            }
                            if (!orgCategories.contains(o1) && orgCategories.contains(o2)) {
                                return 1
                            }
                            if (orgCategories.contains(o1) && orgCategories.contains(o2)) {
                                return orgCategories.indexOf(o1).compareTo(orgCategories.indexOf(o2))
                            }
                        }
                        if (o1 != null && o2 != null) {
                            if (o1.getCode() != null && o2.getCode() != null) {
                                return o1.getCode().compareTo(o2.getCode())
                            }
                            if (o1.getCode() != null && o2.getCode() == null) {
                                return -1
                            }
                            if (o1.getCode() == null && o2.getCode() != null) {
                                return 1
                            }
                            return 0
                        }
                        if (o1 != null && o2 == null) {
                            return -1
                        }
                        if (o1 == null && o2 != null) {
                            return 1
                        }
                        return 0
                    }
                })
            }
            def category = costCode.costCodeProperties?.category
            if (category) {
                categories.add(category)
            }
            costCodeCategoryMap.put(costCode.getOrganization(), categories)
        }
    }

    personMap.entrySet().forEach() { Map.Entry<EntityReference<Organization>, List<Person>> entry ->
        def persons = entry.getValue()

        persons.each { Person person ->
                PersonRow row = new PersonRow()
                row.rec = ind
                row.code = person.code
                result.hasCode |= (false || row.code?.trim())

                row.registrationIdKz = person.registrationIdKz
                result.hasRegistrationIdKz |= (false || row.registrationIdKz?.trim())

                row.gender = genderMap.get(person.gender);
                result.hasGender |= (false || row.gender?.trim())

                //Employments
                prepareEmp(result.employmentsVisibility, row, person)

                row.title = EnumUtil.nameOf(person.title)
                result.hasTitle |= (false || row.title?.trim())

                row.birthdate = person.birthday
                result.hasBirthdate |= (false || row.birthdate)

                // ФИО
                prepareNames(result.namesVisibility, row, person)
                // Паспорта
                preparePasports(result.pasportsVisibility, row, person)
                // Emails
                prepareEmails(result.emailsVisibility, row.emails, person)
                // Рабочие телефоны
                preparePhone(result.workPhonesVisibility, row.workPhones, person,
                        CommunicationType.WORK_PHONE)
                // Мобильные телефоны
                preparePhone(result.mobilePhonesVisibility, row.mobilePhones, person,
                        CommunicationType.MOBILE)
                // Air cards
                prepareAirCards(result.airCardsVisibility, row, person)
                // Rail cards
                prepareRailCards(result.railCardsVisibility, row, person)
                // Hotel cards
                prepareHotelCards(result.hotelCardsVisibility, row, person)
                // Car cards
                prepareCarCards(result.carCardsVisibility, row, person)
                // UDID
                prepareUdids(result.udidsVisibility, row, person, costCodeCategoryMap)
                // Remarks
                prepareRemarks(result.remarksVisibility, row, person)
                // Сorteos
                prepareCorteos(result.corteosVisibility, row, person)

                prepareProfileGroups(result.profileGroupsVisibility, row, person)

                row.notCreateCorteosAccount = person.getCorteosSettings().isNotCreateAccount();
                result.hasNotCreateCorteosAccount |= (false || row.notCreateCorteosAccount?.booleanValue())

                row.comment = person.notes;
                result.hasComment |= (false || row.comment?.trim())

                row.role = person.aclSettings.aclRole?.caption?.trim()
                result.hasRole |= (false || person.aclSettings.aclRole?.caption?.trim())

                result.rows.add(row)
                ind++
        }
    }
    return result
}

void printHead(PersonData data) {
    moveTo(0, getColumnIndex()); text("Номер\nп/п", "title", 0, 2)
    moveTo(2, getColumnIndex()); text(ExcelColumns.ROW_NUMBER)

    nextColumn()
    moveTo(0, getColumnIndex()); text("Режим\nдобавления", null, 0, 2)
    moveTo(2, getColumnIndex()); text(ExcelColumns.APPEND_MODE)

    if (data.hasCode) {
        nextColumn()
        moveTo(0, getColumnIndex()); text("Код", null, 0, 2)
        moveTo(2, getColumnIndex()); text(ExcelColumns.CODE)
    }
    if (data.hasRegistrationIdKz) {
        nextColumn()
        moveTo(0, getColumnIndex()); text("ИИН", null, 0, 2)
        moveTo(2, getColumnIndex()); text(ExcelColumns.INDIVIDUAL_IDENTIFICATION_NUMBER)
    }
    //nextColumn(); text(ExcelColumns.USE_HOLDING)
    if (data.hasGender) {
        nextColumn()
        moveTo(0, getColumnIndex()); text("Пол", null, 0, 2)
        moveTo(2, getColumnIndex()); text(ExcelColumns.PERSON_GENDER)
    }
    if (data.hasBirthdate) {
        nextColumn()
        moveTo(0, getColumnIndex()); text("Дата\nрождения", null, 0, 2)
        moveTo(2, getColumnIndex()); text(ExcelColumns.PERSON_BIRTHDAY)
    }
    //Employments
    printEmpHead(data.employmentsVisibility)

    if (data.hasTitle) {
        nextColumn()
        moveTo(0, getColumnIndex()); text("Титул", null, 0, 2)
        moveTo(2, getColumnIndex()); text(ExcelColumns.TITLE)
    }

    // ФИО
    printNamesHead(data.namesVisibility)
    // Паспортные данные
    printPasportsHead(data.pasportsVisibility)
    // Эл. почта
    printEmailsHead(data.emailsVisibility)
    // Рабочие телефоны
    printPhoneHead(data.workPhonesVisibility, "Телефон (рабочий)",
            ExcelColumns.WORK_PHONE_COUNTRY_PREFIX, ExcelColumns.WORK_PHONE_CITY_PREFIX,
            ExcelColumns.WORK_PHONE_NUMBER_PREFIX, ExcelColumns.WORK_PHONE_INTERNAL_PREFIX,
            ExcelColumns.WORK_PHONE_ORGANIZATION_CODE_PREFIX)
    // Мобильные телефоны
    printPhoneHead(data.mobilePhonesVisibility, "Телефон (мобильный)",
            ExcelColumns.MOB_PHONE_COUNTRY_PREFIX, ExcelColumns.MOB_PHONE_CITY_PREFIX,
            ExcelColumns.MOB_PHONE_NUMBER_PREFIX, ExcelColumns.MOB_PHONE_INTERNAL_PREFIX,
            ExcelColumns.MOB_PHONE_ORGANIZATION_CODE_PREFIX)
    // Air cards
    printAirCardsHead(data.airCardsVisibility)
    // Rail cards
    printRailCardsHead(data.railCardsVisibility)
    // Hotel cards
    printHotelCardsHead(data.hotelCardsVisibility)
    // Car cards
    printCarCardsHead(data.carCardsVisibility)
    // UDID
    printUdidsHead(data.udidsVisibility)
    // Remarks
    printRemarksHead(data.remarksVisibility)
    // Сorteos
    printCorteosHead(data.corteosVisibility)
    // ProfileGroups
    printProfileGroupsHead(data.profileGroupsVisibility)

    if (data.hasNotCreateCorteosAccount) {
        nextColumn()
        moveTo(0, getColumnIndex()); text("Не создавать пользователя\nв Corteos", null, 0, 2)
        moveTo(2, getColumnIndex()); text(ExcelColumns.CORTEOS_NOT_CREATE_ACCOUNT)
    }

    if (data.hasComment) {
        nextColumn()
        moveTo(0, getColumnIndex()); text("Комментарий", null, 0, 2)
        moveTo(2, getColumnIndex()); text(ExcelColumns.COMMENT)
    }

    if (data.hasRole) {
        nextColumn()
        moveTo(0, getColumnIndex()); text("Роль", null, 0, 2)
        moveTo(2, getColumnIndex()); text(ExcelColumns.ROLE)
    }

    data.lastColumn = getColumnIndex()
}

void printData(PersonData data) {
    setStyle(null)
    data.rows.each { PersonRow row ->
        nextRow();
        number(row.rec)
        nextColumn(); bool(true)
        if (data.hasCode) {
            nextColumn(); text(row.code)
        }
        if (data.hasRegistrationIdKz) {
            nextColumn(); text(row.registrationIdKz)
        }
        //nextColumn(); text(ExcelColumns.USE_HOLDING)
        if (data.hasGender) {
            nextColumn(); text(row.gender)
        }
        if (data.hasBirthdate) {
            nextColumn(); date(row?.birthdate, "date")
            setStyle(null)
        }

        //Employments
        printEmpData(data.employmentsVisibility, row)

        if (data.hasTitle) {
            nextColumn(); text(row.title)
        }

        // ФИО
        printNamesData(data.namesVisibility, row)
        // Паспортные данные
        printPasportsData(data.pasportsVisibility, row)
        // Эл. почта
        printEmailsData(data.emailsVisibility, row)
        // Рабочие телефоны
        printPhoneData(data.getWorkPhonesVisibility(), row.workPhones)
        // Мобильные телефоны
        printPhoneData(data.getMobilePhonesVisibility(), row.mobilePhones)
        // Air cards
        printAirCardsData(data.airCardsVisibility, row)
        // Rail cards
        printRailCardsData(data.railCardsVisibility, row)
        // Hotel cards
        printHotelCardsData(data.hotelCardsVisibility, row)
        // Car cards
        printCarCardsData(data.carCardsVisibility, row)
        // UDID
        printUdidsData(data.udidsVisibility, row)
        // Remarks
        printRemarksData(data.remarksVisibility, row)
        // Сorteos
        printCorteosData(data.corteosVisibility, row)
        // ProfileGroups
        printProfileGroupsData(data.profileGroupsVisibility, row)

        if (data.hasNotCreateCorteosAccount) {
            nextColumn(); bool(row.notCreateCorteosAccount)
        }

        if (data.hasComment) {
            nextColumn(); text(row.comment)
        }

        if (data.hasRole) {
            nextColumn(); text(row.role)
        }
    }
}

// =====================================================================================================================
def setAutoWidth(PersonData data) {
    nextRow()
    //rowHeight(15)

    data.lastColumn.times {
        columnAutoWidth(true)
        nextColumn()
    }
}

class PersonData {
    List<PersonRow> rows = new ArrayList();
    boolean hasCode;
    boolean hasRegistrationIdKz;
    boolean hasGender;
    List<EmploymentVisibility> employmentsVisibility = new ArrayList<>();
    boolean hasTitle;
    boolean hasBirthdate;
    boolean hasNotCreateCorteosAccount;
    List<NamesVisibility> namesVisibility = new ArrayList<>();
    List<PasportsVisibility> pasportsVisibility = new ArrayList<>();
    List<EmailVisibility> emailsVisibility = new ArrayList<>();
    List<PhoneVisibility> workPhonesVisibility = new ArrayList<>();
    List<PhoneVisibility> mobilePhonesVisibility = new ArrayList<>();
    List<AirCardVisibility> airCardsVisibility = new ArrayList<>();
    List<RailCardVisibility> railCardsVisibility = new ArrayList<>();
    List<HotelCardVisibility> hotelCardsVisibility = new ArrayList<>();
    List<CarCardVisibility> carCardsVisibility = new ArrayList<>();
    List<UdidVisibility> udidsVisibility = new ArrayList<>();
    List<RemarkVisibility> remarksVisibility = new ArrayList<>();
    List<CorteosVisibility> corteosVisibility = new ArrayList<>();
    List<ProfileGroupVisibility> profileGroupsVisibility = new ArrayList<>();
    boolean hasComment;
    boolean hasRole;
    int lastColumn = 0
}

class PersonRow {
    int rec;
    String code;
    String registrationIdKz;
    boolean useHolding;
    boolean notCreateCorteosAccount;
    String gender;
    List<EmploymentData> employments = new ArrayList<>();
    String title;
    Date birthdate;
    List<NameData> names = new ArrayList<>();
    List<PasportData> pasports = new ArrayList<>();
    List<EmailData> emails = new ArrayList<>();
    List<PhoneData> workPhones = new ArrayList<>();
    List<PhoneData> mobilePhones = new ArrayList<>();
    List<AirCardData> airCards = new ArrayList<>();
    List<RailCardData> railCards = new ArrayList<>();
    List<HotelCardData> hotelCards = new ArrayList<>();
    List<CarCardData> carCards = new ArrayList<>();
    List<UdidData> udids = new ArrayList<>();
    List<RemarkData> remarks = new ArrayList<>();
    List<CorteosData> corteosCodes = new ArrayList<>();
    List<ProfileGroupData> profileGroups = new ArrayList<>();
    String comment;
    String role;
}

// =================================== Employments ==============================================
class EmploymentVisibility {
    boolean hasOrgCode
    boolean hasOpened
    boolean hasClosed
    boolean hasIsMainEmp
    boolean hasEmpCategory
}

class EmploymentData {
    String orgCode
    Date opened
    Date closed
    boolean isMainEmp
    String empCategory
}

void prepareEmp(List<EmploymentVisibility> visibilities,
                PersonRow row, Person person) {

    List<PersonEmployment> employments = person.getEmployments().stream()
            .filter({ emp -> emp.organization != null})
            .collect(Collectors.toList())

    if (employments.size() > visibilities.size()) {
        (employments.size() - visibilities.size()).times {
            visibilities.add(new EmploymentVisibility())
        }
    }
    employments.eachWithIndex { PersonEmployment employment, empInd ->

        EmploymentVisibility vis = visibilities.get(empInd)
        EmploymentData data = new EmploymentData()

        data.orgCode = EntityStorage.get().resolve(employment.organization)?.entity?.code
        vis.hasOrgCode |= TextUtil.nonBlank(data.orgCode?.trim())

        data.opened = employment.opened
        vis.hasOpened |= data.opened != null

        data.closed = employment.closed
        vis.hasClosed |= data.closed != null

        data.isMainEmp = employment.mainEmployment
        vis.hasIsMainEmp = employment.mainEmployment

        EmployeeCategory empCategory =
                GeneralProductHelper.getEmployeeCategory(employment.organization, person)
        data.empCategory = L10nStringHelper.getValue(empCategory?.name, LocaleHelper.RU_LOCALE, true)
        vis.hasEmpCategory |= TextUtil.nonBlank(data.empCategory?.trim())

        row.employments.add(data)
    }
    row.employments.sort(new Comparator<EmploymentData>() {
        @Override
        int compare(EmploymentData o1, EmploymentData o2) {
            int a1 = o1.isMainEmp ? 1 : 0
            int a2 = o2.isMainEmp ? 1 : 0
            return a2 - a1
        }
    })
    visibilities.sort(new Comparator<EmploymentVisibility>() {
        @Override
        int compare(EmploymentVisibility o1, EmploymentVisibility o2) {
            int a1 = o1.hasIsMainEmp ? 1 : 0
            int a2 = o2.hasIsMainEmp ? 1 : 0
            return a2 - a1
        }
    })
}

void printEmpHead(List<EmploymentVisibility> visibilities) {
    visibilities.eachWithIndex { EmploymentVisibility vis, i ->
        int ind = i + 1;
        int colStart = getColumnIndex() + 1

        if (vis.hasOrgCode) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Код\nорганизации")
            moveTo(2, getColumnIndex()); text(ExcelColumns.EMP_ORG_CODE + ind)
        }
        if (vis.hasOpened) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Дата начала\nработы")
            moveTo(2, getColumnIndex()); text(ExcelColumns.EMP_OPENED + ind)
        }
        if (vis.hasClosed) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Дата окончания\nработы")
            moveTo(2, getColumnIndex()); text(ExcelColumns.EMP_CLOSED + ind)
        }
        if (vis.hasIsMainEmp) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Основное\nместо работы")
            moveTo(2, getColumnIndex()); text(ExcelColumns.EMP_IS_MAIN + ind)
        }
        if (vis.hasEmpCategory) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Категория")
            moveTo(2, getColumnIndex()); text(ExcelColumns.EMP_CATEGORY + ind)
        }
        int colEnd = getColumnIndex()
        if (colEnd >= colStart) {
            moveTo(0, colStart); text("Место работы", null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printEmpData(List<EmploymentVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { EmploymentVisibility vis, i ->
        EmploymentData data = row.employments[i]

        if (vis.hasOrgCode) {
            nextColumn(); text(data?.orgCode)
        }
        if (vis.hasOpened) {
            nextColumn(); date(data?.opened, "date")
            setStyle(null)
        }
        if (vis.hasClosed) {
            nextColumn(); date(data?.closed, "date")
            setStyle(null)
        }
        if (vis.hasIsMainEmp) {
            nextColumn(); bool(data?.isMainEmp)
        }
        if (vis.hasEmpCategory) {
            nextColumn(); text(data?.empCategory)
        }
    }
}


// =================================== ФИО ========================================

class NamesVisibility {
    boolean hasSurname;
    boolean hasName;
    boolean hasMidName;
    boolean hasSecName;
}

class NameData {
    String locale;
    String surname;
    String name;
    String middleName;
    String secName;
}

void prepareNames(List<NamesVisibility> visibilities, PersonRow row, Person person) {
    Set<Locale> locs = new TreeSet<>({ l1, l2 -> l1?.language?.compareToIgnoreCase('RU')?.abs() <=> l2?.language?.compareToIgnoreCase('RU')?.abs() ?: l1?.language <=> l2?.language })
    locs.addAll(person.lastName?.values.keySet())
    locs.addAll(person.firstName?.values.keySet())
    locs.addAll(person.middleName?.values.keySet())
    locs.addAll(person.secondName?.values.keySet())
    locs.removeAll { it == null }
    if (locs.size() > visibilities.size()) {
        (locs.size() - visibilities.size()).times { visibilities.add(new NamesVisibility()) }
    }
    locs.eachWithIndex { Locale loc, locInd ->
        NamesVisibility nv = visibilities.get(locInd)
        NameData nameData = new NameData()
        nameData.locale = loc.language

        nameData.surname = L10nStringHelper.getValue(person.lastName, loc, true)
        nv.hasSurname |= (false || nameData.surname?.trim())

        nameData.name = L10nStringHelper.getValue(person.firstName, loc, true)
        nv.hasName |= (false || nameData.name?.trim())

        nameData.middleName = L10nStringHelper.getValue(person.middleName, loc, true)
        nv.hasMidName |= (false || nameData.middleName?.trim())

        nameData.secName = L10nStringHelper.getValue(person.secondName, loc, true)
        nv.hasSecName |= (false || nameData.secName?.trim())

        row.names.add(nameData)
    }
}

void printNamesHead(List<NamesVisibility> visibilities) {
    visibilities.eachWithIndex { NamesVisibility nv, i ->
        int ind = i + 1
        int nameColStart = getColumnIndex() + 1

        nextColumn()
        moveTo(1, getColumnIndex()); text("Язык")
        moveTo(2, getColumnIndex()); text(ExcelColumns.FIO_LOCALE_PREFIX + ind)

        if (nv.hasSurname) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Фамилия")
            moveTo(2, getColumnIndex()); text(ExcelColumns.FIO_SURNAME_PREFIX + ind)
        }
        if (nv.hasName) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Имя")
            moveTo(2, getColumnIndex()); text(ExcelColumns.FIO_NAME_PREFIX + ind)
        }
        if (nv.hasMidName) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Отчество")
            moveTo(2, getColumnIndex()); text(ExcelColumns.FIO_MIDNAME_PREFIX + ind)
        }
        if (nv.hasSecName) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Второе имя")
            moveTo(2, getColumnIndex()); text(ExcelColumns.FIO_SECNAME_PREFIX + ind)
        }
        int nameColEnd = getColumnIndex()
        moveTo(0, nameColStart); text("ФИО", null, nameColEnd - nameColStart + 1, 0)
        moveTo(2, nameColEnd)
    }
}

void printNamesData(List<NamesVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { NamesVisibility nv, i ->
        NameData nd = row.names[i]

        nextColumn(); text(nd?.locale)
        if (nv.hasSurname) {
            nextColumn(); text(nd?.surname)
        }
        if (nv.hasName) {
            nextColumn(); text(nd?.name)
        }
        if (nv.hasMidName) {
            nextColumn(); text(nd?.middleName)
        }
        if (nv.hasSecName) {
            nextColumn(); text(nd?.secName)
        }
    }
}

// ================================ PASPORTS ===========================================

class PasportsVisibility {
    boolean hasType
    boolean hasLastNameCyrillic
    boolean hasFirstNameCyrillic
    boolean hasMiddleNameCyrillic
    boolean hasSecondNameCyrillic
    boolean hasLastNameLatin
    boolean hasFirstNameLatin
    boolean hasMiddleNameLatin
    boolean hasSecondNameLatin
    boolean hasGender
    boolean hasBirthdate
    boolean hasCitizenship
    boolean hasNumber
    boolean hasIssuedate
    boolean hasExpiredate
    boolean hasBithPlace
    boolean hasIssueCountry
    boolean hasIssueCity
    boolean hasIssueOrg
}

class PasportData {
    String type
    String lastNameCyrillic
    String firstNameCyrillic
    String middleNameCyrillic
    String secondNameCyrillic
    String lastNameLatin
    String firstNameLatin
    String middleNameLatin
    String secondNameLatin
    String gender
    Date birthdate
    String citizenship
    String number
    Date issuedate
    Date expiredate
    String bithPlace
    String issueCountry
    String issueCity
    String issueOrg
}

void preparePasports(List<PasportsVisibility> visibilities, PersonRow row, Person person) {
    List<PersonPassportWrapper> passports = PersonPassportWrapper.wrap(person.passports)
    if (passports.size() > visibilities.size()) {
        (passports.size() - visibilities.size()).times {
            visibilities.add(new PasportsVisibility())
        }
    }
    passports.eachWithIndex { PersonPassportWrapper psp, pspInd ->
        PasportsVisibility pv = visibilities.get(pspInd)
        PasportData pspData = new PasportData()

        pspData.type = PersonHelper.getPassportCode(psp.type)
        pv.hasType |= (false || pspData.type?.trim())

        pspData.lastNameCyrillic = psp.lastNameCyrillic
        pv.hasLastNameCyrillic |= (false || pspData.lastNameCyrillic?.trim())

        pspData.firstNameCyrillic = psp.firstNameCyrillic
        pv.hasFirstNameCyrillic |= (false || pspData.firstNameCyrillic?.trim())

        pspData.middleNameCyrillic = psp.middleNameCyrillic
        pv.hasMiddleNameCyrillic |= (false || pspData.middleNameCyrillic?.trim())

        pspData.secondNameCyrillic = psp.secondNameCyrillic
        pv.hasSecondNameCyrillic |= (false || pspData.secondNameCyrillic?.trim())

        pspData.lastNameLatin = psp.lastNameLatin
        pv.hasLastNameLatin |= (false || pspData.lastNameLatin?.trim())

        pspData.firstNameLatin = psp.firstNameLatin
        pv.hasFirstNameLatin |= (false || pspData.firstNameLatin?.trim())

        pspData.middleNameLatin = psp.middleNameLatin
        pv.hasMiddleNameLatin |= (false || pspData.middleNameLatin?.trim())

        pspData.secondNameLatin = psp.secondNameLatin
        pv.hasSecondNameLatin |= (false || pspData.secondNameLatin?.trim())

        pspData.gender = genderMap.get(psp.gender)
        pv.hasGender |= (false || pspData.gender?.trim())

        pspData.birthdate = psp.birthday
        pv.hasBirthdate |= (false || pspData.birthdate)

        pspData.citizenship = psp.citizenship?.code
        pv.hasCitizenship |= (false || pspData.citizenship?.trim())

        pspData.bithPlace = psp.birthPlace
        pv.hasBithPlace |= (false || pspData.bithPlace?.trim())

        pspData.number = psp.number
        pv.hasNumber |= (false || pspData.number?.trim())

        pspData.issuedate = psp.issued
        pv.hasIssuedate |= (false || pspData.issuedate)

        pspData.expiredate = psp.expired
        pv.hasExpiredate |= (false || pspData.expiredate)

        pspData.issueCountry = psp.originCountry?.code
        pv.hasIssueCountry |= (false || pspData.issueCountry?.trim())

        pspData.issueCity = psp.issueCity
        pv.hasIssueCity |= (false || pspData.issueCity?.trim())

        pspData.issueOrg = psp.issueOrganization
        pv.hasIssueOrg |= (false || pspData.issueOrg?.trim())

        row.pasports.add(pspData)
    }
}

void printPasportsHead(List<PasportsVisibility> visibilities) {
    visibilities.eachWithIndex { PasportsVisibility pv, i ->
        int ind = i + 1;
        int pspColStart = getColumnIndex() + 1

        if (pv.hasType) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Тип")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_TYPE_PREFIX + ind)
        }
        if (pv.hasLastNameCyrillic) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Фамилия\n(Кириллица)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_SURNAME_CYRILLIC_PREFIX + ind)
        }
        if (pv.hasFirstNameCyrillic) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Имя\n(Кириллица)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_NAME_CYRILLIC_PREFIX + ind)
        }
        if (pv.hasMiddleNameCyrillic) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Отчество\n(Кириллица)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_MIDNAME_CYRILLIC_PREFIX + ind)
        }
        if (pv.hasSecondNameCyrillic) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Второе имя\n(Кириллица)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_SECNAME_CYRILLIC_PREFIX + ind)
        }
        if (pv.hasLastNameLatin) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Фамилия\n(Латиница)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_SURNAME_LATIN_PREFIX + ind)
        }
        if (pv.hasFirstNameLatin) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Имя\n(Латиница)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_NAME_LATIN_PREFIX + ind)
        }
        if (pv.hasMiddleNameLatin) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Отчество\n(Латиница)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_MIDNAME_LATIN_PREFIX + ind)
        }
        if (pv.hasSecondNameLatin) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Второе имя\n(Латиница)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_SECNAME_LATIN_PREFIX + ind)
        }
        if (pv.hasGender) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Пол")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_GENDER_PREFIX + ind)
        }
        if (pv.hasBirthdate) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Дата\nрождения")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_BIRTHDAY_PREFIX + ind)
        }
        if (pv.hasCitizenship) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Граждан-\nство")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_CITIZENSHIP_PREFIX + ind)
        }
        if (pv.hasBithPlace) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Место\nрождения")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_BIRTH_PLACE_PREFIX + ind)
        }
        if (pv.hasNumber) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Номер")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_NUMBER_PREFIX + ind)
        }
        if (pv.hasIssuedate) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Дата\nвыдачи")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_ISSUE_DATE_PREFIX + ind)
        }
        if (pv.hasExpiredate) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Срок\nдействия")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_EXPIRE_DATE_PREFIX + ind)
        }
        if (pv.hasIssueCountry) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Страна\nвыдачи")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_COUNTRY_PREFIX + ind)
        }
        if (pv.hasIssueCity) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Город\nвыдачи")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_CITY_PREFIX + ind)
        }
        if (pv.hasIssueOrg) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Кем\nвыдано")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PSP_ORG_PREFIX + ind)
        }
        int pspColEnd = getColumnIndex()
        if (pspColEnd > pspColStart) {
            moveTo(0, pspColStart); text("Паспортные данные", null, pspColEnd - pspColStart + 1, 0)
            moveTo(2, pspColEnd)
        }
    }
}

void printPasportsData(List<PasportsVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { PasportsVisibility pv, i ->
        PasportData psp = row.pasports[i]

        if (pv.hasType) {
            nextColumn(); text(psp?.type)
        }
        if (pv.hasLastNameCyrillic) {
            nextColumn(); text(psp?.lastNameCyrillic)
        }
        if (pv.hasFirstNameCyrillic) {
            nextColumn(); text(psp?.firstNameCyrillic)
        }
        if (pv.hasMiddleNameCyrillic) {
            nextColumn(); text(psp?.middleNameCyrillic)
        }
        if (pv.hasSecondNameCyrillic) {
            nextColumn(); text(psp?.secondNameCyrillic)
        }
        if (pv.hasLastNameLatin) {
            nextColumn(); text(psp?.lastNameLatin)
        }
        if (pv.hasFirstNameLatin) {
            nextColumn(); text(psp?.firstNameLatin)
        }
        if (pv.hasMiddleNameLatin) {
            nextColumn(); text(psp?.middleNameLatin)
        }
        if (pv.hasSecondNameLatin) {
            nextColumn(); text(psp?.secondNameLatin)
        }
        if (pv.hasGender) {
            nextColumn(); text(psp?.gender)
        }
        if (pv.hasBirthdate) {
            nextColumn(); date(psp?.birthdate, "date")
            setStyle(null)
        }
        if (pv.hasCitizenship) {
            nextColumn(); text(psp?.citizenship)
        }
        if (pv.hasBithPlace) {
            nextColumn(); text(psp?.bithPlace)
        }
        if (pv.hasNumber) {
            nextColumn(); text(psp?.number)
        }
        if (pv.hasIssuedate) {
            nextColumn(); date(psp?.issuedate, "date")
            setStyle(null)
        }
        if (pv.hasExpiredate) {
            nextColumn(); date(psp?.expiredate, "date")
            setStyle(null)
        }
        if (pv.hasIssueCountry) {
            nextColumn(); text(psp?.issueCountry)
        }
        if (pv.hasIssueCity) {
            nextColumn(); text(psp?.issueCity)
        }
        if (pv.hasIssueOrg) {
            nextColumn(); text(psp?.issueOrg)
        }
    }
}

// ================================ EMAILS ===========================================
class EmailVisibility {
    boolean hasSense;
    boolean hasOrgCode;
}

class EmailData {
    String sense;
    String orgCode;
}

void prepareEmails(List<EmailVisibility> visibilities, List<EmailData> resultEmails, Person person) {
    Collection<PersonCommunication> emails =
            ProfileHelper.filterCommunications(person.communications,
                    Collections.singleton(CommunicationType.EMAIL))
    emails.removeAll { !it.sense?.trim() }
    if (emails.size() > visibilities.size()) {
        (emails.size() - visibilities.size()).times {
            visibilities.add(new EmailVisibility())
        }
    }
    emails.eachWithIndex { PersonCommunication com, i ->
        EmailVisibility vis = visibilities.get(i)
        EmailData emailData = new EmailData()

        emailData.sense = com.sense
        vis.hasSense |= (false || emailData.sense?.trim())

        emailData.orgCode = EntityStorage.get().resolve(com.organization)?.entity?.code
        vis.hasOrgCode |= (false || emailData.orgCode?.trim())

        resultEmails.add(emailData)
    }
}

void printEmailsHead(List<EmailVisibility> visibilities) {
    visibilities.eachWithIndex { EmailVisibility vis, i ->
        int ind = i + 1;
        int colStart = getColumnIndex() + 1

        if (vis.hasSense) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Адрес")
            moveTo(2, getColumnIndex()); text(ExcelColumns.MAIL_PREFIX + ind)
        }
        if (vis.hasOrgCode) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Код организации")
            moveTo(2, getColumnIndex()); text(ExcelColumns.MAIL_ORGANIZATION_CODE_PREFIX + ind)
        }
        int colEnd = getColumnIndex()
        if (colEnd > colStart) {
            moveTo(0, colStart); text("Эл. почта", null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printEmailsData(List<EmailVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { EmailVisibility vis, i ->
        EmailData data = row.emails[i]

        if (vis.hasSense) {
            nextColumn(); text(data?.sense)
        }
        if (vis.hasOrgCode) {
            nextColumn(); text(data?.orgCode)
        }
    }
}

// ================================ PHONE ==============================================

class PhoneVisibility {
    boolean hasCountryCode;
    boolean hasCityCode;
    boolean hasNumber;
    boolean hasInternal;
    boolean hasOrgCode;
}

class PhoneData {
    String countryCode;
    String cityCode;
    String number;
    String internal;
    String orgCode;
}

void preparePhone(List<PhoneVisibility> visibilities, List<PhoneData> resultPhones,
                  Person person, CommunicationType communicationType) {
    Collection<PersonCommunication> phones =
            ProfileHelper.filterPersonCommunications(person.communications,
                    Collections.singleton(communicationType), null)
    if (phones.size() > visibilities.size()) {
        (phones.size() - visibilities.size()).times {
            visibilities.add(new PhoneVisibility())
        }
    }
    phones.eachWithIndex { PersonCommunication com, i ->
        PhoneVisibility pv = visibilities.get(i)
        PhoneData phoneData = new PhoneData()

        phoneData.countryCode = com.countryCode
        pv.hasCountryCode |= (false || phoneData.countryCode?.trim())

        phoneData.cityCode = com.cityCode
        pv.hasCityCode |= (false || phoneData.cityCode?.trim())

        phoneData.number = com.sense
        pv.hasNumber |= (false || phoneData.number?.trim())

        phoneData.internal = com.internalNumber
        pv.hasInternal |= (false || phoneData.internal?.trim())

        phoneData.orgCode = EntityStorage.get().resolve(com.organization)?.entity?.code
        pv.hasOrgCode |= (false || phoneData.orgCode?.trim())

        resultPhones.add(phoneData)
    }
}

void printPhoneHead(List<PhoneVisibility> visibilities, String baseTitle,
                    String countryLabel, String cityLabel, String numberLabel,
                    String internalLabel, String organizationCodeLabel) {
    visibilities.eachWithIndex { PhoneVisibility vis, i ->
        int ind = i + 1;
        int colStart = getColumnIndex() + 1

        if (vis.hasCountryCode) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Код\nстраны")
            moveTo(2, getColumnIndex()); text(countryLabel + ind)
        }
        if (vis.hasCityCode) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Код\nгорода")
            moveTo(2, getColumnIndex()); text(cityLabel + ind)
        }
        if (vis.hasNumber) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Номер")
            moveTo(2, getColumnIndex()); text(numberLabel + ind)
        }
        if (vis.hasInternal) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Добавочный\nномер")
            moveTo(2, getColumnIndex()); text(internalLabel + ind)
        }
        if (vis.hasOrgCode) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Код организации")
            moveTo(2, getColumnIndex()); text(organizationCodeLabel + ind)
        }
        int colEnd = getColumnIndex()
        if (colEnd > colStart) {
            moveTo(0, colStart); text(baseTitle, null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printPhoneData(List<PhoneVisibility> visibilities, List<PhoneData> phones) {
    visibilities.eachWithIndex { PhoneVisibility vis, i ->
        PhoneData pd = phones[i]

        if (vis.hasCountryCode) {
            nextColumn(); text(pd?.countryCode)
        }
        if (vis.hasCityCode) {
            nextColumn(); text(pd?.cityCode)
        }
        if (vis.hasNumber) {
            nextColumn(); text(pd?.number)
        }
        if (vis.hasInternal) {
            nextColumn(); text(pd?.internal)
        }
        if (vis.hasOrgCode) {
            nextColumn(); text(pd?.orgCode)
        }
    }
}

// ================================ AIR BONUS CARDS ===========================================

class AirCardVisibility {
    boolean hasOwner;
    boolean hasNumber;
    boolean hasCarrier;
}

class AirCardData {
    String owner;
    String number;
    String carrier;
}

void prepareAirCards(List<AirCardVisibility> visibilities, PersonRow row, Person person) {
    List<AirlineBonusCard> cards = person.getAirlineBonusCards();
    if (cards.size() > visibilities.size()) {
        (cards.size() - visibilities.size()).times {
            visibilities.add(new AirCardVisibility())
        }
    }
    cards.eachWithIndex { AirlineBonusCard card, i ->
        AirCardVisibility vis = visibilities.get(i)
        AirCardData data = new AirCardData()

        data.owner = card.nameOnCard
        vis.hasOwner |= (false || data.owner?.trim())

        data.number = card.number
        vis.hasNumber |= (false || data.number?.trim())

        data.carrier = DictHelper.getCodeVariant(card.airline, CodeSystem.IATA, CodeSystem.CRT)
        vis.hasCarrier |= (false || data.carrier?.trim())

        row.airCards.add(data)
    }
}

void printAirCardsHead(List<AirCardVisibility> visibilities) {
    visibilities.eachWithIndex { AirCardVisibility vis, i ->
        int ind = i + 1;
        int colStart = getColumnIndex() + 1

        if (vis.hasOwner) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Владелец")
            moveTo(2, getColumnIndex()); text(ExcelColumns.AIR_CARD_OWNER_PREFIX + ind)
        }
        if (vis.hasNumber) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Номер")
            moveTo(2, getColumnIndex()); text(ExcelColumns.AIR_CARD_NUMBER_PREFIX + ind)
        }
        if (vis.hasCarrier) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Авиакомпания")
            moveTo(2, getColumnIndex()); text(ExcelColumns.AIR_CARD_CARRIER_PREFIX + ind)
        }

        int colEnd = getColumnIndex()
        if (colEnd > colStart) {
            moveTo(0, colStart); text("Авиа карты", null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printAirCardsData(List<AirCardVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { AirCardVisibility vis, i ->
        AirCardData rd = row.airCards[i]

        if (vis.hasOwner) {
            nextColumn(); text(rd?.owner)
        }
        if (vis.hasNumber) {
            nextColumn(); text(rd?.number)
        }
        if (vis.hasCarrier) {
            nextColumn(); text(rd?.carrier)
        }
    }
}

// ================================ RAIL BONUS CARDS ===========================================

class RailCardVisibility {
    boolean hasNumber;
    boolean hasCarrier;
}

class RailCardData {
    String number;
    String carrier;
}

void prepareRailCards(List<RailCardVisibility> visibilities, PersonRow row, Person person) {
    List<RailwayBonusCard> cards = person.getRailwayBonusCards()
    if (cards.size() > visibilities.size()) {
        (cards.size() - visibilities.size()).times {
            visibilities.add(new RailCardVisibility())
        }
    }
    cards.eachWithIndex { RailwayBonusCard card, i ->
        RailCardVisibility vis = visibilities.get(i)
        RailCardData data = new RailCardData()

        data.number = card.number
        vis.hasNumber |= (false || data.number?.trim())

        data.carrier = card.railwayCarrierCode
        vis.hasCarrier |= (false || data.carrier?.trim())

        row.railCards.add(data)
    }
}

void printRailCardsHead(List<RailCardVisibility> visibilities) {
    visibilities.eachWithIndex { RailCardVisibility vis, i ->
        int ind = i + 1;
        int colStart = getColumnIndex() + 1

        if (vis.hasNumber) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Номер")
            moveTo(2, getColumnIndex()); text(ExcelColumns.RAIL_CARD_NUMBER_PREFIX + ind)
        }
        if (vis.hasCarrier) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Перевозчик")
            moveTo(2, getColumnIndex()); text(ExcelColumns.RAIL_CARD_CARRIER_PREFIX + ind)
        }

        int colEnd = getColumnIndex()
        if (colEnd > colStart) {
            moveTo(0, colStart); text("ЖД карты", null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printRailCardsData(List<RailCardVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { RailCardVisibility vis, i ->
        RailCardData rd = row.railCards[i]

        if (vis.hasNumber) {
            nextColumn(); text(rd?.number)
        }
        if (vis.hasCarrier) {
            nextColumn(); text(rd?.carrier)
        }
    }
}

// ================================ HOTEL BONUS CARDS ===========================================

class HotelCardVisibility {
    boolean hasNumber;
    boolean hasBrand;
}

class HotelCardData {
    String number;
    String brand;
}

void prepareHotelCards(List<HotelCardVisibility> visibilities, PersonRow row, Person person) {
    List<HotelBonusCard> cards = person.getHotelBonusCards()
    if (cards.size() > visibilities.size()) {
        (cards.size() - visibilities.size()).times {
            visibilities.add(new HotelCardVisibility())
        }
    }
    cards.eachWithIndex { HotelBonusCard card, i ->
        HotelCardVisibility vis = visibilities.get(i)
        HotelCardData data = new HotelCardData()

        data.number = card.number
        vis.hasNumber |= (false || data.number?.trim())

        data.brand = card.hotelBrand?.code
        vis.hasBrand |= (false || data.brand?.trim())

        row.hotelCards.add(data)
    }
}

void printHotelCardsHead(List<HotelCardVisibility> visibilities) {
    visibilities.eachWithIndex { HotelCardVisibility vis, i ->
        int ind = i + 1;
        int colStart = getColumnIndex() + 1

        if (vis.hasNumber) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Номер")
            moveTo(2, getColumnIndex()); text(ExcelColumns.HOTEL_CARD_NUMBER_PREFIX + ind)
        }
        if (vis.hasBrand) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Бренд")
            moveTo(2, getColumnIndex()); text(ExcelColumns.HOTEL_CARD_BRAND_PREFIX + ind)
        }

        int colEnd = getColumnIndex()
        if (colEnd > colStart) {
            moveTo(0, colStart); text("Отельные карты", null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printHotelCardsData(List<HotelCardVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { HotelCardVisibility vis, i ->
        HotelCardData rd = row.hotelCards[i]

        if (vis.hasNumber) {
            nextColumn(); text(rd?.number)
        }
        if (vis.hasBrand) {
            nextColumn(); text(rd?.brand)
        }
    }
}

// ================================ CAR BONUS CARDS ===========================================

class CarCardVisibility {
    boolean hasNumber;
    boolean hasSupplier;
}

class CarCardData {
    String number;
    String supplier;
}

void prepareCarCards(List<CarCardVisibility> visibilities, PersonRow row, Person person) {
    List<CarBonusCard> cards = person.getCarBonusCards()
    if (cards.size() > visibilities.size()) {
        (cards.size() - visibilities.size()).times {
            visibilities.add(new CarCardVisibility())
        }
    }
    cards.eachWithIndex { CarBonusCard card, i ->
        CarCardVisibility vis = visibilities.get(i)
        CarCardData data = new CarCardData()

        data.number = card.number
        vis.hasNumber |= (false || data.number?.trim())

        data.supplier = card.carSupplier?.code
        vis.hasSupplier |= (false || data.supplier?.trim())

        row.carCards.add(data)
    }
}

void printCarCardsHead(List<CarCardVisibility> visibilities) {
    visibilities.eachWithIndex { CarCardVisibility vis, i ->
        int ind = i + 1;
        int colStart = getColumnIndex() + 1

        if (vis.hasNumber) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Номер")
            moveTo(2, getColumnIndex()); text(ExcelColumns.CAR_CARD_NUMBER_PREFIX + ind)
        }
        if (vis.hasSupplier) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Поставщик")
            moveTo(2, getColumnIndex()); text(ExcelColumns.CAR_CARD_SUPPLIER_PREFIX + ind)
        }

        int colEnd = getColumnIndex()
        if (colEnd > colStart) {
            moveTo(0, colStart); text("Авто карты", null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printCarCardsData(List<CarCardVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { CarCardVisibility vis, i ->
        CarCardData rd = row.carCards[i]

        if (vis.hasNumber) {
            nextColumn(); text(rd?.number)
        }
        if (vis.hasSupplier) {
            nextColumn(); text(rd?.supplier)
        }
    }
}

// ================================ UDIDS ===========================================

class UdidVisibility {
    boolean hasOrgCode;
    boolean hasType;
    boolean hasValue;
    boolean hasLocalValue;
    boolean hasEnValue;
}

class UdidData {
    String orgCode;
    String type;
    String value;
    String localValue;
    String enValue;
}

void prepareUdids(List<UdidVisibility> visibilities, PersonRow row, Person person,
                  Map<EntityReference<Organization>, Set<DictionaryReference<CostCodeCategory>>> costCodeCategoryMap) {

    List<PersonCostCode> costCodes = new ArrayList<>()

    for (Entry<EntityReference<Organization>, TreeSet<DictionaryReference<CostCodeCategory>>> entry : costCodeCategoryMap.entrySet()) {
        EntityReference<Organization> organization = entry.getKey()
        for (DictionaryReference<CostCodeCategory> category : entry.getValue()) {
            PersonCostCode costCode = person.getCostCodes().stream()
                .filter({it -> organization.equals(it.getOrganization()) &&
                    category.equals(it.getCostCodeProperties()?.getCategory())})
                .findFirst().orElse(null)
            if (costCode == null) {
                CostCodeProperties costCodeProperties = new CostCodeProperties()
                costCodeProperties.setCategory(category)
                costCode = new PersonCostCode()
                costCode.setCostCodeProperties(costCodeProperties)
                costCode.setOrganization(organization)
            }

            costCodes.add(costCode)
        }
    }

    if (costCodes.size() > visibilities.size()) {
        (costCodes.size() - visibilities.size()).times {
            visibilities.add(new UdidVisibility())
        }
    }
    costCodes.eachWithIndex { PersonCostCode cc, i ->
        UdidVisibility vis = visibilities.get(i)
        UdidData data = new UdidData()

        data.orgCode = EntityStorage.get().resolve(cc.organization)?.entity?.code
        vis.hasOrgCode |= (false || data.orgCode?.trim())

        CostCodeCategory ccc = DictionaryCache.get().resolveReference(cc.getCostCodeProperties()?.getCategory())
        data.type = ccc?.shortName
        vis.hasType |= (false || data.type?.trim())

        data.value = cc.value
        vis.hasValue |= (false || data.value?.trim())

        data.localValue = cc.localValue
        vis.hasLocalValue |= (false || data.localValue?.trim())

        data.enValue = cc.englishValue
        vis.hasEnValue |= (false || data.enValue?.trim())

        row.udids.add(data)
    }
}

void printUdidsHead(List<UdidVisibility> visibilities) {
    visibilities.eachWithIndex { UdidVisibility vis, i ->
        int ind = i + 1;
        int colStart = getColumnIndex() + 1

        if (vis.hasOrgCode) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Код\nорганизации")
            moveTo(2, getColumnIndex()); text(ExcelColumns.UDID_ORG_CODE_PREFIX + ind)
        }
        if (vis.hasType) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Категория")
            moveTo(2, getColumnIndex()); text(ExcelColumns.UDID_TYPE_PREFIX + ind)
        }
        if (vis.hasValue) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Значение")
            moveTo(2, getColumnIndex()); text(ExcelColumns.UDID_VALUE_PREFIX + ind)
        }
        if (vis.hasLocalValue) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Значение\n(RU)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.UDID_LOCAL_VALUE_PREFIX + ind)
        }
        if (vis.hasEnValue) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Значение\n(EN)")
            moveTo(2, getColumnIndex()); text(ExcelColumns.UDID_EN_VALUE_PREFIX + ind)
        }

        int colEnd = getColumnIndex()
        if (colEnd > colStart) {
            moveTo(0, colStart); text("UDIDS", null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printUdidsData(List<UdidVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { UdidVisibility vis, i ->
        UdidData rd = row.udids[i]

        if (vis.hasOrgCode) {
            nextColumn(); text(rd?.orgCode)
        }
        if (vis.hasType) {
            nextColumn(); text(rd?.type)
        }
        if (vis.hasValue) {
            nextColumn(); text(rd?.value)
        }
        if (vis.hasLocalValue) {
            nextColumn(); text(rd?.localValue)
        }
        if (vis.hasEnValue) {
            nextColumn(); text(rd?.enValue)
        }
    }
}

// ================================ REMARKS ===========================================

class RemarkVisibility {
    boolean hasLineType;
    boolean hasCategory;
    boolean hasSubcategory;
    boolean hasValue;
}

class RemarkData {
    String lineType;
    String category;
    String subcategory;
    String value;
}

void prepareRemarks(List<RemarkVisibility> visibilities, PersonRow row, Person person) {
    List<SabreProfileLine> lines = person.getSabreAdditionalProfileLines()
    if (lines.size() > visibilities.size()) {
        (lines.size() - visibilities.size()).times {
            visibilities.add(new RemarkVisibility())
        }
    }
    lines.eachWithIndex { SabreProfileLine line, i ->
        RemarkVisibility vis = visibilities.get(i)
        RemarkData data = new RemarkData()

        data.lineType = PersonHelper.getSabreProfileLineTypeCode(line?.lineType)
        vis.hasLineType |= (false || data.lineType?.trim())

        data.category = PersonHelper.getSabreProfileLineServiceTypeCode(line?.lineServiceType)
        vis.hasCategory |= (false || data.category?.trim())

        data.subcategory = line?.lineSubsection
        vis.hasSubcategory |= (false || data.subcategory?.trim())

        data.value = line?.lineBody
        vis.hasValue |= (false || data.value?.trim())

        row.remarks.add(data)
    }
}

void printRemarksHead(List<RemarkVisibility> visibilities) {
    visibilities.eachWithIndex { RemarkVisibility vis, i ->
        int ind = i + 1;
        int colStart = getColumnIndex() + 1

        if (vis.hasLineType) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Тип\nстроки")
            moveTo(2, getColumnIndex()); text(ExcelColumns.SABRE_LINE_TYPE_PREFIX + ind)
        }
        if (vis.hasCategory) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Категория")
            moveTo(2, getColumnIndex()); text(ExcelColumns.SABRE_LINE_SERVICE_TYPE_PREFIX + ind)
        }
        if (vis.hasSubcategory) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Подкатегория")
            moveTo(2, getColumnIndex()); text(ExcelColumns.SABRE_LINE_SUBSECTION_PREFIX + ind)
        }
        if (vis.hasValue) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Значение")
            moveTo(2, getColumnIndex()); text(ExcelColumns.SABRE_LINE_TEXT_PREFIX + ind)
        }

        int colEnd = getColumnIndex()
        if (colEnd > colStart) {
            moveTo(0, colStart); text("Ремарки", null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printRemarksData(List<RemarkVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { RemarkVisibility vis, i ->
        RemarkData rd = row.remarks[i]

        if (vis.hasLineType) {
            nextColumn(); text(rd?.lineType)
        }
        if (vis.hasCategory) {
            nextColumn(); text(rd?.category)
        }
        if (vis.hasSubcategory) {
            nextColumn(); text(rd?.subcategory)
        }
        if (vis.hasValue) {
            nextColumn(); text(rd?.value)
        }
    }
}
//    =======================================Corteos===================================
class CorteosVisibility {
   boolean hasOrgCorteosCode;
   boolean hasPassangerCorteosCode;
}

class CorteosData {
    String orgCode;
    String passengerCorteosCode;
}

void prepareCorteos(List<CorteosVisibility> visibilities, PersonRow row, Person person) {
    List<CorteosPersonProfileData> profiles = person.getCorteosSettings()?.getProfileData()
    if (!profiles) {
        return
    }

    if (profiles.size() > visibilities.size()) {
        (profiles.size() - visibilities.size()).times {
            visibilities.add(new CorteosVisibility())
        }
    }
    profiles.eachWithIndex { CorteosPersonProfileData profile, i ->
        CorteosVisibility vis = visibilities.get(i)
        CorteosData data = new CorteosData()

        data.orgCode = Optional.ofNullable(profile.corteosOrganizationId)
                .map({ corteosId ->
                    EntityStorage.get().resolve(CorteosHelper.findOrganizationByCorteosId(corteosId)) })
                .map({ org -> org.entity.code })
                .orElse("");
        vis.hasOrgCorteosCode |= (false || data.orgCode?.trim())

        data.passengerCorteosCode = profile.corteosId ?
                String.valueOf(profile.corteosId) : ""
        vis.hasPassangerCorteosCode |= (false || data.passengerCorteosCode?.trim())

        row.corteosCodes.add(data)
    }
}

void printCorteosHead(List<CorteosVisibility> visibilities) {
    visibilities.eachWithIndex { CorteosVisibility cv, i ->
        int ind = i + 1
        int сorteosColStart = getColumnIndex() + 1

        if (cv.hasPassangerCorteosCode) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Id в Corteos")
            moveTo(2, getColumnIndex()); text(ExcelColumns.CORTEOS_ID_PREFIX + ind)
        }
        if (cv.hasOrgCorteosCode) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Код организации")
            moveTo(2, getColumnIndex()); text(ExcelColumns.CORTEOS_ORG_CODE_PREFIX + ind)
        }
        int сorteosColEnd = getColumnIndex()
        if (сorteosColEnd > сorteosColStart) {
            moveTo(0, сorteosColStart); text("Синхронизация в Corteos", null, сorteosColEnd - сorteosColStart + 1, 0)
            moveTo(2, сorteosColEnd)
        }
    }
}

void printCorteosData(List<CorteosVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { CorteosVisibility vis, i ->
        CorteosData cd = row.corteosCodes[i]

        if (vis.hasPassangerCorteosCode) {
            nextColumn(); text(cd?.passengerCorteosCode)
        }
        if (vis.hasOrgCorteosCode) {
            nextColumn(); text(cd?.orgCode)
        }
    }
}

//    =======================================ProfileGroups===================================
class ProfileGroupData {
    String orgCode;
    String profileGroupName;
}

class ProfileGroupVisibility {
   boolean hasOrgCode;
   boolean hasProfileGroupName;
}

void prepareProfileGroups(List<ProfileGroupVisibility> visibilities, PersonRow row, Person person) {
    List<MiscUtil.Pair<EntityReference<Organization>, ProfileGroupReference>> profileGroups = PersonHelper.getProfileGroups(person)
    if (profileGroups.isEmpty()) {
        return
    }

    if (profileGroups.size() > visibilities.size()) {
        (profileGroups.size() - visibilities.size()).times {
            visibilities.add(new ProfileGroupVisibility())
        }
    }

    profileGroups.eachWithIndex { MiscUtil.Pair<EntityReference<Organization>, ProfileGroupReference> entry, i ->
        ProfileGroupVisibility visibility = visibilities.get(i)
        ProfileGroupData data = new ProfileGroupData()

        data.orgCode = EntityStorage.get().resolve(entry.getFirst())?.entity?.code
        data.profileGroupName = entry.getSecond()?.getCaption()

        def isVisible = TextUtil.nonBlank(data.profileGroupName?.trim()) || TextUtil.nonBlank(data.orgCode?.trim())

        visibility.hasOrgCode |= isVisible
        visibility.hasProfileGroupName |= isVisible

        row.profileGroups.add(data)
    }
}

void printProfileGroupsHead(List<ProfileGroupVisibility> visibilities) {
    visibilities.eachWithIndex { ProfileGroupVisibility vis, i ->
        int ind = i + 1
        int colStart = getColumnIndex() + 1

        if (vis.hasOrgCode) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Код организации")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PROFILE_GROUP_ORG_CODE + ind)
        }
        if (vis.hasProfileGroupName) {
            nextColumn()
            moveTo(1, getColumnIndex()); text("Название")
            moveTo(2, getColumnIndex()); text(ExcelColumns.PROFILE_GROUP_NAME + ind)
        }
        int colEnd = getColumnIndex()
        if (colEnd > colStart) {
            moveTo(0, colStart); text("Группы профилей", null, colEnd - colStart + 1, 0)
            moveTo(2, colEnd)
        }
    }
}

void printProfileGroupsData(List<ProfileGroupVisibility> visibilities, PersonRow row) {
    visibilities.eachWithIndex { ProfileGroupVisibility vis, i ->
        ProfileGroupData data = row.profileGroups[i]

        if (vis.hasOrgCode) {
            nextColumn(); text(data?.orgCode)
        }
        if (vis.hasProfileGroupName) {
            nextColumn(); text(data?.profileGroupName)
        }
    }
}