public async Task <IEnumerable <ArchetypeCard> > Update(long archetypeId, IEnumerable <string> cards)
{
var archetypeCards = await _dbContext.ArchetypeCard.Where(ac => ac.ArchetypeId == archetypeId).ToArrayAsync();
if (archetypeCards.Any())
{
_dbContext.ArchetypeCard.RemoveRange(archetypeCards);
await _dbContext.SaveChangesAsync();
}
var tvp = new TableValuedParameterBuilder
(
"tvp_ArchetypeCardsByCardName",
new SqlMetaData("ArchetypeId", SqlDbType.BigInt), new SqlMetaData("CardName", SqlDbType.NVarChar, 255)
);
foreach (var card in cards)
{
tvp.AddRow(archetypeId, card);
}
var cardsParameter = tvp.CreateParameter("@TvpArchetypeCards");
return(await _dbContext
.ArchetypeCard
.FromSql("EXECUTE usp_AddCardsToArchetype @TvpArchetypeCards", cardsParameter)
.ToListAsync());
}
public IEnumerable <ChartData> GetPieChartData(int[] categories)
{
var tvp = new TableValuedParameterBuilder("dbo.ArrayInt", new SqlMetaData("CategoryId", SqlDbType.Int))
.AddRow(categories)
.CreateParameter("p0");
var pieChartData = _ApplicationDbContext.ChartData.FromSql($"EXEC GetTotalPerCategories @p0", tvp).ToList();
return(pieChartData);
}
public IActionResult GetUsersTasksCounts()
{
List <int> userIds = new List <int>();
DateTimeOffset now = DateTimeOffset.Now;
DateTimeOffset?fromDate = null;
DateTimeOffset?toDate = null;
if (Request.Query.ContainsKey("users"))
{
if (Request.Query.TryGetValue("users", out var usersValue))
{
var usersValueStr = usersValue.ToString();
if (!string.IsNullOrWhiteSpace(usersValueStr))
{
userIds = usersValueStr.Split(',').Select(id => Convert.ToInt32(id)).ToList();
}
}
}
if (Request.Query.ContainsKey("fromDate"))
{
if (Request.Query.TryGetValue("fromDate", out var fromDateValue))
{
var fromDateValueStr = fromDateValue.First();
if (!string.IsNullOrWhiteSpace(fromDateValueStr))
{
if (DateTimeOffset.TryParse(fromDateValueStr, out DateTimeOffset outFromDate))
{
fromDate = outFromDate;
}
}
}
}
if (Request.Query.ContainsKey("toDate"))
{
if (Request.Query.TryGetValue("toDate", out var toDateValue))
{
var toDateValueStr = toDateValue.First();
if (!string.IsNullOrWhiteSpace(toDateValueStr))
{
if (DateTimeOffset.TryParse(toDateValueStr, out DateTimeOffset outToDate))
{
toDate = outToDate;
}
}
}
}
var usersTvpBuilder = new TableValuedParameterBuilder("UserTableType", new SqlMetaData("Id", SqlDbType.Int));
foreach (var userId in userIds)
{
usersTvpBuilder.AddRow(userId);
}
var usersParam = usersTvpBuilder.CreateParameter("Users");
var fromDateParam = new SqlParameter()
{
ParameterName = "FromDate",
SqlDbType = SqlDbType.DateTimeOffset,
Direction = ParameterDirection.Input,
IsNullable = true
};
if (fromDate != null)
{
fromDateParam.Value = fromDate.Value;
}
else
{
fromDateParam.Value = DBNull.Value;
}
var toDateParam = new SqlParameter()
{
ParameterName = "ToDate",
SqlDbType = SqlDbType.DateTimeOffset,
Direction = ParameterDirection.Input,
IsNullable = true
};
if (toDate != null)
{
toDateParam.Value = toDate.Value;
}
else
{
toDateParam.Value = DBNull.Value;
}
var nowParam = new SqlParameter()
{
ParameterName = "Now",
SqlDbType = SqlDbType.DateTimeOffset,
Direction = ParameterDirection.Input,
IsNullable = false,
Value = now
};
var countRows = _context.UsersTasksCountsEntities
.FromSql("usp_GetUsersTasksCounts @Users, @FromDate, @ToDate, @Now", usersParam, fromDateParam, toDateParam, nowParam)
.ToList();
var countDtos = countRows.Select(r => new UsersTasksCountsDto
{
Id = r.Id,
ActiveTasks = r.ActiveRequests + r.ActiveProtectionDocs + r.ActiveContracts,
CompletedTasks = r.CompletedRequests + r.CompletedProtectionDocs + r.CompletedContracts,
ExpiredTasks = r.ExpiredRequests + r.ExpiredProtectionDocs + r.ExpiredContracts,
Documents = r.Documents,
CompletedDocuments = r.CompletedDocuments,
ExpiredDocuments = r.ExpiredDocuments
});
return(Ok(countDtos));
}
