161 lines
9.5 KiB
Transact-SQL
161 lines
9.5 KiB
Transact-SQL
|
|
update Sys_Menu set Url='ReportManage/EnergyReport/EnergyWaterConservation.aspx' where MenuId='CCA0E136-9AC7-4A14-BC1F-20758A99E88E'
|
|
update Sys_Menu set Url='ReportManage/EnergyReport/EnergyWaterConservation.aspx' where MenuId='D4541B37-7876-40D8-A48B-AEF8FCDAEE9F'
|
|
update Sys_Menu set Url='ReportManage/EnergyReport/EnergyStatistics.aspx' where MenuId='0BB29D06-E286-45C0-BC6A-3DDF8A21D1B2'
|
|
update Sys_Menu set Url='ReportManage/EnergyReport/EnergyStatistics.aspx' where MenuId='C97C6A48-8FF1-4E74-BF9A-E89C0422AB67'
|
|
go
|
|
insert into Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuType,IsOffice,IsEnd,IsUsed)
|
|
values('49DE1534-20FF-44B0-846F-FF057E836DEC','得分扣分记录流水日志','Score/ScoreFlowLog.aspx',30,'416F0A73-FB3D-4AAC-BC76-3820F52382D9','Menu_S_HSSE',1,1,1)
|
|
go
|
|
insert into Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuType,IsOffice,IsEnd,IsUsed)
|
|
values('10A3CCFE-E445-4832-A9BF-129D89828278','得分扣分记录流水日志','Score/ScoreFlowLog.aspx',30,'EE260447-028F-46AF-8864-9A5DC9DAA5BD','Menu_HSSE',0,1,1)
|
|
go
|
|
|
|
alter table ScoreFlowLog add DataSourceId nvarchar(50)
|
|
go
|
|
alter table ScoreUser add DataSourceId nvarchar(50)
|
|
go
|
|
alter table ScoreFlowLog add CheckDate datetime
|
|
go
|
|
alter table ScoreUser add CheckDate datetime
|
|
go
|
|
|
|
CREATE VIEW View_Training_TestRecord
|
|
as
|
|
/************考试记录信息****************/
|
|
select testRecord.TestRecordId,
|
|
testRecord.ProjectId,
|
|
project.ProjectName,
|
|
Person.UnitId,--单位id
|
|
Unit.UnitName,--单位名称
|
|
testRecord.TestStartTime,--考试开始时间
|
|
testRecord.TestEndTime,--考试结束时间
|
|
testRecord.TestManId,--考生id
|
|
Person.PersonName,--考生姓名
|
|
Person.IdentityCard,--身份证号
|
|
testRecord.TestScores,--考试成绩
|
|
testRecord.CompanyTrainingItemId,--教材明细类型id
|
|
TCItem.CompanyTrainingItemName,--教材明细类型
|
|
TC.CompanyTrainingId,--教材类型id
|
|
TC.CompanyTrainingName,--教材类型
|
|
Person.WorkPostId,--岗位id
|
|
workPost.WorkPostName,--岗位名称
|
|
const.ConstText as WorkPostType--岗位类型
|
|
from Training_TestRecord as testRecord
|
|
LEFT JOIN dbo.Training_CompanyTrainingItem AS TCItem ON TCItem.CompanyTrainingItemId = TestRecord.CompanyTrainingItemId
|
|
LEFT JOIN dbo.Training_CompanyTraining AS TC ON TC.CompanyTrainingId = TCItem.CompanyTrainingId
|
|
LEFT JOIN dbo.SitePerson_Person AS Person ON Person.PersonId = TestRecord.TestManId
|
|
LEFT JOIN dbo.Base_Unit AS Unit ON Person.UnitId=Unit.UnitId
|
|
LEFT JOIN Base_WorkPost as workPost on workPost.WorkPostId = Person.WorkPostId
|
|
LEFT JOIN Sys_Const AS const ON const.ConstValue = workPost.PostType and const.GroupId ='PostType'
|
|
LEFT JOIN Base_Project as project on project.ProjectId = testRecord.ProjectId
|
|
GO
|
|
|
|
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('C6655551-21D8-4E1E-B6C9-27D4A9FF9F71','CCA0E136-9AC7-4A14-BC1F-20758A99E88E','增加',1)
|
|
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('3EE31410-3D5D-448C-ACB9-3BF23DEE6D33','CCA0E136-9AC7-4A14-BC1F-20758A99E88E','修改',2)
|
|
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('C797CA13-F7B9-4BEA-94C8-E900A351E680','CCA0E136-9AC7-4A14-BC1F-20758A99E88E','删除',3)
|
|
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('952B1041-1E06-4557-898B-782EC892C9A8','CCA0E136-9AC7-4A14-BC1F-20758A99E88E','保存',4)
|
|
go
|
|
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('70D26027-BE4F-45FE-8C0B-A6CE31731E73','D4541B37-7876-40D8-A48B-AEF8FCDAEE9F','增加',1)
|
|
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('339ACF6A-124A-410C-B8E8-47949E02C6D3','D4541B37-7876-40D8-A48B-AEF8FCDAEE9F','修改',2)
|
|
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('8FE0D4AB-7208-44CE-B508-A66D01EFBF09','D4541B37-7876-40D8-A48B-AEF8FCDAEE9F','删除',3)
|
|
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('C659818E-300A-44DA-B685-E9C42E157CF4','D4541B37-7876-40D8-A48B-AEF8FCDAEE9F','保存',4)
|
|
go
|
|
|
|
|
|
CREATE TABLE [dbo].[EnergyReport_EnergyWaterConservation](
|
|
[EnergyWaterConservationId] [nvarchar](50) NOT NULL,
|
|
[ProjectId] [nvarchar](50) NULL,
|
|
[EnergySavingIndex] [decimal](18, 2) NULL,
|
|
[WaterSavingIndex] [decimal](18, 2) NULL,
|
|
[EnergySavingSchemes] [nvarchar](200) NULL,
|
|
[WaterSavingSchemes] [nvarchar](200) NULL,
|
|
[SelfEvaluation] [nvarchar](200) NULL,
|
|
[Summary] [nvarchar](200) NULL,
|
|
[SupportingDocuments] [nvarchar](200) NULL,
|
|
[ActualEnergySaving] [decimal](18, 2) NULL,
|
|
[ActualWaterSaving] [decimal](18, 2) NULL,
|
|
[EnergySavingCompletionRate] [decimal](18, 2) NULL,
|
|
[WaterSavingCompletionRate] [decimal](18, 2) NULL,
|
|
[CompileMan] [nvarchar](50) NULL,
|
|
[CompileDate] [datetime] NULL,
|
|
[Remarks] [nvarchar](200) NULL,
|
|
CONSTRAINT [PK_EnergyReport_EnergyWaterConservation] PRIMARY KEY CLUSTERED
|
|
(
|
|
[EnergyWaterConservationId] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[EnergyReport_EnergyWaterConservation] WITH CHECK ADD CONSTRAINT [FK_EnergyReport_EnergyWaterConservation_Base_Project] FOREIGN KEY([ProjectId])
|
|
REFERENCES [dbo].[Base_Project] ([ProjectId])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[EnergyReport_EnergyWaterConservation] CHECK CONSTRAINT [FK_EnergyReport_EnergyWaterConservation_Base_Project]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[EnergyReport_EnergyWaterConservation] WITH CHECK ADD CONSTRAINT [FK_EnergyReport_EnergyWaterConservation_Sys_User] FOREIGN KEY([CompileMan])
|
|
REFERENCES [dbo].[Sys_User] ([UserId])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[EnergyReport_EnergyWaterConservation] CHECK CONSTRAINT [FK_EnergyReport_EnergyWaterConservation_Sys_User]
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'EnergyWaterConservationId'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'ProjectId'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节能指标（吨标煤）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'EnergySavingIndex'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节水指标（吨）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'WaterSavingIndex'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节能措施及方案' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'EnergySavingSchemes'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节水措施及方案' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'WaterSavingSchemes'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节能节水自评' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'SelfEvaluation'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节能节水总结' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'Summary'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节能节水相关证明资料' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'SupportingDocuments'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实际节能（吨标煤）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'ActualEnergySaving'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'实际节水（吨）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'ActualWaterSaving'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节能完成率' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'EnergySavingCompletionRate'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节水完成率' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'WaterSavingCompletionRate'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编制人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'CompileMan'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编制时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation', @level2type=N'COLUMN',@level2name=N'CompileDate'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'节能节水目标指标' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EnergyReport_EnergyWaterConservation'
|
|
GO
|
|
|
|
|