Exporting consignment data from the TPN Live Project is quite easy and can be obtained from the (1) program or (2) via a SOAP method.
(1) In the main screen click on the Import/Export button at the top of the screen.
This will then pop-up another screen. Choose Export Consignments and the range.
The export is in standard CSV format and each value in the produced file holds information about one of the following fields. Note that not all the fields are mandatory and you may notice that some are consequently left blank. The consignment file (both import and export) values are always in the following order no matter how you call for the data :
1. Docket
2. Old system docket - Legacy number from TPN Depot
3. Third party ID
4. Customer reference
5. Depot reference
6. Created date
7. Requesting depot number
8. Collection depot number
9. Delivery depot number
10. Consignee name
11. Consignee address line 1
12. Consignee address line 2
13. Consignee address line 3
14. Consignee address line 4
15. Consignee postcode
16. Consignee contact
17. Consignee telephone
18. Consignee email
19. Consignor name
20. Consignor address line 1
21. Consignor address line 2
22. Consignor address line 3
23. Consignor address line 4
24. Consignor postcode
25. Consignor contact
26. Consignor telephone
27. Consignor email
28. Delivery service code
29. Collection service code
30. Delivery Date/Time/Date and Time (Returns only what the consignment service requires)
31. Consignment public note
32. Delivery charge
33. Collection charge
34. Hub charge
35. Customer account code
36. Customer name
37. Customer contact
38. Customer telephone
39. Customer own paperwork (true/false)
40. Number of Qtr Pallets
41. Weight of Qtr Pallets
42. Number of Half Pallets
43. Weight of Half Pallets
44. Number of Half OS Pallets
45. Weight of Half OS Pallets
46. Number of Full Pallets
47. Weight of Full Pallets
48. Number of Full OS Pallets
49. Weight of Full OS Pallets
50. Sum Insured
51. Premium Payable
52. In Name Of
53. Insured Email Address
54. Entered By
Each record is separated by a new line (\r\n). Remember, the same data will be returned both via the SOAP method and the export button .
(2) The SOAP method to extract this data is as follows. Site URL on request.
The following is a sample SOAP 1.1 request and response. The placeholders shown need to be replaced with actual values.
POST /TPNImportExport/Service.asmx HTTP/1.1 Host: poseidon Content-Type: text/xml; charset=utf-8 Content-Length: length SOAPAction: "TPNImportExport/ConsignmentExport" <?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <ConsignmentExport xmlns="TPNImportExport"> <Username>string</Username> <Password>string</Password> <DocketNumbers> <string>string</string> <string>string</string> </DocketNumbers> </ConsignmentExport> </soap:Body> </soap:Envelope>
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ConsignmentExportResponse xmlns="TPNImportExport">
<ConsignmentExportResult>string</ConsignmentExportResult>
</ConsignmentExportResponse>
</soap:Body>
</soap:Envelope>
The following is a sample SOAP 1.2 request and response. The placeholders shown need to be replaced with actual values.
POST /TPNImportExport/Service.asmx HTTP/1.1
Host: poseidon
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<ConsignmentExport xmlns="TPNImportExport">
<Username>string</Username>
<Password>string</Password>
<DocketNumbers>
<string>string</string>
<string>string</string>
</DocketNumbers>
</ConsignmentExport>
</soap12:Body>
</soap12:Envelope>HTTP/1.1 200 OK
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length
<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<ConsignmentExportResponse xmlns="TPNImportExport">
<ConsignmentExportResult>string</ConsignmentExportResult>
</ConsignmentExportResponse>
</soap12:Body>
</soap12:Envelope>Result example
<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><ConsignmentExportResponse xmlns="TPNImportExport"><ConsignmentExportResult>1457224,,,,02/03/2010 09:42:17,99,99,86,3 Pallets,17A Gresham Street,,Lincoln,Lincolnshire,LN11PZ ,,,,Hub ,,,,,HUB,,,,ND ,ND ,,,27.0000,0.0000,12.4500,,,,,,3,3,300.00,0,0,0,0,0,0,0,0,0,0,,,\r\n1457226,,,,02/03/2010 11:52:41,99,99,57,ECTL,1 Appleby Close,,Wellingborough,Northamptonshire,NN95YN ,,,,Hub ,,,,,HUB,,,,ECTL,ND ,,,10.0000,0.0000,4.1500,,,,,,1,0,0,1,100.00,0,0,0,0,0,0,0,0,,,\r\n</ConsignmentExportResult></ConsignmentExportResponse></soap:Body></soap:Envelope>
Code example (Windev)
This gets a list of consignment numbers in a date range, gets the details of the consignments and writes a CSV file containing the data -
IF SOAPRun(sitetouse, "FindConsignmentsToExport","TPNImportExport","TPNImportExport/FindConsignmentsToExport","utf-8") THEN
mysoapresult = SOAPGetResult(SOAPXMLResult)
IF mysoapresult<>"" THEN
nStartposition = Position(mysoapresult,"<FindConsignmentsToExportResult>")
nEndposition =Position(mysoapresult,"</FindConsignmentsToExportResult>")
nStartposition=nStartposition+32
lengthofdata = nEndposition - nStartposition
sResultstring=Middle(mysoapresult,nStartposition,lengthofdata)
HOpen(Local_Parameters)
HRead(Local_Parameters,1)
IF NOT HOut(Local_Parameters) THEN
tempusername=NoSpace(Local_Parameters.TPN_Live_Username)
temppassword=NoSpace(Local_Parameters.TPN_LIve_Password)
END
HClose(Local_Parameters)
sFullfilename = EDT_Edit3+EDT_Edit4
mysoapresult = ""
SOAP.Value[1] = tempusername
SOAP.Name[1] = "Username"
SOAP.Type[1] = SOAPStringType
SOAP.Value[2] = temppassword
SOAP.Name[2] = "Password"
SOAP.Type[2] = SOAPStringType
SOAP.Name[3] = "DocketNumbers"
SOAP.Type[3] = SOAPStringType
SOAP.XMLParam[3] = "<DocketNumbers>"+sResultstring+"</DocketNumbers>"
IF SOAPRun(sitetouse,"ConsignmentExport","TPNImportExport","TPNImportExport/ConsignmentExport","utf-8") THEN
mysoapresult = SOAPGetResult(SOAPXMLResult)
IF mysoapresult<>"" THEN
EDT_Edit5 = mysoapresult
nStartposition=Position(mysoapresult,"<ConsignmentExportResult>")
IF nStartposition<>0 THEN
nStartposition = nStartposition + 25
nEndposition = Position(mysoapresult,"</ConsignmentExportResult>")
sBigfile=Middle(mysoapresult,nStartposition,nEndposition-nStartposition)
fSaveText(sFullfilename,sBigfile)
END
ELSE
Info("No result")
END
END