01.00.04.sqldataprovider

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

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

create procedure UpdateUserDefinedFieldOrder

@UserDefinedFieldId  int,
@Direction           int

as

declare @ModuleId int
declare @FieldOrder int

select @ModuleId = ModuleId,
       @FieldOrder = FieldOrder
from   UserDefinedFields
where  UserDefinedFieldId = @UserDefinedFieldId

if (@Direction = -1 and @FieldOrder > 0) or (@Direction = 1 and @FieldOrder < ( select (count(*) - 1) from UserDefinedFields where ModuleId = @ModuleId ))
begin
  update UserDefinedFields
  set    FieldOrder = @FieldOrder
  where  ModuleId = @ModuleId
  and    FieldOrder = @FieldOrder + @Direction

  update UserDefinedFields
  set    FieldOrder = @FieldOrder + @Direction
  where  UserDefinedFieldId = @UserDefinedFieldId
end

return 1

GO

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

create procedure UpdateUserDefinedRow

@UserDefinedRowId int

as

if not exists ( select 1 from UserDefinedData where UserDefinedRowId = @UserDefinedRowId )
begin
  delete
  from   UserDefinedRows
  where  userDefinedRowId = @UserDefinedRowId
end

GO

/* data */
if not exists ( select 1 from ModuleDefinitions where FriendlyName = 'User Defined Table' )
begin
  insert into ModuleDefinitions ( FriendlyName, DesktopSrc, MobileSrc, AdminOrder, EditSrc, Secure ) 
  values ( 'User Defined Table', 'DesktopModules/UserDefinedTable/UserDefinedTable.ascx', NULL, NULL, 'DesktopModules/UserDefinedTable/EditUserDefinedTable.ascx', 1 )
end
GO

if not exists ( select 1 from ModuleDefinitions where FriendlyName = 'Manage UDT' )
begin
  insert into ModuleDefinitions ( FriendlyName, DesktopSrc, MobileSrc, AdminOrder, EditSrc, Secure ) 
  values ( 'Manage UDT', NULL, NULL, NULL, 'DesktopModules/UserDefinedTable/ManageUserDefinedTable.ascx', 1 )
end
GO

create procedure GetTabs

@PortalID   int 

as

select TabID,
       'TabOrder' = case when TabOrder = 0 then 999 else Taborder end,
       TabName,
       MobileTabName,
       AuthorizedRoles,
       ShowMobile,
       LeftPaneWidth,
       RightPaneWidth,
       IsVisible,
       ParentId,
       'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end
from   Tabs
where  PortalID = @PortalID
order by TabOrder, TabName

GO

drop procedure UpdatePortalTabOrder
GO

create procedure UpdatePortalTabOrder

@PortalID int

as

declare @TabCounter int
declare @TabOrder int

select @TabCounter = 0

update Tabs
set    TabOrder = -999
where  PortalID = @PortalID
and    (ParentId is not null or IsVisible = 0)

update Tabs
set    TabOrder = 999
where  PortalID = @PortalID
and    TabName = 'Admin'

select @TabOrder = min(TabOrder)
from   Tabs
where  PortalID = @PortalID
and    TabOrder <> -999
while @TabOrder is not null
begin
  select @TabCounter = @TabCounter + 1        

  update Tabs
  set    TabOrder = ((@TabCounter * 2) - 1) * -1 
  where  PortalID = @PortalID
  and    TabOrder = @TabOrder  

  select @TabOrder = min(TabOrder)
  from   Tabs
  where  PortalID = @PortalID
  and    TabOrder > @TabOrder
end 

update Tabs
set    TabOrder = 0
where  PortalID = @PortalID
and    TabOrder = -999

update Tabs
set    TabOrder = TabOrder * -1 
where  PortalID = @PortalID

GO

drop procedure GetPortalSettings
GO

create procedure GetPortalSettings

@PortalAlias nvarchar(200),
@TabID       int

as

declare @PortalID int
declare @VerifyTabID int

/* convert PortalAlias to PortalID */

select @PortalID = null

select @PortalID = PortalID
from   Portals
where  PortalAlias = @PortalAlias

if @PortalID is null
begin
  select @PortalID = min(PortalID)
  from   Portals
  where  PortalAlias like '%' + @PortalAlias + '%' /* multiple alias may be specified seperated by commas */
end

select @VerifyTabID = null

/* verify the TabID belongs to the portal */
if @TabID <> 0
begin
  select @VerifyTabID = Tabs.TabID
  from   Tabs
  left outer join Portals on Tabs.PortalID = Portals.PortalID
  where  TabId = @TabId
  and    ( Portals.PortalID = @PortalID or Tabs.PortalId is null )
end
else
begin
  select @VerifyTabID = null
end

/* get the TabID if none provided */
if @VerifyTabID is null
begin
  select @TabID = min(Tabs.TabID)
  from Tabs
  inner join Portals on Tabs.PortalID = Portals.PortalID
  where Portals.PortalID = @PortalID
end

/* First, get Out Params */
select Portals.PortalAlias,
       Portals.PortalID,
       Portals.GUID,
       Portals.PortalName,
       Portals.LogoFile,
       Portals.FooterText,
       Portals.ExpiryDate,
       Portals.UserRegistration,
       Portals.BannerAdvertising,
       Portals.Currency,
       Portals.AdministratorId,
       Users.Email,
       Portals.HostFee,
       Portals.HostSpace,
       Portals.PayPalId,
       Portals.AdministratorRoleId,
       Portals.RegisteredRoleId,
       Portals.Description,
       Portals.KeyWords,
       Portals.BackgroundFile,
       'SuperUserId' = ( select UserID from Users where IsSuperUser = 1 ),
       'SuperTabId' = ( select TabID from Tabs where PortalId is null ),
       Tabs.TabID,
       Tabs.TabOrder,
       Tabs.TabName,
       Tabs.MobileTabName,
       Tabs.AuthorizedRoles,
       Tabs.ShowMobile,
       Tabs.LeftPaneWidth,
       Tabs.RightPaneWidth,
       Tabs.IsVisible,
       Tabs.ParentId,
       'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end
from   Tabs
inner join Portals on Portals.PortalID = @PortalID
inner join Users on Portals.AdministratorId = Users.UserId
where  TabID = @TabID

/* Get Tabs list */
select TabName,
       AuthorizedRoles,
       TabID,
       TabOrder,
       IsVisible,
       ParentId,
       'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end
from   Tabs
where  PortalID = @PortalId
and    TabOrder <> 0
order  by TabOrder

/* Get Mobile Tabs list */
select MobileTabName,
       AuthorizedRoles,
       TabID,
       IsVisible,
       ParentId,
       'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end
from   Tabs
where  PortalID = @PortalID
and    TabOrder <> 0
and    ShowMobile = 1
order  by TabOrder

/* Then, get the DataTable of module info */
select Modules.*, ModuleDefinitions.*
from   Modules
inner join ModuleDefinitions on Modules.ModuleDefID = ModuleDefinitions.ModuleDefID
inner join Tabs on Modules.TabID = Tabs.TabID
where  Modules.TabID = @TabID
or     (Modules.AllTabs = 1 and Tabs.PortalID = @PortalID)
order by ModuleOrder

GO

ALTER TABLE dbo.Roles
	DROP CONSTRAINT FK_Roles_Portals
GO

ALTER TABLE dbo.Roles
	DROP CONSTRAINT FK_Roles_CodeFrequency
GO

ALTER TABLE dbo.Roles
	DROP CONSTRAINT DF_Roles_ServiceFee
GO

CREATE TABLE dbo.Tmp_Roles
	(
	RoleID int NOT NULL IDENTITY (0, 1),
	PortalID int NULL,
	RoleName nvarchar(50) NOT NULL,
	Description nvarchar(1000) NULL,
	ServiceFee money NULL,
	BillingFrequency char(1) NULL,
	TrialPeriod int NULL,
	TrialFrequency char(1) NULL
	)  ON [PRIMARY]
GO

ALTER TABLE dbo.Tmp_Roles ADD CONSTRAINT
	DF_Roles_ServiceFee DEFAULT (0) FOR ServiceFee
GO

SET IDENTITY_INSERT dbo.Tmp_Roles ON
GO

IF EXISTS(SELECT * FROM dbo.Roles)
	 EXEC('INSERT INTO dbo.Tmp_Roles (RoleID, PortalID, RoleName, Description, ServiceFee, BillingFrequency, TrialPeriod, TrialFrequency) SELECT RoleID, PortalID, RoleName, Description, CONVERT(money, ServiceFee), BillingFrequency, TrialPeriod, TrialFrequency FROM dbo.Roles TABLOCKX')
GO

SET IDENTITY_INSERT dbo.Tmp_Roles OFF
GO

ALTER TABLE dbo.UserRoles
	DROP CONSTRAINT FK_UserRoles_Roles
GO

DROP TABLE dbo.Roles
GO

EXECUTE sp_rename N'dbo.Tmp_Roles', N'Roles', 'OBJECT'
GO

ALTER TABLE dbo.Roles ADD CONSTRAINT
	PK_Roles PRIMARY KEY NONCLUSTERED 
	(
	RoleID
	) ON [PRIMARY]

GO

ALTER TABLE dbo.Roles WITH NOCHECK ADD CONSTRAINT
	FK_Roles_CodeFrequency FOREIGN KEY
	(
	BillingFrequency
	) REFERENCES dbo.CodeFrequency
	(
	Code
	) NOT FOR REPLICATION

GO

ALTER TABLE dbo.Roles WITH NOCHECK ADD CONSTRAINT
	FK_Roles_Portals FOREIGN KEY
	(
	PortalID
	) REFERENCES dbo.Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

ALTER TABLE dbo.UserRoles WITH NOCHECK ADD CONSTRAINT
	FK_UserRoles_Roles FOREIGN KEY
	(
	RoleID
	) REFERENCES dbo.Roles
	(
	RoleID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

drop procedure AddRole
GO

create procedure AddRole

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

as

insert into Roles(
  PortalID,
  RoleName,
  Description,
  ServiceFee,
  BillingFrequency,
  TrialPeriod,
  TrialFrequency
)
values (
  @PortalID,
  @RoleName,
  @Description,
  @ServiceFee,
  @BillingFrequency,
  @TrialPeriod,
  @TrialFrequency
)

GO

drop procedure UpdateRole
GO

create procedure UpdateRole
@RoleID           int,
@RoleName         nvarchar(50),
@Description      nvarchar(1000) = null,
@ServiceFee       money = 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

drop procedure GetRoleMembership
GO

create procedure GetRoleMembership
    
@PortalId int,
@RoleId   int = null,
@UserId   int = null

as

if @RoleId is null
begin
  select UserRoles.UserRoleID,
         UserRoles.UserId,
         'FullName' = Users.FirstName + ' ' + Users.LastName,
         Users.Email,
         UserRoles.RoleId,
         Roles.RoleName,
         UserRoles.ExpiryDate
  from   UserRoles
  inner join Users On Users.UserId = UserRoles.UserId
  inner join Roles On Roles.RoleId = UserRoles.RoleId
  where  Roles.PortalId = @PortalId
  and    UserRoles.UserId = @UserId
end
else
begin
  select UserRoles.UserRoleID,
         UserRoles.UserId,
         'FullName' = Users.FirstName + ' ' + Users.LastName,
         Users.Email,
         UserRoles.RoleId,
         Roles.RoleName,
         UserRoles.ExpiryDate
  from   UserRoles
  inner join Users On Users.UserId = UserRoles.UserId
  inner join Roles On Roles.RoleId = UserRoles.RoleId
  where  Roles.PortalId = @PortalId
  and    UserRoles.RoleId = @RoleId
end

GO

drop procedure GetServices
GO

create procedure GetServices
    
@PortalId  int,
@UserId    int = null

as

select RoleID,
       Roles.RoleName,
       Roles.Description,
       Roles.ServiceFee,
       'BillingFrequency' = C1.Description,
       Roles.TrialPeriod,
       'TrialFrequency' = case when C2.Code <> 0 then C2.Description else '' end,
       'ExpiryDate' = ( select ExpiryDate from UserRoles where UserRoles.RoleId = Roles.RoleID and UserRoles.UserID = @UserID )
from   Roles
inner join CodeFrequency C1 on Roles.BillingFrequency = C1.Code
left outer join CodeFrequency C2 on Roles.TrialFrequency = C2.Code
where  Roles.PortalId = @PortalId
and    Roles.ServiceFee is not null

GO

DELETE FROM CodeCurrency
GO
INSERT INTO [dbo].[CodeCurrency] ([Code], [Description]) VALUES ('CAD', 'Canadian Dollars (CAD)')
GO
INSERT INTO [dbo].[CodeCurrency] ([Code], [Description]) VALUES ('EUR', 'Euros (EUR)')
GO
INSERT INTO [dbo].[CodeCurrency] ([Code], [Description]) VALUES ('GBP', 'Pounds Sterling (GBP)')
GO
INSERT INTO [dbo].[CodeCurrency] ([Code], [Description]) VALUES ('JPY', 'Yen (JPY)')
GO
INSERT INTO [dbo].[CodeCurrency] ([Code], [Description]) VALUES ('USD', 'U.S. Dollars (USD)')
GO

/************************************************************/
/*****              Upgrade Script 1.0.4                *****/
/************************************************************/