'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, order_by = this.ctx.change.order_by) { if (order_by) { return await this.getAllDataByCondition({ where: { cid }, orders: [['order', 'asc']] }); } const sql = 'SELECT * FROM ?? WHERE `cid` = ? ORDER BY `lid` = "0", `id` asc'; const sqlParam = [this.tableName, cid]; const result = await this.db.query(sql, sqlParam); return this._.orderBy(result, ['order'], ['asc']); } /** * 移除清单时，同步其后清单order * @param transaction - 事务 * @param {Number} cid - 变更cid * @param {Number} order - order之后的 * @return {Promise<*>} * @private */ async _syncOrder(transaction, cid, order, selfOperate = '-', num = 1) { this.initSqlBuilder(); this.sqlBuilder.setAndWhere('cid', { value: this.db.escape(cid), operate: '=', }); this.sqlBuilder.setAndWhere('order', { value: order, operate: '>=', }); this.sqlBuilder.setUpdateData('order', { value: num, selfOperate, }); const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update'); const data = await transaction.query(sql, sqlParam); return data; } /** * 添加空白变更清单 * @return {void} */ async add(data, delimit = 100) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } const transaction = await this.db.beginTransaction(); try { let order = null; if (this.ctx.change.order_by) { if (data) { order = parseInt(data) + 1; // order以下的清单+1 await this._syncOrder(transaction, this.ctx.change.cid, order, '+'); } else { order = await this.count({ cid: this.ctx.change.cid }); order = order ? order + 1 : 1; } } const insertData = { tid: this.ctx.tender.id, cid: this.ctx.change.cid, lid: '0', code: '', name: '', bwmx: '', unit: '', unit_price: null, oamount: 0, oamount2: 0, camount: 0, camount_expr: '', samount: '', detail: '', spamount: 0, xmj_code: null, xmj_jldy: null, xmj_dwgc: null, xmj_fbgc: null, xmj_fxgc: null, gcl_id: '', mx_id: '', order, is_valuation: 1, delimit, }; // 新增工料 const result = await transaction.insert(this.tableName, insertData); if (result.affectedRows === 0) { throw '新增空白清单数据失败'; } await transaction.commit(); return await this.getDataById(result.insertId); } catch (err) { await transaction.rollback(); throw err; } } /** * 添加台账清单（从新增部位页新增） * @return {void} */ async adds(datas) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } const transaction = await this.db.beginTransaction(); try { let order = null; if (this.ctx.change.order_by) { const data = this.ctx.change.order_site ? await this.getDataById(this.ctx.change.order_site) : null; if (data) { order = parseInt(data.order) + 1; // order以下的清单+1 await this._syncOrder(transaction, this.ctx.change.cid, order, '+'); } else { order = await this.count({ cid: this.ctx.change.cid }); order = order ? order + 1 : 1; } } const insertData = []; for (const d of datas) { d.tid = this.ctx.tender.id; d.cid = this.ctx.change.cid; d.spamount = d.spamount || null; d.detail = d.detail || ''; d.samount = d.samount || ''; d.order = order ? order : null; order = order ? order + 1 : null; insertData.push(d); } // 新增工料 const result = await transaction.insert(this.tableName, insertData); if (result.affectedRows === 0) { throw '添加清单数据失败'; } await transaction.commit(); return true; } catch (err) { await transaction.rollback(); throw err; } } /** * 批量添加空白变更清单 * @return {void} */ async batchAdd(data, delimit = 100) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } const transaction = await this.db.beginTransaction(); try { const num = data.num ? parseInt(data.num) : 0; if (num < 1 || num > 100) { throw '批量添加的空白清单数目不能小于1或大于100'; } let order = null; if (this.ctx.change.order_by) { if (data) { order = parseInt(data.postData) + 1; // order以下的清单+1 await this._syncOrder(transaction, this.ctx.change.cid, order, '+', num); } else { order = await this.count({ cid: this.ctx.change.cid }); order = order ? order + 1 : 1; } } 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, oamount2: 0, camount: 0, camount_expr: '', samount: '', detail: '', spamount: 0, xmj_code: null, xmj_jldy: null, xmj_dwgc: null, xmj_fbgc: null, xmj_fxgc: null, gcl_id: '', mx_id: '', order: order ? order + i : null, is_valuation: 1, delimit, }; insertArray.push(insertData); } // 新增工料 const result = await transaction.insert(this.tableName, insertArray); if (result.affectedRows !== num) { throw '批量添加空白清单数据失败'; } await transaction.commit(); // // 获取刚批量添加的所有list // for (let j = 0; j < num; j++) { // insertArray[j].id = result.insertId + j; // } // return insertArray; return await this.getList(this.ctx.change.cid); } catch (err) { await transaction.rollback(); throw err; } } /** * 删除变更清单 * @param {int} id 清单id * @return {void} */ async del(data) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } const transaction = await this.db.beginTransaction(); try { // 判断是否可删 await transaction.delete(this.tableName, { id: data.ids }); // // order以下的清单-1 if (this.ctx.change.order_by) { await this._syncOrder(transaction, this.ctx.change.cid, data.postData, '-', data.ids.length); } // 重新算变更令总额 await this.calcCamountSum(transaction); await transaction.commit(); return true; } catch (err) { await transaction.rollback(); throw err; } } async dels(data) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } const transaction = await this.db.beginTransaction(); try { // 判断是否存在调用，存在则报错 const delList = await this.getAllDataByCondition({ where: { id: data.ids } }); const sql1 = 'SELECT a.* FROM ?? as b LEFT JOIN ?? as a ON b.cbid = a.id WHERE b.cid = ? AND b.id in (' + this.ctx.helper.getInArrStrSqlFilter(data.ids) + ') GROUP BY b.cbid'; const sqlParam1 = [this.ctx.service.stageChange.tableName, this.tableName, this.ctx.change.cid]; const usedList = await transaction.query(sql1, sqlParam1); if (usedList.length > 0) { throw '清单已被调用，不可删除'; } await transaction.delete(this.tableName, { id: data.ids }); // // order以下的清单-1 if (this.ctx.change.order_by) { const postData = this.ctx.change.order_site ? await this.getDataById(this.ctx.change.order_site) : null; await this._syncOrder(transaction, this.ctx.change.cid, (postData ? postData.order : null), '-', data.ids.length); } // 重新算变更令总额 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) { 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, data = null, order_by = this.ctx.change.order_by) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } // 判断是否可修改 // 判断t_type是否为费用 const transaction = await this.db.beginTransaction(); try { let usedList = []; let order = null; if (order_by) { if (data) { order = parseInt(data) + 1; // order以下的清单+1 await this._syncOrder(transaction, this.ctx.change.cid, order, '+', datas.length); } else { order = await this.count({ cid: this.ctx.change.cid }); order = order ? order + 1 : 1; } } else { 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]; 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 = ''; data.order = order ? order : null; order = order ? order + 1 : null; insertDatas.push(data); } if (insertDatas.length > 0) await this.insertBigDatas(transaction, insertDatas); await this.calcCamountSum(transaction); if (!order_by) { // 更新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 findFilter = { lid: used.lid, gcl_id: used.gcl_id, bwmx: used.bwmx }; if (used.mx_id) findFilter.mx_id = used.mx_id; const newone = this._.find(newList, findFilter); 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, updateTpDecimal = false) { // 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, is_valuation FROM ?? WHERE cid = ?'; const sqlParam = [this.tableName, this.ctx.change.cid]; const changeList = await transaction.query(sql, sqlParam); let total_price = 0; let positive_tp = 0; let negative_tp = 0; let valuation_tp = 0; let unvaluation_tp = 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) { const price = this.ctx.helper.mul(cl.unit_price, cl.spamount, tp_decimal); total_price = this.ctx.helper.accAdd(total_price, price); if (price >= 0) { positive_tp = this.ctx.helper.accAdd(positive_tp, price); } else { negative_tp = this.ctx.helper.accAdd(negative_tp, price); } if (cl.is_valuation) { valuation_tp = this.ctx.helper.accAdd(valuation_tp, price); } else { unvaluation_tp = this.ctx.helper.accAdd(unvaluation_tp, price); } } const updateData = { total_price, positive_tp, negative_tp, valuation_tp, unvaluation_tp, }; if (updateTpDecimal) { updateData.tp_decimal = tp_decimal; } 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, onlyChecked) { const sql = 'SELECT cb.*' + ' FROM ' + this.tableName + ' cb' + ' LEFT JOIN ' + this.ctx.service.change.tableName + ' c ON cb.cid = c.cid' + ' WHERE c.tid = ? ' + (onlyChecked ? '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) updateCol.code = d.b_code; if (column === 'gcl_id' && d.quantity !== undefined && changePosNum === 0) updateCol.oamount = d.quantity ? d.quantity : 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.b_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; 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); 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.quantity !== undefined) updateCol.oamount = d.quantity ? d.quantity : 0; if (column === 'mx_id' && d.quantity === undefined && ((d.sgfh_expr && d.sgfh_expr === '') || (d.sjcl_expr && d.sjcl_expr === '') || (d.qtcl_expr && d.qtcl_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); } } 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); } } } async updateToLedger(transaction, tid, cid) { // 找出本条变更属于新增部位的数据 const allList = await transaction.select(this.tableName, { where: { tid, cid } }); const result = []; const result2 = []; for (const l of allList) { const changeLedgerInfo = await transaction.get(this.ctx.service.changeLedger.tableName, { id: l.gcl_id }); if (changeLedgerInfo && this._.findIndex(result, { id: l.gcl_id }) === -1) { result.push(changeLedgerInfo); } const changePosInfo = await transaction.get(this.ctx.service.changePos.tableName, { id: l.mx_id }); if (changePosInfo) { result2.push(changePosInfo); } } // const sql = 'SELECT a.* FROM ?? a LEFT JOIN ?? b ON a.id = b.gcl_id WHERE b.tid = ? AND b.cid = ? GROUP BY a.id'; // const sqlParam = [this.ctx.service.changeLedger.tableName, this.tableName, tid, cid]; // const result = await transaction.query(sql, sqlParam); // const sql2 = 'SELECT a.* FROM ?? a LEFT JOIN ?? b ON a.id = b.mx_id WHERE b.tid = ? AND b.cid = ?'; // const sqlParam2 = [this.ctx.service.changePos.tableName, this.tableName, tid, cid]; // const result2 = await transaction.query(sql2, sqlParam2); if (result.length > 0 || result2.length > 0) { const changeLedgerGclIdList = this._.map(result, 'id'); const changeLedgerIdList = this._.uniq(this._.map(result, 'ledger_pid'));// 父节点集合 const needUpdateLedgerList = [];// 找出需要更新的原台账清单的id const needUpdateChangeLedgerList = [];// 找出需要更新的新台账清单的id const tpDecimal = this.ctx.tender.info.decimal.tp; // 要更新的ledger节点,数量及总数 for (const data of result2) { if (this._.indexOf(changeLedgerGclIdList, data.lid) === -1) { const info = this._.find(needUpdateLedgerList, { id: data.lid }); if (info) { info.sgfh_qty = this.ctx.helper.add(info.sgfh_qty, data.sgfh_qty); info.sjcl_qty = this.ctx.helper.add(info.sjcl_qty, data.sjcl_qty); info.qtcl_qty = this.ctx.helper.add(info.qtcl_qty, data.qtcl_qty); info.quantity = this.ctx.helper.add(info.quantity, data.quantity); } else { needUpdateLedgerList.push({ id: data.lid, sgfh_qty: data.sgfh_qty, sjcl_qty: data.sjcl_qty, qtcl_qty: data.qtcl_qty, quantity: data.quantity }); } } else { const info = this._.find(needUpdateChangeLedgerList, { id: data.lid }); if (info) { info.sgfh_qty = this.ctx.helper.add(info.sgfh_qty, data.sgfh_qty); info.sjcl_qty = this.ctx.helper.add(info.sjcl_qty, data.sjcl_qty); info.qtcl_qty = this.ctx.helper.add(info.qtcl_qty, data.qtcl_qty); info.quantity = this.ctx.helper.add(info.quantity, data.quantity); } else { needUpdateChangeLedgerList.push({ id: data.lid, sgfh_qty: data.sgfh_qty, sjcl_qty: data.sjcl_qty, qtcl_qty: data.qtcl_qty, quantity: data.quantity }); } } } // 更新到result上 if (needUpdateChangeLedgerList.length > 0) { for (const nucl of needUpdateChangeLedgerList) { const now = this._.find(result, { id: nucl.id }); now.sgfh_qty = nucl.sgfh_qty; now.sjcl_qty = nucl.sjcl_qty; now.qtcl_qty = nucl.qtcl_qty; now.quantity = nucl.quantity; now.sgfh_tp = this.ctx.helper.mul(now.sgfh_qty, now.unit_price, tpDecimal); now.sjcl_tp = this.ctx.helper.mul(now.sjcl_qty, now.unit_price, tpDecimal); now.qtcl_tp = this.ctx.helper.mul(now.qtcl_qty, now.unit_price, tpDecimal); now.total_price = this.ctx.helper.mul(now.quantity, now.unit_price, tpDecimal); } } // 更新到ledger上 if (needUpdateLedgerList.length > 0) { for (const nul of needUpdateLedgerList) { const ledgerInfo = await this.ctx.service.ledger.getDataById(nul.id); ledgerInfo.sgfh_qty = this.ctx.helper.add(ledgerInfo.sgfh_qty, nul.sgfh_qty); ledgerInfo.sjcl_qty = this.ctx.helper.add(ledgerInfo.sjcl_qty, nul.sjcl_qty); ledgerInfo.qtcl_qty = this.ctx.helper.add(ledgerInfo.qtcl_qty, nul.qtcl_qty); ledgerInfo.quantity = this.ctx.helper.add(ledgerInfo.quantity, nul.quantity); ledgerInfo.sgfh_tp = this.ctx.helper.mul(ledgerInfo.sgfh_qty, ledgerInfo.unit_price, tpDecimal); ledgerInfo.sjcl_tp = this.ctx.helper.mul(ledgerInfo.sjcl_qty, ledgerInfo.unit_price, tpDecimal); ledgerInfo.qtcl_tp = this.ctx.helper.mul(ledgerInfo.qtcl_qty, ledgerInfo.unit_price, tpDecimal); ledgerInfo.total_price = this.ctx.helper.mul(ledgerInfo.quantity, ledgerInfo.unit_price, tpDecimal); await transaction.update(this.ctx.service.ledger.tableName, ledgerInfo); } } // 找出所有新增的父节点并插入到result中 for (const r of changeLedgerIdList) { await this._findParents(transaction, tid, r, result); } // 插入到计量单元表,并删除变更的计量单元数据, 插入清单表，并删除变更的清单表 await this._insertByChangeRevise(transaction, tid, cid, result, result2); // 更新标段总金额 const sumSql = 'SELECT Sum(total_price) As total_price, Sum(deal_tp) As deal_tp' + ' FROM ' + this.ctx.service.ledger.tableName + this.ctx.helper.whereSql({ tender_id: tid }); const sum = await transaction.queryOne(sumSql); await transaction.update(this.ctx.service.tender.tableName, { id: tid, total_price: sum.total_price, deal_tp: sum.deal_tp, }); // 清除修订及台账的maxLid缓存，防止树结构混乱 await this.ctx.service.reviseBills._removeCacheMaxLid(tid); await this.ctx.service.ledger._removeCacheMaxLid(tid); } } async _findParents(transaction, tid, id, result) { const info = await transaction.get(this.ctx.service.changeLedger.tableName, { tender_id: tid, ledger_id: id }); if (info && this._.findIndex(result, { ledger_id: info.ledger_id }) === -1) { result.push(info); await this._findParents(transaction, tid, info.ledger_pid, result); } else { return; } } async _insertByChangeRevise(transaction, tid, cid, ledgerList, posList) { if (ledgerList.length > 0) { const insertLedgerArr = []; for (const l of ledgerList) { const insertL = [ l.id, l.code, l.b_code, l.name, l.unit, l.source, l.remark, l.ledger_id, l.ledger_pid, l.level, l.order, l.full_path, l.is_leaf, l.quantity, l.total_price, l.unit_price, l.drawing_code, l.memo, l.dgn_qty1, l.dgn_qty2, l.deal_qty, l.deal_tp, l.sgfh_qty, l.sgfh_tp, l.sjcl_qty, l.sjcl_tp, l.qtcl_qty, l.qtcl_tp, l.node_type, l.crid, l.ccid, l.tender_id, l.sgfh_expr, l.sjcl_expr, l.qtcl_expr, l.check_calc, l.ex_memo1, l.ex_memo2, l.ex_memo3, ]; insertLedgerArr.push('(' + this.ctx.helper.getInArrStrSqlFilter(insertL) + ')'); await transaction.delete(this.ctx.service.changeLedger.tableName, { id: l.id }); // 日志添加 await transaction.insert(this.ctx.service.changeReviseLog.tableName, { tid, cid, lid: l.id, name: l.name ? l.name : (l.code ? l.code : ''), create_time: new Date() }); } const bSql = 'Insert Into ' + this.ctx.service.ledger.tableName + ' (id, code, b_code, name, unit, source, remark, ledger_id, ledger_pid, level, `order`, full_path, is_leaf,' + ' quantity, total_price, unit_price, drawing_code, memo, dgn_qty1, dgn_qty2, deal_qty, deal_tp,' + ' sgfh_qty, sgfh_tp, sjcl_qty, sjcl_tp, qtcl_qty, qtcl_tp, node_type, crid, ccid, tender_id,' + ' sgfh_expr, sjcl_expr, qtcl_expr, check_calc,' + ' ex_memo1, ex_memo2, ex_memo3) VALUES ' + insertLedgerArr.join(',') + ';'; await transaction.query(bSql, []); } if (posList.length > 0) { const insertPosArr = []; for (const p of posList) { const insertp = [ p.id, p.tid, p.lid, p.name, p.drawing_code, p.quantity, p.add_stage, p.add_stage_order, p.add_times, p.add_user, p.sgfh_qty, p.sjcl_qty, p.qtcl_qty, p.crid, p.ccid, p.porder, p.position, p.sgfh_expr, p.sjcl_expr, p.qtcl_expr, p.real_qty, p.ex_memo1, p.ex_memo2, p.ex_memo3, ]; insertPosArr.push('(' + this.ctx.helper.getInArrStrSqlFilter(insertp) + ')'); await transaction.delete(this.ctx.service.changePos.tableName, { id: p.id }); // 日志添加 await transaction.insert(this.ctx.service.changeReviseLog.tableName, { tid, cid, pid: p.id, name: p.name, create_time: new Date() }); } const pSql = 'Insert Into ' + this.ctx.service.pos.tableName + ' (id, tid, lid, name, drawing_code, quantity, add_stage, add_stage_order, add_times, add_user,' + ' sgfh_qty, sjcl_qty, qtcl_qty, crid, ccid, porder, position, ' + ' sgfh_expr, sjcl_expr, qtcl_expr, real_qty,' + ' ex_memo1, ex_memo2, ex_memo3) VALUES ' + insertPosArr.join(',') + ';'; await transaction.query(pSql, []); } } async checkedChangeBills(tid) { const DefaultDecimal = this.ctx.tender.info.decimal.tp; const sql = 'SELECT cal.*, c.tp_decimal FROM ' + this.ctx.service.changeAuditList.tableName + ' cal LEFT JOIN ' + this.ctx.service.change.tableName + ' c on cal.cid = c.cid where c.tid = ? and c.valid and c.status = ?'; const changeBills = await this.db.query(sql, [tid, audit.flow.status.checked]); changeBills.forEach(x => { x.tp_decimal = x.tp_decimal !== 0 ? x.tp_decimal : DefaultDecimal }); return changeBills; } /** * 交换两个清单的顺序 * @param {Number} id1 - 工料1的id * @param {Number} id2 - 工料2的id * @returns {Promise} */ async changeOrder(datas) { if (!this.ctx.tender || !this.ctx.change) { throw '数据错误'; } // const bill1 = await this.getDataByCondition({ tid: this.ctx.tender.id, id: id1 }); // const bill2 = await this.getDataByCondition({ tid: this.ctx.tender.id, id: id2 }); // if (!bill1 || !bill2) { // throw '数据错误'; // } const transaction = await this.db.beginTransaction(); try { // const order = bill1.order; // bill1.order = bill2.order; // bill2.order = order; // await transaction.update(this.tableName, { id: bill1.id, order: bill1.order }); // await transaction.update(this.tableName, { id: bill2.id, order: bill2.order }); await transaction.updateRows(this.tableName, datas); await transaction.commit(); return true; } catch (err) { await transaction.rollback(); throw err; } } async setAllValuation(cid, ids, is_valuation) { return await this.db.update(this.tableName, { is_valuation }, { where: { cid, id: ids, }, }); } async getBillsSum(tid) { const sql = 'SELECT gcl_id, Sum(qc_qty) AS qc_qty, Sum(qc_tp) AS qc_tp, Sum(qc_minus_qty) AS qc_minus_qty' + ' FROM(' + ' SELECT cal.gcl_id, Sum(cal.checked_amount) AS qc_qty, Sum(cal.checked_price) AS qc_tp, 0 As qc_minus_qty' + ` FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` + ' WHERE c.tid = ? AND c.valid AND c.status = ? AND cal.is_valuation' + ' GROUP BY cal.gcl_id' + ' UNION ALL ' + ' SELECT cal.gcl_id, 0 As qc_qty, 0 As qc_tp, Sum(cal.checked_amount) AS qc_minus_qty' + ` FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` + ' WHERE c.tid = ? AND c.valid AND c.status = ? AND not cal.is_valuation' + ' GROUP BY cal.gcl_id) As TEMP' + ' GROUP BY gcl_id'; return await this.db.query(sql, [tid, audit.flow.status.checked, tid, audit.flow.status.checked]); } async getPosSum(tid) { const sql = 'SELECT mx_id, Sum(qc_qty) AS qc_qty, Sum(qc_tp) AS qc_tp, Sum(qc_minus_qty) AS qc_minus_qty' + ' FROM(' + ' SELECT cal.mx_id, Sum(cal.checked_amount) AS qc_qty, Sum(cal.checked_price) AS qc_tp, 0 As qc_minus_qty' + ` FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` + ' WHERE c.tid = ? AND c.valid AND c.status = ? AND cal.is_valuation' + ' GROUP BY cal.mx_id' + ' UNION ALL ' + ' SELECT cal.mx_id, 0 As qc_qty, 0 As qc_tp, Sum(cal.checked_amount) AS qc_minus_qty' + ` FROM ${this.tableName} cal LEFT JOIN ${this.ctx.service.change.tableName} c ON cal.cid = c.cid` + ' WHERE c.tid = ? AND c.valid AND c.status = ? AND not cal.is_valuation' + ' GROUP BY cal.mx_id) As TEMP' + ' GROUP BY mx_id'; return await this.db.query(sql, [tid, audit.flow.status.checked, tid, audit.flow.status.checked]); } } return ChangeAuditList; };