02.02.02.sqldataprovider

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

GO

drop procedure {databaseOwner}{objectQualifier}AddUrlLog
GO

create procedure {databaseOwner}{objectQualifier}AddUrlLog

@UrlTrackingID int,
@UserID        int

as

insert into {objectQualifier}UrlLog (
  UrlTrackingID,
  ClickDate,
  UserID
)
values (
  @UrlTrackingID,
  getdate(),
  @UserID
)

GO

drop procedure {databaseOwner}{objectQualifier}DeleteUrlTracking
GO

create procedure {databaseOwner}{objectQualifier}DeleteUrlTracking

@PortalID     int,
@Url          nvarchar(255),
@ModuleID     int

as

delete
from   {objectQualifier}UrlTracking
where  PortalID = @PortalID
and    Url = @Url
and    ((ModuleId = @ModuleId) or (ModuleId is null and @ModuleId is null))

GO

drop procedure {databaseOwner}{objectQualifier}GetUrlTracking
GO

create procedure {databaseOwner}{objectQualifier}GetUrlTracking

@PortalID     int,
@Url          nvarchar(255),
@ModuleId     int

as

select *
from   {objectQualifier}UrlTracking
where  PortalID = @PortalID
and    Url = @Url
and    ((ModuleId = @ModuleId) or (ModuleId is null and @ModuleId is null))

GO


drop procedure {databaseOwner}{objectQualifier}GetUrlLog
GO

create procedure {databaseOwner}{objectQualifier}GetUrlLog

@URLTrackingID int,
@StartDate     datetime,
@EndDate       datetime

as

select {objectQualifier}UrlLog.*,
       'FullName' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName
from   {objectQualifier}UrlLog
inner join {objectQualifier}UrlTracking on {objectQualifier}UrlLog.UrlTrackingId = {objectQualifier}UrlTracking.UrlTrackingId
left outer join {objectQualifier}Users on {objectQualifier}UrlLog.UserId = {objectQualifier}Users.UserId
where  {objectQualifier}UrlLog.UrlTrackingID = @UrlTrackingID
and    ((ClickDate >= @StartDate) or @StartDate is null)
and    ((ClickDate <= @EndDate) or @EndDate is null)
order by ClickDate

GO

drop procedure {databaseOwner}{objectQualifier}UpdateUrlTracking
GO

create procedure {databaseOwner}{objectQualifier}UpdateUrlTracking

@PortalID     int,
@Url          nvarchar(255),
@LogActivity  bit,
@TrackClicks  bit,
@ModuleId     int,
@NewWindow    bit

as

update {objectQualifier}UrlTracking
set    LogActivity = @LogActivity,
       TrackClicks = @TrackClicks,
       NewWindow = @NewWindow
where  PortalID = @PortalID
and    Url = @Url
and    ((ModuleId = @ModuleId) or (ModuleId is null and @ModuleId is null))

GO

create procedure {databaseOwner}{objectQualifier}UpdateUrlTrackingStats

@PortalID     int,
@Url          nvarchar(255),
@ModuleId     int

as

update {objectQualifier}UrlTracking
set    Clicks = Clicks + 1,
       LastClick = getdate()
where  PortalID = @PortalID
and    Url = @Url
and    ((ModuleId = @ModuleId) or (ModuleId is null and @ModuleId is null))
   
GO

ALTER TABLE {databaseOwner}{objectQualifier}Announcements
	DROP CONSTRAINT DF_{objectQualifier}Announcements_Clicks
GO

ALTER TABLE {databaseOwner}{objectQualifier}Announcements
	DROP COLUMN Clicks, Syndicate
GO

drop procedure {databaseOwner}{objectQualifier}AddAnnouncement
GO

create procedure {databaseOwner}{objectQualifier}AddAnnouncement

@ModuleId       int,
@UserName       nvarchar(100),
@Title          nvarchar(150),
@URL            nvarchar(150),
@ExpireDate     DateTime,
@Description    nvarchar(2000),
@ViewOrder	int

as

insert into {objectQualifier}Announcements (
  ModuleId,
  CreatedByUser,
  CreatedDate,
  Title,
  URL,
  ExpireDate,
  Description,
  ViewOrder
)
values (
  @ModuleId,
  @UserName,
  getdate(),
  @Title,
  @URL,
  @ExpireDate,
  @Description,
  @ViewOrder
)

select SCOPE_IDENTITY()

GO

drop procedure {databaseOwner}{objectQualifier}UpdateAnnouncement
GO

create procedure {databaseOwner}{objectQualifier}UpdateAnnouncement

@ItemId         int,
@UserName       nvarchar(100),
@Title          nvarchar(150),
@URL            nvarchar(150),
@ExpireDate     datetime,
@Description    nvarchar(2000),
@ViewOrder	int

as

update {objectQualifier}Announcements
set    CreatedByUser = @UserName,
       CreatedDate   = GetDate(),
       Title         = @Title,
       URL           = @URL,
       ExpireDate    = @ExpireDate,
       Description   = @Description,
       ViewOrder     = @ViewOrder
where  ItemId = @ItemId
GO

drop procedure {databaseOwner}{objectQualifier}GetAnnouncement
GO

create procedure {databaseOwner}{objectQualifier}GetAnnouncement

@ItemId   int,
@ModuleId int

as

select {objectQualifier}Announcements.Title,
       {objectQualifier}Announcements.URL,
       {objectQualifier}Announcements.ExpireDate,
       {objectQualifier}Announcements.Description,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Announcements.CreatedDate,
       {objectQualifier}Announcements.ViewOrder,
       {objectQualifier}UrlTracking.TrackClicks,
       {objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Announcements
left outer join {objectQualifier}Users on {objectQualifier}Announcements.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}UrlTracking on {objectQualifier}Announcements.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
where  {objectQualifier}Announcements.ItemId = @ItemId
and    {objectQualifier}Announcements.ModuleId = @ModuleId
GO

drop procedure {databaseOwner}{objectQualifier}GetAnnouncements
GO

create procedure {databaseOwner}{objectQualifier}GetAnnouncements

@ModuleId int

as

select {objectQualifier}Announcements.ItemId,
       {objectQualifier}Announcements.CreatedByUser,
       {objectQualifier}Announcements.CreatedDate,
       {objectQualifier}Announcements.Title,
       {objectQualifier}Announcements.URL,
       {objectQualifier}Announcements.ExpireDate,
       {objectQualifier}Announcements.Description,
       {objectQualifier}Announcements.ViewOrder,
       {objectQualifier}UrlTracking.TrackClicks,
       {objectQualifier}UrlTracking.NewWindow
from {objectQualifier}Announcements
left outer join {objectQualifier}UrlTracking on {objectQualifier}Announcements.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
where  {objectQualifier}Announcements.ModuleId = @ModuleId
and    ({objectQualifier}Announcements.ExpireDate > GetDate() or {objectQualifier}Announcements.ExpireDate is null)
order by {objectQualifier}Announcements.ViewOrder asc, {objectQualifier}Announcements.CreatedDate desc

GO

ALTER TABLE {databaseOwner}{objectQualifier}Documents
	DROP CONSTRAINT DF_{objectQualifier}Documents_Clicks
GO

ALTER TABLE {databaseOwner}{objectQualifier}Documents
	DROP COLUMN Clicks, Syndicate
GO

drop procedure {databaseOwner}{objectQualifier}AddDocument
GO

create procedure {databaseOwner}{objectQualifier}AddDocument

@ModuleId         int,
@Title            nvarchar(150),
@URL              nvarchar(250),
@UserName         nvarchar(100),
@Category         nvarchar(50)

as

insert into {objectQualifier}Documents (
  ModuleId,
  Title,
  URL,
  CreatedByUser,
  CreatedDate,
  Category
)
values (
  @ModuleId,
  @Title,
  @URL,
  @UserName,
  getdate(),
  @Category
)

select SCOPE_IDENTITY()
GO

drop procedure {databaseOwner}{objectQualifier}UpdateDocument
GO

create procedure {databaseOwner}{objectQualifier}UpdateDocument

@ItemId           int,
@Title            nvarchar(150),
@URL              nvarchar(250),
@UserName         nvarchar(100),
@Category         nvarchar(50)

as

update {objectQualifier}Documents
set    Title             = @Title,
       URL               = @URL,
       CreatedByUser     = @UserName,
       CreatedDate       = getdate(),
       Category          = @Category
where  ItemId = @ItemId
GO

drop procedure {databaseOwner}{objectQualifier}GetDocument
GO

create procedure {databaseOwner}{objectQualifier}GetDocument

@ItemId   int,
@ModuleId int

as

select {objectQualifier}Documents.Title,
       {objectQualifier}Documents.URL,
       {objectQualifier}Documents.Category,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {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
where  {objectQualifier}Documents.ItemId = @ItemId
and    {objectQualifier}Documents.ModuleId = @ModuleId

GO

drop procedure {databaseOwner}{objectQualifier}GetDocuments
GO

create procedure {databaseOwner}{objectQualifier}GetDocuments

@ModuleId int,
@PortalId int

as

select {objectQualifier}Documents.ItemId,
       {objectQualifier}Documents.Title,
       {objectQualifier}Documents.URL,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Documents.CreatedDate,
       {objectQualifier}Documents.Category,
       {objectQualifier}Files.Size,
       {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}Files on {objectQualifier}Documents.URL = {objectQualifier}Files.FileName and {objectQualifier}Files.PortalId = @PortalId
left outer join {objectQualifier}UrlTracking on {objectQualifier}Documents.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
where  {objectQualifier}Documents.ModuleId = @ModuleId
order by {objectQualifier}Documents.Title

GO

update {objectQualifier}UrlTracking
set    NewWindow = ( select NewWindow from {objectQualifier}Links where {objectQualifier}UrlTracking.ModuleId = {objectQualifier}Links.ModuleId and {objectQualifier}UrlTracking.Url = {objectQualifier}Links.Url )
GO

ALTER TABLE {databaseOwner}{objectQualifier}Links
	DROP CONSTRAINT DF_{objectQualifier}Links_NewWindow
GO

ALTER TABLE {databaseOwner}{objectQualifier}Links
	DROP CONSTRAINT DF_{objectQualifier}Links_Clicks
GO

ALTER TABLE {databaseOwner}{objectQualifier}Links
	DROP COLUMN Clicks, MobileURL, NewWindow
GO

drop procedure {databaseOwner}{objectQualif