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;
}
}
