コード例 #1
0
        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  :)
            }
        }
コード例 #2
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
        }
コード例 #3
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
        }