01.00.08.sqldataprovider

SharpNuke源代码

  begin
    delete
    from   Users
    where  UserID = @UserID
  end
end

GO

create procedure dbo.DeleteUserDefinedField

@UserDefinedFieldId    int 

as

delete 
from   UserDefinedData
where  UserDefinedFieldId = @UserDefinedFieldId

delete 
from   UserDefinedFields
where  UserDefinedFieldId = @UserDefinedFieldId

GO

create procedure dbo.DeleteUserDefinedRow

@UserDefinedRowId    int 

as

delete 
from   UserDefinedData
where  UserDefinedRowId = @UserDefinedRowId

delete 
from   UserDefinedRows
where  UserDefinedRowId = @UserDefinedRowId

GO

create procedure dbo.DeleteUserRole

@UserRoleID int

as

declare @UserID int
declare @RoleID int
declare @AdministratorRoleId int

select @UserID = UserID,
       @RoleID = RoleID
from   UserRoles
where  UserRoleID = @UserRoleID

select @AdministratorRoleId = AdministratorRoleId
from   Roles
inner join Portals on Roles.PortalID = Portals.PortalID
where  RoleID = @RoleID

/* do not remove Administrators role from Portal Administrator */
if not exists ( select 1 from Portals where AdministratorId = @UserID and @RoleID = @AdministratorRoleId ) 
begin
  delete
  from   UserRoles
  where  UserRoleID = @UserRoleID
end

GO

create procedure dbo.DeleteVendor

@VendorID int

as

delete
from   Vendors
where  VendorID = @VendorID

GO

create procedure dbo.DeleteVendorClassifications

@VendorId  int

as

delete
from   VendorClassification
where  VendorId = @VendorId

GO

create procedure dbo.DeleteVendorFeedback

@VendorId int,
@UserId   int

as

delete
from   VendorFeedback
where  VendorId = @VendorId
and    UserId = @UserId

GO

create procedure dbo.FindBanners

@DisplayPortalId int,
@BannerTypeId int = null,
@SelectPortalId int = null,
@Banners  int = 1

as

declare @RecordCounter int
declare @RandomRecord int
declare @BannerId int
declare @StartDate smalldatetime
declare @EndDate smalldatetime
declare @Views int
declare @Impressions int
declare @VendorId int
declare @SiteLogHistory int

if @BannerTypeId is null
begin
  select @BannerTypeId = BannerTypeId
  from   BannerTypes
  where  BannerTypeName = 'Banner'
end

/* purge vendor log */
select @SiteLogHistory = SiteLogHistory
from   Portals
where  PortalID = @DisplayPortalId

if @SiteLogHistory is not null
begin
  delete
  from   VendorLog
  where  PortalID = @DisplayPortalId
  and    datediff(day,DateTime,getdate()) > @SiteLogHistory
end

/* find number of banners */
select @RecordCounter = count(*)
from   Banners
inner join Vendors on Banners.VendorId = Vendors.VendorId
where  Banners.BannerTypeId = @BannerTypeId
and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )

if @Banners > @RecordCounter
begin
  select @Banners = @RecordCounter
end

/* generate random number */
select @RandomRecord = Round(RAND() * (@RecordCounter - @Banners + 1),0)
if @RandomRecord = 0
begin
  select @RandomRecord = 1
end

/* move record pointer to random record */
select @RecordCounter = 1

select @BannerId = min(Banners.BannerId)
from   Banners
inner join Vendors on Banners.VendorId = Vendors.VendorId
where  Banners.BannerTypeId = @BannerTypeId
and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )

while @BannerId is not null and @RecordCounter <> @RandomRecord
begin
  select @BannerId = min(Banners.BannerId)
  from   Banners
  inner join Vendors on Banners.VendorId = Vendors.VendorId
  where  Banners.BannerTypeId = @BannerTypeId
  and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
  and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
  and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
  and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )
  and    Banners.BannerId > @BannerId

  select @RecordCounter = @RecordCounter + 1
end

/* return matching banners */
set rowcount @Banners

if @SelectPortalId is null
begin
  select BannerId,
         BannerName,
         ImageFile
  from   Banners
  inner join Vendors on Banners.VendorId = Vendors.VendorId
  where  Banners.BannerTypeId = @BannerTypeId
  and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
  and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
  and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
  and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )
  and    BannerId >= @BannerId
end
else
begin
  select BannerId,
         BannerName,
         ImageFile
  from   Banners
  inner join Vendors on Banners.VendorId = Vendors.VendorId
  where  Banners.BannerTypeId = @BannerTypeId
  and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
  and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
  and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
  and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )
  and    BannerId >= @BannerId
end

set rowcount 0

/* update banners */
select @RecordCounter = 0

while @RecordCounter < @Banners
begin
  update Banners
  set    Views = Views + 1
  where  BannerId = @BannerId

  select @vendorId = VendorId
  from   Banners
  where  BannerId = @BannerId

  insert VendorLog (
    DateTime,
    PortalId,
    VendorId,
    BannerId,
    Search
  )
  values (
    getdate(),    @DisplayPortalId,
    @VendorId,
    @BannerId,
    null
  ) 

  select @StartDate = StartDate,
         @EndDate = EndDate,
         @Views = Views,
         @Impressions = Impressions
  from   Banners
  where  BannerId = @BannerId

  if @StartDate is null
    select @StartDate = getdate()
  if @Views = @Impressions
    select @EndDate = getdate()

  update Banners
  set    StartDate = @StartDate,
         EndDate = @EndDate
  where  BannerId = @BannerId

  select @BannerId = min(Banners.BannerId)
  from   Banners
  inner join Vendors on Banners.VendorId = Vendors.VendorId
  where  Banners.BannerTypeId = @BannerTypeId
  and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
  and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
  and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
  and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )
  and    Banners.BannerId > @BannerId

  select @RecordCounter = @RecordCounter + 1
end


GO

create procedure dbo.FindVendors

@DisplayPortalId  int,
@SelectPortalId   int = null,
@Search           nvarchar(200) = null

as

if @Search is not null
begin
  insert VendorSearch (
    PortalId,
    DateTime,
    Search
  )
  values (
    @DisplayPortalId,
    getdate(),
    @Search
  )
end

if @SelectPortalId is null
begin
  update Vendors
  set    Views = Views + 1
  where  VendorId in (
    select distinct Vendors.VendorId
    from Vendors
    left outer join VendorClassification on Vendors.VendorId = VendorClassification.VendorId
    left outer join Classification on VendorClassification.ClassificationId = Classification.ClassificationId
    where  PortalId is null
    and    (VendorName like '%' + @Search + '%' or KeyWords like '%' + @Search + '%' or ClassificationName like '%' + @Search + '%')
  )
  insert VendorLog ( VendorId, DateTime, PortalId, BannerId, Search ) 
    select distinct Vendors.VendorId, getdate(), @DisplayPortalId, null, @Search
    from   Vendors
    left outer join VendorClassification on Vendors.VendorId = VendorClassification.VendorId
    left outer join Classification on VendorClassification.ClassificationId = Classification.ClassificationId
    where  PortalId is null
    and    (VendorName like '%' + @Search + '%' or KeyWords like '%' + @Search + '%' or ClassificationName like '%' + @Search + '%')
  select distinct Vendors.VendorId,
         VendorName,
         Unit,
         Street,
         City,
         Region,
         Country,
         PostalCode,
         Telephone,
         PortalId,
         Fax,
         Email,
         Website,
         Contact,
         LogoFile,
         'Feedback' = ( select sum(Value) from VendorFeedback where VendorFeedback.VendorId = Vendors.VendorId )
  from   Vendors
  left outer join VendorClassification on Vendors.VendorId = VendorClassification.VendorId
  left outer join Classification on VendorClassification.ClassificationId = Classification.ClassificationId
  where  PortalId is null
  and    (VendorName like '%' + @Search + '%' or KeyWords like '%' + @Search + '%' or ClassificationName like '%' + @Search + '%')
end
else
begin
  update Vendors
  set    Views = Views + 1
  where  VendorId in (
    select distinct Vendors.VendorId
    from Vendors
    left outer join VendorClassification on Vendors.VendorId = VendorClassification.VendorId
    left outer join Classification on VendorClassification.ClassificationId = Classification.ClassificationId
    where  PortalId = @SelectPortalId
    and    (VendorName like '%' + @Search + '%' or KeyWords like '%' + @Search + '%' or ClassificationName like '%' + @Search + '%')
  )
  insert VendorLog ( VendorId, DateTime, PortalId, BannerId, Search ) 
    select distinct Vendors.VendorId, getdate(), @DisplayPortalId, null, @Search
    from   Vendors
    left outer join VendorClassification on Vendors.VendorId = VendorClassification.VendorId
    left outer join Classification on VendorClassification.ClassificationId = Classification.ClassificationId
    where  PortalId = @SelectPortalId
    and    (VendorName like '%' + @Search + '%' or KeyWords like '%' + @Search + '%' or ClassificationName like '%' + @Search + '%')
  select distinct Vendors.VendorId,
         VendorName,
         Unit,
         Street,
         City,
         Region,
         Country,
         PostalCode,
         Telephone,
         PortalId,
         Fax,
         Email,
         Website,
         Contact,
         LogoFile,
         'Feedback' = ( select sum(Value) from VendorFeedback where VendorFeedback.VendorId = Vendors.VendorId )
  from   Vendors
  left outer join VendorClassification on Vendors.VendorId = VendorClassification.VendorId
  left outer join Classification on VendorClassification.ClassificationId = Classification.ClassificationId
  where  PortalId = @SelectPortalId
  and    (VendorName like '%' + @Search + '%' or KeyWords like '%' + @Search + '%' or ClassificationName like '%' + @Search + '%')
end

GO

create procedure dbo.GetAnnouncements

@ModuleID int

as

select ItemID,
       CreatedByUser,
       CreatedDate,
       Title,
       URL,
       Syndicate,
       ExpireDate,
       Description,
       ViewOrder
from   Announcements
where  ModuleID = @ModuleID
and    (ExpireDate > GetDate() or ExpireDate is null)
order by ViewOrder

GO

create procedure dbo.GetAuthRoles

@PortalID    int,
@ModuleID    int

as

select Tabs.AuthorizedRoles,
       Modules.AuthorizedEditRoles
from   Modules
inner join Tabs ON Modules.TabID = Tabs.TabID
where  Modules.ModuleID = @ModuleID
and    Tabs.PortalID = @PortalID

GO

create procedure dbo.GetBannerClickThrough

@BannerId int

as

update Banners
set    ClickThroughs = ClickThroughs + 1
where  BannerId = @BannerId

select URL,
       VendorId
from   Banners
where  BannerId = @BannerId

GO

create procedure dbo.GetBannerLog

@BannerId  int

as

select 'LogDate' = convert(varchar,DateTime,102),
       'Views' = count(*)
from   VendorLog
where  BannerId = @BannerId
group by convert(varchar,DateTime,102)
order by LogDate desc

GO

create procedure dbo.GetBannerTypes

as

select BannerTypeId,
       BannerTypeName
from   BannerTypes

GO

create procedure dbo.GetBanners

@VendorId int

as

select BannerId,
       BannerName,
       BannerTypeName,
       URL,
       Impressions,
       CPM,
       Views,
       ClickThroughs,
       StartDate,
       EndDate
from   Banners
inner join BannerTypes on Banners.BannerTypeId = BannerTypes.BannerTypeId
where  VendorId = @VendorId
order  by CreatedDate desc

GO

create procedure dbo.GetBillingFrequencyCode
    
@Code char(1)

as

select Description
from   CodeFrequency
where  Code = @Code

GO

create procedure dbo.GetBillingFrequencyCodes
    
as

select *
from   CodeFrequency

GO

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

create procedure dbo.GetContacts

@ModuleID int

as

select ItemID,
       CreatedDate,
       CreatedByUser,
       Name,
       Role,
       Email,
       Contact1,
       Contact2
from   Contacts
where