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,
}));
}
