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 }