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');
const redis = require("./redis_db")  //导入 db.js
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_balance '
    var sql_where = ' WHERE '
    var sql_where_name = ' usr_address=?  AND chain=? ORDER BY update_tm DESC '
    var query_account_balances_params = [opts.address, opts.chain]
    if (opts.type == 'native') {
        sql_where_name = ' usr_address=? AND token_address=?  AND chain=? ORDER BY update_tm DESC '
        query_account_balances_params = [opts.address, '0x0000000000000000000000000000000000000000', opts.chain]
    } else if (opts.type == 'token') {
        sql_where_name = ' usr_address=? AND token_address!=?  AND chain=? ORDER BY update_tm DESC '
        query_account_balances_params = [opts.address, '0x0000000000000000000000000000000000000000', opts.chain]
    }
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    var query_account_balances_sql = new_sql


    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('getAccountBalances_', error, query_account_balances_sql, query_account_balances_params)
                        resolve(null);
                        return;
                    }
                    logger.log('getAccountBalances_ ret=', error, results);
                    //用完当前连接需要释放,归还给连接池
                    connection.release();
                    resolve({
                        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>=? AND chain=? '
    var query_account_transactions_params = [opts.address, opts.from_block ? opts.from_block : 1, opts.chain]
    if (opts.type == 'native') {
        sql_where_name = ' usr_to_address=? AND block_num>=? AND token_address=? AND chain=?  '
        query_account_transactions_params = [opts.address, opts.from_block ? opts.from_block : 1, '0x0000000000000000000000000000000000000000', opts.chain]
    } else if (opts.type == 'token') {
        sql_where_name = ' usr_to_address=? AND block_num>=? AND token_address!=? AND chain=?  '
        query_account_transactions_params = [opts.address, opts.from_block ? opts.from_block : 1, '0x0000000000000000000000000000000000000000', opts.chain]
    } else if (opts.type == 'hash') {
        sql_where_name = ' trx_hash=? AND chain=? '
        query_account_transactions_params = [opts.transaction_hash, opts.chain]
    } else if (opts.type == 'only_hash') {
        sql_where_name = ' trx_hash=? '
        query_account_transactions_params = [opts.transaction_hash]
    }
    var new_sql = sql_main.concat(sql_table_name, sql_where, sql_where_name);
    var query_account_transactions_sql = new_sql

    logger.log('getAccountTransactions new_sql', new_sql)
    logger.log('getAccountTransactions query_account_transactions_params', query_account_transactions_params)
    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) {
    var ret = await getAccountBalances_(opts);
    var results = []
    if (ret && ret.results) {
        if (ret.results && Array.isArray(ret.results) && ret.results.length > 0) {
            for (let index = 0; index < ret.results.length; index++) {
                const element = ret.results[index];
                if (element.token_address == '0x0000000000000000000000000000000000000000') {
                    //
                    results.push({
                        type: 'native',
                        balance: utils.scientificNotationToString(element.balance).toString(),
                        responseType: 'yqcx'
                    })
                } else {
                    // {
                    //     token_address: '0x03716f32f72c692a0b355fa04639669e3341b94e',
                    //     name: 'BF_BSC_XXXXXX',
                    //     symbol: 'BSC_BF_6X',
                    //     logo: null,
                    //     thumbnail: null,
                    //     decimals: 18,
                    //     balance: '1000000000000000000000'
                    //   }
                    var decimals = 1
                    try {
                        // decimals = await redis.readRedis('REDIS_ERC20_CONTRACT_DECIMALS_' + element.token_address.toLowerCase())
                        decimals = await redis.readAppendRedis('REDIS_ERC20_CONTRACT_DECIMALS', opts.chain, element.token_address.toLowerCase())
                    } catch (error) {

                    }
                    results.push({
                        type: 'token',
                        token_address: element.token_address,
                        balance: utils.scientificNotationToString(element.balance).toString(),
                        decimals: parseInt(decimals),
                        responseType: 'yqcx'
                    })
                }
            }
        }
    }
    return {
        code: 0,
        data: {
            total: results.length,
            results: results
        }
    }
}

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,
}