/****** 对象:  UserDefinedFunction [dbo].[f_addsign]    脚本日期: 04/09/2012 18:43:39 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[f_addsign] (@str nvarchar(1000))
RETURNS nvarchar(1000) 
AS  
--将传入的字符串两边加上逗号
BEGIN 
	Return ',' + @str + ','
END

GO
/****** 对象:  UserDefinedFunction [dbo].[f_BuyLimitPrice]    脚本日期: 04/09/2012 18:43:40 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--CREATE FUNCTION dbo.f_BuyLimitPrice(@productID int,@nowTime dateTime)
--RETURNS decimal(18,2) 
--AS   --获取商品的价格，促销时就获取促销价格，没有促销时就获取原价
--BEGIN
--	Declare @memberPrice decimal(18,2)
--	Declare @temp decimal(18,2)
--	Declare @isBuyLimited int
--	Select @memberPrice = MemberPrice, @isBuyLimited = IsBuyLimited From Product Where ProductID = @productID
--	IF(@isBuyLimited = 1)
--	Begin
--		Select @temp = LimitPrice From BuyLimit Where ProductID = @productID And @nowTime Between StartTime And EndTime And Quantity > 0
--		IF(@temp <> 0 And @temp <> NULL)
--			Set @memberPrice = @temp
--	End
--	Return @memberPrice
--END


CREATE FUNCTION [dbo].[f_BuyLimitPrice](@goodsID int,@nowTime dateTime)
RETURNS decimal(18,2) 
AS   --获取商品的价格，促销时就获取促销价格，没有促销时就获取原价
BEGIN
	Declare @memberPrice decimal(18,2)
	Declare @temp decimal(18,2)
	Declare @isBuyLimited int
	Declare @productID int
	Select @memberPrice = Goods.MemberPrice, @isBuyLimited = IsBuyLimited, @productID = Goods.ProductID From Product,Goods Where GoodsID = @goodsID And Goods.ProductID = Product.ProductID
	IF(@isBuyLimited = 1)
	Begin
		Select @temp = LimitPrice From BuyLimit Where ProductID = @productID And @nowTime Between StartTime And EndTime And Quantity > 0
		IF(@temp <> 0 And @temp <> NULL)
			Set @memberPrice = @temp
	End
	Return @memberPrice
END

GO
/****** 对象:  UserDefinedFunction [dbo].[f_GetCategoryNo]    脚本日期: 04/09/2012 18:43:40 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[f_GetCategoryNo] (@categoryID int)
RETURNS decimal(30,0)
AS
BEGIN
	declare @categoryNo decimal(30,0)
	set @categoryNo = 1
	if exists(select categoryid from category where categoryid = @categoryid)
	begin
		Select @categoryNo = categoryNo From Category Where CategoryID = @categoryID
	end
	return @categoryNo
END


GO
/****** 对象:  UserDefinedFunction [dbo].[f_GetShippingAreaNo]    脚本日期: 04/09/2012 18:43:40 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[f_GetShippingAreaNo] (@ShippingAreaID int)
RETURNS decimal(30,0)
AS
BEGIN
	declare @ShippingAreaNo decimal(30,0)
	if(@ShippingAreaID = 0)
	begin
		set @ShippingAreaNo = 1
	end
	else
	begin
		
		select @ShippingAreaNo = ShippingAreaNo From ShippingArea Where ShippingAreaID = @ShippingAreaID
	end
	return @ShippingAreaNo
END

GO
/****** 对象:  UserDefinedFunction [dbo].[f_getstorage]    脚本日期: 04/09/2012 18:43:41 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[f_getstorage] (@productid int)
returns decimal(30,0)
as
begin
	declare @storage int
	set @storage = 0
	if exists(select productid from product where productid = @productid)
	begin
		if exists(select productid from Goods where productid = @productid)
		begin
			select @storage = sum(storage) from goods where productid = @productid
		end
	end
	return @storage
end



GO
/****** 对象:  UserDefinedFunction [dbo].[f_GetVideoCategoryNo]    脚本日期: 04/09/2012 18:43:41 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[f_GetVideoCategoryNo] (@categoryID int)
RETURNS decimal(30,0)
AS
BEGIN
	declare @categoryNo decimal(30,0)
	set @categoryNo = 1
	if exists(select VideoCatID from VideoCat where VideoCatID = @categoryid)
	begin
		Select @categoryNo = CategoryNo From VideoCat Where VideoCatID = @categoryID
	end
	return @categoryNo
END



GO
/****** 对象:  UserDefinedFunction [dbo].[f_l_removesign]    脚本日期: 04/09/2012 18:43:41 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[f_l_removesign] (@str nvarchar(1000))
RETURNS nvarchar(1000) 
AS
--除去开头的逗号
BEGIN 
	While(@str <> '' And SUBSTRING(@str,1,1) = ',')
	Begin
		IF(Len(@str) > 1)
			Set @str = SUBSTRING(@str,2,Len(@str))
		ElSE
			Set @str = ''
	End
	Return @str
END
GO
/****** 对象:  UserDefinedFunction [dbo].[f_r_removesign]    脚本日期: 04/09/2012 18:43:42 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[f_r_removesign] (@str nvarchar(1000))
RETURNS nvarchar(1000) 
AS
--除去右边的逗号
BEGIN 
	While(@str <> '' And SUBSTRING(@str,Len(@str),Len(@str)) = ',')
	Begin
		IF(Len(@str) > 1)
			Set @str = SUBSTRING(@str,1,Len(@str)-1)
		ElSE
			Set @str = ''
	End
	Return @str
END
GO
/****** 对象:  UserDefinedFunction [dbo].[fnCleanDefaultValue]    脚本日期: 04/09/2012 18:43:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnCleanDefaultValue](@sDefaultValue varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
	RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)
END




GO
/****** 对象:  UserDefinedFunction [dbo].[fnColumnDefault]    脚本日期: 04/09/2012 18:43:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnColumnDefault](@sTableName varchar(128), @sColumnName varchar(128))
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @sDefaultValue varchar(4000)

	SELECT	@sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
	FROM	INFORMATION_SCHEMA.COLUMNS
	WHERE	TABLE_NAME = @sTableName
	 AND 	COLUMN_NAME = @sColumnName

	RETURN 	@sDefaultValue

END


GO
/****** 对象:  UserDefinedFunction [dbo].[fnIsColumnPrimaryKey]    脚本日期: 04/09/2012 18:43:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





CREATE   FUNCTION [dbo].[fnIsColumnPrimaryKey](@sTableName varchar(128), @nColumnName varchar(128))
RETURNS bit
AS
BEGIN
	DECLARE @nTableID int,
		@nIndexID int,
		@i int
	
	SET 	@nTableID = OBJECT_ID(@sTableName)
	
	SELECT 	@nIndexID = indid
	FROM 	sysindexes
	WHERE 	id = @nTableID
	 AND 	indid BETWEEN 1 And 254 
	 AND 	(status & 2048) = 2048
	
	IF @nIndexID Is Null
		RETURN 0
	
	IF @nColumnName IN
		(SELECT sc.[name]
		FROM 	sysindexkeys sik
			INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
		WHERE 	sik.id = @nTableID
		 AND 	sik.indid = @nIndexID)
	 BEGIN
		RETURN 1
	 END


	RETURN 0
END








GO
/****** 对象:  UserDefinedFunction [dbo].[fnTableHasPrimaryKey]    脚本日期: 04/09/2012 18:43:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnTableHasPrimaryKey](@sTableName varchar(128))
RETURNS bit
AS
BEGIN
	DECLARE @nTableID int,
		@nIndexID int
	
	SET 	@nTableID = OBJECT_ID(@sTableName)
	
	SELECT 	@nIndexID = indid
	FROM 	sysindexes
	WHERE 	id = @nTableID
	 AND 	indid BETWEEN 1 And 254 
	 AND 	(status & 2048) = 2048
	
	IF @nIndexID IS NOT Null
		RETURN 1
	
	RETURN 0
END



GO
/****** 对象:  UserDefinedFunction [dbo].[MD5]    脚本日期: 04/09/2012 18:43:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[MD5]
(
    @src varchar(255),-- 源字符串
    @type int = 32-- 加密类型(16/32)
)
RETURNS varchar(255)
WITH EXECUTE AS CALLER
AS
BEGIN

    -- 存放md5加密串(ox)
    DECLARE @smd5 varchar(34)
    -- 加密字符串此处用MD5加密，还有一种是SHA1加密
    SELECT @smd5 = sys.fn_VarBinToHexStr(hashbytes('MD5', @src))
    IF @type=16
        SELECT @smd5 = SUBSTRING(@smd5,11,16)   --16位
    ELSE
        SELECT @smd5 = SUBSTRING(@smd5,3,32)    --32位

    -- 返回加密串
    RETURN @smd5
END

GO

/****** 对象:  UserDefinedFunction [dbo].[fnTableColumnInfo]    脚本日期: 04/09/2012 18:43:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







CREATE       FUNCTION [dbo].[fnTableColumnInfo](@sTableName varchar(128))
RETURNS TABLE
AS
	RETURN
	SELECT	c.name AS sColumnName,
		c.colid AS nColumnID,
		dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn,
		CASE 	WHEN t.name IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar') THEN 1
			WHEN t.name IN ('decimal', 'numeric') THEN 2
			ELSE 0
		END AS nAlternateType,
		c.length AS nColumnLength,
		c.prec AS nColumnPrecision,
		c.scale AS nColumnScale, 
		c.IsNullable, 
		SIGN(c.status & 128) AS IsIdentity,
		t.name as sTypeName,
		dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue
	FROM	syscolumns c 
		INNER JOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype
	WHERE	c.id = OBJECT_ID(@sTableName)









GO
/****** 对象:  UserDefinedFunction [dbo].[func_splitchar]    脚本日期: 04/09/2012 18:43:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[func_splitchar]
(
@SplitString varchar(8000),-- nvarchar(4000)
@Separator varchar(2) = ','
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] varchar(8000)-- nvarchar(4000)
)
AS
BEGIN
    DECLARE @CurrentIndex int;
    DECLARE @NextIndex int;
    DECLARE @ReturnText varchar(8000);-- nvarchar(4000)
    SELECT @CurrentIndex=1;
    WHILE(@CurrentIndex<=len(@SplitString)) 
    BEGIN
        SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
        IF(@NextIndex=0 OR @NextIndex IS NULL)
            SELECT @NextIndex=len(@SplitString)+1;
        
        SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);

        INSERT INTO @SplitStringsTable([value])
        VALUES(@ReturnText);
        
        SELECT @CurrentIndex=@NextIndex+1;
    END
    RETURN;
END

GO
/****** 对象:  UserDefinedFunction [dbo].[func_splitint]    脚本日期: 04/09/2012 18:43:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[func_splitint](@inputstr nvarchar(4000))
Returns @func_splitint table(val int)
as ---------------将1,2,3,4的字符串变成表的样子 结果等同于select dbo.func_splitint('1,2,3,4')
begin
    declare @str nvarchar(4000)
    declare @substr nvarchar(4000)
    declare @ilen int
    declare @istart int
    set @str = rtrim(ltrim(@inputstr))
    set @istart = charindex(',', @str)
    set @ilen = len(@str)
    if @istart > 0
    begin
        set @substr = substring(@str,1, @istart-1)
        set @str = substring(@str, @istart+1, @ilen-@istart)
    end
    else
    begin
        set @substr = @str
        set @str = ''
    end
	set @substr = rtrim(LTRIM(@substr))
	insert @func_splitint select id = cast(@substr as int)
    while len(@str) > 0
	begin
		set @istart = charindex(',', @str)
		set @ilen = len(@str)
		if @istart > 0
		begin
			set @substr = substring(@str,1, @istart-1)
			set @str = substring(@str,@istart+1,@ilen-@istart)
		end
		else
		begin
			set @substr = @str
			set @str = ''
		end
		set @substr = rtrim(ltrim(@substr))
		insert @func_splitint select id = cast(@substr as int)
	end/**/
	return
end






GO
/****** 对象:  UserDefinedFunction [dbo].[func_splitint2]    脚本日期: 04/09/2012 18:43:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[func_splitint2](@inputstr nvarchar(4000))
Returns @SplitIntsTable table([id] int identity(1,1),
value int)
as ---------------将1,2,3,4的字符串变成表的样子 结果等同于select dbo.func_splitint('1,2,3,4')
begin
    declare @str nvarchar(4000)
    declare @substr nvarchar(4000)
    declare @ilen int
    declare @istart int
    set @str = rtrim(ltrim(@inputstr))
    set @istart = charindex(',', @str)
    set @ilen = len(@str)
    if @istart > 0
    begin
        set @substr = substring(@str,1, @istart-1)
        set @str = substring(@str, @istart+1, @ilen-@istart)
    end
    else
    begin
        set @substr = @str
        set @str = ''
    end
	set @substr = rtrim(LTRIM(@substr))
	insert @SplitIntsTable select id = cast(@substr as int)
    while len(@str) > 0
	begin
		set @istart = charindex(',', @str)
		set @ilen = len(@str)
		if @istart > 0
		begin
			set @substr = substring(@str,1, @istart-1)
			set @str = substring(@str,@istart+1,@ilen-@istart)
		end
		else
		begin
			set @substr = @str
			set @str = ''
		end
		set @substr = rtrim(ltrim(@substr))
		insert @SplitIntsTable select id = cast(@substr as int)
	end/**/
	return
end








GO
/****** 对象:  UserDefinedFunction [dbo].[SplitToTable_Func]    脚本日期: 04/09/2012 18:43:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitToTable_Func]
  (
      @SplitString nvarchar(4000),
      @Separator nvarchar(10)=' '
  )
 RETURNS @SplitStringsTable TABLE
 (
  [id] int identity(1,1),
  [value] nvarchar(1000)
 )
 AS
 BEGIN
	IF @SplitString<>''
	BEGIN
     DECLARE @CurrentIndex int
     DECLARE @NextIndex int
     DECLARE @ReturnText nvarchar(50)
     SELECT @CurrentIndex=1
     WHILE(@CurrentIndex<=len(@SplitString)+1)
         BEGIN
             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex)
             IF(@NextIndex=0 OR @NextIndex IS NULL)
                 SELECT @NextIndex=len(@SplitString)+1
                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex)
                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText)
                 SELECT @CurrentIndex=@NextIndex+1
             END
	END
    RETURN
 END


GO
/****** 对象:  View [dbo].[PmtGoodsList]    脚本日期: 04/12/2012 14:15:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PmtGoodsList]
AS
SELECT     g.ProductID, g.GoodsID, g.PmtID, l.PmtName, r.RuleID, r.RuleName, g.PmtNumber, g.Balance, g.LimitedNumber, g.MemberPrice, g.PmtPrice, 
                      l.BeginTime, l.EndTime, r.BasicType, r.SubType, r.GroupIDs, r.BankNo, CASE WHEN g.MemberPrice > g.PmtPrice THEN 1 ELSE 0 END AS islimittimes, 
                      CASE WHEN g.MemberPrice > g.PmtPrice THEN 1 ELSE 0 END AS isspecprice
FROM         dbo.PmtList AS l INNER JOIN
                      dbo.PmtGoods AS g LEFT OUTER JOIN
                      dbo.PmtRule AS r ON 1 = 1 AND g.PmtID = r.PmtID AND r.Status = 1 ON l.PmtID = g.PmtID
WHERE     (l.Status = 1) AND (GETDATE() BETWEEN l.BeginTime AND l.EndTime)



GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "l"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 121
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "g"
            Begin Extent = 
               Top = 6
               Left = 246
               Bottom = 121
               Right = 411
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "r"
            Begin Extent = 
               Top = 126
               Left = 38
               Bottom = 241
               Right = 203
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'PmtGoodsList'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'PmtGoodsList'