CloudSQLAdminPanel/routes/userList.js

const express = require('express');
const router = express.Router();
const crypto = require('crypto');
const events = require('events');
const db = require('../common/mysqlConnection');

/**
 * 获取用户列表
 * @param {*} req 
 * @param {*} res 
 * @param {function} next 
 */
function getUserList(req, res, next) {
    if (req.session.signinStatus) {
        let emitter = new events.EventEmitter(),
            pageIndex = req.query.pageIndex - 1,
            orderBy = req.query.orderBy,
            searchValue = req.query.search;

        emitter.on('ok', (results, count) => {
            res.send({
                status: 'success',
                info: '获取成功',
                data: {
                    'results': results,
                    'count': count
                }
            });
        });

        emitter.on('err', (err, errInfo) => {
            res.json({
                status: 'error',
                info: errInfo,
                error: err
            });
        });

        emitter.on('selectUserInfoFromUserTable', () => {
            switch (orderBy) {
                case 'userId':
                    orderBy = 'uuid';
                    break;

                case 'userNickName':
                    orderBy = 'account';
                    break;

                case 'createDate':
                    orderBy = 'createDate';
                    break;

                default:
                    orderBy = 'uuid';
                    break;
            }

            if (pageIndex > 0) {
                pageIndex = parseInt(pageIndex, 10) * 20;
            } else {
                pageIndex = 0;
            }
            
            //定义SQL语句查询
            let pool = db.pool.userInfoDatabasePool,
                sqlString = 'SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT account, uuid, dbpassword, createDate FROM user_test ORDER BY ? ASC) a LIMIT ?, 20; SELECT FOUND_ROWS()',
                value = [orderBy, pageIndex];

            if (searchValue) {
                sqlString = 'SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT account, uuid, dbpassword, createDate FROM user_test WHERE account LIKE ? OR uuid LIKE ? OR createDate LIKE ? ORDER BY ? ASC) a LIMIT ?, 20; SELECT FOUND_ROWS()';
                searchValue = '%' + searchValue + '%';
                value = [searchValue, searchValue, searchValue, orderBy, pageIndex];
            }
    
            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '数据库查询出错');
                } else {
                    emitter.emit('ok', results[0], results[1][0]['FOUND_ROWS()']);
                }
            });
        });

        emitter.emit('selectUserInfoFromUserTable');
    } else {
        res.send({
            status: 'signout',
            info: '未登录',
            url: '#/signin'
        });
    }
}

/**
 * 获取用户总数
 * @param {*} req 
 * @param {*} res 
 * @param {function} next 
 */
function getUserCount(req, res, next) {
    if (req.session.signinStatus) {
        let emitter = new events.EventEmitter();

        emitter.on('ok', (results) => {
            res.send({
                status: 'success',
                info: '获取成功',
                data: {
                    'results': results
                }
            });
        });

        emitter.on('err', (err, errInfo) => {
            res.json({
                status: 'error',
                info: errInfo,
                error: err
            });
        });

        emitter.on('selectUserCountFromUserTable', () => {

            //定义SQL语句查询
            let pool = db.pool.userInfoDatabasePool,
                sqlString = 'SELECT COUNT(1) FROM user_test',
                value = null;

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '数据库查询出错');
                } else {
                    emitter.emit('ok', results[0]['COUNT(1)']);
                }
            });
        });

        emitter.emit('selectUserCountFromUserTable');
    } else {
        res.send({
            status: 'signout',
            info: '未登录',
            url: '#/signin'
        });
    }
}

/**
 * 获取用户拥有的数据库
 * @param {*} req 
 * @param {*} res 
 * @param {function} next 
 */
function getUserOwnDb(req, res, next) {
    if (req.session.signinStatus) {
        let emitter = new events.EventEmitter(),
            uuid = req.query.userId;

        emitter.on('ok', (results) => {
            res.send({
                status: 'success',
                info: '获取成功',
                data: {
                    'results': results
                }
            });
        });

        emitter.on('err', (err, errInfo) => {
            res.json({
                status: 'error',
                info: errInfo,
                error: err
            });
        });

        emitter.on('selectDbOwnInfoFromDbOwnTable', () => {
            //定义SQL语句查询
            let pool = db.pool.userInfoDatabasePool,
                sqlString = 'SELECT dbName FROM dbOwn_test WHERE dbOwnUuid = ?',
                value = [uuid];

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '数据库查询出错');
                } else {
                    emitter.emit('ok', results);
                }
            });
        });

        emitter.emit('selectDbOwnInfoFromDbOwnTable');
    } else {
        res.send({
            status: 'signout',
            info: '未登录',
            url: '#/signin'
        });
    }
}

/**
 * 删除用户
 * @param {*} req 
 * @param {*} res 
 * @param {function} next 
 */
function removeUser(req, res, next) {
    if (req.session.signinStatus) {
        let emitter = new events.EventEmitter(),
            uuid = req.body.userId,
            userNickName = req.body.userNickName;

        emitter.on('ok', (results) => {
            res.send({
                status: 'success',
                info: '删除成功'
            });
        });

        emitter.on('err', (err, errInfo) => {
            res.json({
                status: 'error',
                info: errInfo,
                error: err
            });
        });

        emitter.on('deleteFromDbOwnTable', () => {
            //定义SQL语句查询
            let pool = db.pool.userInfoDatabasePool,
                sqlString = 'DELETE FROM dbOwn_test WHERE dbOwnUuid = ?',
                value = [userNickName];

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '删除用户信息表记录时出错');
                } else {
                    emitter.emit('ok');
                }
            });
        });

        emitter.on('deleteFromUserTable', () => {
            //定义SQL语句查询
            let pool = db.pool.userInfoDatabasePool,
                sqlString = 'DELETE FROM user_test WHERE uuid = ?',
                value = [uuid];

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '用户数据表删除数据出错');
                } else {
                    emitter.emit('deleteFromDbOwnTable');
                }
            });
        });

        emitter.on('dropUser', () => {
            //定义SQL语句查询
            let pool = db.pool.userDatabasePool,
                sqlString = 'USE mysql; DELETE FROM user WHERE User = ?',
                value = [uuid];

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '用户数据表删除数据出错');
                } else {
                    emitter.emit('deleteFromUserTable');
                }
            });
        });

        emitter.on('dropDatabase', (results) => {
            let pool = db.pool.userDatabasePool,
                value = null;

            results.forEach((item, index) => {
                let sqlString = 'DROP DATABASE ' + item.dbName;

                db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                    if (err) {
                        emitter.emit('err', err.err, '删除数据库时出错');
                    } else {
                        emitter.emit('dropUser');
                    }
                });
            }, this);
        });

        emitter.on('selectDbOwnInfoFromDbOwnTable', () => {
            //定义SQL语句查询
            let pool = db.pool.userInfoDatabasePool,
                sqlString = 'SELECT dbName FROM dbOwn_test WHERE dbOwnUuid = ?',
                value = [uuid];

            db.queryArgs(pool, sqlString, value, (err, results, fields) => {
                if (err) {
                    emitter.emit('err', err.err, '数据库查询出错');
                } else {
                    emitter.emit('dropDatabase', results);
                }
            });
        });

        emitter.emit('selectDbOwnInfoFromDbOwnTable');
    } else {
        res.send({
            status: 'signout',
            info: '未登录',
            url: '#/signin'
        });
    }
}

router.get('/', getUserList);
router.get('/getUserCount', getUserCount);
router.get('/getUserOwnDb', getUserOwnDb);
router.post('/removeUser', removeUser);



module.exports = router