def count_transaction_num_for_all():
search_results = app.db.session.execute(
"select count(*) as tx_num from t_transaction where from_address not like '00000000000000000000%'"
).fetchall()
key_list = ["transaction_num"]
json_str = convert_to_json(search_results, key_list)
return json_str
def search_block_list_no_parameter(pageNum, pageSize):
offset_page = (pageNum - 1) * pageSize
sql1 = "select (max(height)-%d) as max_height,(max(height)-%d-%d+1) as min_hegiht from t_block"
results1 = app.db.session.execute(
sql1 % (offset_page, offset_page, pageSize)).fetchall()
max_height = results1[0][0]
min_height = results1[0][1]
sql = "SELECT height, max(time) as time,count(block_id) as num, ROUND(sum(amount)/1000000,6) as amount, ROUND(sum(award)/1000000,6) as award from " \
"(select " \
"t_block.height, t_block.id as block_id, max(t_block.time) as time, max(t_transaction.amount) as amount, sum(t_award.amount) as award " \
"FROM " \
"t_block " \
"left join t_transaction on t_block.id = t_transaction.block_id " \
"left join t_award on t_transaction.id = t_award.transaction_id " \
"where t_award.type = 'AWARD' and t_block.height <= %d and t_block.height >= %d " \
"GROUP BY t_block.id ORDER BY t_block.height DESC) as tmp " \
"GROUP BY height order by height desc"
results = app.db.session.execute(sql % (max_height, min_height)).fetchall()
key_list = [
"block_height", "block_time", "transaction_num", "transaction_amount",
"transaction_award"
]
json_str = convert_to_json(results, key_list)
return json_str
def get_transaction_amount(yes_date_start, yes_date_end):
get_result = app.db.session.execute(
"select count from t_count_transaction_amount where date>=%d and date<=%d"
% (yes_date_start, yes_date_end)).fetchall()
key_list = ["transaction_amount_24h"]
json_str = convert_to_json(get_result, key_list)
return json_str
def count_block_award_amount_for_all():
search_results = app.db.session.execute(
"select round(sum(amount)/1000000,6) as amount,round(80000000-(sum(amount)/1000000),6) as balance from t_award where type='AWARD'"
).fetchall()
key_list = ["award_total", "award_balance"]
json_str = convert_to_json(search_results, key_list)
return json_str
def get_gas_info_for_search_new(transaction_hash):
sql = "select b.tx_hash, a.time,round(b.amount/1000000,6),b.address as sign_node from t_transaction a " \
"LEFT JOIN t_award b on a.id = b.transaction_id where b.type='GAS' " \
"and b.tx_hash='%s'"
results = app.db.session.execute(sql % transaction_hash).fetchall()
key_list = ["transaction_hash", "transaction_time", "gas", "sign_node"]
json_str = convert_to_json(results, key_list)
return jsonify(json_str)
def search_blockInfo():
block_hash = request.args.get("block_hash")
results = app.db.session.execute(
"select hash, height, time, prevHash from t_block where hash='%s'" %
block_hash).fetchall()
key_list = ['block_hash', 'block_height', 'time', 'prevHash']
json_str = convert_to_json(results, key_list)
return jsonify(json_str)
def search_transactionInfo_index():
results = app.db.session.execute(
"select transaction_hash,time,amount,from_address,to_address from t_transaction order by time desc limit 5"
).fetchall()
key_list = [
"transaction_hash", "time", "amount", "from_address", "to_address"
]
json_str = convert_to_json(results, key_list)
return jsonify(json_str)
def search_topN_for_index():
sql1 = "set @i:=0;"
sql2 = "select (@i:=@i+1) as RowNum, m.* from(select gas,c_gas from(select gas,count(gas) as c_gas from t_gas GROUP BY gas) temp " \
"where gas>=1000 and gas<=100000 ORDER BY c_gas desc limit 10) as m"
app.db.session.execute(sql1)
result = app.db.session.execute(sql2).fetchall()
key_list = ["Sort_num", "gas", "count"]
json_str = convert_to_json(result, key_list)
return json_str
def search_transaction_list(block_hash):
sql = "select b.transaction_hash,b.from_address,b.to_address,round(b.amount/1000000,6) from t_block a " \
"LEFT JOIN t_transaction b on a.id=b.block_id where a.hash='%s' "
results = app.db.session.execute(sql % block_hash).fetchall()
key_list = [
"transaction_hash", "from_address", "to_address", "transaction_amount"
]
json_str = convert_to_json(results, key_list)
return json_str
def get_block_award_for_search(transaction_hash):
sql = "select a.time,b.tx_hash,round(b.amount/1000000,6),b.address as sign_node from t_transaction a " \
"LEFT JOIN t_award b on a.id = b.transaction_id where b.type='AWARD' " \
"and a.transaction_hash='%s'"
results = app.db.session.execute(sql % transaction_hash).fetchall()
key_list = [
"transaction_time", "transaction_hash_award", "award", "sign_node"
]
json_str = convert_to_json(results, key_list)
return json_str
def search_award_transaction(block_hash):
sql = "select c.tx_hash,GROUP_CONCAT(c.address SEPARATOR ',') sign_node,round(sum(IF(c.type='AWARD',c.amount,0))/1000000,6) as award " \
"from t_block a " \
"LEFT JOIN t_transaction b on a.id=b.block_id " \
"LEFT JOIN t_award c on c.transaction_id=b.id " \
"where a.hash='%s' and c.type='AWARD' " \
"GROUP BY c.tx_hash,b.from_address,b.to_address,b.amount"
results = app.db.session.execute(sql % block_hash).fetchall()
key_list = ["transaction_hash", "sign_node", "transaction_award"]
json_str = convert_to_json(results, key_list)
return json_str
def search_block_height_for_blockHeight(block_height):
sql = "SELECT height, count(block_id) as tx_num,ROUND(sum(award)/1000000,6) as award,ROUND(sum(amount)/1000000,6) as amount " \
"from (select " \
"t_block.height, t_block.id as block_id, min(t_transaction.amount) as amount, sum(t_award.amount) as award " \
"FROM " \
"t_block " \
"left join t_transaction on t_block.id = t_transaction.block_id " \
"left join t_award on t_transaction.id = t_award.transaction_id " \
"where t_award.type = 'AWARD' and t_block.height = %d " \
"GROUP BY t_block.id,t_block.height ) as tmp " \
"GROUP BY height"
results = app.db.session.execute(sql % block_height).fetchall()
key_list = ["block_height", "tx_num", "block_award", "transaction_amount"]
json_str = convert_to_json(results, key_list)
return json_str
def search_block_list_for_blockHeight(block_height, pageNum, pageSize):
offset_page = (pageNum - 1) * pageSize
sql = "select a.hash,b.time,b.from_address,b.to_address,round(b.amount/1000000,6) " \
"from t_block a " \
"LEFT JOIN t_transaction b on a.id=b.block_id " \
"LEFT JOIN t_award c on b.id=c.transaction_id " \
"where a.height=%d " \
"group by a.hash,b.from_address,b.to_address,b.amount,b.time limit %d, %d"
results = app.db.session.execute(
sql % (block_height, offset_page, pageSize)).fetchall()
key_list = [
"block_hash", "time", "from_address", "to_address",
"transaction_amount"
]
json_str = convert_to_json(results, key_list)
return json_str
def select_index_blockInfo():
sql1 = "select MAX(height) as max_height,(MAX(height)-5+1) as min_height from t_block"
results1 = app.db.session.execute(sql1).fetchall()
max_height = results1[0][0]
min_height = results1[0][1]
sql = "select height,MAX(time) as time,COUNT(*) as tx_num,round(SUM(amount)/1000000,6) as amount, " \
"round(SUM(award)/1000000,6) as award " \
"from(select a.height,max(a.time) as time,b.amount,a.hash, sum(IF(c.type = 'AWARD', c.amount, 0)) as award from t_block a " \
"LEFT JOIN t_transaction b on a.id=b.block_id " \
"LEFT JOIN t_award c on c.transaction_id=b.id where c.type='award' " \
"and (a.height<=%d and a.height>=%d) " \
"group by a.height,b.amount,a.hash) " \
"temp GROUP BY height ORDER BY height desc"
results = app.db.session.execute(sql % (max_height, min_height)).fetchall()
key_list = ["block_height", "time", "tx_num", "amount", "award"]
json_str = convert_to_json(results, key_list)
return json_str
def get_transaction_info_for_search(transaction_hash):
sql = "select a.transaction_hash,b.height,b.hash,a.time, " \
"a.from_address,a.to_address,a.amount_detail,if(a.from_address=a.to_address,'1','0') as redeem, " \
"if(a.to_address='0000000000000000000000000000000000','1','0') as pledge, " \
"round(a.amount/1000000,6),b.extra,GROUP_CONCAT(distinct c.address SEPARATOR ',') sign_node,round(sum(IF(c.type='GAS',c.amount,0))/1000000,6) as gas, " \
"round(sum(IF(c.type='AWARD',c.amount,0))/1000000,6) as award from t_transaction a " \
"left join t_block b on a.block_id = b.id " \
"left join t_award c on a.id = c.transaction_id " \
"where a.transaction_hash = '%s' " \
"group by a.transaction_hash,b.height,b.hash,a.time,a.from_address,a.to_address,a.amount_detail,a.amount,b.extra"
results = app.db.session.execute(sql % transaction_hash).fetchall()
key_list = [
"transaction_hash", "block_height", "block_hash", "transaction_time",
"from_address", "to_address", "amount_detail", "redeem", "pledge",
"transaction_amount", "extra", "sign_node", "gas", "award"
]
json_str = convert_to_json(results, key_list)
return json_str
def search_address_list(total_award, pageNum, pageSize):
offset_page = (pageNum - 1) * pageSize
zzh = '16psRip78QvUruQr'
sql1 = "set @i:=%d;"
sql2 = "select (@i:=@i+1) as RowNum, m.* from( " \
"select b.address,round(b.amount/1000000,6) as amount,transaction_num, " \
"round((((b.amount/1000000)/((120000000+%f))*100)),6) as proportion " \
"from t_address b " \
"where b.address not like '%s%%' and b.amount>=0 " \
"group by b.address,b.amount,transaction_num order by amount desc " \
"limit %d, %d) as m"
app.db.session.execute(sql1 % offset_page)
results = app.db.session.execute(
sql2 % (total_award, zzh, offset_page, pageSize)).fetchall()
key_list = [
"row_num", "wallet_address", "amount", "transaction_num", "percentage"
]
json_str = convert_to_json(results, key_list)
return json_str
def search_transactionInfo(wallet_address, pageNum, pageSize):
offset_page = (pageNum - 1) * pageSize
sql = "select a.transaction_hash,a.time,round(a.amount/1000000,6),a.from_address,a.to_address, " \
"round(sum(IF(b.type = 'GAS', b.amount, 0))/1000000,6) as gas from t_transaction a " \
"LEFT JOIN t_award b on a.id=b.transaction_id " \
"where b.type='GAS' and ( a.from_address='%s' or a.to_address='%s') or " \
"a.id in (SELECT DISTINCT transaction_id FROM t_award WHERE address = '%s') " \
"or a.id in(select DISTINCT tx_id FROM t_tx_detail WHERE address = '%s') " \
"GROUP BY a.transaction_hash,a.time,a.amount,a.from_address,a.to_address " \
"ORDER BY a.time desc limit %d, %d"
results = app.db.session.execute(
sql % (wallet_address, wallet_address, wallet_address, wallet_address,
offset_page, pageSize)).fetchall()
key_list = [
"transaction_hash", "time", "amount", "from_address", "to_address",
"gas"
]
json_str = convert_to_json(results, key_list)
return json_str
def select_index_transactionInfo():
sql1 = "select max(id) as max_id,max(id)-5+1 as min_id from t_transaction"
results1 = app.db.session.execute(sql1).fetchall()
max_id = results1[0][0]
min_id = results1[0][1]
sql = "select a.transaction_hash,a.time,round((a.amount/1000000),6) as amount, " \
"a.from_address,a.to_address,round(sum(IF(b.type = 'GAS', b.amount, 0))/1000000,6) as gas " \
"from t_transaction a " \
"LEFT JOIN t_award b on a.id=b.transaction_id " \
"where b.type='GAS' and (a.id<=%d and a.id>=%d) " \
"GROUP BY a.transaction_hash,a.time,a.amount,a.from_address,a.to_address " \
"ORDER BY a.time desc"
results = app.db.session.execute(sql % (max_id, min_id)).fetchall()
key_list = [
"transaction_hash", "date", "amount", "from_address", "to_address",
"gas"
]
json_str = convert_to_json(results, key_list)
return json_str
def search_transaction_list_no(pageNum, pageSize):
offset_page = (pageNum - 1) * pageSize
sql1 = "select (max(id)-%d) as max_id,(max(id)-%d-%d+1) as min_id from t_transaction"
results1 = app.db.session.execute(
sql1 % (offset_page, offset_page, pageSize)).fetchall()
max_id = results1[0][0]
min_id = results1[0][1]
sql = "select a.transaction_hash,a.time,round(a.amount/1000000,6),a.from_address,a.to_address, " \
"round(sum(IF(b.type='GAS',b.amount,0))/1000000,6) as gas from t_transaction a " \
"LEFT JOIN t_award b on a.id=b.transaction_id " \
"where b.type='GAS' and a.id between %d and %d " \
"GROUP BY a.transaction_hash,a.time,a.amount,a.from_address,a.to_address " \
"ORDER BY a.time desc limit 20;"
results = app.db.session.execute(sql % (min_id, max_id)).fetchall()
key_list = [
"transaction_hash", "transaction_time", "transaction_amount",
"from_address", "to_address", "gas"
]
json_str = convert_to_json(results, key_list)
return json_str
def search_balance_for_wallet_address(wallet_address):
sql = "select round(amount/1000000,6) from t_address where address='%s'"
results = app.db.session.execute(sql % wallet_address).fetchall()
key_list = ["account_balance"]
json_str = convert_to_json(results, key_list)
return json_str
def count_num_for_setGas_index():
sql = "select count(gas) from t_gas where gas>=1000 and gas<=100000"
result = app.db.session.execute(sql).fetchall()
key_list = ["count"]
json_str = convert_to_json(result, key_list)
return json_str
def search_blockHash(block_hash):
sql = "select hash,height,time,prevHash from t_block where hash='%s'"
results = app.db.session.execute(sql % block_hash).fetchall()
key_list = ["block_hash", "block_height", "block_time", "prevHash"]
json_str = convert_to_json(results, key_list)
return json_str
def get_usdt_and_rmb():
sql = "select time, usdt, rmb from t_usdt order by time desc limit 48"
results = app.db.session.execute(sql).fetchall()
key_list = ["time", "usdt", "rmb"]
json_str = convert_to_json(results, key_list)
return json_str
def get_avgGas_for_10():
sql = "select time,avg_gas from t_avg_gas order by time desc limit 288"
results = app.db.session.execute(sql).fetchall()
key_list = ["date", "block_height_for_100"]
json_str = convert_to_json(results, key_list)
return json_str
def get_transaction_amount_for_7():
sql = "select date,count from t_count_transaction_amount order by date desc limit 7"
get_result = app.db.session.execute(sql).fetchall()
key_list = ["date", "transaction_amount_for_24H"]
json_str = convert_to_json(get_result, key_list)
return json_str
def search_block_height():
search_results = app.db.session.execute(
"select MAX(height) as max_height from t_block").fetchall()
key_list = ["block_height"]
json_str = convert_to_json(search_results, key_list)
return json_str
def get_block_num_for_7():
sql = "select date,count from t_count_block order by date desc limit 7"
results = app.db.session.execute(sql).fetchall()
key_list = ["date", "block_num_for_7"]
json_str = convert_to_json(results, key_list)
return json_str
