/// <summary>
/// Fetches multiple Tags
/// </summary>
/// <param name="page">The current page number</param>
/// <param name="count">The page size</param>
/// <returns>Returns a result containing an optional list of items</returns>
public async Task <Result <Maybe <PaginatedResult <Tag> > > > FetchCountedTags(int page, int count)
{
try
{
using (var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString))
{
using var obj = await con.QueryMultipleAsync("SELECT COUNT(*) FROM \"Tag\"; SELECT * FROM \"Tag\" LIMIT @Limit OFFSET @Offset", new { Limit = count, Offset = page *count }).ConfigureAwait(false);
var totalCount = obj.Read <int>().Single();
var data = obj.Read <Tag>().ToList();
var paginatedData = new PaginatedResult <Tag> {
Data = data ?? new List <Tag>(),
Count = totalCount
};
return(Result.Ok(Maybe <PaginatedResult <Tag> > .From(paginatedData)));
}
}
catch (Exception ex)
{
logger.LogError(ex, "Query failed");
return(Result.Fail <Maybe <PaginatedResult <Tag> > >(ex.ToString()));
}
}
/// <summary>
/// Fetches a single menu
/// </summary>
/// <returns>Returns a result containing an optional item</returns>
public async Task <Result <Maybe <FullMenu> > > FetchFullMenu(int menuId)
{
try
{
var fullMenuDetails = new List <FullMenu>();
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
var lookup = new Dictionary <int, FullMenu>();
var data = await con.QueryMultipleAsync(
@"SELECT m.*, pt.*, p.*
FROM ""Menu"" as m
LEFT OUTER JOIN ""MenuItem"" as mi on mi.menuId = m.menuId
LEFT OUTER JOIN ""Product"" as p on p.productId = mi.productId
LEFT OUTER JOIN ""ProductType"" as pt on pt.productTypeId = p.productTypeId
WHERE m.menuId = @MenuId", new { MenuId = menuId }).ConfigureAwait(false);
fullMenuDetails = GenerateFullMenuResults(data);
if (data == null)
{
return(Result.Ok(Maybe <FullMenu> .None));
}
return(Result.Ok(Maybe <FullMenu> .From(fullMenuDetails.FirstOrDefault())));
}
catch (Exception ex)
{
return(Result.Fail <Maybe <FullMenu> >(ex.ToString()));
}
}
/// <summary>
/// Fetches multiple media entries
/// </summary>
/// <param name="companyId">The company id to query against</param>
/// <param name="page">The current page number</param>
/// <param name="count">The page size</param>
/// <returns>Returns a result containing an optional list of items</returns>
public async Task <Result <Maybe <PaginatedResult <MediaEntry> > > > FetchCountedCompanyMediaEntries(int companyId, int page, int count)
{
try
{
const string cquery = @"select count(*) from (
select i.imageid as id
from ""Image"" i
where i.companyId = @CompanyId
union all
select d.documentid as id
from ""Document"" d
where d.companyId = @CompanyId
order by id
) total";
const string query = @"select i.imageid as id, i.companyid as companyid, i.imagekey as key, i.imagetitle as title, CAST(1 as int) as mediatype
from ""Image"" i
where i.companyId = @CompanyId
union all
select d.documentid as id, d.companyid as companyid, d.documentkey as key, d.documenttitle as title, CAST(2 as int) as mediatype
from ""Document"" d
where d.companyId = @CompanyId
order by id
limit 20
offset 0";
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
using var obj = await con.QueryMultipleAsync($"{cquery}; {query}", new { Limit = count, Offset = page *count, CompanyId = companyId }).ConfigureAwait(false);
var totalCount = obj.Read <int>().Single();
var data = obj.Read <MediaEntry>().ToList();
var paginatedData = new PaginatedResult <MediaEntry>
{
Data = data ?? new List <MediaEntry>(),
Count = totalCount
};
return(Result.Ok(Maybe <PaginatedResult <MediaEntry> > .From(paginatedData)));
}
catch (Exception ex)
{
return(Result.Fail <Maybe <PaginatedResult <MediaEntry> > >(ex.ToString()));
}
}
/// <summary>
/// Fetches multiple Venues for a given company id
/// </summary>
/// <param name="companyId">The company id</param>
/// <param name="page">The current page number</param>
/// <param name="count">The page size</param>
/// <returns>Returns a result containing an optional list of items</returns>
public async Task <Result <Maybe <PaginatedResult <Venue> > > > FetchCountedCompanyVenues(int companyId, int page, int count)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
using var obj = await con.QueryMultipleAsync(@"SELECT COUNT(*) FROM ""Venue"" WHERE companyId = @CompanyId; SELECT * FROM ""Venue"" WHERE companyId = @CompanyId ORDER BY venueName ASC LIMIT @Limit OFFSET @Offset", new { CompanyId = companyId, Limit = count, Offset = page *count }).ConfigureAwait(false);
var totalCount = obj.Read <int>().Single();
var data = obj.Read <Venue>().ToList();
var paginatedData = new PaginatedResult <Venue> {
Data = data ?? new List <Venue>(),
Count = totalCount
};
return(Result.Ok(Maybe <PaginatedResult <Venue> > .From(paginatedData)));
}
catch (Exception ex)
{
return(Result.Fail <Maybe <PaginatedResult <Venue> > >(ex.ToString()));
}
}
/// <summary>
/// Fetches multiple Menus
/// </summary>
/// <param name="page">The current page number</param>
/// <param name="count">The page size</param>
/// <returns>Returns a result containing an optional list of items</returns>
public async Task <Result <Maybe <PaginatedResult <Menu> > > > FetchCountedVenueMenus(int venueId, int page, int count)
{
try
{
using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString);
using var obj = await con.QueryMultipleAsync("SELECT COUNT(*) FROM \"Menu\" WHERE venueId = @VenueId; SELECT * FROM \"Menu\" WHERE venueId = @VenueId LIMIT @Limit OFFSET @Offset", new { Limit = count, Offset = page *count, VenueId = venueId }).ConfigureAwait(false);
var totalCount = obj.Read <int>().Single();
var data = obj.Read <Menu>().ToList();
var paginatedData = new PaginatedResult <Menu>
{
Data = data ?? new List <Menu>(),
Count = totalCount
};
return(Result.Ok(Maybe <PaginatedResult <Menu> > .From(paginatedData)));
}
catch (Exception ex)
{
return(Result.Fail <Maybe <PaginatedResult <Menu> > >(ex.ToString()));
}
}
