public static IQueryable <Product> GetProducts(this OnLineStoreDbContext dbContext, int?productCategoryID = null)
{
var query = dbContext.Products.AsQueryable();
if (productCategoryID.HasValue)
{
query = query.Where(item => item.ProductCategoryID == productCategoryID);
}
return(query);
}
public static IQueryable <ProductInventory> GetProductInventories(this OnLineStoreDbContext dbContext, int?productID = null, string locationID = null)
{
var query = dbContext.ProductInventories.AsQueryable();
if (productID.HasValue)
{
query = query.Where(item => item.ProductID == productID);
}
if (!string.IsNullOrEmpty(locationID))
{
query = query.Where(item => item.LocationID == locationID);
}
return(query);
}
public static OnLineStoreDbContext GetOnLineStoreDbContextInMemory(string dbName)
{
// Create options for DbContext
// Use in memory provider
// Disable transactions because in memory database doesn't support txns
var options = new DbContextOptionsBuilder <OnLineStoreDbContext>()
.UseInMemoryDatabase(databaseName: dbName)
.ConfigureWarnings(w => w.Ignore(InMemoryEventId.TransactionIgnoredWarning))
.Options;
var dbContext = new OnLineStoreDbContext(options);
// Seed data for DbContext instance
dbContext.SeedInMemory();
return(dbContext);
}
public SalesService(ILogger <SalesService> logger, IUserInfo userInfo, OnLineStoreDbContext dbContext)
: base(logger, userInfo, dbContext)
{
}
public Service(ILogger logger, IUserInfo userInfo, OnLineStoreDbContext dbContext)
{
Logger = logger;
UserInfo = userInfo;
DbContext = dbContext;
}
public static IQueryable <OrderInfo> GetOrders(this OnLineStoreDbContext dbContext, short?orderStatusID = null, int?customerID = null, int?employeeID = null, int?shipperID = null, string currencyID = null, Guid?paymentMethodID = null)
{
var query = from order in dbContext.Orders
join currencyJoin in dbContext.Currencies on order.CurrencyID equals currencyJoin.CurrencyID into currencyTemp
from currency in currencyTemp.DefaultIfEmpty()
join customer in dbContext.Customers on order.CustomerID equals customer.CustomerID
join employeeJoin in dbContext.Employees on order.EmployeeID equals employeeJoin.EmployeeID into employeeTemp
from employee in employeeTemp.DefaultIfEmpty()
join orderStatus in dbContext.OrderStatuses on order.OrderStatusID equals orderStatus.OrderStatusID
join paymentMethodJoin in dbContext.PaymentMethods on order.PaymentMethodID equals paymentMethodJoin.PaymentMethodID into paymentMethodTemp
from paymentMethod in paymentMethodTemp.DefaultIfEmpty()
join shipperJoin in dbContext.Shippers on order.ShipperID equals shipperJoin.ShipperID into shipperTemp
from shipper in shipperTemp.DefaultIfEmpty()
select new OrderInfo
{
OrderID = order.OrderHeaderID,
OrderStatusID = order.OrderStatusID,
CustomerID = order.CustomerID,
EmployeeID = order.EmployeeID,
ShipperID = order.ShipperID,
OrderDate = order.OrderDate,
Total = order.Total,
CurrencyID = order.CurrencyID,
PaymentMethodID = order.PaymentMethodID,
Comments = order.Comments,
DetailsCount = order.DetailsCount,
ReferenceOrderID = order.ReferenceOrderID,
CreationUser = order.CreationUser,
CreationDateTime = order.CreationDateTime,
LastUpdateUser = order.LastUpdateUser,
LastUpdateDateTime = order.LastUpdateDateTime,
Timestamp = order.Timestamp,
CurrencyCurrencyName = currency == null ? string.Empty : currency.CurrencyName,
CurrencyCurrencySymbol = currency == null ? string.Empty : currency.CurrencySymbol,
CustomerCompanyName = customer == null ? string.Empty : customer.CompanyName,
CustomerContactName = customer == null ? string.Empty : customer.ContactName,
EmployeeFirstName = employee.FirstName,
EmployeeMiddleName = employee == null ? string.Empty : employee.MiddleName,
EmployeeLastName = employee.LastName,
EmployeeBirthDate = employee.BirthDate,
OrderStatusDescription = orderStatus.Description,
PaymentMethodPaymentMethodName = paymentMethod == null ? string.Empty : paymentMethod.PaymentMethodName,
PaymentMethodPaymentMethodDescription = paymentMethod == null ? string.Empty : paymentMethod.PaymentMethodDescription,
ShipperCompanyName = shipper == null ? string.Empty : shipper.CompanyName,
ShipperContactName = shipper == null ? string.Empty : shipper.ContactName,
};
if (orderStatusID.HasValue)
{
query = query.Where(item => item.OrderStatusID == orderStatusID);
}
if (customerID.HasValue)
{
query = query.Where(item => item.CustomerID == customerID);
}
if (employeeID.HasValue)
{
query = query.Where(item => item.EmployeeID == employeeID);
}
if (shipperID.HasValue)
{
query = query.Where(item => item.ShipperID == shipperID);
}
if (!string.IsNullOrEmpty(currencyID))
{
query = query.Where(item => item.CurrencyID == currencyID);
}
if (paymentMethodID.HasValue)
{
query = query.Where(item => item.PaymentMethodID == paymentMethodID);
}
return(query);
}
public static async Task <Shipper> GetShipperAsync(this OnLineStoreDbContext dbContext, Shipper entity) => await dbContext.Shippers.FirstOrDefaultAsync(item => item.ShipperID == entity.ShipperID);
public static async Task <Customer> GetCustomerAsync(this OnLineStoreDbContext dbContext, Customer entity) => await dbContext.Customers.FirstOrDefaultAsync(item => item.CustomerID == entity.CustomerID);
public static async Task <OrderStatus> GetOrderStatusAsync(this OnLineStoreDbContext dbContext, OrderStatus entity) => await dbContext.OrderStatuses.FirstOrDefaultAsync(item => item.OrderStatusID == entity.OrderStatusID);
public static async Task <EntityLayer.Warehouse.Location> GetWarehouseAsync(this OnLineStoreDbContext dbContext, EntityLayer.Warehouse.Location entity) => await dbContext.Warehouses.FirstOrDefaultAsync(item => item.LocationID == entity.LocationID);
public static async Task <ProductInventory> GetProductInventoryAsync(this OnLineStoreDbContext dbContext, ProductInventory entity) => await dbContext.ProductInventories.FirstOrDefaultAsync(item => item.ProductInventoryID == entity.ProductInventoryID);
public static Product GetProductByName(this OnLineStoreDbContext dbContext, string productName) => dbContext.Products.FirstOrDefault(item => item.ProductName == productName);
public static async Task <Employee> GetEmployeeAsync(this OnLineStoreDbContext dbContext, Employee entity) => await dbContext.Employees.FirstOrDefaultAsync(item => item.EmployeeID == entity.EmployeeID);
public WarehouseService(ILogger <WarehouseService> logger, IUserInfo userInfo, OnLineStoreDbContext dbContext)
: base(logger, userInfo, dbContext)
{
}
public static async Task <OrderHeader> GetOrderAsync(this OnLineStoreDbContext dbContext, OrderHeader entity) => await dbContext.Orders.Include(p => p.OrderDetails).FirstOrDefaultAsync(item => item.OrderHeaderID == entity.OrderHeaderID);
public HumanResourcesService(ILogger <HumanResourcesService> logger, IUserInfo userInfo, OnLineStoreDbContext dbContext)
: base(logger, userInfo, dbContext)
{
}
public static void SeedInMemory(this OnLineStoreDbContext dbContext)
{
var creationUser = "******";
var creationDateTime = DateTime.Now;
var country = new Country
{
CountryID = 1,
CountryName = "USA",
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.Countries.Add(country);
var currency = new Currency
{
CurrencyID = "USD",
CurrencyName = "US Dollar",
CurrencySymbol = "$",
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.Currencies.Add(currency);
var countryCurrency = new CountryCurrency
{
CountryID = country.CountryID,
CurrencyID = currency.CurrencyID,
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.CountryCurrencies.Add(countryCurrency);
dbContext.SaveChanges();
var employee = new Employee
{
EmployeeID = 1,
FirstName = "John",
LastName = "Doe",
BirthDate = DateTime.Now.AddYears(-25),
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.Employees.Add(employee);
dbContext.SaveChanges();
var productCategory = new ProductCategory
{
ProductCategoryID = 1,
ProductCategoryName = "PS4 Games",
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.ProductCategories.Add(productCategory);
var product = new Product
{
ProductID = 1,
ProductName = "The King of Fighters XIV",
ProductCategoryID = 1,
UnitPrice = 29.99m,
Description = "KOF XIV",
Discontinued = false,
Stocks = 15000,
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.Products.Add(product);
var location = new Location
{
LocationID = "W0001",
LocationName = "Warehouse 001",
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.Warehouses.Add(location);
var productInventory = new ProductInventory
{
ProductID = product.ProductID,
LocationID = location.LocationID,
OrderDetailID = 1,
Quantity = 1500,
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.ProductInventories.Add(productInventory);
dbContext.SaveChanges();
var orderStatus = new OrderStatus
{
OrderStatusID = 100,
Description = "Created",
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.OrderStatuses.Add(orderStatus);
var paymentMethod = new PaymentMethod
{
PaymentMethodID = Guid.NewGuid(),
PaymentMethodDescription = "Credit Card",
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.PaymentMethods.Add(paymentMethod);
var customer = new Customer
{
CustomerID = 1,
CompanyName = "Best Buy",
ContactName = "Colleen Dunn",
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.Customers.Add(customer);
var shipper = new Shipper
{
ShipperID = 1,
CompanyName = "DHL",
ContactName = "Ricardo A. Bartra",
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.Shippers.Add(shipper);
var order = new OrderHeader
{
OrderHeaderID = 1,
OrderStatusID = orderStatus.OrderStatusID,
CustomerID = customer.CustomerID,
EmployeeID = employee.EmployeeID,
OrderDate = DateTime.Now,
Total = 29.99m,
CurrencyID = "USD",
PaymentMethodID = paymentMethod.PaymentMethodID,
Comments = "Order from unit tests",
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.Orders.Add(order);
var orderDetail = new OrderDetail
{
OrderDetailID = 1,
OrderHeaderID = order.OrderHeaderID,
ProductID = product.ProductID,
ProductName = product.ProductName,
UnitPrice = 29.99m,
Quantity = 1,
Total = 29.99m,
CreationUser = creationUser,
CreationDateTime = creationDateTime
};
dbContext.OrderDetails.Add(orderDetail);
dbContext.SaveChanges();
}