Export Sales Order to CSV with special requirement

[OliverYan]

Hi,
our clients want to export the sales order to a CSV files in jiwa 7.
I know how to use the standard function in plugin, but the format what we required is different with standard one. the columns order is different and the column name/header is also need to change.

How can I do for this?

Thanks,
Oliver

[Scott.Pearce]

I know how to use the standard function in plugin,

Which plugin are you referring to?

[Mike.Sheen]

Oliver,

The simplest way to alter the output from the Sales Order Export form is to use a custom stored procedure. By default the Sales Order Export uses a stored procedure named usp_JIWA_SalesOrders_Export. You should copy that stored procedure and name it appropriately, and then change the system setting "StoredProcForSalesOrderExport" under the invoicing section of System Configuration to be your newly created stored procedure.

The Sales Order Export will call that stored procedure and every row returned will be a row of the output CSV file, in the order the stored procedure returns columns. If you want to add a header row (the Sales Order Export does not do this), then the attached plugin will do that for you.

Plugin Sales Order CSV Export Add Headers.xml

Sample Plugin to Add CSV Header

(34.95 KiB) Downloaded 192 times

This plugin will look at the column names that the stored procedure outputs, and adds a row to the file when created with the column names separated by a comma. Make sure your stored procedure explicitly names the columns for this to work.

Mike

[OliverYan]

Scott and Mike,
Thank you very much.

I'm also required export sale order to XML with format as Jiwa 6. because of the old format is using for data importing to another system. the client don't want th field "IgnoreDebtorOnHold" etc, and don't want the xmlns tags. How do I do this?

Thanks,
Oliver

[Scott.Pearce]

I'm not sure what sort of mechanism you intend to use to export sales order XML, so my answer may not suit.

If you are using the SalesOrder business logic, you could do something like this:

Code: Select all

Dim mySalesOrderObject as JiwaSales.SalesOrder.SalesOrder
mySalesOrderObject = JiwaApplication.Manager.Instance.BusinessLogicFactory.CreateBusinessLogic(Of JiwaSales.SalesOrder.SalesOrder)(nothing)

Dim invoiceID as string = "000000000800000000NK" 'The SO_Main.InvoiceID of the sales order you are interested in.

'Read the sales order
mySalesOrderObject.Read(invoiceID)

'Get the XML representing the sales order
Dim salesOrderXML as String = ""
mySalesOrderObject.Serialise(salesOrderXML)


'Insert your own code to manipulate the contents of salesOrderXML. 
'It's a string so use System.XML to load it into an XMLDocument object for XML document manipulation, then spit the manipulated XML document back out to a string.
'YOUR CODE HERE


'Save the manipulated salesOrderXML string out to the file system.
Dim myFileName as string = "c:\SalesOrder.xml"

If My.Computer.FileSystem.FileExists(myFileName ) Then
    My.Computer.FileSystem.DeleteFile(myFileName )
Else
    My.Computer.FileSystem.WriteAllText(myFileName, String.Empty, False)
End If

My.Computer.FileSystem.WriteAllText(myFileName , salesOrderXML, True)


[OliverYan]

Hi Scott,
Thanks for your quickly reply.
It's really help.

Best Regards,
Oliver