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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
SELECT DISTINCT tier.nom as cta,
mar.libelle as marque,
d.numerodossier as numero_dossier,
cd.nom, cd.prenom as prenom,
refcom.codereferenceenseigne as code_mercure,
mod.referencetechnique as modele,
f.libelle as famille,
gam.libelle as gamme,
cta.nom as point_depart,
tier.nom as point_arrivee,
tie.nom as transporteur,
pg.libelle as garantie,
mou.datearriveeprevisionnelle,
d.datecreation as date_creation,
e.datecreation as date_derniere_relance,
tete.libelle
FROM t_dossier d
JOIN t_clientdossier cd ON cd.iddossier = d.id
JOIN t_typeclient tc ON tc.id = cd.idtypeclient AND tc.code = 'INTERVENTION'
JOIN t_appareil a ON a.id = d.idappareil
JOIN t_produit p ON p.id = a.idproduit
JOIN t_mouvement mou ON mou.id= (
SELECT DISTINCT max( idaction)
FROM t_actioncomplement
WHERE t_actioncomplement.idtypeaction= 32
AND t_actioncomplement.iddossier = d.id
)
JOIN t_referencecommercialeenseigne refcom ON refcom.id= (
SELECT DISTINCT max(id)
FROM t_referencecommercialeenseigne
WHERE t_referencecommercialeenseigne.idproduit = p.id
)
JOIN
t_modeletechnique mod
ON
mod.id=
(
SELECT DISTINCT
max(id)
FROM
t_modeletechnique
WHERE
t_modeletechnique.idproduit = p.id
)
JOIN
t_marque mar
ON
mar.id= p.idmarque
JOIN
t_gamme gam
ON
gam.id= p.idgamme
JOIN
t_groupe g
ON
g.id = p.idgroupe
JOIN
t_famille f
ON
f.id = p.idfamille
JOIN
t_parametre pg
ON
pg.id = d.idtypegarantie and pg.categorie = 'TypeGarantie'
JOIN
t_actioncomplement ac
ON
ac.iddossier = d.id and ac.id =
(
SELECT DISTINCT
max(id)
FROM
t_actioncomplement
WHERE
t_actioncomplement.iddossier = d.id and t_actioncomplement.cloture=0
)
JOIN
t_etat etat
ON
etat.id= ac.idetat and ac.idtypeaction= 32
JOIN
t_typeetat tete
ON
tete.id =
(
SELECT
max(id)
FROM
t_typeetat tete
WHERE
tete.id = etat .idtypeetat
)
JOIN
t_parametre pac
ON
pac.id = ac.idtypeaction
LEFT OUTER JOIN
t_evenement e
ON
e.iddossier = d.id and e.id =
(
SELECT DISTINCT
max(id)
FROM
t_evenement
WHERE
t_evenement.iddossier = d.id and t_evenement.idtypeaction <> 0 and t_evenement.idtypeevenement = 9
)
JOIN
t_tiers tie
ON
tie.id= mou.idtransporteur
JOIN
t_tiers tier
ON
tier.id= mou.idtierspointarrivee
JOIN
t_tiers cta
ON
cta.id= mou.idtierspointdepart
JOIN
t_parametre ptae
ON
ptae.id = e.idtypeaction and pac.categorie = 'TypeAction'
JOIN
t_typeevenement tev
ON
tev.id = e.idtypeevenement
WHERE
d.cloture = 0 and tev.id <> 4 and d.datecreation >= '2008-01-01 14:47:49' and d.datecreation <= '2008-07-15 18:47:49'
ORDER BY
d.datecreation |
