/// <summary>
/// Update the depot in the database
/// </summary>
/// <param name="depot"></param>
public static void UpdateDepot(Depot depot)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
cnn.Execute("UPDATE 'Depots' SET \"Name\" = @Name, \"CSVName\" = @CSVName, \"OrderName\" = @OrderName WHERE \"Id\" = @Id", depot);
}
}
/// <summary>
/// A function that will export the orders with provided order ID to a CSV file
/// </summary>
/// <param name="orderId">The orderID that is used to group orders together</param>
public static void ExportOrdersToCSV(string orderId)
{
// Create a list of orders that we want to process and populate it
ObservableCollection <Order> orders = IoC.Orders().GetAllOrdersWithID(orderId);
// Make sure that list is not empty
if (orders.Count == 0)
{
return;
}
// Create a list of strings which will represent lines on csv files
List <string> lines = new List <string>();
// Add the row of headings
lines.Add("Customer,Branch,Required Date,Customer Reference,Product Code,Order Qty,Price,");
// Iterate over the list of orders that need to be printed
foreach (Order order in orders)
{
// Get customer and depot for each
Customer cust = IoC.Customers().GetCustomerByID(order.CustomerID);
Depot depot = cust.GetDepot(order.DepotID);
foreach (OrderProduct product in order.Products)
{
// Get the price of the current product
decimal price = cust.GetProduct(product.ProductID).Price;
// Convert the price into the correct format for exporting
string priceString = price == 0m ? "" : string.Format(CultureInfo.CreateSpecificCulture("en-GB"), "{0:F2}", price);
// Add the line with all required information into the list of lines
lines.Add($"{ cust.CSVName },{ depot.CSVName },{ order.Date.ToShortDateString() },{ order.OrderReference },{ cust.GetProduct(product.ProductID).CSVName },{ product.Quantity },{ priceString },");
}
}
if (lines.Count > 0)
{
DateTime time = DateTime.Now;
string pcName = Environment.MachineName;
string fileName = $"order_{pcName}_{time.Year}_{time.Month}_{time.Day}_{time.Hour}_{time.Minute}_{time.Second}_{orderId}.csv";
UserSettings settings = Settings.LoadSettings();
// Before saving the file, make sure that the export path exists, if not then create it
if (!Directory.Exists(settings.UserCSVExportPath))
{
Directory.CreateDirectory(settings.UserCSVExportPath);
}
// Create the file
File.WriteAllLines($"{ settings.UserCSVExportPath }\\{ fileName }", lines);
}
}
/// <summary>
/// Add a new depot to the list of depots if it doesn't already exist
/// </summary>
/// <param name="depot">A <see cref="Depot"/> object</param>
/// <returns><see cref="bool"/> false if the depot already exists</returns>
public bool AddDepot(Depot depot)
{
// Make sure to check whether this depot exists first
// We don't want duplicate depots
if (!HasDepot(depot.Id))
{
Depots.Add(depot);
return(true);
}
return(false);
}
/// <summary>
/// A function that checks if a depot with this CSVName and OrderName already exists for this customer
/// </summary>
/// <param name="depot">A <see cref="Depot"/> object to compare</param>
/// <returns><see cref="bool"/> whether the depot already exists or not</returns>
public bool SameDepotExists(Depot depot)
{
foreach (Depot ourDepot in Depots)
{
if (ourDepot.CSVName == depot.CSVName && ourDepot.OrderName == depot.OrderName)
{
return(true);
}
}
return(false);
}
/// <summary>
/// Add a new depot to the database
/// </summary>
/// <param name="depot">A <see cref="Depot"/> object to be added</param>
public static Depot AddDepot(Depot depot)
{
Depot newDepot;
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
var result = cnn.Query <int>("INSERT INTO 'Depots' (CustomerId, Name, CSVName, OrderName) VALUES (@CustomerId, @Name, @CSVName, @OrderName); SELECT last_insert_rowid();", depot).ToList();
newDepot = cnn.QuerySingle <Depot>($"SELECT * FROM 'Depots' WHERE \"Id\" = {result[0]}", new DynamicParameters());
}
return(newDepot);
}
/// <summary>
/// A function that reads the order and extracts all required information
/// </summary>
/// <param name="orderData">The data that we should read from</param>
/// <param name="fileName">Name of the order file</param>
/// <param name="customer">Customer associated with this order</param>
public static async void ParseOrderAsync(DataSet orderData, string fileName, Customer customer)
{
foreach (DataTable table in orderData.Tables)
{
// TODO: Future improvement: consolidate errors of the same type together and display at once
// Variables that will be required
Dictionary <int, List <OrderWarning> > depotWarnings = new Dictionary <int, List <OrderWarning> >();
List <string> readQtyErrors = new List <string>();
// Required data to extract in string form
string dateString = null;
string orderReference = null;
Dictionary <int, string> depotStrings = new Dictionary <int, string>();
Dictionary <int, string> productStrings = new Dictionary <int, string>();
Dictionary <Cell, double> orderQuantities = new Dictionary <Cell, double>();
// Required data
List <Depot> depots = new List <Depot>();
List <Product> products = new List <Product>();
DateTime deliveryDate = DateTime.MinValue;
// Data locations
Cell dateCell = new Cell("D1");
int productCol1 = 1;
int productCol2 = 3;
int productRowStart = 3;
int depotRow = 2;
int depotColStart = 5;
int depotColEnd = table.Columns.Count - 1;
int refCol = table.Columns.Count;
// Extract the data
dateString = GetCell(dateCell, table);
orderReference = GetCell(refCol, 1, table);
// Get list of depots
for (int c = depotColStart; c <= depotColEnd; c++)
{
depotStrings.Add(c, GetCell(c, depotRow, table));
}
// Get list of products
for (int r = productRowStart; r < productRowStart + 10; r++)
{
string c1 = GetCell(productCol1, r, table);
string c2 = GetCell(productCol2, r, table);
if (c1 == null && c2 == null)
{
break;
}
productStrings.Add(r, $"{c1}{c2}");
}
// Get all cells wich contain ordered product
for (int c = depotColStart; c <= depotColEnd; c++)
{
for (int r = productRowStart; r <= productRowStart + productStrings.Count - 1; r++)
{
Cell cell = new Cell(c, r);
string cellValue = GetCell(cell, table);
if (cellValue != null)
{
if (double.TryParse(cellValue, out double quantity))
{
if (quantity > 0.0)
{
orderQuantities.Add(cell, quantity);
}
}
else
{
readQtyErrors.Add($"Depot: {depotStrings[c]}, Product: {productStrings[r]}\n");
}
}
}
}
List <int> usedDepotColumns = new List <int>();
// Get all the used depot information
foreach (var orderQuantity in orderQuantities)
{
foreach (var depotString in depotStrings)
{
if (orderQuantity.Key.column == depotString.Key)
{
if (customer.HasDepotOrderName(depotString.Value))
{
if (!usedDepotColumns.Contains(depotString.Key))
{
usedDepotColumns.Add(depotString.Key);
}
}
else
{
string errorMessage = $"Unknown depot \"{depotString.Value}\" was found in file {fileName}.\n" +
"If this is a new depot, please add it to the list.\n" +
$"\nThis file will be processed without depot \"{depotString.Value}\". You will need to add order for that depot manually.";
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "File Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
}
}
}
List <int> usedProductRows = new List <int>();
// Get all the used product information
foreach (var orderQuantity in orderQuantities)
{
foreach (var productString in productStrings)
{
if (orderQuantity.Key.row == productString.Key)
{
if (customer.HasProductOrderName(productString.Value))
{
if (!usedProductRows.Contains(productString.Key))
{
usedProductRows.Add(productString.Key);
}
}
else
{
string errorMessage = $"Unknown product \"{productString.Value}\" was found in file {fileName}.\n" +
"If this is a new product, please add it to the list.\n" +
$"\nThis file will be processed without product \"{productString.Value}\". You will need to add that product to order manually if required.";
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "File Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
}
}
}
// Validation
// Make sure that we have found all necessary data
if (orderQuantities.Count == 0 || orderReference == null || dateString == null)
{
string errorMessage = $"Following issues have been found in file {fileName}:\n";
if (orderQuantities.Count == 0)
{
errorMessage += "* No orders have been found\n";
}
if (orderReference == null)
{
errorMessage += "* PO reference number was not found\n";
}
if (dateString == null)
{
errorMessage += "* Delivery date was not found\n";
}
errorMessage += "\nThis file was not processed.\n";
errorMessage += "If you think this file has all the correct data, please contact Patryk Z.";
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "File Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
else
{
// Process and validate delivery date
if (!DateTime.TryParse(dateString.Trim(), out deliveryDate))
{
string errorMessage = $"Could not read the date from file {fileName}.\n" +
"Please check the date in Excel file. If the date format looks correct then please contact Patryk Z.\n" +
"\nThis file was not processed.";
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "File Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
else if (deliveryDate != DateTime.Today.AddDays(1))
{
string errorMessage = $"Delivery date ({deliveryDate.ToShortDateString()}) in file {fileName} is not tomorrow.";
// Create a new warning object for this order
foreach (int col in usedDepotColumns)
{
if (!depotWarnings.ContainsKey(col))
{
depotWarnings.Add(col, new List <OrderWarning>());
}
depotWarnings[col].Add(new OrderWarning(OrderWarning.WarningType.UnusualDate, errorMessage));
}
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Unusual Date Warning",
Message = errorMessage,
ButtonText = "OK"
});
}
if (orderReference.EndsWith("01"))
{
string errorMessage = $"Order reference {orderReference} in file {fileName} indicates that this is a provisional order.\n\n" +
"Provisional orders usually end with \"01\" while full orders usually end with \"02\".\n\n" +
"Would you like to process this order anyway?";
// Display error message to the user
var result = await IoC.UI.ShowMessage(new YesNoBoxDialogViewModel
{
Title = "Order Reference Warning",
Question = errorMessage
});
if (result == DialogResult.No)
{
return;
}
}
// Display all the cells that could not be read from
if (readQtyErrors.Count > 0)
{
string errorMessage = $"Not all data could be read from file {fileName}.\n" +
"Please see below which orders could not be read:\n\n";
foreach (string error in readQtyErrors)
{
errorMessage += error;
}
errorMessage += "\nThis file was not processed.";
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "File Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
// Create the order objects
foreach (int col in usedDepotColumns)
{
Depot depot = customer.GetDepot(depotStrings[col]);
Order order = new Order(orderReference, deliveryDate, customer.Id, depot.Id);
// Add products
foreach (int row in usedProductRows)
{
Cell cell = new Cell(col, row);
foreach (var orderQuantity in orderQuantities)
{
if (orderQuantity.Key.IsSameAs(cell) && orderQuantity.Value > 0.0)
{
Product product = customer.GetProduct(productStrings[row]);
order.AddProduct(product.Id, orderQuantity.Value);
}
}
}
// Add warnings
if (depotWarnings.ContainsKey(col))
{
foreach (OrderWarning warning in depotWarnings[col])
{
order.AddWarning(warning);
}
}
if (order.Products.Count > 0)
{
// Check if the same order already exists
if (IoC.Orders().HasOrder(order))
{
string errorMessage = $"The order in file {fileName} for depot {order.DepotName} could not be processed. The same order already exists.";
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Order Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
else
{
// Add this order to the list
IoC.Orders().AddOrder(order);
}
}
}
}
}
}
public static async void ParseOrderAsync(string[] orderText, string fileName, Customer customer)
{
// Variables that will be required
bool isAllDataFound = true;
double unknownProductQuantity = 0.0;
List <OrderWarning> warnings = new List <OrderWarning>();
// Required data to extract in string form
string depotString = null;
string dateString = null;
string orderTotalString = null;
string orderReference = null;
Dictionary <string, string> productStrings = new Dictionary <string, string>();
// Required data
Depot depot = null;
DateTime deliveryDate = DateTime.MinValue;
double orderTotal = 0.0;
List <OrderProduct> products = new List <OrderProduct>();
// Product code specification
int productCodeMinLength = 7;
// Iterate over the lines to find required data
foreach (string line in orderText)
{
// Look for depot name if not already found
if (depotString == null)
{
if (customer.HasDepotOrderName(line))
{
depotString = line;
}
}
// Look for order reference if not already found
if (orderReference == null)
{
string orderReferenceTitle = "Customer PO Number: ";
if (line.StartsWith(orderReferenceTitle))
{
orderReference = line.Substring(orderReferenceTitle.Length);
}
}
// Look for delivery date if not already found
if (dateString == null)
{
string deliveryDateTitle = "Delivery Date: ";
if (line.StartsWith(deliveryDateTitle))
{
dateString = line.Substring(deliveryDateTitle.Length);
}
}
// Look for total boxes if not already found
if (orderTotalString == null)
{
string orderTotalTitle = "Total Boxes : ";
if (line.StartsWith(orderTotalTitle))
{
orderTotalString = line.Substring(orderTotalTitle.Length);
}
}
// Look for products
if (line.Length > productCodeMinLength)
{
int strLength = 0;
for (int c = 0; c < line.Length; c++)
{
if (char.IsWhiteSpace(line, c))
{
strLength = c;
break;
}
}
if (strLength >= productCodeMinLength && line.Substring(0, strLength).All(char.IsDigit))
{
int qtyAtIndex = 0;
for (int i = line.Length - 1; i > 0; i--)
{
if (char.IsWhiteSpace(line, i))
{
qtyAtIndex = i + 1;
break;
}
}
string productNumber = line.Substring(0, strLength);
string productQuantity = line.Substring(qtyAtIndex, line.Length - qtyAtIndex);
productStrings.Add(productNumber, productQuantity);
}
}
}
// Make sure that we have found all necessary data
if (depotString == null || orderReference == null || dateString == null || orderTotalString == null || productStrings.Count == 0)
{
string errorMessage = $"Following issues have been found in file {fileName}:\n";
if (depotString == null)
{
errorMessage += "* Depot name was not found (that likely means that it's a new depot that Order Reader doesn't know about yet, please manually check and add a new depot if required)\n";
}
if (orderReference == null)
{
errorMessage += "* PO reference number was not found\n";
}
if (dateString == null)
{
errorMessage += "* Delivery date was not found\n";
}
if (orderTotalString == null)
{
errorMessage += "* Total quantity was not found\n";
}
if (productStrings.Count == 0)
{
errorMessage += "* No products were found\n";
}
errorMessage += "\nThis file was not processed.\n";
errorMessage += "If you think this file has all the correct data, please contact Patryk Z.";
isAllDataFound = false;
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "File Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
else
{
// Process and validate delivery date
if (!DateTime.TryParse(dateString.Trim(), out deliveryDate))
{
string errorMessage = $"Could not read the date from file {fileName}.\n" +
"Please check the date in PDF file. If the date format looks correct then please contact Patryk Z.\n" +
"\nThis file was not processed.";
isAllDataFound = false;
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "File Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
else if (deliveryDate != DateTime.Today.AddDays(1))
{
string errorMessage = $"Delivery date ({deliveryDate.ToShortDateString()}) in file {fileName} is not tomorrow.";
// Create a new warning object for this order
warnings.Add(new OrderWarning(OrderWarning.WarningType.UnusualDate, errorMessage));
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Unusual Date Warning",
Message = errorMessage,
ButtonText = "OK"
});
}
// Process and validate all products
foreach (var product in productStrings)
{
// Make sure that this product exists
if (!customer.HasProductOrderName(product.Key))
{
// If the quantity of unknown product can be parsed then we can add this product and just display a warning
// Otherwise, we can not process this order
if (double.TryParse(product.Value, NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands, CultureInfo.InvariantCulture, out double quantity))
{
string errorMessage = $"Unknown product ({product.Key}) in file {fileName}.\n" +
"You can process this order without that product if you intend to add it manually later or if you think it's been put on by mistake.";
// Add the quantity to unknown product quantity
unknownProductQuantity += quantity;
// Create a warning object
warnings.Add(new OrderWarning(OrderWarning.WarningType.UnknownProduct, errorMessage));
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Unknown Product Warning",
Message = errorMessage,
ButtonText = "OK"
});
}
else
{
string errorMessage = $"Unknown product ({product.Key}) in file {fileName}.\n" +
"If this is a new product that Order Reader doesn't know about yet, please add it to Order Reader and try processing this file again. Otherwise, please contact Patryk Z." +
"This file was not processed.";
isAllDataFound = false;
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Unknown Product Error",
Message = errorMessage,
ButtonText = "OK"
});
}
}
else
{
// Get the product quantity
if (double.TryParse(product.Value, NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands, CultureInfo.InvariantCulture, out double quantity))
{
OrderProduct orderProduct = new OrderProduct(customer.Id, customer.GetProduct(product.Key).Id, quantity);
products.Add(orderProduct);
}
else
{
string errorMessage = $"Could not read product quantity ({product.Key}) from file {fileName}.\n" +
"Please check the product quantity in PDF file. If the quantity looks correct then please contact Patryk Z.\n" +
"This file was not processed.";
isAllDataFound = false;
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Order Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
}
}
// Validate the total quantity from PDF file
if (!double.TryParse(orderTotalString, NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands, CultureInfo.InvariantCulture, out orderTotal))
{
string errorMessage = $"Could not read the total quantity from file {fileName}.\n" +
"Please check the total quantity in PDF file. If the quantity looks correct then please contact Patryk Z.\n" +
"This file was not processed.";
isAllDataFound = false;
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Order Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
// Make sure we have valid products to add
if (products.Count == 0)
{
string errorMessage = $"No valid products foud in file {fileName}.\n" +
"This file was not processed.";
isAllDataFound = false;
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Order Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
// Create the depot object
depot = customer.GetDepot(depotString);
// If we found all required data, create the order
if (isAllDataFound)
{
// Create a new order object
Order order = new Order(orderReference, deliveryDate, customer.Id, depot.Id);
// Add products to this order
foreach (OrderProduct product in products)
{
order.AddProduct(product);
}
// Add warnings to this order
foreach (OrderWarning warning in warnings)
{
order.AddWarning(warning);
}
// Make sure that the total product quantity is correct
if (order.GetTotalProductQuantity() == orderTotal || order.GetTotalProductQuantity() == orderTotal - unknownProductQuantity)
{
// Check if the same order already exists
if (IoC.Orders().HasOrder(order) || IoC.Orders().HasOrderWithSameReference(order))
{
string errorMessage = $"The order in file {fileName} could not be processed. An order with the same reference number already exists.";
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Order Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
else
{
// Add this order to the list
IoC.Orders().AddOrder(order);
}
}
else
{
string errorMessage = $"Total field not equal to sum of all product quantities in file {fileName}.\n" +
"Please check the PDF file to see if all product quantities add up to the total amount. " +
"If they do, please contact Patryk Z.\n" +
"This file was not processed.";
// Display error message to the user
await IoC.UI.ShowMessage(new MessageBoxDialogViewModel
{
Title = "Order Processing Error",
Message = errorMessage,
ButtonText = "OK"
});
}
}
}
}