transfer-ration-glj/app/js/transfer.js

'use strict';

/**
 *
 *
 * @author Zhong
 * @date 2019/2/21
 * @version
 */

const TransferExcel = (function () {
    let curTransferType = 'gl';
    const transferType = {
        // 公路
        gl: 'gl',
        // 内蒙养护
        neimengyh: 'neimengyh',
        // 建筑
        jz: 'jz'
    };
    const $transferModal = $('#transfer');
    const $file = $('#transfer-file');
    const $transfer = $('#transferConfirm');
    const $exportSpread = $('#exportSpread');
    let exportSpread = null;
    let transferData = [];
    //--以防需要可配置的情况
    let fixedCount = 4; //固定列数（顺序号、项目、单位、代号）
    const beginCol = 0;//起始的列
    //固定的这几列的列号映射是动态的，因为有一些子表，这些列进行了合并列的处理
    let colMapping = {
        serialNo: 0,
        name: 1,
        unit: 2,
        code: 3,
        consumeAmt: 4, //消耗量开始的列
    };
    //---
    function isUnDef(v) {
        return typeof v === 'undefined' || v === null;
    }
    //取消所有空格
    function trimAll(v) {
        return typeof v === 'string' ? v.replace(/\s/g, '') : v;
    }
    //单元格是否有数据
    function cellHasData(cell) {
        return cell && cell.value;
    }
    //行是否有数据
    //@param {Object}rowData（行数据） {Array}deduct(排除的列)
    function rowHasData(rowData, deduct = null) {
        for (let col in rowData) {
            if (deduct && deduct.includes(col)) {
                continue;
            }
            let cell = rowData[col];
            if (cell && cell.value) {
                return true;
            }
        }
        return false;
    }
    //行为列头行 //第一列为数值，且后面其他列有数据（不单纯认为是第二列，因为怕有合并序号列）
    function headerRow(rowData) {
        return rowData[0] && rowData[0].value && /^\d+$/.test(rowData[0].value) && rowHasData(rowData, ['0']);

    }
    //去除定额子表数组中的空行(有的表格是在 单位：xx 下面还跟着一些垃圾数据，导致subRation的range范围判定变大了，但是四列后面的数据是空的)
    function simplifyRationTable(arr) {
        let target = [];
        for (let subArr of arr) {
            let emptyRow = subArr.every(function (ele) {
                return ele === null;
            });
            if (emptyRow) {
                continue;
            }
            target.push(subArr);
        }
        return target;
    }
    //将二维数组转换成每个元素都有值的二维数据(去没值元素，因为前四列有可能合并列)
    function simplifyGljTable(arr) {
        let target = [];
        for (let subArr of arr) {
            let subTarget = [];
            for (let ele of subArr) {
                if (ele !== null) {
                    subTarget.push(ele);
                }
            }
            target.push(subTarget);
        }
        return target;
    }

    //获取表头定额名称数据(最后一行为定额末位编码)
    //合并的单元格处理：为了更好的让定额获得对应的定额名称，将合并单元格做填值处理, eg: [a]['合并'] = [a][a]
    //@param {Object}dataTable {Object}range(表头的范围，row, col, rowCount, colCount) {Array}spans(spread解析Excel后的合并数组)
    //@return {Array}
    function getSubRationTable(dataTable, range, spans) {
        let subTable = [];
        for (let i = 0; i < range.rowCount; i++) {
            subTable.push(Array(range.colCount).fill(null));
        }
        //获取合并的单元格填充数据
        let fillArr = [];
        for (let i = 0; i < range.rowCount; i++) {
            let row = range.row + i;
            if (!dataTable[row]) {
                continue;
            }
            for (let j = 0; j < range.colCount; j++) {
                let col = range.col + j;
                let cell = dataTable[row][col];
                //只有有值的单元格，判断合并才有意义，有值且合并列的单元格，每列的值认为都是一样的
                if (cellHasData(cell)) {
                    //是否合并了单元格
                    let span = spans.find(function (data) {
                        return data.row === row && data.col === col;
                    });
                    //这个fillData给subData填值用
                    let fillData = {value: trimAll(cell.value), range: {row: i, col: j}};
                    if (span) {
                        fillData.range.rowCount = span.rowCount;
                        fillData.range.colCount = span.colCount;
                    } else {
                        fillData.range.rowCount = 1;
                        fillData.range.colCount = 1;
                    }
                    fillArr.push(fillData);
                }
            }
        }
        //将数据填充到subData中
        for (let fillData of fillArr) {
            //合并行不需要向下填值(否则会重复)
            let row = fillData.range.row;
            //合并列需要向右填值
            for (let j = 0; j < fillData.range.colCount; j++) {
                let col = fillData.range.col + j;
                subTable[row][col] = trimAll(fillData.value);
            }
        }
        return simplifyRationTable(subTable);
    }
    //获取工料机子表数据(序号至最末消耗量)
    //@param {Object}dataTable {Object}range(工料机子表的范围，row, col, rowCount, colCount)
    //@return {Array}
    function getSubGljTable(dataTable, range) {
        let gljTable = [];
        for (let i = 0; i < range.rowCount; i++) {
            gljTable.push(Array(range.colCount).fill(null));
        }
        // 消耗量列
        let consumeAmtCol = colMapping.consumeAmt;
        for (let i = 0; i < range.rowCount; i++) {
            let row = range.row + i;
            if (!dataTable[row]) {
                continue;
            }
            for (let j = 0; j < range.colCount; j++) {
                let col = range.col + j;
                let cell = dataTable[row][col];
                if (cellHasData(cell)) {
                    gljTable[i][j] = cell.value;
                } else if (col >= consumeAmtCol) {
                    // 消耗量单位格，没值则认为是0
                    gljTable[i][j] = 0;
                }
            }
        }
        return gljTable;
    }
    /*
     * 从原本的excel中提取数据
     * 需要的数据结构：eg: [{code: '1-1-1', name: '伐树', unit: '表列单位', subTable: [{subGlj: [], subRation: []}]}]
     * subTable为定额数据对应的子表数据，其中有定额名称子表、工料机子表
     * */
    function extractDataFromExcel(sheetData) {
        // 识别定额行的表达式
        //[\-,—,一] 这里是因为pdf转出来的excel（需要转换的excel）会吧"-"显示成各种奇怪的横杆
        //第一数值限制在3位以内，防止有: 2019-05-01等日期干扰
        // x-x-x xx
        let rationCodeReg = /^(\d{1,3}[\-,_,—,一]{1}\d+[\-,_,—,一]{1}\d+)(\w{0,}[\u4e00-\u9fa5]{1,})/;
        let dataTable = sheetData.data.dataTable,
            spans = sheetData.spans;
        //行数据是定额行 eg: 1-1-1 人工挖土方
        //@param {Number}row
        //@return {Object || Null} eg: {code: '1-1-1', name: '人工挖土方'}
        function rationRow(row) {
            let cell = dataTable[row][beginCol];
            if (!cell || !cell.value || typeof cell.value !== 'string') {
                return false;
            }
            let v = trimAll(cell.value);
            let match = rationCodeReg.exec(v);
            if (match && match.length === 3 && match[0] && match[1] && match[2]) {
                return {code: match[1].replace(/[_,—,一]/g, '-'), name: match[2]};
            }
            return null;
        }
        //单位数据行 eg: 单位：表列单位
        //@return {String || Null} eg: '表列单位'
        function unitRow(row) {
            let cell = dataTable[row][beginCol];
            if (!cell || !cell.value || typeof cell.value !== 'string') {
                return false;
            }
            let v = trimAll(cell.value);
            let reg = /单位[\:, ：]([\w+, \u4e00-\u9fa5]{0,})/;
            let match = reg.exec(v);
            if (match && match.length === 2 && match[0] && match[1]) {
                return match[1];
            }
            return null;
        }
        //行数据是人材机数据行
        //表头后，某行第一列为数值，第二列有值，则该行为紧接着表头的人材机数据行
        //@return {Boolean}
        function rowIsGlj(rowData) {
            let numberCell = rowData[colMapping.serialNo],
                valueCell = rowData[colMapping.name];
            return numberCell && numberCell.value && /^\d+$/.test(numberCell.value) && valueCell && Boolean(valueCell.value);
        }
        //连续有数据的列数(每个子表格中工料机的数据列数)
        //由于序号、项目、单位、代号可能存在合并列，因此从消耗量列(消耗量列不会合并列，消耗量对应的列号已在getColMapping获取)开始统计
        function getDataCount(row) {
            let consumeAmtCol = colMapping.consumeAmt;
            for (let col = consumeAmtCol; col < sheetData.columnCount; col++) {
                let cell = dataTable[row][col];
                if (!cell || !cell.value) {
                    return col;
                }
            }
            return sheetData.columnCount;
        }
        //获取前固定四列的动态列映射（解决这几列可能有合并列的问题）
        function getColMapping(rowData, colCount) {
            let needFields = ['serialNo', 'name', 'unit', 'code', 'consumeAmt'];
            function getUndefinedField(obj) {
                for (let field of needFields) {
                    if (!obj.hasOwnProperty(field)) {
                        return field;
                    }
                }
                return null;
            }
            //假定序号列为第一列
            let mapping = {serialNo: 0};
            for (let i = 1; i < colCount; i++) {
                let col = beginCol + i,
                    cell = rowData[col];
                //还没设置的字段（必须要有的是serialNo, name, unit, code, consumeAmt）
                if (cell && cell.value) {
                    let field = getUndefinedField(mapping);
                    if (field) {
                        mapping[field] = col;
                    }
                    if (typeof mapping.consumeAmt !== 'undefined') {
                        return mapping;
                    }
                }
            }
            return null;
        }
        //获取表格的子表范围（定额名称表头范围、工料机数据范围）
        //遇到单位:xx行rowA后，获取紧跟其后的表格，该表格最少要有一行工料机数据行rowB，表头范围则为rowA至rowB
        //@param {Number}beginRow
        function getTableRange(beginRow) {
            let hasTHead = false,
                hasTable = false,
                hasMapping = false; //是否获取过固定列映射
            let range = {
                subRation: {},
                subGlj: {},
            };
            for (let row = beginRow; row < sheetData.rowCount; row++) {
                if (!dataTable[row]) {
                    continue;
                }
                //第一个有数据的行，为表头第一行
                if (rowHasData(dataTable[row]) && !hasTHead) {
                    hasTHead = true;
                    range.subRation.row = row;
                }
                //获取当前子表的固定列映射
                if (hasTHead && !hasTable && !hasMapping && headerRow(dataTable[row])) {
                    hasMapping = true;
                    colMapping = getColMapping(dataTable[row], sheetData.columnCount);
                    if (!colMapping) {
                        return null;
                    }
                }
                //第一条工料机数据行
                if (hasTHead && !hasTable && colMapping && rowIsGlj(dataTable[row])) {
                    hasTable = true;
                    range.subGlj.row = row;
                    range.subGlj.col = 0;
                    range.subRation.col = colMapping.consumeAmt;
                    range.subRation.rowCount = range.subGlj.row - range.subRation.row;
                    range.subGlj.colCount = getDataCount(row);
                    range.subRation.colCount = range.subGlj.colCount - colMapping.consumeAmt;
                }
                if (hasTable && !rowIsGlj(dataTable[row])) {
                    range.subGlj.rowCount = row - range.subGlj.row;
                    return range;
                }
                if (hasTable && (row === sheetData.rowCount - 1 || !dataTable[row + 1])) {
                    range.subGlj.rowCount = row - range.subGlj.row + 1;
                    return range;
                }
            }
            return null;
        }
        //分析整个表
        let extractData = [];
        //定额行后必须要跟着单位行
        let hasUnit = false;
        for (let row = 0; row < sheetData.rowCount; row++) {
            if (!dataTable[row] || !rowHasData(dataTable[row])) {
                continue;
            }
            let rationData = rationRow(row);
            if (rationData) {
                if (!hasUnit) {
                    extractData.pop();
                }
                hasUnit = false;
                //转换数据数组的每个元素，为定额基本数据：不完整的code、不完整的name、unit、和子表门构成
                //subTable: [{subRation: [], subGlj: []}]，subRation为每个子表表头数据（排除掉了顺序号至代号），
                //subGlj为每个子表工料机数据
                let basicData = {
                    code: rationData.code,
                    name: rationData.name,
                    unit: null,
                    subTable: []
                };
                extractData.push(basicData);
            }
            let unitData = unitRow(row);
            if (unitData) {
                hasUnit = true;
                let thisBasicData = extractData[extractData.length - 1];
                if (thisBasicData) {
                    if (!thisBasicData.unit) {
                        thisBasicData.unit = unitData;
                    }
                    //获取表格数据
                    let range = getTableRange(row + 1);
                    if (range) {
                        let subRationTable = getSubRationTable(dataTable, range.subRation, spans),
                            subGljTable = getSubGljTable(dataTable, range.subGlj);
                        thisBasicData.subTable.push({subRation: subRationTable, subGlj: subGljTable});
                        //跳过其中的行
                        row = range.subGlj.row + range.subGlj.rowCount - 1;
                    }
                }
            }
        }
        return extractData;
    }
    /*
     * 转换数据，将提取出来的数据转换成另外一种数据结构，便于转化Excel的结构
     * 需要的数据结构: eg: [{code: '1-1-1-1', name: '伐树xxx', unit: '表列单位', gljList: [{code,name,unit,comsumeAmt}]}]
     * */
    function transferDataFromExtract(extractData) {
        console.log(extractData);
        let existCodes = [];
        //从一个提取的数据(1定额数据及其子表数据)中获取一份转换数据
        function transfer(source) {
            //以完整定额编码为属性，因为1个定额可能会有多张子表，且完整定额编码相同，子工料机不同，方便直接添加后续的工料机
            let temp = {},
                target = [];
            let basicCode = source.code,
                basicName = source.name,
                basicUnit = source.unit;
            if (existCodes.includes(basicCode)) {
                return [];
            }
            existCodes.push(basicCode);
            //处理消耗量，可能有(3.5) 和 - 的情况, 处理：(3.5) => 3.5  - => 0
            function handleConsumeAmt(consumeAmt) {
                if (typeof consumeAmt === 'string') {
                    consumeAmt = trimAll(consumeAmt);
                    consumeAmt = consumeAmt.replace(/[\-,_,—,一,\(,\),（,）]/g, '');
                    if (!consumeAmt) {
                        return 0;
                    }
                }
                return consumeAmt;
            }
            //从工料机子表中获取工料机数据, index为定额编码对应的下标索引
            function getGljList(gljTable, index) {
                let gljList = [];
                //获取的工料机对应列
                let gljColMapping = {
                    name: 1,
                    unit: 2,
                    code: 3,
                    consumeAmtCol: fixedCount + index
                };
                for (let rowData of gljTable) {
                    //工料机数据必须要有名称、单位、编码
                    if (!rowData[gljColMapping.name] ||
                        !rowData[gljColMapping.unit] ||
                        !rowData[gljColMapping.code]) {
                        continue;
                    }
                    let consumeAmt = isUnDef(rowData[gljColMapping.consumeAmtCol]) ? 0 : handleConsumeAmt(rowData[gljColMapping.consumeAmtCol]);
                    gljList.push({
                        name: rowData[gljColMapping.name],
                        unit: rowData[gljColMapping.unit],
                        code: rowData[gljColMapping.code],
                        consumeAmt: consumeAmt,
                    });
                }
                return gljList;
            }
            //拼接定额工料机数据
            for (let table of source.subTable) {
                let rationTable = table.subRation;
                if (!rationTable || rationTable.length === 0) {
                    continue;
                }
                let lastRationCodes = rationTable.pop(); //定额子表，最后一行是末位定额编码
                for (let i = 0; i < lastRationCodes.length; i++) {
                    let lastCode = lastRationCodes[i];
                    //拼接定额编码
                    let compleCode = `${basicCode}-${lastCode}`,
                        gljList = getGljList(table.subGlj, i);
                    if (!temp[compleCode]) { //该定额不存在
                        temp[compleCode] = {
                            code: compleCode,
                            unit: basicUnit,
                            name: basicName,
                            gljList: gljList
                        };
                    } else { //该定额已存在，则追加工料机
                        temp[compleCode].gljList = temp[compleCode].gljList.concat(gljList);
                    }
                    //拼接定额名称
                    for (let rationNameRow of rationTable) {
                        if (rationNameRow[i]) {
                            temp[compleCode].name += ` ${rationNameRow[i]}`;
                        }
                    }
                }
            }
            //将temp对象转换为数据
            for (let code in temp) {
                target.push(temp[code]);
            }
            return target;
        }
        let transferData = [];
        for (let data of extractData) {
            let unitTargetData = transfer(data);
            transferData = transferData.concat(unitTargetData);
        }
        console.log(transferData);
        return transferData;
    }
    // 提取内蒙养护数据
    function extractDataFromNMYH(sheetData) {
        // 识别定额行的表达式
        //固定列数（顺序号、项目、单位、代号、单价）
        fixedCount = 5;
        // x-x xx
        let rationCodeReg = /^(\d{1,3}[\-,_,—,一]{1}\d+)(\w{0,}[\u4e00-\u9fa5]{1,})/;
        let dataTable = sheetData.data.dataTable,
            spans = sheetData.spans;
        //行数据是定额行 eg: 1-1-1 人工挖土方
        //@param {Number}row
        //@return {Object || Null} eg: {code: '1-1-1', name: '人工挖土方'}
        function rationRow(row) {
            let cell = dataTable[row][beginCol];
            if (!cell || !cell.value || typeof cell.value !== 'string') {
                return false;
            }
            let v = trimAll(cell.value);
            let match = rationCodeReg.exec(v);
            if (match && match.length === 3 && match[0] && match[1] && match[2]) {
                return {code: match[1].replace(/[_,—,一]/g, '-'), name: match[2]};
            }
            return null;
        }
        //单位数据行 eg: 单位：表列单位
        //@return {String || Null} eg: '表列单位'
        function unitRow(row) {
            let cell = dataTable[row][beginCol];
            if (!cell || !cell.value || typeof cell.value !== 'string') {
                return false;
            }
            let v = trimAll(cell.value);
            let reg = /单位[\:, ：]([\w+, \u4e00-\u9fa5]{0,})/;
            let match = reg.exec(v);
            if (match && match.length === 2 && match[0] && match[1]) {
                return match[1];
            }
            return null;
        }
        //行数据是人材机数据行
        //表头后，某行第一列为数值，第二列有值，则该行为紧接着表头的人材机数据行
        //@return {Boolean}
        function rowIsGlj(rowData) {
            let numberCell = rowData[colMapping.serialNo],
                valueCell = rowData[colMapping.name];
            return numberCell && numberCell.value && /^\d+$/.test(numberCell.value) && valueCell && Boolean(valueCell.value);
        }
        //获取前固定四列的动态列映射（解决这几列可能有合并列的问题）
        function getColMapping(rowData, colCount) {
            let needFields = ['serialNo', 'name', 'unit', 'code'];
            function getUndefinedField(obj) {
                for (let field of needFields) {
                    if (!obj.hasOwnProperty(field)) {
                        return field;
                    }
                }
                return null;
            }
            //假定序号列为第一列
            let mapping = {serialNo: 0};
            for (let i = 1; i < colCount; i++) {
                let col = beginCol + i,
                    cell = rowData[col];
                if (cell && cell.value) {
                    let field = getUndefinedField(mapping);
                    if (field) {
                        mapping[field] = col;
                    }
                    if (typeof mapping.code !== 'undefined') {
                        return mapping;
                    }
                }
            }
            return null;
        }
        // 获取定额剩余编号的列号信息
        function getRestCodeInfo(rowData) {
            if (!rowData) {
                return;
            }
            let numReg = /^\d+$/;
            let col,
                colCount = 0;
            Object.values(rowData).forEach((data, index) => {
                let v = data.value || '';
                if (numReg.test(v)) {
                    if (!colCount) {
                        col = index;
                    }
                    colCount++;
                }
            });
            return colCount ? {col, colCount} : null;
        }
        //获取表格的子表范围（定额名称表头范围、工料机数据范围）
        //遇到单位:xx行rowA后，获取紧跟其后的表格，该表格最少要有一行工料机数据行rowB，表头范围则为rowA至rowB
        //@param {Number}beginRow
        function getTableRange(beginRow) {
            let hasTHead = false,
                hasTable = false,
                hasMapping = false; //是否获取过固定列映射
            let range = {
                subRation: {},
                subGlj: {},
            };
            for (let row = beginRow; row < sheetData.rowCount; row++) {
                if (!dataTable[row]) {
                    continue;
                }
                //第一个有数据的行，为表头第一行
                if (rowHasData(dataTable[row]) && !hasTHead) {
                    hasTHead = true;
                    range.subRation.row = row;
                }
                //获取当前子表的固定列映射
                if (hasTHead && !hasTable && !hasMapping && headerRow(dataTable[row])) {
                    hasMapping = true;
                    colMapping = getColMapping(dataTable[row], sheetData.columnCount);
                    if (!colMapping) {
                        return null;
                    }
                    // 获取定额剩余编码所处的col、和colCount
                    let restCodeInfo = getRestCodeInfo(dataTable[row - 1]);
                    if (!restCodeInfo) {
                        return null;
                    }
                    colMapping.consumeAmt = restCodeInfo.col;
                    range.subRation.col = restCodeInfo.col;
                    range.subRation.colCount = restCodeInfo.colCount;
                    range.subGlj.col = 0;
                    range.subGlj.colCount = restCodeInfo.col + restCodeInfo.colCount;
                }
                //第一条工料机数据行
                if (hasTHead && !hasTable && colMapping && rowIsGlj(dataTable[row])) {
                    hasTable = true;
                    range.subGlj.row = row;
                    range.subRation.rowCount = range.subGlj.row - range.subRation.row;
                }
                if (hasTable && !rowIsGlj(dataTable[row])) {
                    range.subGlj.rowCount = row - range.subGlj.row;
                    return range;
                }
                if (hasTable && (row === sheetData.rowCount - 1 || !dataTable[row + 1])) {
                    range.subGlj.rowCount = row - range.subGlj.row + 1;
                    return range;
                }
            }
            return null;
        }
        //分析整个表
        let extractData = [];
        //定额行后必须要跟着单位行
        let hasUnit = false;
        for (let row = 0; row < sheetData.rowCount; row++) {
            if (!dataTable[row] || !rowHasData(dataTable[row])) {
                continue;
            }
            let rationData = rationRow(row);
            if (rationData) {
                if (!hasUnit) {
                    extractData.pop();
                }
                hasUnit = false;
                //转换数据数组的每个元素，为定额基本数据：不完整的code、不完整的name、unit、和子表门构成
                //subTable: [{subRation: [], subGlj: []}]，subRation为每个子表表头数据（排除掉了顺序号至代号），
                //subGlj为每个子表工料机数据
                let basicData = {
                    code: rationData.code,
                    name: rationData.name,
                    unit: null,
                    subTable: []
                };
                extractData.push(basicData);
            }
            let unitData = unitRow(row);
            if (unitData) {
                hasUnit = true;
                let thisBasicData = extractData[extractData.length - 1];
                if (thisBasicData) {
                    if (!thisBasicData.unit) {
                        thisBasicData.unit = unitData;
                    }
                    //获取表格数据
                    let range = getTableRange(row + 1);
                    if (range) {
                        let subRationTable = getSubRationTable(dataTable, range.subRation, spans),
                            subGljTable = getSubGljTable(dataTable, range.subGlj);
                        thisBasicData.subTable.push({subRation: subRationTable, subGlj: subGljTable, colMapping: colMapping});
                        //跳过其中的行
                        row = range.subGlj.row + range.subGlj.rowCount - 1;
                    }
                }
            }
        }
        return extractData;
    }
    // 转换内蒙养护数据
    function transferDataFromNMYH(extractData) {
        console.log(extractData);
        let existCodes = [];
        //从一个提取的数据(1定额数据及其子表数据)中获取一份转换数据
        function transfer(source) {
            //以完整定额编码为属性，因为1个定额可能会有多张子表，且完整定额编码相同，子工料机不同，方便直接添加后续的工料机
            let temp = {},
                target = [];
            let basicCode = source.code,
                basicName = source.name,
                basicUnit = source.unit;
            if (existCodes.includes(basicCode)) {
                return [];
            }
            existCodes.push(basicCode);
            //处理消耗量，可能有(3.5) 和 - 的情况, 处理：(3.5) => 3.5  - => 0
            function handleConsumeAmt(consumeAmt) {
                if (typeof consumeAmt === 'string') {
                    consumeAmt = trimAll(consumeAmt);
                    consumeAmt = consumeAmt.replace(/[\-,_,—,一,\(,\),（,）]/g, '');
                    if (!consumeAmt) {
                        return 0;
                    }
                }
                return consumeAmt;
            }
            //从工料机子表中获取工料机数据, index为定额编码对应的下标索引
            function getGljList(gljColMapping, gljTable, index) {
                let gljList = [];
                let consumeAmtCol = gljColMapping.consumeAmt + index;
                for (let rowData of gljTable) {
                    //工料机数据必须要有名称、单位、编码
                    if (!rowData[gljColMapping.name] ||
                        !rowData[gljColMapping.unit] ||
                        !rowData[gljColMapping.code]) {
                        continue;
                    }
                    let consumeAmt = isUnDef(rowData[consumeAmtCol]) ? 0 : handleConsumeAmt(rowData[consumeAmtCol]);
                    gljList.push({
                        name: rowData[gljColMapping.name],
                        unit: rowData[gljColMapping.unit],
                        code: rowData[gljColMapping.code],
                        consumeAmt: consumeAmt,
                    });
                }
                return gljList;
            }
            //拼接定额工料机数据
            for (let table of source.subTable) {
                let rationTable = table.subRation;
                if (!rationTable || rationTable.length === 0) {
                    continue;
                }
                let lastRationCodes = rationTable.pop(); //定额子表，最后一行是末位定额编码
                for (let i = 0; i < lastRationCodes.length; i++) {
                    let lastCode = lastRationCodes[i];
                    //拼接定额编码
                    let compleCode = `${basicCode}-${lastCode}`,
                        gljList = getGljList(table.colMapping, table.subGlj, i);
                    if (!temp[compleCode]) { //该定额不存在
                        temp[compleCode] = {
                            code: compleCode,
                            unit: basicUnit,
                            name: basicName,
                            gljList: gljList
                        };
                    } else { //该定额已存在，则追加工料机
                        temp[compleCode].gljList = temp[compleCode].gljList.concat(gljList);
                    }
                    //拼接定额名称
                    for (let rationNameRow of rationTable) {
                        if (rationNameRow[i]) {
                            temp[compleCode].name += ` ${rationNameRow[i]}`;
                        }
                    }
                }
            }
            //将temp对象转换为数据
            for (let code in temp) {
                target.push(temp[code]);
            }
            return target;
        }
        let transferData = [];
        for (let data of extractData) {
            let unitTargetData = transfer(data);
            transferData = transferData.concat(unitTargetData);
        }
        console.log(transferData);
        return transferData;
    }
    /*
     * 从原本的建筑Excel中提取数据
     * */
    function extractDataFromJZ(sheetData) {
        let dataTable = sheetData.data.dataTable,
            spans = sheetData.spans;
        //定额编号为定额行
        function rationRow(row) {
            let cell = dataTable[row][beginCol];
            if (!cell || !cell.value || typeof cell.value !== 'string') {
                return false;
            }
            let v = trimAll(cell.value);
            return cell.value === '定额编号';
        }
        function endRationRow(row) {
            let cell = dataTable[row][beginCol];
            if (!cell || !cell.value || typeof cell.value !== 'string') {
                return false;
            }
            let v = trimAll(cell.value);
            return cell.value.includes('基价');
        }
        //表格中"分类"行为人材机子表起始行
        function classRow(row) {
            let cell = dataTable[row][beginCol];
            if (!cell || !cell.value || typeof cell.value !== 'string') {
                return false;
            }
            let v = trimAll(cell.value);
            return cell.value === '分类';
        }
        //人材机行:编码、名称、单位不为空
        function gljRow(rowData, colMapping) {
            let code = rowData[colMapping.code] ? rowData[colMapping.code].value : null,
                name = rowData[colMapping.name] ? rowData[colMapping.name].value : null,
                unit = rowData[colMapping.unit] ? rowData[colMapping.unit].value : null;
            return code && name && unit;
        }
        //获取"分类"行列对应：编码、名称、单位、单价的列
        function getColMapping(rowData, colCount) {
            function validMapping(obj) {
                let needFields = ['code', 'name', 'unit', 'price'];
                for (let field of needFields) {
                    if (!field in obj) {
                        return false;
                    }
                }
                return true;
            }
            let mapping = {};
            for (let i = 0; i < colCount; i++) {
                let col = beginCol + i,
                    cell = rowData[col];
                if (cell && cell.value) {
                    let cellValue = trimAll(cell.value);
                    if (cellValue === '编码') {
                        mapping.code = col;
                    } else if (cellValue === '名称') {
                        mapping.name = col;
                    } else if (cellValue === '单位') {
                        mapping.unit = col;
                    } else if ( cellValue.includes('单价')) {
                        mapping.price = col;
                        return validMapping(mapping) ? mapping : null;
                    }
                }
            }
            return null;
        }
        function getTableRange(beginRow) {
            let range = {
                subRation: {row: beginRow},
                subManage: {},//管理费
                subGlj: {},
            };
            //获取定额子表列数，从定额编号后面的有数据的列算起
            let beginCol = null, //第一个有效数据列
                lastCol = null; //最后一个有效数据列
            for (let col = 0; col < sheetData.columnCount; col++) {
                let cell = dataTable[beginRow][col];
                if (cell && cell.value && trimAll(cell.value) !== '定额编号') {
                    if (!beginCol) {
                        beginCol = col;
                    }
                    lastCol = col;
                }
            }
            if (!beginCol || !lastCol) {
                return null
            }
            range.subRation.col = beginCol;
            range.subRation.colCount = lastCol - beginCol + 1;
            let enterRation = false,
                enterGlj = false;
            for (let row = beginRow; row < sheetData.rowCount; row++) {
                if (!dataTable[row] || !rowHasData(dataTable[row])) {
                    continue;
                }
                if (endRationRow(row)) {
                    enterRation = true;
                    range.subRation.rowCount = row - range.subRation.row;
                }
                if (enterRation && classRow(row)) {
                    enterGlj = true;
                    colMapping = getColMapping(dataTable[row], sheetData.columnCount);
                    if (!colMapping) {
                        return null;
                    }
                    //管理费（人材机）相关
                    range.subManage.row = row - 1;
                    range.subManage.rowCount = 1;
                    range.subManage.col = beginCol;
                    range.subManage.colCount = range.subRation.colCount;
                    //人材机相关
                    range.subGlj.row = row + 1;
                    range.subGlj.col = colMapping.code;
                    range.subGlj.colCount = lastCol - range.subGlj.col + 1;
                }
                if (enterGlj && !gljRow(dataTable[row], colMapping)) {
                    range.subGlj.rowCount = row - range.subGlj.row;
                    return range;
                }
                if (enterGlj && (row === sheetData.rowCount - 1 || !dataTable[row + 1])) {
                    range.subGlj.rowCount = row - range.subGlj.row + 1;
                    return range;
                }
            }
            return null;
        }
        //管理费table并入工料机table
        function mergeManage(gljTable, manageTable) {
            for (let rowData of manageTable) {
                rowData.unshift(1);
                rowData.unshift('元');
                rowData.unshift('管理费');
                rowData.unshift('GLF');
            }
            return gljTable.concat(manageTable);
        }
        //分析整个表
        let extractData = [];
        for (let row = 0; row < sheetData.rowCount; row++) {
            if (!dataTable[row] || !rowHasData(dataTable[row])) {
                continue;
            }
            if (rationRow(row)) {
                //获取表格数据
                let range = getTableRange(row);
                if (range) {
                    let subRationTable = validateRationTable(getSubRationTable(dataTable, range.subRation, spans)),
                        subGljTable = getSubGljTable(dataTable, range.subGlj),
                        subManageTable = getSubGljTable(dataTable, range.subManage);
                    subGljTable = mergeManage(subGljTable, subManageTable);
                    //将管理费合并进人材机
                    extractData.push({subRation: subRationTable, subGlj: subGljTable});
                    //跳过其中的行
                    row = range.subGlj.row + range.subGlj.rowCount - 1;
                }
            }
        }
        function validateRationTable(source) {
            let target = [];
            let codeLine = source[0];
            for (let subSource of source) {
                let subTarget = [];
                for (let i = 0; i < subSource.length; i++) {
                    if (!codeLine[i]) {
                        continue;
                    }
                    subTarget.push(subSource[i]);
                }
                target.push(subTarget);
            }
            return target;
        }
        return extractData;
    }
    /*
    * 转换建筑数据
    * */
    function transferDataFromJZ(extractData) {
        function handleNumber(v) {
            if (typeof v === 'string') {
                v = trimAll(v);
                v = v.replace(/[\-,_,—,一,\(,\),（,）,[,]/g, '');
                if (!v) {
                    return 0;
                }
            }
            return Math.abs(parseFloat(v));
        }
        //从工料机子表中获取工料机数据, index为定额编码对应的下标索引
        function getGljList(gljTable, index) {
            let gljList = [];
            //获取的工料机对应列
            let gljColMapping = {
                code: 0,
                name: 1,
                unit: 2,
                price: 3,
                consumeAmtCol: fixedCount + index
            };
            for (let rowData of gljTable) {
                //工料机数据必须要有名称、单位、编码、单价
                if (!rowData[gljColMapping.name] ||
                    !rowData[gljColMapping.unit] ||
                    !rowData[gljColMapping.code] ||
                    !rowData[gljColMapping.price]) {
                    continue;
                }
                let consumeAmt = isUnDef(rowData[gljColMapping.consumeAmtCol]) ? 0 : handleNumber(rowData[gljColMapping.consumeAmtCol]),
                    price = isUnDef(rowData[gljColMapping.price]) ? 0 : handleNumber(rowData[gljColMapping.price]);
                gljList.push({
                    name: rowData[gljColMapping.name],
                    unit: rowData[gljColMapping.unit],
                    code: rowData[gljColMapping.code],
                    price: price,
                    consumeAmt: consumeAmt,
                });
            }
            return gljList;
        }
        let temp = {},
            target = [];
        for (let source of extractData) {
            let rationTable = source.subRation,
                gljTable = source.subGlj;
            if (!rationTable || rationTable.length === 0) {
                continue;
            }
            //拼接定额工料机数据
            let rationCodes = rationTable.shift(); //定额编码
            for (let i = 0; i < rationCodes.length; i++) {
                let code = rationCodes[i].replace(/[_,—,一]/g, '-');
                let gljList = getGljList(gljTable, i);
                if (!temp[code]) { //该定额不存在
                    temp[code] = {
                        code: code,
                        unit: '',
                        name: '',
                        gljList: gljList
                    };
                } else { //该定额已存在，则追加工料机
                    temp[code].gljList = temp[code].gljList.concat(gljList);
                }
                //拼接定额名称
                for (let rationNameRow of rationTable) {
                    if (rationNameRow[i]) {
                        temp[code].name += ` ${rationNameRow[i]}`;
                    }
                }
            }
        }
        for (let code in temp) {
            target.push(temp[code]);
        }
        return target;
    }
    //导入Excel
    function exportToExcel(transferData, fileName) {
        $.bootstrapLoading.start();
        setTimeout(function () {
            if (exportSpread) {
                exportSpread.destroy();
            }
            exportSpread = new GC.Spread.Sheets.Workbook($exportSpread[0], {sheetCount: 1});
            let sheet = exportSpread.getSheet(0);
            sheet.suspendPaint();
            sheet.suspendEvent();
            //往表格填值
            let curRow = 1,
                fillCol = {
                    code: 1,
                    name: 2,
                    unit: 3,
                    consumeAmt: 4,
                    price: 5
                };
            function getRowCount() {
                let count = 0;
                for (let data of transferData) {
                    count += 1 + data.gljList.length;
                }
                return count;
            }
            sheet.setRowCount(getRowCount()+1);
            //test
           /* let temp = {};
            for (let testData of transferData) {
                if (temp[testData.code]) {
                    temp[testData.code]++;
                } else {
                    temp[testData.code] = 1;
                }
            }
            let consoleData = [];
            for (let i in temp) {
                if (temp[i] > 1) {
                    consoleData.push({code: i, count: temp[i]});
                }
            }
            console.log(consoleData);*/
            //test
            sheet.setValue(0, fillCol.code, '编码');
            sheet.setValue(0, fillCol.name, '名称');
            sheet.setValue(0, fillCol.unit, '单位');
            sheet.setValue(0, fillCol.consumeAmt, '消耗量');
            if (curTransferType === transferType.jz) {
                sheet.setValue(0, fillCol.price, '单价');
            }
            for (let data of transferData) {
                sheet.setValue(curRow, 0, '定额');
                sheet.setValue(curRow, fillCol.code, data.code);
                sheet.setValue(curRow, fillCol.name, data.name);
                sheet.setValue(curRow, fillCol.unit, data.unit);
                curRow++;
                for (let glj of data.gljList) {
                    sheet.setValue(curRow, fillCol.code, glj.code);
                    sheet.setValue(curRow, fillCol.name, glj.name);
                    sheet.setValue(curRow, fillCol.unit, glj.unit);
                    sheet.setValue(curRow, fillCol.consumeAmt, glj.consumeAmt);
                    if (curTransferType === transferType.jz) {
                        sheet.setValue(curRow, fillCol.price, glj.price);
                    }
                    curRow++;
                }
            }
            sheet.resumeEvent();
            sheet.resumePaint();
            let json = exportSpread.toJSON();
            let excelIo = new GC.Spread.Excel.IO();
            excelIo.save(json, function(blob) {
                saveAs(blob, fileName);
                $.bootstrapLoading.end();
                $transferModal.modal('hide');
            }, function(e) {
                $.bootstrapLoading.end();
                $transferModal.modal('hide');
                console.log(e);
            });
        }, 200);
    }
    function eventListener() {
        $('.transfer-excel').click(function () {
            curTransferType = $(this).data('exceltype');
            console.log(curTransferType);
        });
        $transferModal.on('hidden.bs.modal', function () {
            $file.val('');
            transferData = [];
        });
        //导入excel,提取并转换定额数据
        $file.change(function () {
            $transfer.addClass('disabled');
            let file = $(this)[0];
            $.bootstrapLoading.start();
            $('#loadingPage').css('z-index', '2000');
            setTimeout(function () {
                let excelFile = file.files[0];
                if(excelFile) {
                    let xlsReg = /xls$/g;
                    if(excelFile.name && xlsReg.test(excelFile.name)){
                        alert('请选择xlsx文件');
                        $(this).val('');
                        $transfer.removeClass('disabled');
                        $.bootstrapLoading.end();
                        return;
                    }
                    //前端解析excel数据
                    let excelIo = new GC.Spread.Excel.IO();
                    let sDate = +new Date();
                    excelIo.open(excelFile, function (json) {
                        console.log(json);
                        if (curTransferType === transferType.gl) {
                            let extractData = extractDataFromExcel(json.sheets.Sheet1);
                            transferData = transferDataFromExtract(extractData);
                        } else if (curTransferType === transferType.neimengyh) {
                            let extractData = extractDataFromNMYH(json.sheets.Sheet1);
                            transferData = transferDataFromNMYH(extractData);
                        } else {
                            let extractData = extractDataFromJZ(json.sheets.Sheet1);
                            transferData = transferDataFromJZ(extractData);
                            console.log(transferData);
                        }
                        console.log(`解析Excel文件时间：${+new Date() - sDate}`);
                        $.bootstrapLoading.end();
                        $transfer.removeClass('disabled');
                    }, function (e) {
                        $.bootstrapLoading.end();
                        $transfer.removeClass('disabled');
                        alert(e.errorMessage);
                    });
                }
            }, 200);
        });
        //确认转换，导出转换的Excel
        $transfer.click(function () {
            if ($(this).hasClass('disabled')) {
                return;
            }
            if (!transferData || transferData.length === 0) {
                //没有转换数据
                alert('没有转换数据');
                return;
            }
            let fileName = '转换数据.xlsx';
            if ($file[0].files && $file[0].files[0] && $file[0].files[0].name) {
                fileName = '转换数据' + $file[0].files[0].name;
            }
            exportToExcel(transferData, fileName);
        });
    }

    return {eventListener}
})();

$(document).ready(function () {
    TransferExcel.eventListener();
});