/// <summary>
/// Get EDPoS Blocks Detail what is useful
/// </summary>
/// <param name="reward_address"></param>
/// <param name="date"></param>
/// <returns></returns>
public async Task <List <MBlockDetail> > GetBlockDetail(string reward_address, string date)
{
if (SqlAttack.IsDangerous(ref reward_address) || SqlAttack.IsDangerous(ref date))
{
return(new List <MBlockDetail>());
}
using (var conn = new MySqlConnection(connStr))
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT id,hash,fork_hash,prev_hash,time,height,type,reward_address,reward_money,is_useful,bits,reward_state from Block ");
sb.Append("WHERE type = 'primary-dpos' AND is_useful = 1");
if (!string.IsNullOrEmpty(reward_address))
{
sb.Append(" AND reward_address = '" + reward_address + "'");
}
if (!string.IsNullOrEmpty(date))
{
sb.Append(" AND from_unixtime(time, '%Y-%m-%d') = '" + date + "'");
}
var query = conn.QueryAsync <MBlockDetail>(sb.ToString());
return((await query).ToList());
}
}
/// <summary>
/// Get info by addrFrom, addrTo or settlement date
/// </summary>
/// <param name="addrFrom"></param>
/// <param name="addrTo"></param>
/// <param name="date"></param>
/// <returns></returns>
public async Task <List <MPowPoolDailyReward> > GetBySomething(string addrFrom = "", string addrTo = "", string date = "")
{
if (SqlAttack.IsDangerous(ref addrFrom) || SqlAttack.IsDangerous(ref addrTo) || SqlAttack.IsDangerous(ref date))
{
return(new List <MPowPoolDailyReward>());
}
StringBuilder sb = new StringBuilder();
sb.Append("SELECT `id`,`addrFrom`,`addrTo`,`reward`,`settlementDate` FROM PowPoolDailyReward WHERE 1=1 ");
if (!string.IsNullOrEmpty(date))
{
DateTime dt = new DateTime();
if (DateTime.TryParse(date, out dt))
{
date = dt.ToString("yyyy-MM-dd");
sb.Append("AND settlementDate = '" + date + "' ");
}
}
if (!string.IsNullOrEmpty(addrFrom))
{
sb.Append("AND addrFrom = '" + addrFrom + "' ");
}
if (!string.IsNullOrEmpty(addrTo))
{
sb.Append(" AND addrTo = '" + addrTo + "'");
}
using (var conn = new MySqlConnection(connStr))
{
var re = await conn.QueryAsync <MPowPoolDailyReward>(sb.ToString());
return(re.ToList());
}
}
public async Task <bool> InsertOne(MUnlockBlock mo, int zone)
{
StringBuilder sb = new StringBuilder();
string addrFrom = mo.addrFrom;
string addrTo = mo.addrTo;
bool res = false;
if (SqlAttack.IsDangerous(ref addrFrom) || SqlAttack.IsDangerous(ref addrTo))
{
return(res);
}
using (var conn = new MySqlConnection(connStr))
{
string d = string.Empty;
if (mo.date != DateTime.MinValue)
{
d = mo.date.ToString("yyyy-MM-dd");
}
var g = await IsExist(mo.height, mo.addrFrom, mo.addrTo);
if (g.Count > 0)
{
//update
sb.Append("UPDATE `UnlockedBlock` SET ");
sb.Append("`addrFrom`=@addrFrom,`addrTo`=@addrTo,");
sb.Append("`balance`=@balance,`timeSpan`=@timeSpan,`date`=@date,`height`=@height ");
sb.Append("WHERE `id` = " + g[0].id);
return(false);
}
else
{
//INSERT INTO `UnlockedBlock` (`addrFrom`,`addrTo`,`balance`,`timeSpan`,`date`,`height`) VALUES (
//@addrFrom, @addrTo, @balance, @timeSpan, @date, @height)
//WHERE NOT EXISTS (SELECT 1 FROM `UnlockedBlock` WHERE addrTo=@addrTo AND height=@height);
//insert
sb.Append("INSERT INTO `UnlockedBlock` (`addrFrom`,`addrTo`,`balance`,`timeSpan`,`date`,`height`) VALUES (");
sb.Append("@addrFrom, @addrTo, @balance, @timeSpan, @date, @height");
sb.Append(")");
}
var re = await conn.ExecuteAsync(sb.ToString(), new
{
addrFrom = mo.addrFrom.Trim(),
addrTo = mo.addrTo.Trim(),
balance = mo.balance,
timeSpan = mo.timeSpan,
date = Convert.ToDateTime(mo.date).ToString("yyyy-MM-dd"),
height = mo.height
});
if (re > 0)
{
res = true;
}
return(res);
}
}
public async Task<List<MBlockPa>> GetBlockDailyReward(string reward_address, string date, string ConsensusType = "")
{
if (SqlAttack.IsDangerous(ref reward_address) || SqlAttack.IsDangerous(ref date))
{
return new List<MBlockPa>();
}
using (var conn = new MySqlConnection(connStr))
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT sum(reward_money) reward_money,reward_address from Block ");
if (!string.IsNullOrEmpty(ConsensusType))
{
sb.Append(" WHERE type = '" + ConsensusType + "'");
}
sb.Append(" AND is_useful = 1");
if (!string.IsNullOrEmpty(reward_address))
{
sb.Append(" AND reward_address = '" + reward_address + "'");
}
if (!string.IsNullOrEmpty(date))
{
date = DateTime.Parse(date).ToString("yyyy-MM-dd");
sb.Append(" AND from_unixtime(time, '%Y-%m-%d') = '" + date + "'");
}
sb.Append(" GROUP BY reward_address");
var query = conn.QueryAsync<MBlockPa>(sb.ToString());
return (await query).ToList();
}
}
/// <summary>
/// Get unlockBlocks By parameters,there are more params
/// </summary>
/// <param name="addrFrom"></param>
/// <param name="addrTo"></param>
/// <param name="date">If it has this param, then the param timeSpan is invalid</param>
/// <param name="timeSpan"></param>
/// <param name="height"></param>
/// <param name="zone"></param>
/// <returns></returns>
public async Task <List <MUnlockBlock> > GetLst(string addrFrom = "", string addrTo = "", string date = "", long timeSpan = 0, int height = 0, int zone = 0)
{
if (SqlAttack.IsDangerous(ref addrFrom) || SqlAttack.IsDangerous(ref addrTo))
{
return(new List <MUnlockBlock>());
}
StringBuilder sb = new StringBuilder();
sb.Append("SELECT `id`,`addrFrom`,`addrTo`,`balance`,`timeSpan`,`date`,`height` FROM UnlockedBlock WHERE 1=1 ");
if (height != 0)
{
sb.Append("AND height = " + height + " ");
}
if (!string.IsNullOrEmpty(date))
{
DateTime dt = new DateTime();
if (DateTime.TryParse(date, out dt))
{
var dateEnd = dt.AddDays(1);
var dStart = Convert.ToInt64(CommonHelper.GetTimeStamp(dt, zone)) / 1000;
var dEnd = Convert.ToInt64(CommonHelper.GetTimeStamp(dateEnd, zone)) / 1000;
sb.Append("AND timeSpan >= " + dStart + " AND timeSpan <= " + dEnd + " ");
}
else
{
if (timeSpan != 0)
{
sb.Append("AND timeSpan = " + timeSpan + " ");
}
}
}
else
{
if (timeSpan != 0)
{
sb.Append("AND timeSpan = " + timeSpan + " ");
}
}
if (!string.IsNullOrEmpty(addrFrom))
{
sb.Append("AND addrFrom = '" + addrFrom + "' ");
}
if (!string.IsNullOrEmpty(addrTo))
{
sb.Append(" AND addrTo = '" + addrTo + "'");
}
using (var conn = new MySqlConnection(connStr))
{
var re = await conn.QueryAsync <MUnlockBlock>(sb.ToString());
return(re.ToList());
}
}
/// <summary>
/// Get the Voting of voters by dposaddr,if has the voterAddr parameter,return the voter's information
/// </summary>
/// <param name="dposAddr">super node address</param>
/// <param name="voterAddr">voter address</param>
/// <returns></returns>
public async Task <List <MVoters> > GetVoting(string dposAddr, string voterAddr = "")
{
if (SqlAttack.IsDangerous(ref dposAddr) || SqlAttack.IsDangerous(ref voterAddr))
{
return(new List <MVoters>());
}
using (var conn = new MySqlConnection(connStr))
{
string strSql = GetSqlNew(dposAddr, voterAddr);
return((await conn.QueryAsync <MVoters>(strSql)).ToList());
}
}
/// <summary>
/// Get Dpos Daily Reward
/// </summary>
/// <param name="dpos_addr"></param>
/// <param name="date"></param>
/// <returns></returns>
public async Task <List <DposDailyReward> > GetDposDailyReward(string dpos_addr, DateTime date)
{
if (SqlAttack.IsDangerous(ref dpos_addr))
{
return(new List <DposDailyReward>());
}
using (var conn = new MySqlConnection(connStr))
{
Task <IEnumerable <DposDailyReward> > query;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT id,dpos_addr,client_addr,payment_date,payment_money,txid FROM `DposDailyReward` where 1=1 ");
if (!string.IsNullOrEmpty(dpos_addr))
{
sb.Append(" AND dpos_addr = @dpos_addr ");
}
if (date != DateTime.MinValue)
{
sb.Append(" AND payment_date = @payment_date ");
}
if (!string.IsNullOrEmpty(dpos_addr) && date != DateTime.MinValue)
{
query = conn.QueryAsync <DposDailyReward>(sb.ToString(), new { dpos_addr = dpos_addr, payment_date = date.Date });
}
else
{
if (string.IsNullOrEmpty(dpos_addr) && date == DateTime.MinValue)
{
query = conn.QueryAsync <DposDailyReward>(sb.ToString());
}
else
{
if (!string.IsNullOrEmpty(dpos_addr))
{
query = conn.QueryAsync <DposDailyReward>(sb.ToString(), new { dpos_addr = dpos_addr });
}
else
{
query = conn.QueryAsync <DposDailyReward>(sb.ToString(), new { payment_date = date.Date });
}
}
}
return((await query).ToList());
}
}
/// <summary>
/// Get the total rewards of the day
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public async Task <List <DposAddrDaily> > GetPayment_money(string date)
{
if (SqlAttack.IsDangerous(ref date))
{
return(new List <DposAddrDaily>());
}
using (var conn = new MySqlConnection(connStr))
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT dpos_addr, SUM(payment_money) as payment_money,'" + date + "' as payment_date ");
sb.Append(" from DposDailyReward where payment_date = '");
sb.Append(date);
sb.Append("' GROUP BY dpos_addr");
var query = conn.QueryAsync <DposAddrDaily>(sb.ToString());
return((await query).ToList());
}
}
/// <summary>
/// 查询 [节点地址][日期]的所有汇总dpos奖励数据
/// </summary>
/// <param name="dpos_addr"></param>
/// <param name="date"></param>
/// <returns></returns>
public async Task <List <DposAddrDaily> > GetTotalReward(string dpos_addr, string date)
{
if (SqlAttack.IsDangerous(ref dpos_addr) || SqlAttack.IsDangerous(ref date))
{
return(new List <DposAddrDaily>());
}
using (var conn = new MySqlConnection(connStr))
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT dpos_addr,sum(payment_money) as payment_money,payment_date FROM `DposDailyReward` where 1=1 ");
if (!string.IsNullOrEmpty(dpos_addr))
{
sb.Append(" AND dpos_addr = '" + dpos_addr.Trim() + "' ");
}
if (!string.IsNullOrEmpty(date))
{
DateTime dateTmp = new DateTime();
if (DateTime.TryParse(date, out dateTmp))
{
sb.Append(" AND payment_date = '" + dateTmp.ToString("yyyy-MM-dd") + "' ");
}
}
if ((string.IsNullOrEmpty(date) && string.IsNullOrEmpty(dpos_addr)) || !string.IsNullOrEmpty(date) && !string.IsNullOrEmpty(dpos_addr))
{
sb.Append("GROUP BY dpos_addr,payment_date");
}
else
{
if (!string.IsNullOrEmpty(date))
{
sb.Append("GROUP BY dpos_addr");
}
else
{
sb.Append("GROUP BY payment_date");
}
}
return((await conn.QueryAsync <DposAddrDaily>(sb.ToString())).ToList());
}
}
/// <summary>
/// insert one
/// </summary>
/// <param name="mo"></param>
/// <returns></returns>
public async Task <bool> InsertOne(MPowPoolDailyReward mo)
{
string addrFrom = mo.addrFrom;
string addrTo = mo.addrTo;
bool res = false;
if (SqlAttack.IsDangerous(ref addrFrom) || SqlAttack.IsDangerous(ref addrTo))
{
return(res);
}
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO `PowPoolDailyReward` (`id`,`addrFrom`,`addrTo`,`reward`,`settlementDate`) VALUES (");
sb.Append("@id, @addrFrom, @addrTo, @reward, @settlementDate");
sb.Append(")");
using (var conn = new MySqlConnection(connStr))
{
//If it has one,skip
/*var g = await GetBySomething(mo.addrFrom, mo.addrTo, mo.settlementDate);
* if(g.Count > 0)
* {
* return false;
* }*/
var re = await conn.ExecuteAsync(sb.ToString(), new {
id = 0,
addrFrom = mo.addrFrom.Trim(),
addrTo = mo.addrTo.Trim(),
reward = mo.reward,
settlementDate = Convert.ToDateTime(mo.settlementDate).ToString("yyyy-MM-dd")
});
// var re = await conn.ExecuteAsync(sb.ToString(), mo);
if (re > 0)
{
res = true;
}
return(res);
}
}
/// <summary>
/// Add One App
/// </summary>
/// <param name="mo"></param>
/// <returns></returns>
public async Task <bool> InsertOne(MAppInfo mo)
{
bool res = false;
string appID = mo.appID;
string appName = mo.appName;
string note = mo.note;
string secretKey = mo.secretKey;
if (SqlAttack.IsDangerous(ref appID) ||
SqlAttack.IsDangerous(ref appName) ||
SqlAttack.IsDangerous(ref note) ||
SqlAttack.IsDangerous(ref secretKey))
{
return(res);
}
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO `AppInfo` (id,appID,appName,addTime,secretKey,note) VALUES (");
sb.Append("@id, @appID, @appName, @addTime, @secretKey, @note");
sb.Append(")");
using (var conn = new MySqlConnection(connStr))
{
var re = await conn.ExecuteAsync(sb.ToString(), new
{
id = 0,
appID = mo.appID.Trim(),
appName = mo.appName.Trim(),
addTime = mo.addTime,
secretKey = mo.secretKey,
note = mo.note
});
if (re > 0)
{
res = true;
}
return(res);
}
}
/// <summary>
/// Get the max height of the table DposRewardDetails
/// </summary>
/// <param name="date"></param>
/// <param name="dposAddr"></param>
/// <returns></returns>
public async Task <long> GetMaxHeight(string date = "", string dposAddr = "")
{
if (SqlAttack.IsDangerous(ref date) || SqlAttack.IsDangerous(ref dposAddr))
{
return(0);
}
using (var conn = new MySqlConnection(connStr))
{
StringBuilder sb = new StringBuilder("SELECT max(block_height) AS height FROM DposRewardDetails WHERE 1=1 ");
if (!string.IsNullOrEmpty(date))
{
sb.Append("AND reward_date = '" + date + "' ");
}
if (!string.IsNullOrEmpty(dposAddr))
{
sb.Append("AND dpos_addr = '" + dposAddr + "' ");
}
var query = conn.QueryAsync <long>(sb.ToString());
return((await query).ToList <long>().FirstOrDefault());
}
}
/// <summary>
/// Voting and reward details at an address on a given day
/// </summary>
/// <param name="dateTime"></param>
/// <param name="height"></param>
/// <param name="dpos_addr"></param>
/// <param name="client_addr"></param>
/// <returns></returns>
public async Task <List <DposRewardDetails> > GetDposRewardDetails(string dateTime = "", int height = 0, string dpos_addr = "", string client_addr = "")
{
var lst = new List <DposRewardDetails>();
if (SqlAttack.IsDangerous(ref dateTime) || SqlAttack.IsDangerous(ref dpos_addr) || SqlAttack.IsDangerous(ref client_addr))
{
return(lst);
}
using (var conn = new MySqlConnection(connStr))
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT dpos_addr,client_addr,vote_amount,reward_money,reward_date,block_height FROM DposRewardDetails where 1=1 ");
if (!string.IsNullOrEmpty(dateTime))
{
sb.Append("AND reward_date ='" + dateTime + "'");
}
if (height > 0)
{
sb.Append("AND block_height = " + height + " ");
}
if (!string.IsNullOrEmpty(dpos_addr))
{
sb.Append("AND dpos_addr ='" + dpos_addr + "'");
}
if (!string.IsNullOrEmpty(client_addr))
{
sb.Append("AND client_addr = '" + client_addr + "'");
}
var query = conn.QueryAsync <DposRewardDetails>(sb.ToString());
return((await query).ToList());
}
}
/// <summary>
/// Get unlocked blocks by parameters
/// </summary>
/// <param name="timeSpanMin">The start timeSpan</param>
/// <param name="timeSpanMax">The end timeSpan</param>
/// <param name="addrFrom"></param>
/// <param name="addrTo"></param>
/// <returns></returns>
public async Task <List <MUnlockBlockLst> > GetLst(string timeSpanMin, string timeSpanMax, string addrFrom = "", string addrTo = "")
{
if (SqlAttack.IsDangerous(ref addrFrom) || SqlAttack.IsDangerous(ref addrTo) || SqlAttack.IsDangerous(ref timeSpanMin) || SqlAttack.IsDangerous(ref timeSpanMax))
{
return(new List <MUnlockBlockLst>());
}
StringBuilder sb = new StringBuilder();
sb.Append("SELECT `id`,`addrFrom`,`addrTo`,`balance`,`timeSpan`,`date`,`height` FROM UnlockedBlock WHERE 1=1 ");
if (!string.IsNullOrEmpty(timeSpanMin))
{
sb.Append("AND timeSpan >= " + long.Parse(timeSpanMin) + " ");
}
if (!string.IsNullOrEmpty(timeSpanMax))
{
sb.Append("AND timeSpan < " + long.Parse(timeSpanMax) + " ");
}
if (!string.IsNullOrEmpty(addrFrom))
{
sb.Append("AND addrFrom = '" + addrFrom + "' ");
}
if (!string.IsNullOrEmpty(addrTo))
{
sb.Append(" AND addrTo = '" + addrTo + "'");
}
Console.WriteLine(sb.ToString());
using (var conn = new MySqlConnection(connStr))
{
var lst = await conn.QueryAsync <MUnlockBlock>(sb.ToString());
var tmpLst = new List <MUnlockBlockLst>();
foreach (var item in lst)
{
if (tmpLst.Where(p => p.addrFrom.Equals(item.addrFrom) && p.date.ToString("yyyy-MM-dd").Equals(item.date.ToString("yyyy-MM-dd"))).Count() > 0)
{
var tmpMoo = new MUnlockBlockReward();
tmpMoo.addrTo = item.addrTo;
tmpMoo.balance = item.balance;
tmpMoo.timeSpan = item.timeSpan;
tmpMoo.height = item.height;
foreach (var v in tmpLst)
{
if (v.addrFrom.Equals(item.addrFrom) && v.date.ToString("yyyy-MM-dd").Equals(item.date.ToString("yyyy-MM-dd")))
{
v.balanceLst.Add(tmpMoo);
break;
}
}
}
else
{
var tmpMo = new MUnlockBlockLst();
tmpMo.addrFrom = item.addrFrom;
tmpMo.date = item.date;
tmpMo.balanceLst = new List <MUnlockBlockReward>();
var tmpMoo = new MUnlockBlockReward();
tmpMoo.addrTo = item.addrTo;
tmpMoo.balance = item.balance;
tmpMoo.timeSpan = item.timeSpan;
tmpMoo.height = item.height;
tmpMo.balanceLst.Add(tmpMoo);
tmpLst.Add(tmpMo);
}
}
return(tmpLst);
}
}