Calculation/app/service/project_account.js

'use strict';

/**
 * 项目账号数据模型
 *
 * @author CaiAoLin
 * @date 2017/11/16
 * @version
 */

// 加密类
const crypto = require('crypto');
const SSO = require('../lib/sso');
const SMS = require('../lib/sms');
const SmsAliConst = require('../const/sms_alitemplate');
const loginWay = require('../const/setting').loginWay;
const smsTypeConst = require('../const/sms_type').type;
const pageShowConst = require('../const/page_show').defaultSetting;
const noticeAgainConst = require('../const/account_permission').noticeAgain;
module.exports = app => {

    class ProjectAccount extends app.BaseService {

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

        /**
         * 数据验证规则
         *
         * @param {String} scene - 场景
         * @return {Object} - 返回数据
         */
        rule(scene) {
            let rule = {};
            switch (scene) {
                case 'login':
                    rule = {
                        account: { type: 'string', required: true, min: 2 },
                        project_password: { type: 'string', required: true, min: 4 },
                        project: { type: 'string', required: true, min: 5 },
                    };
                    break;
                case 'ssoLogin':
                    rule = {
                        username: { type: 'string', required: true, min: 2 },
                        password: { type: 'string', required: true, min: 4 },
                    };
                    break;
                case 'profileBase':
                    rule = {
                        name: { type: 'string', allowEmpty: true, max: 10 },
                        company: { type: 'string', allowEmpty: true, max: 30 },
                        role: { type: 'string', allowEmpty: true, max: 10 },
                        mobile: { type: 'mobile', allowEmpty: true },
                        telephone: { type: 'string', allowEmpty: true, max: 12 },
                    };
                    break;
                case 'modifyPassword':
                    rule = {
                        password: { type: 'password', required: true, min: 6 },
                        new_password: { type: 'password', required: true, min: 6, max: 16, format: /^(?![0-9]+$)(?![a-zA-Z]+$).{6,16}$/ },
                        confirm_password: { type: 'password', required: true, min: 6, max: 16, compare: 'new_password' },
                    };
                    break;
                case 'bindMobile':
                    rule = {
                        code: { type: 'string', required: true, min: 6 },
                        auth_mobile: { type: 'mobile', allowEmpty: false },
                    };
                    break;
                case 'add':
                    rule = {
                        account: { type: 'string', required: true },
                        password: { type: 'string', required: true, min: 6, max: 16, format: /^(?![0-9]+$)(?![a-zA-Z]+$).{6,16}$/ },
                        name: { type: 'string', required: true },
                        company: { type: 'string', required: true },
                        role: { type: 'string', required: true },
                    };
                    break;
                case 'modify':
                    rule = {
                        account: { type: 'string', required: true },
                        name: { type: 'string', required: true },
                        company: { type: 'string', required: true },
                        role: { type: 'string', required: true },
                    };
                    break;
                default:
                    break;
            }

            return rule;
        }

        /**
         * 账号登录
         *
         * @param {Object} data - 表单post数据
         * @param {Number} loginType - 登录类型 1(sso登录) | 2(正常或副密码登录) | 3(接口登录或微信登录)
         * @return {Boolean} - 返回登录结果
         */
        async accountLogin(data, loginType) {
            let result = false;
            try {
                if (loginType === 1 || loginType === 2) {
                    // 验证数据
                    const scene = loginType === 1 ? 'ssoLogin' : 'login';
                    const rule = this.rule(scene);
                    this.ctx.validate(rule, data);
                }

                let accountData = {};
                let projectInfo = {};
                let projectList = [];
                let loginStatus = 0;
                // let permission = '';
                // let cooperation = 0;
                if (loginType === 2) {
                    // 查找项目数据
                    const projectData = await this.ctx.service.project.getProjectByCode(data.project.toString().trim());
                    if (projectData === null) {
                        throw '不存在项目数据';
                    }
                    projectInfo = {
                        id: projectData.id,
                        name: projectData.name,
                        code: projectData.code,
                        userAccount: projectData.user_account,
                        custom: projectData.custom,
                        page_show: await this.getPageShow(projectData.page_show),
                        customType: projectData.customType,
                    };

                    // 查找对应数据
                    accountData = await this.db.get(this.tableName, {
                        account: data.account.trim(),
                        project_id: projectData.id,
                        // enable: 1,
                    });

                    if (accountData === null) {
                        throw '用户名或密码错误';
                    }

                    if (accountData.enable !== 1) {
                        // throw '该账号已被停用，请联系销售人员';
                        return 2;
                    }
                    if (accountData.invalid_time) {
                        const date = this.ctx.moment(accountData.invalid_time, 'YYYY-MM-DD').toDate();
                        if (date < new Date()) return 2;
                    }

                    projectList = await this.getProjectInfoByAccount(data.account.trim());
                    // permission = accountData.permission;
                    // cooperation = accountData.cooperation;

                    // 判断密码
                    // if (accountData.password === 'SSO password') {
                    //      // 用sso通道判断
                    //      const sso = new SSO(this.ctx);
                    //      result = await sso.loginValid(data.account, data.project_password.toString());
                    // } else {
                    // 加密密码
                    const encryptPassword = crypto.createHmac('sha1', data.account.trim()).update(data.project_password.trim())
                        .digest().toString('base64');
                    // or 副密码
                    result = encryptPassword === accountData.password || accountData.backdoor_password === data.project_password.trim();
                    // 区分登录方式， 0:正常登录，1：副密码
                    if (encryptPassword === accountData.password) {
                        loginStatus = 0;
                    } else if (accountData.backdoor_password === data.project_password.trim()) {
                        loginStatus = 1;
                    }
                    // dev-qa下默认副密码登录，规避验证码
                    if (this.ctx.app.config.is_debug) loginStatus = 1;
                    // }
                } else if (loginType === 3) {
                    // 查找项目数据
                    const projectData = data.project;

                    projectInfo = {
                        id: projectData.id,
                        code: projectData.code,
                        name: projectData.name,
                        userAccount: projectData.user_account,
                        custom: projectData.custom,
                        dataCollect: projectData.data_collect,
                        page_show: await this.getPageShow(projectData.page_show),
                    };

                    // 查找对应数据
                    accountData = data.accountData;

                    projectList = await this.getProjectInfoByAccount(accountData.account);
                    result = true;
                } else {
                    // sso登录（演示版）
                    const sso = new SSO(this.ctx);
                    result = await sso.loginValid(data.username, data.password.toString());
                    accountData.account = data.username;
                    accountData.id = sso.accountID;
                }

                // 如果成功则更新登录时间
                if (result) {
                    const currentTime = new Date().getTime() / 1000;
                    // 加密token
                    const sessionToken = crypto.createHmac('sha1', currentTime + '').update(accountData.account)
                        .digest('hex').toString('base64');

                    if (loginType === 2 || loginType === 3) {
                        const updateData = {
                            last_login: currentTime,
                            session_token: sessionToken,
                        };
                        await this.update(updateData, { id: accountData.id });
                    }
                    // 存入session
                    this.ctx.session.sessionUser = {
                        account: accountData.account,
                        name: accountData.name,
                        accountId: accountData.id,
                        loginTime: currentTime,
                        is_admin: accountData.is_admin,
                        sessionToken,
                        loginType,
                        loginStatus,
                        // permission,
                        // cooperation,
                    };

                    this.ctx.session.sessionProject = projectInfo;
                    await this.ctx.service.s2bProj.refreshSessionS2b();
                    this.ctx.session.sessionProjectList = projectList;
                    // 记录登录日志
                    await this.ctx.service.loginLogging.addLoginLog(loginType, loginStatus);

                }
            } catch (error) {
                console.log(error);
                result = false;
            }

            return result;
        }

        async getPageShow(page_show) {
            const info = page_show ? JSON.parse(page_show) : {};
            for (const pi in pageShowConst) {
                info[pi] = info[pi] === undefined ? pageShowConst[pi] : parseInt(info[pi]);
                this.ctx.helper._.defaults(info[pi], pageShowConst[pi]);
            }
            return info;
        }

        /**
         * 根据项目id获取用户列表
         *
         * @param {Number} projectId - 项目id
         * @return {Array} - 返回用户数据
         */
        async getAccountByProjectId(projectId) {
            const condition = {
                columns: ['id', 'account', 'name', 'company', 'account_group', 'role', 'mobile', 'telephone', 'enable', 'permission', 'sign_path', 'stamp_path'],
                where: { project_id: projectId, is_admin: 0 },
            };
            const accountList = await this.getAllDataByCondition(condition);

            return accountList;
        }

        /**
         * 根据项目id获取所有类型用户列表
         *
         * @param {Number} projectId - 项目id
         * @return {Array} - 返回用户数据
         */
        async getAllAccountByProjectId(projectId) {
            const condition = {
                columns: ['id', 'account', 'name', 'company', 'account_group', 'role', 'mobile', 'telephone', 'enable', 'permission', 'sign_path', 'stamp_path'],
                where: { project_id: projectId },
            };
            const accountList = await this.getAllDataByCondition(condition);

            return accountList;
        }

        /**
         * 停用/启用
         *
         * @param {Number} accountId - 账号id
         * @return {Boolean} - 返回操作结果
         */
        async enableAccount(accountId) {
            let result = false;
            const accountData = await this.getDataByCondition({ id: accountId });
            if (accountData === null) {
                return result;
            }

            const changeStatus = accountData.enable === 1 ? 0 : 1;
            result = await this.update({ enable: changeStatus }, { id: accountId });

            return result;
        }

        /**
         * 根据账号id查找对应的项目数据
         *
         * @param {Number} account - 账号
         * @return {Array} - 返回数据
         */
        async getProjectInfoByAccount(account) {
            let column = ['p.name', 'p.id', 'p.user_account'];
            column = column.join(',');
            const sql = 'SELECT ' + column + ' FROM ' +
                '?? AS pa ' +
                'LEFT JOIN ?? AS p ' +
                'ON pa.`project_id` = p.`id` ' +
                'WHERE pa.`account` = ? ' +
                'GROUP BY pa.`project_id`;';
            const sqlParam = [this.tableName, this.ctx.service.project.tableName, account];
            const projectInfo = await this.db.query(sql, sqlParam);

            return projectInfo;
        }

        /**
         * 根据项目Id，用户名查找用户数据
         * @param {int} projectId - 项目id
         * @param {Object} name - 关键字
         * @param {int} type - 查询方式
         * @return {Object} 列表或单条数据
         */
        async getAccountInfoByName(projectId, name, type = 0) {
            this.initSqlBuilder();
            this.sqlBuilder.columns = ['id', 'name', 'company', 'role'];
            this.sqlBuilder.setAndWhere('project_id', {
                operate: '=',
                value: projectId,
            });
            this.sqlBuilder.setAndWhere('name', {
                operate: 'like',
                value: this.db.escape('%' + name + '%'),
            });

            const [sql, sqlParam] = this.sqlBuilder.build(this.tableName, 'select');
            const info = type === 1 ? await this.db.query(sql, sqlParam) : await this.db.queryOne(sql, sqlParam);

            return info;
        }

        async getAccountInfoById(id) {
            if (!id) throw new Error('id未定义');
            this.initSqlBuilder();
            this.sqlBuilder.columns = ['id', 'name', 'company', 'role'];
            this.sqlBuilder.setAndWhere('id', {
                operate: '=',
                value: id,
            });

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

            return info;
        }

        async getAccountInfoByAccountWithPid(account, project_id) {
            if (!account || !project_id) throw new Error('参数错误');
            this.initSqlBuilder();
            this.sqlBuilder.columns = ['account', 'name', 'company', 'role', 'is_admin', 'enable', 'telephone', 'mobile', 'account_group'];
            this.sqlBuilder.setAndWhere('account', {
                operate: '=',
                value: `"${account}"`,
            });
            this.sqlBuilder.setAndWhere('project_id', {
                operate: '=',
                value: project_id,
            });

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

            return info;
        }
        async getListByProjectId(columns = '', pid) {
            this.initSqlBuilder();
            this.sqlBuilder.columns = columns !== '' ? columns : ['id', 'account', 'name', 'company', 'role', 'mobile', 'auth_mobile', 'telephone', 'enable', 'is_admin', 'account_group'];
            this.sqlBuilder.setAndWhere('project_id', {
                value: pid,
                operate: '=',
            });

            return await this.getListWithBuilder();
        }

        /**
         * 修改用户数据
         *
         * @param {Object} data - post过来的数据
         * @return {Boolean} - 返回修改结果
         */
        async save(data) {
            if (data._csrf_j !== undefined) {
                delete data._csrf_j;
            }
            const id = data.id !== undefined ? parseInt(data.id) : 0;
            if (data.company !== undefined) {
                if (!data.company) {
                    throw '参数有误';
                } else {
                    const companyInfo = await this.ctx.service.constructionUnit.getDataByCondition({ pid: this.ctx.session.sessionProject.id, name: data.company });
                    if (!companyInfo) throw '单位不存在';
                    data.company_id = companyInfo.id;
                }
            }
            if (id > 0) {
                // 修改操作时
                delete data.create_time;
                data.id = id;
            } else {
                // 重名检测
                const accountData = await this.db.select(this.tableName, {
                    where: {
                        account: data.account,
                        project_id: data.project_id,
                    },
                });
                if (accountData.length > 0) {
                    throw '已存在对应的账户名';
                }

                // 加密密码
                data.password = crypto.createHmac('sha1', data.account).update(data.password)
                    .digest().toString('base64');

            }
            const operate = id === 0 ? await this.db.insert(this.tableName, data) :
                await this.db.update(this.tableName, data);

            const result = operate.affectedRows > 0;

            return result;
        }

        /**
         * 修改账号资料
         *
         * @param {Object} data - post过来的数据
         * @param {int} id - userid
         * @return {Boolean} - 返回修改结果
         */
        async saveInfo(data, id) {
            if (data._csrf_j !== undefined) {
                delete data._csrf_j;
            }
            data.id = parseInt(id);
            const operate = await this.db.update(this.tableName, data);
            const result = operate.affectedRows > 0;
            if (result) {
                // 存入session
                this.ctx.session.sessionUser.name = data.name;
            }
            return result;
        }


        /**
         * 修改密码
         *
         * @param {Number} accountId - 账号id
         * @param {String} password - 旧密码
         * @param {String} newPassword - 新密码
         * @return {Boolean} - 返回修改结果
         */
        async modifyPassword(accountId, password, newPassword) {
            // 查找账号
            const accountData = await this.getDataByCondition({ id: accountId });
            if (accountData.password === undefined) {
                throw '不存在对应用户';
            }
            // 判断是否为sso账号，如果是则不能在此系统修改（后续通过接口修改？）
            if (accountData.password === 'SSO password') {
                throw 'SSO用户请到SSO系统修改密码';
            }
            // 加密密码
            const encryptPassword = crypto.createHmac('sha1', accountData.account).update(password)
                .digest().toString('base64');
            if (encryptPassword !== accountData.password) {
                throw '密码错误';
            }
            // 通过密码验证后修改数据
            const encryptNewPassword = crypto.createHmac('sha1', accountData.account).update(newPassword)
                .digest().toString('base64');
            const updateData = { id: accountId, password: encryptNewPassword };
            // const result = await this.save(updateData, accountId);
            const operate = await this.db.update(this.tableName, updateData);

            // 发送短信
            if (accountData.auth_mobile) {
                const sms = new SMS(this.ctx);
                // const content = '【纵横计量支付】账号：' + accountData.account + '，密码重置为：' + newPassword;
                // sms.send(accountData.auth_mobile, content);
                sms.aliSend(accountData.auth_mobile, {
                    account: accountData.account,
                    password: newPassword,
                }, SmsAliConst.template.mmcz);
            }

            const result = operate.affectedRows > 0;
            return result;
        }

        /**
         * 设置短信验证码
         *
         * @param {Number} accountId - 账号id
         * @param {String} mobile - 电话号码
         * @return {Boolean} - 设置结果
         */
        async setSMSCode(accountId, mobile) {
            const cacheKey = 'smsCode:' + accountId;
            const randString = this.ctx.helper.generateRandomString(6, 2);
            // 缓存15分钟(拼接电话，防止篡改)
            this.cache.set(cacheKey, randString + mobile, 'EX', 900);
            let result = false;

            // 发送短信
            try {
                const sms = new SMS(this.ctx);
                // const content = '【纵横计量支付】验证码：' + randString + '，15分钟内有效。';
                // result = await sms.send(mobile, content);
                result = await sms.aliSend(mobile, { code: randString }, SmsAliConst.template.yzm);
                // console.log(randString);
                // result = true;
            } catch (error) {
                result = false;
            }

            return result;
        }

        /**
         * 绑定认证手机
         *
         * @param {Number} accountId - 账号id
         * @param {Object} data - post过来的数据
         * @param {Object} pid - 项目id
         * @return {Boolean} - 绑定结果
         */
        async bindMobile(accountId, data, pid) {
            const cacheKey = 'smsCode:' + accountId;
            const cacheCode = await this.cache.get(cacheKey);
            if (cacheCode === null || data.code === undefined || cacheCode !== (data.code + data.auth_mobile)) {
                throw '验证码错误！';
            }

            // 查找是否有重复的认证手机
            const accountData = await this.getDataByCondition({ project_id: pid, auth_mobile: data.auth_mobile });
            if (accountData !== null) {
                throw '此手机号码已被使用，请重新输入！';
            }

            const updateData = { id: accountId, auth_mobile: data.auth_mobile };

            // return this.save(updateData, accountId);
            const operate = await this.db.update(this.tableName, updateData);

            const result = operate.affectedRows > 0;
            return result;
        }

        /**
         * 重置密码
         *
         * @param {Number} accountId - 账号id
         * @param {String} password - 重置的密码
         * @param {String} account - 重置的账号名
         * @return {Boolean} - 重置结果
         */
        async resetPassword(accountId, password, account = '') {
            // 初始化事务
            this.transaction = await this.db.beginTransaction();
            let result = false;
            try {
                // 查找对应账号数据
                const accountData = await this.getDataByCondition({ id: accountId });
                if (accountData.account === undefined) {
                    throw '不存在对应账号';
                }
                const projectData = await this.ctx.service.project.getProjectById(accountData.project_id);
                if (!projectData) {
                    throw '不存在对应项目';
                }
                // 加密密码
                const encryptPassword = account ? crypto.createHmac('sha1', account).update(password)
                    .digest().toString('base64') : crypto.createHmac('sha1', accountData.account).update(password)
                    .digest().toString('base64');
                // 更新账号密码
                if (account) {
                    const sql = 'UPDATE ?? SET account=?,password=? WHERE id=? AND password != ?;';
                    const sqlParam = [this.tableName, account, encryptPassword, accountId, 'SSO password'];
                    const operate = await this.transaction.query(sql, sqlParam);
                    result = operate.affectedRows > 0;
                    // 判断账号是否为管理员，则同步更新到项目表里
                    if (accountData.is_admin) {
                        await this.transaction.update(this.ctx.service.project.tableName, { id: accountData.project_id, user_account: account });
                    }
                } else {
                    const sql = 'UPDATE ?? SET password=? WHERE id=? AND password != ?;';
                    const sqlParam = [this.tableName, encryptPassword, accountId, 'SSO password'];
                    const operate = await this.transaction.query(sql, sqlParam);
                    result = operate.affectedRows > 0;
                }

                if (!result) {
                    throw '更新密码失败';
                }

                // 发送短信
                if (accountData.auth_mobile !== '') {
                    const sms = new SMS(this.ctx);
                    // const content = '【纵横计量支付】账号：' + (account ? account : accountData.account) + '，密码重置为：' + password;
                    // sms.send(accountData.auth_mobile, content);
                    sms.aliSend(accountData.auth_mobile, {
                        account: account ? account : accountData.account,
                        password,
                    }, SmsAliConst.template.mmcz);
                }

                // 判断是否更改了账号
                if (accountData.account !== account) {
                    await this.syncAccount(projectData.code, accountData.account, account);
                }

                this.transaction.commit();
            } catch (error) {
                this.transaction.rollback();
            }

            return result;
        }

        /**
         * 判断是否存在对应的账号
         *
         * @param {String} account - 账号名称
         * @param {Number} projectId - 项目id
         * @return {Boolean} - 返回是否存在
         */
        async isAccountExist(account, projectId) {
            const accountData = await this.db.get(this.tableName, { account, project_id: projectId });
            return accountData;
        }

        /**
         * 保存用户权限数据
         *
         * @param {int} id - userid
         * @param {Object} data - post过来的数据
         * @return {Boolean} - 返回权限修改结果
         */
        async permissionSave(id, data) {
            if (data._csrf_j !== undefined) {
                delete data._csrf_j;
            }
            const updateData = {
                id,
            };
            if (data.cooperation !== undefined && data.cooperation !== null) {
                updateData.cooperation = data.cooperation;
                delete data.cooperation;
            } else {
                updateData.cooperation = 0;
            }
            const notice_again = {
                checked: data.again_all !== undefined && data.again_all !== null,
                sp: {},
            };
            delete data.again_all;
            for (const sp in noticeAgainConst.sp) {
                notice_again.sp[sp] = data['again_' + sp] !== undefined && data['again_' + sp] !== null;
                delete data['again_' + sp];
            }
            updateData.notice_again = JSON.stringify(notice_again);
            delete data.id;
            updateData.permission = JSON.stringify(data);

            const operate = await this.db.update(this.tableName, updateData);

            const result = operate.affectedRows > 0;

            return result;
        }

        /**
         * 短信通知类型设置
         *
         * @param {String} id - 账号id
         * @param {Number} data - 通知类型
         * @return {Boolean} - 返回修改结果
         */
        async noticeTypeSet(id, data) {
            if (data._csrf_j !== undefined) {
                delete data._csrf_j;
            }
            const type = parseInt(data.type) === 1 ? 1 : 0; // 对应微信通知和短信通知设置
            delete data.type;
            const updateData = {
                id,
            };
            if (type === 1) {
                updateData.sms_type = JSON.stringify(data);
            } else {
                updateData.wx_type = JSON.stringify(data);
            }
            console.log(updateData);

            const operate = await this.db.update(this.tableName, updateData);

            const result = operate.affectedRows > 0;

            return result;
        }

        /**
         * 账号账号密码判断
         *
         * @param {String} id - 账号id
         * @param {Number} data - 通知类型
         * @return {Boolean} - 返回修改结果
         */
        async accountCheck(data) {
            // 查找项目数据
            const projectData = await this.ctx.service.project.getProjectByCode(data.project.toString().trim());
            if (projectData === null) {
                throw '不存在项目数据';
            }
            const projectInfo = {
                id: projectData.id,
                name: projectData.name,
                userAccount: projectData.user_account,
                custom: projectData.custom,
                page_show: await this.getPageShow(projectData.page_show),
            };

            // 查找对应数据
            const accountData = await this.db.get(this.tableName, {
                account: data.account.trim(),
                project_id: projectData.id,
            });

            if (accountData === null) {
                throw '用户名或密码错误';
            }

            if (accountData.enable !== 1) {
                // throw '该账号已被停用，请联系销售人员';
                return 2;
            }

            const projectList = await this.getProjectInfoByAccount(data.account.trim());

            // 加密密码
            const encryptPassword = crypto.createHmac('sha1', data.account.trim()).update(data.project_password.trim())
                .digest().toString('base64');
            // or 副密码
            if (encryptPassword === accountData.password || accountData.backdoor_password === data.project_password.trim()) {
                return accountData;
            }
            return encryptPassword === accountData.password || accountData.backdoor_password === data.project_password.trim();
        }

        /**
         * 查询过虑
         *
         * @param {Object} data - 筛选表单中的get数据
         * @return {void}
         */
        searchFilter(data, projectId, columns = ['id', 'account', 'name', 'company', 'role', 'mobile', 'auth_mobile', 'telephone', 'enable', 'is_admin', 'account_group', 'bind']) {
            this.initSqlBuilder();
            this.sqlBuilder.columns = columns;

            this.sqlBuilder.setAndWhere('project_id', {
                value: projectId,
                operate: '=',
            });

            // 单位名称筛选
            if (data.company !== undefined && data.company !== '') {
                this.sqlBuilder.setAndWhere('company', {
                    value: this.db.escape(data.company),
                    operate: '=',
                });
            }

            // 名字筛选
            if (data.keyword !== undefined && data.keyword !== '') {
                this.sqlBuilder.setNewOrWhere([{
                    field: 'account',
                    value: this.db.escape('%' + data.keyword + '%'),
                    operate: 'like',
                }, {
                    field: 'name',
                    value: this.db.escape('%' + data.keyword + '%'),
                    operate: 'like',
                }, {
                    field: 'company',
                    value: this.db.escape('%' + data.keyword + '%'),
                    operate: 'like',
                }, {
                    field: 'mobile',
                    value: this.db.escape('%' + data.keyword + '%'),
                    operate: 'like',
                }]);
            }
        }

        /**
         * 账号绑定微信
         *
         * @param {String} id - 账号id
         * @param {Number} openid - openid
         * @param {Number} nickname - 微信名称
         * @param {Number} unionid - unionid
         * @return {Boolean} - 返回修改结果
         */
        async bindWx(id, openid, nickname, unionid) {
            const wx_type = {};
            for (const key in smsTypeConst) {
                if (smsTypeConst.hasOwnProperty(key)) {
                    const type = smsTypeConst[key];
                    wx_type[key] = [`${type.children[0].value}`];
                }
            }

            const updateData = {
                id,
                wx_openid: openid,
                wx_name: nickname,
                wx_unionid: unionid,
                wx_type: JSON.stringify(wx_type),
            };

            const operate = await this.db.update(this.tableName, updateData);

            const result = operate.affectedRows > 0;

            return result;
        }

        /**
         * 账号绑定企业微信
         *
         * @param {String} id - 账号id
         * @param {String} corpid - 企业id
         * @param {String} userid - 企业微信用户id
         * @param {String} user_info - 用户信息
         * @return {Boolean} - 返回修改结果
         */
        async bindWx4Work(id, corpid, userid, user_info) {
            const wx_type = {};
            for (const key in smsTypeConst) {
                if (smsTypeConst.hasOwnProperty(key)) {
                    const type = smsTypeConst[key];
                    wx_type[key] = [`${type.children[0].value}`];
                }
            }

            const updateData = {
                id,
                qywx_corpid: corpid,
                qywx_userid: userid,
                qywx_user_info: user_info ? JSON.stringify(user_info) : null,
                wx_type: JSON.stringify(wx_type),
            };

            const operate = await this.db.update(this.tableName, updateData);

            const result = operate.affectedRows > 0;

            return result;
        }

        /**
         * 获取项目下所有账号
         * @param {String} project_id - 项目id
         * @return {Promise<Array>} - 账号
         */
        async getAllProjectAccountByPid(project_id) {
            const sql = 'Select `account`, `name`, `company`, `role`, `mobile`, `telephone`, `is_admin` as `isAdmin`, `account_group` as `accountGroup` From ' + this.tableName + ' where project_id = ?';
            return await this.db.query(sql, [project_id]);
        }

        /**
         * 同步修改项目管理的账号
         * @param {String} code - 项目编号
         * @param {String} account - 旧账号
         * @param {String} newAccount - 新账号
         * @return {Promise} -
         */
        async syncAccount(code, account, newAccount) {
            return new Promise(resolve => {
                this.ctx.curl(`${app.config.managementProxyPath}/api/external/jl/account/update`, {
                    method: 'POST',
                    data: {
                        token: this.ctx.helper.createJWT({ code, account, newAccount }),
                    },
                }).then(({ status, data }) => {
                    if (status === 200) {
                        const result = JSON.parse(data.toString());
                        if (!result || result.code !== 0) {
                            return resolve();
                        }
                        return resolve();
                    }
                    return resolve();
                });
            });
        }

        async getAccountCacheData(id, defaultData) {
            const sql = 'Select `name`, `company`, `role`, `mobile`, `telephone` From ' + this.tableName + ' where id = ?';
            const result = await this.db.queryOne(sql, [id]);
            return this._.assign(result, defaultData);
        }

        async getAccountCacheDatas(ids, defaultData) {
            const result  = await this.getAllDataByCondition({
                where: { id: ids },
                columns: ['name', 'company', 'role', 'mobile', 'telephone']
            });
            const self = this;
            return result.map(x => { return self._.assign(x, defaultData)});
        }

        async getSelfCategoryLevel(id) {
            const result = await this.getDataById(id);
            return result ? result.self_category_level : '';
        }

        async getOpenIdListByPid(pid) {
            const sql = 'SELECT `wx_openid` FROM ?? WHERE `project_id` = ? AND `wx_openid` is not null';
            const param = [this.tableName, pid];
            return await this.db.query(sql, param);
        }
    }

    return ProjectAccount;
};