'use strict'; /** * 提供基础操作: * 1. 增删改查 * 2. 粘贴整块 * 3. 简易导入 * * @author Mai * @date * @version */ const Service = require('./base_service'); // sql拼装器 const SqlBuilder = require('../lib/sql_builder'); class TreeService extends Service { /** * 构造函数 * * @param {Object} ctx - egg全局context * @param {Object} setting - 树结构设置 * e.g.: { * mid: 'tender_id', 分块id(例如tender_id, rid, list_id) * kid: 'ledger_id', 分块内的树结构id * pid: 'ledger_pid', 父节点id * order: 'order', * level: 'level', * fullPath: 'full_path', * isLeaf: 'is_leaf', * keyPre: 'revise_bills_maxLid:' * } * @return {void} */ constructor(ctx, setting) { super(ctx); this.tableName = setting.tableName; this.setting = setting; // 以下字段仅可通过树结构操作改变,不可直接通过update方式从接口提交,发现时过滤 this.readOnlyFields = ['id']; this.readOnlyFields.push(this.setting.mid); this.readOnlyFields.push(this.setting.kid); this.readOnlyFields.push(this.setting.pid); this.readOnlyFields.push(this.setting.order); this.readOnlyFields.push(this.setting.level); this.readOnlyFields.push(this.setting.fullPath); this.readOnlyFields.push(this.setting.isLeaf); } getCondition (condition) { const result = {}; if (condition.mid) result[this.setting.mid] = condition.mid; if (condition.kid) result[this.setting.kid] = condition.kid; if (condition.pid) result[this.setting.pid] = condition.pid; if (condition[this.setting.order]) result[this.setting.order] = condition[this.setting.order]; if (condition.level) result[this.setting.level] = condition.level; if (condition.fullPath) result[this.setting.fullPath] = condition.fullPath; if (condition.isLeaf) result[this.setting.isLeaf] = condition.isLeaf; return result; } /** * 获取 修订 清单数据 * @param {Number} mid - masterId * @returns {Promise} */ async getData(mid) { return await this.db.select(this.tableName, { where: this.getCondition({mid: mid}) }); } /** * 获取节点数据 * @param {Number} mid - masterId * @param {Number} id * @returns {Promise} */ async getDataByLid (mid, kid) { return await this.db.get(this.tableName, this.getCondition({ mid: mid, kid: kid, })); } /** * 获取节点数据 * @param id * @returns {Promise} */ async getDataById(id) { if (id instanceof Array) { return await this.db.select(this.tableName, { where: {id: id} }); } else { return await this.db.get(this.tableName, { id: id }); } } /** * 获取最末的子节点 * @param {Number} mid - masterId * @param {Number} pid - 父节点id * @return {Object} */ async getLastChildData(mid, pid) { this.initSqlBuilder(); this.sqlBuilder.setAndWhere(this.setting.mid, { value: mid, operate: '=', }); this.sqlBuilder.setAndWhere(this.setting.pid, { value: pid, operate: '=', }); this.sqlBuilder.orderBy = [['order', 'DESC']]; const [sql, sqlParam] = this.sqlBuilder.build(this.tableName); const resultData = await this.db.queryOne(sql, sqlParam); return resultData; } /** * 根据 父节点id 和 节点排序order 获取数据 * * @param {Number} mid - master id * @param {Number} pid - 父节点id * @param {Number|Array} order - 排序 * @return {Object|Array} - 查询结果 */ async getDataByParentAndOrder(mid, pid, order) { const result = await this.db.select(this.tableName, { where: this.getCondition({mid: mid, pid: pid, order: order}) }); return order instanceof Array ? result : (result.length > 0 ? result[0] : null); } /** * 根据 父节点ID 和 节点排序order 获取全部后节点数据 * @param {Number} mid - master id * @param {Number} pid - 父节点id * @param {Number} order - 排序 * @return {Array} */ async getNextsData(mid, pid, order) { this.initSqlBuilder(); this.sqlBuilder.setAndWhere(this.setting.mid, { value: mid, operate: '=', }); this.sqlBuilder.setAndWhere(this.setting.pid, { value: pid, operate: '=', }); this.sqlBuilder.setAndWhere(this.setting.order, { value: order, operate: '>', }); const [sql, sqlParam] = this.sqlBuilder.build(this.tableName); const data = await this.db.query(sql, sqlParam); return data; } /** * 获取最大节点id * * @param {Number} mid - master id * @return {Number} * @private */ async _getMaxLid(mid) { const cacheKey = this.setting.keyPre + mid; let maxId = parseInt(await this.cache.get(cacheKey)); if (!maxId) { const sql = 'SELECT Max(??) As max_id FROM ?? Where ' + this.setting.mid + ' = ?'; const sqlParam = ['ledger_id', this.tableName, mid]; const queryResult = await this.db.queryOne(sql, sqlParam); maxId = queryResult.max_id || 0; this.cache.set(cacheKey, maxId, 'EX', this.ctx.app.config.cacheTime); } return maxId; } /** * 缓存最大节点id * * @param {Number} mid - master id * @param {Number} maxId - 当前最大节点id * @returns {Promise} * @private */ _cacheMaxLid(mid, maxId) { this.cache.set(this.setting.keyPre + mid , maxId, 'EX', this.ctx.app.config.cacheTime); } /** * 更新order * @param {Number} mid - master id * @param {Number} pid - 父节点id * @param {Number} order - 开始更新的order * @param {Number} incre - 更新的增量 * @returns {Promise<*>} * @private */ async _updateChildrenOrder(mid, pid, order, incre = 1) { this.initSqlBuilder(); this.sqlBuilder.setAndWhere(this.setting.mid, { value: mid, operate: '=', }); this.sqlBuilder.setAndWhere(this.setting.order, { value: order, operate: '>=', }); this.sqlBuilder.setAndWhere(this.setting.pid, { value: pid, operate: '=', }); this.sqlBuilder.setUpdateData(this.setting.order, { value: Math.abs(incre), selfOperate: incre > 0 ? '+' : '-', }); const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update'); const data = await this.transaction.query(sql, sqlParam); return data; } /** * 新增数据(新增为selectData的后项,该方法不可单独使用) * * @param {Number} mid - master-id * @param {Object} select - 选中节点的数据 * @param {Object} data - 新增节点的初始数据 * @return {Object} - 新增结果 * @private */ async _addNodeData(mid, select, data) { if (!data) { data = {}; } const maxId = await this._getMaxLid(mid); data.id = this.uuid.v4(); data[this.setting.kid] = maxId + 1; data[this.setting.pid] = select[this.setting.pid]; data[this.setting.mid] = mid; data[this.setting.level] = select[this.setting.level]; data[this.setting.order] = select[this.setting.order] + 1; data[this.setting.fullPath] = select[this.setting.fullPath].replace('.' + select[this.setting.kid], '.' + data[this.setting.kid]); data[this.setting.isLeaf] = true; const result = await this.transaction.insert(this.tableName, data); this._cacheMaxLid(mid, maxId + 1); return result; } /** * 新增节点 * @param {Number} mid - master id * @param {Number} kid - 清单节点id * @returns {Promise} */ async addNode(mid, kid, data) { if (!mid || !kid) return null; const select = await this.getDataByLid(mid, kid); if (!select) { throw '新增节点数据错误'; } this.transaction = await this.db.beginTransaction(); try { await this._updateChildrenOrder(mid, select[this.setting.pid], select[this.setting.order]+1); const newNode = await this._addNodeData(mid, select, data); if (newNode.affectedRows !== 1) { throw '新增节点数据额错误'; } await this.transaction.commit(); this.transaction = null; } catch (err) { await this.transaction.rollback(); this.transaction = null; throw err; } const createData = await this.getDataByParentAndOrder(mid, select[this.setting.pid], [select[this.setting.order] + 1]); const updateData = await this.getNextsData(mid, select[this.setting.pid], select[this.setting.order] + 1); return {create: createData, update: updateData}; } /** * 删除节点 * @param {Number} mid - master id * @param {Object} deleteNode - 删除节点数据 * @return {Promise<*>} * @private */ async _deleteNodeData(mid, deleteNode) { this.initSqlBuilder(); this.sqlBuilder.setAndWhere(this.setting.mid, { value: mid, operate: '=', }); this.sqlBuilder.setAndWhere(this.setting.fullPath, { value: this.db.escape(deleteNode[this.setting.fullPath] + '%'), operate: 'Like', }); const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'delete'); const result = await this.transaction.query(sql, sqlParam); return result; } /** * 删除选中节点及其子节点 * * @param {Number} mid - master id * @param {Number} kid - 选中节点id * @return {Array} - 被删除的数据 */ async deleteNode(mid, kid) { if ((mid <= 0) || (kid <= 0)) return []; const select = await this.getDataByNodeId(mid, kid); if (!select) throw '删除节点数据错误'; const parent = await this.getDataByNodeId(mid, select[this.setting.pid]); // 获取将要被删除的数据 const deleteData = await this.getDataByFullPath(mid, select[this.setting.fullPath] + '%'); if (deleteData.length === 0) throw '删除节点数据错误'; this.transaction = await this.db.beginTransaction(); try { // 删除 const operate = await this._deleteNodeData(mid, select); // 选中节点--父节点 只有一个子节点时,应升级is_leaf if (parent) { const count = await this.db.count(this.tableName, this.getCondition({mid: mid, pid: select[this.setting.pid]})); if (count === 1) { const updateParent = {id: parent.id }; updateParent[this.setting.isLeaf] = true; await this.transaction.update(this.tableName, updateParent); } } // 选中节点--全部后节点 order-- await this._updateSelectNextsOrder(select, -1); // 删除部位明细 //await this.ctx.service.pos.deletePosData(this.transaction, tenderId, this._.map(deleteData, 'id')); await this.transaction.commit(); } catch (err) { await this.transaction.rollback(); throw err; } // 查询结果 const updateData = await this.getNextsData(mid, select[this.setting.pid], select[this.setting.order] - 1); if (parent) { const updateData1 = await this.getDataByNodeId(mid, select[this.setting.pid]); if (updateData1[this.setting.isLeaf]) { updateData.push(updateData1); } } return { delete: deleteData, update: updateData }; } /** * 上移节点 * * @param {Number} mid - master id * @param {Number} kid - 选中节点id * @return {Array} - 发生改变的数据 */ async upMoveNode(mid, kid) { if (!mid || !kid) return null; const select = await this.getDataByLid(mid, kid); if (!select) { throw '上移节点数据错误'; } const pre = await this.getDataByParentAndOrder(mid, select[this.setting.pid], select[this.setting.order] - 1); if (!pre) { throw '节点不可上移'; } this.transaction = await this.db.beginTransaction(); try { const sData = await this.transaction.update(this.tableName, { id: select.id, order: select[this.setting.order] - 1 }); const pData = await this.transaction.update(this.tableName, { id: pre.id, order: pre[this.setting.order] + 1 }); await this.transaction.commit(); } catch (err) { await this.transaction.rollback(); throw err; } const resultData = await this.getDataByParentAndOrder(mid, select[this.setting.pid], [select[this.setting.order], pre[this.setting.order]]); return { update: resultData }; } /** * 下移节点 * * @param {Number} mid - master id * @param {Number} kid - 选中节点id * @return {Array} - 发生改变的数据 */ async downMoveNode(mid, kid) { if (!mid || !kid) return null; const select = await this.getDataByLid(mid, kid); if (!select) { throw '下移节点数据错误'; } const next = await this.getDataByParentAndOrder(mid, select[this.setting.pid], select[this.setting.order] + 1); if (!next) { throw '节点不可下移'; } this.transaction = await this.db.beginTransaction(); try { const sData = await this.transaction.update(this.tableName, { id: select.id, order: select[this.setting.order] + 1 }); const pData = await this.transaction.update(this.tableName, { id: next.id, order: next[this.setting.order] - 1 }); await this.transaction.commit(); } catch (err) { await this.transaction.rollback(); throw err; } const resultData = await this.getDataByParentAndOrder(mid, select[this.setting.pid], [select[this.setting.order], next[this.setting.order]]); return { update: resultData }; } /** * 升级selectData, 同步修改所有子节点 * @param {Object} selectData - 升级操作,选中节点 * @return {Object} * @private */ async _syncUplevelChildren(select) { this.initSqlBuilder(); this.sqlBuilder.setAndWhere(this.setting.mid, { value: selectData.tender_id, operate: '=', }); this.sqlBuilder.setAndWhere(this.setting.fullPath, { value: this.db.escape(select[this.setting.fullPath] + '.%'), operate: 'like', }); this.sqlBuilder.setUpdateData(this.setting.level, { value: 1, selfOperate: '-', }); this.sqlBuilder.setUpdateData(this.setting.fullPath, { value: [this.setting.fullPath, this.db.escape(select[this.setting.pid] + '.'), this.db.escape('')], literal: 'Replace', }); const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update'); const data = await this.transaction.query(sql, sqlParam); return data; } /** * 选中节点的后兄弟节点,全部变为当前节点的子节点 * @param {Object} selectData - 选中节点 * @return {Object} * @private */ async _syncUpLevelNexts(select) { // 查询selectData的lastChild const lastChild = await this.getLastChildData(select[this.setting.mid], select[this.setting.kid]); const nexts = await this.getNextsData(select[this.setting.mid], select[this.setting.pid], select[this.setting.order]); if (nexts && nexts.length > 0) { // 修改nextsData pid, 排序 this.initSqlBuilder(); this.sqlBuilder.setUpdateData(this.setting.pid, { value: select[this.setting.kid], }); const orderInc = lastChild ? lastChild[this.setting.order] - select[this.setting.order] : - select[this.setting.order]; this.sqlBuilder.setUpdateData(this.setting.order, { value: Math.abs(orderInc), selfOperate: orderInc > 0 ? '+' : '-', }); this.sqlBuilder.setAndWhere(this.setting.mid, { value: select[this.setting.mid], operate: '=', }); this.sqlBuilder.setAndWhere(this.setting.pid, { value: select[this.setting.pid], operate: '=', }); this.sqlBuilder.setAndWhere(this.setting.order, { value: select[this.setting.order], operate: '>', }); const [sql1, sqlParam1] = this.sqlBuilder.build(this.tableName, 'update'); await this.transaction.query(sql1, sqlParam1); // 选中节点 is_leaf应为false if (select.is_leaf) { const updateData = { id: select.id, is_leaf: false }; await this.transaction.update(this.tableName, updateData); } // 修改nextsData及其子节点的full_path const oldSubStr = this.db.escape(select[this.setting.pid] + '.'); const newSubStr = this.db.escape(select[this.setting.kid] + '.'); const sqlArr = []; sqlArr.push('Update ?? SET `full_path` = Replace(`full_path`,' + oldSubStr + ',' + newSubStr + ') Where'); sqlArr.push('(`' + this.setting.mid + '` = ' + select[this.setting.mid] + ')'); sqlArr.push(' And ('); for (const data of nexts) { sqlArr.push('`' + this.setting.fullPath + '` Like ' + this.db.escape(data[this.setting.fullPath] + '%')); if (nexts.indexOf(data) < nexts.length - 1) { sqlArr.push(' Or '); } } sqlArr.push(')'); const sql = sqlArr.join(''); const resultData = await this.transaction.query(sql, [this.tableName]); return resultData; } } /** * 升级节点 * * @param {Number} mid - master id * @param {Number} kid - 选中节点id * @return {Array} - 发生改变的数据 */ async upLevelNode(mid, kid) { if ((mid <= 0) || (kid <= 0)) return []; const select = await this.getDataByNodeId(mid, kid); if (!select) throw '升级节点数据错误'; const parent = await this.getDataByNodeId(mid, select[this.setting.pid]); if (!parent) throw '升级节点数据错误'; this.transaction = await this.db.beginTransaction(); const newFullPath = select[this.setting.fullPath].replace(select[this.setting.pid] + '.', ''); try { // 选中节点--父节点 选中节点为firstChild时,修改is_leaf if (select[this.setting.order] === 1) { await this.transaction.update(this.tableName, { id: parent.id, is_leaf: true, }); } // 选中节点--父节点--全部后兄弟节点 order+1 await this._updateSelectNextsOrder(parent); // 选中节点 修改pid, order, full_path, level, is_leaf, 清空计算项 const updateData = { id: select.id }; updateData[this.setting.pid] = parent[this.setting.pid]; updateData[this.setting.order] = parent[this.setting.order] + 1; updateData[this.setting.level] = select[this.setting.level] + 1; updateData[this.setting.fullPath] = newFullPath; const nexts = await this.getNextsData(mid, parent[this.setting.kid], select[this.setting.order]); if (nexts.length > 0) { updateData.is_leaf = true; // updateData.unit_price = null; // updateData.quantity = null; // updateData.total_price = null; // updateData.deal_qty = null; // updateData.deal_tp = null; } await this.transaction.update(this.tableName, updateData); // 选中节点--全部子节点(含孙) level-1, full_path变更 await this._syncUplevelChildren(select); // 选中节点--全部后兄弟节点 收编为子节点 修改pid, order, full_path await this._syncUpLevelNexts(select); await this.transaction.commit(); } catch (err) { await this.transaction.rollback(); throw err; } // 查询修改的数据 const resultData1 = await this.getDataByFullPath(mid, newFullPath + '%'); const resultData2 = await this.getNextsData(mid, parent[this.setting.pid], parent[this.setting.order] + 1); return { update: resultData1.concat(resultData2) }; } /** * 降级selectData, 同步修改所有子节点 * @param {Object} selectData - 选中节点 * @param {Object} preData - 选中节点的前一节点(降级后为父节点) * @return {Promise<*>} * @private */ async _syncDownlevelChildren(select, pre) { this.initSqlBuilder(); this.sqlBuilder.setAndWhere(this.setting.mid, { value: select[this.setting.mid], operate: '=', }); this.sqlBuilder.setAndWhere(this.setting.fullPath, { value: this.db.escape(select[this.setting.fullPath] + '.%'), operate: 'like', }); this.sqlBuilder.setUpdateData(this.setting.level, { value: 1, selfOperate: '+', }); this.sqlBuilder.setUpdateData(this.setting.fullPath, { value: [this.setting.fullPath, this.db.escape('.' + select[this.setting.kid]), this.db.escape('.' + pre[this.setting.kid] + '.' + select[this.setting.kid])], literal: 'Replace', }); const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update'); const data = await this.transaction.query(sql, sqlParam); return data; } /** * 降级节点 * * @param {Number} mid - master id * @param {Number} kid - 选中节点id * @return {Array} - 发生改变的数据 */ async downLevelNode(mid, kid) { if ((mid <= 0) || (kid <= 0)) return []; const select = await this.getDataByNodeId(mid, kid); if (!select) throw '降级节点数据错误'; const pre = await this.getDataByParentAndOrder(mid, select[this.setting.pid], select[this.setting.order] - 1); if (!pre) throw '节点不可降级'; const preLastChild = await this.getLastChildData(mid, pre[this.setting.kid]); this.transaction = await this.db.beginTransaction(); const orgLastPath = select[this.setting.level] === 1 ? select[this.setting.kid] : '.' + select[this.setting.kid]; const newLastPath = select[this.setting.level] === 1 ? pre[this.setting.kid] + '.' + select[this.setting.kid] : '.' + pre[this.setting.kid] + '.' + select[this.setting.kid]; const newFullPath = select.full_path.replace(orgLastPath, newLastPath); try { // 选中节点--全部后节点 order-- await this._updateSelectNextsOrder(select, -1); // 选中节点 修改pid, level, order, full_path const updateData = { id: select.id }; updateData[this.setting.kid] = pre[this.setting.kid]; updateData[this.setting.order] = preLastChild ? preLastChild[this.setting.order] + 1 : 1; updateData[this.setting.level] = select[this.setting.level] + 1; updateData[this.setting.fullPath] = newFullPath; await this.transaction.update(this.tableName, updateData); // 选中节点--全部子节点(含孙) level++, full_path await this._syncDownlevelChildren(select, pre); // 选中节点--前兄弟节点 is_leaf应为false, 清空计算相关字段 const updateData2 = { id: pre.id }; updateData2[this.setting.isLeaf] = false; // updateData2.unit_price = null; // updateData2.quantity = null; // updateData2.total_price = null; // updateData2.deal_qty = null; // updateData2.deal_tp = null; await this.transaction.update(this.tableName, updateData2); await this.transaction.commit(); } catch (err) { await this.transaction.rollback(); throw err; } // 查询修改的数据 // 选中节点及子节点 const resultData1 = await this.getDataByFullPath(mid, newFullPath + '%'); // 选中节点--原前兄弟节点&全部后兄弟节点 const resultData2 = await this.getNextsData(mid, pre[this.setting.pid], pre[this.setting.order]); return { update: resultData1.concat(resultData2) }; } /** * 过滤data中update方式不可提交的字段 * @param {Number} id - 主键key * @param {Object} data * @return {Object<{id: *}>} * @private */ _filterUpdateInvalidField(id, data) { const result = {id: id}; for (const prop in data) { if (this.readOnlyFields.indexOf(prop) === -1) { result[prop] = data[prop]; } } return result; } /** * 提交多条数据 - 不影响计算等未提交项 * @param {Number} mid - master id * @param {Array} datas - 提交数据 * @return {Array} - 提交后的数据 */ async updateInfos(mid, datas) { if (mid <= 0) throw '数据错误'; for (const data of datas) { if (mid !== data[this.setting.mid]) throw '提交数据错误'; } this.transaction = await this.db.beginTransaction(); try { for (const data of datas) { const updateNode = await this.getDataById(data.id); if (!updateNode || mid !== updateNode[this.setting.mid] || data.kid !== updateNode[this.setting.kid]) { throw '提交数据错误'; } const updateData = this._filterUpdateInvalidField(updateNode.id, data); await this.transaction.update(this.tableName, updateData); } await this.transaction.commit(); } catch (err) { await this.transaction.rollback(); throw err; } const resultData = await this.getDataById(this._.map(datas, 'id')); return resultData; } } module.exports = TreeService;