1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
DECLARE
@Free1 as varchar(20),
@Free2 as varchar(20),
@Free3 as varchar(20)
SELECT @Free1 = Free1 from x37 where LanguageId = '3' AND PersonType = '1'
SELECT @Free2 = Free2 from x37 where LanguageId = '3' AND PersonType = '1'
Select
x.PersonId as 'Id'
,x.PersonName as 'Nom'
,x.Initials as 'Prénom'
,CASE
WHEN x.PersonType = '1' then 'Employe'
WHEN x.PersonType = '2' then 'Visiteur'
WHEN x.PersonType = '3' then 'Prestataire'
WHEN x.PersonType = '5' then 'Employe temporaire'
end as 'Type de personne'
,x.TelephoneNr as 'N° de téléphone'
,d.DepartmentName as 'Département'
,x.Firm as 'Société'
,x.StartValidity as 'Début de validité'
,x.EndValidity AS 'Fin de validité'
,x.StartEmploymentDate as 'Début de contrat'
,x.EndEmploymentDate as 'Fin de contrat'
,x.Remarks as 'Remarque'
,x.VisitorOf as 'Contact'
,x.PersonNr as 'N° Personnel'
,x.BadgeId as 'N° de badge'
,CASE
WHEN b.BadgeStatus = '1' then 'Affecté'
WHEN b.BadgeStatus = '3' then 'Bloqué'
WHEN b.BadgeStatus = '4' then 'Defectueux'
WHEN b.BadgeStatus = '5' then 'Perdu'
WHEN b.BadgeStatus IS NULL then ''
end as 'Statut du badge'
,pr.Description as 'Profil'
,x.[Free1] as '@Free1'
,x.[Free2] as '@Free2'
,x.[Free3]
,x.[Free4]
,x.[Free5]
,x.[Free6]
,x.[Free7]
,x.[Free8]
,x.[Free9]
,x.[Free10]
,x.[Free11]
,x.[Free12]
,x.[Free13]
,x.[Free14]
,x.[Free15]
,x.[Free16]
,x.[Free17]
,x.[Free18]
,x.[Free19]
,x.[Free20]
,x.[Free21]
,x.[Free22]
,x.[Free23]
,x.[Free24]
,x.[Free25]
,x.[Free26]
,x.[Free27]
,x.[Free28]
,x.[Free29]
,b.EventDateTimeStamp AS 'Dernier passage'
--,t.Name as 'Groupe' -- A activer pour les Groupes supplementaires
--,h.Description as 'Plagehoraire' -- A activer pour les Groupes supplementaires
from x36 x
--LEFT JOIN x13 p ON p.PersonId=x.PersonId -- A activer pour les Groupes supplementaires
--LEFT JOIN x06 t ON p.AccGroupId=t.AccGroupId -- A activer pour les Groupes supplementaires
--LEFT JOIN x16 h ON p.TimeDayzoneId=h.TimeDayzoneId -- A activer pour les Groupes supplementaires
LEFT JOIN x53 d ON x.DepartmentId = d.DepartmentId
LEFT JOIN x48 pr ON x.ProfileId = pr.ProfileId
LEFT JOIN x12 b ON x.BadgeId = b.BadgeId
order by x.PersonName,x.Initials,x.BadgeId |
