123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455 |
- 'use strict';
- /**
- *
- *
- * @author Zhong
- * @date 2018/8/2
- * @version
- */
- /*
- * 清单导入模块,前端导入excel,进行数据提取,用lz-string进行压缩上传处理
- * */
- const importBills = (function(){
- //单元格数据是否存在
- function _isDef(data){
- return typeof data !== 'undefined' && data !== null && data !== '';
- }
-
- //去除转义字符
- function _deESC(data) {
- return _isDef(data) ? data.toString().replace(/[\r\n\s\t]/g, '') : data;
- }
- function _deNR(data) {
- return _isDef(data) ? data.toString().replace(/\r\r/g, '\r') : data;
- }
- //列名对应中文字符
- const colText = {
- serialNo: ['序号'],
- code: ['编码', '项目编码'],
- name: ['名称', '项目名称'],
- itemCharacterText: ['特征', '项目特征', '项目特征描述'],
- unit: ['单位', '计量单位'],
- quantity: ['工程量', '项目工程量'],
- money: ['金额'],
- quantityDetail: ['工程量明细'],
- feeDetail: ['费用明细'],
- summation: ['合计', '本页小计'],
- };
- //导入位置对应清单固定标记
- const positionFlag = {
- fbfx: fixedFlag.SUB_ENGINERRING,
- jscsxm: fixedFlag.CONSTRUCTION_TECH,
- zzcsxm: fixedFlag.CONSTRUCTION_ORGANIZATION,
- };
- //上传类型
- const uploadType = {
- lj: 'lj',
- gld: 'gld',
- };
- //设置导入表内容(选择导入位置)
- //@param {Object}workBook
- function setImportSheetsInfo(sheets){
- let sheetNames = [];
- let indexMapping = {};
- for(let sheetName in sheets){
- indexMapping[sheets[sheetName]['index']] = sheetName;
- }
- let sheetsCount = Object.keys(sheets).length;
- for(let i = 0; i < sheetsCount; i++){
- sheetNames.push(indexMapping[i]);
- }
- let sheetArea = $('#uploadSheets'),
- sheetHeader = $('#uploadSheetsHead');
- $('#uploadSheets').height('');
- sheetArea.empty();
- for(let sheetName of sheetNames){
- let sheetDiv = $(`<div style="margin-left: 5px;margin-top: 5px;" title="${sheetName}" class="input-group form-check"><label class="form-check-label" style="width:270px; overflow: hidden; white-space: nowrap; text-overflow: ellipsis">
- <input class="form-check-input" type="checkbox">${sheetName}</label></div>`);
- sheetDiv.find('input[type="checkbox"]').click(function () {
- if($('#uploadAlert').is(':visible')){
- $('#uploadAlert').hide();
- }
- });
- let sel = $(`<select style="margin-left: 5px; border-radius: .20rem;"><option value="fbfx">分部分项工程</option><option value="zzcsxm">施工组织措施项目</option><option value="jscsxm">施工技术措施项目</option></select>`);
- if(sheetName.includes('分部分项工程项目清单计价表')){
- sheetDiv.find('input[type="checkbox"]').prop('checked', true);
- sel.find('option:eq(0)').prop('selected', true);
- }
- else if(sheetName.includes('施工组织措施项目清单计价表')){
- sheetDiv.find('input[type="checkbox"]').prop('checked', true);
- sel.find('option:eq(1)').prop('selected', true);
- }
- else if(sheetName.includes('施工技术措施项目清单计价表')){
- sheetDiv.find('input[type="checkbox"]').prop('checked', true);
- sel.find('option:eq(2)').prop('selected', true);
- }
- sheetDiv.append(sel);
- sheetArea.append(sheetDiv);
- }
- if(sheetNames.length > 0){
- sheetArea.show();
- sheetHeader.show();
- }
- if($('#uploadSheets').height() > 250){
- sheetArea.css('overflow', 'auto');
- sheetArea.height(250);
- }
- else {
- sheetArea.css('overflow', 'hidden');
- }
- }
- //获得选择导入的表信息(表索引及导入位置)
- //@return {Object}
- function getImportSheetsInfo(){
- let rst = [];
- let sheetArea = $('#uploadSheets');
- let checkedInputs = sheetArea.find('input:checked');
- for(let checked of checkedInputs){
- rst.push({index: $(checked).parent().parent().index(), position: $(checked).parent().next().select().val()})
- }
- return rst;
- }
- function getSheetByIndex(sheets, index){
- for(let sheetName in sheets){
- let sheet = sheets[sheetName];
- if(sheet.index === index){
- return sheet;
- }
- }
- return null;
- }
- //提取excel表头列名与列下标映射
- //@
- function getColMapping(dataTable){
- //获取表头
- function getHeadRow(dataTable){
- for(let rowIdx in dataTable ){
- for(let colIdx in dataTable[rowIdx]){
- let cellData = dataTable[rowIdx][colIdx]['value'];
- if(cellData && _deESC(cellData) === colText.serialNo[0]){
- return dataTable[rowIdx];
- }
- }
- }
- return {};
- }
- //获取需要的表头列与列号对应关系
- let colMapping = {};
- let headRow = getHeadRow(dataTable);
- for(let colIdx in headRow){
- let cellData = headRow[colIdx]['value'];
- if(!_isDef(cellData)){
- continue;
- }
- //序号
- if(colMapping.serialNo === undefined && _deESC(cellData) === colText.serialNo[0]){
- colMapping.serialNo = colIdx;
- }
- //编码
- else if(colMapping.code === undefined && (_deESC(cellData) === colText.code[0] || _deESC(cellData) === colText.code[1])){
- colMapping.code = colIdx;
- }
- //名称
- else if(colMapping.name === undefined && (_deESC(cellData) === colText.name[0] || _deESC(cellData) === colText.name[1])){
- colMapping.name = colIdx;
- }
- //项目特征
- else if(colMapping.itemCharacterText === undefined && (_deESC(cellData) === colText.itemCharacterText[0] || _deESC(cellData) === colText.itemCharacterText[1]
- || _deESC(cellData) === colText.itemCharacterText[2])){
- colMapping.itemCharacterText = colIdx;
- }
- //单位
- else if(colMapping.unit === undefined && (_deESC(cellData) === colText.unit[0] || _deESC(cellData) === colText.unit[1])){
- colMapping.unit = colIdx;
- }
- //工程量
- else if(colMapping.quantity === undefined && (_deESC(cellData) === colText.quantity[0] || _deESC(cellData) === colText.quantity[1])){
- colMapping.quantity = colIdx;
- }
- //金额
- else if(colMapping.money === undefined && _deESC(cellData).includes(colText.money[0])){
- colMapping.money = colIdx;
- }
- //工程量明细
- else if(colMapping.quantityDetail === undefined && _deESC(cellData) === colText.quantityDetail[0]){
- colMapping.quantityDetail = colIdx;
- }
- //费用明细
- else if(colMapping.feeDetail === undefined && _deESC(cellData) === colText.feeDetail[0]){
- colMapping.feeDetail = colIdx;
- }
- }
- return colMapping;
- }
- //是否是有效的表头列格式,只要含有各表需要的列就行,不严格控制多少列
- function isValidSheet(colMapping, fileType){
- function hasField(field, all){
- for(let i of all){
- if(field === i){
- return true;
- }
- }
- return false;
- }
- let needFields;
- if(fileType === uploadType.lj){
- //09表:序号、项目编码、项目名称、项目特征、计量单位、工程量、金额
- needFields = ['serialNo', 'code', 'name', 'money'];
- }
- else {
- //广联达表:序号、项目编码、项目名称、项目特征、计量单位、工程量、工程量明细、费用明细
- needFields = ['serialNo', 'code', 'name', 'itemCharacterText', 'unit', 'quantity', 'quantityDetail', 'feeDetail'];
- }
- let hasFieldCount = 0;
- for(let attr in colMapping){
- if(hasField(attr, needFields)){
- hasFieldCount++;
- }
- }
- return hasFieldCount === needFields.length;
- }
- //获取要无效和有效导入表
- //@param {Array}importSheetInfo 勾选要导入的表
- function getImportSheets(sheets, sheetsInfo, fileType){
- let rst = {invalidSheets: [], validSheets: {fbfx: [], jscsxm: [], zzcsxm: []}};
- for(let sheetInfo of sheetsInfo){
- let sheet = getSheetByIndex(sheets, sheetInfo.index);
- if(!sheet){
- continue;
- }
- //没有数据
- if(!sheet.data.dataTable){
- rst.invalidSheets.push(sheet.name);
- continue;
- }
- //获取表的列设置确定导入的格式是否合法(09、广联达)
- let colMapping = getColMapping(sheet.data.dataTable);
- if(!isValidSheet(colMapping, fileType)){
- rst.invalidSheets.push(sheet.name);
- continue;
- }
- //合法的表
- sheet.colMapping = colMapping;
- //将合法的表按导入位置分类当做一个表来处理
- if(rst.validSheets[sheetInfo.position] !== undefined){
- rst.validSheets[sheetInfo.position].push(sheet)
- }
- }
- return rst;
- }
- //行存在数据
- function rowExistData(rowData){
- for(let colIdx in rowData){
- let colData = rowData[colIdx]['value'];
- if(_isDef(colData)){
- return true;
- }
- }
- return false;
- }
- //提取excel表数据中的有效数据(去表头表尾,提取其中的excel数据)(根据fixedBill获取栏头占行数)
- function getValidImportData(colMapping, sheetData){
- let withingD = false;
- let validData = [];
- function isHead(rData){
- return rData[colMapping.serialNo] && _deESC(rData[colMapping.serialNo]['value']) === colText.serialNo[0];
- }
- function isTail(rowData){
- for(let colIdx in rowData){
- let colData = rowData[colIdx]['value'];
- if(colData){
- let trimColData= _deESC(colData);
- if(trimColData === colText.summation[0] || trimColData === colText.summation[1]){
- return true;
- }
- }
- }
- return false;
- }
- for(let rowIdx in sheetData){
- let rowData = sheetData[rowIdx];
- if(isHead(rowData)){
- withingD = true;
- continue;
- }
- else if(isTail(rowData)){
- withingD = false;
- }
- if(withingD && rowExistData(rowData)){
- validData.push(rowData);
- }
- }
- return validData;
- }
- //excel数据转换成清单数据
- function parseToBillData(validData, colMapping, flag, projectID){
- let rst = [];
- let billIdx = {};
- let preRootID = -1,
- preLeafID = -1,
- preID = -1;
- //父节点:1.无序号 2有编码
- function isRoot(rData){
- //序号和编码去除转义字符(有的表格单元格看起来是没数据,实际含有\r,\n等数据)
- let serialNo = rData[colMapping.serialNo] ? _deESC(rData[colMapping.serialNo]['value']) : '';
- let code = rData[colMapping.code] ? _deESC(rData[colMapping.code]['value']) : '';
- return !_isDef(serialNo) && _isDef(code);
- }
- //子节点:有序号
- function isLeaf(rData){
- let serialNo = rData[colMapping.serialNo] ? _deESC(rData[colMapping.serialNo]['value']) : '';
- return _isDef(serialNo);
- }
- //续数据:1. 前数据有效 2.无序号 3.无编码 4.有名称或特征
- function isExtend(preData, rData){
- let serialNo = rData[colMapping.serialNo] ? _deESC(rData[colMapping.serialNo]['value']) : '';
- let code = rData[colMapping.code] ? _deESC(rData[colMapping.code]['value']) : '';
- let name = rData[colMapping.name] ? _deESC(rData[colMapping.name]['value']) : '';
- let itemCharacterText = rData[colMapping.itemCharacterText] ? _deESC(rData[colMapping.itemCharacterText]['value']) : '';
- return _isDef(preData) && (isRoot(preData) || isLeaf(preData)) && !_isDef(serialNo) && !_isDef(code) && (_isDef(name) || _isDef(itemCharacterText));
- }
- function getBillType(rData, flag){
- if(flag === fixedFlag.CONSTRUCTION_TECH || flag === fixedFlag.CONSTRUCTION_ORGANIZATION){
- return billType.BILL;
- }
- else if(flag === fixedFlag.SUB_ENGINERRING){
- return isLeaf(rData) ? billType.FX : billType.FB;
- }
- return null;
- }
- let preData = null;
- for(let r = 0; r < validData.length; r++){
- let rData = validData[r];
- if(flag == fixedFlag.CONSTRUCTION_TECH && rData[colMapping.name] && rData[colMapping.name]['value'] === '施工技术措施项目'
- || flag == fixedFlag.CONSTRUCTION_ORGANIZATION && rData[colMapping.name] && rData[colMapping.name]['value'] === '施工组织措施项目'){
- continue;
- }
- //过滤无效数据
- if(!isRoot(rData) && !isLeaf(rData) && !isExtend(preData, rData)){
- continue;
- }
- if(isExtend(preData, rData)){
- let preBill = billIdx[preID];
- if(preBill){
- //合并续数据
- preBill.code += rData[colMapping.code] && rData[colMapping.code]['value'] && _isDef(_deESC(rData[colMapping.code]['value'])) ? rData[colMapping.code]['value'] : '';
- preBill.name += rData[colMapping.name] && rData[colMapping.name]['value'] && _isDef(_deESC(rData[colMapping.name]['value'])) ? rData[colMapping.name]['value'] : '';
- preBill.itemCharacterText += rData[colMapping.itemCharacterText] && rData[colMapping.itemCharacterText]['value'] && _isDef(_deESC(rData[colMapping.itemCharacterText]['value']))
- ? '\n' + _deNR(rData[colMapping.itemCharacterText]['value']) : '';
- preBill.unit += rData[colMapping.unit] && rData[colMapping.unit]['value'] && _isDef(_deESC(rData[colMapping.unit]['value'])) ? rData[colMapping.unit]['value'] : '';
- preBill.quantity += rData[colMapping.quantity] && rData[colMapping.quantity]['value'] && _isDef(_deESC(rData[colMapping.quantity]['value'])) ? rData[colMapping.quantity]['value'] : '';
- }
- }
- else {
- let newID = uuid.v1();
- let pID = -1;
- let preBill = null;
- let preRoot = null,
- preLeaf = null;
- let nodeType = 'root';//后端以此标记来设置ParentID
- let preSerialBill = billIdx[preID];
- if(isRoot(rData)){
- //pID = 'fixedBillID';
- preBill = billIdx[preRootID];
- preRoot = billIdx[preRootID];
- }
- else if(isLeaf(rData)){
- nodeType = 'leaf';
- //pID = preRootID !== -1 ? preRootID : fixedBill.ID;
- preBill = billIdx[preLeafID];
- preLeaf = billIdx[preLeafID];
- }
- //set bill data
- billIdx[newID] = {
- nodeType: nodeType,
- ID: newID, ParentID: pID, NextSiblingID: -1,
- code: rData[colMapping.code] && rData[colMapping.code]['value'] ? _deESC(rData[colMapping.code]['value']) : '',
- name: rData[colMapping.name] && rData[colMapping.name]['value'] ? _deESC(rData[colMapping.name]['value']) : '',
- itemCharacterText: rData[colMapping.itemCharacterText] && rData[colMapping.itemCharacterText]['value'] ? _deNR(rData[colMapping.itemCharacterText]['value']) : '',
- itemCharacter: [],
- jobContentText: '',
- jobContent: [],
- programID: null,
- unit: rData[colMapping.unit] && rData[colMapping.unit]['value'] ? _deESC(rData[colMapping.unit]['value']) : '',
- quantity: rData[colMapping.quantity] && rData[colMapping.quantity]['value'] ? _deESC(rData[colMapping.quantity]['value']) : '',
- quantityEXP: rData[colMapping.quantity] && rData[colMapping.quantity]['value'] ? _deESC(rData[colMapping.quantity]['value']) : '',
- //安全文明
- flags: flag === fixedFlag.CONSTRUCTION_ORGANIZATION && (rData[colMapping.name] && (rData[colMapping.name]['value'] === '安全文明施工专项费用' || rData[colMapping.name]['value'] === '安全文明施工费')) ?
- [{fieldName: 'fixed', flag: fixedFlag.SAFETY_CONSTRUCTION}] : [],
- fees: [],
- projectID: projectID,
- type: getBillType(rData, flag)};
- //update preBill NextSibling
- if(nodeType === 'root' && preRoot){
- preRoot.NextSiblingID = newID;
- }
- else if(nodeType === 'leaf' && preLeaf && preSerialBill && preSerialBill.nodeType === preLeaf.nodeType){
- preLeaf.NextSiblingID = newID;
- }
- /* if(preBill){
- preBill.NextSiblingID = newID;
- }*/
- //set new preID
- preID = newID;
- preRootID = isRoot(rData) ? newID : preRootID;
- preLeafID = isLeaf(rData) ? newID : preLeafID;
- }
- preData = rData;
- }
- for(let i in billIdx){
- rst.push(billIdx[i]);
- }
- return rst;
- }
- function getImportData(validSheets, projectID){
- let rst = {fbfx: [], jscsxm: [], zzcsxm: []};
- let validSheetsDatas = [];
- for(let uploadPosition in validSheets){
- let validExcelData = [];
- for(let uSheet of validSheets[uploadPosition]){
- validExcelData = validExcelData.concat(getValidImportData(uSheet.colMapping, uSheet.data.dataTable));
- }
- if(validSheets[uploadPosition].length > 0){
- validSheetsDatas.push({position: uploadPosition, colMapping: validSheets[uploadPosition][0].colMapping, validExcelData: validExcelData});
- }
- }
- for(let validSheetData of validSheetsDatas){
- if(validSheetData.validExcelData.length > 0){
- rst[validSheetData.position] = parseToBillData(validSheetData.validExcelData, validSheetData.colMapping, positionFlag[validSheetData.position], projectID);
- }
- }
- return rst;
- }
- function excelHasValidBills(importBillsData){
- for(let i in importBillsData){
- if(importBillsData[i].length > 0){
- return true;
- }
- }
- return false;
- }
- return {setImportSheetsInfo, getImportSheetsInfo, getImportSheets, getImportData, excelHasValidBills}
- })();
|