SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_EXTAND_GETID]
    @Name varchar(255) = null
AS
	Return (Select ExtandId From DB_EXTAND Where Name=@Name);



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [ET_EXTAND_DETAIL]
    @ExtandId int = 0
AS
	Select * From DB_EXTAND Where ExtandId=@ExtandId;
	Return 0;






GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
















CREATE PROCEDURE [ET_ATTRIBUTE_TAGS]
	@Key varchar(255) = null,
    @AttributeId int = 0,
	@Order varchar(255) = null
AS
	--	声明变量
	Declare @SqlSelect varchar(555)
	Declare @SqlWhere varchar(255)
	Declare @SqlOrder varchar(255)
	Declare @ReturnSql nvarchar(255)
	Declare @ReturnValue int
	--	初始化变量
	Set @SqlSelect = ', (Select Name From DB_ATTRIBUTE Where AttributeId=DB_ATTRIBUTE_TAG.AttributeId) As AttributeName, ';
	Set @SqlSelect = @SqlSelect + ' VouchName=(Case When Vouch=1 Then ''新鲜'' When Vouch=2 Then ''常用'' When Vouch=3 Then ''热门'' When Vouch=4 Then ''推荐'' When Vouch=5 Then ''置顶'' Else ''普通'' End) ';
	Set @SqlWhere = '';
	Set @SqlOrder = '';
	Set @ReturnSql = '';
	--	指定属性
	If @AttributeId>0
		Set @SqlWhere = ' Where AttributeId=' + lTrim(@AttributeId) + ' ';
	--	是否搜索关键字
	If @Key is not NULL And @Key<>''
		Set @SqlWhere = @SqlWhere + ' And (Name=''' + @Key + ''' Or Near Like ''%' + @Key + '%'') ';
	--	指定排序规则
	If @Order<>''
		Set @SqlOrder = ' Order By ' + @Order;
	Else
		Set @SqlOrder = ' Order By Last Desc';
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_ATTRIBUTE_TAG ' + @SqlWhere + ')'
	exec ('Select * ' + @SqlSelect +  ' From DB_ATTRIBUTE_TAG ' + @SqlWhere + ' ' + @SqlOrder);
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;








GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_STORAGE_GETID]
    @Name varchar(255) = null
AS
	Return (Select StorageId From DB_STORAGE Where Name=@Name);


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_GRADE_GETID]
    @Name varchar(255) = null
AS
	If @Name=''
		Return (Select GradeId From DB_GRADE Where IsDefault=1);
	Else	
		Return (Select GradeId From DB_GRADE Where Name=@Name);




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

























CREATE PROCEDURE [ET_USER_SAME]
	@GradeId int = 0,
	@LevelId int = 0,
	@DefaultHeader varchar(255) = null
AS
	--	声明变量
	Declare @SqlSelect varchar(1000)
	Declare @SqlWhere varchar(1000)
	Declare @ReturnSql nvarchar(1000)
	Declare @ReturnValue int
	--	初始化变量
	Set @SqlSelect = '';
	Set @ReturnSql = '';
	Set @SqlWhere = '';
	--	组合显示视图
	Set @SqlWhere = ' Where GradeId=' + lTrim(@GradeId) + ' Or LevelId=' + lTrim(@LevelId) + '';
	Set @SqlSelect = @SqlSelect + ', (Select Name From DB_GRADE Where GradeId=DB_USER.GradeId) As GradeName';
	Set @SqlSelect = @SqlSelect + ', (Select Name From DB_LEVEL Where LevelId=DB_USER.LevelId) As LevelName';
	Set @SqlSelect = @SqlSelect + ', StateName = CASE WHEN State=1 Then ''在线'' When State=2 Then ''隐身'' When State=3 Then ''忙碌'' Else ''离线'' End  ';
	Set @SqlSelect = @SqlSelect + ', SexName = CASE WHEN Sex=1 Then ''男'' When Sex=2 Then ''女'' Else ''未知'' End ';
	Set @SqlSelect = @SqlSelect + ', CONVERT(varchar, Birthday, 23) As BirthdayDate '
	Set @SqlSelect = @SqlSelect + ', HeaderUrl = CASE WHEN Header is null Or Header='''' Then ''' + @DefaultHeader + ''' Else Header End '
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_USER ' + @SqlWhere + ')'
	exec ('Select *' + @SqlSelect + ' From DB_USER ' + @SqlWhere + ' Order By Last Desc');
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;

























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [ET_GRADE_DETAIL]
    @GradeId int = 0
AS
	Select * From DB_GRADE Where GradeId=@GradeId;
	Return 0;








GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_LEVEL_GETID]
    @Name varchar(255) = null
AS
	If @Name=''
		Return (Select LevelId From DB_LEVEL Where IsDefault=1);
	Else
		Return (Select LevelId From DB_LEVEL Where Name=@Name);




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










CREATE PROCEDURE [ET_FILE_DETAIL]
    @FileId int = 0
AS
	Select *
	,(Select Name From DB_CLASS Where ClassId=DB_FILE.ClassId) As ClassName
	,(Select Name From DB_STORAGE Where StorageId=DB_FILE.StorageId) As StorageName
	,(Select Path From DB_STORAGE Where StorageId=DB_FILE.StorageId) As StoragePath
	,(Select Name From DB_FOLDER F Where F.FolderId=DB_FILE.FolderId) As ParentName
	From DB_FILE Where FileId=@FileId;
	Return 0;










GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_POPEM_REMOVE]
	@OwnerId int = 0,
	@OwnerType int = 0,
	@ResourceId int = 0,
	@ResourceType int = 0
AS
	IF (Select Count(*) From DB_POPEM Where OwnerId=@OwnerId And OwnerType=@OwnerType And ResourceId=@ResourceId And ResourceType=@ResourceType)>0 BEGIN
		Delete From DB_POPEM Where OwnerId=@OwnerId And OwnerType=@OwnerType And ResourceId=@ResourceId And ResourceType=@ResourceType;
		Return 0;
	END
	ELSE
		Return 1;







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




























CREATE PROCEDURE [ET_MESSAGE_LIST]
	@Key varchar(255) = null,
    @OwnerId int = 0,
	@State int = 0,
	@Order varchar(255) = null
AS
	--	声明变量
	Declare @SqlSelect varchar(1000)
	Declare @SqlWhere varchar(1000)
	Declare @SqlOrder varchar(1000)
	Declare @ReturnSql nvarchar(1000)
	Declare @ReturnValue int
	--	初始化变量
	Set @SqlSelect = '';
	Set @SqlWhere = '';
	Set @SqlOrder = '';
	Set @ReturnSql = '';
	--	组合显示视图
	Set @SqlSelect = @SqlSelect + ', StateName = CASE WHEN State=0 Then ''未读'' When State=1 Then ''已读'' Else ''未知'' End  ';
	Set @SqlSelect = @SqlSelect + ', (Select State From DB_USER Where UserId=DB_MESSAGE.SendId) As UserState  ';
	--	收信人查询
	Set @SqlWhere = ' Where RecvId=' + lTrim(@OwnerId) + ' ';
	--	关键字查询
	If @Key<>''
		Set @SqlWhere = @SqlWhere + ' And (SendUsername=''' + @Key + ''' Or Name Like''%' + @Key + '%'') ';
	--	是否检索状态
	If @State=-1
		Set @SqlWhere = @SqlWhere + ' And State=0 ';
	If @State>0
		Set @SqlWhere = @SqlWhere + ' And State=' + lTrim(@State) + ' ';
	--	组合排序规则
	If @Order<>''
		Set @SqlOrder = ' Order By ' + @Order;
	Else
		Set @SqlOrder = ' Order By First Desc';
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_MESSAGE ' + @SqlWhere + ')'
	exec ('Select *' + @SqlSelect + ' From DB_MESSAGE ' + @SqlWhere + ' ' + @SqlOrder);
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;




























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





















CREATE PROCEDURE [ET_MESSAGE_FILE_LIST]
    @MessageId int = 0
AS
	--	声明变量
	Declare @SqlWhere varchar(1000)
	Declare @ReturnSql nvarchar(1000)
	Declare @ReturnValue int
	--	初始化变量
	Set @SqlWhere = '';
	Set @ReturnSql = '';
	--	收信人查询
	Set @SqlWhere = ' Where MessageId=' + lTrim(@MessageId) + ' ';
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_MESSAGE_FILE ' + @SqlWhere + ')'
	exec ('Select * From DB_MESSAGE_FILE ' + @SqlWhere + ' Order By Last Desc');
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;



















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










CREATE PROCEDURE [ET_RESOURCE_GETPATH]
    @StorageId int = 0,
	@ParentId int = 0,
	@FolderPath varchar(255) output
AS
	If (Select Count(*) From DB_STORAGE Where StorageId=@StorageId)>0 Begin
		Declare @StorageName varchar(255);
		Set @StorageName = (Select Name From DB_STORAGE Where StorageId=@StorageId);
		while @ParentId>0 Begin
			If (Select Count(*) From DB_FOLDER Where FolderId=@ParentId)>0 Begin
				If @FolderPath<>''
					Set @FolderPath = (Select Name From DB_FOLDER Where FolderId=@ParentId) + '/' + @FolderPath;
				Else
					Set @FolderPath = (Select Name From DB_FOLDER Where FolderId=@ParentId) + '/';
				Set @ParentId = (Select ParentId From DB_FOLDER Where FolderId=@ParentId);
			End
			Else
				Set @ParentId = 0;
		End
		If @FolderPath<>''
			Set @FolderPath = @StorageName + '>' + @FolderPath;
		Else
			Set @FolderPath = @StorageName + '>';
	End
	Else
		Set @FolderPath = Null;
	










GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_CLASS_GETID]
    @Name varchar(255) = null
AS
	Return (Select ClassId From DB_CLASS Where Name=@Name);


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [ET_CLASS_DETAIL]
    @ClassId int = 0
AS
	Select * From DB_CLASS Where ClassId=@ClassId;
	Return 0;





GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO











CREATE PROCEDURE [ET_CLASS_LIST]
AS
	Select * From DB_CLASS Order By ClassId Asc;
	Return 0;











GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_ATTRIBUTE_GETID]
    @Name varchar(255) = null
AS
	Return (Select AttributeId From DB_ATTRIBUTE Where Name=@Name);


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_USER_GETID]
    @Username varchar(255) = null
AS
	Return (Select UserId From DB_USER Where Username=@Username);


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







CREATE PROCEDURE [ET_ATTRIBUTE_DETAIL]
    @AttributeId int = 0
AS
	Select *, 
	(Select Name From DB_CLASS Where ClassId=DB_ATTRIBUTE.ClassId) As ClassName 
	From DB_ATTRIBUTE Where AttributeID=@AttributeId;
	Return 0;







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_FILE_EXISTS]
	@StorageId int = 0,
    @Filename varchar(255) = null
AS
	Return (Select Count(*) From DB_FILE Where Filename=@Filename And StorageId=@StorageId);





GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

















-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_USER_EDIT]
	@UserId int = 0,
    @Username varchar(255) = null,
    @Password varchar(255) = null,
    @Sex int = 0,
    @Email varchar(255) = null,
    @Birthday datetime = null,
    @Header varchar(255) = null,
	@State int = 0,
    @GradeId int = 0,
    @LevelId int = 0,
    @Capacity bigint = 0,
	@HasExtand int = 0,
    @IP varchar(255) = null,
	@Last datetime = null
AS
	IF @Username Is NULL Or rtrim(ltrim(@Username))=''
		Return 3;
	ELSE
		IF (Select Count(*) From DB_USER Where Username=rtrim(ltrim(@Username)) And UserId<>@UserId)>0 
			Return 2;
		ELSE BEGIN
			--	获取用户的分组，分级
			Declare @OldGradeId int
			Declare @OldLevelId int
			Set @OldGradeId=(Select GradeId From DB_USER Where UserId=@UserId);
			Set @OldLevelId=(Select LevelId From DB_USER Where UserId=@UserId);
			--	是否需要删除扩展属性
			IF @HasExtand>0
				Delete From DB_USER_EXTAND Where UserId=@UserId;
			--	重设空间大小
			IF @Capacity<=0 
				Set @Capacity = (Select Capacity From DB_LEVEL Where LevelId=@LevelId);
			If (Select Sum(Capacity) From DB_STORAGE)>=@Capacity
				Set @Capacity = (Select Sum(Capacity) From DB_STORAGE);
			Update DB_USER Set Username=rtrim(ltrim(@Username)), Password=@Password, Sex=@Sex, Email=@Email, Birthday=@Birthday, Header=@Header, State=@State, GradeId=@GradeId, LevelId=@LevelId, Capacity=@Capacity, IP=@IP, Last=@Last Where UserId=@UserId;
			--	更新分组，分级
			IF @GradeId<>@OldGradeId
				Update DB_GRADE Set Users=(Select Count(*) From DB_USER Where GradeId=DB_GRADE.GradeId) Where GradeId=@GradeId Or GradeId=@OldGradeId;
			IF @LevelId<>@OldLevelId
				Update DB_LEVEL Set Users=(Select Count(*) From DB_USER Where LevelId=DB_LEVEL.LevelId) Where LevelId=@LevelId Or LevelId=@OldLevelId;
			Return 0;
		END


















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_POPEM_GETUSER]
	@UserId int = 0,
	@StorageId int = 0,
	@FolderId int = 0
AS
	If @UserId > 0 Begin
		Declare @GradeId int 
		Declare @LevelId int 
		Declare @LevelType int
		Set @GradeId = (Select GradeId From DB_USER Where UserId=@UserId);
		Set @LevelId = (Select LevelId From DB_USER Where UserId=@UserId);
		Set @LevelType = (Select Type From DB_LEVEL Where Type = 2);
		If @LevelType = 2
			Select 0,0,NULL,0,0,NULL,1,1,1,1,1,0;
		Else
			If @FolderId>0
				Select * From DB_POPEM Where ResourceId=@FolderId And ResourceType=2 And OwnerId=@UserId And OwnerType=3;
			Else
				Select * From DB_POPEM Where ResourceId=@StorageId And ResourceType=1 And OwnerId=@UserId And OwnerType=3;
	End
	Else Begin
		declare @GuestId int;
		Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
		If @FolderId>0
			Select * From DB_POPEM Where ResourceId=@FolderId And ResourceType=2 And OwnerId=@GuestId And OwnerType=2;
		Else
			Select * From DB_POPEM Where ResourceId=@StorageId And ResourceType=1 And OwnerId=@GuestId And OwnerType=2;
	End
	Return 0;







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [ET_USER_DETAIL]
    @UserId int = 0
AS
	Select *,
	(Select Count(*) From DB_MESSAGE Where RecvId=DB_USER.UserId And State=0) As Messages
	From DB_USER Where UserId=@UserId;
	Return 0;






GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO











-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_USER_DELETE]
	@UserId int = 0
AS
	IF (Select Count(*) From DB_USER Where UserId=@UserId)>0 BEGIN
		--	获取用户的分组，分级
		Declare @GradeId int
		Declare @LevelId int
		Set @GradeId=(Select GradeId From DB_USER Where UserId=@UserId);
		Set @LevelId=(Select LevelId From DB_USER Where UserId=@UserId);
		--	删除用户扩展信息
		Delete From DB_LOG Where OwnerId=@UserId;
		Delete From DB_MESSAGE_FILE Where Exists(Select * From DB_MESSAGE Where SendId=@UserId Or RecvId=@UserId);
		Delete From DB_MESSAGE Where SendId=@UserId Or RecvId=@UserId;
		Delete From DB_POPEM Where OwnerId=@UserId And OwnerType=3;
		Delete From DB_USER_EXTAND Where UserId=@UserId;
		Delete From DB_USER Where UserId=@UserId;
		--	更新分组，分级信息
		IF @GradeId>0
			Update DB_GRADE Set Users=(Select Count(*) From DB_USER Where GradeId=DB_GRADE.GradeId) Where GradeId=@GradeId;
		IF @LevelId>0
			Update DB_LEVEL Set Users=(Select Count(*) From DB_USER Where LevelId=DB_LEVEL.LevelId) Where LevelId=@LevelId;
		Return 0;
	END
	ELSE
		Return 1;












GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

















CREATE PROCEDURE [ET_RESOURCE_GETPOPEM]
	@UserId int = 0,
	@StorageId int = 0,
	@FolderId int = 0,
	@PopemCreate int = 0 output,
	@PopemWrite int = 0 output,
	@PopemEdit int = 0 output,
	@PopemRead int = 0 output, 
	@PopemDownload int = 0 output
AS
	Declare @GradeId int 
	Declare @LevelId int 
	Declare @IsAdministrator int
	Declare @GuestId int;
	Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
	Set @GradeId = (Select GradeId From DB_USER Where UserId=@UserId);
	Set @LevelId = (Select LevelId From DB_USER Where UserId=@UserId);
	Set @IsAdministrator = (Select Type From DB_LEVEL Where LevelId=@LevelId);
	--	如果为空，则设为零
	If @GradeId is Null Set @GradeId = 0;
	If @LevelId is Null Set @LevelId = 0;
	If @IsAdministrator is Null Set @IsAdministrator = 0;
	--	获取用户分组，分级信息
	If @UserId>0 Begin
		If @IsAdministrator = 2 Begin
			Set @PopemCreate = 1;
			Set @PopemWrite = 1;
			Set @PopemEdit = 1;
			Set @PopemRead = 1;
			Set @PopemDownload = 1;
		End
		Else
			If @FolderId>0 Begin
				Set @PopemCreate = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@FolderId And ResourceType=2 And PopemCreate=1);
				Set @PopemWrite = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@FolderId And ResourceType=2 And PopemWrite=1);
				Set @PopemEdit = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@FolderId And ResourceType=2 And PopemEdit=1);
				Set @PopemRead = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@FolderId And ResourceType=2 And PopemRead=1);
				Set @PopemDownload = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@FolderId And ResourceType=2 And PopemDownload=1);
			End
			Else Begin
				Set @PopemCreate = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@StorageId And ResourceType=1 And PopemCreate=1);
				Set @PopemWrite = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@StorageId And ResourceType=1 And PopemWrite=1);
				Set @PopemEdit = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@StorageId And ResourceType=1 And PopemEdit=1);
				Set @PopemRead = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@StorageId And ResourceType=1 And PopemRead=1);
				Set @PopemDownload = (Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@StorageId And ResourceType=1 And PopemDownload=1);
			End	
	End
	Else Begin
		If @FolderId>0 Begin
			Set @PopemCreate = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@FolderId And ResourceType=2 And PopemCreate=1);
			Set @PopemWrite = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@FolderId And ResourceType=2 And PopemWrite=1);
			Set @PopemEdit = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@FolderId And ResourceType=2 And PopemEdit=1);
			Set @PopemRead = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@FolderId And ResourceType=2 And PopemRead=1);
			Set @PopemDownload = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@FolderId And ResourceType=2 And PopemDownload=1);
		End
		Else Begin
			Set @PopemCreate = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@StorageId And ResourceType=1 And PopemCreate=1);
			Set @PopemWrite = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@StorageId And ResourceType=1 And PopemWrite=1);
			Set @PopemEdit = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@StorageId And ResourceType=1 And PopemEdit=1);
			Set @PopemRead = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@StorageId And ResourceType=1 And PopemRead=1);
			Set @PopemDownload = (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@StorageId And ResourceType=1 And PopemDownload=1);
		End	
	End


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO












CREATE PROCEDURE [ET_USER_ADD]
    @Username varchar(255) = null,
    @Password varchar(255) = null,
    @Sex int = 0,
    @Email varchar(255) = null,
    @Birthday datetime = null,
    @Header varchar(255) = null,
    @State int = 0,
    @GradeId int = 0,
    @LevelId int = 0,
	@IP varchar(255) = null
AS
	IF @Username Is NULL Or rtrim(ltrim(@Username))=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_USER Where Username=rtrim(ltrim(@Username)))>0 
			Return 1;
		ELSE BEGIN
			Declare @Capacity bigint
			--	获得分组信息
			If @GradeId<=0
				Set @GradeId = (Select GradeId From DB_GRADE Where IsDefault=1);
			--	获得分级信息
			If @LevelId>0
				Set @Capacity = (Select Capacity From DB_LEVEL Where LevelId=@LevelId);
			Else Begin
				Set @LevelId = (Select LevelId From DB_LEVEL Where IsDefault=1);
				Set @Capacity = (Select Capacity From DB_LEVEL Where IsDefault=1);
			End
			Insert Into DB_USER (Username, Password, Sex, Email, Birthday, Header, State, GradeId, LevelId, Capacity, IP) Values(rtrim(ltrim(@Username)), @Password, @Sex, @Email, @Birthday, @Header, @State, @GradeId, @LevelId, @Capacity, @IP);
			--	更新分组，分级信息
			IF @GradeId>0
				Update DB_GRADE Set Users=(Select Count(*) From DB_USER Where GradeId=DB_GRADE.GradeId) Where GradeId=@GradeId;
			IF @LevelId>0
				Update DB_LEVEL Set Users=(Select Count(*) From DB_USER Where LevelId=DB_LEVEL.LevelId) Where LevelId=@LevelId;
			Return 0;
		END












GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










































CREATE PROCEDURE [ET_RESOURCE_LIST]
    @Key varchar(255) = null,
	@UserId int = 0,
	@OwnerId int = 0,
	@OwnerIP varchar(50) = null,
	@StorageId int = 0,
	@ParentId int = 0,
	@Source int = 0,
	@State int = 0,
	@Order varchar(255) = null
AS
	--	声明变量
	Declare @SqlFolderWhere varchar(2000)
	Declare @SqlFileWhere varchar(2000)
	Declare @SqlFolderCount varchar(2000)
	Declare @SqlFileCount varchar(2000)
	Declare @SqlOrder varchar(2000)
	Declare @ReturnSql nvarchar(2000)
	Declare @ReturnValue int
	--	初始化变量
	Set @SqlOrder = '';
	Set @ReturnSql = '';
	--	获取匿名访客分级编号
	Declare @GuestId int
	Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
	If @GuestId is Null Set @GuestId = 0;
	--	是否检索路径
	Set @SqlFolderCount = 'Select FolderId As ResourceId ';
	Set @SqlFileCount = 'Select FileId As ResourceId ';
	Set @SqlFolderWhere = 'Select FolderId As ResourceId, 2 As ResourceType, Name, Remark, OwnerId, OwnerName, StorageId, ParentId, DisplayPath, ''文件包'' As Type, ''Folder'' As Extension, NULL As Length, Thumb, State, 0 As DownloadCount, 0 As DisplayCount, 0 As TopicCount, 0 As VouchCount, First, Last ';
	Set @SqlFileWhere = 'Select FileId As ResourceId, 3 As ResourceType, Name, Remark, OwnerId, OwnerName, StorageId, FolderId As ParentId, DisplayPath, Type, Extension, Length, Thumb, State, DownloadCount, DisplayCount, TopicCount, VouchCount, First, Last ';
	--	判断当前用户
	If @UserId>0 Begin
		--	声明分级、分组变量
		Declare @GradeId int
		Declare @LevelId int
		Declare @IsAdministrator int
		--	为分级分组变量赋值
		Set @GradeId = (Select GradeId From DB_USER Where UserId=@UserId);
		Set @LevelId = (Select LevelId From DB_USER Where UserId=@UserId);
		Set @IsAdministrator = (Select Type From DB_LEVEL Where LevelId=@LevelId);
		--	如果为空，则设为零
		If @GradeId is Null Set @GradeId = 0;
		If @LevelId is Null Set @LevelId = 0;
		If @IsAdministrator is Null Set @IsAdministrator = 0;
		--	组合资源权限查询语句
		If @IsAdministrator = 2 Begin
			Set @SqlFolderWhere = @SqlFolderWhere + ',1 As CanEdit,1 As CanRead,1 As CanDownload ';
			Set @SqlFileWhere = @SqlFileWhere + ',1 As CanEdit,1 As CanRead,1 As CanDownload ';
			--	判断状态
			Set @SqlFolderCount = @SqlFolderCount + ' From DB_FOLDER Where State=' + lTrim(@State) + ' ';
			Set @SqlFileCount = @SqlFileCount + ' From DB_FILE Where State=' + lTrim(@State) + ' ';
			Set @SqlFolderWhere = @SqlFolderWhere + ' From DB_FOLDER Where State=' + lTrim(@State) + ' ';
			Set @SqlFileWhere = @SqlFileWhere + ' From DB_FILE Where State=' + lTrim(@State) + ' ';
		End
		Else Begin
			Set @SqlFolderWhere = @SqlFolderWhere + ',(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemEdit=1) As CanEdit ';
			Set @SqlFolderWhere = @SqlFolderWhere + ',(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemRead=1) As CanRead ';
			Set @SqlFolderWhere = @SqlFolderWhere + ',(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemDownload=1) As CanDownload ';
			Set @SqlFileWhere = @SqlFileWhere + ',(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemEdit=1) As CanEdit ';
			Set @SqlFileWhere = @SqlFileWhere + ',(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) As CanRead ';
			Set @SqlFileWhere = @SqlFileWhere + ',(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemDownload=1) As CanDownload ';
			--	判断状态
			Set @SqlFolderCount = @SqlFolderCount + ' From DB_FOLDER Where State=' + lTrim(@State) + ' ';
			Set @SqlFileCount = @SqlFileCount + ' From DB_FILE Where State=' + lTrim(@State) + ' And Exists(Select * From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) ';
			Set @SqlFolderWhere = @SqlFolderWhere + ' From DB_FOLDER Where State=' + lTrim(@State) + ' ';
			Set @SqlFileWhere = @SqlFileWhere + ' From DB_FILE Where State=' + lTrim(@State) + ' And Exists(Select * From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) ';
		End
	End
	Else Begin
		Set @SqlFolderWhere = @SqlFolderWhere + ',(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemEdit=1) As CanEdit ';
		Set @SqlFolderWhere = @SqlFolderWhere + ',(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemRead=1) As CanRead ';
		Set @SqlFolderWhere = @SqlFolderWhere + ',(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemDownload=1) As CanDownload ';
		Set @SqlFileWhere = @SqlFileWhere + ',(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemEdit=1) As CanEdit ';
		Set @SqlFileWhere = @SqlFileWhere + ',(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) As CanRead ';
		Set @SqlFileWhere = @SqlFileWhere + ',(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemDownload=1) As CanDownload ';
		--	判断状态
		Set @SqlFolderCount = @SqlFolderCount + ' From DB_FOLDER Where State=' + lTrim(@State) + ' ';
		Set @SqlFileCount = @SqlFileCount + ' From DB_FILE Where State=' + lTrim(@State) + ' And Exists(Select * From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) ';
		Set @SqlFolderWhere = @SqlFolderWhere + ' From DB_FOLDER Where State=' + lTrim(@State) + ' ';
		Set @SqlFileWhere = @SqlFileWhere + ' From DB_FILE Where State=' + lTrim(@State) + ' And Exists(Select * From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) ';
	End
	--	是否检索来源
	If @Source>0 Begin
		Set @SqlFileCount = @SqlFileCount + ' And Source=' + lTrim(@Source) + ' ';
		Set @SqlFileWhere = @SqlFileWhere + ' And Source=' + lTrim(@Source) + ' ';
	End
	--	是否检索存储设备
	If @StorageId>0 Begin
		Set @SqlFolderCount = @SqlFolderCount + ' And StorageId=' + lTrim(@StorageId) + ' ';
		Set @SqlFileCount = @SqlFileCount + ' And StorageId=' + lTrim(@StorageId) + ' ';
		Set @SqlFolderWhere = @SqlFolderWhere + ' And StorageId=' + lTrim(@StorageId) + ' ';
		Set @SqlFileWhere = @SqlFileWhere + ' And StorageId=' + lTrim(@StorageId) + ' ';
	End
	--	是否检索上级路径
	If @ParentId>=0 Begin
		Set @SqlFolderCount = @SqlFolderCount + ' And ParentId=' + lTrim(@ParentId) + ' ';
		Set @SqlFileCount = @SqlFileCount + ' And FolderId=' + lTrim(@ParentId) + ' ';
		Set @SqlFolderWhere = @SqlFolderWhere + ' And ParentId=' + lTrim(@ParentId) + ' ';
		Set @SqlFileWhere = @SqlFileWhere + ' And FolderId=' + lTrim(@ParentId) + ' ';
	End
	--	关键字查询
	If rTrim(lTrim(@Key))<>'' Begin
		Set @Key = rTrim(lTrim(@Key));
		Declare @Key_I Int 
		Set @Key_I = PATINDEX('%.%', @Key);
		If @Key_I>0 And @Key_I<LEN(@Key) Begin
			Declare @Key_T varchar(255)
			Declare @Key_E varchar(10)
			Set @Key_T = SUBSTRING(@Key, 0, @Key_I);
			Set @Key_E = SUBSTRING(@Key, @Key_I + 1, LEN(@Key));
			If @Key_T = '*' Begin
				Set @SqlFolderCount = @SqlFolderCount + ' And FolderId<=0 ';
				Set @SqlFileCount = @SqlFileCount + ' And Extension=''' + @Key_E + ''' ';
				Set @SqlFolderWhere = @SqlFolderWhere + ' And FolderId<=0 ';
				Set @SqlFileWhere = @SqlFileWhere + ' And Extension=''' + @Key_E + ''' ';
			End
			Else Begin
				Set @SqlFolderCount = @SqlFolderCount + ' And FolderId<=0 ';
				Set @SqlFileCount = @SqlFileCount + ' And Summary Like ''%' + Replace(@Key_T, ' ', '%') + '%'' And Extension=''' + @Key_E + ''' ';
				Set @SqlFolderWhere = @SqlFolderWhere + ' And FolderId<=0 ';
				Set @SqlFileWhere = @SqlFileWhere + ' And Summary Like ''%' + Replace(@Key_T, ' ', '%') + '%'' And Extension=''' + @Key_E + ''' ';
			End
		End
		Else Begin
			Set @SqlFolderCount = @SqlFolderCount + ' And Summary Like ''%' + Replace(@Key, ' ', '%') + '%'' ';
			Set @SqlFileCount = @SqlFileCount + ' And Summary Like ''%' + Replace(@Key, ' ', '%') + '%'' ';
			Set @SqlFolderWhere = @SqlFolderWhere + ' And Summary Like ''%' + Replace(@Key, ' ', '%') + '%'' ';
			Set @SqlFileWhere = @SqlFileWhere + ' And Summary Like ''%' + Replace(@Key, ' ', '%') + '%'' ';
		End
	End
	--	是否检索创建人
	If @OwnerId>0 Begin
		Set @SqlFolderCount = @SqlFolderCount + ' And OwnerId=' + lTrim(@OwnerId) + ' ';
		Set @SqlFileCount = @SqlFileCount + ' And OwnerId=' + lTrim(@OwnerId) + ' ';
		Set @SqlFolderWhere = @SqlFolderWhere + ' And OwnerId=' + lTrim(@OwnerId) + ' ';
		Set @SqlFileWhere = @SqlFileWhere + ' And OwnerId=' + lTrim(@OwnerId) + ' ';
	End
	--	是否匹配用户 IP
	If @OwnerIP<>'' Begin
		Set @SqlFolderCount = @SqlFolderCount + ' And OwnerIP=' + @OwnerIP + ' ';
		Set @SqlFileCount = @SqlFileCount + ' And OwnerIP=' + @OwnerIP + ' ';
		Set @SqlFolderWhere = @SqlFolderWhere + ' And OwnerIP=' + @OwnerIP + ' ';
		Set @SqlFileWhere = @SqlFileWhere + ' And OwnerIP=' + @OwnerIP + ' ';
	End
	--	组合排序规则
	If @Order<>''
		Set @SqlOrder = ' Order By ' + @Order;
	Else
		Set @SqlOrder = ' Order By Last Desc';
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From ((' + @SqlFolderCount + ') UNION (' + @SqlFileCount + ')) As A )'
	exec ('Select * From ((' + @SqlFolderWhere + ') UNION (' + @SqlFileWhere + ')) As A ' + @SqlOrder);
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;





GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


















CREATE PROCEDURE [ET_LOG_ADD]
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
	@OwnerId int = 0,
	@OwnerIP varchar(255) = null,
	@RelationId int = 0,
	@RelationType int = 0,
	@OperateType int = 0,
	@Minute int = 0
AS
	--	获取用户名
	Declare @OwnerName varchar(255);
	If @OwnerId>0
		Set @OwnerName = (Select Username From DB_USER Where UserId=@OwnerId);
	Else
		Set @OwnerName = '匿名用户';
	--	 检查指定的关联资源是否存在
	Declare @RelationExists int;
	Set @RelationExists = Case @RelationType
		When 1 Then (Select Count(*) From DB_CLASS Where ClassId=@RelationId)
		When 2 Then (Select Count(*) From DB_ATTRIBUTE Where AttributeId=@RelationId)
		When 3 Then (Select Count(*) From DB_EXTAND Where ExtandId=@RelationId)
		When 4 Then (Select Count(*) From DB_GRADE Where GradeId=@RelationId)
		When 5 Then (Select Count(*) From DB_LEVEL Where LevelId=@RelationId)
		When 6 Then (Select Count(*) From DB_USER Where UserId=@RelationId)
		When 7 Then (Select Count(*) From DB_STORAGE Where StorageId=@RelationId)
		When 8 Then (Select Count(*) From DB_FOLDER Where FolderId=@RelationId)
		When 9 Then (Select Count(*) From DB_FILE Where FileId=@RelationId)
		Else 0
	End
	If @RelationType=9 Begin
		If @Minute>0 Begin	--	如果有时间值,则判断时间
			If (Select Count(*) From DB_LOG Where (OwnerId=@OwnerId Or OwnerIP=@OwnerIP) And RelationId=@RelationId And RelationType=@RelationType And OperateType=@OperateType And GetDate()<DateAdd(Minute, @Minute, First))<=0 Begin
				Insert Into DB_LOG (Name, Remark, OwnerId, OwnerName, OwnerIP, RelationId, RelationType, OperateType) Values(@Name, @Remark, @OwnerId, @OwnerName, @OwnerIP, @RelationId, @RelationType, @OperateType);
				If @OperateType=6
					Update DB_FILE Set DisplayCount=(Select Count(*) From DB_LOG Where RelationId=@RelationId And RelationType=@RelationType And OperateType=6 And RelationId=DB_FILE.FileId) Where FileId=@RelationId;
				If @OperateType=8
					Update DB_FILE Set TopicCount=(Select Count(*) From DB_LOG Where RelationId=@RelationId And RelationType=@RelationType And OperateType=8 And RelationId=DB_FILE.FileId) Where FileId=@RelationId;
				If @OperateType=9
					Update DB_FILE Set VouchCount=(Select Count(*) From DB_LOG Where RelationId=@RelationId And RelationType=@RelationType And OperateType=9 And RelationId=DB_FILE.FileId) Where FileId=@RelationId;
				Return 0;
			End
			Else Begin
				Return 1;
			End
		End 
		Else Begin
			Insert Into DB_LOG (Name, Remark, OwnerId, OwnerName, OwnerIP, RelationId, RelationType, OperateType) Values(@Name, @Remark, @OwnerId, @OwnerName, @OwnerIP, @RelationId, @RelationType, @OperateType);		
			Return 0;
		End
	End
	Else Begin
		Insert Into DB_LOG (Name, Remark, OwnerId, OwnerName, OwnerIP, RelationId, RelationType, OperateType) Values(@Name, @Remark, @OwnerId, @OwnerName, @OwnerIP, @RelationId, @RelationType, @OperateType);
		Return 0;
	End















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




















CREATE PROCEDURE [ET_MESSAGE_ADD]
    @Name varchar(255) = null,
    @Remark varchar(5000) = null,
	@RecvId int = 0,
	@SendId int = 0,
	@Files int = 0
AS
	--	获取收件人
	Declare @RecvName varchar(255);
	If @RecvId>0
		Set @RecvName = (Select Username From DB_USER Where UserId=@RecvId);
	Else
		Set @RecvName = '匿名用户';
	--	获取发件人
	Declare @SendName varchar(255);
	If @SendId>0
		Set @SendName = (Select Username From DB_USER Where UserId=@SendId);
	Else
		Set @SendName = '匿名用户';
	--	插入新记录
	Insert Into DB_MESSAGE (Name, Remark, RecvId, RecvUsername, SendId, SendUsername, State, Files) Values(@Name, @Remark, @RecvId, @RecvName, @SendId, @SendName, 0, @Files);
	Return 0;

















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO















CREATE PROCEDURE [ET_FILE_SETTOPIC]
    @FileId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(5000) = null,
	@OwnerId int = 0,
    @OwnerIP varchar(50) = null,
	@Minute int = 0
AS
	--	获得用户和扩展属性
	Declare @FileName varchar(255)
	Set @FileName = (Select Name From DB_FILE Where FileId=@FileId);
	--	获取用户名
	Declare @OwnerName varchar(255)
	If @OwnerId>0
		Set @OwnerName = (Select Username From DB_USER Where UserId=@OwnerId);
	Else
		Set @OwnerName = '匿名访客';
	--	如果标签创建成功则插入
	If (Select Count(*) From DB_FILE Where FileId=@FileId)>0
		If (@Minute<=0 Or (Select Count(*) From DB_FILE_TOPIC Where FileId=@FileId And (OwnerId=@OwnerId Or OwnerIP=@OwnerIP) And GetDate()<DateAdd(Minute, @Minute, First))<=0)
			Insert Into DB_FILE_TOPIC (Name, Remark, OwnerId, OwnerIP, OwnerName, FileId, FileName) Values(@Name, @Remark, @OwnerId, @OwnerIP, @OwnerName, @FileId, @FileName);
	Return 0;





GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






























CREATE PROCEDURE [ET_FOLDER_GETFOLDERS]
	@UserId int = 0,
	@FolderId int = 0
AS
	--	获取匿名访客分级编号
	Declare @GuestId int
	Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
	If @GuestId is Null Set @GuestId = 0;
	--	判断当前用户
	If @UserId>0 Begin
		--	声明分级、分组变量
		Declare @GradeId int
		Declare @LevelId int
		Declare @IsAdministrator int
		--	为分级分组变量赋值
		Set @GradeId = (Select GradeId From DB_USER Where UserId=@UserId);
		Set @LevelId = (Select LevelId From DB_USER Where UserId=@UserId);
		Set @IsAdministrator = (Select Type From DB_LEVEL Where LevelId=@LevelId);
		--	如果为空，则设为零
		If @GradeId is Null Set @GradeId = 0;
		If @LevelId is Null Set @LevelId = 0;
		If @IsAdministrator is Null Set @IsAdministrator = 0;
		--	组合资源权限查询语句
		If @IsAdministrator = 2 
			Select *,
			1 As CanEdit
			From DB_FOLDER Where ParentId=@FolderId
			Order By FolderId Asc;
		Else
			Select *,
			(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemEdit=1)  As CanEdit
			From DB_FOLDER Where ParentId=@FolderId
			Order By FolderId Asc;
	End
	Else Begin
		Select *,
		(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemEdit=1) As CanEdit
		From DB_FOLDER Where ParentId=@FolderId
		Order By FolderId Asc;
	End
	Return 0;






























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


















CREATE PROCEDURE [ET_POPEM_SETSUB]
    @OwnerId int = 0,
    @OwnerType int = 0,
	@ResourceId int = 0,
	@ResourceType int = 0,
	@PopemCreate int = 0,
	@PopemWrite int = 0,
	@PopemEdit int = 0,
	@PopemRead int = 0,
	@PopemDownload int = 0
AS
	--	初始化用户名
	Declare @OwnerName varchar(255)
	Set @OwnerName = Case 
		When @OwnerType=1 Then (Select Name From DB_GRADE Where GradeId=@OwnerId)
		When @OwnerType=2 Then (Select Name From DB_LEVEL Where LevelId=@OwnerId)
		Else (Select Username From DB_USER Where UserId=@OwnerId)
	End
	--	初始化锁定状态
	Declare @Lock int
	Set @Lock = 0
	If @OwnerType=2 And (Select Count(*) From DB_LEVEL Where LevelId=@OwnerId And Type=2)>0
		Set @Lock = 1;
	If @OwnerType=3 And (Select Count(*) From DB_USER Where UserId=@OwnerId And Exists(Select * From DB_LEVEL Where LevelId=DB_USER.LevelId And Type=2))>0
		Set @Lock = 1;
	--	初始化上级路径
	Declare @DisplayPath varchar(255);
	Set @DisplayPath = Case 
		When @ResourceType=1 Then (Select Name + '>' From DB_Storage Where StorageId=@ResourceId)
		Else (Select DisplayPath + Name + '/' From DB_FOLDER Where FolderId=@ResourceId)
	End
	--	为无此权限的文件插入新权限项目
	Insert Into DB_POPEM (OwnerId, OwnerType, OwnerName, ResourceId, ResourceType, ResourceName, PopemCreate, PopemWrite, PopemEdit, PopemRead, PopemDownload, Lock) Select @OwnerId, @OwnerType, @OwnerName, FileId, 3, Name, @PopemCreate, @PopemWrite, @PopemEdit, @PopemRead, @PopemDownload, @Lock From DB_FILE Where DisplayPath Like @DisplayPath + '%' And Not Exists(Select * From DB_POPEM Where ResourceId=DB_FILE.FileId And ResourceType=3 And OwnerId=@OwnerId And OwnerType=@OwnerType)
	--	为有此权限的文件更新权限项目
	Update DB_POPEM Set PopemCreate=@PopemCreate,PopemWrite=@PopemWrite,PopemEdit=@PopemEdit,PopemRead=@PopemRead,PopemDownload=@PopemDownload,Lock=@Lock Where OwnerId=@OwnerId And OwnerType=@OwnerType And ResourceType=3 And Exists(Select * From DB_FILE Where DisplayPath Like @DisplayPath + '%' And FileId=DB_POPEM.ResourceId)
	--	为无此权限的文件夹插入新权限项目
	Insert Into DB_POPEM (OwnerId, OwnerType, OwnerName, ResourceId, ResourceType, ResourceName, PopemCreate, PopemWrite, PopemEdit, PopemRead, PopemDownload, Lock) Select @OwnerId, @OwnerType, @OwnerName, FolderId, 2, Name, @PopemCreate, @PopemWrite, @PopemEdit, @PopemRead, @PopemDownload, @Lock From DB_FOLDER Where DisplayPath Like @DisplayPath + '%' And Not Exists(Select * From DB_POPEM Where ResourceId=DB_FOLDER.FolderId And ResourceType=2 And OwnerId=@OwnerId And OwnerType=@OwnerType)
	--	为有此权限的文件夹更新权限项目
	Update DB_POPEM Set PopemCreate=@PopemCreate,PopemWrite=@PopemWrite,PopemEdit=@PopemEdit,PopemRead=@PopemRead,PopemDownload=@PopemDownload,Lock=@Lock Where OwnerId=@OwnerId And OwnerType=@OwnerType And ResourceType=2 And Exists(Select * From DB_FOLDER Where DisplayPath Like @DisplayPath + '%' And FolderId=DB_POPEM.ResourceId)
	Return 0;











GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO































CREATE PROCEDURE [ET_FOLDER_LIST]
    @Key varchar(255) = null,
	@UserId int = 0,
	@OwnerId int = 0,
	@OwnerIP varchar(50) = null, 
	@StorageId int = 0,
	@ParentId int = 0,
	@State int = 0,
	@Order varchar(255) = null
AS
	--	声明变量
	Declare @SqlSelect varchar(2000)
	Declare @SqlWhere varchar(500)
	Declare @SqlOrder varchar(500)
	Declare @ReturnSql nvarchar(500)
	Declare @ReturnValue int
	--	初始化变量
	Set @SqlSelect = ', (Select Count(*) From DB_Folder F Where F.ParentId=DB_FOLDER.FolderId And State=' + lTrim(@State) + ') As HasChild ';
	Set @SqlWhere = '';
	Set @SqlOrder = '';
	Set @ReturnSql = '';
	--	获取匿名访客分级编号
	Declare @GuestId int
	Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
	If @GuestId is Null Set @GuestId = 0;
	--	读取指定状态
	Set @SqlWhere = ' Where State=' + lTrim(@State) + ' ';
	--	判断当前用户
	If @UserId>0 Begin
		--	声明分级、分组变量
		Declare @GradeId int
		Declare @LevelId int
		Declare @IsAdministrator int
		--	为分级分组变量赋值
		Set @GradeId = (Select GradeId From DB_USER Where UserId=@UserId);
		Set @LevelId = (Select LevelId From DB_USER Where UserId=@UserId);
		Set @IsAdministrator = (Select Type From DB_LEVEL Where LevelId=@LevelId);
		--	如果为空，则设为零
		If @GradeId is Null Set @GradeId = 0;
		If @LevelId is Null Set @LevelId = 0;
		If @IsAdministrator is Null Set @IsAdministrator = 0;
		--	组合资源权限查询语句
		If @IsAdministrator = 2 Begin
			Set @SqlSelect = @SqlSelect + ', 1 As CanCreate, 1 As CanWrite, 1 As CanRead ';
		End
		Else Begin
			Set @SqlSelect = @SqlSelect + ',(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemCreate=1) As CanCreate ';
			Set @SqlSelect = @SqlSelect + ',(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemWrite=1) As CanWrite ';
			Set @SqlSelect = @SqlSelect + ',(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ') ) And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemRead=1) As CanRead ';
		End
	End
	Else Begin
		Set @SqlSelect = @SqlSelect + ', (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemCreate=1) As CanCreate ';
		Set @SqlSelect = @SqlSelect + ', (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemWrite=1) As CanWrite ';
		Set @SqlSelect = @SqlSelect + ', (Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FOLDER.FolderId And ResourceType=2 And PopemRead=1) As CanRead ';
	End
	--	是否检索存储设备
	If @StorageId>0
		Set @SqlWhere = @SqlWhere + ' And StorageId=' + lTrim(@StorageId) + ' ';
	--	是否检索上级路径
	If @ParentId>=0
		Set @SqlWhere = @SqlWhere + ' And ParentId=' + lTrim(@ParentId) + ' ';
	--	关键字查询
	If @Key<>''
		Set @SqlWhere = @SqlWhere + ' And Summary Like ''%' + Replace(@Key, ' ', '%') + '%'' ';
	--	是否检索创建人
	If @OwnerId>0
		Set @SqlWhere = @SqlWhere + ' And OwnerId=' + lTrim(@OwnerId) + ' ';
	--	是否检索用户 IP
	If @OwnerIP<>''
		Set @SqlWhere = @SqlWhere + ' And OwnerIP=' + @OwnerIP + ' ';
	--	组合排序规则
	If @Order<>''
		Set @SqlOrder = ' Order By ' + @Order;
	Else
		Set @SqlOrder = ' Order By Last Desc';
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_FOLDER ' + @SqlWhere + ' )'
	exec ('Select * ' + @SqlSelect + ' From DB_FOLDER ' + @SqlWhere + ' ' + @SqlOrder);
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;































GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_POPEM_EXISTS]
	@UserId int = 0,
	@ResourceId int = 0,
	@ResourceType int = 0
AS	
	Declare @GuestId int
	Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
	If @GuestId is Null Set @GuestId = 0;
	If @UserId>0 Begin
		--	声明分级、分组变量
		Declare @GradeId int
		Declare @LevelId int
		Declare @IsAdministrator int
		--	为分级分组变量赋值
		Set @GradeId = (Select GradeId From DB_USER Where UserId=@UserId);
		Set @LevelId = (Select LevelId From DB_USER Where UserId=@UserId);
		Set @IsAdministrator = (Select Type From DB_LEVEL Where LevelId=@LevelId);
		--	如果为空，则设为零
		If @GradeId is Null Set @GradeId = 0;
		If @LevelId is Null Set @LevelId = 0;
		If @IsAdministrator is Null Set @IsAdministrator = 0;
		If @IsAdministrator = 2 Begin
			Select 1 As CanCreate, 1 As CanUpload, 1 As CanEdit, 1 As CanRead, 1 As CanDownload;
		End
		Else Begin
			Select
				(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemCreate=1) As CanCreate,
				(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemWrite=1) As CanWrite,
				(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemEdit=1) As CanEdit,
				(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemRead=1) As CanRead,
				(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemDownload=1) As CanDownload;
		End
	End
	Else Begin
		Select
			(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemCreate=1) As CanCreate,
			(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemWrite=1) As CanWrite,
			(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemEdit=1) As CanEdit,
			(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemRead=1) As CanRead,
			(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=@ResourceId And ResourceType=@ResourceType And PopemDownload=1) As CanDownload;
	End
	Return 0;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO































CREATE PROCEDURE [ET_FOLDER_GETFILES]
	@UserId int = 0,
	@FolderId int = 0
AS
	--	获取匿名访客分级编号
	Declare @GuestId int
	Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
	If @GuestId is Null Set @GuestId = 0;
	--	判断当前用户
	If @UserId>0 Begin
		--	声明分级、分组变量
		Declare @GradeId int
		Declare @LevelId int
		Declare @IsAdministrator int
		--	为分级分组变量赋值
		Set @GradeId = (Select GradeId From DB_USER Where UserId=@UserId);
		Set @LevelId = (Select LevelId From DB_USER Where UserId=@UserId);
		Set @IsAdministrator = (Select Type From DB_LEVEL Where LevelId=@LevelId);
		--	如果为空，则设为零
		If @GradeId is Null Set @GradeId = 0;
		If @LevelId is Null Set @LevelId = 0;
		If @IsAdministrator is Null Set @IsAdministrator = 0;
		--	组合资源权限查询语句
		If @IsAdministrator = 2 
			Select *,
			1 As CanEdit
			From DB_FILE Where FolderId=@FolderId
			Order By FileId Asc;
		Else
			Select *,
			(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemEdit=1)  As CanEdit
			From DB_FILE Where FolderId=@FolderId
			Order By FileId Asc;

	End
	Else Begin
		Select *,
		(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemEdit=1) As CanEdit
		From DB_FILE Where FolderId=@FolderId
		Order By FileId Asc;
	End
	Return 0;































GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_LEVEL_MOVE]
	@LevelId int = 0,
    @TargetId int = 0
AS
	If (Select Type From DB_LEVEL Where LevelId=@TargetId) = 1
		Return 2;
	Else
		If (Select Count(*) From DB_LEVEL Where LevelId=@TargetId)>0 Begin
			Update DB_USER Set LevelId=@TargetId Where LevelId=@LevelId;
			Update DB_LEVEL Set Users=(Select Count(*) From DB_USER Where LevelId=DB_LEVEL.LevelId) Where LevelId=@LevelId Or LevelId=@TargetId;
			Return 0;
		End
		Else
			Return 1;

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_GRADE_MOVE]
	@GradeId int = 0,
    @TargetId int = 0
AS
	If (Select Count(*) From DB_GRADE Where GradeId=@TargetId)>0 Begin
		Update DB_USER Set GradeId=@TargetId Where GradeId=@GradeId;
		Update DB_GRADE Set Users=(Select Count(*) From DB_USER Where GradeId=DB_GRADE.GradeId) Where GradeId=@GradeId Or GradeId=@TargetId;
		Return 0;
	End
	Else
		Return 1;

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
















CREATE PROCEDURE [ET_POPEM_SET]
    @OwnerId int = 0,
    @OwnerType int = 0,
	@ResourceId int = 0,
	@ResourceType int = 0,
	@PopemCreate int = 0,
	@PopemWrite int = 0,
	@PopemEdit int = 0,
	@PopemRead int = 0,
	@PopemDownload int = 0,
	@Lock int = 0
AS
	--	检索关系资源是否存在
	Declare @HasResource int
	Declare @ResourceName varchar(255)
	Set @HasResource = Case 
		When @ResourceType=1 Then (Select Count(*) From DB_STORAGE Where StorageId=@ResourceId)
		When @ResourceType=2 Then (Select Count(*) From DB_FOLDER Where FolderId=@ResourceId)
		Else (Select Count(*) From DB_FILE Where FileId=@ResourceId)
	End
	Set @ResourceName = Case 
		When @ResourceType=1 Then (Select Name From DB_STORAGE Where StorageId=@ResourceId)
		When @ResourceType=2 Then (Select Name From DB_FOLDER Where FolderId=@ResourceId)
		Else (Select Name From DB_FILE Where FileId=@ResourceId)
	End 
	If @HasResource>0 Begin
		Declare @HasOwner int
		Declare @OwnerName varchar(255)
		Set @HasOwner = Case 
			When @OwnerType=1 Then (Select Count(*) From DB_GRADE Where GradeId=@OwnerId)
			When @OwnerType=2 Then (Select Count(*) From DB_LEVEL Where LevelId=@OwnerId)
			Else (Select Count(*) From DB_USER Where UserId=@OwnerId)
		End
		Set @OwnerName = Case 
			When @OwnerType=1 Then (Select Name From DB_GRADE Where GradeId=@OwnerId)
			When @OwnerType=2 Then (Select Name From DB_LEVEL Where LevelId=@OwnerId)
			Else (Select Username From DB_USER Where UserId=@OwnerId)
		End
		If @OwnerType=2 And (Select Count(*) From DB_LEVEL Where LevelId=@OwnerId And Type=2)>0
			Set @Lock = 1;
		If @OwnerType=3 And (Select Count(*) From DB_USER Where UserId=@OwnerId And Exists(Select * From DB_LEVEL Where LevelId=DB_USER.LevelId And Type=2))>0
			Set @Lock = 1;
		If @HasOwner>0 Begin
			If (Select Count(*) From DB_POPEM Where OwnerId=@OwnerId And OwnerType=@OwnerType And ResourceId=@ResourceId And ResourceType=@ResourceType)>0
				Update DB_POPEM Set PopemCreate=@PopemCreate, PopemWrite=@PopemWrite, PopemEdit=@PopemEdit, PopemRead=@PopemRead, PopemDownload=@PopemDownload Where OwnerId=@OwnerId And OwnerType=@OwnerType And ResourceId=@ResourceId And ResourceType=@ResourceType
			Else
				Insert Into DB_POPEM (OwnerId, OwnerType, OwnerName, ResourceId, ResourceType, ResourceName, PopemCreate, PopemWrite, PopemEdit, PopemRead, PopemDownload, Lock) Values(@OwnerId, @OwnerType, @OwnerName, @ResourceId, @ResourceType, @ResourceName, @PopemCreate, @PopemWrite, @PopemEdit, @PopemRead, @PopemDownload, @Lock);
			Return 0;
		End
		Else
			Return 1;
	End
	Else
		Return 2;










GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO























-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_FILE_EDIT]
	@FileId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(2000) = null,
	@StorageId int = 0,
	@FolderId int = 0,
	@Length bigint = 0,
	@Thumb varchar(255) = null,
	@Preview varchar(255) = null,
	@Filename varchar(255) = null,
	@Lock int = 0,
	@Topic int = 0,
	@Vote int = 0,
	@State int = 0
AS
	IF @Name Is NULL Or @Name=''
		Return 3;
	ELSE
		IF (Select Count(*) From DB_FILE Where Name=@Name And FileId<>@FileId And FolderId=@FolderId And StorageId=@StorageId)>0 
			Return 2;
		ELSE
			If (Select Count(*) From DB_FILE Where FileId=@FileId And Lock=1) >0
				Return 1;
			Else BEGIN
				Declare @OwnerId int 
				Declare @OldLength int 
				Declare @Summary varchar(5000);
				--	避免 Summary 空值
				If @Name is null Or rtrim(ltrim(@Name)) = ''
					Set @Name = '';
				If @Remark is null Or @Remark = ''
					Set @Remark = '';
				Set @Summary = @Name + '_' + @Remark;
				Set @OwnerId = (Select OwnerId From DB_FILE Where FileId=@FileId);
				Set @OldLength = (Select Length From DB_FILE Where FileId=@FileId);
				Update DB_FILE Set Name=rtrim(ltrim(@Name)), Remark=@Remark, Length=@Length, Thumb=@Thumb, Preview=@Preview, Filename=@Filename, Summary=@Summary, Lock=@Lock, Topic=@Topic, Vote=@Vote, State=@State Where FileId=@FileId;
				--	更新存储设备
				Update DB_STORAGE Set Used=Used-@OldLength+@Length, Last=GetDate() Where StorageId=@StorageId;
				--	更新用户信息
				Update DB_USER Set Used=Used-@OldLength+@Length Where UserId=@OwnerId;
				--	更新上级路径
				If @FolderId>0 Begin
					Declare @iParentId int
					Set @iParentId = @FolderId;
					while @iParentId>0 Begin
						Update DB_FOLDER Set Last=GetDate() Where FolderId=@iParentId;
						Set @iParentId = (Select ParentId From DB_FOLDER Where FolderId=@iParentId);
					End
				End
				Return 0;
			End
























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





































CREATE PROCEDURE [ET_FILE_LIST]
    @Key varchar(255) = null,
    @Extension varchar(50) = null,
	@ClassId int = 0,
	@Tags varchar(255) = null,
	@UserId int = 0,
	@OwnerId int = 0,
	@OwnerIP varchar(50) = null,
	@StorageId int = 0,
	@FolderId int = 0,
	@Source int = 0,
	@State int = 0,
	@StartDate varchar(255) = null,
	@EndDate varchar(255) = null,
	@DateField int = 0,
	@Order varchar(255) = null
AS
	--	声明变量
	Declare @SqlSelect varchar(2000)
	Declare @SqlWhere varchar(500)
	Declare @SqlOrder varchar(500)
	Declare @ReturnSql nvarchar(500)
	Declare @ReturnValue int
	--	初始化变量
	Set @SqlSelect = '';
	Set @SqlWhere = '';
	Set @SqlOrder = '';
	Set @ReturnSql = '';
	--	获取匿名访客分级编号
	Declare @GuestId int
	Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
	If @GuestId is Null Set @GuestId = 0;
	--	读取指定状态
	Set @SqlWhere = ' Where State=' + lTrim(@State) + ' ';
	--	判断当前用户
	If @UserId>0 Begin
		--	声明分级、分组变量
		Declare @GradeId int
		Declare @LevelId int
		Declare @IsAdministrator int
		--	为分级分组变量赋值
		Set @GradeId = (Select GradeId From DB_USER Where UserId=@UserId);
		Set @LevelId = (Select LevelId From DB_USER Where UserId=@UserId);
		Set @IsAdministrator = (Select Type From DB_LEVEL Where LevelId=@LevelId);
		--	如果为空，则设为零
		If @GradeId is Null Set @GradeId = 0;
		If @LevelId is Null Set @LevelId = 0;
		If @IsAdministrator is Null Set @IsAdministrator = 0;
		--	组合资源权限查询语句
		If @IsAdministrator = 2
			Set @SqlSelect = @SqlSelect + ', 1 As CanRead, 1 As CanEdit, 1 As CanDownload ';
		Else Begin
			Set @SqlSelect = @SqlSelect + ',(Select Count(*) From DB_POPEM Where ((OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ')) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) As CanRead ';
			Set @SqlSelect = @SqlSelect + ',(Select Count(*) From DB_POPEM Where ((OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ')) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemEdit=1) As CanEdit ';
			Set @SqlSelect = @SqlSelect + ',(Select Count(*) From DB_POPEM Where ((OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ')) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemDownload=1) As CanDownload ';
			Set @SqlWhere = @SqlWhere + ' And Exists(Select * From DB_POPEM Where ((OwnerType=1 And OwnerId=' + lTrim(@GradeId) + ') Or (OwnerType=2 And (OwnerId=' + lTrim(@LevelId) + ' Or OwnerId=' + lTrim(@GuestId) + ')) Or (OwnerType=3 And OwnerId=' + lTrim(@UserId) + ')) And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) ';
		End
	End
	Else Begin
		Set @SqlSelect = @SqlSelect + ',(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) As CanRead ';
		Set @SqlSelect = @SqlSelect + ',(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemEdit=1) As CanEdit ';
		Set @SqlSelect = @SqlSelect + ',(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemDownload=1) As CanDownload ';
		Set @SqlWhere = @SqlWhere + ' And Exists(Select * From DB_POPEM Where OwnerType=2 And OwnerId=' + lTrim(@GuestId) + ' And ResourceId=DB_FILE.FileId And ResourceType=3 And PopemRead=1) ';
	End
	--	是否检索来源
	If @Source>0
		Set @SqlWhere = @SqlWhere + ' And Source=' + lTrim(@Source) + ' ';
	--	是否检索分类
	If @ClassId>0
		Set @SqlWhere = @SqlWhere + ' And ClassId=' + lTrim(@ClassId) + ' ';
	--	是否检索后缀经
	If @Extension is not null and @Extension<>''
		Set @SqlWhere = @SqlWhere + ' And Extension=@Extension ';
	--	是否检索属性
	If @Tags is not null and @Tags<>''
		Set @SqlWhere = @SqlWhere + ' And Exists(Select * From DB_FILE_TAG Where TagId In (' + @Tags + ') And FileId=DB_FILE.FileId) ';
	--	是否检索存储设备
	If @StorageId>0
		Set @SqlWhere = @SqlWhere + ' And StorageId=' + lTrim(@StorageId) + ' ';
	--	是否检索上级路径
	If @FolderId>=0
		Set @SqlWhere = @SqlWhere + ' And FolderId=' + lTrim(@FolderId) + ' ';
	--	关键字查询
	If @Key is not null And rTrim(lTrim(@Key))<>'' Begin
		Set @Key = rTrim(lTrim(@Key));
		Declare @Key_I Int 
		Set @Key_I = PATINDEX('%.%', @Key);
		If @Key_I>0 And @Key_I<LEN(@Key) Begin
			Declare @Key_T varchar(255)
			Declare @Key_E varchar(10)
			Set @Key_T = SUBSTRING(@Key, 0, @Key_I);
			Set @Key_E = SUBSTRING(@Key, @Key_I + 1, LEN(@Key));
			If @Key_T = '*' Begin
				Set @SqlWhere = @SqlWhere + ' And Extension=''' + @Key_E + ''' ';
			End
			Else Begin
				Set @SqlWhere = @SqlWhere + ' And Summary Like ''%' + Replace(@Key_T, ' ', '%') + '%'' And Extension=''' + @Key_E + ''' ';
			End
		End
		Else Begin
			Set @SqlWhere = @SqlWhere + ' And Summary Like ''%' + Replace(@Key, ' ', '%') + '%'' ';
		End
	End
	--	是否检索创建人
	If @OwnerId>0
		Set @SqlWhere = @SqlWhere + ' And OwnerId=' + lTrim(@OwnerId) + ' ';
	--	是否检索用户 IP
	If @OwnerIP<>''
		Set @SqlWhere = @SqlWhere + ' And OwnerIP=' + @OwnerIP + ' ';
	If @StartDate is not Null And @StartDate<>'' And @EndDate is not Null And @EndDate<>''
		If @DateField=0
			Set @SqlWhere = @SqlWhere + ' And (First>''' + @StartDate + ''' And First <DateAdd(dd, 1, ''' + @EndDate + ''')) ';
		Else
			Set @SqlWhere = @SqlWhere + ' And (Last>''' + @StartDate + ''' And Last <DateAdd(dd, 1, ''' + @EndDate + ''')) ';
	--	组合排序规则
	If @Order is not null and @Order<>''
		Set @SqlOrder = ' Order By ' + @Order;
	Else
		Set @SqlOrder = ' Order By Last Desc';
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_FILE ' + @SqlWhere + ' )'
	exec ('Select * ' + @SqlSelect + ' From DB_FILE ' + @SqlWhere + ' ' + @SqlOrder);
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;





































GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO














-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_LEVEL_EDIT]
	@LevelId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
    @Type int = 0,
    @SetConfig int = 0,
    @SetUser int = 0,
    @SetRegister int = 0,
    @SetFile int = 0,
    @Capacity bigint = 0,
    @IsDefault int = 0
AS
	IF @Name Is NULL Or @Name=''
		Return 3;
	ELSE
		IF (Select Count(*) From DB_LEVEL Where Name=@Name And LevelId<>@LevelId)>0 
			Return 2;
		ELSE BEGIN
			If (Select Sum(Capacity) From DB_STORAGE)>@Capacity BEGIN
				--	判断是否锁定
				Declare @Lock int
				Set @Lock = 0;
				IF @Type=1 OR @Type=2 BEGIN
					Update DB_LEVEL Set Type=0, Lock=0 Where Type=@Type;
					Set @Lock = 1;
					If @Type=2
						Set @Capacity = (Select Sum(Capacity) From DB_STORAGE);
				END
				IF @IsDefault>0 BEGIN
					Update DB_LEVEL Set IsDefault=0 Where IsDefault>0;
					Set @Lock = 1;
				END
				Update DB_LEVEL Set Name=@Name, Remark=@Remark, Type=@Type, Lock=@Lock, SetConfig=@SetConfig, [SetUser]=@SetUser, SetRegister=@SetRegister, SetFile=@SetFile, Capacity=@Capacity, IsDefault=@IsDefault Where LevelId=@LevelId;
				Update DB_LEVEL Set Users=(Select Count(*) From DB_USER Where LevelId=DB_LEVEL.LevelId) Where LevelId=@LevelId;
				--	修改权限表中的显示名称
				Update DB_POPEM Set OwnerName=@Name Where OwnerId=@LevelId And OwnerType=2;
				Return 0;
			END
			ELSE
				Return 1;
		END















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO















-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_FILE_DELETE]
	@FileId int = 0
AS
	If (Select Count(*) From DB_FILE Where FileId=@FileId And Lock=1) >0
		Return 2;
	Else
		If (Select Count(*) From DB_FILE Where FileId=@FileId)>0 BEGIN
			Declare @StorageId int
			Declare @OwnerId int 
			Declare @Length int 
			Set @StorageId = (Select StorageId From DB_FILE Where FileId=@FileId);
			Set @OwnerId = (Select OwnerId From DB_FILE Where FileId=@FileId);
			Set @Length = (Select Length From DB_FILE Where FileId=@FileId);
			--	删除文件信息
			Delete From DB_POPEM Where ResourceId=@FileId And ResourceType=3;
			Delete From DB_FILE_TOPIC Where FileId=@FileId;
			Delete From DB_FILE_TAG Where FileId=@FileId;
			Delete From DB_FILE Where FileId=@FileId;
			--	更新存储设备
			Update DB_STORAGE Set Used=Used-@Length, Last=GetDate() Where StorageId=@StorageId;
			--	更新用户信息
			Update DB_USER Set Used=Used-@Length Where UserId=@OwnerId;
			Return 0;
		END
		Else
			Return 1;















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO









-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_LEVEL_DELETE]
	@LevelId int = 0
AS
	IF (Select Count(*) From DB_USER Where LevelId=@LevelId)>0
		Return 3;
	ELSE BEGIN
		--	判断是否锁定
		Declare @Lock int
		Set @Lock = (Select Lock From DB_LEVEL Where LevelId=@LevelId);
		IF @Lock>0
			Return 2;
		ELSE
			IF (Select Count(*) From DB_LEVEL Where LevelId=@LevelId)>0 BEGIN
				Delete From DB_LEVEL Where LevelId=@LevelId;
				Delete From DB_POPEM Where OwnerId=@LevelId And OwnerType=2;
				Return 0;
			END
			ELSE
				Return 1;
	END










GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



























CREATE PROCEDURE [ET_STORAGE_LIST]
	@UserId int = 0
AS
	--	获取匿名访客分级编号
	Declare @GuestId int;
	Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
	If @GuestId is Null Set @GuestId = 0;
	--	判断当前用户
	If @UserId>0 Begin
		--	声明分级、分组变量
		Declare @GradeId int
		Declare @LevelId int
		Declare @IsAdministrator int
		--	为分级分组变量赋值
		Set @GradeId = (Select GradeId From DB_USER Where UserId=@UserId);
		Set @LevelId = (Select LevelId From DB_USER Where UserId=@UserId);
		Set @IsAdministrator = (Select Type From DB_LEVEL Where LevelId=@LevelId);
		--	如果为空，则设为零
		If @GradeId is Null Set @GradeId = 0;
		If @LevelId is Null Set @LevelId = 0;
		If @IsAdministrator is Null Set @IsAdministrator = 0;
		--	组合资源权限查询语句
		If @IsAdministrator = 2 Begin
			Select *,
			(Select Count(*) From DB_FOLDER Where StorageId=DB_STORAGE.StorageId) As HasChild,
			1 As CanCreate,
			1 As CanWrite,
			1 As CanRead,
			(Select Count(*) From DB_FILE Where StorageId=DB_STORAGE.StorageId) As Files
			From DB_STORAGE 
			Order By Name Asc;
		End
		Else Begin
			Select *,
			(Select Count(*) From DB_FOLDER Where StorageId=DB_STORAGE.StorageId) As HasChild,
			(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=DB_STORAGE.StorageId And ResourceType=1 And PopemCreate=1)  As CanCreate,
			(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=DB_STORAGE.StorageId And ResourceType=1 And PopemWrite=1)  As CanWrite,
			(Select Count(*) From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=DB_STORAGE.StorageId And ResourceType=1 And PopemRead=1)  As CanRead,
			(Select Count(*) From DB_FILE Where StorageId=DB_STORAGE.StorageId) As Files
			From DB_STORAGE Where Exists(Select * From DB_POPEM Where ( (OwnerType=1 And OwnerId=@GradeId) Or (OwnerType=2 And (OwnerId=@LevelId Or OwnerId=@GuestId)) Or (OwnerType=3 And OwnerId=@UserId) ) And ResourceId=DB_STORAGE.StorageId And ResourceType=1 And PopemRead=1)
			Order By Name Asc;
		End
	End
	Else Begin
		Select *,
		(Select Count(*) From DB_FOLDER Where StorageId=DB_STORAGE.StorageId) As HasChild,
		(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=DB_STORAGE.StorageId And ResourceType=1 And PopemCreate=1) As CanCreate,
		(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=DB_STORAGE.StorageId And ResourceType=1 And PopemWrite=1) As CanWrite,
		(Select Count(*) From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=DB_STORAGE.StorageId And ResourceType=1 And PopemRead=1) As CanRead,
		(Select Count(*) From DB_FILE Where StorageId=DB_STORAGE.StorageId) As Files
		From DB_STORAGE Where Exists(Select * From DB_POPEM Where OwnerType=2 And OwnerId=@GuestId And ResourceId=DB_STORAGE.StorageId And ResourceType=1 And PopemRead=1)
		Order By Name Asc;
	End


























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_USER_GETCOUNT]

AS
	Return (Select Count(*) From DB_USER);


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_GRADE_DELETE]
	@GradeId int = 0
AS
	IF (Select Count(*) From DB_USER Where GradeId=@GradeId)>0
		Return 3;
	ELSE BEGIN
		--	判断是否默认分组
		Declare @IsDefault int
		Set @IsDefault = (Select IsDefault From DB_GRADE Where GradeId=@GradeId);
		IF @IsDefault>0
			Return 2;
		ELSE
			IF (Select Count(*) From DB_GRADE Where GradeId=@GradeId)>0 BEGIN
				Delete From DB_GRADE Where GradeId=@GradeId;
				Delete From DB_POPEM Where OwnerId=@GradeId And OwnerType=1;
				Return 0;
			END
			ELSE
				Return 1;
	END








GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_GRADE_EDIT]
	@GradeId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
    @IsDefault int = 0
AS
	IF @Name Is NULL Or @Name=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_GRADE Where Name=@Name And GradeId<>@GradeId)>0 
			Return 1;
		ELSE BEGIN
			--	默认分组只能有一个
			IF @IsDefault>0 
				Update DB_GRADE Set IsDefault=0 Where IsDefault>0;
			Update DB_GRADE Set Name=@Name, Remark=@Remark, IsDefault=@IsDefault Where GradeId=@GradeId;
			Update DB_GRADE Set Users=(Select Count(*) From DB_USER Where GradeId=DB_GRADE.GradeId) Where GradeId=@GradeId;
			--	修改权限表中的显示名称
			Update DB_POPEM Set OwnerName=@Name Where OwnerId=@GradeId And OwnerType=1;
			Return 0;
		END











GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


























CREATE PROCEDURE [ET_USER_ONLINE]
	@Minute int = 0,
	@DefaultHeader varchar(255) = null
AS
	--	声明变量
	Declare @SqlSelect varchar(1000)
	Declare @SqlWhere varchar(1000)
	Declare @ReturnSql nvarchar(1000)
	Declare @ReturnValue int
	--	初始化变量
	Set @SqlSelect = '';
	Set @ReturnSql = '';
	Set @SqlWhere = '';
	--	组合显示视图
	Set @SqlWhere = ' Where DateAdd(Minute, ' + lTrim(@Minute) + ',Last)>getdate()';
	Set @SqlSelect = @SqlSelect + ', (Select Name From DB_GRADE Where GradeId=DB_USER.GradeId) As GradeName';
	Set @SqlSelect = @SqlSelect + ', (Select Name From DB_LEVEL Where LevelId=DB_USER.LevelId) As LevelName';
	Set @SqlSelect = @SqlSelect + ', StateName = CASE WHEN State=1 Then ''在线'' When State=2 Then ''隐身'' When State=3 Then ''忙碌'' Else ''离线'' End  ';
	Set @SqlSelect = @SqlSelect + ', SexName = CASE WHEN Sex=1 Then ''男'' When Sex=2 Then ''女'' Else ''未知'' End ';
	Set @SqlSelect = @SqlSelect + ', CONVERT(varchar, Birthday, 23) As BirthdayDate ';
	Set @SqlSelect = @SqlSelect + ', HeaderUrl = CASE WHEN Header is null Or Header='''' Then ''' + @DefaultHeader + ''' Else Header End ';
	--	更新状态
	Update DB_USER Set State=0 Where DateAdd(Minute, @Minute, Last)<getdate();
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_USER ' + @SqlWhere + ')'
	exec ('Select *' + @SqlSelect + ' From DB_USER ' + @SqlWhere + ' Order By Last Desc');
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;


























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

















-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_STORAGE_EDIT]
	@StorageId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
    @Path varchar(255) = null,
    @Capacity bigint = 0
AS
	IF @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 3;
	ELSE
		IF (Select Count(*) From DB_STORAGE Where Path=@Path And StorageId=@StorageId)<=0 
			And (
				(Select Count(*) From DB_FOLDER Where StorageId=@StorageId)>0
				Or 
				(Select Count(*) From DB_FILE Where StorageId=@StorageId)>0
			)
			Return 2;
		Else
			IF (Select Count(*) From DB_STORAGE Where Name=rtrim(ltrim(@Name)) And StorageId<>@StorageId)>0 
				Return 1;
			ELSE BEGIN
				Update DB_STORAGE Set Name=rtrim(ltrim(@Name)), Remark=@Remark, [Path]=@Path, Capacity=@Capacity Where StorageId=@StorageId;
				--	更新总空间
				Update DB_LEVEL Set Capacity=(Select Sum(Capacity) From DB_STORAGE) Where Type=2;
				Update DB_USER Set Capacity=(Select Sum(Capacity) From DB_STORAGE) Where Exists(Select * From DB_LEVEL Where LevelId=DB_USER.LevelId And Type=2) Or Capacity>(Select Sum(Capacity) From DB_STORAGE);
				--	修改权限表中的显示名称
				Update DB_POPEM Set ResourceName=rtrim(ltrim(@Name)) Where ResourceId=@StorageId And ResourceType=1;
				Return 0;
			END














GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_STORAGE_DELETE]
	@StorageId int = 0
AS
	IF	(Select Count(*) From DB_FOLDER Where StorageId=@StorageId)>0
		Or 
		(Select Count(*) From DB_FILE Where StorageId=@StorageId)>0
		Return 2;
	ELSE BEGIN
		IF (Select Count(*) From DB_STORAGE Where StorageId=@StorageId)>0 BEGIN
			Delete From DB_POPEM Where ResourceId=@StorageId And ResourceType=1;
			Delete From DB_STORAGE Where StorageId=@StorageId;
			--	更新总空间
			Update DB_LEVEL Set Capacity=(Select Sum(Capacity) From DB_STORAGE) Where Type=2;
			Update DB_USER Set Capacity=(Select Sum(Capacity) From DB_STORAGE) Where Exists(Select * From DB_LEVEL Where LevelId=DB_USER.LevelId And Type=2) Or Capacity>(Select Sum(Capacity) From DB_STORAGE);
			Return 0;
		END
		ELSE
			Return 1;
	END











GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO











CREATE PROCEDURE [ET_USER_SETITEM]
    @Username varchar(255) = null,
    @ExtandName varchar(255) = null,
    @ExtandValue varchar(255) = null
AS
	--	获得用户和扩展属性
	Declare @UserId int
	Declare @ExtandId int
	Set @UserId = (Select UserId From DB_USER Where Username=@Username);
	Set @ExtandId = (Select ExtandId From DB_EXTAND Where Name=@ExtandName);
	--	判断是否可更新
	IF @UserId>0 And @ExtandId>0
		IF @ExtandValue Is NULL Or @ExtandValue=''
			Return 2;
		ELSE BEGIN
			IF (Select Count(*) From DB_USER_EXTAND Where ExtandId=@ExtandId And ExtandValue=@ExtandValue And UserId=@UserId)>0
				Return 1;
			ELSE BEGIN
				Insert Into DB_USER_EXTAND (UserId, ExtandId, ExtandName, ExtandValue) Values(@UserId, @ExtandId, @ExtandName, @ExtandValue);
				Return 0;
			END
		END
	ELSE
		Select @ExtandId, @UserId
		Return 3;











GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










CREATE PROCEDURE [ET_USER_GETITEM]
    @UserId int = 0
AS
	Select * From DB_USER_EXTAND Where UserId=@UserId;
	Return (Select Count(*) From DB_USER_EXTAND Where UserId=@UserId);




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_EXTAND_EDIT]
	@ExtandId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
    @Type int = 0,
    @Required int = 0,
    @DefaultValue varchar(255) = null
AS
	IF @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_EXTAND Where Name=rtrim(ltrim(@Name)) And ExtandId<>@ExtandId)>0 
			Return 1;
		ELSE BEGIN
			Update DB_USER_EXTAND Set ExtandName=rtrim(ltrim(@Name)) Where ExtandId=@ExtandId;
			Update DB_EXTAND Set Name=rtrim(ltrim(@Name)), Remark=@Remark, Type=@Type, Required=@Required, DefaultValue=@DefaultValue Where ExtandId=@ExtandId;
			Return 0;
		END








GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_EXTAND_DELETE]
	@ExtandId int = 0
AS
	IF (Select Count(*) From DB_EXTAND Where ExtandId=@ExtandId)>0 BEGIN
		--	扩展项目相关值
		Delete From DB_USER_EXTAND Where ExtandId=@ExtandId;
		Delete From DB_EXTAND_ITEM Where ExtandId=@ExtandId;
		Delete From DB_EXTAND Where ExtandId=@ExtandId;
		Return 0;
	END
	ELSE
		Return 1;







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO









-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_POPEM_REMOVESUB]
	@OwnerId int = 0,
	@OwnerType int = 0,
	@ResourceId int = 0,
	@ResourceType int = 0
AS
	--	初始化上级路径
	Declare @DisplayPath varchar(255);
	Set @DisplayPath = Case 
		When @ResourceType=1 Then (Select Name + '>' From DB_Storage Where StorageId=@ResourceId)
		Else (Select DisplayPath + Name + '/' From DB_FOLDER Where FolderId=@ResourceId)
	End
	--	删除文件权限项目
	Delete From DB_POPEM Where OwnerId=@OwnerId And OwnerType=@OwnerType And ResourceType=3 And Exists(Select * From DB_FILE Where DisplayPath Like @DisplayPath + '%' And FileId=DB_POPEM.ResourceId)
	--	删除文件夹权限项目
	Delete From DB_POPEM Where OwnerId=@OwnerId And OwnerType=@OwnerType And ResourceType=2 And Exists(Select * From DB_FOLDER Where DisplayPath Like @DisplayPath + '%' And FolderId=DB_POPEM.ResourceId)
	Return 0;










GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


















CREATE PROCEDURE [ET_MESSAGE_FILE_ADD]
    @MessageId int = 0,
    @ResourceId int = 0,
    @ResourceType int = 0
AS
	If @ResourceType=3 
		Insert Into DB_MESSAGE_FILE (MessageId, ResourceId, ResourceType, Name, OwnerId, OwnerName, StorageId, FolderId, DisplayPath, Type, Extension, Length, Thumb, State, First, Last) Select @MessageId, FileId, 3, Name, OwnerId, OwnerName, StorageId, FolderId, DisplayPath, Type, Extension, Length, Thumb, State, First, Last From DB_FILE Where FileId=@ResourceId;
	Else
		Insert Into DB_MESSAGE_FILE (MessageId, ResourceId, ResourceType, Name, OwnerId, OwnerName, StorageId, FolderId, DisplayPath, Type, Extension, Length, Thumb, State, First, Last) Select @MessageId, FolderId, 2, Name, OwnerId, OwnerName, StorageId, FolderId, DisplayPath, '文件包', 'folder', 0, Thumb, State, First, Last From DB_FOLDER Where FolderId=@ResourceId;
	Return 0;















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_FOLDER_GETID]
	@StorageId int = 0,
	@ParentId int = 0,
	@OwnerId int = 0,
    @Name varchar(255) = null
AS
	If @OwnerId>0
		Return (Select FolderId From DB_FOLDER Where Name=@Name And StorageId=@StorageId And ParentId=@ParentId And OwnerId=@OwnerId);
	Else
		Return (Select FolderId From DB_FOLDER Where Name=@Name And StorageId=@StorageId And ParentId=@ParentId);









GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





















-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_FOLDER_EDIT]
	@FolderId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
	@StorageId int = 0,
	@ParentId int = 0,
    @Thumb varchar(255) = null,
	@State int = 0
AS
	IF @Name Is NULL Or @Name=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_FOLDER Where Name=@Name And FolderId<>@FolderId And ParentId=@ParentId And StorageId=@StorageId)>0 
			Return 1;
		ELSE BEGIN
			--	获取摘要
			Declare @OName varchar(255);
			Declare @ODisplayPath varchar(255);
			Declare @Summary varchar(3000);
			Set @OName = (Select Name From DB_FOLDER Where FolderId=@FolderId);
			Set @ODisplayPath = (Select DisplayPath From DB_FOLDER Where FolderId=@FolderId);
			--	避免 Summary 空值
			If @Name is null Or @Name = ''
				Set @Name = '';
			If @Remark is null Or @Remark = ''
				Set @Remark = '';
			Set @Summary = @Name + '_' + @Remark;
			Update DB_FOLDER Set Name=@Name, Remark=@Remark, Summary=@Summary, Thumb=@Thumb, State=@State Where FolderId=@FolderId;
			--	更新路径信息
			Update DB_FOLDER Set DisplayPath = Replace(DisplayPath, @ODisplayPath + @OName + '/', @ODisplayPath + @Name + '/') Where DisplayPath like @ODisplayPath + @OName + '/%';
			Update DB_FILE Set DisplayPath = Replace(DisplayPath, @ODisplayPath + @OName + '/', @ODisplayPath + @Name + '/') Where DisplayPath like @ODisplayPath + @OName + '/%';
			--	更新存储设备
			Update DB_STORAGE Set Last=GetDate() Where StorageId=@StorageId;
			--	修改权限表中的显示名称
			Update DB_POPEM Set ResourceName=@Name Where ResourceId=@FolderId And ResourceType=2;
			--	更新上级路径
			If @ParentId>0 Begin
				Declare @iParentId int
				Set @iParentId = @ParentId;
				while @iParentId>0 Begin
					Update DB_FOLDER Set Last=GetDate() Where FolderId=@iParentId;
					Set @iParentId = (Select ParentId From DB_FOLDER Where FolderId=@iParentId);
				End
			End
			Return 0;
		END






















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO











CREATE PROCEDURE [ET_RESOURCE_GETLENGTH]
	@FolderId int = 0,
	@StorageId int = 0,
	@Length bigint output
AS
	If @FolderId>=0
		If @FolderId=0 Begin
			Set @Length = (Select Sum(Length) From DB_FILE Where StorageId=@StorageId);
			If @Length Is NULL
				Set @Length = 0;
		End
		Else Begin
			If (Select Count(*) From DB_FOLDER Where FolderId=@FolderId)>0 Begin
				Declare @DisplayPath varchar(255)
				Set @DisplayPath = (Select DisplayPath + Name + '/' From DB_FOLDER Where FolderId=@FolderId) + '%';
				Set @Length = (Select Sum(Length) From DB_FILE Where DisplayPath Like @DisplayPath);
				If @Length Is NULL
					Set @Length = 0;
			End
			Else
				Set @Length = 0;
		End
	Else
		Set @Length = 0;


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO












CREATE PROCEDURE [ET_EXTAND_LIST]
AS
	Select *,
	TypeName=Case When Type=2 Then '下拉框' When Type=3 Then '多选框' When Type=4 Then '文本框' Else '输入框' End,
	RequiredName=Case When Required=1 Then '是' Else '否' End
	From DB_EXTAND Order By ExtandId Asc;
	Return 0;












GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [ET_EXTAND_ADDITEM]
    @ExtandId int = 0,
	@Name varchar(255) = null
AS
	IF @ExtandId >0 And (Select Count(*) From DB_EXTAND Where ExtandId=@ExtandId)>0
		IF @Name Is NULL Or rtrim(ltrim(@Name))=''
			Return 2;
		ELSE
			IF (Select Count(*) From DB_EXTAND_ITEM Where Name=rtrim(ltrim(@Name)) And ExtandId=@ExtandId)>0 
				Return 1;
			ELSE BEGIN
				Insert Into DB_EXTAND_ITEM (ExtandId, Name) Values(@ExtandId, rtrim(ltrim(@Name)));
				Return 0;
			END
	Else
		Return 3;








GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [ET_EXTAND_ADD]
    @Name varchar(255) = null,
    @Remark varchar(2000) = null,
    @Type int = 0,
    @Required int = 0,
    @DefaultValue varchar(255) = null
AS
	IF @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_EXTAND Where Name=rtrim(ltrim(@Name)))>0 
			Return 1;
		ELSE BEGIN
			IF @Type=0
				Set @Type=1;
			Insert Into DB_EXTAND (Name, Remark, Type, Required, DefaultValue) Values(rtrim(ltrim(@Name)), @Remark, @Type, @Required, @DefaultValue);
			Return 0;
		END








GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_MESSAGE_DELETE]
	@MessageId int = 0
AS
	IF (Select Count(*) From DB_MESSAGE Where MessageId=@MessageId)>0 BEGIN
		--	删除消息附件
		Delete From DB_MESSAGE_FILE Where MessageId=@MessageId;
		--	更新分组，分级信息
		Delete From DB_MESSAGE Where MessageId=@MessageId;
		Return 0;
	END
	ELSE
		Return 1;











GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO















CREATE PROCEDURE [ET_POPEM_LIST]
    @ResourceId int = 0,
	@ResourceType int = 0
AS
	Select *,
	OwnerTypeName=(Case When OwnerType=1 Then '分组' When OwnerType=2 Then '分级' Else '用户' End),
	CreateName=(Case When PopemCreate=1 Then '有' Else '无' End),
	WriteName=(Case When PopemWrite=1 Then '有' Else '无' End),
	EditName=(Case When PopemEdit=1 Then '有' Else '无' End),
	ReadName=(Case When PopemRead=1 Then '有' Else '无' End),
	DownloadName=(Case When PopemDownload=1 Then '有' Else '无' End)
	From DB_POPEM Where ResourceId=@ResourceId And ResourceType=@ResourceType Order By Lock Desc;
	Return (Select Count(*) From DB_POPEM Where ResourceId=@ResourceId And ResourceType=@ResourceType);









GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_POPEM_GET]
	@OwnerId int = 0,
	@OwnerType int = 0,
	@ResourceId int = 0,
	@ResourceType int = 0
AS
	Select * From DB_POPEM Where OwnerId=@OwnerId And OwnerType=@OwnerType And ResourceId=@ResourceId And ResourceType=@ResourceType;
	Return 0;







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO











-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_FOLDER_DELETE]
	@FolderId int = 0
AS
	IF (Select Count(*) From DB_FOLDER Where ParentId=@FolderId)>0 Or
		(Select Count(*) From DB_FILE Where FolderId=@FolderId)>0
		Return 2;
	ELSE
		IF (Select Count(*) From DB_FOLDER Where FolderId=@FolderId)>0 BEGIN
			Delete From DB_POPEM Where ResourceId=@FolderId And ResourceType=2;
			Delete From DB_FOLDER Where FolderId=@FolderId;
			Return 0;
		END
		Else
			Return 1;











GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
























CREATE PROCEDURE [ET_MESSAGE_GETID]
	@Name varchar(255) = null,
    @RecvId int = 0,
	@SendId int = 0
AS
	Return (Select Max(MessageId) From DB_MESSAGE Where Name=@Name And RecvId=@RecvId And SendId=@SendId);


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
























CREATE PROCEDURE [ET_MESSAGE_DETAIL]
	@MessageId int = 0
AS
	Update DB_MESSAGE Set Last=GetDate(),State=1 Where MessageId=@MessageId;
	Select * From DB_MESSAGE Where MessageId=@MessageId;
	Return 0;

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_STORAGE_GETCOUNT]

AS
	Return (Select Count(*) From DB_STORAGE);


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_STORAGE_GETCAPACITY]
AS
	Select Sum(Capacity) From DB_STORAGE;
	Return 0;




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO















CREATE PROCEDURE [ET_LEVEL_ADD]
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
    @Type int = 0,
    @SetConfig int = 0,
    @SetUser int = 0,
    @SetRegister int = 0,
    @SetFile int = 0,
    @Capacity bigint = 0,
    @IsDefault int = 0
AS
	IF @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 3;
	ELSE
		IF (Select Count(*) From DB_LEVEL Where Name=rtrim(ltrim(@Name)))>0 
			Return 2;
		ELSE BEGIN
			If (Select Sum(Capacity) From DB_STORAGE)>@Capacity BEGIN
				--	判断是否锁定
				Declare @Lock int
				Set @Lock = 0;
				IF @Type=1 OR @Type=2 BEGIN
					Update DB_LEVEL Set Type=0, Lock=0 Where Type=@Type;
					Set @Lock = 1;
					If @Type=2
						Set @Capacity = (Select Sum(Capacity) From DB_STORAGE);
				END
				IF @IsDefault>0 BEGIN
					Update DB_LEVEL Set IsDefault=0 Where IsDefault>0;
					Set @Lock = 1;
				END
				Insert Into DB_LEVEL (Name, Remark, Type, Lock, SetConfig, [SetUser], SetRegister, SetFile, Capacity, IsDefault) Values(rtrim(ltrim(@Name)), @Remark, @Type, @Lock, @SetConfig, @SetUser, @SetRegister, @SetFile, @Capacity, @IsDefault);
				Return 0;
			END
			ELSE
				Return 1;
		END















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







CREATE PROCEDURE [ET_STORAGE_DETAIL]
    @StorageId int = 0
AS
	Select * From DB_STORAGE Where StorageId=@StorageId;
	Return 0;







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO









CREATE PROCEDURE [ET_ATTRIBUTE_ADD]
	@ClassId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
    @Type int = 0,
    @Required int = 0,
    @Special int = 0,
    @DefaultValue varchar(255) = null
AS
	IF @ClassId>0 And (Select Count(*) From DB_CLASS Where ClassId=@ClassId)>0
		IF @Name Is NULL Or rtrim(ltrim(@Name))=''
			Return 3;
		ELSE
			IF @Special>0 And (Select Count(*) From DB_ATTRIBUTE Where Special=@Special)>0
				Return 2;
			ELSE
				IF (Select Count(*) From DB_ATTRIBUTE Where Name=rtrim(ltrim(@Name)))>0 
					Return 1;
				ELSE BEGIN
					Insert Into DB_ATTRIBUTE (ClassId, Name, Remark, Type, Required, Special, DefaultValue) Values(@ClassId, rtrim(ltrim(@Name)), @Remark, @Type, @Required, @Special, @DefaultValue);
					Return 0;
				END
	ELSE 
		Return 4;









GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO









CREATE PROCEDURE [ET_CLASS_ADDEXTENSION]
    @ClassId int = 0,
	@Name varchar(255) = null,
	@Extension varchar(255) = null
AS
	IF @ClassId>0 And (Select Count(*) From DB_CLASS Where ClassId=@ClassId)>0
		IF @Extension Is NULL Or @Extension='' Or @Name Is NULL Or rtrim(ltrim(@Name))=''
			Return 2;
		ELSE
			IF (Select Count(*) From DB_CLASS_EXTENSION Where Extension=@Extension)>0 
				Return 1;
			ELSE BEGIN
				Insert Into DB_CLASS_EXTENSION (ClassId, Name, Extension) Values(@ClassId, rtrim(ltrim(@Name)), @Extension);
				Return 0;
			END
	ELSE
		Return 3;









GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_CLASS_EDIT]
	@ClassId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
	@Icon varchar(255) = null,
	@Url varchar(255) = null,
	@Type int = 0
AS
	IF @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_CLASS Where Name=rtrim(ltrim(@Name)) And ClassId<>@ClassId)>0 
			Return 1;
		ELSE BEGIN
			Update DB_CLASS Set Name=rtrim(ltrim(@Name)), Remark=@Remark, Icon=@Icon, Url=@Url, Type=@Type Where ClassId=@ClassId;
			Return 0;
		END







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







CREATE PROCEDURE [ET_CLASS_ADD]
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
	@Icon varchar(255) = null,
	@Url varchar(255) = null,
	@Type int = 0
AS
	IF @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_CLASS Where Name=rtrim(ltrim(@Name)))>0 
			Return 1;
		ELSE BEGIN
			Insert Into DB_CLASS (Name, Remark, Icon, Url, Type) Values(rtrim(ltrim(@Name)), @Remark, @Icon, @Url, @Type);
			Return 0;
		END







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






























CREATE PROCEDURE [ET_USER_STAT]
	@UserId int = 0
AS
	Select *,
		(Select Count(*) From DB_FILE Where ClassId=DB_CLASS.ClassId And OwnerId=@UserId) As Resources
	From DB_CLASS Where Type=0
	Return 0;

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO














CREATE PROCEDURE [ET_ATTRIBUTE_LIST]
	@ClassId int = 0
AS
	If @ClassId>0
		Select *, 
		(Select Name From DB_CLASS Where ClassId=DB_ATTRIBUTE.ClassId) As ClassName,
		TypeName = (Case When Type=2 Then '下拉框' When Type=3 Then '多选框' When Type=4 Then '文本框' Else '输入框' End),
		RequiredName = (Case When Required=1 Then '是' Else '否' End),
		SpecialName = (Case When Special=1 Then '相机品牌' When Special=2 Then '相机型号' When Special=3 Then '宽度' When Special=4 Then '高度' When Special=5 Then '歌手' When Special=6 Then '专辑' When Special=7 Then '公司' When Special=8 Then '网站' Else '普通' End)
		From DB_ATTRIBUTE Where ClassId=@ClassId Order By AttributeId Asc;
	Else
		Select *, 
		(Select Name From DB_CLASS Where ClassId=DB_ATTRIBUTE.ClassId) As ClassName,
		TypeName = (Case When Type=2 Then '下拉框' When Type=3 Then '多选框' When Type=4 Then '文本框' Else '输入框' End),
		RequiredName = (Case When Required=1 Then '是' Else '否' End),
		SpecialName = (Case When Special=1 Then '相机品牌' When Special=2 Then '相机型号' When Special=3 Then '宽度' When Special=4 Then '高度' When Special=5 Then '歌手' When Special=6 Then '专辑' When Special=7 Then '公司' When Special=8 Then '网站' Else '普通' End)
		From DB_ATTRIBUTE Order By AttributeId Asc;
	Return 0;














GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO














CREATE PROCEDURE [ET_ATTRIBUTE_ADDTAG]
    @AttributeId int = 0,
	@Name varchar(255) = null,
	@Near varchar(255) = null,
	@Vouch int = 0
AS
	IF @AttributeId>0 And (Select Count(*) From DB_ATTRIBUTE Where AttributeId=@AttributeId)>0
		IF @Name Is NULL Or rtrim(ltrim(@Name))=''
			Return 2;
		ELSE BEGIN
			IF (Select Count(*) From DB_ATTRIBUTE_TAG Where Name=rtrim(ltrim(@Name)) And AttributeId=@AttributeId)>0 
				Return 1;
			ELSE BEGIN
				--	获取分类编号
				Declare @ClassId int
				Set @ClassId = (Select ClassId From DB_ATTRIBUTE Where AttributeId=@AttributeId);
				Insert Into DB_ATTRIBUTE_TAG (ClassId, AttributeId, Name, Near, Vouch) Values(@ClassId, @AttributeId, rtrim(ltrim(@Name)), @Near, @Vouch);
				Return 0;
			END
		END
	ELSE
		Return 3;














GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_ATTRIBUTE_EDITTAG]
    @TagId int = 0,
	@Name varchar(255) = null,
	@Near varchar(255) = null,
	@Vouch int = 0
AS
	IF @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_ATTRIBUTE_TAG Where Name=rtrim(ltrim(@Name)) And TagId<>@TagId)>0 
			Return 1;
		ELSE BEGIN
			--	获取分类编号
			Declare @ClassId int
			Set @ClassId = (Select ClassId From DB_ATTRIBUTE Where Exists(Select * From DB_ATTRIBUTE_TAG Where AttributeId=DB_ATTRIBUTE.AttributeId And TagId=@TagId));
			Update DB_ATTRIBUTE_TAG Set ClassId=@ClassId, Name=rtrim(ltrim(@Name)), Near=@Near, Vouch=@Vouch, Last=GetDate() Where TagId=@TagId;
			Return 0;
		END










GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_ATTRIBUTE_EDIT]
	@AttributeId int = 0,
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
    @Type int = 0,
    @Required int = 0,
    @Special int = 0,
    @DefaultValue varchar(255) = null
AS
	IF @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_ATTRIBUTE Where Name=rtrim(ltrim(@Name)) And AttributeId<>@AttributeId)>0 
			Return 1;
		ELSE BEGIN
			--	特殊属性只能存在一条
			IF @Special>0
				Update DB_ATTRIBUTE Set Special=0 Where Special=@Special
			Update DB_ATTRIBUTE Set Name=rtrim(ltrim(@Name)), Remark=@Remark, Type=@Type, Required=@Required, Special=@Special, DefaultValue=@DefaultValue Where AttributeId=@AttributeId;
			Return 0;
		END











GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_ATTRIBUTE_DELETE]
	@AttributeId int = 0
AS
	Declare @Special int
	Set @Special=(Select Special From DB_ATTRIBUTE Where AttributeId=@AttributeId);
	IF @Special>0
		Return 2;
	ELSE
		IF (Select Count(*) From DB_ATTRIBUTE Where AttributeId=@AttributeId)>0 BEGIN
			--	删除相关标签
			Delete From DB_FILE_TAG Where AttributeId=@AttributeId;
			Delete From DB_ATTRIBUTE_TAG Where AttributeId=@AttributeId;
			Delete From DB_ATTRIBUTE Where AttributeId=@AttributeId;
			Return 0;
		END
		ELSE
			Return 1;









GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





























CREATE PROCEDURE [ET_FILE_TOPIC]
	@TopicId int = 0
AS
	Select * From DB_FILE_TOPIC Where TopicId=@TopicId;
	Return 0;





























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO














-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_FILE_REMOVETOPIC]
	@FileId int = 0,
	@TopicId int = 0
AS
	Delete From DB_FILE_TOPIC Where FileId=@FileId And TopicId=@TopicId;
	Return 0;













GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





























CREATE PROCEDURE [ET_FILE_GETTOPIC]
	@FileId int = 0
AS
	Select * From DB_FILE_TOPIC Where FileId=@FileId Order by First Desc;
	Return (Select Count(*) From DB_FILE_TOPIC Where FileId=@FileId);





























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







CREATE PROCEDURE [ET_GRADE_ADD]
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
    @IsDefault int = 0
AS
	IF @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 2;
	ELSE
		IF (Select Count(*) From DB_GRADE Where Name=rtrim(ltrim(@Name)))>0 
			Return 1;
		ELSE BEGIN
			IF @IsDefault>0 
				Update DB_GRADE Set IsDefault=0 Where IsDefault>0;
			Insert Into DB_GRADE (Name, Remark, IsDefault) Values(rtrim(ltrim(@Name)), @Remark, @IsDefault);
			Return 0;
		END







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO












CREATE PROCEDURE [ET_GRADE_LIST]
AS
	Select *,
	IsDefaultName = (Case When IsDefault=1 Then '是' Else '否' End)
	From DB_GRADE Order By GradeId Asc;
	Return 0;












GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

























CREATE PROCEDURE [ET_LOG_LIST]
    @OwnerId int = 0,
	@OwnerIP varchar(50) = null,
	@RelationId int = 0,
	@RelationType int = 0,
	@OperateType int = 0,
	@StartDate varchar(255) = null,
	@EndDate varchar(255) = null,
	@GradeId int = 0
AS
	--	声明变量
	Declare @SqlSelect varchar(2000)
	Declare @SqlWhere varchar(255)
	Declare @SqlOrder varchar(255)
	Declare @ReturnSql nvarchar(255)
	Declare @ReturnValue int
	Declare @SupperLevelId int 
	--	初始化变量
	Set @SqlSelect = '';
	Set @SqlWhere = '';
	Set @SqlOrder = '';
	Set @ReturnSql = '';
	Set @SupperLevelId = (Select LevelId From DB_LEVEL Where Type=2);
	--	组合显示视图
	Set @SqlSelect = @SqlSelect + ', RelationTypeName = case when RelationType=1 then ''分类'' when RelationType=2 then ''属性'' when RelationType=3 then ''扩展项'' when RelationType=4 then ''分组''  when RelationType=5 then ''分级''  when RelationType=6 then ''用户'' when RelationType=7 then ''存储设备'' when RelationType=8 then ''文件夹'' when RelationType=9 then ''文件'' else ''未知'' end ';
	Set @SqlSelect = @SqlSelect + ', OperateTypeName = case when OperateType=1 then ''增加'' when OperateType=2 then ''删除'' when OperateType=3 then ''编辑'' when OperateType=4 then ''修改'' when OperateType=5 then ''查找'' when OperateType=6 then ''读取'' when OperateType=7 then ''下载'' when OperateType=8 then ''推荐'' when OperateType=9 then ''评论'' else ''未知'' end';
	--	指定用户
	If @OwnerId>0
		Set @SqlWhere = ' Where OwnerId=' + lTrim(@OwnerId) + ' And (RelationType<>6 And OperateType<>6) ';
	Else If @OwnerId=-1
		Set @SqlWhere = ' Where ((RelationType<>6 Or RelationType<7) And OperateType<>6) ';
	Else
		Set @SqlWhere = ' Where LogId>0 ';
	If @OwnerIP is not Null And @OwnerIP<>''
		Set @SqlWhere = @SqlWhere + ' And OwnerIP=''' + @OwnerIP + ''' ';
	--	指定分组
	If @GradeId>0
		Set @SqlWhere = @SqlWhere + ' And Exists(Select * From DB_USER Where UserId=DB_LOG.OwnerId And GradeId=' + lTrim(@GradeId) + ' And LevelId<>' + lTrim(@SupperLevelId) + ') ';
	--	指定资源编号
	If @RelationId>0
		Set @SqlWhere = @SqlWhere + ' And RelationId=' + lTrim(@RelationId) + ' ';
	--	指定资源类型
	If @RelationType>0
		Set @SqlWhere =  @SqlWhere + ' And RelationType=' + lTrim(@RelationType) + ' ';
	--	指定操作类型
	If @OperateType>0
		Set @SqlWhere =  @SqlWhere + ' And OperateType=' + lTrim(@OperateType) + ' ';
	If @StartDate is not Null And @StartDate<>'' And @EndDate is not Null And @EndDate<>''
		Set @SqlWhere = @SqlWhere + ' And (First>''' + @StartDate + ''' And First <DateAdd(dd, 1, ''' + @EndDate + ''')) ';
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_LOG ' + @SqlWhere + ')'
	exec ('Select *' + @SqlSelect + ' From DB_LOG ' + @SqlWhere + ' ' + @SqlOrder + ' Order By First Desc ');
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;

















GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_LEVEL_GETSPECIAL]
	@Type int = 0
AS
	Return (Select LevelId From DB_LEVEL Where Type=@Type);




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO










CREATE PROCEDURE [ET_LEVEL_DETAIL]
    @LevelId int = 0
AS
	Select * From DB_LEVEL Where LevelId=@LevelId;
	Return 0;










GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO












CREATE PROCEDURE [ET_LEVEL_LIST]
AS
	Declare @KB bigint;
	Declare @MB bigint;
	Declare @GB bigint;
	Declare @TB bigint;
	Set @KB = 1024;
	Set @MB = 1024*@KB;
	Set @GB = 1024*@MB;
	Set @TB = 1024*@GB;
	Select *,
	TypeName = (Case When Type=1 Then '匿名用户' When Type=2 Then '超级管理员' When Type=3 Then '分组管理员' Else '普通' End),
	LockName = (Case When Lock=1 Then '是' Else '否' End),
	SetConfigName = (Case When SetConfig=1 Then '有' Else '无' End),
	SetUserName = (Case When [SetUser]=1 Then '有' Else '无' End),
	SetRegisterName = (Case When SetRegister=1 Then '有' Else '无' End),
	SetFileName = (Case When SetFile=1 Then '有' Else '无' End),
	CapacityName=(Case When Capacity>=@TB Then lTrim(Capacity/@TB) + 'TB'  When Capacity>=@GB Then lTrim(Capacity/@GB) + 'GB' When Capacity>=@MB Then lTrim(Capacity/@MB) + 'MB' When Capacity>=@KB Then lTrim(Capacity/@KB) + 'KB' Else lTrim(Capacity) + 'B' End),
	IsDefaultName = (Case When IsDefault=1 Then '是' Else '否' End)
	From DB_LEVEL Order By LevelId Asc;
	Return 0;












GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





























CREATE PROCEDURE [ET_USER_LIST]
    @Key varchar(255) = null,
	@Extand int = 0,
	@GradeId int = 0,
	@LevelId int = 0,
	@Order varchar(255) = null,
	@DefaultHeader varchar(255) = null
AS
	--	声明变量
	Declare @SqlSelect varchar(1000)
	Declare @SqlWhere varchar(1000)
	Declare @SqlOrder varchar(1000)
	Declare @ReturnSql nvarchar(1000)
	Declare @ReturnValue int
	Declare @SupperLevelId int
	--	初始化变量
	Set @SqlSelect = '';
	Set @SqlWhere = '';
	Set @SqlOrder = '';
	Set @ReturnSql = '';
	Set @SupperLevelId = (Select LevelId From DB_LEVEL Where Type=2);
	--	组合显示视图
	Set @SqlSelect = @SqlSelect + ', (Select Name From DB_GRADE Where GradeId=DB_USER.GradeId) As GradeName';
	Set @SqlSelect = @SqlSelect + ', (Select Name From DB_LEVEL Where LevelId=DB_USER.LevelId) As LevelName';
	Set @SqlSelect = @SqlSelect + ', StateName = CASE WHEN State=1 Then ''在线'' When State=2 Then ''隐身'' When State=3 Then ''忙碌'' Else ''离线'' End  ';
	Set @SqlSelect = @SqlSelect + ', SexName = CASE WHEN Sex=1 Then ''男'' When Sex=2 Then ''女'' Else ''未知'' End ';
	Set @SqlSelect = @SqlSelect + ', CONVERT(varchar, Birthday, 23) As BirthdayDate ';
	Set @SqlSelect = @SqlSelect + ', HeaderUrl = CASE WHEN Header is null Or Header='''' Then ''' + @DefaultHeader + ''' Else Header End ';
	--	关键字查询
	If @Key<>'' Begin
		Set @SqlWhere = ' Where (Username=''' + rtrim(ltrim(@Key)) + ''' Or Email=''' + rtrim(ltrim(@Key)) + ''' ';
		If @Extand=1
			Set @SqlWhere = @SqlWhere + ' Or Exists(Select * From DB_USER_EXTAND Where UserId=DB_USER.UserId And ExtandValue=''' + rtrim(ltrim(@Key)) + ''') ';
		Set @SqlWhere = @SqlWhere + ') ';
	End
	Else
		Set @SqlWhere = ' Where UserId>0 ';
	--	是否检索分组
	If @GradeId>0
		Set @SqlWhere = @SqlWhere + ' And GradeId=' + lTrim(@GradeId) + ' And LevelId<>' + lTrim(@SupperLevelId) + ' ';
	--	是否检索分级
	If @LevelId>0
		Set @SqlWhere = @SqlWhere + ' And LevelId=' + lTrim(@LevelId) + ' ';
	--	组合排序规则
	If @Order<>''
		Set @SqlOrder = ' Order By ' + @Order;
	Else
		Set @SqlOrder = ' Order By Last Desc';
	--	统计记录总数
	Set @ReturnSql = 'Set @Return = (Select Count(*) From DB_USER ' + @SqlWhere + ')'
	exec ('Select *' + @SqlSelect + ' From DB_USER ' + @SqlWhere + ' ' + @SqlOrder);
	exec sp_executesql @ReturnSql, N'@Return int output', @ReturnValue output ;
	Return @ReturnValue;





























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [ET_ATTRIBUTE_GETTAG]
    @TagId int = 0
AS
	Select *, 
	(Select Name From DB_ATTRIBUTE Where AttributeId=DB_ATTRIBUTE_TAG.AttributeId) As AttributeName , 
	(Select Name From DB_CLASS Where ClassId=DB_ATTRIBUTE_TAG.ClassId) As ClassName 
	From DB_ATTRIBUTE_TAG Where TagId=@TagId;
	Return 0;








GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [ET_ATTRIBUTE_DELETETAG]
    @TagId int = 0
AS
	Delete From DB_ATTRIBUTE_TAG Where TagId=@TagId;
	Return 0;



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [ET_ATTRIBUTE_GETTAGID]
	@AttributeId int = 0,
    @TagName varchar(255)
AS
	Select * From DB_ATTRIBUTE_TAG Where Name=@TagName And AttributeId=@AttributeId;
	Return 0;








GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO









CREATE PROCEDURE [ET_CLASS_EXTENSIONS]
    @ClassId int = 0
AS
	If @ClassId>0
		Select *, (Select Name From DB_CLASS Where ClassId=DB_CLASS_EXTENSION.ClassId) As ClassName From DB_CLASS_EXTENSION Where ClassId=@ClassId Order By Extension Asc;
	Else
		Select *, (Select Name From DB_CLASS Where ClassId=DB_CLASS_EXTENSION.ClassId) As ClassName From DB_CLASS_EXTENSION Order By Extension Asc;
	Return 0;









GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [ET_CLASS_DELETEEXTENSION]
    @ClassId int = 0,
	@Extension varchar(255) = null
AS
	IF (Select Count(*) From DB_CLASS_EXTENSION Where ClassId=@ClassId And Extension=@Extension)>0 BEGIN
		Delete From DB_CLASS_EXTENSION Where ClassId=@ClassId And Extension=@Extension;
		Return 0;
	END
	ELSE
		Return 1;



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_CLASS_DELETE]
	@ClassId int = 0
AS
	IF (Select Count(*) From DB_CLASS_EXTENSION Where ClassId=@ClassId)>0
		Return 3;
	ELSE
		IF(Select Count(*) From DB_ATTRIBUTE Where ClassId=@ClassId)>0
			Return 2;
		ELSE
			IF (Select Count(*) From DB_CLASS Where ClassId=@ClassId)>0 BEGIN
				Delete From DB_CLASS Where ClassId=@ClassId;
				Return 0;
			END
			ELSE
				Return 1;






GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO















-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


CREATE PROCEDURE [ET_FILE_REMOVETAG]
	@FileId int = 0,
	@AttributeId int = 0,
	@TagName varchar(255) = null
AS
	If @AttributeId>0
		If @TagName is not null and @TagName <> ''
			Delete From DB_FILE_TAG Where FileId=@FileId And AttributeId=@AttributeId And TagName=@TagName;
		Else
			Delete From DB_FILE_TAG Where FileId=@FileId And AttributeId=@AttributeId;
	Else
		Delete From DB_FILE_TAG Where FileId=@FileId;
	Return 0;














GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO












CREATE PROCEDURE [ET_FILE_GETTAG]
    @FileId int = 0
AS
	Select * From DB_FILE_TAG Where FileId=@FileId Order By AttributeId Asc;
	Return (Select Count(*) From DB_FILE_TAG Where FileId=@FileId);






GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO









CREATE PROCEDURE [ET_EXTAND_ITEMS]
    @ExtandId int = 0
AS
	IF @ExtandId>0
		Select *, 
		(Select Name From DB_EXTAND Where ExtandId=DB_EXTAND_ITEM.ExtandId) As ExtandName 
		From DB_EXTAND_ITEM Where ExtandId=@ExtandId Order By Name Asc;
	Else
		Select *, 
		(Select Name From DB_EXTAND Where ExtandId=DB_EXTAND_ITEM.ExtandId) As ExtandName 
		From DB_EXTAND_ITEM Order By Name Asc;
	Return 0;









GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [ET_EXTAND_DELETEITEM]
    @ExtandId int = 0,
	@Name varchar(255) = null
AS
	IF (Select Count(*) From DB_EXTAND_ITEM Where Name=@Name And ExtandId=@ExtandId)>0 BEGIN
		Delete From DB_EXTAND_ITEM Where Name=@Name And ExtandId=@ExtandId;
		Return 0;
	END
	ELSE
		Return 1;




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO









-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_FILE_GETID]
	@StorageId int = 0,
	@FolderId int = 0,
	@OwnerId int = 0,
    @Name varchar(255) = null
AS
	If @OwnerId>0
		Return (Select FileId From DB_FILE Where Name=@Name And StorageId=@StorageId And FolderId=@FolderId And OwnerId=@OwnerId);
	Else
		Return (Select FileId From DB_FILE Where Name=@Name And StorageId=@StorageId And FolderId=@FolderId);
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [ET_USER_GETUSED]
	@OwnerIp varchar(50) = null
AS
	Select Sum(Length) From DB_FILE Where OwnerIp=@OwnerIp;
	Return 0;


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
















CREATE PROCEDURE [ET_FILE_SETTAG]
    @FileId int = 0,
    @AttributeId int = 0,
	@OwnerId int = 0,
    @TagName varchar(255) = null
AS
	--	获得用户和扩展属性
	Declare @FileName varchar(255)
	Declare @ClassName varchar(255)
	Declare @AttributeName varchar(255)
	Declare @OwnerName varchar(255)
	Declare @ClassId int
	Declare @TagId int
	Set @FileName = (Select Name From DB_FILE Where FileId=@FileId);
	Set @AttributeName = (Select Name From DB_ATTRIBUTE Where AttributeId=@AttributeId);
	Set @ClassId = (Select ClassId From DB_FILE Where FileId=@FileId);
	Set @ClassName = (Select Name From DB_CLASS Where ClassId=@ClassId);
	--	获取用户名
	If @OwnerId>0
		Set @OwnerName = (Select Username From DB_USER Where UserId=@OwnerId);
	Else
		Set @OwnerName = '匿名访客';
	--	如果标签不存在则创建
	If (Select Count(*) From DB_ATTRIBUTE_TAG Where Name=@TagName And AttributeId=@AttributeId)<=0
		Exec ET_ATTRIBUTE_ADDTAG @AttributeId, @TagName, NULL, 0
	Set @TagId = (Select TagId From DB_ATTRIBUTE_TAG Where Name=@TagName And AttributeId=@AttributeId);
	If @TagId>0 Begin
		Declare @TagCount int
		Set @TagCount = (Select Count(*) From DB_FILE_TAG Where TagId=@TagId);
		If @TagCount is not null And @TagCount>10000
			Update DB_ATTRIBUTE_TAG Set Vouch=3 Where TagId=@TagId;
		Else If @TagCount is not null and @TagCount>1000
			Update DB_ATTRIBUTE_TAG Set Vouch=2 Where TagId=@TagId;
		Else If @TagCount is not null and @TagCount>100
			Update DB_ATTRIBUTE_TAG Set Vouch=1 Where TagId=@TagId;
		Else
			Update DB_ATTRIBUTE_TAG Set Vouch=0 Where TagId<0;
	End
	--	如果标签创建成功则插入
	If @TagId>0 And (Select Count(*) From DB_FILE_TAG Where FileId=@FileId And TagId=@TagId)<=0
		Insert Into DB_FILE_TAG (FileId, FileName, ClassId, ClassName, AttributeId, AttributeName, TagId, TagName, OwnerId, OwnerName) Values(@FileId, @FileName, @ClassId, @ClassName, @AttributeId, @AttributeName, @TagId, @TagName, @OwnerId, @OwnerName);
	Return 0;






GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

















CREATE PROCEDURE [ET_FILE_SETSPECIAL]
    @FileId int = 0,
    @Special int = 0,
	@OwnerId int = 0,
    @TagName varchar(255) = null
AS
	--	获得用户和扩展属性
	Declare @FileName varchar(255)
	Declare @ClassName varchar(255)
	Declare @AttributeName varchar(255)
	Declare @OwnerName varchar(255)
	Declare @AttributeId int 
	Declare @ClassId int
	Declare @TagId int
	Set @FileName = (Select Name From DB_FILE Where FileId=@FileId);
	Set @ClassId = (Select ClassId From DB_FILE Where FileId=@FileId);
	Set @ClassName = (Select Name From DB_CLASS Where ClassId=@ClassId);
	If (Select Count(*) From DB_ATTRIBUTE Where Special=@Special)>0 Begin
		Set @AttributeId = (Select AttributeId From DB_ATTRIBUTE Where Special=@Special);
		Set @AttributeName = (Select Name From DB_ATTRIBUTE Where AttributeId=@AttributeId);
		--	获取用户名
		If @OwnerId>0
			Set @OwnerName = (Select Username From DB_USER Where UserId=@OwnerId);
		Else
			Set @OwnerName = '匿名访客';
		--	如果标签不存在则创建
		If (Select Count(*) From DB_ATTRIBUTE_TAG Where Name=@TagName And AttributeId=@AttributeId)<=0
			Exec ET_ATTRIBUTE_ADDTAG @AttributeId, @TagName, NULL, 0
		Set @TagId = (Select TagId From DB_ATTRIBUTE_TAG Where Name=@TagName And AttributeId=@AttributeId);
		If @TagId<=0
			Set @TagId = (Select TagId From DB_ATTRIBUTE_TAG Where Near='%' + @TagName + '%' And AttributeId=@AttributeId);	
		--	如果标签创建成功则插入
		If @TagId>0 And (Select Count(*) From DB_FILE_TAG Where FileId=@FileId And TagId=@TagId)<=0
			Insert Into DB_FILE_TAG (FileId, FileName, ClassId, ClassName, AttributeId, AttributeName, TagId, TagName, OwnerId, OwnerName) Values(@FileId, @FileName, @ClassId, @ClassName, @AttributeId, @AttributeName, @TagId, @TagName, @OwnerId, @OwnerName);
		Return 0;
	End
	Else
		Return 1;




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO











CREATE PROCEDURE [ET_FOLDER_DETAIL]
    @FolderId int = 0
AS
	Declare @StorageId int
	Declare @Length bigint
	Set @StorageId = (Select StorageId From DB_FOLDER Where FolderId=@FolderId);
	Exec ET_RESOURCE_GETLENGTH @FolderId, @StorageId, @Length output
	Select *
	,(Select Name From DB_STORAGE Where StorageId=DB_FOLDER.StorageId) As StorageName
	,(Select Name From DB_FOLDER F Where F.FolderId=DB_FOLDER.ParentId) As ParentName
	,(Select Count(*) From DB_FOLDER F Where F.ParentId=DB_FOLDER.FolderId) As Folders
	,(Select Count(*) From DB_FILE Where FolderId=DB_FOLDER.FolderId) As Files
	,@Length As Used
	From DB_FOLDER Where FolderId=@FolderId;
	Return 0;











GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
































CREATE PROCEDURE [ET_RESOURCE_CANADD]
    @Name varchar(255) = null,
	@OwnerId int = 0,
	@StorageId int = 0,
	@FolderId int = 0,
	@ResourceType int = 0,
	@Extension varchar(50) = null,
	@Result int = 0 output
AS
	Declare @PopemCreate int
	Declare @PopemWrite int
	Declare @PopemEdit int
	Declare @PopemRead int
	Declare @PopemDownload int
	If @ResourceType = 3 Begin
		If @Name Is NULL Or @Name='' Begin
			Set @Result = 6;
		End
		Else
			If (Select Count(*) From DB_FILE Where Name=@Name And FolderId=@FolderId And StorageId=@StorageId)>0 Begin
				Set @Result = 5;
			End
			Else Begin
				If (Select Count(*) From DB_CLASS_EXTENSION Where Extension=@Extension)>0 Begin
					--	判断存储设备是否存在
					If (Select Count(*) From DB_STORAGE Where StorageId=@StorageId)>0 Begin
						--	判断上级路径是否存在
						If (Select Count(*) From DB_FOLDER Where FolderId=@FolderId And StorageId=@StorageId)>0 Or @FolderId=0 Begin
							--	判断用户是否有相应权限
							Exec ET_RESOURCE_GETPOPEM @OwnerId, @StorageId, @FolderId, @PopemCreate output, @PopemWrite output, @PopemEdit output, @PopemRead output, @PopemDownload output;
							If @PopemWrite>0 Begin
								Set @Result = 0;
							End
							Else Begin
								Set @Result = 1;
							End
						End
						Else Begin
							Set @Result = 2;
						End
					End
					Else Begin
						Set @Result = 3;
					End
				End
				Else Begin
					Set @Result = 4;
				End
			End
	End
	Else Begin
		If @Name='' Begin
			Set @Result = 5;
		End
		Else
			If (Select Count(*) From DB_FOLDER Where Name=@Name And ParentId=@FolderId And StorageId=@StorageId)>0 Begin
				Set @Result = 4;
			End
			Else Begin
				--	判断存储设备是否存在
				If (Select Count(*) From DB_STORAGE Where StorageId=@StorageId)>0 Begin
					--	判断上级路径是否存在
					If (Select Count(*) From DB_FOLDER Where FolderId=@FolderId And StorageId=@StorageId)>0 Or @FolderId=0 Begin
						--	判断用户是否有相应权限
						Exec ET_RESOURCE_GETPOPEM @OwnerId, @StorageId, @FolderId, @PopemCreate output, @PopemWrite output, @PopemEdit output, @PopemRead output, @PopemDownload output;
						If @PopemCreate>0 Begin
							Set @Result = 0;
						End
						Else Begin
							Set @Result = 1;
						End
					End
					Else Begin
						Set @Result = 2;
					End
				End
				Else Begin
					Set @Result = 3;
				End
			End
	End

























GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


















CREATE PROCEDURE [ET_RESOURCE_POPEMINIT]
	@ResourceId int = 0,
	@ResourceType int = 0,
	@StorageId int = 0,
	@ParentId int = 0
AS
	--	添加上级设备权限
	Declare @ResourceName varchar(255)
	Set @ResourceName = Case 
		When @ResourceType=1 Then (Select Name From DB_STORAGE Where StorageId=@ResourceId)
		When @ResourceType=2 Then (Select Name From DB_FOLDER Where FolderId=@ResourceId)
		Else (Select Name From DB_FILE Where FileId=@ResourceId)
	End 
	If @ParentId>0
		Insert Into DB_POPEM (OwnerId, OwnerType, OwnerName, ResourceId, ResourceType, ResourceName, PopemCreate, PopemWrite, PopemEdit, PopemRead, PopemDownload, Lock) Select OwnerId, OwnerType, OwnerName, @ResourceId, @ResourceType, @ResourceName, PopemCreate, PopemWrite, PopemEdit, PopemRead, PopemDownload, Lock From DB_POPEM Where ResourceId=@ParentId And ResourceType=2;
	Else
		Insert Into DB_POPEM (OwnerId, OwnerType, OwnerName, ResourceId, ResourceType, ResourceName, PopemCreate, PopemWrite, PopemEdit, PopemRead, PopemDownload, Lock) Select OwnerId, OwnerType, OwnerName, @ResourceId, @ResourceType, @ResourceName, PopemCreate, PopemWrite, PopemEdit, PopemRead, PopemDownload, Lock From DB_POPEM Where ResourceId=@StorageId And ResourceType=1;
	--	添加超级管理员
	Declare @AdministratorId int 
	Set @AdministratorId = (Select LevelId From DB_LEVEL Where Type = 2);
	If @AdministratorId>0 And (Select Count(*) From DB_POPEM Where ResourceId=@ResourceId And ResourceType=@ResourceType And OwnerId=@AdministratorId And OwnerType=2)<=0
		Exec ET_POPEM_SET @AdministratorId, 2, @ResourceId, @ResourceType, 1,1,1,1,1,1
	--	添加创建者自己
	Declare @SelfId int 
	If @ResourceType = 1
		Set @SelfId = (Select OwnerId From DB_STORAGE Where StorageId=@ResourceId);
	Else If @ResourceType = 2
		Set @SelfId = (Select OwnerId From DB_FOLDER Where FolderId=@ResourceId);
	Else
		Set @SelfId = (Select OwnerId From DB_FILE Where FileId=@ResourceId);
	If @SelfId is not null And @SelfId>0
		Exec ET_POPEM_SET @SelfId, 3, @ResourceId, @ResourceType, 1,1,1,1,1,1
	--	上级存储设备
	Declare @OwnerId int 
	If @StorageId>0 Begin
		Set @OwnerId = (Select OwnerId From DB_STORAGE Where StorageId=@StorageId);
		If @OwnerId>0 And (Select Count(*) From DB_POPEM Where ResourceId=@ResourceId And ResourceType=@ResourceType And OwnerId=@OwnerId And OwnerType=3)<=0
			Exec ET_POPEM_SET @OwnerId, 3, @ResourceId, @ResourceType, 1,1,1,1,1,1
	End
	--	上级文件包
	While @ParentId>0 Begin
		Set @OwnerId = (Select OwnerId From DB_FOLDER Where FolderId=@ParentId);
		If @OwnerId>0 And (Select Count(*) From DB_POPEM Where ResourceId=@ResourceId And ResourceType=@ResourceType And OwnerId=@OwnerId And OwnerType=3)<=0
			Exec ET_POPEM_SET @OwnerId, 3, @ResourceId, @ResourceType, 1,1,1,1,1,1
		Set @ParentId = (Select ParentId From DB_FOLDER Where FolderId=@ParentId);
	End
	--	匿名访客
	If @StorageId=0 And @ParentId=0 Begin
		Declare @GuestId int
		Set @GuestId = (Select LevelId From DB_LEVEL Where Type=1);
		If @GuestId is Null Set @GuestId = 0;
		If (Select Count(*) From DB_POPEM Where ResourceId=@ResourceId And ResourceType=@ResourceType And OwnerId=@GuestId And OwnerType=2)<=0
			Exec ET_POPEM_SET @GuestId, 2, @ResourceId, @ResourceType, 0,0,0,1,0,1
	End




GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



























CREATE PROCEDURE [ET_FOLDER_ADD]
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
	@OwnerId int = 0,
	@OwnerIP varchar(50) = null,
	@StorageId int = 0,
	@ParentId int = 0,
	@State int = 0
AS
	Declare @Result int
	Exec ET_RESOURCE_CANADD @Name, @OwnerId, @StorageId, @ParentId, 2, NULL, @Result output
	If @Result = 0 Begin
		Declare @DisplayPath varchar(255);
		exec ET_RESOURCE_GETPATH @StorageId, @ParentId, @DisplayPath output;
		--	获取用户名
		Declare @OwnerName varchar(255);
		If @OwnerId>0
			Set @OwnerName = (Select Username From DB_USER Where UserId=@OwnerId);
		Else
			Set @OwnerName = '匿名访客';
		--	获取摘要
		Declare @Summary varchar(3000);
		--	避免 Summary 空值
		If @Name is null Or @Name = ''
			Set @Name = '';
		If @Remark is null Or @Remark = ''
			Set @Remark = '';
		Set @Summary = @Name + '_' + @Remark;
		--	插入新存储设备
		Insert Into DB_FOLDER (Name, Remark, OwnerId, OwnerName, OwnerIP, StorageId, ParentId, DisplayPath, Summary, State) Values(@Name, @Remark, @OwnerId, @OwnerName, @OwnerIP, @StorageId, @ParentId, @DisplayPath, @Summary, @State);
		--	更新存储设备
		Update DB_STORAGE Set Last=GetDate() Where StorageId=@StorageId;
		--	更新上级路径
		If @ParentId>0 Begin
			Declare @iParentId int
			Set @iParentId = @ParentId;
			while @iParentId>0 Begin
				Update DB_FOLDER Set Last=GetDate() Where FolderId=@iParentId;
				Set @iParentId = (Select ParentId From DB_FOLDER Where FolderId=@iParentId);
			End
		End
		--	初始化权限项目
		Declare @FolderId int;
		Set @FolderId = (Select FolderId From DB_FOLDER Where Name=@Name And StorageId=@StorageId And ParentId=@ParentId);
		If @FolderId>0 Begin
			Exec ET_RESOURCE_POPEMINIT @FolderId, 2, @StorageId, @ParentId
		End
		Return 0;
	End
	Else
		Return @Result;



GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO































CREATE PROCEDURE [ET_FILE_ADD]
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
	@OwnerId int = 0,
	@OwnerIP varchar(50) = null,
	@StorageId int = 0,
	@FolderId int = 0,
	@Source int = 0,
	@Extension varchar(50) = null,
	@Length int = 0,
	@Thumb varchar(255) = null,
	@Preview varchar(255) = null,
	@Filename varchar(255) = null,
	@Topic int = 0,
	@Vote int = 0,
	@State int = 0,
	@First DateTime
AS
	Declare @Result int
	Declare @ClassId int;
	Set @ClassId = (Select ClassId From DB_CLASS_EXTENSION Where Extension=@Extension);	
	Exec ET_RESOURCE_CANADD @Name, @OwnerId, @StorageId, @FolderId, 3, @Extension, @Result output
	If @Result = 0 Begin
		--	获取路径
		Declare @DisplayPath varchar(255);
		exec ET_RESOURCE_GETPATH @StorageId, @FolderId, @DisplayPath output;
		--	获取用户名
		Declare @OwnerName varchar(255);
		If @OwnerId>0
			Set @OwnerName = (Select Username From DB_USER Where UserId=@OwnerId);
		Else
			Set @OwnerName = '匿名访客';
		--	获取摘要
		Declare @Summary varchar(5000);
		--	避免 Summary 空值
		If @Name is null Or rtrim(ltrim(@Name)) = ''
			Set @Name = '';
		If @Remark is null Or @Remark = ''
			Set @Remark = '';
		Set @Summary = @Name + '_' + @Remark;
		--	获取类型分类
		Declare @Type varchar(255);
		Set @Type = (Select Name From DB_CLASS_EXTENSION Where Extension=@Extension);				
		--	插入新文件
		Insert Into DB_FILE (Name, Remark, OwnerId, OwnerName, OwnerIP, ClassId, StorageId, FolderId, DisplayPath, Source, Type, Extension, Length, Thumb, Preview, Filename, Summary, Topic, Vote, State, First) Values(rtrim(ltrim(@Name)), @Remark, @OwnerId, @OwnerName, @OwnerIP, @ClassId, @StorageId, @FolderId, @DisplayPath, @Source, @Type, @Extension, @Length, @Thumb, @Preview, @Filename, @Summary, @Topic, @Vote, @State, @First);
		--	更新存储设备
		Update DB_STORAGE Set Used=Used+@Length, Last=GetDate() Where StorageId=@StorageId;
		--	更新用户信息
		Update DB_USER Set Used=Used+@Length Where UserId=@OwnerId;
		--	更新上级路径
		If @FolderId>0 Begin
			Declare @iParentId int
			Set @iParentId = @FolderId;
			while @iParentId>0 Begin
				Update DB_FOLDER Set Last=GetDate() Where FolderId=@iParentId;
				Set @iParentId = (Select ParentId From DB_FOLDER Where FolderId=@iParentId);
			End
		End
		--	获取刚才创建的文件编号
		Declare @FileId int;
		Set @FileId = (Select FileId From DB_FILE Where Filename=@Filename And StorageId=@StorageId And FolderId=@FolderId);
		If @FileId>0 Begin
			Exec ET_RESOURCE_POPEMINIT @FileId, 3, @StorageId, @FolderId
		End
		Return 0;
	End
	Else
		Return @Result;







GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





















CREATE PROCEDURE [ET_STORAGE_ADD]
    @Name varchar(255) = null,
    @Remark varchar(255) = null,
	@OwnerId int = 0,
	@Path varchar(255) = null,
	@Capacity bigint = 0
AS
	If @Name Is NULL Or rtrim(ltrim(@Name))=''
		Return 3;
	Else
		IF (Select Count(*) From DB_STORAGE Where Name=rtrim(ltrim(@Name)) Or Path=@Path)>0 
			Return 2;
		Else Begin
			--	获取用户名
			Declare @OwnerName varchar(255);
			If (Select Count(*) From DB_USER Where UserId=@OwnerId)>0 Begin
				Set @OwnerName = (Select Username From DB_USER Where UserId=@OwnerId);
				--	插入新存储设备
				Insert Into DB_STORAGE (Name, Remark, OwnerId, OwnerName, [Path], Capacity) Values(rtrim(ltrim(@Name)), @Remark, @OwnerId, @OwnerName, @Path, @Capacity);
				--	更新总空间
				Update DB_LEVEL Set Capacity=(Select Sum(Capacity) From DB_STORAGE) Where Type=2;
				Update DB_USER Set Capacity=(Select Sum(Capacity) From DB_STORAGE) Where Exists(Select * From DB_LEVEL Where LevelId=DB_USER.LevelId And Type=2) Or Capacity>(Select Sum(Capacity) From DB_STORAGE);
				--	插入默认权限
				Declare @StorageId int
				Set @StorageId = (Select StorageId From DB_STORAGE Where Name=rtrim(ltrim(@Name)));
				If @StorageId>0 Begin
					Exec ET_RESOURCE_POPEMINIT @StorageId, 1, 0, 0
				End
				Return 0;
			End
			Else
				Return 1;
		End


















