02.00.00.sqldataprovider

SharpNuke源代码

GO


create procedure {databaseOwner}{objectQualifier}GetPortalModuleDefinitionFee

@PortalId int

as

select 'HostFee' = sum(HostFee)
from {objectQualifier}PortalModuleDefinitions
where  PortalId = @PortalId


GO
drop procedure dbo.GetPortalModuleDefinitions
GO


create procedure {databaseOwner}{objectQualifier}GetPortalModuleDefinitions

@PortalId int

as

select distinct({objectQualifier}ModuleDefinitions.ModuleDefId),
       'Subscribed' = case when (PortalId = @PortalId and PortalModuleDefinitionId is not null) then 1 else 0 end,
       {objectQualifier}ModuleDefinitions.FriendlyName,
       {objectQualifier}ModuleDefinitions.Description,
       {objectQualifier}PortalModuleDefinitions.HostFee
from {objectQualifier}ModuleDefinitions
left outer join {objectQualifier}PortalModuleDefinitions on {objectQualifier}ModuleDefinitions.ModuleDefId = {objectQualifier}PortalModuleDefinitions.ModuleDefId
where  {objectQualifier}ModuleDefinitions.IsPremium = 1
order  by FriendlyName


GO
drop procedure dbo.GetPortalRoles
GO


create procedure {databaseOwner}{objectQualifier}GetPortalRoles

@PortalId     int

as

select {objectQualifier}Roles.RoleId,
       {objectQualifier}Roles.RoleName,
       {objectQualifier}Roles.Description,
       'ServiceFee' = case when convert(int,{objectQualifier}Roles.ServiceFee) <> 0 then {objectQualifier}Roles.ServiceFee else null end,
       'BillingPeriod' = case when convert(int,{objectQualifier}Roles.ServiceFee) <> 0 then {objectQualifier}Roles.BillingPeriod else null end,
       'BillingFrequency' = case when convert(int,{objectQualifier}Roles.ServiceFee) <> 0 then C1.Description else '' end,
       'TrialFee' = case when {objectQualifier}Roles.TrialFrequency <> 'N' then {objectQualifier}Roles.TrialFee else null end,
       'TrialPeriod' = case when {objectQualifier}Roles.TrialFrequency <> 'N' then {objectQualifier}Roles.TrialPeriod else null end,
       'TrialFrequency' = case when {objectQualifier}Roles.TrialFrequency <> 'N' then C2.Description else '' end,
       'IsPublic' = case when {objectQualifier}Roles.IsPublic = 1 then 'True' else 'False' end,
       'AutoAssignment' = case when {objectQualifier}Roles.AutoAssignment = 1 then 'True' else 'False' end
from {objectQualifier}Roles
left outer join {objectQualifier}CodeFrequency C1 on {objectQualifier}Roles.BillingFrequency = C1.Code
left outer join {objectQualifier}CodeFrequency C2 on {objectQualifier}Roles.TrialFrequency = C2.Code
where  PortalId = @PortalId
or     PortalId is null
order by {objectQualifier}Roles.RoleName


GO
drop procedure dbo.GetPortals
GO


create procedure {databaseOwner}{objectQualifier}GetPortals

as

select {objectQualifier}Portals.*,
       'Users' = ( select count(*) from {objectQualifier}UserPortals where {objectQualifier}UserPortals.PortalId = {objectQualifier}Portals.PortalId )
from {objectQualifier}Portals
order by PortalName


GO

create procedure {databaseOwner}{objectQualifier}VerifyPortalTab

@PortalId int,
@TabId    int

as

select {objectQualifier}Tabs.TabId
from {objectQualifier}Tabs
left outer join {objectQualifier}Portals on {objectQualifier}Tabs.PortalId = {objectQualifier}Portals.PortalId
where  TabId = @TabId
and    ( {objectQualifier}Portals.PortalId = @PortalId or {objectQualifier}Tabs.PortalId is null )

GO

create procedure {databaseOwner}{objectQualifier}VerifyPortal

@PortalId int

as

select {objectQualifier}Tabs.TabId
from {objectQualifier}Tabs
inner join {objectQualifier}Portals on {objectQualifier}Tabs.PortalId = {objectQualifier}Portals.PortalId
where {objectQualifier}Portals.PortalId = @PortalId
and {objectQualifier}Tabs.TabOrder = 1  

GO

drop procedure dbo.GetPortalSettings
GO

create procedure {databaseOwner}{objectQualifier}GetPortalSettings

@PortalId    int

as

select {objectQualifier}Portals.*,
       'Email' = ( select Email from {objectQualifier}Users where {objectQualifier}Users.UserId = {objectQualifier}Portals.AdministratorId ),
       'AdminTabId' = ( select TabId from {objectQualifier}Tabs where PortalId = {objectQualifier}Portals.PortalId and TabName = 'Admin' ),
       'SuperUserId' = ( select UserId from {objectQualifier}Users where IsSuperUser = 1 ),
       'SuperTabId' = ( select TabId from {objectQualifier}Tabs where PortalId is null and ParentId is null )
from   {objectQualifier}Portals
where  PortalId = @PortalId

GO
create procedure {databaseOwner}{objectQualifier}GetPortalTab

@TabId    int

as

select {objectQualifier}Tabs.*,
       'AdminTabIcon' = ( select AdminTabIcon from {objectQualifier}ModuleDefinitions where {objectQualifier}ModuleDefinitions.FriendlyName = {objectQualifier}Tabs.TabName ),
       'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end
from   {objectQualifier}Tabs
where  TabId = @TabId

GO
drop procedure dbo.GetPortalSpaceUsed
GO


create procedure {databaseOwner}{objectQualifier}GetPortalSpaceUsed

@PortalId int

as

select 'SpaceUsed' = sum(Size)
from   {objectQualifier}Files
where  ((PortalId = @PortalId) or (@PortalId is null and PortalId is null))

GO
drop procedure dbo.GetProcessorCodes
GO


create procedure {databaseOwner}{objectQualifier}GetProcessorCodes

as

select Processor,
       URL
from {objectQualifier}CodeProcessor
order by Processor


GO
drop procedure dbo.GetRegionCodes
GO


create procedure {databaseOwner}{objectQualifier}GetRegionCodes
    
@Country char(2)

as

select *
from {objectQualifier}CodeRegion
where  Country = @Country
order by Description


GO
drop procedure dbo.GetRoleMembership
GO


create procedure {databaseOwner}{objectQualifier}GetRoleMembership
    
@PortalId int,
@RoleId   int = null,
@UserId   int = null

as

if @RoleId is null
begin
  select {objectQualifier}UserRoles.UserRoleId,
         {objectQualifier}UserRoles.UserId,
         'FullName' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
         {objectQualifier}Users.Email,
         {objectQualifier}UserRoles.RoleId,
         {objectQualifier}Roles.RoleName,
         {objectQualifier}UserRoles.ExpiryDate
  from {objectQualifier}UserRoles
  inner join {objectQualifier}Users On {objectQualifier}Users.UserId = {objectQualifier}UserRoles.UserId
  inner join {objectQualifier}Roles On {objectQualifier}Roles.RoleId = {objectQualifier}UserRoles.RoleId
  inner join {objectQualifier}UserPortals On {objectQualifier}Users.UserId = {objectQualifier}UserPortals.UserId and {objectQualifier}UserPortals.PortalId = @PortalId
  where  {objectQualifier}Roles.PortalId = @PortalId
  and    {objectQualifier}UserRoles.UserId = @UserId
  and    {objectQualifier}UserPortals.Authorized = 1
end
else
begin
  select {objectQualifier}UserRoles.UserRoleId,
         {objectQualifier}UserRoles.UserId,
         'FullName' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
         {objectQualifier}Users.Email,
         {objectQualifier}UserRoles.RoleId,
         {objectQualifier}Roles.RoleName,
         {objectQualifier}UserRoles.ExpiryDate
  from {objectQualifier}UserRoles
  inner join {objectQualifier}Users On {objectQualifier}Users.UserId = {objectQualifier}UserRoles.UserId
  inner join {objectQualifier}Roles On {objectQualifier}Roles.RoleId = {objectQualifier}UserRoles.RoleId
  inner join {objectQualifier}UserPortals On {objectQualifier}Users.UserId = {objectQualifier}UserPortals.UserId and {objectQualifier}UserPortals.PortalId = @PortalId
  where  {objectQualifier}Roles.PortalId = @PortalId
  and    {objectQualifier}UserRoles.RoleId = @RoleId
  and    {objectQualifier}UserPortals.Authorized = 1
end


GO
drop procedure dbo.GetRolesByUser
GO


create procedure {databaseOwner}{objectQualifier}GetRolesByUser
    
@UserId        int,
@PortalId      int

as

select {objectQualifier}Roles.RoleName,
       {objectQualifier}Roles.RoleId
from {objectQualifier}UserRoles
inner join {objectQualifier}Users on {objectQualifier}UserRoles.UserId = {objectQualifier}Users.UserId
inner join {objectQualifier}Roles on {objectQualifier}UserRoles.RoleId = {objectQualifier}Roles.RoleId
where  {objectQualifier}Users.UserId = @UserId
and    {objectQualifier}Roles.PortalId = @PortalId
and    (ExpiryDate >= getdate() or ExpiryDate is null)


GO
drop procedure dbo.GetSearch
GO


create procedure {databaseOwner}{objectQualifier}GetSearchModule

@ModuleId int

as

select SearchId,
       TableName,
       TitleField,
       DescriptionField,
       CreatedDateField,
       CreatedByUserField
from {objectQualifier}Search
where  ModuleId = @ModuleId
order by TableName


GO
drop procedure dbo.GetServices
GO


create procedure {databaseOwner}{objectQualifier}GetServices
    
@PortalId  int,
@UserId    int = null

as

select RoleId,
       {objectQualifier}Roles.RoleName,
       {objectQualifier}Roles.Description,
       'ServiceFee' = case when convert(int,{objectQualifier}Roles.ServiceFee) <> 0 then {objectQualifier}Roles.ServiceFee else null end,
       'BillingPeriod' = case when convert(int,{objectQualifier}Roles.ServiceFee) <> 0 then {objectQualifier}Roles.BillingPeriod else null end,
       'BillingFrequency' = case when convert(int,{objectQualifier}Roles.ServiceFee) <> 0 then C1.Description else '' end,
       'TrialFee' = case when {objectQualifier}Roles.TrialFrequency <> 'N' then {objectQualifier}Roles.TrialFee else null end,
       'TrialPeriod' = case when {objectQualifier}Roles.TrialFrequency <> 'N' then {objectQualifier}Roles.TrialPeriod else null end,
       'TrialFrequency' = case when {objectQualifier}Roles.TrialFrequency <> 'N' then C2.Description else '' end,
       'ExpiryDate' = ( select ExpiryDate from {objectQualifier}UserRoles where {objectQualifier}UserRoles.RoleId = {objectQualifier}Roles.RoleId and {objectQualifier}UserRoles.UserId = @UserId ),
       'Subscribed' = ( select UserRoleId from {objectQualifier}UserRoles where {objectQualifier}UserRoles.RoleId = {objectQualifier}Roles.RoleId and {objectQualifier}UserRoles.UserId = @UserId )
from {objectQualifier}Roles
inner join {objectQualifier}CodeFrequency C1 on {objectQualifier}Roles.BillingFrequency = C1.Code
left outer join {objectQualifier}CodeFrequency C2 on {objectQualifier}Roles.TrialFrequency = C2.Code
where  {objectQualifier}Roles.PortalId = @PortalId
and    {objectQualifier}Roles.IsPublic = 1


GO
drop procedure dbo.GetSingleAnnouncement
GO


create procedure {databaseOwner}{objectQualifier}GetAnnouncement

@ItemId   int,
@ModuleId int

as

select Title,
       URL,
       Syndicate,
       ExpireDate,
       Description,
       Clicks,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Announcements.CreatedDate,
       ViewOrder
from {objectQualifier}Announcements
left outer join {objectQualifier}Users on {objectQualifier}Announcements.CreatedByUser = {objectQualifier}Users.UserId
where  ItemId = @ItemId
and    ModuleId = @ModuleId


GO
drop procedure dbo.GetSingleBanner
GO


create procedure {databaseOwner}{objectQualifier}GetBanner

@BannerId int,
@VendorId int

as

select BannerId,
       VendorId,
       ImageFile,
       BannerName,
       URL,
       Impressions,
       CPM,
       Views,
       ClickThroughs,
       StartDate,
       EndDate,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Banners.CreatedDate,
       BannerTypeId       
from {objectQualifier}Banners
left outer join {objectQualifier}Users on {objectQualifier}Banners.CreatedByUser = {objectQualifier}Users.UserId
where  BannerId = @BannerId
and    vendorId = @VendorId


GO
drop procedure dbo.GetSingleContact
GO


create procedure {databaseOwner}{objectQualifier}GetContact

@ItemId   int,
@ModuleId int

as

select Name,
       Role,
       {objectQualifier}Contacts.Email,
       Contact1,
       Contact2,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Contacts.CreatedDate
from {objectQualifier}Contacts
left outer join {objectQualifier}Users on {objectQualifier}Contacts.CreatedByUser = {objectQualifier}Users.UserId
where  ItemId = @ItemId
and    ModuleId = @ModuleId


GO
drop procedure dbo.GetSingleCountry
GO


create procedure {databaseOwner}{objectQualifier}GetCountry

@Code        char(2) = null,
@Description varchar(100) = null

as

select	*
from	{objectQualifier}CodeCountry
where	(Code = @Code or @Code is null)
or	(Description = @Description or @Description is null)

GO
drop procedure dbo.GetSingleDocument
GO


create procedure {databaseOwner}{objectQualifier}GetDocument

@ItemId   int,
@ModuleId int

as

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


GO
drop procedure dbo.GetSingleFAQ
GO


create procedure {databaseOwner}{objectQualifier}GetFAQ

@ItemId   int,
@ModuleId int

as

select ItemId,
       ModuleId,
       Question,
       Answer,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}FAQs.CreatedDate
from {objectQualifier}FAQs
left outer join {objectQualifier}Users on {objectQualifier}FAQs.CreatedByUser = {objectQualifier}Users.UserId
where  ItemId = @ItemId
and    ModuleId = @ModuleId


GO
drop procedure dbo.GetSingleFile
GO


create procedure {databaseOwner}{objectQualifier}GetFile

@FileName  nvarchar(100),
@PortalId  int

as

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

GO
drop procedure dbo.GetSingleLink
GO


create procedure {databaseOwner}{objectQualifier}GetLink

@ItemId   int,
@ModuleId int

as

select Title,
       Url,
       MobileUrl,
       ViewOrder,
       Description,
       NewWindow,
       Clicks,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Links.CreatedDate
from {objectQualifier}Links
left outer join {objectQualifier}Users on {objectQualifier}Links.CreatedByUser = {objectQualifier}Users.UserId
where  ItemId = @ItemId
and    ModuleId = @ModuleId


GO
drop procedure dbo.GetSingleMessage
GO


create procedure {databaseOwner}{objectQualifier}GetMessage

@ItemId   int,
@ModuleId int

as

select ItemId,
       Title,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Discussion.CreatedDate,
       Body,
       DisplayOrder
from {objectQualifier}Discussion
left outer join {objectQualifier}Users on {objectQualifier}Discussion.CreatedByUser = {objectQualifier}Users.UserId
where  ItemId = @ItemId
and    ModuleId = @ModuleId


GO
drop procedure dbo.GetSingleModuleDefinition