03.00.04.sqldataprovider

SharpNuke源代码

       LastName      = @LastName,
       CreatedByUser = @UserName,
       CreatedDate   = getdate(),
       LogoFile      = @LogoFile,
       KeyWords      = @KeyWords,
       Authorized    = @Authorized
where  VendorId = @VendorId
GO

drop procedure {databaseOwner}{objectQualifier}GetVendor
GO

CREATE procedure {databaseOwner}{objectQualifier}GetVendor

@VendorId int

as

select {objectQualifier}Vendors.VendorName, 
       {objectQualifier}Vendors.Unit, 
       {objectQualifier}Vendors.Street, 
       {objectQualifier}Vendors.City, 
       {objectQualifier}Vendors.Region, 
       {objectQualifier}Vendors.Country, 
       {objectQualifier}Vendors.PostalCode, 
       {objectQualifier}Vendors.Telephone,
       {objectQualifier}Vendors.Fax,
       {objectQualifier}Vendors.Cell,
       {objectQualifier}Vendors.Email,
       {objectQualifier}Vendors.Website,
       {objectQualifier}Vendors.FirstName,
       {objectQualifier}Vendors.LastName,
       {objectQualifier}Vendors.ClickThroughs,
       {objectQualifier}Vendors.Views,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Vendors.CreatedDate,
      'LogoFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Vendors.LogoFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Vendors.KeyWords,
       {objectQualifier}Vendors.Authorized
from {objectQualifier}Vendors
left outer join {objectQualifier}Users on {objectQualifier}Vendors.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}Files on {objectQualifier}Vendors.LogoFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  VendorId = @VendorId
GO

drop procedure {databaseOwner}{objectQualifier}GetThreadMessages
GO

create procedure {databaseOwner}{objectQualifier}GetThreadMessages

@Parent nvarchar(750)

as

select ItemId,
       DisplayOrder,
       'Indent' = ((LEN( DisplayOrder ) / 19 ) - 1 ) * 5,
       Title,  
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Discussion.CreatedDate,
       Body
from {objectQualifier}Discussion
left outer join {objectQualifier}Users on {objectQualifier}Discussion.CreatedByUser = {objectQualifier}Users.UserId
where  LEFT(DisplayOrder, 19) = @Parent
and    (LEN( DisplayOrder ) / 19 ) > 1
order by DisplayOrder

GO

drop procedure {databaseOwner}{objectQualifier}GetTopLevelMessages
GO


create procedure {databaseOwner}{objectQualifier}GetTopLevelMessages

@ModuleId int

as

select ItemId,
       DisplayOrder,
       'Parent' = LEFT(DisplayOrder, 19),    
       'ChildCount' = (SELECT COUNT(*) -1  from {objectQualifier}Discussion Disc2 WHERE LEFT(Disc2.DisplayOrder,LEN(RTRIM({objectQualifier}Discussion.DisplayOrder))) = {objectQualifier}Discussion.DisplayOrder),
       Title,  
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Discussion.CreatedDate
from {objectQualifier}Discussion
left outer join {objectQualifier}Users on {objectQualifier}Discussion.CreatedByUser = {objectQualifier}Users.UserId
where  ModuleId = @ModuleId
and    (LEN( DisplayOrder ) / 19 ) = 1
order by DisplayOrder


GO

drop procedure {databaseOwner}{objectQualifier}AddUser
GO

create procedure {databaseOwner}{objectQualifier}AddUser

@PortalID	int,
@Username	nvarchar(100),
@FirstName	nvarchar(50),
@LastName	nvarchar(50),
@AffiliateId    int,
@IsSuperUser    bit

as

declare @UserID int

select @UserID = UserID
from   {objectQualifier}Users
where  Username = @Username

if @UserID is null
begin
  insert into {objectQualifier}Users (
    Username,
    FirstName, 
    LastName, 
    AffiliateId,
    IsSuperUser
  )
  values (
    @Username,
    @FirstName, 
    @LastName, 
    @AffiliateId,
    @IsSuperUser
  )

  select @UserID = SCOPE_IDENTITY()
end

if not exists ( select 1 from {objectQualifier}UserPortals where UserID = @UserID and PortalID = @PortalID )
begin
  insert into {objectQualifier}UserPortals (
    UserID,
    PortalID
  )
  values (
    @UserID,
    @PortalID
  )
end

select @UserID

GO

ALTER TABLE {databaseOwner}{objectQualifier}Portals ADD
	SplashTabId int NULL
GO

drop procedure {databaseOwner}{objectQualifier}GetPortal
GO

CREATE procedure {databaseOwner}{objectQualifier}GetPortal

@PortalId  int

as

select {objectQualifier}Portals.PortalID,
       {objectQualifier}Portals.PortalName,
       'LogoFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Portals.LogoFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Portals.FooterText,
       {objectQualifier}Portals.ExpiryDate,
       {objectQualifier}Portals.UserRegistration,
       {objectQualifier}Portals.BannerAdvertising,
       {objectQualifier}Portals.AdministratorID,
       {objectQualifier}Portals.Currency,
       {objectQualifier}Portals.HostFee,
       {objectQualifier}Portals.HostSpace,
       {objectQualifier}Portals.AdministratorRoleID,
       {objectQualifier}Portals.RegisteredRoleID,
       {objectQualifier}Portals.Description,
       {objectQualifier}Portals.KeyWords,
       'BackGroundFile' = case when {objectQualifier}Files2.FileName is null then {objectQualifier}Portals.BackGroundFile else {objectQualifier}Files2.Folder + {objectQualifier}Files2.FileName end,
       {objectQualifier}Portals.GUID,
       {objectQualifier}Portals.PaymentProcessor,
       {objectQualifier}Portals.ProcessorPassword,
       {objectQualifier}Portals.SiteLogHistory,
       {objectQualifier}Portals.SplashTabID,
       {objectQualifier}Portals.HomeTabID,
       {objectQualifier}Portals.LoginTabID,
       {objectQualifier}Portals.UserTabID,
       {objectQualifier}Portals.DefaultLanguage,
       {objectQualifier}Portals.TimeZoneOffset,
       {objectQualifier}Portals.AdminTabID,
       {objectQualifier}Portals.HomeDirectory,
       'SuperTabId' = ( select TabId from {objectQualifier}Tabs where PortalId is null and ParentId is null ),
       'AdministratorRoleName' = ( select RoleName from {objectQualifier}Roles where RoleId = {objectQualifier}Portals.AdministratorRoleID ),
       'RegisteredRoleName' = ( select RoleName from {objectQualifier}Roles where RoleId = {objectQualifier}Portals.RegisteredRoleID )
from {objectQualifier}Portals
left outer join {objectQualifier}Files on {objectQualifier}Portals.LogoFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
left outer join {objectQualifier}Files as {objectQualifier}Files2 on {objectQualifier}Portals.BackGroundFile = 'fileid=' +convert(varchar,{objectQualifier}Files2.FileID)
where {objectQualifier}Portals.PortalId = @PortalId

GO

drop procedure {databaseOwner}{objectQualifier}UpdatePortalInfo
GO

CREATE procedure {databaseOwner}{objectQualifier}UpdatePortalInfo

@PortalId           int,
@PortalName         nvarchar(128),
@LogoFile           nvarchar(50),
@FooterText         nvarchar(100),
@ExpiryDate         datetime,
@UserRegistration   int,
@BannerAdvertising  int,
@Currency           char(3),
@AdministratorId    int,
@HostFee            money,
@HostSpace          int,
@PaymentProcessor   nvarchar(50),
@ProcessorUserId    nvarchar(50),
@ProcessorPassword  nvarchar(50),
@Description        nvarchar(500),
@KeyWords           nvarchar(500),
@BackgroundFile     nvarchar(50),
@SiteLogHistory     int,
@SplashTabId          int,
@HomeTabId          int,
@LoginTabId         int,
@UserTabId          int,
@DefaultLanguage    nvarchar(6),
@TimeZoneOffset	    int,
@HomeDirectory varchar(100)

as

update {databaseOwner}{objectQualifier}Portals
set    PortalName = @PortalName,
       LogoFile = @LogoFile,
       FooterText = @FooterText,
       ExpiryDate = @ExpiryDate,
       UserRegistration = @UserRegistration,
       BannerAdvertising = @BannerAdvertising,
       Currency = @Currency,
       AdministratorId = @AdministratorId,
       HostFee = @HostFee,
       HostSpace = @HostSpace,
       PaymentProcessor = @PaymentProcessor,
       ProcessorUserId = @ProcessorUserId,
       ProcessorPassword = @ProcessorPassword,
       Description = @Description,
       KeyWords = @KeyWords,
       BackgroundFile = @BackgroundFile,
       SiteLogHistory = @SiteLogHistory,
       SplashTabId = @SplashTabId,
       HomeTabId = @HomeTabId,
       LoginTabId = @LoginTabId,
       UserTabId = @UserTabId,
       DefaultLanguage = @DefaultLanguage,
       TimeZoneOffset = @TimeZoneOffset,
       HomeDirectory = @HomeDirectory
where  PortalId = @PortalId

GO

drop procedure {databaseOwner}{objectQualifier}UpdatePortalSetup
GO

create procedure {databaseOwner}{objectQualifier}UpdatePortalSetup

@PortalId            int,
@AdministratorId     int,
@AdministratorRoleId int,
@RegisteredRoleId    int,
@SplashTabId         int,
@HomeTabId           int,
@LoginTabId          int,
@UserTabId           int,
@AdminTabId          int

as

update {objectQualifier}Portals
set    AdministratorId = @AdministratorId, 
       AdministratorRoleId = @AdministratorRoleId, 
       RegisteredRoleId = @RegisteredRoleId,
       SplashTabId = @SplashTabId,
       HomeTabId = @HomeTabId,
       LoginTabId = @LoginTabId,
       UserTabId = @UserTabId,
       AdminTabId = @AdminTabId
where  PortalId = @PortalId

GO

drop procedure {databaseOwner}{objectQualifier}GetPortalByPortalAliasID
GO

CREATE procedure {databaseOwner}{objectQualifier}GetPortalByPortalAliasID

@PortalAliasId  int

as

select {objectQualifier}Portals.PortalID,
       {objectQualifier}Portals.PortalName,
       'LogoFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Portals.LogoFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Portals.FooterText,
       {objectQualifier}Portals.ExpiryDate,
       {objectQualifier}Portals.UserRegistration,
       {objectQualifier}Portals.BannerAdvertising,
       {objectQualifier}Portals.AdministratorID,
       {objectQualifier}Portals.Currency,
       {objectQualifier}Portals.HostFee,
       {objectQualifier}Portals.HostSpace,
       {objectQualifier}Portals.AdministratorRoleID,
       {objectQualifier}Portals.RegisteredRoleID,
       {objectQualifier}Portals.Description,
       {objectQualifier}Portals.KeyWords,
       'BackGroundFile' = case when {objectQualifier}Files2.FileName is null then {objectQualifier}Portals.BackGroundFile else {objectQualifier}Files2.Folder + {objectQualifier}Files2.FileName end,
       {objectQualifier}Portals.GUID,
       {objectQualifier}Portals.PaymentProcessor,
       {objectQualifier}Portals.ProcessorPassword,
       {objectQualifier}Portals.SiteLogHistory,
       {objectQualifier}Portals.SplashTabID,
       {objectQualifier}Portals.HomeTabID,
       {objectQualifier}Portals.LoginTabID,
       {objectQualifier}Portals.UserTabID,
       {objectQualifier}Portals.DefaultLanguage,
       {objectQualifier}Portals.TimeZoneOffset,
       {objectQualifier}Portals.AdminTabID,
       {objectQualifier}Portals.HomeDirectory, 
       PA.HTTPAlias, 
       PA.PortalAliasID, 
       'SuperTabId' = ( select TabId from {objectQualifier}Tabs where PortalId is null and ParentId is null )
from {objectQualifier}Portals
INNER JOIN {objectQualifier}PortalAlias PA ON {objectQualifier}Portals.PortalID = PA.PortalID
left outer join {objectQualifier}Files on {objectQualifier}Portals.LogoFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
left outer join {objectQualifier}Files as {objectQualifier}Files2 on {objectQualifier}Portals.BackGroundFile = 'fileid=' +convert(varchar,{objectQualifier}Files2.FileID)
where PA.PortalAliasId = @PortalAliasId
GO

drop procedure {databaseOwner}{objectQualifier}GetPortals
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetPortals

as

select {objectQualifier}Portals.PortalID,
       {objectQualifier}Portals.PortalName,
       'LogoFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Portals.LogoFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Portals.FooterText,
       {objectQualifier}Portals.ExpiryDate,
       {objectQualifier}Portals.UserRegistration,
       {objectQualifier}Portals.BannerAdvertising,
       {objectQualifier}Portals.AdministratorID,
       {objectQualifier}Portals.Currency,
       {objectQualifier}Portals.HostFee,
       {objectQualifier}Portals.HostSpace,
       {objectQualifier}Portals.AdministratorRoleID,
       {objectQualifier}Portals.RegisteredRoleID,
       {objectQualifier}Portals.Description,
       {objectQualifier}Portals.KeyWords,
       'BackGroundFile' = case when {objectQualifier}Files2.FileName is null then {objectQualifier}Portals.BackGroundFile else {objectQualifier}Files2.Folder + {objectQualifier}Files2.FileName end,
       {objectQualifier}Portals.GUID,
       {objectQualifier}Portals.PaymentProcessor,
       {objectQualifier}Portals.ProcessorPassword,
       {objectQualifier}Portals.SiteLogHistory,
       {objectQualifier}Portals.SplashTabID,
       {objectQualifier}Portals.HomeTabID,
       {objectQualifier}Portals.LoginTabID,
       {objectQualifier}Portals.UserTabID,
       {objectQualifier}Portals.DefaultLanguage,
       {objectQualifier}Portals.TimeZoneOffset,
       {objectQualifier}Portals.AdminTabID,
       {objectQualifier}Portals.HomeDirectory,
       'Users' = ( select count(*) from {objectQualifier}UserPortals where {objectQualifier}UserPortals.PortalId = {objectQualifier}Portals.PortalId )
from {objectQualifier}Portals
left outer join {objectQualifier}Files on {objectQualifier}Portals.LogoFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
left outer join {objectQualifier}Files as {objectQualifier}Files2 on {objectQualifier}Portals.BackGroundFile = 'fileid=' +convert(varchar,{objectQualifier}Files2.FileID)
order by {objectQualifier}Portals.PortalName
GO

drop procedure {databaseOwner}{objectQualifier}GetModule
GO

CREATE procedure {databaseOwner}{objectQualifier}GetModule

@ModuleId int,
@TabId    int

as

select	{objectQualifier}Modules.ModuleID,
		{objectQualifier}Modules.ModuleDefID,
		{objectQualifier}Modules.ModuleTitle,
		{objectQualifier}Modules.AllTabs,
		{objectQualifier}Modules.IsDeleted,
		{objectQualifier}Modules.InheritViewPermissions,
		{objectQualifier}Modules.Header,