'use strict'; /** * * * @author Mai * @date 2018/8/14 * @version */ const audit = require('../const/audit'); module.exports = app => { class ChangeAuditList extends app.BaseService { /** * 构造函数 * * @param {Object} ctx - egg全局变量 * @return {void} */ constructor(ctx) { super(ctx); this.tableName = 'change_audit_list'; } /** * 取出变更令清单列表，并按台账清单在前，空白清单在后排序 * @return {void} */ async getList(cid) { const sql = 'SELECT * FROM ?? WHERE `cid` = ? ORDER BY `lid` = "0", `id` asc'; const sqlParam = [this.tableName, cid]; return await this.db.query(sql, sqlParam); } /** * 添加空白变更清单 * @return {void} */ async add(data) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } const insertData = { tid: this.ctx.tender.id, cid: this.ctx.change.cid, lid: '0', code: '', name: '', bwmx: '', unit: '', unit_price: null, oamount: 0, camount: 0, samount: '', detail: '', spamount: 0, xmj_code: null, xmj_jldy: null, xmj_dwgc: null, xmj_fbgc: null, xmj_fxgc: null, gcl_id: '', }; // 新增工料 const result = await this.db.insert(this.tableName, insertData); if (result.affectedRows === 0) { throw '新增空白清单数据失败'; } return await this.getDataById(result.insertId); } /** * 批量添加空白变更清单 * @return {void} */ async batchAdd(data) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } const num = data.num ? parseInt(data.num) : 0; if (num < 1 || num > 100) { throw '批量添加的空白清单数目不能小于1或大于100'; } const insertArray = []; for (let i = 0; i < num; i++) { const insertData = { tid: this.ctx.tender.id, cid: this.ctx.change.cid, lid: '0', code: '', name: '', bwmx: '', unit: '', unit_price: null, oamount: 0, camount: 0, samount: '', detail: '', spamount: 0, xmj_code: null, xmj_jldy: null, xmj_dwgc: null, xmj_fbgc: null, xmj_fxgc: null, gcl_id: '', }; insertArray.push(insertData); } // 新增工料 const result = await this.db.insert(this.tableName, insertArray); if (result.affectedRows !== num) { throw '批量添加空白清单数据失败'; } // 获取刚批量添加的所有list for (let j = 0; j < num; j++) { insertArray[j].id = result.insertId + j; } return insertArray; } /** * 删除变更清单 * @param {int} id 清单id * @return {void} */ async del(id) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } const transaction = await this.db.beginTransaction(); try { // 判断是否可删 await transaction.delete(this.tableName, { id }); // 重新算变更令总额 await this.calcCamountSum(transaction); await transaction.commit(); return true; } catch (err) { await transaction.rollback(); throw err; } } /** * 修改变更清单 * @param {Object} data 工料内容 * @param {int} order 期数 * @return {void} */ async save(data, order) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } const transaction = await this.db.beginTransaction(); try { // const mb_id = data.mb_id; // delete data.mb_id; await transaction.update(this.tableName, data); // await this.calcQuantityByML(transaction, mb_id); await this.calcCamountSum(transaction); await transaction.commit(); return true; } catch (err) { await transaction.rollback(); throw err; } } /** * 修改变更清单 复制粘贴 * @param {Object} datas 修改内容 * @return {void} */ async saveDatas(datas) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } // 判断是否可修改 // 判断t_type是否为费用 const transaction = await this.db.beginTransaction(); try { // for (const data of datas) { // const mb_id = data.mb_id; // delete data.mb_id; // await transaction.update(this.tableName, data); // await this.calcQuantityByML(transaction, mb_id); // } await transaction.updateRows(this.tableName, datas); await this.calcCamountSum(transaction); await transaction.commit(); return true; } catch (err) { await transaction.rollback(); throw err; } } /** * 台账数据清单 重新选择 * @param {Object} datas 内容 * @return {void} */ async saveLedgerListDatas(datas) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } // 判断是否可修改 // 判断t_type是否为费用 const transaction = await this.db.beginTransaction(); try { const sql1 = 'SELECT a.* FROM ?? as b LEFT JOIN ?? as a ON b.cbid = a.id WHERE b.cid = ? GROUP BY b.cbid'; const sqlParam1 = [this.ctx.service.stageChange.tableName, this.tableName, this.ctx.change.cid]; const usedList = await transaction.query(sql1, sqlParam1); // 先删除原本的台账清单数据 const sql = 'DELETE FROM ?? WHERE cid = ? and lid != "0"'; const sqlParam = [this.tableName, this.ctx.change.cid]; await transaction.query(sql, sqlParam); const insertDatas = []; for (const data of datas) { data.tid = this.ctx.tender.id; data.cid = this.ctx.change.cid; data.spamount = data.camount; data.samount = ''; insertDatas.push(data); } if (insertDatas.length > 0) await transaction.insert(this.tableName, insertDatas); await this.calcCamountSum(transaction); // 更新stage_change和stage_change_final的cbid if (usedList.length > 0) { const updateList = []; const sql2 = 'SELECT * FROM ?? WHERE `cid` = ? AND `lid` != "0"'; const sqlParam2 = [this.tableName, this.ctx.change.cid]; const newList = await transaction.query(sql2, sqlParam2); // const newList = await transaction.select(this.tableName, { where: { cid: this.ctx.change.cid } }); for (const used of usedList) { const newone = this._.find(newList, { code: used.code, lid: used.lid, gcl_id: used.gcl_id, bwmx: used.bwmx }); if (newone) { updateList.push({ row: { cbid: newone.id, }, where: { cid: this.ctx.change.cid, cbid: used.id, }, }); } } if (updateList.length > 0) { await transaction.updateRows(this.ctx.service.stageChange.tableName, updateList); await transaction.updateRows(this.ctx.service.stageChangeFinal.tableName, updateList); } } await transaction.commit(); return true; } catch (err) { await transaction.rollback(); throw err; } } /** * 台账数据清单 清除部分并重新算原设计总金额 * @param {Object} datas 内容 * @return {void} */ async removeLedgerListDatas(datas) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } // 判断是否可修改 // 判断t_type是否为费用 const transaction = await this.db.beginTransaction(); try { // 先删除原本的台账清单数据 // const sql = 'DELETE FROM ?? WHERE cid = ? and lid != "0"'; // const sqlParam = [this.tableName, this.ctx.change.cid]; // await transaction.query(sql, sqlParam); // const insertDatas = []; for (const data of datas) { // data.tid = this.ctx.tender.id; // data.cid = this.ctx.change.cid; // data.spamount = data.camount; // data.samount = ''; // insertDatas.push(data); await transaction.delete(this.tableName, { id: data.id }); } // if (insertDatas.length > 0) await transaction.insert(this.tableName, insertDatas); await this.calcCamountSum(transaction); await transaction.commit(); return true; } catch (err) { await transaction.rollback(); throw err; } } async calcCamountSum(transaction) { // const sql = 'SELECT SUM(ROUND(`camount`*`unit_price`, )) as total_price FROM ?? WHERE cid = ?'; // const sqlParam = [this.tableName, this.change.cid]; // const tp = await transaction.queryOne(sql, sqlParam); // 防止小数位不精确，采用取值计算 const sql = 'SELECT unit_price, spamount FROM ?? WHERE cid = ?'; const sqlParam = [this.tableName, this.ctx.change.cid]; const changeList = await transaction.query(sql, sqlParam); let total_price = 0; const tp_decimal = this.ctx.change.tp_decimal ? this.ctx.change.tp_decimal : this.ctx.tender.info.decimal.tp; for (const cl of changeList) { total_price = this.ctx.helper.accAdd(total_price, this.ctx.helper.mul(cl.unit_price, cl.spamount, tp_decimal)); } const updateData = { total_price, }; const options = { where: { cid: this.ctx.change.cid, }, }; await transaction.update(this.ctx.service.change.tableName, updateData, options); } /** * 用户数据数量提交 * @param {Object} data 内容 * @return {void} */ async saveAmountData(data) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } // 判断是否可修改 // 判断t_type是否为费用 const transaction = await this.db.beginTransaction(); try { await transaction.update(this.tableName, data); await this.calcCamountSum(transaction); await transaction.commit(); return true; } catch (err) { await transaction.rollback(); throw err; } } async gatherBgBills(tid) { const sql = 'SELECT cb.code, cb.name, cb.unit, cb.unit_price, Round(Sum(cb.samount + 0), 6) as quantity' + ' FROM ' + this.tableName + ' cb' + ' LEFT JOIN ' + this.ctx.service.change.tableName + ' c ON cb.cid = c.cid' + ' WHERE cb.tid = ? and c.status = ?' + ' GROUP BY code, name, unit, unit_price'; const param = [tid, audit.flow.status.checked]; const result = await this.db.query(sql, param); for (const b of result) { b.total_price = this.ctx.helper.mul(b.unit_price, b.quantity, this.ctx.tender.info.decimal.tp); } return result; } /** * 报表用 * Tony Kang * @param {tid} tid - 标段id * @return {void} */ async getChangeAuditBills(tid) { const sql = 'SELECT cb.*' + ' FROM ' + this.tableName + ' cb' + ' LEFT JOIN ' + this.ctx.service.change.tableName + ' c ON cb.cid = c.cid' + ' WHERE c.tid = ? and c.status = 3' + ' ORDER BY cb.cid, cb.code'; const param = [tid]; const result = await this.db.query(sql, param); return result; } /** * 删除变更清单（form 变更新增部位页） * Tony Kang * @param {String} transaction - 队列 * @param {String} tid - 标段id * @param {Array} ids - id列表 * @param {String} column - id所属字段 * @param {String} mx_id - mx_id为空列删除 * @return {void} */ async deleteDataByRevise(transaction, tid, ids, column = 'gcl_id', mx_id = 'hello') { if (ids.length > 0) { const addSql = mx_id === '' ? ' AND (`mx_id` is NULL OR `mx_id` = "")' : ''; const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ')' + addSql + ' GROUP BY `cid`'; const params = [this.tableName, tid]; const changes = await transaction.query(sql, params); if (changes.length > 0) { const delData = { tid, }; delData[column] = ids; await transaction.delete(this.tableName, delData); for (const c of changes) { // 重算选了此清单的变更令已变更金额 await this.reCalcTp(transaction, c.cid); } } } } /** * 修改变更清单（form 变更新增部位页台账子节点清单编号编辑） * Tony Kang * @param {String} transaction - 队列 * @param {String} tid - 标段id * @param {Array} datas - 更新列表 * @param {String} column - id所属字段 * @return {void} */ async updateDataByReviseLedger(transaction, tid, datas, column = 'gcl_id') { if (datas.length > 0) { const ids = this._.map(datas, 'id'); const sql = 'SELECT ' + column + ' FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY ' + column; const params = [this.tableName, tid]; const changeAuditLists = await transaction.query(sql, params); if (changeAuditLists.length > 0) { const updateArr = []; const cidList = []; for (const ca of changeAuditLists) { const d = this._.find(datas, { id: ca[column] }); if (d.id) { const changePosNum = await transaction.count(this.ctx.service.changePos.tableName, { lid: d.id }); const updateCol = {}; if (column === 'gcl_id' && d.b_code !== undefined) updateCol.code = d.b_code; if (column === 'gcl_id' && d.sgfh_qty !== undefined && changePosNum === 0) updateCol.oamount = d.sgfh_qty ? d.sgfh_qty : 0; if (column === 'gcl_id' && d.unit_price !== undefined) updateCol.unit_price = d.unit_price ? d.unit_price : 0; if (column === 'gcl_id' && d.unit !== undefined) updateCol.unit = d.unit; if (column === 'gcl_id' && d.name !== undefined) updateCol.name = d.name; if (d.code !== undefined && d.b_code === null) { // 清单升级成了项目节，故删除变更已有的此清单，并找出需要重新计算的变更令 const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`'; const params = [this.tableName, tid, d.id]; const changes = await transaction.query(sql, params); for (const c of changes) { if (this._.indexOf(cidList, c.cid) === -1) { cidList.push(c.cid); } } const delData = { tid, }; delData[column] = d.id; console.log(delData); await transaction.delete(this.tableName, delData); } else { const options = { row: {}, where: {}, }; options.row = updateCol; options.where[column] = d.id; if (!this._.isEmpty(options.row)) updateArr.push(options); if (updateCol.unit !== undefined || updateCol.unit_price !== undefined) { const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`'; const params = [this.tableName, tid, d.id]; const changes = await transaction.query(sql, params); for (const c of changes) { if (this._.indexOf(cidList, c.cid) === -1) { cidList.push(c.cid); } } } } } } if (updateArr.length > 0) await transaction.updateRows(this.tableName, updateArr); if (cidList.length > 0) { for (const c of cidList) { await this.reCalcTp(transaction, c); } } } // 针对项目节更新可能对清单影响判断,修正变更清单项目节编号，细目，单位工程，分部分项工程数据 for (const data of datas) { const select = await transaction.get(this.ctx.service.changeLedger.tableName, { id: data.id }); if (select && select.is_leaf === 0) { const lists = await this.ctx.service.changeLedger.getDataByFullPath(this.ctx.service.changeLedger.tableName, tid, select.full_path + '%', transaction); const childLists = this._.filter(lists, { level: select.level + 1 }); // 细目or项目节编号更新 if (childLists.length > 0) { const d = { xmj_code: '', xmj_jldy: '' }; if (select.code !== null) { d.xmj_code = select.code; d.xmj_jldy = select.name; } else { // 再找出上一个项目节节点并更新 this.newBills = false; const parents = await this.ctx.service.changeLedger.getDataByKid(tid, select.ledger_pid); console.log('hello :', parents); d.xmj_code = parents.code; d.xmj_jldy = parents.name; } for (const cl of childLists) { await transaction.update(this.tableName, { xmj_code: d.xmj_code, xmj_jldy: d.xmj_jldy }, { where: { tid, gcl_id: cl.id } }); } } if (select.code !== null && data.name !== undefined) { // 名称修改则可能影响几个数据 const secondChildLists = this._.filter(lists, { level: select.level + 2 }); // 分项工程更新 const thirdChildLists = this._.filter(lists, { level: select.level + 3 }); // 分部工程更新 const fourthChildLists = this._.filter(lists, { level: select.level + 4 }); // 单位工程更新 if (secondChildLists.length > 0) { for (const sl of secondChildLists) { await transaction.update(this.tableName, { xmj_fxgc: select.name }, { where: { tid, gcl_id: sl.id } }); } } if (thirdChildLists.length > 0) { for (const tl of thirdChildLists) { await transaction.update(this.tableName, { xmj_fbgc: select.name }, { where: { tid, gcl_id: tl.id } }); } } if (fourthChildLists.length > 0 && select.level === 2) { for (const fl of fourthChildLists) { await transaction.update(this.tableName, { xmj_dwgc: select.name }, { where: { tid, gcl_id: fl.id } }); } } } } } } } /** * 修改变更清单（form 变更新增部位页台账节点清单编号升降级） * Tony Kang * @param {String} transaction - 队列 * @param {String} tid - 标段id * @param {Array} datas - 更新列表 * @param {String} column - id所属字段 * @return {void} */ async updateDataByReviseLedgerUpDownLevel(transaction, tid, datas, column = 'gcl_id') { if (datas.length > 0) { console.log(datas); // const ids = this._.map(datas, 'id'); // const sql = 'SELECT ' + column + ' FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY ' + column; // const params = [this.tableName, tid]; // const changeAuditLists = await transaction.query(sql, params); // if (changeAuditLists.length > 0) { // const updateArr = []; // const cidList = []; // for (const ca of changeAuditLists) { // const d = this._.find(datas, { id: ca[column] }); // console.log(d); // if (d.id) { // const changePosNum = await transaction.count(this.ctx.service.changePos.tableName, { lid: d.id }); // const updateCol = {}; // if (column === 'gcl_id' && d.b_code !== undefined) updateCol.code = d.b_code; // if (column === 'gcl_id' && d.sgfh_qty !== undefined && changePosNum === 0) updateCol.oamount = d.sgfh_qty ? d.sgfh_qty : 0; // if (column === 'gcl_id' && d.unit_price !== undefined) updateCol.unit_price = d.unit_price ? d.unit_price : 0; // if (column === 'gcl_id' && d.unit !== undefined) updateCol.unit = d.unit; // if (column === 'gcl_id' && d.name !== undefined) updateCol.name = d.name; // if (d.code !== undefined && d.b_code === null) { // // 清单升级成了项目节，故删除变更已有的此清单，并找出需要重新计算的变更令 // const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`'; // const params = [this.tableName, tid, d.id]; // const changes = await transaction.query(sql, params); // for (const c of changes) { // if (this._.indexOf(cidList, c.cid) === -1) { // cidList.push(c.cid); // } // } // const delData = { // tid, // }; // delData[column] = d.id; // console.log(delData); // await transaction.delete(this.tableName, delData); // } else { // const options = { // row: {}, // where: {}, // }; // options.row = updateCol; // options.where[column] = d.id; // if (!this._.isEmpty(options.row)) updateArr.push(options); // if (updateCol.unit !== undefined || updateCol.unit_price !== undefined) { // const sql = 'SELECT `cid` FROM ?? WHERE `tid` = ? AND ' + column + ' = ? GROUP BY `cid`'; // const params = [this.tableName, tid, d.id]; // const changes = await transaction.query(sql, params); // for (const c of changes) { // if (this._.indexOf(cidList, c.cid) === -1) { // cidList.push(c.cid); // } // } // } // } // } // } // console.log(updateArr, cidList); // if (updateArr.length > 0) await transaction.updateRows(this.tableName, updateArr); // if (cidList.length > 0) { // for (const c of cidList) { // await this.reCalcTp(transaction, c); // } // } // } // 针对项目节更新可能对清单影响判断,修正变更清单项目节编号，细目，单位工程，分部分项工程数据 // for (const data of datas) { // const select = await transaction.get(this.ctx.service.changeLedger.tableName, { id: data.id }); // console.log(select); // if (select && select.is_leaf === 0) { // const lists = await this.ctx.service.changeLedger.getDataByFullPath(this.ctx.service.changeLedger.tableName, tid, select.full_path + '%', transaction); // const childLists = this._.filter(lists, { level: select.level + 1 }); // 细目or项目节编号更新 // if (childLists.length > 0) { // const d = { xmj_code: '', xmj_jldy: '' }; // if (select.code !== null) { // d.xmj_code = select.code; // d.xmj_jldy = select.name; // } else { // // 再找出上一个项目节节点并更新 // this.newBills = false; // const parents = await this.ctx.service.changeLedger.getDataByKid(tid, select.ledger_pid); // console.log('hello :', parents); // d.xmj_code = parents.code; // d.xmj_jldy = parents.name; // } // for (const cl of childLists) { // await transaction.update(this.tableName, { xmj_code: d.xmj_code, xmj_jldy: d.xmj_jldy }, { where: { tid, gcl_id: cl.id } }); // } // } // if (select.code !== null && data.name !== undefined) { // 名称修改则可能影响几个数据 // const secondChildLists = this._.filter(lists, { level: select.level + 2 }); // 分项工程更新 // const thirdChildLists = this._.filter(lists, { level: select.level + 3 }); // 分部工程更新 // const fourthChildLists = this._.filter(lists, { level: select.level + 4 }); // 单位工程更新 // if (secondChildLists.length > 0) { // for (const sl of secondChildLists) { // await transaction.update(this.tableName, { xmj_fxgc: select.name }, { where: { tid, gcl_id: sl.id } }); // } // } // if (thirdChildLists.length > 0) { // for (const tl of thirdChildLists) { // await transaction.update(this.tableName, { xmj_fbgc: select.name }, { where: { tid, gcl_id: tl.id } }); // } // } // if (fourthChildLists.length > 0) { // for (const fl of fourthChildLists) { // await transaction.update(this.tableName, { xmj_dwgc: select.name }, { where: { tid, gcl_id: fl.id } }); // } // } // } // } // } } } /** * 修改变更清单（form 变更新增部位页计量单元编辑） * Tony Kang * @param {String} transaction - 队列 * @param {String} tid - 标段id * @param {Array} datas - 更新列表 * @param {String} column - id所属字段 * @return {void} */ async updateDataByRevisePos(transaction, tid, datas, column = 'mx_id') { if (datas.length > 0) { const ids = this._.map(datas, 'id'); const sql = 'SELECT ' + column + ' FROM ?? WHERE `tid` = ? AND ' + column + ' in (' + this.ctx.helper.getInArrStrSqlFilter(ids) + ') GROUP BY ' + column; const params = [this.tableName, tid]; const changeAuditLists = await transaction.query(sql, params); if (changeAuditLists.length > 0) { const updateArr = []; for (const ca of changeAuditLists) { const d = this._.find(datas, { id: ca[column] }); if (d.id) { const updateCol = {}; if (column === 'mx_id' && d.name !== undefined) updateCol.bwmx = d.name; if (column === 'mx_id' && d.sgfh_qty !== undefined) updateCol.oamount = d.sgfh_qty ? d.sgfh_qty : 0; if (column === 'mx_id' && d.sgfh_qty === undefined && d.sgfh_expr === '') updateCol.oamount = 0; const options = { row: {}, where: {}, }; options.row = updateCol; options.where[column] = d.id; // if (!this._.isEmpty(updateCol)) await transaction.update(this.tableName, updateCol, options); if (!this._.isEmpty(options.row)) updateArr.push(options); } } console.log(updateArr); if (updateArr.length > 0) await transaction.updateRows(this.tableName, updateArr); } } } /** * 重算变更令总金额(变更新增部位设置时使用) * @param {String} transaction - 队列 * @param {String} cid - 变更令id */ async reCalcTp(transaction, cid) { const change = await transaction.get(this.ctx.service.change.tableName, { cid }); let count = ''; if (change.status === audit.flow.status.uncheck || change.status === audit.flow.status.back || change.status === audit.flow.status.revise) { count = '`camount`'; } else if (change.status === audit.flow.status.checking || change.status === audit.flow.status.backnew) { count = '`spamount`'; } if (count) { const sql = 'SELECT `unit_price`, ' + count + ' as `count` FROM ?? WHERE `cid` = ?'; const params = [this.tableName, change.cid]; const caLists = await transaction.query(sql, params); let tp = 0; const tpUnit = change.tp_decimal ? change.tp_decimal : this.ctx.tender.info.decimal.tp; for (const ca of caLists) { const catp = this.ctx.helper.round(this.ctx.helper.mul(ca.unit_price, ca.count), tpUnit); tp = this.ctx.helper.add(tp, catp); } console.log(tp); if (tp !== change.total_price) { const options = { where: { cid: change.cid, }, }; const change_update = { total_price: tp, }; await transaction.update(this.ctx.service.change.tableName, change_update, options); } } } } return ChangeAuditList; };