public AdamsExportToExcel(DataSet ds, ImportTemplateSettings ImS, List <String> dl)
{
//set data
Data2Export = ds.Copy();
ImportSettings = ImS;
exportStringList = dl;
//do combining here
CombineMixedBoxesOnPallets CombinedBoxes = new CombineMixedBoxesOnPallets(Data2Export, ImportSettings, "Adams");
CombinedBoxes.CombineMixedPallets();
CombinedBoxes.ViewData();
Data2Export = CombinedBoxes.CombinedData();
SaveFileDialog saveFileDialogSaveExportFile = new SaveFileDialog();
object misValue = System.Reflection.Missing.Value;
string sExporter = exportStringList[11].ToString();
string sVesselNumber = exportStringList[13].ToString();
//Get export file name and path
saveFileDialogSaveExportFile.InitialDirectory = Properties.Settings.Default.AdamsExportFilePath; //get default path from settings
saveFileDialogSaveExportFile.FileName = saveFileDialogSaveExportFile.FileName = TruncateString.Truncate2(sExporter, 4) + "_"
+ sVesselNumber.ToString() + "_Export.xls";
DialogResult result = saveFileDialogSaveExportFile.ShowDialog();
if (saveFileDialogSaveExportFile.FileName != "") //check if file name is blank
{
ExcelFileName = saveFileDialogSaveExportFile.FileName;
}
else //use default path and name if one not selected
{
ExcelFileName = Properties.Settings.Default.AdamsExportFilePath + "\\Export_File.xls";
}
saveFileDialogSaveExportFile.Dispose();
if (result == DialogResult.OK) //only try to open excel file if open dialog <OK> button was clicked.
{
Excel.ApplicationClass excelapp = new Excel.ApplicationClass();
excelapp.Visible = true;
excelapp.DisplayAlerts = false; //don't display any dialog boxes or alerts from the excel app.
Excel._Workbook workbook = (Excel._Workbook)(excelapp.Workbooks.Add(Type.Missing));
Excel._Worksheet worksheet = (Excel._Worksheet)workbook.ActiveSheet;
try
{
//Write top section labels
worksheet.Cells[1, 1] = "Exporter:";
worksheet.Cells[2, 1] = "Port:";
worksheet.Cells[3, 1] = "Vessel Name:";
worksheet.Cells[4, 1] = "Pandol Lot Number:";
worksheet.Cells[1, 8] = "Total Box Count:";
//Set headers of data section
worksheet.Cells[5, 1] = "\n\nPallet \nPrefix";
worksheet.Cells[5, 2] = "\n\nPallet\nNumber";
worksheet.Cells[5, 3] = "\n\n\nSpecies";
worksheet.Cells[5, 4] = "\n\n\nVariety";
worksheet.Cells[5, 5] = "\n\n\nLabel";
worksheet.Cells[5, 6] = "\nPack\nDescription";
worksheet.Cells[5, 7] = "Majority\nGrade\nSize";
worksheet.Cells[5, 8] = "\nMajority\nPack Date";
worksheet.Cells[5, 9] = "\nMajority\nProducer";
worksheet.Cells[5, 10] = "\nBox\nCount";
worksheet.Cells[5, 11] = "\n\n\nHatch";
worksheet.Cells[5, 12] = "\n\n\nDeck";
worksheet.Cells[5, 13] = "\nFumigation\nCode";
worksheet.Cells[5, 14] = "\n\n\nPallet Memo";
}
catch (Exception ex)
{
MessageBox.Show("Error creating Excel workbook for Export. \nContact the adminstrator for help and note what you just did. \n" +
"See error log for more information. \n");
Error_Logging el = new Error_Logging("Error creating Excel workbook for Export. \n" + ex);
}
try
{
//Write exporter and shipping data in top section of spreadsheet
worksheet.Cells[1, 2] = exportStringList[11].ToString(); //Exporter Name
worksheet.Cells[2, 2] = exportStringList[15].ToString(); //Destination Port
worksheet.Cells[3, 2] = exportStringList[14].ToString(); //Vessel Name
worksheet.Cells[4, 2] = exportStringList[13].ToString(); //Pandol Lot Number
}
catch (Exception ex)
{
MessageBox.Show("Error writing top section of Excel workbook for Export. \nContact the adminstrator for help and note what you just did. \n" +
"See error log for more information. \n");
Error_Logging el = new Error_Logging("Error writing top section of Excel workbook for Export. \n" + ex);
}
//write pallet data
try
{
int tempdataRow = 0;
BoxCount = 0;
for (int ExportDataRow = 0; ExportDataRow < Data2Export.Tables[0].Rows.Count; ExportDataRow++)
{
tempdataRow = ExportDataRow + 6; //start on row 6
if (exportStringList[10].ToString().Length < 1) //check to see if prefix is blank
{
worksheet.Cells[tempdataRow, 1] =
Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim().Substring(0, 3);
}
else
{
worksheet.Cells[tempdataRow, 1] = exportStringList[10].ToString(); //Pallet Prefix
}
if (ImportSettings.PalletPrefixColumn == ImportSettings.TagNumberColumn || exportStringList[10].ToString().Length < 1)
{
tempString = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim();
worksheet.Cells[tempdataRow, 2] = tempString.Substring(3, tempString.Length - 3); //Pallet Number
}
else
{
worksheet.Cells[tempdataRow, 2] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString(); //Pallet Number
}
worksheet.Cells[tempdataRow, 3] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.CommodityColumn].ToString(); //Species/Commodity
worksheet.Cells[tempdataRow, 4] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.VarietyColumn].ToString(); //Variety
worksheet.Cells[tempdataRow, 5] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.LabelColumn].ToString(); //Label
//Pack Description
worksheet.Cells[tempdataRow, 6] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.PackCodeColumn].ToString(); //Pack Code
worksheet.Cells[tempdataRow, 7] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GradeColumn].ToString(); //Majority Grade
Dt = DateTime.Parse(DateNumberToDateString.ConvertDateNumberToDateString(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.FirstPackDateColumn].ToString()), mFomatter);
worksheet.Cells[tempdataRow, 8] = Dt.ToString("MM/dd/yyyy"); //Pack Date
worksheet.Cells[tempdataRow, 9] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GrowerNumberColumn].ToString(); //Producer
worksheet.Cells[tempdataRow, 10] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.InventoryQuantityColumn].ToString(); //Box Count
if (Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim() ==
Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim() &
Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim().Split('-').Length == 2 &
Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim().Length < 4)
{
worksheet.Cells[tempdataRow, 11] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim().Split('-')[0]; //Hatch
worksheet.Cells[tempdataRow, 12] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim().Split('-')[1]; //Deck
}
else
{
worksheet.Cells[tempdataRow, 11] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim(); //Hatch
worksheet.Cells[tempdataRow, 12] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim(); //Deck
}
worksheet.Cells[tempdataRow, 13] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.FumigatedColumn].ToString(); //Fumigation
worksheet.Cells[tempdataRow, 14] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.MemoColumn].ToString(); //Memo
BoxCount = BoxCount + Convert.ToInt32(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.InventoryQuantityColumn]); //get running total of boxes
}
worksheet.Cells[1, 10] = BoxCount;
workbook.SaveAs(ExcelFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges, misValue, misValue, misValue, misValue);
excelapp.UserControl = true;
}
catch (Exception ex)
{
MessageBox.Show("Error writing pallet data to Export Excel spreadsheet. \nContact the adminstrator for help and note what you just did. \n" +
"See error log for more information. \n");
Error_Logging el = new Error_Logging("Error writing pallet data to Export Excel spreadsheet. \n" + ex);
}
workbook.Close(true, misValue, misValue);
excelapp.Quit();
releaseObject(worksheet);
releaseObject(workbook);
releaseObject(excelapp);
exported = true; //Happy export :)
}
}
public bool ExportData() //creates the export list from the Data2Export dataset
{
StringBuilder exportLineString = new StringBuilder(); //string builder used to create export text lines.
string palletnumber; //temp string for tag number creation
string tempString; //used for string manipulation
string tempRowID; //used to identify the rows to sort export data into Export sort order
int BoxCount = 0; //boxcount variable used mainly for format manipulation
int TotalBoxCount = 0; //running total of boxes
DateTime Dt; //used for date time manipulation
RecordsToExport.Clear(); //clear list incase this method has been run already.
try
{
//Create boat information and first line to be exported, record ID = 0 item
exportLineString = new StringBuilder(); //Start with as fresh stingbulder
exportLineString.AppendFormat("{0,1}", "0"); //Record ID = 0
exportLineString.AppendFormat("{0,1}", "0"); //File revision number
exportLineString.AppendFormat("{0,-10}", TruncateString.Truncate2(exportStringList[11].ToString(), 10)); //Exporter
Dt = DateTime.Parse(exportStringList[12], mFomatter); //format date to yyMMdd format
exportLineString.AppendFormat("{0,-6}", Dt.ToString("yyMMdd")); //Ship Date in form of YYMMDD
exportLineString.AppendFormat("{0,-6}", TruncateString.Truncate2(exportStringList[13].ToString(), 6)); //Exporter's ship number
exportLineString.AppendFormat("{0,-15}", TruncateString.Truncate2(exportStringList[14].ToString(), 15)); //Ship's name
exportLineString.AppendFormat("{0,-15}", TruncateString.Truncate2(exportStringList[15].ToString(), 15)); //Destination
RecordsToExport.Add(exportLineString.ToString()); //add record to record list to send to file
}
catch (Exception e)
{
exported = false;
MessageBox.Show("Export process for ship information had an error. \nPlease note what was done and see administrator for help. \n");
Error_Logging el = new Error_Logging("Export (Adams) process for ship information had an error. \n" + e);
return(false);
}
try
{
//Record ID = 1 items. Puts them on the RecordsToExportOne list. *******
RecordsToExportIDOne.Clear();
for (int ExportDataRow = 0; ExportDataRow < Data2Export.Tables[0].Rows.Count; ExportDataRow++)
{
exportLineString = new StringBuilder(); //Start with as fresh stingbulder
exportLineString.AppendFormat("{0,1}", "1"); //Record ID = 1
//add prefix number to front of tag/pallet number.
if (ImportSettings.PalletPrefixColumn == ImportSettings.TagNumberColumn)
{
palletnumber = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim();
}
else
{
palletnumber = exportStringList[10].ToString() +
Convert.ToInt32(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim()).ToString("0000000");
}
exportLineString.AppendFormat("{0,-10}", TruncateString.Truncate2(palletnumber, 10)); //add pallet Number
exportLineString.AppendFormat("{0,-4}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.VarietyColumn].ToString().Trim(), 4)); //add Variety ID
exportLineString.AppendFormat("{0,-3}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.LabelColumn].ToString().Trim(), 3)); //add Label ID
exportLineString.AppendFormat("{0,-4}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.PackCodeColumn].ToString().Trim(), 4)); //add Pack Code
if ((TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.VarietyColumn].ToString().Trim(), 1) != "G") &
(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim().Length < 4))
{ //Left justified if
exportLineString.AppendFormat("{0,4}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim(), 4)); //add Grade ID
}
else
{
exportLineString.AppendFormat("{0,-4}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim(), 4)); //add Grade ID
}
Dt = DateTime.Parse(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.FirstPackDateColumn].ToString(), mFomatter);
exportLineString.AppendFormat("{0,-6}", Dt.ToString("yyMMdd")); //add Pack Date
exportLineString.AppendFormat("{0,-4}", Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GrowerNumberColumn].ToString().Trim()); //add Grower ID
BoxCount = Convert.ToInt32(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.InventoryQuantityColumn].ToString().Trim());
exportLineString.AppendFormat("{0,-6}", BoxCount.ToString("000000")); //add Inventory/box count
//RPC box type goes here!!!!!!!!!!!!!!!!!!!!!!! tbd
exportLineString.AppendFormat("{0,-4}", ""); //add RPC Box
RecordsToExportIDOne.Add(exportLineString.ToString()); //add record to record list to send to file
}
}
catch (Exception e)
{
exported = false;
MessageBox.Show("Export process for record ID = 1 items had an error. \nPlease note what was done and see administrator for help. \n");
Error_Logging el = new Error_Logging("Export (Adams) process for record ID = 1 items had an error. \n" + e);
return(false);
}
//do combining here
CombineMixedBoxesOnPallets CombinedBoxes = new CombineMixedBoxesOnPallets(Data2Export, ImportSettings, "Adams");
CombinedBoxes.CombineMixedPallets();
CombinedBoxes.ViewData();
Data2Export = CombinedBoxes.CombinedData();
try
{
//Record ID = 2 items. Puts them on the RecordsToExportTwo list. ***************
RecordsToExportIDTwo.Clear();
TotalBoxCount = 0;
BoxCount = 0;
for (int ExportDataRow = 0; ExportDataRow < Data2Export.Tables[0].Rows.Count; ExportDataRow++)
{
tempString = "";
exportLineString = new StringBuilder(); //Start with as fresh stingbulder
exportLineString.AppendFormat("{0,1}", "2"); //Record ID = 2
//add prefix number to front of tag/pallet number.
if (ImportSettings.PalletPrefixColumn == ImportSettings.TagNumberColumn)
{
palletnumber = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim();
}
else
{
palletnumber = exportStringList[10].ToString() +
Convert.ToInt32(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim()).ToString("0000000");
}
exportLineString.AppendFormat("{0,-10}", palletnumber); //add pallet Number
exportLineString.AppendFormat("{0,-4}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.VarietyColumn].ToString().Trim(), 4)); //add Variety ID
exportLineString.AppendFormat("{0,-3}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.LabelColumn].ToString().Trim(), 3)); //add Label ID
exportLineString.AppendFormat("{0,-4}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.PackCodeColumn].ToString().Trim(), 4)); //add Pack Code
//exportLineString.AppendFormat("{0,-14}", ""); //Blank spaces If spec is followed use this instead of the code below
//******added to match Export spreadsheet results
if ((TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.VarietyColumn].ToString().Trim(), 1) != "G") &
(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim().Length < 4))
{
exportLineString.AppendFormat("{0,4}", Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim()); //add Grade ID
}
else
{
exportLineString.AppendFormat("{0,-4}", Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim()); //add Grade ID
}
Dt = DateTime.Parse(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.FirstPackDateColumn].ToString(), mFomatter);
exportLineString.AppendFormat("{0,-6}", Dt.ToString("yyMMdd")); //add Pack Date
exportLineString.AppendFormat("{0,-4}", Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GrowerNumberColumn].ToString().Trim()); //add Grower ID
//*******
BoxCount = Convert.ToInt32(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.InventoryQuantityColumn].ToString().Trim());
exportLineString.AppendFormat("{0,-6}", BoxCount.ToString("000000")); //Box Count
//keep running total
TotalBoxCount = TotalBoxCount + Convert.ToInt32(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.InventoryQuantityColumn]);
exportLineString.AppendFormat("{0,1}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim(), 1)); //add Hatch
exportLineString.AppendFormat("{0,1}", "-"); //add -
exportLineString.AppendFormat("{0,1}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim(), 1)); //add Deck
exportLineString.AppendFormat("{0,-9}", ""); //add blanks
exportLineString.AppendFormat("{0,1}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.FumigatedColumn].ToString().Trim(), 1)); //Fumigation
exportLineString.AppendFormat("{0,-4}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow]
[ImportSettings.BillOfLadingColumn].ToString(), 4).Trim()); //add B/L Number
if (Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.PalletTypeColumn].ToString().Trim() == "Chep")
{
tempString = "C";
}
else
{
tempString = "";
}
exportLineString.AppendFormat("{0,1}", tempString); //add pallet type, "C" for chep and blank otherwise
exportLineString.AppendFormat("{0,-10}", TruncateString.Truncate2(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.MemoColumn].ToString().Trim(), 10));
RecordsToExportIDTwo.Add(exportLineString.ToString()); //add record to record list to send to file
}
}
catch (Exception e)
{
exported = false;
MessageBox.Show("Export process for record ID = 2 items had an error. \nPlease note what was done and see administrator for help. \n");
Error_Logging el = new Error_Logging("Export (Adams) process for record ID = 2 items had an error. \n" + e);
return(false);
}
// **********
try
{
//Merge RecordsToExportIDOne and RecordsToExportIDTwo in proper order for export file
tempString = ""; //initialize tempString to blank/empty
tempRowID = ""; //set tempRowID to empty string as there have been no rows checked yet
int lastsameItem = 0;
int lastsameItem2 = 0;
for (int ItemOne = 0; ItemOne < RecordsToExportIDOne.Count; ItemOne++)
{
if (string.IsNullOrEmpty(tempRowID) & !(string.IsNullOrEmpty(RecordsToExportIDOne[ItemOne]))) //add first record from RecordsToExportOne to
//records to export as this would be the next record and is a special case
{
RecordsToExport.Add(RecordsToExportIDOne[0]);
//record which should be in the list
tempRowID = RecordsToExportIDOne[ItemOne].Substring(1, 14);
}
for (int nextItem = lastsameItem; nextItem < RecordsToExportIDOne.Count; nextItem++) //add item 1 records that have the same row ID
{
if ((RecordsToExportIDOne[nextItem].Substring(1, 14) == tempRowID) & nextItem != 0)
{
RecordsToExport.Add(RecordsToExportIDOne[nextItem]);
lastsameItem = nextItem;
tempRowID = RecordsToExportIDOne[lastsameItem].Substring(1, 14);
ItemOne = nextItem; //start at the next item in the list as others have now been checked
}
}
for (int nextItem2 = lastsameItem2; nextItem2 < RecordsToExportIDTwo.Count; nextItem2++) //next add the item 2 record
{
if ((RecordsToExportIDTwo[nextItem2].Substring(1, 14) == tempRowID))
{
RecordsToExport.Add(RecordsToExportIDTwo[nextItem2]);
lastsameItem2 = nextItem2; //fount the item on list and set it as the last found item
}
}
if (lastsameItem + 1 < RecordsToExportIDOne.Count) //set the tempRowID to the next value unless it is the last item
{
tempRowID = RecordsToExportIDOne[lastsameItem + 1].Substring(1, 14);
}
}
}
catch (Exception e)
{
exported = false;
MessageBox.Show(" Error in ordering Export records for export. \nPlease note what was done and see administrator for help. \n");
Error_Logging el = new Error_Logging(" Error in ordering Export (Adams) records for export. \n" + e);
return(false);
}
try
{
//Record ID 3 item Overall summary record
exportLineString = new StringBuilder();
exportLineString.AppendFormat("{0,1}", "3"); //add Record ID
exportLineString.AppendFormat("{0,-35}", ""); //add blanks
exportLineString.AppendFormat("{0,6}", TotalBoxCount.ToString("000000")); //add grand total count for boxes
RecordsToExport.Add(exportLineString.ToString()); //add record to record list to send to file
exported = true;
}
catch (Exception e)
{
exported = false;
MessageBox.Show("Export process for record ID = 3 item had an error. \nPlease note what was done and see administrator for help. \n");
Error_Logging el = new Error_Logging("Export (Adams) process for record ID = 3 item had an error. \n" + e);
return(false);
}
return(exported); //let progrom know it completed
}
public bool ExportData() //creates the export list from the Data2Export dataset
{
StringBuilder exportLineString = new StringBuilder(); //string builder used to create export text lines.
string tagnumber; //temp string for tag number creation
string LineNumberStringCheck = ""; //Used to hold the concatenated string to check for changes to increment the line number
string TempLineNumberStringCheck = ""; //Temp string variable for the above
int LineNumber = 0;
string Tempgrade = "";
RecordsToExport.Clear(); //clear list in case this method has been run already.
DataRow[] CommodityValidateRows = CommodityTable.Select("Data_Column_Name = 'Commodity'"); //get all rows for Commodity from validation table;
//do combining of mixed pallets here and send name of export to use for title of window
//Instantiate the box combining object for mixed pallets.
CombineMixedBoxesOnPallets CombinedBoxes = new CombineMixedBoxesOnPallets(Data2Export, ImportSettings, "Famous");
CombinedBoxes.CombineMixedPallets();
CombinedBoxes.ViewData();
Data2Export = CombinedBoxes.CombinedData();
//Sort data before exporting *********************************************************
DataViewManager dvm = new DataViewManager();
dvm.DataSet = Data2Export.Copy();
//Name columns so they are always named the same for the sorting
dvm.DataSet.Tables[0].Columns[ImportSettings.GradeColumn].ColumnName = "Grade Column";
dvm.DataSet.Tables[0].Columns[1].ColumnName = "Grower Block Column";
dvm.DataSet.Tables[0].Columns[ImportSettings.CommodityColumn].ColumnName = "Commodity Column";
dvm.DataSet.Tables[0].Columns[ImportSettings.StyleColumn].ColumnName = "Style Column";
dvm.DataSet.Tables[0].Columns[ImportSettings.SizeColumn].ColumnName = "PackSize Column";
dvm.DataSet.Tables[0].Columns[ImportSettings.LabelColumn].ColumnName = "Label Column";
//sort
dvm.DataViewSettings[0].Sort = "[[Grower Block Column], [Label Column], [Style Column], [PackSize Column], [Grade Column]"; //"[Label], [PackSize], [Style], [Grade], [Grower Block], [Commodity]";
dvm.DataSet.AcceptChanges();
//Fill the Datarow array with the sorted data
DataRow[] rows = dvm.DataSet.Tables[0].Select(string.Empty, "[Grower Block Column], [Grade Column]");
//*************************************************************************************
//Code to check for exporter and vessel number in archive
string connString = Properties.Settings.Default.ArchiveConnectionString; //get connection string from the application settings
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(Properties.Settings.Default.ConnectionString); // Instantiate connection object
SqlDataAdapter ArchivedReceiptsDataAdaptor = null; //DataAdaptor for the Exporter-Vessel_Number, Receipt number table
SqlCommandBuilder cmdBuilder; //using sql command builder to create update command
SqlCommand QueryCommand = null; //query string
//get receipt numbers data from archive database
DataSet dt = new DataSet();
if (Properties.Settings.Default.Mode.ToString() == "Test") //check for test mode and use test archive if test mode
{
QueryCommand = new SqlCommand(" Select DISTINCT [Exporter] + '-' + [Vessel_Number] AS EXNUM, [Receipt_Number] " +
"FROM [ImportDataWarehouse].[dbo].[FAPI_Import_Data_Archive_Test]" +
"ORDER BY EXNUM", conn);
}
else
{
QueryCommand = new SqlCommand(" Select DISTINCT [Exporter] + '-' + [Vessel_Number] AS EXNUM, [Receipt_Number] " +
"FROM [ImportDataWarehouse].[dbo].[FAPI_Import_Data_Archive]" +
"ORDER BY EXNUM", conn);
}
ArchivedReceiptsDataAdaptor = new SqlDataAdapter(QueryCommand);
cmdBuilder = new SqlCommandBuilder(ArchivedReceiptsDataAdaptor);
conn.Open();
ArchivedReceiptsDataAdaptor.Fill(dt);
conn.Close();
//get data for exporter-vessel from archive. if it exists popup get receipt number window to get receipt number
string ExporterVessel = "'" + exportStringList[11].ToString() + "-" + exportStringList[13].ToString() + "'";
DataRow[] ReceiptRows = dt.Tables[0].Select("EXNUM = " + ExporterVessel);
if (ReceiptRows.Length == 0) //No matches were found for the exporter and vessel number
{
IncrementReceiptNumber rn = new IncrementReceiptNumber();
ReceiptNumber = rn.GetNewReceiptNumber(); //get incremented receipt number
ReUseReceiptNumber = 0;
}
else //Matches found
{
//Open Select Receipt form
Select_Receipt_Number Select_Receipt_Form = new Select_Receipt_Number(ReceiptRows);
DialogResult result = Select_Receipt_Form.ShowDialog();
if (result == DialogResult.OK) //used clicked OK
{
ReceiptNumber = Select_Receipt_Form.GetReceiptNumber();
ReUseReceiptNumber = 1; //Reused the receipt number
}
else //Cancel or window closed
{
IncrementReceiptNumber rn = new IncrementReceiptNumber();
ReceiptNumber = rn.GetNewReceiptNumber();
ReUseReceiptNumber = 0;
}
Select_Receipt_Form.Dispose();
}
//get last line number used for this receipt number *****************************************
LineNumber = CreateLineNumber.LastLineNumber(ReceiptNumber); //set the start point to the last line number for this receipt number
//the number will be incremented by 1 before using.
try //Begin creation of the export string list for export
{
for (int ExportDataRow = 0; ExportDataRow < rows.Length; ExportDataRow++)
{
//used to check to see if any of the product defining variables have changed.
TempLineNumberStringCheck = rows[ExportDataRow][1].ToString().Trim() //Exporter
+ rows[ExportDataRow][ImportSettings.CommodityColumn].ToString().Trim() //Commodity
+ rows[ExportDataRow][ImportSettings.VarietyColumn].ToString().Trim() //Variety
+ rows[ExportDataRow][ImportSettings.StyleColumn].ToString().Trim() //Style
+ rows[ExportDataRow][ImportSettings.SizeColumn].ToString().Trim() //Size
+ rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim() //Grade
+ rows[ExportDataRow][ImportSettings.LabelColumn].ToString().Trim(); //Label
exportLineString = new StringBuilder(); //Start with as fresh stingbuilder object to create the export text line
exportLineString.AppendFormat("{0,-9}", ReceiptNumber); //blank for Receipt/Entry number Increment this number!!!!
exportLineString.AppendFormat("{0,1}", exportStringList[0]); //add Transaction type
exportLineString.AppendFormat("{0,-10}", exportStringList[1].Split(' ')[0]); //add Receive/Packdate, splitting off date
exportLineString.AppendFormat("{0,1}", exportStringList[2]); //add Bulk Flag
exportLineString.AppendFormat("{0,-40}", exportStringList[3]); //add Warehouse
exportLineString.AppendFormat("{0,-12}", TruncateString.Truncate2(rows[ExportDataRow][ImportSettings.MemoColumn].ToString().Trim(), 12)); //add Memo values
exportLineString.AppendFormat("{0,-40}", ""); //add blanks for Description field
exportLineString.AppendFormat("{0,-40}", ""); //add blanks for Access Group field
//If product definition changes, increment the line number
if (LineNumberStringCheck != TempLineNumberStringCheck)
{
LineNumber++;
}
LineNumberStringCheck = TempLineNumberStringCheck; //set old line identifier text to new one
exportLineString.AppendFormat("{0,5}", LineNumber.ToString()); //line number field
//add prefix number to front of tag number.
if ((ImportSettings.PalletPrefixColumn == ImportSettings.TagNumberColumn) || (exportStringList[10].ToString().Length < 1)) //special case for which has prefix as part of tag number already
{ //and the import setting point to same column for both
tagnumber = rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim();
}
else
{
tagnumber = rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim(); //set tag number to the data tag number
if (tagnumber.Length > 3) //if is less than 4, it can not contain a prefix and a tag number in one
{
if (!(exportStringList[10].ToString() == (rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim().Substring(0, 3)))) //Prefix not in Tag Number
{
tagnumber = exportStringList[10].ToString() + rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim(); //append prefix code
}
}
}
exportLineString.AppendFormat("{0,-12}", tagnumber); //add Tag Number
exportLineString.AppendFormat("{0,-12}", rows[ExportDataRow][1].ToString().Trim()); //add Grower Block ID, is always in column 1
exportLineString.AppendFormat("{0,-12}", ""); //add blank Pool ID field
exportLineString.AppendFormat("{0,1}", ""); //add blank for receipt# as lot id field
exportLineString.AppendFormat("{0,-12}", exportStringList[13].ToString().Trim()); //add Lot ID/Vessel number field
exportLineString.AppendFormat("{0,-40}", ""); //add blanks for Lot Description field
exportLineString.AppendFormat("{0,-12}", ""); //add blanks for Product ID field
exportLineString.AppendFormat("{0,-10}", rows[ExportDataRow][ImportSettings.CommodityColumn].ToString().Trim()); //add commondity ID
exportLineString.AppendFormat("{0,-16}", rows[ExportDataRow][ImportSettings.VarietyColumn].ToString().Trim()); //add Variety ID
exportLineString.AppendFormat("{0,-10}", rows[ExportDataRow][ImportSettings.StyleColumn].ToString().Trim()); //add Style ID
exportLineString.AppendFormat("{0,-10}", rows[ExportDataRow][ImportSettings.SizeColumn].ToString().Trim()); //add Size ID
//Clear stone fruit Grade
Tempgrade = rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim(); //add Grade ID
foreach (DataRow Commodityrow in CommodityValidateRows)
{
if (rows[ExportDataRow][ImportSettings.CommodityColumn].ToString() == Commodityrow[2].ToString() &&
Commodityrow[3].ToString() == "Stone Fruit")
{
Tempgrade = ""; //use blank for grade
}
}
exportLineString.AppendFormat("{0,-10}", Tempgrade); //set grade
exportLineString.AppendFormat("{0,-16}", rows[ExportDataRow][ImportSettings.LabelColumn].ToString().Trim()); //add Label ID
exportLineString.AppendFormat("{0,-16}", exportStringList[6]); //add Region ID
exportLineString.AppendFormat("{0,-16}", exportStringList[7]); //add Method ID
exportLineString.AppendFormat("{0,-16}", exportStringList[8]); //add Storage ID
exportLineString.AppendFormat("{0,-16}", rows[ExportDataRow][ImportSettings.PalletTypeColumn].ToString().Trim()); //add Color ID/Pallet Type
exportLineString.AppendFormat("{0,-16}", ""); //add blanks for Quality
exportLineString.AppendFormat("{0,-16}", ""); //add blanks for Condition
exportLineString.AppendFormat("{0,1}", exportStringList[9]); //add Availabel Flag
exportLineString.AppendFormat("{0,8}", rows[ExportDataRow][ImportSettings.InventoryQuantityColumn].ToString().Trim()); //add Inventory/box count
exportLineString.AppendFormat("{0,-3}", "000"); //add 3 zeros for inventory quantity which has 3 implied decimal places
exportLineString.AppendFormat("{0,-11}", ""); //add blanks for Quantity field
exportLineString.AppendFormat("{0,-5}", ""); //add blanks for Unit of Measure field
exportLineString.AppendFormat("{0,-4}", ""); //add blanks for Units per Pallet field
exportLineString.AppendFormat("{0,-12}", ""); //add blanks for Room/Row ID field
exportLineString.AppendFormat("{0,-10}", DateTime.Parse(DateNumberToDateString.ConvertDateNumberToDateString(rows[ExportDataRow]
[ImportSettings.FirstPackDateColumn].ToString().Trim()), mFomatter).ToString("MM/dd/yyyy")); //add First Pack Date
exportLineString.AppendFormat("{0,-14}", ""); //add blanks for GTIN field
exportLineString.AppendFormat("{0,-20}", ""); //add blanks for GS1Lot field
exportLineString.AppendFormat("{0,-10}", ""); //add blanks for GS1 Date field
RecordsToExport.Add(exportLineString.ToString()); //add record to record list to send to file
}
CreateLineNumber.UpdateLineNumber(ReceiptNumber, LineNumber);
exported = true; //happy export :)
}
catch (Exception e)
{
exported = false;
MessageBox.Show("Famous Export process failed. \nPlease note what was done and see admin for help. \n");
Error_Logging el = new Error_Logging("Famous Export process had an error. \n" + e);
}
return(exported); //let progrom know it completed
}