private void OutputToExcel()
{
ExcelPackage package = null;
ExcelWorksheet workSheet = null;
OpenFileDialog ofd = null;
if (InvokeRequired)
{
ofd = Invoke(new dSelectOutput(selectOutput)) as OpenFileDialog;
}
if (ofd == null)
{
return;
}
workSheet = ExcelToll.Load(ref package, ofd.FileName, txtFormat.Text);
if (workSheet == null)
{
Log("Failed to load worksheet ");
return;
}
// output column locations
var customerPO = ExcelToll.GetCellColumn(workSheet, "CUSTOMER PO #", 0);
var invoiceNO = ExcelToll.GetCellColumn(workSheet, "INVOICE #", 0);
var conId = ExcelToll.GetCellColumn(workSheet, "CONSIGNMENT REFERENCE", 0);
var date = ExcelToll.GetCellColumn(workSheet, "DATE DELIVERED", 0);
var pickup = ExcelToll.GetCellColumn(workSheet, "Date of Pickup", 0);
var pieces1 = ExcelToll.GetCellColumn(workSheet, "Pieces", 0);
var pieces2 = ExcelToll.GetCellColumn(workSheet, "Pieces", 1);
var anspec = ExcelToll.GetCellColumn(workSheet, "Anspec Date", 0);
var courier = ExcelToll.GetCellColumn(workSheet, "Courier", 0);
anspec = anspec == 0 ? ExcelToll.GetCellColumn(workSheet, "DBS Date", 0) : anspec;
// prevent crash if a column is missing
if (customerPO == 0 || invoiceNO == 0 || conId == 0 || date == 0 || pickup == 0 || anspec == 0)
{
Log("Failed to find one of the columns");
return;
}
var donelist = new List <Delivery>();
var matches = 0;
FindMatchesByInvoiceID(ExcelToll.GetColumnRange(workSheet, "INVOICE #"), workSheet, anspec, pickup, ref matches, donelist, courier, pieces1, pieces2, customerPO, invoiceNO, conId, date);
FindMatchesByCustomerPo(ExcelToll.GetColumnRange(workSheet, "CUSTOMER PO #"), workSheet, anspec, pickup, ref matches, donelist, courier, pieces1, pieces2, customerPO, invoiceNO, conId, date);
if (donelist.Count == deliveries.Count)
{
return;
}
var notDone = deliveries.Where(d => !donelist.Contains(d)).ToList();
deliveries = notDone;
Log($"{matches} matches updated");
package.Save();
// show details of deliveries not found in output for manual assignment
var frm = new Form2(deliveries);
frm.ShowDialog();
// only process the orders that could not be matched to speed up future processing
}
private void ReadExcel()
{
var isNZInput = false;
var isAutoUpdate = false;
ExcelPackage package = null;
ExcelWorksheet workSheet = null;
var ofd = new OpenFileDialog
{
Filter = @"Excel Files|*.xlsx;*.xlsm;*.xls;*.csv;",
Title = @"Select Output File"
};
if (ofd.ShowDialog() != DialogResult.OK)
{
return;
}
workSheet = ExcelToll.Load(ref package, ofd.FileName, "SHIPPED");
//deliveries.Clear();
// only continue if excel file loaded
if (package == null)
{
return;
}
/*workSheet = ExcelToll.Load(ref package, ofd.FileName, "SHIPPED") ??
* ExcelToll.Load(ref package, ofd.FileName, "BNMA") ??
* ExcelToll.Load(ref package, ofd.FileName, "BNM STATS") ??
* ExcelToll.Load(ref package, ofd.FileName, "ABBOTTS STATS");*/
if (workSheet == null)
{
// loads packages multiple times
// workSheet = ExcelToll.Load(ref package, ofd.FileName, "BNMA") ?? ExcelToll.Load(ref package, ofd.FileName, "BNM STATS")?? ExcelToll.Load(ref package, ofd.FileName, "ABBOTTS STATS");
workSheet = ExcelToll.GetWorksheet(package, "BNMA") ?? ExcelToll.GetWorksheet(package, "BNM STATS") ??
ExcelToll.GetWorksheet(package, "ABBOTTS STATS");
if (package.Workbook.Worksheets.Any(w => w.Name.ToUpper() == "BNMA"))
{
// if there is a worksheet called BNMA /BNMNZ / BMA then it is reprocessing.
isAutoUpdate = true;
var work = package.Workbook.Worksheets.FirstOrDefault(w =>
string.Equals(w.Name, txtFormat.Text, StringComparison.CurrentCultureIgnoreCase));
workSheet = work ?? package.Workbook.Worksheets.First();
}
else if (package.Workbook.Worksheets.Any(w =>
w.Name.ToUpper() == "BNM STATS" || w.Name.ToUpper() == "ABBOTTS STATS"))
{
isNZInput = true;
}
else
{
Log("Failed to load worksheet ");
return;
}
}
if (package.Workbook.Worksheets.Any(w => w.Name.ToUpper() == "BNMA"))
{
// if there is a worksheet called BNMA /BNMNZ / BMA then it is reprocessing.
isAutoUpdate = true;
var work = package.Workbook.Worksheets.FirstOrDefault(w =>
string.Equals(w.Name, txtFormat.Text, StringComparison.CurrentCultureIgnoreCase));
workSheet = work ?? package.Workbook.Worksheets.First();
}
else if (package.Workbook.Worksheets.Any(w =>
w.Name.ToUpper() == "BNM STATS" || w.Name.ToUpper() == "ABBOTTS STATS"))
{
isNZInput = true;
}
// adds all rows that are are missing the date delivered
if (isAutoUpdate)
{
// read columns into lists
var invoiceIds = ExcelToll.GetColumn(workSheet, "INVOICE #");
var customerPOs = ExcelToll.GetColumn(workSheet, "CUSTOMER PO #");
var conIds = ExcelToll.GetColumn(workSheet, "CONSIGNMENT REFERENCE");
var dates = ExcelToll.GetColumn(workSheet, "DATE DELIVERED");
var courier = ExcelToll.GetColumn(workSheet, "COURIER");
if (invoiceIds == null || customerPOs == null || conIds == null || dates == null || courier == null)
{
Log("Failed to find all columns with the correct names");
return;
}
// row 94 failed in excel range
// changed to default for loop
for (var i = 0; i < conIds.Count; i++)
{
if (i >= dates.Count)
{
continue;
}
if (dates[i] == "")
{
if (string.IsNullOrWhiteSpace(conIds[i]))
{
continue;
}
// Console.WriteLine($"{i + 3} {customerPOs[i]} {invoiceIds[i]} {conIds[i]} {courier[i]}");
deliveries.Add(new Delivery(customerPOs[i], invoiceIds[i], conIds[i], "Unknown", courier[i],
new DateTime()));
}
}
}
else if (isNZInput)
{
workSheet = package.Workbook.Worksheets.First(w =>
w.Name.ToUpper() == "BNM STATS" || w.Name.ToUpper() == "ABBOTTS STATS");
var consignmentIds = ExcelToll.GetColumn(workSheet, "Order #");
var courier = ExcelToll.GetColumn(workSheet, "Carrier");
var pickup = ExcelToll.GetColumn(workSheet, "First scan Date");
var pieces = ExcelToll.GetColumn(workSheet, "No. of Cartons");
if (consignmentIds == null || courier == null || pickup == null)
{
Log("Failed to find all columns with the correct names");
return;
}
for (var i = 0; i < consignmentIds.Count; i++)
{
if (string.IsNullOrWhiteSpace(consignmentIds[i]))
{
continue;
}
deliveries.Add(new Delivery(string.Empty, "NZ" + consignmentIds[i].Substring(3), consignmentIds[i],
"Unknown", courier[i], new DateTime(),
DateTime.ParseExact(pickup[i], "d/MM/yyyy", new DateTimeFormatInfo()), pieces[i]));
}
}
// adds all deliveries by default
else
{
// read columns into lists
var invoiceIds = ExcelToll.GetColumn(workSheet, "PACKSLIP");
var customerPOs = ExcelToll.GetColumn(workSheet, "CUST REF");
var conIds = ExcelToll.GetColumn(workSheet, "CON NOTE NUMBER");
var pieces = ExcelToll.GetColumn(workSheet, "Cartons");
var pickup = ExcelToll.GetColumn(workSheet, "Shipped Date");
if (invoiceIds == null || customerPOs == null || conIds == null || pickup == null || pieces == null)
{
Log("Failed to find all columns with the correct names");
return;
}
for (var i = 0; i < pickup.Count; i++)
{
if (string.IsNullOrWhiteSpace(pickup[i]))
{
pickup[i] = pickup[i - 1];
}
}
for (var i = 0; i < conIds.Count; i++)
{
if (string.IsNullOrWhiteSpace(conIds[i]))
{
continue;
}
deliveries.Add(new Delivery(customerPOs[i],
invoiceIds[i],
conIds[i],
"Unknown",
"Toll",
new DateTime(),
DateTime.ParseExact(pickup[i],
"d/MM/yyyy",
new DateTimeFormatInfo()),
pieces[i])
);
}
}
// remove certain entries
var num = 0;
deliveries = deliveries.GroupBy(d => d.conID).Select(group => group.First()).ToList();
deliveries.ForEach(i => i.invoiceID = i.invoiceID.Replace("GS", ""));
//deliveries.ForEach(i => i.invoiceID = i.invoiceID.Replace("NZ", ""));
deliveries.RemoveAll(i =>
i.invoiceID.ToUpper() == "SAMPLES" || i.invoiceID.ToUpper() == "PLES" ||
i.invoiceID.ToUpper() == "REPLACEMENT");
deliveries.RemoveAll(i => i.conID.ToUpper() == "TRANSFER" || int.TryParse(i.conID, out num));
// deliveries.RemoveAll(i => !i.conID.Contains("ARE"));
// split into separate lists matched by courier
// makes it easier to process
groupedDeliveries = deliveries.GroupBy(i => i.courier).Select(grp => grp.ToList()).ToList();
Log($"Done Loading input {deliveries.Count}");
btnRun.Enabled = true;
btnOut.Enabled = true;
}