deal_bills.js 9.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202
  1. 'use strict';
  2. /**
  3. *
  4. *
  5. * @author Mai
  6. * @date 2018/5/8
  7. * @version
  8. */
  9. module.exports = app => {
  10. class DealBills extends app.BaseService {
  11. /**
  12. * 构造函数
  13. * @param ctx
  14. */
  15. constructor(ctx) {
  16. super(ctx);
  17. this.tableName = 'deal_bills';
  18. }
  19. /**
  20. * 导入Excel数据(node-xlsx)
  21. *
  22. * @param {Array} sheet - Excel文件中的全部工作表
  23. * @param {Number} tenderId - 所属标段Id
  24. * @returns {Promise<boolean>}
  25. */
  26. async importData(sheet, tenderId) {
  27. let result = false;
  28. const transaction = await this.db.beginTransaction();
  29. try {
  30. const bills = [];
  31. // 识别表头导入
  32. let iCode = -1, iName = -1, iUnit = -1, iUp = -1, iQty = -1, iTp = -1, bCheckCol = false;
  33. for (let iRow = 0; iRow < sheet.data.length; iRow++) {
  34. const row = sheet.data[iRow];
  35. if (!bCheckCol) {
  36. for (let iCol = 0; iCol < row.length; iCol++) {
  37. const value = row[iCol];
  38. if (typeof value !== "string") { continue }
  39. if (['清单编号', '子目号', '子目编号', '编号', '清单编号'].indexOf(value) >= 0) iCode = iCol;
  40. if (['清单名称', '名称', '子目名称'].indexOf(value) >= 0) iName = iCol;
  41. if (value.indexOf('单位') >= 0) iUnit = iCol;
  42. if (value.indexOf('单价') >= 0) iUp = iCol;
  43. if (value.indexOf('数量') >= 0) iQty = iCol;
  44. if (value.indexOf('金额') >= 0 || value.indexOf('合价') >= 0) iTp = iCol;
  45. }
  46. bCheckCol = (iCode >= 0 && iName >= 0 && iUnit >= 0 && iUp >= 0 && iQty >= 0);
  47. if (!bCheckCol) {
  48. iCode = -1;
  49. iName = -1;
  50. iUnit = -1;
  51. iUp = -1;
  52. iQty = -1;
  53. }
  54. } else if (this.ctx.helper.validBillsCode(row[iCode])) {
  55. const data = {
  56. deal_id: bills.length + 1,
  57. tender_id: tenderId,
  58. code: row[iCode],
  59. name: row[iName],
  60. unit: row[iUnit],
  61. unit_price: row[iUp],
  62. quantity: row[iQty],
  63. };
  64. if ((data.unit_price && !this._.isNumber(data.unit_price)) || (data.quantity && !this._.isNumber(data.quantity))) {
  65. throw '导入的Excel的数据类型有误,请检查第' + (iRow + 1) + '行';
  66. }
  67. bills.push(data);
  68. }
  69. }
  70. if (!bCheckCol) {
  71. throw '导入的Excel表头定义有误,请下载示例检查';
  72. }
  73. // 固定列,从第一行开始导入
  74. // let iCode = 0, iName = 1, iUnit = 2, iUp = 4, iQty = 3;
  75. // for (let iRow = 1; iRow < sheet.data.length; iRow++) {
  76. // const data = {
  77. // deal_id: bills.length + 1,
  78. // tender_id: tenderId,
  79. // code: row[iCode],
  80. // name: row[iName],
  81. // unit: row[iUnit],
  82. // unit_price: row[iUp],
  83. // quantity: row[iQty],
  84. // };
  85. // if (!data.code || data.code === '' || !data.name || data.name === '') continue;
  86. // if ((data.unit_price && !this._.isNumber(data.unit_price)) || (data.quantity && !this._.isNumber(data.quantity))) {
  87. // throw '导入的Excel的数据类型有误,请检查第' + (iRow + 1) + '行';
  88. // }
  89. // bills.push(data);
  90. // }
  91. if (bills.length > 0) {
  92. await transaction.delete(this.tableName, {tender_id: tenderId});
  93. const billsResult = await transaction.insert(this.tableName, bills);
  94. if (billsResult.affectedRows !== bills.length) {
  95. throw '导入签约清单数据出错';
  96. }
  97. } else {
  98. throw 'Excel文件中无签约清单数据';
  99. }
  100. await transaction.commit();
  101. result = true;
  102. } catch (err) {
  103. await transaction.rollback();
  104. throw err;
  105. }
  106. return result;
  107. }
  108. /**
  109. * 导入Excel数据(js-xlsx)
  110. *
  111. * @param {Array} sheet - Excel文件中的全部工作表
  112. * @param {Number} tenderId - 所属标段Id
  113. * @returns {Promise<boolean>}
  114. */
  115. async importDataJsXlsx(sheet, tenderId) {
  116. let result = false;
  117. // 整理数据
  118. const bills = [];
  119. let iCode = -1, iName = -1, iUnit = -1, iUp = -1, iQty = -1, iTp = -1, bCheckCol = false;
  120. for (let iRow = 0; iRow < sheet.rows.length; iRow++) {
  121. const row = sheet.rows[iRow];
  122. if (!bCheckCol) {
  123. for (let iCol = 0; iCol < row.length; iCol++) {
  124. const value = row[iCol];
  125. if (typeof value !== "string") { continue }
  126. if (value === '子目号' || value === '清单编号') iCode = iCol;
  127. if (value.indexOf('名称') >= 0) iName = iCol;
  128. if (value.indexOf('单位') >= 0) iUnit = iCol;
  129. if (value.indexOf('单价') >= 0) iUp = iCol;
  130. if (value.indexOf('数量') >= 0) iQty = iCol;
  131. if (value.indexOf('金额') >= 0 || value.indexOf('合价') >= 0) iTp = iCol;
  132. }
  133. bCheckCol = (iCode >= 0 && iName >= 0 && iUnit >= 0 && iUp >= 0 && iQty >= 0 && iTp >= 0);
  134. if (!bCheckCol) {
  135. iCode = -1;
  136. iName = -1;
  137. iUnit = -1;
  138. iUp = -1;
  139. iQty = -1;
  140. iTp = -1;
  141. }
  142. } else if (this.ctx.helper.validBillsCode(row[iCode])) {
  143. const data = {
  144. deal_id: bills.length + 1,
  145. tender_id: tenderId,
  146. code: this.ctx.helper.replaceReturn(row[iCode]),
  147. name: this.ctx.helper.replaceReturn(row[iName]),
  148. unit: this.ctx.helper.replaceReturn(row[iUnit]),
  149. unit_price: (row[iUp] === undefined || row[iUp] === null) ? 0 : this._.toNumber(row[iUp]),
  150. quantity: (row[iQty] === undefined || row[iQty] === null) ? 0 : this._.toNumber(row[iQty]),
  151. total_price: (row[iTp] === undefined || row[iTp] === null) ? 0 : this._.toNumber(row[iTp]),
  152. };
  153. if (this._.isNaN(data.unit_price) || this._.isNaN(data.quantity) || this._.isNaN(data.total_price)) {
  154. throw '导入的Excel的数据类型有误,请检查第' + (iRow + 1) + '行';
  155. }
  156. bills.push(data);
  157. }
  158. }
  159. if (!bCheckCol) {
  160. throw '导入的Excel表头定义有误,请下载示例检查';
  161. }
  162. // 写入数据
  163. const transaction = await this.db.beginTransaction();
  164. try {
  165. if (bills.length > 0) {
  166. await transaction.delete(this.tableName, {tender_id: tenderId});
  167. const billsResult = await transaction.insert(this.tableName, bills);
  168. if (billsResult.affectedRows !== bills.length) {
  169. throw '导入签约清单数据出错';
  170. }
  171. } else {
  172. throw 'Excel文件中无签约清单数据';
  173. }
  174. await transaction.commit();
  175. result = true;
  176. } catch (err) {
  177. await transaction.rollback();
  178. throw err;
  179. }
  180. return result;
  181. }
  182. /*
  183. * 报表用
  184. * @param {Number} tenderId - 所属标段Id
  185. */
  186. async getDataByTenderId(tenderId) {
  187. const sql = 'SELECT Bills.* FROM ' + this.tableName + ' As Bills WHERE tender_id = ?';
  188. const sqlParam = [tenderId];
  189. return await this.db.query(sql, sqlParam);
  190. // let rst = await this.getDataByCondition({tender_id: tenderId});
  191. // return rst;
  192. }
  193. }
  194. return DealBills;
  195. }