02.00.00.sqldataprovider

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

create procedure {databaseOwner}{objectQualifier}AddModule
    
@TabId                         int,
@ModuleDefId                   int,
@ModuleOrder                   int,
@PaneName                      nvarchar(50),
@ModuleTitle                   nvarchar(256),
@AuthorizedEditRoles           nvarchar(256),
@CacheTime                     int,
@ShowMobile                    bit,
@AuthorizedViewRoles           nvarchar(256),
@Alignment                     nvarchar(10),
@Color                         nvarchar(20),
@Border                        nvarchar(1),
@IconFile                      nvarchar(100),
@AllTabs                       bit,
@ShowTitle                     bit,
@Personalize                   int,
@Container                     nvarchar(1000)

as

insert {objectQualifier}Modules ( 
  TabId,
  ModuleDefId,
  ModuleOrder,
  PaneName,
  ModuleTitle,
  AuthorizedEditRoles,
  CacheTime,
  ShowMobile,
  AuthorizedViewRoles,
  Alignment,
  Color,
  Border,
  IconFile,
  AllTabs,
  ShowTitle,
  Personalize,
  Container
)
values (
  @TabId,
  @ModuleDefId,
  @ModuleOrder,
  @PaneName,
  @ModuleTitle,
  @AuthorizedEditRoles,
  @CacheTime,
  @ShowMobile,
  @AuthorizedViewRoles,
  @Alignment,
  @Color,
  @Border,
  @IconFile,
  @AllTabs,
  @ShowTitle,
  @Personalize,
  @Container
)

select SCOPE_IDENTITY()

GO

drop procedure dbo.AddModuleEvent
GO


create procedure {databaseOwner}{objectQualifier}AddModuleEvent

@ModuleID    int,
@Description nvarchar(2000),
@DateTime    datetime,
@Title       nvarchar(100),
@ExpireDate  datetime = null,
@UserName    nvarchar(200),
@Every       int,
@Period      char(1),
@IconFile    nvarchar(256),
@AltText     nvarchar(50)

as

insert into {objectQualifier}ModuleEvents ( 
  ModuleID,
  Description,
  DateTime,
  Title,
  ExpireDate,
  CreatedByUser,
  CreatedDate,
  Every,
  Period,
  IconFile,
  AltText
)
values (
  @ModuleID,
  @Description,
  @DateTime,
  @Title,
  @ExpireDate,
  @UserName,
  getdate(),
  @Every,
  @Period,
  @IconFile,
  @AltText
)

select SCOPE_IDENTITY()

GO
drop procedure dbo.AddPortalInfo
GO


create procedure {databaseOwner}{objectQualifier}AddPortalInfo

@PortalName         nvarchar(128),
@PortalAlias        nvarchar(200),
@Currency           char(3) = null,
@FirstName          nvarchar(100),
@LastName           nvarchar(100),
@Username           nvarchar(100),
@Password           nvarchar(50),
@Email              nvarchar(100),
@ExpiryDate         datetime = null,
@HostFee            money = 0,
@HostSpace          int = null,
@SiteLogHistory     int = null

as

insert into {objectQualifier}Portals (
  PortalName,
  PortalAlias,
  LogoFile,
  FooterText,
  ExpiryDate,
  UserRegistration,
  BannerAdvertising,
  Currency,
  AdministratorId,
  HostFee,
  HostSpace,
  AdministratorRoleId,
  RegisteredRoleId,
  Description,
  KeyWords,
  BackgroundFile
)
values (
  @PortalName,
  @PortalAlias,
  null,
  null,
  @ExpiryDate,
  0,
  0,
  @Currency,
  null,
  @HostFee,
  @HostSpace,
  null,
  null,
  @PortalName,
  @PortalName,
  null
)

select SCOPE_IDENTITY()

GO
create procedure {databaseOwner}{objectQualifier}GetPortalUsers

@PortalId int

as

select *
from   {objectQualifier}UserPortals
where  PortalId = @PortalId
GO
drop procedure dbo.AddRole
GO


create procedure {databaseOwner}{objectQualifier}AddRole

@PortalId         int,
@RoleName         nvarchar(50),
@Description      nvarchar(1000),
@ServiceFee       money,
@BillingPeriod    int,
@BillingFrequency char(1),
@TrialFee         money,
@TrialPeriod      int,
@TrialFrequency   char(1),
@IsPublic         bit,
@AutoAssignment   bit

as

insert into {objectQualifier}Roles (
  PortalId,
  RoleName,
  Description,
  ServiceFee,
  BillingPeriod,
  BillingFrequency,
  TrialFee,
  TrialPeriod,
  TrialFrequency,
  IsPublic,
  AutoAssignment
)
values (
  @PortalId,
  @RoleName,
  @Description,
  @ServiceFee,
  @BillingPeriod,
  @BillingFrequency,
  @TrialFee,
  @TrialPeriod,
  @TrialFrequency,
  @IsPublic,
  @AutoAssignment
)

select SCOPE_IDENTITY()

GO
drop procedure dbo.AddSearch
GO


create procedure {databaseOwner}{objectQualifier}AddSearch

@ModuleId  int,
@TableName nvarchar(50)

as

insert into {objectQualifier}Search (
  ModuleId,
  TableName
)
values (
  @ModuleId,
  @TableName
)

select SCOPE_IDENTITY()

GO
drop procedure dbo.AddSiteLog
GO


create procedure {databaseOwner}{objectQualifier}AddSiteLog

@PortalId                      int,
@UserId                        int                   = null,
@Referrer                      nvarchar(255)         = null,
@Url                           nvarchar(255)         = null,
@UserAgent                     nvarchar(255)         = null,
@UserHostAddress               nvarchar(255)         = null,
@UserHostName                  nvarchar(255)         = null,
@TabId                         int                   = null,
@AffiliateId                   int                   = null

as
 
declare @SiteLogHistory int

insert into {objectQualifier}SiteLog ( 
  DateTime,
  PortalId,
  UserId,
  Referrer,
  Url,
  UserAgent,
  UserHostAddress,
  UserHostName,
  TabId,
  AffiliateId
)
values (
  getdate(),
  @PortalId,
  @UserId,
  @Referrer,
  @Url,
  @UserAgent,
  @UserHostAddress,
  @UserHostName,
  @TabId,
  @AffiliateId
)

/* purge site log history */
select @SiteLogHistory = SiteLogHistory
from {objectQualifier}Portals
where  PortalId = @PortalId

if @SiteLogHistory is not null
begin
  delete
  from {objectQualifier}SiteLog
  where  PortalId = @PortalId
  and    datediff(day,DateTime,getdate()) > @SiteLogHistory
  and    AffiliateId is null
end


GO

drop procedure dbo.AddTab
GO


create procedure {databaseOwner}{objectQualifier}AddTab

@PortalId           int,
@TabName            nvarchar(50),
@ShowMobile         bit,
@MobileTabName      nvarchar(50),
@AuthorizedRoles    nvarchar (256),
@LeftPaneWidth      nvarchar(5),
@RightPaneWidth     nvarchar(5),
@IsVisible          bit,
@DisableLink        bit,
@ParentId           int,
@IconFile           nvarchar(100),
@AdministratorRoles nvarchar (256)

as

insert into {objectQualifier}Tabs (
    PortalId,
    TabName,
    ShowMobile,
    MobileTabName,
    AuthorizedRoles,
    LeftPaneWIdth,
    RightPaneWIdth,
    IsVisible,
    DisableLink,
    ParentId,
    IconFile,
    AdministratorRoles
)
values (
    @PortalId,
    @TabName,
    @ShowMobile,
    @MobileTabName,
    @AuthorizedRoles,
    @LeftPaneWIdth,
    @RightPaneWIdth,
    @IsVisible,
    @DisableLink,
    @ParentId,
    @IconFile,
    @AdministratorRoles
)

select SCOPE_IDENTITY()

GO
create procedure {databaseOwner}{objectQualifier}AddPortalUser

@PortalId   int, 
@UserId     int, 
@Authorized bit

as

insert into {objectQualifier}UserPortals ( 
  UserId, 
  PortalId, 
  Authorized, 
  CreatedDate 
) 
values ( 
  @UserId,
  @PortalId, 
  @Authorized, 
  getdate() 
)

select SCOPE_IDENTITY()

GO

drop procedure dbo.AddUser
GO


create procedure {databaseOwner}{objectQualifier}AddUser

@FirstName	nvarchar(50),
@LastName	nvarchar(50),
@Unit		nvarchar(50),
@Street		nvarchar(50),
@City		nvarchar(50),
@Region		nvarchar(50),
@PostalCode	nvarchar(50),
@Country	nvarchar(50),
@Telephone      nvarchar(50),
@Email		nvarchar(100),
@Username	nvarchar(100),
@Password	nvarchar(50)

as

insert into {objectQualifier}Users (
  FirstName,
  LastName,
  Unit, 
  Street, 
  City,
  Region, 
  PostalCode,
  Country,
  Telephone,
  Email,
  Username,
  Password
)
values (
  @FirstName,
  @LastName,
  @Unit,
  @Street,
  @City,
  @Region,
  @PostalCode,
  @Country,
  @Telephone,
  @Email,
  @Username,
  @Password
)

select SCOPE_IDENTITY()

GO
drop procedure dbo.AddUserDefinedField
GO


create procedure {databaseOwner}{objectQualifier}AddUserDefinedField

@ModuleId     int,
@FieldTitle   varchar(50),
@Visible      bit,
@FieldType    varchar(20)

as

insert into {objectQualifier}UserDefinedFields ( 
  ModuleId,
  FieldTitle,
  Visible,
  FieldType
)
values (
  @ModuleId,
  @FieldTitle,
  @Visible,
  @FieldType
)

select SCOPE_IDENTITY()

GO
drop procedure dbo.AddUserDefinedRow
GO


create procedure {databaseOwner}{objectQualifier}AddUserDefinedRow

@ModuleId         int

as

insert into {objectQualifier}UserDefinedRows ( 
  ModuleId
)
values (
  @ModuleId
)

select SCOPE_IDENTITY()


GO
drop procedure dbo.AddUserRole
GO


create procedure {databaseOwner}{objectQualifier}AddUserRole

@PortalId   int,
@UserId     int,
@RoleId     int,
@ExpiryDate datetime = null

as

declare @UserRoleId int

select @UserRoleId = null

select @UserRoleId = UserRoleId
from {objectQualifier}UserRoles
inner join {objectQualifier}UserPortals on {objectQualifier}UserPortals.UserId = @UserId
where  {objectQualifier}UserRoles.UserId = @UserId
and    {objectQualifier}UserRoles.RoleId = @RoleId
and    PortalId = @PortalId
 
if @UserRoleId is not null
begin
  update {objectQualifier}UserRoles
  set    ExpiryDate = @ExpiryDate
  where  UserRoleId = @UserRoleId

  select @UserRoleId
end
else
begin
  insert into {objectQualifier}UserRoles (
    UserId,
    RoleId,
    ExpiryDate
  )