def proccess(self, rpcconn):
#1.从t_htdf_active_addr获取所有的地址
strSql = f"""SELECT address FROM t_htdf_active_addrs WHERE `balance` > 0.001 ORDER BY `balance` DESC LIMIT 100;"""
sqlRet = sql.run(strSql)
addrs = []
for item in sqlRet:
if "address" in item:
addrs.append(item["address"])
#2.实时获取所有地址的账户信息( addr, balance, account_no, sequence )
retData = []
for strAddr in addrs:
retInfo = USDP_GetAccountInfo.account_info(rpcconn, strAddr)
if not retInfo: #如果账户一毛钱不剩, 返回None
strSql = """DELETE FROM t_htdf_active_addrs WHERE address='{0}';""".format(
strAddr)
logging.info("sql: {}".format(strSql))
sqlRet = sql.run(strSql)
continue
retData.append(retInfo)
#对返回数据进行排序
sortedRet = sorted(retData,
key=lambda keys: Decimal(keys['balance']),
reverse=True)
#3.返回数据
return sortedRet
def QueryBalanceProc(addrs):
for strAddr in addrs:
print(type(strAddr))
strBalance = self.GetBalanceInEther(strAddr) #获取余额
if Decimal(strBalance) > 0.001:
strSql = """REPLACE INTO tb_etc_series_active_addrs(`symbol`,`address`, `balance`) VALUES('ETC', '{0}', {1})""".format(
strAddr, strBalance)
print("sql: {}".format(strSql))
sqlRet = sql.run(strSql)
# #检查代币余额
# for contract_addr in ERC20_CONTRACTS_LIST:
# strSymbol = self.eth_erc20_symbol(contract_addr)
# strBalance = self.eth_erc20_balanceOf(contract_addr, strAddr, True)
# if Decimal(strBalance) > 0.001:
# strSql = """REPLACE INTO tb_eth_series_active_addrs(`symbol`,`address`, `balance`) VALUES('{}', '{}', {})""".format(strSymbol, strAddr, strBalance)
# print("sql: {}".format(strSql))
# sqlRet = sql.run(strSql)
# else:
# strSql = """DELETE FROM tb_eth_series_active_addrs WHERE `symbol`='{}' and `address`='{}'""".format(strSymbol, strAddr)
# print("sql: {}".format(strSql))
# sqlRet = sql.run(strSql)
strSql = """DELETE FROM t_etc_patch_addrs WHERE address='{0}';""".format(
strAddr)
print("sql: {}".format(strSql))
sqlRet = sql.run(strSql)
pass
def StartScan(self):
nLatestestBlock = self.__GetLatestBlockNumber()
nStartBlock = self.__GetScanStartBlock('trx')
# for n in range(nStartBlock, nLatestestBlock + 1):
n = nStartBlock
while n < nLatestestBlock + 1:
nTmpEnd = n + self.N_BLOCK_COUNT_EACH_TIME if n + self.N_BLOCK_COUNT_EACH_TIME <= nLatestestBlock + 1 else nLatestestBlock + 1
txs = self._GetDepositTrxsFromBlockRange(n, nTmpEnd)
for tx in txs:
self._PushTxIntoDB(tx)
# 保存本次扫描的结束区块高度
strSql = """update t_scan_start set start_block={0} where coin_type='{1}';""".format(
nTmpEnd - 1, 'trx')
logging.info("sql : {}".format(strSql))
sql.run(strSql)
n = nTmpEnd
pass
def _UpdateBalanceAndScannedBlockIntoDB(self, straccount_name: str,
n_scanned_block_num: int,
strbalance: str):
accname = straccount_name.strip()
balance = strbalance.strip()
sqlstr = """UPDATE `tb_eos_scan_records` SET `balance`='{}', `scanned_block`={} WHERE `account`='{}';"""\
.format(balance, n_scanned_block_num, accname)
print("sql: {}".format(sqlstr))
sql.run(sqlstr)
def __RefreshHRC20TokenBalance(self, contract_addr, address, symbol):
try:
strbalance = self.getHRC20TokenBalance(contract_addr=contract_addr,
address=address)
strsql = f"""REPLACE INTO tb_hrc20_active_addrs(`symbol`,`address`, `balance`) VALUES('{symbol}', '{address}', {strbalance});"""
print(strsql)
sql.run(strsql)
pass
except Exception as e:
print(f'__RefreshHRC20TokenBalance error {str(e)}')
def GetTxDataFromDB(self, nBegin: int, nEnd: int, symbol: str) -> list:
txRet = []
if symbol.upper() == 'HTDF':
strSql = """SELECT txdata FROM t_htdf_charge WHERE height >= {0} and height <= {1} LIMIT 500;""".format(
nBegin, nEnd)
sqlRet = sql.run(strSql)
if not isinstance(sqlRet, list):
return []
for item in sqlRet:
txListStr = item["txdata"]
txList = json.loads(txListStr)
txRet.extend(txList)
# 如果没有 symbol字段则加上 symbol 字段
for item in txRet:
if 'symbol' not in item:
item['symbol'] = 'HTDF'
return txRet
else: # HRC20
# TODO: 将 t_htdf_charge 和 tb_hrc20_deposit 表结构重构, 合并为一张表, 删除t_htdf_charge
strsql = f"""select * from tb_hrc20_deposit WHERE `symbol`='{symbol}' and `block_number`>={nBegin} and `block_number`<={nEnd} LIMIT 1000;"""
logging.info(strsql)
sqlret = sql.run(strsql)
if not isinstance(sqlret, list):
return txRet
for item in sqlret:
tx = {}
tx['symbol'] = item['symbol']
tx["txid"] = item['txid']
tx["from"] = item["from"]
tx["to"] = item["to"]
# tx["nonce"] = item['nonce']
tx["blocktime"] = item['block_time']
tx["confirmations"] = item['confirmations']
tx["blockNumber"] = item['block_number']
tx["value"] = item['value']
if item['symbol'].upper() in g_special_token_list:
tx['symbol'] = 'HRC20-' + item['symbol'].upper()
txRet.append(tx)
return txRet
pass
def GetTxDataFromDB(self, nBegin, nEnd, symbol='ETC'):
try:
if not (isinstance(nBegin, int) and
(isinstance(nEnd, int) or isinstance(nEnd, int))):
print("nBegin or nEnd is not int type.")
return []
txRet = []
# strSql = """SELECT txdata FROM t_etc_charge WHERE height >= {0} and height <= {1};""".format(nBegin, nEnd)
strSql = """SELECT * FROM tb_etc_series_deposit WHERE symbol='{}' and block_number>{} and block_number<{}; """.format(
symbol, nBegin, nEnd)
print("sql : {}".format(strSql))
sqlRet = sql.run(strSql)
if not isinstance(sqlRet, list):
return None
for item in sqlRet:
tx = {}
tx['symbol'] = item['symbol']
tx["txid"] = item['txid']
tx["from"] = item["from"]
tx["to"] = item["to"]
tx["nonce"] = item['nonce']
tx["blocktime"] = item['block_time']
tx["confirmations"] = item['confirmations']
tx["blockNumber"] = item['block_number']
tx["value"] = item['value']
txRet.append(tx)
return txRet
except Exception as e:
print("GetTxDataInfoDB(nBegin, nEnd, symbol):", e)
return None
pass
def _GetBalanceAndScannedBlockFromDB(self, account_name: str):
accname = account_name.strip()
sqlstr = """SELECT `account`,`balance`,`scanned_block` FROM `tb_eos_scan_records` WHERE `account`='{}'; """.format(
accname)
sqlret = sql.run(sqlstr)
return sqlret[0]['account'], sqlret[0]['balance'], int(
sqlret[0]['scanned_block'])
def GetExchangeUserAddress(strTbName):
sqlRet = sql.run("""SELECT address FROM {0};""".format(strTbName))
addrs = set()
for item in sqlRet:
if 'address' not in item: continue
addrs.add(item['address'])
return list(addrs)
def __GetTxDataFromDB(self, nBegin: int, nEnd: int):
if not (isinstance(nBegin, int) and
(isinstance(nEnd, int) or isinstance(nEnd, int))):
print("nBegin or nEnd is not int type.")
return []
try:
txRet = []
strSql = """SELECT txdata FROM {} WHERE height >= {} and height <= {};""".format(
self.__strTbNameCharge, nBegin, nEnd)
print(strSql)
sqlRet = sql.run(strSql)
#print(sqlRet)
if not isinstance(sqlRet, list):
return None
for item in sqlRet:
txListStr = item["txdata"]
txList = json.loads(txListStr)
txRet.extend(txList)
return txRet
except Exception as e:
print("GetTxDataInfoDB(nBegin, nEnd):", e)
return None
pass
def _GetTxDataFromDB(self, n_start_block_num : int, symbol='EOS' ):
strsql = """SELECT * FROM tb_eos_deposit WHERE symbol='{}' and block_number>={} LIMIT 100;"""\
.format(symbol, n_start_block_num)
logging.info("sql : {}".format(strsql))
sqlret = sql.run(strsql)
if not isinstance(sqlret, list):
return []
ret_trxs = []
for trx in sqlret:
tx = {}
tx['symbol'] = symbol
tx['txid'] = trx['txid']
tx['from'] = trx['src_account']
tx['to'] = trx['dst_account']
tx['value'] = trx['amount']
tx['memo'] = trx['memo']
tx['confirmations'] = trx['confirmations']
tx['blockNumber'] = trx['block_number']
tx['blocktime'] = trx['timestamp']
ret_trxs.append( tx )
return ret_trxs
def GetExchangeUserAddress():
sqlRet = sql.run('select address from t_eth_accounts;') #使用ETH地址即可
addrs = []
for item in sqlRet:
if 'address' not in item: continue
addrs.append(item['address'].strip())
return addrs
def get_order_from_db(self, order_id):
import sql
sqlRet = sql.run("select * from tb_eos_broadcast where order_id='{0}';".format(order_id))
if len(sqlRet) == 0: return (False, "", {})
txid = sqlRet[0]['txid']
tx_json = json.loads( sqlRet[0]['tx_json'] )
return (True, txid, tx_json)
def _push_tx_into_db(self, tx: dict):
strSql = """INSERT INTO tb_xmr_deposit(`txid`,`timestamp`,`dst_addr`,`amount`,`symbol`,`confirmations`, `block_number`) """
strSql += f"""VALUES('{tx['txid']}',{tx['timestamp']}, '{tx['dst_addr']}','{tx['amount']}','XMR',{tx['confirmations']}, {tx['blocknumber']}) """
strSql += """ ON DUPLICATE KEY UPDATE `confirmations`={}; """.format(
tx['confirmations'])
logging.info("sql: {} ".format(strSql))
ret = sql.run(strSql)
def get_order_from_db(self, order_id):
import sql
sqlRet = sql.run(
"select * from tb_etc_broadcast where order_id='{0}';".format(
order_id))
if len(sqlRet) == 0: return (False, "")
txid = sqlRet[0]['txid']
return (True, txid)
def QueryBalanceProc(addrs):
for strAddr in addrs:
strBalance = self.getBalance(strAddr) #获取余额
if Decimal(strBalance) > 0.001:
strSql = """REPLACE INTO {}(`address`, `balance`) VALUES('{}', {})""".format(
self.__strTbNameActiveAddrs, strAddr, strBalance)
print("sql: {}".format(strSql))
sqlRet = sql.run(strSql)
else: #金额太小的直接删掉?
strSql = """DELETE FROM {} WHERE address='{}'""".format(
self.__strTbNameActiveAddrs, strAddr)
print("sql: {}".format(strSql))
sqlRet = sql.run(strSql)
strSql = """DELETE FROM {} WHERE address='{}'""".format(
self.__strTbNamePatchAddrs, strAddr)
print("sql: {}".format(strSql))
sqlRet = sql.run(strSql)
pass
def _PushTxIntoDB(self, tx: dict):
strSql = """INSERT INTO tb_trx_deposit(`txid`,`timestamp`,`from`,`to`,`amount`,`symbol`,`confirmations`, `block_number`) """
strSql += f"""VALUES('{tx['txid']}',{tx['timestamp']},'{tx['from']}',
'{tx['to']}','{tx['amount']}','{tx['symbol']}',{tx['confirmations']}, {tx['blocknumber']}) """
strSql += """ ON DUPLICATE KEY UPDATE `confirmations`={}; """.format(
tx['confirmations'])
logging.info("sql: {} ".format(strSql))
sqlRet = sql.run(strSql)
def proccess_HRC20(self, rpcconn, symbol):
# 1.从t_hrc20_active_addr获取所有的地址
strSql = f"""SELECT address FROM tb_hrc20_active_addrs WHERE `symbol`='{symbol}' and `balance` > 0.001 ORDER BY `balance` DESC LIMIT 50;"""
sqlRet = sql.run(strSql)
addrs = []
for item in sqlRet:
if "address" in item:
addrs.append(item["address"])
#2.获取每个地址的信息( account_number, sequence, balances{htdf_balance, hrc20_token_balance }
retdata = []
for address in addrs:
# balances = USDP_GetBalance.get_all_balance(rpcconn=rpcconn, addr=address)
balances = USDP_GetBalance.get_single_token_balance(
rpcconn=rpcconn, addr=address, token_name=symbol)
assert isinstance(balances, dict)
token_symbol = symbol.upper(
) #if symbol.upper() != 'AJC' else 'HRC20-AJC'
if symbol.upper() in g_special_token_list:
token_symbol = 'HRC20-' + token_symbol
if Decimal(balances[token_symbol]) < 0.00001:
logging.info(
f" {address} balance of {symbol} is too small, skipped . "
)
continue
#如果一个账户尚未激活(即从未有过htdf交易) , 但有 HRC20 代币的余额
if Decimal(balances['HTDF']) < 0.21:
acct_info = {
'address': str(address).strip(),
'account_number': '0',
'sequence': '0',
'balances': balances
}
else:
htdf_acct_info = USDP_GetAccountInfo.account_info(
rpcconn, address)
if not htdf_acct_info: # 如果账户一毛钱不剩, 返回None
pass
acct_info = {
'address': htdf_acct_info['address'],
'account_number': htdf_acct_info['account_number'],
'sequence': htdf_acct_info['sequence'],
'balances': balances
}
retdata.append(acct_info)
return retdata
def _GetExDepositAddrsFromDB(self):
try:
sqlstr = """SELECT DISTINCT `address` from `tb_trx_deposit_addrs`;"""
sql_result = sql.run(sqlstr)
addrs = []
for item in sql_result:
if 'address' not in item: continue
addrs.append(item['address'].strip())
return addrs
except Exception as e:
logging.error(" _GetAddresses() error: {}".format(e))
return []
def proccess(self, rpcconn, symbol):
#1.从t_eth_active_addr 表中获取所有活跃地址,按照余额排序
strSql = """SELECT address FROM tb_eth_series_active_addrs WHERE `symbol`='{}' AND `balance` > 0.0001 ORDER BY `balance` DESC LIMIT 100;""".format(
symbol)
sqlRet = sql.run(strSql)
addrs = []
for item in sqlRet:
if "address" in item:
if item['address'] not in addrs: addrs.append(item["address"])
#2.遍历所有地址, 实时查询地址余额
tmpSymbol = 'ERC20-USDT' if symbol == 'USDT' else symbol
return self.QueryBalanceAndNonce(rpcconn, addrs, tmpSymbol)
def __RefreshBalanceIntoDB(self, strAddr):
try:
print("active_addr is : {}".format(strAddr))
strBalance = self.getBalance(strAddr) #获取余额
if Decimal(strBalance) < Decimal("0.00000100"): #余额太小, 从活跃地址表中删除
strSql = """DELETE FROM {} WHERE address='{}';""".format(
self.__strTbNameActiveAddrs, strAddr)
else:
strSql = """REPLACE INTO {}(`address`, `balance`) VALUES('{}', {})""".format(
self.__strTbNameActiveAddrs, strAddr, strBalance)
print("sql: {}".format(strSql))
sqlRet = sql.run(strSql)
except Exception as e:
print(" PustActiveAddrIntoDB error:", e)
def GetTxDataFromDB(self, nBegin, nEnd):
txRet = []
import sql
strSql = """SELECT txdata FROM t_usdp_charge WHERE height >= {0} and height <= {1} LIMIT 100;""".format(nBegin, nEnd)
sqlRet = sql.run(strSql)
if not isinstance(sqlRet, list):
return []
for item in sqlRet:
txListStr = item["txdata"]
txList = json.loads(txListStr)
txRet.extend(txList)
return txRet
def GetMaxBlockNumberFromDB(self):
try:
strSql = """select MAX(height) from t_eth_charge;"""
#print(strSql)
sqlRet = sql.run(strSql)
#print(sqlRet)
if isinstance(sqlRet, list) and len(sqlRet) > 0:
if isinstance(sqlRet[0], dict):
strMaxRet = sqlRet[0][u"MAX(height)"]
if strMaxRet: return int(str(strMaxRet), 10)
return 0
except Exception as e:
print("GetMaxBlockNumberFromDB() error:", e)
return 0
def PushTxDataIntoDB(self, txDatas):
try:
for tx in txDatas:
strSql = """INSERT INTO tb_etc_series_deposit(`txid`,`symbol`,`from`,`to`,`value`,`block_number`,`block_time`,`nonce`,`confirmations`) """
strSql += """ VALUES('{}','{}','{}','{}','{}',{},{},{},{}) """\
.format( tx['txid'], tx['symbol'], tx['from'], tx['to'], tx['value'], tx['blockNumber'] ,tx['blocktime'], tx['nonce'], tx['confirmations'])
strSql += """ ON DUPLICATE KEY UPDATE `confirmations`={}; """.format(
tx['confirmations'])
print("sql: {} ".format(strSql))
sqlRet = sql.run(strSql)
#如果 from地址(归集) , 或to地址 属于交易所, 则放入临时表, 下次扫描会更新其余额
for addr in [tx['from'], tx['to']]:
if addr in self.exUserAddrs:
strSql = """INSERT INTO t_etc_patch_addrs(`address`) VALUES('{}')""".format(
addr)
sqlRet = sql.run(strSql)
return True
except Exception as e:
print("PushTxDataIntoDB( txDatas):", e)
return None
def __PushTxDataIntoDB(self, nHeight, txDatas):
if not isinstance(nHeight, int):
print("nHeight is %s, nHeight is not integer." % (type(nHeight)))
return None
try:
jsonTxStr = json.dumps(txDatas)
#print(jsonTxStr)
strSql = """REPLACE INTO {}(height, txdata) VALUES({}, '{}')""".format(
self.__strTbNameCharge, nHeight, jsonTxStr)
print(strSql)
sqlRet = sql.run(strSql)
return True
except Exception as e:
print("PushTxDataIntoDB(nHeight, txDatas):", e)
return None
def _GetAddresses(self) -> list:
"""
获取地址
:return: [addr1, addr2]
"""
try:
sqlstr = """SELECT DISTINCT `address` FROM `tb_eos_deposit_addrs`;"""
sql_result = sql.run(sqlstr)
addrs = []
for item in sql_result:
if 'address' not in item: continue
addrs.append(item['address'].strip())
return addrs
except Exception as e:
print(" _GetAddresses() error: ", e)
return []
def _GetDepositTxDataFromDB(self,
starttime,
endtime=(1 << 64) - 1) -> list:
try:
if not (isinstance(starttime, int) and isinstance(endtime, int)):
logging.error("nBegin or nEnd is not int type.")
return []
txRet = []
# strSql = """SELECT txdata FROM t_eth_charge WHERE height >= {0} and height <= {1};""".format(nBegin, nEnd)
strSql = """SELECT * FROM tb_xrp_deposit WHERE `timestamp`>={} and `timestamp`<={}; """.format(
starttime, endtime)
logging.info("sql : {}".format(strSql))
# print(strSql)
sqlRet = sql.run(strSql)
# print(sqlRet)
if not isinstance(sqlRet, list):
return []
for item in sqlRet:
tx = {}
tx['currency'] = 'XRP'
tx["txid"] = item['txid']
tx["from"] = item["src_addr"]
tx["to"] = item["dst_addr"] + '_' + item[
'destination_tag'] #由地址和tag拼接
tx['source_tag'] = item['source_tag']
tx['destination_tag'] = item['destination_tag']
tx["sequence"] = item['sequence']
tx["blocktime"] = item['timestamp']
tx["confirmations"] = item['confirmations']
tx["ledger_index"] = item['ledger_index']
tx["value"] = item['delivered_amount']
txRet.append(tx)
return txRet
except Exception as e:
logging.error("GetTxDataInfoDB(nBegin, nEnd): {}".format(e))
return []
def _UpdateActiveBalance(self, addr: str):
account_info = self.api.get_account(address=addr)
if 'balance' in account_info:
decBalance = Decimal(account_info['balance']) / Decimal('1000000')
fmtBalance = decBalance.quantize(Decimal("0.000000"),
getattr(decimal, 'ROUND_DOWN'))
else:
fmtBalance = '0.000000'
strSql = """INSERT INTO `tb_trx_active_addrs`(`symbol`, `address`, `balance`)"""
strSql += f"""VALUES('TRX', '{addr}', {fmtBalance})"""
strSql += """ON DUPLICATE KEY UPDATE `balance`=VALUES(`balance`);"""
logging.info("sql: {} ".format(strSql))
sqlRet = sql.run(strSql)
pass
def __GetScanStartBlock(self, strCoinType):
"""
从数据库中获取币种的 扫描的起始区块
"""
#类型判断
assert (isinstance(strCoinType, str))
#对参数进行检查
#注意sql注入
strType = strCoinType.lower()
sqlRet = sql.run(
"""SELECT start_block FROM t_scan_start WHERE coin_type='{0}';""".
format(strType))
if len(sqlRet) > 0:
item = sqlRet[0]
if 'start_block' in item:
nRet = int(str(item['start_block']), 10)
return nRet
return 0
def process(self, symbol: str = 'TRX') -> list:
#每次只查部分地址, 以防http超时
strSql = f"""SELECT address FROM tb_trx_active_addrs WHERE `symbol`='{symbol}' AND `balance` >= 1 ORDER BY `balance` DESC LIMIT 25;"""
sqlRet = sql.run(strSql)
addrs = []
for item in sqlRet:
if "address" in item:
if item['address'] not in addrs: addrs.append(item["address"])
trx = Trx(
Tron(full_node=TRON_RPC_URL,
solidity_node=TRON_RPC_URL,
event_server=TRON_RPC_URL))
retList = []
for addr in addrs:
account_info = trx.get_account(address=addr)
if 'balance' in account_info:
decBalance = Decimal(
account_info['balance']) / Decimal('1000000')
fmtBalance = decBalance.quantize(
Decimal("0.000000"), getattr(decimal, 'ROUND_DOWN'))
else:
fmtBalance = '0.000000'
if Decimal(fmtBalance) < Decimal('1.0'):
logging.info(
f"{addr}'s balance({fmtBalance}) is less than 1.0TRX, skip it."
)
continue
retList.append({
'address': addr,
'balance': str(fmtBalance),
'symbol': symbol
})
return retList
