/**
 * This exporter class creates an XLSX file that has 2 sheets:
 *
 * - one sheet that has the pivot table
 * - one sheet that has the raw data
 *
 * To be able to correctly export the pivot table definition to Excel you need to ensure that:
 * 
 * - only the following aggregator functions are used: min, max, avg, count, countNumbers, variance,
 * varianceP, stdDev and stdDevP
 * - the pivot matrix provided should be {@link Ext.pivot.matrix.Local}
 * - the config {@link Ext.pivot.matrix.Local#calculateAsExcel} is set to `true`
 *
 * This exporter can be used together with the {@link Ext.pivot.plugin.Exporter}.
 */
Ext.define('Ext.exporter.excel.PivotXlsx', {
    extend: 'Ext.exporter.Base',

    alias: [
        'exporter.pivotxlsx'
    ],

    requires: [
        'Ext.exporter.file.ooxml.Excel'
    ],

    config: {
        /**
         * @cfg {Ext.exporter.file.excel.Style} titleStyle
         *
         * Default style applied to the title
         */
        titleStyle: {
            alignment: {
                horizontal: 'Center',
                vertical: 'Center'
            },
            font: {
                fontName: 'Arial',
                family: 'Swiss',
                size: 18,
                color: '#1F497D'
            }
        },

        /**
         * @cfg {Ext.pivot.matrix.Local} matrix
         *
         * Reference to the pivot matrix that needs to be exported
         */
        matrix: null,

        /**
         * @cfg {Ext.exporter.file.ooxml.excel.PivotTableStyleInfo} pivotTableStyle
         *
         * Represent information on style applied to the PivotTable.
         *
         * The following style names are predefined:
         * - `PivotStyleLight1` -> `PivotStyleLight28`
         * - `PivotStyleMedium1` -> `PivotStyleMedium28`
         * - `PivotStyleDark1` -> `PivotStyleDark28`
         *
         */
        pivotTableStyle: {
            name: 'PivotStyleMedium7'
        }

        /**
         * @cfg data
         * @hide
         */
    },

    fileName: 'export.xlsx',
    charset: 'ascii',
    mimeType: 'application/zip',
    binary: true,

    aggregateMap: {
        avg: 'average',
        sum: 'sum',
        count: 'count',
        countNumbers: 'countNums',
        min: 'min',
        max: 'max',
        variance: 'var',
        varianceP: 'varp',
        stdDev: 'stdDev',
        stdDevP: 'stdDevp'
    },

    titleRowHeight: 22.5,
    columnLabelsText: 'Column Labels',
    rowLabelsText: 'Row Labels',
    valuesText: 'Values',
    totalText: 'Total',
    grandTotalText: 'Grand total',

    getContent: function() {
        var me = this,
            matrix = me.getMatrix(),
            excel, ws1, ws2, store, result;

        // if no matrix is available then the Excel file has no data in it
        excel = new Ext.exporter.file.ooxml.Excel({
            properties: {
                title: me.getTitle(),
                author: me.getAuthor()
            }
        });

        store = matrix && matrix.store;

        if (!matrix || !store || !store.isStore || store.isDestroyed) {
            //<debug>
            Ext.raise('No pivot matrix provided to the exporter or there is no store defined' +
                      'on the matrix');
            //</debug>

            excel.addWorksheet({
                rows: [{
                    cells: 'Unable to export the pivot table since no raw data is available'
                }]
            });

            return excel.render();
        }

        //<debug>
        if (matrix && !matrix.calculateAsExcel) {
            Ext.raise('The pivot table calculations are different than what Excel does. ' +
                      'Check "calculateAsExcel" config on the pivot matrix!');
        }
        //</debug>

        ws1 = excel.addWorksheet();
        ws2 = excel.addWorksheet();

        result = me.generateDataSheet({
            store: store,
            worksheet: ws2
        });

        me.generatePivotSheet({
            worksheet: ws1,
            data: result
        });

        result = excel.render();
        excel.destroy();

        return result;
    },

    /**
     * Generates the Excel sheet that contains the data that will be aggregated by the pivot table
     *
     * @param params
     * @return {Object}
     * @private
     */
    generateDataSheet: function(params) {
        var store = params.store,
            ws = params.worksheet,
            result;

        result = this.buildStoreRows(store);
        result.worksheet = ws;

        // let's use cached rendering for performance reasons
        ws.beginRowRendering();
        ws.renderRows([result.fields]);
        ws.renderRows(result.rows);
        ws.endRowRendering();

        return result;
    },

    /**
     * Generates the Excel sheet that contains the pivot table
     *
     * @param params
     * @private
     */
    generatePivotSheet: function(params) {
        var me = this,
            matrix = me._matrix,
            ws = params.worksheet,
            data = params.data;

        // let's use cached rendering for performance reasons
        ws.beginRowRendering();

        me.generatePivotSheetTitle(ws);

        // in Excel the aggregate names should be unique so we need to generate Excel friendly names
        me.setupUniqueAggregateNames(data.fields);

        // let's extract the unique values for used dimensions
        me.setupUniqueValues(matrix.aggregate, data.uniqueValues);
        me.setupUniqueValues(matrix.leftAxis.dimensions, data.uniqueValues);
        me.setupUniqueValues(matrix.topAxis.dimensions, data.uniqueValues);

        me.generatePivotSheetData(params);

        ws.endRowRendering();
    },

    /**
     * Generates the title of the pivot table sheet
     *
     * @param ws
     * @private
     */
    generatePivotSheetTitle: function(ws) {
        ws.renderRow({
            height: this.titleRowHeight,
            cells: [{
                mergeAcross: 5,
                value: this.getTitle(),
                styleId: ws.getWorkbook().addCellStyle(this.getTitleStyle())
            }]
        });
    },

    /**
     * Generates the pivot table definition xml
     *
     * @param params
     * @private
     */
    generatePivotSheetData: function(params) {
        var me = this,
            ws = params.worksheet;

        me.generatePivotConfig(params);
        me.generatePivotLocation(params);
        me.generatePivotDataFields(params);
        me.generatePivotFields(params);
        me.generatePivotRowFields(params);
        me.generatePivotColumnFields(params);

        ws.addPivotTable(params.pivotConfig);
    },

    /**
     * Generates a new pivot table definition object and fills in some configs
     *
     * @param params
     * @private
     */
    generatePivotConfig: function(params) {
        var matrix = this._matrix,
            data = params.data,
            dataWs = data.worksheet,
            pivotConfig;

        pivotConfig = params.pivotConfig = {
            // rowGrandTotals: matrix.rowGrandTotalsPosition !== 'none',
            // colGrandTotals: matrix.colGrandTotalsPosition !== 'none',
            grandTotalCaption: matrix.textGrandTotalTpl,
            location: {
                ref: '',
                firstHeaderRow: 1,
                firstDataRow: 1,
                firstDataCol: 1
            },
            cacheDefinition: {
                cacheSource: {
                    worksheetSource: {
                        ref: dataWs.getTopLeftRef() + ':' + dataWs.getBottomRightRef(),
                        sheet: dataWs.getName()
                    }
                },
                cacheFields: [],
                cacheRecords: {
                    items: data.cache
                }
            },
            pivotTableStyleInfo: this.getPivotTableStyle(),
            pivotFields: [],
            rowFields: [],
            colFields: [],
            dataFields: [],
            rowItems: [],
            colItems: []
        };

        pivotConfig.rowHeaderCaption = this.rowLabelsText;
        pivotConfig.colHeaderCaption = this.columnLabelsText;
        pivotConfig.viewLayoutType = matrix.viewLayoutType;
    },

    /**
     * Generates the location of the pivot table on the excel sheet
     *
     * @param params
     * @private
     */
    generatePivotLocation: function(params) {
        var me = this,
            matrix = me._matrix,
            location = params.pivotConfig.location,
            countAgg = matrix.aggregate.items.length,
            countLeft = matrix.leftAxis.dimensions.items.length,
            countTop = matrix.topAxis.dimensions.items.length;

        params.header = [];
        params.body = [];
        params.totals = [];
        params.dataIndexes = [];
        params.columns = [];

        if (countLeft === 0 && countTop === 0) {
            if (countAgg === 0) {
                me.generatePivotDataEmpty(params);
            }
            else {
                me.generatePivotDataAgg(params);
            }
        }
        else {
            location.firstDataRow += countTop + (countAgg > 1 ? 1 : 0);
            location.firstDataCol = 1;
            location.firstHeaderRow = 1;

            // let's build the Excel pivot table cache data
            me.generatePivotHeader(params);

            if (countAgg === 0) {
                if (countTop && countLeft) {
                    me.generatePivotDataLeftTopAgg(params);
                }
                else if (countLeft) {
                    me.generatePivotDataLeft(params);
                }
                else {
                    me.generatePivotDataTop(params);
                }
            }
            else {
                if (countTop && countLeft) {
                    me.generatePivotDataLeftTopAgg(params);
                }
                else if (countLeft) {
                    me.generatePivotDataLeftAgg(params);
                }
                else {
                    me.generatePivotDataTopAgg(params);
                }
            }

            me.generatePivotBody(params);
            me.generatePivotRowTotals(params);
        }

        me.generatePivotData(params);
    },

    /**
     * If no dimensions are defined for left/top/aggregate then the pivot table needs
     * to have some empty cells defined on that excel sheet. This function does that.
     *
     * @param params
     * @private
     */
    generatePivotDataEmpty: function(params) {
        var i;

        // add 18 empty rows just to mark the pivot table as Excel does
        params.header.push({
            cells: [null, null, null]
        });

        for (i = 0; i < 17; i++) {
            params.header.push({
                cells: [null, null, null]
            });
        }
    },

    /**
     * If there are no dimensions on left/top axis then the pivot table needs to have a special
     * layout on the excel sheet. This function does that.
     *
     * @param params
     * @private
     */
    generatePivotDataAgg: function(params) {
        var matrix = this._matrix,
            location = params.pivotConfig.location,
            countAgg = matrix.aggregate.items.length,
            i, row, row2, record, item;

        row = {
            cells: [{
                value: null
            }]
        };

        row2 = {
            cells: [{
                value: this.totalText
            }]
        };

        params.header.push(row, row2);

        for (i = 0; i < countAgg; i++) {
            item = matrix.aggregate.items[i];

            row.cells.push({
                value: item.excelName
            });

            record = matrix.results.items.map[matrix.grandTotalKey + '/' + matrix.grandTotalKey];

            row2.cells.push({
                value: record ? record.values[item.id] : null
            });
        }

        if (countAgg > 1) {
            location.firstHeaderRow = 0;
        }
    },

    generatePivotDataLeftTopAgg: function(params) {
        var matrix = this._matrix,
            pivotConfig = params.pivotConfig,
            location = pivotConfig.location,
            layout = pivotConfig.viewLayoutType,
            countAgg = matrix.aggregate.items.length,
            countLeft = matrix.leftAxis.dimensions.items.length,
            countTop = matrix.topAxis.dimensions.items.length,
            dataIndexes = params.dataIndexes,
            rows = params.header,
            row = rows[0],
            len = rows.length,
            i, j, item;

        for (i = 0; i < len; i++) {
            if (layout === 'compact') {
                Ext.Array.insert(rows[i].cells, 0, [{
                    value: i === len - 1
                        ? this.rowLabelsText
                        : (i === 0 && countAgg === 1 ? matrix.aggregate.items[0].excelName : null)
                }]);
            }
            else {
                for (j = 0; j < countLeft; j++) {
                    if (i === 0 && j === 0 && countAgg === 1) {
                        item = {
                            value: matrix.aggregate.items[0].excelName
                        };
                    }
                    else {
                        item = {
                            value: i === len - 1
                                ? matrix.leftAxis.dimensions.items[j].dataIndex
                                : null
                        };
                    }

                    Ext.Array.insert(rows[i].cells, j, [item]);
                }
            }
        }

        if (layout === 'compact') {
            row.cells.push({
                value: this.columnLabelsText
            });

            Ext.Array.insert(dataIndexes, 0, [{
                aggregate: false,
                dataIndex: matrix.compactViewKey
            }]);
        }
        else {
            for (i = 0; i < countLeft; i++) {
                Ext.Array.insert(dataIndexes, i, [{
                    aggregate: false,
                    dataIndex: matrix.leftAxis.dimensions.items[i].id
                }]);
            }

            for (i = 0; i < countTop; i++) {
                row.cells.push({
                    value: matrix.topAxis.dimensions.items[i].dataIndex
                });
            }

            if (countAgg > 1) {
                row.cells.push({
                    value: this.valuesText
                });
            }
        }

        this.generatePivotColTotals(params, rows[1]);
        location.firstDataCol = (layout === 'compact' ? 1 : countLeft);
    },

    generatePivotDataLeftTop: function(params) {
        var matrix = this._matrix,
            pivotConfig = params.pivotConfig,
            location = pivotConfig.location,
            layout = pivotConfig.viewLayoutType,
            countLeft = matrix.leftAxis.dimensions.items.length,
            countTop = matrix.topAxis.dimensions.items.length,
            dataIndexes = params.dataIndexes,
            rows = params.header,
            row = rows[0],
            len = rows.length,
            i, j;

        for (i = 0; i < len; i++) {
            if (layout === 'compact') {
                Ext.Array.insert(rows[i].cells, 0, [{
                    value: i === len - 1 ? this.rowLabelsText : null
                }]);
            }
            else {
                for (j = 0; j < countLeft; j++) {
                    Ext.Array.insert(rows[i].cells, j, [{
                        value: i === len - 1 ? matrix.leftAxis.dimensions.items[j].dataIndex : null
                    }]);
                }
            }
        }

        if (layout === 'compact') {
            row.cells.push({
                value: this.columnLabelsText
            });

            Ext.Array.insert(dataIndexes, 0, [{
                aggregate: false,
                dataIndex: matrix.compactViewKey
            }]);
        }
        else {
            for (i = 0; i < countLeft; i++) {
                Ext.Array.insert(dataIndexes, i, [{
                    aggregate: false,
                    dataIndex: matrix.leftAxis.dimensions.items[i].id
                }]);
            }

            for (i = 0; i < countTop; i++) {
                row.cells.push({
                    value: matrix.topAxis.dimensions.items[i].dataIndex
                });
            }
        }

        this.generatePivotColTotals(params, rows[1]);
        location.firstDataCol = (layout === 'compact' ? 1 : countLeft);
    },

    generatePivotDataLeftAgg: function(params) {
        var matrix = this._matrix,
            pivotConfig = params.pivotConfig,
            location = pivotConfig.location,
            layout = pivotConfig.viewLayoutType,
            countAgg = matrix.aggregate.items.length,
            countLeft = matrix.leftAxis.dimensions.items.length,
            dataIndexes = params.dataIndexes,
            row = params.header[0],
            i, item;

        if (layout === 'compact') {
            row.cells.push({
                value: this.rowLabelsText
            });
            dataIndexes.push({
                aggregate: false,
                dataIndex: matrix.compactViewKey
            });
        }
        else {
            for (i = 0; i < countLeft; i++) {
                item = matrix.leftAxis.dimensions.items[i];
                row.cells.push({
                    value: item.dataIndex
                });
                dataIndexes.push({
                    aggregate: false,
                    dataIndex: item.id
                });
            }
        }

        this.generatePivotColTotals(params, row);
        location.firstHeaderRow = countAgg > 1 ? 0 : 1;
        location.firstDataRow = 1;
        location.firstDataCol = (layout === 'compact' ? 1 : countLeft);
    },

    generatePivotDataLeft: function(params) {
        var matrix = this._matrix,
            pivotConfig = params.pivotConfig,
            location = pivotConfig.location,
            layout = pivotConfig.viewLayoutType,
            countLeft = matrix.leftAxis.dimensions.items.length,
            row = params.header[0],
            dataIndexes = params.dataIndexes,
            i, item;

        if (layout === 'compact') {
            row.cells.push({
                value: this.rowLabelsText
            });
            dataIndexes.push({
                aggregate: false,
                dataIndex: matrix.compactViewKey
            });
        }
        else {
            for (i = 0; i < countLeft; i++) {
                item = matrix.leftAxis.dimensions.items[i];
                row.cells.push({
                    value: item.dataIndex
                });
                dataIndexes.push({
                    aggregate: false,
                    dataIndex: item.id
                });
            }
        }

        dataIndexes.push({
            aggregate: false
        });
        location.firstDataCol = (layout === 'compact' ? 1 : countLeft);
    },

    generatePivotDataTopAgg: function(params) {
        var matrix = this._matrix,
            pivotConfig = params.pivotConfig,
            layout = pivotConfig.viewLayoutType,
            countAgg = matrix.aggregate.items.length,
            countTop = matrix.topAxis.dimensions.items.length,
            dataIndexes = params.dataIndexes,
            rows = params.header,
            row = rows[0],
            len = rows.length,
            i;

        for (i = 0; i < len; i++) {
            Ext.Array.insert(rows[i].cells, 0, [{
                value: countAgg === 1 && i === 0 ? matrix.aggregate.items[0].excelName : null
            }]);
        }

        Ext.Array.insert(dataIndexes, 0, [{
            aggregate: false,
            dataIndex: layout === 'compact' ? matrix.compactViewKey : ''
        }]);

        if (layout === 'compact') {
            row.cells.push({
                value: this.columnLabelsText
            });
        }
        else {
            for (i = 0; i < countTop; i++) {
                row.cells.push({
                    value: matrix.topAxis.dimensions.items[i].dataIndex
                });
            }

            if (countAgg > 1) {
                row.cells.push({
                    value: this.valuesText
                });
            }
        }

        this.generatePivotColTotals(params, rows[1]);
    },

    generatePivotDataTop: function(params) {
        var matrix = this._matrix,
            pivotConfig = params.pivotConfig,
            location = pivotConfig.location,
            layout = pivotConfig.viewLayoutType,
            countTop = matrix.topAxis.dimensions.items.length,
            dataIndexes = params.dataIndexes,
            rows = params.header,
            row = rows[0],
            len = rows.length,
            i;

        Ext.Array.insert(dataIndexes, 0, [{
            aggregate: false,
            dataIndex: layout === 'compact' ? matrix.compactViewKey : ''
        }]);

        for (i = 0; i < len; i++) {
            Ext.Array.insert(rows[i].cells, 0, [{
                value: null
            }]);
        }

        if (layout === 'compact') {
            row.cells.push({
                value: this.columnLabelsText
            });
        }
        else {
            for (i = 0; i < countTop; i++) {
                row.cells.push({
                    value: matrix.topAxis.dimensions.items[i].dataIndex
                });
            }
        }

        this.generatePivotColTotals(params, rows[1]);

        // If countTop then we have a 2nd row with all group names listed + Grand Total
        location.firstDataCol = 1;
        location.firstDataRow = countTop + 1;
    },

    generatePivotHeader: function(params) {
        var me = this,
            columns = params.columns,
            rows, i, j, len, length, item, row;

        me.generateTopAxisColumns(params, -1);

        rows = params.header;

        // the pivot table header has at least one row
        rows.push({
            cells: []
        });

        len = columns.length;

        for (i = 0; i < len; i++) {
            row = {
                cells: []
            };

            item = columns[i];
            length = item.length;

            for (j = 0; j < length; j++) {
                row.cells.push({
                    value: item[j].value
                });
            }

            rows.push(row);
        }

        if (len) {
            params.pivotConfig.colItems = Ext.Array.pluck(columns[len - 1], 'colItem');
            params.dataIndexes = columns[len - 1];
        }
    },

    generatePivotBody: function(params, items) {
        var matrix = this._matrix,
            i, len;

        if (!items) {
            items = matrix.leftAxis.getTree();
        }

        len = items.length;

        for (i = 0; i < len; i++) {
            this.generatePivotBodyItem(params, items[i]);
        }
    },

    generatePivotBodyItem: function(params, item) {
        var matrix = this._matrix,
            ws = params.worksheet,
            uniqueValues = params.data.uniqueValues,
            columns = params.dataIndexes,
            length = columns.length,
            layout = params.pivotConfig.viewLayoutType,
            rows = params.body,
            rowItems = params.pivotConfig.rowItems,
            i, col, cell, result, rowItem, row, level;

        rowItem = {
            r: item.level,
            x: [Ext.Array.indexOf(uniqueValues[item.dimension.dataIndex], item.value)]
        };
        row = {
            cells: []
        };

        for (i = 0; i < length; i++) {
            col = columns[i];
            cell = {
                value: null
            };

            if (col.aggregate) {
                result = matrix.results.items.map[item.key + '/' + col.key];
                cell.value = result ? result.values[col.dataIndex] : null;
            }
            else if (col.dataIndex === item.dimensionId) {
                cell.value = item.value;
            }
            else if (layout === 'compact' && col.dataIndex === matrix.compactViewKey) {
                params.styles = params.styles || {};
                level = 'level' + item.level;
                params.styles[level] = params.styles[level] || ws.getWorkbook().addCellStyle({
                    alignment: {
                        horizontal: 'left',
                        indent: item.level * 2
                    }
                });

                cell.styleId = params.styles[level];
                cell.value = item.value;
            }

            row.cells.push(cell);
        }

        rowItems.push(rowItem);
        rows.push(row);

        if (item.children) {
            if (layout === 'tabular') {
                this.generatePivotBodyTabularItem(params, item, item);
            }
            else {
                this.generatePivotBody(params, item.children);
            }
        }
    },

    generatePivotBodyTabularItem: function(params, item) {
        var matrix = this._matrix,
            uniqueValues = params.data.uniqueValues,
            items = item.children,
            len = items.length,
            columns = params.dataIndexes,
            length = columns.length,
            rows = params.body,
            cells = rows[rows.length - 1].cells,
            rowItems = params.pivotConfig.rowItems,
            rowItem = rowItems[rowItems.length - 1],
            i, j, col, cell, result, group;

        for (i = 0; i < len; i++) {
            group = items[i];

            if (i === 0) {
                rowItem.x.push(
                    Ext.Array.indexOf(uniqueValues[group.dimension.dataIndex], group.value)
                );

                for (j = 0; j < length; j++) {
                    col = columns[j];
                    cell = cells[j];

                    if (col.aggregate) {
                        result = matrix.results.items.map[group.key + '/' + col.key];
                        cell.value = result ? result.values[col.dataIndex] : null;
                    }
                    else if (col.dataIndex === group.dimensionId) {
                        cell.value = group.value;
                    }
                }

                if (group.children) {
                    this.generatePivotBodyTabularItem(params, group);
                }
            }
            else {
                this.generatePivotBodyItem(params, group);
            }
        }

        // add group total
        rowItems.push({
            r: item.level,
            t: 'default',
            x: [Ext.Array.indexOf(uniqueValues[item.dimension.dataIndex], item.value)]
        });

        cells = [];

        for (j = 0; j < length; j++) {
            col = columns[j];
            cell = {
                value: null
            };

            if (col.aggregate) {
                result = matrix.results.items.map[item.key + '/' + col.key];
                cell.value = result ? result.values[col.dataIndex] : null;
            }
            else if (col.dataIndex === item.dimensionId) {
                cell.value = item.data[col.dataIndex] + ' ' + this.totalText;
            }

            cells.push(cell);
        }

        rows.push({
            cells: cells
        });
    },

    generatePivotRowTotals: function(params) {
        var matrix = this._matrix,
            countAgg = matrix.aggregate.items.length,
            countLeft = matrix.leftAxis.dimensions.items.length,
            columns = params.dataIndexes,
            length = columns.length,
            rows = params.totals,
            cells = [],
            rowItems = params.pivotConfig.rowItems,
            j, col, cell, result;

        for (j = 0; j < length; j++) {
            col = columns[j];
            cell = {
                value: null
            };

            if (j === 0) {
                cell.value = countLeft ? this.grandTotalText : (countAgg ? this.totalText : null);
            }
            else if (col.aggregate) {
                result = matrix.results.items.map[matrix.grandTotalKey + '/' + col.key];
                cell.value = result ? result.values[col.dataIndex] : null;
            }

            cells.push(cell);
        }

        rows.push({
            cells: cells
        });

        rowItems.push({
            t: 'grand',
            x: 0
        });
    },

    generatePivotColTotals: function(params, row) {
        var matrix = this._matrix,
            colItems = params.pivotConfig.colItems,
            dataIndexes = params.dataIndexes,
            aggregates = matrix.aggregate.items,
            countAgg = matrix.aggregate.items.length,
            countTop = matrix.topAxis.dimensions.items.length,
            i, item;

        if (countTop === 0) {
            for (i = 0; i < countAgg; i++) {
                item = aggregates[i];

                row.cells.push({
                    value: item.excelName
                });
                dataIndexes.push({
                    aggregate: true,
                    key: matrix.grandTotalKey,
                    dataIndex: item.id
                });
                colItems.push({
                    t: 'grand',
                    i: i,
                    x: 0
                });
            }
        }
        else {
            if (countAgg <= 1) {
                row.cells.push({
                    value: this.grandTotalText
                });
                dataIndexes.push({
                    aggregate: true,
                    key: matrix.grandTotalKey,
                    dataIndex: countAgg ? aggregates[0].id : ''
                });
                colItems.push({
                    t: 'grand',
                    x: 0
                });
            }
            else {
                for (i = 0; i < countAgg; i++) {
                    item = aggregates[i];

                    row.cells.push({
                        value: this.totalText + ' ' + item.excelName
                    });
                    dataIndexes.push({
                        aggregate: true,
                        key: matrix.grandTotalKey,
                        dataIndex: item.id
                    });
                    colItems.push({
                        t: 'grand',
                        i: i,
                        x: 0
                    });
                }

            }
        }
    },

    generatePivotData: function(params) {
        var ws = params.worksheet,
            location = params.pivotConfig.location,
            len, i, ref;

        len = params.header.length;

        for (i = 0; i < len; i++) {
            ref = ws.renderRow(params.header[i]);

            if (i === 0) {
                location.ref = ref.first + ':';
            }
        }

        ws.renderRows(params.body);
        ws.renderRows(params.totals);
        location.ref += ws.getBottomRightRef();
    },

    /**
     * The pivot table needs to know what fields are used as data (aggregate) fields. This function
     * generates this list of fields.
     *
     * @param params
     * @private
     */
    generatePivotDataFields: function(params) {
        var matrix = this._matrix,
            fields = params.data.fields,
            aggMap = this.aggregateMap,
            countAgg = matrix.aggregate.items.length,
            i, dimension;

        for (i = 0; i < countAgg; i++) {
            dimension = matrix.aggregate.items[i];

            //<debug>
            if (!aggMap[dimension.aggregator]) {
                Ext.raise('Custom aggregate functions are not supported by this exporter');
            }
            //</debug>

            params.pivotConfig.dataFields.push({
                name: dimension.excelName,
                fld: Ext.Array.indexOf(fields, dimension.dataIndex),
                subtotal: aggMap[dimension.aggregator] || 'sum',
                baseField: 0,
                baseItem: 0
            });
        }
    },

    /**
     * The pivot table needs a list of all fields available in the source sheet and what unique
     * values are available for each field.
     *
     * @param params
     * @private
     */
    generatePivotFields: function(params) {
        var me = this,
            matrix = me._matrix,
            data = params.data,
            fields = data.fields,
            uniqueValues = data.uniqueValues,
            countAgg = matrix.aggregate.items.length,
            i, j, len, length, field, item, dimension;

        // setup pivot fields: the pivot table needs to know what unique values are available
        // for each pivot field, which fields are used on row/column axis and which fields
        // are data fields
        len = fields.length;

        for (i = 0; i < len; i++) {
            field = fields[i];
            item = {
                showAll: false
            };

            for (j = 0; j < countAgg; j++) {
                dimension = matrix.aggregate.items[j];

                if (dimension.dataIndex === field) {
                    item.dataField = true;
                    break;
                }
            }

            /* eslint-disable no-cond-assign */
            if (dimension = me.getDimension(matrix.aggregate, field)) {
                // do nothing; we parsed all aggregates above;
                // we just need its unique values
            }
            else if (dimension = me.getDimension(matrix.leftAxis.dimensions, field)) {
                item.axis = 'axisRow';

                if (dimension.getSortable()) {
                    item.sortType = (dimension.direction === 'ASC' ? 'ascending' : 'descending');
                }
                else {
                    item.sortType = 'manual';
                }
            }
            else if (dimension = me.getDimension(matrix.topAxis.dimensions, field)) {
                item.axis = 'axisCol';

                if (dimension.getSortable()) {
                    item.sortType = (dimension.direction === 'ASC' ? 'ascending' : 'descending');
                }
                else {
                    item.sortType = 'manual';
                }
            }
            /* eslint-enable no-cond-assign */

            if (dimension) {
                item.items = [];
                length = uniqueValues[field].length;

                for (j = 0; j < length; j++) {
                    item.items.push({
                        x: j
                    });
                }

                item.items.push({
                    t: 'default'
                });
            }

            params.pivotConfig.cacheDefinition.cacheFields.push({
                name: field,
                sharedItems: {
                    items: dimension && !dimension.isAggregate ? uniqueValues[field] : []
                }
            });

            params.pivotConfig.pivotFields.push(item);
        }
    },

    /**
     * Generates the list of fields used as left axis dimensions used by the pivot table.
     *
     * @param params
     * @private
     */
    generatePivotRowFields: function(params) {
        var matrix = this._matrix,
            fields = params.data.fields,
            countLeft = matrix.leftAxis.dimensions.items.length,
            i;

        // setup row fields: this informs the pivot table what fields are used on the row axis
        for (i = 0; i < countLeft; i++) {
            params.pivotConfig.rowFields.push({
                x: Ext.Array.indexOf(fields, matrix.leftAxis.dimensions.items[i].dataIndex)
            });
        }
    },

    /**
     * Generates the list of top axis dimensions used by the pivot table.
     *
     * @param params
     * @private
     */
    generatePivotColumnFields: function(params) {
        var matrix = this._matrix,
            fields = params.data.fields,
            countAgg = matrix.aggregate.items.length,
            countTop = matrix.topAxis.dimensions.items.length,
            i;

        // setup col fields: this informs the pivot table what fields are used on the column axis
        for (i = 0; i < countTop; i++) {
            params.pivotConfig.colFields.push({
                x: Ext.Array.indexOf(fields, matrix.topAxis.dimensions.items[i].dataIndex)
            });
        }

        if (countAgg > 1) {
            params.pivotConfig.colFields.push({
                x: -2
            });
        }
    },

    /**
     * This function parses the matrix store and extracts all data and all unique values
     dimension = me.getDimension(matrix.aggregate, field)for each field.
     *
     * @param store
     * @return {Object}
     * @private
     */
    buildStoreRows: function(store) {
        var result = {
                rows: [],
                cache: [],
                fields: [],
                uniqueValues: {}
            },
            fields, i, j, len, length, field, row, record, item, cache;

        fields = store.model.getFields();
        len = fields.length;

        for (i = 0; i < len; i++) {
            field = fields[i].getName();
            result.fields.push(field);
            result.uniqueValues[field] = [];
        }

        length = store.data.length;

        for (i = 0; i < length; i++) {
            row = [];
            cache = [];
            record = store.data.items[i];

            for (j = 0; j < len; j++) {
                field = result.fields[j];
                item = record.get(field);
                row.push({
                    value: item
                });
                cache.push(item);

                if (Ext.Array.indexOf(result.uniqueValues[field], item) === -1) {
                    result.uniqueValues[field].push(item);
                }
            }

            result.rows.push(row);
            result.cache.push(cache);
        }

        return result;
    },

    /**
     * Excel pivot table needs unique aggregate names so we need to ensure the matrix aggregate
     * dimensions are unique
     *
     * @param fields
     * @private
     */
    setupUniqueAggregateNames: function(fields) {
        var aggregates = this._matrix.aggregate,
            len = aggregates.getCount(),
            length = fields.length,
            data = [],
            index, i, j, agg, name, temp;

        for (i = 0; i < len; i++) {
            agg = aggregates.getAt(i);
            name = agg.dataIndex || agg.header;
            temp = name;
            index = 2;

            for (j = 0; j < length; j++) {
                if (String(temp).toLowerCase() === String(fields[j]).toLowerCase() ||
                    Ext.Array.indexOf(data, temp) >= 0) {
                    temp = name + index;
                    index++;
                    j = -1;
                }
            }

            data.push(temp);
            agg.excelName = temp;
        }
    },

    setupUniqueValues: function(dimensions, uniqueValues) {
        var items = dimensions.items,
            len = items.length,
            i, item;

        for (i = 0; i < len; i++) {
            item = items[i];
            uniqueValues[item.dataIndex] = Ext.Array.pluck(item.values.items, 'value');
        }
    },

    /**
     * Find the dimension that matches the dataIndex and return the index
     *
     * @param collection
     * @param name
     * @return {Number}
     * @private
     */
    getDimension: function(collection, name) {
        var index = collection.findIndex('dataIndex', name, 0, false, true);

        return index >= 0 ? collection.getAt(index) : null;
    },

    /* eslint-disable max-len */
    /**
     *  When there's only one aggregate available then the pivot table headers look like this
     *
     *  | USA |   |   |          |     |   |          | USA Sum | Sum |
     *  | 2012|   |   | 2012 Sum | 2013|   | 2013 Sum |         |     |
     *  | 1   | 2 | 3 |          | 1   | 2 |          |         |     |
     *
     *  When there are multiple aggregates available then the pivot table headers look like this
     *
     *  | USA |     |     |     |          |          |     |     |     |     |          |          | USA Sum | USA Avg | Sum | Avg |
     *  | 2012|     |     |     | 2012 Sum | 2012 Avg | 2013|     |     |     | 2013 Sum | 2013 Avg |         |         |     |     |
     *  | 1   |     | 2   |     |          |          | 1   |     | 2   |     |          |          |         |         |     |     |
     *  | sum | avg | sum | avg |          |          | sum | avg | sum | avg |          |          |         |         |     |     |
     *
     *
     * @param params
     * @param colIndex
     * @param items
     * @private
     */
    generateTopAxisColumns: function(params, colIndex, items) {
        /* eslint-enable max-len */
        var matrix = this._matrix,
            uniqueValues = params.data.uniqueValues,
            columns = params.columns,
            aggregates = matrix.aggregate.items,
            ret = [],
            i, j, k, len, length, levels, item, agg, col, level, obj, result, index;

        if (!items) {
            items = matrix.topAxis.getTree();
        }

        len = items.length;
        length = aggregates.length;

        for (i = 0; i < len; i++) {
            item = items[i];

            index = Ext.Array.indexOf(uniqueValues[item.dimension.dataIndex], item.value);
            level = item.level;

            columns[level] = columns[level] || [];

            col = {
                value: item.value == null ? '(blank)' : item.value,
                index: index
            };

            columns[level].push(col);

            if (item.children) {

                result = this.generateTopAxisColumns(params, colIndex, item.children);

                // fill in the current level with empty columns
                levels = result.length;

                for (j = 1; j < levels; j++) {
                    columns[level].push({
                        value: '',
                        empty: true
                    });
                }

                for (j = 0; j < length; j++) {
                    // add group total
                    agg = aggregates[j];
                    obj = {
                        value: col.value + ' ' + (agg.excelName)
                    };
                    columns[level].push(obj);
                    result.push(obj);

                    // fill in the bottom levels with empty columns below the group total
                    levels = columns.length;

                    for (k = level + 1; k < levels; k++) {
                        if (k === levels - 1) {
                            // on the last level we store the dataIndex
                            columns[k].push({
                                aggregate: true,
                                value: '',
                                key: item.key,
                                dataIndex: agg.id,
                                colItem: {
                                    t: 'default',
                                    i: j,
                                    x: index
                                }
                            });
                        }
                        else {
                            columns[k].push({
                                value: '',
                                empty: true
                            });
                        }
                    }
                }

                Ext.Array.insert(ret, ret.length, result);
                colIndex += result.length;

            }
            else {
                if (length <= 1) {
                    ret.push(col);
                    colIndex++;
                    col.aggregate = true;
                    col.colItem = this.getColItem(colIndex, 0, columns);
                }

                if (length === 1) {
                    col.key = item.key;
                    col.dataIndex = aggregates[0].id;
                }
                else if (length > 1) {
                    level++;
                    columns[level] = columns[level] || [];

                    for (j = 0; j < length; j++) {
                        agg = aggregates[j];
                        colIndex++;
                        obj = {
                            aggregate: true,
                            value: agg.excelName,
                            index: j,
                            key: item.key,
                            dataIndex: agg.id
                        };
                        columns[level].push(obj);
                        obj.colItem = this.getColItem(colIndex, j, columns);
                        ret.push(obj);

                        if (j > 0) {
                            columns[level - 1].push({
                                value: '',
                                empty: true
                            });
                        }
                    }
                }

            }
        }

        return ret;
    },

    /**
     * Format an excel pivot table colItem
     *
     * @param colIndex
     * @param aggIndex
     * @param columns
     * @return {Object}
     * @private
     */
    getColItem: function(colIndex, aggIndex, columns) {
        var colItem = {
                i: aggIndex
            },
            r = 0,
            x = [],
            len = columns.length,
            i, col;

        // we need to parse columns from top to bottom on the colIndex and find out what values
        // are empty so we can calculate r & x
        for (i = 0; i < len; i++) {
            col = columns[i][colIndex];

            if (col) {
                if (!col.empty) {
                    x.push(col.index);
                }
                else {
                    // r++;
                }
            }
            else {
                r++;
            }
        }

        colItem.r = r;
        colItem.x = x;

        return colItem;
    }
});
