public static async Task <Organisation> RetrieveById(string connectionString, Guid id) { var query = @"SELECT * FROM dbo.Organisations WHERE OrganisationId=@id"; var results = await SqlExecutor.ExecuteAsync <Organisation>(connectionString, query, new { id }); return(results.Single()); }
public async Task <User> RetrieveRandomBuyerUser(string connectionString) { var query = @"SELECT Convert(UniqueIdentifier, Id) as Id ,[UserName] ,[NormalizedUserName] ,[Email] ,[NormalizedEmail] ,[EmailConfirmed] ,[PasswordHash] ,[SecurityStamp] ,Convert(UniqueIdentifier, ConcurrencyStamp) as ConcurrencyStamp ,[PhoneNumber] ,[PhoneNumberConfirmed] ,[TwoFactorEnabled] ,[LockoutEnd] ,[LockoutEnabled] ,[AccessFailedCount] ,Convert(UniqueIdentifier, PrimaryOrganisationId) as PrimaryOrganisationId ,[OrganisationFunction] ,[Disabled] ,[CatalogueAgreementSigned] ,[FirstName] ,[LastName] FROM [dbo].[AspNetUsers] WHERE OrganisationFunction='Buyer'"; var listOfItems = await SqlExecutor.ExecuteAsync <User>(connectionString, query, this); return(listOfItems.ElementAt(new Faker().Random.Number(listOfItems.Count() - 1))); }
public async Task <Organisation> RetrieveRandomOrganisation(string connectionString) { var query = @"SELECT * FROM dbo.Organisations WHERE OrganisationId NOT IN (SELECT DISTINCT OrganisationId FROM RelatedOrganisations)"; var listOfItems = await SqlExecutor.ExecuteAsync <Organisation>(connectionString, query, this); return(listOfItems.ElementAt(new Faker().Random.Number(listOfItems.Count() - 1))); }
public async Task Update(string connectionString) { var query = @"UPDATE AspNetUsers SET UserName=@userName, NormalizedUserName=@normalizedUserName, Email=@email, NormalizedEmail=@normalizedEmail, EmailConfirmed=@emailConfirmed, PasswordHash=@passwordHash, SecurityStamp=@securityStamp, ConcurrencyStamp=@concurrencyStamp, PhoneNumber=@phoneNumber, PhoneNumberConfirmed=@phoneNumberConfirmed, TwoFactorEnabled=@twoFactorEnabled, LockoutEnd=@lockoutEnd, LockoutEnabled=@lockoutEnabled, AccessFailedCount=@accessFailedCount, PrimaryOrganisationId=@primaryOrganisationId, OrganisationFunction=@organisationFunction, Disabled=@disabled, CatalogueAgreementSigned=@catalogueAgreementSigned, FirstName=@firstName, LastName=@lastName WHERE Id=@id"; await SqlExecutor.ExecuteAsync <User>(connectionString, query, this); }
public static async Task <Organisation> RetrieveByODSCode(string connectionString, string odsCode) { var query = @"SELECT * FROM dbo.Organisations WHERE OdsCode=@ODSCode"; var result = await SqlExecutor.ExecuteAsync <Organisation>(connectionString, query, new { odsCode }); return(result.Single()); }
public static async Task <Organisation> GetByODSCode(string odsCode, string connectionString) { var query = "SELECT * FROM Organisations WHERE OdsCode = @odsCode;"; var result = await SqlExecutor.ExecuteAsync <Organisation>(connectionString, query, new { odsCode }); return(result.Single()); }
public static async Task <Organisation> GetOrganisationById(string connectionString, Guid organisationId) { var query = "SELECT * FROM Organisations WHERE OrganisationId = @organisationId;"; var result = await SqlExecutor.ExecuteAsync <Organisation>(connectionString, query, new { organisationId }); return(result.Single()); }
public async Task <User> Retrieve(string connectionString) { var query = @"SELECT Convert(UniqueIdentifier, Id) as Id ,[UserName] ,[NormalizedUserName] ,[Email] ,[NormalizedEmail] ,[EmailConfirmed] ,[PasswordHash] ,[SecurityStamp] ,Convert(UniqueIdentifier, ConcurrencyStamp) as ConcurrencyStamp ,[PhoneNumber] ,[PhoneNumberConfirmed] ,[TwoFactorEnabled] ,[LockoutEnd] ,[LockoutEnabled] ,[AccessFailedCount] ,Convert(UniqueIdentifier, PrimaryOrganisationId) as PrimaryOrganisationId ,[OrganisationFunction] ,[Disabled] ,[CatalogueAgreementSigned] ,[FirstName] ,[LastName] FROM [dbo].[AspNetUsers] WHERE Email=@email"; var users = await SqlExecutor.ExecuteAsync <User>(connectionString, query, this); return(users.Single()); }
public async Task Delete(string connectionString) { var relatedQuery = @"DELETE FROM dbo.RelatedOrganisations WHERE OrganisationId=@organisationId OR RelatedOrganisationId=@organisationId;"; await SqlExecutor.ExecuteAsync <Organisation>(connectionString, relatedQuery, this); var query = @"DELETE FROM dbo.Organisations WHERE OrganisationId=@organisationId OR OdsCode=@odsCode"; await SqlExecutor.ExecuteAsync <Organisation>(connectionString, query, this); }
public async Task ThenTheNameOfTheSelectedAssociatedServiceIsDisplayedOnTheAssociatedServiceEditForm() { var itemId = (string)Context[ContextKeys.ChosenItemId]; var query = "Select Name FROM [dbo].[CatalogueItem] where CatalogueItemId=@itemId"; var expectedSolutionName = (await SqlExecutor.ExecuteAsync <string>(Test.BapiConnectionString, query, new { itemId })).Single(); Test.Pages.OrderForm.TextDisplayedInPageTitle(expectedSolutionName).Should().BeTrue(); }
public static async Task <OrderingParty> GetByOdsCode(this OrderingParty orderingParty, string connectionString) { var query = "SELECT * FROM OrderingParty WHERE OdsCode=@OdsCode"; var results = await SqlExecutor.ExecuteAsync <OrderingParty>(connectionString, query, orderingParty); return(results.SingleOrDefault()); }
public static async Task DeleteContactsForOrdersNoLongerInDb(string connectionString) { var query = "DELETE FROM dbo.Contact " + "WHERE ContactId NOT IN (SELECT OrganisationContactId FROM dbo.[Order])" + "OR ContactId NOT IN (SELECT SupplierContactId FROM dbo.[Order];"; await SqlExecutor.ExecuteAsync <string>(connectionString, query, null); }
public static async Task <SupplierDetails> GetSupplierWithId(string supplierId, string connectionString) { var query = "SELECT [Id] as 'SupplierId', [Name], [Address] FROM Supplier WHERE Id = @supplierId"; var result = await SqlExecutor.ExecuteAsync <SupplierDetails>(connectionString, query, new { supplierId }); return(result.Single()); }
public async Task <int> ExecuteAsync(RequestContext context) { int result = await SqlExecutor.ExecuteAsync <int>(context, DataSourceType.Write, (sqlstr, session) => { return(session.Connection.ExecuteAsync(sqlstr, context.DapperDynamicParams, session.DbTransaction)); }); return(result); }
public async Task ThenTheSelectedServiceRecipientWithTheirODSCodeIsDisplayedOnTheCatalogueSolutionEditForm() { var chosenOdsCode = (string)Context[ContextKeys.ChosenOdsCode]; var query = "Select Name FROM [dbo].[Organisations] where OdsCode=@ChosenOdsCode"; var expectedOrganisationName = (await SqlExecutor.ExecuteAsync <string>(Test.IsapiConnectionString, query, new { chosenOdsCode })).Single(); var expectedFormattedValue = string.Format("{0} ({1})", expectedOrganisationName, chosenOdsCode); Test.Pages.OrderForm.TextDisplayedInPageTitle(expectedFormattedValue).Should().BeTrue(); }
public static async Task <ProvisioningType> GetProvisioningTypeForItemAsync(string itemId, string connectionString) { var query = @"SELECT ProvisioningTypeId FROM dbo.CataloguePrice WHERE CataloguePriceId = @itemId;"; var result = (await SqlExecutor.ExecuteAsync <ProvisioningType>(connectionString, query, new { itemId })).Single(); return(result); }
public static async Task <string> GetSolutionWithMultipleAdditionalServices(string connectionString) { var query = @"SELECT SolutionId FROM dbo.AdditionalService GROUP BY SolutionId ORDER BY COUNT(*) DESC"; return((await SqlExecutor.ExecuteAsync <string>(connectionString, query, null)).First()); }
public async Task ThenAllTheAvailablePricesForThatAssociatedServiceArePresented() { Test.Pages.OrderForm.EditNamedSectionPageDisplayed("List price").Should().BeTrue(); var itemId = (string)Context[ContextKeys.ChosenItemId]; var query = "Select count(*) FROM [dbo].[CataloguePrice] where CatalogueItemId=@itemId"; var expectedNumberOfPrices = (await SqlExecutor.ExecuteAsync <int>(Test.BapiConnectionString, query, new { itemId })).Single(); Test.Pages.OrderForm.NumberOfRadioButtonsDisplayed().Should().Be(expectedNumberOfPrices); }
public async Task <T> QuerySingleAsync <T>(RequestContext context, DataSourceType dataSourceType = DataSourceType.Read) { T result = await SqlExecutor.ExecuteAsync <T>(context, dataSourceType, (sqlStr, session) => { return(session.Connection.QuerySingleOrDefaultAsync <T>(sqlStr, context.DapperDynamicParams, session.DbTransaction)); }); return(result); }
public static async Task <string> GetCatalogueItemIdForNameAsync(string itemName, string bapiConnectionString) { var query = @"SELECT CatalogueItemId FROM dbo.CatalogueItem WHERE [Name] = @itemName;"; var result = (await SqlExecutor.ExecuteAsync <string>(bapiConnectionString, query, new { itemName })).Single(); return(result); }
public static async Task <bool> GetSupplierWithContactDetails(string connectionString, string supplierName) { var query = @"SELECT Id FROM SupplierContact WHERE SupplierId = (SELECT Id FROM Supplier AS s WHERE s.[Name] = @supplierName);"; var result = (await SqlExecutor.ExecuteAsync <Guid?>(connectionString, query, new { supplierName })).SingleOrDefault(); return(result is not null); }
public async Task SetRelatedOrgs(string connectionString) { var query = @"INSERT INTO dbo.RelatedOrganisations ( OrganisationId, RelatedOrganisationId ) VALUES ( @OrganisationId, @RelatedOrganisationId)"; await SqlExecutor.ExecuteAsync <RelatedOrganisations>(connectionString, query, this); }
public static async Task <IEnumerable <CatalogueItemModel> > GetAllPublishedItemsOfTypeAsync(string connectionString, string supplierId, CatalogueItemType itemType) { var query = $@"SELECT *, CatalogueItemTypeId AS 'CatalogueItemType' FROM dbo.CatalogueItem WHERE SupplierId = @supplierId AND PublishedStatusId = 3 AND CatalogueItemTypeId = @itemType AND CatalogueItemId NOT LIKE 'Auto%';"; return(await SqlExecutor.ExecuteAsync <CatalogueItemModel>(connectionString, query, new { supplierId, itemType })); }
public static async Task <string> GetSupplierWithMultipleAssociatedServices(string connectionString) { var query = @"SELECT TOP (1000) s.Id FROM dbo.AssociatedService AS a INNER JOIN CatalogueItem ci ON ci.CatalogueItemId = a.AssociatedServiceId INNER JOIN Supplier AS s ON s.Id = ci.SupplierId WHERE ci.PublishedStatusId = 3 GROUP BY s.Id ORDER BY COUNT(*) DESC;"; return((await SqlExecutor.ExecuteAsync <string>(connectionString, query, null)).First()); }
private static async Task <PricingUnit> GetPricingUnitAsync(ProvisioningType provisioningType, string bapiConnectionString) { var query = @"SELECT pu.[Name], pu.Description FROM PricingUnit AS pu INNER JOIN CataloguePrice AS cp ON cp.PricingUnitId = pu.PricingUnitId WHERE cp.ProvisioningTypeId = @provisioningType AND CataloguePriceTypeId = '1';"; var result = (await SqlExecutor.ExecuteAsync <PricingUnit>(bapiConnectionString, query, new { provisioningType })).FirstOrDefault(); return(result); }
public static async Task <IEnumerable <SupplierDetails> > SuppliersWithout(string connectionString, CatalogueItemType catalogueItemType) { var query = @"SELECT DISTINCT ci.SupplierId, su.[Name] FROM CatalogueItem AS ci INNER JOIN Supplier AS su On ci.SupplierId=su.Id WHERE SupplierId NOT IN( SELECT DISTINCT SupplierId FROM CatalogueItem WHERE CatalogueItemTypeId = @catalogueItemType )"; return(await SqlExecutor.ExecuteAsync <SupplierDetails>(connectionString, query, new { catalogueItemType = (int)catalogueItemType })); }
public async Task UpdateAsync(string connectionString) { var query = @"UPDATE Supplier SET Name=@name, LegalName=@legalName, Summary=@summary, SupplierUrl=@supplierUrl, LastUpdated=@lastUpdated, LastUpdatedBy=@lastUpdatedBy WHERE Id=@Id"; await SqlExecutor.ExecuteAsync <Supplier>(connectionString, query, this); }
public async Task Create(string connectionString) { var query = @"INSERT INTO AspNetUsers ( Id, UserName, NormalizedUserName, Email, NormalizedEmail, EmailConfirmed, PasswordHash, SecurityStamp, ConcurrencyStamp, PhoneNumber, PhoneNumberConfirmed, TwoFactorEnabled, LockoutEnd, LockoutEnabled, AccessFailedCount, PrimaryOrganisationId, OrganisationFunction, Disabled, CatalogueAgreementSigned, FirstName, LastName ) VALUES ( @id, @userName, @normalizedUserName, @email, @normalizedEmail, @emailConfirmed, @passwordHash, @securityStamp, @concurrencyStamp, @phoneNumber, @phoneNumberConfirmed, @twoFactorEnabled, @lockoutEnd, @lockoutEnabled, @accessFailedCount, @primaryOrganisationId, @organisationFunction, @disabled, @catalogueAgreementSigned, @firstName, @lastName )"; await SqlExecutor.ExecuteAsync <User>(connectionString, query, this); }
public static async Task <IEnumerable <CatalogueItemModel> > GetPublishedCatalogueItemsNoTieredAsync(string connectionString, string supplierId, CatalogueItemType itemType) { var query = $@"SELECT *, CatalogueItemTypeId AS 'CatalogueItemType' FROM dbo.CatalogueItem AS ci INNER JOIN CataloguePrice AS cp ON cp.CatalogueItemId = ci.CatalogueItemId WHERE ci.SupplierId = @supplierId AND ci.PublishedStatusId = 3 AND ci.CatalogueItemTypeId = @itemType AND ci.CatalogueItemId NOT LIKE 'Auto%' AND cp.CataloguePriceTypeId = 1;"; return(await SqlExecutor.ExecuteAsync <CatalogueItemModel>(connectionString, query, new { supplierId, itemType })); }
public static async Task <IEnumerable <SupplierDetails> > SupplierLookup(string connectionString, CatalogueItemType catalogueItemType, ProvisioningType provisioningType) { var query = @"SELECT ci.SupplierId, su.[Name], su.Address FROM dbo.CatalogueItem AS ci INNER JOIN CataloguePrice AS pr ON ci.CatalogueItemId=pr.CatalogueItemId INNER JOIN Supplier AS su On ci.SupplierId=su.Id WHERE ci.CatalogueItemTypeId=@catalogueItemType AND pr.ProvisioningTypeId=@provisioningType"; return(await SqlExecutor.ExecuteAsync <SupplierDetails>(connectionString, query, new { catalogueItemType = (int)catalogueItemType, provisioningType = (int)provisioningType, })); }