[Noalyss-commit] [noalyss] 127/219: Task #0001484: CA : opération diverses , possibilité d'ajouter une fiche Add card to misc operation ANC, - implemented also in Printing : balance , History , General Ledger...

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit cdc4bb1396fee50c851f67c42520f6ed69c227c8
Author: Dany De Bontridder <address@hidden>
Date:   Wed Nov 1 17:44:17 2017 +0100

    Task #0001484: CA : opération diverses , possibilité d'ajouter une fiche
    Add card to misc operation ANC,
    - implemented also in Printing : balance , History , General Ledger...
---
 html/js/anc_script.js                       |   1 +
 include/class/anc_acc_list.class.php        | 144 +++++++++++++++++++++-------
 include/class/anc_balance_double.class.php  |   7 +-
 include/class/anc_grandlivre.class.php      |  52 +++++++---
 include/class/anc_group_operation.class.php |  35 +++++--
 include/class/anc_operation.class.php       |  38 ++++++--
 sql/upgrade.sql                             | 135 ++++++++++++++++++++++++++
 7 files changed, 343 insertions(+), 69 deletions(-)

diff --git a/html/js/anc_script.js b/html/js/anc_script.js
index 7d4491f..20a6b69 100644
--- a/html/js/anc_script.js
+++ b/html/js/anc_script.js
@@ -367,6 +367,7 @@ function anc_add_row(tableid)
         var newCell = oRow.insertCell(e);
         var tt = rowToCopy.cells[e].innerHTML;
         var new_tt = tt.replace(/pop0/g, "pop" + nb.value);
+        var new_tt = tt.replace(/qcode0/g, "qcode" + nb.value);
         new_tt = new_tt.replace(/pamount0/g, "pamount" + nb.value);
         new_tt = new_tt.replace(/pdeb0/g, "pdeb" + nb.value);
         newCell.innerHTML = new_tt;
diff --git a/include/class/anc_acc_list.class.php 
b/include/class/anc_acc_list.class.php
index 001066b..b6185ab 100644
--- a/include/class/anc_acc_list.class.php
+++ b/include/class/anc_acc_list.class.php
@@ -64,24 +64,43 @@ class Anc_Acc_List extends Anc_Acc_Link
     $sql_from_poste=($this->from_poste!='')?" and  po.po_name >= 
upper('".Database::escape_string($this->from_poste)."')":'';
     $sql_to_poste=($this->to_poste!='')?" and  po.po_name <= 
upper('".Database::escape_string($this->to_poste)."')":'';
     $this->arow=$this->db->get_array("
- SELECT po.po_id, po.pa_id, po.po_name, po.po_description, sum(
+        with m as (select 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_poste is not null then
+        jrnx.j_poste
+        when jrnx.j_poste is null then
+        (SELECT fiche_detail.ad_value
+           FROM fiche_detail
+          WHERE fiche_detail.ad_id = 5 AND fiche_detail.f_id = 
operation_analytique.f_id) end as j_poste
+   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, jrnx.j_poste, tmp_pcmn.pcm_lib AS name
+        END) AS sum_amount, m.j_poste, tmp_pcmn.pcm_lib AS name
    FROM operation_analytique
    JOIN poste_analytique po USING (po_id)
-   JOIN jrnx USING (j_id)
-   JOIN tmp_pcmn ON jrnx.j_poste::text = tmp_pcmn.pcm_val::text ".
-"                                      where
-               pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste."
-
-  GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.j_poste, tmp_pcmn.pcm_lib, 
po.po_description
+   JOIN m USING (po_id)
+   JOIN tmp_pcmn ON m.j_poste::text = tmp_pcmn.pcm_val::text 
+                             where pa_id=$1 
".$date.$sql_from_poste.$sql_to_poste."
+  GROUP BY po.po_id, po.po_name, po.pa_id, m.j_poste, tmp_pcmn.pcm_lib, 
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  order by po_id,j_poste",array($this->pa_id));
+END) <> 0::numeric  order by po_id,j_poste
+",array($this->pa_id));
 
   }
   /**
@@ -94,21 +113,38 @@ END) <> 0::numeric  order by 
po_id,j_poste",array($this->pa_id));
     $date=($date != '')?"  $date":'';
     $sql_from_poste=($this->from_poste!='')?" and  po.po_name >= 
upper('".Database::escape_string($this->from_poste)."')":'';
     $sql_to_poste=($this->to_poste!='')?" and  po.po_name <= 
upper('".Database::escape_string($this->to_poste)."')":'';
-    $this->arow=$this->db->get_array(" SELECT po.po_id, po.pa_id, po.po_name, 
po.po_description, sum(
+    $this->arow=$this->db->get_array("
+with m as (select 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_poste is not null then
+        jrnx.j_poste
+        when jrnx.j_poste 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, jrnx.f_id, jrnx.j_qcode, ( SELECT 
fiche_detail.ad_value
-           FROM fiche_detail
-          WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id) AS 
name
+        END) AS sum_amount, m.f_id1 as f_id, m.j_qcode, m.name
    FROM operation_analytique
    JOIN poste_analytique po USING (po_id)
-   JOIN jrnx USING (j_id) ".
+   JOIN m USING (po_id) ".
                                     " where pa_id=$1 
".$date.$sql_from_poste.$sql_to_poste
                                     ."
-  GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.f_id, jrnx.j_qcode, ( SELECT 
fiche_detail.ad_value
-   FROM fiche_detail
-  WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id), 
po.po_description
+  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
@@ -127,24 +163,46 @@ END) <> 0::numeric order by 
po_name,name",array($this->pa_id));
     $date=($date != '')?"  $date":'';
     $sql_from_poste=($this->from_poste!='')?" and  po.po_name >= 
upper('".Database::escape_string($this->from_poste)."')":'';
     $sql_to_poste=($this->to_poste!='')?" and  po.po_name <= 
upper('".Database::escape_string($this->to_poste)."')":'';
-  $this->arow=$this->db->get_array("SELECT po.po_id, po.pa_id, po.po_name, 
po.po_description, sum(
+  $this->arow=$this->db->get_array("
+       with m as (select 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_poste is not null then
+        jrnx.j_poste
+        when jrnx.j_poste is null then
+        (SELECT fiche_detail.ad_value
+           FROM fiche_detail
+          WHERE fiche_detail.ad_id = 5 AND fiche_detail.f_id = 
operation_analytique.f_id) end as j_poste
+   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, jrnx.j_poste, tmp_pcmn.pcm_lib AS name
+        END) AS sum_amount, m.j_poste, tmp_pcmn.pcm_lib AS name
    FROM operation_analytique
    JOIN poste_analytique po USING (po_id)
-   JOIN jrnx USING (j_id)
-   JOIN tmp_pcmn ON jrnx.j_poste::text = tmp_pcmn.pcm_val::text ".
-"                                      where
-               pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste."
-
-  GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.j_poste, tmp_pcmn.pcm_lib, 
po.po_description
+   JOIN m USING (po_id)
+   JOIN tmp_pcmn ON m.j_poste::text = tmp_pcmn.pcm_val::text 
+   where
+                            pa_id=$1 ".$date.$sql_from_poste.$sql_to_poste."
+  GROUP BY po.po_id, po.po_name, po.pa_id, m.j_poste, tmp_pcmn.pcm_lib, 
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  order by j_poste,po_name",array($this->pa_id));
+END) <> 0::numeric  order by po_id,po_name
+
+",array($this->pa_id));
 
   }
 
@@ -159,21 +217,39 @@ END) <> 0::numeric  order by 
j_poste,po_name",array($this->pa_id));
     $sql_from_poste=($this->from_poste!='')?" and  po.po_name >= 
upper('".Database::escape_string($this->from_poste)."')":'';
     $sql_to_poste=($this->to_poste!='')?" and  po.po_name <= 
upper('".Database::escape_string($this->to_poste)."')":'';
 
-   $this->arow=$this->db->get_array(" SELECT po.po_id, po.pa_id, po.po_name, 
po.po_description, sum(
+   $this->arow=$this->db->get_array(" 
+with m as (select 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, jrnx.f_id, jrnx.j_qcode, ( SELECT 
fiche_detail.ad_value
-           FROM fiche_detail
-          WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id) AS 
name
+        END) AS sum_amount,m.f_id1  as f_id, m.j_qcode, m.name
    FROM operation_analytique
+   join m using(po_id)
    JOIN poste_analytique po USING (po_id)
-   JOIN jrnx USING (j_id) ".
+    ".
                                     " where pa_id=$1 
".$date.$sql_from_poste.$sql_to_poste
                                     ."
-  GROUP BY po.po_id, po.po_name, po.pa_id, jrnx.f_id, jrnx.j_qcode, ( SELECT 
fiche_detail.ad_value
-   FROM fiche_detail
-  WHERE fiche_detail.ad_id = 1 AND fiche_detail.f_id = jrnx.f_id), 
po.po_description
+  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
diff --git a/include/class/anc_balance_double.class.php 
b/include/class/anc_balance_double.class.php
index 88985d0..e73ac9c 100644
--- a/include/class/anc_balance_double.class.php
+++ b/include/class/anc_balance_double.class.php
@@ -482,13 +482,12 @@ class Anc_Balance_Double extends Anc_Print
              ";
 
 
-        $res=$this->db->exec_sql($sql);
-        $this->has_data=Database::num_row($res);
-        if ( Database::num_row($res) == 0 )
+        $array=$this->db->get_array($sql);
+        $this->has_data=count($array);
+        if ( $this->has_data == 0 )
             return null;
         $a=array();
         $count=0;
-        $array=Database::fetch_all($res);
         foreach ($array as $row)
         {
             $a[$count]['a_po_id']=$row['a_po_id'];
diff --git a/include/class/anc_grandlivre.class.php 
b/include/class/anc_grandlivre.class.php
index 8e6d813..6318df9 100644
--- a/include/class/anc_grandlivre.class.php
+++ b/include/class/anc_grandlivre.class.php
@@ -71,10 +71,20 @@ class Anc_GrandLivre extends Anc_Print
        j_id ,
        jr_internal,
        jr_id,
-       jr_comment,
-       j_poste,
-       jrnx.f_id,
-       ( select ad_value from fiche_Detail where f_id=jrnx.f_id and ad_id=23) 
as qcode,
+       coalesce(jr_comment,b.oa_description) as jr_comment,
+       case when j_poste is null and b.f_id is not null then
+        (select ad_value from fiche_detail where fiche_detail.f_id=b.f_id and 
ad_id=".ATTR_DEF_ACCOUNT.")
+            when j_poste is not null then
+            j_poste
+            end as j_poste
+        ,
+       coalesce(jrnx.f_id,b.f_id) as f_id,
+        case when jrnx.f_id is not null then 
+                (select ad_value from fiche_Detail where f_id=jrnx.f_id and 
ad_id=23) 
+                when b.f_id is not null then
+                (select ad_value from fiche_Detail where f_id=b.f_id and 
ad_id=23)
+       end
+                as qcode,
         jr_pj_number
        from operation_analytique as B join poste_analytique using(po_id)
        left join jrnx using (j_id)
@@ -99,14 +109,25 @@ class Anc_GrandLivre extends Anc_Print
         $array=$this->db->get_array("  select
        po_name,
        to_char(oa_date,'DD.MM.YYYY') as oa_date,
-       j_poste,
-       ( select ad_value from fiche_Detail where f_id=jrnx.f_id and ad_id=23) 
as qcode,
-       jr_comment,
-        jr_pj_number,
-       jr_internal,
-        oa_row,
-       case when oa_debit='t' then 'D' else 'C' end,
-       oa_amount
+       case when j_poste is null and b.f_id is not null then
+        (select ad_value from fiche_detail where fiche_detail.f_id=b.f_id and 
ad_id=".ATTR_DEF_ACCOUNT.")
+            when j_poste is not null then
+            j_poste
+            end as j_poste
+        ,
+        case when jrnx.f_id is not null then 
+                (select ad_value from fiche_Detail where f_id=jrnx.f_id and 
ad_id=23) 
+                when b.f_id is not null then
+                (select ad_value from fiche_Detail where f_id=b.f_id and 
ad_id=23)
+       end
+                as qcode,
+        coalesce(jr_comment,b.oa_description) as jr_comment,
+        coalesce (jr_pj_number,'') as jr_pj_number,
+       coalesce(jr_internal,'') as jr_internal,
+        coalesce(oa_group,0) as oa_group,
+       case when oa_debit='t' then oa_amount else  0 end as amount_deb,
+       case when oa_debit='f' then oa_amount else  0 end as amount_cred,
+        case when oa_debit='f' then 'C' else  'D' end as deb_cred
        from operation_analytique as B join poste_analytique using(po_id)
        left join jrnx using (j_id)
        left join jrn on  (j_grpt=jr_grpt_id)
@@ -303,9 +324,10 @@ class Anc_GrandLivre extends Anc_Print
         $aheader[]=array("title"=>'libelle','type'=>'string');
         $aheader[]=array("title"=>'Pièce','type'=>'string');
         $aheader[]=array("title"=>'Num.interne','type'=>'string');
-        $aheader[]=array("title"=>'row','type'=>'num');
-        $aheader[]=array("title"=>'Debit','type'=>'string');
+        $aheader[]=array("title"=>'row','type'=>'string');
+        $aheader[]=array("title"=>'Debit','type'=>'num');
         $aheader[]=array("title"=>'Credit','type'=>'num');
-        Impress::array_to_csv($array, $aheader);
+        $aheader[]=array("title"=>'D/C','type'=>'string');
+        Impress::array_to_csv($array, $aheader,"export-anc-grandlivre");
     }
 }
diff --git a/include/class/anc_group_operation.class.php 
b/include/class/anc_group_operation.class.php
index 4878cce..8c7e22d 100644
--- a/include/class/anc_group_operation.class.php
+++ b/include/class/anc_group_operation.class.php
@@ -108,7 +108,7 @@ class Anc_Group_Operation
 
         $ret.='<table style="result"   >';
 
-        $ret.="<TR>".$wDate->input()."</tr>";
+        $ret.="<TR>".td(_("Date")).td($wDate->input())."</tr>";
         $ret.='<tr><td>Description</td>'.
               '<td colspan="3">'.
               $wDescription->input()."</td></tr>";
@@ -118,27 +118,44 @@ class Anc_Group_Operation
         $ret.='</table><table  id="ago" style="width: 100%;">';
         /* show 10 rows */
         $ret.="<tr>";
+        $ret.=th(_("Fiche"),'style="text-align:left"').th("");
         foreach ($aPlan as $d)
         {
             $idx=$d['id'];
             /* array of possible value for the select */
-            $aPoste[$idx]=$this->db->make_array("select po_id as value,".
+            $aPoste[$idx]=$this->db->make_array("select 0 as value,'-' as 
label "
+                    . " union select po_id as value,".
                                                 " 
po_name||':'||coalesce(po_description,'-') as label ".
                                                 " from poste_analytique ".
                                                 " where pa_id = ".$idx.
-                                                " order by po_name ");
+                                                " order by 2 ");
 
-            $ret.="<th> Poste </th>";
+            $ret.="<th style=\"text-align:left\">".$d['name']."</th>";
         }
         $ret.="<th></th>".
-              "<th> Montant</th>".
-              "<th>D&eacute;bit</th>".
+              "<th style=\"text-align:left\">"._('Montant') ."</th>".
+              "<th style=\"text-align:left\">"._("Débit")."</th>".
               "</tr>";
 
         for ($i = 0;$i < $max;$i++)
         {
+            if ( $p_readonly == 1 &&   isset($this->a_operation[$i]) && 
$this->a_operation[$i]->po_id ==0 ) continue;
             $ret.="<tr>";
-
+            $ret.="<td>";
+            $card=new ICard("qcode$i");
+            $card->set_attribute("typecard", sprintf("[sql] fd_id in (select 
fd_id from jnt_fic_attr where ad_id=%s)",ATTR_DEF_ACCOUNT));
+            $card->set_attribute('label', "qcode{$i}_label");
+            $card->setReadOnly($p_readonly);
+            
$card->value=(isset($this->a_operation[$i]->card))?$this->a_operation[$i]->card:"";
+            $ret.=$card->input();
+            $ret.=$card->search();
+            
+            $ret.='</td>';
+            $ret.='<td>';
+            $label=new ISpan("qcode{$i}_label");
+            $label->style="vertical-align:top";
+            $ret.=$label->input();
+            $ret.='</td>';
             foreach ($aPlan as $d)
             {
                 $idx=$d['id'];
@@ -213,11 +230,12 @@ class Anc_Group_Operation
                 $p->oa_amount=$p_array["pamount$i"];
 
                 $p->oa_description=$p_array["pdesc"];
+                $p->oa_row=$i;
                 $p->oa_date=$p_array['pdate'];
                 $p->j_id=0;
                 $p->oa_debit=(isset ($p_array["pdeb$i"]))?'t':'f';
                 $p->oa_group=0;
-
+                $p->card=(isset($p_array["qcode".$i]))?$p_array["qcode".$i]:"";
                 $p->po_id=$p_array["pop$i"."plan".$idx];
                 $p->pa_id=$idx;
                 $this->a_operation[]=clone $p;
@@ -234,6 +252,7 @@ class Anc_Group_Operation
             $oa_group=$this->db->get_next_seq('s_oa_group');
             for ($i=0;$i<count($this->a_operation);$i++)
             {
+                if ( $this->a_operation[$i]->po_id == 0 ) continue;
                 $this->a_operation[$i]->oa_group=$oa_group;
                 $this->a_operation[$i]->add();
             }
diff --git a/include/class/anc_operation.class.php 
b/include/class/anc_operation.class.php
index e1b06d2..3fc2dd6 100644
--- a/include/class/anc_operation.class.php
+++ b/include/class/anc_operation.class.php
@@ -49,6 +49,7 @@ class Anc_Operation
     var $oa_group;   /*!< group of operation  */
     var $oa_date;         /*!< equal to j_date if j_id is not    null */
     var $pa_id;        /*!< the plan analytique id */
+    var $card;  /*!< Card linked to the operation */
     /**
      * In the case, the amount comes from a ND VAT, the variable
      * contents the jrnx.j_id of the source which was used to compute 
@@ -71,9 +72,10 @@ class Anc_Operation
         $this->oa_positive='Y';
         $this->has_data=0;
         $this->in_div="";
+        $this->card="";
     }
     /*!\brief add a row  to the table operation_analytique
-     * \note if $this->oa_group if 0 then a sequence id will be computed for
+     * \note if $this->oa_group == 0 then a sequence id will be computed for
      * the oa_group, if $this->j_id=0 then it will be null
      *
      */
@@ -114,6 +116,14 @@ class Anc_Operation
             $this->oa_debit=($this->oa_debit=='t')?'f':'t';
         }
         
+        // Retrieve the f_id of the card
+        $n_fid=0;
+        if ( $this->card != "") {
+            $fiche=new Fiche($this->db);
+            $fiche->get_by_qcode($this->card);
+            $n_fid=$fiche->id;
+        }
+        $n_fid=($n_fid!=0)?$n_fid:NULL;
         $oa_row=(isset($this->oa_row))?$this->oa_row:null;
         $sql="insert into operation_analytique (
              po_id, 
@@ -125,8 +135,9 @@ class Anc_Operation
              oa_date,
              oa_row,
              oa_jrnx_id_source,
-             oa_positive
-             ) values ($1,$2,$3,$4,$5,$6,to_date($7,'DD.MM.YYYY'),$8,$9,$10)";
+             oa_positive,
+             f_id
+             ) values 
($1,$2,$3,$4,$5,$6,to_date($7,'DD.MM.YYYY'),$8,$9,$10,$11)";
 
         $this->db->exec_sql($sql,array(
                 $this->po_id, // 1
@@ -138,7 +149,8 @@ class Anc_Operation
                 $this->oa_date, //7
                 $oa_row, //8
                 $this->oa_jrnx_id_source, //8
-                $this->oa_positive
+                $this->oa_positive,
+                $n_fid // fiche.f_id , can be null
                 ));
 
     }
@@ -184,10 +196,20 @@ class Anc_Operation
        j_id ,
        jr_internal,
        jr_id,
-       jr_comment,
-       j_poste,
-       jrnx.f_id,
-       ( select ad_value from fiche_Detail where f_id=jrnx.f_id and ad_id=23) 
as qcode,
+       coalesce(jr_comment,b.oa_description) as jr_comment,
+       case when j_poste is null and b.f_id is not null then
+        (select ad_value from fiche_detail where fiche_detail.f_id=b.f_id and 
ad_id=".ATTR_DEF_ACCOUNT.")
+            when j_poste is not null then
+            j_poste
+            end as j_poste
+        ,
+       coalesce(jrnx.f_id,b.f_id) as f_id,
+        case when jrnx.f_id is not null then 
+                (select ad_value from fiche_Detail where f_id=jrnx.f_id and 
ad_id=23) 
+                when b.f_id is not null then
+                (select ad_value from fiche_Detail where f_id=b.f_id and 
ad_id=23)
+       end
+                as qcode,
         jr_pj_number
        from operation_analytique as B join poste_analytique using(po_id)
        left join jrnx using (j_id)
diff --git a/sql/upgrade.sql b/sql/upgrade.sql
index 81bbc0c..a623d02 100644
--- a/sql/upgrade.sql
+++ b/sql/upgrade.sql
@@ -14,3 +14,138 @@ COMMENT ON COLUMN tmp_pcmn.id IS 'allow to identify the 
row, it is unique and no
 insert into bilan (b_name,b_file_template,b_file_form,b_type) values 
('ASBL','document/fr_be/bnb-asbl.rtf','document/fr_be/bnb-asbl.form','RTF');
 
 alter table jnt_letter drop jl_amount_deb;
+
+ALTER TABLE operation_analytique ADD COLUMN f_id bigint;
+ALTER TABLE operation_analytique  ADD CONSTRAINT 
operation_analytique_fiche_id_fk FOREIGN KEY (f_id)       REFERENCES fiche 
(f_id) MATCH SIMPLE       ON UPDATE cascade ON cascade;
+COMMENT ON COLUMN operation_analytique.f_id IS 'FK to fiche.f_id , used only 
with ODS';
+
+CREATE OR REPLACE FUNCTION comptaproc.table_analytic_account(p_from text, p_to 
text)
+ RETURNS SETOF anc_table_account_type
+ LANGUAGE plpgsql
+AS $function$
+declare
+       ret ANC_table_account_type%ROWTYPE;
+       sql_from text:='';
+       sql_to text:='';
+       sWhere text:='';
+       sAnd text:='';
+       sResult text:='';
+begin
+if p_from <> '' and p_from is not null then
+       sql_from:='oa_date >= to_date('''||p_from::text||''',''DD.MM.YYYY'')';
+       sWhere:=' where ';
+end if;
+
+if p_to <> '' and p_to is not null then
+       sql_to=' oa_date <= to_date('''||p_to::text||''',''DD.MM.YYYY'')';
+       sWhere := ' where ';
+end if;
+
+if sql_to <> '' and sql_from <> '' then
+       sAnd:=' and ';
+end if;
+
+sResult := sWhere || sql_from || sAnd || sql_to;
+
+for ret in EXECUTE '
+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 j_poste, 
tmp_pcmn.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)
+   join tmp_pcmn ON (jrnx.j_poste::text = tmp_pcmn.pcm_val::text or 
tmp_pcmn.pcm_val=fd1.ad_value)
+'|| 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 
+'
+       loop
+       return next ret;
+end loop;
+end;
+$function$
+;
+
+
+CREATE OR REPLACE FUNCTION comptaproc.table_analytic_card(p_from text, p_to 
text)
+ RETURNS SETOF anc_table_card_type
+ LANGUAGE plpgsql
+AS $function$
+declare
+       ret ANC_table_card_type%ROWTYPE;
+       sql_from text:='';
+       sql_to text:='';
+       sWhere text:='';
+       sAnd text:='';
+       sResult text:='';
+begin
+if p_from <> '' and p_from is not null then
+       sql_from:='oa_date >= to_date('''||p_from::text||''',''DD.MM.YYYY'')';
+       sWhere:=' where ';
+end if;
+
+if p_to <> '' and p_to is not null then
+       sql_to=' oa_date <= to_date('''||p_to::text||''',''DD.MM.YYYY'')';
+       sWhere := ' where ';
+end if;
+
+if sql_to <> '' and sql_from <> '' then
+       sAnd :=' and ';
+end if;
+
+sResult := sWhere || sql_from || sAnd || sql_to;
+
+for ret in EXECUTE ' 
+with m as (select 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, 
+        m.name,
+        m.j_qcode
+   FROM operation_analytique
+   JOIN poste_analytique po USING (po_id)
+   join m using (po_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'
+       loop
+       return next ret;
+end loop;
+end;
+$function$
+;
\ No newline at end of file