CREATE TABLE [dbo].[Sys_CQMS_DataInTemp](
	[TempId] [nvarchar](50) NOT NULL,
	[ProjectId] [nvarchar](50) NULL,
	[UserId] [nvarchar](50) NULL,
	[Time] [datetime] NULL,
	[RowNo] [int] NULL,
	[Value1] [nvarchar](100) NULL,
	[Value2] [nvarchar](100) NULL,
	[Value3] [nvarchar](100) NULL,
	[Value4] [nvarchar](100) NULL,
	[Value5] [nvarchar](100) NULL,
	[Value6] [nvarchar](100) NULL,
	[Value7] [nvarchar](100) NULL,
	[Value8] [nvarchar](100) NULL,
	[Value9] [nvarchar](100) NULL,
	[Value10] [nvarchar](100) NULL,
	[Value11] [nvarchar](100) NULL,
	[Value12] [nvarchar](100) NULL,
	[Value13] [nvarchar](100) NULL,
	[Value14] [nvarchar](100) NULL,
	[Value15] [nvarchar](100) NULL,
	[Value16] [nvarchar](100) NULL,
	[Value17] [nvarchar](100) NULL,
	[Value18] [nvarchar](100) NULL,
	[Value19] [nvarchar](100) NULL,
	[Value20] [nvarchar](100) NULL,
	[ToopValue] [nvarchar](4000) NULL,
	[Type] [nvarchar](50) NULL,
 CONSTRAINT [PK_Sys_CQMS_DataInTemp] PRIMARY KEY CLUSTERED 
(
	[TempId] 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

ALTER TABLE [dbo].[Sys_CQMS_DataInTemp]  WITH CHECK ADD  CONSTRAINT [FK_Sys_CQMS_DataInTemp_Base_Project] FOREIGN KEY([ProjectId])
REFERENCES [dbo].[Base_Project] ([ProjectId])
GO

ALTER TABLE [dbo].[Sys_CQMS_DataInTemp] CHECK CONSTRAINT [FK_Sys_CQMS_DataInTemp_Base_Project]
GO

ALTER TABLE [dbo].[Sys_CQMS_DataInTemp]  WITH CHECK ADD  CONSTRAINT [FK_Sys_CQMS_DataInTemp_Sys_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[Sys_User] ([UserId])
GO

ALTER TABLE [dbo].[Sys_CQMS_DataInTemp] CHECK CONSTRAINT [FK_Sys_CQMS_DataInTemp_Sys_User]
GO


insert into Sys_Menu(MenuId,MenuName,Url,SortIndex,SuperMenu,MenuType,IsOffice,IsEnd,IsUsed)
values('2C49EC5E-BF6F-4C44-BA02-9BD183CF68C6','特设质保体系','CQMS/Comprehensive/QualityAssurance.aspx',190,'751BFAB7-1589-4CCF-AF6C-85B214D94E6D','Menu_CQMS',1,1,1)
go
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
values('346AB6FE-C031-40F7-BD28-06A7B625B388','2C49EC5E-BF6F-4C44-BA02-9BD183CF68C6','增加',1)
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
values('8627240E-5308-42AE-8D76-3C7B60F5F89A','2C49EC5E-BF6F-4C44-BA02-9BD183CF68C6','修改',2)
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
values('EBF65B39-C1D5-4B62-B08A-332B01852368','2C49EC5E-BF6F-4C44-BA02-9BD183CF68C6','删除',3)
insert into Sys_ButtonToMenu(ButtonToMenuId,MenuId,ButtonName,SortIndex)
values('5D6DC189-EBF8-4144-94E7-3455EE8CEFED','2C49EC5E-BF6F-4C44-BA02-9BD183CF68C6','保存',4)
GO


ALTER VIEW [dbo].[View_License_LicenseManager]
AS
/*现场安全许可证*/
SELECT LicenseManager.LicenseManagerId, 
	LicenseManager.ProjectId, 
	LicenseManager.LicenseTypeId, 
	LicenseManager.LicenseManagerCode, 
	LicenseManager.LicenseManageName, 
	LicenseManager.UnitId, 
	LicenseManager.LicenseManageContents, 
	LicenseManager.CompileMan, 
	LicenseManager.CompileDate, 
	LicenseManager.States,
	LicenseManager.IsHighRisk,
	Project.ProjectCode,
	Project.ProjectName,
	Project.IsCNCECShow,
	LicenseType.LicenseTypeName,
	Unit.UnitName,
	LicenseManager.IsUpdate,
	UnitType.UnitTypeName,
	Users.PersonName,
	LicenseManager.WorkAreaId,
	--WorkArea.WorkAreaName,
	LicenseManager.StartDate,
	LicenseManager.EndDate,
	LicenseManager.ApplicantMan,
	case LicenseManager.WorkStates when '1' then '待开工'
	                               when '2' then '作业中'
								   when '3' then '已关闭'
								   when '-1' then '已取消'
								   else '' end as WorkStatesStr,
	WorkAreaName = STUFF(( SELECT ',' + UnitWorkName FROM WBS_UnitWork
				    where PATINDEX('%,' + RTRIM(WBS_UnitWork.UnitWorkId) + ',%',',' + LicenseManager.WorkAreaId + ',')>0
					ORDER BY PATINDEX('%,' + RTRIM(LicenseManager.WorkAreaId) + ',%',',' + LicenseManager.WorkAreaId + ',')
					FOR XML PATH('')), 1, 1,'')
FROM dbo.License_LicenseManager AS LicenseManager
LEFT JOIN dbo.Base_Project AS Project ON Project.ProjectId = LicenseManager.ProjectId
LEFT JOIN dbo.Base_LicenseType AS LicenseType ON LicenseType.LicenseTypeId = LicenseManager.LicenseTypeId
LEFT JOIN dbo.Base_Unit AS Unit ON Unit.UnitId = LicenseManager.UnitId
LEFT JOIN dbo.Person_Persons AS Users ON Users.PersonId = LicenseManager.CompileMan
LEFT JOIN dbo.WBS_UnitWork AS WorkArea ON WorkArea.UnitWorkId = LicenseManager.WorkAreaId
LEFT JOIN dbo.Base_UnitType as UnitType ON UnitType.UnitTypeId=Unit.UnitTypeId


GO