-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-20
-- ------------------------------
-- 产品购物车表
-- ==============================

--创建产品购物车表
if not exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductCart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [PPFOR_TZB_ProductCart] (
	[cartID] int identity(1,1) not null primary key,
	[memberID] int NOT NULL REFERENCES [PPFOR_TZB_Member]([_ID]) ON DELETE CASCADE,  		--会员ID
	[belongLanguage] int NOT NULL REFERENCES [PPFOR_TZB_Language]([ID]) ON DELETE CASCADE, 	--所属语言
	[proID] int NOT NULL REFERENCES [PPFOR_TZB_Product]([ID]) ON DELETE CASCADE,  			--商品ID
	[goodsID] int NOT NULL default(0),								--货品ID
	[buyNumber] int NOT NULL default (0),							--购买数量
	[buyTime] datetime not null										--购买时间
)
PPFOR_GO;


--是否存在购物记录
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductCart_Exists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductCart_Exists]
PPFOR_GO;
create procedure PPFOR_TZB_ProductCart_Exists(@memberID int,@belongLanguage int,@proID int,
	@goodsID int,@retValue bit out)
as
	begin
		declare @TempID int
		select @TempID=[cartID] from [PPFOR_TZB_ProductCart] where [memberID]=@memberID 
			and [belongLanguage]=@belongLanguage and [proID]=@proID and [goodsID]=@goodsID
		if @TempID>0
			set @retValue=1
		else 
			set @retValue=0
	end
PPFOR_GO;

--添加购物记录
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductCart_Add]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductCart_Add]
PPFOR_GO;
create procedure PPFOR_TZB_ProductCart_Add(@cartID int out,@memberID int,@belongLanguage int,@proID int,
	@goodsID int,@buyNumber int,@buyTime datetime)
as	
	begin
		insert into [PPFOR_TZB_ProductCart] Values(@memberID,@belongLanguage,@proID,@goodsID,
			@buyNumber,@buyTime)
		set @cartID=@@identity	
	end
PPFOR_GO;

--更新购物记录
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductCart_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductCart_Update]
PPFOR_GO;
create procedure PPFOR_TZB_ProductCart_Update(@cartID int,@memberID int,@belongLanguage int,@proID int,
	@goodsID int,@buyNumber int,@buyTime datetime)
as
	begin
		update [PPFOR_TZB_ProductCart] set [buyNumber]=@buyNumber,
			[buyTime]=@buyTime where [cartID]=@cartID and [memberID]=@memberID 
			and [belongLanguage]=@belongLanguage and [proID]=@proID and [goodsID]=@goodsID
	end
PPFOR_GO;

--删除购物记录
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductCart_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductCart_Delete]
PPFOR_GO;
create procedure PPFOR_TZB_ProductCart_Delete(@memberID int,@belongLanguage int,@cartID int)
as
	delete from [PPFOR_TZB_ProductCart] where [memberID]=@memberID 
		and [belongLanguage]=@belongLanguage and [cartID]=@cartID
PPFOR_GO;

--删除购物记录
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductCart_DeleteAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductCart_DeleteAll]
PPFOR_GO;
create procedure PPFOR_TZB_ProductCart_DeleteAll(@memberID int,@belongLanguage int)
as
	delete from [PPFOR_TZB_ProductCart] where [memberID]=@memberID 
		and [belongLanguage]=@belongLanguage 
PPFOR_GO;

--获取购物记录
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductCart_GetModel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductCart_GetModel]
PPFOR_GO;
create procedure PPFOR_TZB_ProductCart_GetModel(@memberID int,@belongLanguage int,@cartID int)
as
	begin
		select [cartID],[memberID],[belongLanguage],[proID],[goodsID],[buyNumber],[buyTime] 
			from [PPFOR_TZB_ProductCart] where [memberID]=@memberID 
			and [belongLanguage]=@belongLanguage and [cartID]=@cartID
	end
PPFOR_GO;

--获取购物记录
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductCart_GetModelByGoods]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductCart_GetModelByGoods]
PPFOR_GO;
create procedure PPFOR_TZB_ProductCart_GetModelByGoods(@memberID int,@belongLanguage int,@proID int,
	@goodsID int)
as
	begin
		select [cartID],[memberID],[belongLanguage],[proID],[goodsID],[buyNumber],[buyTime] 
			from [PPFOR_TZB_ProductCart] where [memberID]=@memberID 
			and [belongLanguage]=@belongLanguage and [proID]=@proID and [goodsID]=@goodsID
	end
PPFOR_GO;

--根据会员ID获取所有购物记录
if exists (select * from sysobjects where id = object_id(N'[PPFOR_TZB_ProductCart_GetModelList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [PPFOR_TZB_ProductCart_GetModelList]
PPFOR_GO;
create procedure PPFOR_TZB_ProductCart_GetModelList(@memberID int,@belongLanguage int)
as
	begin
		select [cartID],[memberID],[belongLanguage],[proID],[goodsID],[buyNumber],[buyTime] 
			from [PPFOR_TZB_ProductCart] where [memberID]=@memberID and [belongLanguage]=@belongLanguage
	end
PPFOR_GO;

