Order Importer - Retrive Orders Page

<?php

//create dataset and set its namespace

function AddFieldToXML($FieldName, $Value)

{

 $FindStr = "&";

 $NewStr  = "&amp;";        

 $Result = str_replace($FindStr, $NewStr, $Value);

 echo "\t\t<$FieldName>$Result</$FieldName>\n";

}

//connect to database

$db_connection = new COM("ADODB.Connection");

$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("SageDemoData.mdb") ." ;DefaultDir=". realpath(".");

$db_connection->open($db_connstr);

//get customer records

$Customers = $db_connection->execute("SELECT * FROM Customers WHERE CustomerID IN(SELECT DISTINCT CustomerID FROM Orders WHERE ExportedToSage=0)");

//get order records

$Orders = $db_connection->execute("SELECT * FROM Orders WHERE ExportedToSage=0");

//get order item records

$OrderItems = $db_connection->execute("SELECT * FROM OrderItems WHERE OrderID IN(SELECT DISTINCT OrderID FROM Orders WHERE ExportedToSage=0)");

//begin outputing XML

echo "<?xml version=\"1.0\" standalone=\"yes\" ?>\n";

echo "<DsOrders xmlns=\"http://www.tempuri.org/DsOrderInfo.xsd\">\n";

//process Customers

$Cust_CustomerID = $Customers->Fields("CustomerID");

$Cust_ContactName = $Customers->Fields("ContactName");

$Cust_Address1 = $Customers->Fields("Address1");

$Cust_City = $Customers->Fields("City");

$Cust_Company = $Customers->Fields("Company");

$Cust_Address2 = $Customers->Fields("Address2");

$Cust_County = $Customers->Fields("County");

$Cust_PostCode = $Customers->Fields("PostCode");

$Cust_Tel = $Customers->Fields("Tel");

$Cust_Fax = $Customers->Fields("Fax");

$Cust_VATNumber = $Customers->Fields("VATNumber");

$Cust_CountryCode = $Customers->Fields("CountryCode");

while (!$Customers->EOF)

{

 echo "\t<Customers>\n";

 //Required Fields

 AddFieldToXML("CustomerID", $Cust_CustomerID->value);

 AddFieldToXML("ContactName", $Cust_ContactName->value);

 AddFieldToXML("Address1", $Cust_Address1->value);

 AddFieldToXML("Town", $Cust_City->value);

 //Optional Fields (Used)

 AddFieldToXML("CompanyName", $Cust_Company->value);

 AddFieldToXML("Address2", $Cust_Address2->value);

 AddFieldToXML("County", $Cust_County->value);

 AddFieldToXML("PostCode", $Cust_PostCode->value);

 AddFieldToXML("Telephone", $Cust_Tel->value);

 AddFieldToXML("Fax", $Cust_Fax->value);

 AddFieldToXML("VatNumber", $Cust_VATNumber->value);

 AddFieldToXML("CountryCode", $Cust_CountryCode->value);

 //Optional Fields (Not Used)

   //AddFieldToXML("EmailAddress", PutValueHere);

 echo "\t</Customers>\n";

 $Customers->MoveNext();

}

//process Orders

$Ord_CustomerID = $Orders->Fields("CustomerID");

$Ord_OrderID = $Orders->Fields("OrderID");

$Ord_OrderDate = $Orders->Fields("OrderDate");

$Ord_DelContactName = $Orders->Fields("DelContactName");

$Ord_DelCompany = $Orders->Fields("DelCompany");

$Ord_DelAddress1 = $Orders->Fields("DelAddress1");

$Ord_DelAddress2 = $Orders->Fields("DelAddress2");

$Ord_City = $Orders->Fields("City");

$Ord_County = $Orders->Fields("County");

$Ord_PostCode = $Orders->Fields("PostCode");

while (!$Orders->EOF)

{

 echo "\t<Orders>\n";

 //Required Fields

 AddFieldToXML("CustomerID", $Ord_CustomerID->value);        

 AddFieldToXML("OrderID", $Ord_OrderID->value);

 AddFieldToXML("OrderDate", $Ord_OrderDate->value);

 //Optional Fields (Used)

 AddFieldToXML("DelContactName", $Ord_DelContactName->value);

 AddFieldToXML("DelCompany", $Ord_DelCompany->value);

 AddFieldToXML("DelAddress1", $Ord_DelAddress1->value);

 AddFieldToXML("DelAddress2", $Ord_DelAddress2->value);

 AddFieldToXML("DelTown", $Ord_City->value);

 AddFieldToXML("DelCounty", $Ord_County->value);

 AddFieldToXML("DelPostCode", $Ord_PostCode->value);

   //Optional Fields (Not Used)

   //AddFieldToXML("Carriage", PutValueHere);

 //AddFieldToXML("CarriageTaxCode", PutValueHere);

 //AddFieldToXML("CcAuthCode", PutValueHere);

 //AddFieldToXML("CcCardType", PutValueHere);

 //AddFieldToXML("CcIssueNo", PutValueHere);

 //AddFieldToXML("CcNameOnCard", PutValueHere);

 //AddFieldToXML("CcNumber", PutValueHere);

 //AddFieldToXML("CcValidFrom", PutValueHere);

 //AddFieldToXML("CcValidTo", PutValueHere);

 //AddFieldToXML("DelCountry", PutValueHere);

 echo "\t</Orders>\n";

 $Orders->MoveNext();

}

//process Order Items

$Itm_OrderID = $OrderItems->Fields("OrderID");

$Itm_Description = $OrderItems->Fields("Description");

$Itm_Price = $OrderItems->Fields("Price");

$Itm_PartNo = $OrderItems->Fields("PartNo");

$Itm_Qty = $OrderItems->Fields("Qty");

while (!$OrderItems->EOF)

{

 echo "\t<OrderItems>\n";

 //Required Fields

 AddFieldToXML("OrderID", $Itm_OrderID->value);

 AddFieldToXML("Description", $Itm_Description->value);

 AddFieldToXML("Price", $Itm_Price->value);

 AddFieldToXML("ProductCode", $Itm_PartNo->value);

 AddFieldToXML("Quantity", $Itm_Qty->value);        

 //Optional Fields (Not Used)

   //AddFieldToXML("TaxCode", PutValueHere);

 echo "\t</OrderItems>\n";

 $OrderItems->MoveNext();

}

//finish outputing XML

echo "</DsOrders>";

//close recordsets and database connection

$Customers->Close();

$Orders->Close();

$OrderItems->Close();

$db_connection->Close();

?>