209 lines
11 KiB
Transact-SQL
209 lines
11 KiB
Transact-SQL
|
|
|
|
--1、问题类别定义修改为紧急程度定义
|
|
update Sys_Menu set MenuName='紧急程度定义' where MenuId='3044D68E-5018-4B57-BFC4-FBE4BCCA8B8B'
|
|
GO
|
|
--2、增加事项类别
|
|
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GJSX' AND COLUMN_NAME = 'GJSXTypeId')
|
|
BEGIN
|
|
ALTER TABLE GJSX ADD GJSXTypeId varchar(8000);
|
|
ALTER TABLE GJSX ADD ProgressStatus char(1);
|
|
END
|
|
GO
|
|
--关键事项类别定义
|
|
IF NOT EXISTS (SELECT * FROM Sys_Menu WHERE MenuId = 'C7E69F5F-04A8-41F9-8EA1-C41B13248421')
|
|
BEGIN
|
|
INSERT INTO dbo.Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuType,IsOffice,IsEnd,IsUsed)
|
|
VALUES('C7E69F5F-04A8-41F9-8EA1-C41B13248421','关键事项类别定义','BaseInfo/GJSXType.aspx',160,'5196A6FD-4BF1-46B3-8D24-9A3CE5BB4760','Menu_SysSet',1,1,1)
|
|
END
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT * FROM Sys_ButtonToMenu WHERE MenuId = 'C7E69F5F-04A8-41F9-8EA1-C41B13248421')
|
|
BEGIN
|
|
--关键事项类别定义
|
|
INSERT INTO dbo.Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
VALUES('7F9A2B0B-10F7-408A-BAAB-A2E063830FAD','C7E69F5F-04A8-41F9-8EA1-C41B13248421','增加',1)
|
|
INSERT INTO dbo.Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
VALUES('A7CC9EED-EE05-4B78-9CD0-BCD7DDD071A0','C7E69F5F-04A8-41F9-8EA1-C41B13248421','修改',2)
|
|
INSERT INTO dbo.Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
VALUES('4A0E13C4-602C-4963-BC3E-2E0AEC1E06E3','C7E69F5F-04A8-41F9-8EA1-C41B13248421','删除',3)
|
|
INSERT INTO dbo.Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
|
|
VALUES('AC64BD89-2793-48D0-A881-70BDCDF87BCA','C7E69F5F-04A8-41F9-8EA1-C41B13248421','保存',4)
|
|
END
|
|
GO
|
|
|
|
|
|
--关键事项类别
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Base_GJSXType') AND type = N'U')
|
|
BEGIN
|
|
CREATE TABLE [dbo].[Base_GJSXType](
|
|
[GJSXTypeID] [nvarchar](50) NOT NULL,
|
|
[GJSXTypeCode] [nvarchar](50) NOT NULL,
|
|
[GJSXTypeName] [nvarchar](50) NOT NULL,
|
|
[ProjectId] [nvarchar](50) NULL,
|
|
CONSTRAINT [PK_Base_GJSXType] PRIMARY KEY CLUSTERED
|
|
(
|
|
[GJSXTypeID] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
ALTER TABLE [dbo].[Base_GJSXType] WITH CHECK ADD CONSTRAINT [FK_Base_GJSXType_Base_Project] FOREIGN KEY([ProjectId])
|
|
REFERENCES [dbo].[Base_Project] ([ProjectId])
|
|
ALTER TABLE [dbo].[Base_GJSXType] CHECK CONSTRAINT [FK_Base_GJSXType_Base_Project]
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_GJSXType', @level2type=N'COLUMN',@level2name=N'GJSXTypeID'
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别Code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_GJSXType', @level2type=N'COLUMN',@level2name=N'GJSXTypeCode'
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_GJSXType', @level2type=N'COLUMN',@level2name=N'GJSXTypeName'
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_GJSXType', @level2type=N'COLUMN',@level2name=N'ProjectId'
|
|
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'关键事项类别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_GJSXType'
|
|
END
|
|
GO
|
|
--关键事项类别初始数据
|
|
IF NOT EXISTS (SELECT * FROM Base_GJSXType)
|
|
BEGIN
|
|
INSERT INTO Base_GJSXType(GJSXTypeID,GJSXTypeCode,GJSXTypeName)
|
|
SELECT 'E3D94017-C68C-497C-BED2-5AFA261BDBBB','001','设计图纸'
|
|
INSERT INTO Base_GJSXType(GJSXTypeID,GJSXTypeCode,GJSXTypeName)
|
|
SELECT '9D1743BC-53C7-4EF4-94F0-468D5695F8FC','002','采购材料'
|
|
INSERT INTO Base_GJSXType(GJSXTypeID,GJSXTypeCode,GJSXTypeName)
|
|
SELECT '2770598E-502C-4CA8-89E6-FE3753AAF7D0','003','进度控制'
|
|
INSERT INTO Base_GJSXType(GJSXTypeID,GJSXTypeCode,GJSXTypeName)
|
|
SELECT 'DCA279F3-EA8D-477E-BBF5-A8D920CA8781','004','费用控制'
|
|
INSERT INTO Base_GJSXType(GJSXTypeID,GJSXTypeCode,GJSXTypeName)
|
|
SELECT 'E40A5FE5-F19B-416D-8999-3ABC8C5DAC15','005','施工进度'
|
|
INSERT INTO Base_GJSXType(GJSXTypeID,GJSXTypeCode,GJSXTypeName)
|
|
SELECT 'A1096545-28EB-40DE-BAAE-A05E0F80CBB2','006','施工质量'
|
|
INSERT INTO Base_GJSXType(GJSXTypeID,GJSXTypeCode,GJSXTypeName)
|
|
SELECT '1080D3FB-1351-4FF0-943F-608549D36FF4','007','HSE管理'
|
|
INSERT INTO Base_GJSXType(GJSXTypeID,GJSXTypeCode,GJSXTypeName)
|
|
SELECT '8B4CE458-466A-4028-BF99-429DD4578AAF','008','供应商事宜'
|
|
INSERT INTO Base_GJSXType(GJSXTypeID,GJSXTypeCode,GJSXTypeName)
|
|
SELECT '41D56485-9502-44FE-9CC7-87069ADB93A8','009','分包商事宜'
|
|
END
|
|
GO
|
|
|
|
|
|
|
|
ALTER PROCEDURE [dbo].[Sp_GJSX_getlist]
|
|
-- Add the parameters for the stored procedure here
|
|
@ProjectId nvarchar(max),
|
|
@sql_where varchar(max)
|
|
AS
|
|
BEGIN
|
|
--IF EXISTS(select * from tempdb..sysobjects where id=object_id('tempdb..#GJSX_List'))
|
|
--BEGIN
|
|
-- DROP TABLE #GJSX_List
|
|
--END
|
|
--DECLARE @Sql NVARCHAR(200)
|
|
--select * into #GJSX_List from(
|
|
-- select
|
|
--a.GJSXID
|
|
--,a.ProjectId
|
|
--,a.detail
|
|
--,b.username
|
|
--,a.createDate
|
|
--,User_ReceiveID=STUFF(( SELECT ',' + p2.UserName FROM dbo.Sys_User as p2 where PATINDEX('%,' + RTRIM(p2.UserId) + ',%',',' +a.User_ReceiveID + ',') >0 FOR XML PATH('')), 1, 1,'')
|
|
--, CNProfessionalId=STUFF(( SELECT ',' + Base_CNProfessional.ProfessionalName FROM dbo.Base_CNProfessional where PATINDEX('%,' + RTRIM(Base_CNProfessional.CNProfessionalId) + ',%',',' +a.CNProfessional_ID + ',') >0 FOR XML PATH('')), 1, 1,'')
|
|
--,Base_Project.ProjectName
|
|
--,Base_Unit.unitname
|
|
--,a.CloseDate
|
|
--,case a.state when 2 then '正在进行' when 0 then '关闭' when 1 then '开放' end as state
|
|
--,QuestionTypeName=STUFF(( SELECT ',' + Base_QuestionType.QuestionTypeName FROM dbo.Base_QuestionType where PATINDEX('%,' + RTRIM(Base_QuestionType.QuestionTypeID) + ',%',',' +a.QuestionTypeID + ',') >0 FOR XML PATH('')), 1, 1,'')
|
|
--,a.IsManypeople
|
|
--,a.CompleteDate
|
|
--,a.AttachUrl
|
|
--,user_Acceptance =STUFF(( SELECT ',' + p2.UserName FROM dbo.Sys_User as p2 where PATINDEX('%,' + RTRIM(p2.UserId) + ',%',',' +a.user_Acceptance + ',') >0 FOR XML PATH('')), 1, 1,'')
|
|
--from GJSX as a left join Sys_User as b on a.UserId=b.UserId
|
|
--left join [dbo].[Base_Project] on a.ProjectId =Base_Project.ProjectId
|
|
--left join [dbo].[Base_Unit] on a.UnitId=Base_Unit.UnitId
|
|
--left join [dbo].[Base_QuestionType] on a.QuestionTypeID=Base_QuestionType.QuestionTypeID ) as a
|
|
|
|
--set @Sql ='select * from #GJSX_List where ProjectId='''+@ProjectId+''' ' +@sql_where
|
|
|
|
--exec(@Sql)
|
|
|
|
select
|
|
a.GJSXID
|
|
,a.ProjectId
|
|
,a.detail
|
|
,b.username
|
|
,a.createDate
|
|
,User_ReceiveID=STUFF(( SELECT ',' + p2.UserName FROM dbo.Sys_User as p2 where PATINDEX('%,' + RTRIM(p2.UserId) + ',%',',' +a.User_ReceiveID + ',') >0 FOR XML PATH('')), 1, 1,'')
|
|
, CNProfessionalId=STUFF(( SELECT ',' + Base_CNProfessional.ProfessionalName FROM dbo.Base_CNProfessional where PATINDEX('%,' + RTRIM(Base_CNProfessional.CNProfessionalId) + ',%',',' +a.CNProfessional_ID + ',') >0 FOR XML PATH('')), 1, 1,'')
|
|
,Base_Project.ProjectName
|
|
,Base_Unit.unitname
|
|
,a.CloseDate
|
|
,case a.state when 2 then '正在进行' when 3 then '待办' when 0 then '已关闭' when 1 then '开放' end as state
|
|
,QuestionTypeName=STUFF(( SELECT ',' + Base_QuestionType.QuestionTypeName FROM dbo.Base_QuestionType where PATINDEX('%,' + RTRIM(Base_QuestionType.QuestionTypeID) + ',%',',' +a.QuestionTypeID + ',') >0 FOR XML PATH('')), 1, 1,'')
|
|
,GJSXTypeName = STUFF((SELECT ',' + Base_GJSXType.GJSXTypeName FROM dbo.Base_GJSXType where PATINDEX('%,' + RTRIM(Base_GJSXType.GJSXTypeID) + ',%', ',' + a.GJSXTypeID + ',') > 0 FOR XML PATH('')), 1, 1,'')
|
|
,a.IsManypeople
|
|
,a.CompleteDate
|
|
,a.AttachUrl
|
|
,user_Acceptance =STUFF(( SELECT ',' + p2.UserName FROM dbo.Sys_User as p2 where PATINDEX('%,' + RTRIM(p2.UserId) + ',%',',' +a.user_Acceptance + ',') >0 FOR XML PATH('')), 1, 1,'')
|
|
from GJSX as a left join Sys_User as b on a.UserId=b.UserId
|
|
left join [dbo].[Base_Project] on a.ProjectId =Base_Project.ProjectId
|
|
left join [dbo].[Base_Unit] on a.UnitId=Base_Unit.UnitId
|
|
--left join [dbo].[Base_QuestionType] on a.QuestionTypeID=Base_QuestionType.QuestionTypeID
|
|
--left join[dbo].[Base_GJSXType] on a.GJSXTypeID = Base_GJSXType.GJSXTypeID
|
|
where a.ProjectId=@ProjectId
|
|
END
|
|
GO
|
|
|
|
|
|
|
|
ALTER PROCEDURE [dbo].[Sp_GJSXStatistic]
|
|
(
|
|
@UserID nvarchar(50)=null, --提出人
|
|
@unit nvarchar(50)=null, --责任单位
|
|
@projectId nvarchar(50) =null, --所属项目
|
|
@CNProfessional_ID nvarchar(500) =null, --专业
|
|
@questionType nvarchar(500)=null, --紧急程度
|
|
@GJSXType nvarchar(1000)=null, --事项类别
|
|
@processMan nvarchar(50) =null, --接收人
|
|
@User_Acceptance nvarchar(50) =null --验收人
|
|
)
|
|
AS
|
|
|
|
select
|
|
count(*) as allcount,
|
|
(select count(*) from GJSX as z where z.state='0' and
|
|
(z.UserID=@UserID or @UserID is null) and
|
|
(UnitId = @unit or @unit is null) and
|
|
(ProjectId=@projectId or @projectId is null) and
|
|
(CNProfessional_ID like '%' + @CNProfessional_ID + '%' or @CNProfessional_ID is null) and
|
|
(QuestionTypeID like '%' + @questionType + '%' or @questionType is null) and
|
|
(GJSXTypeID like '%' + @GJSXType + '%' or @GJSXType is null) and
|
|
(z.User_Acceptance like '%' + @User_Acceptance + '%' or @User_Acceptance is null) and
|
|
(@processMan is null or (GJSXID in (select distinct GJSXID from GJSX_Process where UserId=@processMan)))) as closecount,
|
|
(select count(*) from GJSX as z where z.state='1' and
|
|
(z.UserID=@UserID or @UserID is null) and
|
|
(UnitId = @unit or @unit is null) and
|
|
(ProjectId=@projectId or @projectId is null) and
|
|
(CNProfessional_ID like '%' + @CNProfessional_ID + '%' or @CNProfessional_ID is null) and
|
|
(QuestionTypeID like '%' + @questionType + '%' or @questionType is null) and
|
|
(GJSXTypeID like '%' + @GJSXType + '%' or @GJSXType is null) and
|
|
(z.User_Acceptance like '%' + @User_Acceptance + '%' or @User_Acceptance is null) and
|
|
(@processMan is null or (GJSXID in (select distinct GJSXID from GJSX_Process where UserId=@processMan)))) as opencount,
|
|
(select count(*) from GJSX as z where z.state='2' and
|
|
(z.UserID=@UserID or @UserID is null) and
|
|
(UnitId = @unit or @unit is null) and
|
|
(ProjectId=@projectId or @projectId is null) and
|
|
(CNProfessional_ID like '%' + @CNProfessional_ID + '%' or @CNProfessional_ID is null) and
|
|
(QuestionTypeID like '%' + @questionType + '%' or @questionType is null) and
|
|
(GJSXTypeID like '%' + @GJSXType + '%' or @GJSXType is null) and
|
|
(z.User_Acceptance like '%' + @User_Acceptance + '%' or @User_Acceptance is null)and
|
|
(@processMan is null or (GJSXID in (select distinct GJSXID from GJSX_Process where UserId=@processMan)))) as proceingcount
|
|
from GJSX
|
|
where
|
|
(UserID=@UserID or @UserID is null) and
|
|
(UnitId = @unit or @unit is null) and
|
|
(GJSX.ProjectId=@projectId or @projectId is null) and
|
|
(GJSX.CNProfessional_ID like '%' + @CNProfessional_ID + '%' or @CNProfessional_ID is null) and
|
|
(GJSX.QuestionTypeID like '%' + @questionType + '%' or @questionType is null) and
|
|
(GJSX.GJSXTypeID like '%' + @GJSXType + '%' or @GJSXType is null) and
|
|
(GJSX.User_Acceptance like '%' + @User_Acceptance + '%' or @User_Acceptance is null) and
|
|
(@processMan is null or (GJSXID in (select distinct GJSXID from GJSX_Process where UserId=@processMan)))
|
|
|
|
GO
|
|
|
|
|
|
|