/// <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())); } }