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>(); // } //} }