acc_bilan.class.php

Go to the documentation of this file.

<?php
/*
 *   This file is part of NOALYSS.
 *
 *   NOALYSS is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 *   NOALYSS is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 *
 *   You should have received a copy of the GNU General Public License
 *   along with NOALYSS; if not, write to the Free Software
 *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/
 
// Copyright Author Dany De Bontridder danydb@aevalys.eu
 
/*!\file
 * \brief this class handle the different bilan, from the table bilan
 *
 */
require_once NOALYSS_INCLUDE.'/header_print.php';
 
/*!
 * \brief this class handle the different bilan, from the table bilan, parse the form and replace
 * in the template
 */
#[AllowDynamicProperties]
class Acc_Bilan
{
    var $db;                                            /*!< database connection */
    var $b_id;                                  /*!< id of the bilan (bilan.b_id) */
    var $from;                                  /*!< from periode */
    var $to;                                    /*!< end periode */
 
    function __construct($p_cn)
    {
        $this->db=$p_cn;
    }
    /*!
     * \brief return a string with the form for selecting the periode and
     * the type of bilan
     * \param $p_filter_year filter on a year
     *
     * \return a string
     */
    function display_form($p_filter_year="")
    {
        global $g_parameter;
        $r="";
        $r.=dossier::hidden();
        if ( $g_parameter->MY_REPORT == 'Y') {
 
            $r.= '<TABLE>';
 
            $r.='<TR>';
    // filter on the current year
            $w=new ISelect();
            $w->table=1;
 
            $periode_start=$this->db->make_array("select p_id,to_char(p_start,'DD-MM-YYYY') from parm_periode $p_filter_year order by p_start,p_end");
 
            $periode_end=$this->db->make_array("select p_id,to_char(p_end,'DD-MM-YYYY') from parm_periode $p_filter_year order by p_end,p_start");
 
            $w->label=_("Depuis");
            $w->value=$this->from;
            $w->selected=$this->from;
            $r.= td($w->input('from_periode',$periode_start));
            $w->label=_(" jusque ");
            $w->value=$this->to;
            $w->selected=$this->to;
            $r.= td($w->input('to_periode',$periode_end));
            $r.= "</TR>";
            $r.="<tr>";
            $mod=new ISelect();
            $mod->table=1;
            $mod->value=$this->db->make_array("select b_id, b_name from bilan order by b_name");
            $mod->label=_("Choix du bilan");
            $r.=td($mod->input('b_id'));
            $r.="</tr>";
            $r.= '</TABLE>';
            return $r;
        } elseif($g_parameter->MY_REPORT=="N") {
 
            $periode=new Periode($this->db);
            $from_periode=new IDate("from_periode");
            $from_periode->value=$periode->get_first_date();
            $to_periode=new IDate("to_periode");
            $to_periode->value=$this->db->get_value("select to_char(max(p_end),'DD.MM.YYYY') from parm_periode $p_filter_year");
            $r.='<p>';
            $r.=_("Depuis")." ".$from_periode->input()." "._("jusque")." ".$to_periode->input();
            $r.='</p>';
            $mod=new ISelect();
            $mod->value=$this->db->make_array("select b_id, b_name from bilan order by b_name");
            $r.='<p>';
            $r.=_("Choix du bilan");
            $r.=$mod->input('b_id');
            $r.='</p>';
            return $r;
        }else {
            $dossier_id=Dossier::id();
            $msg=_("Configurer le report ");
            echo_warning($msg . HtmlInput::anchor('COMPANY',"?gDossier=$dossier_id&ac=CFG/COMPANY"));
        }
    }
    /**
     * @brief check and warn if an accound has the wrong saldo
     * @param $p_message legend of the fieldset
     * @param $p_type type of the Acccount ACT actif, ACTINV...
     * @param $p_type the saldo must debit or credit
     */
    private function warning($p_message,$p_type,$p_deb)
    {
        global $g_parameter;
        $filter_sql="";
        if ( $g_parameter->MY_REPORT=="Y") {
            $filter_sql= sql_filter_per($this->db,$this->from,$this->to,'p_id','j_tech_per') ;
            $periode=new Periode($this->db,$this->from);
            $exercice=$periode->get_exercice();
        } else {
            $filter_sql=$this->filter_date();
            $exercice=$this->db->get_value("select max( p_exercice) from parm_periode 
                                            where p_start >= to_date($1,'DD.MM.YYYY') 
                                              and p_end <=  to_date($2,'DD.MM.YYYY') ",
                [$this->from,$this->to]);
        }
        $sql="select
                pcm_val,
                pcm_lib,
                sum(amount_deb) as amount_debit,
                sum(amount_cred) as amount_credit
             from
                tmp_pcmn
                join (select
                        j_poste,
                        case when j_debit='t' then j_montant  else 0 end as amount_deb,
                        case when j_debit='f' then j_montant else 0 end as amount_cred
                        from jrnx
                        where ".$filter_sql."
                 
                ) as m on (j_poste=pcm_val)
        where
                pcm_type = '$p_type'
        group by pcm_val,pcm_lib";
        $res=$this->db->exec_sql($sql);
        if ( Database::num_row($res) ==0 )
            return;
        $count=0;
        $nRow=Database::num_row($res);
 
        $ret="";
        $obj=new Acc_Account_Ledger($this->db,0);
       // $sql=sql_filter_per($this->db,$this->from,$this->to,'p_id','j_tech_per');
        // Find exercice
 
        for ($i=0;$i<$nRow;$i++)
        {
 
            $line=Database::fetch_array($res,$i);
            /* set the periode filter */
            $obj->id=$line['pcm_val'];
            $solde_signed=bcsub($line['amount_debit'],$line['amount_credit']);
 
            if (
                ($solde_signed < 0 && $p_deb == 'D' ) ||
                ($solde_signed > 0 && $p_deb == 'C' )
            )
            {
                $ret.= '<li> '.HtmlInput::history_account($line['pcm_val'],_('Anomalie pour le compte ').$line['pcm_val'].' '.h($line['pcm_lib']).
                       "  D: ".$line['amount_debit'].
                       "  C: ".$line['amount_credit']." diff ".(abs($solde_signed)),"",$exercice);
                $count++;
            }
 
        }
 
        echo '<fieldset>';
        echo '<legend>'.$p_message.'</legend>';
        if ( $count <> 0 )
        {
            echo '<ol>'.$ret.'</ol>';
            echo '<span class="error">'._("Nbres anomalies").' : '.$count.'</span>';
        }
        else
            echo _("Pas d'anomalie détectée");
        echo '</fieldset>';
 
 
    }
    /*!\brief verify that the saldo is good for the type of account */
    function verify()
    {
        global $g_parameter;
                bcscale(2);
        echo '<h3>'._("Comptes normaux").'</h3>';
        $this->warning(_('Actif avec un solde crediteur'),'ACT','D');
        $this->warning(_('Passif avec un solde debiteur'),'PAS','C');
        $this->warning(_('Compte de resultat : Charge avec un solde crediteur'),'CHA','D');
        $this->warning(_('Compte de resultat : produit avec un solde debiteur'),'PRO','C');
        echo '<hr>';
        echo '<h3>'._("Comptes inverses").' </h3>';
        $this->warning(_('Compte inverse : actif avec un solde debiteur'),'ACTINV','C');
        $this->warning(_('Compte inverse : passif avec un solde crediteur'),'PASINV','D');
        $this->warning(_('Compte inverse : Charge avec un solde debiteur'),'CHAINV','C');
        $this->warning(_('Compte inverse : produit avec un solde crediteur'),'PROINV','D');
        echo '<h3'._("Solde").' </h3>';
        if ( $g_parameter->MY_REPORT == 'Y') {
 
            /* set the periode filter */
            $sql_periode = sql_filter_per($this->db, $this->from, $this->to, 'p_id', 'j_tech_per');
        } else {
            $sql_periode=$this->filter_date();
        }
        $sqlAccount="select sum(amount_deb) as amount_debit ,
                            sum(amount_cred) as amount_credit
                 from (
                select j_poste,
                    case when j_debit='t' then j_montant else 0 end as amount_deb,
                    case when j_debit='f' then j_montant else 0 end as amount_cred
                    from 
                    jrnx
                    where
                    $sql_periode
                ) as JP1 join tmp_pcmn on (JP1.j_poste=pcm_val) 
              where pcm_type=$1 or pcm_type=$2";
 
 
        if ( ! $this->db->is_prepare("sqlAccount") ) $this->db->prepare("sqlAccount",$sqlAccount);
        
      
        
        /* debit Actif */
         $res=$this->db->execute("sqlAccount",array('ACT','ACTINV'));
         $result=Database::fetch_array($res, 0);
        
         /* Show detail */
        
        $debit_actif=($result === false)?0:$result['amount_debit'];
        /* Credit Actif */
        $credit_actif=($result === false)?0:$result['amount_credit'];
        $total_actif=abs(bcsub($debit_actif,$credit_actif));
        echo '<table >';
        echo tr(td(_('Total actif')).td($total_actif,'style="text-align:right"'));
 
        /* debit passif */
        $res=$this->db->execute("sqlAccount",array('PAS','PASINV'));
        $result=Database::fetch_array($res, 0);
        $debit_passif=($result === false)?0:$result['amount_debit'];
 
        /* Credit passif */
        $credit_passif=($result === false)?0:$result['amount_credit'];
 
        $total_passif=abs(bcsub($debit_passif,$credit_passif));
 
        /* diff actif / passif */
        echo tr(td(_('Total passif')).td($total_passif,'style="text-align:right"'));
        if ( $total_actif != $total_passif )
        {
            $diff=bcsub($total_actif,$total_passif);
            echo tr(td(' Difference Actif - Passif ').td($diff,'style="text-align:right"'),'style="font-weight:bolder"');
        }
 
        /* debit charge */
        $res=$this->db->execute("sqlAccount",array('CHA','CHAINV'));
        $result=Database::fetch_array($res, 0);
        $debit_charge=($result === false)?0:$result['amount_debit'];
 
        /* Credit charge */
        $credit_charge=($result === false)?0:$result['amount_credit'];
        
        $total_charge=abs(bcsub($debit_charge,$credit_charge));
        echo tr(td(_('Total charge ')).td($total_charge,'style="text-align:right"'));
 
 
        /* debit prod */
        $res=$this->db->execute("sqlAccount",array('PRO','PROINV'));
        $result=Database::fetch_array($res, 0);
        $debit_pro=($result === false)?0:$result['amount_debit'];
 
        /* Credit prod */
        $credit_pro=($result === false)?0:$result['amount_credit'];
        
        $total_pro=abs(bcsub($debit_pro,$credit_pro));
        echo tr(td(_('Total produit')).td($total_pro,'style="text-align:right"'));
 
        $diff=bcsub($total_pro,$total_charge);
 
        echo tr( td(_("Difference Produit - Charge"),'style="padding-right:20px"').td($diff,'style="text-align:right"'),'style="font-weight:bolder"');
        echo '</table>';
    }
    /*!
     * \brief get data from the $_GET
     *
     */
    function get_request_get()
    {
        // $g_parameter=new Noalyss_Parameter_Folder($this->db);
        global $g_parameter;
        $http=new \HttpInput();
        $this->b_id=$http->get("b_id","number","");
        if ( $g_parameter->MY_REPORT=="Y") {
 
            $this->from=$http->get("from_periode","number",-1);
            $this->to=$http->get("to_periode","number",-1);
        }else {
            $this->from=$http->get("from_periode","date","");
            $this->to=$http->get("to_periode","date","");
 
        }
    }
    /*!\brief load from the database the document data  */
    function load():void
    {
        try
        {
            if ( $this->b_id=="")
                throw new Exception(_("le formulaire id n'est pas donnee"));
 
            $sql="select b_name,b_file_template,b_file_form,lower(b_type) as b_type from bilan where".
                 " b_id = $1";
            $res=$this->db->exec_sql($sql,[$this->b_id]);
 
            if ( Database::num_row($res)==0)
                throw new Exception (_('Aucun enregistrement trouve'));
            $array=Database::fetch_array($res,0);
            $this->b_name=$array['b_name'];
            $this->b_file_template=$array['b_file_template'];
            $this->b_file_form=$array['b_file_form'];
            $this->b_type=$array['b_type'];
 
        }
        catch(Exception $Ex)
        {
            record_log($Ex);
            echo $Ex->getMessage();
            throw $Ex;
        }
    }
    /*!\brief open the file of the form */
    /*\return an handle to this file */
    function file_open_form()
    {
        $form=fopen($this->b_file_form,'r');
        if ( $form == false)
        {
            echo 'Cannot Open';
           throw new Exception(_('Echec ouverture fichier '.$this->b_file_form));
        }
        return $form;
    }
    /*!\brief open the file with the template */
    /*\return an handle to this file */
    function file_open_template()
    {
        $templ=fopen($this->b_file_template,'r');
        if ( $templ == false)
        {
            echo 'Cannot Open';
              throw new Exception(_('Echec ouverture fichier '.$this->b_file_template));
        }
        return $templ;
 
    }
    /*!
     * \brief Compute all the formula
     * \param $p_handle the handle to the file
     * \param
     * \param
     *
     *
     * \return
     */
    function compute_formula($p_handle)
    {
        global $g_parameter;
        while (! feof ($p_handle))
        {
            $buffer=trim(fgets($p_handle));
            //echo "buffer [$buffer]\n";
            // $a=(Impress::check_formula($buffer)  == true)?"$buffer ok<br>":'<font color="red">'.'Pas ok '.$buffer."</font><br>";
            // echo $a;
            // blank line are skipped
            if (noalyss_strlentrim($buffer)==0)
                continue;
            // skip comment
            if ( strpos($buffer,'#') === true )
                continue;
            // buffer contains a formula A$=....
            // We need to eval it
            $type_date= ($g_parameter->MY_REPORT=="Y")?0:1;
            $a=Impress::parse_formula($this->db,"$buffer",$buffer,$this->from,$this->to,false,$type_date);
            $b=noalyss_str_replace("$","\$this->",$a);
           // echo $b;
            if ( eval("$b;") === false )
                echo(__FILE__.__LINE__."Code failed with $b");
 
 
        }// end read form line per line
    }
    /*!\brief generate the ods document
    * \param the handle to the template file
    * \return the xml
    *@note
    * Sur une seule ligne il y a plusieurs données, donc il y a plusieurs boucles, pour les autres documents
    * cela devrait être fait aussi, actuellement ces documents, n'acceptent qu'une formule par ligne.
    *@note
    * Pas de header dans les entêtes car n'est pas compris dans le document qu'on utilise
    */
    function generate_odt()
    {
        // create a temp directory in /tmp to unpack file and to parse it
        $dirname=tempnam($_ENV['TMP'],'bilan_');
 
 
        unlink($dirname);
        mkdir ($dirname);
        chdir($dirname);
            
        // Les documents sont dans noalyss/html/document/...
        $file_base=NOALYSS_HOME.DIRECTORY_SEPARATOR.$this->b_file_template;
        $work_file=basename($file_base);
        if ( copy ($file_base,$work_file) == false )
        {
            echo _("erreur Ouverture fichier");
              throw new Exception(_('Echec ouverture fichier '.$file_base));
        }
        ob_start();
        /* unzip the document */
        $zip = new Zip_Extended;
        if ($zip->open($work_file) === TRUE)
          {
            $zip->extractTo($dirname.DIRECTORY_SEPARATOR);
            $zip->close();
          } else
          {
            echo __FILE__.":".__LINE__."cannot unzip model ".$file_base;
          }
 
        ob_end_clean();
        unlink($work_file);
        // remove the zip file
        $p_file=fopen('content.xml','r');
 
        if ( $p_file == false)
        {
             throw new Exception(_('Echec ouverture fichier '.$p_file));
        }
 
        $r="";
        $regex="/&lt;&lt;\\$[A-Z]*[0-9]*&gt;&gt;/";
        $lt="&lt;";
        $gt="&gt;";
            $header_txt=mb_convert_encoding(header_txt($this->db),'UTF-8','ISO8859-1');
        $header_txt=iconv('ISO-8859-1','UTF-8//IGNORE',header_txt($this->db));
        
        
 
        while ( !feof($p_file) )
        {
            $line_rtf=fgets($p_file);
 
            /*
             * replace the header tag, doesn't work if inside header
             */
            $line_rtf=preg_replace('/&lt;&lt;header&gt;&gt;/',$header_txt,$line_rtf);
 
 
            // the line contains the magic <<
            $tmp="";
 
 
            while (preg_match_all($regex,$line_rtf,$f2) > 0 )
              {
                // the f2 array contains all the magic << in the line
                foreach ($f2 as $f2_array)
                  {
                    foreach ($f2_array as $f2_str)
                      {
                        $to_remove=$f2_str;
                        $f2_value=noalyss_str_replace("&lt;","",$f2_str);
                        $f2_value=noalyss_str_replace("&gt;","",$f2_value);
                        $f2_value=noalyss_str_replace("$","",$f2_value);
 
 
 
                        // check for missing variables and labels (N vars)
                        if( ! isset($this->$f2_value))
                          {
 
                            $a = "!!".$f2_value."!!";
                            if( substr($f2_value, 0, 1) == "N" )
                              {
                                $ret = $this->db->get_array("SELECT pcm_lib AS acct_name FROM tmp_pcmn WHERE pcm_val::text LIKE ".
                                                            " substr($1, 2)||'%' ORDER BY pcm_val ASC LIMIT 1",array($f2_value));
                                if($ret[0]['acct_name'])
                                  {
                                    $a = $ret[0]['acct_name'];
                                    $a=noalyss_str_replace('<','&lt;',$a);
                                    $a=noalyss_str_replace('>','&gt;',$a);
                                  }
                              }
                          }
                        else
                          {
                            $a=$this->$f2_value;
                          }
                        if ( $a=='-0' ) $a=0;
 
                        /*  allow numeric cel in ODT for the formatting and formula */
                        if ( is_numeric($a) )
                          {
                            /* -- for libreOffice < 5 or openoffice -- */
                            $searched='office:value-type="string"><text:p>'.$f2_str;
                            $replaced='office:value-type="float" office:value="'.$a.'"><text:p>'.$f2_str;
                            $line_rtf=noalyss_str_replace($searched, $replaced, $line_rtf);
                            /* -- for libreOffice > 5 -- */
                            $searched='office:value-type="string" calcext:value-type="string"><text:p>'.$f2_str;
                            $replaced='office:value-type="float" office:value="'.$a.'" calcext:value-type="float"><text:p>'.$f2_str;
                            $line_rtf=noalyss_str_replace($searched, $replaced, $line_rtf);
                          }
 
 
                        $line_rtf=noalyss_str_replace($f2_str,$a,$line_rtf);
 
                      }// foreach end
                  } // foreach
              } // preg_match_all
            $r.=$line_rtf;
 
        }// odt file is read
 
        return $r;
 
    }
 
    /*!
     * \brief generate the plain  file (rtf,txt, or html)
     * \param the handle to the template file
     */
    function generate_plain($p_file)
    {
        $r="";
        if ( $this->b_type=='html')
        {
            $lt='&lt;';
            $gt='&gt;';
            $pattern='/&lt;&lt;header&gt;&gt;/';
        }
        else
        {
            $lt='<';
            $gt='>';
            $pattern='/<<header>>/';
        }
 
        $header_txt=header_txt($this->db);
        
        if  ($this->b_type == "rtf") {
            $header_txt=convert_to_rtf($header_txt);
        }
        while ( !feof($p_file) )
        {
            $line_rtf=fgets($p_file);
 
            $line_rtf=preg_replace($pattern,$header_txt,$line_rtf);
 
 
            // the line contains the magic <<
            if (preg_match_all("/".$lt.$lt."\\$[a-zA-Z]*[0-9]*".$gt.$gt."/",$line_rtf,$f2) > 0)
            {
                // DEBUG
                //    echo $r.'<br>';
                // the f2 array contains all the magic << in the line
                foreach ($f2 as $f2_str)
                {
                    // DEBUG
                    // echo "single_f2 = $f2_str <br>";
                    // replace single_f2 by its value
                    $f2_value=noalyss_str_replace($lt,"",$f2_str);
                    $f2_value=noalyss_str_replace($gt,"",$f2_value);
                    $f2_value=noalyss_str_replace("$","",$f2_value);
                    $f2_value=$f2_value[0];
 
                    // check for missing variables and labels (N vars)
                    if( ! isset($this->$f2_value))
                    {
                        $a = "!!".$f2_value."!!";
                        if( substr($f2_value, 0, 1) == "N" )
                        {
                            $ret = $this->db->get_array("SELECT pcm_lib AS acct_name FROM tmp_pcmn WHERE ".
                                                        " pcm_val::text LIKE substr($1, 2)||'%' ORDER BY pcm_val ASC LIMIT 1",
                                                        array($f2_value));
                            if($ret[0]['acct_name'])
                            {
                                /* for rtf we have the string to put it in latin1 */
                                if ( $this->b_type != "rtf") { $a = mb_convert_encoding(($ret[0]['acct_name']),'ISO-8859-1','UTF-8');}
                                if ( $this->b_type == "rtf") { $a =convert_to_rtf($ret[0]['acct_name']);}
                            }
                        }
                    }
                    else
                    {
                        // DEBUG
                        //echo "f2_value=$f2_value";
                        //                $a=${"$f2_value"};
                        $a=$this->$f2_value;
                    }
                    // DEBUG      echo " a = $a";
                    if ( $a=='-0' ) $a=0;
                    $line_rtf=noalyss_str_replace($f2_str,$a,$line_rtf);
 
                }// foreach end
            }
            $r.=$line_rtf;
 
        }// rtf file is read
        // DEBUG
        //  fwrite($out,$r);
 
        return $r;
 
 
 
 
    }
    /*!\brief generate the document and send it to the browser
     */
    function generate()
    {
        // Load the data
        $this->load();
        // Open the files
        $form=$this->file_open_form();
 
        // Compute all the formula and add the value to this
        $this->compute_formula($form);
        fclose($form);
        // open the form
        $templ=$this->file_open_template();
        switch ($this->b_type)
        {
        case 'rtf':
            $result=$this->generate_plain($templ);
            $this->send($result);
            break;
        case 'txt':
            $result=$this->generate_plain($templ);
            $this->send($result);
        case 'html':
            $result=$this->generate_plain($templ);
            $this->send($result);
 
            break;
        case 'odt':
        case 'ods':
            $result=$this->generate_odt($templ);
            $this->send($result);
            break;
 
        }
        fclose($templ);
    }
    /*!\brief send the result of generate plain to the browser
     * \param $p_result is the string returned by generate_...
     */
    function send($p_result)
    {
        switch ($this->b_type)
        {
        case 'rtf':
            // A rtf file is generated
            header('Content-type: application/rtf');
            header('Content-Disposition: attachment; filename="'.$this->b_name.'.rtf"');
            echo $p_result;
            break;
 
        case 'txt':
            // A txt file is generated
            header('Content-type: application/txt');
            header('Content-Disposition: attachment; filename="'.$this->b_name.'.txt"');
 
            echo $p_result;
            break;
        case 'html':
            // A txt file is generated
            header('Content-type: application/html');
            header('Content-Disposition: attachment; filename="'.$this->b_name.'.html"');
 
            echo $p_result;
            break;
        case 'odt':
        case 'ods':
            header("Pragma: public");
            header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
            header("Cache-Control: must-revalidate");
            if ( $this->b_type == 'odt' )
            {
                header('Content-type: application/vnd.oasis.opendocument.text');
                header('Content-Disposition: attachment;filename="'.$this->b_name.'.odt"',FALSE);
            }
            if ( $this->b_type == 'ods' )
            {
                header('Content-type: application/vnd.oasis.opendocument.spreadsheet');
                header('Content-Disposition: attachment;filename="'.$this->b_name.'.ods"',FALSE);
            }
            
            header("Accept-Ranges: bytes");
            ob_start();
            // save the file in a temp folder
            // create a temp directory in /tmp to unpack file and to parse it
            // bug PHP 7 tempnam throw a notice
            $dirname=@tempnam($_ENV['TMP'].DIRECTORY_SEPARATOR.'tmp','bilan_');
 
 
            unlink($dirname);
            mkdir ($dirname);
            chdir($dirname);
            // create a temp directory in /tmp to unpack file and to parse it
            $file_base=NOALYSS_HOME.DIRECTORY_SEPARATOR.$this->b_file_template;
            $work_file=basename($file_base);
            if ( copy ($file_base,$work_file) == false )
            {
                throw new Exception ( _("Ouverture fichier impossible"));
            }
            /*
             * unzip the document
             */
            ob_start();
            $zip = new Zip_Extended;
            if ($zip->open($work_file) === TRUE)
              {
                $zip->extractTo($dirname.DIRECTORY_SEPARATOR);
                $zip->close();
              }
            else
              {
                echo __FILE__.":".__LINE__."cannot unzip model ".$file_base;
              }
 
            // Remove the file we do  not need anymore
            unlink ($work_file);
 
 
            // replace the file
            $p_file=fopen($dirname.DIRECTORY_SEPARATOR.'content.xml','wb');
            if ( $p_file == false )
            {
                  throw new Exception ( _("erreur Ouverture fichier").' content.xml');
 
            }
            $a=fwrite($p_file,$p_result);
            if ( $a==false)
            {
                throw new Exception ( _("erreur écriture fichier").' content.xml');
            }
            // repack
            $zip = new Zip_Extended;
            $res = $zip->open($this->b_name.".".$this->b_type, ZipArchive::CREATE);
            if($res !== TRUE)
              {
                throw new Exception (__FILE__.":".__LINE__."cannot recreate zip");
              }
            $zip->add_recurse_folder($dirname.DIRECTORY_SEPARATOR);
            $zip->close();
 
            ob_end_clean();
            fclose($p_file);
            $fdoc=fopen($dirname.DIRECTORY_SEPARATOR.$this->b_name.'.'.$this->b_type,'r');
            if ( $fdoc == false )
            {
                  throw new Exception   (_("erreur Ouverture fichier"));
            }
            $buffer=fread ($fdoc,filesize($dirname.DIRECTORY_SEPARATOR.$this->b_name.'.'.$this->b_type));
            echo $buffer;
 
            break;
            // and send
        }
 
    }
    static function test_me()
    {
 
        if ( isset($_GET['result']))
        {
            ob_start();
            $cn=Dossier::connect();
            $a=new Acc_Bilan($cn);
            $a->get_request_get();
 
            $a->load();
            $form=$a->file_open_form();
            $a->compute_formula($form);
            fclose($form);
            // open the form
            $templ=$a->file_open_template();
            $r=$a->generate_odt($templ);
            fclose($templ);
            ob_end_clean();
 
            $a->send($r);
        }
        else
        {
            $cn=Dossier::connect();
            $a=new Acc_Bilan($cn);
            $a->get_request_get();
 
            echo '<form method="get">';
            echo $a->display_form();
            echo HtmlInput::hidden('test_select',$_GET['test_select']).dossier::hidden();
            echo HtmlInput::submit('result','Sauve');
            echo '</form>';
        }
    }
 
    /**
     * @brief compute the where clause for filtering with the date
     * @return string where clause : where jrnx.j_date < ...
     * @throws Exception if one of the dates is not valid
     */
    private function filter_date():string
    {
        global $g_parameter;
        if ( empty(isDate($this->from)) || empty(isDate($this->to)))
            throw new Exception (_("Date invalide"));
        $from=format_date($this->from,'DD.MM.YYYY','YYYYMMDD');
        $to=format_date($this->to,'DD.MM.YYYY','YYYYMMDD');
        $sql_periode=" to_char(jrnx.j_date,'YYYYMMDD')>= '$from' 
        and  to_char(jrnx.j_date,'YYYYMMDD') <='$to' ";
        return $sql_periode;
    }
}