Beispiel #1
0
        ImportTemplateSettings TemplateSettings = new ImportTemplateSettings(); //application import settings

        public UpdateImportDatasetForQC(DataSet ds, ImportTemplateSettings ts)
        {
            DataForQCDataSet = new DataSet(); //start with clean dataset
            DataForQCDataSet = ds.Copy();     //Data for QC selection from the main data dataset
            TemplateSettings = ts;            //Template settings

            //Get the list of pallets to QC
            string        QCconnString   = Properties.Settings.Default.ArchiveConnectionString;
            StringBuilder QCCommand_Text = new StringBuilder();

            if (Properties.Settings.Default.Mode.ToString() == "Test")
            {
                QCquery = "select [Pallet_Number] FROM [QC_Archive_Test]"; // List of pallets for QC from test table;
            }
            else
            {
                QCquery = "select [Pallet_Number] FROM [QC_Archive]"; // List of pallets for QC;
            }

            System.Data.SqlClient.SqlConnection QCconn = new System.Data.SqlClient.SqlConnection(QCconnString);
            System.Data.SqlClient.SqlCommand    QCcmd  = new System.Data.SqlClient.SqlCommand(QCquery, QCconn);
            QCconn.Open();

            DataSet QCDataSet = new DataSet();

            // create data adapter
            System.Data.SqlClient.SqlDataAdapter QCda = new System.Data.SqlClient.SqlDataAdapter(QCcmd);
            // this will query the database and return the result to your datatable
            QCda.Fill(QCDataSet);
            QCconn.Close();
            QCda.Dispose();
            QCTable = QCDataSet.Tables[0];  //set the translation table
            QCDataSet.Dispose();
        }
        bool Completed = false;               //did the translation complete?

        public AdamsTranslator(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl)
        {
            Data2Translate   = ds.Copy();      //dataset containing data to translate
            TemplateSettings = ImportSettings; //import seetings showing where data is in dataset
            List2Translate   = tl;             //List of fields which need translation
            Completed        = false;          //Did tranlation complete?  Not yet.
        }
 public QCPalletSelection(DataSet ds, ImportTemplateSettings ims, List <string> asl, int rn)
 {
     DataForQCDataSet = new DataSet(); //start with clean dataset
     DataForQCDataSet = ds.Copy();     //Data for QC selection from the main data dataset
     ImportSettings   = ims;           //import settings show where in the dataset each field is located
     QCStringList     = asl;           //string data from main app controls
     ReceiptNumber    = rn;            //receipt number for export data
 }
 public Archive_Data(DataSet ds, ImportTemplateSettings ims, List <string> asl, int rn)
 {
     DataToArchiveDataSet = new DataSet(); //start with clean dataset
     DataToArchiveDataSet = ds.Copy();     //Data to archive from the main data dataset
     ImportSettings       = ims;           //import settings show where in the dataset each field is located
     archiveStringList    = asl;           //string data from main app controls
     ReceiptNumber        = rn;            //receipt number for export data
 }
        public DataValidation(DataSet ds, ImportTemplateSettings IS, List <String> lv)
        //this is the only constructor so it is important to us it correctly.
        {
            Data2Validate    = ds;   //Data from the imported dataset
            TemplateSettings = IS;   //Settings from the template
            List2Validate    = lv;   //List of fields to validate
            InvalidItemList.Clear(); //List of invalid data


            Cursor.Current = Cursors.WaitCursor;  //set the wait curser

            //Update Tag Number List
            try
            {
                string     connString = Properties.Settings.Default.ConnectionString;
                SqlCommand Command    = new SqlCommand();
                System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
                conn.Open();

                Command.Connection  = conn;
                Command.CommandType = CommandType.StoredProcedure;
                Command.CommandText = "RebuildTagNumberTable";   // Rebuild local list of famous tag numbers

                Command.ExecuteNonQuery();
                conn.Close();
            }

            catch (Exception exp)
            {
                MessageBox.Show("There was an error while trying to get Famous Tag Numbers.  \n" +
                                " Note what happened and contact administrator for help or see error log.  \n");
                Error_Logging el = new Error_Logging("There was an error while trying to get Famous Tag Numbers. \n" + exp);
                Cursor.Current = Cursors.Default;
                return;
            }

            Passed         = false; //did not pass until it passes
            Cursor.Current = Cursors.Default;
        }
Beispiel #6
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;
            }
        }
        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;
            }
        }
Beispiel #8
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;
            }
        }
Beispiel #9
0
 public AdamsExporter(DataSet ds, ImportTemplateSettings ImS, List <String> dl)
 {
     Data2Export      = ds.Copy(); //copy the incoming data to a working dataset
     exportStringList = dl;        //string list for control data from the main application
     ImportSettings   = ImS;       //import settting for import file type
 }
        public AdamsExportToExcel(DataSet ds, ImportTemplateSettings ImS, List <String> dl)
        {
            //set data
            Data2Export      = ds.Copy();
            ImportSettings   = ImS;
            exportStringList = dl;

            //do combining here
            CombineMixedBoxesOnPallets CombinedBoxes = new CombineMixedBoxesOnPallets(Data2Export, ImportSettings, "Adams");

            CombinedBoxes.CombineMixedPallets();
            CombinedBoxes.ViewData();
            Data2Export = CombinedBoxes.CombinedData();


            SaveFileDialog saveFileDialogSaveExportFile = new SaveFileDialog();
            object         misValue      = System.Reflection.Missing.Value;
            string         sExporter     = exportStringList[11].ToString();
            string         sVesselNumber = exportStringList[13].ToString();



            //Get export file name and path
            saveFileDialogSaveExportFile.InitialDirectory = Properties.Settings.Default.AdamsExportFilePath;  //get default path from settings
            saveFileDialogSaveExportFile.FileName         = saveFileDialogSaveExportFile.FileName = TruncateString.Truncate2(sExporter, 4) + "_"
                                                                                                    + sVesselNumber.ToString() + "_Export.xls";
            DialogResult result = saveFileDialogSaveExportFile.ShowDialog();


            if (saveFileDialogSaveExportFile.FileName != "")  //check if file name is blank
            {
                ExcelFileName = saveFileDialogSaveExportFile.FileName;
            }
            else  //use default path and name if one not selected
            {
                ExcelFileName = Properties.Settings.Default.AdamsExportFilePath + "\\Export_File.xls";
            }

            saveFileDialogSaveExportFile.Dispose();


            if (result == DialogResult.OK)  //only try to open excel file if open dialog <OK> button was clicked.
            {
                Excel.ApplicationClass excelapp = new Excel.ApplicationClass();
                excelapp.Visible       = true;
                excelapp.DisplayAlerts = false;      //don't display any dialog boxes or alerts from the excel app.


                Excel._Workbook  workbook  = (Excel._Workbook)(excelapp.Workbooks.Add(Type.Missing));
                Excel._Worksheet worksheet = (Excel._Worksheet)workbook.ActiveSheet;


                try
                {
                    //Write top section labels
                    worksheet.Cells[1, 1] = "Exporter:";
                    worksheet.Cells[2, 1] = "Port:";
                    worksheet.Cells[3, 1] = "Vessel Name:";
                    worksheet.Cells[4, 1] = "Pandol Lot Number:";

                    worksheet.Cells[1, 8] = "Total Box Count:";

                    //Set headers of data section
                    worksheet.Cells[5, 1]  = "\n\nPallet \nPrefix";
                    worksheet.Cells[5, 2]  = "\n\nPallet\nNumber";
                    worksheet.Cells[5, 3]  = "\n\n\nSpecies";
                    worksheet.Cells[5, 4]  = "\n\n\nVariety";
                    worksheet.Cells[5, 5]  = "\n\n\nLabel";
                    worksheet.Cells[5, 6]  = "\nPack\nDescription";
                    worksheet.Cells[5, 7]  = "Majority\nGrade\nSize";
                    worksheet.Cells[5, 8]  = "\nMajority\nPack Date";
                    worksheet.Cells[5, 9]  = "\nMajority\nProducer";
                    worksheet.Cells[5, 10] = "\nBox\nCount";
                    worksheet.Cells[5, 11] = "\n\n\nHatch";
                    worksheet.Cells[5, 12] = "\n\n\nDeck";
                    worksheet.Cells[5, 13] = "\nFumigation\nCode";
                    worksheet.Cells[5, 14] = "\n\n\nPallet Memo";
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error creating Excel workbook for Export. \nContact the adminstrator for help and note what you just did.  \n" +
                                    "See error log for more information.  \n");
                    Error_Logging el = new Error_Logging("Error creating Excel workbook for Export. \n" + ex);
                }

                try
                {
                    //Write exporter and shipping data in top section of spreadsheet
                    worksheet.Cells[1, 2] = exportStringList[11].ToString();  //Exporter Name
                    worksheet.Cells[2, 2] = exportStringList[15].ToString();  //Destination Port
                    worksheet.Cells[3, 2] = exportStringList[14].ToString();  //Vessel Name
                    worksheet.Cells[4, 2] = exportStringList[13].ToString();  //Pandol Lot Number
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error writing top section of Excel workbook for Export. \nContact the adminstrator for help and note what you just did.  \n" +
                                    "See error log for more information.  \n");
                    Error_Logging el = new Error_Logging("Error writing top section of Excel workbook for Export. \n" + ex);
                }


                //write pallet data
                try
                {
                    int tempdataRow = 0;
                    BoxCount = 0;

                    for (int ExportDataRow = 0; ExportDataRow < Data2Export.Tables[0].Rows.Count; ExportDataRow++)
                    {
                        tempdataRow = ExportDataRow + 6;                //start on row 6

                        if (exportStringList[10].ToString().Length < 1) //check to see if prefix is blank
                        {
                            worksheet.Cells[tempdataRow, 1] =
                                Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim().Substring(0, 3);
                        }
                        else
                        {
                            worksheet.Cells[tempdataRow, 1] = exportStringList[10].ToString();  //Pallet Prefix
                        }

                        if (ImportSettings.PalletPrefixColumn == ImportSettings.TagNumberColumn || exportStringList[10].ToString().Length < 1)
                        {
                            tempString = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim();
                            worksheet.Cells[tempdataRow, 2] = tempString.Substring(3, tempString.Length - 3);  //Pallet Number
                        }
                        else
                        {
                            worksheet.Cells[tempdataRow, 2] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString();      //Pallet Number
                        }
                        worksheet.Cells[tempdataRow, 3] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.CommodityColumn].ToString();          //Species/Commodity
                        worksheet.Cells[tempdataRow, 4] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.VarietyColumn].ToString();            //Variety
                        worksheet.Cells[tempdataRow, 5] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.LabelColumn].ToString();              //Label
                        //Pack Description
                        worksheet.Cells[tempdataRow, 6] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.PackCodeColumn].ToString();           //Pack Code
                        worksheet.Cells[tempdataRow, 7] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GradeColumn].ToString();              //Majority Grade
                        Dt = DateTime.Parse(DateNumberToDateString.ConvertDateNumberToDateString(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.FirstPackDateColumn].ToString()), mFomatter);
                        worksheet.Cells[tempdataRow, 8] = Dt.ToString("MM/dd/yyyy");                                                                     //Pack Date
                        worksheet.Cells[tempdataRow, 9] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GrowerNumberColumn].ToString();       //Producer

                        worksheet.Cells[tempdataRow, 10] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.InventoryQuantityColumn].ToString(); //Box Count

                        if (Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim() ==
                            Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim() &
                            Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim().Split('-').Length == 2 &
                            Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim().Length < 4)
                        {
                            worksheet.Cells[tempdataRow, 11] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim().Split('-')[0]; //Hatch
                            worksheet.Cells[tempdataRow, 12] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim().Split('-')[1];  //Deck
                        }
                        else
                        {
                            worksheet.Cells[tempdataRow, 11] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim(); //Hatch
                            worksheet.Cells[tempdataRow, 12] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim();  //Deck
                        }
                        worksheet.Cells[tempdataRow, 13] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.FumigatedColumn].ToString();        //Fumigation
                        worksheet.Cells[tempdataRow, 14] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.MemoColumn].ToString();             //Memo

                        BoxCount = BoxCount + Convert.ToInt32(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.InventoryQuantityColumn]);       //get running total of boxes
                    }

                    worksheet.Cells[1, 10] = BoxCount;



                    workbook.SaveAs(ExcelFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges, misValue, misValue, misValue, misValue);

                    excelapp.UserControl = true;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error writing pallet data to Export Excel spreadsheet. \nContact the adminstrator for help and note what you just did.  \n" +
                                    "See error log for more information.  \n");
                    Error_Logging el = new Error_Logging("Error writing pallet data to Export Excel spreadsheet. \n" + ex);
                }

                workbook.Close(true, misValue, misValue);
                excelapp.Quit();

                releaseObject(worksheet);
                releaseObject(workbook);
                releaseObject(excelapp);

                exported = true;  //Happy export  :)
            }
        }
Beispiel #11
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;
            }
        }
 public CombineMixedBoxesOnPallets(DataSet ds, ImportTemplateSettings ts, string name)
 {
     WorkingDataSet   = ds; //dataset frpm import spreadsheet
     TemplateSettings = ts; //Import template settings
     FormName         = name;
 }