noalyss Version-10
NOALYSS : serveur de comptabilité et ERP (2002)
Loading...
Searching...
No Matches
Public Member Functions | Data Fields | Private Member Functions | Private Attributes
Anc_Table Class Reference

printing analytic and accountancy as a table More...

+ Inheritance diagram for Anc_Table:
+ Collaboration diagram for Anc_Table:

Public Member Functions

 __construct ($p_cn)
 
 display_form ($p_hidden='')
 display form to get the parameter
 
 display_html ()
 
 export_csv ()
 
 load_card ()
 load the data does not return anything but give a value to this->aheader and this->arow
 
 load_poste ()
 load the data does not return anything but give a value to this->aheader and this->arow
 
 show_button ($p_hidden)
 display the button export CSV
 
- Public Member Functions inherited from Anc_Acc_Link
 __contruct ($p_cn)
 
 get_request ()
 get the parameters
 
 set_sql_filter ()
 Set the filter (account_date)
 
- Public Member Functions inherited from Anc_Print
 check ()
 

Data Fields

 $card_poste
 
- Data Fields inherited from Anc_Print
 $db
 
 $from
 
 $from_poste
 
 $has_data
 
 $pa_id
 
 $to
 
 $to_poste
 

Private Member Functions

 create_temp_table_account ()
 create the temporary table TABLE_ANALYTIC to store values for an account.
 
 create_temp_table_card ()
 create the temporary table TABLE_ANALYTIC to store values for a card
 

Private Attributes

 $aheader
 
 $arow
 
 $sql
 

Detailed Description

printing analytic and accountancy as a table

Definition at line 32 of file anc_table.class.php.

Constructor & Destructor Documentation

◆ __construct()

Anc_Table::__construct ( $p_cn)

Reimplemented from Anc_Print.

Definition at line 38 of file anc_table.class.php.

39 {
40 parent::__construct($p_cn);
41 }

Member Function Documentation

◆ create_temp_table_account()

Anc_Table::create_temp_table_account ( )
private

create the temporary table TABLE_ANALYTIC to store values for an account.

Definition at line 70 of file anc_table.class.php.

71 {
72 $sWhere = "";
73 $sql_from = "";
74 $sql_to = "";
75 $sql_and = "";
76
77 if (trim($this->from) != "" && isDate($this->from) != NULL) {
78 $sql_from = "oa_date >= to_date('" . $this->from . "','DD.MM.YYYY')";
79 $sWhere = " where ";
80 }
81 if (trim($this->to) != "" && isDate($this->to) != NULL) {
82 $sql_to = "oa_date <= to_date('" . $this->to . "','DD.MM.YYYY')";
83 $sWhere = " where ";
84 }
85 if ($sql_to != "" && $sql_from != "") {
86 $sql_and = " and ";
87 }
88 $sResult = $sWhere . $sql_from . $sql_and . $sql_to;
89
90 $sql = "
91 create temporary table table_analytic as
92 SELECT po.po_id,
93 po.pa_id, po.po_name,
94 po.po_description,sum(
95 CASE
96 WHEN oa1.oa_debit = true THEN oa1.oa_amount * (-1)::numeric
97 ELSE oa1.oa_amount
98 END) AS sum_amount,
99 coalesce(jrnx.j_poste,fd1.ad_value) as card_account,
100 pcm_lib as name
101 FROM operation_analytique as oa1
102 JOIN poste_analytique po USING (po_id)
103 left join fiche_detail as fd1 on (oa1.f_id=fd1.f_id and fd1.ad_id=5)
104 left JOIN jrnx USING (j_id)
105 left join tmp_pcmn ON (jrnx.j_poste::text = tmp_pcmn.pcm_val::text)
106 {$sResult}
107 GROUP BY po.po_id, po.po_name, po.pa_id, coalesce(jrnx.j_poste,fd1.ad_value), tmp_pcmn.pcm_lib, po.po_description
108 HAVING sum(
109 CASE
110 WHEN oa1.oa_debit = true THEN oa1.oa_amount * (-1)::numeric
111 ELSE oa1.oa_amount
112 END) <> 0::numeric ";
113 $this->db->exec_sql($sql);
114 }
isDate
isDate($p_date)
Verifie qu'une date est bien formaté en d.m.y et est valable.
Definition ac_common.php:236
to
$anc_grandlivre to
Definition ajax_history_anc_account.php:62
from
$anc_grandlivre from
Definition ajax_history_anc_account.php:61
Anc_Table\$sql
$sql
Definition anc_table.class.php:36
db
$SecUser db
Definition export_security_pdf.php:118

References $sql, db, from, isDate(), and to.

Referenced by load_poste().

+ Here is the call graph for this function:

◆ create_temp_table_card()

Anc_Table::create_temp_table_card ( )
private

create the temporary table TABLE_ANALYTIC to store values for a card

Definition at line 119 of file anc_table.class.php.

120 {
121 $sWhere = "";
122 $sql_from = "";
123 $sql_to = "";
124 $sql_and = "";
125
126 if (trim($this->from) != "" && isDate($this->from) != NULL) {
127 $sql_from = "oa_date >= to_date('" . $this->from . "','DD.MM.YYYY')";
128 $sWhere = " where ";
129 }
130 if (trim($this->to) != "" && isDate($this->to) != NULL) {
131 $sql_to = "oa_date <= to_date('" . $this->to . "','DD.MM.YYYY')";
132 $sWhere = " where ";
133 }
134 if ($sql_to != "" && $sql_from != "") {
135 $sql_and = " and ";
136 }
137 $sResult = $sWhere . $sql_from . $sql_and . $sql_to;
138 $sql = "
139 create temporary table table_analytic as
140 with m as (select oa_id,po_id,
141 coalesce(jrnx.f_id,operation_analytique.f_id) as f_id1,
142 case when jrnx.j_qcode is not null then
143 ( SELECT fiche_detail.ad_value
144 FROM fiche_detail
145 WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id)
146 when jrnx.f_id is null and operation_analytique.f_id is not null then
147 ( SELECT fiche_detail.ad_value
148 FROM fiche_detail
149 WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = operation_analytique.f_id)
150 end
151 AS name,
152 case when jrnx.j_qcode is not null then
153 jrnx.j_qcode
154 when jrnx.f_id is null then
155 (SELECT fiche_detail.ad_value
156 FROM fiche_detail
157 WHERE fiche_detail.ad_id = 23 AND fiche_detail.f_id = operation_analytique.f_id) end as j_qcode
158 FROM operation_analytique
159 left JOIN jrnx USING (j_id) )
160 SELECT po.po_id, po.pa_id, po.po_name, po.po_description, sum(
161 CASE
162 WHEN operation_analytique.oa_debit = true THEN operation_analytique.oa_amount * (-1)::numeric
163 ELSE operation_analytique.oa_amount
164 END) AS sum_amount,
165 m.f_id1 as f_id,
166 m.name,
167 m.j_qcode as card_account
168 FROM operation_analytique
169 JOIN poste_analytique po USING (po_id)
170 join m using (oa_id)
171 {$sResult}
172 GROUP BY po.po_id, po.po_name, po.pa_id, m.f_id1, m.j_qcode,m.name, po.po_description
173 HAVING sum(
174 CASE
175 WHEN operation_analytique.oa_debit = true THEN operation_analytique.oa_amount * (-1)::numeric
176 ELSE operation_analytique.oa_amount
177 END) <> 0::numeric";
178 $this->db->exec_sql($sql);
179 }

References $sql, db, from, isDate(), and to.

Referenced by load_card().

+ Here is the call graph for this function:

◆ display_form()

Anc_Table::display_form ( $p_hidden = '')

display form to get the parameter

  • card_poste 1 by card, 2 by account
  • from_poste
  • to_poste
  • from from date
  • to until date
  • pa_id Analytic plan to use

Reimplemented from Anc_Print.

Definition at line 52 of file anc_table.class.php.

53 {
54 $r = parent::display_form($p_hidden);
55 $icard = new ISelect('card_poste');
56 $icard->value = array(
57 array('value' => 1, 'label' => 'Par fiche'),
58 array('value' => 2, 'label' => 'Par poste comptable')
59 );
60 $icard->selected = $this->card_poste;
61 $r .= $icard->input();
62 $r .= HtmlInput::request_to_hidden(array('ac'));
63 return $r;
64 }
$r
catch(Exception $exc) if(! $g_user->can_write_action($ag_id)) $r
Definition ajax_add_concerned_card.php:53
Anc_Table\$card_poste
$card_poste
Definition anc_table.class.php:34
HtmlInput\request_to_hidden
static request_to_hidden(array $array)
transform $_REQUEST data to hidden
Definition html_input.class.php:723

References $card_poste, and $r.

◆ display_html()

Anc_Table::display_html ( )

Definition at line 251 of file anc_table.class.php.

252 {
253 bcscale(2);
254 if ($this->check() != 0) {
255 alert(_("Date invalide"));
256 return;
257 }
258
259 if ($this->card_poste == '1') {
260 $this->load_card();
261
262 echo '<table class="result">';
263 echo '<tr>';
264 echo th('Fiche');
265 foreach ($this->aheader as $h) {
266 echo '<th style="text-align:right">' . h($h['po_name']) . '</th>';
267 }
268 echo th('Total', ' style="text-align:right"');
269 echo '</tr>';
270 /*
271 * Show all the result
272 */
273 $tot_global = 0;
274 for ($i = 0; $i < count($this->arow); $i++) {
275 $tr = ($i % 2 == 0) ? '<tr class="even">' : '<tr class="odd">';
276 echo $tr;
277 echo td(HtmlInput::history_card($this->arow[$i]['f_id'], $this->arow[$i]['card_account'] . ' ' . $this->arow[$i]['name']));
278 $tot_row = 0;
279 for ($x = 0; $x < count($this->aheader); $x++) {
280 $amount = $this->db->get_value($this->sql, array($this->arow[$i]['f_id'], $this->aheader[$x]['po_id']));
281 if ($amount == null) $amount = 0;
282 if (isset($tot_col[$x])) {
283 $tot_col[$x] = bcadd($tot_col[$x], $amount);
284 } else {
285 $tot_col[$x] = $amount;
286 }
287 $side = ($amount < 0) ? 'D' : 'C';
288 $side = ($amount == 0) ? "=" : $side;
289 echo td(nbm(abs($amount)) . " " . $side, ' class="num" ');
290 $tot_row = bcadd($tot_row, $amount);
291 }
292 $side = ($tot_row < 0) ? 'D' : 'C';
293 $side = ($tot_row == 0) ? "=" : $side;
294 echo td(nbm(abs($tot_row)) . " " . $side, ' class="num"');
295 $tot_global = bcadd($tot_global, $tot_row);
296 echo '</tr>';
297
298
299 }
300 echo '<tr class="highlight">';
301 echo td('Totaux');
302 for ($i = 0; $i < count($this->aheader); $i++) {
303 $side = ($tot_col[$i] < 0) ? "D" : "C";
304 $side = ($tot_global == 0) ? "=" : $side;
305 echo td(nbm(abs($tot_col[$i])) . " " . $side, ' class="num"');
306 }
307 $side = ($tot_global > 0) ? "C" : "D";
308 $side = ($tot_global == 0) ? "=" : $side;
309 echo td(nbm($tot_global) . " " . $side, ' class="num " ');
310 echo '</tr>';
311 echo '</table>';
312 }
313 if ($this->card_poste == '2') {
314 $this->load_poste();
315
316 echo '<table class="result">';
317 echo '<tr>';
318 echo th('poste comptable ');
319 foreach ($this->aheader as $h) {
320 echo '<th style="text-align:right">' . h($h['po_name']) . '</th>';
321 }
322 echo th('Total', ' style="text-align:right"');
323 echo '</tr>';
324 /*
325 * Show all the result
326 */
327 $tot_global = 0;
328 for ($i = 0; $i < count($this->arow); $i++) {
329 $tr = ($i % 2 == 0) ? '<tr class="even">' : '<tr class="odd">';
330 echo $tr;
331 echo td(HtmlInput::history_account($this->arow[$i]['card_account'], $this->arow[$i]['card_account'] . ' ' . $this->arow[$i]['name']));
332 $tot_row = 0;
333 for ($x = 0; $x < count($this->aheader); $x++) {
334 $amount = $this->db->get_value($this->sql, array($this->arow[$i]['card_account'], $this->aheader[$x]['po_id']));
335 if ($amount == null) $amount = 0;
336 if (isset($tot_col[$x])) {
337 $tot_col[$x] = bcadd($tot_col[$x], $amount);
338 } else {
339 $tot_col[$x] = $amount;
340 }
341 $side = ($amount < 0) ? 'D' : 'C';
342 $side = ($amount == 0) ? "=" : $side;
343 echo td(nbm(abs($amount)) . " " . $side, ' class="num" ');
344 $tot_row = bcadd($tot_row, $amount);
345 }
346 $side = ($tot_row < 0) ? 'D' : 'C';
347 $side = ($tot_row == 0) ? "=" : $side;
348 echo td(nbm(abs($tot_row)) . " " . $side, ' class="num"');
349 $tot_global = bcadd($tot_global, $tot_row);
350 echo '</tr>';
351
352
353 }
354 echo '<tr class="highlight">';
355
356
357 echo td('Totaux');
358 for ($i = 0; $i < count($this->aheader); $i++) {
359 $side = ($tot_col[$i] < 0) ? "D" : "C";
360 echo td(nbm(abs($tot_col[$i])) . " " . $side, ' class="num"');
361 }
362 $side = ($tot_global > 0) ? "C" : "D";
363 $side = ($tot_global == 0) ? "=" : $side;
364
365 echo td(nbm($tot_global) . " " . $side, ' class="num input_text" ');
366 echo '</tr>';
367 echo '</table>';
368
369 }
370
371 }
th
th($p_string, $p_extra='', $raw='')
Definition ac_common.php:58
td
td($p_string='', $p_extra='')
surround the string with td
Definition ac_common.php:83
nbm
nbm($p_number, $p_dec=2)
format the number with a sep.
Definition ac_common.php:137
alert
alert($p_msg, $buffer=false)
alert in javascript
Definition ac_common.php:791
$i
$i
Definition action_document_type_mtable_input.php:83
h
h( $row[ 'oa_description'])
Definition ajax_anc_detail_operation.php:46
$amount
$amount
Definition ajax_anc_key_compute.php:40
_
_("actif, passif,charge,...")
Definition balance.inc.php:195
Anc_Print\check
check()
Definition anc_print.class.php:207
Anc_Table\load_card
load_card()
load the data does not return anything but give a value to this->aheader and this->arow
Definition anc_table.class.php:211
Anc_Table\load_poste
load_poste()
load the data does not return anything but give a value to this->aheader and this->arow
Definition anc_table.class.php:186
$side
$side
Definition letter_prop.php:162

References $amount, $i, $side, $x, _, alert(), Anc_Print\check(), db, h, load_card(), load_poste(), nbm(), td(), and th().

+ Here is the call graph for this function:

◆ export_csv()

Anc_Table::export_csv ( )

Definition at line 373 of file anc_table.class.php.

374 {
375 bcscale(2);
376 if ($this->check() != 0) {
377 throw new Exception ("DATE INVALIDE");
378 }
379 $csv = new Noalyss_Csv("Anc-table");
380 $csv->send_header();
381 //---------------
382 // By Card
383 if ($this->card_poste == '1') {
384 $this->load_card();
385
386 $csv->add("Fiche");
387 foreach ($this->aheader as $h) {
388 $csv->add($h['po_name']);
389 }
390 $csv->add("Total");
391 $csv->write();
392
393 /*
394 * Show all the result
395 */
396
397 for ($i = 0; $i < count($this->arow); $i++) {
398
399 $csv->add($this->arow[$i]['card_account'] . ' ' . $this->arow[$i]['name']);
400 $tot_row = 0;
401 for ($x = 0; $x < count($this->aheader); $x++) {
402 $amount = $this->db->get_value($this->sql, array($this->arow[$i]['f_id'], $this->aheader[$x]['po_id']));
403 if ($amount == null) $amount = 0;
404 if (isset($tot_col[$x])) {
405 $tot_col[$x] = bcadd($tot_col[$x], $amount);
406 } else {
407 $tot_col[$x] = $amount;
408 }
409 $csv->add($amount, "number");
410 $tot_row = bcadd($tot_row, $amount);
411 }
412 $csv->add($tot_row, "number");
413 $csv->write();
414
415 }
416 /* ----
417 * Sum for each column
418 */
419 $csv->add("");
420 $sum_col = 0;
421 for ($x = 0; $x < count($this->aheader); $x++) {
422 $csv->add($tot_col[$x], "number");
423 $sum_col = bcadd($sum_col, $tot_col[$x]);
424 }
425 $csv->add($sum_col, "number");
426 $csv->write();
427 }
428 //---------------
429 // By Accounting
430 if ($this->card_poste == '2') {
431 $this->load_poste();
432
433 $csv->add(_("Poste"));
434 $csv->add(_("Description"));
435 foreach ($this->aheader as $h) {
436 $csv->add($h['po_name']);
437 }
438 $csv->add("Total");
439 $csv->write();
440 /*
441 * Show all the result
442 */
443
444 for ($i = 0; $i < count($this->arow); $i++) {
445
446 $csv->add($this->arow[$i]['card_account']);
447 $csv->add($this->arow[$i]['name']);
448 $tot_row = 0;
449 for ($x = 0; $x < count($this->aheader); $x++) {
450 $amount = $this->db->get_value($this->sql, array($this->arow[$i]['card_account'], $this->aheader[$x]['po_id']));
451 if ($amount == null) $amount = 0;
452 if (isset($tot_col[$x])) {
453 $tot_col[$x] = bcadd($tot_col[$x], $amount);
454 } else {
455 $tot_col[$x] = $amount;
456 }
457 $csv->add($amount, "number");
458 $tot_row = bcadd($tot_row, $amount);
459 }
460 $csv->add($tot_row, "number");
461 $csv->write();
462
463
464 }
465 /* ----
466 * Sum for each column
467 */
468 $csv->add("");
469 $csv->add("");
470 $sum_col = 0;
471 for ($x = 0; $x < count($this->aheader); $x++) {
472 $csv->add($tot_col[$x], "number");
473 $sum_col = bcadd($sum_col, $tot_col[$x]);
474 }
475 $csv->add($sum_col, "number");
476 $csv->write();
477 }
478
479 }
$csv
$csv
Definition ajax_action_concerned_list.php:48

References $amount, $csv, $i, $x, _, Anc_Print\check(), db, load_card(), and load_poste().

+ Here is the call graph for this function:

◆ load_card()

Anc_Table::load_card ( )

load the data does not return anything but give a value to this->aheader and this->arow

Definition at line 211 of file anc_table.class.php.

212 {
213 $sql_from_poste = ($this->from_poste != '') ? " and po_name >= upper('" . Database::escape_string($this->from_poste) . "')" : '';
214 $sql_to_poste = ($this->to_poste != '') ? " and po_name <= upper('" . Database::escape_string($this->to_poste) . "')" : '';
215 // $this->db->exec_sql('create temporary table table_analytic as select * from comptaproc.table_analytic_card(\''.$this->from.'\',\''.$this->to.'\')');
216 $this->create_temp_table_card();
217 $header = "select distinct po_id,po_name from table_analytic
218 where
219 pa_id=$1 " . $sql_from_poste . $sql_to_poste . " order by po_name";
220 $this->aheader = $this->db->get_array($header, array($this->pa_id));
221
222 $this->arow = $this->db->get_array("select distinct f_id,card_account,name from table_analytic
223 where
224 pa_id=$1 " . $sql_from_poste . $sql_to_poste . " order by name", array($this->pa_id));
225 $this->sql = 'select sum_amount from table_analytic where f_id=$1 and po_id=$2 and pa_id=' . $this->pa_id . ' ' . $sql_from_poste . $sql_to_poste;
226 }
pa_id
$anc pa_id
Definition ajax_anc_accounting.php:38
from_poste
$anc_grandlivre from_poste
Definition ajax_history_anc_account.php:48
to_poste
$anc_grandlivre to_poste
Definition ajax_history_anc_account.php:49
Anc_Table\create_temp_table_card
create_temp_table_card()
create the temporary table TABLE_ANALYTIC to store values for a card
Definition anc_table.class.php:119
DatabaseCore\escape_string
static escape_string($p_string)
wrapper for the function pg_escape_string
Definition database_core.class.php:984
$header
if(count($a_accounting)==0) $header
Definition export_gl_csv.php:69

References $header, create_temp_table_card(), db, DatabaseCore\escape_string(), from_poste, pa_id, and to_poste.

Referenced by display_html(), and export_csv().

+ Here is the call graph for this function:

◆ load_poste()

Anc_Table::load_poste ( )

load the data does not return anything but give a value to this->aheader and this->arow

Definition at line 186 of file anc_table.class.php.

187 {
188 $sql_from_poste = ($this->from_poste != '') ? " and po_name >= upper('" . Database::escape_string($this->from_poste) . "')" : '';
189 $sql_to_poste = ($this->to_poste != '') ? " and po_name <= upper('" . Database::escape_string($this->to_poste) . "')" : '';
190
191
192 $this->create_temp_table_account();
193
194 $header = "select distinct po_id,po_name from table_analytic
195 where
196 pa_id=$1 " . $sql_from_poste . $sql_to_poste . " order by po_name";
197 $this->aheader = $this->db->get_array($header, array($this->pa_id));
198
199 $this->arow = $this->db->get_array("select distinct card_account,name
200 from table_analytic
201 where
202 pa_id=$1 " . $sql_from_poste . $sql_to_poste . " order by card_account", array($this->pa_id));
203
204 $this->sql = 'select sum_amount from table_analytic where card_account=$1 and po_id=$2 and pa_id=' . $this->pa_id . ' ' . $sql_from_poste . $sql_to_poste;
205 }
Anc_Table\create_temp_table_account
create_temp_table_account()
create the temporary table TABLE_ANALYTIC to store values for an account.
Definition anc_table.class.php:70

References $header, create_temp_table_account(), db, DatabaseCore\escape_string(), from_poste, pa_id, and to_poste.

Referenced by display_html(), and export_csv().

+ Here is the call graph for this function:

◆ show_button()

Anc_Table::show_button ( $p_hidden)

display the button export CSV

Parameters
$p_hiddenis a string containing hidden items
Returns
html string

Definition at line 233 of file anc_table.class.php.

234 {
235 $r = "";
236 $r .= '<form method="GET" action="export.php" style="display:inline">';
237 $r .= HtmlInput::hidden("act", "CSV:AncTable");
238 $r .= HtmlInput::hidden("to", $this->to);
239 $r .= HtmlInput::hidden("from", $this->from);
240 $r .= HtmlInput::hidden("pa_id", $this->pa_id);
241 $r .= HtmlInput::hidden("from_poste", $this->from_poste);
242 $r .= HtmlInput::hidden("to_poste", $this->to_poste);
243 $r .= HtmlInput::hidden("card_poste", $this->card_poste);
244 $r .= $p_hidden;
245 $r .= dossier::hidden();
246 $r .= HtmlInput::submit('bt_csv', _("Export en CSV"));
247 $r .= '</form>';
248 return $r;
249 }
HtmlInput\hidden
static hidden($p_name, $p_value, $p_id="")
Definition html_input.class.php:248
HtmlInput\submit
static submit($p_name, $p_value, $p_javascript="", $p_class="smallbutton")
Definition html_input.class.php:229

References $r, _, from, from_poste, pa_id, to, and to_poste.

Field Documentation

◆ $aheader

Anc_Table::$aheader
private

Definition at line 35 of file anc_table.class.php.

◆ $arow

Anc_Table::$arow
private

Definition at line 37 of file anc_table.class.php.

◆ $card_poste

Anc_Table::$card_poste

Definition at line 34 of file anc_table.class.php.

Referenced by display_form().

◆ $sql

Anc_Table::$sql
private

Definition at line 36 of file anc_table.class.php.

Referenced by create_temp_table_account(), and create_temp_table_card().

The documentation for this class was generated from the following file:
Generated on Thu Jan 15 2026 10:14:40 for noalyss by doxygen 1.13.2