private void generateVescoSpreadSheet()
{
Excel.Application excelObj2 = new Excel.Application();
Excel.Workbook vescoExcelWorkbook = null;
Excel.Worksheet vescoExcelWorksheet = null;
Excel.Worksheet locationWorkSheet = null;
Excel.Range locationRange = null;
Excel.Worksheet driverExcelWorksheet = null;
Excel.Range driverRange = null;
ExecutedOrder tempAssExOrder;
try
{
vescoExcelWorkbook = excelObj2.Workbooks.Open(Application.StartupPath + "\\Templates\\Associated Template.xlsx");
// Re-Optimize
if (checkBox1.Checked)
{
driverExcelWorksheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.get_Item("Driver");
driverRange = driverExcelWorksheet.UsedRange;
int driverRowCount = driverRange.Rows.Count;
int driverCount = 0;
String tempDriverId;
DateTime tempDateTime;
DateTime earliestStartTime;
DateTime dayEndsAt;
double hoursElapsed;
double newDrivingHours;
double newDutyHours;
// loop through all the drivers
for (int i = 2; i <= driverRowCount; i++) // Start at the second row and skip the header
{
tempDriverId = driverRange[i, 1].Value2.ToString();
// find if this driver has been added to the list
tempAssExOrder = assExOrders.Find(
delegate(ExecutedOrder aeo)
{
return(aeo.Driver == tempDriverId);
}
);
if (tempAssExOrder != null)
{
tempDateTime = DateTime.FromOADate(driverRange[i, 4].Value2);
earliestStartTime = tempAssExOrder.dtDevliveryDateTime.Date.AddHours(tempDateTime.Hour);
dayEndsAt = earliestStartTime.AddHours(driverRange[i, 3].Value2);
hoursElapsed = tempAssExOrder.dtDevliveryDateTime.Subtract(earliestStartTime).TotalHours;
newDrivingHours = Convert.ToDouble(driverRange[i, 2].Value2) - hoursElapsed;
newDrivingHours = newDrivingHours > 0 ? newDrivingHours : 0.0;
newDutyHours = Convert.ToDouble(driverRange[i, 3].Value2) - hoursElapsed;
newDutyHours = newDutyHours > 0 ? newDutyHours : 0.0;
Console.WriteLine("Earliest Start Time -> " + earliestStartTime);
Console.WriteLine("Available Driving Hours -> " + driverRange[i, 2].Value2);
Console.WriteLine("Available Duty Hours -> " + driverRange[i, 3].Value2);
Console.WriteLine("Last Delivery Time -> " + tempAssExOrder.dtDevliveryDateTime);
Console.WriteLine("\tHours Elapsed -> " + hoursElapsed);
Console.WriteLine("\tNew Earliest Start Time -> " + tempAssExOrder.dtDevliveryDateTime);
Console.WriteLine("\tNew Driving Hours -> " + newDrivingHours);
Console.WriteLine("\tNew Duty Hours -> " + newDutyHours);
Console.WriteLine();
driverExcelWorksheet.Cells[i, 2] = newDrivingHours;
driverExcelWorksheet.Cells[i, 3] = newDutyHours;
driverExcelWorksheet.Cells[i, 4] = tempAssExOrder.dtDevliveryDateTime;
driverExcelWorksheet.Cells[i, 5] = tempAssExOrder.DeliverTo;
driverCount++;
assExOrders.Remove(tempAssExOrder);
}
}
}
Console.Out.WriteLine(vescoOrders.Count());
List <String> badAssociatedOrders = AssociatedConverter.badOrders;
foreach (String b in badAssociatedOrders)
{
Console.Out.WriteLine("Bad Associated Order#," + b);
}
Console.WriteLine(Application.StartupPath);
vescoExcelWorksheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.get_Item("RouteStop");
//Location based stop delay
locationWorkSheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.get_Item("Location");
locationRange = locationWorkSheet.UsedRange;
int locationRowCount = locationRange.Rows.Count;
List <SpecialLocation> specialLocations = new List <SpecialLocation>();
SpecialLocation specialLocation;
for (int i = 2; i <= locationRowCount; i++) // Start at the second row and skip the header
{
if (locationRange[i, 6].Value2 != null)
{
specialLocation = new SpecialLocation();
specialLocation.Name = locationRange[i, 1].Value2.ToString();
specialLocation.Address = locationRange[i, 2].Value2.ToString();
specialLocation.StopDelay = locationRange[i, 6].Value2.ToString();
specialLocations.Add(specialLocation);
}
}
specialLocation = new SpecialLocation();
String stopActionCode;
String windowStart = null;
int row = 2;
//Add the converted orders
foreach (VescoOrder vo in vescoOrders)
{
vescoExcelWorksheet.Cells[row, 1] = vo.Job;
vescoExcelWorksheet.Cells[row, 2] = vo.Sequence;
vescoExcelWorksheet.Cells[row, 3] = vo.StopAction;
vescoExcelWorksheet.Cells[row, 4] = vo.Location;
vescoExcelWorksheet.Cells[row, 5] = vo.Address;
specialLocation = specialLocations.Find(item => item.Name == vo.Location && item.Address == vo.Address);
stopActionCode = vo.StopAction.Split('-')[0];
if ("11".Equals(stopActionCode) || "12".Equals(stopActionCode))
{
if (specialLocation != null)
{
vescoExcelWorksheet.Cells[row, 6] = specialLocation.StopDelay;
}
else
{
vescoExcelWorksheet.Cells[row, 6] = "60";
}
}
else
{
vescoExcelWorksheet.Cells[row, 6] = vo.StopDelay;
}
if (vo.WindowStart != null)
{
windowStart = (vo.WindowStart.Equals(vo.WindowEnd) ? null : vo.WindowStart);
}
else
{
windowStart = null;
}
vescoExcelWorksheet.Cells[row, 7] = windowStart;
vescoExcelWorksheet.Cells[row, 8] = vo.WindowEnd;
vescoExcelWorksheet.Cells[row, 9] = vo.Hazardous;
vescoExcelWorksheet.Cells[row, 10] = vo.Overweight;
vescoExcelWorksheet.Cells[row, 11] = vo.OriginalLegNumber;
vescoExcelWorksheet.Cells[row, 12] = vo.ContainerNumber;
vescoExcelWorksheet.Cells[row, 13] = vo.SteamshipLine;
vescoExcelWorksheet.Cells[row, 14] = vo.LiveLoad;
vescoExcelWorksheet.Cells[row, 15] = vo.OriginalLegType;
vescoExcelWorksheet.Cells[row, 16] = vo.DispatcherSequence;
row++;
}
// Delete the Vesco file first since I can't figure out how to get rid of the confirmation dialog
File.Delete(Application.StartupPath + "\\Templates\\Vesco.xlsx");
vescoExcelWorkbook.SaveAs(Application.StartupPath + "\\Templates\\Vesco.xlsx",
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing,
Type.Missing, false, false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read " + label7.Text + ". Original error: " + ex.Message);
}
finally
{
if (vescoExcelWorksheet != null)
{
Marshal.ReleaseComObject(vescoExcelWorksheet);
}
if (vescoExcelWorkbook != null)
{
Marshal.ReleaseComObject(vescoExcelWorkbook);
}
if (excelObj2 != null)
{
excelObj2.Quit();
Marshal.ReleaseComObject(excelObj2);
}
}
Vesco vesco = new Vesco(checkBox1.Checked, vescoOrders, badOrders, label7.Text, "Associated");
vesco.Show();
this.Hide();
vesco.performSteps();
}
private void shouldContinue()
{
if (isPmFileLoaded && isCosFileLoaded)
{
this.Cursor = Cursors.WaitCursor;
Console.Out.WriteLine("File Name" + openFileDialog1.FileName);
Excel.Application excelObj2 = new Excel.Application();
Excel.Workbook vescoExcelWorkbook = null;
Excel.Worksheet vescoExcelWorksheet = null;
Excel.Worksheet driverExcelWorksheet = null;
Excel.Range driverRange = null;
ExecutedOrder tempSwExOrder;
try
{
Console.Out.WriteLine(vescoOrders.Count());
List <String> badSouthwestOrders = SouthwestConverter.badOrders;
foreach (String b in badSouthwestOrders)
{
Console.Out.WriteLine("Bad Southwest Order#," + b);
}
vescoExcelWorkbook = excelObj2.Workbooks.Open(Application.StartupPath + "\\Templates\\Southwest Template.xlsx");
// Re-Optimize
if (checkBox1.Checked)
{
driverExcelWorksheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.get_Item("Driver");
driverRange = driverExcelWorksheet.UsedRange;
int driverRowCount = driverRange.Rows.Count;
int driverCount = 0;
String tempDriverId;
DateTime tempDateTime;
DateTime earliestStartTime;
DateTime dayEndsAt;
double hoursElapsed;
double newDrivingHours;
double newDutyHours;
// loop through all the drivers
for (int i = 2; i <= driverRowCount; i++) // Start at the second row and skip the header
{
tempDriverId = driverRange[i, 1].Value2.ToString();
// find if this driver has been added to the list
tempSwExOrder = swExOrders.Find(
delegate(ExecutedOrder aeo)
{
return(aeo.Driver == tempDriverId);
}
);
if (tempSwExOrder != null)
{
tempDateTime = DateTime.FromOADate(driverRange[i, 4].Value2);
earliestStartTime = tempSwExOrder.dtDevliveryDateTime.Date.AddHours(tempDateTime.Hour);
dayEndsAt = earliestStartTime.AddHours(driverRange[i, 3].Value2);
hoursElapsed = tempSwExOrder.dtDevliveryDateTime.Subtract(earliestStartTime).TotalHours;
newDrivingHours = Convert.ToDouble(driverRange[i, 2].Value2) - hoursElapsed;
newDrivingHours = newDrivingHours > 0 ? newDrivingHours : 0.0;
newDutyHours = Convert.ToDouble(driverRange[i, 3].Value2) - hoursElapsed;
newDutyHours = newDutyHours > 0 ? newDutyHours : 0.0;
Console.WriteLine("Earliest Start Time -> " + earliestStartTime);
Console.WriteLine("Available Driving Hours -> " + driverRange[i, 2].Value2);
Console.WriteLine("Available Duty Hours -> " + driverRange[i, 3].Value2);
Console.WriteLine("Last Delivery Time -> " + tempSwExOrder.dtDevliveryDateTime);
Console.WriteLine("\tHours Elapsed -> " + hoursElapsed);
Console.WriteLine("\tNew Earliest Start Time -> " + tempSwExOrder.dtDevliveryDateTime);
Console.WriteLine("\tNew Driving Hours -> " + newDrivingHours);
Console.WriteLine("\tNew Duty Hours -> " + newDutyHours);
Console.WriteLine();
driverExcelWorksheet.Cells[i, 2] = newDrivingHours;
driverExcelWorksheet.Cells[i, 3] = newDutyHours;
driverExcelWorksheet.Cells[i, 4] = tempSwExOrder.dtDevliveryDateTime;
driverExcelWorksheet.Cells[i, 5] = tempSwExOrder.DeliverTo;
driverCount++;
swExOrders.Remove(tempSwExOrder);
}
}
}
vescoExcelWorksheet = (Excel.Worksheet)vescoExcelWorkbook.Sheets.Item[1];
int row = 2;
String stopActionCode;
// String windowEnd = null;
String windowStart = null;
//Add the converted orders
foreach (VescoOrder vo in vescoOrders)
{
vescoExcelWorksheet.Cells[row, 1] = vo.Job;
vescoExcelWorksheet.Cells[row, 2] = vo.Sequence;
vescoExcelWorksheet.Cells[row, 3] = vo.StopAction;
vescoExcelWorksheet.Cells[row, 4] = vo.Location;
vescoExcelWorksheet.Cells[row, 5] = vo.Address;
stopActionCode = vo.StopAction.Split('-')[0];
if ("11".Equals(stopActionCode) || "12".Equals(stopActionCode))
{
vescoExcelWorksheet.Cells[row, 6] = "60";
}
else
{
vescoExcelWorksheet.Cells[row, 6] = vo.StopDelay;
}
if (vo.WindowStart != null && vo.WindowEnd != null)
{
if (vo.WindowStart.Equals(vo.WindowEnd))
{
// Both the window start and end times are the same. Increment the window end time by an hour for a more accomodating window.
DateTime dt = DateTime.Parse(vo.WindowStart, System.Globalization.CultureInfo.CurrentCulture);
windowStart = dt.AddHours(-1).ToString("HH:mm:ss");
}
else
{
windowStart = vo.WindowStart;
}
}
else
{
windowStart = vo.WindowStart;
}
vescoExcelWorksheet.Cells[row, 7] = windowStart;
vescoExcelWorksheet.Cells[row, 8] = vo.WindowEnd;
vescoExcelWorksheet.Cells[row, 9] = vo.Hazardous;
vescoExcelWorksheet.Cells[row, 10] = vo.Overweight;
vescoExcelWorksheet.Cells[row, 11] = vo.OriginalLegNumber;
vescoExcelWorksheet.Cells[row, 12] = vo.ContainerNumber;
vescoExcelWorksheet.Cells[row, 13] = vo.SteamshipLine;
vescoExcelWorksheet.Cells[row, 14] = vo.LiveLoad;
vescoExcelWorksheet.Cells[row, 15] = vo.OriginalLegType;
vescoExcelWorksheet.Cells[row, 16] = vo.DispatcherSequence;
row++;
}
// Delete the file first since I can't figure out how to get rid of the confirmation dialog
File.Delete(Application.StartupPath + "\\Templates\\Vesco.xlsx");
vescoExcelWorkbook.SaveAs(Application.StartupPath + "\\Templates\\Vesco.xlsx",
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read " + label7.Text + ". Original error: " + ex.Message);
}
finally
{
if (vescoExcelWorksheet != null)
{
Marshal.ReleaseComObject(vescoExcelWorksheet);
}
if (vescoExcelWorkbook != null)
{
Marshal.ReleaseComObject(vescoExcelWorkbook);
}
if (excelObj2 != null)
{
excelObj2.Quit();
Marshal.ReleaseComObject(excelObj2);
}
}
Vesco vesco = new Vesco(checkBox1.Checked, vescoOrders, badOrders, label7.Text + "," + label1.Text, "SouthWest", runDate);
vesco.Show();
vesco.SouthWestForm = this;
this.Hide();
vesco.performSteps();
}
}