-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpat2_bensporvalorDepartamento002.php
238 lines (219 loc) · 9.07 KB
/
pat2_bensporvalorDepartamento002.php
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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
<?php
require_once("fpdf151/pdf.php");
require_once("libs/db_sql.php");
require_once("classes/db_bens_classe.php");
require_once("classes/db_bensbaix_classe.php");
require_once("classes/db_cfpatriplaca_classe.php");
require_once("classes/db_benscadcedente_classe.php");
require_once("classes/db_situabens_classe.php");
$clbenscadcedente = new cl_benscadcedente();
$clbens = new cl_bens;
$clbensbaix = new cl_bensbaix;
$clcfpatriplaca = new cl_cfpatriplaca;
$clsituabens = new cl_situabens;
parse_str($HTTP_SERVER_VARS['QUERY_STRING']);
$where = "";
if($codigoDepartamento){
$where .= " AND t52_depart = $codigoDepartamento";
$sqlDepartamento = "select descrdepto from db_depart where coddepto = ".$codigoDepartamento;
$resultDepart = db_query($sqlDepartamento) or die(pg_last_error());
$oResultDepart = db_utils::fieldsMemory($resultDepart, 0);
}
if($itipobens){
$where .= " AND t24_sequencial = $itipobens";
}
$sqlTiposbens = "select t24_descricao from bemtipos where t24_sequencial = ".$itipobens;
$resultTipobens = db_query($sqlTiposbens) or die(pg_last_error());
$oResultTipos = db_utils::fieldsMemory($resultTipobens, 0);
$sql = "SELECT DISTINCT t52_bem AS codigo,
t52_ident AS placa,
t52_descr AS descricao,
t52_valaqu AS valoraquisicao,
(SELECT t58_valoratual
FROM benshistoricocalculobem
JOIN benshistoricocalculo ON t57_sequencial=t58_benshistoricocalculo
WHERE t57_ano = $ano
AND t58_bens = t52_bem
AND t57_mes = $mes
ORDER BY t58_sequencial DESC
LIMIT 1)+t44_valorresidual AS valoratual,
t52_dtaqu AS dtaquisicao,
descrdepto AS departamento,
t33_divisao,
t30_descr AS divisao,
t64_descr as classificacao
FROM bens
JOIN bensdepreciacao ON t44_bens = t52_bem
JOIN db_depart ON coddepto = t52_depart
JOIN clabens ON t64_codcla=t52_codcla
JOIN bemtipos ON t24_sequencial=t64_bemtipos
JOIN benshistoricocalculobem ON t58_bens=t52_bem
JOIN benshistoricocalculo ON t57_sequencial=t58_benshistoricocalculo
AND t57_ano = $ano
AND t57_mes = $mes
LEFT JOIN bensdiv ON t33_bem=t52_bem
LEFT JOIN departdiv ON t30_codigo=t33_divisao
LEFT JOIN bensbaix ON t55_codbem=t52_bem and DATE_PART('MONTH',t55_baixa) = $mes and DATE_PART('YEAR',t55_baixa) = $ano
AND t30_depto=t52_depart
WHERE t52_instit = ".db_getsession('DB_instit')."
$where
AND (
EXTRACT(YEAR FROM t52_dtaqu) < $ano
OR (
EXTRACT(YEAR FROM t52_dtaqu) = $ano
AND EXTRACT(MONTH FROM t52_dtaqu) <= $mes
)
)
AND t52_bem NOT IN
(SELECT t55_codbem
FROM bensbaix
WHERE EXTRACT(YEAR
FROM t55_baixa) < $ano
OR (EXTRACT(YEAR
FROM t55_baixa) = $ano
AND EXTRACT(MONTH
FROM t55_baixa) <= $mes))
UNION
SELECT DISTINCT t52_bem AS codigo,
t52_ident AS placa,
t52_descr AS descricao,
t52_valaqu AS valoraquisicao,
t44_valoratual+t44_valorresidual AS valoratual,
t52_dtaqu AS dtaquisicao,
descrdepto AS departamento,
t33_divisao,
t30_descr AS divisao,
t64_descr as classificacao
FROM bens
JOIN bensdepreciacao ON t44_bens = t52_bem
JOIN db_depart ON coddepto = t52_depart
JOIN clabens ON t64_codcla=t52_codcla
JOIN bemtipos ON t24_sequencial=t64_bemtipos
LEFT JOIN bensdiv ON t33_bem=t52_bem
LEFT JOIN departdiv ON t30_codigo=t33_divisao
LEFT JOIN bensbaix ON t55_codbem=t52_bem and DATE_PART('MONTH',t55_baixa) = $mes and DATE_PART('YEAR',t55_baixa) = $ano
WHERE t52_instit = ".db_getsession('DB_instit')."
$where
AND (
EXTRACT(YEAR FROM t52_dtaqu) < $ano
OR (
EXTRACT(YEAR FROM t52_dtaqu) = $ano
AND EXTRACT(MONTH FROM t52_dtaqu) <= $mes
)
)
AND t52_bem NOT IN
(SELECT t55_codbem
FROM bensbaix
WHERE EXTRACT(YEAR
FROM t55_baixa) < $ano
OR (EXTRACT(YEAR
FROM t55_baixa) = $ano
AND EXTRACT(MONTH
FROM t55_baixa) <= $mes))
AND t52_bem NOT IN
(SELECT t58_bens
FROM benshistoricocalculobem
JOIN benshistoricocalculo ON t57_sequencial=t58_benshistoricocalculo
WHERE t58_bens= t52_bem
AND t57_ano = $ano
AND t57_mes = $mes)
order by placa
";
$resultBens = db_query($sql);
$pdf = new PDF('Landscape', 'mm', 'A4');
$pdf->Open();
$pdf->AliasNbPages();
$alt = 5;
$pdf->setfillcolor(235);
$pdf->setfont('arial', 'b', 10);
$totalAtual = 0;
$totalAquisicao = 0;
$pdf->addpage();
$pdf->setfont('arial', 'b', 9);
$pdf->text(215, 10, 'Relatorio Bens Por Valor');
$pdf->text(215, 15, 'Mês:');
$pdf->setfont('arial', '', 9);
$pdf->text(223, 15, $mes);
$pdf->setfont('arial', 'b', 9);
$pdf->text(215, 20, 'Ano:');
$pdf->setfont('arial', '', 9);
$pdf->text(223, 20, $ano);
$pdf->SetFont('arial','B',9);
$pdf->cell(40 ,$alt ,"Tipo do Bem:",1,0,"L",1);
$pdf->cell(240 ,$alt ,$oResultTipos->t24_descricao,1,1,"L",1);
if($oResultDepart->descrdepto){
$pdf->cell(40 ,$alt ,"Departamento:",1,0,"L",1);
$pdf->cell(240 ,$alt ,$oResultDepart->descrdepto,1,1,"L",1);
}
$pdf->cell(12 ,$alt ,"Código",1,0,"C",1);
$pdf->cell(12 ,$alt ,"Placa",1,0,"C",1);
$pdf->cell(100 ,$alt ,"Descrição",1,0,"L",1);
$pdf->cell(25 ,$alt ,"Data Aquisição",1,0,"C",1);
$pdf->cell(15 ,$alt ,"Situação",1,0,"C",1);
$pdf->cell(25 ,$alt ,"Vlr. Aquisição",1,0,"C",1);
$pdf->cell(20 ,$alt ,"Vlr. Atual",1,0,"C",1);
$pdf->cell(71 ,$alt ,"Classificação",1,1,"C",1);
$aDadosRelatoriogruppordivisao = array();
for ($iCont = 0; $iCont < pg_num_rows($resultBens); $iCont++) {
$oResult = db_utils::fieldsMemory($resultBens, $iCont);
$aDadosRelatoriogruppordivisao[$oResult->divisao][] = $oResult;
}
foreach ($aDadosRelatoriogruppordivisao as $key => $aDadosRelatorio) {
$passouDiv[] = array($key);
$descricaodisao = $key;
if(!in_array($key,$passouDiv)){
$pdf->SetFont('arial','B',9);
if($descricaodisao){
$pdf->cell(280, $alt, "Divisão: " . $descricaodisao , 1, 1, "L", 0);
}else{
$pdf->cell(280, $alt, "Divisão: Sem Divisão", 1, 1, "L", 0);
}
}
$totalDivisaoAquisicao = 0;
$totalDivisao = 0;
foreach ($aDadosRelatorio as $oDados){
if ($pdf->getY() > $pdf->h - 45) {
$pdf->addpage();
$pdf->setfont('arial', 'b', 9);
$pdf->text(215, 10, 'Relatorio Bens Por Valor');
$pdf->text(215, 15, 'Mês:');
$pdf->setfont('arial', '', 9);
$pdf->text(223, 15, $mes);
$pdf->setfont('arial', 'b', 9);
$pdf->text(215, 20, 'Ano:');
$pdf->setfont('arial', '', 9);
$pdf->text(223, 20, $ano);
}
$rsSituacaobem = db_query("SELECT * FROM histbem JOIN situabens ON t70_situac=t56_situac WHERE t56_codbem = $oDados->codigo ORDER BY t56_histbem desc limit 1");
$oResultSituacao = db_utils::fieldsMemory($rsSituacaobem, 0);
$dtaquisicao = implode('/', array_reverse(explode('-', $oDados->dtaquisicao)));
$pdf->SetFont('arial', '', 7);
$pdf->cell(12, $alt, $oDados->codigo, 0, 0, "C", 0);
$pdf->cell(12, $alt, $oDados->placa, 0, 0, "C", 0);
$pdf->cell(100, $alt, substr($oDados->descricao,0,70), 0, 0, "L", 0);
$pdf->cell(25, $alt, $dtaquisicao, 0, 0, "C", 0);
$pdf->cell(15, $alt, $oResultSituacao->t70_descr, 0, 0, "C", 0);
$pdf->cell(25, $alt, db_formatar($oDados->valoraquisicao,'f'), 0, 0, "C", 0);
$pdf->cell(20, $alt, db_formatar($oDados->valoratual,'f'), 0, 0, "C", 0);
$pdf->SetFont('arial', '', 6);
$pdf->cell(71, $alt, $oDados->classificacao, 0, 1, "L", 0);
$pdf->SetFont('arial', '', 7);
$totalDivisao += $oDados->valoratual;
$totalDivisaoAquisicao += $oDados->valoraquisicao;
$totalAquisicao += $oDados->valoraquisicao;
$totalAtual += $oDados->valoratual;
}
$pdf->SetFont('arial','B',9);
$pdf->cell(168, $alt,"Total Divisão:", 0, 0, "L", 0);
$pdf->cell(25, $alt,db_formatar($totalDivisaoAquisicao,'f'), 0, 0, "L", 0);
$pdf->cell(20, $alt,db_formatar($totalDivisao,'f'), 0, 1, "L", 0);
$pdf->SetFont('arial', '', 7);
}
$pdf->SetFont('arial','B',10);
$pdf->cell(40 ,$alt ,"Total:",1,0,"C",1);
$pdf->SetFont('arial','B',9);
$pdf->cell(175, $alt, '', 1, 0, "R", 0);
$pdf->cell(35, $alt, db_formatar($totalAquisicao,'f'), 1, 0, "R", 0);
$pdf->cell(30, $alt, db_formatar($totalAtual,'f'), 1, 1, "R", 0);
$pdf->cell(260,$alt ,"TOTAL GERAL DE REGISTROS: ".$iCont,0,0,"R",0);
$pdf->Output();