datainit.sql

本系统是一个报表分析查询系统

/****** Object:  Trigger dbo.RptDel    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RptDel]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[RptDel]
GO

/****** 对象:  用户定义的函数 dbo.getGUserList    脚本日期: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getGUserList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getGUserList]
GO

/****** 对象:  用户定义的函数 dbo.getUserGroupList    脚本日期: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getUserGroupList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getUserGroupList]
GO

/****** 对象:  用户定义的函数 dbo.getUserList    脚本日期: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getUserList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[getUserList]
GO

/****** 对象:  用户定义的函数 dbo.DefSplit    脚本日期: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DefSplit]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[DefSplit]
GO

/****** Object:  Stored Procedure dbo.NewEvent    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewEvent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewEvent]
GO

/****** Object:  Stored Procedure dbo.NewFieldControl    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewFieldControl]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewFieldControl]
GO

/****** Object:  Stored Procedure dbo.NewFieldDsec    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewFieldDsec]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewFieldDsec]
GO

/****** Object:  Stored Procedure dbo.NewFilt    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewFilt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewFilt]
GO

/****** Object:  Stored Procedure dbo.NewHeaderFooter    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewHeaderFooter]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewHeaderFooter]
GO

/****** Object:  Stored Procedure dbo.NewLog    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewLog]
GO

/****** Object:  Stored Procedure dbo.NewLogonPrecept    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewLogonPrecept]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewLogonPrecept]
GO

/****** Object:  Stored Procedure dbo.NewRpt    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewRpt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewRpt]
GO

/****** Object:  Stored Procedure dbo.NewRptGroup    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewRptGroup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewRptGroup]
GO

/****** Object:  Stored Procedure dbo.NewTitle    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewTitle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewTitle]
GO

/****** Object:  Stored Procedure dbo.NewUser    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewUser]
GO

/****** Object:  Stored Procedure dbo.NewUserGroup    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewUserGroup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewUserGroup]
GO

/****** Object:  Stored Procedure dbo.NewRight    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NewRight]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[NewRight]
GO

/****** Object:  Stored Procedure dbo.Test    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Test]
GO

/****** Object:  Table [dbo].[Js_EditTest]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_EditTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_EditTest]
GO

/****** Object:  Table [dbo].[Js_Event]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_Event]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_Event]
GO

/****** Object:  Table [dbo].[Js_FieldControl]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_FieldControl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_FieldControl]
GO

/****** Object:  Table [dbo].[Js_FieldDsec]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_FieldDsec]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_FieldDsec]
GO

/****** Object:  Table [dbo].[Js_Filt]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_Filt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_Filt]
GO

/****** Object:  Table [dbo].[Js_HeaderFooter]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_HeaderFooter]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_HeaderFooter]
GO

/****** Object:  Table [dbo].[Js_InterID]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_InterID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_InterID]
GO

/****** Object:  Table [dbo].[Js_Log]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_Log]
GO

/****** Object:  Table [dbo].[Js_LogonPrecept]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_LogonPrecept]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_LogonPrecept]
GO

/****** Object:  Table [dbo].[Js_LogonPreceptClass]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_LogonPreceptClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_LogonPreceptClass]
GO

/****** Object:  Table [dbo].[Js_Right]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_Right]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_Right]
GO

/****** Object:  Table [dbo].[Js_RightData]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_RightData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_RightData]
GO

/****** Object:  Table [dbo].[Js_Rpt]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_Rpt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_Rpt]
GO

/****** Object:  Table [dbo].[Js_RptGroup]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_RptGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_RptGroup]
GO

/****** Object:  Table [dbo].[Js_SysFeildMap]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_SysFeildMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_SysFeildMap]
GO

/****** Object:  Table [dbo].[Js_Table]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_Table]
GO

/****** Object:  Table [dbo].[Js_Title]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_Title]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_Title]
GO

/****** Object:  Table [dbo].[Js_User]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_User]
GO

/****** Object:  Table [dbo].[Js_UserGroup]    Script Date: 2007-06-27 16:15:20 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Js_UserGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Js_UserGroup]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/****** 对象:  用户定义的函数 dbo.DefSplit    脚本日期: 2007-06-27 16:15:23 ******/
create function DefSplit(@SourceSql varchar(8000),@StrSeprate varchar(10))
 returns @temp table(a varchar(100))
--实现Split功能 的函数
--Date    :2007-06-05
--Author :冯孝刚
as 
 begin
  declare @i int
  set @SourceSql=rtrim(ltrim(@SourceSql))
  set @i=charindex(@StrSeprate,@SourceSql)
  while @i>=1
   begin
    insert @temp values(left(@SourceSql,@i-1))
    set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
    set @i=charindex(@StrSeprate,@SourceSql)
   end
  if @SourceSql<>'\'
   insert @temp values(@SourceSql)
  return 
end




GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象:  用户定义的函数 dbo.getGUserList    脚本日期: 2007-06-27 16:15:23 ******/





create function getGUserList(@GroupID int)
 returns varchar(8000)
 as
 begin
 
  declare @UserName varchar(128) 
  declare @UserNameList varchar(8000)
  declare @UserIDList varchar(8000)
  declare @UserIDStr varchar(128)
  declare @UserIDInt int

  set @UserNameList=''
  select @UserIDList=substring(replace(Js_UserList,'^',','),2,len(replace(Js_UserList,'^',','))-2) from Js_UserGroup where Js_GroupID=@GroupID
  
  --定义游标
  declare UserIDs cursor for
   select *  from dbo.DefSplit(@UserIDList,',')

  open UserIDs

  fetch next from UserIDs into @UserIDStr
  while @@fetch_status=0
   begin
    set @UserIDInt=convert(int,@UserIDStr)
    set @UserName=''
    select @UserName=js_UserName from js_user where js_userid=@UserIDInt
    if @UserName<>''
     begin
      set @UserNameList=@UserNameList+@UserName+']['   
     end
    fetch next from UserIDs into @UserIDStr
   end   
  if(len(ltrim(rtrim(@UserNameList)))>1)
   set @UserNameList='['+substring(@UserNameList,1,len(@UserNameList)-1)
  return @UserNameList
 end


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** 对象:  用户定义的函数 dbo.getUserGroupList    脚本日期: 2007-06-27 16:15:23 ******/




create function getUserGroupList(@GroupID int)
 returns varchar(8000)
 as
 begin
 
  declare @UserName varchar(128) 
  declare @UserNameList varchar(8000)
  declare @UserIDList varchar(8000)
  declare @UserIDStr varchar(128)
  declare @UserIDInt int

  set @UserNameList=''
  select @UserIDList=substring(replace(Js_UserGroupList,'^',','),2,len(replace(Js_UserGroupList,'^',','))-2) from Js_User where Js_UserID=@GroupID
  
  --定义游标
  declare UserIDs cursor for
   select *  from dbo.DefSplit(@UserIDList,',')

  open UserIDs

  fetch next from UserIDs into @UserIDStr
  while @@fetch_status=0
   begin
    set @UserIDInt=convert(int,@UserIDStr)
    set @UserName=''
    select @UserName=Js_GroupName from Js_UserGroup where Js_GroupID=@UserIDInt    
    if @UserName<>''
     begin
      set @UserNameList=@UserNameList+@UserName+']['   
     end
    fetch next from UserIDs into @UserIDStr
   end   
  if(len(ltrim(rtrim(@UserNameList)))>1)
   set @UserNameList='['+substring(@UserNameList,1,len(@UserNameList)-1)
  return @UserNameList
 end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON