/*
  SQL Stored Procedures File Created 2011
	
  Remove Comments RegEx: \/\*(.*)\*\/
  Remove Extra Stuff: SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\n\n 
*/

if  exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bigpage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--最通用的分页存储过程
-- 获取指定页的数据
DROP PROCEDURE [dbo].[bigpage]
GO
CREATE    PROCEDURE [dbo].[bigpage](
    @tblName   varchar(255),       -- 表名
    @strGetFields varchar(500) = '*',  -- 需要返回的列
	@fldName varchar(255)='',      -- 排序的字段名
	@PageSize   int = 10,          -- 页尺寸
	@PageIndex  int = 1,           -- 页码 
	@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回 
	@OrderType bit =1,  -- 设置排序类型, 非 0 值则降序
	@strWhere  varchar(8000) = '' ) -- 查询条件 (注意: 不要加 where) 
	AS
	BEGIN
		declare @strSQL   varchar(8000)       -- 主语句 
		declare @strTmp   varchar(200)        -- 临时变量
		declare @strOrder varchar(400)        -- 排序类型   
		if @doCount != 0 
		   BEGIN   
			   if @strWhere !=''   
				  set @strSQL = 'select count(*) as Total from '+ @tblName +' where '+ @strWhere   
			   else  
				  set @strSQL = 'select count(*) as Total from '+ @tblName +' '
		   END 
		   --以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。

		 --以下的所有代码都是@doCount为0的情况  
		else
			BEGIN 
				  if @OrderType != 0 
						BEGIN  
						 set @strTmp = '<(select min' 
						 set @strOrder = ' order by '+ @fldName +' desc'
						 --如果@OrderType不是0，就执行降序，这句很重要！ 
						END 
				  else  
						BEGIN  
							set @strTmp = '>(select max' 
							set @strOrder = ' order by '+ @fldName +' asc'
						END  
 

				if @PageIndex = 1 
					BEGIN  
						if @strWhere != ''   
							set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName +' where ' + @strWhere + ' ' + @strOrder 
						else 
							set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName +' '+ @strOrder 
						--如果是第一页就执行以上代码，这样会加快执行速度 
					END 
				else 
					BEGIN 
						--以下代码赋予了@strSQL以真正执行的SQL代码 
						declare  @fldName2 varchar(255)
						set @fldName2=@fldName
						declare  @i int 
						set  @i=charindex('.',@fldName)
						if @i>0
						set @fldName2=substring(@fldName,@i+1,100)  
						set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' + @tblName +' where ' + @fldName + ' ' + @strTmp + '('+ @fldName2 + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from '+ @tblName +'' + @strOrder + ') as tblTmp)'+ @strOrder  
						if @strWhere != ''  
							set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName +' where ' + @fldName + ' ' + @strTmp + '('+ @fldName2 + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from '+ @tblName +' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
					END 
			 END   
		--select ( @strSQL)
		exec ( @strSQL)
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[haopage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--最通用的分页存储过程
-- 获取指定页的数据  
DROP PROCEDURE [dbo].[haopage]
GO
CREATE PROCEDURE [dbo].[haopage](  
@tblName   varchar(255),       -- 表名  
@strGetFields varchar(500) = '*',  -- 需要返回的列  
@fldName varchar(100)='',      -- 排序的字段名  
@PageSize   int = 10,          -- 页尺寸  
@PageIndex  int = 1,           -- 页码 
@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回 
@OrderType bit =1,  -- 设置排序类型, 非 0 值则降序  
@strWhere  varchar(8000) = ''  -- 查询条件 (注意: 不要加 where)  
)
AS  
	BEGIN
			declare @strSQL   varchar(8000)       -- 主语句 
			declare @strTmp   varchar(110)        -- 临时变量 
			declare @strOrder varchar(400)        -- 排序类型   
			if @doCount != 0 
				 BEGIN 
				   if @strWhere !='' 
				   set @strSQL = 'select count(*) as Total from ['+ @tblName +'] where '+ @strWhere 
				   else 
				   set @strSQL = 'select count(*) as Total from ['+ @tblName +']'
				 END 
				 --以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。

			 --以下的所有代码都是@doCount为0的情况 
			else 
				BEGIN   
					if @OrderType != 0 
						BEGIN 
						   set @strTmp = '<(select min' 
						   set @strOrder = ' order by ['+ @fldName +'] desc'
						   --如果@OrderType不是0，就执行降序，这句很重要！ 
						END 
					else 
						BEGIN 
						   set @strTmp = '>(select max'
						   set @strOrder = ' order by ['+ @fldName +'] asc'
						END  
 
					if @PageIndex = 1 
						BEGIN 
						   if @strWhere != ''   
							 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['+ @tblName +'] where ' + @strWhere + ' ' + @strOrder 
						   else 
							 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['+ @tblName +'] '+ @strOrder 
						   --如果是第一页就执行以上代码，这样会加快执行速度 
						END 
					else 
						BEGIN 
						   --以下代码赋予了@strSQL以真正执行的SQL代码 
						   set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from [' + @tblName +'] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from ['+ @tblName +']' + @strOrder + ') as tblTmp)'+ @strOrder  
 						   if @strWhere != '' 
							   set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ['+ @tblName +'] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from ['+ @tblName +'] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
						END 
				END   
			--select ( @strSQL)
			exec ( @strSQL)
	END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mfpage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--最通用的分页存储过程 --多字段排序 (mf-More Fields)
-- 获取指定页的数据 
DROP PROCEDURE [dbo].[mfpage]
GO
CREATE  PROCEDURE [dbo].[mfpage](  
@tblName   varchar(255),       -- 表名 
@strGetFields varchar(500) = '*',  -- 需要返回的列  
@fldName varchar(255)='',      -- 排序的字段名  
@PageSize   int = 10,          -- 页尺寸  
@PageIndex  int = 1,           -- 页码  
@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回  
@OrderType bit =1,  -- 设置排序类型, 非 0 值则降序  
@strWhere  varchar(8000) = '',  -- 查询条件 (注意: 不要加 where)  
@pkey varchar(255)=''  -- 第一表主键
)
AS  
BEGIN
		declare @strSQL   varchar(8000)       -- 主语句  
		declare @strOrder varchar(400)        -- 排序类型  
		if @doCount != 0 
			 BEGIN 
			   if @strWhere !='' 
			      set @strSQL = 'select count(*) as Total from '+ @tblName +' where '+ @strWhere 
			   else 
			      set @strSQL = 'select count(*) as Total from '+ @tblName +' '
			 END 
		--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都
		--是@doCount为0的情况 
		else 
			BEGIN  
				if @OrderType != 0 
					BEGIN  
					   set @strOrder = ' order by '+ @fldName +' desc'
					   --如果@OrderType不是0，就执行降序，这句很重要！ 
					END 
				else 
					BEGIN 
					   set @strOrder = ' order by '+ @fldName +' asc'
					END  
 
				if @PageIndex = 1 
					BEGIN 
					   if @strWhere != ''   
						  set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName +' where ' + @strWhere + ' ' + @strOrder 
					   else 
						  set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName +' '+ @strOrder 
					--如果是第一页就执行以上代码，这样会加快执行速度 
					END 
				else 
					BEGIN 
						 --以下代码赋予了@strSQL以真正执行的SQL代码 
						 --declare  @fldName2 varchar(255)
						 --set @fldName2=@fldName
						 --declare  @i int 
						 --set  @i=charindex('.',@fldName)
						 --if @i>0
						 --set @fldName2=substring(@fldName,@i+1,100)  
						 --@fldName2为多表设计
						 set @strSQL='select top '+cast(@PageSize as nvarchar(15)) +' '+@strGetFields+'  from '+ @tblName +' where '+@pkey+' not in(select top '+cast(str((@PageIndex-1)*@PageSize) as nvarchar(15))+ '  '+@pkey+' from '+ @tblName +' ' + @strOrder +' ) '+' ' +@strOrder
						 if @strWhere != '' 
	    					set @strSQL='select top '+cast(@PageSize as nvarchar(15)) +' '+@strGetFields+'  from '+ @tblName +' where '+@pkey+' not in(select top '+cast(str((@PageIndex-1)*@PageSize) as nvarchar(15))+ '  '+@pkey+' from '+ @tblName +' where '+@strWhere+ ' ' + @strOrder +' ) '+' and  '+@strWhere+ ' ' +@strOrder
					END 
			END   
		--select ( @strSQL)
		exec ( @strSQL)
END
GO

IF EXISTS (SELECT *
           FROM   dbo.sysobjects
           WHERE  id = Object_id(N'[dbo].[bk_system_updateversion]')
           AND Objectproperty(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[bk_system_updateversion] 
GO

CREATE PROCEDURE [dbo].[bk_system_updateversion](
                @Version VARCHAR(50))
AS
    BEGIN
        DECLARE  @tmpValue  AS NVARCHAR(100)
        SET @tmpValue = @Version
        update e_setting set appversion=@tmpValue            
    END
GO

/*4.8-4.9
如何使用：    
declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max)  
set @str1 = '1,2,3'  
set @str2 = '1###2###3'  
set @str3 = '1###2###3###'  
select [Value] from [dbo].[SplitString](@str1, ',', 1)  
select [Value] from [dbo].[SplitString](@str2, '###', 1)  
select [Value] from [dbo].[SplitString](@str3, '###', 0)  
*/
IF EXISTS (SELECT *
			FROM   dbo.sysobjects
			WHERE  id = Object_id(N'[dbo].[SplitString]')
					AND xtype IN (N'FN',N'IF',N'TF'))
  DROP FUNCTION [dbo].[SplitString]
GO
CREATE function [dbo].[SplitString]  
(  
    @Input varchar(8000), --input string to be separated  
    @Separator varchar(8000)=',', --a string that delimit the substrings in the input string  
    @RemoveEmptyEntries bit=1 --the return value does not include array elements that contain an empty string  
)  
returns @TABLE table   
(  
    [Id] int identity(1,1),  
    [Value] varchar(8000)  
)   
as  
begin   
    declare @Index int, @Entry varchar(8000)  
    set @Index = charindex(@Separator,@Input)  
	--add @Separator
	if (len(@Input)>0  and @Index>0 )
		begin  
		   set @Input=@Input +@Separator
		end     
    while (@Index>0)  
    begin  
        set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))           
        if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')  
            begin  
                insert into @TABLE([Value]) Values(@Entry)  
            end     
		set @Input = substring(@Input, @Index+1, len(@Input))
        set @Index = charindex(@Separator, @Input)  
    end  
      
    set @Entry=ltrim(rtrim(@Input))  
    if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')  
        begin  
            insert into @TABLE([Value]) Values(@Entry)  
        end   
    return  
 end   
GO
