ImportTemplateSettings TemplateSettings = new ImportTemplateSettings(); //application import settings
public UpdateImportDatasetForQC(DataSet ds, ImportTemplateSettings ts)
{
DataForQCDataSet = new DataSet(); //start with clean dataset
DataForQCDataSet = ds.Copy(); //Data for QC selection from the main data dataset
TemplateSettings = ts; //Template settings
//Get the list of pallets to QC
string QCconnString = Properties.Settings.Default.ArchiveConnectionString;
StringBuilder QCCommand_Text = new StringBuilder();
if (Properties.Settings.Default.Mode.ToString() == "Test")
{
QCquery = "select [Pallet_Number] FROM [QC_Archive_Test]"; // List of pallets for QC from test table;
}
else
{
QCquery = "select [Pallet_Number] FROM [QC_Archive]"; // List of pallets for QC;
}
System.Data.SqlClient.SqlConnection QCconn = new System.Data.SqlClient.SqlConnection(QCconnString);
System.Data.SqlClient.SqlCommand QCcmd = new System.Data.SqlClient.SqlCommand(QCquery, QCconn);
QCconn.Open();
DataSet QCDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter QCda = new System.Data.SqlClient.SqlDataAdapter(QCcmd);
// this will query the database and return the result to your datatable
QCda.Fill(QCDataSet);
QCconn.Close();
QCda.Dispose();
QCTable = QCDataSet.Tables[0]; //set the translation table
QCDataSet.Dispose();
}
bool Completed = false; //did the translation complete?
public AdamsTranslator(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl)
{
Data2Translate = ds.Copy(); //dataset containing data to translate
TemplateSettings = ImportSettings; //import seetings showing where data is in dataset
List2Translate = tl; //List of fields which need translation
Completed = false; //Did tranlation complete? Not yet.
}
public QCPalletSelection(DataSet ds, ImportTemplateSettings ims, List <string> asl, int rn)
{
DataForQCDataSet = new DataSet(); //start with clean dataset
DataForQCDataSet = ds.Copy(); //Data for QC selection from the main data dataset
ImportSettings = ims; //import settings show where in the dataset each field is located
QCStringList = asl; //string data from main app controls
ReceiptNumber = rn; //receipt number for export data
}
public Archive_Data(DataSet ds, ImportTemplateSettings ims, List <string> asl, int rn)
{
DataToArchiveDataSet = new DataSet(); //start with clean dataset
DataToArchiveDataSet = ds.Copy(); //Data to archive from the main data dataset
ImportSettings = ims; //import settings show where in the dataset each field is located
archiveStringList = asl; //string data from main app controls
ReceiptNumber = rn; //receipt number for export data
}
public DataValidation(DataSet ds, ImportTemplateSettings IS, List <String> lv)
//this is the only constructor so it is important to us it correctly.
{
Data2Validate = ds; //Data from the imported dataset
TemplateSettings = IS; //Settings from the template
List2Validate = lv; //List of fields to validate
InvalidItemList.Clear(); //List of invalid data
Cursor.Current = Cursors.WaitCursor; //set the wait curser
//Update Tag Number List
try
{
string connString = Properties.Settings.Default.ConnectionString;
SqlCommand Command = new SqlCommand();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
conn.Open();
Command.Connection = conn;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "RebuildTagNumberTable"; // Rebuild local list of famous tag numbers
Command.ExecuteNonQuery();
conn.Close();
}
catch (Exception exp)
{
MessageBox.Show("There was an error while trying to get Famous Tag Numbers. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to get Famous Tag Numbers. \n" + exp);
Cursor.Current = Cursors.Default;
return;
}
Passed = false; //did not pass until it passes
Cursor.Current = Cursors.Default;
}
bool Completed; //did the translation complete?
public DataTranslator(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gb)
{
Data2Translate = ds;
TemplateSettings = ImportSettings;
List2Translate = tl;
GrowerBlock = gb;
Completed = false; //Did tranlation complete? Not yet.
try
{
//get Commodity data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
DataSet commodityDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(commodityDataSet);
conn.Close();
da.Dispose();
CommodityTable = commodityDataSet.Tables[0]; //set the translation table
commodityDataSet.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Commodity data for translation. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for translation. \n" + e);
ds.Dispose();
return;
}
}
public CombineMixedBoxesOnPallets(DataSet ds, ImportTemplateSettings ts, string name)
{
WorkingDataSet = ds; //dataset frpm import spreadsheet
TemplateSettings = ts; //Import template settings
FormName = name;
try
{
//get Commodity data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
DataSet commodityDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(commodityDataSet);
conn.Close();
da.Dispose();
CommodityTable = commodityDataSet.Tables[0]; //set the translation table
commodityDataSet.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Commodity data for the combining. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data. \n" + e);
ds.Dispose();
return;
}
}
public FamousXMLExporter(DataSet ds, ImportTemplateSettings ImS, List <String> dl)
{
Data2Export = ds.Copy();
exportStringList = dl;
ImportSettings = ImS;
try
{
//get Commodity data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Commodity] FROM [StoneFuitCommodities]"; // List of stone fruits and berries;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
DataSet StoneFruitDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(StoneFruitDataSet);
conn.Close();
da.Dispose();
StoneFruitTable = StoneFruitDataSet.Tables[0]; //set the translation table
StoneFruitDataSet.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Commodity data for XML export. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for XML export. \n" + e);
ds.Dispose();
return;
}
}
public AdamsExporter(DataSet ds, ImportTemplateSettings ImS, List <String> dl)
{
Data2Export = ds.Copy(); //copy the incoming data to a working dataset
exportStringList = dl; //string list for control data from the main application
ImportSettings = ImS; //import settting for import file type
}
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 :)
}
}
StringBuilder notTranslatedError = new StringBuilder(); //list to hold the name of fields that did not translate
public DataTranslatorByGrower(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gi, string pf)
{
Data2Translate = ds;
TemplateSettings = ImportSettings;
Prefix = pf;
GrowerID = gi;
// Completed = false; //Did tranlation complete? Not yet.
if (Properties.Settings.Default.Mode == "Test")
{
GCVInfoTable = "GCV_Information_Test2";
TranslationDetailsTable = "Translation_Details_Test2";
}
else
{
GCVInfoTable = "GCV_Information2";
TranslationDetailsTable = "Translation_Details2";
}
/* Location in datarow of data to translate.
* Grower_ID - 0,
* Grower_Name - 1,
* Grower_Commodity_Code - 2,
* Commodity_Code - 3,
* Commodity - 4,
* Grower_Variety_Code - 5,
* Variety_Code - 6,
* Variety - 7,
* Stone_Fruit - 8,
* GCV_Code - 9,
* Grower_Style_Code - 10,
* Famous_Style_Code - 11,
* Grower_Size_Code - 12,
* Famous_Size_Code - 13,
* Grower_Pack_Code - 14,
* Famous_Pack_Code - 15,
* Adams_Pack_Code - 16,
* Grower_Label_Code - 17,
* Famous_Label_Code - 18,
* Adams_Label_Code - 19,
* Grower_Grade_Code - 20,
* Famous_Grade_Code - 21,
* Adams_Grade_Code - 22,
* Grower_Pallet_Type - 23,
* Famous_Pallet_Type - 24,
* Adams_Pallet_Type - 25
*
*/
try
{
//get Translation data from database
string connString = Properties.Settings.Default.ConnectionString;
string query =
"SELECT Grower_ID, Grower_Name, Grower_Commodity_Code, Commodity_Code, Commodity, Grower_Variety_Code, " +
"Variety_Code, Variety, Stone_Fruit, " + GCVInfoTable.ToString() + ".GCV_Code, Grower_Style_Code, " +
"Famous_Style_Code, Grower_Size_Code, Famous_Size_Code, Grower_Pack_Code, " +
"Famous_Pack_Code, Adams_Pack_Code, Grower_Label_Code, Famous_Label_Code, " +
"Adams_Label_Code, Grower_Grade_Code, Famous_Grade_Code, " +
"Adams_Grade_Code, Grower_Pallet_Type, Famous_Pallet_Type, " +
"Adams_Pallet_Type " +
"FROM " + GCVInfoTable.ToString() + " INNER JOIN " + TranslationDetailsTable.ToString() +
" ON " + GCVInfoTable.ToString() + ".GCV_Code = " + TranslationDetailsTable.ToString() + ".GCV_Code " +
"WHERE " + GCVInfoTable.ToString() + ".Grower_ID = " + GrowerID.ToString(); // Form translation table for grower;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn);
conn.Open();
DataSet translationDataSet = new DataSet();
// create data adapter
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
// this will query the database and return the result to the datatable
da.Fill(translationDataSet);
conn.Close();
da.Dispose();
TranslationTable = translationDataSet.Tables[0]; //set the translation table
translationDataSet.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to create and load the translation data. \n" +
" Note what happened and contact administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("There was an error while trying to create and load translation data. \n" + e);
ds.Dispose();
return;
}
}
public CombineMixedBoxesOnPallets(DataSet ds, ImportTemplateSettings ts, string name)
{
WorkingDataSet = ds; //dataset frpm import spreadsheet
TemplateSettings = ts; //Import template settings
FormName = name;
}
