Order Processing at the Supplier | Programming Microsoft SQL Server(TM) 2000 with XML (Pro-Developer)

So what happens to an order once it is received by the supplier? The order data first needs to be inserted into a database. Then the individual items for the order need to be gathered in the warehouse and prepared for shipment. Finally, the details of the order need to be sent to a package delivery company so that the goods can be delivered to the customer.

Inserting the Order in the Supplier Database

As you might recall, when a customer places an order at the online retail site, the application sends the order information as an XML document to the PlaceOrder.asp page in the SupplierSite extranet. The PlaceOrder.asp page receives the order and passes it to a stored procedure, as you can see from the following code:

 Dim strXML Dim cmd Dim con Const adCmdStoredProc = 4 strXML = Request.QueryString("txtOrder") Set con = CreateObject("ADODB.Connection") con.Open "PROVIDER=SQLOLEDB; INTEGRATED SECURITY=SSPI;" & _     "DATA SOURCE=(local);INITIAL CATALOG=SupplierSite;" Set cmd = CreateObject("ADODB.Command") strXML = Replace(strXML, "encoding=""utf-8""", "") With cmd     Set .ActiveConnection = con     .CommandText = "importOrder"     .CommandType = adCmdStoredProc     .Parameters(1).Value = strXML     .Execute End With Set cmd = Nothing Set con = Nothing

Note PlaceOrder.asp also includes code that sends an invoice back to the retailer through BizTalk Server. I'll discuss this aspect of the SupplierSite application in the "BizTalk Server Messaging" section later in this chapter.

The unnecessary encoding information is removed from the document and then the XML purchase order is passed to the importOrder stored procedure in the SupplierSite database, which is defined as follows:

 CREATE PROC ImportOrder @xml ntext AS BEGIN TRAN DECLARE @idoc int EXEC sp_xml_preparedocument @idoc OUTPUT, @xml -- Extract order header details from XML purchase order and insert into  -- Orders table. INSERT Orders     SELECT * FROM OpenXML(@idoc, 'PurchaseOrder/Order', 3)     WITH (OrderNo integer,           OrderDate datetime,           RetailerID varchar(30),           SupplierID varchar(30),           Customer varchar(40),           DeliveryAddress varchar(255),           OrderStatus varchar(40)) -- Extract order details from XML and insert into OrderDetails table. INSERT OrderDetails     SELECT * FROM OpenXML(@idoc, 'PurchaseOrder/Order/OrderDetail', 2)     WITH (OrderNo integer '../@OrderNo',           ProductID int,           Quantity int,           UnitPrice money,           Tax money) EXEC sp_xml_removedocument @idoc COMMIT TRAN

This stored procedure parses the XML document and creates an internal tree representation using the sp_xml_preparedocument system stored procedure and then uses two INSERT statements containing the OpenXML function to insert the data into the Orders and OrderDetails tables.

Preparing Goods for Shipment with the Order Picking Application

Now that the order information is in the SupplierSite database, warehouse employees can use the Order Picking intranet application to prepare the items for shipment. The Order Picking application is implemented as an intranet site in a virtual directory named OrderPicking that retrieves data from a SQLISAPI virtual directory named OrderPickingXML. The need for two virtual directories arises because we want to use an HTML page with frames as the default page in the site, but conventional HTML pages can't be hosted in a SQLISAPI virtual directory. Both virtual directories reference the same physical folder.

Default.htm, which is the home page in the OrderPicking virtual directory, defines three horizontal frames. The top frame contains Header.htm, a static heading for the site. The middle frame retrieves data from the Orders.xml template in the OrderPickingXML virtual directory, and the bottom frame retrieves data from the OrderDetails.xml template in the OrderPickingXML virtual directory. Users arrive at this page by using the URL http://localhost/OrderPicking. Here's the HTML code for Default.htm:

 <HTML>     <HEAD>         <TITLE>Supplier Intranet</TITLE>     </HEAD>     <FRAMESET rows="100,*" framespacing="0" border="0">         <FRAME name='header' target='header' scrolling='0'               src='/books/2/762/1/html/2/Header.htm'/>  <FRAMESET rows="50%,*">             <FRAME name="Orders" target="OrderDetails" scrolling="auto"             src="/books/2/762/1/html/2/http://localhost/OrderPickingXML/Templates/Orders.xml"/>             <FRAME name="OrderDetails" scrolling="auto" src="http://localhost/OrderPickingXML/Templates/OrderDetails.xml"/>         </FRAMESET>         <NOFRAMES>             <BODY>                 <P>This page uses frames, but your browser doesn't                      support them.</P>             </BODY>         </NOFRAMES>     </FRAMESET> </HTML>

The Orders.xml template is defined in the Templates virtual name in the OrderPickingXML virtual directory. It retrieves the list of current orders using a FOR XML query, as shown here:

 <?xml-stylesheet type="text/xsl" href="Orders.xsl"?> <Orders xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <sql:query>         SELECT OrderNo, Customer, OrderDate          FROM Orders CustomerOrder          FOR XML AUTO, ELEMENTS     </sql:query> </Orders>

The XML retrieved by this template looks like this:

 <?xml-stylesheet type="text/xsl" href="Orders.xsl"?> <Orders xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <CustomerOrder>         <OrderNo>1000</OrderNo>         <Customer>C0842D3B-A712-47E5-9045-7A91DC07A6F7</Customer>         <OrderDate>2001-03-13T14:37:58.503</OrderDate>     </CustomerOrder>     <CustomerOrder>         <OrderNo>1001</OrderNo>         <Customer>2F72FEB7-7436-4BA2-B5C5-03EB4275FD5A</Customer>         <OrderDate>2001-03-13T14:38:36.317</OrderDate>     </CustomerOrder>     <CustomerOrder>         <OrderNo>1002</OrderNo>         <Customer>2F72FEB7-7436-4BA2-B5C5-03EB4275FD5A</Customer>         <OrderDate>2001-03-13T17:04:22.460</OrderDate>     </CustomerOrder> </Orders>

This template contains a processing instruction so that the Orders.xsl style sheet will be applied to the XML results by the browser. We can safely rely on the browser to apply the style sheet because the application will only ever be used in an intranet environment in which the use of an XML-aware browser such as Internet Explorer can be mandated. The Orders.xsl style sheet is shown below:

 <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"     version="1.0">     <xsl:template match="/">         <Title>             Customer-Order Browsing         </Title>         <BODY bgproperties="fixed" bgcolor="white">             <P><FONT face="Arial" size="4">                 Choose an order to see details...                 </FONT></P>             <TABLE border="0" width="100%" bordercolor="#009900">                 <TR STYLE="font-size:12pt;                      font-family:Verdana; font-weight:bold;                      text-decoration:underline">                     <TD>Order Number</TD>                     <TD>Customer No</TD>                     <TD>Order Date</TD>                 </TR>                 <xsl:for-each select="Orders/CustomerOrder">                     <TR>                         <TD>                             <A TARGET="OrderDetails">                                 <xsl:attribute name="HREF">                                     OrderDetails.xml?OrderNo=                                     <xsl:value-of select="OrderNo"/>                                 </xsl:attribute>                                 <xsl:value-of select="OrderNo"/>                             </A>                         </TD>                         <TD>                             <xsl:value-of select="Customer"/>                         </TD>                         <TD>                             <xsl:value-of select="OrderDate"/>                         </TD>                     </TR>                 </xsl:for-each>             </TABLE>         </BODY>     </xsl:template> </xsl:stylesheet>

This style sheet creates an HTML document containing a list of orders. Each order number is a link that causes the details for that order to be displayed in the bottom pane of the site by executing the OrderDetails.xml template, which passes the OrderNo field as a parameter.

The OrderDetails.xml template retrieves the details for a particular order as shown here:

 <?xml-stylesheet type="text/xsl" href="OrderDetails.xsl"?> <OrderDetails xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <sql:header>         <sql:param name="OrderNo">1000</sql:param>     </sql:header>     <sql:query>         SELECT OrderNo, LineItem.ProductID, Quantity, ProductName          FROM OrderDetails LineItem          JOIN Products on LineItem.ProductID = Products.ProductID         WHERE LineItem.OrderNo = @OrderNo for XML AUTO, ELEMENTS     </sql:query> </OrderDetails>

The XML results produced by this template look like this:

 <?xml-stylesheet type="text/xsl" href="OrderDetails.xsl"?> <OrderDetails xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <LineItem>         <OrderNo>1000</OrderNo>         <ProductID>1</ProductID>         <Quantity>1</Quantity>         <Products>             <ProductName>Desktop System</ProductName>         </Products>     </LineItem>     <LineItem>         <OrderNo>1000</OrderNo>         <ProductID>9</ProductID>         <Quantity>1</Quantity>         <Products>             <ProductName>ISDN Router</ProductName>         </Products>     </LineItem> </OrderDetails>

Again, the template includes a processing instruction to apply a style sheet. This time the style sheet is OrderDetails.xsl, which you can see here:

 <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"      version="1.0">     <xsl:template match="/">         <HTML>             <HEAD>                 <TITLE>Order Details</TITLE>    <!-- DHTML Scripting -->    <SCRIPT language='vbscript'>    Sub Window_onload        frmDispatch.cmdSubmit.disabled = True    End Sub    Sub validate    Dim bValid    Dim ifields    Dim iField    bValid = True    Set ifields = document.getElementsByTagName("input")    For each iField in ifields        If iField.Type = "checkbox" and iField.checked = False Then            bValid = False        End If    Next    If bValid = True Then        frmDispatch.cmdSubmit.disabled = False    Else        frmDispatch.cmdSubmit.disabled = True    End If    End Sub    </SCRIPT>             </HEAD>             <BODY bgproperties="fixed" bgcolor="white">                 <H3>Pick List</H3>                 <TABLE border="0" bordercolor="#009900" width='100%'>                     <TR STYLE="font-size:12pt; font-family:Verdana;                             font-weight:bold; text-decoration:underline">                         <TD>Product Code</TD>                         <TD>Product Name</TD>                         <TD>Units</TD>                         <TD>Picked?</TD>                     </TR>                     <xsl:for-each select="OrderDetails/LineItem">                     <TR>                         <TD>                             <xsl:value-of select="ProductID"/>                         </TD>                         <TD>                             <xsl:value-of select="Products/ProductName"/>                         </TD>                         <TD>                             <xsl:value-of select="Quantity"/>                         </TD>                         <TD>                             <INPUT type='checkbox' value='off'                                          onclick='validate'/>                         </TD>                     </TR>                     </xsl:for-each>                 </TABLE>                 <P/>                 <FORM name='frmDispatch' id='frmDispatch' method='post'                     action='http://localhost/OrderPicking/Dispatch.asp'>                     <INPUT type='hidden' name='txtOrderNo'>                         <xsl:attribute name='value'>                             <xsl:value-of select=                                 "OrderDetails/LineItem/OrderNo"/>                         </xsl:attribute>                     </INPUT>                     <INPUT id='cmdSubmit' type='submit'                          value='Ready for Dispatch'/>                 </FORM>             </BODY>         </HTML>     </xsl:template> </xsl:stylesheet>

This style sheet includes some client-side DHTML script, which enables or disables a Submit button named Ready For Dispatch depending on whether all the items have been checked. The application uses a form to post the order information to the Dispatch.asp page in the OrderPicking virtual directory when the order has been assembled for shipping.

Figure 9-6 shows the Order Picking application user interface.

Figure 9.6 - The Order Picking application

Sending the Order Details to the Delivery Company

Once an order has been prepared for shipment, the details of that order are sent to the delivery company by the code in Dispatch.asp. Dispatch.asp retrieves the order data from the database as an XML document and uses a style sheet to create an updategram that will be posted to the delivery company's Web site.

Dispatch.asp contains the following code:

 Const XML_HEADER = "<deliverynote " & _     "xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" Const XML_FOOTER = "</deliverynote>" Const adExecuteStream = 1024 Const adWriteChar = 0 Dim strOrderNo strOrderNo = Request.Form("txtOrderNo") Dim strQuery strQuery = XML_HEADER strQuery = strQuery & "<sql:query>" strQuery = strQuery & "SELECT OrderNo reference, " & _     DeliveryAddress delivery_address " strQuery = strQuery & "FROM Orders package WHERE OrderNo = " & _     strOrderno & " FOR XML AUTO, ELEMENTS" strQuery = strQuery & "</sql:query>" strQuery = strQuery & XML_FOOTER 'Execute query using ADO. Dim cmdPackage Dim strmResult Dim strmQuery Set cmdPackage = CreateObject("ADODB.Command") cmdPackage.ActiveConnection = "provider=SQLOLEDB;data source=(local)" & _     ";initial catalog=SupplierSite;integrated security=sspi;" Set strmQuery = CreateObject("ADODB.Stream") strmQuery.Open strmQuery.WriteText strQuery, adWriteChar strmQuery.Position = 0 Set cmdPackage.CommandStream = strmQuery Set strmResult = CreateObject("ADODB.Stream") strmResult.Open cmdPackage.Properties("Output Stream") = strmResult cmdPackage.Properties("XSL") = Server.MapPath("Dispatch.xsl") cmdPackage.Execute , , adExecuteStream Dim strXML strXML = strmResult.ReadText Response.Write strXML

This script includes a query template that retrieves the order data from the SupplierSite database. The order data is retrieved in the following XML format:

 <deliverynote xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <package>         <reference>1000</reference>         <delivery_address>1 Any Street, Anytown</delivery_address>     </package> </deliverynote>

The application uses the Dispatch.xsl style sheet to format this XML data. Here's the Dispatch.xsl style sheet:

 <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"      version="1.0">     <xsl:template match="/">         <HTML>             <HEAD>                 <TITLE>Dispatch Order</TITLE>             </HEAD>             <BODY bgproperties="fixed" bgcolor="white">                 <H3>Arrange Delivery</H3>                 <P>Click Submit to send this delivery request to the                      shipping company</P>                 <BR/>                 <B>Order No:</B>                  <xsl:value-of select="deliverynote/package/reference"/>                 <BR/>                 <B>Address:</B>                  <xsl:value-of select=                     "deliverynote/package/delivery_address"/>                 <BR/>                      <FORM name='frmDelivery' id='frmDelivery' method='post'                      action='http://localhost/DeliveryCo'>                     <INPUT type='hidden' name='xsl' value='Delivery.xsl'/>                     <INPUT type='hidden' name='template'>                         <xsl:attribute name='value'>                         &lt;deliveryrequest xmlns:updg=                             "urn:schemas-microsoft-com:xml-updategram"&gt;;                         &lt;updg:sync&gt;                         &lt;updg:before&gt;                         &lt;/updg:before&gt;                         &lt;updg:after&gt;                         &lt;deliveries customer='1'                          reference='<xsl:value-of                              select="deliverynote/package/reference"/>'                         delivery_address='<xsl:value-of                              select="deliverynote/package/delivery_address"/>'                         package_type='1' shipping_method='2'/&gt;                         &lt;/updg:after&gt;                         &lt;/updg:sync&gt;                         &lt;/deliveryrequest&gt;                         </xsl:attribute>                     </INPUT>                     <INPUT type='submit' value="Send Delivery Request"/>                 </FORM>             </BODY>         </HTML>     </xsl:template> </xsl:stylesheet>

The style sheet generates an HTML page containing a form that posts an XML updategram to the DeliveryCo Web site. The updategram looks like this:

 <deliveryrequest xmlns:updg="urn:schemas-microsoft-com:xml-updategram">     <updg:sync>         <updg:before>         </updg:before>         <updg:after>             <deliveries customer='1'                  reference='1000'                 delivery_address='1 Any Street, Anytown'                 package_type='1' shipping_method='2'/>         </updg:after>     </updg:sync> </deliveryrequest>

The posted updategram inserts a record in the Deliveries table in the DeliveryCo database.

Using an updategram to send delivery requests allows us to update the DeliveryCo database over HTTP. In a production environment, this task is likely to be performed over an HTTPS connection, and the supplier site would need to be authenticated at the DeliveryCo site in some way.