public void addCarryOvers(TriniumOrder assOrder)
{
this.Hazardous = assOrder.Haz.ToString();
this.Overweight = assOrder.Overweight.ToString();
this.OriginalLegNumber = assOrder.LegNumber;
this.ContainerNumber = assOrder.ContainerNumber;
this.SteamshipLine = assOrder.Ssl;
this.LiveLoad = assOrder.Ll.ToString();
this.OriginalLegType = assOrder.LegType;
this.DispatcherSequence = assOrder.DispatchSequence;
this.StopDelay = "15";
this.OriginalScheduledPickupDate = assOrder.ScheduledPickupDate;
this.OriginalScheduledPickupTimeFrom = assOrder.ScheduledPickupTimeFrom;
this.OriginalScheduledPickupTimeTo = assOrder.ScheduledPickupTimeTo;
this.OriginalScheduledDeliverDate = assOrder.ScheduledDeliverDate;
this.OriginalScheduledDeliverTimeFrom = assOrder.ScheduledDeliverTimeFrom;
this.OriginalScheduledDeliverTimeTo = assOrder.ScheduledDeliverTimeTo;
}
private void btnAssLoad_Click(object sender, EventArgs e)
{
openFileDialog1 = new OpenFileDialog();
// Set filter options and filter index.
openFileDialog1.Filter =
"Excel Files (*saic optimization*.xlsx, *saic optimization*.xls, *saic optimization*.csv)|" +
"*saic optimization*.xlsx;*saic optimization*.xls;*saic optimization*.csv|" +
"All Files (*.*)|*.*";
openFileDialog1.FilterIndex = 1;
Boolean shouldContinue = false;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
Excel.Application excelObj = new Excel.Application();
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
Excel.Range assRange = null;
object misValue = System.Reflection.Missing.Value;
try
{
if (openFileDialog1.OpenFile() != null)
{
this.Cursor = Cursors.WaitCursor;
label7.Text = Path.GetFileName(openFileDialog1.FileName);
workbook = excelObj.Workbooks.Open(openFileDialog1.FileName);
int assOrdersCount = 0;
worksheet = (Excel.Worksheet)workbook.Sheets.Item[1];
assRange = worksheet.UsedRange;
assRowCount = assRange.Rows.Count;
assColCount = assRange.Columns.Count;
if (assColCount != EXPECTED_ASS_COL_COUNT)
{
throw new Exception(label7.Text + " does not contain " + EXPECTED_ASS_COL_COUNT + " columns and can't be imported");
}
label5.Text = (assRowCount - 1).ToString();
runDate = getRunDate();
// Sort based upon the order number and then the leg number
assRange.Sort(assRange.Columns[1, Type.Missing], // the first sort key - Order Number
Excel.XlSortOrder.xlAscending,
assRange.Columns[15, Type.Missing], // second sort key - Leg Number
Type.Missing, Excel.XlSortOrder.xlAscending,
Type.Missing, Excel.XlSortOrder.xlAscending, // this would be the third key
Excel.XlYesNoGuess.xlYes, // ignore the header
Type.Missing,
Type.Missing,
Excel.XlSortOrientation.xlSortColumns,
Excel.XlSortMethod.xlPinYin,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal);
List <VescoOrder> tempVescoOrders;
List <TriniumOrder> assOrders = new List <TriniumOrder>();
TriniumOrder assOrd;
Double dblPickupDate;
DateTime dtPickupDate;
Double dblDeliveryDate;
DateTime dtDeliveryDate;
for (int i = 2; i <= assRowCount; i++) // Start at the second row and skip the header
{
assOrd = new TriniumOrder();
assOrd.OrderNumber = assRange[i, 1].Value.ToString();
assOrd.DispatchCategoryCode = assRange[i, 2].Value.ToString();
assOrd.Sts = assRange[i, 3].Value.ToString();
assOrd.PickupName = assRange[i, 4].Value.ToString();
assOrd.PickupAddress = assRange[i, 5].Value.ToString();
assOrd.PickupCity = assRange[i, 6].Value.ToString();
assOrd.PickupState = assRange[i, 7].Value.ToString();
assOrd.PickupZip = (assRange[i, 8].Value != null ? assRange[i, 8].Value.ToString() : "");
assOrd.DeliverName = assRange[i, 9].Value.ToString();
assOrd.DeliverAddress = assRange[i, 10].Value.ToString();
assOrd.DeliverCity = assRange[i, 11].Value.ToString();
assOrd.DeliverState = assRange[i, 12].Value.ToString();
assOrd.DeliverZip = (assRange[i, 13].Value != null ? assRange[i, 13].Value.ToString() : "");
assOrd.LegType = assRange[i, 14].Value.ToString();
assOrd.LegNumber = assRange[i, 15].Value.ToString();
dblPickupDate = Convert.ToDouble(assRange[i, 16].Value2);
if (dblPickupDate != 0.0)
{
dtPickupDate = DateTime.FromOADate(dblPickupDate);
if (dtPickupDate.CompareTo(runDate) == 0 || dtPickupDate.CompareTo(runDate.AddDays(1)) == 0)
{
assOrd.ScheduledPickupDate = dblPickupDate;
assOrd.ScheduledPickupTimeFrom = Convert.ToDouble(assRange[i, 17].Value2);
assOrd.ScheduledPickupTimeTo = Convert.ToDouble(assRange[i, 18].Value2);
}
}
dblDeliveryDate = Convert.ToDouble(assRange[i, 19].Value2);
if (dblDeliveryDate != 0.0)
{
dtDeliveryDate = DateTime.FromOADate(dblDeliveryDate);
if (dtDeliveryDate.CompareTo(runDate) == 0 || dtDeliveryDate.CompareTo(runDate.AddDays(1)) == 0)
{
assOrd.ScheduledDeliverDate = dblDeliveryDate;
assOrd.ScheduledDeliverTimeFrom = Convert.ToDouble(assRange[i, 20].Value2);
assOrd.ScheduledDeliverTimeTo = Convert.ToDouble(assRange[i, 21].Value2);
}
}
assOrd.Ssl = (assRange[i, 22].Value != null ? assRange[i, 22].Value.ToString() : "");
assOrd.Size = assRange[i, 23].Value.ToString();
assOrd.Type = assRange[i, 24].Value.ToString();
assOrd.Ll = string.Equals(assRange[i, 25].Value, "yes", StringComparison.OrdinalIgnoreCase);
assOrd.Haz = (assRange[i, 26].Value == "ü" || string.Equals(assRange[i, 26].Value, "yes", StringComparison.OrdinalIgnoreCase));
assOrd.SupplierCode = assRange[i, 27].Value.ToString();
assOrd.DispatchSequence = assRange[i, 28].Value.ToString();
assOrd.Overweight = string.Equals(assRange[i, 29].Value, "yes", StringComparison.OrdinalIgnoreCase);
assOrd.ContainerNumber = (assRange[i, 30].Value != null ? assRange[i, 30].Value.ToString() : "");
assOrders.Add(assOrd);
// A new order number is coming up or this is the last record in the collection, add this job to the jobs list
if (i == assRowCount || assRange[i, 1].Value2.ToString() != assRange[i + 1, 1].Value2.ToString())
{
assOrdersCount++;
//Convert the Associated Order to a Vesco Order
tempVescoOrders = AssociatedConverter.Convert(assOrders);
if (tempVescoOrders.Count() == 0)
{
badOrders.Add(assOrd.OrderNumber);
}
//Add the converted orders
foreach (VescoOrder vo in tempVescoOrders)
{
vescoOrders.Add(vo);
}
triniumOrderCollection.Add(assOrders);
assOrders.Clear();
}
}
workbook.Close(false, misValue, misValue);
shouldContinue = true;
}
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read " + label7.Text + ". Original error: " + ex.Message);
Console.Out.WriteLine(ex.StackTrace);
}
finally
{
if (assRange != null)
{
Marshal.ReleaseComObject(assRange);
}
if (worksheet != null)
{
Marshal.ReleaseComObject(worksheet);
}
if (workbook != null)
{
Marshal.ReleaseComObject(workbook);
}
if (excelObj != null)
{
excelObj.Quit();
Marshal.ReleaseComObject(excelObj);
}
}
}
if (shouldContinue)
{
generateVescoSpreadSheet();
}
}
private void loadCosFile(Excel.Workbook workbook)
{
Excel.Worksheet worksheet = null;
Excel.Range cosRange = null;
int cosOrdersCount = 0;
try
{
worksheet = (Excel.Worksheet)workbook.Sheets.Item[1];
cosRange = worksheet.UsedRange;
cosRowCount = cosRange.Rows.Count;
cosColCount = cosRange.Columns.Count;
if (cosColCount != EXPECTED_COS_COL_COUNT)
{
throw new Exception("File does not contain " + EXPECTED_COS_COL_COUNT + " columns and can't be imported");
}
label3.Text = (cosRowCount - 1).ToString();
// runDate = getRunDate();
// Sort based upon the order number and then the leg number
cosRange.Sort(cosRange.Columns[1, Type.Missing], // the first sort key - Order Number
Excel.XlSortOrder.xlAscending,
cosRange.Columns[15, Type.Missing], // second sort key - Leg Number
Type.Missing, Excel.XlSortOrder.xlAscending,
Type.Missing, Excel.XlSortOrder.xlAscending, // this would be the third key
Excel.XlYesNoGuess.xlYes, // ignore the header
Type.Missing,
Type.Missing,
Excel.XlSortOrientation.xlSortColumns,
Excel.XlSortMethod.xlPinYin,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal);
List <VescoOrder> tempVescoOrders;
List <TriniumOrder> cosOrders = new List <TriniumOrder>();
TriniumOrder cosOrd;
Double dblPickupDate;
DateTime dtPickupDate;
Double dblDeliveryDate;
DateTime dtDeliveryDate;
for (int i = 2; i <= cosRowCount; i++) // Start at the second row and skip the header
{
cosOrd = new TriniumOrder();
cosOrd.OrderNumber = cosRange[i, 1].Value.ToString();
cosOrd.DispatchCategoryCode = cosOrd.OrderNumber.Substring(0, 3);
cosOrd.Ssl = cosRange[i, 22].Value.ToString();
cosOrd.LegType = cosRange[i, 14].Value.ToString();
cosOrd.Haz = string.Equals(cosRange[i, 26].Value, "yes", StringComparison.OrdinalIgnoreCase);
cosOrd.LegNumber = cosRange[i, 15].Value.ToString();
cosOrd.PickupName = cosRange[i, 4].Value.ToString();
cosOrd.PickupAddress = cosRange[i, 5].Value.ToString();
cosOrd.PickupCity = cosRange[i, 6].Value.ToString();
cosOrd.PickupState = cosRange[i, 7].Value.ToString();
cosOrd.PickupZip = cosRange[i, 8].Value.ToString();
cosOrd.DeliverName = cosRange[i, 9].Value.ToString();
cosOrd.DeliverAddress = cosRange[i, 10].Value.ToString();
cosOrd.DeliverCity = cosRange[i, 11].Value.ToString();
cosOrd.DeliverState = cosRange[i, 12].Value.ToString();
cosOrd.DeliverZip = cosRange[i, 13].Value.ToString();
cosOrd.Ll = string.Equals(cosRange[i, 25].Value, "yes", StringComparison.OrdinalIgnoreCase);
dblPickupDate = Convert.ToDouble(cosRange[i, 16].Value2);
if (dblPickupDate != 0.0)
{
dtPickupDate = DateTime.FromOADate(dblPickupDate);
if (dtPickupDate.CompareTo(runDate) == 0 || dtPickupDate.CompareTo(runDate.AddDays(1)) == 0)
{
cosOrd.ScheduledPickupDate = dblPickupDate;
cosOrd.ScheduledPickupTimeFrom = Convert.ToDouble(cosRange[i, 17].Value2);
cosOrd.ScheduledPickupTimeTo = Convert.ToDouble(cosRange[i, 18].Value2);
}
}
dblDeliveryDate = Convert.ToDouble(cosRange[i, 19].Value2);
if (dblDeliveryDate != 0.0)
{
dtDeliveryDate = DateTime.FromOADate(dblDeliveryDate);
if (dtDeliveryDate.CompareTo(runDate) == 0 || dtDeliveryDate.CompareTo(runDate.AddDays(1)) == 0)
{
cosOrd.ScheduledDeliverDate = dblDeliveryDate;
cosOrd.ScheduledDeliverTimeFrom = Convert.ToDouble(cosRange[i, 20].Value2);
cosOrd.ScheduledDeliverTimeTo = Convert.ToDouble(cosRange[i, 21].Value2);
}
}
cosOrd.DispatchSequence = cosRange[i, 28].Value.ToString();
cosOrd.Overweight = string.Equals(cosRange[i, 29].Value, "yes", StringComparison.OrdinalIgnoreCase);
cosOrd.ContainerNumber = (cosRange[i, 30].Value != null ? cosRange[i, 30].Value.ToString() : false);
cosOrders.Add(cosOrd);
// A new order number is coming up or this is the last record in the collection, add this job to the jobs list
if (i == cosRowCount || cosRange[i, 1].Value2.ToString() != cosRange[i + 1, 1].Value2.ToString())
{
cosOrdersCount++;
//Convert the Southwest to a Vesco Order
tempVescoOrders = SouthwestConverter.Convert(cosOrders);
if (tempVescoOrders.Count() == 0)
{
badOrders.Add(cosOrd.OrderNumber);
}
//Add the converted orders
foreach (VescoOrder vo in tempVescoOrders)
{
vescoOrders.Add(vo);
}
cosOrders.Clear();
}
}
workbook.Close(false, misValue, misValue);
}
catch (Exception ex)
{
Console.Out.WriteLine(ex.StackTrace);
MessageBox.Show(
"Error has occured with the one of the fields in the COS file." +
Environment.NewLine + ex.StackTrace,
"Invalid Southwest COS file, " + label1.Text,
MessageBoxButtons.OK,
MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1
);
}
finally
{
if (cosRange != null)
{
Marshal.ReleaseComObject(cosRange);
}
if (worksheet != null)
{
Marshal.ReleaseComObject(worksheet);
}
}
Console.Out.WriteLine(Environment.NewLine);
Console.Out.WriteLine("Total Number Cos Rows Imported => " + (cosRowCount - 1).ToString());
Console.Out.WriteLine("Total Number of Cos Orders => " + cosOrdersCount.ToString());
Console.Out.WriteLine("Total Number of Good Southwest Orders => " + SouthwestConverter.getGoodVescoOrdersCount());
Console.Out.WriteLine("Total Number of Bad Southwest Orders => " + SouthwestConverter.getBadOrdersCount());
}
