noalyss Version-9
anc_balance_double.class.php
Go to the documentation of this file.
1<?php
2/*
3 * This file is part of NOALYSS.
4 *
5 * NOALYSS is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
9 *
10 * NOALYSS is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with NOALYSS; if not, write to the Free Software
17 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18*/
19
20// Copyright Author Dany De Bontridder danydb@aevalys.eu
21
22/**
23 *@file
24 *@brief Print the crossed balance between 2 plan
25 */
26
27/**
28 * @class Anc_Balance_Double
29 * @brief Print the crossed balance between 2 plan
30 *
31 */
32
34{
35 /*!
36 * \brief compute the html display
37 *
38 *
39 * \return string
40 */
41
42 function display_html ()
43 {
44 if ($this->pa_id == $this->pa_id2) throw new Exception(_("Pas de croisement avec un seul plan"),1000);
45
46 bcscale(2);
47 $r="";
48
49 $array=$this->load();
50 $odd=0;
51 if ( is_array($array) == false )
52 {
53 return $array;
54
55 }
56 $old="";
57 $tot_deb=0;
58 $tot_cred=0;
59
60 foreach ( $array as $row)
61 {
62 $odd++;
63
64 $r.=($odd%2==0)?'<tr class="odd">':'<tr class="even">';
65
66 if ( $old == $row['a_po_name'] )
67 {
68 $r.='<td></td>';
69 }
70 else
71 {
72
73 if ( $tot_deb != 0 || $tot_cred !=0 )
74 {
75 $r.="<tr>".td('');
76 $r.="<td>"._("Total")." </td>".td(nbm($tot_deb),' class="num"').td(nbm($tot_cred),' class="num"');
77 $s=abs(bcsub($tot_deb,$tot_cred));
78
79 $d=($tot_deb>$tot_cred)?'debit':'credit';
80 $r.="<td class=\"num\">".nbm($s)."</td><td>$d</td>";
81 $r.="</tr>";
82 }
83 $tot_deb=0;
84 $tot_cred=0;
85
86 // new
87 $r.="</table>";
88 $r.="<table class=\"result\" style=\"margin-bottom:3px\">";
89 $r.="<tr>";
90 $r.="<th style=\"width:30%\" >"._("Poste Analytique")."</th>";
91 $r.="<th style=\"width:30%\">"._("Poste Analytique")."</th>";
92 $r.="<th style=\"text-align:right\">"._("Débit")."</th>";
93 $r.="<th style=\"text-align:right\">"._("Crédit")."</th>";
94 $r.="<th style=\"text-align:right\">"._("Solde")."</th>";
95 $r.="<th>D/C</th>";
96 $r.="</tr>";
97 $r.='<tr>';
98 $r.=td($row['a_po_name'].' '.$row['a_po_description']);
99 $old=$row['a_po_name'];
100 $r.= '</tr>';
101 $r.= '<tr>';
102 $r.=td('');
103 }
104 $tot_deb=bcadd($tot_deb,$row['a_d']);
105 $tot_cred=bcadd($tot_cred,$row['a_c']);
106
107 $r.=td($row['b_po_name']." ".$row['b_po_description']);
108
109 $r.=td(nbm($row['a_d']),' class="num"');
110 $r.=td(nbm($row['a_c']),' class="num"');
111 $r.=td(nbm($row['a_solde']),' class="num"');
112 $r.=sprintf("<td>%s</td>",$row['a_debit']);
113 $r.="</tr>";
114 } /* end loop */
115
116 if ( $tot_deb != 0 || $tot_cred !=0 )
117 {
118 $r.="<tr>".td('');
119 $r.="<td>Total </td> <td ' class=\"num\"> ".nbm($tot_deb)." </td> <td ' class=\"num\">".nbm($tot_cred)."</td>";
120 $s=abs($tot_deb-$tot_cred);
121 $d=($tot_deb>$tot_cred)?'debit':'credit';
122 $r.=td(nbm($s),' class="num"')."<td>$d</td>";
123 $r.="</tr>";
124 }
125
126 $r.="</table>";
127 $r.=h2info('Résumé');
128 $r.='<table class="result">';
129 $r.='<tr>';
130 $r.=th('Po').
131 th('Nom').
132 th('Débit',' style="text-align:right"').
133 th('Crédit','style="text-align:right" ').
134 th('Solde',' style="text-align:right"');
135
136 $sum=$this->show_sum($array);
138 foreach ($sum as $row)
139 {
140 $r.='<tr>';
141 $r.='<td>'.$row['poste'].'</td>';
142 $r.='<td>'.$row['desc'].'</td>';
143 $r.='<td class="num">'.nbm($row['debit']).'</td>';
144 $r.='<td class="num">'.nbm($row['credit']).'</td>';
145 $diff=bcsub($row['credit'],$row['debit']);
146 $tot_cred=bcadd($tot_cred,$row['credit']);
147 $tot_deb=bcadd($tot_deb,$row['debit']);
148
149 $r.=td(nbm($diff),' class="num" ');
150
151 $r.='<td>'.$row['dc'].'</td>';
152 $r.='</tr>';
153 }
154 $r.='<tr class="highlight">';
155 $r.=td('');
156 $r.=td('total');
157 $r.=td(nbm($tot_deb),'class="num"');
158 $r.=td(nbm($tot_cred),'class="num"');
160 $sign=($tot_cred<$tot_deb)?_("Débit"):_("Crédit");
161 $r.=td(nbm($solde),'class="num" style="border:solid 1px blue;font-weight:bold"');
162 $r.=td($sign);
163 $r.='</tr>';
164 $r.='</table>';
165
166 return $r;
167 }
168 /*!
169 * \brief Display the result in pdf
170 *
171 * \return none
172 */
173 function display_pdf()
174 {
175 $array=$this->load();
176 bcscale(2);
177 if (empty($array))return;
178 $pdf=new PDF($this->db);
179 $pdf->Setdossierinfo(dossier::name());
180 $pdf->setTitle("Balance analytique",true);
181 $pdf->SetAuthor('NOALYSS');
182 $pdf->AliasNbPages();
183 $pdf->AddPage();
184
185 $pa=new Anc_Plan($this->db,$this->pa_id);
186 $pa->get();
187 $pb=new Anc_Plan($this->db,$this->pa_id2);
188 $pb->get();
189 $pdf->SetFont('DejaVu','B',9);
190 $pdf->write_cell(0,7,sprintf("Balance croise plan %s %s ",
191 $pa->name,
192 $pb->name),1,0,'C');
193 $filtre_date="";
194 $filtre_pa="";
195 $filtre_pb="";
196
197 if ( $this->from !="" ||$this->to !="")
198 $filtre_date=sprintf("Filtre date %s %s",
199 $this->from,
200 $this->to);
201 if ( $this->from_poste !="" ||$this->to_poste !="")
202 $filtre_pa=sprintf("Filtre poste plan1 %s %s",
203 ($this->from_poste!="")?"de ".$this->from_poste:" ",
204 ($this->to_poste!="")?"jusque ".$this->to_poste:"");
205
206 if ( $this->from_poste2 !="" ||$this->to_poste2 !="")
207 $filtre_pb=sprintf("Filtre poste plan2 %s %s",
208 ($this->from_poste2!="")?"de ".$this->from_poste2:" ",
209 ($this->to_poste2!="")?"jusque ".$this->to_poste2:"");
210
211 $pdf->SetFont('DejaVu','',8);
212 $pdf->write_cell(50,7,$filtre_date);
213 $pdf->write_cell(50,7,$filtre_pa);
214 $pdf->write_cell(50,7,$filtre_pb);
215 $pdf->line_new();
216
217 $pdf->SetFont('DejaVu','',6);
218 $pdf->write_cell(20,7,'id','B');
219 $pdf->write_cell(100,7,'Poste Comptable','B');
220 $pdf->write_cell(20,7,'Débit','B',0,'L');
221 $pdf->write_cell(20,7,'Crédit','B',0,'L');
222 $pdf->write_cell(20,7,'Solde','B',0,'L');
223 $pdf->write_cell(20,7,'D/C','B',0,'L');
224 $pdf->line_new();
225
226 for ($i=0;$i<count($array);$i++)
227 {
228 $row=$array[$i];
229 $pdf->write_cell(20,6,$row['a_po_name'],0,0,'L');
230 $pdf->write_cell(40,6,mb_substr($row['a_po_description'],0,31),0,0,'L');
231 $pdf->write_cell(20,6,$row['b_po_name'],0,0,'L');
232 $pdf->write_cell(40,6,mb_substr($row['b_po_description'],0,31),0,0,'L');
233 $pdf->write_cell(20,6,$row['a_d'],0,0,'R');
234 $pdf->write_cell(20,6,$row['a_c'],0,0,'R');
235 $side=($row['a_c']>$row['a_d'])?"+":"-";
236 $side=($row['a_c'] == $row['a_d'])?"=":$side;
237 $pdf->write_cell(20,6,$side." ".$row['a_solde'],0,0,'R');
238 $pdf->write_cell(20,6,$row['a_debit'],0,0,'C');
239 $pdf->line_new();
240 }
241
242 $sum=$this->show_sum($array);
243 $pdf->SetFont('DejaVu','B',8);
244 $pdf->write_cell(70,6,'Somme',1,0,'C');
245 $pdf->line_new(5);
246 $pdf->SetFont('DejaVu','',6);
247
248 $pdf->write_cell(20,7,'Poste');
249 $pdf->write_cell(60,7,'Description','B');
250 $pdf->write_cell(20,7,'Débit','B',0,'L');
251 $pdf->write_cell(20,7,'Crédit','B',0,'L');
252 $pdf->write_cell(20,7,'Solde','B',0,'L');
253 $pdf->write_cell(20,7,'D/C','B',0,'L');
254 $pdf->line_new();
255 // Summary
257 for ($i=0;$i<count($sum);$i++)
258 {
259 $row=$sum[$i];
260 $pdf->write_cell(20,6,$row['poste'],0,0,'L');
261 $pdf->write_cell(60,6,$row['desc'],0,0,'L');
262 $pdf->write_cell(20,6,sprintf('%.2f',$row['debit']),0,0,'R');
263 $pdf->write_cell(20,6,sprintf('%.2f',$row['credit']),0,0,'R');
264 $side=($row['credit']>$row['debit'])?"+":"-";
265 $side=($row['debit'] == $row['credit'])?"=":$side;
266 $pdf->write_cell(20,6,sprintf($side." ".'%.2f',$row['solde']),0,0,'R');
267 $pdf->write_cell(20,6,$row['dc'],0,0,'R');
268 $pdf->line_new();
269 $tot_cred=bcadd($tot_cred,$row['credit']);
270 $tot_deb=bcadd($tot_deb,$row['debit']);
271 }
272 $pdf->write_cell(20,6,"",0,0,'L');
273 $pdf->write_cell(60,6,_('Total'),0,0,'L');
274 $pdf->write_cell(20,6,nb($tot_deb),0,0,'R');
275 $pdf->write_cell(20,6,nb($tot_cred),0,0,'R');
276 $side=($tot_cred > $tot_deb)?"+":"-";
277 $side=($tot_deb == $tot_cred)?"=":$side;
278 $solde=abs(bcsub($tot_cred,$tot_deb));
279 $pdf->write_cell(20,6,$side." ".$solde,0,0,'R');
280 $pdf->write_cell(20,6,$row['dc'],0,0,'R');
281 $pdf->line_new();
282 $fDate=date('dmy-Hi');
283 $pdf->output('crossbalance-'.$fDate.'.pdf','D');
284 }
285
286
287 /*!
288 * \brief Compute the csv export
289 * \return string with the csv
290 */
291 function display_csv()
292 {
293 $csv = new Noalyss_Csv("ca_bal_croise");
294 $csv->send_header();
295
296 $csv->add("Poste comptable Analytique");
297 $csv->add("Poste comptable Analytique");
298 $csv->add("Debit");
299 $csv->add("Credit");
300 $csv->add("Solde");
301 $csv->add("D/C");
302
303 $csv->write();
304
305 $array=$this->load();
306 if ( is_array($array) == false )
307 {
308 return $array;
309
310 }
311 foreach ( $array as $row)
312 {
313
314 $csv->add($row['a_po_name']);
315 $csv->add($row['b_po_name']);
316 $csv->add($row['a_d'],'number');
317 $csv->add($row['a_c'],'number');
318 $csv->add($row['a_solde'],'number');
319 $csv->add($row['a_debit'],'text');
320 $csv->write();
321 }
322
323
324 }
325 /*!
326 * \brief Compute the form to display
327 * \param $p_hidden hidden tag to be included (gDossier,...)
328 *
329 *
330 * \return string containing the data
331 */
332 function display_form($p_string='')
333 {
334 $r=parent::display_form($p_string);
335 // show the second plan
336 $r.='<span style="padding:5px;margin:5px;border:2px double blue;display:block;">';
337 $plan=new Anc_Plan($this->db);
338 $plan_id=new ISelect("pa_id2");
339 $plan_id->value=$this->db->make_array("select pa_id, pa_name from plan_analytique order by pa_name");
340 $plan_id->selected=$this->pa_id2;
341 $r.= "Plan Analytique :".$plan_id->input();
342 $r.=HtmlInput::request_to_hidden(array('ac'));
343
344
345
346
347 $poste=new IText();
348 $poste->size=10;
349 $r.=_('Entre le poste ');
350 $r.=$poste->input("from_poste2",$this->from_poste2);
351 $javascript="search_ca(".dossier::id().",'from_poste2','pa_id2')";
352 $r.=Icon_Action::icon_magnifier(uniqid(), $javascript);
353
354 $r.=_(" et le poste ");
355 $poste->id="to_poste2";
356 $r.=$poste->input("to_poste2",$this->to_poste2);
357 $javascript="search_ca(".dossier::id().",'to_poste2','pa_id2')";
358 $r.=Icon_Action::icon_magnifier(uniqid(), $javascript);
359
360 $r.='<span class="notice" style="display:block">'.
361 _('Selectionnez le plan qui vous int&eacute;resse avant de cliquer sur Recherche').
362 '</span>';
363
364 $r.='</span>';
365 $r.=HtmlInput::submit('Affiche', _('Rechercher'));
366 return $r;
367 }
368 /*!
369 * \brief Show the button to export in PDF or CSV
370 * \param $p_string hidden data to include in the form
371 *
372 *
373 * \return string with the button
374 */
375 function show_button($p_string="")
376 {
377 $r="";
378 $r.= '<form method="GET" action="export.php" style="display:inline">';
379 $r.= $p_string;
380 $r.= HtmlInput::hidden("to",$this->to);
381 $r.= HtmlInput::hidden("act","PDF:AncBalDouble");
382 $r.= HtmlInput::hidden("from",$this->from);
383 $r.= HtmlInput::hidden("pa_id",$this->pa_id);
384 $r.= HtmlInput::hidden("from_poste",$this->from_poste);
385 $r.= HtmlInput::hidden("to_poste",$this->to_poste);
386 $r.= HtmlInput::hidden("pa_id2",$this->pa_id2);
387 $r.= HtmlInput::hidden("from_poste2",$this->from_poste2);
388 $r.= HtmlInput::hidden("to_poste2",$this->to_poste2);
389 $r.=dossier::hidden();
390 $r.=HtmlInput::submit('bt_pdf',"Export en PDF");
391 $r.= '</form>';
392
393 $r.= '<form method="GET" action="export.php" style="display:inline">';
394 $r.= HtmlInput::hidden("to",$this->to);
395 $r.= HtmlInput::hidden("act","CSV:AncBalDouble");
396 $r.= HtmlInput::hidden("from",$this->from);
397 $r.= HtmlInput::hidden("pa_id",$this->pa_id);
398 $r.= HtmlInput::hidden("from_poste",$this->from_poste);
399 $r.= HtmlInput::hidden("to_poste",$this->to_poste);
400 $r.= HtmlInput::hidden("pa_id2",$this->pa_id2);
401 $r.= HtmlInput::hidden("from_poste2",$this->from_poste2);
402 $r.= HtmlInput::hidden("to_poste2",$this->to_poste2);
403 $r.= $p_string;
404 $r.= dossier::hidden();
405 $r.=HtmlInput::submit('bt_csv',"Export en CSV");
406 $r.= '</form>';
407 return $r;
408
409 }
410 /*!
411 * \brief complete the object with the data in $_REQUEST
412 */
413 function get_request()
414 {
415 parent::get_request();
416 $this->from_poste2=(isset($_REQUEST['from_poste2']))?$_REQUEST['from_poste2']:"";
417 $this->to_poste2=(isset($_REQUEST['to_poste2']))?$_REQUEST['to_poste2']:"";
418 $this->pa_id2=(isset($_REQUEST['pa_id2']))?$_REQUEST['pa_id2']:"";
419
420 }
421 /*!
422 * \brief load the data from the database
423 *
424 * \return array
425 */
426 function load()
427 {
428 $filter_poste="";
429 $and="";
430 if ( $this->from_poste != "" )
431 {
432 $filter_poste.=" $and upper(pa.po_name)>= upper('".Database::escape_string($this->from_poste)."')";
433 $and=" and ";
434
435 }
436 if ( $this->to_poste != "" )
437 {
438 $filter_poste.=" $and upper(pa.po_name)<= upper('".Database::escape_string($this->to_poste)."')";
439 $and=" and ";
440 }
441
442 if ( $this->from_poste2 != "" )
443 {
444 $filter_poste.=" $and upper(pb.po_name)>= upper('".Database::escape_string($this->from_poste2)."')";
445 $and=" and ";
446 }
447 if ( $this->to_poste2 != "" )
448 {
449 $filter_poste.=" $and upper(pb.po_name)<= upper('".Database::escape_string($this->to_poste2)."')";
450 $and=" and ";
451 }
452 if ( $filter_poste != "")
453 $filter_poste=" where ".$filter_poste;
454
455 $sql="
456 select a_po_id ,
457 pa.po_name as a_po_name,
458 pa.po_description as a_po_description,
459 pb.po_description as b_po_description,
460
461 b_po_id,
462 pb.po_name as b_po_name,
463 sum(a_oa_amount_c) as a_c,
464 sum(a_oa_amount_d) as a_d
465 from (select
466 a.j_id,
467 a.po_id as a_po_id,
468 b.po_id as b_po_id,
469 case when a.oa_debit='t' then a.oa_amount else 0 end as a_oa_amount_d,
470 case when a.oa_debit='f' then a.oa_amount else 0 end as a_oa_amount_c
471 from
472 operation_analytique as a join operation_analytique as b on (a.oa_row=b.oa_row
473 and a.oa_group=b.oa_group
474 and a.j_id = b.j_id)
475 join poste_analytique as poa on (a.po_id=poa.po_id)
476 join poste_analytique as pob on (b.po_id=pob.po_id)
477 where poa.pa_id= $1
478 and pob.pa_id= $2 ".$this->set_sql_filter()."
479 ) as m join poste_analytique as pa on ( a_po_id=pa.po_id)
480 join poste_analytique as pb on (b_po_id=pb.po_id)
481
482 $filter_poste
483
484 group by a_po_id,b_po_id,pa.po_name,pa.po_description,pb.po_name,pb.po_description
485 order by 2;
486 ";
487
488
489 $array=$this->db->get_array($sql,[$this->pa_id,$this->pa_id2]);
490 $this->has_data=count($array);
491 if ( $this->has_data == 0 )
492 return null;
493 $a=array();
494 $count=0;
495 foreach ($array as $row)
496 {
497 $a[$count]['a_po_id']=$row['a_po_id'];
498 $a[$count]['a_d']=$row['a_d'];
499 $a[$count]['a_c']=$row['a_c'];
500 $a[$count]['b_po_id']=$row['b_po_id'];
501
502 $a[$count]['a_po_name']=$row['a_po_name'];
503 $a[$count]['a_po_description']=$row['a_po_description'];
504 $a[$count]['b_po_name']=$row['b_po_name'];
505 $a[$count]['b_po_description']=$row['b_po_description'];
506 $a[$count]['a_solde']=abs($row['a_c']-$row['a_d']);
507 $a[$count]['a_debit']=($row['a_d']>$row['a_c'])?"debit":"credit";
508
509 $count++;
510 }
511 return $a;
512
513
514 }
515
516
517 /*!
518 * \brief add extra lines with sum of each account
519 * \param $p_array array returned by load()
520 */
522 {
523 $tot_deb=0;
524 $tot_cred=0;
525 $old="";
526 $first=true;
527 $array=array();
528 foreach ( $p_array as $row)
529 {
530
531 if ( $old != $row['a_po_name'] && $first==false )
532
533 {
534 $s=abs($tot_deb-$tot_cred);
535 $d=($tot_deb>$tot_cred)?'debit':'credit';
536 $array[]=array('poste'=>$old,'desc'=>$old_desc
537 ,'debit'=>$tot_deb,'credit'=>$tot_cred,
538 'solde'=>$s,'dc'=>$d);
539
540 $tot_deb=0;
541 $tot_cred=0;
542
543 $old=$row['a_po_name'];
544 $old_desc=$row['a_po_description'];
545 }
546
547 if ( $first )
548 {
549 $first=false;
550 $old=$row['a_po_name'];
551 $old_desc=$row['a_po_description'];
552 }
553
554 $tot_deb+=$row['a_d'];
555 $tot_cred+=$row['a_c'];
556
557
558 }
559 $s=abs($tot_deb-$tot_cred);
560 $d=($tot_deb>$tot_cred)?'debit':'credit';
561 $array[]=array('poste'=>$old,'desc'=>$old_desc
562 ,'debit'=>$tot_deb,'credit'=>$tot_cred,
563
564 'solde'=>$s,'dc'=>$d);
565
566
567 return $array;
568
569 }
570 /*!
571 * \brief for testing and debuggind the class
572 * it must never be called from production system, it is
573 * intended only for developpers
574 * \param
575 * \param
576 * \param
577 *
578 *
579 * \return none
580 */
581 static function test_me()
582 {
584
586 $bal->get_request();
587
588 echo '<form method="GET">';
589
590 echo $bal->display_form();
591 echo '</form>';
592 if ( isset($_GET['result']))
593 {
594 echo $bal->show_button("","");
595 echo "<h1>HTML</h1>";
596 echo $bal->display_html();
597 echo "<h1>CSV</h1>";
598 echo $bal->display_csv();
599
600 }
601 }
602}
th($p_string, $p_extra='', $raw='')
Definition: ac_common.php:58
h2info($p_string)
Definition: ac_common.php:63
nb($p_number)
format the number for the CSV export
Definition: ac_common.php:107
td($p_string='', $p_extra='')
surround the string with td
Definition: ac_common.php:83
nbm($p_number, $p_dec=2)
format the number with a sep.
Definition: ac_common.php:137
catch(Exception $exc) if(! $g_user->can_write_action($ag_id)) $r
$anc pa_id
$anc_grandlivre from_poste
$anc_grandlivre to
$anc_grandlivre to_poste
$anc_grandlivre from
$_REQUEST['ac']
$pa
Definition: anc_od.inc.php:34
$from_poste name
$_GET['qcode']
Print the crossed balance between 2 plan.
show_sum($p_array)
add extra lines with sum of each account
static test_me()
for testing and debuggind the class it must never be called from production system,...
get_request()
complete the object with the data in $_REQUEST
display_html()
compute the html display
show_button($p_string="")
Show the button to export in PDF or CSV.
display_form($p_string='')
Compute the form to display.
display_csv()
Compute the csv export.
display_pdf()
Display the result in pdf.
load()
load the data from the database
Concerns the Analytic plan (table plan_analytique)
this class is the mother class for the CA printing
static connect()
static request_to_hidden(array $array)
transform $_REQUEST data to hidden
static hidden($p_name, $p_value, $p_id="")
static submit($p_name, $p_value, $p_javascript="", $p_class="smallbutton")
Html Input , create a tag <SELECT> ... </SELECT> if readonly == true then display the label correspon...
Html Input.
Definition: itext.class.php:30
static icon_magnifier($id, $p_javascript, $p_style="")
Display a icon with a magnify glass.
Manage the CSV : manage files and write CSV record.
API for creating PDF, unicode, based on tfpdf.
Definition: pdf.class.php:34
$count
$SecUser db
$side