01.00.08.sqldataprovider

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

    @LeftPaneWidth,
    @RightPaneWidth,
    @IsVisible,
    @ParentId,
    @IconFile,
    @AdministratorRoles
)

select @TabID = @@IDENTITY

GO

create procedure dbo.AddUser

@PortalId       int,
@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),
@Authorized     bit,
@UserID	int	OUTPUT

as

select @UserID = null

select	@UserID = UserID
from 	Users
where	Username = @Username 

if @UserID is null
begin
  insert into 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 @UserID = @@IDENTITY
end

if not exists ( select 1 from UserPortals where UserId = @UserId and PortalId = @PortalId )
begin
  insert into UserPortals (
    UserId,
    PortalId,
    Authorized,
    CreatedDate
  )
  values (
    @UserId,
    @PortalId,
    @Authorized,
    getdate()
  )
end

GO

create procedure dbo.AddUserDefinedField

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

as

declare @FieldOrder int

select @FieldOrder = count(*) + 1
from   UserDefinedFields
where  ModuleId = @ModuleId

insert UserDefinedFields ( 
  ModuleId,
  FieldTitle,
  Visible,
  FieldType
)
values (
  @ModuleId,
  @FieldTitle,
  @Visible,
  @FieldType
)

GO

create procedure dbo.AddUserDefinedRow

@ModuleId         int,
@UserDefinedRowId int OUTPUT

as

insert UserDefinedRows ( 
  ModuleId
)
values (
  @ModuleId
)

select @UserDefinedRowId = @@IDENTITY

GO

create procedure dbo.AddUserRole

@PortalID   int,
@UserID     int,
@RoleID     int,
@ExpiryDate datetime = null

as

declare @UserRoleID int

select @UserRoleID = null

select @UserRoleID = UserRoleID
from   UserRoles
inner join UserPortals on UserPortals.UserId = @UserID
where  UserRoles.UserID = @UserID
and    UserRoles.RoleID = @RoleID
and    PortalID = @PortalID
 
if @UserRoleID is not null
begin
  update UserRoles
  set    ExpiryDate = @ExpiryDate
  where  UserRoleId = @UserRoleID
end
else
begin
  insert UserRoles (
    UserID,
    RoleID,
    ExpiryDate
  )
  values (
    @UserID,
    @RoleID,
    @ExpiryDate
  )
end

GO

create procedure dbo.AddVendor

@PortalID 	int,
@VendorName 	nvarchar(50),
@Unit    	nvarchar(50),
@Street 	nvarchar(50),
@City		nvarchar(50),
@Region	        nvarchar(50),
@Country	nvarchar(50),
@PostalCode	nvarchar(50),
@Telephone	nvarchar(50),
@Fax   	        nvarchar(50),
@Email    	nvarchar(50),
@Website	nvarchar(100),
@Contact	nvarchar(50),
@UserName       nvarchar(100),
@LogoFile       nvarchar(100),
@KeyWords       text,
@VendorID	int OUTPUT

as

insert into Vendors (
  VendorName,
  Unit,
  Street,
  City,
  Region,
  Country,
  PostalCode,
  Telephone,
  PortalId,
  Fax,
  Email,
  Website,
  Contact,
  ClickThroughs,
  Views,
  CreatedByUser,
  CreatedDate,
  LogoFile,
  KeyWords
)
values (
  @VendorName,
  @Unit,
  @Street,
  @City,
  @Region,
  @Country,
  @PostalCode,
  @Telephone,
  @PortalID,
  @Fax,
  @Email,
  @Website,
  @Contact,
  0,
  0,
  @UserName,
  getdate(), 
  @LogoFile,
  @KeyWords
)
select @VendorID = @@IDENTITY

GO

create procedure dbo.AddVendorClassification

@VendorId           int,
@ClassificationId   int

as

insert VendorClassification ( 
  VendorId,
  ClassificationId
)
values (
  @VendorId,
  @ClassificationId
)

GO

create procedure dbo.CopyTab

@FromTabId       int,
@ToTabId         int

as

declare @ModuleId int

select @ModuleId = min(ModuleId)
from   Modules
where  TabId = @FromTabId

while @ModuleId is not null
begin
  insert into Modules ( TabId, ModuleDefID, ModuleOrder, PaneName, ModuleTitle, AuthorizedEditRoles, CacheTime, ShowMobile, AuthorizedViewRoles, Alignment, Color, Border, IconFile )
    select @ToTabId, ModuleDefID, ModuleOrder, PaneName, ModuleTitle, AuthorizedEditRoles, CacheTime, ShowMobile, AuthorizedViewRoles, Alignment, Color, Border, IconFile
    from   Modules
    where  ModuleId = @ModuleId
  select @ModuleId = min(ModuleId)
  from   Modules
  where  TabId = @FromTabId
  and    ModuleId > @ModuleId
end

GO

create procedure dbo.DeleteAnnouncement

@ItemID int

as

delete
from   Announcements
where  ItemID = @ItemID

GO

create procedure dbo.DeleteBanner

@BannerId int

as

delete
from   Banners
where  BannerId = @BannerId

GO

create procedure dbo.DeleteContact

@ItemID int

as

delete
from   Contacts
where  ItemID = @ItemID

GO

create procedure dbo.DeleteDocument

@ItemID int

as

delete
from   Documents
where  ItemID = @ItemID

GO

create procedure dbo.DeleteFAQ

@ItemID int

as

delete
from   FAQs
where  ItemID = @ItemID

GO

create procedure dbo.DeleteFile

@FileName nvarchar(100),
@PortalId int

as

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

GO

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

GO

create procedure dbo.DeleteLink

@ItemID int

as

delete
from   Links
where  ItemID = @ItemID

GO

create procedure dbo.DeleteMessage

@ItemId int

as

declare @ModuleId int
declare @Start int
declare @Parent nvarchar(23)

select @ModuleId = ModuleId,
       @Start = len(DisplayOrder) - 22,
       @Parent = substring(DisplayOrder,len(DisplayOrder) - 22,23)
from   Discussion
where  ItemId = @ItemId

delete
from   Discussion
where  ModuleId = @ModuleId
and    substring(DisplayOrder, @Start,23) = @Parent

GO

create procedure dbo.DeleteModule

@ModuleID       int

as

delete
from   Modules 
where  ModuleID = @ModuleID

GO

create procedure dbo.DeleteModuleDefinition

@ModuleDefID int

as

delete
from   ModuleDefinitions
where  ModuleDefID = @ModuleDefID

GO

create procedure dbo.DeleteModuleEvent

@ItemID int

as

delete
from   ModuleEvents
where  ItemID = @ItemID

GO

create procedure dbo.DeletePortalInfo

@PortalID int

as

delete
from   Portals
where  PortalID = @PortalID

GO

create procedure dbo.DeleteRole

@RoleID int

as

if @RoleID <> 0 /* Admins Role */
begin
  delete 
  from   Roles
  where  RoleID = @RoleID
end

GO

create procedure dbo.DeleteSearch

@SearchID int

as

delete
from   Search
where  SearchId = @SearchId

GO

create procedure dbo.DeleteTab

@TabID int

as

if not exists ( select 1 from Tabs where ParentId = @TabID )
begin 
  delete
  from   Tabs
  where  TabID = @TabID
end

GO

create procedure dbo.DeleteUser

@PortalId int,
@UserID   int

as

declare @RoleId int

if not exists ( select 1 from Portals where AdministratorId = @UserID )
begin
  delete
  from   UserPortals
  where  PortalId = @PortalId
  and    UserID = @UserID

  select @RoleId = min(RoleId)
  from   Roles
  where  PortalId = @PortalId
  while @RoleId is not null
  begin
    delete
    from   UserRoles
    where  UserId = @UserId
    and    RoleId = @RoleId
    select @RoleId = min(RoleId)
    from   Roles
    where  PortalId = @PortalId
    and    RoleId > @RoleId
  end

  if not exists ( select 1 from UserPortals where UserId = @UserID )