03.00.08.sqldataprovider

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

values (
    @VendorId,
    @ImageFile,
    @BannerName,
    @URL,
    @Impressions,
    @CPM,
    0,
    0,
    @StartDate,
    @EndDate,
    @UserName,
    getdate(),
    @BannerTypeId,
    @Description,
    @GroupName,
    @Criteria
)

select SCOPE_IDENTITY()

GO

drop procedure {databaseOwner}{objectQualifier}UpdateBanner
GO

create procedure {databaseOwner}{objectQualifier}UpdateBanner

@BannerId     int,
@BannerName   nvarchar(100),
@ImageFile    nvarchar(50),
@URL          nvarchar(255),
@Impressions  int,
@CPM          float,
@StartDate    datetime,
@EndDate      datetime,
@UserName     nvarchar(100),
@BannerTypeId int,
@Description  nvarchar(2000),
@GroupName    nvarchar(100),
@Criteria     bit

as

update {objectQualifier}Banners
set    ImageFile     = @ImageFile,
       BannerName    = @BannerName,
       URL           = @URL,
       Impressions   = @Impressions,
       CPM           = @CPM,
       StartDate     = @StartDate,
       EndDate       = @EndDate,
       CreatedByUser = @UserName,
       CreatedDate   = getdate(),
       BannerTypeId  = @BannerTypeId,
       Description   = @Description,
       GroupName     = @GroupName,
       Criteria      = @Criteria
where  BannerId = @BannerId 

GO

update {objectQualifier}Banners
set    GroupName = 'Site Banner'
where  BannerTypeId = 1
and    GroupName is null
GO

drop procedure {databaseOwner}{objectQualifier}GetAffiliate
GO

create procedure {databaseOwner}{objectQualifier}GetAffiliate

@AffiliateId int,
@VendorId int,
@PortalID int

as

select {objectQualifier}Affiliates.AffiliateId,
       {objectQualifier}Affiliates.VendorId,
       {objectQualifier}Affiliates.StartDate,
       {objectQualifier}Affiliates.EndDate,
       {objectQualifier}Affiliates.CPC,
       {objectQualifier}Affiliates.Clicks,
       {objectQualifier}Affiliates.CPA,
       {objectQualifier}Affiliates.Acquisitions
from   {objectQualifier}Affiliates INNER JOIN {objectQualifier}Vendors ON {objectQualifier}Affiliates.VendorId = {objectQualifier}Vendors.VendorId
where  {objectQualifier}Affiliates.AffiliateId = @AffiliateId
and    {objectQualifier}Affiliates.VendorId = @VendorId
and	 {objectQualifier}Vendors.PortalId=@PortalID

GO

drop procedure {databaseOwner}{objectQualifier}GetBanner
GO

CREATE procedure {databaseOwner}{objectQualifier}GetBanner

@BannerId int,
@VendorId int,
@PortalID int

as

select {objectQualifier}Banners.BannerId,
       {objectQualifier}Banners.VendorId,
       {objectQualifier}Banners.ImageFile,
       {objectQualifier}Banners.BannerName,
       {objectQualifier}Banners.Impressions,
       {objectQualifier}Banners.CPM,
       {objectQualifier}Banners.Views,
       {objectQualifier}Banners.ClickThroughs,
       {objectQualifier}Banners.StartDate,
       {objectQualifier}Banners.EndDate,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Banners.CreatedDate,
       {objectQualifier}Banners.BannerTypeId,
       {objectQualifier}Banners.Description,
       {objectQualifier}Banners.GroupName,
       {objectQualifier}Banners.Criteria,
       {objectQualifier}Banners.URL        
FROM   {objectQualifier}Banners INNER JOIN
{objectQualifier}Vendors ON {objectQualifier}Banners.VendorId = {objectQualifier}Vendors.VendorId LEFT OUTER JOIN
{objectQualifier}Users ON {objectQualifier}Banners.CreatedByUser = {objectQualifier}Users.UserID
where  {objectQualifier}Banners.BannerId = @BannerId
and   {objectQualifier}Banners.vendorId = @VendorId
AND {objectQualifier}Vendors.PortalId = @PortalID

GO

UPDATE {databaseOwner}{objectQualifier}tabmodules
SET alignment=NULL
FROM {databaseOwner}{objectQualifier}tabmodules t
JOIN {databaseOwner}{objectQualifier}modules m on t.moduleid=m.moduleid
WHERE m.portalid is null

GO

drop procedure {databaseOwner}{objectQualifier}GetDocument
GO

CREATE procedure {databaseOwner}{objectQualifier}GetDocument

@ItemId   int,
@ModuleId int

as

select {objectQualifier}Documents.Itemid,
       {objectQualifier}Documents.Moduleid,
       {objectQualifier}Documents.Title,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Documents.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Documents.Category,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Files.Size,
       {objectQualifier}Files.ContentType,
       {objectQualifier}Documents.CreatedDate,
       {objectQualifier}UrlTracking.TrackClicks,
       {objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Documents
left outer join {objectQualifier}Users on {objectQualifier}Documents.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}UrlTracking on {objectQualifier}Documents.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
left outer join {objectQualifier}Files on {objectQualifier}Documents.URL = 'fileid=' +
convert(varchar,{objectQualifier}Files.FileID)
where  {objectQualifier}Documents.ItemId = @ItemId
and    {objectQualifier}Documents.ModuleId = @ModuleId

GO

drop procedure {databaseOwner}{objectQualifier}UpdateSearchItem
GO

create procedure {databaseOwner}{objectQualifier}UpdateSearchItem
	@SearchItemID int, 
	@Title varchar(200), 
	@Description nvarchar(2000), 
	@Author int, 
	@PubDate datetime, 
	@ModuleId int, 
	@SearchKey nvarchar(100), 
	@Guid nvarchar(200), 
	@HitCount int, 
	@ImageFileId int
AS

UPDATE {objectQualifier}SearchItem 
SET	[Title] = @Title,
	[Description] = @Description,
	[Author] = @Author,
	[PubDate] = @PubDate,
	[ModuleId] = @ModuleId,
	[SearchKey] = @SearchKey,
	[Guid] = @Guid,
	[HitCount] = @HitCount,
	ImageFileId = 	@ImageFileId
WHERE   [SearchItemID] = @SearchItemID
GO





drop procedure {databaseOwner}{objectQualifier}AddUser
GO

create procedure {databaseOwner}{objectQualifier}AddUser

@PortalID	int,
@Username	nvarchar(100),
@FirstName	nvarchar(50),
@LastName	nvarchar(50),
@AffiliateId    int,
@IsSuperUser    bit

as

declare @UserID int

select @UserID = UserID
from   {objectQualifier}Users
where  Username = @Username

if @UserID is null
begin
  insert into {objectQualifier}Users (
    Username,
    FirstName, 
    LastName, 
    AffiliateId,
    IsSuperUser
  )
  values (
    @Username,
    @FirstName, 
    @LastName, 
    @AffiliateId,
    @IsSuperUser
  )

  select @UserID = SCOPE_IDENTITY()
end

if @IsSuperUser = 0
begin
  if not exists ( select 1 from {objectQualifier}UserPortals where UserID = @UserID and PortalID = @PortalID )
  begin
    insert into {objectQualifier}UserPortals (
      UserID,
      PortalID
    )
    values (
      @UserID,
      @PortalID
    )
  end
end

select @UserID

GO

drop procedure {databaseOwner}{objectQualifier}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
where  UserId = @UserId
and    RoleId = @RoleId
 
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
  )
  values (
    @UserId,
    @RoleId,
    @ExpiryDate
  )

  select SCOPE_IDENTITY()
end

GO

drop procedure {databaseOwner}{objectQualifier}GetPortals
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetPortals

as

select P.*,
       'LogoFile' = case when F1.FileName is null then P.LogoFile else F1.Folder + F1.FileName end,
       'BackGroundFile' = case when F2.FileName is null then P.BackGroundFile else F2.Folder + F2.FileName end,
       'Users' = ( select count(*) from {objectQualifier}UserPortals where {objectQualifier}UserPortals.PortalId = P.PortalId )
from {objectQualifier}Portals P
left outer join {objectQualifier}Files F1 on P.LogoFile = 'fileid=' + convert(varchar,F1.FileID)
left outer join {objectQualifier}Files F2 on P.BackGroundFile = 'fileid=' + convert(varchar,F2.FileID)
order by P.PortalName

GO

drop procedure {databaseOwner}{objectQualifier}GetSuperUsers
GO

CREATE procedure {databaseOwner}{objectQualifier}GetSuperUsers

as

select U.*,
       'PortalId' = -1,
       'FullName' = U.FirstName + ' ' + U.LastName
from   {objectQualifier}Users U
where  U.IsSuperUser = 1

GO

drop procedure {databaseOwner}{objectQualifier}GetUser
GO

create procedure {databaseOwner}{objectQualifier}GetUser

@PortalId int,
@UserId int

as

select U.UserId,
       UP.PortalId,
       U.Username,
       'FullName' = U.FirstName + ' ' + U.LastName,
       U.FirstName,
       U.LastName,
       U.IsSuperUser
from {objectQualifier}Users U
left outer join {objectQualifier}UserPortals UP On U.UserId = UP.UserId
where  U.UserId = @UserId
and    (UP.PortalId = @PortalId or U.IsSuperUser = 1)

GO

drop procedure {databaseOwner}{objectQualifier}GetUserByUsername
GO


create procedure {databaseOwner}{objectQualifier}GetUserByUsername

@PortalId int,
@Username nvarchar(100)

as
 
select U.UserId,
       UP.PortalID,
       U.Username,
       'FullName' = U.FirstName + ' ' + U.LastName,
       U.FirstName,
       U.LastName,
       U.IsSuperUser
from {objectQualifier}Users U
left outer join {objectQualifier}UserPortals UP On U.UserId = UP.UserId
where  Username = @Username
and    (UP.PortalId = @PortalId or U.IsSuperUser = 1 or @PortalId is null)

GO

drop procedure {databaseOwner}{objectQualifier}GetUserRole
GO

create procedure {databaseOwner}{objectQualifier}GetUserRole

@PortalId int, 
@UserId int, 
@RoleId int

as

select	r.*, 
        ur.UserRoleID, 
        ur.UserID, 
        ur.ExpiryDate, 
        ur.IsTrialUsed
from	{objectQualifier}UserRoles ur
inner join {objectQualifier}UserPortals up on ur.UserId = up.UserId
inner join {objectQualifier}Roles r on r.RoleID = ur.RoleID
where   up.UserId = @UserId
and     up.PortalId = @PortalId
and     ur.RoleId = @RoleId

GO

drop procedure {databaseOwner}{objectQualifier}GetUserRolesByUsername
GO


CREATE procedure {databaseOwner}{objectQualifier}GetUserRolesByUsername

@PortalId int, 
@Username nvarchar(100), 
@Rolename nvarchar(50)

as

SELECT	R.*,        
        'FullName' = U.FirstName + ' ' + U.LastName,
        UR.UserRoleID,
        UR.UserID,
        UR.ExpiryDate,
        UR.IsTrialUsed
FROM	{objectQualifier}UserRoles UR
INNER JOIN {objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {objectQualifier}UserPortals UP ON U.UserID = UP.UserID
INNER JOIN {objectQualifier}Roles R ON R.RoleID = UR.RoleID
WHERE  UP.PortalId = @PortalId
AND    (U.Username = @Username or @Username is NULL)
AND    (R.Rolename = @Rolename or @RoleName is NULL)


GO

drop procedure {databaseOwner}{objectQualifier}GetUsers
GO

create procedure {databaseOwner}{objectQualifier}GetUsers

@PortalId int

as

select *
from {objectQualifier}Users U
left join {objectQualifier}UserPortals UP on U.UserId = UP.UserId
where ( UP.PortalId = @PortalId or @PortalId is null )
order by U.FirstName + ' ' + U.LastName   

GO

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/