public async Task <IActionResult> GetDays(DatesRangeModel datesModel)
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
if (datesModel.To.Value - datesModel.From.Value < TimeSpan.FromDays(1))
{
return(BadRequest());
}
await _connection.OpenAsync();
return(Ok(await _connection.QueryAsync($@"
SELECT
MIN([Time]) AT TIME ZONE 'Israel Standard Time' AS [FirstPerson]
,MAX([Time]) AT TIME ZONE 'Israel Standard Time' AS [LastPerson]
,COUNT(*) AS [TotalPersons]
,AVG([CognitiveHappiness]) AS [AvgHappiness]
,AVG([CognitiveSadness]) AS [AvgSadness]
FROM [Faces]
WHERE [Time] >= @start AND [Time] <= @end
GROUP BY DATEPART(DAYOFYEAR, [Time] AT TIME ZONE 'Israel Standard Time')",
new
{
start = datesModel.From.Value.UtcDateTime,
end = datesModel.To.Value.UtcDateTime
})));
}
public async Task <IActionResult> WeekDayNonNeutralPercent(DatesRangeModel datesModel, int minFacesInDay = 10)
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
await _connection.OpenAsync();
return(Ok(await _connection.QueryAsync(@"
SELECT
DATEPART(WEEKDAY, [Time] AT TIME ZONE 'Israel Standard Time') AS [WeekDay]
,COUNT(*) [Total]
, SUM(CognitiveAnger) AS SumAnger
, SUM(CognitiveContempt) AS SumContempt
, SUM(CognitiveDisgust) AS SumDisgust
, SUM(CognitiveFear) AS SumFear
, SUM(CognitiveHappiness) AS SumHappiness
, SUM(CognitiveSadness) AS SumSadness
, SUM(CognitiveSurprise) AS SumSurprise
, SUM(CognitiveNeutral) AS SumNeutral
FROM [dbo].[Faces]
WHERE [Time] >= @start AND [Time] <= @end
GROUP BY DATEPART(WEEKDAY, [Time] AT TIME ZONE 'Israel Standard Time')
HAVING COUNT(*) > @minFacesInDay
ORDER BY [WeekDay]",
new
{
start = datesModel.From.Value.UtcDateTime,
end = datesModel.To.Value.UtcDateTime,
minFacesInDay = minFacesInDay
})));
}
public async Task <IActionResult> PeopleOnImageCounts(DatesRangeModel datesModel)
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
await _connection.OpenAsync();
return(Ok(await _connection.QueryAsync(@"
SELECT
[PeopleOnImage],
COUNT(*) AS [Count]
FROM (
SELECT
COUNT(*) as [PeopleOnImage]
FROM [dbo].[Faces]
WHERE [Time] >= @start AND [Time] <= @end
GROUP BY [Image]) A
GROUP BY [PeopleOnImage]
ORDER BY [PeopleOnImage]
",
new
{
start = datesModel.From.Value.UtcDateTime,
end = datesModel.To.Value.UtcDateTime
})));
}
public async Task <IActionResult> DominantMoodsCounts(DatesRangeModel datesModel, int minFacesInDay = 10)
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
if (datesModel.From.Value > datesModel.To.Value)
{
return(BadRequest());
}
var datePart = datePartFromRange(datesModel.From.Value, datesModel.To.Value);
await _connection.OpenAsync();
return(Ok(await _connection.QueryAsync($@"
SELECT
{@datePart.selectPart} AS [Group]
,MIN([Time]) AS [StartTime]
,COUNT(*) [Total]
,SUM(CASE WHEN {dominantCondition("CognitiveAnger")} THEN 1 ELSE 0 END) AS Anger
,SUM(CASE WHEN {dominantCondition("CognitiveContempt")} THEN 1 ELSE 0 END) AS Contempt
,SUM(CASE WHEN {dominantCondition("CognitiveDisgust")} THEN 1 ELSE 0 END) AS Disgust
,SUM(CASE WHEN {dominantCondition("CognitiveFear")} THEN 1 ELSE 0 END) AS Fear
,SUM(CASE WHEN {dominantCondition("CognitiveHappiness")} THEN 1 ELSE 0 END) AS Happiness
,SUM(CASE WHEN {dominantCondition("CognitiveNeutral")} THEN 1 ELSE 0 END) AS Neutral
,SUM(CASE WHEN {dominantCondition("CognitiveSadness")} THEN 1 ELSE 0 END) AS Sadness
,SUM(CASE WHEN {dominantCondition("CognitiveSurprise")} THEN 1 ELSE 0 END) AS Surprise
FROM [dbo].[Faces]
WHERE [Time] >= @start AND [Time] <= @end
GROUP BY {@datePart.groupByPart}
HAVING COUNT(*) > @minFacesInDay
ORDER BY [StartTime]",
new
{
start = datesModel.From.Value.UtcDateTime,
end = datesModel.To.Value.UtcDateTime,
minFacesInDay = minFacesInDay
})));
}
public async Task <IActionResult> MoodsAverages(DatesRangeModel datesModel, int minFacesInDay = 10)
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
if (datesModel.From.Value > datesModel.To.Value)
{
return(BadRequest());
}
var datePart = datePartFromRange(datesModel.From.Value, datesModel.To.Value);
await _connection.OpenAsync();
return(Ok(await _connection.QueryAsync($@"
SELECT
{@datePart.selectPart} AS [Group]
,MIN([Time]) AS [StartTime]
,AVG([CognitiveAnger]) AS AvgAnger
,AVG([CognitiveContempt]) AS AvgContempt
,AVG([CognitiveDisgust]) AS AvgDisgust
,AVG([CognitiveFear]) AS AvgFear
,AVG([CognitiveHappiness]) AS AvgHappiness
,AVG([CognitiveNeutral]) AS AvgNeutral
,AVG([CognitiveSadness]) AS AvgSadness
,AVG([CognitiveSurprise]) AS AvgSurprise
FROM [dbo].[Faces]
WHERE [Time] >= @start AND [Time] <= @end
GROUP BY {@datePart.groupByPart}
HAVING COUNT(*) > @minFacesInDay
ORDER BY [StartTime]",
new
{
start = datesModel.From.Value.UtcDateTime,
end = datesModel.To.Value.UtcDateTime,
minFacesInDay = minFacesInDay
})));
}
public async Task <IActionResult> HourlyMoodsCounts(DatesRangeModel datesModel, double score = 0.1, int workDayStartHour = 7, int workDayEndHour = 20)
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
await _connection.OpenAsync();
return(Ok(await _connection.QueryAsync(@"
SELECT
DATEPART(HOUR, [Time] AT TIME ZONE 'Israel Standard Time') AS [Hour]
,COUNT(*) [Total]
,SUM(CASE WHEN CognitiveAnger > @score THEN 1 ELSE 0 END) AS Anger
,SUM(CASE WHEN CognitiveContempt > @score THEN 1 ELSE 0 END) AS Contempt
,SUM(CASE WHEN CognitiveDisgust > @score THEN 1 ELSE 0 END) AS Disgust
,SUM(CASE WHEN CognitiveFear > @score THEN 1 ELSE 0 END) AS Fear
,SUM(CASE WHEN CognitiveHappiness > @score THEN 1 ELSE 0 END) AS Happiness
,SUM(CASE WHEN CognitiveNeutral > @score THEN 1 ELSE 0 END) AS Neutral
,SUM(CASE WHEN CognitiveSadness > @score THEN 1 ELSE 0 END) AS Sadness
,SUM(CASE WHEN CognitiveSadness > @score THEN 1 ELSE 0 END) AS Sadness
,SUM(CASE WHEN CognitiveSurprise > @score THEN 1 ELSE 0 END) AS Surprise
FROM [dbo].[Faces]
WHERE [Time] >= @start AND [Time] <= @end
AND DATEPART(HOUR, [Time] AT TIME ZONE 'Israel Standard Time') > @workDayStartHour
AND DATEPART(HOUR, [Time] AT TIME ZONE 'Israel Standard Time') < @workDayEndHour
GROUP BY DATEPART(HOUR, [Time] AT TIME ZONE 'Israel Standard Time')
ORDER BY [Hour]",
new
{
start = datesModel.From.Value.UtcDateTime,
end = datesModel.To.Value.UtcDateTime,
score = score,
workDayStartHour = workDayStartHour,
workDayEndHour = workDayEndHour
})));
}
