USE SQL2008SBS
GO

--Create invalid order table
CREATE TABLE Orders.InvalidOrders
(PartnerOrder   XML     NOT NULL)
GO

CREATE PROCEDURE asp_processpartnerorders @submitorder XML, @order XML OUTPUT
AS
BEGIN
    DECLARE @dochandle      INT,
            @CustomerID     INT,
            @OrderID        INT,
            @inboundorder   XML,
            @neworder       VARCHAR(MAX)
    
    DECLARE @customer   TABLE
            (CompanyName    VARCHAR(50))
            
    DECLARE @orderitems TABLE
            (SKU         VARCHAR(10),
             Quantity    INT)

    --Rip out the xmlns required by Service Broker, because OPENXML cannot
    -- handle XML with a namespace designation
    SET @inboundorder = CAST(REPLACE(CAST(@submitorder AS VARCHAR(MAX)),' xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2"','') AS XML)

    EXEC sp_xml_preparedocument @docHandle OUTPUT, @inboundorder

    INSERT INTO @customer
    SELECT * 
    FROM OPENXML(@docHandle, N'/Customer',2) 
     WITH (CompanyName VARCHAR(50))

    INSERT INTO @orderitems
    SELECT * 
    FROM OPENXML(@docHandle, N'//OrderLine',2) 
      WITH (SKU         VARCHAR(10),
            Quantity    INT)
        
    EXEC sp_xml_removedocument @docHandle
    
    SELECT @CustomerID = a.CustomerID
    FROM Customers.Customer a INNER JOIN @customer b ON a.CompanyName = b.CompanyName

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO Orders.InvalidOrders
        (PartnerOrder)
        VALUES (@submitorder)
        
        RETURN -1
    END
    
    BEGIN TRY
        INSERT INTO Orders.OrderHeader
        (CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount)
        SELECT @CustomerID, GETDATE(), ST.Subtotal, 0, 5.00
        FROM (SELECT SUM(a.UnitPrice*b.Quantity) Subtotal
            FROM Products.ProductOptions a INNER JOIN @orderitems b ON a.SKU = b.SKU) ST

        SET @OrderID = SCOPE_IDENTITY()
        
        INSERT INTO Orders.OrderDetail
        (OrderID, SKU, Quantity, UnitPrice)
        SELECT @OrderID, a.SKU, a.Quantity, b.UnitPrice
        FROM @orderitems a INNER JOIN Products.ProductOptions b ON a.SKU = b.SKU
    END TRY
    BEGIN CATCH
        INSERT INTO Orders.InvalidOrders
        (PartnerOrder)
        VALUES (@submitorder)
        
        RETURN -1
    END CATCH   

    SELECT @neworder = '<Customer xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
      <CompanyName>' + CompanyName + '</CompanyName>
      <OrderTotal>
        <OrderID>' + CAST(@OrderID AS VARCHAR(20)) + '</OrderID>
        <OrderDate>'
    FROM @customer

    SELECT @neworder += CAST(OrderDate AS VARCHAR(30)) + '</OrderDate>
        <SubTotal>' + CAST(SubTotal AS VARCHAR(20)) + '</SubTotal>
        <ShippingAmount>' + 
        CAST(ShippingAmount AS VARCHAR(20)) + '</ShippingAmount>
        <TaxAmount>' + CAST(TaxAmount AS VARCHAR(20)) + '</TaxAmount>
        <GrandTotal>' + CAST(GrandTotal AS VARCHAR(20)) + '</GrandTotal>
      </OrderTotal>
    </Customer>'
    FROM Orders.OrderHeader
    WHERE OrderID = @OrderID
    
    SET @order = @neworder
END
GO