進階開發篇：Commerce Starter Kit 可抄的寶在那裡？

Private Sub SubmitBtn_Click(ByVal sender As Object, 
ByVal e As System.Web.UI.ImageClickEventArgs) Handles SubmitBtn.Click
  Dim cart As ASPNET.StarterKit.Commerce.ShoppingCartDB = 
  New ASPNET.StarterKit.Commerce.ShoppingCartDB()

  '取得shopping cart ID
  Dim cartId As String = cart.GetShoppingCartId()

  '取得customerID
  Dim customerId As String = User.Identity.Name

  If (Not cartId Is Nothing) And (Not customerId Is Nothing) Then

   '下訂單(Place the order)
   Dim ordersDatabase As ASPNET.StarterKit.Commerce.OrdersDB = 
   New ASPNET.StarterKit.Commerce.OrdersDB()
   Dim orderId As Integer = ordersDatabase.PlaceOrder(customerId, cartId)

   Header.Text = "結帳完畢(Check Out Complete)!"
   Message.Text = "<b>您的訂單代號(Order Number)為: </b>" & orderId
   SubmitBtn.Visible = False
  End If
End Sub	

Public Function PlaceOrder(ByVal customerID As String, ByVal cartID As String) As Integer
  '由Web.Config取得資料庫連線字串, 設定預存程序名稱
  Dim myConnection As SqlConnection = 
  New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
  Dim myCommand As SqlCommand = New SqlCommand("CMRC_OrdersAdd", myConnection)

  '設定命令為預存程序
  myCommand.CommandType = CommandType.StoredProcedure

  '輸入預存程序輸入參數值
  Dim parameterCustomerID As SqlParameter = New SqlParameter("@CustomerID", SqlDbType.Int, 4)
  parameterCustomerID.Value = CInt(customerID)
  myCommand.Parameters.Add(parameterCustomerID)

  Dim parameterCartID As SqlParameter = New SqlParameter("@CartID", SqlDbType.NVarChar, 50)
  parameterCartID.Value = cartID
  myCommand.Parameters.Add(parameterCartID)

  Dim parameterShipDate As SqlParameter = New SqlParameter("@ShipDate", SqlDbType.DateTime, 8)
  parameterShipDate.Value = CalculateShippingDate(customerID, cartID)
  myCommand.Parameters.Add(parameterShipDate)

  Dim parameterOrderDate As SqlParameter = New SqlParameter("@OrderDate", SqlDbType.DateTime, 8)
  parameterOrderDate.Value = DateTime.Now
  myCommand.Parameters.Add(parameterOrderDate)

  '預存程序輸出參數
  Dim parameterOrderID As SqlParameter = New SqlParameter("@OrderID", SqlDbType.Int, 4)
  parameterOrderID.Direction = ParameterDirection.Output
  myCommand.Parameters.Add(parameterOrderID)

  '連線資料庫並執行預存程序
  myConnection.Open()
  myCommand.ExecuteNonQuery()
  myConnection.Close()

  '傳回OrderID
  Return CInt(parameterOrderID.Value)
End Function	

CREATE Procedure CMRC_OrdersAdd
(
    @CustomerID int,
    @CartID     nvarchar(50),
    @OrderDate  datetime,        
    @ShipDate   datetime,
    @OrderID    int OUTPUT
)
AS
BEGIN TRAN AddOrder

/* 新增資料到訂單資料表CMRC_Orders */
INSERT INTO CMRC_Orders(CustomerID, OrderDate, ShipDate)
VALUES(@CustomerID, @OrderDate, @ShipDate)
SELECT @OrderID = @@Identity    

/* 複製購物車資料到訂單明細資料表CMRC_OrderDetails */
INSERT INTO CMRC_OrderDetails(OrderID, ProductID, Quantity, UnitCost)
SELECT @OrderID, CMRC_ShoppingCart.ProductID, 
    Quantity, CMRC_Products.UnitCost
FROM CMRC_ShoppingCart 
  INNER JOIN CMRC_Products ON CMRC_ShoppingCart.ProductID = CMRC_Products.ProductID
WHERE CartID = @CartID

/* 移除購物車資料 */
EXEC CMRC_ShoppingCartEmpty @CartID

COMMIT TRAN AddOrder
GO	

'透過Web Services查詢訂單
 _
Public Function CheckStatus(ByVal userName As String, ByVal password As String, 
ByVal orderID As Integer) As ASPNET.StarterKit.Commerce.OrderDetails

  '驗證使用者身份
  Dim accountSystem As ASPNET.StarterKit.Commerce.CustomersDB = 
  New ASPNET.StarterKit.Commerce.CustomersDB()
  Dim customerId As String = 
  accountSystem.Login(userName, ASPNET.StarterKit.Commerce.Security.Encrypt(password))

  If customerId = "" Then
   '驗證失敗
   Throw New Exception("Error: Invalid Login!")
  End If

  '傳回此訂單之明細
  Dim orderSystem As ASPNET.StarterKit.Commerce.OrdersDB = 
  New ASPNET.StarterKit.Commerce.OrdersDB()
  Return orderSystem.GetOrderDetails(orderID, customerId)
End Function

Public Function GetOrderDetails(ByVal orderID As Integer, ByVal customerID As String) 
As OrderDetails

 '由Web.Config取得資料庫連線字串, 設定預存程序名稱
 Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
 Dim myCommand As New SqlDataAdapter("CMRC_OrdersDetail", myConnection)

 '設定命令為預存程序
 myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

 '輸入預存程序輸入參數值
 Dim parameterOrderID As New SqlParameter("@OrderID", SqlDbType.Int, 4)
 parameterOrderID.Value = orderID
 myCommand.SelectCommand.Parameters.Add(parameterOrderID)

 Dim parameterCustomerID As New SqlParameter("@CustomerID", SqlDbType.Int, 4)
 parameterCustomerID.Value = Int32.Parse(customerID)
 myCommand.SelectCommand.Parameters.Add(parameterCustomerID)

 '預存程序輸出參數
 Dim parameterOrderDate As New SqlParameter("@OrderDate", SqlDbType.DateTime, 8)
 parameterOrderDate.Direction = ParameterDirection.Output
 myCommand.SelectCommand.Parameters.Add(parameterOrderDate)

 '預存程序輸出參數
 Dim parameterShipDate As New SqlParameter("@ShipDate", SqlDbType.DateTime, 8)
 parameterShipDate.Direction = ParameterDirection.Output
 myCommand.SelectCommand.Parameters.Add(parameterShipDate)

 '預存程序輸出參數
 Dim parameterOrderTotal As New SqlParameter("@OrderTotal", SqlDbType.Money, 8)
 parameterOrderTotal.Direction = ParameterDirection.Output
 myCommand.SelectCommand.Parameters.Add(parameterOrderTotal)

 '取得資料儲存到DataSet
 Dim myDataSet As New DataSet()
 myCommand.Fill(myDataSet, "OrderItems")

 If Not parameterShipDate.Value Is DBNull.Value Then
   Dim myOrderDetails As New OrderDetails()
   myOrderDetails.OrderDate = CType(parameterOrderDate.Value, DateTime)
   myOrderDetails.ShipDate = CType(parameterShipDate.Value, DateTime)
   myOrderDetails.OrderTotal = CDec(parameterOrderTotal.Value)
   myOrderDetails.OrderItems = myDataSet

   '傳回DataSet
   Return myOrderDetails
 Else
   Return Nothing
 End If
End Function

CREATE Procedure CMRC_OrdersDetail
(
    @OrderID int, @CustomerID int, @OrderDate  datetime OUTPUT,
    @ShipDate datetime OUTPUT, @OrderTotal money OUTPUT
)
AS
SELECT @OrderDate = OrderDate, @ShipDate = ShipDate
FROM CMRC_Orders
WHERE OrderID = @OrderID AND CustomerID = @CustomerID

IF @@Rowcount = 1
  BEGIN
    SELECT  
    @OrderTotal = Cast(SUM(CMRC_OrderDetails.Quantity * CMRC_OrderDetails.UnitCost) as money)
FROM CMRC_OrderDetails WHERE OrderID= @OrderID
SELECT CMRC_Products.ProductID, CMRC_Products.ModelName,
    CMRC_Products.ModelNumber, CMRC_OrderDetails.UnitCost,
    CMRC_OrderDetails.Quantity,
    (CMRC_OrderDetails.Quantity * CMRC_OrderDetails.UnitCost) as ExtendedAmount
FROM CMRC_OrderDetails
  INNER JOIN CMRC_Products ON CMRC_OrderDetails.ProductID = CMRC_Products.ProductID
  
    WHERE OrderID = @OrderID
  END
GO

'透過Web Services下訂單
 _
Public Function OrderItem(ByVal userName As String, ByVal password As String, 
ByVal productID As Integer, ByVal quantity As Integer) As ASPNET.StarterKit.Commerce.OrderDetails

  '驗證使用者身份
  Dim accountSystem As ASPNET.StarterKit.Commerce.CustomersDB = 
  New ASPNET.StarterKit.Commerce.CustomersDB()
  Dim customerId As String = accountSystem.Login(userName, ASPNET.StarterKit.
  Commerce.Security.Encrypt(password))

  If customerId Is Nothing Then
   '驗證失敗
   Throw New Exception("Error: Invalid Login!")
  End If

  Dim qty As Integer = System.Math.Abs(quantity)
  If qty = quantity And qty < 1000 Then
    '新增項目到購物車
    Dim myShoppingCart As ASPNET.StarterKit.Commerce.ShoppingCartDB = 
    New ASPNET.StarterKit.Commerce.ShoppingCartDB()
    myShoppingCart.AddItem(customerId, productID, quantity)

    '下訂單
    Dim orderSystem As ASPNET.StarterKit.Commerce.OrdersDB = 
    New ASPNET.StarterKit.Commerce.OrdersDB()
    Dim orderID As Integer = orderSystem.PlaceOrder(customerId, customerId)

    '傳回訂單明細
    Return orderSystem.GetOrderDetails(orderID, customerId)
  Else
    ' invalid input
    Return Nothing
  End If
End Function