141 lines
7.1 KiB
Transact-SQL
141 lines
7.1 KiB
Transact-SQL
|
|
insert into Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuModule)
|
|
values('D4E0EBE8-C13B-4E61-8765-EE7BA77BFAB1','管道试压包一览表','SYBData/PressureTestPackageList.aspx',40,'3EAFF140-E5CA-4978-B83D-D2C9F36E0D52','3')
|
|
go
|
|
insert into ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('246D9514-04C2-410F-A9B9-47FB4A2FB2C9','D4E0EBE8-C13B-4E61-8765-EE7BA77BFAB1','增加',1)
|
|
insert into ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('0970DAF6-0BEC-4362-871E-E2B2036CFD13','D4E0EBE8-C13B-4E61-8765-EE7BA77BFAB1','修改',2)
|
|
insert into ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('0171DAC6-F8C9-4803-B934-48F824A6F332','D4E0EBE8-C13B-4E61-8765-EE7BA77BFAB1','删除',3)
|
|
insert into ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('4E8F0CB9-BF6B-4041-AD7C-9D8E917D2F48','D4E0EBE8-C13B-4E61-8765-EE7BA77BFAB1','保存',4)
|
|
go
|
|
|
|
CREATE TABLE [dbo].[SYBData_PressureTestPackageList](
|
|
[PressureTestPackageListId] [nvarchar](50) NOT NULL,
|
|
[ProjectId] [nvarchar](50) NULL,
|
|
[SerialNumber] [nvarchar](50) NULL,
|
|
[PipelineCode] [nvarchar](50) NULL,
|
|
[PressureTestPackageCode] [nvarchar](50) NULL,
|
|
[Remark] [nvarchar](500) NULL,
|
|
[CompileMan] [nvarchar](50) NULL,
|
|
[CompileDate] [datetime] NULL,
|
|
CONSTRAINT [PK_SYBData_PressureTestPackageList] PRIMARY KEY CLUSTERED
|
|
(
|
|
[PressureTestPackageListId] 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].[SYBData_PressureTestPackageList] WITH CHECK ADD CONSTRAINT [FK_SYBData_PressureTestPackageList_Base_Project] FOREIGN KEY([ProjectId])
|
|
REFERENCES [dbo].[Base_Project] ([ProjectId])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[SYBData_PressureTestPackageList] CHECK CONSTRAINT [FK_SYBData_PressureTestPackageList_Base_Project]
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYBData_PressureTestPackageList', @level2type=N'COLUMN',@level2name=N'PressureTestPackageListId'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYBData_PressureTestPackageList', @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'SYBData_PressureTestPackageList', @level2type=N'COLUMN',@level2name=N'SerialNumber'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'管道编号/单线号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYBData_PressureTestPackageList', @level2type=N'COLUMN',@level2name=N'PipelineCode'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'试压包编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYBData_PressureTestPackageList', @level2type=N'COLUMN',@level2name=N'PressureTestPackageCode'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYBData_PressureTestPackageList', @level2type=N'COLUMN',@level2name=N'Remark'
|
|
GO
|
|
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编制人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYBData_PressureTestPackageList', @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'SYBData_PressureTestPackageList', @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'SYBData_PressureTestPackageList'
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[SYB_TestPackagePipeList]
|
|
(
|
|
@projectId nvarchar(50),
|
|
@iso_no nvarchar(50)=null
|
|
)
|
|
/***************管道试压包一览表*********************/
|
|
AS
|
|
create table #SIS(
|
|
Number1 INT,
|
|
PipelineCode1 nvarchar(50),
|
|
TestPackageNo1 nvarchar(50),
|
|
Number2 INT,
|
|
PipelineCode2 nvarchar(50),
|
|
TestPackageNo2 nvarchar(50)
|
|
)
|
|
|
|
BEGIN
|
|
DECLARE My_Cursor CURSOR
|
|
FOR SELECT ROW_NUMBER() OVER(ORDER BY pp.PipelineCode,pp.PressureTestPackageCode) AS Number,
|
|
pp.PipelineCode,pp.PressureTestPackageCode
|
|
FROM dbo.SYBData_PressureTestPackageList pp
|
|
--LEFT JOIN dbo.Pipeline_Pipeline pipe ON pipe.PipelineId = pp.PipelineId
|
|
--LEFT JOIN dbo.PTP_TestPackage pages ON pages.PTP_ID = pp.PTP_ID
|
|
WHERE pp.ProjectId=@projectId
|
|
and (pp.PipelineCode like @iso_no or @iso_no is null)
|
|
ORDER BY pp.PipelineCode,pp.PressureTestPackageCode
|
|
|
|
OPEN My_Cursor; --打开游标
|
|
DECLARE @Number int,@PipelineCode nvarchar(50),@TestPackageNo nvarchar(50)
|
|
|
|
FETCH NEXT FROM My_Cursor into @Number,@PipelineCode,@TestPackageNo; --读取第一行数据
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
IF(@Number%2=1)
|
|
BEGIN
|
|
INSERT into #SIS values(@Number,@PipelineCode,@TestPackageNo,null,null,null)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE #SIS SET Number2=@Number,PipelineCode2=@PipelineCode,TestPackageNo2=@TestPackageNo
|
|
WHERE Number1=@Number-1
|
|
END
|
|
FETCH NEXT FROM My_Cursor into @Number,@PipelineCode,@TestPackageNo; --读取第下一行数据
|
|
END
|
|
CLOSE My_Cursor; --关闭游标
|
|
DEALLOCATE My_Cursor; --释放游标
|
|
|
|
END
|
|
SELECT * from #SIS
|
|
DROP TABLE #SIS
|
|
|
|
GO
|
|
insert into Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuModule)
|
|
values('ECFED5F8-59ED-4CF8-9FF4-893E095CBDF8','管道焊接工作记录','JGZL/PipeWeldingWorkRecord.aspx',80,'3EAFF140-E5CA-4978-B83D-D2C9F36E0D52','3')
|
|
go
|
|
insert into Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuModule)
|
|
values('720F843F-3FDD-4072-9724-B320BDB9B6E0','管道系统压力实验条件确认记录','JGZL/PressureTestOfPipelineSystemConfirmationRecord.aspx',90,'3EAFF140-E5CA-4978-B83D-D2C9F36E0D52','3')
|
|
go
|
|
insert into Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuModule)
|
|
values('91F25D94-7A6E-422E-A5C8-7485A3D8B155','管道系统压力实验记录','JGZL/PressureTestOfPipelineSystemRecord.aspx',100,'3EAFF140-E5CA-4978-B83D-D2C9F36E0D52','3')
|
|
go
|
|
|
|
insert into Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuModule)
|
|
values('784D8370-C0E0-4C81-AC85-AA72BAB6188B','班组设置','WeldMat/BaseInfo/TeamGrouop.aspx',40,'5BEECA92-7901-4D2B-843F-2053A085B9F3','7')
|
|
go
|
|
insert into ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('954F82BE-D41F-4D82-8E05-DA853A3769D2','784D8370-C0E0-4C81-AC85-AA72BAB6188B','增加',1)
|
|
insert into ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('6AD64688-2927-4F9A-94B4-E5D38691C03E','784D8370-C0E0-4C81-AC85-AA72BAB6188B','修改',2)
|
|
insert into ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('69CACA4F-21A8-4C5D-8D1A-29845CF73563','784D8370-C0E0-4C81-AC85-AA72BAB6188B','删除',3)
|
|
insert into ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
values('AF575CDF-5300-4FED-91F1-59AA7731C9DF','784D8370-C0E0-4C81-AC85-AA72BAB6188B','保存',4)
|
|
go
|
|
|
|
alter table Base_TeamGroup add IsUsed bit
|
|
go |