private async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestPlayerTotal(string fieldName, bool fieldValueCanBeNegative, bool isFieldingStatistic, string extraSelectFields, string outerQueryIncludingOrderBy, string totalInningsFilter, StatisticsFilter filter)
{
var clonedFilter = filter.Clone();
clonedFilter.SwapBattingFirstFilter = isFieldingStatistic;
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(clonedFilter);
var group = "GROUP BY PlayerId, PlayerRoute";
var having = fieldValueCanBeNegative ? "HAVING 1=1" : $"HAVING SUM({fieldName}) > 0";
var minimumValue = fieldValueCanBeNegative ? string.Empty : $"AND {fieldName} >= 0";
// The result set can be limited in two mutually-exlusive ways:
// 1. Max results (eg top ten) but where results beyond but equal to the max are also included
// 2. Paging
var preQuery = string.Empty;
var offsetWithExtraResults = string.Empty;
var offsetPaging = string.Empty;
if (clonedFilter.MaxResultsAllowingExtraResultsIfValuesAreEqual.HasValue)
{
// Get the values from what should be the last row according to the maximum number of results.
preQuery = $@"DECLARE @MaxResult int;
SELECT @MaxResult = SUM({fieldName}) FROM {Tables.PlayerInMatchStatistics} WHERE {fieldName} IS NOT NULL {minimumValue} {where} {group} {having} ORDER BY SUM({fieldName}) DESC
OFFSET {clonedFilter.MaxResultsAllowingExtraResultsIfValuesAreEqual - 1} ROWS FETCH NEXT 1 ROWS ONLY; ";
// If @MaxResult IS NULL there are fewer rows than the requested maximum, so just fetch all.
// Otherwise look for results that are greater than or equal to the value(s) in the last row retrieved above.
offsetWithExtraResults = $"AND (@MaxResult IS NULL OR SUM({fieldName}) >= @MaxResult) ";
// Add an ORDER BY clause to sort the results, unless we're relying on an outer query to do that because it's not valid in a sub-query
if (string.IsNullOrEmpty(outerQueryIncludingOrderBy))
{
offsetWithExtraResults += $"ORDER BY SUM({fieldName}) DESC, TotalInnings ASC, TotalMatches ASC";
}
}
else
{
offsetPaging = $"ORDER BY SUM({fieldName}) DESC, TotalInnings ASC, TotalMatches ASC OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";
parameters.Add("@PageOffset", clonedFilter.Paging.PageSize * (clonedFilter.Paging.PageNumber - 1));
parameters.Add("@PageSize", clonedFilter.Paging.PageSize);
}
var totalInningsQuery = !string.IsNullOrEmpty(totalInningsFilter) ? $"SELECT COUNT(PlayerInMatchStatisticsId) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {totalInningsFilter} {where}" : "NULL";
var sql = $@"SELECT PlayerId, PlayerRoute,
(SELECT COUNT(DISTINCT MatchId) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {where}) AS TotalMatches,
({totalInningsQuery}) AS TotalInnings,
(SELECT SUM({ fieldName}) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {where}) AS Total
<<SELECT>>
FROM {Tables.PlayerInMatchStatistics} AS s
WHERE {fieldName} IS NOT NULL {minimumValue} {where}
{group}
{having}
{offsetWithExtraResults}
{offsetPaging}";
if (!string.IsNullOrEmpty(extraSelectFields))
{
extraSelectFields = extraSelectFields.Replace("<<WHERE>>", where);
sql = sql.Replace("<<SELECT>>", extraSelectFields);
}
else
{
sql = sql.Replace("<<SELECT>>", string.Empty);
}
if (!string.IsNullOrEmpty(outerQueryIncludingOrderBy))
{
sql = outerQueryIncludingOrderBy.Replace("<<QUERY>>", sql);
}
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var results = await connection.QueryAsync <Player, BestStatistic, StatisticsResult <BestStatistic> >($"{preQuery} {sql}",
(player, totals) =>
{
totals.Player = player;
return(new StatisticsResult <BestStatistic>
{
Result = totals
});
},
parameters,
splitOn : $"TotalMatches",
commandTimeout : 60).ConfigureAwait(false);
var players = await _playerDataSource.ReadPlayers(new PlayerFilter { PlayerIds = results.Select(x => x.Result.Player.PlayerId.Value).ToList() }).ConfigureAwait(false);
foreach (var result in results)
{
result.Result.Player = players.Single(x => x.PlayerId == result.Result.Player.PlayerId);
}
return(results);
}
}
/// <summary>
/// Used to get the AdminUserStatics
/// </summary>
/// <returns></returns>
public JsonResult GetAdminUserStatics([DataSourceRequest] DataSourceRequest request, StatisticsFilter model)
{
try
{
var token = objToken.GetAPIToken();
model.Request = request;
HttpResponseMessage httpResponse;
model.UserID = model.UserID == "1" ? null : model.UserID;
// Call web API method
httpResponse = API.Post.PostObject("AdminUserStatistics/GetAllUserStatisticsForGrid", model, token);
string response = httpResponse.Content.ReadAsStringAsync().Result;
var data = JsonConvert.DeserializeObject <List <AdminUserStatisticModel> >(response).ToList();
return(Json(data.ToDataSourceResult(request)));
}
catch (Exception ex)
{
return(Json("", JsonRequestBehavior.AllowGet));
}
}
/// <inheritdoc/>
public async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadMostRunsScored(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
var outerQuery = @"SELECT PlayerId, PlayerRoute, TotalMatches, TotalInnings, Total,
CASE WHEN TotalDismissals > 0 THEN CAST(Total AS DECIMAL)/ TotalDismissals ELSE NULL END AS Average
FROM(
<<QUERY>>
) AS BestTotal
ORDER BY Total DESC, TotalInnings ASC, TotalMatches ASC";
var extraSelectFields = $", (SELECT COUNT(PlayerInMatchStatisticsId) FROM { Tables.PlayerInMatchStatistics } WHERE PlayerId = s.PlayerId AND PlayerWasDismissed = 1 AND RunsScored IS NOT NULL <<WHERE>>) AS TotalDismissals";
return(await ReadBestPlayerTotal("RunsScored", true, false, extraSelectFields, outerQuery, $"AND RunsScored IS NOT NULL", filter).ConfigureAwait(false));
}
/// <inheritdoc/>
public async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadMostRunOuts(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadBestPlayerTotal("RunOuts", false, true, null, null, string.Empty, filter).ConfigureAwait(false));
}
private async Task ActAndAssertStatistics(StatisticsFilter filter, SqlServerBestPlayerAverageStatisticsDataSource dataSource, Func <Stoolball.Matches.Match, bool> matchFilter, Func <MatchInnings, bool> battingInningsFilter, Func <MatchInnings, bool> bowlingInningsFilter)
{
var results = await dataSource.ReadBestEconomyRate(filter).ConfigureAwait(false);
var oversHelper = new OversHelper();
var expected = _databaseFixture.TestData.Players.Select(p => new BestStatistic
{
Player = p,
TotalMatches = (int)_databaseFixture.TestData.Matches
.Where(matchFilter)
.Count(m => m.MatchInnings.Where(battingInningsFilter).Any(mi => mi.PlayerInnings.Any(pi => pi.Batter.Player.PlayerId == p.PlayerId)) ||
m.MatchInnings.Where(bowlingInningsFilter).Any(mi =>
mi.PlayerInnings.Any(pi => pi.DismissedBy?.Player.PlayerId == p.PlayerId || pi.Bowler?.Player.PlayerId == p.PlayerId) ||
mi.OversBowled.Any(o => o.Bowler.Player.PlayerId == p.PlayerId) ||
mi.BowlingFigures.Any(bf => bf.Bowler.Player.PlayerId == p.PlayerId)
) ||
m.Awards.Any(aw => aw.PlayerIdentity.Player.PlayerId == p.PlayerId)),
TotalInnings = (int)_databaseFixture.TestData.Matches
.Where(matchFilter)
.SelectMany(m => m.MatchInnings)
.Where(bowlingInningsFilter)
.SelectMany(mi => mi.BowlingFigures)
.Count(pi => pi.Bowler.Player.PlayerId == p.PlayerId && pi.RunsConceded.HasValue),
Average = (_databaseFixture.TestData.Matches
.Where(matchFilter)
.SelectMany(m => m.MatchInnings)
.Where(bowlingInningsFilter)
.SelectMany(mi => mi.BowlingFigures)
.Any(bf => bf.Bowler.Player.PlayerId == p.PlayerId && bf.Overs > 0 && bf.RunsConceded.HasValue) ?
((decimal)_databaseFixture.TestData.Matches
.Where(matchFilter)
.SelectMany(m => m.MatchInnings)
.Where(bowlingInningsFilter)
.SelectMany(mi => mi.BowlingFigures)
.Where(bf => bf.Bowler.Player.PlayerId == p.PlayerId && bf.RunsConceded.HasValue)
.Sum(pi => pi.RunsConceded))
/
_databaseFixture.TestData.Matches
.Where(matchFilter)
.SelectMany(m => m.MatchInnings)
.Where(bowlingInningsFilter)
.SelectMany(mi => mi.BowlingFigures)
.Where(bf => bf.Bowler.Player.PlayerId == p.PlayerId && bf.RunsConceded.HasValue)
.Sum(bf => ((decimal)oversHelper.OversToBallsBowled(bf.Overs.Value)) / StatisticsConstants.BALLS_PER_OVER)
: (decimal?)null)
}).Where(x => x.Average.HasValue);
foreach (var player in expected)
{
var result = results.SingleOrDefault(x => x.Result.Player.PlayerId == player.Player.PlayerId);
Assert.NotNull(result);
Assert.Equal(player.TotalMatches, result.Result.TotalMatches);
Assert.Equal(player.TotalInnings, result.Result.TotalInnings);
if (player.Average.HasValue)
{
Assert.Equal(player.Average.Value.AccurateToTwoDecimalPlaces(), result.Result.Average.Value.AccurateToTwoDecimalPlaces());
}
else
{
Assert.Null(result.Result.Average);
}
}
}
/// <inheritdoc />
public async Task <IEnumerable <StatisticsResult <BowlingFigures> > > ReadBowlingFigures(StatisticsFilter filter, StatisticsSortOrder sortOrder)
{
filter = filter ?? new StatisticsFilter();
var cachePolicy = _policyRegistry.Get <IAsyncPolicy>(CacheConstants.StatisticsPolicy);
return(await cachePolicy.ExecuteAsync(async context => await _statisticsDataSource.ReadBowlingFigures(filter, sortOrder).ConfigureAwait(false), new Context(nameof(ReadBowlingFigures) + _statisticsFilterSerializer.Serialize(filter) + sortOrder.ToString())));
}
/// <inheritdoc/>
public async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestEconomyRate(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadBestPlayerAverage("RunsConceded", $"CAST(BallsBowled AS DECIMAL)/{StatisticsConstants.BALLS_PER_OVER}", 1, "ASC", true, $"AND RunsConceded IS NOT NULL", filter).ConfigureAwait(false));
}
private async Task ActAndAssertStatistics(StatisticsFilter filter, SqlServerBestPlayerTotalStatisticsDataSource dataSource, Func <Stoolball.Matches.Match, bool> matchFilter, Func <MatchInnings, bool> matchInningsFilter)
{
var results = await dataSource.ReadMostRunsScored(filter).ConfigureAwait(false);
var expected = _databaseFixture.TestData.Players.Select(p => new BestStatistic
{
Player = p,
TotalMatches = (int)_databaseFixture.TestData.Matches
.Where(matchFilter)
.Count(m => m.MatchInnings.Where(matchInningsFilter).Any(mi =>
mi.PlayerInnings.Any(pi => pi.Batter.Player.PlayerId == p.PlayerId || pi.DismissedBy?.Player.PlayerId == p.PlayerId || pi.Bowler?.Player.PlayerId == p.PlayerId) ||
mi.OversBowled.Any(o => o.Bowler.Player.PlayerId == p.PlayerId) ||
mi.BowlingFigures.Any(bf => bf.Bowler.Player.PlayerId == p.PlayerId)
) || m.Awards.Any(aw => aw.PlayerIdentity.Player.PlayerId == p.PlayerId)),
TotalInnings = (int)_databaseFixture.TestData.Matches
.Where(matchFilter)
.SelectMany(m => m.MatchInnings)
.Where(matchInningsFilter)
.SelectMany(mi => mi.PlayerInnings)
.Count(pi => pi.Batter.Player.PlayerId == p.PlayerId && pi.RunsScored.HasValue),
Total = (int)_databaseFixture.TestData.Matches
.Where(matchFilter)
.SelectMany(m => m.MatchInnings)
.Where(matchInningsFilter)
.SelectMany(mi => mi.PlayerInnings)
.Where(pi => pi.Batter.Player.PlayerId == p.PlayerId && pi.RunsScored.HasValue)
.Sum(pi => pi.RunsScored),
Average = (_databaseFixture.TestData.Matches
.Where(matchFilter)
.SelectMany(m => m.MatchInnings)
.Where(matchInningsFilter)
.SelectMany(mi => mi.PlayerInnings)
.Any(pi => pi.Batter.Player.PlayerId == p.PlayerId && StatisticsConstants.DISMISSALS_THAT_ARE_OUT.Contains(pi.DismissalType) && pi.RunsScored.HasValue) ?
((decimal)_databaseFixture.TestData.Matches
.Where(matchFilter)
.SelectMany(m => m.MatchInnings)
.Where(matchInningsFilter)
.SelectMany(mi => mi.PlayerInnings)
.Where(pi => pi.Batter.Player.PlayerId == p.PlayerId && pi.RunsScored.HasValue)
.Sum(pi => pi.RunsScored))
/
_databaseFixture.TestData.Matches
.Where(matchFilter)
.SelectMany(m => m.MatchInnings)
.Where(matchInningsFilter)
.SelectMany(mi => mi.PlayerInnings)
.Count(pi => pi.Batter.Player.PlayerId == p.PlayerId && StatisticsConstants.DISMISSALS_THAT_ARE_OUT.Contains(pi.DismissalType) && pi.RunsScored.HasValue)
: (decimal?)null)
}).Where(x => x.Total > 0);
foreach (var player in expected)
{
var result = results.SingleOrDefault(x => x.Result.Player.PlayerId == player.Player.PlayerId);
Assert.NotNull(result);
Assert.Equal(player.TotalMatches, result.Result.TotalMatches);
Assert.Equal(player.TotalInnings, result.Result.TotalInnings);
Assert.Equal(player.Total, result.Result.Total);
if (player.Average.HasValue)
{
Assert.Equal(player.Average.Value.AccurateToTwoDecimalPlaces(), result.Result.Average.Value.AccurateToTwoDecimalPlaces());
}
else
{
Assert.Null(result.Result.Average);
}
}
}
/// <inheritdoc/>
public async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestBattingAverage(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadBestPlayerAverage("RunsScored", "CAST(PlayerWasDismissed AS INT)", 1, "DESC", false, $"AND RunsScored IS NOT NULL", filter).ConfigureAwait(false));
}
/// <inheritdoc/>
public async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestBowlingAverage(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadBestPlayerAverage("RunsConceded", "Wickets", 1, "ASC", true, $"AND RunsConceded IS NOT NULL", filter).ConfigureAwait(false));
}
private async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestPlayerAverage(string divideThisField, string byThisField, int multiplier, string sortOrder, bool isFieldingStatistic, string inningsFilter, StatisticsFilter filter)
{
var clonedFilter = filter.Clone();
clonedFilter.SwapBattingFirstFilter = isFieldingStatistic;
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(clonedFilter);
var sql = $@"SELECT PlayerId, PlayerRoute, TotalMatches, TotalInnings, Average
FROM(
SELECT PlayerId, PlayerRoute, (CAST(SUM({divideThisField}) AS DECIMAL) / SUM({byThisField}))*{multiplier} AS Average,
(SELECT COUNT(DISTINCT MatchId) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {where}) AS TotalMatches,
(SELECT COUNT(PlayerInMatchStatisticsId) FROM { Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {inningsFilter} {where}) AS TotalInnings
FROM {Tables.PlayerInMatchStatistics} AS s
WHERE 1=1 {inningsFilter} {where}
GROUP BY PlayerId, PlayerRoute
HAVING SUM({byThisField}) > 0
AND (SELECT COUNT(PlayerInMatchStatisticsId) FROM {Tables.PlayerInMatchStatistics} WHERE PlayerId = s.PlayerId {inningsFilter} {where}) >= @MinimumQualifyingInnings
ORDER BY CAST(SUM({divideThisField}) AS DECIMAL) / SUM({byThisField}) {sortOrder}, TotalInnings DESC, TotalMatches DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY
) AS BestAverage
ORDER BY Average {sortOrder}, TotalInnings DESC, TotalMatches DESC";
parameters.Add("@MinimumQualifyingInnings", filter.MinimumQualifyingInnings ?? 1);
parameters.Add("@PageOffset", clonedFilter.Paging.PageSize * (clonedFilter.Paging.PageNumber - 1));
parameters.Add("@PageSize", clonedFilter.Paging.PageSize);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var results = await connection.QueryAsync <Player, BestStatistic, StatisticsResult <BestStatistic> >(sql,
(player, totals) =>
{
totals.Player = player;
return(new StatisticsResult <BestStatistic>
{
Result = totals
});
},
parameters,
splitOn : $"TotalMatches",
commandTimeout : 60).ConfigureAwait(false);
var players = await _playerDataSource.ReadPlayers(new PlayerFilter { PlayerIds = results.Select(x => x.Result.Player.PlayerId.Value).ToList() }).ConfigureAwait(false);
foreach (var result in results)
{
result.Result.Player = players.Single(x => x.PlayerId == result.Result.Player.PlayerId);
}
return(results);
}
}
private async Task <int> ReadTotalPlayersWithData(string divideThisField, string byThisField, bool requireBothFields, StatisticsFilter filter)
{
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(filter);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
var sql = $@"SELECT COUNT(PlayerId) FROM (
SELECT PlayerId FROM {Tables.PlayerInMatchStatistics}
WHERE {divideThisField} IS NOT NULL {(requireBothFields ? $"AND {byThisField} IS NOT NULL" : string.Empty)} {where}
GROUP BY PlayerId
HAVING SUM({byThisField}) > 0 AND
COUNT(PlayerInMatchStatisticsId) >= @MinimumQualifyingInnings
) AS Total";
parameters.Add("@MinimumQualifyingInnings", filter.MinimumQualifyingInnings ?? 1);
return(await connection.ExecuteScalarAsync <int>(sql, parameters).ConfigureAwait(false));
}
}
/// <inheritdoc />
public async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestEconomyRate(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
var cachePolicy = _policyRegistry.Get <IAsyncPolicy>(CacheConstants.StatisticsPolicy);
return(await cachePolicy.ExecuteAsync(async context => await _statisticsDataSource.ReadBestEconomyRate(filter).ConfigureAwait(false), new Context(nameof(ReadBestEconomyRate) + _statisticsFilterSerializer.Serialize(filter))));
}
/// <summary>
/// Used to get the single admin user statistics
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public AdminUserStatisticModel GetUserStatistics(StatisticsFilter model)
{
AdminUserStatisticModel objAdminUserStatisticModel = new AdminUserStatisticModel();
var unitOfWork = new UnitOfWork <CarMDEntities>();
var diagnosticReportFixRepo = unitOfWork.GetRepository <DiagnosticReportFixFeedback>();
var diagnosticReportRepo = unitOfWork.GetRepository <DiagnosticReport>();
var fixRepo = unitOfWork.GetRepository <Fix>();
var fixAdminUser = unitOfWork.GetRepository <AdminUser>();
List <int?> codeSystemList = new List <int?>();
codeSystemList.Add(0);
codeSystemList.Add(1);
codeSystemList.Add(2);
codeSystemList.Add(3);
//User
var query_User = (from u in fixAdminUser.DoQuery()
join d in diagnosticReportFixRepo.DoQuery()
on u.AdminUserId equals d.AdminUserId_FeedbackReviewedBy
where d.AdminUserId_FeedbackReviewedBy == model.UserID
select u
).FirstOrDefault();
//Reports Closed(New Fix Added)
var query_ReportsClosedNewFixAdded = (from d in diagnosticReportFixRepo.DoQuery()
where d.DiagnosticReportFixFeedbackStatus == 3 &&
codeSystemList.Contains(d.DiagnosticReportErrorCodeSystemType) &&
(model.StartDate == null || d.FeedbackReviewedDateTimeUTC >= model.StartDate) &&
(model.EndDate == null || d.FeedbackReviewedDateTimeUTC <= model.EndDate) &&
d.AdminUserId_FeedbackReviewedBy == model.UserID
select d.DiagnosticReportId).Count();
//Reports Closed(Existing Fix Selected)
var query_ReportsClosedExistingFixSelected = (from d in diagnosticReportFixRepo.DoQuery()
where (d.DiagnosticReportFixFeedbackStatus == 4 &&
codeSystemList.Contains(d.DiagnosticReportErrorCodeSystemType)) &&
(model.StartDate == null || d.FeedbackReviewedDateTimeUTC >= model.StartDate) &&
(model.EndDate == null || d.FeedbackReviewedDateTimeUTC <= model.EndDate) &&
d.AdminUserId_FeedbackReviewedBy == model.UserID
select d.DiagnosticReportId).Count();
// Reports Closed(Rejected)
var query_ReportsClosedRejected = (from d in diagnosticReportFixRepo.DoQuery()
where (d.DiagnosticReportFixFeedbackStatus == 9 &&
codeSystemList.Contains(d.DiagnosticReportErrorCodeSystemType)) &&
(model.StartDate == null || d.FeedbackReviewedDateTimeUTC >= model.StartDate) &&
(model.EndDate == null || d.FeedbackReviewedDateTimeUTC <= model.EndDate) &&
d.AdminUserId_FeedbackReviewedBy == model.UserID
select d.DiagnosticReportId).Count();
List <int?> FixFeedbackList = new List <int?>();
FixFeedbackList.Add(3);
FixFeedbackList.Add(4);
// # of Fixes from Fix Report
var query_NumOfFixesFromFixReport = (from d in diagnosticReportRepo.DoQuery()
join dfix in diagnosticReportFixRepo.DoQuery()
on d.DiagnosticReportId equals dfix.DiagnosticReportId
where
(d.CreatedDateTimeUTC >= model.StartDate || model.StartDate == null)
&&
(d.CreatedDateTimeUTC <= model.EndDate || model.EndDate == null)
&&
(
(
d.AdminUserId_PwrWorkingOnFix == model.UserID
||
d.AdminUserId_AbsWorkingOnFix == model.UserID
||
d.AdminUserId_SrsWorkingOnFix == model.UserID
)
&&
(
d.PwrDiagnosticReportFixStatusWhenCreated == 2 // There was no fix available when report was created
&&
dfix.DiagnosticReportErrorCodeSystemType == 0
&&
FixFeedbackList.Contains(dfix.DiagnosticReportFixFeedbackStatus) //The new fix provided was approved or an existing fix was selected.
)
)
||
(
(
d.AdminUserId_PwrWorkingOnFix == model.UserID
||
d.AdminUserId_AbsWorkingOnFix == model.UserID
||
d.AdminUserId_SrsWorkingOnFix == model.UserID
)
&&
(
d.Obd1DiagnosticReportFixStatusWhenCreated == 2 // There was no fix available when report was created
&&
dfix.DiagnosticReportErrorCodeSystemType == 1
&&
FixFeedbackList.Contains(dfix.DiagnosticReportFixFeedbackStatus)//The new fix provided was approved or an existing fix was selected.
)
)
||
(
(
d.AdminUserId_PwrWorkingOnFix == model.UserID
||
d.AdminUserId_AbsWorkingOnFix == model.UserID
||
d.AdminUserId_SrsWorkingOnFix == model.UserID
)
&&
(
d.AbsDiagnosticReportFixStatusWhenCreated == 2 // There was no fix available when report was created
&&
dfix.DiagnosticReportErrorCodeSystemType == 2
&&
FixFeedbackList.Contains(dfix.DiagnosticReportFixFeedbackStatus) //The new fix provided was approved or an existing fix was selected.
)
)
||
(
(
d.AdminUserId_PwrWorkingOnFix == model.UserID
||
d.AdminUserId_AbsWorkingOnFix == model.UserID
||
d.AdminUserId_SrsWorkingOnFix == model.UserID
)
&&
(
d.AbsDiagnosticReportFixStatusWhenCreated == 2 // There was no fix available when report was created
&&
dfix.DiagnosticReportErrorCodeSystemType == 3
&&
FixFeedbackList.Contains(dfix.DiagnosticReportFixFeedbackStatus) //The new fix provided was approved or an existing fix was selected.
)
)
select d.DiagnosticReportId).Count();
//# of Direct Fixes
var query_NumOfDirectFixes = (from f in fixRepo.DoQuery()
where f.CreatedByAdminUserId == model.UserID &&
(model.StartDate == null || f.CreatedDateTimeUTC >= model.StartDate) &&
(model.EndDate == null || f.CreatedDateTimeUTC >= model.EndDate)
select f.FixId
).Count();
// Total Reports
var NumOfTotalReport = query_ReportsClosedNewFixAdded + query_ReportsClosedExistingFixSelected + query_ReportsClosedRejected;
if (NumOfTotalReport == 0)
{
NumOfTotalReport = query_NumOfFixesFromFixReport;
}
if (query_User == null)
{
objAdminUserStatisticModel.User = "";
}
else
{
objAdminUserStatisticModel.User = query_User.FirstName + " " + query_User.LastName;
}
objAdminUserStatisticModel.ReportsClosedNewFixAdded = query_ReportsClosedNewFixAdded;
objAdminUserStatisticModel.ReportsClosedExistingFixSelected = query_ReportsClosedExistingFixSelected;
objAdminUserStatisticModel.ReportsClosedRejected = query_ReportsClosedRejected;
objAdminUserStatisticModel.NumOfFixesFromFixReport = query_NumOfFixesFromFixReport;
objAdminUserStatisticModel.NumOfDirectFixes = query_NumOfDirectFixes;
objAdminUserStatisticModel.NumOfTotalReport = NumOfTotalReport;
return(objAdminUserStatisticModel);
}
/// <inheritdoc/>
public async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestBattingStrikeRate(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadBestPlayerAverage("RunsScored", "BallsFaced", 100, "DESC", false, $"AND RunsScored IS NOT NULL AND BallsFaced IS NOT NULL", filter).ConfigureAwait(false));
}
public void Config()
{
reportType = new ReportType()
{
Id = "5d027ea59b358d247cd219az",
Name = "comment"
};
report = new Report()
{
Id = "5d247a04eff1030d7c5209a0",
ReportTypeId = "5d247a04eff1030d7c5209a3",
TargetId = "5d247a04eff1030d7c52034e",
Content = "vi pham",
Date = DateTime.Now,
IsResolved = false,
ReporterId = "5d247a04eff1030d7c5209a0",
ReportType = reportType,
Target = null
};
user = new User()
{
Id = "5d300f07a346270001a5bef4",
Active = true,
Address = "Nam Dinh",
Avatar = "https://storage.googleapis.com/trip-sharing-final-image-bucket/image-default-user-avatar.png",
ContributionPoint = 0,
CreatedDate = DateTime.Now,
DisplayName = "PhongTv",
Dob = DateTime.Parse("02/01/1997"),
FirstName = "Tran",
FollowerCount = 0,
FollowingCount = 34,
Gender = true,
Interested = null,
IsFirstTime = false,
LastName = "phong",
UserName = "******"
};
userSecond = new User()
{
Id = "5d027ea59b358d212o3iu456b",
Active = true,
Address = "Nam Dinh",
Avatar = "",
ContributionPoint = 0,
CreatedDate = DateTime.Now,
DisplayName = "PhongTv",
Dob = DateTime.Parse("02/01/1997"),
FirstName = "Tran",
FollowerCount = 0,
FollowingCount = 34,
Gender = true,
Interested = null,
IsFirstTime = false,
LastName = "phong",
UserName = "******"
};
claims = new ClaimsIdentity(new Claim[]
{
new Claim(ClaimTypes.Name, "abc"),
new Claim(ClaimTypes.Role, "member"),
new Claim("user_id", "afa5fafaf4aga4g")
});
statisticsFilter = new StatisticsFilter()
{
From = DateTime.Parse("01/01/2019"),
To = DateTime.Now
};
mockReportService = new Mock <IReportService>();
mockUserService = new Mock <IUserService>();
}
/// <inheritdoc/>
public async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadBestBowlingStrikeRate(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadBestPlayerAverage("BallsBowled", "WicketsWithBowling", 1, "ASC", true, $"AND BallsBowled IS NOT NULL AND WicketsWithBowling IS NOT NULL", filter).ConfigureAwait(false));
}
public void Statistics([SmartBinder] StatisticsFilter filter)
{
PropertyBag["Statistics"] = filter.Find();
PropertyBag["filter"] = filter;
}
/// <inheritdoc/>
public async Task <int> ReadTotalPlayersWithBattingAverage(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadTotalPlayersWithData("RunsScored", "CAST(PlayerWasDismissed AS INT)", false, filter).ConfigureAwait(false));
}
public async Task <IList <MeasurementStatisticsModel> > GetTemperatureMeasurementsAsync(StatisticsFilter filter)
{
var query = SmartHomeAppDbContext.Query <TemperatureMeasurement>();
if (filter.IsInside != null)
{
query = query.Where(x => x.Place.IsInside == filter.IsInside.Value);
}
if (filter.DateFrom != null)
{
query = query.Where(x => x.MeasurementDateTime >= filter.DateFrom);
}
if (filter.DateTo != null)
{
query = query.Where(x => x.MeasurementDateTime <= filter.DateTo);
}
IEnumerable <MeasurementStatisticsModel> modelQuery;
switch (filter.AggregateOver)
{
case AggregateOver.DayOfYear:
{
modelQuery = AggregateOverDayOfYear(query, filter.AggregateOverPlace);
break;
}
case AggregateOver.Month:
{
modelQuery = AggregateOverMonth(query, filter.AggregateOverPlace);
break;
}
case AggregateOver.Year:
{
modelQuery = AggregateOverYear(query, filter.AggregateOverPlace);
break;
}
case null:
{
IQueryable <Tuple <long?, DateTime, int, double> > groupedQuery;
if (!filter.AggregateOverPlace)
{
groupedQuery = query
// make groups that have same date + hour and place and make temperature average over them
.GroupBy(x => new { x.MeasurementDateTime.Date, x.MeasurementDateTime.Hour, x.PlaceId })
.Select(x => Tuple.Create((long?)x.Key.PlaceId, x.Key.Date, x.Key.Hour,
x.Average(y => y.Temperature)));
}
else
{
groupedQuery = query
// make groups that have same date + hour and place and make temperature average over them
.GroupBy(x => new { x.MeasurementDateTime.Date, x.MeasurementDateTime.Hour })
.Select(x =>
Tuple.Create((long?)null, x.Key.Date, x.Key.Hour, x.Average(y => y.Temperature)));
}
modelQuery = groupedQuery.AsEnumerable()
.Select(x => new MeasurementStatisticsModel()
{
MeasurementDateTime = new DateTime(x.Item2.Year, x.Item2.Month, x.Item2.Day, x.Item3, 0, 0),
Value = x.Item4,
PlaceId = x.Item1
});
break;
}
default:
throw new ArgumentOutOfRangeException();
}
var list = modelQuery
.ToList();
return(list);
}
/// <inheritdoc/>
public async Task <int> ReadTotalPlayersWithBowlingAverage(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadTotalPlayersWithData("RunsConceded", "Wickets", false, filter).ConfigureAwait(false));
}
public string Serialize(StatisticsFilter filter)
{
return(Serialize(filter, null));
}
/// <inheritdoc/>
public async Task <int> ReadTotalPlayersWithEconomyRate(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadTotalPlayersWithData("RunsConceded", $"CAST(BallsBowled AS DECIMAL)/{StatisticsConstants.BALLS_PER_OVER}", false, filter).ConfigureAwait(false));
}
/// <inheritdoc/>
public async Task <IEnumerable <StatisticsResult <BestStatistic> > > ReadMostWickets(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadBestPlayerTotal("Wickets", false, true, null, null, "AND BowlingFiguresId IS NOT NULL", filter).ConfigureAwait(false));
}
/// <inheritdoc/>
public async Task <int> ReadTotalPlayersWithBattingStrikeRate(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadTotalPlayersWithData("RunsScored", "BallsFaced", true, filter).ConfigureAwait(false));
}
/// <inheritdoc/>
public async Task <int> ReadTotalPlayersWithRunOuts(StatisticsFilter filter)
{
return(await ReadTotalPlayersWithData("RunOuts", filter, false).ConfigureAwait(false));
}
/// <inheritdoc/>
public async Task <int> ReadTotalPlayersWithBowlingStrikeRate(StatisticsFilter filter)
{
filter = filter ?? new StatisticsFilter();
return(await ReadTotalPlayersWithData("BallsBowled", "WicketsWithBowling", true, filter).ConfigureAwait(false));
}
public StatisticsViewModel(StatisticsFilter filter)
{
Filter = filter;
}
public async Task <IEnumerable <StatisticsResult <PlayerInnings> > > ReadPlayerInnings(StatisticsFilter filter)
{
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
var sql = $@"SELECT MatchName, MatchRoute, MatchStartTime AS StartTime, PlayerIdentityName, PlayerRoute, PlayerInningsId, DismissalType, RunsScored, BowledByPlayerIdentityName AS PlayerIdentityName, BowledByPlayerRoute AS PlayerRoute
FROM {Tables.PlayerInMatchStatistics}
<<WHERE>>
ORDER BY MatchStartTime DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";
var(where, parameters) = _statisticsQueryBuilder.BuildWhereClause(filter);
sql = sql.Replace("<<WHERE>>", $"WHERE 1=1 {where}");
parameters.Add("PageOffset", (filter.Paging.PageNumber - 1) * filter.Paging.PageSize);
parameters.Add("PageSize", filter.Paging.PageSize);
using (var connection = _databaseConnectionFactory.CreateDatabaseConnection())
{
return(await connection.QueryAsync <MatchListing, PlayerIdentity, Player, PlayerInnings, PlayerIdentity, Player, StatisticsResult <PlayerInnings> >(sql,
(match, batterIdentity, batter, playerInnings, bowlerIdentity, bowler) =>
{
batterIdentity.Player = batter;
playerInnings.Batter = batterIdentity;
if (bowlerIdentity != null)
{
bowlerIdentity.Player = bowler;
playerInnings.Bowler = bowlerIdentity;
}
return new StatisticsResult <PlayerInnings>
{
Match = match,
Result = playerInnings
};
},
parameters,
splitOn : "PlayerIdentityName, PlayerRoute, PlayerInningsId, PlayerIdentityName, PlayerRoute").ConfigureAwait(false));
}
}
