/****** 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

