public static async Task <OrderEntity> FetchOrderByOrderId(string connectionString, string orderId)
{
return(await SqlRunner.QueryFirstAsync <OrderEntity>(connectionString, @"SELECT
OrderId,
Description,
OrganisationId,
OrganisationName,
OrganisationOdsCode,
OrganisationAddressId,
OrganisationContactId,
OrderStatusId,
Created,
SupplierId,
SupplierName,
SupplierAddressId,
SupplierContactId,
LastUpdated,
LastUpdatedBy,
CommencementDate,
LastUpdatedByName,
SupplierId,
SupplierName,
ServiceRecipientsViewed,
CatalogueSolutionsViewed
FROM dbo.[Order]
WHERE OrderId = @orderId;", new { orderId }));
}
public static async Task <OrderEntity> FetchOrderByOrderId(string connectionString, int id)
{
const string sql =
@"SELECT Id,
Revision,
CallOffId,
[Description],
OrderingPartyId,
OrderingPartyContactId,
OrderStatusId AS OrderStatus,
Created,
SupplierId,
SupplierContactId,
LastUpdated,
LastUpdatedBy,
CommencementDate,
LastUpdatedByName,
FundingSourceOnlyGMS,
IsDeleted,
Completed
FROM dbo.[Order]
WHERE Id = @id;";
return(await SqlRunner.QueryFirstAsync <OrderEntity>(connectionString, sql, new { id }));
}
public static async Task <DefaultDeliveryDateEntity> Fetch(string connectionString, object parameters)
{
const string sql = @"
SELECT OrderId, CatalogueItemId, DeliveryDate
FROM dbo.DefaultDeliveryDate
WHERE OrderId = @orderId
AND CatalogueItemId = @catalogueItemId;" ;
return(await SqlRunner.QueryFirstAsync <DefaultDeliveryDateEntity>(connectionString, sql, parameters));
}
public static async Task <SupplierEntity> FetchById(string connectionString, string id)
{
const string sql = @"
SELECT Id,
[Name],
AddressId
FROM dbo.Supplier
WHERE Id = @id;";
return(await SqlRunner.QueryFirstAsync <SupplierEntity>(connectionString, sql, new { id }));
}
public static async Task <OrderProgressEntity> FetchOrderByOrderId(string connectionString, int orderId)
{
const string sql = @"
SELECT OrderId,
CatalogueSolutionsViewed,
AdditionalServicesViewed,
AssociatedServicesViewed
FROM dbo.OrderProgress
WHERE OrderId = @orderId;";
return(await SqlRunner.QueryFirstAsync <OrderProgressEntity>(connectionString, sql, new { orderId }));
}
public static async Task <ContactEntity> FetchContactById(string connectionString, int?contactId)
{
if (contactId == null)
{
return(null);
}
return(await SqlRunner.QueryFirstAsync <ContactEntity>(connectionString, @"SELECT
FirstName,
LastName,
Email,
Phone
FROM dbo.Contact
WHERE ContactId = @contactId;", new { contactId }));
}
public static async Task <ContactEntity> FetchContactById(string connectionString, int?id)
{
if (id is null)
{
return(null);
}
const string sql =
@"SELECT Id,
FirstName,
LastName,
Email,
Phone
FROM dbo.Contact
WHERE Id = @id;";
return(await SqlRunner.QueryFirstAsync <ContactEntity>(connectionString, sql, new { id }));
}
public static async Task <AddressEntity> FetchAddressById(string connectionString, int?addressId)
{
if (addressId == null)
{
return(null);
}
return(await SqlRunner.QueryFirstAsync <AddressEntity>(connectionString, @"SELECT
Line1,
Line2,
Line3,
Line4,
Line5,
Town,
County,
Postcode,
Country
FROM dbo.[Address]
WHERE AddressId = @addressId;", new { addressId }));
}
public static async Task <AddressEntity> FetchAddressById(string connectionString, int?id)
{
if (id is null)
{
return(null);
}
const string sql = @"
SELECT Id,
Line1,
Line2,
Line3,
Line4,
Line5,
Town,
County,
Postcode,
Country
FROM dbo.[Address]
WHERE Id = @id;";
return(await SqlRunner.QueryFirstAsync <AddressEntity>(connectionString, sql, new { id }));
}
public static async Task <OrderItemEntity> FetchByKey(
string connectionString,
int orderId,
string catalogueItemId)
{
const string sql = @"
SELECT OrderId,
CatalogueItemId,
ProvisioningTypeId AS ProvisioningType,
CataloguePriceTypeId AS CataloguePriceType,
PricingUnitName,
TimeUnitId AS TimeUnit,
CurrencyCode,
EstimationPeriodId AS EstimationPeriod,
PriceId,
Price,
Created,
LastUpdated
FROM dbo.OrderItem
WHERE OrderId = @orderId
AND CatalogueItemId = @catalogueItemId;";
return(await SqlRunner.QueryFirstAsync <OrderItemEntity>(connectionString, sql, new { orderId, catalogueItemId }));
}
public async Task <T> InsertAsync <T>() => await SqlRunner.QueryFirstAsync <T>(InsertSql, this);
public async Task <T> InsertAsync <T>(string connectionString)
{
return(await SqlRunner.QueryFirstAsync <T>(connectionString, InsertSql, this));
}
public async Task <T> InsertAsync <T>()
{
return(await SqlRunner.QueryFirstAsync <T>(InsertSql, this));
}