01.00.08.sqldataprovider

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

drop procedure UpdateModule
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateModuleDefinition') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateModuleDefinition
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateModuleEvent') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateModuleEvent
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateModuleOrder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateModuleOrder
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateModuleSetting') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateModuleSetting
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdatePortalExpiry') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdatePortalExpiry
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdatePortalInfo') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdatePortalInfo
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdatePortalModuleDefinition') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdatePortalModuleDefinition
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdatePortalTabOrder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdatePortalTabOrder
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateReferrer') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateReferrer
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateRole') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateRole
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateSearch') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateSearch
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateService') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateService
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateTab') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateTab
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateTabModuleOrder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateTabModuleOrder
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateTabOrder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateTabOrder
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateUser
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateUserDefinedData') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateUserDefinedData
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateUserDefinedField') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateUserDefinedField
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateUserDefinedFieldOrder') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateUserDefinedFieldOrder
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateUserDefinedRow') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateUserDefinedRow
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateUserLogin') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateUserLogin
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateVendor') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateVendor
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UpdateVendorFeedback') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateVendorFeedback
GO

if exists (select * from dbo.sysobjects where id = object_id(N'UserLogin') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UserLogin
GO

/* add all stored procedures with dbo owner */

create procedure dbo.AddAnnouncement

@ModuleID       int,
@UserName       nvarchar(100),
@Title          nvarchar(150),
@URL            nvarchar(150),
@Syndicate      bit,
@ExpireDate     DateTime,
@Description    nvarchar(2000),
@ViewOrder	int

as

insert into Announcements (
  ModuleID,
  CreatedByUser,
  CreatedDate,
  Title,
  URL,
  Syndicate,
  ExpireDate,
  Description,
  ViewOrder
)
values (
  @ModuleID,
  @UserName,
  getdate(),
  @Title,
  @URL,
  @Syndicate,
  @ExpireDate,
  @Description,
  @ViewOrder
)

GO

create procedure dbo.AddBanner

@BannerName    nvarchar(100),
@VendorId      int,
@ImageFile     nvarchar(50),
@URL           nvarchar(100) = null,
@Impressions   int,
@CPM           float,
@StartDate     datetime = null,
@EndDate       datetime = null,
@UserName      nvarchar(100),
@BannerTypeId  int = null

as

insert into Banners (
    VendorId,
    ImageFile,
    BannerName,
    URL,
    Impressions,
    CPM,
    Views,
    ClickThroughs,
    StartDate,
    EndDate,
    CreatedByUser,
    CreatedDate,
    BannerTypeId
)
values (
    @VendorId,
    @ImageFile,
    @BannerName,
    @URL,
    @Impressions,
    @CPM,
    0,
    0,
    @StartDate,
    @EndDate,
    @UserName,
    getdate(),
    @BannerTypeId
)

GO

create procedure dbo.AddContact

@ModuleID int,
@UserName nvarchar(100),
@Name     nvarchar(50),
@Role     nvarchar(100),
@Email    nvarchar(100),
@Contact1 nvarchar(250),
@Contact2 nvarchar(250)

as

insert into Contacts (
  CreatedByUser,
  CreatedDate,
  ModuleID,
  Name,
  Role,
  Email,
  Contact1,
  Contact2
)
values (
  @UserName,
  getdate(),
  @ModuleID,
  @Name,
  @Role,
  @Email,
  @Contact1,
  @Contact2
)

GO

create procedure dbo.AddDocument

@ModuleID         int,
@Title            nvarchar(150),
@URL              nvarchar(250),
@UserName         nvarchar(100),
@Category         nvarchar(50),
@Syndicate        bit

as

insert into Documents (
  ModuleID,
  Title,
  URL,
  CreatedByUser,
  CreatedDate,
  Category,
  Syndicate
)
values (
  @ModuleID,
  @Title,
  @URL,
  @UserName,
  getdate(),
  @Category,
  @Syndicate
)

GO

create procedure dbo.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

create procedure dbo.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

create procedure dbo.AddLink

@ModuleID    int,
@UserName    nvarchar(100),
@Title       nvarchar(100),
@Url         nvarchar(250),
@MobileUrl   nvarchar(250),
@ViewOrder   int,
@Description nvarchar(2000),
@NewWindow   bit

as

insert into Links (
  ModuleID,
  CreatedByUser,
  CreatedDate,
  Title,
  Url,
  MobileUrl,
  ViewOrder,
  Description,
  NewWindow
)
values (
  @ModuleID,
  @UserName,
  getdate(),
  @Title,
  @Url,
  @MobileUrl,
  @ViewOrder,
  @Description,
  @NewWindow
)

GO

create procedure dbo.AddMessage

@Title nvarchar(100),
@Body nvarchar(3000),
@ParentID int,
@UserName nvarchar(100),
@ModuleID int

as

declare @ParentDisplayOrder nvarchar(750)

select @ParentDisplayOrder = ''

select @ParentDisplayOrder = DisplayOrder
from   Discussion 
where  ItemID = @ParentID

insert into Discussion (
  Title,
  Body,
  DisplayOrder,
  CreatedDate, 
  CreatedByUser,
  ModuleID
)
values (
  @Title,
  @Body,
  @ParentDisplayOrder + convert(nvarchar(24),getdate(),21),
  getdate(),
  @UserName,
  @ModuleID
)

GO

create procedure dbo.AddModule
    
@TabID          int,
@ModuleOrder    int,
@ModuleTitle    nvarchar(256),
@PaneName       nvarchar(50),
@ModuleDefID    int,
@CacheTime      int,
@EditRoles      nvarchar(256),
@ShowMobile     bit

as

if @ModuleOrder = -1
begin
  select @ModuleOrder = max(ModuleOrder) + 2
  from   Modules
  where  TabID = @TabID
  and    PaneName = @PaneName
  if @ModuleOrder is null
    select @ModuleOrder = 1
end

insert into Modules (
  TabID,
  ModuleOrder,
  ModuleTitle,
  PaneName,
  ModuleDefID,
  CacheTime,
  AuthorizedEditRoles,
  ShowMobile
) 
values (
  @TabID,
  @ModuleOrder,
  @ModuleTitle,
  @PaneName,
  @ModuleDefID,
  @CacheTime,
  @EditRoles,
  @ShowMobile
)

GO

create procedure dbo.AddModuleDefinition
    
@FriendlyName nvarchar(128),
@DesktopSrc   nvarchar(256),
@MobileSrc    nvarchar(256),
@AdminOrder   int,
@EditSrc      nvarchar(256),
@Secure       bit,
@Description  nvarchar(2000),
@HostFee      money

as

declare @ModuleDefId int
declare @TabId int
declare @AdministratorRoleId int
declare @PortalId int
declare @TabOrder int
declare @ChildTabId int

insert into ModuleDefinitions (
  FriendlyName,
  DesktopSrc,
  MobileSrc,
  AdminOrder,
  EditSrc,
  Secure,
  Description,
  HostFee
)
values (
  @FriendlyName,
  @DesktopSrc,
  @MobileSrc,
  @AdminOrder,
  @EditSrc,
  @Secure,
  @Description,
  @HostFee
)

select @ModuleDefID = @@IDENTITY

/* add to all Admin tabs */
if @AdminOrder is not null and @AdminOrder > 0
begin
  select @TabId = min(TabId)
  from Tabs
  where TabName = 'Admin'
  while @TabId is not null
  begin
    select @AdministratorRoleId = AdministratorRoleId,
           @PortalId = Tabs.PortalId
    from   Portals
    inner join Tabs on Portals.PortalId = Tabs.PortalId
    where TabId = @TabId
    if not exists ( select 1 from Tabs inner join Modules on Tabs.TabId = Modules.TabId where PortalID = @PortalId and ModuleDefID = @ModuleDefId )
    begin
      select @TabOrder = (max(TabOrder) + 2)
      from   Tabs
      where  PortalId = @PortalId
      and    IsVisible = 1
      insert into Tabs (
        TabOrder,
        PortalID,
        TabName,
        MobileTabName,
        AuthorizedRoles,
        ShowMobile,
        LeftPaneWidth,
        RightPaneWidth,
        IsVisible,
        ParentId,
        IconFile,
        Level
      )
      values (
        @TabOrder,
        @PortalID,
        @FriendlyName,
        '',
        convert(varchar,@AdministratorRoleId) + ';',
        0,
        '200',
        '200',
        1,
        @TabID,
        null,
        1      
      )
      select @ChildTabId = @@IDENTITY
      
      insert into Modules (
        TabID,
        ModuleDefID,
        ModuleOrder,
        PaneName,
        ModuleTitle,