01.00.06.sqldataprovider

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

  insert into UserRoles (
    UserId,
    RoleId,
    ExpiryDate
  )
  values (
    @UserId,
    @RegisteredRoleId, /* Registered */
    null
  )
end

update Portals
set    AdministratorId = @UserId,
       AdministratorRoleId = @AdministratorRoleId,
       RegisteredRoleId = @RegisteredRoleId
where  PortalID = @PortalID

if @@error <> 0
  rollback transaction
else
  commit transaction

GO

drop procedure UpdatePortalInfo
GO

create procedure UpdatePortalInfo

@PortalID           int,
@PortalName         nvarchar(128),
@PortalAlias        nvarchar(200) = null,
@LogoFile           nvarchar(50) = null,
@FooterText         nvarchar(100) = null,
@ExpiryDate         datetime = null,
@UserRegistration   int = null,
@BannerAdvertising  int = null,
@Currency           char(3) = null,
@AdministratorId    int = null,
@HostFee            money = 0,
@HostSpace          int = null,
@PaymentProcessor   nvarchar(50) = null,
@ProcessorUserId    nvarchar(50) = null,
@ProcessorPassword  nvarchar(50) = null,
@Description        nvarchar(500) = null,
@KeyWords           nvarchar(500) = null,
@BackgroundFile     nvarchar(50) = null,
@SiteLogHistory     int = null

as

update Portals
set    PortalName = @PortalName,
       PortalAlias = isnull(@PortalAlias,PortalAlias),
       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
where  PortalID = @PortalID

GO

drop procedure AddTab
GO

create procedure AddTab

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

as

if @ParentId is not null
begin
  select @IsVisible = 1
end

insert into Tabs (
    PortalID,
    TabName,
    ShowMobile,
    MobileTabName,
    AuthorizedRoles,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    ParentId,
    IconFile,
    AdministratorRoles
)
values (
    @PortalID,
    @TabName,
    @ShowMobile,
    @MobileTabName,
    @AuthorizedRoles,
    @LeftPaneWidth,
    @RightPaneWidth,
    @IsVisible,
    @ParentId,
    @IconFile,
    @AdministratorRoles
)

select @TabID = @@IDENTITY

GO

drop procedure UpdateTab
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,
@ParentId           int,
@IconFile           nvarchar(100),
@AdministratorRoles nvarchar(256)

as

declare @PortalID int

select @PortalID = PortalID
from   Tabs
where  TabID = @TabID

/* hierarchical tabs must be visible */
if (exists ( select 1 from Tabs where ParentId = @TabId )) or (@ParentId is not null)
begin
  select @IsVisible = 1
end

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

GO

drop procedure GetTabById
GO

create procedure GetTabById

@TabId int

as

select TabID,
       TabOrder,
       PortalID,
       TabName,
       MobileTabName,
       AuthorizedRoles,
       ShowMobile,
       LeftPaneWidth,
       RightPaneWidth,
       IsVisible,
       ParentId,
       Level,
       IconFile,
       AdministratorRoles
from   Tabs
where  TabId = @TabId

GO

drop procedure GetTabs
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,
       Level,
       IconFile,
       AdministratorRoles,
       '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 GetTabsByParentId
GO

create procedure GetTabsByParentId

@ParentId int

as

select TabID,
       TabOrder,
       PortalID,
       TabName,
       MobileTabName,
       AuthorizedRoles,
       ShowMobile,
       LeftPaneWidth,
       RightPaneWidth,
       IsVisible,
       Level,
       IconFile,
       AdministratorRoles
from   Tabs
where  ParentId = @ParentId
order by TabOrder

GO

CREATE TABLE dbo.CodeProcessor
	(
	Processor nvarchar(50) NOT NULL,
	URL nvarchar(250) NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE CodeProcessor ADD CONSTRAINT
	PK_CodeProcessor PRIMARY KEY CLUSTERED 
	(
	Processor
	) ON [PRIMARY]

GO

if not exists ( select 1 from CodeProcessor where Processor = 'PayPal' )
begin
  insert into CodeProcessor ( Processor, URL ) values ( 'PayPal', 'http://www.paypal.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'Authorize.net', 'http://www.authorize.net' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'Authorize.net 3.1 with eCHECK', 'http://www.authorize.net' )    
  insert into CodeProcessor ( Processor, URL ) values ( 'Itransact', 'http://www.itransact.com' )   
  insert into CodeProcessor ( Processor, URL ) values ( 'PayReady', 'http://www.PayReady.net' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'PayflowLink', 'http://www.verisign.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'Intellipay', 'http://www.intellipay.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'viaKLIX (Nova)', 'http://www.novainfo.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'Ecx', 'http://www.ecx.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'iBill', 'http://www.ibill.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'eProcessing', 'http://www.eprocessingnetwork.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'PlanetPayment', 'http://www.planetpayment.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'Mcps', 'http://www.merchantcommerce.net' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'Cybercash', 'http://www.cybercash.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'SkipJack', 'http://www.skipjack.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'LinkPoint', 'http://www.linkpoint.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'PayflowPro', 'http://www.verisign.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'NetBilling', 'http://www.netbilling.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'ioNgate', 'http://www.iongate.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'PSiGate', 'http://www.psigate.com' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'PayCom', 'http://www.paycom.net' )  
  insert into CodeProcessor ( Processor, URL ) values ( 'ePoch', 'http://www.ePochsystems.com' )  
end
GO

create procedure GetProcessorCodes

as

select Processor,
       URL
from   CodeProcessor
order by Processor

GO


declare @PayPalId nvarchar(50)

select @PayPalId = SettingValue
from   HostSettings
where  SettingName = 'PayPalId'

if not exists ( select 1 from HostSettings where SettingName = 'PaymentProcessor' )
begin
  insert into HostSettings ( SettingName, SettingValue ) values ( 'PaymentProcessor', 'PayPal' )
  insert into HostSettings ( SettingName, SettingValue ) values ( 'ProcessorUserId', @PayPalId )
  insert into HostSettings ( SettingName, SettingValue ) values ( 'ProcessorPassword', '' )
end

delete 
from HostSettings
where SettingName = 'PayPalId'
GO

ALTER TABLE Links ADD
	Clicks int NOT NULL CONSTRAINT DF_Links_Clicks DEFAULT 0
GO

update Links
set    Clicks = 0
where  Clicks is null
GO

ALTER TABLE Documents ADD
	Clicks int NOT NULL CONSTRAINT DF_Documents_Clicks DEFAULT 0
GO

update Documents
set    Clicks = 0
where  Clicks is null
GO

ALTER TABLE Announcements ADD
	Clicks int NOT NULL CONSTRAINT DF_Announcements_Clicks DEFAULT 0
GO

update Announcements
set    Clicks = 0
where  Clicks is null
GO

CREATE TABLE dbo.ClickLog
	(
	ClickLogId int NOT NULL IDENTITY (1, 1),
	TableName nvarchar(50) NOT NULL,
        ItemId int NOT NULL,
	DateTime datetime NOT NULL,
	UserId int NULL
	)  ON [PRIMARY]
GO

ALTER TABLE ClickLog ADD CONSTRAINT
	PK_ClickLog PRIMARY KEY CLUSTERED 
	(
	ClickLogId
	) ON [PRIMARY]

GO

create procedure UpdateClicks

@TableName nvarchar(50),
@KeyField  nvarchar(50),
@ItemId    int,
@UserId    int = null

as

declare @SQL nvarchar(200)

select @SQL = 'update ' + @TableName + ' set Clicks = Clicks + 1 where ' + @KeyField + ' = ' + convert(varchar,@ItemId)

EXEC sp_executesql @SQL

insert into ClickLog (
  TableName,
  ItemId,
  DateTime,
  UserId
)
values (
  @TableName,
  @ItemId,
  getdate(),
  @UserId
)

GO

drop procedure GetSingleAnnouncement
GO

create procedure GetSingleAnnouncement

@ItemID   int,
@ModuleId int

as

select Title,
       URL,
       Syndicate,
       ExpireDate,
       Description,
       Clicks,
       'CreatedByUser' = Users.FirstName + ' ' + Users.LastName,
       Announcements.CreatedDate
from   Announcements
left outer join Users on Announcements.CreatedByUser = Users.UserID
where  ItemID = @ItemID
and    ModuleId = @ModuleId

GO

drop procedure GetSingleLink
GO

create procedure GetSingleLink

@ItemID   int,
@ModuleId int

as

select Title,
       Url,
       MobileUrl,
       ViewOrder,
       Description,
       NewWindow,
       Clicks,
       'CreatedByUser' = Users.FirstName + ' ' + Users.LastName,
       Links.CreatedDate
from   Links
left outer join Users on Links.CreatedByUser = Users.UserID
where  ItemID = @ItemID
and    ModuleId = @ModuleId

GO

drop procedure GetSingleDocument
GO

create procedure GetSingleDocument

@ItemID   int,
@ModuleId int

as

select Title,
       URL,
       Category,
       Syndicate,
       Clicks,
       'CreatedByUser' = Users.FirstName + ' ' + Users.LastName,
       Documents.CreatedDate
from   Documents
left outer join Users on Documents.CreatedByUser = Users.UserID
where  ItemID = @ItemID
and    ModuleId = @ModuleId

GO

create procedure GetClicks

@TableName nvarchar(50),
@ItemId    int

as

select DateTime,
       'FullName' = Users.FirstName + ' ' + Users.LastName
from   ClickLog
left outer join Users on ClickLog.UserId = Users.UserId
where  TableName = @TableName
and    ItemId = @ItemId
order by DateTime desc

go

update ModuleDefinitions
set    EditSrc = null
where  FriendlyName = 'Site Log'
GO

drop procedure GetSiteLog
GO

create procedure GetSiteLog

@PortalId   int,
@PortalAlias nvarchar(50),
@ReportType int = null,
@StartDate  datetime = null,
@EndDate    datetime = null

as

if @StartDate is null
  select @StartDate = min(DateTime) from SiteLog where PortalId = @PortalId

if @EndDate is null
  select @EndDate = max(DateTime) from SiteLog where PortalId = @PortalId

if @ReportType = 1 /* page views per day */
begin
  select 'Date' = convert(varchar,DateTime,102),
         'Views' = count(*),
         'Visitors' = count(distinct SiteLog.UserHostAddress),
         'Users' = count(distinct SiteLog.UserId)
  from   SiteLog
  where  PortalId = @PortalId