01.00.00.sqldataprovider

SharpNuke源代码

where  ItemID = @ItemID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE UpdateHtmlText
(
    @ModuleID      int,
    @DesktopHtml   ntext,
    @MobileSummary ntext,
    @MobileDetails ntext
)
AS

IF NOT EXISTS (
    SELECT 
        * 
    FROM 
        HtmlText 
    WHERE 
        ModuleID = @ModuleID
)
INSERT INTO HtmlText (
    ModuleID,
    DesktopHtml,
    MobileSummary,
    MobileDetails
) 
VALUES (
    @ModuleID,
    @DesktopHtml,
    @MobileSummary,
    @MobileDetails
)
ELSE
UPDATE
    HtmlText

SET
    DesktopHtml   = @DesktopHtml,
    MobileSummary = @MobileSummary,
    MobileDetails = @MobileDetails

WHERE
    ModuleID = @ModuleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure UpdateImage

@EntityType nvarchar(20), 
@EntityId int, 
@ImagePath varchar(50),
@ImageWidth int,
@ImageHeight int

as

declare @ImageId int

if @EntityType = 'Group'
begin
  select @ImageId = GroupImageId
  from   GroupsImages
  where  GroupId = @EntityId
  and    ImagePath = @ImagePath

  if @ImageId is null
  begin
    insert into GroupsImages (
      GroupId,
      ImagePath,
      ImageWidth,
      ImageHeight
    )
    values (
      @EntityId,
      @ImagePath,
      @ImageWidth,
      @ImageHeight
    )
  end
  else
  begin
    update GroupsImages
    set    ImageWidth = @ImageWidth,
           ImageHeight = @ImageHeight
    where  GroupImageId = @ImageId
  end
end

if @EntityType = 'Participant'
begin
  select @ImageId = ParticipantImageId
  from   ParticipantsImages
  where  ParticipantId = @EntityId
  and    ImagePath = @ImagePath

  if @ImageId is null
  begin
    insert into ParticipantsImages (
      ParticipantId,
      ImagePath,
      ImageWidth,
      ImageHeight
    )
    values (
      @EntityId,
      @ImagePath,
      @ImageWidth,
      @ImageHeight
    )
  end
  else
  begin
    update ParticipantsImages
    set    ImageWidth = @ImageWidth,
           ImageHeight = @ImageHeight
    where  ParticipantImageId = @ImageId
  end
end

if @EntityType = 'Event'
begin
  select @ImageId = EventImageId
  from   EventsImages
  where  EventId = @EntityId
  and    ImagePath = @ImagePath

  if @ImageId is null
  begin
    insert into EventsImages (
      EventId,
      ImagePath,
      ImageWidth,
      ImageHeight
    )
    values (
      @EntityId,
      @ImagePath,
      @ImageWidth,
      @ImageHeight
    )
  end
  else
  begin
    update EventsImages
    set    ImageWidth = @ImageWidth,
           ImageHeight = @ImageHeight
    where  EventImageId = @ImageId
  end
end

return 1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE UpdateModuleOrder
(
    @ModuleID           int,
    @ModuleOrder        int,
    @PaneName           nvarchar(50)
)
AS

UPDATE
    Modules

SET
    ModuleOrder = @ModuleOrder,
    PaneName    = @PaneName

WHERE
    ModuleID = @ModuleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE UpdateModuleSetting
(
    @ModuleID      int,
    @SettingName   nvarchar(50),
    @SettingValue  nvarchar(256)
)
AS

IF NOT EXISTS (
    SELECT 
        * 
    FROM 
        ModuleSettings 
    WHERE 
        ModuleID = @ModuleID
      AND
        SettingName = @SettingName
)
INSERT INTO ModuleSettings (
    ModuleID,
    SettingName,
    SettingValue
) 
VALUES (
    @ModuleID,
    @SettingName,
    @SettingValue
)
ELSE
UPDATE
    ModuleSettings

SET
    SettingValue = @SettingValue

WHERE
    ModuleID = @ModuleID
  AND
    SettingName = @SettingName



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure UpdateRole

@RoleID           int,
@RoleName         nvarchar(50),
@Description      nvarchar(1000) = null,
@ServiceFee       decimal = null,
@BillingFrequency char(1),
@TrialPeriod      int = null,
@TrialFrequency   char(1)

as

update Roles
set    RoleName = @RoleName,
       Description = @Description,
       ServiceFee = @ServiceFee,
       BillingFrequency = @BillingFrequency,
       TrialPeriod = @TrialPeriod,
       TrialFrequency = @TrialFrequency
where  RoleID = @RoleID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure UpdateService
    
@UserId       int,
@RoleId       int,
@Units        int

as

declare @Frequency char(1)
declare @ExpiryDate datetime
declare @IsTrialUsed bit

select @ExpiryDate = ExpiryDate,
       @IsTrialUsed = IsTrialUsed
from   UserRoles
where  UserId = @UserId
and    RoleId = @RoleId

if @Units = 0
begin
  if @IsTrialUsed is null /* trial period not used */
  begin
    select @Frequency = TrialFrequency,
           @Units = TrialPeriod
    from   Roles
    where  RoleId = @RoleId

    if @Units is null /* no trial period for role */
    begin
      select @Frequency = '0'
    end
    else
    begin
      if @ExpiryDate is null or @ExpiryDate < getdate()
        select @ExpiryDate = getdate()
    end
  end
  else
  begin
    select @Frequency = '0'
  end
end
else
begin
  select @Frequency = BillingFrequency
  from   Roles
  where  RoleId = @RoleId

  if @ExpiryDate is null or @ExpiryDate < getdate()
    select @ExpiryDate = getdate()
end

select @ExpiryDate =
  case
    when @Frequency = '0' then @ExpiryDate
    when @Frequency = '1' then convert(datetime,'12/31/9999')
    when @Frequency = '2' then dateadd(Day,@Units,@ExpiryDate)
    when @Frequency = '3' then dateadd(Week,@Units,@ExpiryDate)
    when @Frequency = '4' then dateadd(Month,@Units,@ExpiryDate)
    when @Frequency = '5' then dateadd(Year,@Units,@ExpiryDate)
  end

if exists ( select 1 from UserRoles where UserId = @UserId and RoleId = @RoleId )
begin
  update UserRoles
  set    ExpiryDate = @ExpiryDate,
         IsTrialUsed = 1
  where  UserId = @UserId
  and    RoleId = @RoleId
end
else
begin
  insert UserRoles (
    UserId,
    RoleId,
    ExpiryDate,
    IsTrialUsed
  )
  values (
    @UserId,
    @RoleId,
    @ExpiryDate,
    1
  )
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure UpdateTab

@TabID           int,
@TabName         nvarchar(50),
@ShowMobile      bit,
@MobileTabName   nvarchar(50),
@AuthorizedRoles nvarchar(256),
@LeftPaneWidth   nvarchar(5),
@RightPaneWidth  nvarchar(5),
@IsVisible       bit

as

update Tabs
set    TabName = @TabName,
       ShowMobile = @ShowMobile,
       MobileTabName = @MobileTabName,
       AuthorizedRoles = @AuthorizedRoles,
       LeftPaneWidth = @LeftPaneWidth,
       RightPaneWidth = @RightPaneWidth,
       IsVisible = @IsVisible
where  TabID = @TabID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE UpdateTabOrder
(
    @TabID           int,
    @TabOrder        int
)
AS

UPDATE
    Tabs

SET
    TabOrder = @TabOrder

WHERE
    TabID = @TabID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure AddFAQ

@ModuleID int,
@UserName nvarchar(100),
@Question text,
@Answer   text

as

insert into FAQs (
  CreatedByUser,
  CreatedDate,
  ModuleID,
  Question,
  Answer
)
values (
  @UserName,
  getdate(),
  @ModuleID,
  @Question,
  @Answer
)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure AddFile

@PortalId    int,
@FileName    nvarchar(100),
@Extension   nvarchar(100),
@Size        int,
@Width       int,
@Height      int,
@ContentType nvarchar(200)

as

declare @FileId int

select @FileId = null

select @FileId = FileId
from   Files
where  FileName = @FileName
and    PortalId = @PortalId

if @FileId is null
begin
  insert Files ( 
    PortalId,
    FileName,
    Extension,
    Size,
    Width,
    Height,
    ContentType 
  )
  values (
    @PortalId,
    @FileName,
    @Extension,
    @Size,
    @Width,
    @Height,
    @ContentType 
  )
end
else
begin
  update Files
  set    FileName = @FileName,
         Extension = @Extension,
         Size = @Size,
         Width = @Width,
         Height = @Height,
         ContentType = @ContentType
  where  FileId = @FileId
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure DeleteFile

@FileName nvarchar(100),
@PortalId int

as

delete 
from   Files
where  FileName = @FileName
and    PortalId = @PortalId

return 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure DeleteFiles

@PortalId int

as

if @PortalId is null
begin
  delete 
  from   Files
  where  PortalId is null
end
else
begin
  delete 
  from   Files
  where  PortalId = @PortalId
end

return 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetBannerTypes

as

select BannerTypeId,
       BannerTypeName
from   BannerTypes

return 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetCountryCodes
    
as

select *
from   CodeCountry
order by Description


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetCurrencies

as

select Code,
       Description
from   CodeCurrency

return 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetFiles

@PortalId   int

as

if @PortalId is null
begin
  select FileId,
         PortalId,
         FileName,
         Extension,
         Size,
         Width,
         Height,
         ContentType
  from   Files
  where  PortalId is null
end
else
begin
  select FileId,
         PortalId,
         FileName,
         Extension,
         Size,
         Width,
         Height,
         ContentType
  from   Files
  where  PortalId = @PortalId
end

return 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetModuleDefinitions

@Admin bit = 1

as