01.00.00.sqldataprovider

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

		[ModuleID]
	) REFERENCES [dbo].[Modules] (
		[ModuleID]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[HtmlText] ADD 
	CONSTRAINT [FK_HtmlText_Modules] FOREIGN KEY 
	(
		[ModuleID]
	) REFERENCES [dbo].[Modules] (
		[ModuleID]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[Links] ADD 
	CONSTRAINT [FK_Links_Modules] FOREIGN KEY 
	(
		[ModuleID]
	) REFERENCES [dbo].[Modules] (
		[ModuleID]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ModuleEvents] ADD 
	CONSTRAINT [FK_Events_Modules] FOREIGN KEY 
	(
		[ModuleID]
	) REFERENCES [dbo].[Modules] (
		[ModuleID]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ModuleSettings] ADD 
	CONSTRAINT [FK_ModuleSettings_Modules] FOREIGN KEY 
	(
		[ModuleID]
	) REFERENCES [dbo].[Modules] (
		[ModuleID]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

/************************************************************/
/*****          Create Stored Procedures                *****/
/************************************************************/

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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,
@TabID           int OUTPUT

as

insert into Tabs (
    PortalID,
    TabName,
    TabOrder,
    ShowMobile,
    MobileTabName,
    AuthorizedRoles,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible
)
values (
    @PortalID,
    @TabName,
    0,
    @ShowMobile,
    @MobileTabName,
    @AuthorizedRoles,
    @LeftPaneWidth,
    @RightPaneWidth,
    @IsVisible
)

select @TabID = @@IDENTITY


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE DeleteAnnouncement
(
    @ItemID int
)
AS

DELETE FROM
    Announcements

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



create procedure DeleteBanner
(
    @BannerId int
)

as

delete
from   Banners
where  BannerId = @BannerId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE DeleteContact
(
    @ItemID int
)
AS

DELETE FROM
    Contacts

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE DeleteDocument
(
    @ItemID int
)
AS

DELETE FROM
    Documents

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE DeleteFAQ
(
    @ItemID int
)
AS

DELETE FROM
    FAQs

WHERE
    ItemID = @ItemID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE DeleteLink
(
    @ItemID int
)
AS

DELETE FROM
    Links

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE DeleteModule
(
    @ModuleID       int
)
AS

DELETE FROM 
    Modules 
WHERE 
    ModuleID = @ModuleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE DeleteModuleDefinition
(
    @ModuleDefID int
)
AS

DELETE FROM
    ModuleDefinitions

WHERE
    ModuleDefID = @ModuleDefID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure DeleteModuleEvent

@ItemID int

AS

delete
from   ModuleEvents
where  ItemID = @ItemID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure DeletePortalInfo

@PortalID int

as

delete
from   Portals
where  PortalID = @PortalID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE DeleteTab
(
    @TabID int
)
AS

DELETE FROM
    Tabs

WHERE
    TabID = @TabID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure DeleteVendor

@VendorID int

as

delete
from   Vendors
where  VendorID = @VendorID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




create procedure GetBannerClickThrough

@BannerId int

as

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

select URL,
       VendorId
from   Banners
where  BannerId = @BannerId

return 1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


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

return 1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetBillingFrequencyCode
    
@Code char(1)

as

select Description
from   CodeFrequency
where  Code = @Code


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetBillingFrequencyCodes
    
as

select *
from   CodeFrequency


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE GetFAQs
(
    @ModuleID int
)
AS

SELECT
    ItemID,
    CreatedDate,
    CreatedByUser,
    Question,
    Answer

FROM
    FAQs

WHERE
    ModuleID = @ModuleID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE GetHtmlText
(
    @ModuleID int
)
AS

SELECT
    *

FROM
    HtmlText

WHERE
    ModuleID = @ModuleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE GetModuleSettings
(
    @ModuleID int
)
AS

SELECT
    SettingName,
    SettingValue

FROM
    ModuleSettings

WHERE
    ModuleID = @ModuleID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetSingleUserDefinedRow

@UserDefinedRowID   int,
@ModuleId           int

as

select UserDefinedFields.FieldTitle,
       UserDefinedData.FieldValue
from   UserDefinedData
inner join UserDefinedFields on UserDefinedData.UserDefinedFieldId = UserDefinedFields.UserDefinedFieldId
where  UserDefinedData.UserDefinedRowID = @UserDefinedRowID
and    UserDefinedFields.ModuleId = @ModuleId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetTabById

@TabId int

as

select TabID,
       TabOrder,
       TabName,
       MobileTabName,
       AuthorizedRoles,
       ShowMobile,
       LeftPaneWidth,
       RightPaneWidth,
       IsVisible
from   Tabs
where  TabId = @TabId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetTabByName

@PortalID int,
@TabName  nvarchar(50)

as

select TabID,
       TabOrder
from   Tabs
where  PortalID = @PortalID
and    TabName = @TabName


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE UpdateContact
(
    @ItemID   int,
    @UserName nvarchar(100),
    @Name     nvarchar(50),
    @Role     nvarchar(100),
    @Email    nvarchar(100),
    @Contact1 nvarchar(250),
    @Contact2 nvarchar(250)
)
AS

UPDATE
    Contacts

SET
    CreatedByUser = @UserName,
    CreatedDate   = GetDate(),
    Name          = @Name,
    Role          = @Role,
    Email         = @Email,
    Contact1      = @Contact1,
    Contact2      = @Contact2

WHERE
    ItemID = @ItemID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure UpdateFAQ

@ItemID   int,
@UserName nvarchar(100),
@Question  text,
@Answer    text

as

update FAQs
set    Question = @Question,
       Answer = @Answer,
       CreatedByUser = @UserName,
       CreatedDate = getdate()