public bool Store_Data() { //stores the data in the archive database try { SqlConnection sqlConnectionArchive = new SqlConnection(Properties.Settings.Default.ArchiveConnectionString); //connection string in application settings SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = sqlConnectionArchive; sqlConnectionArchive.Open(); StringBuilder Command_Text = new StringBuilder(); foreach (DataRow row in DataToArchiveDataSet.Tables[0].Rows) //insert each record from the dataset and string list { //Note the ' character is used to delineate text so must be removed from any text strings. Command_Text = new StringBuilder(); if (Properties.Settings.Default.Mode.ToString() == "Test") { Command_Text.Append("INSERT FAPI_Import_Data_Archive_Temp_Test ("); } else { Command_Text.Append("INSERT FAPI_Import_Data_Archive_Temp ("); } Command_Text.Append("[Receipt_Number], [Exporter],[Departure_Date],[Vessel_Number],[Vessel_Name],[Destination],[Pallet_Prefix],[Warehouse]"); Command_Text.Append(",[Region],[Grower_Block],[Commodity_ID],[Transaction_Type],[Receiving_Date],[Method_Id],[Storage_ID]"); Command_Text.Append(",[Import_Template],[Pallet_Number],[Variety_Code], [Label_Code] ,[Style_Code],[Size_Code],[Pack_Code]"); Command_Text.Append(",[Grade_Code],[Pack_Date], [Grower_Number], [Boxes_Count], [hatch], [Deck], [Fumigated], [Bill_of_Lading],[Pallet_Type],[Memo],[Other],[Test_Data])"); Command_Text.Append(" VALUES ("); Command_Text.Append("'" + ReceiptNumber + "', "); //Receipt Number Command_Text.Append("'" + archiveStringList[11].ToString().Trim().Replace("'", "") + "', "); //exporter Command_Text.Append("'" + archiveStringList[12] + "', "); //Departure Date Command_Text.Append("'" + archiveStringList[13].ToString().Trim() + "', "); //Vessel Number Command_Text.Append("'" + archiveStringList[14].ToString().Trim().Replace("'", "") + "', "); //Vessel Name Command_Text.Append("'" + archiveStringList[15].ToString().Trim().Replace("'", "") + "', "); //Destination Command_Text.Append("'" + archiveStringList[10] + "', "); //Pallet Prefix Command_Text.Append("'" + archiveStringList[3].ToString().Trim() + "', "); //Warehouse Command_Text.Append("'" + archiveStringList[6].ToString().Trim() + "', "); //Region Command_Text.Append("'" + row[1].ToString() + "', "); //Grower Block ID, always in column 1 Command_Text.Append("'" + row[ImportSettings.CommodityColumn] + "', "); //Commodity ID Command_Text.Append("'" + archiveStringList[0] + "', "); //Transaction Type Command_Text.Append("'" + archiveStringList[1] + "', "); //Receiving Date Command_Text.Append("'" + archiveStringList[7] + "', "); //Method ID Command_Text.Append("'" + archiveStringList[8] + "', "); //Storage ID Command_Text.Append("'" + ImportSettings.TemplateName + "', "); //Template Name Command_Text.Append("'" + row[ImportSettings.TagNumberColumn].ToString().Trim() + "', "); //pallet number/tag number Command_Text.Append("'" + row[ImportSettings.VarietyColumn] + "', "); //Variety Code Command_Text.Append("'" + row[ImportSettings.LabelColumn].ToString().Trim().Replace("'", "") + "', "); //Label Code if (ImportSettings.SizeColumn == ImportSettings.StyleColumn) //if size and style column are same it is a pack code { Command_Text.Append("'" + "', "); //Style Code Command_Text.Append("'" + "', "); //Size Code Command_Text.Append("'" + row[ImportSettings.PackCodeColumn] + "', "); //if pack code was used then put blank size and style } else { Command_Text.Append("'" + row[ImportSettings.StyleColumn] + "', "); //Style Code Command_Text.Append("'" + row[ImportSettings.SizeColumn] + "', "); //Size Code Command_Text.Append("'" + row[ImportSettings.PackCodeColumn] + "', "); //Pack code } Command_Text.Append("'" + row[ImportSettings.GradeColumn] + "', "); //Grade Code Command_Text.Append("'" + DateNumberToDateString.ConvertDateNumberToDateString(row[ImportSettings.FirstPackDateColumn].ToString()) + "', "); //First Pack Date Command_Text.Append("'" + row[ImportSettings.GrowerNumberColumn].ToString().Trim() + "', "); //Grower Number Command_Text.Append("'" + row[ImportSettings.InventoryQuantityColumn] + "', "); //Box count or Inventory Quantity Command_Text.Append("'" + row[ImportSettings.HatchColumn].ToString().Trim() + "', "); //Hatch Command_Text.Append("'" + row[ImportSettings.DeckColumn].ToString().Trim() + "', "); //Deck Command_Text.Append("'" + row[ImportSettings.FumigatedColumn] + "', "); //Fumigated Command_Text.Append("'" + row[ImportSettings.BillOfLadingColumn].ToString().Trim() + "', "); //Bill of Lading Command_Text.Append("'" + row[ImportSettings.PalletTypeColumn] + "', "); //Pallet Type Command_Text.Append("'" + row[ImportSettings.MemoColumn].ToString().Trim().Replace("'", "") + "', "); //Memo field, Since "'" messes up the command string it is removed Command_Text.Append("'" + archiveStringList[16] + "', "); //Other field, Used as the flag to reuse receipt number if (Properties.Settings.Default.Mode.ToString() == "Test") { Command_Text.Append("' Test " + Application.ProductVersion.ToString() + "')"); // Test Mode } else { Command_Text.Append("'" + Application.ProductVersion.ToString() + "')"); // write version into test data field } cmd.CommandText = Command_Text.ToString(); cmd.ExecuteNonQuery(); commandstringtext = cmd.CommandText.ToString(); } sqlConnectionArchive.Close(); } catch (Exception e) { MessageBox.Show("Error trying to archive import data. \n" + "Verify that the internet connection is OK and the Archive database is up. \n" + " See error log for more details. - The command string is: \n" + commandstringtext); Error_Logging el = new Error_Logging("Error trying to archive import data. \n" + " - The command string is: \n" + commandstringtext + "Error message is: \n" + e); return(false); } //update the archive data table with export name and variety for reporting using (SqlConnection sqlConnection1 = new SqlConnection(Properties.Settings.Default.ArchiveConnectionString)) { using (SqlCommand cmd = new SqlCommand()) { Int32 rowsAffected; if (Properties.Settings.Default.Mode.ToString() == "Test") { cmd.CommandText = "sp_UpdateManifestUploadArchiveInfoTest"; //this is the stored procedure on the server } //uses the Test import archive to store data else { cmd.CommandText = "sp_UpdateManifestUploadArchiveInfoVersion2"; //this is the stored procedure on the server } cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = sqlConnection1; sqlConnection1.Open(); try { rowsAffected = cmd.ExecuteNonQuery(); if (!(rowsAffected == -1)) { MessageBox.Show("There was an error in the script while updating the archive table."); } } catch (Exception ex) { MessageBox.Show("Error trying to run archive update script. \n"); Error_Logging el = new Error_Logging("Error trying to run archive update script. \n" + ex); return(false); } } } return(true); }
public 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 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 :) } }
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 }