public void processAssDrop(string _fullPath)
{
VescoLog.LogEvent("processAssDrop, _fullPath=" + _fullPath);
Boolean shouldContinue = false;
Excel.Application excelObj = new Excel.Application();
Excel.Workbook workbook = null;
Excel.Worksheet worksheet = null;
Excel.Range assRange = null;
object misValue = System.Reflection.Missing.Value;
System.Threading.Thread.Sleep(5000);
try
{
workbook = excelObj.Workbooks.Open(_fullPath);
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)
{
String errMessage = _fullPath + " does not contain " + EXPECTED_ASS_COL_COUNT +
" columns and can't be imported. The most likely cause is an invlaid comma in the data.";
EmailHelper.sendGmail("Import Error", errMessage, false);
throw new Exception(errMessage);
}
runDate = getRunDate(_fullPath);
// 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)
{
VescoLog.LogEvent(ex.Message);
VescoLog.LogEvent(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(_fullPath);
}
}
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();
}
}