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 More...

	display_html ()

	export_csv ()

	load_card ()
	load the data does not return anything but give a value to this->aheader and this->arow More...

	load_poste ()
	load the data does not return anything but give a value to this->aheader and this->arow More...

	show_button ($p_hidden)
	display the button export CSV More...

Public Member Functions inherited from Anc_Acc_Link
	__contruct ($p_cn)

	get_request ()
	get the parameters More...

	set_sql_filter ()
	Set the filter (account_date) More...

Public Member Functions inherited from Anc_Print
	__construct ($p_cn)

	check ()

	display_form ($p_hidden="")
	Compute the form to display. More...

	get_request ()
	complete the object with the data in $_REQUEST More...

	set_sql_filter ()
	Set the filter (account_date) More...

Private Member Functions
	create_temp_table_account ()
	create the temporary table TABLE_ANALYTIC to store values for an account. More...

	create_temp_table_card ()
	create the temporary table TABLE_ANALYTIC to store values for a card More...

Additional Inherited Members
Data Fields inherited from Anc_Print
	$db

	$from

	$from_poste

	$to

	$to_poste

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 34 of file anc_table.class.php.

    {
        parent::__construct($p_cn);
    }

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 66 of file anc_table.class.php.

    {
        $sWhere = "";
        $sql_from = "";
        $sql_to = "";
        $sql_and = "";
 
        if (trim($this->from) != "" && isDate($this->from) != NULL) {
            $sql_from = "oa_date >= to_date('" . $this->from . "','DD.MM.YYYY')";
            $sWhere = " where ";
        }
        if (trim($this->to) != "" && isDate($this->to) != NULL) {
            $sql_to = "oa_date <= to_date('" . $this->to . "','DD.MM.YYYY')";
            $sWhere = " where ";
        }
        if ($sql_to != "" && $sql_from != "") {
            $sql_and = " and ";
        }
        $sResult = $sWhere . $sql_from . $sql_and . $sql_to;
 
        $sql = "
          create temporary table table_analytic as 
      SELECT po.po_id,
                            po.pa_id, po.po_name, 
                            po.po_description,sum(
                CASE
                    WHEN oa1.oa_debit = true THEN oa1.oa_amount * (-1)::numeric
                    ELSE oa1.oa_amount
                END) AS sum_amount, 
                coalesce(jrnx.j_poste,fd1.ad_value) as card_account,
                pcm_lib as name
        FROM operation_analytique as oa1
        JOIN poste_analytique po USING (po_id)
        left join fiche_detail as fd1 on (oa1.f_id=fd1.f_id and fd1.ad_id=5)
        left JOIN jrnx USING (j_id)
        left join tmp_pcmn ON (jrnx.j_poste::text = tmp_pcmn.pcm_val::text)
     {$sResult}
       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
      HAVING sum(
     CASE
         WHEN oa1.oa_debit = true THEN oa1.oa_amount * (-1)::numeric
         ELSE oa1.oa_amount
     END) <> 0::numeric ";
        $this->db->exec_sql($sql);
    }

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 115 of file anc_table.class.php.

    {
        $sWhere = "";
        $sql_from = "";
        $sql_to = "";
        $sql_and = "";
 
        if (trim($this->from) != "" && isDate($this->from) != NULL) {
            $sql_from = "oa_date >= to_date('" . $this->from . "','DD.MM.YYYY')";
            $sWhere = " where ";
        }
        if (trim($this->to) != "" && isDate($this->to) != NULL) {
            $sql_to = "oa_date <= to_date('" . $this->to . "','DD.MM.YYYY')";
            $sWhere = " where ";
        }
        if ($sql_to != "" && $sql_from != "") {
            $sql_and = " and ";
        }
        $sResult = $sWhere . $sql_from . $sql_and . $sql_to;
        $sql = "
           create temporary table table_analytic as 
          with m as (select oa_id,po_id,
                coalesce(jrnx.f_id,operation_analytique.f_id) as f_id1,
                case when jrnx.j_qcode is not null then 
                ( SELECT fiche_detail.ad_value
                   FROM fiche_detail
                  WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id) 
                  when jrnx.f_id is null and operation_analytique.f_id is not null then 
                  ( SELECT fiche_detail.ad_value
                   FROM fiche_detail
                  WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = operation_analytique.f_id)
                 end
                        AS name,
                         case when jrnx.j_qcode is not null then
                      jrnx.j_qcode
                      when jrnx.f_id is null then
                      (SELECT fiche_detail.ad_value
                         FROM fiche_detail
                        WHERE fiche_detail.ad_id = 23 AND fiche_detail.f_id = operation_analytique.f_id) end as j_qcode
                 FROM operation_analytique
                 left JOIN jrnx USING (j_id) ) 
            SELECT po.po_id, po.pa_id, po.po_name, po.po_description,  sum(
              CASE
                  WHEN operation_analytique.oa_debit = true THEN operation_analytique.oa_amount * (-1)::numeric
                  ELSE operation_analytique.oa_amount
              END) AS sum_amount,
                      m.f_id1 as f_id, 
              m.name,
              m.j_qcode as card_account
         FROM operation_analytique
         JOIN poste_analytique po USING (po_id)
         join m using (oa_id)
      {$sResult}
        GROUP BY po.po_id, po.po_name, po.pa_id, m.f_id1, m.j_qcode,m.name, po.po_description
       HAVING sum(
      CASE
          WHEN operation_analytique.oa_debit = true THEN operation_analytique.oa_amount * (-1)::numeric
          ELSE operation_analytique.oa_amount
      END) <> 0::numeric";
        $this->db->exec_sql($sql);
    }

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 48 of file anc_table.class.php.

    {
        $r = parent::display_form($p_hidden);
        $icard = new ISelect('card_poste');
        $icard->value = array(
            array('value' => 1, 'label' => 'Par fiche'),
            array('value' => 2, 'label' => 'Par poste comptable')
        );
        $icard->selected = $this->card_poste;
        $r .= $icard->input();
        $r .= HtmlInput::request_to_hidden(array('ac'));
        return $r;
    }

References $r, and HtmlInput\request_to_hidden().

Here is the call graph for this function:

◆ display_html()

Anc_Table::display_html ( )

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

    {
        bcscale(2);
        if ($this->check() != 0) {
            alert(_("Date invalide"));
            return;
        }
 
        if ($this->card_poste == '1') {
            $this->load_card();
 
            echo '<table class="result">';
            echo '<tr>';
            echo th('Fiche');
            foreach ($this->aheader as $h) {
                echo '<th style="text-align:right">' . h($h['po_name']) . '</th>';
            }
            echo th('Total', ' style="text-align:right"');
            echo '</tr>';
            /*
             * Show all the result
             */
            $tot_global = 0;
            for ($i = 0; $i < count($this->arow); $i++) {
                $tr = ($i % 2 == 0) ? '<tr class="even">' : '<tr class="odd">';
                echo $tr;
                echo td(HtmlInput::history_card($this->arow[$i]['f_id'], $this->arow[$i]['card_account'] . ' ' . $this->arow[$i]['name']));
                $tot_row = 0;
                for ($x = 0; $x < count($this->aheader); $x++) {
                    $amount = $this->db->get_value($this->sql, array($this->arow[$i]['f_id'], $this->aheader[$x]['po_id']));
                    if ($amount == null) $amount = 0;
                    if (isset($tot_col[$x])) {
                        $tot_col[$x] = bcadd($tot_col[$x], $amount);
                    } else {
                        $tot_col[$x] = $amount;
                    }
                    $side = ($amount < 0) ? 'D' : 'C';
                    $side = ($amount == 0) ? "=" : $side;
                    echo td(nbm(abs($amount)) . " " . $side, ' class="num" ');
                    $tot_row = bcadd($tot_row, $amount);
                }
                $side = ($tot_row < 0) ? 'D' : 'C';
                $side = ($tot_row == 0) ? "=" : $side;
                echo td(nbm(abs($tot_row)) . " " . $side, ' class="num"');
                $tot_global = bcadd($tot_global, $tot_row);
                echo '</tr>';
 
 
            }
            echo '<tr class="highlight">';
            echo td('Totaux');
            for ($i = 0; $i < count($this->aheader); $i++) {
                $side = ($tot_col[$i] < 0) ? "D" : "C";
                $side = ($tot_global == 0) ? "=" : $side;
                echo td(nbm(abs($tot_col[$i])) . " " . $side, ' class="num"');
            }
            $side = ($tot_global > 0) ? "C" : "D";
            $side = ($tot_global == 0) ? "=" : $side;
            echo td(nbm($tot_global) . " " . $side, ' class="num  " ');
            echo '</tr>';
            echo '</table>';
        }
        if ($this->card_poste == '2') {
            $this->load_poste();
 
            echo '<table class="result">';
            echo '<tr>';
            echo th('poste comptable ');
            foreach ($this->aheader as $h) {
                echo '<th style="text-align:right">' . h($h['po_name']) . '</th>';
            }
            echo th('Total', ' style="text-align:right"');
            echo '</tr>';
            /*
             * Show all the result
             */
            $tot_global = 0;
            for ($i = 0; $i < count($this->arow); $i++) {
                $tr = ($i % 2 == 0) ? '<tr class="even">' : '<tr class="odd">';
                echo $tr;
                echo td(HtmlInput::history_account($this->arow[$i]['card_account'], $this->arow[$i]['card_account'] . ' ' . $this->arow[$i]['name']));
                $tot_row = 0;
                for ($x = 0; $x < count($this->aheader); $x++) {
                    $amount = $this->db->get_value($this->sql, array($this->arow[$i]['card_account'], $this->aheader[$x]['po_id']));
                    if ($amount == null) $amount = 0;
                    if (isset($tot_col[$x])) {
                        $tot_col[$x] = bcadd($tot_col[$x], $amount);
                    } else {
                        $tot_col[$x] = $amount;
                    }
                    $side = ($amount < 0) ? 'D' : 'C';
                    $side = ($amount == 0) ? "=" : $side;
                    echo td(nbm(abs($amount)) . " " . $side, ' class="num" ');
                    $tot_row = bcadd($tot_row, $amount);
                }
                $side = ($tot_row < 0) ? 'D' : 'C';
                $side = ($tot_row == 0) ? "=" : $side;
                echo td(nbm(abs($tot_row)) . " " . $side, ' class="num"');
                $tot_global = bcadd($tot_global, $tot_row);
                echo '</tr>';
 
 
            }
            echo '<tr class="highlight">';
 
 
            echo td('Totaux');
            for ($i = 0; $i < count($this->aheader); $i++) {
                $side = ($tot_col[$i] < 0) ? "D" : "C";
                echo td(nbm(abs($tot_col[$i])) . " " . $side, ' class="num"');
            }
            $side = ($tot_global > 0) ? "C" : "D";
            $side = ($tot_global == 0) ? "=" : $side;
 
            echo td(nbm($tot_global) . " " . $side, ' class="num input_text" ');
            echo '</tr>';
            echo '</table>';
 
        }
 
    }

References $amount, $i, $side, alert(), Anc_Print\check(), db, h, HtmlInput\history_account(), HtmlInput\history_card(), 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 369 of file anc_table.class.php.

    {
        bcscale(2);
        if ($this->check() != 0) {
            throw new Exception ("DATE INVALIDE");
        }
        $csv = new Noalyss_Csv("Anc-table");
        $csv->send_header();
        //---------------
        // By Card
        if ($this->card_poste == '1') {
            $this->load_card();
 
            $csv->add("Fiche");
            foreach ($this->aheader as $h) {
                $csv->add($h['po_name']);
            }
            $csv->add("Total");
            $csv->write();
 
            /*
             * Show all the result
             */
 
            for ($i = 0; $i < count($this->arow); $i++) {
 
                $csv->add($this->arow[$i]['card_account'] . ' ' . $this->arow[$i]['name']);
                $tot_row = 0;
                for ($x = 0; $x < count($this->aheader); $x++) {
                    $amount = $this->db->get_value($this->sql, array($this->arow[$i]['f_id'], $this->aheader[$x]['po_id']));
                    if ($amount == null) $amount = 0;
                    if (isset($tot_col[$x])) {
                        $tot_col[$x] = bcadd($tot_col[$x], $amount);
                    } else {
                        $tot_col[$x] = $amount;
                    }
                    $csv->add($amount, "number");
                    $tot_row = bcadd($tot_row, $amount);
                }
                $csv->add($tot_row, "number");
                $csv->write();
 
            }
            /* ----
             * Sum for each column
             */
            $csv->add("");
            $sum_col = 0;
            for ($x = 0; $x < count($this->aheader); $x++) {
                $csv->add($tot_col[$x], "number");
                $sum_col = bcadd($sum_col, $tot_col[$x]);
            }
            $csv->add($sum_col, "number");
            $csv->write();
        }
        //---------------
        // By Accounting
        if ($this->card_poste == '2') {
            $this->load_poste();
 
            $csv->add(_("Poste"));
            $csv->add(_("Description"));
            foreach ($this->aheader as $h) {
                $csv->add($h['po_name']);
            }
            $csv->add("Total");
            $csv->write();
            /*
             * Show all the result
             */
 
            for ($i = 0; $i < count($this->arow); $i++) {
 
                $csv->add($this->arow[$i]['card_account']);
                $csv->add($this->arow[$i]['name']);
                $tot_row = 0;
                for ($x = 0; $x < count($this->aheader); $x++) {
                    $amount = $this->db->get_value($this->sql, array($this->arow[$i]['card_account'], $this->aheader[$x]['po_id']));
                    if ($amount == null) $amount = 0;
                    if (isset($tot_col[$x])) {
                        $tot_col[$x] = bcadd($tot_col[$x], $amount);
                    } else {
                        $tot_col[$x] = $amount;
                    }
                    $csv->add($amount, "number");
                    $tot_row = bcadd($tot_row, $amount);
                }
                $csv->add($tot_row, "number");
                $csv->write();
 
 
            }
            /* ----
             * Sum for each column
             */
            $csv->add("");
            $csv->add("");
            $sum_col = 0;
            for ($x = 0; $x < count($this->aheader); $x++) {
                $csv->add($tot_col[$x], "number");
                $sum_col = bcadd($sum_col, $tot_col[$x]);
            }
            $csv->add($sum_col, "number");
            $csv->write();
        }
 
    }

References $amount, $csv, $i, 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 207 of file anc_table.class.php.

    {
        $sql_from_poste = ($this->from_poste != '') ? " and  po_name >= upper('" . Database::escape_string($this->from_poste) . "')" : '';
        $sql_to_poste = ($this->to_poste != '') ? " and  po_name <= upper('" . Database::escape_string($this->to_poste) . "')" : '';
        // $this->db->exec_sql('create temporary table table_analytic as select * from comptaproc.table_analytic_card(\''.$this->from.'\',\''.$this->to.'\')');
        $this->create_temp_table_card();
        $header = "select distinct po_id,po_name from table_analytic
                where
                pa_id=$1 " . $sql_from_poste . $sql_to_poste . " order by po_name";
        $this->aheader = $this->db->get_array($header, array($this->pa_id));
 
        $this->arow = $this->db->get_array("select distinct f_id,card_account,name from  table_analytic 
                        where
                pa_id=$1 " . $sql_from_poste . $sql_to_poste . " order by name", array($this->pa_id));
        $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;
    }

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 182 of file anc_table.class.php.

    {
        $sql_from_poste = ($this->from_poste != '') ? " and  po_name >= upper('" . Database::escape_string($this->from_poste) . "')" : '';
        $sql_to_poste = ($this->to_poste != '') ? " and  po_name <= upper('" . Database::escape_string($this->to_poste) . "')" : '';
 
 
        $this->create_temp_table_account();
 
        $header = "select distinct po_id,po_name  from table_analytic
                where
                pa_id=$1 " . $sql_from_poste . $sql_to_poste . " order by po_name";
        $this->aheader = $this->db->get_array($header, array($this->pa_id));
 
        $this->arow = $this->db->get_array("select distinct card_account,name
                from table_analytic
                where
                pa_id=$1 " . $sql_from_poste . $sql_to_poste . " order by card_account", array($this->pa_id));
 
        $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;
    }

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_hidden is a string containing hidden items

Returns: html string

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

    {
        $r = "";
        $r .= '<form method="GET" action="export.php"  style="display:inline">';
        $r .= HtmlInput::hidden("act", "CSV:AncTable");
        $r .= HtmlInput::hidden("to", $this->to);
        $r .= HtmlInput::hidden("from", $this->from);
        $r .= HtmlInput::hidden("pa_id", $this->pa_id);
        $r .= HtmlInput::hidden("from_poste", $this->from_poste);
        $r .= HtmlInput::hidden("to_poste", $this->to_poste);
        $r .= HtmlInput::hidden("card_poste", $this->card_poste);
        $r .= $p_hidden;
        $r .= dossier::hidden();
        $r .= HtmlInput::submit('bt_csv', _("Export en CSV"));
        $r .= '</form>';
        return $r;
    }