03.01.00.sqldataprovider

完整的商业模板和强大的后台管理功能

       M.PortalID,
       TM.TabModuleId,
       TM.TabId,
       TM.PaneName,
       TM.ModuleOrder,
       TM.CacheTime,
       TM.Alignment,
       TM.Color,
       TM.Border,
       TM.Visibility,
       TM.ContainerSrc,
       TM.DisplayTitle,
       TM.DisplayPrint,
       TM.DisplaySyndicate,
       'IconFile' = case when F.FileName is null then TM.IconFile else F.Folder + F.FileName end,
       DM.*,
       MC.ModuleControlId,
       MC.ControlSrc,
       MC.ControlType,
       MC.ControlTitle,
       MC.HelpURL
from {objectQualifier}Modules M
inner join {objectQualifier}TabModules TM on M.ModuleId = TM.ModuleId
inner join {objectQualifier}Tabs T on TM.TabId = T.TabId
inner join {objectQualifier}ModuleDefinitions MD on M.ModuleDefId = MD.ModuleDefId
inner join {objectQualifier}DesktopModules DM on MD.DesktopModuleId = DM.DesktopModuleId
inner join {objectQualifier}ModuleControls MC on MD.ModuleDefId = MC.ModuleDefId
left outer join {objectQualifier}Files F on TM.IconFile = 'fileid=' + convert(varchar,F.FileID)
where  M.IsDeleted = 0  
	and T.IsDeleted = 0  
	and ControlKey is null 
	and DM.IsAdmin = 0
	and (DM.SupportedFeatures & 2 = 2)
	and (T.EndDate < GETDATE() or T.EndDate IS NULL) 
	and (T.StartDate > GETDATE() or T.StartDate IS NULL) 
	and (M.StartDate > GETDATE() or M.StartDate IS NULL) 
	and (M.EndDate < GETDATE() or M.EndDate IS NULL) 
	and (NOT (DM.BusinessControllerClass IS NULL))
	and (T.PortalID = @PortalID OR (T.PortalID IS NULL AND @PortalID Is NULL))
order by TM.ModuleOrder

GO

insert into {objectQualifier}HostSettings (
  SettingName,
  SettingValue,
  SettingIsSecure
)
values (
  'ModuleCaching',
  'M',
  0
)

GO

/* -- remove orphan modules: not deleted but without any tabmodule instance -- */
DELETE {databaseOwner}{objectQualifier}modules
FROM {databaseOwner}{objectQualifier}modules
LEFT OUTER JOIN {databaseOwner}{objectQualifier}TabModules ON 
	{databaseOwner}{objectQualifier}Modules.ModuleID = {databaseOwner}{objectQualifier}TabModules.ModuleID
WHERE ({databaseOwner}{objectQualifier}Modules.PortalID IS NOT NULL AND 
	{databaseOwner}{objectQualifier}Modules.IsDeleted = 0 AND 
	{databaseOwner}{objectQualifier}TabModules.TabModuleID IS NULL)

GO


ALTER TABLE {databaseOwner}{objectQualifier}Schedule
	ADD [Servers] nvarchar(150) NULL
	
GO

ALTER TABLE {databaseOwner}{objectQualifier}ScheduleHistory
	ADD [Server] nvarchar(150) NULL

GO
DROP PROCEDURE {databaseOwner}{objectQualifier}GetSchedule
GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSchedule
	@Server varchar(150)
AS
SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart, S.Servers
FROM {objectQualifier}Schedule S
LEFT JOIN {objectQualifier}ScheduleHistory SH
ON S.ScheduleID = SH.ScheduleID
WHERE (SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID FROM {objectQualifier}ScheduleHistory S1 WHERE S1.ScheduleID = S.ScheduleID ORDER BY S1.NextStart DESC)
OR  SH.ScheduleHistoryID IS NULL)
AND (@Server IS NULL or S.Servers LIKE ',%' + @Server + '%,' or S.Servers IS NULL)
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart, S.Servers

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetScheduleByEvent

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleByEvent
@EventName varchar(50),
@Server varchar(150)
AS
SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled
FROM {databaseOwner}{objectQualifier}Schedule S
WHERE S.AttachToEvent = @EventName
AND (S.Servers LIKE ',%' + @Server + '%,' or S.Servers IS NULL)
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateSchedule

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSchedule
@ScheduleID int
,@TypeFullName varchar(200)
,@TimeLapse int
,@TimeLapseMeasurement varchar(2)
,@RetryTimeLapse int
,@RetryTimeLapseMeasurement varchar(2)
,@RetainHistoryNum int
,@AttachToEvent varchar(50)
,@CatchUpEnabled bit
,@Enabled bit
,@ObjectDependencies varchar(300)
,@Servers varchar(150)
AS
UPDATE {databaseOwner}{objectQualifier}Schedule
SET TypeFullName = @TypeFullName
,TimeLapse = @TimeLapse
,TimeLapseMeasurement = @TimeLapseMeasurement
,RetryTimeLapse = @RetryTimeLapse
,RetryTimeLapseMeasurement = @RetryTimeLapseMeasurement
,RetainHistoryNum = @RetainHistoryNum
,AttachToEvent = @AttachToEvent
,CatchUpEnabled = @CatchUpEnabled
,Enabled = @Enabled
,ObjectDependencies = @ObjectDependencies
,Servers = @Servers
WHERE ScheduleID = @ScheduleID

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}AddSchedule

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddSchedule
@TypeFullName varchar(200)
,@TimeLapse int
,@TimeLapseMeasurement varchar(2)
,@RetryTimeLapse int
,@RetryTimeLapseMeasurement varchar(2)
,@RetainHistoryNum int
,@AttachToEvent varchar(50)
,@CatchUpEnabled bit
,@Enabled bit
,@ObjectDependencies varchar(300)
,@Servers varchar(150)
AS
INSERT INTO {objectQualifier}Schedule
(TypeFullName
,TimeLapse
,TimeLapseMeasurement
,RetryTimeLapse
,RetryTimeLapseMeasurement
,RetainHistoryNum
,AttachToEvent
,CatchUpEnabled
,Enabled
,ObjectDependencies
,Servers
)
VALUES
(@TypeFullName
,@TimeLapse
,@TimeLapseMeasurement
,@RetryTimeLapse
,@RetryTimeLapseMeasurement
,@RetainHistoryNum
,@AttachToEvent
,@CatchUpEnabled
,@Enabled
,@ObjectDependencies
,@Servers
)


select SCOPE_IDENTITY()

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetScheduleNextTask

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleNextTask
	@Server varchar(150)
AS
SELECT TOP 1 S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart
FROM {objectQualifier}Schedule S
LEFT JOIN {objectQualifier}ScheduleHistory SH
ON S.ScheduleID = SH.ScheduleID
WHERE ((SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID FROM {objectQualifier}ScheduleHistory S1 WHERE S1.ScheduleID = S.ScheduleID ORDER BY S1.NextStart DESC)
OR  SH.ScheduleHistoryID IS NULL) AND S.Enabled = 1)
AND (S.Servers LIKE ',%' + @Server + '%,' or S.Servers IS NULL)
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart
ORDER BY SH.NextStart ASC

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetScheduleByTypeFullName

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleByTypeFullName
@TypeFullName varchar(200)
AS
SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, S.Servers
FROM {objectQualifier}Schedule S
WHERE S.TypeFullName = @TypeFullName
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, S.Servers
GO


DROP PROCEDURE {databaseOwner}{objectQualifier}AddScheduleHistory
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddScheduleHistory
@ScheduleID int,
@StartDate datetime,
@Server varchar(150)
AS
INSERT INTO {databaseOwner}{objectQualifier}ScheduleHistory
(ScheduleID,
StartDate,
Server)
VALUES
(@ScheduleID,
@StartDate,
@Server)

select SCOPE_IDENTITY()
GO


DROP PROCEDURE {databaseOwner}{objectQualifier}GetScheduleHistory
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleHistory
@ScheduleID int
AS
SELECT S.ScheduleID, S.TypeFullName, SH.StartDate, SH.EndDate, SH.Succeeded, SH.LogNotes, SH.NextStart, SH.Server
FROM {databaseOwner}{objectQualifier}Schedule S
INNER JOIN {databaseOwner}{objectQualifier}ScheduleHistory SH
ON S.ScheduleID = SH.ScheduleID
WHERE S.ScheduleID = @ScheduleID or @ScheduleID = -1
GO



CREATE TABLE {databaseOwner}{objectQualifier}EventLog (
	[LogGUID] [varchar] (36) NOT NULL ,
	[LogTypeKey] [nvarchar] (35) NOT NULL ,
	[LogConfigID] [int] NULL ,
	[LogUserID] [int] NULL ,
	[LogUserName] [nvarchar] (50) NULL ,
	[LogPortalID] [int] NULL ,
	[LogPortalName] [nvarchar] (100) NULL ,
	[LogCreateDate] [datetime] NOT NULL ,
	[LogServerName] [nvarchar] (50) NOT NULL ,
	[LogProperties] [ntext] NOT NULL ,
	[LogNotificationPending] [bit] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE {databaseOwner}{objectQualifier}EventLogConfig (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[LogTypeKey] [nvarchar] (35) NULL ,
	[LogTypePortalID] [int] NULL ,
	[LoggingIsActive] [bit] NOT NULL ,
	[KeepMostRecent] [int] NOT NULL ,
	[EmailNotificationIsActive] [bit] NOT NULL ,
	[NotificationThreshold] [int] NULL ,
	[NotificationThresholdTime] [int] NULL ,
	[NotificationThresholdTimeType] [int] NULL ,
	[MailFromAddress] [nvarchar] (50) NOT NULL ,
	[MailToAddress] [nvarchar] (50) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE {databaseOwner}{objectQualifier}EventLogTypes (
	[LogTypeKey] [nvarchar] (35) NOT NULL ,
	[LogTypeFriendlyName] [nvarchar] (50) NOT NULL ,
	[LogTypeDescription] [nvarchar] (128) NOT NULL ,
	[LogTypeOwner] [nvarchar] (100) NOT NULL ,
	[LogTypeCSSClass] [nvarchar] (40) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}EventLog WITH NOCHECK ADD 
	CONSTRAINT [PK_EventLogMaster] PRIMARY KEY  CLUSTERED 
	(
		[LogGUID]
	)  ON [PRIMARY] 
GO

ALTER TABLE {databaseOwner}{objectQualifier}EventLogConfig WITH NOCHECK ADD 
	CONSTRAINT [PK_EventLogConfig] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE {databaseOwner}{objectQualifier}EventLogTypes WITH NOCHECK ADD 
	CONSTRAINT [PK_EventLogTypes] PRIMARY KEY  CLUSTERED 
	(
		[LogTypeKey]
	)  ON [PRIMARY] 
GO

 CREATE  UNIQUE  INDEX [LogTypeKey_LogTypePortalID] ON {databaseOwner}{objectQualifier}EventLogConfig([LogTypeKey], [LogTypePortalID]) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}EventLog ADD 
	CONSTRAINT [FK_{objectQualifier}EventLog_{objectQualifier}EventLogConfig] FOREIGN KEY 
	(
		[LogConfigID]
	) REFERENCES {databaseOwner}{objectQualifier}EventLogConfig (
		[ID]
	)
GO

ALTER TABLE {databaseOwner}{objectQualifier}EventLogConfig ADD 
	CONSTRAINT [FK_EventLogConfig_EventLogTypes] FOREIGN KEY 
	(
		[LogTypeKey]
	) REFERENCES {databaseOwner}{objectQualifier}EventLogTypes (
		[LogTypeKey]
	)
GO

ALTER TABLE {databaseOwner}{objectQualifier}EventLog ADD CONSTRAINT
	FK_{objectQualifier}EventLog_{objectQualifier}EventLogTypes FOREIGN KEY
	(
	LogTypeKey
	) REFERENCES {databaseOwner}{objectQualifier}EventLogTypes
	(
	LogTypeKey
	)
GO