Order Importer - Retrive Orders Page |
<?xml version="1.0" standalone="yes" ?> <cffunction access="public" name="AddFieldToXML" output="false" returntype="string"> <cfargument name="FieldName" type="string" required="yes"> <cfargument name="Value" type="string" required="yes"> <cfset RetVal = "<" & #FieldName# & ">" & #Replace(Value, "&", "&", "ALL")# & "</" & #FieldName# & ">" > <cfreturn RetVal> </cffunction><DsOrders xmlns="http://www.tempuri.org/DsOrderInfo.xsd"><cfquery name="Customers" datasource="WebXeLOrderImporter"> SELECT * FROM Customers WHERE CustomerID IN(SELECT DISTINCT CustomerID FROM Orders WHERE ExportedToSage=0) </cfquery><cfquery name="Orders" datasource="WebXeLOrderImporter"> SELECT * FROM Orders WHERE ExportedToSage=0 </cfquery><cfquery name="OrderItems" datasource="WebXeLOrderImporter"> SELECT * FROM OrderItems WHERE OrderID IN(SELECT DISTINCT OrderID FROM Orders WHERE ExportedToSage=0) </cfquery><cfoutput query="Customers"> <Customers> <!--- Required Fields --->#AddFieldToXML("CustomerID", CustomerID)# #AddFieldToXML("ContactName", ContactName)# #AddFieldToXML("Address1", Address1)# #AddFieldToXML("Town", City)# <!--- Optional Fields (Used) --->#AddFieldToXML("CompanyName", Company)# #AddFieldToXML("Address2", Address2)# #AddFieldToXML("County", County)# #AddFieldToXML("PostCode", PostCode)# #AddFieldToXML("Telephone", Tel)# #AddFieldToXML("Fax", Fax)# #AddFieldToXML("VatNumber", VATNumber)# #AddFieldToXML("CountryCode", CountryCode)#<!--- Optional Fields (Not Used) EmailAddress ---> </Customers></cfoutput><cfoutput query="Orders"> <Orders> <!--- Required Fields --->#AddFieldToXML("CustomerID", CustomerID)# #AddFieldToXML("OrderID", OrderID)# #AddFieldToXML("OrderDate", OrderDate)# <!--- Optional Fields (Used) --->#AddFieldToXML("DelContactName", DelContactName)# #AddFieldToXML("DelCompany", DelCompany)# #AddFieldToXML("DelAddress1", DelAddress1)# #AddFieldToXML("DelAddress2", DelAddress2)# #AddFieldToXML("DelTown", City)# #AddFieldToXML("DelCounty", County)# #AddFieldToXML("DelPostCode", PostCode)#<!--- Optional Fields (Not Used) Carriage, CarriageTaxCode, CcAuthCode, CcCardType, CcIssueNo, CcNameOnCard, CcNumber, "CcValidFrom, CcValidTo, DelCountry ---> </Orders></cfoutput><cfoutput query="OrderItems"> <OrderItems> <!--- Required Fields --->#AddFieldToXML("OrderID", OrderID)# #AddFieldToXML("Description", Description)# #AddFieldToXML("Price", Price)# #AddFieldToXML("ProductCode", PartNo)# #AddFieldToXML("Quantity", Qty)#<!--- Optional Fields (Not Used) TaxCode ---> </OrderItems></cfoutput> </DsOrders> |