Calculation/app/service/ledger.js

'use strict';

/**
 * 标段--台账 数据模型
 *
 * @author CaiAoLin
 * @date 2017/12/1
 * @version
 */

const needField = {
    id: 'ledger_id',
    pid: 'ledger_pid',
    order: 'order',
    level: 'level',
    fullPath: 'full_path'
}

module.exports = app => {

    class Ledger extends app.BaseService {

        /**
         * 构造函数
         *
         * @param {Object} ctx - egg全局变量
         * @return {void}
         */
        constructor(ctx) {
            super(ctx);
            this.tableName = 'ledger';
        }

        /**
         * 新增数据
         *
         * @param {Object} data - 新增的数据(可批量)
         * @param {Number} tenderId - 标段id
         * @return {Boolean} - 返回新增的结果
         */
        async add(data, tenderId) {
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                if (tenderId <= 0) {
                    throw '标段id错误';
                }
                if (data instanceof Array) {
                    // 数组则为批量插入
                    if (data.length <= 0) {
                        throw '插入数据为空';
                    }
                    // 整理数据
                    const insertData = [];
                    for (const tmp of data) {
                        tmp.ledger_id = tmp.id;
                        tmp.ledger_pid = tmp.pid;
                        tmp.tender_id = tenderId;
                        delete tmp.id;
                        delete tmp.pid;
                        insertData.push(tmp);
                    }
                    const operate = await this.transaction.insert(this.tableName, insertData);
                    this.transaction.commit();
                    result = operate.affectedRows > 0;
                } else {
                    // 对象则单个插入
                }
            } catch (error) {
                result = false;
            }

            return result;
        }

        /**
         * 根据层级获取数据
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} showLevel - 显示层数
         * @return {Array} - 返回数据
         */
        async getDataByTenderId(tenderId, showLevel = 4) {
            if (tenderId <= 0) {
                return [];
            }
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });

            if (showLevel > 0) {
                this.sqlBuilder.setAndWhere('level', {
                    value: showLevel,
                    operate: '<=',
                });
            }

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const data = await this.db.query(sql, sqlParam);

            return data;
        }

        /**
         * 根据节点Id获取数据
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} nodeId - 项目节/工程量清单节点id
         * @return {Object} - 返回查询到的节点数据
         */
        async getDataByNodeId(tenderId, nodeId) {
            if ((nodeId <= 0) || (tenderId <= 0)) {
                return undefined;
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_id', {
                value: nodeId,
                operate: '=',
            });

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const data = await this.db.queryOne(sql, sqlParam);

            return data;
        }

        /**
         * 获取最末的子节点
         * @param {Number} tenderId - 标段id
         * @param {Number} pid - 父节点id
         * @returns {Object}
         */
        async getLastChildData(tenderId, pid) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '='
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: pid,
                operate: '='
            });
            this.sqlBuilder.orderBy = [['order', 'DESC']];
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const resultData = this.db.queryOne(sql, sqlParam);

            return resultData;
        }

        /**
         * 根据 父节点id 和 节点排序order 获取数据
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} pid - 父节点id
         * @param {Number|Array} order - 排序
         * @returns {Object|Array} - 查询结果
         */
        async getDataByParentAndOrder(tenderId, pid, order) {
            if ((tenderId <= 0) || (pid <= 0) || (order <= 0)) {
                return undefined;
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '='
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: pid,
                operate: '=',
            });
            if (order instanceof Array) {
                this.sqlBuilder.setAndWhere('order', {
                    value: order,
                    operate: 'in'
                });
            } else {
                this.sqlBuilder.setAndWhere('order', {
                    value: order,
                    operate: '='
                });
            }

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            let data;
            if (order instanceof Array) {
                data = await this.db.query(sql, sqlParam);
            } else {
                data = await this.db.queryOne(sql, sqlParam);
            }

            return data;
        }

        /**
         * 根据 父节点id 获取子节点
         * @param tenderId
         * @param nodeId
         * @returns {Promise<*>}
         */
        async getChildrenByParentId(tenderId, nodeId) {
            if ((nodeId <= 0) || (tenderId <= 0)) {
                return undefined;
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: nodeId,
                operate: '=',
            });

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const data = await this.db.query(sql, sqlParam);

            return data;
        }

        /**
         * 根据full_path获取数据 full_path Like ‘1.2.3%’（传参full_path = '1.2.3%'）
         * @param {Number} tenderId - 标段id
         * @param {String} full_path - 路径
         * @returns {Promise<void>}
         */
        async getDataByFullPath(tenderId, full_path) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '='
            });
            this.sqlBuilder.setAndWhere('full_path', {
                value: this.db.escape(full_path),
                operate: 'Like'
            });
            let [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const resultData = await this.db.query(sql, sqlParam);
            return resultData;
        }

        /**
         * 根据 父节点ID 和 节点排序order 获取全部后节点数据
         * @param {Number} tenderId - 标段id
         * @param {Number} pid - 父节点id
         * @param {Number} order - 排序
         * @returns {Array}
         */
        async getNextsData(tenderId, pid, order) {
            if ((tenderId <= 0) || (pid <= 0) || (order <= 0)) {
                return undefined;
            }

            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: tenderId,
                operate: '='
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: pid,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('order', {
                value: order,
                operate: '>'
            });

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName);
            const data = await this.db.query(sql, sqlParam);

            return data;
        }

        /**
         *  select的全部后兄弟节点，Order自增
         *
         * @param {Object} select - 选中的节点
         * @param {Number} incre - 自增值
         * @return {Array} - 自增后的数据
         * @private
         */
        async _updateSelectNextsOrder(select, incre = 1) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: select.tender_id,
                operate: '=',
            });
            this.sqlBuilder.setAndWhere('order', {
                value: select.order + 1,
                operate: '>=',
            });
            this.sqlBuilder.setAndWhere('ledger_pid', {
                value: select.ledger_pid,
                operate: '=',
            });
            this.sqlBuilder.setUpdateData('order', {
                value: Math.abs(incre),
                selfOperate: incre > 0 ? '+' : '-',
            });
            // sql = update this.tableName set order = order + 1 where (tender_id = select.tender_id) && (pid = select.pid) && (order >= select.order+1)
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
            const data = await this.transaction.query(sql, sqlParam);

            return data;
        }

        /**
         * 从数据库获取标段的最大节点id
         *
         * @param {Number} tenderId - 标段id
         * @return {Number}
         * @private
         */
        async _getMaxNodeId(tenderId) {
            const sql = 'SELECT Max(??) As max_id FROM ?? Where tender_id = ' + tenderId;
            const sqlParam = ['ledger_id', this.tableName];
            const queryResult = await this.db.queryOne(sql, sqlParam);
            return queryResult.max_id;
        }

        /**
         * 根据selectData, data 新增数据
         *
         * @param {Number} tenderId - 标段id
         * @param {Object} selectData - 选中节点的数据
         * @param {Object} data - 新增节点的初始数据
         * @return {Object} - 新增结果
         * @private
         */
        async _addNodeData(tenderId, selectData, data) {
            if (tenderId <= 0) {
                return undefined;
            }
            if (!data) {
                data = {};
            }
            const cacheKey = 'tender_node_maxId:' + tenderId;
            let maxId = parseInt(await this.cache.get(cacheKey));
            if (!maxId) {
               maxId = await this._getMaxNodeId(tenderId);
               this.cache.set(cacheKey, maxId, 'EX', this.ctx.app.config.cacheTime);
            }

            data.tender_id = tenderId;
            data.ledger_id = maxId + 1;
            data.ledger_pid = selectData.ledger_pid;
            data.level = selectData.level;
            data.order = selectData.order + 1;
            data.full_path = selectData.full_path.replace(selectData.ledger_id, data.ledger_id);
            data.is_leaf = true;
            const result = await this.transaction.insert(this.tableName, data);

            this.cache.set(cacheKey, maxId + 1, 'EX', this.ctx.app.config.cacheTime);

            return result;
        }

        /**
         * tenderId标段中, 在selectId后新增一个节点
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @param {Object} data - 新增节点初始化数据
         * @return {Array} 新增后的数据，其他被修改的数据
         */
        async addNode(tenderId, selectId, data) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '新增节点数据错误';
            }
            this.transaction = await this.db.beginTransaction();


            try {
                // 选中节点的所有后兄弟节点，order+1
                await this._updateSelectNextsOrder(selectData);
                // 数据库创建新增节点数据
                await this._addNodeData(tenderId, selectData, data);
                await this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询应返回的结果
            const createData = await this.getDataByParentAndOrder(selectData.tender_id, selectData.ledger_pid, [selectData.order + 1]);
            const updateData = await this.getNextsData(selectData.tender_id, selectData.ledger_pid, selectData.order + 1);
            return {create: createData, update: updateData};
        }

        /**
         *  tenderId标段中, 删除选中节点及其子节点
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 被删除的数据
         */
        async deleteNode(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '删除节点数据错误';
            }
            const parentData = await this.getDataByNodeId(tenderId, selectData.ledger_pid);

            this.transaction = await this.db.beginTransaction();
            let deleteData = [];
            try {
                // 获取将要被删除的数据
                deleteData = await this.getDataByFullPath(tenderId, selectData.full_path + '%');
                // 删除
                this.initSqlBuilder();
                this.sqlBuilder.setAndWhere('tender_id', {
                    value: tenderId,
                    operate: '='
                });
                this.sqlBuilder.setAndWhere('full_path', {
                    value: this.db.escape(selectData.full_path + '%'),
                    operate: 'Like'
                });
                const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'delete');
                const operate = await this.transaction.query(sql, sqlParam);
                // 选中节点--父节点 只有一个子节点时，应升级is_leaf
                if (parentData) {
                    const count = this.db.count(this.tableName, {ledger_pid: selectData.ledger_pid});
                    if (count === 1) {
                        await this.transaction.update({
                            id: parentData.id,
                            is_leaf: true
                        });
                    }
                }
                // 选中节点--全部后节点 order--
                await this._updateSelectNextsOrder(selectData, -1);
                await this.transaction.commit();
            } catch(err) {
                deleteData = [];
                await this.transaction.rollback();
                throw err;
            }
            // 查询结果
            let updateData = [];
            if (deleteData.length > 0) {
                updateData = await this.getNextsData(tenderId, selectData.ledger_pid, selectData.order - 1);
                updateData = updateData ? updateData : [];
                const updateData2 = await this.getDataByNodeId(tenderId, selectData.ledger_pid);
                if (updateData2.is_leaf === parentData.is_leaf) {
                    updateData.push(updateData2);
                }
            }
            return {delete: deleteData, update: updateData};
        }

        /**
         * tenderId标段中, 选中节点selectId上移
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async upMoveNode(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '上移节点数据错误';
            }
            const preData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, selectData.order - 1);
            if (!preData) {
                throw '节点不可上移';
            }

            this.transaction = await this.db.beginTransaction();
            try {
                const sData = await this.transaction.update(this.tableName, {id: selectData.id, order: selectData.order - 1});
                const pData = await this.transaction.update(this.tableName, {id: preData.id, order: preData.order + 1});
                this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            const resultData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, [selectData.order, preData.order]);
            return {update: resultData};
        }

        /**
         * tenderId标段中, 选中节点selectId下移
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async downMoveNode(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '下移节点数据错误';
            }
            const nextData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, selectData.order + 1);
            if (!nextData) {
                throw '节点不可下移'
            }

            this.transaction = await this.db.beginTransaction();
            try {
                const sData = await this.transaction.update(this.tableName, {id: selectData.id, order: selectData.order + 1});
                const pData = await this.transaction.update(this.tableName, {id: nextData.id, order: nextData.order - 1});
                this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            const resultData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, [selectData.order, nextData.order]);
            return {update: resultData};
        }

        /**
         * 升级selectData, 同步修改所有子节点
         * @param {Object} selectData - 升级操作，选中节点
         * @return {Object}
         * @private
         */
        async _syncUplevelChildren(selectData) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: selectData.tender_id,
                operate: '='
            });
            this.sqlBuilder.setAndWhere('full_path', {
                value: this.db.escape(selectData.full_path + '.%'),
                operate: 'like'
            });
            this.sqlBuilder.setUpdateData('level', {
                value: 1,
                selfOperate: '-'
            });
            this.sqlBuilder.setUpdateData('full_path', {
                value: ['`full_path`', this.db.escape(selectData.ledger_pid + '.'), this.db.escape('')],
                literal: 'Replace'
            });
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
            const data = this.transaction.query(sql, sqlParam);

            return data;
        }

        /**
         * 选中节点的后兄弟节点，全部变为当前节点的子节点
         * @param {Object} selectData - 选中节点
         * @return {Object}
         * @private
         */
        async _syncUpLevelNexts(selectData) {
            // 查询selectData的lastChild
            const lastChildData = await this.getLastChildData(selectData.tender_id, selectData.ledger_id);
            const nextsData = await this.getNextsData(selectData.tender_id, selectData.ledger_pid, selectData.order);
            if (nextsData && nextsData.length > 0) {
                // 修改nextsData pid, 排序
                this.initSqlBuilder();
                this.sqlBuilder.setUpdateData('ledger_pid', {
                    value: selectData.ledger_id
                });
                const orderInc = lastChildData ? lastChildData.order - selectData.order : - selectData.order;
                this.sqlBuilder.setUpdateData('order', {
                    value: Math.abs(orderInc),
                    selfOperate: orderInc > 0 ? '+' : '-'
                });
                this.sqlBuilder.setAndWhere('ledger_pid', {
                    value: selectData.ledger_pid,
                    operate: '='
                });
                this.sqlBuilder.setAndWhere('order', {
                    value: selectData.order,
                    operate: '>'
                });
                const [sql1, sqlParam1] = this.sqlBuilder.build(this.tableName, 'update');
                await this.transaction.query(sql1, sqlParam1);

                // 选中节点 is_leaf应为false
                if (selectData.is_leaf) {
                    const updateData = {id: selectData.id,
                        is_leaf: false
                    }
                    await this.transaction.update(this.tableName, updateData);
                }

                // 修改nextsData及其子节点的full_path
                const oldSubStr = this.db.escape(selectData.ledger_pid + '.');
                const newSubStr = this.db.escape(selectData.ledger_id + '.');
                const sqlArr = [];
                sqlArr.push('Update ?? SET `full_path` = Replace(`full_path`,' + oldSubStr + ',' + newSubStr + ') Where');
                sqlArr.push('(`tender_id` = ' + selectData.tender_id +')');
                sqlArr.push(' And (');
                for (const data of nextsData) {
                    sqlArr.push('`full_path` Like ' + this.db.escape(data.full_path + '%'));
                    if (nextsData.indexOf(data) < nextsData.length - 1) {
                        sqlArr.push(' Or ');
                    }
                }
                sqlArr.push(')');
                const sql = sqlArr.join('');
                const resultData = await this.transaction.query(sql, [this.tableName]);
                return resultData;
            }
        }

        /**
         * 升级节点
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async upLevelNode(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '升级节点数据错误';
            }
            const parentData = await this.getDataByNodeId(tenderId, selectData.ledger_pid);
            if (!parentData) {
                throw '升级节点数据错误'
            }

            this.transaction = await this.db.beginTransaction();
            const newFullPath = selectData.full_path.replace(selectData.ledger_pid + '.', '');
            try {
                // 选中节点--父节点 选中节点为firstChild时，修改is_leaf
                if (selectData.order === 1) {
                    this.transaction.update(this.tableName, {
                        id: parentData.id,
                        is_leaf: true
                    })
                }
                // 选中节点--父节点--全部后兄弟节点 order+1
                await this._updateSelectNextsOrder(parentData);
                // 选中节点 修改pid, order, full_path
                const updateData = {id: selectData.id,
                    ledger_pid: parentData.ledger_pid,
                    order: parentData.order + 1,
                    level: selectData.level - 1,
                    full_path: newFullPath
                };
                await this.transaction.update(this.tableName, updateData);
                // 选中节点--全部子节点（含孙） level-1, full_path变更
                await this._syncUplevelChildren(selectData);
                // 选中节点--全部后兄弟节点 收编为子节点 修改pid, order, full_path
                await this._syncUpLevelNexts(selectData);
                this.transaction.commit();
            } catch (err) {
                await this.transaction.rollback();
                throw err;
            }

            // 查询修改的数据
            const resultData1 = await this.getDataByFullPath(tenderId, newFullPath + '%');
            const resultData2 = await this.getNextsData(tenderId, parentData.ledger_pid, parentData.order + 1);
            if (selectData.order === 1) {
                const preParent = await this.getDataByNodeId(tenderId, parentData.ledger_id);
                resultData2.push(preParent);
            }
            return {update: resultData1.concat(resultData2)};
        }

        /**
         * 降级selectData, 同步修改所有子节点
         * @param {Object} selectData - 选中节点
         * @param {Object} preData - 选中节点的前一节点（降级后为父节点）
         * @returns {Promise<*>}
         * @private
         */
        async _syncDownlevelChildren(selectData, preData) {
            this.initSqlBuilder();
            this.sqlBuilder.setAndWhere('tender_id', {
                value: selectData.tender_id,
                operate: '='
            });
            this.sqlBuilder.setAndWhere('full_path', {
                value: this.db.escape(selectData.full_path + '.%'),
                operate: 'like'
            });
            this.sqlBuilder.setUpdateData('level', {
                value: 1,
                selfOperate: '+'
            });
            this.sqlBuilder.setUpdateData('full_path', {
                value: ['`full_path`', this.db.escape(selectData.ledger_id), this.db.escape(preData.ledger_id + '.' + selectData.ledger_id)],
                literal: 'Replace'
            });
            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'update');
            const data = this.transaction.query(sql, sqlParam);

            return data;
        }

        /**
         * 降级节点
         *
         * @param {Number} tenderId - 标段id
         * @param {Number} selectId - 选中节点id
         * @return {Array} - 发生改变的数据
         */
        async downLevelNode(tenderId, selectId) {
            if ((tenderId <= 0) || (selectId <= 0)) {
                return [];
            }
            const selectData = await this.getDataByNodeId(tenderId, selectId);
            if (!selectData) {
                throw '降级节点数据错误';
            }
            const preData = await this.getDataByParentAndOrder(tenderId, selectData.ledger_pid, selectData.order-1);
            if (!preData) {
                throw '节点不可降级';
            }
            const preLastChildData = await this.getLastChildData(tenderId, preData.ledger_id);

            this.transaction = await this.db.beginTransaction();
            const newFullPath = selectData.full_path.replace(selectData.ledger_id, preData.ledger_id + '.' + selectData.ledger_id);
            try {
                // 选中节点--全部后节点 order--
                await this._updateSelectNextsOrder(selectData, -1);
                // 选中节点 修改pid, level, order, full_path
                const updateData = {id: selectData.id,
                    ledger_pid: preData.ledger_id,
                    order: preLastChildData ? preLastChildData.order + 1 : 1,
                    level: selectData.level + 1,
                    full_path: newFullPath
                };
                await this.transaction.update(this.tableName, updateData);
                // 选中节点--全部子节点（含孙） level++, full_path
                await this._syncDownlevelChildren(selectData, preData);
                // 选中节点--前兄弟节点 is_leaf应为false
                if (preData.is_leaf) {
                    const updateData2 = {id: preData.id,
                        is_leaf: false
                    }
                    await this.transaction.update(this.tableName, updateData);
                }
                this.transaction.commit();
            } catch (err) {
                this.transaction.rollback();
                throw err;
            }

            // 查询修改的数据
            // 选中节点及子节点
            const resultData1 = await this.getDataByFullPath(tenderId, newFullPath + '%');
            // 选中节点--原前兄弟节点&全部后兄弟节点
            const queryOrder = preData.is_leaf ? preData.order - 1 : preData.order;
            const resultData2 = await this.getNextsData(tenderId, preData.ledger_pid, queryOrder);
            return {update: resultData1.concat(resultData2)};
        }
    }

    return Ledger;
};