Слияние кода завершено, страница обновится автоматически
USE [OUKReport]
GO
/****** Object: StoredProcedure [dbo].[GetPeroidReport] Script Date: 2019/3/23 19:21:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[GetPeroidReport]
@peroid char,
@start int ,
@end int ,
@deviceIds nvarchar(500)
as
begin
declare @sql nvarchar(1000)
if @peroid= 'h' begin
set @sql = N'select [hour],[deviceId],[EA_Amout] from [Data_EL_Hour] where [Hour]>=@start and [Hour]<=@end and DeviceId in ('+ @deviceIds +')'
end else if @peroid= 'd' begin
set @sql = N'select [day],[deviceId],sum([EA_Amout]) from [Data_EL_Hour] where [Hour]>=@start and [Hour]<=@end and DeviceId in ('+ @deviceIds +') group by [day],deviceid'
end else if @peroid= 'w' begin
set @sql = N'select [week],[deviceId],sum([EA_Amout]) from [Data_EL_Hour] where [Hour]>=@start and [Hour]<=@end and DeviceId in ('+ @deviceIds +') group by [week],deviceid'
end else if @peroid= 'm' begin
set @sql = N'select [month],[deviceId],sum([EA_Amout]) from [Data_EL_Hour] where [Hour]>=@start and [Hour]<=@end and DeviceId in ('+ @deviceIds +') group by [month],deviceid'
end else if @peroid= 'y' begin
set @sql = N'select [year],[deviceId],sum([EA_Amout]) from [Data_EL_Hour] where [Hour]>=@start and [Hour]<=@end and DeviceId in ('+ @deviceIds +') group by [year],deviceid'
end
exec sp_executesql @sql,N'@start int ,@end int ',@start=@start,@end=@end
end
GO
/****** Object: Table [dbo].[Data_EL_Hour] Script Date: 2019/3/23 19:21:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Data_EL_Hour](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[DeviceId] [int] NOT NULL,
[EA] [float] NULL,
[EA_Amount] [float] NULL,
[IA] [float] NULL,
[IB] [float] NULL,
[IC] [float] NULL,
[P] [float] NULL,
[UAB] [float] NULL,
[UBC] [float] NULL,
[UCA] [float] NULL,
[HourInDay] AS ([Hour]%(100)) PERSISTED,
[Hour] [int] NOT NULL,
[Day] [int] NOT NULL,
[Week] [int] NOT NULL,
[Month] [int] NOT NULL,
[Year] [int] NOT NULL,
[FGP] [int] NOT NULL,
[CreateTime] [datetime] NOT NULL,
CONSTRAINT [PK_Data_EL_Hour] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[DeviceInfo] Script Date: 2019/3/23 19:21:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DeviceInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[OrgId] [int] NOT NULL,
[OrgPath] [nvarchar](200) NOT NULL,
[Remark] [nvarchar](50) NULL,
[CreateById] [int] NOT NULL,
[CreateTime] [datetime] NOT NULL,
[CreateByName] [nvarchar](50) NOT NULL,
[ModifyByName] [nvarchar](50) NULL,
[ModifyTime] [datetime] NULL,
[ModifyById] [int] NULL,
CONSTRAINT [PK_DeviceInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Organization] Script Date: 2019/3/23 19:21:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Organization](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[SysKey] [nvarchar](50) NULL,
[ParentId] [int] NOT NULL,
[NamePath] [nvarchar](200) NULL,
[KeyPath] [nvarchar](200) NULL,
[Remark] [nvarchar](50) NULL,
[CreateById] [int] NOT NULL,
[ModifyTime] [datetime] NULL,
[CreateByName] [nvarchar](50) NOT NULL,
[CreateTime] [datetime] NOT NULL,
[ModifyById] [int] NULL,
[ModifyByName] [nvarchar](50) NULL,
CONSTRAINT [PK_Organization] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[SysUser] Script Date: 2019/3/23 19:21:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SysUser](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
[IsAdmin] [bit] NOT NULL,
[CreateById] [int] NOT NULL,
[CreateByName] [nvarchar](50) NOT NULL,
[CreateTime] [datetime] NOT NULL,
[ModifyById] [int] NULL,
[ModifyByName] [nvarchar](50) NULL,
[ModifyTime] [datetime] NULL,
CONSTRAINT [PK_SysUser] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: View [dbo].[view_OrgData] Script Date: 2019/3/23 19:21:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[view_OrgData]
AS
SELECT o.Id, o.Name, d.Id AS deviceId, el.EA, el.EA_Amount, el.IA, el.IB, el.IC, el.P, el.UAB, el.UBC, el.UCA, el.HourInDay, el.Hour,
el.Day, el.Week, el.Month, el.Year, el.FGP
FROM dbo.Data_EL_Hour AS el INNER JOIN
dbo.DeviceInfo AS d ON el.DeviceId = d.Id CROSS JOIN
dbo.Organization AS o
WHERE (d.OrgPath LIKE o.KeyPath + '%')
GO
/****** Object: Index [IX_Data_EL_Hour] Script Date: 2019/3/23 19:21:44 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Data_EL_Hour] ON [dbo].[Data_EL_Hour]
(
[Hour] ASC,
[DeviceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_DeviceId] Script Date: 2019/3/23 19:21:44 ******/
CREATE NONCLUSTERED INDEX [IX_DeviceId] ON [dbo].[Data_EL_Hour]
(
[DeviceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IXFK_DeviceInfo_Organization] Script Date: 2019/3/23 19:21:44 ******/
CREATE NONCLUSTERED INDEX [IXFK_DeviceInfo_Organization] ON [dbo].[DeviceInfo]
(
[OrgId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_KeyPath] Script Date: 2019/3/23 19:21:44 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_KeyPath] ON [dbo].[Organization]
(
[KeyPath] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [IX_NamPath] Script Date: 2019/3/23 19:21:44 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_NamPath] ON [dbo].[Organization]
(
[NamePath] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Data_EL_Hour] ADD CONSTRAINT [DF_Data_EL_Hour_FGP] DEFAULT ((0)) FOR [FGP]
GO
ALTER TABLE [dbo].[DeviceInfo] ADD CONSTRAINT [DF__DeviceInf__Creat__173876EA] DEFAULT (getdate()) FOR [CreateTime]
GO
ALTER TABLE [dbo].[Organization] ADD CONSTRAINT [DF_Organization_ParentId] DEFAULT ((0)) FOR [ParentId]
GO
ALTER TABLE [dbo].[Organization] ADD CONSTRAINT [DF__Organizat__Creat__182C9B23] DEFAULT (getdate()) FOR [CreateTime]
GO
ALTER TABLE [dbo].[SysUser] ADD CONSTRAINT [DF__SysUser__IsAdmin__4222D4EF] DEFAULT ((0)) FOR [IsAdmin]
GO
ALTER TABLE [dbo].[SysUser] ADD CONSTRAINT [DF__SysUser__CreateT__4316F928] DEFAULT (getdate()) FOR [CreateTime]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'峰谷平' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Data_EL_Hour', @level2type=N'COLUMN',@level2name=N'FGP'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[20] 4[22] 2[12] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1[50] 4[25] 3) )"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "d"
Begin Extent =
Top = 2
Left = 420
Bottom = 165
Right = 627
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "o"
Begin Extent =
Top = 0
Left = 722
Bottom = 163
Right = 929
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "el"
Begin Extent =
Top = 7
Left = 48
Bottom = 170
Right = 228
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 19
Width = 284
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
Width = 1200
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_OrgData'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'view_OrgData'
GO
USE [master]
GO
ALTER DATABASE [OUKReport] SET READ_WRITE
GO
Вы можете оставить комментарий после Вход в систему
Неприемлемый контент может быть отображен здесь и не будет показан на странице. Вы можете проверить и изменить его с помощью соответствующей функции редактирования.
Если вы подтверждаете, что содержание не содержит непристойной лексики/перенаправления на рекламу/насилия/вульгарной порнографии/нарушений/пиратства/ложного/незначительного или незаконного контента, связанного с национальными законами и предписаниями, вы можете нажать «Отправить» для подачи апелляции, и мы обработаем ее как можно скорее.
Опубликовать ( 0 )