requete complexe et rendu
et oui me revoila.
Ce coup ci j'essaye d'obtenir un report statistique a partir d'une requête assez complexe..
et bien sur ça ne marche pas.
Voici le schéma en question:
Code:
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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
sfGuardUser:
actAs: [Timestampable]
columns:
first_name: string(255)
last_name: string(255)
email_address:
type: string(255)
notnull: true
unique: true
username:
type: string(128)
notnull: true
unique: true
algorithm:
type: string(128)
default: sha1
notnull: true
salt: string(128)
password: string(128)
is_active:
type: boolean
default: 1
is_super_admin:
type: boolean
default: false
last_login:
type: timestamp
matricule:
type: integer
unique: true
epass:
type: string(20)
unique: true
costcenter_id: { type: integer }
company_id: integer
emailintranet:
type: string(255)
unique: true
site_id: { type: integer }
contrat:
type: string(3)
relationwithdupont:
type: string(5)
esi:
type: boolean
default: 0
grpe_chq_dej_list_id:
type: integer
startdate:
type: date
gardenleavedate:
type: date
offrolldate:
type: date
indexes:
is_active_idx:
fields: [is_active]
unique:
fields: [first_name, last_name]
type: unique
relations:
Groups:
class: sfGuardGroup
local: user_id
foreign: group_id
refClass: sfGuardUserGroup
foreignAlias: Users
Permissions:
class: sfGuardPermission
local: user_id
foreign: permission_id
refClass: sfGuardUserPermission
foreignAlias: Users
Costcenter:
class: Costcenter
local: costcenter_id
foreign: id
onDelete: CASCADE
Site:
local: site_id
foreign: id
onDelete: SET NULL
Company:
local: company_id
foreign: id
grpe_chq_dej_list:
class: GrpeChqDej
local: grpe_chq_dej_list_id
foreign: id
onDelete: SET NULL
groupasset:
actAs:
Timestampable:
Sluggable:
fields: [name, site]
unique: true
canUpdate: true
columns:
name: string(255)
site_id: integer
user_id: integer
status: string(2)
person: string(2)
lastview: date
indexes:
Unique1:
fields: [name, site_id]
type: unique
relations:
owner:
class: sfGuardUser
local: user_id
foreign: id
foreignAlias: groupassets
onDelete: SET NULL
site:
class: Site
local: site_id
foreign: id
onDelete: SET NULL
Asset:
tableName: asset
actAs: [Timestampable]
columns:
typeasset_id: integer
value: string(255)
groupasset_id: integer
etat: string(50)
action: string(50)
chargebycsc:
type: boolean
lastview: date
indexes:
unique1:
fields: [typeasset_id,value,groupasset_id]
type: unique
lastview:
fields:
lastview:
sorting: DESC
relations:
type:
class: Typeasset
local: typeasset_id
foreign: id
foreignAlias: listTypeAsset
onDelete: CASCADE
group:
class: groupasset
local: groupasset_id
foreign: id
foreignAlias: listGroupAsset
email:
columns:
datestate: date
asset_id: integer
specialaccount: string(50)
total: integer
billable_euro: float(2)
billable_act: float(2)
snapshot_annual_act: float(2)
billable_adj: float(2)
snapshot_annual_adj: float(2)
indexes:
unique:
fields: [datestate, asset_id]
type: unique
relations:
Asset:
local: asset_id
foreign: id
foreignAlias: monthlydiskspaceln |
mon fichier action:
Code:
1
2
|
$this->report = Doctrine_Core::getTable('email')->reportbyCostCenter($site); |
mon fichier emailTable:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
public function reportbyCostCenter($site)
{
$q = $this->createQuery('e')
->select('e.datestate as date, own.costcenter_id as costcenter, SUM(e.total) as Mb, SUM(e.billable_euro) as euro')
->leftJoin('e.Asset asset')
->leftJoin('asset.group grp')
->leftJoin('grp.owner own ')
->where('own.site_id= ?', $site)
->groupBy('e.datestate, own.costcenter_id')
->orderBy('e.datestate DESC');
return $q->execute();
} |
et mon template:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
|
<?php include_partial('filtersite', array('filter' => $filter)) ?>
<table>
<?php foreach($report as $line): ?>
<tr>
<td><?php echo $line->getDate()?></td>
<td><?php echo $line->getCostcenterid()?></td>
<td><?php echo $line->getMb()?></td>
<td><?php echo $line->getEuro()?></td>
</tr>
<?php endforeach;?> |
le but étant d'afficher un tableau par date/centre de cout la taille total et le cout.
Sauf que alors que j'ai plusieurs valeurs date, il ne m'affiche que la premiere ligne !!
