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

--创建货币表
if not exists (select * from sysobjects where name='PPFOR_TZB_Money')
CREATE TABLE [PPFOR_TZB_Money] (
	[ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[MoneyName] nvarchar (20) COLLATE Chinese_PRC_CS_AS NOT NULL unique,		--货币名称		
	[MoneyCode] char (3) COLLATE Chinese_PRC_CI_AS NOT NULL unique,				--货币代码		
	[MoneySign] varchar (8) COLLATE Chinese_PRC_CI_AS NOT NULL unique,			--货币符号	
	[MoneyRate] decimal (10,4) NOT NULL,										--货币符号	
	[IsEnable]  bit NOT NULL default(0),										--货币符号	
	[IsDefault] bit NOT NULL default(0),										--是否默认货币
	[Order]		int not null default(0)											--排序
) 
PPFOR_GO;

--添加货币
if exists(select name from sysobjects where name='PPFOR_TZB_Money_Add' and type='P')
	drop procedure [PPFOR_TZB_Money_Add]
PPFOR_GO;
CREATE PROCEDURE PPFOR_TZB_Money_Add(@ID int output,@MoneyName nvarchar(20),@MoneyCode char(3),
	@MoneySign varchar(8),@MoneyRate decimal(10,4),@IsEnable bit,@IsDefault bit,@Order int)
 AS 
	begin
		INSERT INTO [PPFOR_TZB_Money]([MoneyName],[MoneyCode],[MoneySign],[MoneyRate],[IsEnable],[IsDefault],
			[Order]) VALUES (@MoneyName,@MoneyCode,@MoneySign,@MoneyRate,@IsEnable,@IsDefault,@Order)	
		SET @ID = @@IDENTITY
		if @ID>0 and @IsDefault=1		
			update [PPFOR_TZB_Money] set [IsDefault]=0 where [ID]<>@ID		
		if @ID>0 and @IsDefault=1 and @IsEnable=0
			update [PPFOR_TZB_Money] set [IsEnable]=1 where [ID]=@ID	
	end
PPFOR_GO;

---删除
if exists(select name from sysobjects where name='PPFOR_TZB_Money_Delete' and type='P')
	drop procedure [PPFOR_TZB_Money_Delete]
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Money_Delete(@ID int)
 AS 
	DELETE [PPFOR_TZB_Money] WHERE [ID]=@ID and [IsDefault]=0
PPFOR_GO;

---检查是否存在该货币名称
if exists(select name from sysobjects where name='PPFOR_TZB_Money_IsExists' and type='P')
	drop procedure [PPFOR_TZB_Money_IsExists]
PPFOR_GO;

CREATE   PROCEDURE PPFOR_TZB_Money_IsExists(@MoneyName nvarchar(20),@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_Money] WHERE [MoneyName]=@MoneyName
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1	
	end
PPFOR_GO;

---检查是否存在默认货币
if exists(select name from sysobjects where name='PPFOR_TZB_Money_IsExistsDefault' and type='P')
	drop procedure [PPFOR_TZB_Money_IsExistsDefault]
PPFOR_GO;

CREATE   PROCEDURE PPFOR_TZB_Money_IsExistsDefault(@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_Money] WHERE [IsDefault]=1
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1	
	end
PPFOR_GO;

---检查是否存在该货币代码
if exists(select name from sysobjects where name='PPFOR_TZB_Money_IsExistsCode' and type='P')
	drop procedure [PPFOR_TZB_Money_IsExistsCode]
PPFOR_GO;

CREATE   PROCEDURE PPFOR_TZB_Money_IsExistsCode(@MoneyCode char(3),@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_Money] WHERE [MoneyCode]=@MoneyCode
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1	
	end
PPFOR_GO;

---检查是否存在该货币符号
if exists(select name from sysobjects where name='PPFOR_TZB_Money_IsExistsSign' and type='P')
	drop procedure [PPFOR_TZB_Money_IsExistsSign]
PPFOR_GO;

CREATE   PROCEDURE PPFOR_TZB_Money_IsExistsSign(@MoneySign varchar(8),@retValue bit out)
AS
	begin
		DECLARE @TempID int
		SELECT @TempID = count(1) FROM [PPFOR_TZB_Money] WHERE [MoneySign]=@MoneySign
		IF @TempID = 0
			set @retValue= 0
		ELSE
			set @retValue= 1	
	end
PPFOR_GO;

--获取货币
if exists(select name from sysobjects where name='PPFOR_TZB_Money_GetModel' and type='P')
	drop procedure [PPFOR_TZB_Money_GetModel]
PPFOR_GO;

CREATE  PROCEDURE PPFOR_TZB_Money_GetModel(@ID int)
 AS 
	SELECT * FROM [PPFOR_TZB_Money] WHERE [ID]=@ID 
PPFOR_GO;

--获取默认货币
if exists(select name from sysobjects where name='PPFOR_TZB_Money_GetDefaultModel' and type='P')
	drop procedure [PPFOR_TZB_Money_GetDefaultModel]
PPFOR_GO;

CREATE  PROCEDURE PPFOR_TZB_Money_GetDefaultModel
 AS 
	SELECT top 1 * FROM [PPFOR_TZB_Money] WHERE [IsDefault]=1 order by [Order] desc, [ID] desc
PPFOR_GO;

---获得所有货币
if exists(select name from sysobjects where name='PPFOR_TZB_Money_GetModelList' and type='P')
	drop procedure [PPFOR_TZB_Money_GetModelList]
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Money_GetModelList
AS	
	SELECT * FROM [PPFOR_TZB_Money] order by [Order] asc,[ID] asc
PPFOR_GO;

--更新货币
if exists(select name from sysobjects where name='PPFOR_TZB_Money_Update' and type='P')
	drop procedure [PPFOR_TZB_Money_Update]
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Money_Update(@ID int,@MoneyName nvarchar(20),@MoneyCode char(3),
	@MoneySign varchar(8),@MoneyRate decimal(10,4),@IsEnable bit,@IsDefault bit,@Order int)
 AS 
	begin
		declare @TempID int

		UPDATE [PPFOR_TZB_Money] SET @TempID=1,[MoneyName] = @MoneyName,[MoneyCode] = @MoneyCode,
		[MoneySign] = @MoneySign,[MoneyRate] = @MoneyRate,[IsEnable] = @IsEnable,
		[IsDefault]=@IsDefault,[Order]=@Order WHERE [ID]=@ID 
	
		if @TempID=1 and @IsDefault=1
			UPDATE [PPFOR_TZB_Money] SET [IsDefault]=0 where [ID]<> @ID	
		if @TempID=1 and @IsDefault=1 and @IsEnable=0
			UPDATE [PPFOR_TZB_Money] SET [IsEnable]=1 where [ID]= @ID	
	end
PPFOR_GO;

--更新货币列表
if exists(select name from sysobjects where name='PPFOR_TZB_Money_UpdateList' and type='P')
	drop procedure [PPFOR_TZB_Money_UpdateList]
PPFOR_GO;

CREATE PROCEDURE PPFOR_TZB_Money_UpdateList(@ID int,@IsEnable bit,@Order int)
 AS 
	begin		
		if @IsEnable=0
			UPDATE [PPFOR_TZB_Money] SET [IsEnable]=@IsEnable,[Order] = @Order 
				WHERE [ID]=@ID and [IsDefault]=0
		else
			UPDATE [PPFOR_TZB_Money] SET [IsEnable]=@IsEnable,[Order] = @Order 
				WHERE [ID]=@ID
	end
PPFOR_GO;