Calculation/app/base/base_service.js

'use strict';

/**
 * 业务逻辑基类
 *
 * @author CaiAoLin
 * @date 2017/10/11
 * @version
 */

const Service = require('egg').Service;
// sql拼装器
const SqlBuilder = require('../lib/sql_builder');
class BaseService extends Service {
    /**
     * 构造函数
     *
     * @param {Object} ctx - egg全局context
     * @return {void}
     */
    constructor(ctx) {
        super(ctx);
        this.db = this.app.mysql;
        this.cache = this.app.redis;
        this.uuid = this.app.uuid;
        this.transaction = null;
        this.sqlBuilder = null;
        this._ = this.app._;
        this.depart = 0;
    }

    /**
     * 设置表名
     *
     * @param {String} table - 表名
     * @return {void}
     */
    set tableName(table) {
        this._table = this.app.config.tablePrefix + table;
    }

    /**
     * 获取表名
     *
     * @return {String} - 返回表名
     */
    get tableName() {
        if (this.depart <= 0) return this._table;

        if (!this.ctx.tender) throw  new TypeError('tableName error: Without Tender');
        return this._table + '_' + (this.ctx.tender.id % this.depart);
    }

    departTableName(key) {
        if (this.depart <= 0) return this._table;

        if (!key) throw  new TypeError('tableName error: Without DepartKey');
        return this._table + '_' + (key % this.depart);
    }

    /**
     * 初始化sqlBuilder
     *
     * @return {void}
     */
    initSqlBuilder() {
        if (this.sqlBuilder === null) {
            this.sqlBuilder = new SqlBuilder();
        }
    }

    /**
     * 根据id查找数据
     *
     * @param {Number} id - 数据库中的id
     * @return {Object} - 返回单条数据
     */
    async getDataById(id) {
        return await this.db.get(this.tableName, { id });
    }

    /**
     * 根据条件查找单条数据
     *
     * @param {Object} condition - 筛选条件
     * @return {Object} - 返回单条数据
     */
    async getDataByCondition(condition) {
        return await this.db.get(this.tableName, condition);
    }

    /**
     * 根据条件查找列表数据
     *
     * @param {Object} condition - 筛选条件
     * @return {Array} - 返回数据
     */
    async getAllDataByCondition(condition, transaction = false) {
        if (!condition.where && !condition.limit) throw '缺少查询条件，如查询全表，请自建sql';
        const conn = transaction || this.db;
        return await conn.select(this.tableName, condition);
    }

    /**
     * 主动分隔插入数据
     *
     * @param transaction 事务
     * @param tableName 表名
     * @param datas 插入数据
     * @param split 分隔条数，默认5000
     * @returns {Promise<void>}
     */
    async insertDatasSplit(transaction, tableName, datas, split = 5000) {
        const conn = transaction || this.db;
        for (let i = 0; i < datas.length; i += split) {
            await conn.insert(tableName, datas.slice(i, i + split));
        }
    }

    /**
     * 根据id删除数据
     *
     * @param {Number} id - 数据库中的id
     * @return {boolean} - 返回是否删除成功
     */
    async deleteById(id) {
        const result = await this.db.delete(this.tableName, { id });
        return result.affectedRows > 0;
    }

    /**
     * 获取总数
     *
     * @param {Object} option - 过滤条件(参考select方法中的condition)
     * @return {Number} - 返回查找的总数
     */
    async count(option = {}) {
        const result = await this.db.count(this.tableName, option);
        return result;
    }

    async defaultUpdate(data, options, transaction = false) {
        const conn = transaction || this.db;
        const result = await conn.update(this.tableName, data, options);
        return result;
    }

    async defaultUpdateRows(data, transaction = false) {
        const conn = transaction || this.db;
        const result = await conn.updateRows(this.tableName, data);
        return result;
    }

    /**
     * 更新数据
     *
     * @param {Object} data - 需要更新的数据
     * @param {Object} condition - 更新的条件筛选
     * @return {Boolean} - 返回更新结果
     */
    async update(data, condition, transaction = false) {
        if (Object.keys(data).length <= 0 || Object.keys(condition).length <= 0) {
            return false;
        }
        const sqlParam = [];
        const param = [];
        for (const key in data) {
            param.push(key + ' = ?');
            sqlParam.push(data[key]);
        }
        const paramString = param.join(',');

        const where = [];
        for (const key in condition) {
            where.push(key + ' = ?');
            sqlParam.push(condition[key]);
        }
        const whereString = where.join(' AND ');

        const sql = 'UPDATE ' + this.tableName + ' SET ' + paramString + ' WHERE ' + whereString;
        const result = transaction ? await transaction.query(sql, sqlParam) : await this.db.query(sql, sqlParam);
        return result.affectedRows > 0;
    }

    /**
     * 获取分页数据(SqlBuilder版本)
     *
     * @return {Array} - 返回分页数据
     */
    async getListWithBuilder() {

        // 由于egg-mysql不提供like、不等于操作，所以需要自己组sql
        if (this.sqlBuilder === null) {
            return [];
        }

        // 分页相关
        this.sqlBuilder.limit = this.ctx.pageSize ? this.ctx.pageSize : this.app.config.pageSize;
        this.sqlBuilder.offset = this.sqlBuilder.limit * (this.ctx.page - 1);

        // 数据筛选
        if (this.ctx.sort !== undefined && this.ctx.sort.length > 0) {
            this.sqlBuilder.orderBy = [this.ctx.sort];
        }

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

        return list;
    }

    /**
     * 获取数据条数(SqlBuilder版本)
     *
     * @return {Array} - 返回分页数据
     */
    async getCountWithBuilder() {
        // 由于egg-mysql不提供like、不等于操作，所以需要自己组sql
        if (this.sqlBuilder === null) {
            return [];
        }
        const [sql, sqlParam] = this.sqlBuilder.buildCount(this.tableName);
        const result = await this.db.query(sql, sqlParam);
        return result ? result.length : 0;
    }

    /**
     * 获取分页数据
     *
     * @param {Object} condition - 搜索条件
     * @return {Array} - 返回分页数据
     */
    async getList(condition) {
        const page = this.ctx.page;

        // 分页设置
        condition.limit = condition.limit === undefined ? this.app.config.pageSize : condition.limit;
        condition.offset = condition.offset === undefined ? this.app.config.pageSize * (page - 1) : condition.offset;

        if (this.ctx.sort !== undefined && this.ctx.sort.length > 0) {
            condition.orders = [this.ctx.sort];
        }
        const list = await this.db.select(this.tableName, condition);

        return list;
    }

    round(value, decimal) {
        return this.ctx.helper.round(value, this._.isNumber(decimal) ? decimal : 8);
    }

    // 针对大批量数据插入，优化插入datas，按50条为分隔
    async insertBigDatas(transaction, insertDatas) {
        const batchSize = 50;
        for (let i = 0; i < insertDatas.length; i += batchSize) {
            const batchDatas = insertDatas.slice(i, i + batchSize);
            await transaction.insert(this.tableName, batchDatas);
        }
    }

    /**
     * 开启数据库事务。
     */
    async beginTransaction() {
        return await this.db.beginTransaction();
    }

    /**
     * 在事务中锁定符合非空标量等值条件的数据，可按指定字段升序排列。
     */
    async getAllDataForUpdate(condition, transaction, orderBy) {
        if (!transaction) throw '锁定查询必须使用事务';
        if (!condition || typeof condition !== 'object' || Array.isArray(condition) || !Object.keys(condition).length) {
            throw '锁定查询缺少等值条件';
        }
        const where = [];
        const sqlParam = [this.tableName];
        for (const key of Object.keys(condition)) {
            if (condition[key] === null || !['string', 'number', 'boolean'].includes(typeof condition[key])) {
                throw '锁定查询仅支持非空标量等值条件';
            }
            where.push('?? = ?');
            sqlParam.push(key, condition[key]);
        }
        if (orderBy) sqlParam.push(orderBy);
        const sql = 'SELECT * FROM ?? WHERE ' + where.join(' AND ') + (orderBy ? ' ORDER BY ?? ASC' : '') + ' FOR UPDATE';
        return await transaction.query(sql, sqlParam);
    }
}
module.exports = BaseService;