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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
| declare @id_emport as integer;
declare @id_vol as integer;
declare @dateDeb datetime
declare @dateFin datetime
declare @codeIata varchar(10)
declare @listeCie varchar(50)
declare @listeLigne varchar(50)
declare @listeCre varchar(50)
declare @classeBag as bit
declare @listeStatutSurete varchar(50)
declare @listeStatutTache varchar(50)
declare @listeTroncon varchar(50)
--Via l'Id emport
set @id_emport = null
--Via l'Id vol
set @id_vol = 281646
--Via le filtre de recherche
set @dateDeb = null
set @dateFin = null
set @listeCie = null --'TG'
set @listeLigne = null
set @listeCre = null
set @classeBag = null
set @listeStatutSurete = null
set @listeStatutTache = null
set @listeTroncon = null
USE [CDG1]
select o.ordre_apparition, b.id_vol, b.id_bsm, tgb.id_emport,
-- DETAILS
'CodeIata' = b.CODE_IATA, 'Compagnie' = b.CIEE, 'Ligne' = b.Lige, 'JourExploitation' = b.Jexe, 'Creation' = b.DAT_CRE,
master.dbo.OrigineBsm(b.CRE) as OrigineCreation, 'Recolle' = b.RECOL, master.dbo.SourceIndicator(b.typ) as SourceIndicator,
'Doublon' = b.DUP, 'Embarquable' = b.EMB, 'ReRoutage' = b.CHGVOL, 'Suppression' = b.SUP,
master.dbo.getParticularites(b.id_bsm) as 'Particularites',
--ITINERIARE
'AeroportContinuation' = b.ITI, 'Continuation' = b.CNT, 'AeroportDestination' = b.AER_DES,
--PASSAGERS
'Noms' = b.NOM_PASSAGER, master.dbo.PassagerStatus(b.STA) as PassagerStatus, 'Prioritaire' = b.CLA,
--SURETE
'ResultatControle' = b.ISUR, master.dbo.BagageStatus(b.SSUR) as BagageStatus, master.dbo.SecurityMethod(B.TSUR) as SecurityMethod,
'Signature' = B.GSUR, 'ForcageN3' = b.SSURC,
st.nom_res as Localisation,
master.dbo.CodeTrace(48, o.STATUT_SURETE_EJECTION) as StatutSurete, 'Commentaire' = b.CSUR,
tgb.CAR_CLASS as CarClass, tgb.Statut_temporel as SatatuTemporel
FROM BSM b
-- left outer join OCCURENCE_BAGAGE o on o.id_Bagage = master.dbo.FunctGetIdBag(b.id_bsm)
left outer join OCCURENCE_BAGAGE o on o.id_bsm = b.id_bsm
left outer join BSM_VOL_APPORT bva on bva.ID_BSM = b.ID_BSM
left outer join tac_grp_bags tgb on tgb.id_tache = o.id_tache_1
left outer join Sortie_tri st on st.id_res = tgb.id_res
--left outer join TRACE_BAGAGE t on type = 80 and o.ID_BAGAGE = t.ID_BAGAGE and statut = 0 -- Trace d'éjection bagage
WHERE (not (SUP=1 and RECOL=0))
and
(
(@id_emport is not null and b.Id_BSM in
(
select bsm.id_bsm from bsm where bsm.id_vol in
(
select id_vol from vol_depart where vol_depart.id_emport = @id_emport
)
)
)
or
(@id_emport is null and @id_vol is not null and b.Id_BSM in (select bsm.id_bsm from bsm where bsm.id_vol = @id_vol))
or
(@id_emport is null and @id_vol is null and
((@listeCie is not null and charindex(','+ltrim(rtrim(b.CIEE))+',', ','+@listeCie+',')>0)
or @listeCie is null)
and
((@listeCre is not null and charindex(','+b.cre+',', ','+@listeCre+',')>0)
or @listeCre is null)
and
((@listeLigne is not null and charindex(','+ltrim(rtrim(b.LIGE))+',', ','+@listeLigne+',')>0)
or @listeLigne is null)
and
((@codeIata is not null and b.code_Iata like ('%'+@codeIata+'%'))
or @codeIata is null)
and
((@classeBag is not null and b.CLA = @classeBag)
or @classeBag is null)
and
((@listeStatutSurete is not null and charindex(','+ltrim(rtrim(master.dbo.CodeTrace(48, o.STATUT_SURETE_EJECTION)))+',', ','+@listeStatutSurete+',')>0)
or @listeStatutSurete is null)
and
((@listeStatutTache is not null and charindex(','+ltrim(rtrim(tgb.Statut_temporel))+',', ','+@listeStatutTache+',')>0)
or @listeStatutTache is null)
and
((@listeTroncon is not null and charindex(','+ltrim(rtrim(st.nom_res))+',', ','+@listeTroncon+',')>0)
or @listeTroncon is null)
)
)
order by o.id_bsm |
