Exemplo n.º 1
0
        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);
            }
        }
Exemplo n.º 2
0
        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);
            }
        }
Exemplo n.º 5
0
        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);
     }
 }
Exemplo n.º 7
0
        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);
        }
Exemplo n.º 10
0
        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);
            }
        }
Exemplo n.º 11
0
        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;
        }
Exemplo n.º 12
0
        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;
            }
        }
Exemplo n.º 13
0
        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();
        }
Exemplo n.º 15
0
        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;
            }
        }
Exemplo n.º 17
0
        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();
                }
            }
        }
Exemplo n.º 21
0
        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
        }
Exemplo n.º 22
0
        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
Exemplo n.º 23
0
        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
Exemplo n.º 25
0
        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);
        }
Exemplo n.º 26
0
        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
Exemplo n.º 27
0
        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  :)
            }
        }
Exemplo n.º 29
0
        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;
            }
        }
Exemplo n.º 30
0
        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
        }