SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  function f_BlogPostTags(@id int)
returns varchar(8000) 
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret + ',' + tag.TagName from tbl_Tags tag join tbl_EntityToTag pt on tag.TagID=pt.TagID  and pt.EntityID=@id
    set @ret = stuff(@ret,1,1,'')
    return @ret
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create  function f_NickNameByAccount(@Account nvarchar(50))
returns varchar(50) 
as
begin
    declare @ret varchar(50)
  
    select @ret =[NickName]  from Users  where Account=@Account

    return @ret
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function f_getForumAdmins(@id int)
returns varchar(8000) 
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret + ',' + m.Account+'|'+u.NickName
from bbs_Manage m join Users u on m.Account=u.Account where m.ForumID =@ID
    set @ret = stuff(@ret,1,1,'')
    return @ret
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TABLE [dbo].[BBS_Forums] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ParentID] [int] NULL ,
	[Description] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
	[Theme] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SortOrder] [int] NULL ,
	[Locked] [bit] NOT NULL ,
	[Hidden] [bit] NULL ,
	[IsPublic] [bit] NOT NULL ,
	[LastTopicID] [int] NULL ,
	[LastPosted] [datetime] NULL ,
	[LastAccount] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[LastNickName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[NumTopics] [int] NULL ,
	[NumPosts] [int] NULL ,
	[CreateDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BBS_Manage] (
	[ForumID] [int] NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BBS_Personal_Forums] (
	[PersonalModuleID] [int] NOT NULL ,
	[UserID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BBS_Rank] (
	[RankID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[IsStart] [bit] NULL ,
	[MixPosts] [int] NULL ,
	[RankImage] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BBS_Reply] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[TopicID] [int] NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Subject] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Content] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ReplyDate] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[BBS_Topic] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Type] [int] NOT NULL ,
	[Subject] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Content] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[PublishDate] [datetime] NOT NULL ,
	[ForumID] [int] NOT NULL ,
	[PersonalForumID] [int] NOT NULL ,
	[Status] [int] NOT NULL ,
	[ReplyCount] [int] NOT NULL ,
	[ReadCount] [int] NOT NULL ,
	[LastReplyAccount] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[LastReplyDate] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[BBS_UserConfig] (
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[signature] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
	[RankID] [int] NULL ,
	[Approved] [bit] NULL ,
	[Theme] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[NumTopics] [int] NULL ,
	[NumReply] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Bank_Account] (
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Amount] [money] NOT NULL ,
	[Credit] [int] NOT NULL ,
	[InquiryPassword] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ConsumptionPassword] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Freeze] [int] NOT NULL ,
	[TodayConsume] [decimal](18, 0) NOT NULL ,
	[LastConsumeTime] [datetime] NOT NULL ,
	[DateCreated] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Blog_Config] (
	[BlogID] [int] IDENTITY (1, 1) NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ShowPostCount] [int] NULL ,
	[ShowCommentCount] [int] NULL ,
	[DisabledComment] [bit] NULL ,
	[PostShowMode] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Blog_Content] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[BlogID] [int] NOT NULL ,
	[Author] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Title] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[Text] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[TitleUrl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[SourceName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ParentID] [int] NOT NULL ,
	[FeedBackCount] [int] NULL ,
	[PostType] [int] NULL ,
	[EnableComment] [bit] NULL ,
	[Hiding] [bit] NULL ,
	[Price] [int] NULL ,
	[Tags] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[WebCount] [int] NULL ,
	[AggCount] [int] NULL ,
	[WebLastUpdated] [datetime] NULL ,
	[AggLastUpdated] [datetime] NULL ,
	[DateAdded] [datetime] NOT NULL ,
	[DateUpdated] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[NetDisk_Directory] (
	[DirectoryID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Directory] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ParentID] [int] NOT NULL ,
	[Attribute] [int] NOT NULL ,
	[Password] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
	[limits] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Created] [datetime] NOT NULL ,
	[Updated] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NetDisk_Domain] (
	[DomainID] [int] IDENTITY (1, 1) NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Title] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[Advertise] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[RedirectURL] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[Type] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NetDisk_Resource] (
	[FileID] [int] IDENTITY (1, 1) NOT NULL ,
	[DirectoryID] [int] NOT NULL ,
	[Attribute] [int] NOT NULL ,
	[SourceName] [nvarchar] (250) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Name] [nvarchar] (250) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Description] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FileSize] [int] NOT NULL ,
	[Hits] [int] NOT NULL ,
	[Price] [int] NOT NULL ,
	[Tags] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[Created] [datetime] NOT NULL ,
	[Updated] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NetDisk_filekey] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Account] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FileID] [int] NOT NULL ,
	[Filekey] [nvarchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[DownCount] [int] NOT NULL ,
	[EndTime] [datetime] NOT NULL ,
	[AddTime] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Photo_Categories] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Name] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Password] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Limits] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
	[PhotoType] [int] NULL ,
	[MainPhotoID] [int] NULL ,
	[NumPhotos] [int] NULL ,
	[Updated] [datetime] NULL ,
	[created] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Photo_Data] (
	[PhotoID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryID] [int] NOT NULL ,
	[image] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[title] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[SmallPic] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[PhotoSize] [int] NULL ,
	[Width] [int] NULL ,
	[height] [int] NULL ,
	[detail] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[IsCover] [int] NULL ,
	[Order] [int] NULL ,
	[Tags] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[created] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[User_Amt] (
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[moneyBean] [money] NOT NULL ,
	[credit] [int] NOT NULL ,
	[present] [money] NULL ,
	[Reward] [money] NOT NULL ,
	[NowLoan] [money] NULL ,
	[freeze] [bit] NULL ,
	[InquiryPassword] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ConsumptionPassword] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[User_Group] (
	[GroupID] [int] IDENTITY (1, 1) NOT NULL ,
	[GroupName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[TotalSize] [int] NOT NULL ,
	[MonthPrice] [int] NOT NULL ,
	[DefaultGroup] [bit] NULL ,
	[Active] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[User_Links] (
	[LinkID] [int] IDENTITY (1, 1) NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[LinkName] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[UrlAddress] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[NewWindow] [bit] NOT NULL ,
	[LinkType] [int] NOT NULL ,
	[Description] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[User_List] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ListType] [int] NOT NULL ,
	[Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Parameter] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[SourceUrl] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[Description] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[User_UserConfig] (
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[GroupID] [int] NULL ,
	[GuestLimits] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Encoding] [int] NULL ,
	[Server] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[AvatarImage] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[Theme] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[Language] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[EnableBlog] [bit] NULL ,
	[EnableDisk] [bit] NULL ,
	[Description] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
	[Layout] [varbinary] (7500) NULL ,
	[Created] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
	[UserID] [int] IDENTITY (1, 1) NOT NULL ,
	[Username] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Account] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[NickName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[Email] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Gender] [int] NULL ,
	[Password] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[IDNumber] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Phone] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Answer] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Address] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[City] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Question] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Birthday] [smalldatetime] NULL ,
	[Active] [int] NULL ,
	[RegIP] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[LastIP] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[DateLastLogin] [datetime] NULL ,
	[Created] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[bank_OrderRecord] (
	[RecordID] [int] NOT NULL ,
	[OrderID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[bank_record] (
	[ID] [int] NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[IPAddress] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[AddTime] [datetime] NULL ,
	[Type] [int] NULL ,
	[Description] [ntext] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[blog_Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[Title] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Active] [bit] NOT NULL ,
	[BlogID] [int] NOT NULL ,
	[CategoryType] [tinyint] NULL ,
	[Description] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ParentID] [int] NULL ,
	[UpdateTime] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[blog_Links] (
	[LinkID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryID] [int] NULL ,
	[BlogID] [int] NOT NULL ,
	[PostID] [int] NULL ,
	[NewWindow] [bit] NULL ,
	[Active] [bit] NULL ,
	[Updated] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[blog_ViewCount_bak] (
	[PostID] [int] NOT NULL ,
	[BlogID] [int] NOT NULL ,
	[WebCount] [int] NOT NULL ,
	[AggCount] [int] NOT NULL ,
	[WebLastUpdated] [datetime] NULL ,
	[AggLastUpdated] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_Admin] (
	[AdminID] [int] IDENTITY (1, 1) NOT NULL ,
	[Username] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Password] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_DisallowedWords] (
	[DisallowedWord] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Type] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_EntityToTag] (
	[EntityID] [int] NOT NULL ,
	[TagID] [int] NOT NULL ,
	[TagType] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_Friends] (
	[UserID] [int] NOT NULL ,
	[FID] [int] NOT NULL ,
	[FriendType] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_SiteSettings] (
	[SiteID] [int] IDENTITY (1, 1) NOT NULL ,
	[disabledSpaces] [bit] NOT NULL ,
	[Settings] [image] NOT NULL ,
	[SpacesVersion] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_Tags] (
	[TagID] [int] IDENTITY (1, 1) NOT NULL ,
	[TagName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[Hits] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_Visitor] (
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[VisitorAccount] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[VisitorDate] [datetime] NOT NULL ,
	[IPAddress] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_order] (
	[OrderID] [int] NOT NULL ,
	[Account] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tedst] (
	[鸟] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BBS_Forums] WITH NOCHECK ADD 
	CONSTRAINT [PK_BBS_Personal_Module] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[BBS_Manage] WITH NOCHECK ADD 
	CONSTRAINT [PK_BBS_Forum_User] PRIMARY KEY  CLUSTERED 
	(
		[ForumID],
		[Account]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[BBS_Personal_Forums] WITH NOCHECK ADD 
	CONSTRAINT [PK_BBS_Personal_Module_Auth] PRIMARY KEY  CLUSTERED 
	(
		[PersonalModuleID],
		[UserID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[BBS_Reply] WITH NOCHECK ADD 
	CONSTRAINT [PK_BBS_Reply] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[BBS_Topic] WITH NOCHECK ADD 
	CONSTRAINT [PK_BBS_Main] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[BBS_UserConfig] WITH NOCHECK ADD 
	CONSTRAINT [PK_BBS_UserConfig] PRIMARY KEY  CLUSTERED 
	(
		[Account]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Bank_Account] WITH NOCHECK ADD 
	CONSTRAINT [PK_Bank_Account] PRIMARY KEY  CLUSTERED 
	(
		[Account]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Blog_Config] WITH NOCHECK ADD 
	CONSTRAINT [PK_Blog_Config] PRIMARY KEY  CLUSTERED 
	(
		[BlogID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Blog_Content] WITH NOCHECK ADD 
	CONSTRAINT [PK_Blog_Content] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NetDisk_Directory] WITH NOCHECK ADD 
	CONSTRAINT [PK_NetDisk_Directory] PRIMARY KEY  CLUSTERED 
	(
		[Account],
		[Directory]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NetDisk_Domain] WITH NOCHECK ADD 
	CONSTRAINT [PK_NetDisk_Domain] PRIMARY KEY  CLUSTERED 
	(
		[Account]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NetDisk_Resource] WITH NOCHECK ADD 
	CONSTRAINT [PK_NetDisk_Resource] PRIMARY KEY  CLUSTERED 
	(
		[FileID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NetDisk_filekey] WITH NOCHECK ADD 
	CONSTRAINT [PK_NetDisk_filekey] PRIMARY KEY  CLUSTERED 
	(
		[Account],
		[Filekey]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Photo_Categories] WITH NOCHECK ADD 
	CONSTRAINT [PK_Photo_Categories] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Photo_Data] WITH NOCHECK ADD 
	CONSTRAINT [PK_Photo_Data] PRIMARY KEY  CLUSTERED 
	(
		[PhotoID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_Amt] WITH NOCHECK ADD 
	CONSTRAINT [PK_User_Amt] PRIMARY KEY  CLUSTERED 
	(
		[Account]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_Group] WITH NOCHECK ADD 
	CONSTRAINT [PK_User_Group] PRIMARY KEY  CLUSTERED 
	(
		[GroupID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_Links] WITH NOCHECK ADD 
	CONSTRAINT [PK_User_Links] PRIMARY KEY  CLUSTERED 
	(
		[LinkID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_List] WITH NOCHECK ADD 
	CONSTRAINT [PK_User_List] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[User_UserConfig] WITH NOCHECK ADD 
	CONSTRAINT [PK_User_UserConfig] PRIMARY KEY  CLUSTERED 
	(
		[Account]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD 
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[UserID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[bank_OrderRecord] WITH NOCHECK ADD 
	CONSTRAINT [PK_bank_OrderRecord] PRIMARY KEY  CLUSTERED 
	(
		[RecordID],
		[OrderID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[bank_record] WITH NOCHECK ADD 
	CONSTRAINT [PK_bank_record] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[blog_Categories] WITH NOCHECK ADD 
	CONSTRAINT [PK_blog_Categories] PRIMARY KEY  CLUSTERED 
	(
		[CategoryID] DESC 
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[blog_Links] WITH NOCHECK ADD 
	CONSTRAINT [PK_blog_Links] PRIMARY KEY  CLUSTERED 
	(
		[LinkID] DESC 
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[blog_ViewCount_bak] WITH NOCHECK ADD 
	CONSTRAINT [PK_blog_EntryViewCount] PRIMARY KEY  CLUSTERED 
	(
		[PostID],
		[BlogID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_Admin] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_Admin] PRIMARY KEY  CLUSTERED 
	(
		[AdminID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_DisallowedWords] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_DisallowedWords] PRIMARY KEY  CLUSTERED 
	(
		[DisallowedWord],
		[Type]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_EntityToTag] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_EntityToTag] PRIMARY KEY  CLUSTERED 
	(
		[EntityID],
		[TagID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_Friends] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_Friends] PRIMARY KEY  CLUSTERED 
	(
		[UserID],
		[FID],
		[FriendType]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_Tags] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_Tags] PRIMARY KEY  CLUSTERED 
	(
		[TagName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_Visitor] WITH NOCHECK ADD 
	CONSTRAINT [PK_Spaces_Visitor] PRIMARY KEY  CLUSTERED 
	(
		[Account],
		[VisitorAccount]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tbl_order] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_order] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[BBS_Forums] ADD 
	CONSTRAINT [DF_BBS_Forums_ParentID] DEFAULT (0) FOR [ParentID],
	CONSTRAINT [DF_BBS_Forums_Description] DEFAULT ('') FOR [Description],
	CONSTRAINT [DF_BBS_Forums_Theme] DEFAULT ('default') FOR [Theme],
	CONSTRAINT [DF_BBS_Forums_SortOrder] DEFAULT (1) FOR [SortOrder],
	CONSTRAINT [DF_BBS_Forums_Locked] DEFAULT (0) FOR [Locked],
	CONSTRAINT [DF_BBS_Forums_Hidden] DEFAULT (0) FOR [Hidden],
	CONSTRAINT [DF_BBS_Personal_Module_IsPublic] DEFAULT (0) FOR [IsPublic],
	CONSTRAINT [DF_BBS_Forums_LastTopicID] DEFAULT (0) FOR [LastTopicID],
	CONSTRAINT [DF_BBS_Forums_LastPosted] DEFAULT (getdate()) FOR [LastPosted],
	CONSTRAINT [DF_BBS_Forums_LastAccount] DEFAULT ('') FOR [LastAccount],
	CONSTRAINT [DF_BBS_Forums_LastNickName] DEFAULT ('') FOR [LastNickName],
	CONSTRAINT [DF_BBS_Forums_NumTopics] DEFAULT (0) FOR [NumTopics],
	CONSTRAINT [DF_BBS_Forums_NumPosts] DEFAULT (0) FOR [NumPosts],
	CONSTRAINT [DF_BBS_Personal_Module_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[BBS_Personal_Forums] ADD 
	CONSTRAINT [DF_BBS_Personal_Module_Auth_PersonalModuleID] DEFAULT ((-1)) FOR [PersonalModuleID],
	CONSTRAINT [DF_BBS_Personal_Module_Auth_UserID] DEFAULT ((-1)) FOR [UserID]
GO

ALTER TABLE [dbo].[BBS_Reply] ADD 
	CONSTRAINT [DF_BBS_Reply_ReplyDate] DEFAULT (getdate()) FOR [ReplyDate]
GO

ALTER TABLE [dbo].[BBS_Topic] ADD 
	CONSTRAINT [DF_BBS_Main_PersonalModuleID] DEFAULT ((-1)) FOR [PersonalForumID],
	CONSTRAINT [IX_BBS_Topic] UNIQUE  NONCLUSTERED 
	(
		[Account]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[BBS_UserConfig] ADD 
	CONSTRAINT [DF_BBS_UserConfig_signature] DEFAULT ('') FOR [signature],
	CONSTRAINT [DF_BBS_UserConfig_Approved] DEFAULT (1) FOR [Approved],
	CONSTRAINT [DF_BBS_UserConfig_Theme] DEFAULT ('default') FOR [Theme]
GO

ALTER TABLE [dbo].[Bank_Account] ADD 
	CONSTRAINT [DF_Bank_Account_Amount] DEFAULT (0) FOR [Amount],
	CONSTRAINT [DF_Bank_Account_Credit] DEFAULT (0) FOR [Credit],
	CONSTRAINT [DF_Bank_Account_Freeze] DEFAULT (0) FOR [Freeze],
	CONSTRAINT [DF_Bank_Account_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[Blog_Config] ADD 
	CONSTRAINT [DF_Blog_Config_ShowPostCount] DEFAULT (10) FOR [ShowPostCount],
	CONSTRAINT [DF_Blog_Config_ShowCommentCount] DEFAULT (10) FOR [ShowCommentCount],
	CONSTRAINT [DF_Blog_Config_DisabledComment] DEFAULT (0) FOR [DisabledComment],
	CONSTRAINT [DF_Blog_Config_PostShowMode] DEFAULT (1) FOR [PostShowMode],
	CONSTRAINT [IX_Blog_Config] UNIQUE  NONCLUSTERED 
	(
		[Account]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Blog_Content] ADD 
	CONSTRAINT [DF_GuestBook_ParentID] DEFAULT ((-1)) FOR [ParentID],
	CONSTRAINT [DF_GuestBook_FeedBackCount] DEFAULT (0) FOR [FeedBackCount],
	CONSTRAINT [DF_Blog_Content_EnableComment] DEFAULT (1) FOR [EnableComment],
	CONSTRAINT [DF_Blog_Content_Hiding] DEFAULT (0) FOR [Hiding],
	CONSTRAINT [DF_Blog_Content_Price] DEFAULT (0) FOR [Price],
	CONSTRAINT [DF_Blog_Content_WebCount] DEFAULT (0) FOR [WebCount],
	CONSTRAINT [DF_Blog_Content_WebLastUpdated] DEFAULT (getdate()) FOR [WebLastUpdated],
	CONSTRAINT [DF_Blog_Content_AggLastUpdated] DEFAULT (getdate()) FOR [AggLastUpdated],
	CONSTRAINT [DF_GuestBook_DataAdded] DEFAULT (getdate()) FOR [DateAdded],
	CONSTRAINT [DF_GuestBook_DataUpdated] DEFAULT (getdate()) FOR [DateUpdated]
GO

ALTER TABLE [dbo].[NetDisk_Directory] ADD 
	CONSTRAINT [DF_NetDisk_Directory_ParentID] DEFAULT (0) FOR [ParentID],
	CONSTRAINT [DF_NetDisk_Directory_Created] DEFAULT (getdate()) FOR [Created],
	CONSTRAINT [DF_NetDisk_Directory_Updated] DEFAULT (getdate()) FOR [Updated],
	CONSTRAINT [IX_NetDisk_Directory] UNIQUE  NONCLUSTERED 
	(
		[DirectoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NetDisk_Domain] ADD 
	CONSTRAINT [IX_NetDisk_Domain_1] UNIQUE  NONCLUSTERED 
	(
		[Account]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_NetDisk_Domain] ON [dbo].[NetDisk_Domain]([DomainID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NetDisk_Resource] ADD 
	CONSTRAINT [DF_NetDisk_Resource_Hits] DEFAULT (0) FOR [Hits],
	CONSTRAINT [DF_NetDisk_Resource_Price] DEFAULT (0) FOR [Price],
	CONSTRAINT [DF_NetDisk_Resource_CreateDate] DEFAULT (getdate()) FOR [Created],
	CONSTRAINT [DF_NetDisk_Resource_UpdateDate] DEFAULT (getdate()) FOR [Updated]
GO

 CREATE  INDEX [IX_NetDisk_Resource] ON [dbo].[NetDisk_Resource]([Name]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NetDisk_filekey] ADD 
	CONSTRAINT [IX_NetDisk_filekey] UNIQUE  NONCLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Photo_Categories] ADD 
	CONSTRAINT [DF_Photo_Categories_PhotoType] DEFAULT (1) FOR [PhotoType],
	CONSTRAINT [DF_Photo_Categories_MainPhotoID] DEFAULT ((-1)) FOR [MainPhotoID],
	CONSTRAINT [DF_Photo_Categories_NumPhotos] DEFAULT (0) FOR [NumPhotos],
	CONSTRAINT [DF_Photo_Categories_Updated] DEFAULT (getdate()) FOR [Updated],
	CONSTRAINT [DF_Photo_Categories_created] DEFAULT (getdate()) FOR [created]
GO

ALTER TABLE [dbo].[Photo_Data] ADD 
	CONSTRAINT [DF_Photo_Data_PhotoSize] DEFAULT (0) FOR [PhotoSize],
	CONSTRAINT [DF_Photo_Data_Width] DEFAULT (0) FOR [Width],
	CONSTRAINT [DF_Photo_Data_height] DEFAULT (0) FOR [height],
	CONSTRAINT [DF_Photo_Data_IsCover] DEFAULT (0) FOR [IsCover],
	CONSTRAINT [DF_Photo_Data_Order] DEFAULT (1) FOR [Order],
	CONSTRAINT [DF_Photo_Data_created] DEFAULT (getdate()) FOR [created]
GO

ALTER TABLE [dbo].[User_Amt] ADD 
	CONSTRAINT [DF_User_Amt_Reward] DEFAULT (0) FOR [Reward],
	CONSTRAINT [DF_User_Amt_NowLoan] DEFAULT (0) FOR [NowLoan],
	CONSTRAINT [DF_User_Amt_freeze] DEFAULT (0) FOR [freeze]
GO

ALTER TABLE [dbo].[User_Group] ADD 
	CONSTRAINT [DF_User_Group_DefaultGroup] DEFAULT (0) FOR [DefaultGroup],
	CONSTRAINT [DF_NetDisk_Group_Active] DEFAULT (1) FOR [Active]
GO

ALTER TABLE [dbo].[User_UserConfig] ADD 
	CONSTRAINT [DF_User_UserConfig_GuestLimits] DEFAULT (N'LR') FOR [GuestLimits],
	CONSTRAINT [DF_NetDisk_UserConfig_Encoding] DEFAULT (936) FOR [Encoding],
	CONSTRAINT [DF_User_UserConfig_Theme] DEFAULT (N'msn') FOR [Theme],
	CONSTRAINT [DF_User_UserConfig_Language] DEFAULT (N'zh-CN') FOR [Language],
	CONSTRAINT [DF_User_UserConfig_ShowBlog] DEFAULT (1) FOR [EnableBlog],
	CONSTRAINT [DF_User_UserConfig_EnableDisk] DEFAULT (1) FOR [EnableDisk],
	CONSTRAINT [DF_NetDisk_UserConfig_Created] DEFAULT (getdate()) FOR [Created]
GO

ALTER TABLE [dbo].[Users] ADD 
	CONSTRAINT [DF_Users_Birthday] DEFAULT (getdate()) FOR [Birthday],
	CONSTRAINT [IX_Users] UNIQUE  NONCLUSTERED 
	(
		[Account]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[blog_Categories] ADD 
	CONSTRAINT [DF_blog_Categories_CategoryType] DEFAULT (0) FOR [CategoryType]
GO

ALTER TABLE [dbo].[blog_Links] ADD 
	CONSTRAINT [DF_blog_Links_CategoryID] DEFAULT (0) FOR [CategoryID],
	CONSTRAINT [DF_blog_Links_BlogID] DEFAULT ((-1)) FOR [BlogID],
	CONSTRAINT [DF_blog_Links_PostID] DEFAULT ((-1)) FOR [PostID],
	CONSTRAINT [DF__blog_Link__NewWi__084B3915] DEFAULT (0) FOR [NewWindow],
	CONSTRAINT [DF_blog_Links_Active] DEFAULT (1) FOR [Active],
	CONSTRAINT [DF_blog_Links_Updated] DEFAULT (getdate()) FOR [Updated]
GO

ALTER TABLE [dbo].[blog_ViewCount_bak] ADD 
	CONSTRAINT [DF_blog_EntryViewCount_WebCount] DEFAULT (0) FOR [WebCount],
	CONSTRAINT [DF_blog_EntryViewCount_AggCount] DEFAULT (0) FOR [AggCount]
GO

ALTER TABLE [dbo].[tbl_Tags] ADD 
	CONSTRAINT [DF_tbl_Tags_Hits] DEFAULT (0) FOR [Hits]
GO

ALTER TABLE [dbo].[tbl_Visitor] ADD 
	CONSTRAINT [DF_Spaces_Visitor_VisitorDate] DEFAULT (getdate()) FOR [VisitorDate]
GO

ALTER TABLE [dbo].[bank_OrderRecord] ADD 
	CONSTRAINT [FK_bank_OrderRecord_bank_record] FOREIGN KEY 
	(
		[RecordID]
	) REFERENCES [dbo].[bank_record] (
		[ID]
	),
	CONSTRAINT [FK_bank_OrderRecord_tbl_order] FOREIGN KEY 
	(
		[OrderID]
	) REFERENCES [dbo].[tbl_order] (
		[OrderID]
	)
GO

ALTER TABLE [dbo].[bank_record] ADD 
	CONSTRAINT [FK_bank_record_Bank_Account] FOREIGN KEY 
	(
		[Account]
	) REFERENCES [dbo].[Bank_Account] (
		[Account]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE           View view_Content
AS
SELECT 
c.*,bc.Account,d.CategoryID
 /*dbo.f_BlogPostTags(c.ID)as tags*/  FROM 
  [Blog_Content] c 
inner join blog_Config bc on bc.BlogID=c.BlogID
left  join [blog_Links] l on (c.ID=l.PostID )
left  join [blog_Categories] d  on (d.CategoryID=l.CategoryID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE            View view_Users
AS
SELECT 
u.*,
 c.[GroupID],  c.[GuestLimits],  c.[Encoding],  c.[Server],  c.[Title],  c.[Theme],  c.[Language],  c.[EnableBlog],  c.[EnableDisk],  c.[AvatarImage],  c.[Description],  c.[Layout],
 
bc.[BlogID],  bc.[ShowPostCount], bc.[ShowCommentCount],
ug.TotalSize,ug.GroupName,
dir.DirectoryID as RootDirID
FROM 
  [Users] u 
Inner join [User_UserConfig] c on(u.Account=c.Account) 
Inner join  [blog_Config] bc on bc.Account=u.Account
Inner join  [User_Group] ug  on c.GroupID=ug.GroupID
left join [NetDisk_Directory] dir on (dir.Account=u.Account and dir.ParentID=0)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE       proc BBS_AddReply

@TopicID int,
@Account nvarchar(50),
@Subject nvarchar(255),
@Content ntext,
@ReplyDate DateTime
AS

if not exists (select * from bbs_UserConfig where Account=@Account)
begin
exec bbs_CreateUserConfig @Account
end
INSERT INTO [BBS_Reply]([TopicID], [Account], [Subject], [Content], [ReplyDate])
VALUES(@TopicID,@Account,@Subject,@Content,@ReplyDate)

UPDATE [BBS_Topic]  --更新回复数,最后回复人
SET [ReplyCount]=[ReplyCount]+1,  [LastReplyAccount]=@Account, [LastReplyDate]=@ReplyDate
WHERE [ID]=@TopicID

UPDATE [BBS_UserConfig]
SET   [NumReply]=[NumReply]+1
WHERE [Account]=@Account

UPDATE [BBS_Forums] 
 set [LastPosted]=getdate(), [LastAccount]=@Account, [LastNickName]=u.NickName,  [NumPosts]=[NumPosts]+1
from Users u ,[BBS_Topic] t 
WHERE u.Account=@Account and t.[ID]=@TopicID and u.Account=t.Account and [BBS_Forums].ID=t.ForumID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE     proc BBS_DelReply
@ID int
As

declare @TopicID int
declare @Account nvarchar(50)
declare @ForumID int
declare @TopicDate Datetime

declare @LastReplyAccount nvarchar(50)
declare @LastReplyDate  DateTime

SELECT @TopicID=r.[TopicID], @Account=r.[Account],@ForumID=t.ForumID
, @TopicDate=t.PublishDate
 FROM [BBS_Reply] r inner join [BBS_Topic] t on (t.ID=r.TopicID)
where r.ID=@ID

UPDATE [BBS_Forums]   --更新论坛回复数
SET      [NumPosts]= [NumPosts]-1 
WHERE [ID]=@ForumID

UPDATE [BBS_UserConfig]   --更新用户回复数
SET   [NumReply]=[NumReply]-1
WHERE [Account]=@Account


SELECT top 1   @LastReplyAccount=[Account], @LastReplyDate=[ReplyDate] FROM [BBS_Reply] WHERE [TopicID]=@TopicID order by ID desc

if (@LastReplyAccount is null) --根据条件更新用户最后回复,回复数 时间
begin
UPDATE [BBS_Topic]
SET      [ReplyCount]=0
 ,[LastReplyAccount]=''

WHERE [ID]=@ID
end
else
begin
UPDATE [BBS_Topic]
SET      [ReplyCount]=[ReplyCount]-1
, [LastReplyAccount]=@LastReplyAccount
,[LastReplyDate]=@LastReplyDate
WHERE [ID]=@TopicID
end

DELETE FROM [BBS_Reply] --删除回复
 WHERE [ID]=@ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc BBS_DelTopic
@ID int
As


declare @Account nvarchar(50)
declare @ForumID int
SELECT  @Account=[Account],
@ForumID=[ForumID]
 FROM  [BBS_Topic] 
where [ID]=@ID

declare @LastAccount nvarchar(50)
declare @LastNickName nvarchar(50)
declare @LastTime     DateTime
declare @TopicCount int




UPDATE [BBS_UserConfig]   --更新用户回复数和主题数
SET   [NumReply]=(SELECT count(*) FROM [BBS_Reply] where [Account]=@Account and [TopicID]<>@ID)
,[NumTopics]=(SELECT count(*) FROM [BBS_Topic] where [Account]=@Account and [ID]<>@ID)

WHERE [Account] in 
(select  t.Account from bbs_Topic t where T.ID=@ID
UNION  
select  r.Account from bbs_Reply r where R.TopicID =@ID)

declare @lastTopicID int
SELECT @LastAccount=[Account],@LastTime=max(publishDate),@LastNickName=dbo.f_NickNameByAccount(Account) ,
 @lastTopicID=max(ID)
FROM [BBS_Topic] where forumID=@ForumID and [ID]<>@ID group by Account 

if( @lastTopicID is null) set  @lastTopicID=0
UPDATE [BBS_Forums]   --更新论坛主题数
SET      [NumTopics]= [NumTopics]-1
,[NumPosts]=[NumPosts]-(SELECT count(*) FROM [BBS_Reply] where topicID=@ID)
,[LastAccount]=@LastAccount
,[LastNickName]=@LastNickName
,LastPosted=@LastTime,
LastTopicID=@lastTopicID
WHERE [ID]=@ForumID

DELETE FROM [BBS_Reply] --删除主题下回复
WHERE [TopicID]=@ID

DELETE FROM [BBS_Topic] --删除主题
 WHERE [ID]=@ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE proc BBS_Init
AS
UPDATE [BBS_Forums]
SET numTopics=(select count(*) from bbs_Topic where ForumID=[BBS_Forums].[ID])
,NumPosts=(select count(*) from bbs_Topic t,bbs_Reply r where r.TopicID=t.ID and t.ForumID=[BBS_Forums].[ID])
,LastTopicID=(select IsNull(Max(ID),0) from bbs_Topic where ForumID=[BBS_Forums].[ID])


Update [BBS_UserConfig] 
SET NumTopics=(select count(*) from bbs_Topic where [Account]=[BBS_UserConfig].[Account])
,NumReply=(select count(*) from bbs_Reply r where r.[Account]=[BBS_UserConfig].[Account])

UPDATE [BBS_Topic]
SET    [ReplyCount]=(SELECT Count([ID]) FROM [BBS_Reply] where TopicID=[BBS_Topic].ID)
, [LastReplyAccount]=(SELECT max([ID]) FROM [BBS_Reply] where TopicID=[BBS_Topic].ID) 

,[LastReplyDate]=(SELECT max([ReplyDate]) FROM [BBS_Reply] where TopicID=[BBS_Topic].ID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create  proc Bbs_AlterTopicStatus
@ID int,
@Status int
AS

UPDATE [BBS_Topic]
SET     [Status]=@Status 
WHERE [ID]=@ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    proc Blog_CreateLink
@CategoryID int,
@BlogID     int,
@PostID     int
--@NewWindow  bit,
--@Active     bit
AS
if not exists (select * from [Blog_Links] where [PostID]=@PostID and [CategoryID]=@CategoryID)
begin
INSERT INTO [blog_Links]([CategoryID], [BlogID], [PostID])
VALUES(@CategoryID,@BlogID,@PostID)
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE           proc Blog_Insert_Content(
        @BlogID int,
        @Title nvarchar(255),
	@TitleUrl nvarchar(255),
        @SourceName nvarchar(255),	
	@Text ntext,
	@Author nvarchar(50),
        @PostType int,
        @EnableComment bit,--启用评论
        @Hiding  bit ,--是否隐藏
        @Price   int,  --价格
        @Tags    nvarchar(255),
	@DateAdded datetime,
	@ParentID int,
	@ID int output)
AS
INSERT INTO [Blog_Content]([BlogID], [Author],[PostType],[EnableComment],[Hiding],[Price],[Tags], [Title], [Text], [TitleUrl],[SourceName], [ParentID], [FeedBackCount],[WebCount], [AggCount], [WebLastUpdated], [AggLastUpdated], [DateAdded], [DateUpdated])
VALUES(@BlogID,@Author,@PostType,@EnableComment,@Hiding,@Price,@Tags,@Title,@Text, @TitleUrl,@SourceName,@ParentID,0, 0, 0, getdate(), getdate(),@DateAdded,@DateAdded)
Select @ID = @@Identity

if(@PostType =8 or @PostType =4)
Begin
/*declare @tempID int 
select @tempID =BlogID from Blog_Content where [ID]=@ParentID
Update Blog_Content Set blogID = @tempID where [ID] = @ID
*/
Update Blog_Content Set FeedBackCount = FeedBackCount + 1 where [ID] = @ParentID
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create procedure Blog_NewLoggedBlogs
@Count int
as


declare @sql varchar(8000)

select @sql='SELECT  top '+ rtrim(@count) + ' bc.BlogID,uc.[Account], uc.[Title] FROM [User_UserConfig] uc join users on (uc.Account=users.Account) join [Blog_Config] bc on (bc.Account=users.Account) order by   users.[DateLastLogin] desc'
exec(@sql)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create procedure Blog_NewLoginBlogs
@Count int
as


declare @sql varchar(8000)

select @sql='SELECT  top '+ rtrim(@count) + ' bc.BlogID,uc.[Account], uc.[Title] FROM [User_UserConfig] uc join users on (uc.Account=users.Account) join [Blog_Config] bc on (bc.Account=users.Account) order by   users.[DateLastLogin] desc'
exec(@sql)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE   procedure Blog_SearchTopic
(
	@PageIndex int,
	@PageSize int,
        @BlogID   int,
        @SearchType   int=1,
        @PostType     int=1,
        @Hiding       int=0,
        @KeyWord nvarchar(255),
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)
Declare @SearchSQL nvarchar(4000)
DECLARE @StartTime datetime
SET @StartTime = GetDate()

set @SearchSQL='Insert  into #IDs(EntryID) SELECT c.[ID] FROM  [Blog_Content] c 
inner join [Blog_Config] f on(c.BlogID=f.BlogID)
inner join [Users] uc on(uc.Account=f.Account) where 1=1  '

 set @SearchSQL=@SearchSQL+' and c.Hiding='+cast(@Hiding as varchar) +' '

if(@BlogID>0)
 set @SearchSQL=@SearchSQL+' and c.BlogID='+cast(@BlogID as varchar) +' '

if(@PostType>0)
 set @SearchSQL=@SearchSQL+' and c.PostType='+cast(@PostType as varchar) +' '


if(@SearchType=1)
 set @SearchSQL=@SearchSQL+' and c.[Title] like  ''%'+@KeyWord+'%'''  

if(@SearchType=2)
 set @SearchSQL=@SearchSQL+' and c.[Author] like  ''%'+@KeyWord+'%''  or uc.NickName like    ''%'+@KeyWord+'%'' '

if(@SearchType=3)
 set @SearchSQL=@SearchSQL+' and c.[Text] like  ''%'+@KeyWord+'%'' '

 set @SearchSQL=@SearchSQL+' order by c.[ID] desc'
 
exec sp_executesql @SearchSQL

SELECT c.[ID],f.Account,c.[ParentID],c.Author,c.Title,c.hiding,c.WebCount,c.DateAdded FROM  [Blog_Content] c 
inner join [Blog_Config] f on(c.BlogID=f.BlogID)

    	INNER JOIN #IDS tmp ON (c.[ID] = tmp.EntryID)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY tmp.TempID desc
SELECT @Count=COUNT(*) FROM 	#IDS 
--SELECT Duration = GetDate() - @StartTime
DROP TABLE #IDS
return @Count
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create    proc Blog_TopicToPutCategory
@CategoryID int,
@BlogID     int,
@PostID     int
--@NewWindow  bit,
--@Active     bit
AS
if not exists (select * from [Blog_Links] where [PostID]=@PostID and [CategoryID]=@CategoryID)
begin
INSERT INTO [blog_Links]([CategoryID], [BlogID], [PostID])
VALUES(@CategoryID,@BlogID,@PostID)
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE      proc Blog_Update_Content
        @ID int,
        @BlogID int,
	@Title nvarchar(255),
	@TitleUrl nvarchar(255),
	@Text ntext,
        @SourceName nvarchar(255),
        @EnableComment bit,
        @Hiding        bit,
        @Price         int,
        @Tags          nvarchar(255),
        @PostType int,
	@Author nvarchar(50),
        @DateAdded datetime,
	@DateUpdated datetime,
	@ParentID int
AS
DELETE FROM [blog_Links]
WHERE [PostID]=@ID

UPDATE [Blog_Content]
SET   [Author]=@Author, 
       [BlogID]=@BlogID,
       [TitleUrl]=@TitleUrl,
      [Title]=@Title, 
      [SourceName]=@SourceName,
      [Text]=@Text, 
      [EnableComment]=@EnableComment,
       [Hiding]=@Hiding,
       [Price]=@Price,
      [Tags]=@Tags,
       [PostType]=@PostType,
      [DateAdded]=@DateAdded,
      [ParentID]=@ParentID, 
      [DateUpdated]=@DateUpdated 
WHERE [ID]=@ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create proc Domain_Create
@Account NVarchar(50),
@Title nvarchar(255),
@Advertise nvarchar(255),
@RedirectURL nvarchar(255),
@Type     int
AS
INSERT INTO [NetDisk_Domain]([Account], [Title], [Advertise], [RedirectURL], [Type])
VALUES(@Account,@Title,@Advertise,@RedirectURL,@Type)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc Domain_Get
@Account nvarchar(255)
AS
SELECT [DomainID], [Account], [Title], [Advertise], [RedirectURL], [Type]
 FROM [NetDisk_Domain] where [Account]=@Account
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create proc Domain_Update
@Account NVarchar(50),
@Title nvarchar(255),
@Advertise nvarchar(255),
@RedirectURL nvarchar(255),
@Type     int
AS
UPDATE [NetDisk_Domain]
SET [Title]=@Title, [Advertise]=@Advertise, [RedirectURL]=@RedirectURL, 
[Type]=@Type
WHERE [Account]=@Account
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create  PROCEDURE NetDisk_AddFileKey
@Filekey     varchar(80) = NULL,
@Account    varchar(20) = NULL,
@FileID    int =0,
@DownCount  int = 0,
@EndTime      datetime
AS
set nocount on
if(not exists(select * from NetDisk_filekey where (Filekey=@Filekey)))
begin
	
		insert  NetDisk_filekey(Filekey,Account,FileID,DownCount,EndTime,AddTime) 
		values (@Filekey,@Account,@FileID,@DownCount,@EndTime,GetDate())
		select null as ErrorText
	
end
else
begin
	select '文件ID "' + @Filekey + '" 已存在 !' as ErrorText
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE      proc NetDisk_CreateDirectory
@Account nvarchar(50),
@Directory nvarchar(255),
@ParentID int,
@Attribute int,
@Password  nvarchar(100),
@Description nvarchar(1000),
@limits nvarchar(100)--,
--@DirectoryID int output
AS
declare @NewParentID int 
set @NewParentID=@ParentID
if(@ParentID<1)
begin
if( @Directory<>'/')
begin
SELECT @NewParentID=DirectoryID FROM [NetDisk_Directory] where [Account]=@Account and Directory='/'
end
else
set @NewParentID=0
end
if not exists(SELECT * FROM [NetDisk_Directory] where [Account]=@Account and [Directory]=@Directory)
INSERT INTO [NetDisk_Directory]([Account], [Directory], [ParentID], [Attribute],[Password], [Description],  [limits], [Created], [Updated])
VALUES(@Account, @Directory, @NewParentID,@Attribute,@Password ,@Description,@limits ,getdate(),getdate())
--Select @DirectoryID = @@Identity
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc NetDisk_CreateFile
@DirectoryID int,
@Attribute int,
@SourceName nvarchar(255),
@Name nvarchar(255),
@Description  nvarchar(1000),
@FileSize int,
@Hits int=0,
@Price int=0,
@Tags  nvarchar(255),
@FileID int output
AS
INSERT INTO [NetDisk_Resource]
([DirectoryID], [Attribute], [SourceName], 
[Name], [Description], [FileSize], 
[Hits], [Price], [Tags],[Created], [Updated])
VALUES(@DirectoryID,@Attribute,@SourceName,
@Name,@Description,
@FileSize,@Hits,@Price,@Tags,getdate(),getdate())
Select @FileID = @@Identity
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc NetDisk_DelDirectory
@DirectoryID int
AS
DELETE FROM [NetDisk_Directory]
WHERE  [DirectoryID]=@DirectoryID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create proc NetDisk_DelFile
@FileID int
AS
DELETE FROM [NetDisk_Resource]
WHERE [FileID]=@FileID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create   PROCEDURE NetDisk_FileKeyExists
@Account        nvarchar(100) = NULL,
@FileID         int
AS
select * from NetDisk_filekey where (Account = @Account and [FileID]=@FileID  )
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc NetDisk_GetDefaultRes
@ParentID int ,
@Directory   nvarchar(255),
@Count     int =10
AS
Declare @DirectoryID int
SELECT @DirectoryID=[DirectoryID] FROM [NetDisk_Directory]
where ParentID=@ParentID and [Directory]=@Directory
SELECT f.*,d.Directory,d.[Password],d.Attribute as FolderAttribute,d.limits FROM [NetDisk_Resource] f join
[NetDisk_Directory] d on(d.[DirectoryID]=@DirectoryID and  f.DirectoryID=d.DirectoryID)
order by f.Created desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    proc NetDisk_GetDirectory
@DirectoryID int=0,
@Account nvarchar(255)=null
AS
if(@DirectoryID=0)
begin
SELECT * FROM [NetDisk_Directory] where  [Account]=@Account and Directory='/'
end
else
begin
SELECT * FROM [NetDisk_Directory] where  DirectoryID=@DirectoryID
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc NetDisk_GetDirectorys
@Account     nvarchar(255),
@DirectoryID int
AS
if(@DirectoryID=0)
begin
Select @DirectoryID =DirectoryID FROM [NetDisk_Directory] where [Account]=@Account  and [Directory]='/'
end 
SELECT * FROM [NetDisk_Directory] where   [ParentID]=@DirectoryID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    proc NetDisk_GetFile
@FileID int
AS
SELECT f.*,d.Directory,d.[Password],d.Attribute as FolderAttribute,d.limits FROM [NetDisk_Resource] f join
[NetDisk_Directory] d on( [FileID]=@FileID and f.DirectoryID=d.DirectoryID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    PROCEDURE NetDisk_GetFilePath
@FileKey nvarchar(100) = NULL
 AS
DECLARE @DownCount int
DECLARE @EndTime   DateTime
select @DownCount=DownCount,@EndTime=EndTime from NetDisk_filekey where (FileKey=@FileKey)
if(@DownCount>0 and @EndTime>GetDate())
begin
UPDATE [NetDisk_filekey] SET DownCount=DownCount-1 where (FileKey=@FileKey)
select k.* ,d.Directory,f.SourceName from NetDisk_filekey k join NetDisk_Resource f
on (f.FileID=k.FileID and k.FileKey=@FileKey) join NetDisk_Directory d
on d.DirectoryID=f.DirectoryID
end
else
begin
DELETE FROM [NetDisk_filekey] WHERE (FileKey=@FileKey)
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    Proc NetDisk_GetFiles
@DirectoryID int
AS
SELECT f.*,d.Directory,d.[Password],d.Attribute as FolderAttribute,d.limits FROM [NetDisk_Resource] f join
[NetDisk_Directory] d on(d.[DirectoryID]=@DirectoryID and  f.DirectoryID=d.DirectoryID)
order by f.Created desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE               proc NetDisk_GetUserConfig
@Account nvarchar(50)
AS
SELECT uc.* ,ng.[GroupName], ng.[TotalSize]
,bc.BlogID,bc.ShowPostCount,bc.ShowCommentCount, isnull(dir.DirectoryID,-1) as RootDirID
FROM [User_UserConfig] uc
join  [User_Group]  ng on (uc.Account=@Account and uc.GroupID=ng.GroupID)
left join  [Blog_Config]  bc on (bc.Account=uc.Account)
left join [NetDisk_Directory] dir on (dir.Account=@Account and dir.ParentID=0)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE     proc NetDisk_GetUserConfigByBlogID
@BlogID int
AS
declare @Account nvarchar(400)
SELECT @Account=[Account] FROM [Blog_Config] where [BlogID]= @BlogID
exec User_GetConfig @Account
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc NetDisk_GetUserDisk
@DirectoryID int 
AS
--顺序不能乱
SELECT * FROM [NetDisk_Directory] where  DirectoryID=@DirectoryID
SELECT * FROM [NetDisk_Directory] where   [ParentID]=@DirectoryID
SELECT f.*,d.Directory,d.[Password],d.Attribute as FolderAttribute,d.limits FROM [NetDisk_Resource] f join
[NetDisk_Directory] d on(d.[DirectoryID]=@DirectoryID and  f.DirectoryID=d.DirectoryID)
order by f.Created desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create  proc NetDisk_GetUserDiskByName
@ParentID int ,
@Directory nvarchar(255)
AS
declare @DirectoryID int 
SELECT @DirectoryID=DirectoryID FROM [NetDisk_Directory] where 
ParentID=@ParentID and Directory=@Directory
exec NetDisk_GetUserDisk @DirectoryID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc NetDisk_UpdateDirectory
@DirectoryID int,
@Account nvarchar(50),
@Directory nvarchar(255),
@ParentID int,
@Attribute int,
@Password  nvarchar(100),
@Description nvarchar(1000),
@limits nvarchar(100)
AS
Declare @CurrentDirectory nvarchar(1000) 
Declare @CurrentParentID int
Select @CurrentDirectory=Directory, @CurrentParentID =ParentID FROM [NetDisk_Directory] where [DirectoryID]=@DirectoryID
if(@CurrentDirectory<>@Directory)
begin
UPDATE [NetDisk_Directory] 
Set [Directory]=@Directory+Right(Directory,len(Directory)-len(left(Directory,Len(@CurrentDirectory))))
WHERE Account=@Account and left(Directory,len(@CurrentDirectory))=@CurrentDirectory
--ParentID =@CurrentParentID
end
begin
UPDATE [NetDisk_Directory]
SET  [ParentID]=@ParentID, [Directory]=@Directory,
[Attribute]=@Attribute, [Password]=@Password, [Description]=@Description, 
[limits]=@limits,  [Updated]=getdate()
WHERE [DirectoryID]=@DirectoryID 
end
if(@Directory='/')
begin
UPDATE [User_UserConfig]
SET  [GuestLimits]=@limits
WHERE [Account]=@Account
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc NetDisk_UpdateFile
@FileID      int,
@DirectoryID int,
@Attribute int,
@SourceName nvarchar(255),
@Name nvarchar(255),
@Description  nvarchar(1000),
@FileSize int,
@Hits int,
@Price int,
@Tags  nvarchar(255),
@Created datetime,
@Updated datetime
AS
UPDATE [NetDisk_Resource]
SET  [DirectoryID]=@DirectoryID, [Attribute]=@Attribute, 
[SourceName]=@SourceName, [Name]=@Name, [Description]=@Description, 
[FileSize]=@FileSize, [Hits]=@Hits, [Price]=@Price,
[Tags]=@Tags, 
[Created]=@Created, [Updated]=@Updated
WHERE [FileID]=@FileID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  procedure Photo_CreateCategory

@Account nvarchar(50),
@Name    nvarchar(255),
@Password nvarchar(50),
@Limits   char(10),
@Description nvarchar(4000),
@ID int output
AS


INSERT INTO  [Photo_Categories]
( [Account], 
[Name], 
[Password], 
[Limits], 
[Description], 
[PhotoType],
[MainPhotoID], 
[Updated],
[created])
VALUES(
@Account , 
@Name,
@Password,
@Limits,
@Description, 
1, 
-1,
getdate(),
getdate())

Select @ID = @@Identity
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    procedure Photo_CreatePhoto
@CategoryID INT,
@image nvarchar(255),
@title nvarchar(255),
@SmallPic nvarchar(255),
@PhotoSize int,
@Width     int,
@height    int,
@detail    ntext,
@IsCover   int,
@Order     int,
@Tags      nvarchar(255),
@ID       int output 

AS
begin

declare @MaxOrder  int
declare @NumPhotos  int
declare @MainPhotoID int 
set @MainPhotoID=-1
if(@Order<1)
begin
select @MaxOrder=(isnull(Max([Order]),0)+1),@NumPhotos=(isnull(count(*),0)+1)  FROM [Photo_Data] where [CategoryID]=@CategoryID
end
else
begin
set @MaxOrder=@Order
end

INSERT INTO  [Photo_Data](  
[CategoryID], 
[image], 
[title], 
[SmallPic], 
[PhotoSize], 
[Width], [height],
 [detail],
[IsCover],
[Order],
[Tags],
 [created])
VALUES(@CategoryID,
@image,
@title ,
@SmallPic,
@PhotoSize,
@Width,
@height,@detail,
@IsCover,
@MaxOrder,
@Tags,
getdate())

Select @ID = @@Identity

if  exists(select * from [Photo_Categories] where [ID]=@CategoryID and MainPhotoID=-1 )
begin
update Photo_Categories SET [NumPhotos]=@NumPhotos,
[Updated]=getdate()
,MainPhotoID=@ID
where [ID]=@CategoryID
end
else
begin

update Photo_Categories SET [NumPhotos]=@NumPhotos,
[Updated]=getdate()
where [ID]=@CategoryID
end 

end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure Photo_DelCategory
@ID      int
AS

DELETE FROM [Photo_Categories]
WHERE [ID]=@ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   procedure Photo_DelPhoto
@PhotoID    int
AS
declare @CategoryID int
declare @MainID     int
--获取目录ID，封面图片ID
select @CategoryID=d.CategoryID,@MainID=c.MainPhotoID FROM  
[Photo_Data] d inner join Photo_Categories c on (c.[ID]=d.CategoryID) 
WHERE [PhotoID]=@PhotoID
--print  'CategoryID'+ cast(@CategoryID  as varchar)+' \n mainID:'+cast(@MainID as varchar)

DELETE FROM  [Photo_Data]
WHERE [PhotoID]=@PhotoID

if(@MainID=@PhotoID)--当删除的图片是封面图片
begin
if exists(Select top 1   PhotoID FROM  [Photo_Data] WHERE [CategoryID]=@CategoryID order by PhotoID  asc)
begin
Select top 1 @MainID=  PhotoID FROM  [Photo_Data]
WHERE [CategoryID]=@CategoryID order by PhotoID  asc
end 
else
set @MainID=-1
end
--print  'CategoryID'+ cast(@CategoryID  as varchar)+' \n mainID:'+cast(@MainID as varchar)
UPDATE  [Photo_Categories]--更新图片数，主ID
SET   [NumPhotos]=[NumPhotos]-1
,MainPhotoID=@MainID
where [ID]=@CategoryID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE     procedure Photo_GetCategories
@Account nvarchar(255)

AS
 
 SELECT c.*,p.smallPic ,p.image FROM  [Photo_Categories] c
left join [Photo_Data] p on (c.ID=p.CategoryID and c.MainPhotoID=p.PhotoID)

where Account=@Account


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE     procedure Photo_GetCategoy
@ID int

AS
 
 SELECT c.*,p.smallPic ,p.image FROM  [Photo_Categories] c
left join [Photo_Data] p on (c.ID=p.CategoryID and c.MainPhotoID=p.PhotoID)

where c.[ID]=@ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create   procedure Photo_GetPhoto
 
@PhotoID int =0
AS


SELECT d.*,c.Account,c.Name,c.Password,c.Limits,c.ID 
as CategoryID FROM [Photo_Categories] c inner join [Photo_Data] 
d on (c.ID=d.CategoryID) where d.PhotoID= @PhotoID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   procedure Photo_GetPhotos
@Account nvarchar(255),
@CategoryID int =0
AS


Declare @SQL nvarchar(4000)
set @SQL='SELECT d.*,c.Account,c.Name,c.Password,c.Limits,c.ID 
as CategoryID FROM [Photo_Categories] c inner join [Photo_Data] d on (c.ID=d.CategoryID) where 1=1 '

if(@Account<>'')
 set @SQL=@SQL+' and c.Account='''+@Account+''' '
if(@CategoryID>0)
  set @SQL=@SQL+' and c.ID='+cast(@CategoryID as varchar)+' '
 
 set @SQL=@SQL+' order by d.[order] desc '
exec sp_executesql @SQL
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  procedure Photo_UpdateCategory
@ID      int,
@Account nvarchar(50),
@Name    nvarchar(255),
@Password nvarchar(50),
@Limits   char(10),
@Description nvarchar(4000),
@MainPhotoID int,
@Updated     DateTime
AS
UPDATE [Photo_Categories]
SET 
  [Account]=@Account, 
  [Name]=@Name,
 [Password]=@Password,
 [Limits]=@Limits,
 
 [Description]=@Description,
 [MainPhotoID]=@MainPhotoID, 
[Updated]=@Updated


WHERE [ID]=@ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    procedure Photo_UpdatePhoto
@PhotoID    int,
@CategoryID INT,
@image nvarchar(255),
@title nvarchar(255),
@SmallPic nvarchar(255),
@PhotoSize int,
@Width     int,
@height    int,
@IsCover   int,
@Order     int,
@detail    ntext,
@Tags      nvarchar(255)
AS

/*if(@IsCover=1)
begin
UPDATE  [Photo_Data]
SET [IsCover]=1  WHERE [CategoryID]=@CategoryID
end
*/
UPDATE  [Photo_Data]
SET 
 [CategoryID]=@CategoryID,
 [image]=@image, 
 [title]=@title, 
 [SmallPic]=@SmallPic,
 [PhotoSize]=@PhotoSize, 
 [Width]=@Width,
 [height]=@height,
 [detail]=@detail ,
  [IsCover]=@IsCover,
   [Order]=@Order,
   [Tags]=@Tags
WHERE [PhotoID]=@PhotoID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE          proc User_Create
@Username nvarchar(50),
@Account nvarchar(50),
@Email     nvarchar(50),
@Password  nvarchar(50),
@NickName nvarchar(20),
@Gender  int,
@City      nvarchar(100),
@Address   nvarchar(200),
--@PostCode  nvarchar(50),
@IDNumber  nvarchar(50),
--@Phone     nvarchar(50),
@Birthday  dateTime,
@Question  nvarchar(50),
@Answer    nvarchar(50),
@RegIP     nvarchar(50),
@GoToAddress   nvarchar(255) --域名转向地址
AS
INSERT INTO [Users]([Username], [Account], [Email],[Password],[NickName],  
[Gender], [City], [Address],
  [IDNumber], --[Phone], 
 [Birthday],
[Question], 
[Answer],[Active],
[RegIP], [LastIP], [DateLastLogin], 
[Created])
VALUES(@Username,@Account,@Email,@Password,@NickName,@Gender,
@City,@Address,--@PostCode,
@IDNumber,--@Phone,
@Birthday,
@Question,@Answer,1,
@RegIP,@RegIP,getdate(),getdate())
declare @Title nvarchar(200)
if(@NickName='')
set @Title=@Account+'的空间'
else
set @Title=@NickName+'的空间'
--创建blog
declare @blogID int
INSERT INTO [Blog_Config]([Account], [ShowPostCount], [ShowCommentCount])
VALUES(@Account, 10,10)
Select @blogID = @@Identity
--创建二级域名
if(@GoToAddress<>'')
begin 
INSERT INTO [NetDisk_Domain]( [Account], [Title], [Advertise], [RedirectURL], [Type])
VALUES(@Account,@Title, 1, @GoToAddress, 1)
end
--获取默认用户组
declare @DefaultGroupID int
SELECT top 1 @DefaultGroupID=[GroupID] FROM  [User_Group] where DefaultGroup=1
--创建用户配置文件
INSERT INTO [User_UserConfig]([Account], [GroupID],[Title],[Description])
VALUES(@Account,@DefaultGroupID,@Title,@Title+'说明')


/*
declare @ID  int
declare @Context nvarchar(1000)
declare @Time DateTime
select @Time=getdate()
set @Context='我已经在这儿安家了，欢迎你时常过来做客，大家多多交流哦。我会把一些新鲜有趣的东西记录下来一块与你分享。也希望你记住我的BLOG地址，你可以把她添加到你的收藏夹，也可以把她复制下来告诉你的朋友们。 <BR>
<BR>
　　 ：）<BR>
<BR>'
exec Blog_Insert_Content @blogID,'嗨！亲爱的朋友们，欢迎您光临我的空间','','',@Context,'系统',1,1,0,0,'', @Time,0,@ID
*/



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc User_CreateLink
@Account nvarchar(100),
@LinkName nvarchar(255),
@UrlAddress nvarchar(255),
@NewWindow  bit,
@LinkType   int,
@Description nvarchar(4000)
AS
INSERT INTO [User_Links]( [Account], [LinkName],[UrlAddress], [NewWindow], [LinkType],[Description])
VALUES(@Account,@LinkName, @UrlAddress, @NewWindow, @LinkType,@Description)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc User_CreateList
@Account nvarchar(100),
@ListType int,
@Title nvarchar(255),
@Parameter nvarchar(800),
@SourceUrl   nvarchar(1024),
@Description nvarchar(4000),
@ID int output
AS
INSERT INTO [User_List]([Account], [ListType], [Title], [Parameter], [SourceUrl],[Description])
VALUES(@Account,@ListType,@Title,@Parameter,@SourceUrl,@Description)
Select @ID = @@Identity
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create      proc User_CreateUserConfig
@Account nvarchar(50),
@GroupID int=-1,
@GuestLimits nvarchar(20)='LR',
@Encoding  int=936,
@Server    nvarchar(255)='',
@Title     nvarchar(255)='',
@AvatarImage nvarchar(255)='',
@Theme       nvarchar(50)='default',
@Language    nvarchar(50)='zh-CN',
@EnableBlog  bit=1,
@EnableDisk bit=1,
@Description nvarchar(1000)='',
@Layout      varbinary(7500)=null,
@Created   DateTime=null
AS

if(@Created is Null)  --创建时间
  set @Created=Getdate()

if(@GroupID<1)  --设置默认组
begin
select top 1 @GroupID=GroupID from User_Group where DefaultGroup=1 
end

INSERT INTO [User_UserConfig]([Account], 
[GroupID], 
[GuestLimits], 
[Encoding],  
[Server],
[Title],
[AvatarImage],
[Theme],
[Language],
[EnableBlog],
[EnableDisk],
[Description], 
[Layout],
[Created])
VALUES(@Account,@GroupID,@GuestLimits,@Encoding,@Server,
@Title,
@AvatarImage,
@Theme,
@Language,
@EnableBlog,
@EnableDisk, 

@Description,
@Layout,
@Created)

exec NetDisk_CreateDirectory @Account,'/',0,1,'','','LR'   --创建用户默认目录
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create proc User_DelLink
@LinkID int
AS
DELETE FROM [User_Links]
WHERE [LinkID]=@LinkID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE   proc User_Get
@Username nvarchar(50)
AS
SELECT * FROM [Users] where  [Username]=@Username

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE                 proc User_GetConfig
@Account nvarchar(50)
AS
SELECT uc.* ,ng.[GroupName], ng.[TotalSize],u.NickName
,bc.PostShowMode,bc.BlogID,bc.ShowPostCount,bc.ShowCommentCount,bc.DisabledComment, isnull(dir.DirectoryID,-1) as RootDirID
FROM [User_UserConfig] uc 
inner join [Users] u on(u.Account=uc.Account)
 join  [User_Group]  ng on (uc.Account=@Account and uc.GroupID=ng.GroupID)
left join  [Blog_Config]  bc on (bc.Account=uc.Account)
left join [NetDisk_Directory] dir on (dir.Account=@Account and dir.ParentID=0)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE  PROCEDURE User_GetDayRegIPCount

@IPAddress varchar(50) = NULL

 AS

declare @Count int 
Select  @Count=isnull(count([Account]),0) From Users where 
RegIP=@IPAddress and Active=1  and datediff(d,created,Getdate())<1
select @count

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc User_GetLink
@Account nvarchar(100)
AS
SELECT * FROM [User_Links]
where [Account]=@Account order by LinkID desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc User_GetListByAccount
@Account nvarchar(100)
AS
SELECT * FROM [User_List] 
where [Account]=@Account
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE     PROC User_GetUsersPaged
(
	@PageIndex int,
	@PageSize int,
        @GroupID   int=0,
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID nvarchar(50) not null
)



if(@GroupID>0)
begin
Insert  into #IDs(EntryID)  select u.[Account] from Users u inner join User_UserConfig c on (u.Account=c.Account) where c.GroupID=@GroupID order by  u.Created desc

end
else
begin
Insert  into #IDs(EntryID)  select [Account] from Users  order by  Created desc
end 




select  u.Account,c.Title,u.Created,u.Active,c.Theme,u.DateLastLogin from Users u inner join User_UserConfig c on (u.Account=c.Account)
--select  Account,Title,Created,Active,Theme,DateLastLogin from  view_Users u
   	INNER JOIN #IDS tmp ON (c.[Account] = tmp.EntryID)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY u.Created  desc
SELECT @Count=COUNT(*) FROM 	#IDS 

DROP TABLE #IDS
return @Count

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE   proc User_Update
@Username nvarchar(50),
@Account nvarchar(50),
@Email    nvarchar(50),
@NickName nvarchar(20),
@Gender   int,
@Password  nvarchar(50),
@Question  nvarchar(50),
@Answer    nvarchar(50),
@City      nvarchar(100),
@Address   nvarchar(200),
@IDNumber  nvarchar(50),
@Phone     nvarchar(50),
@Birthday smallDatetime,
@LastIP    nvarchar(50),
@DateLastLogin DateTime
AS
UPDATE .[Users]
SET 
[NickName]=@NickName, 
[Email]=@Email, 
[Gender]=@Gender,
[Password]=@Password, 
[IDNumber]=@IDNumber, 
[Phone]=@Phone,
[Answer]=@Answer, 
[Address]=@Address,
[City]=@City, 
[Question]=@Question,
[Birthday]=@Birthday,
[LastIP]=@LastIP, 
[DateLastLogin]=@DateLastLogin
WHERE [Account]=@Account

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc User_UpdateLink
@LinkID int,
@Account nvarchar(100),
@LinkName nvarchar(255),
@UrlAddress nvarchar(255),
@NewWindow  bit,
@LinkType   int,
@Description nvarchar(4000)
AS
UPDATE [User_Links]
SET  [Account]=@Account,[LinkName] =@LinkName , 
[UrlAddress]=@UrlAddress, [NewWindow]=@NewWindow, [LinkType]=@LinkType
,[Description]=@Description
WHERE [LinkID]=@LinkID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc User_UpdateList
@ID int,
@Account nvarchar(100),
@ListType int,
@Title nvarchar(255),
@Parameter nvarchar(800),
@SourceUrl   nvarchar(1024),
@Description nvarchar(4000)

AS
UPDATE [User_List]
SET  [Account]=@Account, [ListType]=@ListType, 
    [Title]=@Title, 
    [Parameter]=@Parameter, 
    [SourceUrl]=@SourceUrl,
    [Description]=@Description
WHERE [ID]=@ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE          proc User_UpdateUserConfig
@Account nvarchar(50),
@GroupID int,
@GuestLimits nvarchar(20),
@Encoding  int,
@Server    nvarchar(255),
@Title     nvarchar(1000),
@AvatarImage nvarchar(255),
@Theme       nvarchar(50)='default',  
@Language  nvarchar(255)='zh-CN',
@EnableBlog  bit,
@EnableDisk  bit,
@Layout    varbinary (7500),
@Description nvarchar(1000)
--@Created   DateTime
AS
UPDATE [User_UserConfig]
SET  [GroupID]=@GroupID, 
[GuestLimits]=@GuestLimits, 
[Encoding]=@Encoding, 
 [Server]=@Server, 
[Title]=@Title, 
[Theme]=@Theme,
[Language]=@Language, 
[EnableDisk]=@EnableDisk,
[AvatarImage]=@AvatarImage,
[Description]=@Description,
[Layout] =@Layout --,[Created]=@Created
WHERE [Account]=@Account
UPDATE [NetDisk_Domain]
SET   [Title]=@Title
WHERE [Account]=@Account
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc User_delList
@ID int
AS

DELETE FROM [User_List]
WHERE [ID]=@ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  procedure Visitor_GetList
@Account nvarchar(50),
@Count   int=10
AS
set rowcount @Count
SELECT u.Account,u.[NickName],c.AvatarImage,c.Title,c.[Description],v.VisitorDate,v.IPAddress FROM  [Users] u inner join  [tbl_Visitor] v on (u.Account=v.VisitorAccount)
inner join [User_UserConfig] c on (c.Account=v.VisitorAccount) where v.Account=@Account
order by v.VisitorDate desc

set rowcount 0


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure Visitor_UpdateList
@Account nvarchar(50),
@VisitorAccount nvarchar(50),

@IPAddress      varchar(30),
@VisitorDate    DateTime=null
AS
if(@VisitorDate=null)
set @VisitorDate=Getdate()

if exists(SELECT [Account]  FROM  [tbl_Visitor] where [Account]=@Account and [VisitorAccount]=@VisitorAccount)
begin
UPDATE  [tbl_Visitor]
SET  [VisitorDate]=@VisitorDate, [IPAddress]=@IPAddress 
WHERE [Account]=@Account and [VisitorAccount]=@VisitorAccount
end

else
begin
INSERT INTO [tbl_Visitor]([Account], [VisitorAccount], [VisitorDate], [IPAddress])
VALUES(@Account, @VisitorAccount, @VisitorDate, @IPAddress)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc bbs_AddManage
@ForumID int,
@Account nvarchar(50)
AS

INSERT INTO [BBS_Manage]([ForumID], [Account])
VALUES(@ForumID, @Account)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc bbs_CreateTopic
@Account nvarchar(50),
@ForumID int,
@PersonalForumID int,
@Subject nvarchar(255),
@Content ntext,
@Status int,
@PublishDate DateTime
AS
if not exists (select * from bbs_UserConfig where Account=@Account)
begin
exec bbs_CreateUserConfig @Account
end
INSERT INTO [BBS_Topic](
 [Account],
 [Type], 
[Subject], [Content], [PublishDate], [ForumID], [PersonalForumID], [Status], [ReplyCount], [ReadCount], [LastReplyAccount], [LastReplyDate])
VALUES(@Account, 1, @Subject,
@Content,
@PublishDate,@ForumID,@PersonalForumID,@Status, 0, 0, '',getdate())

declare @LastTopicID int
Set @LastTopicID= @@Identity

UPDATE [BBS_UserConfig]
set [NumTopics]=[NumTopics]+1
WHERE [Account]=@Account

UPDATE [BBS_Forums]
SET   [LastTopicID]=@LastTopicID, [LastPosted]=@PublishDate, 
[LastAccount]=@Account, [LastNickName]=u.NickName, 
[NumTopics]=[NumTopics]+1
from users u 

WHERE  [ID]=@ForumID and u.Account=@Account

select @LastTopicID as TopicID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc bbs_CreateUserConfig
@Account     nvarchar(50),

@Signature   nvarchar(1000)='',
@Approved    bit=1
AS

declare @RankID int

select top 1 @RankID=RankID  from [bbs_Rank] where isStart=1
if(@RankID is null)
 set @RankID =0

if exists (select * from Users where Account=@Account)
begin
INSERT INTO [BBS_UserConfig]([Account],  [signature], [RankID], [Approved], [Theme], [NumTopics], [NumReply])
VALUES(@Account, @Signature,@RankID,@Approved,'default', 0, 0)
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc bbs_GetForums
@ID int =0
AS
if(@ID>0)
SELECT f.*,dbo.f_getForumAdmins(f.ID) as manager FROM  [BBS_Forums] f where f.ParentID=@ID or f.ID=@ID order by f.SortOrder
else
SELECT f.*,dbo.f_getForumAdmins(f.ID) as manager FROM  [BBS_Forums] f  order by f.SortOrder
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE proc bbs_GetPostItem
@ID int,
@IsReply bit
AS

if(@isReply=0)
begin
SELECT 
t.ID,
t.Account,
u.NickName,
c.NumTopics as PostCount,
c.NumReply as ReplyCount,
uc.AvatarImage,
g.Name  as RankName,
g.RankImage,
u.Created,
c.Signature,
t.Subject,
t.Content,
t.PublishDate,
0 AS IsReply
FROM BBS_Topic t
inner join BBS_UserConfig c   on(c.Account=t.Account)
inner join BBS_Rank       g   on(g.RankID=c.RankID)
inner Join User_UserConfig uc on(uc.Account=t.Account)
inner JOIN Users u            ON (u.Account = t.Account)
WHERE t.[ID] = @ID
end
else
begin 
SELECT 
r.ID,
r.Account,
u.NickName,
c.NumTopics as PostCount,
c.NumReply as ReplyCount,
uc.AvatarImage,
g.Name  as RankName,
g.RankImage,
u.Created,
c.Signature,
r.Subject,
r.Content,
r.ReplyDate AS PublishDate,
1 AS IsReply
FROM BBS_Reply r 
inner join BBS_UserConfig c   on(c.Account=r.Account)
inner join BBS_Rank       g   on(g.RankID=c.RankID)
inner Join User_UserConfig uc on(uc.Account=r.Account)
inner JOIN Users u            ON (u.Account = r.Account)
WHERE r.ID = @ID


end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc bbs_GetPostPaged
(
	@PageIndex int,
	@PageSize int,
        @ModuleID  int=0,
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1



Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)
if(@ModuleID>0)
begin
Insert  into #IDs(EntryID)  SELECT [ID] FROM [BBS_Main] where [ModuleID]=@ModuleID
end
else
begin
Insert  into #IDs(EntryID)  SELECT [ID] FROM [BBS_Main]
end


SELECT 	
 
 b.*,u.NickName as NickName, r.NickName as LastReplyUser FROM  [BBS_Main] b
    	INNER JOIN #IDS tmp ON (b.[ID] = tmp.EntryID)
        left join [users] u on (u.Account=b.account)
        left join [users] r on (r.Account=b.LastReplyAccount)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound 
order by  
			 b.Status  
			asc, 
			 b.LastReplyDate desc
SELECT @Count=COUNT(*) FROM 	#IDS 

DROP TABLE #IDS
return @Count
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE      proc bbs_GetPostViewPaged
(
	@PageIndex int,
	@PageSize int,
        @ID  int=0,
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1



Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)

Insert  into #IDs(EntryID)  SELECT [ID] FROM [BBS_Reply] where [TopicID]=@ID
if(@PageIndex=1)
begin
Update BBS_Topic SET [ReadCount]=[ReadCount]+1
WHERE [ID]=@ID
SELECT * FROM
(
SELECT 
t.ID,
t.Account,
u.NickName,
c.NumTopics as PostCount,
c.NumReply as ReplyCount,
uc.AvatarImage,
g.Name  as RankName,
g.RankImage,
u.Created,
c.Signature,
t.Subject,
t.Content,
t.PublishDate,
0 AS IsReply
FROM BBS_Topic t
inner join BBS_UserConfig c   on(c.Account=t.Account)
inner join BBS_Rank       g   on(g.RankID=c.RankID)
inner Join User_UserConfig uc on(uc.Account=t.Account)
inner JOIN Users u            ON (u.Account = t.Account)
WHERE t.[ID] = @ID

UNION ALL

SELECT 
r.ID,
r.Account,
u.NickName,
c.NumTopics as PostCount,
c.NumReply as ReplyCount,
uc.AvatarImage,
g.Name  as RankName,
g.RankImage,
u.Created,
c.Signature,
r.Subject,
r.Content,
r.ReplyDate AS PublishDate,
1 AS IsReply
FROM BBS_Reply r 
inner join #IDS tmp           ON (r.[ID] = tmp.EntryID)
inner join BBS_UserConfig c   on(c.Account=r.Account)
inner join BBS_Rank       g   on(g.RankID=c.RankID)
inner Join User_UserConfig uc on(uc.Account=r.Account)
inner JOIN Users u            ON (u.Account = r.Account)
WHERE r.TopicID = @ID and 
tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound 
) A
ORDER BY IsReply,PublishDate ASC 
end
else
begin 



SELECT 
r.ID,
r.Account,
u.NickName,
c.NumTopics as PostCount,
c.NumReply as ReplyCount,
uc.AvatarImage,
g.Name  as RankName,
g.RankImage,
u.Created,
c.Signature,
r.Subject,
r.Content,
r.ReplyDate AS PublishDate,
1 AS IsReply
FROM BBS_Reply r 
inner join #IDS tmp           ON (r.[ID] = tmp.EntryID)
inner join BBS_UserConfig c   on(c.Account=r.Account)
inner join BBS_Rank       g   on(g.RankID=c.RankID)
inner Join User_UserConfig uc on(uc.Account=r.Account)
inner JOIN Users u            ON (u.Account = r.Account)
WHERE r.TopicID = @ID and 
tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound 

ORDER BY r.PublishDate ASC 
end



SELECT @Count=COUNT(*) FROM 	#IDS 

DROP TABLE #IDS
return @Count
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create   proc bbs_GetTopicPaged
(
	@PageIndex int,
	@PageSize int,
        @ForumID  int=0,
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1



Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)
if(@ForumID>0)
begin
Insert  into #IDs(EntryID)  SELECT [ID] FROM [BBS_Topic] where [ForumID]=@ForumID
end
else
begin
Insert  into #IDs(EntryID)  SELECT [ID] FROM [BBS_Topic]
end


SELECT 	
 
 b.*,u.NickName as NickName, r.NickName as LastReplyUser FROM  [BBS_Topic] b
    	INNER JOIN #IDS tmp ON (b.[ID] = tmp.EntryID)
        left join [users] u on (u.Account=b.account)
        left join [users] r on (r.Account=b.LastReplyAccount)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound 
order by  
			 b.Status  
			asc, 
			 b.LastReplyDate desc
SELECT @Count=COUNT(*) FROM 	#IDS 

DROP TABLE #IDS
return @Count
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc bbs_GetUserForumConfig
@Account nvarchar(50)
AS

if not exists (select * from bbs_UserConfig where Account=@Account)
begin
exec bbs_CreateUserConfig @Account
end
select c.*,u.NickName,r.Name as RankName ,
r.RankImage ,
uc.AvatarImage
 from Users u 
inner join bbs_UserConfig c on (u.Account=c.Account)
Inner join User_UserConfig uc on uc.Account=c.Account
left  join BBS_Rank r
on(r.RankID=c.RankID)
where u.Account=@Account
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc bbs_UpdateUserConfig
@Account     nvarchar(50),
@RankID      int,
@Signature   nvarchar(1000)='',
@AvatarImage nvarchar(255)='',
@Theme       nvarchar(50)='default',
@NumTopics   int ,
@NumReply    int,
@Approved    bit=1
AS


UPDATE [BBS_UserConfig]
SET 
  [signature]=@Signature , 
[RankID]=@RankID, [Approved]=@Approved , 
[Theme]=@Theme, [NumTopics]=@NumTopics, [NumReply]=@NumReply
WHERE [Account]=@Account
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc blog_CreateTag
@Tag nvarchar(255),
@PostID int
AS
declare @TagID int
--Select 
if not exists(SELECT [TagID] FROM [tbl_Tags] where [TagName]=@Tag)
begin
INSERT INTO [tbl_Tags]([TagName]) VALUES(@Tag)
Select @TagID = @@Identity
end
else
begin
Select @TagID =TagID FROM [tbl_Tags] where [TagName]=@Tag
end

if not exists (select * from [tbl_PostToTag] where [PostID]=@PostID and [TagID]=@TagID )
INSERT INTO [tbl_PostToTag]([PostID], [TagID])
VALUES(@PostID,@TagID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc blog_DelCategory
@CategoryID int
AS
DELETE FROM [blog_Categories]
WHERE [CategoryID]=@CategoryID
DELETE FROM [blog_Links]
WHERE [CategoryID]=@CategoryID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE             proc blog_DelContent
@ID int
AS
declare @ParentID int
declare @PostType int 
SELECT  @ParentID=[ParentID],@PostType=PostType FROM [Blog_Content] where [ID]=@ID
DELETE FROM [Blog_Content]
WHERE  [ID]=@ID

DELETE FROM [blog_Links]
WHERE [PostID]=@ID

if(@ID>0)
DELETE FROM [Blog_Content]
WHERE [ParentID]=@ID



if(@PostType=8 or @PostType=4)
begin
UPDATE [Blog_Content]
SET  [FeedBackCount]=[FeedBackCount]-1
WHERE [ID]=@ParentID
end

exec blog_DelPostTag @ID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc blog_DelPostTag
@PostID int
AS

DELETE FROM [tbl_EntityToTag] WHERE TagType=1 and [EntityID]=@PostID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc blog_DelTag
@Tag nvarchar(255),
@PostID int
AS
declare @TagID int
Select @TagID =TagID FROM [tbl_Tags] where [TagName]=@Tag
DELETE FROM [tbl_PostToTag] WHERE [TagID]=@TagID and [PostID]=@PostID
if not exists(Select TagID FROM [tbl_PostToTag] WHERE [TagID]=@TagID)--检查是否还有引用标签
begin 
DELETE FROM [tbl_Tags] WHERE [TagID]=@TagID
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE         Proc blog_GetAllPagedFeedBack 
(
        @PageIndex int,
	@PageSize int,
	@BlogID int,
        @Count    int output 
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)
Insert  into #IDs(EntryID) SELECT [ID] FROM blog_Content
WHERE  blog_Content.BlogID = @BlogID and (PostType=8 or PostType=4) order by [ID] Desc

SELECT 	

c.*,bc.Account,c.Text as Content
FROM  	blog_content c
    	INNER JOIN #IDs tmp ON (c .[ID] = tmp.EntryID)
        inner join Blog_Content c1 on (c.parentID=c1.[ID])
        inner join Blog_Config bc  on(c1.BlogID=bc.BlogID)
WHERE 	 tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY c .[ID] desc
SELECT @Count=COUNT(*) FROM 	#IDS 
DROP TABLE #IDS
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE               Proc blog_GetAllPostsByMonth -- 2,2003,0
(
        @BlogID int,
	@Month int,
	@Year int
)
as
if(@BlogID>0)
begin
SELECT c.* ,v.* FROM blog_Content c left join blog_viewCount v on v.PostID=c.ID
WHERE c.PostType=1   and c.BlogID=@BlogID 
	and Month(c.DateAdded)  = @Month and Year(c.DateAdded)  = @Year
ORDER BY c.DateAdded desc
end 
else
begin
SELECT c.*,v.* FROM blog_Content c left join blog_viewCount v on v.PostID=c.ID
WHERE c.PostType=1  
	and Month(c.DateAdded)  = @Month and Year(c.DateAdded)  = @Year
ORDER BY c.DateAdded desc
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create   proc blog_GetBlogCategory
@CategoryID int
AS
SELECT * FROM 
[blog_Categories] where [CategoryID]=@CategoryID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc blog_GetBlogCategoryByAccout
@Account nvarchar(50)
AS
SELECT * FROM 
[blog_Categories] bc join [Blog_Config] con on (bc.blogID=con.BlogID)
 where con.[Account]=@Account
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc blog_GetBlogCategoryByBlogID
@BlogID int
AS
SELECT * FROM [blog_Categories] where [blogID]=@BlogID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create  proc blog_GetBlogCategoryByPostID
@PostID int
AS
SELECT c.* FROM [blog_Categories] c 
join [blog_Links] l 
on (c.CategoryID=l.CategoryID and PostID=@PostID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE            Proc blog_GetFeedBack 
(
	@ParentID int,
	@BlogID int=null
)
as
SELECT *,0 as WebCount,0 as AggCount FROM blog_Content
WHERE  blog_Content.ParentID = @ParentID
ORDER BY [ID] desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create proc blog_GetLinksByBlogID
@BlogID int
AS
SELECT [LinkID], [CategoryID], [BlogID], [PostID], [NewWindow], [Active], [Updated] FROM [blog_Links]
where blogID=@BlogID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE          PROC blog_GetPagedByTag
(
	@PageIndex int,
	@PageSize int,
        @BlogID   int=0,
        @TagName nvarchar(255)='',
        @Hiding     bit =0,
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)



if(@BlogID>0)
begin
Insert  into #IDs(EntryID)  select DISTINCT [ID] from view_Content  where [ID] in(
SELECT pt.EntityID FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID and tag.TagName=@TagName and pt.TagType=1 join blog_Content bc on bc.ID=pt.EntityID and  bc.BlogID=@BlogID and PostType=1 and bc.hiding=0
) 
 order by [ID] desc

end
else
begin
Insert  into #IDs(EntryID)  select DISTINCT [ID] from view_Content  where hiding=0 and [ID] in(SELECT pt.EntityID FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID and tag.TagName=@TagName and pt.TagType=1)   order by [ID] desc
end 



SELECT 	
 
vc.*

FROM  	View_Content vc 
    	INNER JOIN #IDS tmp ON (vc .[ID] = tmp.EntryID)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY tmp.TempID
SELECT @Count=COUNT(*) FROM 	#IDS 
DROP TABLE #IDS
return @Count
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE    Proc blog_GetPagedFeedBack 
(
        @PageIndex int,
	@PageSize int,
	@ParentID int,
        @Count    int output 
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)
Insert  into #IDs(EntryID) select DISTINCT [ID] FROM blog_Content
WHERE  blog_Content.ParentID = @ParentID ORDER BY blog_content .[ID] desc

SELECT 	c.*,bc.Account	FROM  blog_Content c inner join 
blog_Config bc on (c.blogID=bc.blogID)
	WHERE 
		c.[ID]=@ParentID
if(@PageIndex=1)
begin
UPDATE Blog_Content
SET    Blog_Content.WebCount=Blog_Content.WebCount+1   , Blog_Content.WebLastUpdated=getdate()
WHERE Blog_Content.[ID]=@ParentID
end

SELECT 	

c.*,bc.Account
FROM  	blog_content c
    	INNER JOIN #IDs tmp ON (c .[ID] = tmp.EntryID)
        inner join Blog_Config bc  on(c.BlogID=bc.BlogID)
WHERE 	c.ParentID = @ParentID and tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY c .[ID] desc
SELECT @Count=COUNT(*) FROM 	#IDS 
DROP TABLE #IDS
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE            PROC blog_GetPagedPosts
(
	@PageIndex int,
	@PageSize int,
        @BlogID   int=0,
        @PostType int=-1,
        @CategoryID int=-1,
        @Hiding     bit =0,
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)

Declare @PagedSQL nvarchar(4000)
declare @Hide  int


set @PagedSQL='Insert  into #IDs(EntryID)  select DISTINCT [ID] from view_Content  where 1=1 '

if(@Hiding=1)
set @PagedSQL=@PagedSQL+ ' and [Hiding]=0  '

if(@BlogID>0)
set @PagedSQL=@PagedSQL+' and  [BlogID]='+cast(@BlogID as varchar)+'  '

if(@PostType!=-1)
set @PagedSQL=@PagedSQL+' and [PostType]='+cast(@PostType as varchar)+' '

if(@CategoryID>0)
set @PagedSQL=@PagedSQL+' and [CategoryID]='+cast(@CategoryID as varchar)+' '


set @PagedSQL=@PagedSQL+' order by [ID] desc'

exec sp_executesql @PagedSQL
SELECT 	
 
vc.* FROM  	View_Content vc 
    	INNER JOIN #IDS tmp ON (vc .[ID] = tmp.EntryID)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound 
ORDER BY tmp.TempID
SELECT @Count=COUNT(*) FROM 	#IDS 

DROP TABLE #IDS
return @Count
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create        PROC blog_GetPagedPosts_test
(
	@PageIndex int,
	@PageSize int,
        @BlogID   int=0,
        @PostType int=-1,
        @CategoryID int=-1,
        @Hiding     bit =0 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)
if(@PostType=-1)
begin
if(@CategoryID>0)
Insert  into #IDs(EntryID)  select [ID] from view_Content  where CategoryID=@CategoryID and blogID=@BlogID   order by DateAdded desc
else
Insert  into #IDs(EntryID)  select [ID] from view_Content where blogID=@BlogID  order by DateAdded desc
end 
else
begin
if(@CategoryID>0)
Insert  into #IDs(EntryID)  select [ID] from view_Content  where CategoryID=@CategoryID and blogID=@BlogID and PostType=@PostType order by DateAdded desc
else
Insert  into #IDs(EntryID) select [ID] from view_Content where blogID=@BlogID and PostType=@PostType  order by DateAdded desc
end
SELECT 	
	--Host, Application, 
blog_Content.*,
--(vc.WebCount+vc.AggCount) as ViewCount
	vc.*
FROM  	blog_content 
    	INNER JOIN #IDS tmp ON (blog_content .[ID] = tmp.EntryID)
	Left outer JOIN  blog_ViewCount vc  ON (blog_Content.[ID] = vc.PostID)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY tmp.TempID
DROP TABLE #IDS
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE         Proc blog_GetPostByID
(
	@PostID int,
	@BlogID int
)
as
/*if(@BlogID>0)
begin
SELECT 
		bc.*,vc.* 
	FROM 
		blog_Content bc
		Left JOIN  blog_ViewCount vc ON (bc.[ID] = vc.PostID )
	WHERE 
		bc.ID=@PostID and bc.BlogID=@BlogID
end
else
*/
begin
SELECT 
		*
	FROM 
		view_Content
	WHERE 
		[ID]=@PostID
end 



UPDATE [blog_ViewCount]
SET   [WebCount]=[WebCount]+1, [WebLastUpdated]=Getdate()
WHERE [PostID]=@PostID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc blog_GetSiteStatus
AS

select (select count(UserID) from [Users]) as UserCount ,
(select isnull(count(ID),0) from [Blog_Content] where PostType=1) as PostCount,
(select count(ID) from [Blog_Content] where PostType=8) as CommentCount,
(select count(DirectoryID) from [NetDisk_Directory]) as DirectoryCount,
(select count(FileID) from [NetDisk_Resource]) as FileCount,
(select isnull(sum(FileSize),0) from [NetDisk_Resource]) as FileSize,
(select count(ID) from [NetDisk_filekey]) as FileKeyCount,
(select count(TagID) from [tbl_Tags]) as TagCount,
(select count(TagID) from [tbl_EntityToTag]) as TagRefCount,
(select count(CategoryID) from [blog_Categories]) as BlogCategoryCount,
(select isnull(sum(WebCount)+sum(AggCount),0) from [blog_Content]) as PostHitCount,
(select count(LinkID) from [User_Links]) as LinkCount,
(select count(ID) from [User_List]) as ListCount
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE     Proc blog_GetTopic 
(
        @PageIndex int,
	@PageSize int,
	@ParentID int,
        @Count    int output 
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID int not null
)
Insert  into #IDs(EntryID) select DISTINCT [ID] FROM blog_Content
WHERE  blog_Content.ParentID = @ParentID ORDER BY blog_content .[ID] desc

SELECT 	c.*,bc.Account	FROM  blog_Content c inner join 
blog_Config bc on (c.blogID=bc.blogID)
	WHERE 
		c.[ID]=@ParentID
if(@PageIndex=1)
begin
UPDATE Blog_Content
SET    Blog_Content.WebCount=Blog_Content.WebCount+1   , Blog_Content.WebLastUpdated=getdate()
WHERE Blog_Content.[ID]=@ParentID
end

SELECT 	

c.*,isnull(bc.Account,'') as Account,c.Text as Content
FROM  	blog_content c
    	INNER JOIN #IDs tmp ON (c .[ID] = tmp.EntryID)
        left join Blog_Config bc  on(c.BlogID=bc.BlogID)
WHERE 	  tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY c .[ID] desc
SELECT @Count=COUNT(*) FROM 	#IDS 
DROP TABLE #IDS
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create      PROC blog_GetUserConfigPaged
(
	@PageIndex int,
	@PageSize int,
        @GroupID   int=0,
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID nvarchar(50) not null
)



if(@GroupID>0)
begin
Insert  into #IDs(EntryID)  select [Account] from view_Users where GroupID=@GroupID order by  Created desc

end
else
begin
Insert  into #IDs(EntryID)  select [Account] from view_Users  order by  Created desc
end 




SELECT uc.* ,ng.[GroupName], ng.[TotalSize]
,bc.BlogID,bc.ShowPostCount,bc.ShowCommentCount, dir.DirectoryID as RootDirID
FROM [User_UserConfig] uc
join  [User_Group]  ng on ( uc.GroupID=ng.GroupID)
left join  [Blog_Config]  bc on (bc.Account=uc.Account)
left join [NetDisk_Directory] dir on (dir.Account=uc.Account and dir.ParentID=0)
    	INNER JOIN #IDS tmp ON (uc .[Account] = tmp.EntryID)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY tmp.TempID
SELECT @Count=COUNT(*) FROM 	#IDS 
DROP TABLE #IDS
return @Count
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE     PROC blog_GetUserPaged
(
	@PageIndex int,
	@PageSize int,
        @GroupID   int=0,
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID nvarchar(50) not null
)



if(@GroupID>0)
begin
Insert  into #IDs(EntryID)  select [Account] from view_Users where GroupID=@GroupID order by  Created desc

end
else
begin
Insert  into #IDs(EntryID)  select [Account] from view_Users  order by  Created desc
end 




SELECT u.* from [Users] u
    	INNER JOIN #IDS tmp ON (u.[Account] = tmp.EntryID)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY tmp.TempID
SELECT @Count=COUNT(*) FROM 	#IDS 
DROP TABLE #IDS
return @Count
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE      Proc blog_InsertCategory
(
	@Title nvarchar(150),
	@Active bit,
	@BlogID int,
	@CategoryType tinyint,
	@Description nvarchar(1000),
	@ParentID int=null,
	@CategoryID int output
)
as
Set NoCount On
INSERT INTO blog_Categories ( Title, Active, CategoryType, [Description], BlogID,ParentID,UpdateTime )
VALUES (@Title,@Active, @CategoryType, @Description, @BlogID,@ParentID,getdate())
Select @CategoryID = @@Identity
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create  Proc blog_InsertViewCount-- 1, 0, 1
(
	@PostID int,
	@BlogID int,
	@IsWeb bit=1
)
as
Begin
	--Do we have an existing entry in the blog_InsertEntryViewCount table?
	if exists(Select PostID From blog_ViewCount where PostID = @PostID and BlogID = @BlogID)
	begin
		if(@IsWeb = 1) -- Is this a web view?
		begin
			Update blog_ViewCount
			Set [WebCount] = [WebCount] + 1, WebLastUpdated = getdate()
			where PostID = @PostID and BlogID = @BlogID
		end
		else
		begin
			Update blog_ViewCount
			Set [AggCount] = [AggCount] + 1, AggLastUpdated = getdate()
			where PostID = @PostID and BlogID = @BlogID
		end
	end
	else
	begin
		if(@IsWeb = 1) -- Is this a web view
		begin
			Insert blog_ViewCount (PostID, BlogID, WebCount, AggCount, WebLastUpdated, AggLastUpdated)
		       values (@PostID, @BlogID, 1, 0, getdate(), null)
		end
		else
		begin
			Insert blog_ViewCount (PostID, BlogID, WebCount, AggCount, WebLastUpdated, AggLastUpdated)
		       values (@PostID, @BlogID, 0, 1, null, getdate())
		end
	end
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc blog_UpdateCategory
        @CategoryID int,
        @Title nvarchar(150),
	@Active bit,
	@BlogID int,
	@CategoryType tinyint,
	@Description nvarchar(1000),
	@ParentID int=null
	AS
UPDATE [blog_Categories]
SET  [Title]=@Title, [Active]=@Active, [BlogID]=@BlogID, 
[CategoryType]=@CategoryType, [Description]=@Description,
 [ParentID]=@ParentID, [UpdateTime]=getdate()
WHERE [CategoryID]=@CategoryID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure blog_delLink
@PostID int
AS 

DELETE FROM [blog_Links]
WHERE [PostID]=@PostID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE    proc  blog_getNewPostes
@Count int
AS
set nocount on
declare @s Nvarchar(4000)

set @s ='select top '+cast(@Count as varchar)+' t.*,c.Account,u.NickName from blog_Content t inner join blog_Config c on(c.blogID=t.BlogID) inner join [Users] u on (u.Account=c.Account)  where t.postType=1 and t.hiding<>1 and t.Author<>''系统'' order by t.[DateAdded] desc'
exec sp_executesql @s

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc blog_getTagTop
@BlogID int=0,
@Count  int=10
AS
SET ROWCOUNT @Count
if(@BlogID>0)
begin

SELECT  tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID 
join Blog_Content bc on bc.ID=pt.EntityID and pt.TagType=1 and bc.BlogID=@BlogID
group by tagName order by RefCount desc
end
else
begin
SELECT  tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID 

group by tagName order by RefCount desc
end
SET ROWCOUNT 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc sfsdfsdfsdfsdfsdf
as
select (select count(*) from Users ) as UserCount
,(select count(*) from bbs_Topic) as TopicCount
,(select count(*) from bbs_Reply )as ReplyCount
,(select max(ID) from bbs_Topic) as LastTopicID
,(select max(ID) from bbs_Reply) as LastReplyID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  PROCEDURE sp_GetFilePath
@FileKey nvarchar(100) = NULL
 AS
DECLARE @DownCount int
DECLARE @EndTime   DateTime
select @DownCount=DownCount,@EndTime=EndTime from NetDisk_filekey where (FileKey=@FileKey)
if(@DownCount>0 and @EndTime>GetDate())
begin
UPDATE [NetDisk_filekey] SET DownCount=DownCount-1 where (FileKey=@FileKey)
select * from NetDisk_filekey where (FileKey=@FileKey)
end
else
begin
DELETE FROM [NetDisk_filekey] WHERE (FileKey=@FileKey)
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  procedure spaces_CreateDisallowedWord

@DisallowedWord nvarchar(255),
@Type  int
AS

if not exists(SELECT * FROM [tbl_DisallowedWords] where [DisallowedWord]=@DisallowedWord and [Type]=@Type)

begin
INSERT INTO [tbl_DisallowedWords]([DisallowedWord], [Type])
VALUES(@DisallowedWord, @Type )
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE   proc spaces_CreateTag
@Tag nvarchar(255),
@EntityID int,
@TagType  int
AS
declare @TagID int
--Select 
if not exists(SELECT [TagID] FROM [tbl_Tags] where [TagName]=@Tag)
begin
INSERT INTO [tbl_Tags]([TagName]) VALUES(@Tag)
Select @TagID = @@Identity
end
else
begin
Select @TagID =TagID FROM [tbl_Tags] where [TagName]=@Tag
end

if not exists (select * from [tbl_EntityToTag] where [EntityID]=@EntityID and [TagID]=@TagID and [TagType]=@TagType)
INSERT INTO [tbl_EntityToTag]([EntityID], [TagID],[TagType])
VALUES(@EntityID,@TagID,@TagType)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure spaces_DelDisallowedWords
@Type int
AS

if(@Type>0)
DELETE FROM  [tbl_DisallowedWords]
WHERE [Type]=@Type
else
DELETE FROM  [tbl_DisallowedWords]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create     proc spaces_DelEntityTag
@EntityID int,
@TagType int
AS

DELETE FROM [tbl_EntityToTag] WHERE TagType=@TagType and [EntityID]=@EntityID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure spaces_GetDisallowedWords
@Type int
As

select * from [tbl_DisallowedWords] where Type=@Type
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  PROCEDURE spaces_SiteSettings_Get

AS



 IF NOT EXISTS (SELECT SiteID FROM tbl_SiteSettings)
	RETURN
ELSE
	SELECT
		top 1 * 
	FROM
		tbl_SiteSettings
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE procedure spaces_SiteSettings_Save
(
	@DisabledSpaces  	bit,
	@Settings 		varbinary(8000)
)
AS
BEGIN

	IF EXISTS (SELECT top 1 * FROM tbl_SiteSettings )
		UPDATE
			tbl_SiteSettings
		SET
			DisabledSpaces = @DisabledSpaces ,
			Settings = @Settings
		 
	ELSE
		INSERT INTO
			tbl_SiteSettings
			(
 
				DisabledSpaces,
				Settings
			)
		VALUES
			(
				 
				@DisabledSpaces,
				@Settings
			)
	
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  proc spaces_getTagTop
@BlogID int=0,
@TagType int=1,
@Count  int=10
AS
SET ROWCOUNT @Count
if(@BlogID>0)
begin

 if(@TagType>0)
SELECT  tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID 
join Blog_Content bc on bc.ID=pt.EntityID and pt.TagType=@TagType and bc.BlogID=@BlogID
group by tagName order by RefCount desc
else
SELECT  tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID 
join Blog_Content bc on bc.ID=pt.EntityID   and bc.BlogID=@BlogID
group by tagName order by RefCount desc
end
else
begin
 if(@TagType>0)
SELECT  tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID 
and pt.TagType=@TagType
group by tagName order by RefCount desc
else
SELECT  tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID 
group by tagName order by RefCount desc
end
SET ROWCOUNT 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  procedure tag_GetTagTop 
@Account nvarchar(50),
@TagType int=1,
@Count  int=10
AS
declare @sql nvarchar(4000)
if(@TagType=0)
begin 
set @sql='SELECT top '+cast(@Count as varchar)+' tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID 
group by tagName order by RefCount desc'
end
else if(@TagType=1)
begin
 
set @sql='SELECT top '+cast(@Count as varchar)+' tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag join [tbl_EntityToTag] pt on tag.TagID=pt.TagID and pt.TagType=1'

if(@Account<>'*')
begin
set @sql=@sql+'inner join Blog_Content bc on bc.ID=pt.EntityID inner join blog_Config cc on (bc.blogID=cc.blogID)  '
set @sql=@sql+' and cc.Account='''+@Account+ ''''

end 
set @sql=@sql+'  group by tagName order by RefCount desc'
end

else if(@TagType=2)
begin
set @sql='SELECT  top '+cast(@Count as varchar)+' tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag inner join [tbl_EntityToTag] pt on tag.TagID=pt.TagID 
and pt.TagType=2 and pt.EntityID in(SELECT [FileID] FROM [NetDisk_Resource] r inner join [NetDisk_Directory] d on(d.DirectoryID=r.DirectoryID)  '
if(@Account<>'*')
set @sql=@sql+' and d.Account='''+@Account+ ''''
set @sql=@sql+' ) group by tagName order by RefCount desc'

end

else if(@TagType=3)
begin
set @sql='SELECT  top '+cast(@Count as varchar)+' tag.TagName ,Count(tag.TagID) as RefCount FROM [tbl_Tags] tag inner join [tbl_EntityToTag] pt on tag.TagID=pt.TagID 
and pt.TagType=3 and pt.EntityID in(SELECT [PhotoID] FROM [Photo_Data] d inner join [Photo_Categories] c on (d.CategoryID=c.ID)   '
if(@Account<>'*')
set @sql=@sql+' and c.Account='''+@Account+ ''''
set @sql=@sql+' ) group by tagName order by RefCount desc'
end

exec sp_executesql @sql


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE      PROC user_GetUserInAlphabetical
(
	@PageIndex int,
	@PageSize int,
        @Letter   nvarchar(1),
        @Count    int output 
        
)
as
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
Create Table #IDs
(
	TempID int IDENTITY (1, 1) NOT NULL,
	EntryID nvarchar(50) not null
)




begin
Insert  into #IDs(EntryID)  select [UserID] from view_Users where [Account] Like @Letter+'%' order by  Created desc

end



SELECT u.* from [view_Users] u
    	INNER JOIN #IDS tmp ON (u.[UserID] = tmp.EntryID)
WHERE 	tmp.TempID > @PageLowerBound 
	AND tmp.TempID < @PageUpperBound
ORDER BY tmp.TempID
SELECT @Count=COUNT(*) FROM 	#IDS 
DROP TABLE #IDS
return @Count
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE       proc user_delUser
@Account nvarchar(50)
AS
Declare @BlogID int 

Select @BlogID=[BlogID] from Blog_Config WHERE [Account]=@Account 

delete From Tbl_EntityToTag where EntityID  --删除标记键接
in( Select [ID] from blog_Content where postType=1 and BlogID=@BlogID) and TagType=1

delete From Tbl_EntityToTag where EntityID  --删除标记键接
in(SELECT [PhotoID] FROM [Photo_Data] d inner join [Photo_Categories] c on (d.CategoryID=c.ID) and c.Account=@Account) 
 and TagType=3

delete From Tbl_EntityToTag where EntityID  --删除标记键接
in(SELECT [FileID] FROM [NetDisk_Resource] r inner join [NetDisk_Directory] d on(d.DirectoryID=r.DirectoryID) and d.Account=@Account)  
 and TagType=2

DELETE FROM [blog_Links] --删除主题目录映射
WHERE BlogID=@BlogID

DELETE FROM [blog_Categories] --删除目录
WHERE BlogID=@BlogID

 

delete From blog_Content where  [ParentID] --删除主题和回复
in (Select [ID] from blog_Content where postType=1 and BlogID=@BlogID) or BlogID=@BlogID 

delete From NetDisk_Resource where --删除用户文件
DirectoryID in(SELECT [DirectoryID] FROM [NetDisk_Directory] where [Account]=@Account)

delete From [NetDisk_Directory] where --删除目录
 [Account]=@Account

DELETE FROM [User_Links] --删除用户自定义连接
WHERE [Account]=@Account 

DELETE FROM [NetDisk_filekey] --删除文件key
WHERE [Account]=@Account 

DELETE FROM [User_List] --删除自定义列表
WHERE  [Account]=@Account 

DELETE FROM [Users]   --删除用户
WHERE [Account]=@Account 


Delete From [User_UserConfig] --删除用户配置
WHERE [Account]=@Account 

DELETE FROM [NetDisk_Domain]  --删除域
WHERE [Account]=@Account

DELETE FROM [Photo_Data]    --删除相片
where [CategoryID] in (select [ID] FROM [Photo_Categories] where [Account]=@Account )

DELETE FROM [Photo_Categories] --删除图片目录 
where [Account]=@Account

DELETE FROM [tbl_Visitor] --删除访客
where [Account]=@Account


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

INSERT INTO [User_Group](  [GroupName], [TotalSize], [MonthPrice], [DefaultGroup], [Active])
VALUES(  'default', 10, 1, 1, 1);
GO