Calculation/app/lib/sql_builder.js

'use strict';

/**
 * sql语句构建器
 *
 * @author CaiAoLin
 * @date 2017/10/11
 * @version
 */
class SqlBuilder {

    /**
     * 构造函数
     *
     * @return {void}
     */
    constructor() {
        this.resetCondition();
    }

    /**
     * 设置andWhere数据
     *
     * @param {String} field - where中的字段名称
     * @param {Object} data - where中的value部分
     * @return {void}
     */
    setAndWhere(field, data) {
        if (Object.keys(data).length <= 0) {
            return;
        }
        this.andWhere.push({ field, data });
    }

    /**
     * 设置新的orWhere数据
     *
     * @param {String} field - where中的字段名称
     * @param {Object} data - where中的value部分
     * @return {void}
     */
    setNewOrWhere(data) {
        if (Object.keys(data).length <= 0) {
            return;
        }
        this.newOrWhere.push(data);
    }

    /**
     * 更新字段设置
     *
     * @param {String} field - set的字段
     * @param {Object} data - set中的字段
     * @return {void}
     */
    setUpdateData(field, data) {
        if (Object.keys(data).length <= 0) {
            return;
        }
        this.setData.push({ field, data });
    }

    /**
     * 重置条件
     *
     * @return {void}
     */
    resetCondition() {
        this.andWhere = [];
        this.orWhere = [];
        this.newOrWhere = [];
        this.columns = [];
        this.limit = -1;
        this.offset = -1;
        this.orderBy = [];
        this.setData = [];

        this.sql = '';
        this.sqlParam = [];
        this.type = 'select';
    }

    /**
     * 类型设置
     *
     * @param {String} tableName - 表名
     * @return {void}
     */
    _typeBuild(tableName) {
        switch (this.type) {
            case 'select':
                this.sql = this.columns.length === 0 ? 'SELECT * FROM ??' : 'SELECT ?? FROM ??';
                this.sqlParam = this.columns.length === 0 ? [tableName] : [this.columns, tableName];
                break;
            case 'update':
                this.sql = 'UPDATE ?? SET ';
                this.sqlParam = [tableName];
                break;
            case 'delete':
                this.sql = 'DELETE FROM ??';
                this.sqlParam = [tableName];
                break;
            default:
                break;
        }
    }

    /**
     * 设置update数据
     *
     * @return {void}
     */
    _setDataBuild() {
        if (this.setData.length <= 0) {
            return;
        }
        const setDataArr = [];
        for (const set of this.setData) {
            if (set.data.literal) {
                const values = set.data.value instanceof Array ? set.data.value : [set.data.value];
                setDataArr.push(' ?? = ' + set.data.literal + '(' + values.join(',') + ')');
                this.sqlParam.push(set.field);
            } else {
                const tmp = set.data.selfOperate !== undefined ?
                    ' ?? = IF(IsNull(??), 0, ??) ' + set.data.selfOperate + ' ' + set.data.value : ' ?? = ' + set.data.value;
                setDataArr.push(tmp);
                // 如果是自身操作则压多一次字段进数组
                if (set.data.selfOperate !== undefined) {
                    this.sqlParam.push(set.field);
                    this.sqlParam.push(set.field);
                }
                this.sqlParam.push(set.field);
            }
        }

        const setString = setDataArr.join(',');
        this.sql += setString;
    }

    /**
     * andWhere设置
     *
     * @return {void}
     */
    _andWhereBuild() {
        if (this.andWhere.length <= 0) {
            return;
        }

        const whereArr = [];
        for (const where of this.andWhere) {
            if (where.data.operate === 'in') {
                // in操作
                const valueLength = where.data.value instanceof Array ? where.data.value.length : 1;
                // 生成参数集
                const inArr = [];
                for (let i = 0; i < valueLength; i++) {
                    inArr.push('?');
                }
                const inData = inArr.join(',');
                whereArr.push(' ?? IN (' + inData + ')');
                this.sqlParam.push(where.field);
                // 赋值参数
                this.sqlParam.push.apply(this.sqlParam, where.data.value);
            } else if (where.data.operate === 'find_in_set') {
                whereArr.push(' find_in_set (' + where.data.value + ', ?? )');
                this.sqlParam.push(where.field);
            } else {
                // 普通操作
                whereArr.push(' ?? ' + where.data.operate + ' ' + where.data.value);
                this.sqlParam.push(where.field);
            }

        }
        const whereString = whereArr.join(' AND ');
        this.sql += this.sql.indexOf('WHERE') > 0 ? whereString : ' WHERE ' + whereString;
    }

    /**
     * orWhere设置
     *
     * @return {void}
     */
    _orWhereBuild() {
        if (this.orWhere.length <= 0) {
            return;
        }

        const whereArr = [];
        for (const where in this.orWhere) {
            whereArr.push(' ?? ' + where.data.operate + ' ' + where.data.value);
            this.sqlParam.push(where.field);
        }
        const whereString = whereArr.join(' OR ');
        // 如果前面已经有设置过WHERE则不需要再重复添加
        this.sql += this.sql.indexOf('WHERE') > 0 ? whereString : ' WHERE ' + whereString;
    }

    /**
     * orWhere设置-旧的不好用，独立写了一个包含括号的or兼容,传对象数组，
     * 例：
     * [{ field: 'office_share', value: managerSession.office, operate: 'find_in_set'},
     * {field: 'office',value: managerSession.office,operate: '='},......]
     *
     * @return {void}
     */
    _newOrWhereBuild() {
        if (this.newOrWhere.length <= 0) {
            return;
        }

        const whereArr = [];
        for (const oneOw in this.newOrWhere) {
            for (const where of this.newOrWhere[oneOw]) {
                if (where.operate === 'in') {
                    // in操作
                    const valueLength = where.value instanceof Array ? where.value.length : 1;
                    // 生成参数集
                    const inArr = [];
                    for (let i = 0; i < valueLength; i++) {
                        inArr.push('?');
                    }
                    const inData = inArr.join(',');
                    whereArr.push(' ?? IN (' + inData + ')');
                    this.sqlParam.push(where.field);
                    // 赋值参数
                    this.sqlParam.push.apply(this.sqlParam, where.value);
                } else if (where.operate === 'find_in_set') {
                    whereArr.push(' find_in_set (' + where.value + ', ?? )');
                    this.sqlParam.push(where.field);
                } else {
                    // 普通操作
                    whereArr.push(' ?? ' + where.operate + ' ' + where.value);
                    this.sqlParam.push(where.field);
                }
            }
            let whereString = whereArr.join(' OR ');
            whereString = '(' + whereString + ')';
            // 如果前面已经有设置过WHERE则不需要再重复添加
            this.sql += this.sql.indexOf('WHERE') > 0 ? ' AND ' + whereString : ' WHERE ' + whereString;
        }
    }

    /**
     * limit设置
     *
     * @return {void}
     */
    _limitBuild() {
        if (typeof this.limit !== 'number' || this.limit <= 0) {
            return;
        }

        this.offset = parseInt(this.offset);
        this.offset = isNaN(this.offset) || this.offset < 0 ? 0 : this.offset;
        const limitString = this.offset >= 0 ? this.offset + ',' + this.limit : this.limit;
        this.sql += ' LIMIT ' + limitString;
    }

    /**
     * orderby 设置
     *
     * @return {void}
     */
    _orderByBuild() {
        if (this.orderBy.length <= 0) {
            return;
        }

        const orderArr = [];
        for (const index in this.orderBy) {
            if (!this.orderBy[index]) continue;
            orderArr.push(' ?? ' + this.orderBy[index][1]);
            this.sqlParam.push(this.orderBy[index][0]);
        }
        const orderByString = orderArr.join(',');
        this.sql += ' ORDER BY ' + orderByString;
    }

    /**
     * 构建sql
     *
     * @param {String} tableName - 表名
     * @param {String} type - 类型
     * @return {Array} - 返回数组，第一个元素为sql语句，第二个元素为sql中问号部分的param
     */
    build(tableName, type = 'select') {
        this.type = type;
        this._typeBuild(tableName);
        if (this.sql === '') {
            throw '类型错误';
        }

        this._setDataBuild();
        this._andWhereBuild();
        this._orWhereBuild();
        this._newOrWhereBuild();

        this._orderByBuild();
        this._limitBuild();

        const sql = this.sql;
        const sqlParam = this.sqlParam;
        // 重置数据
        this.resetCondition();

        return [sql, sqlParam];
    }

    /**
     * 构建sql
     *
     * @param {String} tableName - 表名
     * @param {String} type - 类型
     * @return {Array} - 返回数组，第一个元素为sql语句，第二个元素为sql中问号部分的param
     */
    buildCount(tableName, type = 'select') {
        this.type = type;
        this._typeBuild(tableName);
        if (this.sql === '') {
            throw '类型错误';
        }

        this._setDataBuild();
        this._andWhereBuild();
        this._orWhereBuild();
        this._newOrWhereBuild();

        const sql = this.sql;
        const sqlParam = this.sqlParam;
        // 重置数据
        // this.resetCondition();

        return [sql, sqlParam];
    }

}
module.exports = SqlBuilder;