public DataSet UpdateDataSet()
{
try
{
int CurrentRow = 0;
DataSet tempWorkingDataSet = new DataSet(); //
tempWorkingDataSet = DataForQCDataSet.Copy();
for (int QCCurrentRow = 0; QCCurrentRow < QCTable.Rows.Count; QCCurrentRow++) //iterate through the QC pallet list
{
for (CurrentRow = 0; CurrentRow < tempWorkingDataSet.Tables[0].Rows.Count; CurrentRow++)
{
if (tempWorkingDataSet.Tables[0].Rows[CurrentRow][TemplateSettings.TagNumberColumn].ToString().Trim() == QCTable.Rows[QCCurrentRow][0].ToString().Trim())
{
tempWorkingDataSet.Tables[0].Rows[CurrentRow][TemplateSettings.MemoColumn] = "QC";
}
}
}
DataForQCDataSet = tempWorkingDataSet;
return(DataForQCDataSet);
}
catch (Exception e)
{
MessageBox.Show("QC process failed. It will continue without selecting pallets for QC \nPlease note what was done and see admin for help. \n");
Error_Logging el = new Error_Logging("QC process had an error. \n" + e);
return(DataForQCDataSet);
}
}
private void buttonSave_Click(object sender, EventArgs e)
{
try
{
conn.Open();
TranslationValidationDataAdaptor.Update(TranslationValidationDataSet);
MessageBox.Show("Changes to Translation-Validation Table made.");
}
catch (Exception ex)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
else if (conn.State == ConnectionState.Broken)
{
MessageBox.Show("Connection was broken, can not save changes. \n" +
"Contact administrator to report connection problem.");
Error_Logging el = new Error_Logging("Connection was broken, can not save changes. " + "\n" + ex);
}
else
{
TranslationValidationDataAdaptor.Dispose();
MessageBox.Show("Error while updating database with update command: \n" + cmdBuilder.GetUpdateCommand().CommandText + "\nError was: \n" + ex);
Error_Logging el = new Error_Logging("Error while updating database with update command: \n" + cmdBuilder.GetUpdateCommand().CommandText + "\n" + ex);
}
}
conn.Close();
this.Close();
}
public static int LastLineNumber(int rn)
{
int Line_Number = 0;
int Receipt_Number = rn;
try
{
//Code to check for last line number for the reciept number
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 LastLineNumberDataAdaptor = null; //DataAdaptor for the last line number
//SqlCommandBuilder cmdBuilder; //using sql command builder to create 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 ISNULL(MAX([Last_Line_Number]), 0) " +
"FROM [ImportDataWarehouse].[dbo].[Last_Line_Number_Test]" +
"WHERE [Receipt_Number] = " + Receipt_Number, conn);
}
else
{
QueryCommand = new SqlCommand(" Select ISNULL(MAX([Last_Line_Number]), 0) " +
"FROM [ImportDataWarehouse].[dbo].[Last_Line_Number]" +
"WHERE [Receipt_Number] = " + Receipt_Number, conn);
}
LastLineNumberDataAdaptor = new SqlDataAdapter(QueryCommand);
//cmdBuilder = new SqlCommandBuilder(LastLineNumberDataAdaptor);
conn.Open();
LastLineNumberDataAdaptor.Fill(dt);
conn.Close();
if (!(dt.Tables[0].Rows[0][0].Equals(null)))
{
if (dt.Tables[0].Rows.Count == 1)
{
Line_Number = Convert.ToInt32(dt.Tables[0].Rows[0][0]);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Problem trying to find the last line number for export. /n" +
"Check network connection and verify that SQL database is running. Check Error log " +
"for more information.");
Error_Logging el = new Error_Logging("Problem trying to find the last line number for export. /n" +
"Error message is: \n" + ex);
}
return(Line_Number);
}
private void comboBoxAdamsCodes_SelectedIndexChanged(object sender, EventArgs e)
{
//Code to filter tanslation-validation table goes here.
if (comboBoxAdamsCodes.SelectedItem != null)
{
if (!string.IsNullOrEmpty(comboBoxAdamsCodes.SelectedValue.ToString()))
{
Data_Column_Name = comboBoxAdamsCodes.SelectedValue.ToString();
ExportCodesDataSet.Clear();
}
}
try
{
//get Adams Values table data from database
QueryCommand = new SqlCommand("SELECT [Data_Column_Name], [Value], [Export_Value], [Export_Key] FROM Export_Values WHERE [Data_Column_Name] = '" + Data_Column_Name + "'", conn);
ExportCodesDataAdaptor = new SqlDataAdapter(QueryCommand);
cmdBuilder = new SqlCommandBuilder(ExportCodesDataAdaptor);
ExportCodesDataAdaptor.Fill(ExportCodesDataSet);
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("There was an error while trying to load the Export Codes table for editing. \n" +
"Check your internet connection and verify that the database server is online. \n" +
" Note what happened and contact administrator for help. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load the Export Codes table for editing. " +
" \n" + ex);
conn.Close();
ExportCodesDataSet.Dispose();
}
try //Load the imported data into datagrid
{
dataGridViewAdamsCodes.DataSource = ExportCodesDataSet.Tables[0];
dataGridViewAdamsCodes.Columns[0].ReadOnly = true;
dataGridViewAdamsCodes.Columns[3].Visible = false;
}
catch (Exception exceptioncode)
{
MessageBox.Show("Error loading Export Codes datagrid for edit. \n");
Error_Logging el = new Error_Logging("Error loading Adams Codes datagrid for edit. \n" + exceptioncode);
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
//Code to filter tanslation-validation table goes here.
if (comboBoxFieldSelect.SelectedItem != null)
{
if (!string.IsNullOrEmpty(comboBoxFieldSelect.SelectedValue.ToString()))
{
Data_Column_Name = comboBoxFieldSelect.SelectedValue.ToString();
TranslationValidationDataSet.Clear();
}
}
try
{
//get tranlation-validation table data from database
//QueryCommand = new SqlCommand("SELECT [Description],[Value], [Adams_Value], [Ignore], [id] FROM Translation_Validation_Table WHERE [Data_Column_Name] = '" + Data_Column_Name + "'", conn);
QueryCommand = new SqlCommand("SELECT * FROM Translation_Validation_Table WHERE [Data_Column_Name] = '" + Data_Column_Name + "'", conn);
TranslationValidationDataAdaptor = new SqlDataAdapter(QueryCommand);
cmdBuilder = new SqlCommandBuilder(TranslationValidationDataAdaptor);
TranslationValidationDataAdaptor.Fill(TranslationValidationDataSet);
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("There was an error while trying to load the translation table for editing. " +
" Note what happened and contact administrator for help. " + ex);
Error_Logging el = new Error_Logging("There was an error while trying to load the translation-validation table for editing. " +
" Note what happened and contact administrator for help. " + ex);
conn.Close();
TranslationValidationDataSet.Dispose();
}
try //Load the imported data into datagrid
{
dataGridViewtranslationValidationTable.DataSource = TranslationValidationDataSet.Tables[0]; //TranslationTable.DefaultView;
}
catch (Exception exceptioncode)
{
MessageBox.Show("Error loading translator-validator datagrid for edit. " + "\n" + exceptioncode);
Error_Logging el = new Error_Logging("Error loading translator-validator datagrid for edit. " + "\n" + exceptioncode);
}
}
private void releaseObject(object obj) //Release the excel object and any connections and memmory
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing excel object \nPlease note what was done and contact administrator. \n");
Error_Logging el = new Error_Logging("Exception Occured while releasing excel object. \n" + ex);
}
}
private void Import_Template_Editor_Load(object sender, EventArgs e)
{
try
{
//get Import Templates table data from database
/*
* if (false) //Properties.Settings.Default.Mode == "Test")
* {
* QueryCommand = new SqlCommand("SELECT * FROM Spreadsheet_Import_Templates_Test", conn);
* }
* else
* {
* QueryCommand = new SqlCommand("SELECT * FROM Spreadsheet_Import_Templates", conn);
* }
*/
QueryCommand = new SqlCommand("SELECT * FROM Spreadsheet_Import_Templates", conn);
ImportTemplateEditorDataAdaptor = new SqlDataAdapter(QueryCommand);
cmdBuilder = new SqlCommandBuilder(ImportTemplateEditorDataAdaptor);
ImportTemplateEditorDataAdaptor.Fill(ImportTemplateEditorDataSet);
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("There was an error while trying to load the import template table for editing. \n" +
"Check your internet connection and verify that the database server is online. \n" +
" Note what happened and contact administrator for help. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load the Import template table for editing. \n" + ex);
conn.Close();
ImportTemplateEditorDataSet.Dispose();
}
try //Load the import template data into datagrid
{
this.dataGridViewImportTemplateEditor.DataSource = ImportTemplateEditorDataSet.Tables[0];
}
catch (Exception exceptioncode)
{
MessageBox.Show("Error loading Import Template datagrid for edit. \n");
Error_Logging el = new Error_Logging("Error loading Import Template datagrid for edit. \n" + exceptioncode);
}
}
public static bool UpdateLineNumber(int rn, int ln)
{
int Line_Number = ln;
int Receipt_Number = rn;
//string commandstringtext;
try
{
//Code to check for last line number for the reciept number
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
conn.Open();
// 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("INSERT INTO [ImportDataWarehouse].[dbo].[Last_Line_Number_Test] " +
"([Receipt_Number], [Last_Line_Number]) " +
"VALUES(" + Receipt_Number + ", " + Line_Number + ")", conn);
}
else
{
QueryCommand = new SqlCommand("INSERT INTO [ImportDataWarehouse].[dbo].[Last_Line_Number] " +
"([Receipt_Number], [Last_Line_Number]) " +
"VALUES(" + Receipt_Number + ", " + Line_Number + ")", conn);
}
QueryCommand.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Failed last line number update. Contact administater or see error log for more informaion.");
Error_Logging el = new Error_Logging("Error trying to update the last line number. \n" +
"Error message is: \n" + ex);
}
return(true);
}
public string GetExporterNameFromPrefix(string prfx, string expt)
{
DataSet ds = new DataSet();
string prefix = prfx;
exporter = expt;
try
{
//get tranlation table data from database
string connString = Properties.Settings.Default.ApplicationSettingsConnectionString;
//get only the row where the column name is Prefix and the value is the prefix
string query = "SELECT TOP 1 * FROM Translation_Validation_Table WHERE [Data_Column_Name] = 'Prefix' AND [Value] = '" + prefix + "'";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(ds);
conn.Close();
da.Dispose();
if (ds.Tables[0].Rows.Count == 1)
{
exporter = ds.Tables[0].Rows[0][3].ToString(); //set the exporter name
}
ds.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to get the exporter via prefix code. \n" +
" Note what happened and contact administrator for help or see error log for more details. \n");
Error_Logging el = new Error_Logging("There was an error while trying to get the exporter via prefix code. \n" +
" \n" + e);
ds.Dispose();
}
return(exporter);
}
private void StoneFruitList_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'pBIApplicationTablesDataSetStoneFruitList.StoneFuitCommodities' table. You can move, or remove it, as needed.
this.stoneFuitCommoditiesTableAdapter.Fill(this.pBIApplicationTablesDataSetStoneFruitList.StoneFuitCommodities);
try
{
//get Warehouse table data from database
QueryCommand = new SqlCommand("SELECT [Commodity],[ID] FROM StoneFuitCommodities", conn);
StoneFruitListDataAdaptor = new SqlDataAdapter(QueryCommand);
cmdBuilder = new SqlCommandBuilder(StoneFruitListDataAdaptor);
StoneFruitListDataAdaptor.Fill(StoneFuitListDataSet);
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("There was an error while trying to load the Stone Fruit List table for editing. \n" +
" Note what happened and contact administrator for help. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load the Stone Fruit List table for editing. \n" + ex);
conn.Close();
StoneFuitListDataSet.Dispose();
}
try //Load the Stone Fruit List data into datagrid
{
this.dataGridViewStoneFuitList.DataSource = StoneFuitListDataSet.Tables[0];
this.dataGridViewStoneFuitList.Columns[1].Visible = false;
}
catch (Exception exceptioncode)
{
MessageBox.Show("Error loading Stone Fruit datagrid for edit. \n" + exceptioncode);
Error_Logging el = new Error_Logging("Error loading Stone Fruit list datagrid for edit. \n" + exceptioncode);
}
}
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;
}
}
private void RegionEditor_Load(object sender, EventArgs e)
{
try
{
//get Warehouse table data from database
QueryCommand = new SqlCommand("SELECT * FROM Region_ID_Codes", conn);
RegionEditorDataAdaptor = new SqlDataAdapter(QueryCommand);
cmdBuilder = new SqlCommandBuilder(RegionEditorDataAdaptor);
RegionEditorDataAdaptor.Fill(RegionEditorDataSet);
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("There was an error while trying to load the Region List table for editing. \n" +
"Check your internet connection and verify that the database server is online. \n" +
" Note what happened and contact administrator for help. \n");
Error_Logging el = new Error_Logging("There was an error while trying to load the Region List table for editing. \n" + ex);
conn.Close();
RegionEditorDataSet.Dispose();
}
try //Load the import template data into datagrid
{
this.dataGridViewRegionEditor.DataSource = RegionEditorDataSet.Tables[0];
}
catch (Exception exceptioncode)
{
MessageBox.Show("Error loading Region datagrid for edit. \n");
Error_Logging el = new Error_Logging("Error loading Region list datagrid for edit. \n" + exceptioncode);
}
}
private void buttonSave_Click(object sender, EventArgs e)
{
try
{
conn.Open();
ExportCodesDataAdaptor.Update(ExportCodesDataSet);
MessageBox.Show("Changes to Export Codes Table made.");
}
catch (Exception ex)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
else if (conn.State == ConnectionState.Broken)
{
MessageBox.Show("Connection was broken, can not save changes. \n" +
"Check your internet connection and verify that the database server is online. \n" +
"Contact administrator to report connection problem. \n");
Error_Logging el = new Error_Logging("Connection was broken, can not save changes. \n" + ex);
}
else
{
ExportCodesDataAdaptor.Dispose();
MessageBox.Show("Error while updating database with update command: \n" + cmdBuilder.GetUpdateCommand().CommandText + "\nError was: \n");
Error_Logging el = new Error_Logging("Error while updating database with update command: \n" + cmdBuilder.GetUpdateCommand().CommandText + "\n" + ex);
}
}
conn.Close();
this.Close();
}
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 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;
}
}
private void GrowerBlockEditor_Load(object sender, EventArgs e)
{
try
{
//get Warehouse table data from database
QueryCommand = new SqlCommand("SELECT * FROM Grower_Block_ID_Codes", conn);
GrowerBlockEditorDataAdaptor = new SqlDataAdapter(QueryCommand);
cmdBuilder = new SqlCommandBuilder(GrowerBlockEditorDataAdaptor);
GrowerBlockEditorDataAdaptor.Fill(GrowerBlockEditorDataSet);
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("There was an error while trying to load the Grower Block List table for editing. \n" +
" Note what happened and contact administrator for help. \n" + ex);
Error_Logging el = new Error_Logging("There was an error while trying to load the Grower Block List table for editing. \n" + ex);
conn.Close();
GrowerBlockEditorDataSet.Dispose();
}
try //Load the import template data into datagrid
{
this.dataGridViewGrowerBlockEditor.DataSource = GrowerBlockEditorDataSet.Tables[0];
}
catch (Exception exceptioncode)
{
MessageBox.Show("Error loading Grower Block datagrid for edit. \n" + exceptioncode);
Error_Logging el = new Error_Logging("Error loading Grower Block list datagrid for edit. \n" + exceptioncode);
}
}
public bool Select_Pallets()
{
//Clear Temp selection table
using (SqlConnection sqlConnectionClear =
new SqlConnection(Properties.Settings.Default.ArchiveConnectionString))
{
SqlCommand cmdClear = new SqlCommand();
{
Int32 rowsAffected;
if (Properties.Settings.Default.Mode.ToString() == "Test")
{
cmdClear.CommandText = "DELETE FROM [ImportDataWarehouse].[dbo].[FAPI_QC_Pallet_Selection_Test]"; //Clear FAPI_QC_Pallet_Selection_Test Table
} //before entering new pallets to work with
else
{
cmdClear.CommandText = "DELETE FROM [ImportDataWarehouse].[dbo].[FAPI_QC_Pallet_Selection]"; //Clear FAPI_QC_Pallet_Selection table
}
cmdClear.Connection = sqlConnectionClear;
sqlConnectionClear.Open();
try
{
rowsAffected = cmdClear.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error trying clearing the temp QC. \n");
Error_Logging el = new Error_Logging("Error trying to clear selection table for QC. \n" + ex);
return(false);
}
}
}
//Selection of the pallets and stores in the QC_Archive table.
try
{
SqlConnection sqlConnectionArchive =
new SqlConnection(Properties.Settings.Default.ArchiveConnectionString); //connection string in application settings
SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = sqlConnectionArchive;
sqlConnectionArchive.Open();
StringBuilder Command_Text = new StringBuilder();
foreach (DataRow row in DataForQCDataSet.Tables[0].Rows) //insert each record from the dataset and string list
{
//Note the ' character is used to delineate text so must be removed from any text strings.
Command_Text = new StringBuilder();
if (Properties.Settings.Default.Mode.ToString() == "Test")
{
Command_Text.Append("INSERT FAPI_QC_Pallet_Selection_Test (");
}
else
{
Command_Text.Append("INSERT FAPI_QC_Pallet_Selection (");
}
Command_Text.Append("[Selection_Date], [Receipt_Number], [Exporter], [Vessel_Number],[Pallet_Prefix],[Warehouse]");
Command_Text.Append(",[Region],[Grower_Block],[Commodity_ID],[Receiving_Date]");
Command_Text.Append(",[Pallet_Number],[Variety_Code], [Label_Code] ,[Style_Code],[Size_Code],[Pack_Code]");
Command_Text.Append(",[Grade_Code], [Grower_Number], [Boxes_Count], [Memo])");
Command_Text.Append(" VALUES (");
Command_Text.Append("'" + DateTime.Now + "', "); //Selection Date
Command_Text.Append("'" + ReceiptNumber + "', "); //Receipt Number
Command_Text.Append("'" + QCStringList[11].ToString().Trim().Replace("'", "") + "', "); //exporter
Command_Text.Append("'" + QCStringList[13].ToString().Trim() + "', "); //Vessel Number
Command_Text.Append("'" + QCStringList[10].ToString().Trim() + "', "); //Pallet Prefix
Command_Text.Append("'" + QCStringList[3].ToString().Trim() + "', "); //Warehouse
Command_Text.Append("'" + QCStringList[6].ToString().Trim() + "', "); //Region
Command_Text.Append("'" + row[1].ToString().Trim() + "', "); //Grower Block ID, always in column 1
Command_Text.Append("'" + row[ImportSettings.CommodityColumn].ToString().Trim() + "', "); //Commodity ID
Command_Text.Append("'" + QCStringList[1] + "', "); //Receiving Date
Command_Text.Append("'" + row[ImportSettings.TagNumberColumn].ToString().Trim() + "', "); //pallet number/tag number
Command_Text.Append("'" + row[ImportSettings.VarietyColumn].ToString().Trim() + "', "); //Variety Code
Command_Text.Append("'" + row[ImportSettings.LabelColumn].ToString().Replace("'", "") + "', ").ToString().Trim(); //Label Code
if (ImportSettings.SizeColumn == ImportSettings.StyleColumn) //if size and style column are same it is a pack code
{
Command_Text.Append("'" + "', "); //Style Code
Command_Text.Append("'" + "', "); //Size Code
Command_Text.Append("'" + row[ImportSettings.PackCodeColumn].ToString().Trim() + "', "); //if pack code was used then put blank size and style
}
else
{
Command_Text.Append("'" + row[ImportSettings.StyleColumn].ToString().Trim() + "', "); //Style Code
Command_Text.Append("'" + row[ImportSettings.SizeColumn].ToString().Trim() + "', "); //Size Code
Command_Text.Append("'" + row[ImportSettings.PackCodeColumn].ToString().Trim() + "', "); //Pack code
}
Command_Text.Append("'" + row[ImportSettings.GradeColumn].ToString().Trim() + "', "); //Grade Code
Command_Text.Append("'" + row[ImportSettings.GrowerNumberColumn].ToString().Trim() + "', "); //Grower Number
Command_Text.Append("'" + row[ImportSettings.InventoryQuantityColumn] + "', "); //Box count or Inventory Quantity
Command_Text.Append("'" + "" + "')"); //Memo field blank, Since "'" messes up the command string it is removed
cmd.CommandText = Command_Text.ToString();
cmd.ExecuteNonQuery();
commandstringtext = cmd.CommandText.ToString();
}
sqlConnectionArchive.Close();
}
catch (Exception e)
{
MessageBox.Show("Error trying to Select QC Pallets import data. \n" +
"Verify that the internet connection is OK and the Archive database is up. \n" +
" See error log for more details. - The command string is: \n" + commandstringtext);
Error_Logging el = new Error_Logging("Error trying to Select QC Pallets import data. \n" + " - The command string is: \n" + commandstringtext +
"Error message is: \n" + e);
return(false);
}
//Select pallets for inspection and update the archive data table with "QC" in the memo column for pallets for QC inspection
using (SqlConnection sqlConnection1 =
new SqlConnection(Properties.Settings.Default.ArchiveConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
Int32 rowsAffected;
if (Properties.Settings.Default.Mode.ToString() == "Test")
{
cmd.CommandText = "UpdateQCArchiveTable_Test"; //this is the stored procedure on the server
} //uses the Test table to store data
else
{
cmd.CommandText = "UpdateQCArchiveTable"; //this is the stored procedure on the server
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
try
{
rowsAffected = cmd.ExecuteNonQuery();
if (!(rowsAffected == -1))
{
MessageBox.Show("There was an error in the script while Selecting pallets for QC.");
}
}
catch (Exception ex)
{
MessageBox.Show("Error trying to select pallets for QC. \n" +
" Connection error or missing stored procedure.");
Error_Logging el = new Error_Logging("Error trying to select pallets for QC. \n" +
" Connection error or missing stored procedure. \n" + ex);
return(false);
}
}
}
return(true);
}
public DataSet TranslateData() //Method to translate the data in the Data2ToTranslate dataset
{
int rownumber;
DataRow[] FoundRows;
string sTempField;
DataSet ds = new DataSet();
try
{
//get tranlation table data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Data_Column_Name],[Value],[Export_Value] from Export_Values";
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();
// 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(ds);
conn.Close();
da.Dispose();
TranslationTable = ds.Tables[0]; //set the translation table
ds.Dispose();
}
catch (Exception e)
{
MessageBox.Show("Error loading Export translation table from database. \n" +
"Internet connection or SQL server may be down. \n" +
"Please note what was done prior to error and contact administrator or see error log for more details. \n");
Error_Logging el = new Error_Logging("Error loading Export (Adams) translation table from database. \n" + e);
ds.Dispose();
Completed = false;
return(Data2Translate);
}
//Translate values generic routine *************************************************************************
//Takes the list of fields to evaluate and iterates through each of them.
try
{
foreach (string sField in List2Translate)
{
rownumber = 0;
//This is for the case where the size and style are contatonated into one field in the data streadsheet. ***
if ((TemplateSettings.SizeColumn == TemplateSettings.StyleColumn) & ((sField == "Size") || (sField == "Style")))
{
sTempField = "PackCode";
}
else
{
sTempField = sField;
}
//***
FoundRows = TranslationTable.Select("Data_Column_Name = " + "'" + sTempField + "'"); //get all rows for sField from Translation table
foreach (DataRow row in Data2Translate.Tables[0].Rows)
{
foreach (DataRow TranslatingRow in FoundRows) //FoundRows contains all the translation rows for sField
{
if (row[TemplateSettings.DataColumnLocation(sField).Column].ToString().ToUpper().Trim() == TranslatingRow[1].ToString().ToUpper().Trim())
// && TranslatingRow[2].ToString() != "") //Famous value in column 1
{
//set field to translated value. Export value in column 2
Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.DataColumnLocation(sField).Column] =
TranslatingRow[2].ToString();
}
}
rownumber++;
}
}
Completed = true;
} //end of try block
catch (Exception e)
{
Completed = false;
MessageBox.Show("Export Translation process had an error. Please note what was done and contact administrator for help or see error log for more details. \n");
Error_Logging el = new Error_Logging("Export (Adams) Translation process had an error. \n " + e);
}
return(Data2Translate);
} //end of TranslateData method
public string[] GetDataSheetNameList(string FileName)
{
sExcelFileName = FileName;
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
String connString;
// String ServerVersionTxt;
try
{
// Connection String. Change the excel file to the file you
// will search.
// String connString = "Provider=Microsoft.ACE.OLEDB.16.0;" +
// "Data Source=" + sExcelFileName; + ";Extended Properties=Excel 12.0;";
if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
{
connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sExcelFileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";//for above excel 2007 //"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sExcelFileName + ";Extended Properties=\"Excel 12.0; HDR=No;\"";
}
//"Provider=Microsoft.ACE.OLEDB.12.0;"Data Source=" + sExcelFileName + ";Extended Properties= \"Excel 12.0 Xml;HDR=No; IMEX=1\"";
//"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sExcelFileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=No\"";
else
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFileName + ";Extended Properties=\"Excel 8.0;;HDR= No; IMEX=1\"";
}
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString); //new OleDbConnection(connString);
// Open connection with the database.
// OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Data$]", objConn);
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
// Loop through all of the sheets if you want too...
// for (int j = 0; j < excelSheets.Length; j++)
//{
// Query each excel sheet.
//}
return(excelSheets);
}
catch (Exception exceptioncode)
{
MessageBox.Show("Error reading excel file. \n" + exceptioncode +
"\n Otherwise note what was done and contact administrator. \n");
Error_Logging el = new Error_Logging("Error Reading excel file. \n" + exceptioncode);
return(null);
}
finally
{
// Clean up.
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
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 Validate() //Validation routine
{
DataRow[] FoundValidateRows;
DataRow[] CommodityValidateRows;
Passed = false;
int rownumber = 0;
string sTempField;
DataTable DatTable = new DataTable();
DatTable = Data2Validate.Tables[0];
DataSet ds = new DataSet();
try
{
//get Validation data from database
string connString = Properties.Settings.Default.ConnectionString;
string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value], [Famous_Validate], [Ignore] 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();
// 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(ds);
conn.Close();
da.Dispose();
ValidationTable = ds.Tables[0]; //set the translation table
ds.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Validation table. \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 the Validation Table. \n" + e);
ds.Dispose();
return(false);
}
try
{
//Validate values generic routine *************************************************************************
//Takes the list of fields to evaluate and iterates through each of them.
foreach (string sField in List2Validate)
{
rownumber = 0;
//This is for the case where the size and style are contatonated into one field in the data streadsheet. ***
if ((TemplateSettings.SizeColumn == TemplateSettings.StyleColumn) & ((sField == "Size") || (sField == "Style")))
{
sTempField = "PackCode";
}
else
{
sTempField = sField;
}
//*** ***
FoundValidateRows = ValidationTable.Select("Data_Column_Name = " + "'" + sTempField + "'"); //get all rows for sField from validation table
CommodityValidateRows = ValidationTable.Select("Data_Column_Name = 'Commodity'"); //get all rows for Commodity from validation table
foreach (DataRow row in Data2Validate.Tables[0].Rows) //interate through data rows and check the sField column
{
Passed = false;
foreach (DataRow validaterow in FoundValidateRows) //FoundValidateRows contains all the validation rows for sField
{
if (row[TemplateSettings.DataColumnLocation(sField).Column].ToString().Trim() == validaterow[ValidateWhat].ToString().Trim() &&
validaterow[4].ToString() == "1") //Validate what tells
//it to check value or description in validate table
{
Passed = true; //If found, set passed to true
}
else if (sField == "Grade") //special case for grade. Stone Fruit has no grade and should not be validated
{
foreach (DataRow Commodityrow in CommodityValidateRows)
{
if (row[TemplateSettings.CommodityColumn].ToString().Trim() == Commodityrow[2].ToString() && Commodityrow[3].ToString() == "Stone Fruit")
{
Passed = true; //If stone fruit and Grade, set passed to true as grade is not validated
}
}
}
}
if (!Passed) //if it failed the validation add the location in the spreadsheet to the invalid list
{
if (rownumber >= TemplateSettings.DataColumnLocation(sField).Row) //The sField row parm is the start row.
{
if (!((TemplateSettings.SizeColumn == TemplateSettings.StyleColumn) & (sField == "Style"))) //Skip for second cycle for case of Style_Size field
{ // data as it will cycle for both size and style.
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.DataColumnLocation(sField).Column)); //add location of all failures to list.
}
}
}
if (InvalidItemList.Count() > 0) //if there were any invalid entries, it did not pass
{
Passed = false;
}
rownumber++;
}
}
//Check for missing pack code, fumigation, hatch, deck values and duplicate Tag Numbers.
rownumber = 0;
foreach (DataRow row in Data2Validate.Tables[0].Rows) //interate through data rows and validate other fields
{
Passed = false;
if (String.IsNullOrEmpty(row[TemplateSettings.PackCodeColumn].ToString())) //check for empty pack codes
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.PackCodeColumn)); //add location of failures to list.
}
if (String.IsNullOrEmpty(row[TemplateSettings.HatchColumn].ToString())) //check for empty hatch
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.HatchColumn)); //add location of failures to list.
row[TemplateSettings.HatchColumn] = " ";
}
if (String.IsNullOrEmpty(row[TemplateSettings.DeckColumn].ToString())) //check for empty Deck
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.DeckColumn)); //add location of failures to list.
row[TemplateSettings.DeckColumn] = " ";
}
if (row[TemplateSettings.TagNumberColumn].ToString().Trim().Length > 12 || row[TemplateSettings.TagNumberColumn].ToString().Trim().Length < 1) //check for too long too short of tag numbers.
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.TagNumberColumn)); //add location of failures to list.
}
if (InvalidItemList.Count() > 0) //if there were any invalid entries, it did not pass
{
Passed = false;
}
if (String.IsNullOrEmpty(row[TemplateSettings.FumigatedColumn].ToString())) //check for empty fumigation
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.FumigatedColumn)); //add location of failures to list.
}
else
if (!(row[TemplateSettings.FumigatedColumn].ToString() == "1" || row[TemplateSettings.FumigatedColumn].ToString() == "0"))
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.FumigatedColumn)); //add location of failures to list.
}
//check Tag Numbers
if (TagNumberExist.TagNumberExist(row[TemplateSettings.TagNumberColumn].ToString().Trim()))
{
InvalidItemList.Add(new DataItemLocation(rownumber, TemplateSettings.TagNumberColumn));
}
rownumber++;
}
}
catch (Exception e)
{
MessageBox.Show("Data Validation process had an error. Please note what was done and see administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("Data Validation process had an error. \n" + e);
return(false);
}
if (InvalidItemList.Count() > 0) //if there were any invalid entries, it did not pass
{
Passed = false;
}
else
{
Passed = true;
}
return(Passed);
} //end of Validate Method
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
}
public bool CombineMixedPallets() //Method to combine mixed pallet information into one entry record
{
DataSet TempDataSet = WorkingDataSet.Clone(); //temporary working dataset to add records to
List <int> SkipRow = new List <int>(); //used to keep a list of duplicate records so they are not checked again or entered twice
DataRow[] CommodityValidateRows = CommodityTable.Select("Data_Column_Name = 'Commodity'"); //get all rows for Commodity from validation table;
int MaxBoxCount = 0; //Highest number of boxes in a set of duplicate pallet numbers
//Now iterate through each record of the dataset to check for duplicate pallet numbers and add records to the temporary
//dataset or add summary record to it.
try
{
string MaxFruitSize = ""; //Largest size for stone fruit
for (int CurrentRow = TemplateSettings.TagNumberRow; CurrentRow < WorkingDataSet.Tables[0].Rows.Count; CurrentRow++)
{
int BoxCount = 0; //Running total of boxes for duplicates pallet numbers
MaxFruitSize = WorkingDataSet.Tables[0].Rows[CurrentRow][TemplateSettings.GradeColumn].ToString(); //Current size number for stone fruit
bool IsStoneFruit = false;
DataSet tempWorkingDataSet = new DataSet(); //Dataset which will contain the summarized data
DataRow TempDataRow = WorkingDataSet.Tables[0].Rows[CurrentRow]; //this will be current row when added to the tempDataSet
// or the summary row if there are multiple pallet numbers
if (!(SkipRow.Contains(CurrentRow))) //Do not reset the MaxBoxCount if it is a duplicate pallet number as this
//holds the highest box count for dups
{
MaxBoxCount = 0;
MaxFruitSize = WorkingDataSet.Tables[0].Rows[CurrentRow][TemplateSettings.GradeColumn].ToString();
}
//add current box count to BoxCount
BoxCount = BoxCount + Convert.ToInt32(WorkingDataSet.Tables[0].Rows[CurrentRow][TemplateSettings.InventoryQuantityColumn]);
//Iterate through remaining rows to check for duplicates and add any found rows that have the same pallet number to the
//SkipRow list to use when updating dataset
for (int CurrentTempRow = (CurrentRow + 1); CurrentTempRow < WorkingDataSet.Tables[0].Rows.Count; CurrentTempRow++)
{
foreach (DataRow Commodityrow in CommodityValidateRows)
{
if (WorkingDataSet.Tables[0].Rows[CurrentRow][TemplateSettings.CommodityColumn].ToString() == Commodityrow[2].ToString() && Commodityrow[3].ToString() == "Stone Fruit")
{
IsStoneFruit = true; //If stone fruit and Grade, set passed to true as grade is not validated
}
}
//Check for dup
if (WorkingDataSet.Tables[0].Rows[CurrentRow][TemplateSettings.TagNumberColumn].ToString().Trim() ==
WorkingDataSet.Tables[0].Rows[CurrentTempRow][TemplateSettings.TagNumberColumn].ToString().Trim()) //case where there is a dup pallet number
{
SkipRow.Add(CurrentTempRow); //skip duplicate rows
//TempDataRow[TemplateSettings.MemoColumn] = "MX"; //add MX to the memo field for duplicate pallet numbers
//Get max fruit size for stone fruit
if ((MaxFruitSize.CompareTo(WorkingDataSet.Tables[0].Rows[CurrentTempRow][TemplateSettings.GradeColumn].ToString()) < 0) &&
(IsStoneFruit)) //
{
MaxFruitSize = WorkingDataSet.Tables[0].Rows[CurrentTempRow][TemplateSettings.GradeColumn].ToString();
TempDataRow = WorkingDataSet.Tables[0].Rows[CurrentTempRow];
if (!SkipRow.Contains(CurrentTempRow)) //don't use if already checked
{
TempDataRow = WorkingDataSet.Tables[0].Rows[CurrentTempRow];
}
}
//check to see if the current records box count/quantity is greater than the others that have already been
// checked with the same pallet number
if ((MaxBoxCount < Convert.ToInt32(WorkingDataSet.Tables[0].Rows[CurrentTempRow][TemplateSettings.InventoryQuantityColumn])) &&
!(IsStoneFruit))
{
MaxBoxCount = Convert.ToInt32(WorkingDataSet.Tables[0].Rows[CurrentTempRow][TemplateSettings.InventoryQuantityColumn]);
if (!SkipRow.Contains(CurrentTempRow)) //don't use if already checked
{
TempDataRow = WorkingDataSet.Tables[0].Rows[CurrentTempRow];
}
}
BoxCount = BoxCount + Convert.ToInt32(WorkingDataSet.Tables[0].Rows[CurrentTempRow][TemplateSettings.InventoryQuantityColumn]);
}
}
TempDataRow[TemplateSettings.InventoryQuantityColumn] = BoxCount.ToString(); //put total box count in boxes/quantity field
if (IsStoneFruit)
{
TempDataRow[TemplateSettings.GradeColumn] = MaxFruitSize; //put total box count in boxes/quantity field
}
if (!(SkipRow.Contains(CurrentRow))) //if not already checked add to dataset
{
TempDataSet.Tables[0].ImportRow(TempDataRow); //add tempDataRow to the temporary dataset only if is not duplicate pallet number
}
}
}
catch (Exception e)
{
MessageBox.Show("Error while combining mixed pallets. \n Note what you were doing and contact administrator for help or see error log for more details. \n");
Error_Logging el = new Error_Logging("Error while combining mixed pallets. \n" + e);
}
WorkingDataSet = TempDataSet;
CombiningSuccessful = true;
return(CombiningSuccessful);
} //end of CombineMixedPallets
public bool Store_Data()
{
//stores the data in the archive database
try
{
SqlConnection sqlConnectionArchive =
new SqlConnection(Properties.Settings.Default.ArchiveConnectionString); //connection string in application settings
SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = sqlConnectionArchive;
sqlConnectionArchive.Open();
StringBuilder Command_Text = new StringBuilder();
foreach (DataRow row in DataToArchiveDataSet.Tables[0].Rows) //insert each record from the dataset and string list
{
//Note the ' character is used to delineate text so must be removed from any text strings.
Command_Text = new StringBuilder();
if (Properties.Settings.Default.Mode.ToString() == "Test")
{
Command_Text.Append("INSERT FAPI_Import_Data_Archive_Temp_Test (");
}
else
{
Command_Text.Append("INSERT FAPI_Import_Data_Archive_Temp (");
}
Command_Text.Append("[Receipt_Number], [Exporter],[Departure_Date],[Vessel_Number],[Vessel_Name],[Destination],[Pallet_Prefix],[Warehouse]");
Command_Text.Append(",[Region],[Grower_Block],[Commodity_ID],[Transaction_Type],[Receiving_Date],[Method_Id],[Storage_ID]");
Command_Text.Append(",[Import_Template],[Pallet_Number],[Variety_Code], [Label_Code] ,[Style_Code],[Size_Code],[Pack_Code]");
Command_Text.Append(",[Grade_Code],[Pack_Date], [Grower_Number], [Boxes_Count], [hatch], [Deck], [Fumigated], [Bill_of_Lading],[Pallet_Type],[Memo],[Other],[Test_Data])");
Command_Text.Append(" VALUES (");
Command_Text.Append("'" + ReceiptNumber + "', "); //Receipt Number
Command_Text.Append("'" + archiveStringList[11].ToString().Trim().Replace("'", "") + "', "); //exporter
Command_Text.Append("'" + archiveStringList[12] + "', "); //Departure Date
Command_Text.Append("'" + archiveStringList[13].ToString().Trim() + "', "); //Vessel Number
Command_Text.Append("'" + archiveStringList[14].ToString().Trim().Replace("'", "") + "', "); //Vessel Name
Command_Text.Append("'" + archiveStringList[15].ToString().Trim().Replace("'", "") + "', "); //Destination
Command_Text.Append("'" + archiveStringList[10] + "', "); //Pallet Prefix
Command_Text.Append("'" + archiveStringList[3].ToString().Trim() + "', "); //Warehouse
Command_Text.Append("'" + archiveStringList[6].ToString().Trim() + "', "); //Region
Command_Text.Append("'" + row[1].ToString() + "', "); //Grower Block ID, always in column 1
Command_Text.Append("'" + row[ImportSettings.CommodityColumn] + "', "); //Commodity ID
Command_Text.Append("'" + archiveStringList[0] + "', "); //Transaction Type
Command_Text.Append("'" + archiveStringList[1] + "', "); //Receiving Date
Command_Text.Append("'" + archiveStringList[7] + "', "); //Method ID
Command_Text.Append("'" + archiveStringList[8] + "', "); //Storage ID
Command_Text.Append("'" + ImportSettings.TemplateName + "', "); //Template Name
Command_Text.Append("'" + row[ImportSettings.TagNumberColumn].ToString().Trim() + "', "); //pallet number/tag number
Command_Text.Append("'" + row[ImportSettings.VarietyColumn] + "', "); //Variety Code
Command_Text.Append("'" + row[ImportSettings.LabelColumn].ToString().Trim().Replace("'", "") + "', "); //Label Code
if (ImportSettings.SizeColumn == ImportSettings.StyleColumn) //if size and style column are same it is a pack code
{
Command_Text.Append("'" + "', "); //Style Code
Command_Text.Append("'" + "', "); //Size Code
Command_Text.Append("'" + row[ImportSettings.PackCodeColumn] + "', "); //if pack code was used then put blank size and style
}
else
{
Command_Text.Append("'" + row[ImportSettings.StyleColumn] + "', "); //Style Code
Command_Text.Append("'" + row[ImportSettings.SizeColumn] + "', "); //Size Code
Command_Text.Append("'" + row[ImportSettings.PackCodeColumn] + "', "); //Pack code
}
Command_Text.Append("'" + row[ImportSettings.GradeColumn] + "', "); //Grade Code
Command_Text.Append("'" + DateNumberToDateString.ConvertDateNumberToDateString(row[ImportSettings.FirstPackDateColumn].ToString()) + "', "); //First Pack Date
Command_Text.Append("'" + row[ImportSettings.GrowerNumberColumn].ToString().Trim() + "', "); //Grower Number
Command_Text.Append("'" + row[ImportSettings.InventoryQuantityColumn] + "', "); //Box count or Inventory Quantity
Command_Text.Append("'" + row[ImportSettings.HatchColumn].ToString().Trim() + "', "); //Hatch
Command_Text.Append("'" + row[ImportSettings.DeckColumn].ToString().Trim() + "', "); //Deck
Command_Text.Append("'" + row[ImportSettings.FumigatedColumn] + "', "); //Fumigated
Command_Text.Append("'" + row[ImportSettings.BillOfLadingColumn].ToString().Trim() + "', "); //Bill of Lading
Command_Text.Append("'" + row[ImportSettings.PalletTypeColumn] + "', "); //Pallet Type
Command_Text.Append("'" + row[ImportSettings.MemoColumn].ToString().Trim().Replace("'", "") + "', "); //Memo field, Since "'" messes up the command string it is removed
Command_Text.Append("'" + archiveStringList[16] + "', "); //Other field, Used as the flag to reuse receipt number
if (Properties.Settings.Default.Mode.ToString() == "Test")
{
Command_Text.Append("' Test " + Application.ProductVersion.ToString() + "')"); // Test Mode
}
else
{
Command_Text.Append("'" + Application.ProductVersion.ToString() + "')"); // write version into test data field
}
cmd.CommandText = Command_Text.ToString();
cmd.ExecuteNonQuery();
commandstringtext = cmd.CommandText.ToString();
}
sqlConnectionArchive.Close();
}
catch (Exception e)
{
MessageBox.Show("Error trying to archive import data. \n" +
"Verify that the internet connection is OK and the Archive database is up. \n" +
" See error log for more details. - The command string is: \n" + commandstringtext);
Error_Logging el = new Error_Logging("Error trying to archive import data. \n" + " - The command string is: \n" + commandstringtext +
"Error message is: \n" + e);
return(false);
}
//update the archive data table with export name and variety for reporting
using (SqlConnection sqlConnection1 =
new SqlConnection(Properties.Settings.Default.ArchiveConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
Int32 rowsAffected;
if (Properties.Settings.Default.Mode.ToString() == "Test")
{
cmd.CommandText = "sp_UpdateManifestUploadArchiveInfoTest"; //this is the stored procedure on the server
} //uses the Test import archive to store data
else
{
cmd.CommandText = "sp_UpdateManifestUploadArchiveInfoVersion2"; //this is the stored procedure on the server
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
try
{
rowsAffected = cmd.ExecuteNonQuery();
if (!(rowsAffected == -1))
{
MessageBox.Show("There was an error in the script while updating the archive table.");
}
}
catch (Exception ex)
{
MessageBox.Show("Error trying to run archive update script. \n");
Error_Logging el = new Error_Logging("Error trying to run archive update script. \n" + ex);
return(false);
}
}
}
return(true);
}
public DataSet TranslateData() //Method to translate the data in the Data2ToTranslate dataset
{
int rownumber;
DataRow[] FoundRows;
DataRow GrowerBlockRow;
DataRow CommodityRow;
string sTempField;
int tempColumn;
DataSet ds = new DataSet(); //temp dataset for translation table
DataSet dsgb = new DataSet(); //temp dataset for grower block table
bool styleSizedAdded = false;
bool bmissingVariety = false;
DataRow[] CommodityValidateRows = CommodityTable.Select("Data_Column_Name = 'Commodity'"); //get all rows for Commodity from validation table;
try
{
//get tranlation table data from database
string connString = Properties.Settings.Default.ApplicationSettingsConnectionString;
string query = "select [Data_Column_Name],[Description],[Value], [Adams_Value], [Style], [Size], [Custom_Value], [Ignore] from Translation_Validation_Table";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query the database and return the result to your datatable
da.Fill(ds);
conn.Close();
da.Dispose();
TranslationTable = ds.Tables[0]; //set the translation table
ds.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the translation table. \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 the translation table. \n" + e);
ds.Dispose();
Completed = false;
return(Data2Translate);
}
//***************************
try
{
//get Grower Block table data from database for the selected grower
string connString = Properties.Settings.Default.ApplicationSettingsConnectionString;
string query = "SELECT [ID] " +
",[Famous_Grower_Block_Data].[NAME] " +
",[Famous_Grower_Block_Data].[GROWERNAMEIDX]" +
",Famous_Variety_Data.[VARIETYIDX] " +
",Famous_Variety_Data.NAME AS 'VarietyCode'" +
"FROM [Famous_Grower_Block_Data], Famous_Variety_Data " +
"WHERE [Famous_Grower_Block_Data].VARIETYIDX = " +
"[Famous_Variety_Data].VARIETYIDX " +
"AND GROWERNAMEIDX = '" + GrowerBlock.ToString() + "'";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
// create data adapter for the grower block data
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query the database and return the result to the datatable
da.Fill(dsgb);
conn.Close();
da.Dispose();
GrowerBlockTable = dsgb.Tables[0]; //set the Grower Block table
ds.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Grower Block table. \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 the Grower Block table. \n" + e);
ds.Dispose();
Completed = false;
return(Data2Translate);
}
//Check to see if style and size columns needed to be added ********
string binary = Convert.ToString(TemplateSettings.Custom_1, 2);
char[] AddCustomColumn = binary.ToCharArray();
Array.Reverse(AddCustomColumn);
if (AddCustomColumn.Length > 0)
{
if (AddCustomColumn[0] == '1') //for add style-size column
{
styleSizedAdded = true;
}
}
//Translate values generic routine *************************************************************************
//Takes the list of fields to evaluate and iterates through each of them.
try
{
foreach (string sField in List2Translate) //go through list of fields to translate
{
rownumber = 0;
//This is for the case where packcodes are used in the data streadsheet. ***
if ((styleSizedAdded) & (sField == "Style")) //((sField == "Size") || (sField == "Style")))
{
sTempField = "PackCode";
}
else
{
sTempField = sField;
}
//***
FoundRows = TranslationTable.Select("Data_Column_Name = " + "'" + sTempField + "'"); //get all rows for sField from Translation table
foreach (DataRow row in Data2Translate.Tables[0].Rows) //go through data table and translate
{
foreach (DataRow TranslatingRow in FoundRows) //FoundRows contains all the translation rows for sField
{
if ((sField == "Commodity") && (String.IsNullOrEmpty(row[TemplateSettings.CommodityColumn].ToString())))
{
//get commodity from tranlation table
CommodityRow = TranslationTable.Select("Value = '" + row[TemplateSettings.VarietyColumn] + "'")[0];
row[TemplateSettings.CommodityColumn] = CommodityRow[6]; //The commodity is at index 6 of the translation row
}
//set size for stone fruit
foreach (DataRow Commodityrow in CommodityValidateRows)
{
//if commodity is a stone fruit
if (Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.CommodityColumn].ToString() == Commodityrow[2].ToString() &&
Commodityrow[3].ToString() == "Stone Fruit")
{ //set the size to the grade value for stone fruit
Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.SizeColumn] =
Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.GradeColumn].ToString();
}
}
if (sTempField == "PackCode") //For pack codes us the packcode location
{
tempColumn = TemplateSettings.PackCodeColumn;
}
else
{
tempColumn = TemplateSettings.DataColumnLocation(sField).Column;
}
if (row[tempColumn].ToString().ToUpper().Trim() == TranslatingRow[TranslateFromWhat].ToString().ToUpper().Trim())
{
//set field to translated value
Data2Translate.Tables[0].Rows[rownumber][tempColumn] =
TranslatingRow[Translate2What].ToString();
if (sTempField == "PackCode")
{
Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.StyleColumn] = TranslatingRow[StyleValueColumn];
foreach (DataRow Commodityrow in CommodityValidateRows)
{
//if commodity is a stone fruit
if (Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.CommodityColumn].ToString() == Commodityrow[2].ToString() &&
(Commodityrow[3].ToString() == "Stone Fruit"))
{ //set the size if stone fruit
}
else
{
Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.SizeColumn] = TranslatingRow[SizeValueColumn];
}
}
}
}
if ((row[tempColumn].ToString().ToUpper().Trim() == TranslatingRow[AdamsValueColulmn].ToString().ToUpper().Trim()) &
!(TranslatingRow[AdamsValueColulmn].ToString().Trim() == "")) //Check for Adams codes and translate
{
//set field to translated value
Data2Translate.Tables[0].Rows[rownumber][tempColumn] =
TranslatingRow[Translate2What].ToString();
if (sTempField == "PackCode")
{
Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.StyleColumn] = TranslatingRow[StyleValueColumn];
foreach (DataRow Commodityrow in CommodityValidateRows)
{
//if commodity is a stone fruit
if (Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.CommodityColumn].ToString() == Commodityrow[2].ToString() &&
(Commodityrow[3].ToString() == "Stone Fruit"))
{ //set the size if stone fruit
}
else
{
Data2Translate.Tables[0].Rows[rownumber][TemplateSettings.SizeColumn] = TranslatingRow[SizeValueColumn];
}
}
}
}
} //end of FoundRows foreach loop
try
{
//Get the GrowerBlock from the grower block table for the variety and set the growerblock field for the
//Data row to the growerblock value.
if (GrowerBlockTable.Select("VarietyCode = '" + row[TemplateSettings.VarietyColumn] + "'").Length > 0)
{ //Check to see if the variety was found in the grower block data
GrowerBlockRow = GrowerBlockTable.Select("VarietyCode = '" + row[TemplateSettings.VarietyColumn] + "'")[0];
row[1] = GrowerBlockRow[0].ToString();
}
else
{
if (!bmissingVariety) //only show message box for first occurance of missing variety in the grower block data.
{
MessageBox.Show("Variety code " + row[TemplateSettings.VarietyColumn].ToString() +
" is not in the Grower Block data for this grower. ");
}
bmissingVariety = true;
}
}
catch (Exception e)
{
Completed = false;
MessageBox.Show("Getting Grower Block process had an error. Please note what was done and see administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("Getting Grower Block process had an error. \n " + e);
}
//fill in blank pack codes and convert fumigation codes.
if (String.IsNullOrEmpty(row[TemplateSettings.PackCodeColumn].ToString()) &
(sTempField == "Style" || sTempField == "Size"))
{
//Create a pack code from existing style and size with a space between them
row[TemplateSettings.PackCodeColumn] = row[TemplateSettings.StyleColumn].ToString().Trim() + " " +
row[TemplateSettings.SizeColumn].ToString().Trim();
}
if (String.IsNullOrEmpty(row[TemplateSettings.FumigatedColumn].ToString())) //Fumigation code translation
{
row[TemplateSettings.FumigatedColumn] = 0;
}
if ((row[TemplateSettings.FumigatedColumn].ToString().ToUpper().Trim() == "FUMIGATED") ||
row[TemplateSettings.FumigatedColumn].ToString().ToUpper().Trim() == "Y")
{
row[TemplateSettings.FumigatedColumn] = 1;
}
if ((row[TemplateSettings.FumigatedColumn].ToString().ToUpper().Trim() == "NON FUMIGATED") ||
row[TemplateSettings.FumigatedColumn].ToString().ToUpper().Trim() == "N")
{
row[TemplateSettings.FumigatedColumn] = 0;
}
rownumber++;
}
} //end of list of fields to translate foreach loop
Completed = true; //translation succeeded :)
} //end of try block
catch (Exception e)
{
Completed = false;
MessageBox.Show("Translation process had an error. Please note what was done and see administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("Translation process had an error. \n " + e);
}
return(Data2Translate); //translated data set
} //end of TranslateData method
public DataSet TranslateData() //Method to translate the data in the Data2ToTranslate dataset
{
DataRow GrowerBlockRow;
DataSet dsgb = new DataSet(); //temp dataset for grower block table
// Completed = false;
// bool styleSizeAdded = false;
bool bmissingVariety = false;
StringBuilder notTranslatedError = new StringBuilder();
//***************************
try
{
//get Grower Block table data from database for the selected grower
string connString = Properties.Settings.Default.ApplicationSettingsConnectionString;
string query = "SELECT [ID] " +
",[Famous_Grower_Block_Data].[NAME] " +
",[Famous_Grower_Block_Data].[GROWERNAMEIDX]" +
",Famous_Variety_Data.[VARIETYIDX] " +
",Famous_Variety_Data.NAME AS 'VarietyCode'" +
"FROM [Famous_Grower_Block_Data], Famous_Variety_Data " +
"WHERE [Famous_Grower_Block_Data].VARIETYIDX = " +
"[Famous_Variety_Data].VARIETYIDX " +
"AND GROWERNAMEIDX = '" + GrowerID.ToString() + "'";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
// create data adapter for the grower block data
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query the database and return the result to the datatable
da.Fill(dsgb);
conn.Close();
da.Dispose();
GrowerBlockTable = dsgb.Tables[0]; //set the Grower Block table
//ds.Dispose();
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to load the Grower Block table. \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 the Grower Block table. \n" + e);
// ds.Dispose();
// Completed = false;
return(Data2Translate);
}
//Check to see if style and size columns needed to be added ********
string binary = Convert.ToString(TemplateSettings.Custom_1, 2);
char[] AddCustomColumn = binary.ToCharArray();
Array.Reverse(AddCustomColumn);
if (AddCustomColumn.Length > 0)
{
if (AddCustomColumn[0] == '1') //for add style-size column
{
// styleSizeAdded = true;
}
}
if (Data2Translate.Tables.Count == 0)
{
MessageBox.Show("There is no data to translate, please import a data file.");
return(Data2Translate);
}
//***************************
// Add code to check if field is already translated.
//Translate fields
foreach (DataRow row in Data2Translate.Tables[0].Rows) //go through data table and translate
{
try
{
// Translate Commodity
if (String.IsNullOrEmpty(row[TemplateSettings.CommodityColumn].ToString())) //Check to see if commodity is empty, if so set it by variety
{
//if (row[TemplateSettings.CommodityColumn] != TranslationTable.Select("Grower_Variety_Code = '" + row[TemplateSettings.VarietyColumn] + "'")[0][3])
if (!String.IsNullOrEmpty(TranslationTable.Select("Grower_Variety_Code = '" + UppercaseWords.Uppercase(row[TemplateSettings.VarietyColumn].ToString()) + "'")[0][3].ToString().Trim()))
{
row[TemplateSettings.CommodityColumn] = TranslationTable.Select("Grower_Variety_Code = '" + UppercaseWords.Uppercase(row[TemplateSettings.VarietyColumn].ToString().Trim()) + "'")[0][3];
GrowerCommodity = UppercaseWords.Uppercase(row[TemplateSettings.CommodityColumn].ToString().Trim()); //Set the commodity variable to the current commodity
}
}
else
{
GrowerCommodity = UppercaseWords.Uppercase(row[TemplateSettings.CommodityColumn].ToString().Trim()); //Set the commodity variable to the current commodity
if (TranslationTable.Select("Commodity_Code = '" + UppercaseWords.Uppercase(row[TemplateSettings.CommodityColumn].ToString()) + "'").Count() == 0) //Is it not already translated
{
if (row[TemplateSettings.CommodityColumn] != TranslationTable.Select("Grower_Commodity_Code = '" + UppercaseWords.Uppercase(row[TemplateSettings.CommodityColumn].ToString().Trim()) + "'")[0][3])
{
//Translate Commodity
GrowerCommodity = UppercaseWords.Uppercase(row[TemplateSettings.CommodityColumn].ToString().Trim()); //Reset commodity variable to updated value.
row[TemplateSettings.CommodityColumn] = TranslationTable.Select("Grower_Commodity_Code = '" + UppercaseWords.Uppercase(row[TemplateSettings.CommodityColumn].ToString().Trim()) + "'")[0][3];
}
}
}
Commodity = row[TemplateSettings.CommodityColumn].ToString();
//Translate Variety
if (TranslationTable.Select("Variety_Code = '" + UppercaseWords.Uppercase(row[TemplateSettings.VarietyColumn].ToString()) + "'").Count() == 0) //Is variety not translated
{
// if (row[TemplateSettings.VarietyColumn] != TranslationTable.Select("Grower_Variety_Code = '" + row[TemplateSettings.VarietyColumn] + "'")[0][6])
if (TranslationTable.Select("Grower_Variety_Code = '" + UppercaseWords.Uppercase(row[TemplateSettings.VarietyColumn].ToString().Trim()) + "'").Count() != 0)
{
//Translate variety
GrowerVariety = UppercaseWords.Uppercase(row[TemplateSettings.VarietyColumn].ToString().Trim());
row[TemplateSettings.VarietyColumn] = TranslationTable.Select("Grower_Variety_Code = '" + UppercaseWords.Uppercase(row[TemplateSettings.VarietyColumn].ToString().Trim()) + "'")[0][6];
}
}
else
{
GrowerVariety = UppercaseWords.Uppercase(row[TemplateSettings.VarietyColumn].ToString().Trim());
}
Variety = row[TemplateSettings.VarietyColumn].ToString().Trim();
StoneFruit = TranslationTable.Select("Variety_Code = '" + UppercaseWords.Uppercase(row[TemplateSettings.VarietyColumn].ToString().Trim()) + "'")[0][8].ToString();
GCV_ID = GrowerID.ToString() + "-" + row[TemplateSettings.CommodityColumn].ToString().Trim() +
"-" + GrowerCommodity +
"-" + row[TemplateSettings.VarietyColumn].ToString().Trim() +
"-" + GrowerVariety;
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to Translate Commodity and Variety. \n" +
"For the " + Commodity + "commodity and/or the " + Variety +
" Variety. \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 Translate Commodity and Variety. \n" + e);
return(Data2Translate); //paritial translated data set
}
// Translate remaing details
// Style translation
try
{
if (row[TemplateSettings.StyleColumn].ToString().Length > 0) // is there a style in exporter data
{
if (TranslationTable.Select("Famous_Style_Code = '" + row[TemplateSettings.StyleColumn] + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() == 0) //Is style not translated, not found in translation table.
{
if (TranslationTable.Select("Grower_Style_Code = '" + row[TemplateSettings.StyleColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() != 0) // Grower style in translation table
{
row[TemplateSettings.StyleColumn] = TranslationTable.Select("Grower_Style_Code = '" + row[TemplateSettings.StyleColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'")[0][11]; //translate style
}
else
{
missingStyle = true;
}
}
}
else if (row[TemplateSettings.PackCodeColumn].ToString().Length > 0) // No style in grower sheet so use packcode if present
{
if (TranslationTable.Select("Grower_Pack_Code = '" + row[TemplateSettings.PackCodeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() != 0) // Grower packcode found in translation table
{
row[TemplateSettings.StyleColumn] = TranslationTable.Select("Grower_Pack_Code = '" + row[TemplateSettings.PackCodeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'")[0][11]; // get style from packcode
}
else
{
missingStyle = true;
}
}
else
{
missingStyle = true;
}
// Size translation
if (row[TemplateSettings.SizeColumn].ToString().Length > 0) // is there a grower size in exporter data
{
if (TranslationTable.Select("Famous_Size_Code = '" + row[TemplateSettings.SizeColumn] + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() == 0) // is the size not found in translation table for famous code
{
if (TranslationTable.Select("Grower_Size_Code = '" + row[TemplateSettings.SizeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() != 0) // Grower size code found in translation table
{
row[TemplateSettings.SizeColumn] = TranslationTable.Select("Grower_Size_Code = '" + row[TemplateSettings.SizeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'")[0][13]; // translate
}
else
{
missingSize = true;
}
}
}
else if (row[TemplateSettings.PackCodeColumn].ToString().Length > 0) //else is there a packcode to use
{
if (TranslationTable.Select("Grower_Pack_Code = '" + row[TemplateSettings.PackCodeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() != 0) // Is the grower packcode found in translation table.
{
row[TemplateSettings.SizeColumn] = TranslationTable.Select("Grower_Pack_Code = '" + row[TemplateSettings.PackCodeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'")[0][13]; // Translate
}
else
{
missingSize = true;
}
}
else
{
missingSize = true;
}
// Packcode translation
if (row[TemplateSettings.PackCodeColumn].ToString().Length > 0) // Is there a packcode in the exporter data
{
if (TranslationTable.Select("Grower_Pack_Code = '" + row[TemplateSettings.PackCodeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() != 0) // Is the grower packcode found in the translation table
{
row[TemplateSettings.PackCodeColumn] = TranslationTable.Select("Grower_Pack_Code = '" + row[TemplateSettings.PackCodeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'")[0][15]; // Translate
}
else
{
missingPackcode = true;
}
}
else if (row[TemplateSettings.PackCodeColumn].ToString().Length == 0) // if grower packcode not found in exporter data
{ // Can it be found by style and size
if (TranslationTable.Select("Grower_Style_Code = '" + row[TemplateSettings.StyleColumn].ToString().Trim().ToUpper() + "' AND " +
"Grower_Size_Code = '" + row[TemplateSettings.SizeColumn].ToString().Trim().ToUpper() + "' AND " + "GCV_Code = '" + GCV_ID + "'").Count() != 0)
{
row[TemplateSettings.PackCodeColumn] = TranslationTable.Select("Grower_Style_Code = '" + row[TemplateSettings.StyleColumn].ToString().Trim().ToUpper() + "' AND " +
"Grower_Size_Code = '" + row[TemplateSettings.SizeColumn].ToString().Trim().ToUpper() + "' AND " + "GCV_Code = '" + GCV_ID + "'")[0][15]; // Translate
}
else
{
missingPackcode = true;
}
}
else
{
missingPackcode = true;
}
// Label Translation
if (row[TemplateSettings.LabelColumn].ToString().Length > 0) //Is grower label code found in exporter data
{
if (TranslationTable.Select("Grower_Label_Code = '" +
UppercaseWords.Uppercase(row[TemplateSettings.LabelColumn].ToString().Replace("'", "''").Trim()) + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() != 0) //Grower label code found in translation table
{
row[TemplateSettings.LabelColumn] = TranslationTable.Select("Grower_Label_Code = '" +
UppercaseWords.Uppercase(row[TemplateSettings.LabelColumn].ToString().Replace("'", "''").Trim()) + "' AND " +
"GCV_Code = '" + GCV_ID + "'")[0][18]; // Translate
}
else
{
missingLabel = true;
}
}
else
{
missingLabel = true;
}
// Grade Translation if it not a stone fruit
if (row[TemplateSettings.GradeColumn].ToString().Length > 0 & StoneFruit == "N") // is there a grower grade and is it not a stone fruit (or berry)
{
if (TranslationTable.Select("Famous_Grade_Code = '" + row[TemplateSettings.GradeColumn].ToString().Trim() + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() == 0) // Is the grower grade not a Famous grade code
{
if (TranslationTable.Select("Grower_Grade_Code = '" + row[TemplateSettings.GradeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() != 0) // Is the grower grade code found in the translation table.
{
row[TemplateSettings.GradeColumn] = TranslationTable.Select("Grower_Grade_Code = '" + row[TemplateSettings.GradeColumn].ToString().Trim().ToUpper() + "' AND " +
"GCV_Code = '" + GCV_ID + "'")[0][21]; // Translate
}
else
{
missingGrade = true;
}
}
}
else if (StoneFruit == "Y")
{
missingGrade = false;
}
else
{
missingGrade = true;
}
// Pallet Type Translation
if (row[TemplateSettings.PalletTypeColumn].ToString().Length > 0) // Is the pallet type in the exporter data
{
if (TranslationTable.Select("Grower_Pallet_Type = '" + UppercaseWords.Uppercase(row[TemplateSettings.PalletTypeColumn].ToString().Trim()) + "' AND " +
"GCV_Code = '" + GCV_ID + "'").Count() != 0) // Is the grower pallet type found in the translation table
{
row[TemplateSettings.PalletTypeColumn] = TranslationTable.Select("Grower_Pallet_Type = '" + UppercaseWords.Uppercase(row[TemplateSettings.PalletTypeColumn].ToString().Trim()) + "' AND " +
"GCV_Code = '" + GCV_ID + "'")[0][24]; // Translate
}
else
{
missingPalletType = true;
}
}
else
{
missingPalletType = true;
}
}
catch (Exception e)
{
MessageBox.Show("There was an error while trying to Translate details part of the 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 translate details part of the translation. \n" + e);
return(Data2Translate); //paritial translated data set
}
try
{
//Get the GrowerBlock from the grower block table for the variety and set the growerblock field for the
//Data row to the growerblock value.
if (GrowerBlockTable.Select("VarietyCode = '" + row[TemplateSettings.VarietyColumn].ToString().Trim() + "'").Length > 0)
{ //Check to see if the variety was found in the grower block data
GrowerBlockRow = GrowerBlockTable.Select("VarietyCode = '" + row[TemplateSettings.VarietyColumn].ToString().Trim() + "'")[0];
row[1] = GrowerBlockRow[0].ToString();
}
else
{
if (!bmissingVariety) //only show message box for first occurance of missing variety in the grower block data.
{
MessageBox.Show("Variety code " + row[TemplateSettings.VarietyColumn].ToString() +
" is not in the Grower Block data for this grower. ");
}
bmissingVariety = true;
}
}
catch (Exception e)
{
// Completed = false;
MessageBox.Show("Getting Grower Block process had an error. Please note what was done and see administrator for help or see error log. \n");
Error_Logging el = new Error_Logging("Getting Grower Block process had an error. \n " + e);
}
//fill in blank pack codes and convert fumigation codes.
if (String.IsNullOrEmpty(row[TemplateSettings.PackCodeColumn].ToString()))
{
//Create a pack code from existing style and size with a space between them
row[TemplateSettings.PackCodeColumn] = row[TemplateSettings.StyleColumn].ToString().Trim() + " " +
row[TemplateSettings.SizeColumn].ToString().Trim();
}
if (String.IsNullOrEmpty(row[TemplateSettings.FumigatedColumn].ToString())) //Fumigation code translation
{
row[TemplateSettings.FumigatedColumn] = 0;
}
if ((row[TemplateSettings.FumigatedColumn].ToString().ToUpper().Trim() == "FUMIGATED") ||
row[TemplateSettings.FumigatedColumn].ToString().ToUpper().Trim() == "Y")
{
row[TemplateSettings.FumigatedColumn] = 1;
}
if ((row[TemplateSettings.FumigatedColumn].ToString().ToUpper().Trim() == "NON FUMIGATED") ||
row[TemplateSettings.FumigatedColumn].ToString().ToUpper().Trim() == "N")
{
row[TemplateSettings.FumigatedColumn] = 0;
}
if (TemplateSettings.PalletPrefixColumn == TemplateSettings.TagNumberColumn) //special case for which has prefix as part of tag number already
{ //and the import setting point to same column for both
TagNumber = row[TemplateSettings.TagNumberColumn].ToString().Trim();
}
else
{
TagNumber = row[TemplateSettings.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 (!(Prefix == (row[TemplateSettings.TagNumberColumn].ToString().Trim().Substring(0, 3)))) //Prefix not in Tag Number
{
TagNumber = Prefix + row[TemplateSettings.TagNumberColumn].ToString().Trim(); //append prefix code
row[TemplateSettings.TagNumberColumn] = TagNumber;
}
}
}
}
if (missingGrade)
{
notTranslatedError.Append("Grade, "); //add to not Translated list
}
if (missingLabel)
{
notTranslatedError.Append("Label, "); //add to not Translated list
}
if (missingPackcode)
{
notTranslatedError.Append("Pack Code, "); //add to not Translated list
}
if (missingPalletType)
{
var result = MessageBox.Show("Pallet Type is missing, would you like to use HT Wood?", "Warning",
MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
foreach (DataRow row in Data2Translate.Tables[0].Rows) //go through data table and translate
{
row[TemplateSettings.PalletTypeColumn] = "HT Wood";
}
}
else
{
notTranslatedError.Append("Pallet Type, "); //add to not Translated list
}
}
if (missingSize)
{
notTranslatedError.Append("Size, "); //add to not Translated list
}
if (missingStyle)
{
notTranslatedError.Append("Style, "); //add to not Translated list
}
if (missingVariety)
{
notTranslatedError.Append("Variety, "); //add to not Translated list
}
if (notTranslatedError.Length == 0) //if it is 0 Then all have been translated
{
MessageBox.Show("Translation Succeded!");
}
else
{
MessageBox.Show("The following parameters could not be translated: " + notTranslatedError
+ " check that all required paramaters are in manifest and that translation is setup correctly.");
}
//Completed = true;
return(Data2Translate); //translated data set
}
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 bool ExportData() //creates the export list from the Data2Export dataset
{
StringBuilder exportLineString = new StringBuilder(); //string builder used to create export text lines.
DataSet TempDataSet = new DataSet(); //dataset for summing the box count for each grower block
string tagnumber; //temp string for tag number creation
string ProductCheck = ""; //Used to hold the concatenated string to check for changes to increment the line number
string TempProductCheck = ""; //Temp string variable for the above
//string TempGrowerBlockID = ""; //Temp string for holding grower block ID to detect when it changes
int LineNumber = 0;
string Tempgrade = "";
int TempGrowerBoxCount = 0;
string TempGrowerBlock;
string TempCommodity;
string TempVariety;
string TempStyle;
string TempSize;
string TempGrade2;
string TempLabel;
bool FirstProductBlock = true;
RecordsToExport.Clear(); //clear list in case this method has been run already.
// DataRow[] StoneFruitRows = StoneFruitTable.Select("[Commodity] = *"); //get all rows for stone fruit commodities;
//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();
DataTable dt1 = new DataTable();
//Name columns so they are always named the same for the sorting
dvm.DataSet.Tables[0].Columns[ImportSettings.GradeColumn].ColumnName = "GradeColumn";
dvm.DataSet.Tables[0].Columns[1].ColumnName = "GrowerBlockColumn";
dvm.DataSet.Tables[0].Columns[ImportSettings.CommodityColumn].ColumnName = "CommodityColumn";
dvm.DataSet.Tables[0].Columns[ImportSettings.StyleColumn].ColumnName = "StyleColumn";
dvm.DataSet.Tables[0].Columns[ImportSettings.SizeColumn].ColumnName = "PackSizeColumn";
dvm.DataSet.Tables[0].Columns[ImportSettings.LabelColumn].ColumnName = "LabelColumn";
dvm.DataSet.Tables[0].Columns[ImportSettings.PackCodeColumn].ColumnName = "PackCode";
//sort
// dvm.DataViewSettings[0].Sort = "GrowerBlockColumn, LabelColumn, PackCode, PackSizeColumn, StyleColumn, GradeColumn"; //"[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, "GrowerBlockColumn, LabelColumn, PackCode, PackSizeColumn, StyleColumn, GradeColumn", DataViewRowState.CurrentRows);
//*************************************************************************************
//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
// SqlDataAdapter ArchivedReceiptsDataAdaptor2 = null; //DataAdaptor for the Exporter_Name-Vessel_Number, Receipt number table
SqlCommandBuilder cmdBuilder; //using sql command builder to create update command
//SqlCommandBuilder cmdBuilder2; //using sql command builder to create update command
SqlCommand QueryCommand = null; //query string
//SqlCommand QueryCommand2 = null; //query string
//get receipt numbers data from archive database for Exporter
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_Name] + '-' + [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_Name] + '-' + [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().Trim() + "-" + exportStringList[13].ToString().Trim() + "'";
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;
CrudType = "Create";
}
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
CrudType = "Update";
}
else //Cancel or window closed
{
IncrementReceiptNumber rn = new IncrementReceiptNumber();
ReceiptNumber = rn.GetNewReceiptNumber();
ReUseReceiptNumber = 0;
CrudType = "Create";
}
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
{
string tempString;
//Add Header info
exportLineString.AppendLine("<InventoryTransaction>");
exportLineString.AppendLine(" <CRUDType>" + CrudType + "</CRUDType>");
if (exportStringList[0] == "1") //convert transaction type code to text
{
tempString = "Receive";
}
else if (exportStringList[0] == "2")
{
tempString = "Pack";
}
else
{
tempString = "repack";
}
exportLineString.AppendLine(" <TransactionType>" + tempString + "</TransactionType>");
exportLineString.AppendLine(" <ProductType>Grower</ProductType>");
exportLineString.AppendLine(" <ReceivingEntryNumber>" + ReceiptNumber.ToString() + "</ReceivingEntryNumber>");
exportLineString.AppendLine(" <TransactionDate>" +
DateTime.Parse(DateNumberToDateString.ConvertDateNumberToDateString(exportStringList[1].Split(' ')[0]), mFomatter).ToString("yyyy-MM-dd")
+ "</TransactionDate>");
//exportStringList[1].Split(' ')[0] + "</TransactionDate>");
exportLineString.AppendLine(" <BulkFlag>" + exportStringList[2].ToString() + "</BulkFlag>");
// exportLineString.AppendLine(" <FirstReceiveDate>2011-05-02</FirstReceiveDate>"); //Optional
exportLineString.AppendLine(" <Warehouse>" + exportStringList[3] + "</Warehouse>");
exportLineString.AppendLine(" <LotId>" + exportStringList[13].ToString().Trim() + "</LotId>"); //Vessel Number/Lot ID
exportLineString.AppendLine(" <ProductLines>");
RecordsToExport.Add(exportLineString.ToString()); //add record to record list to send to file
//Add imported data to export
for (int ExportDataRow = 0; ExportDataRow < rows.Length; ExportDataRow++)
{
exportLineString = new StringBuilder();
/*
* if (TempGrowerBlockID != rows[ExportDataRow][1].ToString().Trim()) //Look for changed blockID
* {
*
* exportLineString.AppendLine(" <ProductLine>");
* exportLineString.AppendLine(" <CRUDType>Create</CRUDType>");
*
* LineNumber++; //Line number starts from zero so increment even the first time through
*
* exportLineString.AppendLine(" <LineNumber>" + LineNumber.ToString() + "</LineNumber>");
* exportLineString.AppendLine(" <BlockId>" + rows[ExportDataRow][1].ToString().Trim() + "</BlockId>");
*
*
*
* //add code to get inventory quantity for block ID *********************************************************
* DataViewManager Gdvm = new DataViewManager();
* Gdvm.DataSet = Data2Export.Copy();
* TempGrowerBlock = rows[ExportDataRow][1].ToString().Trim();
* Gdvm.DataSet.Tables[0].Columns[1].ColumnName = "Grower_Block_Column";
* DataRow[] GrowerBlockrows = Gdvm.DataSet.Tables[0].Select("Grower_Block_Column = '" + TempGrowerBlock + "'");
*
* TempGrowerBoxCount = 0;
* foreach (DataRow Growerrow in GrowerBlockrows) // Sum up the total inventory quantity for the grower block
* {
* TempGrowerBoxCount = TempGrowerBoxCount + Convert.ToInt32(Growerrow[ImportSettings.InventoryQuantityColumn]);
*
* }
*
* exportLineString.AppendLine(" <InventoryQuantity>" +
* TempGrowerBoxCount + "</InventoryQuantity>");
*
* exportLineString.AppendLine(" <AvailableFlag>" + exportStringList[9] + "</AvailableFlag>");
* //exportLineString.AppendLine(" <CompletedFlag>" + exportStringList[9] + "</CompletedFlag>"); //Optional
*
* }
*/
// TempGrowerBlockID = rows[ExportDataRow][1].ToString().Trim(); //set old BlockID identifier text to new one
//used to check to see if any of the product defining variables have changed.
TempProductCheck = rows[ExportDataRow][1].ToString().Trim() //Grower block
+ 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
//details
//If product definition changes, increment the line number
if (ProductCheck != TempProductCheck)
{
//****************
if (!(FirstProductBlock)) //skip the first time through
{
exportLineString.AppendLine(" </InventoryTags>");
exportLineString.AppendLine(" </ProductLine>");
}
FirstProductBlock = false; // set to false after first pass
exportLineString.AppendLine(" <ProductLine>");
exportLineString.AppendLine(" <CRUDType>Create</CRUDType>"); //" + CrudType + "</CRUDType>");
LineNumber++; //Line number starts from zero so increment even the first time through
exportLineString.AppendLine(" <LineNumber>" + LineNumber.ToString() + "</LineNumber>");
exportLineString.AppendLine(" <BlockId>" + rows[ExportDataRow][1].ToString().Trim() + "</BlockId>");
//add code to get inventory quantity for block ID *********************************************************
DataViewManager Gdvm = new DataViewManager();
Gdvm.DataSet = Data2Export.Copy();
TempGrowerBlock = rows[ExportDataRow][1].ToString().Trim();
TempCommodity = rows[ExportDataRow][ImportSettings.CommodityColumn].ToString().Trim();
TempVariety = rows[ExportDataRow][ImportSettings.VarietyColumn].ToString().Trim();
TempStyle = rows[ExportDataRow][ImportSettings.StyleColumn].ToString().Trim();
TempSize = rows[ExportDataRow][ImportSettings.SizeColumn].ToString().Trim();
TempGrade2 = rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim();
TempLabel = rows[ExportDataRow][ImportSettings.LabelColumn].ToString().Replace("'", "''").Trim();
Gdvm.DataSet.Tables[0].Columns[1].ColumnName = "TempGrower_Block_Column";
Gdvm.DataSet.Tables[0].Columns[ImportSettings.CommodityColumn].ColumnName = "TempCommodity";
Gdvm.DataSet.Tables[0].Columns[ImportSettings.VarietyColumn].ColumnName = "TempVariety";
Gdvm.DataSet.Tables[0].Columns[ImportSettings.StyleColumn].ColumnName = "TempStyle";
Gdvm.DataSet.Tables[0].Columns[ImportSettings.SizeColumn].ColumnName = "TempSize";
Gdvm.DataSet.Tables[0].Columns[ImportSettings.GradeColumn].ColumnName = "TempGrade";
Gdvm.DataSet.Tables[0].Columns[ImportSettings.LabelColumn].ColumnName = "TempLabel";
// DataRow[] GrowerBlockrows = Gdvm.DataSet.Tables[0].Select("Grower_Block_Column = '" + TempGrowerBlock + "'");
DataRow[] GrowerBlockrows; //Don't use Grade if it is blank.
if (TempGrade2.Length > 0)
{
GrowerBlockrows = Gdvm.DataSet.Tables[0].Select("TempGrower_Block_Column = '" + TempGrowerBlock +
"' AND TempCommodity = '" + TempCommodity + "' AND TempVariety = '" + TempVariety + "' AND TempStyle = '" +
TempStyle + "' AND TempSize = '" + TempSize + "' AND TempGrade = '" + TempGrade2 +
"' AND TempLabel = '" + TempLabel + "'");
}
else
{
GrowerBlockrows = Gdvm.DataSet.Tables[0].Select("TempGrower_Block_Column = '" + TempGrowerBlock +
"' AND TempCommodity = '" + TempCommodity + "' AND TempVariety = '" + TempVariety + "' AND TempStyle = '" +
TempStyle + "' AND TempSize = '" + TempSize + "' AND TempLabel = '" + TempLabel + "'");
}
TempGrowerBoxCount = 0;
foreach (DataRow Growerrow in GrowerBlockrows) // Sum up the total inventory quantity for the grower block
{
TempGrowerBoxCount = TempGrowerBoxCount + Convert.ToInt32(Growerrow[ImportSettings.InventoryQuantityColumn]);
}
exportLineString.AppendLine(" <InventoryQuantity>" +
TempGrowerBoxCount + "</InventoryQuantity>");
exportLineString.AppendLine(" <AvailableFlag>" + exportStringList[9] + "</AvailableFlag>");
//*************
exportLineString.AppendLine(" <Product>");
exportLineString.AppendLine(" <CommodityId>" + rows[ExportDataRow][ImportSettings.CommodityColumn].ToString().Trim()
+ "</CommodityId>");
exportLineString.AppendLine(" <VarietyId>" + rows[ExportDataRow][ImportSettings.VarietyColumn].ToString().Trim()
+ "</VarietyId>");
exportLineString.AppendLine(" <StyleId>" + rows[ExportDataRow][ImportSettings.StyleColumn].ToString().Trim()
+ "</StyleId>");
exportLineString.AppendLine(" <SizeId>" + rows[ExportDataRow][ImportSettings.SizeColumn].ToString().Trim()
+ "</SizeId>");
//Check to see if it is a stone fruit. then use size for Grade otherwise use the grade
Tempgrade = rows[ExportDataRow][ImportSettings.GradeColumn].ToString().Trim(); //add Grade ID
//foreach (DataRow StoneFruitRow in StoneFruitTable.Rows) //check all listed change if it is a stone fruit
// {
// if (rows[ExportDataRow][ImportSettings.CommodityColumn].ToString() == StoneFruitRow[0].ToString())
// {
// Tempgrade = rows[ExportDataRow][ImportSettings.SizeColumn].ToString().Trim(); //use size for grade
// }
// }
if (Tempgrade.Length > 0) // export grade only if it has a value
{
exportLineString.AppendLine(" <GradeId>" + Tempgrade + "</GradeId>");
}
exportLineString.AppendLine(" <LabelId>" + rows[ExportDataRow][ImportSettings.LabelColumn].ToString().Trim()
+ "</LabelId>");
//exportLineString.AppendLine(" <MethodId>" + exportStringList[7]
//+ "</MethodId>");
exportLineString.AppendLine(" <RegionId>" + exportStringList[6]
+ "</RegionId>");
exportLineString.AppendLine(" <StorageId>" + exportStringList[8]
+ "</StorageId>");
exportLineString.AppendLine(" <ColorId>" + rows[ExportDataRow][ImportSettings.PalletTypeColumn].ToString().Trim()
+ "</ColorId>"); //Used for Pallet code
exportLineString.AppendLine(" </Product>");
exportLineString.AppendLine(" <InventoryTags>");
}
ProductCheck = TempProductCheck; // Set the product check variable to current
//Inventory tag section
exportLineString.AppendLine(" <InventoryTag>");
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.AppendLine(" <TagId>" + tagnumber + "</TagId>");
//Total quantity
exportLineString.AppendLine(" <InventoryQuantity>" +
rows[ExportDataRow][ImportSettings.InventoryQuantityColumn].ToString().Trim() + "</InventoryQuantity>");
exportLineString.AppendLine(" <Quantity>" + rows[ExportDataRow][ImportSettings.InventoryQuantityColumn].ToString().Trim() +
"</Quantity>");
exportLineString.AppendLine(" <AvailableFlag>" + exportStringList[9] + "</AvailableFlag>");
// exportLineString.AppendLine(" <GS1Date>" + DateTime.Parse(DateNumberToDateString.ConvertDateNumberToDateString(rows[ExportDataRow]
// [ImportSettings.FirstPackDateColumn].ToString().Trim()), mFomatter).ToString("yyyy-MM-dd") +
// "</GS1Date>"); //First Pack Date
exportLineString.AppendLine(" </InventoryTag>");
RecordsToExport.Add(exportLineString.ToString()); //add record to record list to send to file
}
exportLineString = new StringBuilder();
exportLineString.AppendLine(" </InventoryTags>");
exportLineString.AppendLine(" </ProductLine>");
exportLineString.AppendLine(" </ProductLines>");
exportLineString.AppendLine("</InventoryTransaction>"); //add final tags
RecordsToExport.Add(exportLineString.ToString());
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 program know it completed
}