42 lines
1.2 KiB
MySQL
42 lines
1.2 KiB
MySQL
|
|
|||
|
|
|
|||
|
|
/****** Object: View [dbo].[View_SitePerson_Person] Script Date: 2022/11/9 <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD> 20:01:31 ******/
|
|||
|
|
SET ANSI_NULLS ON
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
SET QUOTED_IDENTIFIER ON
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
CREATE VIEW [dbo].[View_SitePerson_PersonList]
|
|||
|
|
AS
|
|||
|
|
|
|||
|
|
SELECT
|
|||
|
|
Person.PersonId,
|
|||
|
|
Person.ProjectId,
|
|||
|
|
Person.IsUsed,
|
|||
|
|
Person.CardNo,
|
|||
|
|
Person.PersonName,
|
|||
|
|
Person.UnitId,
|
|||
|
|
Unit.UnitName,
|
|||
|
|
Post.WorkPostName,
|
|||
|
|
WorkAreaName= STUFF((SELECT ',' + UnitWorkName FROM dbo.WBS_UnitWork where PATINDEX('%,' + RTRIM(UnitWorkId) + ',%',',' +Person.WorkAreaId + ',')>0 FOR XML PATH('')), 1, 1,''),
|
|||
|
|
TeamGroup.TeamGroupName,
|
|||
|
|
Person.InTime,
|
|||
|
|
Person.OutTime,
|
|||
|
|
(CASE Person.IsUsed WHEN 'TRUE' THEN '<EFBFBD><EFBFBD>' ELSE '<EFBFBD><EFBFBD>' END) AS IsUsedName,
|
|||
|
|
Person.IdentityCard,
|
|||
|
|
Person.TeamGroupId,
|
|||
|
|
Person.WorkPostId,
|
|||
|
|
Person.IdcardType,
|
|||
|
|
Person.PhotoUrl,
|
|||
|
|
edu.TrainCount
|
|||
|
|
FROM SitePerson_Person AS Person
|
|||
|
|
LEFT JOIN Base_Unit AS Unit ON Unit.UnitId = Person.UnitId
|
|||
|
|
LEFT JOIN ProjectData_TeamGroup AS TeamGroup ON TeamGroup.TeamGroupId=Person.TeamGroupId
|
|||
|
|
LEFT JOIN Base_WorkPost AS Post ON Post.WorkPostId = Person.WorkPostId
|
|||
|
|
LEFT JOIN (select PersonId,count(PersonId)as TrainCount from EduTrain_TrainRecordDetail group by PersonId) as edu on edu.PersonId= Person.PersonId
|
|||
|
|
|
|||
|
|
GO
|
|||
|
|
|
|||
|
|
|