public Task <List <Receivable> > GetReceivablesByRoute([FromRoute] string routeCode)
{
List <QueryParameter> parameters = new List <QueryParameter>();
parameters.Add(new QueryParameter {
key = "{schema}", value = AS400Schema.GetSchema(Constants.AS400_PREFIX)
});
parameters.Add(new QueryParameter {
key = "{routeCode}", value = routeCode
});
return(_BPCSContext
.Receivables
.FromSql(QueryBuilder.Build("GetReceivablesByRoute.txt", parameters))
.ToListAsync());
}
public Task <List <InitialInventoryLoadWithLot> > GetInitialLoadWithLotByRoute([FromRoute] string routeCode)
{
// Build query parameters
List <QueryParameter> parameters = new List <QueryParameter>();
parameters.Add(new QueryParameter {
key = "{schema}", value = AS400Schema.GetSchema(Constants.AS400_PREFIX)
});
parameters.Add(new QueryParameter {
key = "{routeCode}", value = routeCode
});
return(_BPCSContext
.InitialInventoryLoadWithLots
.FromSql(QueryBuilder.Build("GetInitialInventoryLoadWithLotByRoute.txt", parameters))
.ToListAsync());
}
public Task <List <PurchaseHistory> > GetPurchaseHistoryByRoute([FromRoute] string routeCode)
{
List <ApplicationSetting> applicationSettings = _distributionContext.ApplicationSettings.ToListAsync().Result;
ApplicationSetting NUMBER_OF_MONTHS_BACK = applicationSettings.Find(x => x.Name == NUMBER_OF_MONTHS_BACK_NAME);
ApplicationSetting NUMBER_OF_PURCHASES = applicationSettings.Find(x => x.Name == NUMBER_OF_PURCHASES_NAME);
string numberOfMonthsBack = DEFAULT_NUMBER_OF_MONTHS_BACK.ToString();
if (NUMBER_OF_MONTHS_BACK != null)
{
numberOfMonthsBack = NUMBER_OF_MONTHS_BACK.Value;
}
string numberOfPurchases = DEFAULT_NUMBER_OF_PURCHASES.ToString();
if (NUMBER_OF_PURCHASES != null)
{
numberOfPurchases = NUMBER_OF_PURCHASES.Value;
}
List <QueryParameter> parameters = new List <QueryParameter>();
parameters.Add(new QueryParameter {
key = "{schema}", value = AS400Schema.GetSchema(Constants.DATA_WAREHOUSE_PREFIX)
});
parameters.Add(new QueryParameter {
key = "{numberOfMonthsBack}", value = numberOfMonthsBack
});
parameters.Add(new QueryParameter {
key = "{numberOfPurchases}", value = numberOfPurchases
});
parameters.Add(new QueryParameter {
key = "{routeCode}", value = routeCode
});
return(_BPCSContext
.PurchaseHistories
.FromSql(QueryBuilder.Build("GetPurchaseHistoryByRoute.txt", parameters))
.ToListAsync());
}
public List <InitialInventoryLoad> GetInitialLoadByRoute([FromRoute] string routeCode)
{
// Build query parameters
List <QueryParameter> parameters = new List <QueryParameter>();
parameters.Add(new QueryParameter {
key = "{schema}", value = AS400Schema.GetSchema(Constants.AS400_PREFIX)
});
parameters.Add(new QueryParameter {
key = "{routeCode}", value = routeCode
});
//// Create new initial inventory helper instance
//Helpers.InitialInventory initialInventory = new Helpers.InitialInventory(_distributionContext);
//// Verify if initial inventory has a record or not for today
//if (!initialInventory.IsThereInventoryForToday(routeCode))
//{
// // Get initial inventory with lot
// List<InitialInventoryLoadWithLot> initialInventoryLoadWithLots = _BPCSContext
// .InitialInventoryLoadWithLots
// .FromSql(QueryBuilder.Build("GetInitialInventoryLoadByRoute.txt", parameters))
// .ToListAsync()
// .Result;
// // Verify whether initial inventories with lots is not empty
// if (initialInventoryLoadWithLots.Count > 0)
// {
// // Create initial inventory for today
// initialInventory.LoadInventoryForToday(routeCode, initialInventoryLoadWithLots);
// }
//}
// Return initial inventories withouth lots
return(_BPCSContext
.InitialInventoryLoads
.FromSql(QueryBuilder.Build("GetInitialInventoryLoadByRoute.txt", parameters))
.ToListAsync()
.Result);
}
public IEnumerable <Item> GetItems([FromQuery] string search, [FromQuery] Boolean?isForExport)
{
// List all allowed items and get item code
List <AllowedItem> allowedItems = _distributionContext.AllowedItems.ToList();
// Verify whether the isForExport parameter is present
if (isForExport != null)
{
// Filter allowed items by its flag
allowedItems = allowedItems.Where(x => x.IsForExport == isForExport).ToList();
}
// Get allow item codes
List <string> allowedItemCodes = allowedItems.Select(x => x.ItemCode).ToList();
// Build query parameters
List <QueryParameter> parameters = new List <QueryParameter>();
parameters.Add(new QueryParameter {
key = "{schema}", value = AS400Schema.GetSchema(Constants.AS400_PREFIX)
});
// List all items
List <Item> items = _BPCSContext
.Items
.FromSql(QueryBuilder.Build("GetItems.txt", parameters))
.ToList();
// Verify whether any item exists
if (items.Any())
{
// Verify if any allowed item was found
if (allowedItems.Any())
{
items = items
.Where(x => allowedItemCodes.Contains(x.ItemCode))
.ToList();
}
// Verify whether the search is not null or empty
if (!string.IsNullOrEmpty(search))
{
// Delete any inconsistency in search parameter
string updatedSearch = search.ToLower();
// Apply search filter
items = items
.Where(
x =>
x.ItemCode.ToLower().Contains(updatedSearch) ||
x.ItemName.ToLower().Contains(updatedSearch)
)
.ToList();
}
// Return result
return(items);
}
else
{
return(new List <Item>());
}
}
public async Task <IActionResult> GetDispatchByOrderNumber([FromQuery] string routeCode, [FromQuery] string startDate, [FromQuery] string endDate)
{
// FIXME: Validate emptiness query string values
// Format string dates
DateTime formattedStartDate = DateTimeOffset.ParseExact(startDate, "yyyy-MM-dd", CultureInfo.InvariantCulture).Date;
DateTime formattedEndDate = DateTimeOffset.ParseExact(endDate, "yyyy-MM-dd", CultureInfo.InvariantCulture).Date;
// List orders that apply to the filters
List <Models.Order> orders = await _context
.Orders
.Include(x => x.Items)
.Where(x => x.DeliveryRoute == routeCode)
.Where(x => x.DeliveryDate.Date >= formattedStartDate && x.DeliveryDate.Date <= formattedEndDate)
.Where(x => x.IsOrderConfirmed == true)
.Where(x => x.IsOrderFinished == false)
.Where(x => x.ConsolidatedOrderId == null)
.ToListAsync();
// Verify whether there is any order found
if (orders.Any())
{
// Get customer codes list
List <string> customerCodes = orders
.Select(x => x.CustomerCode)
.ToList();
// Create empty list of customers
List <Customer> customers = new List <Customer>();
// Verify whether customer codes is not empty
if (customerCodes.Any())
{
// Build query parameters
List <QueryParameter> parameters = new List <QueryParameter>();
parameters.Add(new QueryParameter {
key = "{schema}", value = AS400Schema.GetSchema(Constants.DATA_WAREHOUSE_PREFIX)
});
parameters.Add(new QueryParameter {
key = "{customerCodes}", value = $"'{string.Join("','", customerCodes)}'"
});
customers = _context
.Customers
.FromSql(QueryBuilder.Build("GetCustomersById.txt", parameters))
.ToList();
}
// Create empty list
List <Object> result = new List <Object>();
// Iterate through all orders found
foreach (var order in orders)
{
// Create empty list for result items
List <Object> resultItems = new List <Object>();
// Get items from each order
var items = order.Items;
// Iterate through all items found in the order
foreach (var item in items)
{
// Add new dispatch item to the result item
resultItems.Add(new
{
item.ItemCode,
Quantity = item.TotalOfUnits
});
}
// Add result item to result
result.Add(new
{
order.OrderNumber,
order.CustomerCode,
customerName = customers.Any()
?
customers
.Where(x => x.CustomerCode == order.CustomerCode)
.Select(x => x.CustomerName).FirstOrDefault()
:
"",
order.OrderTypeId,
order.DeliveryDate,
RouteCode = order.DeliveryRoute,
Items = resultItems
});
}
// Return result in proper JSON
return(Ok(result));
}
else
{
return(NotFound(new List <Object>()));
}
}
public List <Models.AS400.Customer> GetCustomersByRoute(
[FromRoute] string routeCode,
[FromRoute] Decimal?customerCode = null
)
{
// Create a new route helper instance
Helpers.Route routeHelper = new Helpers.Route(_distributionContext);
// Save route type
var isDeliveryRoute = IsDeliveryRoute(routeCode);
// Build query standard parameters
List <QueryParameter> parameters = new List <QueryParameter>();
parameters.Add(new QueryParameter {
key = "{schema}", value = AS400Schema.GetSchema(Constants.DATA_WAREHOUSE_PREFIX)
});
parameters.Add(new QueryParameter {
key = "{routeCodes}", value = $"'{routeCode}'"
});
// Query all customers
List <Models.AS400.Customer> customers = _BPCSContext
.Customers
.FromSql(QueryBuilder.Build("GetCustomersByRoute.txt", parameters))
.ToListAsync()
.Result;
// Verify whether customer code is not empty
if (customerCode != null)
{
// Filter customer list by customer code
customers = customers.FindAll(x => x.CustomerCode == customerCode);
}
// Verify whether route is for delivery or not
if (isDeliveryRoute)
{
// Query orders and extract customer codes
List <Models.Order> orders = routeHelper.GetRouteOrders(routeCode);
// Verify whether any order was found
if (orders.Count > 0)
{
// Create an empty list to store the customer codes
List <int> customerCodes = new List <int>();
// Iterate through all orders found
foreach (var order in orders)
{
// Verify whether the customer codes list do not contain the same customer code
if (!customerCodes.Contains(Int32.Parse(order.CustomerCode)))
{
customerCodes.Add(Int32.Parse(order.CustomerCode));
}
}
// Verify whether the customer codes list is not empty
if (customerCodes.Count > 0)
{
// Filter customers list by customer codes
return(customers
.FindAll(x => customerCodes.Contains(x.CustomerCode) || x.CreditLimit > 0));
}
}
else
{
return(new List <Models.AS400.Customer>());
}
}
// Return result
return(customers);
}
public List <PriceList> GetPriceListByRoute([FromRoute] string routeCode, [FromQuery] int numberOfRecords = 10)
{
// Create a new route helper instance
Helpers.Route routeHelper = new Helpers.Route(_distributionContext);
// Save route type
var isDeliveryRoute = IsDeliveryRoute(routeCode);
// Build query standard parameters
List <QueryParameter> parameters = new List <QueryParameter>();
parameters.Add(new QueryParameter {
key = "{numberOfRecords}", value = numberOfRecords.ToString()
});
parameters.Add(new QueryParameter {
key = "{schema}", value = AS400Schema.GetSchema(Constants.AS400_PREFIX)
});
parameters.Add(new QueryParameter {
key = "{routeCode}", value = routeCode
});
// Query all price lists
var priceLists = _BPCSContext
.PriceLists
.FromSql(QueryBuilder.Build("GetPriceListByRoute.txt", parameters))
.ToListAsync()
.Result;
// Verify whether route is for delivery or not
if (isDeliveryRoute)
{
// Query orders and extract customer codes
List <Models.Order> orders = routeHelper.GetRouteOrders(routeCode);
// Verify whether any order was found
if (orders.Count > 0)
{
// Create an empty list to store the item codes
List <string> itemCodes = new List <string>();
// Iterate through all orders found
foreach (var order in orders)
{
// Get order items
List <OrderItem> items = order.Items;
// Verify whether any item was found in the order
if (items.Count > 0)
{
// Iterate through all items found
foreach (var item in items)
{
// Verify whether the item codes list do not contain the same item code
if (!itemCodes.Contains(item.ItemCode))
{
itemCodes.Add(item.ItemCode);
}
}
}
}
// Verify whether the item codes list is not empty
if (itemCodes.Count > 0)
{
// Filter price lists by item codes
return(priceLists.FindAll(x => itemCodes.Contains(x.ItemCode)));
}
}
else
{
return(new List <PriceList>());
}
}
return(priceLists);
}
public IEnumerable <Models.AS400.Customer> GetCustomers([FromQuery] string search)
{
List <string> routeCodes = new List <string>();
routeCodes.Add("A01");
routeCodes.Add("A02");
routeCodes.Add("A03");
routeCodes.Add("A04");
routeCodes.Add("A05");
routeCodes.Add("A06");
routeCodes.Add("A07");
routeCodes.Add("A08");
routeCodes.Add("A09");
routeCodes.Add("A10");
routeCodes.Add("A11");
routeCodes.Add("A12");
routeCodes.Add("A13");
routeCodes.Add("A14");
routeCodes.Add("A15");
routeCodes.Add("A16");
routeCodes.Add("A17");
routeCodes.Add("A18");
routeCodes.Add("A19");
routeCodes.Add("A20");
routeCodes.Add("A21");
routeCodes.Add("B01");
routeCodes.Add("BCH");
routeCodes.Add("BFC");
routeCodes.Add("BPF");
routeCodes.Add("BTA");
routeCodes.Add("C42");
routeCodes.Add("T01");
routeCodes.Add("T02");
routeCodes.Add("T03");
routeCodes.Add("T04");
routeCodes.Add("T05");
routeCodes.Add("T06");
routeCodes.Add("T07");
routeCodes.Add("T08");
routeCodes.Add("T09");
routeCodes.Add("T10");
routeCodes.Add("T11");
routeCodes.Add("T12");
routeCodes.Add("T13");
routeCodes.Add("T14");
routeCodes.Add("T15");
routeCodes.Add("T16");
routeCodes.Add("T17");
routeCodes.Add("T18");
routeCodes.Add("T19");
routeCodes.Add("T20");
routeCodes.Add("T21");
routeCodes.Add("T22");
routeCodes.Add("T23");
routeCodes.Add("T24");
routeCodes.Add("T25");
routeCodes.Add("T26");
routeCodes.Add("T27");
routeCodes.Add("T28");
routeCodes.Add("T29");
routeCodes.Add("T30");
routeCodes.Add("T31");
routeCodes.Add("T32");
routeCodes.Add("T33");
routeCodes.Add("T34");
routeCodes.Add("T35");
routeCodes.Add("T36");
routeCodes.Add("T37");
routeCodes.Add("T38");
routeCodes.Add("T39");
routeCodes.Add("T40");
routeCodes.Add("T41");
routeCodes.Add("T42");
routeCodes.Add("T43");
routeCodes.Add("T44");
routeCodes.Add("T45");
routeCodes.Add("T46");
routeCodes.Add("T47");
routeCodes.Add("T50");
routeCodes.Add("T51");
routeCodes.Add("T52");
routeCodes.Add("T53");
routeCodes.Add("T54");
routeCodes.Add("T55");
routeCodes.Add("T56");
routeCodes.Add("T57");
routeCodes.Add("T58");
routeCodes.Add("T59");
routeCodes.Add("T60");
routeCodes.Add("T61");
routeCodes.Add("T62");
routeCodes.Add("T63");
routeCodes.Add("T64");
routeCodes.Add("V01");
routeCodes.Add("V02");
// Build query parameters
List <QueryParameter> parameters = new List <QueryParameter>();
parameters.Add(new QueryParameter {
key = "{schema}", value = AS400Schema.GetSchema(Constants.DATA_WAREHOUSE_PREFIX)
});
parameters.Add(new QueryParameter {
key = "{routeCodes}", value = $"'{string.Join("','", routeCodes)}'"
});
// List all customers by routes
List <Models.AS400.Customer> customers = _BPCSContext
.Customers
.FromSql(QueryBuilder.Build("GetCustomersByRoute.txt", parameters))
.ToList();
// Verify whether the search is not null or empty
if (!string.IsNullOrEmpty(search))
{
// Delete any inconsistency in search parameter
string updatedSearch = search.ToLower();
// Apply search filter
customers = customers
.Where(
x =>
x.CustomerCode.ToString().Contains(updatedSearch) ||
x.CustomerName.ToLower().Contains(updatedSearch) ||
x.BusinessName.ToLower().Contains(updatedSearch)
)
.ToList();
}
// Return result
return(customers);
//// Get current domain user
//string username = User.Identity.Name;
//// Verify whether the user authenticated exists
//if (string.IsNullOrEmpty(username))
//{
// // Return empty result
// return new List<Customer>();
//}
//else
//{
// // Build query parameters
// List<QueryParameter> parameters = new List<QueryParameter>();
// parameters.Add(new QueryParameter { key = "{username}", value = username.Replace(@"\\", @"\") });
// // Get list of routes associated to current logged user
// List<Models.Route> routes = _distributionContext
// .Routes
// .FromSql(QueryBuilder.Build("GetRoutesFromDomainUser.txt", parameters))
// .ToList();
// // Verify whether any route was found
// if (routes.Any())
// {
// // Get only the route code from the list
// List<string> routeCodes = routes.Select(x => x.RouteCode).ToList();
// // Remove previous parameter
// parameters.RemoveAt(0);
// // Build extra parameters
// parameters.Add(new QueryParameter { key = "{schema}", value = AS400Schema.GetSchema(Constants.DATA_WAREHOUSE_PREFIX) });
// parameters.Add(new QueryParameter { key = "{routeCodes}", value = $"'{string.Join("','", routeCodes)}'" });
// // List all customers by routes
// List<Customer> customers = _BPCSContext
// .Customers
// .FromSql(QueryBuilder.Build("GetCustomersByRoute.txt", parameters))
// .ToList();
// // Verify whether the search is not null or empty
// if (!string.IsNullOrEmpty(search))
// {
// // Delete any inconsistency in search parameter
// string updatedSearch = search.ToLower();
// // Apply search filter
// customers = customers
// .Where(
// x =>
// x.CustomerCode.ToString().Contains(updatedSearch) ||
// x.CustomerName.ToLower().Contains(updatedSearch) ||
// x.BusinessName.ToLower().Contains(updatedSearch)
// )
// .ToList();
// }
// // Return result
// return customers;
// }
// else
// {
// // Return empty result
// return new List<Customer>();
// }
//}
}
