|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace Cdf\BiCoreBundle\Utils\Tabella; |
|
4
|
|
|
|
|
5
|
|
|
use Doctrine\ORM\Tools\Pagination\Paginator; |
|
6
|
|
|
use Doctrine\Common\Collections\Expr\Comparison; |
|
7
|
|
|
use Cdf\BiCoreBundle\Utils\FieldType\FieldTypeUtils; |
|
8
|
|
|
use function count; |
|
9
|
|
|
|
|
10
|
|
|
trait TabellaQueryTrait |
|
11
|
|
|
{ |
|
12
|
12 |
|
protected function biQueryBuilder() |
|
13
|
|
|
{ |
|
14
|
12 |
|
$nometabellaalias = $this->generaAlias($this->tablename); |
|
|
|
|
|
|
15
|
12 |
|
$qb = $this->em->createQueryBuilder() |
|
16
|
12 |
|
->select(array($nometabellaalias)) |
|
17
|
12 |
|
->from($this->entityname, $nometabellaalias); |
|
18
|
12 |
|
$campi = array_keys($this->em->getMetadataFactory()->getMetadataFor($this->entityname)->reflFields); |
|
19
|
12 |
|
$this->recursiveJoin($qb, $campi, $this->tablename, $nometabellaalias); |
|
20
|
12 |
|
$this->buildWhere($qb); |
|
21
|
12 |
|
$this->orderByBuilder($qb); |
|
22
|
|
|
|
|
23
|
12 |
|
return $qb; |
|
24
|
|
|
} |
|
25
|
|
|
|
|
26
|
12 |
|
protected function recursiveJoin(&$qb, $campi, $nometabella, $alias, $ancestors = array()) |
|
27
|
|
|
{ |
|
28
|
12 |
|
foreach ($campi as $campo) { |
|
29
|
12 |
|
if (false !== strpos(strtolower($campo), 'relatedby')) { |
|
30
|
|
|
continue; |
|
31
|
|
|
} |
|
32
|
12 |
|
if (!in_array($nometabella, $ancestors)) { |
|
33
|
12 |
|
$ancestors[] = $nometabella; |
|
34
|
|
|
} |
|
35
|
|
|
|
|
36
|
12 |
|
$configurazionecampo = isset($this->configurazionecolonnetabella[ucfirst(implode('.', $ancestors)).'.'.$campo]) ? |
|
37
|
12 |
|
$this->configurazionecolonnetabella[ucfirst(implode('.', $ancestors)).'.'.$campo] : false; |
|
38
|
12 |
|
if ($configurazionecampo && true === $configurazionecampo['association']) { |
|
39
|
|
|
// crea la relazione con $padre = $nometabella in corso e figlio = $nomecampo con $alias generato |
|
40
|
6 |
|
if ((isset($configurazionecampo['sourceentityclass'])) && (null !== $configurazionecampo['sourceentityclass'])) { |
|
41
|
6 |
|
$entitysrc = $configurazionecampo['sourceentityclass']; |
|
42
|
6 |
|
$nometabellasrc = $this->em->getClassMetadata($entitysrc)->getTableName(); |
|
43
|
|
|
} else { |
|
44
|
|
|
$nometabellasrc = $nometabella; |
|
45
|
|
|
} |
|
46
|
|
|
|
|
47
|
6 |
|
$entitytarget = $configurazionecampo['associationtable']['targetEntity']; |
|
48
|
6 |
|
$nometabellatarget = $this->em->getClassMetadata($entitytarget)->getTableName(); |
|
49
|
6 |
|
$aliastarget = $this->generaAlias($nometabellatarget, $nometabellasrc, $ancestors); |
|
50
|
|
|
//$qb->leftJoin($alias . "." . $configurazionecampo["nomecampo"], $aliastarget); |
|
51
|
|
|
//$camporelazionejoin = strtolower(substr($configurazionecampo["nomecampo"], strpos($configurazionecampo["nomecampo"], ".") + 1)); |
|
52
|
6 |
|
$parti = explode('.', $configurazionecampo['nomecampo']); |
|
53
|
|
|
|
|
54
|
6 |
|
$camporelazionejoin = strtolower($parti[count($parti) - 1]); |
|
55
|
6 |
|
$qb->leftJoin($alias.'.'.$camporelazionejoin, $aliastarget); |
|
56
|
6 |
|
$campitarget = array_keys($this->em->getMetadataFactory()->getMetadataFor($entitytarget)->reflFields); |
|
57
|
6 |
|
$this->recursiveJoin($qb, $campitarget, $nometabellatarget, $aliastarget, $ancestors); |
|
58
|
|
|
|
|
59
|
|
|
// lancia rescursiveJoin su questo campo con padre = $aliasgenerato |
|
60
|
|
|
// --- figlio = $nomecampo |
|
61
|
|
|
// --- alias = alias generato nuovo |
|
62
|
|
|
} |
|
63
|
|
|
} |
|
64
|
12 |
|
} |
|
65
|
|
|
|
|
66
|
12 |
|
protected function buildWhere(&$qb) |
|
67
|
|
|
{ |
|
68
|
12 |
|
$filtro = ''; |
|
|
|
|
|
|
69
|
12 |
|
$prefiltro = ''; |
|
|
|
|
|
|
70
|
12 |
|
foreach ($this->prefiltri as $key => $prefiltro) { |
|
71
|
2 |
|
$this->prefiltri[$key]['prefiltro'] = true; |
|
|
|
|
|
|
72
|
|
|
} |
|
73
|
12 |
|
foreach ($this->filtri as $key => $filtro) { |
|
74
|
2 |
|
$this->filtri[$key]['prefiltro'] = false; |
|
|
|
|
|
|
75
|
|
|
} |
|
76
|
12 |
|
$tuttifiltri = array_merge($this->filtri, $this->prefiltri); |
|
77
|
12 |
|
$parametribag = array(); |
|
78
|
12 |
|
if (count($tuttifiltri)) { |
|
79
|
3 |
|
$descrizionefiltri = ''; |
|
80
|
3 |
|
foreach ($tuttifiltri as $num => $filtrocorrente) { |
|
81
|
3 |
|
$tablename = substr($filtrocorrente['nomecampo'], 0, strripos($filtrocorrente['nomecampo'], '.')); |
|
82
|
3 |
|
$alias = $this->findAliasByTablename($tablename); |
|
|
|
|
|
|
83
|
3 |
|
$fieldname = $alias.'.'.(substr($filtrocorrente['nomecampo'], strripos($filtrocorrente['nomecampo'], '.') + 1)); |
|
84
|
3 |
|
$fieldvalue = $this->getFieldValue($filtrocorrente['valore']); |
|
|
|
|
|
|
85
|
3 |
|
$fieldoperator = $this->getOperator($filtrocorrente['operatore']); |
|
|
|
|
|
|
86
|
3 |
|
$fitrocorrenteqp = 'fitrocorrente'.$num; |
|
87
|
3 |
|
$filtronomecampocorrente = $this->findFieldnameByAlias($filtrocorrente['nomecampo']); |
|
|
|
|
|
|
88
|
2 |
|
$criteria = new ParametriQueryTabellaDecoder( |
|
89
|
2 |
|
$fieldname, |
|
90
|
|
|
$fieldoperator, |
|
91
|
|
|
$fieldvalue, |
|
92
|
|
|
$fitrocorrenteqp, |
|
93
|
|
|
$filtronomecampocorrente |
|
94
|
|
|
); |
|
95
|
|
|
|
|
96
|
2 |
|
$querycriteria = $criteria->getQueryCriteria(); |
|
97
|
2 |
|
$queryparameter = $criteria->getQueryParameters(); |
|
98
|
|
|
|
|
99
|
2 |
|
if ($querycriteria) { |
|
100
|
2 |
|
$qb->andWhere($querycriteria); |
|
101
|
2 |
|
$parametribag = array_merge($queryparameter, $parametribag); |
|
102
|
|
|
} else { |
|
103
|
2 |
|
$qb->andWhere($fieldname.' '.$fieldoperator.' '.":$fitrocorrenteqp"); |
|
104
|
2 |
|
$parametribag = array_merge(array($fitrocorrenteqp => $fieldvalue), $parametribag); |
|
105
|
|
|
} |
|
106
|
2 |
|
$this->getDescrizioneFiltro($descrizionefiltri, $filtrocorrente, $criteria); |
|
|
|
|
|
|
107
|
|
|
} |
|
108
|
2 |
|
$this->traduzionefiltri = substr($descrizionefiltri, 2); |
|
|
|
|
|
|
109
|
|
|
} |
|
110
|
12 |
|
$qb->setParameters($parametribag); |
|
111
|
|
|
|
|
112
|
12 |
|
if (isset($this->wheremanuale)) { |
|
113
|
|
|
$qb->andWhere($this->wheremanuale); |
|
114
|
|
|
} |
|
115
|
12 |
|
} |
|
116
|
|
|
|
|
117
|
12 |
|
protected function orderByBuilder(&$qb) |
|
118
|
|
|
{ |
|
119
|
12 |
|
foreach ($this->colonneordinamento as $nomecampo => $tipoordinamento) { |
|
120
|
10 |
|
$tablename = substr($nomecampo, 0, strripos($nomecampo, '.')); |
|
121
|
10 |
|
$alias = $this->getAliasGenerato($tablename); |
|
|
|
|
|
|
122
|
10 |
|
$fieldname = $alias.'.'.(substr($nomecampo, strripos($nomecampo, '.') + 1)); |
|
123
|
10 |
|
$qb->addOrderBy($fieldname, $tipoordinamento); |
|
124
|
|
|
} |
|
125
|
12 |
|
} |
|
126
|
|
|
|
|
127
|
|
|
|
|
128
|
|
|
/** |
|
129
|
|
|
* It appends the new filter string part to the given filter string ($filterString) |
|
130
|
|
|
*/ |
|
131
|
|
|
private function appendFilterString(String &$filterString, $swaggerType, $fieldvalue) |
|
132
|
|
|
{ |
|
133
|
|
|
if ($swaggerType == null /*|| $swaggerFormats[ $nomeCampo ] == 'datetime'*/) { |
|
134
|
|
|
$filterString .= '"%'.$fieldvalue.'%"'; |
|
135
|
|
|
} elseif ($swaggerType == 'datetime' || $swaggerType == 'date') { |
|
136
|
|
|
$fieldvalue = \str_replace("/", "-", $fieldvalue); |
|
137
|
|
|
//does it contain an hour ? |
|
138
|
|
|
$hour = strpos($fieldvalue, ":"); |
|
139
|
|
|
$time = strtotime($fieldvalue); |
|
140
|
|
|
$backend_format = FieldTypeUtils::getEnvVar("BE_DATETIME_FORMAT", "Y-m-d\TH:i:sP"); |
|
141
|
|
|
if ($hour === false) { |
|
142
|
|
|
$backend_format = FieldTypeUtils::getEnvVar("BE_DATE_FORMAT", "Y-m-d"); |
|
143
|
|
|
} |
|
144
|
|
|
$filter = date($backend_format, $time); |
|
145
|
|
|
$filterString .= $filter; |
|
146
|
|
|
} else { |
|
147
|
|
|
$filterString .= $fieldvalue; |
|
148
|
|
|
} |
|
149
|
|
|
} |
|
150
|
|
|
|
|
151
|
|
|
/** |
|
152
|
|
|
* It composes filtering string to be used with api rest services |
|
153
|
|
|
*/ |
|
154
|
|
|
protected function filterByApiBuilder(): ?String |
|
155
|
|
|
{ |
|
156
|
|
|
$filterString = null; |
|
157
|
|
|
$filtro = ''; |
|
|
|
|
|
|
158
|
|
|
$prefiltro = ''; |
|
|
|
|
|
|
159
|
|
|
foreach ($this->prefiltri as $key => $prefiltro) { |
|
160
|
|
|
$this->prefiltri[$key]['prefiltro'] = true; |
|
|
|
|
|
|
161
|
|
|
} |
|
162
|
|
|
foreach ($this->filtri as $key => $filtro) { |
|
163
|
|
|
$this->filtri[$key]['prefiltro'] = false; |
|
|
|
|
|
|
164
|
|
|
} |
|
165
|
|
|
$tuttifiltri = array_merge($this->filtri, $this->prefiltri); |
|
166
|
|
|
//$parametribag = array(); |
|
167
|
|
|
if (count($tuttifiltri)) { |
|
168
|
|
|
$attributeMap = $this->entityname::attributeMap(); |
|
169
|
|
|
$swaggerFormats = $this->entityname::swaggerFormats(); |
|
170
|
|
|
//compose the string |
|
171
|
|
|
$descrizionefiltri = ''; |
|
172
|
|
|
foreach ($tuttifiltri as $num => $filtrocorrente) { |
|
173
|
|
|
$nomeCampo = substr($filtrocorrente['nomecampo'], strripos($filtrocorrente['nomecampo'], '.') + 1); |
|
174
|
|
|
$fieldname = ' '.$nomeCampo; |
|
175
|
|
|
$filteringValues = $this->getFieldValue($filtrocorrente['valore']); |
|
176
|
|
|
|
|
177
|
|
|
|
|
178
|
|
|
$fieldoperator = $this->getOperator($filtrocorrente['operatore']); |
|
179
|
|
|
$fitrocorrenteqp = 'fitrocorrente'.$num; |
|
180
|
|
|
$filtronomecampocorrente = $this->findFieldnameByAlias($filtrocorrente['nomecampo']); |
|
181
|
|
|
|
|
182
|
|
|
$criteria = new ParametriQueryTabellaDecoder( |
|
183
|
|
|
$fieldname, |
|
184
|
|
|
$fieldoperator, |
|
185
|
|
|
$filteringValues, |
|
186
|
|
|
$fitrocorrenteqp, |
|
187
|
|
|
$filtronomecampocorrente |
|
188
|
|
|
); |
|
189
|
|
|
|
|
190
|
|
|
if (is_array($filteringValues)) { |
|
191
|
|
|
$fieldstring = '( '; |
|
192
|
|
|
foreach ($filteringValues as $num => $filterValue) { |
|
|
|
|
|
|
193
|
|
|
$fieldstring .= $attributeMap[ $nomeCampo ]; |
|
194
|
|
|
$fieldstring .= ' '.$this->getApiOperator($filtrocorrente['operatore']).' '; |
|
195
|
|
|
$fieldvalue = urldecode($filterValue); |
|
196
|
|
|
$this->appendFilterString($fieldstring, $swaggerFormats[ $nomeCampo ], $fieldvalue); |
|
197
|
|
|
if ($num < count($filteringValues)-1) { |
|
198
|
|
|
$fieldstring .= ' OR '; |
|
199
|
|
|
} |
|
200
|
|
|
} |
|
201
|
|
|
$fieldstring .= ' )'; |
|
202
|
|
|
} else { |
|
203
|
|
|
$fieldstring = $attributeMap[ $nomeCampo ]; |
|
204
|
|
|
$fieldstring .= ' '.$this->getApiOperator($filtrocorrente['operatore']).' '; |
|
205
|
|
|
$fieldvalue = urldecode($filteringValues); |
|
|
|
|
|
|
206
|
|
|
$this->appendFilterString($fieldstring, $swaggerFormats[ $nomeCampo ], $filteringValues); |
|
207
|
|
|
} |
|
208
|
|
|
|
|
209
|
|
|
if ($filterString != null) { |
|
210
|
|
|
$filterString .= ' AND '; |
|
211
|
|
|
} |
|
212
|
|
|
$filterString .= $fieldstring; |
|
213
|
|
|
$this->getDescrizioneFiltro($descrizionefiltri, $filtrocorrente, $criteria); |
|
214
|
|
|
} |
|
215
|
|
|
$this->traduzionefiltri = substr($descrizionefiltri, 2); |
|
|
|
|
|
|
216
|
|
|
} |
|
217
|
|
|
return $filterString; |
|
218
|
|
|
} |
|
219
|
|
|
|
|
220
|
|
|
/** |
|
221
|
|
|
* Return the operator to be used |
|
222
|
|
|
*/ |
|
223
|
|
|
private function getApiOperator($operator) |
|
224
|
|
|
{ |
|
225
|
|
|
switch (strtoupper($operator)) { |
|
226
|
|
|
case Comparison::CONTAINS: |
|
227
|
|
|
$operator = '~'; |
|
228
|
|
|
break; |
|
229
|
|
|
/*case 'IN': |
|
230
|
|
|
$operator = Comparison::IN; |
|
231
|
|
|
break; |
|
232
|
|
|
case 'NOT IN': |
|
233
|
|
|
$operator = Comparison::NIN; |
|
234
|
|
|
break;*/ |
|
235
|
|
|
default: |
|
236
|
|
|
$operator = '='; |
|
237
|
|
|
break; |
|
238
|
|
|
} |
|
239
|
|
|
|
|
240
|
|
|
return $operator; |
|
241
|
|
|
} |
|
242
|
|
|
|
|
243
|
|
|
/** |
|
244
|
|
|
* Build the ordering string compliant with API REST services |
|
245
|
|
|
*/ |
|
246
|
|
|
protected function orderByApiBuilder(): ?String |
|
247
|
|
|
{ |
|
248
|
|
|
$attributeMap = $this->entityname::attributeMap(); |
|
249
|
|
|
$orderingString = null; |
|
250
|
|
|
foreach ($this->colonneordinamento as $nomecampo => $tipoordinamento) { |
|
251
|
|
|
$fieldname = $attributeMap[ substr($nomecampo, strripos($nomecampo, '.') + 1) ]; |
|
252
|
|
|
$fieldname .= ':'.$tipoordinamento; |
|
253
|
|
|
if ($orderingString != null) { |
|
254
|
|
|
$orderingString .= ','; |
|
255
|
|
|
} |
|
256
|
|
|
$orderingString .= $fieldname; |
|
257
|
|
|
} |
|
258
|
|
|
return $orderingString; |
|
259
|
|
|
} |
|
260
|
|
|
|
|
261
|
12 |
|
public function getRecordstabella() |
|
262
|
|
|
{ |
|
263
|
|
|
//Look for all tables |
|
264
|
12 |
|
$qb = $this->biQueryBuilder(); |
|
265
|
|
|
|
|
266
|
12 |
|
if (false === $this->estraituttirecords) { |
|
|
|
|
|
|
267
|
12 |
|
$paginator = new Paginator($qb, true); |
|
268
|
12 |
|
$this->righetotali = count($paginator); |
|
|
|
|
|
|
269
|
12 |
|
$this->paginetotali = (int) $this->calcolaPagineTotali($this->getRigheperpagina()); |
|
|
|
|
|
|
270
|
|
|
/* imposta l'offset, ovvero il record dal quale iniziare a visualizzare i dati */ |
|
271
|
12 |
|
$offsetrecords = ($this->getRigheperpagina() * ($this->getPaginacorrente() - 1)); |
|
|
|
|
|
|
272
|
|
|
|
|
273
|
|
|
/* Imposta il limite ai record da estrarre */ |
|
274
|
12 |
|
if ($this->getRigheperpagina()) { |
|
275
|
12 |
|
$qb = $qb->setMaxResults($this->getRigheperpagina()); |
|
276
|
|
|
} |
|
277
|
|
|
/* E imposta il primo record da visualizzare (per la paginazione) */ |
|
278
|
12 |
|
if ($offsetrecords) { |
|
279
|
|
|
$qb = $qb->setFirstResult($offsetrecords); |
|
280
|
|
|
} |
|
281
|
|
|
/* Dall'oggetto querybuilder si ottiene la query da eseguire */ |
|
282
|
12 |
|
$recordsets = $qb->getQuery()->getResult(); |
|
283
|
|
|
} else { |
|
284
|
|
|
/* Dall'oggetto querybuilder si ottiene la query da eseguire */ |
|
285
|
4 |
|
$recordsets = $qb->getQuery()->getResult(); |
|
286
|
4 |
|
$this->righetotali = count($recordsets); |
|
287
|
4 |
|
$this->paginetotali = 1; |
|
288
|
|
|
} |
|
289
|
|
|
|
|
290
|
12 |
|
$this->records = array(); |
|
|
|
|
|
|
291
|
12 |
|
$rigatabellahtml = array(); |
|
292
|
12 |
|
foreach ($recordsets as $record) { |
|
293
|
12 |
|
$this->records[$record->getId()] = $record; |
|
294
|
12 |
|
unset($rigatabellahtml); |
|
295
|
|
|
} |
|
296
|
|
|
|
|
297
|
12 |
|
return $this->records; |
|
298
|
|
|
} |
|
299
|
|
|
|
|
300
|
|
|
/** |
|
301
|
|
|
* Read the API in order to obtains the pages features |
|
302
|
|
|
*/ |
|
303
|
|
|
public function getApiRecordstabella() |
|
304
|
|
|
{ |
|
305
|
|
|
$newApi = $this->apiController; |
|
306
|
|
|
$apiController = new $newApi(); |
|
307
|
|
|
|
|
308
|
|
|
if (false === $this->estraituttirecords) { |
|
|
|
|
|
|
309
|
|
|
$countMethod = $this->apiBook->getCount(); |
|
310
|
|
|
|
|
311
|
|
|
$count = $apiController->$countMethod(); |
|
312
|
|
|
$this->righetotali = $count; |
|
|
|
|
|
|
313
|
|
|
$this->paginetotali = (int) $this->calcolaPagineTotali($this->getRigheperpagina()); |
|
|
|
|
|
|
314
|
|
|
/* imposta l'offset, ovvero il record dal quale iniziare a visualizzare i dati */ |
|
315
|
|
|
$offsetrecords = ($this->getRigheperpagina() * ($this->getPaginacorrente() - 1)); |
|
316
|
|
|
|
|
317
|
|
|
/*$offset = null, $limit = null, $sort = null, $condition = null*/ |
|
318
|
|
|
$paginationMethod = $this->apiBook->getAll(); |
|
319
|
|
|
//dump($this->filterByApiBuilder()); |
|
320
|
|
|
|
|
321
|
|
|
$recordsets = $apiController->$paginationMethod( |
|
322
|
|
|
$offsetrecords, |
|
323
|
|
|
$this->getRigheperpagina(), |
|
324
|
|
|
$this->orderByApiBuilder(), |
|
325
|
|
|
$this->filterByApiBuilder() |
|
326
|
|
|
); |
|
327
|
|
|
//dump($recordsets); |
|
328
|
|
|
} else { |
|
329
|
|
|
/* Dall'oggetto querybuilder si ottiene la query da eseguire */ |
|
330
|
|
|
$paginationMethod = $this->apiBook->getAll(); |
|
331
|
|
|
$recordsets = $apiController->$paginationMethod(0, null, $this->orderByApiBuilder(), $this->filterByApiBuilder()); |
|
332
|
|
|
$this->paginetotali = 1; |
|
333
|
|
|
$this->righetotali = count($recordsets); |
|
334
|
|
|
} |
|
335
|
|
|
$this->records = array(); |
|
|
|
|
|
|
336
|
|
|
$rigatabellahtml = array(); |
|
337
|
|
|
foreach ($recordsets as $record) { |
|
338
|
|
|
$this->records[$record->getId()] = $record; |
|
339
|
|
|
unset($rigatabellahtml); |
|
340
|
|
|
} |
|
341
|
|
|
|
|
342
|
|
|
return $this->records; |
|
343
|
|
|
} |
|
344
|
|
|
} |
|
345
|
|
|
|
ComuneFI /
BiCoreBundle
Loading history...
