/** * Created by jimiz on 2017/4/7. */ let mongoose = require('mongoose'); var billsData = require('../models/bills'); let ration_model = require('../models/ration'); let ProjectsData = require('../../pm/models/project_model').project; let logger = require("../../../logs/log_helper").logger; let quantity_detail = require("../facade/quantity_detail_facade"); let bill_facade = require("../facade/bill_facade"); let ration_glj = mongoose.model('ration_glj'); let ration_coe = mongoose.model('ration_coe'); let rationInstallationModel = mongoose.model('ration_installation'); let stdBillsModel = mongoose.model('std_bills_lib_bills'); let stdBillJobsModel = mongoose.model('std_bills_lib_jobContent'); let stdBillCharacterModel = mongoose.model('std_bills_lib_itemCharacter'); import fixedFlag from '../../common/const/bills_fixed'; const uuidV1 = require('uuid/v1'); const billType ={ DXFY:1,//大项费用 FB:2,//分部 FX:3,//分项 BILL:4,//清单 BX:5//补项 }; //上传的09表、广联达表 const uploadType = {lj: 'lj', gld: 'gld'}; // 上传控件 const multiparty = require("multiparty"); const fs = require("fs"); // excel解析 const excel = require("node-xlsx"); //统一回调函数 var callback = function(req, res, err, message, data){ res.json({error: err, message: message, data: data}); }; module.exports = { getData: function(req, res){ var data = JSON.parse(req.body.data); billsData.getData(data.projectId, function(err, message, billsList){ if (err === 0) { callback(req, res, err, message, billsList); } else { callback(req, res, err, message, null); } }); }, getItemTemplate: function(req, res){ //var data = JSON.parse(req.body.data); billsData.getItemTemplate(function(err, message, billsItem){ if (billsItem) { callback(req, res, err, message, billsItem); } else { callback(req, res, err, message, null); } }); }, allocIDs: function(req, res){ billsData.allocIDs(function(err, message, data){ if (err) { callback(req, res, err, message, data); } else { callback(req, res, err, message, null); } }); }, //zhong 2017-9-1 updateCharacterContent: function (req, res) { let data = JSON.parse(req.body.data); let findSet = data.findSet, updateObj = data.updateObj, txtObj = data.txtObj; billsData.updateCharacterContent(findSet, updateObj, txtObj, function (err, message) { callback(req, res, err, message, null); }); }, updateBill: async function(request, response) { const data = JSON.parse(request.body.data); const findSet = data.findSet; const updateData = data.updateData; let settingData = {}; // 筛选出要保存在项目属性的设置 for (const index in updateData) { if (updateData[index].field === 'addRule') { settingData = updateData[index].value; delete updateData[index]; } } // 更新项目属性 const propertyUpdateData = { property: 'addRule', data: settingData }; const projectResult = await ProjectsData.updateProjectProperty(findSet.projectID, propertyUpdateData); const result = await billsData.updateBill(findSet, updateData); const message = !result || !projectResult ? '修改失败' : '修改成功'; const err = !result || !projectResult ? 1 : 0; callback(request, response, err, message, null); }, singleDelete:async function(req, res){ let result={ error:0 } try { let data = req.body.data; data = JSON.parse(data); let tasks = generateSingleDeleteTasks(data); let resultData= await billsData.model.bulkWrite(tasks); //删除工程量明细 await quantity_detail.deleteByQuery({projectID: data.projectID, billID: data.ID}) ; result.data=resultData; }catch (err){ logger.err(err); result.error=1; result.message = err.message; } res.json(result); }, multiDelete:async function(req, res){ let result={ error:0 }; try { let data = req.body.data; data = JSON.parse(data); result.data=await doBillsOrRationsDelete(data); }catch (err){ logger.err(err); result.error=1; result.message = err.message; } res.json(result); }, getSectionInfo:async function(req, res){ let result={ error:0 } try { let data = req.body.data; data = JSON.parse(data); let sectionInfo= await bill_facade.getSectionInfo(data); result.data=sectionInfo; }catch (err){ logger.err(err); result.error=1; result.message = err.message; } res.json(result); }, reorganizeFBFX:async function(req,res){ let result={ error:0 } try { let data = req.body.data; data = JSON.parse(data); let reorganizeResult= await bill_facade.reorganizeFBFX(data); result.data=reorganizeResult; }catch (err){ logger.err(err); result.error=1; result.message = err.message; } res.json(result); }, pasteBlock:async function(req,res){ let result={ error:0 }; try { let data = req.body.data; data = JSON.parse(data); let pasteResult = await bill_facade.pasteBlock(data); result.data = pasteResult; }catch (err){ logger.err(err); result.error=1; result.message = err.message; } res.json(result); }, //下载导入清单示例 downloadExample: async function(request, response) { try { const filePath = './public/static/uploadExample.xlsx'; const stats = fs.statSync(filePath); // 下载相关header response.set({ 'Content-Type': 'application/octet-stream', 'Content-Disposition': 'attachment; filename=uploadExample.xlsx', 'Content-Length': stats.size }); fs.createReadStream(filePath).pipe(response); } catch (error) { response.end(error); } }, upload: async function(req, res){ let responseData = { err: 0, msg: '', data: null }; const allowHeader = ['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']; const uploadOption = { uploadDir: './public' }; const form = new multiparty.Form(uploadOption); let uploadFullName; form.parse(req, async function(err, fields, files) { try{ const projectID = fields.projectID !== undefined && fields.projectID.length > 0 ? parseInt(fields.projectID[0]) : 0; if (projectID <= 0) { throw '参数错误'; } const file = files.file !== undefined ? files.file[0] : null; if (err || file === null) { throw '上传失败'; } // 判断类型 if (file.headers['content-type'] === undefined || allowHeader.indexOf(file.headers['content-type']) < 0) { throw '不支持该类型'; } //导入表类型(09表lj、广联达gld) const fileType = fields.fileType !== undefined && fields.fileType.length > 0 ? fields.fileType[0] : uploadType.lj; //广联达表始终插入到分部分项，将文件名重命名为分部分项触发导入到分部分项部分 if(fileType === uploadType.gld){ file.originalFilename = '广联达分部分项工程'; } // 重命名文件名 uploadFullName = uploadOption.uploadDir + '/' + file.originalFilename; fs.renameSync(file.path, uploadFullName); const sheet = excel.parse(uploadFullName); if (sheet[0] === undefined || sheet[0].data === undefined) { throw 'excel没有对应数据'; } //获取表的列设置确定导入的格式是否合法（09、广联达） //console.log(sheet[0].data); let colMapping = getColMapping(sheet[0].data); console.log(fileType); console.log(`colMapping`); console.log(colMapping); console.log(`sheet[0].data`); console.log(sheet[0].data); if(!isValidSheet(colMapping, fileType)){ throw `excel数据格式错误`; } //导入的数据是否含有固定行(分部分项、施工技术措施项目、施工组织措施项目，通过文件名判断)、确定导入位置 let flag = getImportFlag(file.originalFilename); if(!flag){ throw 'excel数据错误'; } let fixedBill = await billsData.model.findOne({projectID: projectID, 'flags.flag': flag, deleteInfo: null}); let insertFixedBill = null; let vData = getValidImportData(colMapping, sheet[0].data, fixedBill); console.log(`vData`); console.log(vData); for(let rData of vData){ let t = {}; t.serialNo = rData[colMapping.serialNo]; t.code = rData[colMapping.code]; t.name = rData[colMapping.name]; t.itemCharacterText = rData[colMapping.itemCharacterText]; t.unit = rData[colMapping.unit]; t.quantity = rData[colMapping.quantity]; console.log(t); } // throw 'test'; //导入xx措施项目,若不存在此固定清单，则先插入相关固定清单 if(!fixedBill){ //分部分项工程(不可删除)应存在 if(flag === fixedFlag.SUB_ENGINERRING){ throw '项目不存在分部分项工程' } //措施项目是否存在 let csxm = await billsData.model.findOne({projectID: projectID, 'flags.flag': fixedFlag.MEASURE, deleteInfo: null}); if(!csxm){ throw '项目不存在措施项目' } //插入清单固定行(施工技术措施项目、施工组织措施项目可删除) insertFixedBill = {projectID: projectID, name: flag === fixedFlag.CONSTRUCTION_TECH ? '施工技术措施项目' : '施工组织措施项目', code: '1', ID: uuidV1(), NextSiblingID: -1, ParentID: csxm.ID, flags: [{fieldName: 'fixed', flag: flag}], type: billType.BILL}; //更新前节点 let preDatas = await billsData.model.find({projectID: projectID, ParentID: csxm.ID, deleteInfo: null}); for(let preData of preDatas){ if(preData.NextSiblingID == -1){ await billsData.model.update({ID: preData.ID}, {$set: {NextSiblingID: insertFixedBill.ID}}); break; } } await billsData.model.create(insertFixedBill); fixedBill = insertFixedBill; } console.log(`fixedBill--------------`); console.log(fixedBill); //匹配的清单库 const billsLibId = fields.billsLibId !== undefined && fields.billsLibId.length > 0 && fields.billsLibId[0]? parseInt(fields.billsLibId[0]) : null; let stdBills = [], stdJobs = [], stdCharacters = []; if(billsLibId){ stdBills = await stdBillsModel.find({billsLibId: billsLibId, deleted: false}, '-_id code jobs items engineering'); stdJobs = await stdBillJobsModel.find({billsLibId: billsLibId, deleted: false}); stdCharacters = await stdBillCharacterModel.find({billsLibId: billsLibId, deleted: false}); } //将excel数据转换成清单树结构数据 let insertDatas = parseToBillData(getValidImportData(colMapping, sheet[0].data, fixedBill), colMapping, fixedBill, projectID, {stdBills: stdBills, stdJobs: stdJobs, stdCharacters: stdCharacters}); console.log(`insertDatas`); console.log(insertDatas); if(insertDatas.length === 0){ throw 'excel无有效数据'; } //删除相关数据 let deleteDatas = await billsData.deepDeleteBill([fixedBill], req.session.sessionUser.id); //新增清单数据 await billsData.importBills(insertDatas); //返回数据以更新前端 if(insertFixedBill){ insertDatas.push(insertFixedBill); } responseData.data = {fixedBill: fixedBill, insert: {bill: insertDatas, ration: []}, remove: {bill: deleteDatas.bill, ration: deleteDatas.ration}}; //删除暂存文件 fs.unlink(uploadFullName); res.json(responseData); } catch (error){ if(fs.existsSync(uploadFullName)){ fs.unlink(uploadFullName); } responseData.err = 1; console.log(error); responseData.msg = typeof error === 'object' ? '上传失败' : error; res.json(responseData); } }); } }; //是否是有效的表头列格式,只要含有各表需要的列就行，不严格控制多少列 function isValidSheet(colMapping, fileType){ //09表：序号、项目编码、项目名称、项目特征、计量单位、工程量、金额 let isValid = true; function hasField(field, all){ for(let i of all){ if(field === i){ return true; } } return false; } let needFields; if(fileType === uploadType.lj){ 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; //广联达表：序号、项目编码、项目名称、项目特征、计量单位、工程量、工程量明细、费用明细 } //提取excel表头列对应数据 function getColMapping(sheetData){ //获取表头 function getHeadRow(sheetData){ for(let rData of sheetData) { //寻找含有序号的行，认作表头行 for(let cData of rData){ if (cData && cData.toString().replace(/\s/g, '') === '序号') { headRow = rData; return rData; } } } return []; } let headRow = getHeadRow(sheetData); //获取需要的表头列与列号对应关系 let colMapping = {}; for(let c = 0; c < headRow.length; c++){ if(headRow[c]){ headRow[c] = headRow[c].toString().replace(/\s/g, ''); //重复的，只取第一个 console.log(headRow[c]); if(headRow[c] === '序号' && colMapping.serialNo === undefined){ colMapping.serialNo = c; } else if((headRow[c] === '编码' || headRow[c] === '项目编码') && colMapping.code === undefined){ colMapping.code = c; } else if((headRow[c] === '名称' || headRow[c] === '项目名称') && colMapping.name === undefined){ colMapping.name = c; } else if((headRow[c] === '特征' || headRow[c] === '项目特征') && colMapping.itemCharacterText === undefined){ colMapping.itemCharacterText = c; } else if((headRow[c] === '单位' || headRow[c] === '计量单位') && colMapping.unit === undefined){ colMapping.unit = c; } else if((headRow[c] === '工程量' || headRow[c] === '项目工程量') && colMapping.quantity === undefined){ colMapping.quantity = c; } else if(headRow[c].includes('金额') && colMapping.money === undefined){ colMapping.money = c; } else if(headRow[c] === '工程量明细' && colMapping.quantityDetail === undefined){ colMapping.quantityDetail = c; } else if(headRow[c] === '费用明细' && colMapping.feeDetail === undefined){ colMapping.feeDetail = c; } } } return colMapping; } function rowExistData(rowData){ for(let cData of rowData){ if(cData !== undefined && cData !== ''){ return true; } } return false; } //提取excel表数据中的有效数据(去表头表尾，提取其中的excel数据)(根据fixedBill获取栏头占行数) function getValidImportData(colMapping, sheetData, fixedBill){ let withingD = false; let validData = []; function isHead(rData){ return rData[colMapping.serialNo] && rData[colMapping.serialNo].toString().replace(/\s/g, '') === '序号'; } function isTail(rData){ for(let cData of rData){ if(cData){ let trimCData = cData.toString().replace(/\s/g, ''); if(trimCData === '本页小计' || trimCData === '本页小计'){ return true; } } } return false; } for(let r = 0; r < sheetData.length; r++){ let rData = sheetData[r]; if(isHead(rData)){ withingD = true; if(fixedBill.name !== '施工组织措施项目'){ r++; } continue; } else if(isTail(rData)){ withingD = false; } if(withingD && rowExistData(rData)){ validData.push(rData); } /*if(rData[0]){ //首列去空格 rData[0] = rData[0].toString().replace(/\s/g, ''); //表头 if(rData[0] === '序号'){ withingD = true; if(fixedBill.name !== '施工组织措施项目'){ r++; } continue; } //表尾 else if(rData[0] === '本页小计' || rData[0] === '合计'){ withingD = false; } } if(withingD && rowExistData(rData)){ validData.push(rData); }*/ } return validData; } function getImportFlag(sheetName){ const fixedItem = {'分部分项': fixedFlag.SUB_ENGINERRING, '施工技术措施项目': fixedFlag.CONSTRUCTION_TECH, '施工组织措施项目': fixedFlag.CONSTRUCTION_ORGANIZATION}; for(let flag in fixedItem){ if(sheetName.includes(flag)){ return fixedItem[flag]; } } return null; } function isDef(data){ return typeof data !== 'undefined' && data !== null && data !== ''; } //excel数据转换成清单数据 function parseToBillData(validData, colMapping, fixedBill, projectID, stdData){ let rst = []; let billIdx = {}; let preRootID = -1, preLeafID = -1, preID = -1; //去除转义字符 function removeESC(data){ return isDef(data) ? data.toString().replace(/[\r,\n,\s,\t]/g, '') : data; } //父节点：1.无序号 2有编码 function isRoot(rData){ //序号和编码去除转义字符(有的表格单元格看起来是没数据，实际含有\r,\n等数据) let serialNo = removeESC(rData[colMapping.serialNo]); let code = removeESC(rData[colMapping.code]); return !isDef(serialNo) && isDef(code); } //子节点：有序号 function isLeaf(rData){ let serialNo = removeESC(rData[colMapping.serialNo]); return isDef(serialNo); } //续数据：1. 前数据有效 2.无序号 3.无编码 4.有名称或特征 function isExtend(preData, rData){ let serialNo = removeESC(rData[colMapping.serialNo]); let code = removeESC(rData[colMapping.code]); let name = rData[colMapping.name]; let itemCharacterText = rData[colMapping.itemCharacterText]; 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; } //excel数据与标准库数据匹配，根据清单前九位编码匹配，匹配成功则获取标准清单对应的工程专业、特征及内容 function matchStdBill(excelBill, stdData){ let isMatch = false; let regExp = /^\d{12}$/g; if(regExp.test(excelBill.code)){ let nineCode = excelBill.code.substr(0, 9); for(let stdBill of stdData.stdBills){ //set programID if(nineCode == stdBill.code){ isMatch = true; excelBill.programID = stdBill.engineering ? stdBill.engineering : null; //set jobContent and itemCharacter let tempJob = [], tempCharacter = []; for(let billJob of stdBill.jobs){ for(let stdJob of stdData.stdJobs) { if (billJob.id == stdJob.id) { tempJob.push({isChecked: false, serialNo: billJob.serialNo, content: stdJob.content}); } } } for(let billCharacter of stdBill.items){ for(let stdCharacter of stdData.stdCharacters){ if(billCharacter.id == stdCharacter.id){ let eigenvalue = []; for(let eValue of stdCharacter.itemValue){ eigenvalue.push({isSelected: false, value: eValue.value}); } tempCharacter.push({isChecked: false, serialNo: billCharacter.serialNo, character: stdCharacter.content, eigenvalue: eigenvalue}); } } } excelBill.jobContent = tempJob; excelBill.itemCharacter = tempCharacter; } } } if(!isMatch && excelBill.type === billType.FX){//分项不为空，同时在标准清单中不匹配，则识别为补项 excelBill.type = billType.BX; } } for(let r = 0; r < validData.length; r++){ /* //序号和编码去除转义字符(有的表格单元格看起来是没数据，实际含有\r,\n等数据) let serialNo = validData[r][colMapping.serialNo]; let code = validData[r][colMapping.code]; if(isDef(serialNo)){ serialNo = removeESC(serialNo); } if(isDef(code)){ code = removeESC(code); }*/ let preData = validData[r-1], rData = validData[r]; if(fixedBill.flags[0].flag == fixedFlag.CONSTRUCTION_TECH && rData[colMapping.name] === '施工技术措施项目' || fixedBill.flags[0].flag == fixedFlag.CONSTRUCTION_ORGANIZATION && rData[colMapping.name] === '施工组织措施项目'){ 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] : ''; preBill.name += rData[colMapping.name] ? rData[colMapping.name] : ''; preBill.itemCharacterText += rData[colMapping.itemCharacterText] ? rData[colMapping.itemCharacterText] : ''; preBill.unit += rData[colMapping.unit] ? rData[colMapping.unit] : ''; preBill.quantity += rData[colMapping.quantity] ? rData[colMapping.quantity] : ''; } } else { let newID = uuidV1(); let pID = -1; let preBill = null; if(isRoot(rData)){ pID = fixedBill.ID; preBill = billIdx[preRootID]; } else if(isLeaf(rData)){ pID = preRootID !== -1 ? preRootID : fixedBill.ID; preBill = billIdx[preLeafID]; } //set bill data billIdx[newID] = { ID: newID, ParentID: pID, NextSiblingID: -1, code: rData[colMapping.code] ? removeESC(rData[colMapping.code]) : '', name: rData[colMapping.name] ? removeESC(rData[colMapping.name]) : '', itemCharacterText: rData[colMapping.itemCharacterText] ? rData[colMapping.itemCharacterText] : '', itemCharacter: [], jobContentText: '', jobContent: [], programID: null, unit: rData[colMapping.unit] ? rData[colMapping.unit] : '', quantity: rData[colMapping.quantity] ? rData[colMapping.quantity] : '', //安全文明 flags: fixedBill.flags[0].flag === fixedFlag.CONSTRUCTION_ORGANIZATION && (rData[colMapping.name] === '安全文明施工专项费用' || rData[colMapping.name] === '安全文明施工费') ? [{fieldName: 'fixed', flag: fixedFlag.SAFETY_CONSTRUCTION}] : [], fees: [], projectID: projectID, type: getBillType(rData, fixedBill.flags[0].flag)}; //match stdBill and reset programID、jobContent、itemCharacter matchStdBill(billIdx[newID], stdData); //update preBill NextSibling if(preBill){ preBill.NextSiblingID = newID; } //set new preID preID = newID; preRootID = isRoot(rData) ? newID : preRootID; preLeafID = isLeaf(rData) ? newID : preLeafID; } } for(let i in billIdx){ rst.push(billIdx[i]); } return rst; } async function doBillsOrRationsDelete(data) { let billTask = []; let deleteBillIDs = []; let rationTask=[]; let deleteRationIDs=[]; let qd_query=null; let sub_query=null; if(data['bills']){ billTask = generateUpdateTasks(data['bills'],data.projectID,data.user_id); for(let b_key in data['bills']){ if(data['bills'][b_key]===true){ deleteBillIDs.push(b_key+''); } } if(deleteBillIDs.length>0){ qd_query={projectID: data.projectID, billID: {"$in": deleteBillIDs}}; } } if(data['ration']){ rationTask = generateUpdateTasks(data['ration'],data.projectID,data.user_id); for(let r_key in data['ration']){ if(data['ration'][r_key]===true){ deleteRationIDs.push(r_key+''); } } if(deleteRationIDs.length>0){ if(qd_query==null){//说明没删除清单 qd_query={projectID: data.projectID, rationID: {"$in": deleteRationIDs}}; }else { qd_query={ "$or":[ {projectID: data.projectID, billID: {"$in": deleteBillIDs}}, {projectID: data.projectID, rationID: {"$in": deleteRationIDs}} ] } } sub_query={projectID: data.projectID, rationID: {"$in": deleteRationIDs}}; } } //先删除工程量明细 if(qd_query!=null){ await quantity_detail.deleteByQuery(qd_query) ; } if(sub_query!=null){ await ration_coe.deleteMany(sub_query);//删除附注条件 await ration_glj.deleteMany(sub_query);//删除定额工料机 await rationInstallationModel.deleteMany(sub_query);//删除安装增加费 } if(rationTask.length>0){ await ration_model.model.bulkWrite(rationTask);//删除定额 } if(billTask.length>0){ await billsData.model.bulkWrite(billTask);//删除清单 } return 'success'; } function generateSingleDeleteTasks(data) { let updateData = data.updateData; updateData[data.ID]=true; let tasks = generateUpdateTasks(updateData,data.projectID,data.user_id); return tasks; } function generateUpdateTasks(data,projectID,user_id) { let tasks=[]; let updateData = data; let deleteInfo={deleted: true, deleteDateTime: new Date(), deleteBy: user_id}; for(let key in updateData){ let task={ updateOne:{ filter:{ ID:key, projectID:projectID } } }; if(updateData[key]===true){ task.updateOne.update={ deleteInfo:deleteInfo }; }else { task.updateOne.update=updateData[key]; } tasks.push(task); } return tasks; }