-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-21
-- ------------------------------
-- 货品表
-- ==============================

--创建货品表
if not exists(select name from sysobjects where name='PPFOR_TZB_OrderGoods' and type='U')
CREATE TABLE [PPFOR_TZB_OrderGoods] (
	[ID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[orderID] int NOT NULL REFERENCES [PPFOR_TZB_Order]([ID]) ON DELETE CASCADE,	--订单ID
	[productID] int NOT NULL,	 							--所属产品	
    [goodsID] int NOT NULL,									--货品ID
	[name] text not null,									--货品名称
	[code] nvarchar(18) not null,							--货品货号
	[score] int not null default(0),						--积分
	[price] money not null default(0),						--销售价	
	[buyNumber] int not null default(0),					--购买数量	
	[weight] decimal(19,3) not null default(0)				--重量	
)
PPFOR_GO;

--添加货品
if exists(select name from sysobjects where name='PPFOR_TZB_OrderGoods_Add' and type='P')
	drop procedure PPFOR_TZB_OrderGoods_Add
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_OrderGoods_Add(@ID int output,@orderID int,@productID int,@goodsID int,
	@name text,@code nvarchar(18),@score int,@price money,@buyNumber int,@weight decimal(19,3))
 AS 
	INSERT INTO [PPFOR_TZB_OrderGoods]([orderID],[productID],[goodsID],[name],[code],[score],
		[price],[buyNumber],[weight]) 
	VALUES (@orderID,@productID,@goodsID,@name,@code,@score,@price,@buyNumber,@weight)
	if @@ROWCOUNT>0	
		SET @ID = @@IDENTITY
	else
		SET @ID = 0
PPFOR_GO;

--删除货品
if exists(select name from sysobjects where name='PPFOR_TZB_OrderGoods_Delete' and type='P')
	drop procedure PPFOR_TZB_OrderGoods_Delete
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_OrderGoods_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_OrderGoods] WHERE [ID]=@ID
PPFOR_GO;

--获取货品
if exists(select name from sysobjects where name='PPFOR_TZB_OrderGoods_GetModel' and type='P')
	drop procedure PPFOR_TZB_OrderGoods_GetModel
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_OrderGoods_GetModel(@ID int)
 AS 
	SELECT * FROM [PPFOR_TZB_OrderGoods] WHERE [ID]=@ID 
PPFOR_GO;

--获取货品
if exists(select name from sysobjects where name='PPFOR_TZB_OrderGoods_GetModelList' and type='P')
	drop procedure PPFOR_TZB_OrderGoods_GetModelList
PPFOR_GO;
CREATE   PROCEDURE PPFOR_TZB_OrderGoods_GetModelList(@orderID int)
 AS 
	SELECT * FROM [PPFOR_TZB_OrderGoods] WHERE [orderID]=@orderID 
PPFOR_GO;

--更新货品
if exists(select name from sysobjects where name='PPFOR_TZB_OrderGoods_Update' and type='P')
	drop procedure PPFOR_TZB_OrderGoods_Update
PPFOR_GO;
CREATE  PROCEDURE PPFOR_TZB_OrderGoods_Update(@ID int,@orderID int,@name text,@code nvarchar(18),
	@score int,@price money,@buyNumber int,@weight decimal(19,3))
 AS 	
	UPDATE [PPFOR_TZB_OrderGoods] SET [name]=@name,[code]=@code,[price]=@price,[buyNumber]=@buyNumber,
		[weight]=@weight WHERE [orderID]=@orderID and [ID]=@ID
PPFOR_GO;