denet-node-server/model/db/account_info_db.js

const logger = require('../logger')
var { db_config } = require('../../config/config.js')
const utils = require('../utils.js')
var mysql = require('mysql');
var port = db_config.mysql.PORT
var host = db_config.mysql.HOST
var username = db_config.mysql.USERNAME
var password = db_config.mysql.PASSWORD
var database = db_config.mysql.DATABASE_MY_NODE
var open_pool = db_config.mysql.OPEN_POOL
var pool_size = db_config.mysql.POOL_SIZE

var opts = {
    host: host,
    user: username,
    password: password,
    port: port,
    database: database,
}
var MYSQL_INSTANCE = null;

//程序启动默认创建一个 mysql 连接
createDefMysqlConnect();
// 开始创建
function createDefMysqlConnect() {
    disDefMysqlConnect();
    logger.log('createDefMysqlConnect', database)
    if (open_pool) {//创建连接池
        opts.connectionLimit = pool_size
        MYSQL_INSTANCE = mysql.createPool(opts);
        addConnEvent()
    } else {//创建单连接
        MYSQL_INSTANCE = mysql.createConnection(opts);
        addConnEvent()
        MYSQL_INSTANCE.connect();
    }
    return MYSQL_INSTANCE;
}
//关闭 mysql
function disDefMysqlConnect() {
    if (MYSQL_INSTANCE) {
        if (open_pool) {
            MYSQL_INSTANCE.end();
        } else {
            MYSQL_INSTANCE.end();
        }
        MYSQL_INSTANCE = null;
        logger.debug('disDefMysqlConnect')
    }
}

function getMySqlInstance() {
    if (MYSQL_INSTANCE) return MYSQL_INSTANCE
    return createDefMysqlConnect();
}

function addConnEvent() {
    if (!MYSQL_INSTANCE) return
    if (open_pool) {
        MYSQL_INSTANCE.on('acquire', function (connection) {
            logger.log('Connection %d acquired', connection.threadId);
        });

        MYSQL_INSTANCE.on('connection', function (connection) {
            logger.log('mysql connection', connection.threadId);
        });
        MYSQL_INSTANCE.on('enqueue', function () {
            logger.log('Waiting for available connection slot');
        });

        MYSQL_INSTANCE.on('release', function (connection) {
            logger.log('Connection %d released', connection.threadId);
        });
    } else {
        // logger.log('connected to mysql ps=', db_config.mysql)
        MYSQL_INSTANCE.on('connect', () => {
            logger.log('connected to mysql')
        })

        MYSQL_INSTANCE.on('error', function (err) {
            logger.error('mysql  Error =>', err);
        });


        MYSQL_INSTANCE.on('restart', function () {
            logger.error('mysql  restart =>');
        });
    }
}


async function getAccountBalances_(opts) {
    logger.log('getAccountBalances', opts)
    var sql_main = 'select * from '
    var sql_table_name = ' user_banlance '
    var sql_where = ' WHERE '
    var sql_where_name = ' id= ? '
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    var query_account_balances_sql = new_sql

    var query_account_balances_params = [withdrawId]

    return new Promise((resolve) => {
        getMySqlInstance().getConnection(function (err, connection) {
            if (err) {
                logger.error('getAccountBalances error', err)
                logger.error('getAccountBalances sql', create_withdraw_sql)
                resolve(null);
                return;
            }
            connection.query(
                query_account_balances_sql, query_account_balances_params,
                function selectCb(error, results) {
                    if (error) {
                        logger.error('create_collect_coins_task', error, query_account_balances_sql, query_account_balances_params)
                        resolve(null);
                        return;
                    }
                    logger.log('create_collect_coins_task ret=', error, results);
                    //用完当前连接需要释放,归还给连接池
                    connection.release();
                    resolve({
                        err: error,
                        results: results
                    });
                }
            );
        })
    })
}

async function getAccountTransactions_(opts) {
    logger.log('getAccountTransactions', opts)
    var sql_main = 'select * from '
    var sql_table_name = ' user_transaction_log '
    var sql_where = ' WHERE '
    var sql_where_name = ' usr_to_address=? AND block_num>=?'
    var query_account_transactions_params = [opts.address, opts.from_block ? opts.from_block : 1]
    if (opts.type == 'native') {
        sql_where_name = ' usr_to_address=? AND block_num>=? AND token_address=? '
        query_account_transactions_params = [opts.address, opts.from_block ? opts.from_block : 1, '0x0000000000000000000000000000000000000000']
    } else if (opts.type == 'token') {
        sql_where_name = ' usr_to_address=? AND block_num>=? AND token_address!=? '
        query_account_transactions_params = [opts.address, opts.from_block ? opts.from_block : 1, '0x0000000000000000000000000000000000000000']
    } else if (opts.type == 'hash') {
        sql_where_name = ' trx_hash=? '
        query_account_transactions_params = [opts.transaction_hash, opts.from_block ? opts.from_block : 1, '0x0000000000000000000000000000000000000000']
    }
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    var query_account_transactions_sql = new_sql

    return new Promise((resolve) => {
        getMySqlInstance().getConnection(function (err, connection) {
            if (err) {
                logger.error('getAccountTransactions_', err)
                logger.error('getAccountTransactions_', query_account_transactions_sql)
                resolve(null);
                return;
            }
            connection.query(
                query_account_transactions_sql, query_account_transactions_params,
                function selectCb(error, results) {
                    if (error) {
                        logger.error('getAccountTransactions_', error, query_account_transactions_sql, query_account_transactions_params)
                        resolve(null);
                        return;
                    }
                    //用完当前连接需要释放,归还给连接池
                    connection.release();
                    resolve({
                        results: results
                    });
                }
            );
        })
    })
}

async function getAccountBalances(opts) {

}


async function getAccountTransactions(opts) {
    var ret = await getAccountTransactions_(opts);
    if (ret && ret.results) {
        if (ret.results && Array.isArray(ret.results) && ret.results.length > 0) {
            try {
                var results = []
                ret.results.forEach(element => {
                    var isNativeTrans = element.token_address == '0x0000000000000000000000000000000000000000'
                    element.type = isNativeTrans == true ? 'native' : 'token'
                    element.gas = element.gas.toString()
                    element.gas_price = element.gas_price.toString()
                    try {
                        element.value = utils.scientificNotationToString(element.value).toString()
                    } catch (error) {
                        element.value = element.value.toString();
                    }
                    logger.log('getAccountTransactions_ element:', element)
                    results.push({
                        type: isNativeTrans == true ? 'native' : 'token',
                        from_address: element.usr_from_address,
                        to_address: element.usr_to_address,
                        token_address: isNativeTrans == false ? element.token_address : null,
                        block_number: element.block_num.toString(),
                        value: element.value,
                        gas: element.gas,
                        gas_price: element.gas_price,
                        block_timestamp: utils.getTimestampToDate(element.block_tm*1000),
                        trx_hash: element.trx_hash,
                        responseType:'yqcx'
                    })
                });
                ret.results = results
            } catch (error) {

            }
            logger.log('getAccountTransactions_ respose:', ret)
            return {
                code: 0,
                data: {
                    total: ret.results.length,
                    results: ret.results
                },
                errMsg: null,
            }
        }
    }
    return {
        code: 0,
        data: {
            total: 0,
            results: []
        },
        errMsg: null,
    }
}

module.exports = {
    getAccountBalances,
    getAccountTransactions,
}