importBills.js 19 KB

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