importBills.js 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442
  1. 'use strict';
  2. /**
  3. *
  4. *
  5. * @author Zhong
  6. * @date 2018/8/2
  7. * @version
  8. */
  9. /*
  10. * 清单导入模块,前端导入excel,进行数据提取,用lz-string进行压缩上传处理
  11. * */
  12. const importBills = (function(){
  13. //单元格数据是否存在
  14. function _isDef(data){
  15. return typeof data !== 'undefined' && data !== null && data !== '';
  16. }
  17. //去除转义字符
  18. function _deESC(data) {
  19. return _isDef(data) ? data.toString().replace(/[\r,\n,\s,\t]/g, '') : data;
  20. }
  21. //列名对应中文字符
  22. const colText = {
  23. serialNo: ['序号'],
  24. code: ['编码', '项目编码'],
  25. name: ['名称', '项目名称'],
  26. itemCharacterText: ['特征', '项目特征'],
  27. unit: ['单位', '计量单位'],
  28. quantity: ['工程量', '项目工程量'],
  29. money: ['金额'],
  30. quantityDetail: ['工程量明细'],
  31. feeDetail: ['费用明细'],
  32. summation: ['合计', '本页小计'],
  33. };
  34. //导入位置对应清单固定标记
  35. const positionFlag = {
  36. fbfx: fixedFlag.SUB_ENGINERRING,
  37. jscsxm: fixedFlag.CONSTRUCTION_TECH,
  38. zzcsxm: fixedFlag.CONSTRUCTION_ORGANIZATION,
  39. };
  40. //上传类型
  41. const uploadType = {
  42. lj: 'lj',
  43. gld: 'gld',
  44. };
  45. //设置导入表内容(选择导入位置)
  46. //@param {Object}workBook
  47. function setImportSheetsInfo(sheets){
  48. let sheetNames = [];
  49. let indexMapping = {};
  50. for(let sheetName in sheets){
  51. indexMapping[sheets[sheetName]['index']] = sheetName;
  52. }
  53. let sheetsCount = Object.keys(sheets).length;
  54. for(let i = 0; i < sheetsCount; i++){
  55. sheetNames.push(indexMapping[i]);
  56. }
  57. let sheetArea = $('#uploadSheets'),
  58. sheetHeader = $('#uploadSheetsHead');
  59. $('#uploadSheets').height('');
  60. sheetArea.empty();
  61. for(let sheetName of sheetNames){
  62. 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">
  63. <input class="form-check-input" type="checkbox">${sheetName}</label></div>`);
  64. sheetDiv.find('input[type="checkbox"]').click(function () {
  65. if($('#uploadAlert').is(':visible')){
  66. $('#uploadAlert').hide();
  67. }
  68. });
  69. let sel = $(`<select style="margin-left: 5px; border-radius: .20rem;"><option value="fbfx">分部分项工程</option><option value="zzcsxm">施工组织措施项目</option><option value="jscsxm">施工技术措施项目</option></select>`);
  70. if(sheetName.includes('分部分项工程项目清单计价表')){
  71. sheetDiv.find('input[type="checkbox"]').prop('checked', true);
  72. sel.find('option:eq(0)').prop('selected', true);
  73. }
  74. else if(sheetName.includes('施工组织措施项目清单计价表')){
  75. sheetDiv.find('input[type="checkbox"]').prop('checked', true);
  76. sel.find('option:eq(1)').prop('selected', true);
  77. }
  78. else if(sheetName.includes('施工技术措施项目清单计价表')){
  79. sheetDiv.find('input[type="checkbox"]').prop('checked', true);
  80. sel.find('option:eq(2)').prop('selected', true);
  81. }
  82. sheetDiv.append(sel);
  83. sheetArea.append(sheetDiv);
  84. }
  85. if(sheetNames.length > 0){
  86. sheetArea.show();
  87. sheetHeader.show();
  88. }
  89. if($('#uploadSheets').height() > 250){
  90. sheetArea.css('overflow', 'auto');
  91. sheetArea.height(250);
  92. }
  93. else {
  94. sheetArea.css('overflow', 'hidden');
  95. }
  96. }
  97. //获得选择导入的表信息(表索引及导入位置)
  98. //@return {Object}
  99. function getImportSheetsInfo(){
  100. let rst = [];
  101. let sheetArea = $('#uploadSheets');
  102. let checkedInputs = sheetArea.find('input:checked');
  103. for(let checked of checkedInputs){
  104. rst.push({index: $(checked).parent().parent().index(), position: $(checked).parent().next().select().val()})
  105. }
  106. return rst;
  107. }
  108. function getSheetByIndex(sheets, index){
  109. for(let sheetName in sheets){
  110. let sheet = sheets[sheetName];
  111. if(sheet.index === index){
  112. return sheet;
  113. }
  114. }
  115. return null;
  116. }
  117. //提取excel表头列名与列下标映射
  118. //@
  119. function getColMapping(dataTable){
  120. //获取表头
  121. function getHeadRow(dataTable){
  122. for(let rowIdx in dataTable ){
  123. for(let colIdx in dataTable[rowIdx]){
  124. let cellData = dataTable[rowIdx][colIdx]['value'];
  125. if(cellData && _deESC(cellData) === colText.serialNo[0]){
  126. return dataTable[rowIdx];
  127. }
  128. }
  129. }
  130. return {};
  131. }
  132. //获取需要的表头列与列号对应关系
  133. let colMapping = {};
  134. let headRow = getHeadRow(dataTable);
  135. for(let colIdx in headRow){
  136. let cellData = headRow[colIdx]['value'];
  137. if(!_isDef(cellData)){
  138. continue;
  139. }
  140. //序号
  141. if(colMapping.serialNo === undefined && cellData === colText.serialNo[0]){
  142. colMapping.serialNo = colIdx;
  143. }
  144. //编码
  145. else if(colMapping.code === undefined && (cellData === colText.code[0] || cellData === colText.code[1])){
  146. colMapping.code = colIdx;
  147. }
  148. //名称
  149. else if(colMapping.name === undefined && (cellData === colText.name[0] || cellData === colText.name[1])){
  150. colMapping.name = colIdx;
  151. }
  152. //项目特征
  153. else if(colMapping.itemCharacterText === undefined && (cellData === colText.itemCharacterText[0] || cellData === colText.itemCharacterText[1])){
  154. colMapping.itemCharacterText = colIdx;
  155. }
  156. //单位
  157. else if(colMapping.unit === undefined && (cellData === colText.unit[0] || cellData === colText.unit[1])){
  158. colMapping.unit = colIdx;
  159. }
  160. //工程量
  161. else if(colMapping.quantity === undefined && (cellData === colText.quantity[0] || cellData === colText.quantity[1])){
  162. colMapping.quantity = colIdx;
  163. }
  164. //金额
  165. else if(colMapping.money === undefined && cellData.includes(colText.money[0])){
  166. colMapping.money = colIdx;
  167. }
  168. //工程量明细
  169. else if(colMapping.quantityDetail === undefined && cellData === colText.quantityDetail[0]){
  170. colMapping.quantityDetail = colIdx;
  171. }
  172. //费用明细
  173. else if(colMapping.feeDetail === undefined && cellData === colText.feeDetail[0]){
  174. colMapping.feeDetail = colIdx;
  175. }
  176. }
  177. return colMapping;
  178. }
  179. //是否是有效的表头列格式,只要含有各表需要的列就行,不严格控制多少列
  180. function isValidSheet(colMapping, fileType){
  181. function hasField(field, all){
  182. for(let i of all){
  183. if(field === i){
  184. return true;
  185. }
  186. }
  187. return false;
  188. }
  189. let needFields;
  190. if(fileType === uploadType.lj){
  191. //09表:序号、项目编码、项目名称、项目特征、计量单位、工程量、金额
  192. needFields = ['serialNo', 'code', 'name', 'money'];
  193. }
  194. else {
  195. //广联达表:序号、项目编码、项目名称、项目特征、计量单位、工程量、工程量明细、费用明细
  196. needFields = ['serialNo', 'code', 'name', 'itemCharacterText', 'unit', 'quantity', 'quantityDetail', 'feeDetail'];
  197. }
  198. let hasFieldCount = 0;
  199. for(let attr in colMapping){
  200. if(hasField(attr, needFields)){
  201. hasFieldCount++;
  202. }
  203. }
  204. return hasFieldCount === needFields.length;
  205. }
  206. //获取要无效和有效导入表
  207. //@param {Array}importSheetInfo 勾选要导入的表
  208. function getImportSheets(sheets, sheetsInfo, fileType){
  209. let rst = {invalidSheets: [], validSheets: {fbfx: [], jscsxm: [], zzcsxm: []}};
  210. for(let sheetInfo of sheetsInfo){
  211. let sheet = getSheetByIndex(sheets, sheetInfo.index);
  212. if(!sheet){
  213. continue;
  214. }
  215. //没有数据
  216. if(!sheet.data.dataTable){
  217. rst.invalidSheets.push(sheet.name);
  218. continue;
  219. }
  220. //获取表的列设置确定导入的格式是否合法(09、广联达)
  221. let colMapping = getColMapping(sheet.data.dataTable);
  222. if(!isValidSheet(colMapping, fileType)){
  223. rst.invalidSheets.push(sheet.name);
  224. continue;
  225. }
  226. //合法的表
  227. sheet.colMapping = colMapping;
  228. //将合法的表按导入位置分类当做一个表来处理
  229. if(rst.validSheets[sheetInfo.position] !== undefined){
  230. rst.validSheets[sheetInfo.position].push(sheet)
  231. }
  232. }
  233. return rst;
  234. }
  235. //行存在数据
  236. function rowExistData(rowData){
  237. for(let colIdx in rowData){
  238. let colData = rowData[colIdx]['value'];
  239. if(_isDef(colData)){
  240. return true;
  241. }
  242. }
  243. return false;
  244. }
  245. //提取excel表数据中的有效数据(去表头表尾,提取其中的excel数据)(根据fixedBill获取栏头占行数)
  246. function getValidImportData(colMapping, sheetData){
  247. let withingD = false;
  248. let validData = [];
  249. function isHead(rData){
  250. return rData[colMapping.serialNo] && _deESC(rData[colMapping.serialNo]['value']) === colText.serialNo[0];
  251. }
  252. function isTail(rowData){
  253. for(let colIdx in rowData){
  254. let colData = rowData[colIdx]['value'];
  255. if(colData){
  256. let trimColData= _deESC(colData);
  257. if(trimColData === colText.summation[0] || trimColData === colText.summation[1]){
  258. return true;
  259. }
  260. }
  261. }
  262. return false;
  263. }
  264. for(let rowIdx in sheetData){
  265. let rowData = sheetData[rowIdx];
  266. if(isHead(rowData)){
  267. withingD = true;
  268. continue;
  269. }
  270. else if(isTail(rowData)){
  271. withingD = false;
  272. }
  273. if(withingD && rowExistData(rowData)){
  274. validData.push(rowData);
  275. }
  276. }
  277. return validData;
  278. }
  279. //excel数据转换成清单数据
  280. function parseToBillData(validData, colMapping, flag, projectID){
  281. let rst = [];
  282. let billIdx = {};
  283. let preRootID = -1,
  284. preLeafID = -1,
  285. preID = -1;
  286. //父节点:1.无序号 2有编码
  287. function isRoot(rData){
  288. //序号和编码去除转义字符(有的表格单元格看起来是没数据,实际含有\r,\n等数据)
  289. let serialNo = rData[colMapping.serialNo] ? _deESC(rData[colMapping.serialNo]['value']) : '';
  290. let code = rData[colMapping.code] ? _deESC(rData[colMapping.code]['value']) : '';
  291. return !_isDef(serialNo) && _isDef(code);
  292. }
  293. //子节点:有序号
  294. function isLeaf(rData){
  295. let serialNo = rData[colMapping.serialNo] ? _deESC(rData[colMapping.serialNo]['value']) : '';
  296. return _isDef(serialNo);
  297. }
  298. //续数据:1. 前数据有效 2.无序号 3.无编码 4.有名称或特征
  299. function isExtend(preData, rData){
  300. let serialNo = rData[colMapping.serialNo] ? _deESC(rData[colMapping.serialNo]['value']) : '';
  301. let code = rData[colMapping.code] ? _deESC(rData[colMapping.code]['value']) : '';
  302. let name = rData[colMapping.name] ? _deESC(rData[colMapping.name]['value']) : '';
  303. let itemCharacterText = rData[colMapping.itemCharacterText] ? _deESC(rData[colMapping.itemCharacterText]['value']) : '';
  304. return _isDef(preData) && (isRoot(preData) || isLeaf(preData)) && !_isDef(serialNo) && !_isDef(code) && (_isDef(name) || _isDef(itemCharacterText));
  305. }
  306. function getBillType(rData, flag){
  307. if(flag === fixedFlag.CONSTRUCTION_TECH || flag === fixedFlag.CONSTRUCTION_ORGANIZATION){
  308. return billType.BILL;
  309. }
  310. else if(flag === fixedFlag.SUB_ENGINERRING){
  311. return isLeaf(rData) ? billType.FX : billType.FB;
  312. }
  313. return null;
  314. }
  315. let preData = null;
  316. for(let r = 0; r < validData.length; r++){
  317. let rData = validData[r];
  318. if(flag == fixedFlag.CONSTRUCTION_TECH && rData[colMapping.name] && rData[colMapping.name]['value'] === '施工技术措施项目'
  319. || flag == fixedFlag.CONSTRUCTION_ORGANIZATION && rData[colMapping.name] && rData[colMapping.name]['value'] === '施工组织措施项目'){
  320. continue;
  321. }
  322. //过滤无效数据
  323. if(!isRoot(rData) && !isLeaf(rData) && !isExtend(preData, rData)){
  324. continue;
  325. }
  326. if(isExtend(preData, rData)){
  327. let preBill = billIdx[preID];
  328. if(preBill){
  329. //合并续数据
  330. preBill.code += rData[colMapping.code] && rData[colMapping.code]['value'] ? rData[colMapping.code]['value'] : '';
  331. preBill.name += rData[colMapping.name] && rData[colMapping.name]['value'] ? rData[colMapping.name]['value'] : '';
  332. preBill.itemCharacterText += rData[colMapping.itemCharacterText] && rData[colMapping.itemCharacterText]['value'] ? '\n' + rData[colMapping.itemCharacterText]['value'] : '';
  333. preBill.unit += rData[colMapping.unit] && rData[colMapping.unit]['value'] ? rData[colMapping.unit]['value'] : '';
  334. preBill.quantity += rData[colMapping.quantity] && rData[colMapping.quantity]['value'] ? rData[colMapping.quantity]['value'] : '';
  335. }
  336. }
  337. else {
  338. let newID = uuid.v1();
  339. let pID = -1;
  340. let preBill = null;
  341. let nodeType = 'root';//后端以此标记来设置ParentID
  342. if(isRoot(rData)){
  343. //pID = 'fixedBillID';
  344. preBill = billIdx[preRootID];
  345. }
  346. else if(isLeaf(rData)){
  347. nodeType = 'leaf';
  348. //pID = preRootID !== -1 ? preRootID : fixedBill.ID;
  349. preBill = billIdx[preLeafID];
  350. }
  351. //set bill data
  352. billIdx[newID] = {
  353. nodeType: nodeType,
  354. ID: newID, ParentID: pID, NextSiblingID: -1,
  355. code: rData[colMapping.code] && rData[colMapping.code]['value'] ? _deESC(rData[colMapping.code]['value']) : '',
  356. name: rData[colMapping.name] && rData[colMapping.name]['value'] ? _deESC(rData[colMapping.name]['value']) : '',
  357. itemCharacterText: rData[colMapping.itemCharacterText] && rData[colMapping.itemCharacterText]['value'] ? rData[colMapping.itemCharacterText]['value'] : '',
  358. itemCharacter: [],
  359. jobContentText: '',
  360. jobContent: [],
  361. programID: null,
  362. unit: rData[colMapping.unit] && rData[colMapping.unit]['value'] ? rData[colMapping.unit]['value'] : '',
  363. quantity: rData[colMapping.quantity] && rData[colMapping.quantity]['value'] ? rData[colMapping.quantity]['value'] : '',
  364. //安全文明
  365. flags: flag === fixedFlag.CONSTRUCTION_ORGANIZATION && (rData[colMapping.name] && (rData[colMapping.name]['value'] === '安全文明施工专项费用' || rData[colMapping.name]['value'] === '安全文明施工费')) ?
  366. [{fieldName: 'fixed', flag: fixedFlag.SAFETY_CONSTRUCTION}] : [],
  367. fees: [],
  368. projectID: projectID,
  369. type: getBillType(rData, flag)};
  370. //match stdBill and reset programID、jobContent、itemCharacter
  371. //matchStdBill(billIdx[newID], stdData);
  372. //update preBill NextSibling
  373. if(preBill){
  374. preBill.NextSiblingID = newID;
  375. }
  376. //set new preID
  377. preID = newID;
  378. preRootID = isRoot(rData) ? newID : preRootID;
  379. preLeafID = isLeaf(rData) ? newID : preLeafID;
  380. }
  381. preData = rData;
  382. }
  383. for(let i in billIdx){
  384. rst.push(billIdx[i]);
  385. }
  386. return rst;
  387. }
  388. function getImportData(validSheets, projectID){
  389. let rst = {fbfx: [], jscsxm: [], zzcsxm: []};
  390. let validSheetsDatas = [];
  391. for(let uploadPosition in validSheets){
  392. let validExcelData = [];
  393. for(let uSheet of validSheets[uploadPosition]){
  394. validExcelData = validExcelData.concat(getValidImportData(uSheet.colMapping, uSheet.data.dataTable));
  395. }
  396. if(validSheets[uploadPosition].length > 0){
  397. validSheetsDatas.push({position: uploadPosition, colMapping: validSheets[uploadPosition][0].colMapping, validExcelData: validExcelData});
  398. }
  399. }
  400. console.log(validSheetsDatas);
  401. for(let validSheetData of validSheetsDatas){
  402. if(validSheetData.validExcelData.length > 0){
  403. rst[validSheetData.position] = parseToBillData(validSheetData.validExcelData, validSheetData.colMapping, positionFlag[validSheetData.position], projectID);
  404. }
  405. }
  406. console.log(rst);
  407. return rst;
  408. }
  409. function excelHasValidBills(importBillsData){
  410. for(let i in importBillsData){
  411. if(importBillsData[i].length > 0){
  412. return true;
  413. }
  414. }
  415. return false;
  416. }
  417. return {setImportSheetsInfo, getImportSheetsInfo, getImportSheets, getImportData, excelHasValidBills}
  418. })();