📄 03.00.04.sqldataprovider

📁 完整的商业模板和强大的后台管理功能
💻 SQLDATAPROVIDER
📖 第 1 页 / 共 3 页
字号:
上一页 1 23
		{objectQualifier}Modules.Footer,
		{objectQualifier}Modules.StartDate,
		{objectQualifier}Modules.EndDate,
		{objectQualifier}Modules.PortalID,
		{objectQualifier}TabModules.TabID,
        {objectQualifier}TabModules.PaneName,
		{objectQualifier}TabModules.ModuleOrder,
		{objectQualifier}TabModules.CacheTime,
		{objectQualifier}TabModules.Alignment,
		{objectQualifier}TabModules.Color,
		{objectQualifier}TabModules.Border,
		'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}TabModules.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
		{objectQualifier}TabModules.Visibility,
		{objectQualifier}TabModules.ContainerSrc,
        {objectQualifier}DesktopModules.DesktopModuleID,
		{objectQualifier}DesktopModules.FriendlyName,
		{objectQualifier}DesktopModules.Description,
		{objectQualifier}DesktopModules.Version,
		{objectQualifier}DesktopModules.IsPremium,
		{objectQualifier}DesktopModules.IsAdmin,
		{objectQualifier}DesktopModules.BusinessControllerClass
from	{objectQualifier}Modules
inner join {objectQualifier}ModuleDefinitions on {objectQualifier}Modules.ModuleDefID = {objectQualifier}ModuleDefinitions.ModuleDefID
inner join {objectQualifier}DesktopModules on {objectQualifier}ModuleDefinitions.DesktopModuleID = {objectQualifier}DesktopModules.DesktopModuleID
left outer join {objectQualifier}TabModules on {objectQualifier}Modules.ModuleId = {objectQualifier}TabModules.ModuleId
left outer join {objectQualifier}Files on {objectQualifier}TabModules.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where   {objectQualifier}Modules.ModuleId = @ModuleId
and     ({objectQualifier}TabModules.TabId = @TabId or @TabId is null)
GO

drop procedure {databaseOwner}{objectQualifier}GetPortalTabModules
GO

CREATE procedure {databaseOwner}{objectQualifier}GetPortalTabModules

@PortalId int, 
@TabId int

as

select M.ModuleID,
       M.PortalID,
       M.ModuleDefId,
       M.ModuleTitle,
       M.AllTabs,
       M.Header,
       M.Footer,
       M.StartDate,
       M.EndDate,
       M.InheritViewPermissions,
       M.IsDeleted,
       TM.TabModuleId,
       TM.TabId,
       TM.ModuleOrder,
       TM.PaneName,
       TM.CacheTime,
       TM.Alignment,
       TM.Color,
       TM.Border,
       'IconFile' = case when F.FileName is null then TM.IconFile else F.Folder + F.FileName end,
       TM.Visibility,
       TM.ContainerSrc,
       MC.ModuleControlId,
       MC.ControlSrc,
       MC.ControlType,
       MC.ControlTitle,
       MC.HelpURL,
       DM.*
from {objectQualifier}Modules M
inner join {objectQualifier}TabModules TM on M.ModuleId = TM.ModuleId
inner join {objectQualifier}Tabs T on TM.TabId = T.TabId
inner join {objectQualifier}ModuleDefinitions MD on M.ModuleDefId = MD.ModuleDefId
inner join {objectQualifier}ModuleControls MC on MD.ModuleDefId = MC.ModuleDefId
inner join {objectQualifier}DesktopModules DM on MD.DesktopModuleId = DM.DesktopModuleId
left outer join {objectQualifier}Files F on TM.IconFile = 'fileid=' + convert(varchar,F.FileID)
where  TM.TabId = @TabId
and    ControlKey is null
order by TM.ModuleOrder
GO

drop procedure {databaseOwner}{objectQualifier}GetModuleByDefinition
GO

CREATE procedure {databaseOwner}{objectQualifier}GetModuleByDefinition

@PortalId int,
@FriendlyName nvarchar(128)

as

select	{objectQualifier}Modules.ModuleID,
		{objectQualifier}Modules.ModuleDefID,
		{objectQualifier}Modules.ModuleTitle,
		{objectQualifier}Modules.AllTabs,
		{objectQualifier}Modules.IsDeleted,
		{objectQualifier}Modules.InheritViewPermissions,
		{objectQualifier}Modules.Header,
		{objectQualifier}Modules.Footer,
		{objectQualifier}Modules.StartDate,
		{objectQualifier}Modules.EndDate,
		{objectQualifier}Modules.PortalID,
       	{objectQualifier}TabModules.PaneName,
		{objectQualifier}TabModules.TabModuleID,
		{objectQualifier}TabModules.TabID,
		{objectQualifier}TabModules.ModuleID,
		{objectQualifier}TabModules.ModuleOrder,
		{objectQualifier}TabModules.CacheTime,
		{objectQualifier}TabModules.Alignment,
		{objectQualifier}TabModules.Color,
		{objectQualifier}TabModules.Border,
		'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}TabModules.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
		{objectQualifier}TabModules.Visibility,
		{objectQualifier}TabModules.ContainerSrc,
       	{objectQualifier}DesktopModules.DesktopModuleID,
		{objectQualifier}DesktopModules.FriendlyName,
		{objectQualifier}DesktopModules.Description,
		{objectQualifier}DesktopModules.Version,
		{objectQualifier}DesktopModules.IsPremium,
		{objectQualifier}DesktopModules.IsAdmin,
		{objectQualifier}DesktopModules.BusinessControllerClass
from   {objectQualifier}Modules
inner join {objectQualifier}ModuleDefinitions on {objectQualifier}Modules.ModuleDefId = {objectQualifier}ModuleDefinitions.ModuleDefId
inner join {objectQualifier}DesktopModules on {objectQualifier}ModuleDefinitions.DesktopModuleId = {objectQualifier}DesktopModules.DesktopModuleId
left outer join {objectQualifier}TabModules on {objectQualifier}Modules.ModuleID = {objectQualifier}TabModules.ModuleID
left outer join {objectQualifier}Files on {objectQualifier}TabModules.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  (({objectQualifier}Modules.PortalId = @PortalId) or ({objectQualifier}Modules.PortalId is null and @PortalID is null))
and    {objectQualifier}ModuleDefinitions.FriendlyName = @FriendlyName
GO

drop procedure {databaseOwner}{objectQualifier}GetEvent
GO

CREATE procedure {databaseOwner}{objectQualifier}GetEvent

@ItemId   int,
@ModuleId int

as

select {objectQualifier}Events.ItemId,
       {objectQualifier}Events.Description,
       {objectQualifier}Events.DateTime,
       {objectQualifier}Events.Title,
       {objectQualifier}Events.ExpireDate,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Events.CreatedDate,
       {objectQualifier}Events.Every,
       {objectQualifier}Events.Period,
       'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Events.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Events.AltText
from   {objectQualifier}Events
left outer join {objectQualifier}Users on {objectQualifier}Events.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}Files on {objectQualifier}Events.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  ItemId = @ItemId
and    ModuleId = @ModuleId
GO

drop procedure {databaseOwner}{objectQualifier}GetEvents
GO

CREATE procedure {databaseOwner}{objectQualifier}GetEvents

@ModuleId int

as

select {objectQualifier}Events.ItemId,
       {objectQualifier}Events.Description,
       {objectQualifier}Events.DateTime,
       {objectQualifier}Events.Title,
       {objectQualifier}Events.ExpireDate,
       {objectQualifier}Events.CreatedByUser,
       {objectQualifier}Events.CreatedDate,
       'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Events.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Events.AltText,
       'MaxWIdth' = (select max(WIdth) from {objectQualifier}Events left outer join {objectQualifier}Files on {objectQualifier}Events.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID) where ModuleId = @ModuleId and (ExpireDate > getdate() or ExpireDate is null))
from   {objectQualifier}Events
left outer join {objectQualifier}Files on {objectQualifier}Events.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  ModuleId = @ModuleId
and    (ExpireDate > getdate() or ExpireDate is null)
order by DateTime
GO

drop procedure {databaseOwner}{objectQualifier}GetEventsByDate
GO

CREATE procedure {databaseOwner}{objectQualifier}GetEventsByDate

@ModuleId int,
@StartDate datetime,
@EndDate datetime

as

select {objectQualifier}Events.ItemId,
       {objectQualifier}Events.Description,
       {objectQualifier}Events.DateTime,
       {objectQualifier}Events.Title,
       {objectQualifier}Events.ExpireDate,
       {objectQualifier}Events.CreatedByUser,
       {objectQualifier}Events.CreatedDate,
       {objectQualifier}Events.Every,
       {objectQualifier}Events.Period,
       'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Events.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Events.AltText
from   {objectQualifier}Events
left outer join {objectQualifier}Files on {objectQualifier}Events.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  ModuleId = @ModuleId
and    ( (Period is null and (DateTime >= @StartDate and DateTime <= @EndDate)) or Period is not null )
order by DateTime
GO


/* Remove Vendor Feedback */
delete from {databaseOwner}{objectQualifier}ModuleControls
where ControlSrc = 'Admin/Vendors/VendorFeedback.ascx'
GO

drop procedure {databaseOwner}{objectQualifier}GetTabByName
GO

CREATE procedure {databaseOwner}{objectQualifier}GetTabByName

@TabName  nvarchar(50),
@PortalId int

as

select TabID, 
TabOrder, 
{objectQualifier}Tabs.PortalID, 
TabName, 
IsVisible, 
ParentId, 
[Level], 
DisableLink, 
Title, 
Description, 
KeyWords, 
IsDeleted, 
Url, 
SkinSrc, 
ContainerSrc, 
TabPath, 
StartDate, 
EndDate,
 'IconFile' = case when {objectQualifier}Files_1.FileName is null then {objectQualifier}Tabs.IconFile else {objectQualifier}Files_1.Folder + {objectQualifier}Files_1.FileName end,
'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
LEFT OUTER JOIN {objectQualifier}Files {objectQualifier}Files_1 ON {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files_1.FileID)
where  TabName = @TabName
and    (({objectQualifier}Tabs.PortalId = @PortalId) or (@PortalId is null AND {objectQualifier}Tabs.PortalId is null))
order by TabID
GO


ALTER procedure {databaseOwner}{objectQualifier}GetAllTabs

as

select TabID, 
       TabOrder, 
       {objectQualifier}Tabs.PortalID, 
       TabName, 
       IsVisible, 
       ParentId, 
       [Level], 
       'IconFile' = case when {objectQualifier}Files_1.FileName is null then {objectQualifier}Tabs.IconFile else {objectQualifier}Files_1.Folder + {objectQualifier}Files_1.FileName end,
       DisableLink, 
       Title, 
       Description, 
       KeyWords, 
       IsDeleted,
       SkinSrc,
       ContainerSrc,
       TabPath,
       StartDate,
       EndDate,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Tabs.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.URL = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
LEFT OUTER JOIN dbo.{objectQualifier}Files {objectQualifier}Files_1 ON {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files_1.FileID)
order by TabOrder, TabName
GO

drop procedure {databaseOwner}{objectQualifier}GetTab
GO


CREATE procedure {databaseOwner}{objectQualifier}GetTab

@TabId    int

as

select TabID, 
TabOrder, 
{objectQualifier}Tabs.PortalID, 
TabName, 
IsVisible, 
ParentId, 
[Level], 
DisableLink, 
Title, 
Description, 
KeyWords, 
IsDeleted, 
Url, 
SkinSrc, 
ContainerSrc, 
TabPath, 
StartDate, 
EndDate,
'IconFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Tabs.IconFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  TabId = @TabId
GO

drop procedure {databaseOwner}{objectQualifier}GetRole
GO

create procedure {databaseOwner}{objectQualifier}GetRole

@RoleId   int,
@PortalId int

as

select RoleId,
       PortalId,
       RoleName,
       Description,
       ServiceFee,
       BillingPeriod,
       BillingFrequency,
       TrialFee,
       TrialPeriod,
       TrialFrequency,
       IsPublic,
       AutoAssignment
from   {objectQualifier}Roles
where  RoleId = @RoleId
and    PortalId = @PortalId

GO

drop procedure {databaseOwner}{objectQualifier}GetVendor
GO

CREATE procedure {databaseOwner}{objectQualifier}GetVendor

@VendorId int,
@PortalId int

as

select {objectQualifier}Vendors.VendorName, 
       {objectQualifier}Vendors.Unit, 
       {objectQualifier}Vendors.Street, 
       {objectQualifier}Vendors.City, 
       {objectQualifier}Vendors.Region, 
       {objectQualifier}Vendors.Country, 
       {objectQualifier}Vendors.PostalCode, 
       {objectQualifier}Vendors.Telephone,
       {objectQualifier}Vendors.Fax,
       {objectQualifier}Vendors.Cell,
       {objectQualifier}Vendors.Email,
       {objectQualifier}Vendors.Website,
       {objectQualifier}Vendors.FirstName,
       {objectQualifier}Vendors.LastName,
       {objectQualifier}Vendors.ClickThroughs,
       {objectQualifier}Vendors.Views,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Vendors.CreatedDate,
      'LogoFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Vendors.LogoFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Vendors.KeyWords,
       {objectQualifier}Vendors.Authorized,
       {objectQualifier}Vendors.PortalId
from {objectQualifier}Vendors
left outer join {objectQualifier}Users on {objectQualifier}Vendors.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}Files on {objectQualifier}Vendors.LogoFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
where  VendorId = @VendorId
and    (({objectQualifier}Vendors.PortalId = @PortalId) or ({objectQualifier}Vendors.PortalId is null and @PortalId is null))

GO

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/
上一页 1 23
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -