/*================================================= =====*/
/*Replace nether code¸ü¸ÄÏÂÃæµÄ´úÂë
/*1.²éÕÒÏÂÃæµÄ×Ö·û´®²¢Ì滻ΠªÃ„Ã£Â±Â¾Â»ÃºÂµÃ„Ã‚Â·Â¾Â¶
/* Find "C:\Program Files\Microsoft SQL Server\MSSQL$NetSDK\Data"
/* Replace "You Sql Data folder path"
/*
/*2.²éÕÒÏÂÃæµÄ×Ö·û´®²¢Ì滻ΠªÃ„Ã£Â¼Ã†Ã‹Ã£Â»ÃºÃƒÃ»
/* Find "ROO"
/* Replace "[You computer name]
/*
/* Example: "ROO\ASPNET" replace "MyComputerName\ASPNET"
/*
/*3.²éÕÒÏÂÃæµÄ×Ö·û´®²¢Ì滻ΠªÃ„Ã£Â½Â«Ã’ÂªÃŠÂ¹Ã“ÃƒÂµÃ„Ã“Ã¯Ã‘Ã”
/* Find "us_english"
/* Replace "[language]"
/*
/*
/*4.ÌØÊâµÄ¼¸¸öÓû§Äã±ØÐë²éÕà ’ÂºÃ³ÃŒÃ¦Â»Â»
/* "ROO\IUSR_ROO"
/*
/*5.ÔËÐÐsql´´½¨Êý¾Ý¿â
/*
/* osql -E -S (local) -i ThisFileName.sql
/*================================================= =========*/

/****** Object: Database bids Script Date: 04/06/2002 18:09:29 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'bids')
    DROP DATABASE [bids]
GO

CREATE DATABASE [bids] ON (NAME = N'bids', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$NetSDK\Data\bids.mdf' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'bids_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$NetSDK\Data\bids_log.LDF' , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'bids', N'autoclose', N'true'
GO

exec sp_dboption N'bids', N'bulkcopy', N'false'
GO

exec sp_dboption N'bids', N'trunc. log', N'true'
GO

exec sp_dboption N'bids', N'torn page detection', N'true'
GO

exec sp_dboption N'bids', N'read only', N'false'
GO

exec sp_dboption N'bids', N'dbo use', N'false'
GO

exec sp_dboption N'bids', N'single', N'false'
GO

exec sp_dboption N'bids', N'autoshrink', N'true'
GO

exec sp_dboption N'bids', N'ANSI null default', N'false'
GO

exec sp_dboption N'bids', N'recursive triggers', N'false'
GO

exec sp_dboption N'bids', N'ANSI nulls', N'false'
GO

exec sp_dboption N'bids', N'concat null yields null', N'false'
GO

exec sp_dboption N'bids', N'cursor close on commit', N'false'
GO

exec sp_dboption N'bids', N'default to local cursor', N'false'
GO

exec sp_dboption N'bids', N'quoted identifier', N'false'
GO

exec sp_dboption N'bids', N'ANSI warnings', N'false'
GO

exec sp_dboption N'bids', N'auto create statistics', N'true'
GO

exec sp_dboption N'bids', N'auto update statistics', N'true'
GO

use [bids]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Bid_Item]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Bid] DROP CONSTRAINT FK_Bid_Item
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Sale_Item]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Sale] DROP CONSTRAINT FK_Sale_Item
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Item_Person]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Item] DROP CONSTRAINT FK_Item_Person
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Sale_Person]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Sale] DROP CONSTRAINT FK_Sale_Person
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Seller_Person]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Seller] DROP CONSTRAINT FK_Seller_Person
GO

/****** Object: Stored Procedure dbo.sp_Get_Bid_Details Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Get_Bid_Details]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_Get_Bid_Details]
GO

/****** Object: Stored Procedure dbo.sp_bid_isp Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_bid_isp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_bid_isp]
GO

/****** Object: Stored Procedure dbo.sp_get_highest_bid Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_get_highest_bid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_get_highest_bid]
GO

/****** Object: Stored Procedure dbo.sp_item_dsp Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_item_dsp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_item_dsp]
GO

/****** Object: Stored Procedure dbo.sp_item_isp Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_item_isp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_item_isp]
GO

/****** Object: Stored Procedure dbo.sp_item_sel Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_item_sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_item_sel]
GO

/****** Object: Stored Procedure dbo.sp_item_usp Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_item_usp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_item_usp]
GO

/****** Object: Stored Procedure dbo.sp_items_for_sale Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_items_for_sale]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_items_for_sale]
GO

/****** Object: Stored Procedure dbo.sp_login Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_login]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_login]
GO

/****** Object: Stored Procedure dbo.sp_my_winning_bids Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_my_winning_bids]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_my_winning_bids]
GO

/****** Object: Stored Procedure dbo.sp_person_isp Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_person_isp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_person_isp]
GO

/****** Object: Stored Procedure dbo.sp_person_sel Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_person_sel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_person_sel]
GO

/****** Object: Stored Procedure dbo.sp_person_sel_by_id Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_person_sel_by_id]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_person_sel_by_id]
GO

/****** Object: Stored Procedure dbo.sp_person_usp Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_person_usp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_person_usp]
GO

/****** Object: Stored Procedure dbo.sp_sale_complete Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sale_complete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_sale_complete]
GO

/****** Object: Stored Procedure dbo.sp_sale_isp Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sale_isp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_sale_isp]
GO

/****** Object: Table [dbo].[Bid] Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bid]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Bid]
GO

/****** Object: Table [dbo].[Item] Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Item]
GO

/****** Object: Table [dbo].[Person] Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Person]
GO

/****** Object: Table [dbo].[Sale] Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sale]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Sale]
GO

/****** Object: Table [dbo].[Seller] Script Date: 04/06/2002 18:09:31 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Seller]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Seller]
GO

/****** Object: Login anon Script Date: 04/06/2002 18:09:30 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'anon')
BEGIN
    declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'Northwind', @loginlang = N'us_english'
    if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
        select @logindb = N'master'
    if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
        select @loginlang = @@language
    exec sp_addlogin N'anon', null, @logindb, @loginlang
END
GO

/****** Object: Login ROO\ASPNET Script Date: 04/06/2002 18:09:30 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'ROO\ASPNET')
    exec sp_grantlogin N'ROO\ASPNET'
    exec sp_defaultdb N'ROO\ASPNET', N'master'
    exec sp_defaultlanguage N'ROO\ASPNET', N'us_english'
GO

/****** Object: Login ROO\IUSR_ROO Script Date: 04/06/2002 18:09:30 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'ROO\IUSR_ROO')
    exec sp_grantlogin N'ROO\IUSR_ROO'
    exec sp_defaultdb N'ROO\IUSR_ROO', N'master'
    exec sp_defaultlanguage N'ROO\IUSR_ROO', N'us_english'
GO

/****** Object: User ROO\ASPNET Script Date: 04/06/2002 18:09:31 ******/
if not exists (select * from dbo.sysusers where name = N'ROO\ASPNET' and uid < 16382)
    EXEC sp_grantdbaccess N'ROO\ASPNET', N'ROO\ASPNET'
GO

/****** Object: Table [dbo].[Bid] Script Date: 04/06/2002 18:09:36 ******/
CREATE TABLE [dbo].[Bid] (
    [BidID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [ItemID] [bigint] NOT NULL ,
    [BidderID] [bigint] NOT NULL ,
    [Timestamp] [datetime] NOT NULL ,
    [BidAmount] [money] NOT NULL ,
    [BidChange] [money] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Item] Script Date: 04/06/2002 18:09:39 ******/
CREATE TABLE [dbo].[Item] (
    [ItemID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [ItemName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Description] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AskingPrice] [money] NOT NULL ,
    [NotifyPrice] [money] NULL ,
    [SellerID] [bigint] NOT NULL ,
    [ListingDate] [datetime] NOT NULL ,
    [ExpirationDate] [datetime] NOT NULL ,
    [ItemStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [HighestBid] [money] NULL ,
    [HighestBidNumber] [bigint] NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Person] Script Date: 04/06/2002 18:09:39 ******/
CREATE TABLE [dbo].[Person] (
    [PersonID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [FamilyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [GivenName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [EmailAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Password] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [StreetAddress1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [StreetAddress2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Active] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastLogin] [datetime] NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Sale] Script Date: 04/06/2002 18:09:39 ******/
CREATE TABLE [dbo].[Sale] (
    [SaleID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [ItemID] [bigint] NOT NULL ,
    [WinningBid] [money] NOT NULL ,
    [BuyerID] [bigint] NOT NULL ,
    [SellerApproval] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [BuyerAcceptance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [CompletionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Seller] Script Date: 04/06/2002 18:09:39 ******/
CREATE TABLE [dbo].[Seller] (
    [ID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [PersonID] [bigint] NOT NULL ,
    [ItemsListed] [int] NOT NULL ,
    [ItemsActive] [int] NOT NULL ,
    [LastActivity] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Bid] WITH NOCHECK ADD
    CONSTRAINT [PK_Bid] PRIMARY KEY CLUSTERED
    (
        [BidID]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Item] WITH NOCHECK ADD
    CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
    (
        [ItemID]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
    CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
    (
        [PersonID]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sale] WITH NOCHECK ADD
    CONSTRAINT [PK_Sale] PRIMARY KEY CLUSTERED
    (
        [SaleID]
    ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Seller] WITH NOCHECK ADD
    CONSTRAINT [PK_Seller] PRIMARY KEY CLUSTERED
    (
        [ID]
    ) ON [PRIMARY]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Bid] TO [ROO\ASPNET]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Item] TO [ROO\ASPNET]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Person] TO [ROO\ASPNET]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Sale] TO [ROO\ASPNET]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Seller] TO [ROO\ASPNET]
GO

ALTER TABLE [dbo].[Bid] ADD
    CONSTRAINT [FK_Bid_Item] FOREIGN KEY
    (
        [ItemID]
    ) REFERENCES [dbo].[Item] (
        [ItemID]
    )
GO

ALTER TABLE [dbo].[Item] ADD
    CONSTRAINT [FK_Item_Person] FOREIGN KEY
    (
        [SellerID]
    ) REFERENCES [dbo].[Person] (
        [PersonID]
    )
GO

ALTER TABLE [dbo].[Sale] ADD
    CONSTRAINT [FK_Sale_Item] FOREIGN KEY
    (
        [ItemID]
    ) REFERENCES [dbo].[Item] (
        [ItemID]
    ),
    CONSTRAINT [FK_Sale_Person] FOREIGN KEY
    (
        [BuyerID]
    ) REFERENCES [dbo].[Person] (
        [PersonID]
    )
GO

ALTER TABLE [dbo].[Seller] ADD
    CONSTRAINT [FK_Seller_Person] FOREIGN KEY
    (
        [PersonID]
    ) REFERENCES [dbo].[Person] (
        [PersonID]
    )
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_Get_Bid_Details Script Date: 04/06/2002 18:09:39 ******/

/****** Object: Stored Procedure dbo.sp_Get_Bid_Details Script Date: 03/06/2002 13:46:18 ******/

CREATE proc sp_Get_Bid_Details
    @itemid bigint
as
    Select * from
        Bid
    where
        itemID=@itemid
    order by
        BidAmount
    Desc

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_Get_Bid_Details] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_bid_isp Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_bid_isp Script Date: 03/06/2002 13:46:18 ******/

CREATE proc sp_bid_isp
    @itemid bigint,
    @bidderid bigint,
    @bidamount money,
    @status char(1) output
as
begin
    Declare @highbid as money
    Declare @bidchange as money
    Declare @bidnumber as bigint

    Select @highbid=Max(BidAmount) from Bid where itemid=@itemid
    Select @highbid = isnull(@highbid, 0)
    Select @bidchange = @bidamount-@highbid

    if @bidchange > 0
    begin
        Insert into
            Bid (itemid, bidderid, timestamp, bidamount, bidchange)
        Values (@itemid, @bidderid, getdate(), @bidamount, @bidchange)
        Select @bidnumber=@@identity

        Update Item
            Set HighestBid=@bidamount,
                HighestBidNumber=@bidnumber
        where itemid=@itemid

        select @status='1'
    end
    else
        select @status='0'

end

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_bid_isp] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_get_highest_bid Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_get_highest_bid Script Date: 03/06/2002 13:46:18 ******/

CREATE proc sp_get_highest_bid
    @itemid bigint,
    @highbid money output
as
    Select
        @highbid=Max(BidAmount)
    from
        Bid
    where
        itemid = @itemid

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_get_highest_bid] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/****** Object: Stored Procedure dbo.sp_item_dsp Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_item_dsp Script Date: 03/06/2002 13:46:18 ******/

CREATE PROCEDURE sp_item_dsp
    @itemid bigint
AS
    delete Item
    Where
        ItemID = @itemid

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_item_dsp] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_item_isp Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_item_isp Script Date: 03/06/2002 13:46:18 ******/

CREATE PROCEDURE sp_item_isp
    @name varchar(500),
    @desc varchar(1000),
    @ask money,
    @notify money,
    @personid bigint,
    @exp datetime
AS

Begin
    Insert into Item
        (ItemName, Description, AskingPrice, NotifyPrice, SellerID,
         ListingDate, ExpirationDate, ItemStatus, HighestBid, HighestBidNumber)
    Values
        (@name, @desc, @ask, @notify, @personid,
        getdate(), @exp, '', 0, 0)

    Declare @ItemsListed int
    Declare @ItemsActive int

    Select
        @ItemsListed=ItemsListed,
        @ItemsActive=ItemsActive
    From
        Seller
    Where
        PersonID=@personid

    if @@ROWCOUNT < 1
        Insert into
        Seller (PersonID, ItemsListed, ItemsActive, LastActivity)
        Values (@personid, 1, 1, getdate())
    else
    Begin
        SELECT @ItemsListed = @ItemsListed + 1,
            @ItemsActive = @ItemsActive + 1

        Update Seller
        Set
            ItemsListed = @ItemsListed,
            ItemsActive = @ItemsActive,
            LastActivity = getdate()
        Where
            PersonID = @personid
    End
End

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_item_isp] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_item_sel Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_item_sel Script Date: 03/06/2002 13:46:18 ******/
CREATE proc sp_item_sel
    @sellerid bigint
as
    Select * from Item Where sellerid=@sellerid

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_item_sel] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_item_usp Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_item_usp Script Date: 03/06/2002 13:46:18 ******/

CREATE PROCEDURE sp_item_usp
    @itemid bigint,
    @itemname varchar(500),
    @desc varchar(1000),
    @ask money,
    @notify money
AS

Begin
    Update Item
    SET
        ItemName = @itemname,
        Description = @desc,
        AskingPrice = @ask,
        NotifyPrice = @notify
    Where
        ItemID = @itemid
End

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_item_usp] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_items_for_sale Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_items_for_sale Script Date: 03/06/2002 13:46:18 ******/

CREATE proc sp_items_for_sale
as
    Select * from item
    where itemstatus NOT IN ('Pending', 'Sold')

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_items_for_sale] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_login Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_login Script Date: 03/06/2002 13:46:18 ******/

CREATE PROCEDURE sp_login
    @email varchar(255),
    @pwd varchar(255),
    @GivenName varchar(255) output,
    @PersonID varchar(255) output
AS

Begin
    Select
        @PersonID = personid,
        @GivenName = givenname
    From
        Person
    Where
        EmailAddress = @email AND
        password = @pwd

    if @@ROWCOUNT < 1
        SELECT @givenname='', @PersonID=0
    else
        Update Person set LastLogin=getdate() where personid=@personid
End

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_login] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_my_winning_bids Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_my_winning_bids Script Date: 03/06/2002 13:46:18 ******/

CREATE Proc sp_my_winning_bids
    @personid bigint
as
    SELECT Itemname, Highestbid, Item.ItemID as "ItemID", BidID
    FROM Item INNER JOIN Bid
    ON Item.ItemID = Bid.ItemID
    WHERE bidderid = @personid
    AND itemstatus = 'Pending'
    AND Item.HighestBidNumber = Bid.BidID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_my_winning_bids] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_person_isp Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_person_isp Script Date: 03/06/2002 13:46:18 ******/

CREATE PROCEDURE sp_person_isp
    @email varchar(255),
    @givenname varchar(255),
    @familyname varchar(255),
    @add1 varchar(255),
    @add2 varchar(255),
    @city varchar(255),
    @state varchar(255),
    @zip varchar(10),
    @country varchar(255),
    @pwd varchar(255),
    @personid bigint output
AS

Begin
    Insert into Person
        (familyname, givenname, emailaddress, password, streetaddress1,
         streetaddress2, city, state, postalcode, country, active, lastlogin)
    Values
        (@familyname, @givenname, @email, @pwd, @add1,
        @add2, @city, @state, @zip, @country, 'Y', getdate())

    select @personid=SCOPE_IDENTITY()
End

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_person_isp] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_person_sel Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_person_sel Script Date: 03/06/2002 13:46:19 ******/

CREATE Procedure sp_person_sel
    @email varchar(255),
    @familyName varchar(255) OUTPUT,
    @givenname varchar(255) OUTPUT,
     @password varchar(255) OUTPUT,
    @add1 varchar(255) OUTPUT,
    @add2 varchar(255) OUTPUT,
    @city varchar(255) OUTPUT,
    @state varchar(255) OUTPUT,
    @zip varchar(10) OUTPUT,
    @country varchar(255) OUTPUT,
    @active char(1) output,
    @lastlogin datetime output
AS

SELECT
    @familyname = FamilyName,
    @givenname = givenname,
    @add1 = streetaddress1,
    @add2 = streetaddress2,
    @city = city,
    @state = state,
    @zip = postalcode,
    @country = country,
    @password = password,
    @active = active,
    @lastlogin = lastlogin
FROM Person
WHERE EmailAddress = @Email

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_person_sel] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_person_sel_by_id Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_person_sel_by_id Script Date: 03/06/2002 13:46:19 ******/

CREATE proc sp_person_sel_by_id
    @bidid bigint,
    @givenname varchar(255) OUTPUT,
    @email varchar(255) OUTPUT,
    @city varchar(255) OUTPUT,
    @state varchar(255) OUTPUT,
    @country varchar(255) OUTPUT
AS
SELECT
    @givenname = givenname,
    @email = EmailAddress,
    @city = city,
    @state = state,
    @country = country
FROM Person P, Bid B
WHERE
    BidID = @bidid and
    B.BidderID = P.PersonID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_person_sel_by_id] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_person_usp Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_person_usp Script Date: 03/06/2002 13:46:19 ******/

CREATE PROCEDURE sp_person_usp
    @email varchar(255),
    @givenname varchar(255),
    @familyname varchar(255),
    @add1 varchar(255),
    @add2 varchar(255),
    @city varchar(255),
    @state varchar(255),
    @zip varchar(10),
    @country varchar(255),
    @pwd varchar(255)
AS

Begin
    Update Person
    SET
        givenname = @givenname,
        familyname = @familyname,
        streetaddress1 = @add1,
        streetaddress2 = @add2,
        city = @city,
        state = @state,
        postalcode = @zip,
        country = @country,
        password = @pwd
    Where
        EmailAddress = @email
End

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_person_usp] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_sale_complete Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_sale_complete Script Date: 03/06/2002 13:46:19 ******/

CREATE proc sp_sale_complete
    @itemid bigint,
    @bidamount money
as
    Update Sale
    Set BuyerAcceptance='Y',
        CompletionDate=GetDate()
    Where
        ItemID = @itemid and
        WinningBid = @bidamount

    Update Item
    Set ItemStatus = 'Sold'
    Where itemid= @itemid

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_sale_complete] TO [ROO\ASPNET]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_sale_isp Script Date: 04/06/2002 18:09:40 ******/

/****** Object: Stored Procedure dbo.sp_sale_isp Script Date: 03/06/2002 13:46:19 ******/

create proc sp_sale_isp
@itemid bigint,
@bidid bigint
as
    Insert into Sale
    (ItemID, WinningBid, BuyerID, SellerApproval, BuyerAcceptance, CompletionDate)
    Select @itemid, BidAmount, BidderID, 'Y', 'N', getdate()
    from bid where bidid=@bidid

    Update Item
    Set ItemStatus = 'Pending'
    Where ItemID = @itemid


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[sp_sale_isp] TO [ROO\ASPNET]
GO