03.00.05.sqldataprovider

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

from {objectQualifier}Discussion
left outer join {objectQualifier}Users on {objectQualifier}Discussion.CreatedByUser = {objectQualifier}Users.UserId
where  ItemId = @ItemId
and    ModuleId = @ModuleId

GO

DROP procedure {databaseOwner}{objectQualifier}GetDocument
GO

CREATE procedure {databaseOwner}{objectQualifier}GetDocument

@ItemId   int,
@ModuleId int

as

select {objectQualifier}Documents.Itemid,
		{objectQualifier}Documents.Moduleid,
		{objectQualifier}Documents.Title,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Documents.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Documents.Category,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Files.Size,
       {objectQualifier}Documents.CreatedDate,
       {objectQualifier}UrlTracking.TrackClicks,
       {objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Documents
left outer join {objectQualifier}Users on {objectQualifier}Documents.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}UrlTracking on {objectQualifier}Documents.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
left outer join {objectQualifier}Files on {objectQualifier}Documents.URL = 'fileid=' +
convert(varchar,{objectQualifier}Files.FileID)
where  {objectQualifier}Documents.ItemId = @ItemId
and    {objectQualifier}Documents.ModuleId = @ModuleId
GO

DROP procedure {databaseOwner}{objectQualifier}GetDocuments
GO

CREATE procedure {databaseOwner}{objectQualifier}GetDocuments

@ModuleId int,
@PortalId int

as

select {objectQualifier}Documents.ItemId,
		{objectQualifier}Documents.Moduleid,
       {objectQualifier}Documents.Title,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Documents.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Documents.CreatedDate,
       {objectQualifier}Documents.Category,
       {objectQualifier}Files.Size,
       {objectQualifier}UrlTracking.TrackClicks,
       {objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Documents
left outer join {objectQualifier}Users on {objectQualifier}Documents.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}Files on {objectQualifier}Documents.URL = 'fileid=' +
convert(varchar,{objectQualifier}Files.FileID)
left outer join {objectQualifier}UrlTracking on {objectQualifier}Documents.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
where  {objectQualifier}Documents.ModuleId = @ModuleId
order by {objectQualifier}Documents.Title
GO

drop procedure {databaseOwner}{objectQualifier}GetEventsByDate
GO

CREATE procedure {databaseOwner}{objectQualifier}GetEventsByDate

@ModuleId int,
@StartDate datetime,
@EndDate datetime

as

select {objectQualifier}Events.ItemId,
       {objectQualifier}Events.ModuleId,
       {objectQualifier}Events.Description,
       {objectQualifier}Events.DateTime,
       {objectQualifier}Events.Title,
       {objectQualifier}Events.ExpireDate,
       {objectQualifier}Events.CreatedByUser,
       {objectQualifier}Events.CreatedDate,
       {objectQualifier}Events.Every,
       {objectQualifier}Events.Period,
       'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Events.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Events.AltText
from   {objectQualifier}Events
left outer join {objectQualifier}Files on {objectQualifier}Events.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  ModuleId = @ModuleId
and    ( (Period is null and (DateTime >= @StartDate and DateTime <= @EndDate)) or Period is not null )
order by DateTime
GO

drop procedure {databaseOwner}{objectQualifier}GetEvents
GO

CREATE procedure {databaseOwner}{objectQualifier}GetEvents

@ModuleId int

as

select {objectQualifier}Events.ItemId,
       {objectQualifier}Events.ModuleId,
       {objectQualifier}Events.Description,
       {objectQualifier}Events.DateTime,
       {objectQualifier}Events.Title,
       {objectQualifier}Events.ExpireDate,
       {objectQualifier}Events.CreatedByUser,
       {objectQualifier}Events.CreatedDate,
       'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Events.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Events.AltText,
       'MaxWIdth' = (select max(WIdth) from {objectQualifier}Events left outer join {objectQualifier}Files on {objectQualifier}Events.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID) where ModuleId = @ModuleId and (ExpireDate > getdate() or ExpireDate is null))
from   {objectQualifier}Events
left outer join {objectQualifier}Files on {objectQualifier}Events.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  ModuleId = @ModuleId
and    (ExpireDate > getdate() or ExpireDate is null)
order by DateTime
GO

drop procedure {databaseOwner}{objectQualifier}GetEvent
GO

CREATE procedure {databaseOwner}{objectQualifier}GetEvent

@ItemId   int,
@ModuleId int

as

select {objectQualifier}Events.ItemId,
		{objectQualifier}Events.ModuleId,
       {objectQualifier}Events.Description,
       {objectQualifier}Events.DateTime,
       {objectQualifier}Events.Title,
       {objectQualifier}Events.ExpireDate,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Events.CreatedDate,
       {objectQualifier}Events.Every,
       {objectQualifier}Events.Period,
       'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Events.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Events.AltText
from   {objectQualifier}Events
left outer join {objectQualifier}Users on {objectQualifier}Events.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}Files on {objectQualifier}Events.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  ItemId = @ItemId
and    ModuleId = @ModuleId
GO

drop procedure {databaseOwner}{objectQualifier}GetLinks
GO

CREATE procedure {databaseOwner}{objectQualifier}GetLinks

@ModuleId int

as

select {objectQualifier}Links.ItemId,
		{objectQualifier}Links.ModuleId,
       {objectQualifier}Links.CreatedByUser,
       {objectQualifier}Links.CreatedDate,
       {objectQualifier}Links.Title,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Links.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Links.ViewOrder,
       {objectQualifier}Links.Description,
       {objectQualifier}UrlTracking.TrackClicks,
       {objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Links
left outer join {objectQualifier}UrlTracking on {objectQualifier}Links.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID left outer join {objectQualifier}Files on {objectQualifier}Links.URL = 'fileid=' +
convert(varchar,{objectQualifier}Files.FileID)
where  {objectQualifier}Links.ModuleId = @ModuleId
order by {objectQualifier}Links.ViewOrder, {objectQualifier}Links.Title

GO

drop procedure {databaseOwner}{objectQualifier}GetLink
GO

CREATE procedure {databaseOwner}{objectQualifier}GetLink

@ItemId   int,
@ModuleId int

as

select {objectQualifier}Links.ItemId,
		{objectQualifier}Links.ModuleId,
		{objectQualifier}Links.Title,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Links.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Links.ViewOrder,
       {objectQualifier}Links.Description,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Links.CreatedDate,
       {objectQualifier}UrlTracking.TrackClicks,
       {objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Links
left outer join {objectQualifier}Users on {objectQualifier}Links.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}UrlTracking on {objectQualifier}Links.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID left outer join {objectQualifier}Files on {objectQualifier}Links.URL = 'fileid=' +
convert(varchar,{objectQualifier}Files.FileID)
where  {objectQualifier}Links.ItemId = @ItemId
and    {objectQualifier}Links.ModuleId = @ModuleId
GO

drop procedure {databaseOwner}{objectQualifier}GetFAQs
GO


create procedure {databaseOwner}{objectQualifier}GetFAQs

@ModuleId int

as

select ItemId,
		ModuleId,
       CreatedDate,
       CreatedByUser,
       Question,
       Answer
from {objectQualifier}FAQs
where  ModuleId = @ModuleId


GO

drop procedure {databaseOwner}{objectQualifier}GetUserDefinedFields
GO

create procedure {databaseOwner}{objectQualifier}GetUserDefinedFields

@ModuleId  int

as

select UserDefinedFieldId,
		ModuleID,
       FieldTitle,
       Visible,
       FieldOrder,
       FieldType
from {objectQualifier}UserDefinedFields
where  ModuleId = @ModuleId
order by FieldOrder
GO

drop procedure {databaseOwner}{objectQualifier}GetUserDefinedField
GO

create procedure {databaseOwner}{objectQualifier}GetUserDefinedField

@UserDefinedFieldId  int

as

select UserDefinedFieldId,
		ModuleID,
       FieldTitle,
       Visible,
       FieldOrder,
       FieldType
from {objectQualifier}UserDefinedFields
where  UserDefinedFieldId = @UserDefinedFieldId


GO

create procedure {databaseOwner}{objectQualifier}GetFileById

@FileId   int,
@PortalId int

as

select *
from   {objectQualifier}Files
where  FileId = @FileId
and    ((PortalId = @PortalId) or (@PortalId is null and PortalId is null))

GO

INSERT INTO {databaseOwner}{objectQualifier}Schedule VALUES ('DotNetNuke.Modules.Admin.ResourceInstaller.InstallResources, DOTNETNUKE', 1, 'm', 5, 'm', 60, '', 0, 1, 'ResourceInstaller')
GO

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/