PrinterCounterRepository.php
4.58 KB
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
<?php
namespace Swpb\Bundle\CocarBundle\Entity;
use Doctrine\ORM\EntityRepository;
/**
* PrinterCounterRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
//acrescentado serie e local
class PrinterCounterRepository extends EntityRepository
{
/**
* Classe do relatório geral de impressão
*
* @param $start
* @param $end
* @return array
*/
public function relatorioGeral($start, $end) {
$_dql = "SELECT printer.id,
pc1.blackInk,
pc1.coloredInk,
max(pc1.date) as endDate,
max(pc1.prints) as printsEnd,
min(pc2.date) as startDate,
min(pc2.prints) as printsStart,
printer.name,
printer.description,
printer.serie,
printer.local,
printer.host
FROM CocarBundle:Printer printer
LEFT JOIN CocarBundle:PrinterCounter pc1 WITH (pc1.printer = printer.id AND pc1.date BETWEEN :start AND :end)
LEFT JOIN CocarBundle:PrinterCounter pc2 WITH (pc2.printer = printer.id AND pc2.date BETWEEN :start AND :end)
GROUP BY printer.id,
pc1.blackInk,
pc1.coloredInk,
printer.name,
printer.description,
printer.host,
printer.serie,
printer.local
ORDER BY printer.id ASC";
return $this->getEntityManager()->createQuery( $_dql )
->setParameter('start', $start)
->setParameter('end', $end)
->getArrayResult();
}
/**
* Classe do relatório geral de impressão no formato CSV
*
* @param $start
* @param $end
* @return array
*/
public function relatorioCsvGeral($start, $end) {
$_dql = "SELECT printer.id,
printer.name,
printer.host,
printer.serie,
printer.local,
max(pc1.prints) as printsEnd,
min(pc2.prints) as printsStart,
(max(pc1.prints) - min(pc2.prints)) as totalPrints
FROM CocarBundle:Printer printer
LEFT JOIN CocarBundle:PrinterCounter pc1 WITH (pc1.printer = printer.id AND pc1.date BETWEEN :start AND :end)
LEFT JOIN CocarBundle:PrinterCounter pc2 WITH (pc2.printer = printer.id AND pc2.date BETWEEN :start AND :end)
GROUP BY printer.id,
printer.name,
printer.description,
printer.host,
printer.serie,
printer.local
ORDER BY printer.id ASC";
return $this->getEntityManager()->createQuery( $_dql )
->setParameter('start', $start)
->setParameter('end', $end)
->getArrayResult();
}
/**
* Classe do relatório geral de impressão no formato CSV detalhado
*
* @param $start
* @param $end
* @return array
*/
public function relatorioCsvGeralDetalhado($start, $end) {
$_dql = "SELECT printer.id,
max(pc1.prints) as printsEnd,
max(pc1.date) as endDate,
min(pc2.prints) as printsStart,
min(pc2.date) as startDate,
printer.name,
printer.host,
printer.serie,
printer.local,
(max(pc1.prints) - min(pc2.prints)) as totalPrints
FROM CocarBundle:Printer printer
LEFT JOIN CocarBundle:PrinterCounter pc1 WITH (pc1.printer = printer.id AND pc1.date BETWEEN :start AND :end)
LEFT JOIN CocarBundle:PrinterCounter pc2 WITH (pc2.printer = printer.id AND pc2.date BETWEEN :start AND :end)
GROUP BY printer.id,
printer.name,
printer.description,
printer.host,
printer.serie,
printer.local
ORDER BY printer.id ASC";
return $this->getEntityManager()->createQuery( $_dql )
->setParameter('start', $start)
->setParameter('end', $end)
->getArrayResult();
}
}