public async Task <NodeDataHolderDetailedModel> Get([SwaggerParameter("The ERC 725 identity for the node", Required = true)] string nodeId)
{
TickerInfo tickerInfo = await TickerHelper.GetTickerInfo(_cache);
await using (var connection =
new MySqlConnection(OTHubSettings.Instance.MariaDB.ConnectionString))
{
var profile = await connection.QueryFirstOrDefaultAsync <NodeDataHolderDetailedModel>(DataHolderSql.GetDetailed, new { nodeId = nodeId,
userID = User?.Identity?.Name });
if (profile != null)
{
profile.Identities = (await connection.QueryAsync <NodeDetailedIdentity>(
@"SELECT i.Identity, bc.DisplayName BlockchainName, i.Stake, i.StakeReserved FROM otidentity i
JOIN blockchains bc ON bc.id = i.blockchainid
WHERE i.NodeId = @NodeId", new
{
nodeId = nodeId
})).ToArray();
profile.LiveTracUSDPrice = tickerInfo?.PriceUsd;
}
return(profile);
}
}
public async Task <NodesPerYearMonthResponse> GetJobsPerMonth()
{
if (_cache.TryGetValue("MyNodes-JobsPerMonth-" + User.Identity.Name, out var cached))
{
return((NodesPerYearMonthResponse)cached);
}
TickerInfo ticker = await TickerHelper.GetTickerInfo(_cache);
NodesPerYearMonthResponse response = new NodesPerYearMonthResponse();
await using (MySqlConnection connection =
new MySqlConnection(OTHubSettings.Instance.MariaDB.ConnectionString))
{
JobsPerMonthModel[] data = (await connection.QueryAsync <JobsPerMonthModel>(@"WITH JobsCTE AS (
SELECT
mn.DisplayName,
i.NodeId,
YEAR(o.FinalizedTimestamp) AS 'Year',
MONTH(o.FinalizedTimestamp) AS 'Month',
SUM(o.TokenAmountPerHolder) AS TokenAmount,
COUNT(o.OfferID) AS JobCount,
SUM((CASE WHEN u.USDPriceCalculationMode = 0 THEN ticker.Price ELSE @overrideUSDPrice END) * o.TokenAmountPerHolder) AS USDAmount
FROM otoffer o
JOIN otoffer_holders h ON h.OfferID = o.OfferID AND h.BlockchainID = o.BlockchainID
JOIN otidentity i ON i.Identity = h.Holder AND i.BlockchainID = o.BlockchainID
JOIN mynodes mn ON mn.NodeID = i.NodeId
JOIN users u ON u.ID = mn.UserID
LEFT JOIN ticker_trac ticker ON u.USDPriceCalculationMode = 0 AND ticker.Timestamp = (
SELECT MAX(TIMESTAMP)
FROM ticker_trac
WHERE TIMESTAMP <= o.FinalizedTimestamp)
WHERE mn.UserID = @userID
GROUP BY i.NodeId, YEAR(o.FinalizedTimestamp), MONTH(o.FinalizedTimestamp)
)
SELECT JobsCTE.DisplayName, JobsCTE.NodeId, JobsCTE.Year, JobsCTE.Month, JobsCTE.TokenAmount, JobsCTE.JobCount, JobsCTE.USDAmount
FROM JobsCTE
ORDER BY JobsCTE.DisplayName, JobsCTE.NodeID, JobsCTE.Year, JobsCTE.Month", new
{
userID = User.Identity.Name,
overrideUSDPrice = ticker?.PriceUsd ?? 0
})).ToArray();
IEnumerable <IGrouping <string, JobsPerMonthModel> > groupedByNodes = data.GroupBy(m => m.NodeId);
foreach (IGrouping <string, JobsPerMonthModel> nodeGroup in groupedByNodes)
{
List <JobsPerYear> years = new List <JobsPerYear>();
IEnumerable <IGrouping <int, JobsPerMonthModel> > groupedByYears =
nodeGroup.GroupBy(g => g.Year).OrderBy(g => g.Key);
JobsPerMonth lastMonth = null;
foreach (IGrouping <int, JobsPerMonthModel> yearGroup in groupedByYears)
{
Dictionary <int, JobsPerMonthModel> months = yearGroup
.ToDictionary(k => k.Month, v => v);
JobsPerYear year = new JobsPerYear
{
Year = yearGroup.Key.ToString(),
Active = yearGroup.Key == DateTime.Now.Year
};
for (int i = 1; i <= 12; i++)
{
JobsPerMonth month;
if (months.TryGetValue(i, out JobsPerMonthModel monthData))
{
month = new JobsPerMonth(monthData);
}
else
{
month = new JobsPerMonth
{
Month = CultureInfo.CurrentCulture.DateTimeFormat.GetAbbreviatedMonthName(i)
};
}
year.Months.Add(month);
if (lastMonth != null)
{
if (lastMonth.JobCount > month.JobCount || (lastMonth.JobCount == 0 && month.JobCount == 0))
{
month.Down = true;
}
}
else
{
month.Down = true;
}
lastMonth = month;
}
years.Add(year);
}
response.Nodes.Add(new NodeJobsPerYear
{
NodeId = nodeGroup.Key,
DisplayName = nodeGroup.First().DisplayName,
Years = years
});
}
}
JobsPerYear[] allYears = response.Nodes.SelectMany(n => n.Years).ToArray();
IEnumerable <IGrouping <string, JobsPerYear> > allGroupedByYear = allYears.GroupBy(a => a.Year);
response.AllNodes = new NodeJobsPerYear
{
DisplayName = "All Nodes",
Years = allGroupedByYear.Select(y => new JobsPerYear
{
Year = y.Key,
Active = y.First().Active,
Months = y.SelectMany(d => d.Months)
.GroupBy(m => m.Month)
.Select(m => new JobsPerMonth
{
Month = m.Key,
JobCount = m.Sum(d => d.JobCount),
TokenAmount = m.Sum(d => d.TokenAmount),
USDAmount = m.Sum(d => d.USDAmount)
})
.ToList()
}).OrderBy(y => y.Year).ToList()
};
JobsPerMonth previousMonth = null;
foreach (JobsPerYear year in response.AllNodes.Years)
{
foreach (JobsPerMonth month in year.Months)
{
if (previousMonth == null)
{
month.Down = true;
}
else
{
if (previousMonth.JobCount == 0 && month.JobCount == 0)
{
month.Down = true;
}
else if (previousMonth.JobCount > month.JobCount)
{
month.Down = true;
}
}
previousMonth = month;
}
}
_cache.Set("MyNodes-JobsPerMonth-" + User.Identity.Name, response, TimeSpan.FromSeconds(15));
return(response);
}
public async Task <RecentJobsByDay[]> GetRecentJobs([FromQuery] string nodeID)
{
if (string.IsNullOrWhiteSpace(nodeID))
{
nodeID = null;
}
if (_cache.TryGetValue($"MyNodes-GetRecentJobs-{User.Identity.Name}-{nodeID}", out var cached))
{
return((RecentJobsByDay[])cached);
}
TickerInfo ticker = await TickerHelper.GetTickerInfo(_cache);
await using (MySqlConnection connection =
new MySqlConnection(OTHubSettings.Instance.MariaDB.ConnectionString))
{
var jobs = (await connection.QueryAsync <RecentJobs>(@"SELECT mn.NodeID, mn.DisplayName, o.OfferID, o.HoldingTimeInMinutes, o.TokenAmountPerHolder, o.FinalizedTimestamp,
(CASE WHEN u.USDPriceCalculationMode = 0 THEN ticker.Price ELSE @overrideUSDPrice END) * o.TokenAmountPerHolder AS USDAmount,
b.DisplayName Blockchain, b.LogoLocation BlockchainLogo
FROM mynodes mn
JOIN users u ON u.ID = mn.UserID
JOIN otidentity i ON i.NodeId = mn.NodeID
JOIN otoffer_holders h ON h.Holder = i.Identity AND h.BlockchainID = i.BlockchainID
JOIN otoffer o ON o.OfferID = h.OfferID AND o.BlockchainID = i.BlockchainID
JOIN blockchains b ON b.ID = o.BlockchainID
LEFT JOIN ticker_trac ticker ON u.USDPriceCalculationMode = 0 AND ticker.Timestamp = (
SELECT MAX(TIMESTAMP)
FROM ticker_trac
WHERE TIMESTAMP <= o.FinalizedTimestamp)
WHERE o.FinalizedTimestamp >= DATE_Add(DATE(NOW()), INTERVAL -7 DAY) AND mn.UserID = @userID AND (@nodeID IS NULL OR @nodeID = mn.NodeID)
ORDER BY o.FinalizedTimestamp DESC", new
{
userID = User.Identity.Name,
overrideUSDPrice = ticker?.PriceUsd ?? 0,
nodeID = nodeID
})).ToArray();
List <RecentJobsByDay> days = new List <RecentJobsByDay>(7);
DateTime date = DateTime.Now.Date;
for (int i = 1; i <= 7; i++)
{
RecentJobsByDay day = new RecentJobsByDay();
day.Active = i == 1;
day.Day = CultureInfo.CurrentCulture.DateTimeFormat.GetAbbreviatedDayName(date.DayOfWeek);
day.Jobs = jobs.Where(j => j.FinalizedTimestamp.Date == date).ToArray();
days.Add(day);
date = date.AddDays(-1);
}
var data = days.ToArray();
_cache.Set($"MyNodes-GetRecentJobs-{User.Identity.Name}-{nodeID}", data, TimeSpan.FromSeconds(15));
return(data);
}
}
public async Task <NodeStats> GetNodeStats([FromQuery] string nodeID)
{
TickerInfo ticker = await TickerHelper.GetTickerInfo(_cache);
await using (MySqlConnection connection =
new MySqlConnection(OTHubSettings.Instance.MariaDB.ConnectionString))
{
using (var multi = await connection.QueryMultipleAsync(@"
SET @priceMode = (SELECT u.USDPriceCalculationMode FROM users u WHERE u.ID = @userID);
CREATE TEMPORARY TABLE JobsCTELocal (SELECT
i.NodeId,
SUM(o.TokenAmountPerHolder) AS TokenAmount,
SUM((CASE WHEN @priceMode = 0 THEN ticker.Price ELSE @overrideUSDPrice END) * o.TokenAmountPerHolder) AS USDAmount,
COUNT(o.OfferID) AS JobCount,
CASE WHEN mn.ID IS NOT NULL THEN 1 ELSE 0 END AS IsMyNode
FROM otoffer o
JOIN otoffer_holders h ON h.OfferID = o.OfferID AND h.BlockchainID = o.BlockchainID
JOIN otidentity i ON i.Identity = h.Holder AND i.BlockchainID = o.BlockchainID
LEFT JOIN mynodes mn ON mn.UserID = @userID AND mn.NodeID = i.NodeId
LEFT JOIN ticker_trac ticker ON @priceMode = 0 AND (i.NodeId = @nodeID OR mn.ID IS NOT null) AND ticker.Timestamp = (
SELECT MAX(TIMESTAMP)
FROM ticker_trac
WHERE TIMESTAMP <= o.FinalizedTimestamp)
WHERE i.Version > 0 AND i.NodeId != '0000000000000000000000000000000000000000'
GROUP BY i.NodeId);
SELECT * FROM (
SELECT l.NodeId
, l.IsMyNode
,SUM(l.TokenAmount) RewardTokenAmount
,SUM(l.USDAmount) RewardUSDAmount
, ROUND(PERCENT_RANK() OVER (
ORDER BY SUM(l.TokenAmount)
) * 100 ) RewardTokenAmountRank
,SUM(l.JobCount) JobCount
, ROUND(PERCENT_RANK() OVER (
ORDER BY SUM(l.JobCount)
) * 100) JobCountRank
FROM JobsCTELocal l
GROUP BY l.NodeId) X
WHERE (@nodeID IS NULL AND X.IsMyNode = 1) OR X.NodeId = @nodeID
;
SELECT * FROM (
SELECT
i.NodeId
,SUM(i.Stake) StakeTokenAmount
,SUM(i.Stake) * @overrideUSDPrice StakeUSDAmount
, ROUND(PERCENT_RANK() OVER (
ORDER BY SUM(i.Stake)
) * 100) StakeTokenAmountRank
,SUM(i.StakeReserved) StakeReservedTokenAmount
,SUM(i.StakeReserved) * @overrideUSDPrice StakeReservedUSDAmount
, ROUND(PERCENT_RANK() OVER (
ORDER BY SUM(i.StakeReserved)
) * 100) StakeReservedTokenAmountRank
FROM otidentity i
WHERE i.NodeId IN (SELECT j.NodeID FROM JobsCTELocal j) AND i.Version > 0 AND i.NodeId != '0000000000000000000000000000000000000000'
GROUP BY i.NodeId) X
WHERE (@nodeID IS NULL AND X.NodeId IN (SELECT j.NodeID FROM JobsCTELocal j WHERE j.IsMyNode = 1)) OR X.NodeId = @nodeID
", new
{
userID = User.Identity.Name,
overrideUSDPrice = ticker?.PriceUsd ?? 0,
nodeID
}))
{
NodeStatsModel1 model1;
NodeStatsModel2 model2;
if (!String.IsNullOrWhiteSpace(nodeID))
{
model1 = multi.ReadFirstOrDefault <NodeStatsModel1>();
model2 = multi.ReadFirstOrDefault <NodeStatsModel2>();
if (model1 == null || model2 == null)
{
return(null);
}
}
else
{
var model1rows = multi.Read <NodeStatsModel1>().ToArray();
model1 = new NodeStatsModel1
{
JobCount = model1rows.Sum(r => r.JobCount),
RewardTokenAmount = model1rows.Sum(r => r.RewardTokenAmount),
RewardUSDAmount = model1rows.Sum(r => r.RewardUSDAmount)
};
var model2rows = multi.Read <NodeStatsModel2>().ToArray();
model2 = new NodeStatsModel2
{
StakeReservedTokenAmount = model2rows.Sum(r => r.StakeReservedTokenAmount),
StakeReservedUSDAmount = model2rows.Sum(r => r.StakeReservedUSDAmount),
StakeTokenAmount = model2rows.Sum(r => r.StakeTokenAmount),
StakeUSDAmount = model2rows.Sum(r => r.StakeUSDAmount)
};
}
NodeStats stats = new NodeStats
{
TotalJobs = new NodeStats.NodeStatsNumeric
{
Value = model1.JobCount,
BetterThanActiveNodesPercentage = model1.JobCountRank
},
TotalLocked =
new NodeStats.NodeStatsToken
{
TokenAmount = model2.StakeReservedTokenAmount,
USDAmount = model2.StakeReservedUSDAmount,
BetterThanActiveNodesPercentage = model2.StakeReservedTokenAmountRank
},
TotalRewards =
new NodeStats.NodeStatsToken
{
TokenAmount = model1.RewardTokenAmount,
USDAmount = model1.RewardUSDAmount,
BetterThanActiveNodesPercentage = model1.RewardTokenAmountRank
},
TotalStaked = new NodeStats.NodeStatsToken
{
TokenAmount = model2.StakeTokenAmount,
USDAmount = model2.StakeUSDAmount,
BetterThanActiveNodesPercentage = model2.StakeTokenAmountRank
}
};
return(stats);
}
}
}
public async Task <HomeV3Model> HomeV3([FromQuery] bool excludeBreakdown)
{
string key = $"HomeV3-{excludeBreakdown}";
if (_cache.TryGetValue(key, out object homeModel))
{
return((HomeV3Model)homeModel);
}
TickerInfo tickerInfo = await TickerHelper.GetTickerInfo(_cache);
await using (var connection =
new MySqlConnection(OTHubSettings.Instance.MariaDB.ConnectionString))
{
HomeV3Model model = new HomeV3Model();
model.Blockchains = (await connection.QueryAsync <HomeV3BlockchainModel>(@"SELECT
b.Id BlockchainID,
b.GasTicker,
b.TokenTicker,
b.DisplayName BlockchainName,
b.LogoLocation,
(
SELECT COUNT(DISTINCT I.NodeId)
FROM OTOffer O
JOIN OTOffer_Holders H ON H.OfferID = O.OfferId
JOIN otidentity I ON I.Identity = H.Holder
WHERE O.BlockchainID = b.Id and O.IsFinalized = 1 AND NOW() <= DATE_ADD(O.FinalizedTimeStamp, INTERVAL + O.HoldingTimeInMinutes MINUTE)) ActiveNodes,
(
SELECT COUNT(*)
FROM otoffer
WHERE otoffer.IsFinalized = 1 AND otoffer.BlockchainID = b.Id
) TotalJobs,
(
SELECT COALESCE(SUM(CASE WHEN IsFinalized = 1 AND NOW() <= DATE_ADD(FinalizedTimeStamp, INTERVAL +HoldingTimeInMinutes MINUTE) THEN 1 ELSE 0 END), 0)
FROM otoffer WHERE blockchainid = b.id) AS ActiveJobs,
(select COALESCE(sum(Stake), 0) from otidentity WHERE blockchainid = b.id) StakedTokens,
(SELECT COUNT(*) FROM OTOffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS Jobs24H,
(SELECT AVG(otoffer.TokenAmountPerHolder) FROM otoffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS JobsReward24H,
(SELECT AVG(otoffer.HoldingTimeInMinutes) FROM OTOffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS JobsDuration24H,
(SELECT AVG(otoffer.DataSetSizeInBytes) FROM OTOffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS JobsSize24H,
(SELECT SUM(otoffer.TokenAmountPerHolder * 6) FROM OTOffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS TokensLocked24H,
(SELECT SUM(otcontract_holding_paidout.Amount) FROM otcontract_holding_paidout WHERE blockchainid = b.id and Timestamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS TokensPaidout24H,
(SELECT MIN(otoffer.EstimatedLambda) FROM otoffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS PriceFactorLow24H,
(SELECT MAX(otoffer.EstimatedLambda) FROM otoffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS PriceFactorHigh24H,
(SELECT MIN(otoffer.TokenAmountPerHolder) FROM otoffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS JobsRewardLow24H,
(SELECT MAX(otoffer.TokenAmountPerHolder) FROM otoffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS JobsRewardHigh24H,
(SELECT MIN(otoffer.HoldingTimeInMinutes) FROM OTOffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS JobsDurationLow24H,
(SELECT MAX(otoffer.HoldingTimeInMinutes) FROM OTOffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS JobsDurationHigh24H,
(SELECT MIN(otoffer.DataSetSizeInBytes) FROM OTOffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS JobsSizeLow24H,
(SELECT MAX(otoffer.DataSetSizeInBytes) FROM OTOffer WHERE blockchainid = b.id and IsFinalized = 1 AND FinalizedTimeStamp >= DATE_Add(NOW(), INTERVAL -1 DAY)) AS JobsSizeHigh24H
FROM blockchains b
order by b.id desc")).ToArray();
foreach (HomeV3BlockchainModel blockchain in model.Blockchains)
{
blockchain.Fees = (await connection.QueryFirstOrDefaultAsync <HomeFeesModel>(@"SELECT
bc.ShowCostInUSD,
CAST(AVG((CAST(oc.GasUsed AS DECIMAL(20,4)) * (CAST(oc.GasPrice AS DECIMAL(20,6)) / 1000000000000000000)) * (CASE WHEN bc.ShowCostInUSD AND bc.IsGasStableCoin = 0 THEN ocTicker.Price ELSE 1 END)) AS DECIMAL(20,6)) JobCreationCost,
CAST(AVG((CAST(of.GasUsed AS DECIMAL(20,4)) * (CAST(of.GasPrice AS DECIMAL(20,6)) / 1000000000000000000)) * (CASE WHEN bc.ShowCostInUSD AND bc.IsGasStableCoin = 0 THEN ofTicker.Price ELSE 1 END)) AS DECIMAL(20,6)) JobFinalisedCost
FROM blockchains bc
LEFT JOIN otcontract_holding_offercreated oc ON bc.ID = oc.BlockchainID AND oc.Timestamp >= DATE_Add(NOW(), INTERVAL -1 DAY)
LEFT JOIN otcontract_holding_offerfinalized of ON of.OfferID = oc.OfferID AND of.BlockchainID = oc.BlockchainID AND of.Timestamp >= DATE_Add(NOW(), INTERVAL -1 DAY)
LEFT JOIN ticker_eth_to_usd ocTicker ON bc.IsGasStableCoin = 0 AND ocTicker.Timestamp = (
SELECT MAX(TIMESTAMP)
FROM ticker_eth_to_usd
WHERE TIMESTAMP <= oc.Timestamp)
LEFT JOIN ticker_eth_to_usd ofTicker ON bc.IsGasStableCoin = 0 AND ofTicker.Timestamp = (
SELECT MAX(TIMESTAMP)
FROM ticker_eth_to_usd
WHERE TIMESTAMP <= of.Timestamp)
WHERE bc.ID = @blockchainID", new
{
blockchainID = blockchain.BlockchainID
}));
decimal?payoutFee = (await connection.ExecuteScalarAsync <decimal?>(@"SELECT
CAST(AVG((CAST(po.GasUsed AS DECIMAL(20,4)) * (CAST(po.GasPrice as decimal(20,6)) / 1000000000000000000)) * (CASE WHEN bc.ShowCostInUSD AND bc.IsGasStableCoin = 0 THEN ocTicker.Price ELSE 1 END)) AS DECIMAL(20, 8)) PayoutCost
FROM blockchains bc
LEFT JOIN otcontract_holding_paidout po ON po.BlockchainID = bc.ID AND po.Timestamp >= DATE_Add(NOW(), INTERVAL -1 DAY)
LEFT JOIN ticker_eth_to_usd ocTicker ON bc.IsGasStableCoin = 0 AND ocTicker.Timestamp = (
SELECT MAX(TIMESTAMP)
FROM ticker_eth_to_usd
WHERE TIMESTAMP <= po.Timestamp)
WHERE bc.ID = @blockchainID", new
{
blockchainID = blockchain.BlockchainID
}));
blockchain.Fees.PayoutCost = payoutFee;
if (blockchain.BlockchainName != "Ethereum")
{
blockchain.HoursTillFirstJob = await connection.ExecuteScalarAsync <int?>(@"
WITH CTE AS (
SELECT
I.Identity,
I.NodeID,
(
SELECT o.FinalizedTimestamp
FROM otoffer_holders h
JOIN otoffer o ON o.OfferID = h.OfferID
WHERE h.Holder = i.Identity
ORDER BY o.FinalizedTimestamp
LIMIT 1
) FirstOfferDate,
bb.Timestamp CreatedDate
FROM otidentity i
JOIN otcontract_profile_identitycreated ic ON ic.NewIdentity = i.Identity AND ic.BlockchainID = i.BlockchainID
JOIN ethblock bb ON bb.BlockchainID = ic.BlockchainID AND bb.BlockNumber = ic.BlockNumber
WHERE i.BlockchainID = @id AND i.VERSION > 0 AND (
SELECT o.FinalizedTimestamp
FROM otoffer_holders h
JOIN otoffer o ON o.OfferID = h.OfferID
WHERE h.Holder = i.Identity
ORDER BY o.FinalizedTimestamp
LIMIT 1
) >= DATE_Add(NOW(), INTERVAL -7 DAY)
ORDER BY FirstOfferDate DESC
)
SELECT AVG(TIMESTAMPDIFF(HOUR, CreatedDate, FirstOfferDate)) TimeTillFirstJob FROM CTE", new
{
id = blockchain.BlockchainID
});
}
}
model.PriceUsd = tickerInfo?.PriceUsd;
model.PercentChange24H = tickerInfo?.PercentChange24H;
model.CirculatingSupply = tickerInfo?.CirculatingSupply;
model.MarketCapUsd = tickerInfo?.MarketCapUsd;
model.Volume24HUsd = tickerInfo?.Volume24HUsd;
model.PriceBtc = tickerInfo?.PriceBtc;
model.All = new HomeV3BlockchainModel
{
BlockchainID = 0,
BlockchainName = "All Blockchains",
ActiveJobs = model.Blockchains.Sum(b => b.ActiveJobs),
ActiveNodes = model.Blockchains.Sum(b => b.ActiveNodes),
Jobs24H = model.Blockchains.Sum(b => b.Jobs24H),
JobsDuration24H = (long?)model.Blockchains.Select(b => b.JobsDuration24H).DefaultIfEmpty(null).Average(),
JobsReward24H = (decimal?)model.Blockchains.Where(b => b.JobsReward24H.HasValue).Average(b => b.JobsReward24H),
JobsSize24H = (long?)model.Blockchains.Where(b => b.JobsSize24H.HasValue).Average(b => b.JobsSize24H),
StakedTokens = model.Blockchains.Sum(b => b.StakedTokens),
TotalJobs = model.Blockchains.Sum(b => b.TotalJobs),
TokenTicker = model.Blockchains.Select(b => b.TokenTicker).Aggregate((a, b) => a + " | " + b),
TokensLocked24H = model.Blockchains.Select(b => b.TokensLocked24H).DefaultIfEmpty(null).Sum(),
TokensPaidout24H = model.Blockchains.Select(b => b.TokensPaidout24H).DefaultIfEmpty(null).Sum(),
PriceFactorLow24H = model.Blockchains.Select(b => b.PriceFactorLow24H).DefaultIfEmpty(null).Min(),
PriceFactorHigh24H = model.Blockchains.Select(b => b.PriceFactorHigh24H).DefaultIfEmpty(null).Max(),
};
if (excludeBreakdown)
{
model.Blockchains = null;
}
_cache.Set(key, model, TimeSpan.FromSeconds(45));
return(model);
}
}