internal static bool GetShippingPreparationValidationMessage(float quantity, float stockLevel, string partNr, out ShippingPreparationValidationMessage shippingPreparationValidationMessage) { var newInstance = new ShippingPreparationValidationMessage(); newInstance.PartNr = partNr; var valid = true; if ((quantity >= 0 && stockLevel >= 0) && (quantity > stockLevel)) { newInstance.Message = string.Format("The stock level for part with nr {0} is too low. Ordered quantity: {1}, actual stock level: {2}", partNr, quantity, stockLevel); valid = false; } shippingPreparationValidationMessage = newInstance; return(valid); }
/// <summary> /// Prepares the shipping json. /// </summary> /// <returns></returns> public ActionResult PrepareShippingJSON() { _logger.LogDebug("User {0} chose to display the Shipping Preparation Interface.", User.Identity.Name); try { // create lists for picking, packaging and validations var packagingList = new List <OrderLine>(); var pickingList = new List <Picking>(); var bomList = new List <dynamic>(); var validationMessages = new List <ShippingPreparationValidationMessage>(); // retrieve shipping GTS meta data from the database var activeShippingPreparations = getActiveShippingPreparations(); // do an old-school Sql Cmd approach var connection = new SqlConnection(SAGE_CONN_STR); using (connection) { var SqlCmd = new SqlCommand(SAGE_SQL_STR, connection); connection.Open(); using (SqlDataReader oReader = SqlCmd.ExecuteReader()) { while (oReader.Read()) { Console.WriteLine(oReader.ToString()); // create a list of BOM nrs bomList.Add( new { BomNr = oReader["PickingBOM"].ToString(), IsActive = activeShippingPreparations.Where(x => x.BOMNr == oReader["PickingBOM"].ToString()).Count() > 0 }); // filter out data for non-activated Shippings (activation starts / stops are done in this page as well) if (activeShippingPreparations.Where(x => x.BOMNr == oReader["PickingBOM"].ToString()).Count() == 0) { continue; } // Use query result row to cast to internal DTO classes var packagingDTO = new OrderLine(oReader, User.Identity.Name); packagingList.Add(packagingDTO); } connection.Close(); } } if (packagingList.Count > 0) { // ensure ClientRef and SalesOrder are found on every line var clientRef = packagingList.Where(order => !string.IsNullOrEmpty(order.SalesOrder)).First().ClientRef; var salesOrder = packagingList.Where(order => !string.IsNullOrEmpty(order.SalesOrder)).First().SalesOrder; var colorTagIndex = 0; var isCommercialBom = false; packagingList.ForEach(order => { if (order.CommercialBOM == "O") { isCommercialBom = true; } if (string.IsNullOrEmpty(order.CommercialBOM)) { isCommercialBom = false; } if (!isCommercialBom) { colorTagIndex = (colorTagIndex + 1) % 6; } else if (order.CommercialBOM != "O") { order.DoIndent = true; } if (!string.IsNullOrEmpty(order.PartNr)) { order.ColorTag = _colorTable[colorTagIndex]; } order.ClientRef = clientRef; order.SalesOrder = salesOrder; }); } // when we're done creating an picking list, regroup the orderlist according to BOM nr var tempOrderList = new List <OrderLine>(); packagingList.OrderBy(x => x.PickingBOM).ToList().ForEach(order => { var currentorderQuantity = order.Quantity; if (currentorderQuantity > 1 && order.UoM == "PCE") { for (var c = 0; c < currentorderQuantity; c++) { //order.Quantity = 1; tempOrderList.Add(order); } } else { tempOrderList.Add(order); } }); var groupedOrderList = tempOrderList.GroupBy(order => order.PickingBOM); // create a picking list from the list of orders made in the previous step packagingList.ToList().ForEach(packagingDTO => { var existingItemInPickingList = pickingList.Where(item => item.PartNr == packagingDTO.PartNr).SingleOrDefault(); if (existingItemInPickingList == null) { existingItemInPickingList = new Picking(packagingDTO); existingItemInPickingList.Quantity = 0; // set quantity to null, we will recalculate this below // create a fake stock location if needed if (string.IsNullOrEmpty(packagingDTO.StockLocation) && packagingDTO.StockLevel > 0 && !string.IsNullOrEmpty(packagingDTO.UoM)) { existingItemInPickingList.StockLocation = ""; } pickingList.Add(existingItemInPickingList); } if (/*packagingDTO.UoM == "PCE" && */ packagingDTO.Quantity > 0) { existingItemInPickingList.Quantity += packagingDTO.Quantity; } }); // check stock levels to create a picking issue summary ShippingPreparationValidationMessage shippingPrepValidationMessage = null; pickingList.ForEach(shippingDTO => { if (ShippingPreparationValidationMessage.GetShippingPreparationValidationMessage(shippingDTO.Quantity, shippingDTO.StockLevel, shippingDTO.PartNr, out shippingPrepValidationMessage) == false) { validationMessages.Add(shippingPrepValidationMessage); } }); _logger.LogInfo("User {0} successfully requested Shipping Preparation data.", User.Identity.Name); assignIdMasks(tempOrderList); // try to retrieve existing orderLines with the BOM from the database var searchCriteria = UnitOfWorkSession.CreateCriteria <OrderLine>() .Add(Expression.Eq("PickingBOM", packagingList.Count > 0 ? packagingList[0]?.PickingBOM : "")); // put the results in a list var existingPackagingEntities = searchCriteria.List <OrderLine>().ToList(); // for every collection of order lines with the same BOM foreach (var item in packagingList) { // iterate over all current oderlines and use the previously created list to check if the current line // is already in the database and create it, if not var potentiallyExistingOrderLines = existingPackagingEntities.Where(entity => entity.OrderLineNr == item.OrderLineNr); if (potentiallyExistingOrderLines.Count() > 1) { throw new InvalidOperationException(string.Format("More then 1 row was found in the database for the current Picking BOM {0} and Order Line Nr {1}", item.PickingBOM, item.OrderLineNr)); } if (potentiallyExistingOrderLines.Count() == 0) { item.Id = Guid.NewGuid(); item.EditedBy = User.Identity.Name; GTSDataRepository.Create <OrderLine>(item); } else // at this point we can assume there's only orderline found in the database, retrieve any input value if possible { item.FieldInput = potentiallyExistingOrderLines.First().FieldInput; } } return(SerializeForWeb(new { Picking = pickingList, Packing = groupedOrderList, BomList = bomList.Distinct().ToList(), ValidationMessages = validationMessages, TotalToPick = pickingList.Where(picking => !string.IsNullOrEmpty(picking.StockLocation)).DistinctBy(picking => picking.PartNr).Count() })); } catch (Exception ex) { _logger.LogError(ex); throw; } }