def getLatestDataForVal(conn, hPoolId, mode):
sql = "select * from " + tables.hpoolperf + " p "
sql += " where p.hPoolId= %s and mode=%s "
sql += " order by syncTime desc "
sql += " limit 1 "
return dbUtil.getSingleRecordNoJsonWithConn(sql, conn, (hPoolId, mode))
def getSyncStatus(conn, app, syncType):
sql = " select * from " + tables.syncstatus
sql += " where app = %s "
sql += " and syncType = %s "
# logger.info(sql)
return dbUtil.getSingleRecordNoJsonWithConn(sql, conn, (app, syncType))
def getTxSummary(conn, txDate, txCategory, shardId):
sql = "select * "
sql += " from " + tables.htxsummary
sql += " where txDate = %s and txCategory = %s and shardId = %s "
sql += " limit 1"
return dbUtil.getSingleRecordNoJsonWithConn(sql, conn,
(txDate, txCategory, shardId))
def getActiveAddressCount(conn):
sql = "select count(*) as totalAddress "
sql += " from " + tables.haddress
sql += " where totalBalance > 0 "
# logger.info(sql)
record = dbUtil.getSingleRecordNoJsonWithConn(sql, conn)
return record["totalAddress"]
def getHarmonyPool(conn, address):
sql = "select * from " + tables.hpool
sql += " where address = %s "
# logger.info(sql)
record = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, address)
# logger.info("record is: " + str(record))
return record
def getFeeIncreaseCount(conn):
sql = " select count(*) as FeeIncrease "
sql += " from " + tables.hpool + " p "
sql += " where p.feeChangedEpoch > "
sql += " (select currentEpoch - 7 from " + tables.coinstat + " where symbol='HARMONY') "
# logger.info(sql)
record = dbUtil.getSingleRecordNoJsonWithConn(sql, conn)
return record["FeeIncrease"]
def getHPoolById(conn, hPoolId):
sql = "select p.*, (UNIX_TIMESTAMP() - p.syncEpochTime) as lastUpdatedGap "
sql += " from " + tables.hpool + " p "
sql += " where p.hPoolId = %s "
# logger.info(sql)
record = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, hPoolId)
# logger.info("record is: " + str(record))
return record
def isNotForUnderBidPresent(conn, currentEpoch, notificationType):
sql = "select * "
sql += " from " + tables.hnotification + " hn "
sql += " where hn.epochNumber = %s "
sql += " and hn.notificationType = %s "
sql += " and hn.watchInd = '1' "
args = (currentEpoch, notificationType)
# logger.info("sql: {}, args: {}".format(sql, args))
return dbUtil.getSingleRecordNoJsonWithConn(sql, conn, args)
def getAddressDetails(conn, address):
sql = " select ranking, address, round(totalStake,0) as totalStake, "
sql += " round(addressBalance,0) as addressBalance, "
sql += " round(totalRewards,2) as totalRewards, "
sql += " round(totalBalance,0) as totalBalance, alias "
sql += " from " + tables.haddress
sql += " where address = %s "
# logger.info(sql)
return dbUtil.getSingleRecordNoJsonWithConn(sql, conn, address)
def getSSHealthDetails(conn):
sql = "select round(time_to_sec((TIMEDIFF(NOW(), checkupTime))) / 60,0) as tsLastCheck, "
sql += " (case when heightGap between -4 and 4 then 'Good' when heightGap not between -4 and 4 then 'Bad' else null end) as healthStatus "
sql += " from " + tables.nodehealth + " nh"
sql += " where symbol=%s"
sql += " order by checkupTime desc"
sql += " limit 1"
# logger.info(sql)
resp = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, app)
# logger.info(resp)
return resp
def getUserSettings(conn, chatId, app):
sql = "select * "
sql += " from " + tables.tgsetting
sql += " where chatId = %s and app=%s "
sql += " limit 1"
# logger.info(sql)
setting = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, (chatId, app))
# logger.info(setting)
return setting
def isAlreadyExistingAddress(conn, address):
sql = "select count(*) as recordCount from " + tables.haddress
sql += " where address = %s "
record = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, address)
count = record["recordCount"]
if count > 0:
return True
return False
def getAddressFromAlias(conn, app, alias):
sql = " select address from " + tables.addressalias
sql += " where app = %s and alias = %s "
# logger.info(sql)
args = (app, alias)
# logger.info(args)
data = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, args)
# logger.info(data)
return data
def isAlreadyExistingTwoKeys(conn,
tableName,
columnName1,
key1,
columnName2=None,
key2=None):
sql = "select count(*) as recordCount from " + tableName
sql += " where " + columnName1 + " = %s"
if columnName2 is not None:
sql += " and " + columnName2 + " = %s"
record = getSingleRecordNoJsonWithConn(sql, conn, (key1, key2))
else:
record = getSingleRecordNoJsonWithConn(sql, conn, key1)
# logger.info("record is: " + str(record))
count = record["recordCount"]
if count > 0:
return True
return False
def getAddressStake(conn, address):
sql = " select round(totalStake,0) as totalStake "
sql += " from " + tables.haddress
sql += " where address = %s "
# logger.info(sql)
record = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, address)
if record:
return record["totalStake"]
return 0
def getDelegation(conn, hPoolId, address):
sql = "select stake "
sql += " from " + tables.hpooldel
sql += " where hPoolId=%s and address=%s "
# logger.info(sql)
record = dbUtil.getSingleRecordNoJsonWithConn(sql, conn,
(hPoolId, address))
if not record:
return 0
return record["stake"]
def getNodeHealth(conn, poolId):
oldestTime = (datetime.datetime.now() -
datetime.timedelta(days=rds_config.pool_max_days))
sql = " select min(heightGap) as gap, max(lastUpdated) as checkupTime "
sql += " from (select nh.*, "
sql += " UNIX_TIMESTAMP(lastUpdated) as lw "
sql += " from " + tables.nodehealth + " nh "
sql += " where poolId= " + str(poolId)
sql += " and lastUpdated > '" + str(oldestTime) + "'"
sql += " order by lw desc limit 2) as nh "
# logger.info(sql)
return getSingleRecordNoJsonWithConn(sql, conn)
def getExisting(conn, epoch, dataType, poolId=None): sql = "select * from " + tables.hhistory sql += " where epoch = %s" sql += " and dataType = %s" args = (epoch, dataType) if poolId is not None: sql += " and poolId = %s" args = (epoch, dataType, poolId) # logger.info(sql) # logger.info(args) record = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, args) return record
def getAverageAprForCurrentEpoch(conn, app, epochNumber):
sql = "select sum(totalStaked * apr)/sum(totalStaked) as averageApr "
sql += " from " + tables.hpoolperf
sql += " where mode=%s "
sql += " and epochNumber=%s and elected='True' "
args = (constants.H_EPOCH_MODE, epochNumber)
# logger.info(sql)
# logger.info(args)
startTime = commonUtils.getCurrentTime()
data = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, args)
commonUtils.logTimeDiff(startTime,
"after processing getAverageAprForCurrentEpoch")
return data["averageApr"]
def getAccessKey(conn, app, key):
sql = "select ak.accessKeyId, ak.accessKey, ak.assignedTo, "
sql += " ak.accessFn1, ak.accessFn2, ak.creationDate, ak.dailyLimit, "
sql += " ak.app, ak.status, aa.appAuditId, aa.auditDate, "
sql += " aa.hits, aa.deniedHits "
sql += " from " + tables.accesskey + " ak "
sql += " left join " + tables.appaudit + " aa "
sql += " on aa.accessKeyId=ak.accessKeyId and auditDate=curDate() "
sql += " where ak.accessKey = %s "
sql += " and app = %s and status='ACTIVE' "
# logger.info(sql)
accessKey = dbUtil.getSingleRecordNoJsonWithConn(sql, conn, (key, app))
# logger.info(accessKey)
# logger.info(accessKey["accessKeyId"])
return accessKey
def getAddressDisplayDetails(conn, app, address, alias, subType=None):
startTime = commonUtils.getCurrentTime()
# logger.info("subType : " + subType)
if address is None and alias is not None:
record = aliasUtils.getAddressFromAlias(conn, app, alias)
commonUtils.logTimeDiff(startTime, "after getting address from alias")
if not record:
response = "'" + alias + "' - alias not found in server. Please enter the address, alias and use the 'Save Alias in Server' button to save the alias."
return dbUtil.getResponse(dbUtil.jsondumps({"error": response}))
address = record["address"]
coinStats = dbUtil.getSingleRecordNoJsonWithConn(
commonUtils.getCoinStatSql(), conn, app)
commonUtils.logTimeDiff(startTime, "after getting coinstat")
addressDetails = getAddressDetails(conn, app, address, alias)
commonUtils.logTimeDiff(startTime, "after getting address details")
lastUpdated = commonUtils.getEventLastUpdated(
conn, eventName.syncHarmonyDelegations)
commonUtils.logTimeDiff(startTime, "after getting event last updated")
delegations = getDelegationDetails(conn, address)
commonUtils.logTimeDiff(startTime, "after getting delegations details")
smartStake = None
if subType == "myAccount":
# logger.info("checking if smart stake user")
smartStake = isUsingSmartStake(delegations)
commonUtils.logTimeDiff(startTime, "after getting is smart stake")
rewards = harmonyEvents.getRewardsSummary(conn, address)
commonUtils.logTimeDiff(startTime, "after getting rewards summary")
stakeHistory = listAddressStakeHistoryChartData(conn, address,
coinStats["currentEpoch"])
stakeHistory.sort(key=lambda item: item['epoch'], reverse=False)
commonUtils.logTimeDiff(startTime, "after getting stake history chart")
# addressEvents = harmonyEvents.listEventsByAddress(conn, address)
addressEvents = []
commonUtils.logTimeDiff(startTime, "after getting event details")
# logger.info("after calling reportData.listRewardComparisonReportData")
return dbUtil.combineResults8("addressDetails", addressDetails,
"lastUpdated", lastUpdated, "delegations",
delegations, "rewards", rewards,
"stakeHistory", stakeHistory, "events",
addressEvents, "coinStats", coinStats,
"smartStake", smartStake)
def validateStartBlock(conn, startBlock, eventKey, normalMode):
logger.info("in validateStartBlock: event - {}, normalMode - {}".format(
eventKey, normalMode))
if not normalMode:
return
event = dbUtil.getSingleRecordNoJsonWithConn(commonUtils.getEventSql(),
conn, eventKey)
# logger.info(event)
dbBlock = int(event["description"])
msg = "Expected starting block: {}, actual starting block: {}".format(
dbBlock, startBlock)
logger.info(msg)
if dbBlock + 1 != startBlock:
raise Exception("Transaction blocks do not align. " + msg)
def getAddressDetails(conn, app, address, alias):
sql = " select ranking, ha.alias, ha.address, "
sql += " round(totalStake,2) as totalStake, "
sql += " round(addressBalance,2) as addressBalance, "
sql += " round(totalRewards,2) as totalRewards, "
sql += " round(totalBalance, 2) as totalBalance "
sql += " from " + tables.haddress + " ha "
if address is not None:
sql += " where ha.address = %s "
args = address
else:
sql += " inner join " + tables.addressalias + " aa on ha.address=aa.address"
sql += " where app = %s and aa.alias = %s "
args = (app, alias)
# logger.info(sql)
return dbUtil.getSingleRecordNoJsonWithConn(sql, conn, args)
def getNotification(conn, app, poolId=None):
sql = "select message, endDate "
sql += " from " + tables.notification
sql += " where startDate < NOW() "
sql += " and endDate > NOW() "
sql += " and app = %s "
args = ()
if not poolId:
sql += " and poolId is null "
args = (app)
else:
sql += " and poolId =%s "
args = (app, poolId)
sql += " limit 1"
# logger.info(sql)
# logger.info(args)
return getSingleRecordNoJsonWithConn(sql, conn, args)
def getAccount(conn, chatId, userId):
# conn = dbUtil.getConnection()
setting = tgBotUtils.prepUserSettings(conn, chatId, userId, app)
if not setting["address"]:
return "Rewards address is not configured. Check /settings command"
coin = dbUtil.getSingleRecordNoJsonWithConn(commonUtils.getCoinStatSql(),
conn, app)
# logger.info("starting reward processing")
# logger.info("searching by address")
rewards = dbUtil.listResultsWithConn(
searchByAddressSql(setting["address"]), conn)
totalOne = getTotalOne(conn, rewards)
message, totals = getRewardsMessage(coin, rewards, totalOne)
# logger.info("rewards message: " + message)
header = "Account Summary: \n"
header += totals + "\n"
header += "Rank \t | Address\t\t | Stake \t | Not-staked \t | Rewards \t | Total \t | Total USD\n"
addressProcessed = {}
# logger.info("preparing header for rewards")
# logger.info(rewards)
for reward in rewards:
address = reward["address"]
# logger.info(address)
if address not in addressProcessed:
# logger.info("processing header for the address")
addressProcessed[address] = True
header += getAddressHeader(conn, coin, reward["address"])
# logger.info(header)
header += "\n"
# logger.info("end of processing")
# conn.close()
return header + message
def getKeyDetails(conn, blsKey):
sql = "select * from " + tables.hblskey
sql += " where blsKey = %s "
return dbUtil.getSingleRecordNoJsonWithConn(sql, conn, blsKey)
def getProp(conn, app, propName):
return getSingleRecordNoJsonWithConn(getPropSql(), conn, (app, propName))
def getTotalTransactions(conn):
sql = "select sum(txCount) as totalTransactions from " + tables.htxsummary
data = dbUtil.getSingleRecordNoJsonWithConn(sql, conn)
return data["totalTransactions"]
def getCoinStat(conn, symbol):
sql = getCoinStatSql()
logger.info("getting coinstat: {}, data: {}".format(sql, symbol))
return getSingleRecordNoJsonWithConn(sql, conn, symbol)
def getEventDetails(conn, eventName):
return getSingleRecordNoJsonWithConn(getEventSql(), conn, eventName)
