ImportTemplateSettings TemplateSettings = new ImportTemplateSettings(); //application import settings public UpdateImportDatasetForQC(DataSet ds, ImportTemplateSettings ts) { DataForQCDataSet = new DataSet(); //start with clean dataset DataForQCDataSet = ds.Copy(); //Data for QC selection from the main data dataset TemplateSettings = ts; //Template settings //Get the list of pallets to QC string QCconnString = Properties.Settings.Default.ArchiveConnectionString; StringBuilder QCCommand_Text = new StringBuilder(); if (Properties.Settings.Default.Mode.ToString() == "Test") { QCquery = "select [Pallet_Number] FROM [QC_Archive_Test]"; // List of pallets for QC from test table; } else { QCquery = "select [Pallet_Number] FROM [QC_Archive]"; // List of pallets for QC; } System.Data.SqlClient.SqlConnection QCconn = new System.Data.SqlClient.SqlConnection(QCconnString); System.Data.SqlClient.SqlCommand QCcmd = new System.Data.SqlClient.SqlCommand(QCquery, QCconn); QCconn.Open(); DataSet QCDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter QCda = new System.Data.SqlClient.SqlDataAdapter(QCcmd); // this will query the database and return the result to your datatable QCda.Fill(QCDataSet); QCconn.Close(); QCda.Dispose(); QCTable = QCDataSet.Tables[0]; //set the translation table QCDataSet.Dispose(); }
bool Completed = false; //did the translation complete? public AdamsTranslator(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl) { Data2Translate = ds.Copy(); //dataset containing data to translate TemplateSettings = ImportSettings; //import seetings showing where data is in dataset List2Translate = tl; //List of fields which need translation Completed = false; //Did tranlation complete? Not yet. }
public QCPalletSelection(DataSet ds, ImportTemplateSettings ims, List <string> asl, int rn) { DataForQCDataSet = new DataSet(); //start with clean dataset DataForQCDataSet = ds.Copy(); //Data for QC selection from the main data dataset ImportSettings = ims; //import settings show where in the dataset each field is located QCStringList = asl; //string data from main app controls ReceiptNumber = rn; //receipt number for export data }
public Archive_Data(DataSet ds, ImportTemplateSettings ims, List <string> asl, int rn) { DataToArchiveDataSet = new DataSet(); //start with clean dataset DataToArchiveDataSet = ds.Copy(); //Data to archive from the main data dataset ImportSettings = ims; //import settings show where in the dataset each field is located archiveStringList = asl; //string data from main app controls ReceiptNumber = rn; //receipt number for export data }
public DataValidation(DataSet ds, ImportTemplateSettings IS, List <String> lv) //this is the only constructor so it is important to us it correctly. { Data2Validate = ds; //Data from the imported dataset TemplateSettings = IS; //Settings from the template List2Validate = lv; //List of fields to validate InvalidItemList.Clear(); //List of invalid data Cursor.Current = Cursors.WaitCursor; //set the wait curser //Update Tag Number List try { string connString = Properties.Settings.Default.ConnectionString; SqlCommand Command = new SqlCommand(); System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); conn.Open(); Command.Connection = conn; Command.CommandType = CommandType.StoredProcedure; Command.CommandText = "RebuildTagNumberTable"; // Rebuild local list of famous tag numbers Command.ExecuteNonQuery(); conn.Close(); } catch (Exception exp) { MessageBox.Show("There was an error while trying to get Famous Tag Numbers. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to get Famous Tag Numbers. \n" + exp); Cursor.Current = Cursors.Default; return; } Passed = false; //did not pass until it passes Cursor.Current = Cursors.Default; }
bool Completed; //did the translation complete? public DataTranslator(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gb) { Data2Translate = ds; TemplateSettings = ImportSettings; List2Translate = tl; GrowerBlock = gb; Completed = false; //Did tranlation complete? Not yet. try { //get Commodity data from database string connString = Properties.Settings.Default.ConnectionString; string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); DataSet commodityDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to your datatable da.Fill(commodityDataSet); conn.Close(); da.Dispose(); CommodityTable = commodityDataSet.Tables[0]; //set the translation table commodityDataSet.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to load the Commodity data for translation. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for translation. \n" + e); ds.Dispose(); return; } }
public CombineMixedBoxesOnPallets(DataSet ds, ImportTemplateSettings ts, string name) { WorkingDataSet = ds; //dataset frpm import spreadsheet TemplateSettings = ts; //Import template settings FormName = name; try { //get Commodity data from database string connString = Properties.Settings.Default.ConnectionString; string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); DataSet commodityDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to your datatable da.Fill(commodityDataSet); conn.Close(); da.Dispose(); CommodityTable = commodityDataSet.Tables[0]; //set the translation table commodityDataSet.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to load the Commodity data for the combining. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data. \n" + e); ds.Dispose(); return; } }
public FamousXMLExporter(DataSet ds, ImportTemplateSettings ImS, List <String> dl) { Data2Export = ds.Copy(); exportStringList = dl; ImportSettings = ImS; try { //get Commodity data from database string connString = Properties.Settings.Default.ConnectionString; string query = "select [Commodity] FROM [StoneFuitCommodities]"; // List of stone fruits and berries; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); DataSet StoneFruitDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to your datatable da.Fill(StoneFruitDataSet); conn.Close(); da.Dispose(); StoneFruitTable = StoneFruitDataSet.Tables[0]; //set the translation table StoneFruitDataSet.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to load the Commodity data for XML export. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for XML export. \n" + e); ds.Dispose(); return; } }
public AdamsExporter(DataSet ds, ImportTemplateSettings ImS, List <String> dl) { Data2Export = ds.Copy(); //copy the incoming data to a working dataset exportStringList = dl; //string list for control data from the main application ImportSettings = ImS; //import settting for import file type }
public AdamsExportToExcel(DataSet ds, ImportTemplateSettings ImS, List <String> dl) { //set data Data2Export = ds.Copy(); ImportSettings = ImS; exportStringList = dl; //do combining here CombineMixedBoxesOnPallets CombinedBoxes = new CombineMixedBoxesOnPallets(Data2Export, ImportSettings, "Adams"); CombinedBoxes.CombineMixedPallets(); CombinedBoxes.ViewData(); Data2Export = CombinedBoxes.CombinedData(); SaveFileDialog saveFileDialogSaveExportFile = new SaveFileDialog(); object misValue = System.Reflection.Missing.Value; string sExporter = exportStringList[11].ToString(); string sVesselNumber = exportStringList[13].ToString(); //Get export file name and path saveFileDialogSaveExportFile.InitialDirectory = Properties.Settings.Default.AdamsExportFilePath; //get default path from settings saveFileDialogSaveExportFile.FileName = saveFileDialogSaveExportFile.FileName = TruncateString.Truncate2(sExporter, 4) + "_" + sVesselNumber.ToString() + "_Export.xls"; DialogResult result = saveFileDialogSaveExportFile.ShowDialog(); if (saveFileDialogSaveExportFile.FileName != "") //check if file name is blank { ExcelFileName = saveFileDialogSaveExportFile.FileName; } else //use default path and name if one not selected { ExcelFileName = Properties.Settings.Default.AdamsExportFilePath + "\\Export_File.xls"; } saveFileDialogSaveExportFile.Dispose(); if (result == DialogResult.OK) //only try to open excel file if open dialog <OK> button was clicked. { Excel.ApplicationClass excelapp = new Excel.ApplicationClass(); excelapp.Visible = true; excelapp.DisplayAlerts = false; //don't display any dialog boxes or alerts from the excel app. Excel._Workbook workbook = (Excel._Workbook)(excelapp.Workbooks.Add(Type.Missing)); Excel._Worksheet worksheet = (Excel._Worksheet)workbook.ActiveSheet; try { //Write top section labels worksheet.Cells[1, 1] = "Exporter:"; worksheet.Cells[2, 1] = "Port:"; worksheet.Cells[3, 1] = "Vessel Name:"; worksheet.Cells[4, 1] = "Pandol Lot Number:"; worksheet.Cells[1, 8] = "Total Box Count:"; //Set headers of data section worksheet.Cells[5, 1] = "\n\nPallet \nPrefix"; worksheet.Cells[5, 2] = "\n\nPallet\nNumber"; worksheet.Cells[5, 3] = "\n\n\nSpecies"; worksheet.Cells[5, 4] = "\n\n\nVariety"; worksheet.Cells[5, 5] = "\n\n\nLabel"; worksheet.Cells[5, 6] = "\nPack\nDescription"; worksheet.Cells[5, 7] = "Majority\nGrade\nSize"; worksheet.Cells[5, 8] = "\nMajority\nPack Date"; worksheet.Cells[5, 9] = "\nMajority\nProducer"; worksheet.Cells[5, 10] = "\nBox\nCount"; worksheet.Cells[5, 11] = "\n\n\nHatch"; worksheet.Cells[5, 12] = "\n\n\nDeck"; worksheet.Cells[5, 13] = "\nFumigation\nCode"; worksheet.Cells[5, 14] = "\n\n\nPallet Memo"; } catch (Exception ex) { MessageBox.Show("Error creating Excel workbook for Export. \nContact the adminstrator for help and note what you just did. \n" + "See error log for more information. \n"); Error_Logging el = new Error_Logging("Error creating Excel workbook for Export. \n" + ex); } try { //Write exporter and shipping data in top section of spreadsheet worksheet.Cells[1, 2] = exportStringList[11].ToString(); //Exporter Name worksheet.Cells[2, 2] = exportStringList[15].ToString(); //Destination Port worksheet.Cells[3, 2] = exportStringList[14].ToString(); //Vessel Name worksheet.Cells[4, 2] = exportStringList[13].ToString(); //Pandol Lot Number } catch (Exception ex) { MessageBox.Show("Error writing top section of Excel workbook for Export. \nContact the adminstrator for help and note what you just did. \n" + "See error log for more information. \n"); Error_Logging el = new Error_Logging("Error writing top section of Excel workbook for Export. \n" + ex); } //write pallet data try { int tempdataRow = 0; BoxCount = 0; for (int ExportDataRow = 0; ExportDataRow < Data2Export.Tables[0].Rows.Count; ExportDataRow++) { tempdataRow = ExportDataRow + 6; //start on row 6 if (exportStringList[10].ToString().Length < 1) //check to see if prefix is blank { worksheet.Cells[tempdataRow, 1] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim().Substring(0, 3); } else { worksheet.Cells[tempdataRow, 1] = exportStringList[10].ToString(); //Pallet Prefix } if (ImportSettings.PalletPrefixColumn == ImportSettings.TagNumberColumn || exportStringList[10].ToString().Length < 1) { tempString = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString().Trim(); worksheet.Cells[tempdataRow, 2] = tempString.Substring(3, tempString.Length - 3); //Pallet Number } else { worksheet.Cells[tempdataRow, 2] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.TagNumberColumn].ToString(); //Pallet Number } worksheet.Cells[tempdataRow, 3] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.CommodityColumn].ToString(); //Species/Commodity worksheet.Cells[tempdataRow, 4] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.VarietyColumn].ToString(); //Variety worksheet.Cells[tempdataRow, 5] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.LabelColumn].ToString(); //Label //Pack Description worksheet.Cells[tempdataRow, 6] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.PackCodeColumn].ToString(); //Pack Code worksheet.Cells[tempdataRow, 7] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GradeColumn].ToString(); //Majority Grade Dt = DateTime.Parse(DateNumberToDateString.ConvertDateNumberToDateString(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.FirstPackDateColumn].ToString()), mFomatter); worksheet.Cells[tempdataRow, 8] = Dt.ToString("MM/dd/yyyy"); //Pack Date worksheet.Cells[tempdataRow, 9] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.GrowerNumberColumn].ToString(); //Producer worksheet.Cells[tempdataRow, 10] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.InventoryQuantityColumn].ToString(); //Box Count if (Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim() == Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim() & Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim().Split('-').Length == 2 & Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim().Length < 4) { worksheet.Cells[tempdataRow, 11] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim().Split('-')[0]; //Hatch worksheet.Cells[tempdataRow, 12] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim().Split('-')[1]; //Deck } else { worksheet.Cells[tempdataRow, 11] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.HatchColumn].ToString().Trim(); //Hatch worksheet.Cells[tempdataRow, 12] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.DeckColumn].ToString().Trim(); //Deck } worksheet.Cells[tempdataRow, 13] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.FumigatedColumn].ToString(); //Fumigation worksheet.Cells[tempdataRow, 14] = Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.MemoColumn].ToString(); //Memo BoxCount = BoxCount + Convert.ToInt32(Data2Export.Tables[0].Rows[ExportDataRow][ImportSettings.InventoryQuantityColumn]); //get running total of boxes } worksheet.Cells[1, 10] = BoxCount; workbook.SaveAs(ExcelFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges, misValue, misValue, misValue, misValue); excelapp.UserControl = true; } catch (Exception ex) { MessageBox.Show("Error writing pallet data to Export Excel spreadsheet. \nContact the adminstrator for help and note what you just did. \n" + "See error log for more information. \n"); Error_Logging el = new Error_Logging("Error writing pallet data to Export Excel spreadsheet. \n" + ex); } workbook.Close(true, misValue, misValue); excelapp.Quit(); releaseObject(worksheet); releaseObject(workbook); releaseObject(excelapp); exported = true; //Happy export :) } }
StringBuilder notTranslatedError = new StringBuilder(); //list to hold the name of fields that did not translate public DataTranslatorByGrower(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gi, string pf) { Data2Translate = ds; TemplateSettings = ImportSettings; Prefix = pf; GrowerID = gi; // Completed = false; //Did tranlation complete? Not yet. if (Properties.Settings.Default.Mode == "Test") { GCVInfoTable = "GCV_Information_Test2"; TranslationDetailsTable = "Translation_Details_Test2"; } else { GCVInfoTable = "GCV_Information2"; TranslationDetailsTable = "Translation_Details2"; } /* Location in datarow of data to translate. * Grower_ID - 0, * Grower_Name - 1, * Grower_Commodity_Code - 2, * Commodity_Code - 3, * Commodity - 4, * Grower_Variety_Code - 5, * Variety_Code - 6, * Variety - 7, * Stone_Fruit - 8, * GCV_Code - 9, * Grower_Style_Code - 10, * Famous_Style_Code - 11, * Grower_Size_Code - 12, * Famous_Size_Code - 13, * Grower_Pack_Code - 14, * Famous_Pack_Code - 15, * Adams_Pack_Code - 16, * Grower_Label_Code - 17, * Famous_Label_Code - 18, * Adams_Label_Code - 19, * Grower_Grade_Code - 20, * Famous_Grade_Code - 21, * Adams_Grade_Code - 22, * Grower_Pallet_Type - 23, * Famous_Pallet_Type - 24, * Adams_Pallet_Type - 25 * */ try { //get Translation data from database string connString = Properties.Settings.Default.ConnectionString; string query = "SELECT Grower_ID, Grower_Name, Grower_Commodity_Code, Commodity_Code, Commodity, Grower_Variety_Code, " + "Variety_Code, Variety, Stone_Fruit, " + GCVInfoTable.ToString() + ".GCV_Code, Grower_Style_Code, " + "Famous_Style_Code, Grower_Size_Code, Famous_Size_Code, Grower_Pack_Code, " + "Famous_Pack_Code, Adams_Pack_Code, Grower_Label_Code, Famous_Label_Code, " + "Adams_Label_Code, Grower_Grade_Code, Famous_Grade_Code, " + "Adams_Grade_Code, Grower_Pallet_Type, Famous_Pallet_Type, " + "Adams_Pallet_Type " + "FROM " + GCVInfoTable.ToString() + " INNER JOIN " + TranslationDetailsTable.ToString() + " ON " + GCVInfoTable.ToString() + ".GCV_Code = " + TranslationDetailsTable.ToString() + ".GCV_Code " + "WHERE " + GCVInfoTable.ToString() + ".Grower_ID = " + GrowerID.ToString(); // Form translation table for grower; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); DataSet translationDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to the datatable da.Fill(translationDataSet); conn.Close(); da.Dispose(); TranslationTable = translationDataSet.Tables[0]; //set the translation table translationDataSet.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to create and load the translation data. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to create and load translation data. \n" + e); ds.Dispose(); return; } }
public CombineMixedBoxesOnPallets(DataSet ds, ImportTemplateSettings ts, string name) { WorkingDataSet = ds; //dataset frpm import spreadsheet TemplateSettings = ts; //Import template settings FormName = name; }