--增加了webState=0状态运行的站点
if exists (select * from dbo.sysobjects where id = object_id(N'GetWebInfoForNewsStat') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetWebInfoForNewsStat
GO
CREATE PROCEDURE GetWebInfoForNewsStat
(
	@statType int
)
AS
	SET NOCOUNT ON;
	
SELECT * FROM WebInfo
WHERE applyID not in (SELECT DISTINCT applyID FROM NewsStat WHERE type=@statType) and webState=0


GO

--增加了webState=1状态运行的站点
if exists (select * from dbo.sysobjects where id = object_id(N'GetNewsStatByLastStatTime') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetNewsStatByLastStatTime
GO
CREATE PROCEDURE GetNewsStatByLastStatTime
(
	@statType int
)
AS
	SET NOCOUNT ON;
	
	SELECT a.statTime as statTime,a.applyID as applyID ,b.schoolName as schoolName,b.dbIP as dbIP, b.dbName as dbName, b.dbPwd as dbPwd, b.dbUser as dbUser FROM 
	(SELECT Max(statTime) as statTime,applyID  FROM NewsStat
			WHERE (type = @statType) GROUP BY applyID) a, WebInfo b
	WHERE (((Year(GetDate()) - Year(a.statTime)) * 12 + Month(GetDate()) - Month(a.statTime)) > 1) AND a.applyID=b.applyID and b.webState=0

GO



if exists (select * from dbo.sysobjects where id = object_id(N'InsertWebInfo') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertWebInfo
GO
CREATE PROCEDURE InsertWebInfo
(
	@pkID int=0,
	@applyID int,
	@schoolName varchar(100),
	@serialNO varchar(100),
	@domainName varchar(100),
	@IP varchar(100),
	@port int,
	@webSpace int,
	@destinationPath varchar(100),
	@regTime datetime,
	@operateIP varchar(100),
	@webState int,
	@remark varchar(200),
	@sysIP varchar(100),
	@sysUser varchar(100),
	@sysPwd varchar(100),
	@dbIP varchar(100),
	@dbUser varchar(100),
	@dbPwd varchar(100),
	@dbName varchar(100),
	@usedSpace int,
	@spaceStateTime datetime,
	@dbAdmin varchar(100),
	@dbAdminPwd	varchar(100),
	@usedImageSpace float,
	@usedBackupSpace float,
	@dbId int,
	@webId int,
	@follow int,
	@versionId varchar(100),
	@apiUpdateTime datetime,
	@updateState varchar(100)
)
AS
	SET NOCOUNT OFF;
INSERT INTO WebInfo(applyID, schoolName, serialNO, domainName, IP, port, webSpace, destinationPath, regTime, operateIP, webState, remark, sysIP, sysUser, sysPwd, dbIP, dbUser, dbPwd, dbName,usedSpace,spaceStateTime,dbAdmin,dbAdminPwd,usedImageSpace,usedBackupSpace,dbId,webId,follow,versionId,apiUpdateTime,updateState) VALUES (@applyID, @schoolName, @serialNO, @domainName, @IP, @port, @webSpace, @destinationPath, @regTime, @operateIP, @webState, @remark, @sysIP, @sysUser, @sysPwd, @dbIP, @dbUser, @dbPwd, @dbName, @usedSpace, @spaceStateTime,@dbAdmin,@dbAdminPwd,@usedImageSpace,@usedBackupSpace,@dbId,@webId,@follow,@versionId,@apiUpdateTime,@updateState)


GO


if exists (select * from dbo.sysobjects where id = object_id(N'UpdateWebInfo') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateWebInfo
GO
CREATE PROCEDURE UpdateWebInfo
(
	@applyID int,
	@schoolName varchar(100),
	@serialNO varchar(100),
	@domainName varchar(100),
	@IP varchar(100),
	@port int,
	@webSpace int,
	@destinationPath varchar(100),
	@regTime datetime,
	@operateIP varchar(100),
	@webState int,
	@remark varchar(200),
	@sysIP varchar(100),
	@sysUser varchar(100),
	@sysPwd varchar(100),
	@dbIP varchar(100),
	@dbUser varchar(100),
	@dbPwd varchar(100),
	@dbName varchar(100),
	@pkID int,
	@usedSpace int,
	@spaceStateTime datetime,
	@dbAdmin varchar(100),
	@dbAdminPwd varchar(100),
	@usedImageSpace float,
	@usedBackupSpace float,
	@dbId int,
	@webId int,
	@follow int,
	@versionId varchar(100),
	@apiUpdateTime datetime,
	@updateState varchar(100)
)
AS
	SET NOCOUNT OFF;
UPDATE WebInfo SET applyID = @applyID, schoolName = @schoolName, serialNO = @serialNO, domainName = @domainName, IP = @IP, port = @port, webSpace = @webSpace, destinationPath = @destinationPath, regTime = @regTime, operateIP = @operateIP, webState = @webState, remark = @remark, sysIP = @sysIP, sysUser = @sysUser, sysPwd = @sysPwd, dbIP = @dbIP, dbUser = @dbUser, dbPwd = @dbPwd, dbName = @dbName, usedSpace=@usedSpace, spaceStateTime=@spaceStateTime,dbAdmin=@dbAdmin,dbAdminPwd=@dbAdminPwd 
,usedImageSpace=@usedImageSpace
,usedBackupSpace=@usedBackupSpace
,dbId=@dbId
,webId=@webId
,follow=@follow
,versionId=@versionId
,apiUpdateTime=@apiUpdateTime
,updateState=@updateState
WHERE (pkID = @pkID)


GO


--增加端口参数
if exists (select * from dbo.sysobjects where id = object_id(N'GetApplyInfoByDomainName') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetApplyInfoByDomainName
GO
CREATE PROCEDURE GetApplyInfoByDomainName
	@domainName varchar(100),
	@port int
AS
	SELECT * FROM WebInfo WHERE domainName = @domainName and port = @port

GO

--新注册默认不加关注。
if exists (select * from dbo.sysobjects where id = object_id(N'AddRegister') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure AddRegister
GO
CREATE PROCEDURE AddRegister
(	
	@pkId int = 0 out,
	@schoolName varchar(100),
	@serialNO varchar(100),
	@domainName varchar(100),
	@linkman varchar(100),
	@phone varchar(100),
	@eMail varchar(100),
	@QQ varchar(100),
	@MSN varchar(100),
	@mobile varchar(100),
	@applyTime datetime,
	@applyState int,
	@remark varchar(200),
	@validParm varchar(200),
	@applyID int,
	@IP varchar(100),
	@port int,
	@webSpace int,
	@destinationPath varchar(100),
	@regTime datetime,
	@operateIP varchar(100),
	@webState int,
	@sysIP varchar(100),
	@sysUser varchar(100),
	@sysPwd varchar(100),
	@dbIP varchar(100),
	@dbUser varchar(100),
	@dbPwd varchar(100),
	@dbName varchar(100),	
	@usedSpace int,
	@spaceStateTime datetime,
	@dbAdmin varchar(100),
	@dbAdminPwd varchar(100),
	@usedImageSpace float,
	@usedBackupSpace float,
	@dbId int,
	@webId int,
	@follow int,
	@versionId varchar(100),
    @apiUpdateTime datetime,
    @updateState varchar(100)
)
AS	
	SET NOCOUNT OFF;
	INSERT INTO ApplyInfo(schoolName, serialNO, domainName, linkman, phone, eMail, QQ, MSN, mobile, applyTime, applyState, 
remark, validParm) VALUES (@schoolName, @serialNO, @domainName, @linkman, @phone, @eMail, @QQ, @MSN, @mobile, @applyTime, 
@applyState, @remark, @validParm)
	SET @applyID = @@IDENTITY
	SET @pkID = @@IDENTITY
	INSERT INTO WebInfo(applyID, schoolName, serialNO, domainName, IP, port, webSpace, destinationPath, regTime, operateIP, 
webState, remark, sysIP, sysUser, sysPwd, dbIP, dbUser, dbPwd, dbName, usedSpace, spaceStateTime,dbAdmin,dbAdminPwd,usedImageSpace,usedBackupSpace,dbId,webId,follow,versionId,apiUpdateTime,updateState) 
VALUES (@applyID, @schoolName, @serialNO, @domainName,
@IP, @port, @webSpace, @destinationPath, @regTime, @operateIP, @webState, @remark, @sysIP, @sysUser, @sysPwd, @dbIP, @dbUser,
@dbPwd, @dbName, @usedSpace, @spaceStateTime,@dbAdmin,@dbAdminPwd,@usedImageSpace,@usedBackupSpace,@dbId,@webId,'0',@versionId,@apiUpdateTime,@updateState)


GO


--新注册用户
if exists (select * from dbo.sysobjects where id = object_id(N'InsertAdmin') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure InsertAdmin
GO
CREATE PROCEDURE InsertAdmin
(
	@pkId int,
	@adminName varchar(100),
	@fullName varchar(100),
	@password varchar(100),
	@allowAllAssign int,
	@allowEditOthersAdmin int,
	@isEqualAdmin int,
	@allowLogin int,
	@isSbwUser int
)
AS
	SET NOCOUNT OFF;
INSERT INTO Admin(adminName, fullName, password, allowAllAssign, allowEditOthersAdmin, isEqualAdmin,allowLogin,isSbwUser) VALUES (@adminName, @fullName, @password, @allowAllAssign, @allowEditOthersAdmin, @isEqualAdmin,@allowLogin,@isSbwUser)

GO

--子站报表调用的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'StatNewsByStatType') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure StatNewsByStatType
GO
CREATE PROCEDURE StatNewsByStatType
(
	@statType int,		-- 统计类型
	@lastStatTime datetime	-- 上次统计时间，处理后传过来
)
AS
	SET NOCOUNT ON
	/*
	@lastStatTime 为开始统计时间，具体到每月1号
	查询范围为：大于开始时间，小于服务器当前时间1号
	*/
	
	DECLARE @nowTime varchar(10)	-- 设置当前时间
	SET @nowTime = RTrim(Year(GetDate())) + '-' + RTrim(Month(GetDate())) + '-01'
	
	IF(@statType=1)			-- 月报
	BEGIN
		SELECT Count(*) AS newsCount, Max(updateTime) as lastUpdateTime 
		FROM News 
			WHERE (updateTime >= @lastStatTime) and (updateTime < @nowTime)
			GROUP BY Year(updateTime),Month(updateTime)
			ORDER BY lastUpdateTime DESC
	END
	ELSE IF(@statType=2)	-- 年报
	BEGIN
		DECLARE @years int, @tempTime varchar(10)
		--SET @years = Year(@nowTime) - Year(@lastStatTime)
		--IF (@years > 0)
		--BEGIN
			--SET	@years = Year(@lastStatTime) + @years
			--SET @tempTime = Convert(varchar(4),@years) + '-12-31'
		--END
		SET @tempTime = GetDate()
		SELECT Count(*) AS newsCount, Max(updateTime) as lastUpdateTime 
		FROM News 
		WHERE (updateTime >= @lastStatTime) and (updateTime < @tempTime) --and (year(@lastStatTime)<>RTrim(Year(GetDate()))) 
		GROUP BY Year(updateTime)
		ORDER BY lastUpdateTime DESC		
	END	
	-- 周报 @statType=0
	RETURN


GO

