Beispiel #1
0
        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
        }
Beispiel #2
0
        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;
        }