Auto Generate CSV and write to FTP when users print PackSlip

Epicor ERP 10
, ,
1

Hi everyone, I’m trying to automatically generate a CSV for a customer and write it to FTP (or server folder) when users print PackSlip for that customer. Has anyone gone through the same path? I would appreciate learning the solution step by step as I haven’t been able to play around much on the coding side.
I did have a look at the below post but can’t get the Report Options field working. I keep getting an “Unauthorized Server Path” error. Also, this way the users who print the PackSlip would have to print 2 times, 1 for PackSlip and 1 for saving CSV?

Thank you very much for any sharing on this.

2

Is the FTP site yours or the customers?

Is the CSV data limited to a subset of the packer, like just the info from the lines? Or include all the header info as well?

And do you have the Advanced Print Routing module?

1 Like
3

If you don’t have APM, you can add a method directive on ReportMonitor.Update and generate the file there.
This fires several times per report submission so you need to make sure it’s the right report and that the report completed successfully.

4

I’ve got a couple of bits of code that should help. One creates a CSV and emails it.

C# code to Create CSV and attach to email - Standard-Data directive BPM on ShipHead. 
string bpmFrom = string.Empty;
string bpmTo = string.Empty;
string bpmCC = string.Empty;
string bpmSubject = string.Empty;
string bpmBody = string.Empty;

string sDelDate = Convert.ToDateTime(ttShipHead[0].ShipDate).ToString("dd/MM/yyyy");
int iPackNum = ttShipHead[0].PackNum;
int iCustNum = ttShipHead[0].CustNum;

var shipLines = from sd in Db.ShipDtl
    
                // Left join to CustXPart
                join cp in Db.CustXPrt.With(LockHint.NoLock)
                    on new { sd.Company, sd.CustNum, sd.PartNum }  
                equals new { cp.Company, cp.CustNum, cp.PartNum }
                into sdl
                from cp in sdl.DefaultIfEmpty()
                
                // Left Join to PartLot
                join pl in Db.PartLot.With(LockHint.NoLock)
                    on new { sd.Company, sd.PartNum, sd.LotNum } equals new { pl.Company, pl.PartNum, pl.LotNum }
                into pllj
                from pl in pllj.DefaultIfEmpty()     
                                
                where sd.PackNum == iPackNum && sd.CustNum == iCustNum
                orderby sd.PartNum
                select new {sd.PackNum, sd.PackLine, sd.OrderNum, sd.PartNum, CustPrt = cp != null ? cp.XPartNum : "", sd.LineDesc, pl.ExpirationDate, sd.OurInventoryShipQty, 
                                bookingRef = (from or in Db.OrderRel
                                              where or.OrderNum == sd.OrderNum
                                              orderby or.OrderLine
                                              select or.Character03).FirstOrDefault()} ;

List<string> columns = new List<string>();
        columns.Add("Del Note #");
        columns.Add("Line #");
        columns.Add("Our Order #");
        columns.Add("Our Part #");
        columns.Add("Cust Part Ref #");
        columns.Add("Description");
        columns.Add("Qty");
        columns.Add("Use By");
        columns.Add("Storage Type");
        columns.Add("Booking In Ref");

StringBuilder strHTMLBuilder = new StringBuilder();   
strHTMLBuilder.Append("<html>");   
strHTMLBuilder.Append("<head>");   
strHTMLBuilder.Append("</head>");   
strHTMLBuilder.Append("<body>");   

strHTMLBuilder.Append("<p>Pre-Advice<p>");
strHTMLBuilder.Append("<p>Please find below details of lines being shipped to *** for Delivery on " + sDelDate + "<p>");

// Create Table in Column Headers
strHTMLBuilder.Append("<table border='1px' cellpadding='1' cellspacing='4'>");
strHTMLBuilder.Append("<tr>");   
foreach (var myColumn in columns)   
{   
    strHTMLBuilder.Append("<td>");   
    strHTMLBuilder.Append(myColumn);   
    strHTMLBuilder.Append("</td>");  
}   
strHTMLBuilder.Append("</tr>");
      
// Add data rows to table
foreach (var shipLn in shipLines)   
{   
    strHTMLBuilder.Append("<tr>");   
    
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append(shipLn.PackNum);   
        strHTMLBuilder.Append("</td>");  
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append(shipLn.PackLine);   
        strHTMLBuilder.Append("</td>"); 
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append(shipLn.OrderNum);   
        strHTMLBuilder.Append("</td>"); 
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append(shipLn.PartNum);   
        strHTMLBuilder.Append("</td>"); 
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append(shipLn.CustPrt);   
        strHTMLBuilder.Append("</td>"); 
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append(shipLn.LineDesc);   
        strHTMLBuilder.Append("</td>");
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append(Convert.ToInt32(shipLn.OurInventoryShipQty));   
        strHTMLBuilder.Append("</td>"); 
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append(Convert.ToDateTime(shipLn.ExpirationDate).ToString("dd/MM/yyyy"));   
        strHTMLBuilder.Append("</td>"); 
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append("Chilled");   
        strHTMLBuilder.Append("</td>"); 
        strHTMLBuilder.Append("<td>");   
        strHTMLBuilder.Append(shipLn.bookingRef);   
        strHTMLBuilder.Append("</td>");
            
    strHTMLBuilder.Append("</tr>");
    
} 

strHTMLBuilder.Append("</table>"); 
strHTMLBuilder.Append("<p>Kind Regards<br>***</p>");

//Close tags 
strHTMLBuilder.Append("</body>");   
strHTMLBuilder.Append("</html>");   
   
// Build CSV File
Ice.Lib.FileName myFile = new Ice.Lib.FileName(Db);
string fullFilePath = myFile.Get(PreAdvice.txt", Ice.Lib.FileName.ServerFileType.UserData);

// CSV - Write Headers
var myCols = columns.ToArray();
string columnHeaders = Ice.Lib.SharedUtilities.ImportExport.CsvWriter.GetCSVLine(myCols);
Ice.Lib.writeFileLib.FileWriteLine(fullFilePath, columnHeaders);

// CSV - Write Data Line(s)
foreach(var shipLn in shipLines)   
{   
    List<string> dataCellList = new List<string>();
    
    dataCellList.Add(shipLn.PackNum.ToString());   
    dataCellList.Add(shipLn.PackLine.ToString());
    dataCellList.Add(shipLn.OrderNum.ToString()); 
    dataCellList.Add(shipLn.PartNum);   
    dataCellList.Add(shipLn.CustPrt);
    dataCellList.Add(shipLn.LineDesc); 
    dataCellList.Add(Convert.ToInt32(shipLn.OurInventoryShipQty).ToString());   
    dataCellList.Add(Convert.ToDateTime(shipLn.ExpirationDate).ToString("dd/MM/yyyy"));  
    dataCellList.Add("Chilled");   
    dataCellList.Add(shipLn.bookingRef);   
      
    var dataCells = dataCellList.ToArray();
    string columnData = Ice.Lib.SharedUtilities.ImportExport.CsvWriter.GetCSVLine(dataCells);
    Ice.Lib.writeFileLib.FileWriteLine(fullFilePath, columnData);      
} 

// Build Email
string Htmltext = strHTMLBuilder.ToString();

bpmFrom = "epicor@***.co.uk";
bpmTo = "***@***.co.uk;***@***.com";
bpmSubject = "Pre-advice";
bpmBody = Htmltext;

// Send Email
var mailer = GetMailer(async: true);
var message = new Ice.Mail.SmtpMail();
message.SetFrom(bpmFrom);
message.SetTo(bpmTo);
message.SetCC(bpmCC);
message.SetSubject(bpmSubject);
message.IsBodyHtml = true;
message.SetBody(bpmBody);

//Add Attachments
Dictionary<string, string> attachments = new Dictionary<string, string>();
attachments.Add("PreAdvice.csv", fullFilePath);

mailer.Send(message, attachments);

I cannot currently find the code that I generated within a BPM to do an upload to FTP, but I have code that does it from a button on screen mod.

C# code to Create CSV and Upload via FTP 
EpiDataView edvOrders = (EpiDataView)oTrans.EpiDataViews["V_LangCSVExport_1View"];
            
            string fileName = "CompName_" + String.Format("{0:dd-MM-yyyy}", edvOrders.dataView[0]["BVD_LangCSV_CollectDate"]);
            string filePath = @"\\domain\sharedfolders\CSV\" + fileName + ".csv";   
            string delimiter = ","; 
            
            if (File.Exists(filePath)) 
            {
                EpiMessageBox.Show("File already exists, therefore orders already transmitted");
            }
            else
            {
    
                StringBuilder sb = new StringBuilder();
                
                
                            
        
                // Write file headers to String Builder
                string[][] header = new string[][]
                {
                    new string[] { "OrderNo", "CustomerReference", "BookingReference", "Customer", "Pallets", "WorkType", "CollectDate", "DeliveryDate", "CollectionAddress1", "CollectionAddress2", 
                                    "CollectionAddress3", "CollectionAddress4", "CollectionAddress5", "CollectionPostcode", "DeliveryAddress1", "DeliveryAddress2","DeliveryAddress3","DeliveryAddress4",
                                    "DeliveryAddress5","DeliveryPostcode"},
                };
        
                int hdrLength = header.GetLength(0);
                
                for (int index = 0; index < hdrLength; index++)
                    sb.AppendLine(string.Join(delimiter, header[index]));
        
                
                //Loop through all rows in grid, and append to String Builder
                int x = 0; 
                 
                for (x = 0; x <= edvOrders.dataView.Count - 1; x++ ) 
                {
                                
                    string[][] output = new string[][]
                    {
                        new string[] { "\"" + edvOrders.dataView[x]["BVD_LangCSV_OrderNo"].ToString() + "\"", 
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_CustomerReference"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_BookingReference"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_Customer"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["Calculated_QtyPallets"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_WorkType"].ToString() + "\"",
                                        "\"" + String.Format("{0:dd/MM/yyyy}", edvOrders.dataView[x]["BVD_LangCSV_CollectDate"]) + "\"",
                                        "\"" + String.Format("{0:dd/MM/yyyy}", edvOrders.dataView[x]["BVD_LangCSV_DeliveryDate"]) + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_CollectionAddress1"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_CollectionAddress2"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_CollectionAddress3"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_CollectionAddress4"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_CollectionAddress5"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_CollectionPostcode"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_DeliveryAddress1"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_DeliveryAddress2"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_DeliveryAddress3"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_DeliveryAddress4"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_DeliveryAddress5"].ToString() + "\"",
                                        "\"" + edvOrders.dataView[x]["BVD_LangCSV_DeliveryPostcode"].ToString() + "\""},
                        
                    };
                    int length = output.GetLength(0);
                    
                    for (int index = 0; index < length; index++)
                        sb.AppendLine(string.Join(delimiter, output[index]));
                } 
                
                //Write contents of string builder to CSV
                File.WriteAllText(filePath, sb.ToString()); 
        
        
                //Upload to FTP Site
                // Get the object used to communicate with the server.
                FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://ftp.servername.co.uk/IN/" + fileName + ".tmp");
                request.Method = WebRequestMethods.Ftp.UploadFile;
                request.Credentials = new NetworkCredential ("user","password");
        
                // Copy the contents of the file to the request stream.
                StreamReader sourceStream = new StreamReader(@filePath);
                byte [] fileContents = Encoding.UTF8.GetBytes(sourceStream.ReadToEnd());
                sourceStream.Close();
                request.ContentLength = fileContents.Length;
        
                Stream requestStream = request.GetRequestStream();
                requestStream.Write(fileContents, 0, fileContents.Length);
                requestStream.Close();
        
                FtpWebResponse response = (FtpWebResponse)request.GetResponse();
                response.Close();
    
                FtpWebRequest requestFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://ftp.servername.co.uk/IN/" + fileName + ".tmp"));
                requestFTP.Credentials = new NetworkCredential ("user","password");
                
                string oldFileName = fileName + ".tmp";            
                string newFilename = oldFileName.Replace(".tmp", ".csv");
                requestFTP.Method = WebRequestMethods.Ftp.Rename;
                requestFTP.RenameTo = newFilename;
                FtpWebResponse responseFTP = (FtpWebResponse)requestFTP.GetResponse();
    
                EpiMessageBox.Show("File Upload Complete"", "CSV File Uploaded");

The FTP code is written to allow for the server at the destination picking up the file and acting upon it on a schedule. To avoid clashes with part written files, it uploads with .tmp extension and then does a rename to put .csv on the file after it’s completely uploaded.

4 Likes
5

You could easily use Mark’s code in conjunction with a FTP drive mapper like NetDrive etc.

2 Likes
6

What’s a drive mapper? You mean where it would create a network drive on your PC, so you can just go after say the M:\ drive and credentials etc are handled by the drive mapping tool?

1 Like
7

Yessir, then it’s like writing standard file

8

Thanks all for your inputs.

@ckrusen: I’m planning to set up our FTP site and share it to customer. The CSV data would need Ship To, PackNum, Tracking Number so we need header info as well.

@adaniell; We have APR, not APM

@markdamen / @Chris_Conn; Thanks for sharing your code and drive mapper suggestion. I would give it a try shortly. Where can I view that edvOrders data if I want to change the columns of the CSV file.

9

So it won’t be that datagrid, as I think that’s on a custom dashboard.

Assuming that you’re doing it on a BPM, you can get the data from the tables that way. I just gave sample code that was easy to hand.

10

Id probably do it on the Standard Data Directive, when the Printed value is changed from false to true on ShipHead.

You can extract the pdf or generate a csv, write it out to a folder and have another software, service or service connect handle the transfer to SFTP.

EDIT:
I see @markdamen and I think alike… Standard directive for the win

11

Hi, No quite the same, but we do this nightly for customers through SSIS. But it is at the end of the day and not per shipment

12

Mark you are the man.

I was able to gut this and use it to FTP locally made BT files to our Epicor Cloud FTP folder for the Bartender server to pick up.

I had a functional ad hoc label producing customization that called batch files to move the files.
This cleans it up and removes dependence on other programs. Thank you for posting your code here.

We’ve had a lot of trouble with Bartender and Epicor and the Cloud working together. This is a very nice piece to smooth out a very clunky puzzle that quite frankly neither Epicor nor Seagull could figure out.

2 Likes