InsertSale

 

--------------------------------------------------
-- InsertSale
--------------------------------------------------
ALTER PROCEDURE InsertSale
    @PKId               INT = NULL OUTPUT,
    @CustomerId         INT = NULL,
    @Status             INT = NULL,
    @OrderDate          DATETIME = NULL,
    @ShippingHandling   MONEY = NULL,
    @ShipToName         NVARCHAR(40) = NULL,
    @Address            NVARCHAR(255) = NULL,
    @Country            NVARCHAR(40) = NULL,
    @PhoneNumber        NVARCHAR(30) = NULL,
    @Fax                NVARCHAR(30) = NULL,
    @SubTotal           MONEY = NULL,
    @Tax                MONEY = NULL,
    @CreditCardType     NVARCHAR(40) = NULL,
    @CreditCardNumber   NVARCHAR(32) = NULL,
    @ExpirationDate     NVARCHAR(30) = NULL,
    @NameOnCard         NVARCHAR(40) = NULL,
    @ItemIdList         NVARCHAR(4000) = NULL,
    @QuantityList       NVARCHAR(4000) = NULL,
    @PriceList          NVARCHAR(4000) = NULL
AS
    SET NOCOUNT ON

    -- GET ADDRESS IDENTIFIER
    -- Retrieve Address reference into @AddressId
    DECLARE @ShipToAddressId INT

    SET XACT_ABORT ON

    BEGIN TRANSACTION
        -- Insert New Shipping Address
        -- Retrieve Shipping Address into @AddressId
        -- CustomerId is NULL for a shipping address
        EXEC InsertAddress @Address,
                           @Country,
                           @PhoneNumber,
                           @Fax,
                           NULL,
                           @ShipToAddressId OUTPUT

        INSERT Orders(CustomerId,
                      Status,
                      OrderDate,
                      ShippingHandling,
                      ShipToName,
                      ShipToAddressId,
                      SubTotal,
                      Tax,
                      CreditCardType,
                      CreditCardNumber,
                      ExpirationDate,
                      NameOnCard)

        SELECT @CustomerId,
               @Status,
               @OrderDate,
               @ShippingHandling,
               @ShipToName,
               @ShipToAddressId,
               @SubTotal,
               @Tax,
               @CreditCardType,
               @CreditCardNumber,
               @ExpirationDate,
               @NameOnCard

        --Get PKId from inserted Order
        SELECT @PKId = @@IDENTITY

        -- Insert Order Detail List
        IF @ItemIdList IS NOT NULL
            EXECUTE InsertOrderDetailsByList @PKId, @ItemIdList, @QuantityList, @PriceList

        -- return @PKId in resultset
        SELECT @PKId 'PKId'

    COMMIT TRANSACTION

    RETURN 0
--------------------------------------------------
===============================================================

------------------------------------------------
-- InsertAddress
------------------------------------------------
ALTER PROCEDURE InsertAddress
    @Address        NVARCHAR(255) = NULL,
    @Country        NVARCHAR(40) = NULL,
    @PhoneNumber    NVARCHAR(30) = NULL,
    @Fax            NVARCHAR(30) = NULL,
    @CustomerId     NVARCHAR(255) = NULL,
    @PKId           INT = NULL OUTPUT
AS
    SET NOCOUNT ON

    -- Insert the new address and then return the
    -- new identifier (PKId)

    INSERT Addresses (Address,
                      Country,
                      PhoneNumber,
                      Fax,
                      CustomerId)

    SELECT @Address,
           @Country,
           @PhoneNumber,
           @Fax,
           @CustomerId

    -- return ID for new Address record
    SELECT @PKId = @@IDENTITY

    RETURN 0
------------------------------------------------
========================================================

 

--------------------------------------------------
-- InsertOrderDetailsByList
--------------------------------------------------
ALTER PROCEDURE InsertOrderDetailsByList
    @OrderId        INT,
    @ItemIdList     NVARCHAR(4000) = NULL,
    @QuantityList   NVARCHAR(4000) = NULL,
    @PriceList      NVARCHAR(4000) = NULL
AS
    SET NOCOUNT ON

    DECLARE @Length INT
    DECLARE @FirstItemIdWord NVARCHAR(4000)
    DECLARE @FirstQuantityWord NVARCHAR(4000)
    DECLARE @FirstPriceWord NVARCHAR(4000)
    DECLARE @ItemId INT
    DECLARE @Quantity INT
    DECLARE @Price MONEY
   
    SELECT @Length = DATALENGTH(@ItemIdList)

    WHILE @Length > 0
    BEGIN
        EXECUTE @Length = PopFirstWord @ItemIdList OUTPUT, @FirstItemIdWord OUTPUT
        EXECUTE PopFirstWord @QuantityList OUTPUT, @FirstQuantityWord OUTPUT
        EXECUTE PopFirstWord @PriceList OUTPUT, @FirstPriceWord OUTPUT

        IF @Length > 0
        BEGIN
            SELECT @ItemId = CONVERT(INT, @FirstItemIdWord)
            SELECT @Quantity = CONVERT(INT, @FirstQuantityWord)
            SELECT @Price = CONVERT(MONEY, @FirstPriceWord)

            EXECUTE InsertOrderDetail @OrderId, @ItemId, @Price, @Quantity
        END
    END
--------------------------------------------------
==============================================================

--------------------------------------------------
-- InsertOrderDetail
--------------------------------------------------
ALTER PROCEDURE InsertOrderDetail
    @OrderId    INT = NULL,
    @ItemId     INT = NULL,
    @UnitPrice  MONEY = NULL,
    @Quantity   INT = NULL
AS
    SET NOCOUNT ON

    -- INSERT OrderItems INFORMATION
    INSERT OrderItems (OrderId,
                       ItemId,
                       UnitPrice,
                       Quantity)

    SELECT @OrderId,
           @ItemId,
           @UnitPrice,
           @Quantity

    RETURN 0
--------------------------------------------------
==========================================

------------------------------------------------
-- PopFirstWord
------------------------------------------------
ALTER PROCEDURE PopFirstWord
    @SourceString   NVARCHAR(4000) = NULL OUTPUT,
    @FirstWord      NVARCHAR(4000) = NULL OUTPUT
AS
    SET NOCOUNT ON

    -- Procedure accepts a comma delimited string as the first parameter
    -- Procedure outputs the first word in the second parameter
    -- Procedure outputs the remainder of the delimeted string in the first parameter
    -- Procedure yields the length of the First Word as the return value

    DECLARE @Oldword        NVARCHAR(4000)
    DECLARE @Length         INT
    DECLARE @CommaLocation  INT

    SELECT @Oldword = @SourceString

    IF NOT @Oldword IS NULL
    BEGIN
        SELECT @CommaLocation = CHARINDEX(',',@Oldword)
        SELECT @Length = DATALENGTH(@Oldword)

        IF @CommaLocation = 0
        BEGIN
            SELECT @FirstWord = @Oldword
            SELECT @SourceString = NULL

            RETURN @Length
        END

        SELECT @FirstWord = SUBSTRING(@Oldword, 1, @CommaLocation -1)
        SELECT @SourceString = SUBSTRING(@Oldword, @CommaLocation + 1, @Length - @CommaLocation)

        RETURN @Length - @CommaLocation
    END

    RETURN 0
------------------------------------------------
=============================================

------------------------------------------------
-- UpdateCustomer
------------------------------------------------
ALTER PROCEDURE UpdateCustomer
    @PKId           INT = NULL,
    @Email          NVARCHAR(50) = NULL,
    @Password       BINARY(24) = NULL,
    @Name           NVARCHAR(40) = NULL,
    @Address        NVARCHAR(255) = NULL,
    @Country        NVARCHAR(40) = NULL,
    @PhoneNumber    NVARCHAR(30) = NULL,
    @Fax            NVARCHAR(30) = NULL
AS
    SET XACT_ABORT ON

    BEGIN TRANSACTION
        -- Update customer address by CustomerId
        EXEC UpdateCustomerAddress @PKId,
                                   @Address,
                                   @Country,
                                   @PhoneNumber,
                                   @Fax

        -- Update customers table with new customer info
        UPDATE Customers
           SET Email = @Email,
               Password = @Password,
               Name = @Name
         WHERE PKId = @PKId
   
    COMMIT TRANSACTION

    RETURN 0
------------------------------------------------
-- unique index on Customers (PKId)
------------------------------------------------
==================================================

------------------------------------------------
-- UpdateCustomerAddress
------------------------------------------------
ALTER PROCEDURE UpdateCustomerAddress
    @CustomerId     Int,
    @Address        NVARCHAR(255) = NULL,
    @Country        NVARCHAR(40) = NULL,
    @PhoneNumber    NVARCHAR(30) = NULL,
    @Fax            NVARCHAR(30) = NULL
AS
    SET NOCOUNT ON

    -- Update an existing address record

    UPDATE Addresses
       SET Address = @Address,
           Country = @Country,
           PhoneNumber = @PhoneNumber,
           Fax = @Fax
     WHERE CustomerId = @CustomerId
       AND CustomerId IS NOT NULL

    RETURN 0
------------------------------------------------
-- duplicate index on Addresses (CustomerId)
------------------------------------------------
===========================================

 

--------------------------------------------------
-- GetBooksByISBN
--------------------------------------------------
ALTER PROCEDURE GetBooksByISBN
    @ISBN NVARCHAR(13)
AS
    SET NOCOUNT ON

   SELECT DISTINCT TOP 50
           i.PKId,
           i.TypeId,
           b.PublisherId,
           b.ISBN,
           i.ImageFileSpec,
           i.Name 'Title',
           i.Description,
           i.UnitPrice,
           i.UnitCost,
           t.Name 'ItemType',
           p.Name 'PublisherName'
      FROM Items i,
           Books b,
           ItemType t,
           Publishers p
     WHERE CHARINDEX(@ISBN, b.ISBN) > 0
       AND i.PKId = b.ItemId
       AND t.PKId = i.TypeId
       AND p.PKId = b.PublisherId
  ORDER BY i.PKId

  RETURN 0
--------------------------------------------------
=============================================

------------------------------------------------
-- GetAddressByCustomerId
------------------------------------------------
ALTER PROCEDURE GetAddressByCustomerId
    @CustomerId     INT,
    @PKId           INT = NULL OUTPUT,
    @Address        NVARCHAR(255) = NULL OUTPUT,
    @Country        NVARCHAR(40) = NULL OUTPUT,
    @PhoneNumber    NVARCHAR(30) = NULL OUTPUT,
    @Fax            NVARCHAR(30) = NULL OUTPUT
AS
    SET NOCOUNT ON

    SELECT @PKId = PKId,
           @Address = Address,
           @Country = Country,
           @PhoneNumber = PhoneNumber,
           @Fax = Fax
      FROM Addresses
     WHERE CustomerId = @CustomerId

    RETURN 0
------------------------------------------------
-- duplicate index on Addresses (CustomerId)
------------------------------------------------
================================================

private SqlCommand GetInsertCommand()
  {
   SqlCommand tmpInsertCommand=new SqlCommand("InsertSale",new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]));
   tmpInsertCommand.CommandType=CommandType.StoredProcedure;
   SqlParameterCollection diaparams=tmpInsertCommand.Parameters;
   diaparams.Add(new SqlParameter("@PKId",SqlDbType.Int)).Direction=ParameterDirection.Output;
   diaparams.Add(new SqlParameter("@CustomerId",SqlDbType.Int));
   
   diaparams.Add(new SqlParameter("@Status",SqlDbType.Int));
   diaparams.Add(new SqlParameter("@OrderDate",SqlDbType.DateTime));
   diaparams.Add(new SqlParameter("@ShippingHandling",SqlDbType.Money));
   diaparams.Add(new SqlParameter("@ShipToName",SqlDbType.NVarChar,40));
   diaparams.Add(new SqlParameter("@Address",SqlDbType.NVarChar,255));
   diaparams.Add(new SqlParameter("@Country",SqlDbType.NVarChar,40));
   diaparams.Add(new SqlParameter("@PhoneNumber",SqlDbType.NVarChar,30));
   diaparams.Add(new SqlParameter("@Fax",SqlDbType.NVarChar,30));
   diaparams.Add(new SqlParameter("@SubTotal",SqlDbType.Money));
   diaparams.Add(new SqlParameter("@Tax",SqlDbType.Money));
   diaparams.Add(new SqlParameter("@CreditCardType",SqlDbType.NVarChar,40));
   diaparams.Add(new SqlParameter("@CreditCardNumber",SqlDbType.NVarChar,32));
   diaparams.Add(new SqlParameter("@ExpirationDate",SqlDbType.NVarChar,30));
   diaparams.Add(new SqlParameter("@NameOnCard",SqlDbType.NVarChar,40));
   diaparams.Add(new SqlParameter("@ItemIdList",SqlDbType.NVarChar,4000));
   diaparams.Add(new SqlParameter("@QuantityList",SqlDbType.NVarChar,4000));
   diaparams.Add(new SqlParameter("@PriceList",SqlDbType.NVarChar,4000));
   return tmpInsertCommand;
  }

 

public Orders()
  {
   insertCommand=GetInsertCommand();
  }

 

using(DataAccess.Orders ordersdataaccess=new prostatute2003.DataAccess.Orders())
    {
     return(ordersdataaccess.InsertOrderDetail(diaorder))>0;
    }

 

public OrderData AddOrder(OrderData diaorder)
  {
   (new BusinessRules.rorder()).InsertOrder(diaorder);
   return diaorder;
  }

 

public void AddOrder()
  {
   diaorderdata=(new OrderSystem()).AddOrder(diaorderdata);
  }

 

private bool SubmitOrder()
  {
   ShoppingCart(false).AddOrder();
   Response.Redirect("order.aspx", false);      
   return false;
  }

 

stage = IsPostBack ? Int32.Parse(ViewState[KEY_STAGE].ToString()) : 0;

public void NextImageButton_Click (object sender, System.Web.UI.ImageClickEventArgs e)
  {
   bool stepSuccess = false;
       
   ShipToNameTextBox.Text = ShipToNameTextBox.Text.Trim();
   AddressTextBox.Text = AddressTextBox.Text.Trim();
   CountryTextBox.Text = CountryTextBox.Text.Trim();
   PhoneNumberTextBox.Text = PhoneNumberTextBox.Text.Trim();
   FaxTextBox.Text = FaxTextBox.Text.Trim(); 
   NameOnCardTextBox.Text = NameOnCardTextBox.Text.Trim();
   CardNumberTextBox.Text = CardNumberTextBox.Text.Trim();
   BillingInfoTextBox.Text = BillingInfoTextBox.Text.Trim();
   foreach (IValidator val in Page.Validators)
   {
    val.Validate();
   }
           
   switch (stage)
   {
    case 0:
     stepSuccess = ValidateShipping();
     break;
                   
    case 1:
     stepSuccess = ValidatePayment();
     break;
                   
    case 2:
     stepSuccess = SubmitOrder();
     break;
   }
           
   if (stepSuccess)
   {
    ++stage;
   }
           
   SetPanelDisplay();
  }

===================================================================

public bool InsertOrder(Common.Data.OrderData diaorder)
  {
   bool a=true;
   DataRow summaryRow=diaorder.Tables[Common.Data.OrderData.ORDER_SUMMARY_TABLE].Rows[0];
   summaryRow.ClearErrors();
   if(CalculateShipping(diaorder)!=(decimal)(summaryRow[OrderData.SHIPPING_HANDLING_FIELD]))
   {
    summaryRow.SetColumnError(OrderData.SHIPPING_HANDLING_FIELD,OrderData.INVALID_FIELD);
    a=false;
   }
   if(CalculateTax(diaorder)!=(decimal)(summaryRow[OrderData.TAX_FIELD]))
   {
    summaryRow.SetColumnError(OrderData.TAX_FIELD,OrderData.INVALID_FIELD);
    a=false;
   }
   a&=IsValidField(diaorder,OrderData.SHIPPING_ADDRESS_TABLE,OrderData.SHIP_TO_NAME_FIELD,40);
   DataRow paymentRow=diaorder.Tables[OrderData.PAYMENT_TABLE].Rows[0];
   paymentRow.ClearErrors();
   a&=IsValidField(paymentRow,OrderData.CREDIT_CARD_TYPE_FIELD,40);
   a&=IsValidField(paymentRow,OrderData.CREDIT_CARD_NUMBER_FIELD,32);
   a&=IsValidField(paymentRow,OrderData.EXPIRATION_DATE_FIELD,30);
   a&=IsValidField(paymentRow,OrderData.BILLING_ADDRESS_FIELD,255);
   DataRowCollection itemrows=diaorder.Tables[OrderData.ORDER_ITEMS_TABLE].Rows;
   decimal subTotal=0;
   foreach(DataRow itemrow in itemrows)
   {
    itemrow.ClearErrors();
    subTotal+=(decimal)(itemrow[OrderData.EXTENDED_FIELD]);//extended=itemtotal
    if((decimal)(itemrow[OrderData.PRICE_FIELD])<=0)
    {
     itemrow.SetColumnError(OrderData.PRICE_FIELD,OrderData.INVALID_FIELD);
     a=false;
    }
    if((short)(itemrow[OrderData.QUANTITY_FIELD])<=0)
    {
     itemrow.SetColumnError(OrderData.QUANTITY_FIELD,OrderData.INVALID_FIELD);
     a=false;
    }
   }
   if(subTotal!=(decimal)(summaryRow[OrderData.SUB_TOTAL_FIELD]))
   {
    summaryRow.SetColumnError(OrderData.SUB_TOTAL_FIELD,OrderData.INVALID_FIELD);
    a=false;
   }
   if(a)
   {
    using(DataAccess.Orders ordersdataaccess=new prostatute2003.DataAccess.Orders())
    {
     return(ordersdataaccess.InsertOrderDetail(diaorder))>0;
    }
   }
   else
    return false;
  }

===========================================================

private bool IsValidField(OrderData diaorder,string tableName,string fieldName,short maxLen)
  {
   return IsValidField(diaorder.Tables[tableName].Rows[0],fieldName,maxLen);//检查定单某个表第一行某个字段，调用下面的函数
  }
  private bool IsValidField(DataRow orderrow,string fieldName,short maxLen)//检查一行记录的某个字段
  {
   short i=(short)(orderrow[fieldName].ToString().Trim().Length);
   if((i<1)||(i>maxLen))
   {
    orderrow.SetColumnError(fieldName,OrderData.INVALID_FIELD);//如果字段的值小于1或大于给定的长度返回错误
    return false;
   }
   else
    return true;
  }

===============================================================