02.00.00.sqldataprovider

SharpNuke源代码

@BannerId  int, 
@Views     int, 
@StartDate datetime, 
@EndDate   datetime

as

update {objectQualifier}Banners
set    Views = @Views,
       StartDate = @StartDate,
       EndDate = @EndDate
where  BannerId = @BannerId
GO
drop procedure dbo.FindVendors
GO


drop procedure dbo.GetAnnouncements
GO


create procedure {databaseOwner}{objectQualifier}GetAnnouncements

@ModuleId int

as

select ItemId,
       CreatedByUser,
       CreatedDate,
       Title,
       URL,
       Syndicate,
       ExpireDate,
       Description,
       ViewOrder
from {objectQualifier}Announcements
where  ModuleId = @ModuleId
and    (ExpireDate > GetDate() or ExpireDate is null)
order by ViewOrder asc, CreatedDate desc


GO
drop procedure dbo.GetAuthRoles
GO


create procedure {databaseOwner}{objectQualifier}GetAuthRoles

@PortalId    int,
@ModuleId    int

as

select {objectQualifier}Tabs.AuthorizedRoles,
       {objectQualifier}Modules.AuthorizedEditRoles
from {objectQualifier}Modules
inner join {objectQualifier}Tabs ON {objectQualifier}Modules.TabId = {objectQualifier}Tabs.TabId
where  {objectQualifier}Modules.ModuleId = @ModuleId
and    {objectQualifier}Tabs.PortalId = @PortalId


GO
drop procedure dbo.GetBannerClickThrough
GO


create procedure {databaseOwner}{objectQualifier}UpdateBannerClickThrough

@BannerId int,
@VendorId int

as

update {objectQualifier}Banners
set    ClickThroughs = ClickThroughs + 1
where  BannerId = @BannerId
and    VendorId = @VendorId

GO
drop procedure dbo.GetBannerLog
GO

drop procedure dbo.GetBanners
GO


create procedure {databaseOwner}{objectQualifier}GetBanners

@VendorId int

as

select BannerId,
       BannerName,
       BannerTypeName,
       URL,
       Impressions,
       CPM,
       Views,
       ClickThroughs,
       StartDate,
       EndDate
from {objectQualifier}Banners
inner join {objectQualifier}BannerTypes on {objectQualifier}Banners.BannerTypeId = {objectQualifier}BannerTypes.BannerTypeId
where  VendorId = @VendorId
order  by CreatedDate desc


GO
drop procedure dbo.GetBannerTypes
GO


create procedure {databaseOwner}{objectQualifier}GetBannerTypes

as

select BannerTypeId,
       BannerTypeName
from {objectQualifier}BannerTypes


GO
drop procedure dbo.GetBillingFrequencyCode
GO


create procedure {databaseOwner}{objectQualifier}GetBillingFrequencyCode
    
@Code char(1)

as

select Description
from {objectQualifier}CodeFrequency
where  Code = @Code


GO
drop procedure dbo.GetBillingFrequencyCodes
GO


create procedure {databaseOwner}{objectQualifier}GetBillingFrequencyCodes
    
as

select *
from {objectQualifier}CodeFrequency


GO
drop procedure dbo.GetClicks
GO


create procedure {databaseOwner}{objectQualifier}GetClicks

@TableName nvarchar(50),
@ItemId    int

as

select DateTime,
       'FullName' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName
from {objectQualifier}ClickLog
left outer join {objectQualifier}Users on {objectQualifier}ClickLog.UserId = {objectQualifier}Users.UserId
where  TableName = @TableName
and    ItemId = @ItemId
order by DateTime desc


GO
drop procedure dbo.GetContacts
GO


create procedure {databaseOwner}{objectQualifier}GetContacts

@ModuleId int

as

select ItemId,
       CreatedDate,
       CreatedByUser,
       Name,
       Role,
       Email,
       Contact1,
       Contact2
from {objectQualifier}Contacts
where  ModuleId = @ModuleId
order by Name

GO
drop procedure dbo.GetCountryCodes
GO


create procedure {databaseOwner}{objectQualifier}GetCountryCodes
    
as

select *
from {objectQualifier}CodeCountry
order by Description


GO
drop procedure dbo.GetCurrencies
GO


create procedure {databaseOwner}{objectQualifier}GetCurrencies

as

select Code,
       Description
from {objectQualifier}CodeCurrency


GO
drop procedure dbo.GetDocuments
GO


create procedure {databaseOwner}{objectQualifier}GetDocuments

@ModuleId int,
@PortalId int

as

select ItemId,
       Title,
       URL,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Documents.CreatedDate,
       Category,
       Size,
       Syndicate
from {objectQualifier}Documents
left outer join {objectQualifier}Users on {objectQualifier}Documents.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}Files on {objectQualifier}Documents.URL = {objectQualifier}Files.FileName and {objectQualifier}Files.PortalId = @PortalId
where  ModuleId = @ModuleId
order by Title


GO
drop procedure dbo.GetFAQs
GO


create procedure {databaseOwner}{objectQualifier}GetFAQs

@ModuleId int

as

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


GO
drop procedure dbo.GetFiles
GO


create procedure {databaseOwner}{objectQualifier}GetFiles

@PortalId   int

as

select FileId,
       PortalId,
       FileName,
       Extension,
       Size,
       WIdth,
       Height,
       ContentType
from {objectQualifier}Files
where  ((PortalId = @PortalId) or (@PortalId is null and PortalId is null))
order by FileName

GO
drop procedure dbo.GetHostSetting
GO


create procedure {databaseOwner}{objectQualifier}GetHostSetting

@SettingName nvarchar(50)

as

select SettingValue
from {objectQualifier}HostSettings
where  SettingName = @SettingName


GO
drop procedure dbo.GetHostSettings
GO


create procedure {databaseOwner}{objectQualifier}GetHostSettings

as

select SettingName,
       SettingValue
from {objectQualifier}HostSettings


GO
drop procedure dbo.GetHtmlText
GO


create procedure {databaseOwner}{objectQualifier}GetHtmlText

@ModuleId int

as

select *
from {objectQualifier}HtmlText
where  ModuleId = @ModuleId


GO
drop procedure dbo.GetLinks
GO


create procedure {databaseOwner}{objectQualifier}GetLinks

@ModuleId int

as

select ItemId,
       CreatedByUser,
       CreatedDate,
       Title,
       Url,
       ViewOrder,
       Description,
       NewWindow
from {objectQualifier}Links
where  ModuleId = @ModuleId
order by ViewOrder, Title


GO
drop procedure dbo.GetModule
GO


create procedure {databaseOwner}{objectQualifier}GetModule

@ModuleId int

as

select *
from {objectQualifier}Modules
inner join {objectQualifier}ModuleDefinitions on {objectQualifier}Modules.ModuleDefId = {objectQualifier}ModuleDefinitions.ModuleDefId
where  ModuleId = @ModuleId


GO
create procedure {databaseOwner}{objectQualifier}GetHostModuleDefinitions

as

select ModuleDefId,
       FriendlyName,
       Description,
       'IsPremium' = case when IsPremium = 1 then 'Y' else 'N' end
from {objectQualifier}ModuleDefinitions
where  AdminOrder is null
and    DesktopSrc is not null
order  by FriendlyName

GO
create procedure {databaseOwner}{objectQualifier}GetAdminModuleDefinitions

as

select *
from   {objectQualifier}ModuleDefinitions 
where  AdminOrder is not null
and    AdminOrder > 0
order by AdminOrder
GO

drop procedure dbo.GetModuleDefinitions
GO

create procedure {databaseOwner}{objectQualifier}GetModuleDefinitions

@PortalId int

as

select distinct({objectQualifier}ModuleDefinitions.ModuleDefId) as ModuleDefId,
       {objectQualifier}ModuleDefinitions.FriendlyName,
       {objectQualifier}ModuleDefinitions.Description
from {objectQualifier}ModuleDefinitions
left outer join {objectQualifier}PortalModuleDefinitions on {objectQualifier}ModuleDefinitions.ModuleDefId = {objectQualifier}PortalModuleDefinitions.ModuleDefId
where  AdminOrder is null
and    DesktopSrc is not null
and    ( {objectQualifier}ModuleDefinitions.IsPremium = 0 or (PortalId = @PortalId and PortalModuleDefinitionId is not null))
order  by FriendlyName

GO
drop procedure dbo.GetModuleEvents
GO


create procedure {databaseOwner}{objectQualifier}GetModuleEvents

@ModuleId int

as

select ItemId,
       Description,
       DateTime,
       Title,
       ExpireDate,
       CreatedByUser,
       CreatedDate,
       IconFile,
       AltText,
       'MaxWIdth' = (select max(WIdth) from {objectQualifier}ModuleEvents left outer join {objectQualifier}Files on {objectQualifier}ModuleEvents.IconFile = {objectQualifier}Files.FileName where ModuleId = @ModuleId and (ExpireDate > getdate() or ExpireDate is null))
from {objectQualifier}ModuleEvents
where  ModuleId = @ModuleId
and    (ExpireDate > getdate() or ExpireDate is null)
order by DateTime

GO
create procedure {databaseOwner}{objectQualifier}GetModuleEventsByDate

@ModuleId int,
@StartDate datetime,
@EndDate datetime

as

select ItemId,
       Description,
       DateTime,
       Title,
       ExpireDate,
       CreatedByUser,
       CreatedDate,
       Every,
       Period,
       IconFile,
       AltText
from {objectQualifier}ModuleEvents
where  ModuleId = @ModuleId
and    ( (Period is null and (DateTime >= @StartDate and DateTime <= @EndDate)) or Period is not null )
order by DateTime

GO
drop procedure dbo.GetModuleSettings
GO


create procedure {databaseOwner}{objectQualifier}GetModuleSettings

@ModuleId int

as

select SettingName,
       SettingValue
from {objectQualifier}ModuleSettings
where  ModuleId = @ModuleId


GO
drop procedure dbo.GetPortalByAlias
GO


create procedure {databaseOwner}{objectQualifier}GetPortalByAlias

@PortalAlias nvarchar(200)

as

select PortalId
from {objectQualifier}Portals
where  PortalAlias = @PortalAlias

GO
create procedure {databaseOwner}{objectQualifier}UpdatePortalAlias

@PortalAlias nvarchar(200)

as

update {objectQualifier}Portals 
set    PortalAlias = @PortalAlias
where  PortalAlias = '_default'

GO

drop procedure dbo.GetPortalByTab
GO


create procedure {databaseOwner}{objectQualifier}GetPortalByTab

@TabId int,
@PortalAlias nvarchar(200)
 
as

select PortalAlias
from {objectQualifier}Portals
inner join {objectQualifier}Tabs on {objectQualifier}Portals.PortalId = {objectQualifier}Tabs.PortalId
where  TabId = @TabId
and    PortalAlias like '%' + @PortalAlias + '%'

GO
drop procedure dbo.GetPortalModuleDefinitionFee