follow_up.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
require_once NOALYSS_INCLUDE.'/lib/user_common.php';
 
/**
 * \file
 * \brief class_action for manipulating actions
 * action can be :
 * <ul>
 * <li>an invoice
 * <li>a meeting
 * <li>an order
 * <li>a letter
 * </ul>
 * The table document_type are the possible actions
 */
 
/**
 * \class
 * \brief class_action for manipulating actions
 * action can be :
 * <ul>
 * <li> a meeting
 * <li>an order
 * <li>a letter
 * </ul>
 * The table document_type are the possible actions
 */
class Follow_Up
{
 
    var $db; /*!<  $db  database connexion    */
    var $ag_timestamp;  /*!<   $ag_timestamp document date (ag_gestion.ag_timestamp) */
    var $dt_id;   /*!<   $dt_id type of the document (document_type.dt_id) */
    var $ag_state; /*!<   $ag_state stage of the document (printed, send to client...) */
    var $d_number;   /*!<   $d_number number of the document */
    var $d_filename; /*!<   $d_filename filename's document      */
    var $d_mimetype; /*!<   $d_mimetype document's filename      */
    var $ag_title;   /*!<   $ag_title title document          */
    var $f_id; /*!<   $f_id fiche id (From field )  */
    var $ag_ref;  /*!< $ag_ref is the ref  */
    var $ag_hour;  /*!< $ag_hour is the hour of the meeting, action */
    var $ag_priority; /*!< $ag_priority is the priority 1 High, 2 medium, 3 low */
    var $ag_dest;  /*!< $ag_dest person who is in charged */
    var $ag_contact;  /*!< $ag_contact contact */
    var $ag_remind_date;  /*!< $ag_contact contact */
    var $ag_description; /*!< description of the action with HTML tag*/
    var $ag_id; //!< Follow_Up.ag_id
    var $f_id_dest; /*!< followup action recipient */
    var $aAction_detail; //!< Array of action details
 
    /**
     * @var integer $ag_description_id if greater than 0 , it is the id in action_comment
     * of the description (1st comment)
     */
    var $ag_description_id;
    /**
     * $operation string related operation
     */
    var $operation;
 
    /**
     * @brief $action string related action
     */
    var $action;
 
    /**
     * @brief constructor
     * \brief constructor
     * \param p_cn database connection
     */
    function __construct($p_cn, $p_id=0)
    {
        $this->db=$p_cn;
        $this->ag_id=$p_id;
        $this->f_id=0;
        $this->aAction_detail=array();
        $this->operation="";
        $this->action="";
        $this->f_id_dest=0;
        $this->ag_priority=2;
    }
 
    public function __toString(): string
    {
        $r  = " Follow_Up object =>";
        $r .= 'ag_timestamp = ['. $this->ag_timestamp.']';
        $r .= 'dt_id = ['. $this->dt_id.']';
        $r .= 'ag_state = ['. $this->ag_state.']';
        $r .= 'd_number = ['. $this->d_number.']';
        $r .= 'd_filename = ['. $this->d_filename.']';
        $r .= 'd_mimetype = ['. $this->d_mimetype.']';
        $r .= 'ag_title = ['. $this->ag_title.']';
        $r .= 'f_id = ['. $this->f_id.']';
        $r .= 'ag_ref = ['. $this->ag_ref.']';
        $r .= 'ag_hour = ['. $this->ag_hour.']';
        $r .= 'ag_priority = ['. $this->ag_priority.']';
        $r .= 'ag_dest = ['. $this->ag_dest.']';
        $r .= 'ag_contact = ['. $this->ag_contact.']';
        $r .= 'ag_remind_date = ['. $this->ag_remind_date.']';
        $r .= 'f_id_dest = ['. $this->f_id_dest.']';
        $r .= 'ag_description = ['. $this->ag_description.']';
 
        return $r;
 
    }
 
 
    /**
     * @brief Create a filter based on the current user,
     * @remark type $g_user Connected user
     * @param type $cn Database connection
     * @param type $p_mode Mode is R (for Read) or W (for write)
     * @return string SQL where clause to include in the SQL 
     * example: (ag_dest in (select p_granted from user_sec_action_profile where p_id=x)
     */
    static function sql_security_filter($cn, $p_mode)
    {
        global $g_user;
        $profile=$cn->get_value("select p_id from profile_user where user_name=$1", array($g_user->login));
        if ($profile=='')
            die("Security");
        if ( $g_user->isAdmin() == 1) { 
            return "(true)";
        }
        if ($p_mode=='R')
        {
            $sql=" (ag_dest in (select p_granted from user_sec_action_profile where p_id=$profile and ua_right !='X' ) ) ";
        } else if ($p_mode=='W')
        {
            $sql=" ( ag_dest in (select p_granted from user_sec_action_profile where p_id=$profile and ua_right in ('W','O') ) )";
        } else  {
            record_log(_('Securité'));
            throw new Exception(_('Securité'));
        }
        return $sql;
    }
 
    //----------------------------------------------------------------------
    /**
     * \brief Display the object, the tags for the FORM
     *        are in the caller. It will be used for adding and updating
     *        action
     * \note  If  ag_id is not equal to zero then it is an update otherwise
     *        it is a new document
     *
     * \param $p_view form will be in readonly mode (value: READ, UPD or NEW  )
     * \param $p_gen true we show the tag for generating a doc (value : true or false) and adding files
     * \param $p_base is the ac parameter
     * \param $retour is the html code for the return button
     * \note  update the reference number or the document type is not allowed
     *
     *
     * \return string containing the html code
     */
    function display($p_view, $p_gen, $p_base, $retour="")
    {
        global $g_user;
        if ($p_view=='UPD')
        {
            $upd=true;
            $readonly=false;
        }
        elseif ($p_view=="NEW")
        {
            $upd=false;
            $readonly=false;
        }
        elseif ($p_view=='READ')
        {
            $upd=true;
            $readonly=true;
        }
        else
        {
            throw new Exception('class_action'.__LINE__.'Follow_Up::Display error unknown parameter'.$p_view);
        }
        
       
        // Compute the widget
        // Date
        $date=new IDate();
        $date->readOnly=$readonly;
        $date->name="ag_timestamp";
        $date->id="ag_timestamp";
        $date->value=$this->ag_timestamp;
 
        $remind_date=new IDate();
        $remind_date->readOnly=$readonly;
        $remind_date->name="ag_remind_date";
        $remind_date->id="ag_remind_date";
        $remind_date->value=$this->ag_remind_date;
 
 
        // Doc Type
        $doc_type=new ISelect();
        $doc_type->name="dt_id";
        $doc_type->value=$this->db->make_array("select dt_id,dt_value from document_type order by dt_value", 1);
        $doc_type->selected=$this->dt_id;
        $doc_type->readOnly=$readonly;
        $str_doc_type=$doc_type->input();
 
        // Description
        $desc=new ITextArea();
        $desc->set_enrichText("enrich");
        $desc->style=' class="itextarea" style="width:80%;margin-left:5%;"';
        $desc->name="ag_comment";
        $desc->readOnly=$readonly;
        $acomment=$this->db->get_array("SELECT agc_id, ag_id, to_char(agc_date,'DD.MM.YYYY HH24:MI') as str_agc_date, agc_comment, agc_comment_raw,tech_user
                                 FROM action_gestion_comment where ag_id=$1 order by agc_id", array($this->ag_id)
        );
 
        // List opération liées
        $operation=$this->db->get_array("select ago_id,j.jr_id,j.jr_internal,j.jr_comment,to_char(j.jr_date,'DD.MM.YY') as str_date
                        from jrn as j join action_gestion_operation as ago on (j.jr_id=ago.jr_id)
                        where ag_id=$1 order by jr_date", array($this->ag_id));
        $iconcerned=new IConcerned('operation');
 
        // List related action
        $iaction=new IRelated_Action('action');
        $iaction->value=(isset($this->action))?$this->action:"";
 
        // state
        // Retrieve the value
        $a=$this->db->make_array("select s_id,s_value from document_state ");
        $state=new ISelect();
        $state->readOnly=$readonly;
        $state->name="ag_state";
        $state->value=$a;
        $state->selected=$this->ag_state;
        $str_state=$state->input();
 
        // Retrieve the value if there is an attached doc
        $doc_ref="";
        // Document id
 
        $h2=new IHidden();
        $h2->name="d_id";
        $h2->value=$this->d_id;
 
        if ($this->d_id!=0&&$this->d_id!="")
        {
            $h2->readonly=($p_view=='NEW')?false:true;
            $doc=new Document($this->db, $this->d_id);
            $doc->get();
            if (noalyss_strlentrim($doc->d_lob)!=0)
            {
                $d_id=new IHidden();
                $doc_ref="<p> Document ".$doc->anchor().'</p>';
                $doc_ref.=$h2->input().$d_id->input('d_id', $this->d_id);
            }
        }
 
 
        // title
        $title=new IText();
        $title->readOnly=$readonly;
        $title->name="ag_title";
        $title->value=$this->ag_title;
        $title->size=60;
            $title->style='style="font-size:2rem;font-weight:bold"';
 
 
        // Priority of the ag_priority
        $ag_priority=new ISelect();
        $ag_priority->readOnly=$readonly;
        $ag_priority->name="ag_priority";
        $ag_priority->selected=$this->ag_priority;
        $ag_priority->value=array(array('value'=>1, 'label'=>_('Haute')),
            array('value'=>2, 'label'=>_('Normale')),
            array('value'=>3, 'label'=>_('Basse'))
        );
        $str_ag_priority=$ag_priority->input();
 
        // hour of the action (meeting) ag_hour
        $ag_hour=new IText();
        $ag_hour->readOnly=$readonly;
        $ag_hour->name="ag_hour";
        $ag_hour->value=$this->ag_hour;
        $ag_hour->size=6;
        $ag_hour->javascript=" onblur=check_hour('ag_hour');";
        $str_ag_hour=$ag_hour->input();
 
        // Profile in charged of the action
        $ag_dest=new ISelect();
        $ag_dest->readOnly=$readonly;
        $ag_dest->name="ag_dest";
        // select profile
        $aAg_dest=$this->db->make_array("select  p_id as value, ".
                "p_name as label ".
                " from profile  where p_id in ".$g_user->sql_writable_profile()." order by 2");
 
        $ag_dest->value=$aAg_dest;
        $ag_dest->selected=$this->ag_dest;
        $str_ag_dest=$ag_dest->input();
 
        // ag_ref
        // Always false for update
 
        $client_label=new ISpan();
 
        /* Add button */
        $f_add_button=new IButton('add_card');
        $f_add_button->label=_('Créer une nouvelle fiche');
        $f_add_button->set_attribute('ipopup', 'ipop_newcard');
        $filter=$this->db->make_list('select fd_id from fiche_def ');
        $f_add_button->set_attribute('filter', $filter);
 
        $f_add_button->javascript=" select_card_type(this);";
        $str_add_button=$f_add_button->input();
 
        // f_id_dest sender
        if ($this->qcode_dest!=NOTFOUND&&noalyss_strlentrim($this->qcode_dest)!=0)
        {
            $tiers=new Fiche($this->db);
            $tiers->get_by_qcode($this->qcode_dest);
            $qcode_dest_label=strtoupper($tiers->strAttribut(1));
            $qcode_dest_label.=" ".$tiers->strAttribut(ATTR_DEF_FIRST_NAME,0);
            $this->f_id_dest=$tiers->id;
        }
        else
        {
            $qcode_dest_label=($this->f_id_dest==0||trim($this->qcode_dest??"")=="")?'Interne ':'Error';
        }
 
        $h_ag_id=new IHidden();
        // if concerns another action : show the link otherwise nothing
        //
                // sender
        $w=new ICard();
        $w->readOnly=$readonly;
        $w->jrn=0;
        $w->name='qcode_dest';
        $w->value=($this->f_id_dest!=0)?$this->qcode_dest:"";
        $w->label="";
        $list_recipient=$this->db->make_list('select fd_id from fiche_def');
        $w->extra=$list_recipient;
        $w->set_attribute('typecard', $list_recipient);
        $w->set_dblclick("fill_ipopcard(this);");
        $w->set_attribute('ipopup', 'ipopcard');
 
        // name of the field to update with the name of the card
        $w->set_attribute('label', 'qcode_dest_label');
        // name of the field to update with the name of the card
        $w->set_attribute('typecard', $w->extra);
        $w->set_function('fill_data');
        $w->javascript=sprintf(' onchange="fill_data_onchange(\'%s\');" ', $w->name);
 
 
        $sp=new ISpan();
        $sp->extra='class="text-"';
        $sp->name='qcode_dest_label';
        $sp->value=$qcode_dest_label;
 
        // autre - a refaire pour avoir plusieurs fiches
        // Sur le modèle des tags
        $ag_contact=new ICard();
        $ag_contact->readOnly=$readonly;
        $ag_contact->jrn=0;
        $ag_contact->name='ag_contact';
        $ag_contact->value='';
        $ag_contact->set_attribute('ipopup', 'ipopcard');
        if ($this->ag_contact!=0)
        {
            $contact=new Fiche($this->db, $this->ag_contact);
            $ag_contact->value=$contact->get_quick_code();
        }
 
        $ag_contact->label="";
 
        $list_contact=$this->db->make_list('select fd_id from fiche_def where frd_id=16');
        $ag_contact->extra=$list_contact;
 
        $ag_contact->set_dblclick("fill_ipopcard(this);");
        // name of the field to update with the name of the card
        $ag_contact->set_attribute('label', 'ag_contact_label');
        // name of the field to update with the name of the card
        $ag_contact->set_attribute('typecard', $list_contact);
        $ag_contact->set_function('fill_data');
        $ag_contact->javascript=sprintf(' onchange="fill_data_onchange(\'%s\');" ', $ag_contact->name);
 
        $spcontact=new ISpan();
        $spcontact->name='ag_contact_label';
        $spcontact->value='';
        $fiche_contact=new Fiche($this->db,$this->ag_contact);
 
        if ($fiche_contact->id!=0)
        {
            $spcontact->value=strtoupper($fiche_contact->strAttribut(ATTR_DEF_NAME)??"");
            $spcontact->value.=" ".$fiche_contact->strAttribut(ATTR_DEF_FIRST_NAME,0);
        }
 
 
        $h_agrefid=new IHidden();
        $iag_ref=new IText("ag_ref");
        $iag_ref->value=$this->ag_ref;
        $iag_ref->readOnly=false;
        $str_ag_ref=$iag_ref->input();
        // Preparing the return string
        $r="";
 
        /* for new files */
        $upload=new IFile();
        $upload->name="file_upload[]";
        $upload->set_multiple(true);
        $upload->setAlertOnSize(true);
        $upload->readOnly=$readonly;
        $upload->value="";
        $aAttachedFile=$this->db->get_array('select d_id,d_filename,d_description,d_mimetype,'.
                '\'export.php?act=RAW:document&'.
                Dossier::get().'&d_id=\'||d_id as link'.
                ' from document where ag_id=$1', array($this->ag_id));
        /* create the select for document */
        $aDocMod=new ISelect();
        $aDocMod->name='doc_mod';
        $aDocMod->value=$this->db->make_array('select md_id,dt_value||\' : \'||md_name as md_name'.
                ' from document_modele join document_type on (md_type=dt_id)'.
                " where md_affect ='GES' and md_type = $1 ".
        ' order by md_name',0,[$this->dt_id]);
        $str_select_doc=$aDocMod->input();
        /* if no document then do not show the generate button */
        if (empty($aDocMod->value))
            $str_submit_generate="";
        else
            $str_submit_generate=HtmlInput::submit("generate", _("Génére le document"));
 
        $ag_id=$this->ag_id;
 
        /* fid = Icard  */
        $icard=new ICard();
        $icard->jrn=0;
        $icard->table=0;
        $icard->extra2='QuickCode';
        $icard->noadd="no";
        $icard->extra='all';
 
        /* Text desc  */
        $text=new IText();
        $num=new INum();
 
        /* Add the needed hidden values */
        $r.=dossier::hidden();
 
        $r.=HtmlInput::request_to_hidden(array("closed_action", "remind_date_end", "remind_date", "sag_ref", "only_internal", "state", "qcode", "ag_dest_query", "action_query", "tdoc", "date_start", "date_end", "hsstate", "searchtag"));
        $a_tag=$this->tag_get();
        $menu=new Default_Menu();
        /* get template */
        ob_start();
        require NOALYSS_TEMPLATE.'/follow_up-display.php';
        $content=ob_get_contents();
        ob_end_clean();
        $r.=$content;
 
        //hidden
        $r.="<p>";
        $r.=$h2->input();
        $r.=$h_ag_id->input('ag_id', $this->ag_id);
        $hidden2=new IHidden();
        $r.=$hidden2->input('f_id_dest', $this->f_id_dest);
        $r.="</p>";
 
        return $r;
    }
 
    //----------------------------------------------------------------------
    /** 
     * \brief This function shows the detail of an action thanks the ag_id
     */
    function get()
    {
        $sql="select ag_id,to_char (ag_timestamp,'DD.MM.YYYY') as ag_timestamp,".
                " f_id_dest,ag_title,ag_ref,d_id,ag_type,ag_state, ag_owner, ".
                "  ag_dest, ag_hour, ag_priority, ag_contact,to_char (ag_remind_date,'DD.MM.YYYY') as ag_remind_date ".
                " from action_gestion left join document using (ag_id) where ag_id=".$this->ag_id;
        $r=$this->db->exec_sql($sql);
        $row=Database::fetch_all($r);
        if ($row==false)
        {
            $this->ag_id=0;
            return;
        }
        $this->ag_timestamp=$row[0]['ag_timestamp'];
        $this->ag_contact=$row[0]['ag_contact'];
        $this->f_id_dest=$row[0]['f_id_dest'];
        $this->ag_title=$row[0]['ag_title'];
        $this->ag_type=$row[0]['ag_type'];
        $this->ag_ref=$row[0]['ag_ref'];
        $this->ag_state=$row[0]['ag_state'];
        $this->d_id=$row[0]['d_id'];
        $this->ag_dest=$row[0]['ag_dest'];
        $this->ag_hour=$row[0]['ag_hour'];
        $this->ag_priority=$row[0]['ag_priority'];
        $this->ag_remind_date=$row[0]['ag_remind_date'];
        $this->ag_owner=$row[0]['ag_owner'];
 
        $action_detail=new Follow_Up_Detail($this->db);
        $action_detail->set_parameter('ag_id', $this->ag_id);
        $this->aAction_detail=$action_detail->load_all();
 
 
        // if there is no document set 0 to d_id
        if ($this->d_id=="")
            $this->d_id=0;
        // if there is a document fill the object
        if ($this->d_id!=0)
        {
            $this->state=$row['0']['ag_state'];
            $this->ag_state=$row[0]['ag_state'];
        }
        $this->dt_id=$this->ag_type;
        $aexp=new Fiche($this->db, $this->f_id_dest);
        $this->qcode_dest=$aexp->strAttribut(ATTR_DEF_QUICKCODE);
    }
 
    /**
     * \brief Save the document and propose to save the generated document or
     *  to upload one, the data are included except the file. Temporary the generated
     * document is save.
     * The files into $_FILES['file_upload'] will be saved
     * @note the array $_POST['input_desc'] must be set, contains the description
     * of the uploaded files
     *
     * \return
     */
    function save()
    {
 
       
        $str_file="";
        $add_file='';
 
        // f_id exp
        $exp=new Fiche($this->db);
        $exp->get_by_qcode($this->qcode_dest);
        $exp->id=($exp->id==0)?null:$exp->id;
 
        $contact=new Fiche($this->db);
        $contact->get_by_qcode($this->ag_contact);
 
        if (trim($this->ag_title??"")=="")
        {
            $doc_mod=new document_type($this->db);
            $doc_mod->dt_id=$this->dt_id;
            $doc_mod->get();
            $this->ag_title=$doc_mod->dt_value;
        }
        $this->ag_id=$this->db->get_next_seq('action_gestion_ag_id_seq');
        $seq_name="seq_doc_type_".$this->dt_id;
        // to avoid duplicate
        do {
            // Create the reference
            $ag_ref=$this->db->get_value('select dt_prefix from document_type where dt_id=$1', array($this->dt_id)).'-'.$this->db->get_next_seq($seq_name);
            // if reference does not exist , finish the loop
            if ( $this->db->get_value("select count(*) from action_gestion where ag_ref = $1",array($ag_ref)) == 0)
            break;
        } while (1);
        // check if the reference already exist and try to compute new one
        $this->ag_ref=$ag_ref;
        // save into the database
        if ($this->ag_remind_date!=null||$this->ag_remind_date!='')
        {
            $sql="insert into action_gestion".
                    "(ag_id,ag_timestamp,ag_type,ag_title,f_id_dest,ag_ref, ag_dest, ".
                    " ag_hour, ag_priority,ag_owner,ag_contact,ag_state,ag_remind_date) ".
                    " values ($1,to_date($2,'DD.MM.YYYY'),$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,to_date($13,'DD.MM.YYYY'))";
        }
        else
        {
            $this->ag_remind_date=null;
            $sql="insert into action_gestion".
                    "(ag_id,ag_timestamp,ag_type,ag_title,f_id_dest,ag_ref, ag_dest, ".
                    " ag_hour, ag_priority,ag_owner,ag_contact,ag_state,ag_remind_date) ".
                    " values ($1,to_date($2,'DD.MM.YYYY'),$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)";
        }
        $this->db->exec_sql($sql, array($this->ag_id, /* 1 */
            $this->ag_timestamp, /* 2 */
            $this->dt_id, /* 3 */
            $this->ag_title, /* 4 */
            $exp->id, /* 5 */
            $this->ag_ref, /* 6 */
            $this->ag_dest, /* 7 */
            $this->ag_hour, /* 8 */
            $this->ag_priority, /* 9 */
            $_SESSION[SESSION_KEY.'g_user'], /* 10 */
            $contact->id, /* 11 */
            $this->ag_state, /* 12 */
            $this->ag_remind_date /* 13 */
                )
        );
        $http=new HttpInput();
        $nb_item=$http->post("nb_item","number",0);
        /* insert also the details */
        for ($i=0; $i<$nb_item; $i++)
        {
            $act=new Follow_Up_Detail($this->db);
            $act->from_array($_POST, $i);
            if ($act->f_id==0)
                continue;
            $act->ag_id=$this->ag_id;
            $act->save();
        }
 
        /* upload the documents */
        $doc=new Document($this->db);
        $doc->upload($this->ag_id);
        if (noalyss_trim($this->ag_comment??"")!='' && Document_Option::can_add_comment($this->ag_id))
        {
            $this->db->exec_sql("insert into action_gestion_comment (ag_id,tech_user,agc_comment,agc_comment_raw) values ($1,$2,$3,$4)"
                , array($this->ag_id, $_SESSION[SESSION_KEY.'g_user'], strip_tags($this->ag_description),$this->ag_comment));
        }
        if (noalyss_trim($this->ag_description)!='' && Document_Option::can_add_comment($this->ag_id))
        {
            $this->db->exec_sql("insert into action_gestion_comment (ag_id,tech_user,agc_comment,agc_comment_raw) values ($1,$2,$3,$4)"
                , array($this->ag_id, $_SESSION[SESSION_KEY.'g_user'],strip_tags($this->ag_description), $this->ag_description));
        }
        $this->insert_operation();
        $this->insert_action();
    }
    /**
     * @brief return the SQL to make a list of actions
     * Colum :
     *      -  my_date string date action format DD.MM.YYYY
     *      -  my_remind string date  reminder format DD.MM.YYYY
     *      -  str_last_comment string last colemnt format DD.MM.YYYY
     *      -  last_comment  date last comment
     *      -  f_id_dest int fiche.f_id concerned card
     *      -  s_value    string state of the action (cloturé, à suivre ,...)
     *      -  s_id int id of document_state
     *      -  ag_title   string Title of the follow-up
     *      -  dt_value   string Type of document
     *      -  ag_ref,    string ref of the action
     *      -  ag_priority  int priority of the action
     *      -  ag_state,   int state of the followup (see table : document_state )
     *      -  dest   string profil group
     *      -  name   string name of the recipient
     *      -  qcode string qcode of the recipient
     *      -  tag   string list of tags separated by  comma
     *      - tags_color string list of tag colors separated by  comma
     * @returns a SQL string to retrieve list of actions
     */
    public static function SQL_list_action():string
    {
       $sql = " select ag_id,to_char(ag_timestamp,'DD.MM.YYYY') as my_date,
                to_char(ag_remind_date,'DD.MM.YYYY') as my_remind,
                to_char(coalesce((select max(agc_date) from action_gestion_comment as agc where agc.ag_id=ag.ag_id),ag_timestamp),'DD.MM.YY') as str_last_comment,
                coalesce((select max(agc_date) from action_gestion_comment as agc where agc.ag_id=ag.ag_id),ag_timestamp) as last_comment,
                f_id_dest,
                s_id,
                s_value,
                ag_title,dt_value,ag_ref, ag_priority,ag_state,
                coalesce((select p_name from profile where p_id=ag_dest),'Aucun groupe') as dest,
                (select ad_value from fiche_Detail where f_id=ag.f_id_dest and ad_id=1) as name,
                (select ad_value from fiche_Detail where f_id=ag.f_id_dest and ad_id=23) as qcode,
                array_to_string((select array_agg(t1.t_tag) from action_tags as a1 join tags as t1 on (a1.t_id=t1.t_id) where a1.ag_id=ag.ag_id ),',') as tags,
                array_to_string((select array_agg(t1.t_color) from action_tags as a1 join tags as t1 on (a1.t_id=t1.t_id) where a1.ag_id=ag.ag_id ),',') as tags_color
            from action_gestion as ag
                join document_type on (ag_type=dt_id)
                join document_state on (ag_state=s_id)
                ";
       return $sql;
    }
    /**
     * @brief myList($p_base, $p_filter = "", $p_search = "")
     * Show list of action by default if sorted on date
     * @param $p_base base url with ac...
     * @param $p_filter filters on the document_type
     * @param $p_search must a valid sql command ( ex 'and  ag_title like upper('%hjkh%'))
     * @see Follow_Up::create_query()
     * @return string containing html code
     */
    function myList($p_base, $p_filter="", $p_search="")
    {
        // for the sort
        $arg=HtmlInput::get_to_string(array("closed_action", "remind_date_end", "remind_date", "sag_ref", "only_internal", "state", "qcode", "ag_dest_query", "action_query", "tdoc", "date_start", "date_end", "hsstate", "searchtag"),"");
        $url=$p_base.$arg;
 
        $table=new Sort_Table();
        // 0
        $table->add(_('Date Doc.'), $url, 'order by ag_timestamp asc', 'order by ag_timestamp desc', 'da', 'dd'); 
        //$table->add(_('Date Comm.'), $url, 'order by last_comment', 'order by last_comment desc', 'dca', 'dcd');
        //1
        $table->add(_('Date Limite'), $url, 'order by ag_remind_date asc nulls last', 'order by ag_remind_date  desc nulls last', 'ra', 'rd');
        //2
        $table->add(_('Réf.'), $url, 'order by ag_ref asc', 'order by ag_ref desc', 'ra', 'rd');
        //3
        $table->add(_('Etiquette'), $url, 'order by tags asc nulls last', 'order by tags desc nulls last', 'taa', 'tad');
        //4
        $table->add(_('Groupe'), $url, "order by coalesce((select p_name from profile where p_id=ag_dest),'Aucun groupe')", "order by coalesce((select p_name from profile where p_id=ag_dest),'Aucun groupe') desc", 'dea', 'ded');
        //5
        $table->add(_('Dest/Exp'), $url, 'order by qcode asc nulls last', 'order by qcode desc nulls last', 'ea', 'ed');
        //6
        $table->add(_('Titre'), $url, 'order by ag_title asc', 'order by ag_title desc', 'ta', 'td');
        //7
        $table->add(_('Etat'), $url, 'order by s_value asc', 'order by s_value desc', 'ea', 'ed');
 
        $ord=(!isset($_GET['ord']))?"dd":$_GET['ord'];
        $sort=$table->get_sql_order($ord);
 
        if (noalyss_strlentrim($p_filter)!=0)
            $p_filter_doc=" dt_id in ( $p_filter )";
        else
            $p_filter_doc=" 1=1 ";
 
        // make SQL to find the action
        $sql=Follow_Up::SQL_list_action()."                     where $p_filter_doc $p_search $sort";
 
        $max_line=$this->db->count_sql($sql);
        $step=$_SESSION[SESSION_KEY.'g_pagesize'];
        $page=(isset($_GET['offset']))?$_GET['page']:1;
        $offset=(isset($_GET['offset']))?Database::escape_string($_GET['offset']):0;
        if ($step!=-1)
            $limit=" LIMIT $step OFFSET $offset ";
        else
            $limit='';
        $bar=navigation_bar($offset, $max_line, $step, $page);
 
        $Res=$this->db->exec_sql($sql.$limit);
        $a_row=Database::fetch_all($Res);
 
        $r="";
        $r.=HtmlInput::filter_table('mylist_tb', '1,2,3,4,5,6,7,8,9',1);
        $r.='<p>'.$bar.'</p>';
        
        $r.='<table id="mylist_tb" class="document">';
        $r.="<tr>";
        $r.='<th name="ag_id_td" style="display:none" >'.ICheckBox::toggle_checkbox('ag', 'list_ag_frm').'</th>';
        $r.='<th style="width:5.57%">'.$table->get_header(0).'</th>';
        $r.='<th style="width:5.57%">'.$table->get_header(1).'</th>';
        $r.='<th style="width:5.57%">'.$table->get_header(2).'</th>';
        $r.='<th style="width:5.57%">'.$table->get_header(5).'</th>';
//        $r.='<th>'.$table->get_header(1).'</th>';
        $r.=th('Priorité','style="width:5.57%"');
        $r.='<th style="min-width:45%">'.$table->get_header(6).'</th>';
        $r.='<th style="width:5.57%">'.$table->get_header(7).'</th>';
        $r.='<th style="max-width:10%">'.$table->get_header(3).'</th>';
        $r.='<th style="width:5.57%">'.$table->get_header(4).'</th>';
        $r.="</tr>";
 
 
        // if there are no records return a message
        if ($a_row==false || sizeof($a_row)==0 )
        {
            $r='<div style="clear:both">';
            $r.='<hr>'._("Aucun enregistrement trouvé");
            $r.="</div>";
            return $r;
        }
        $today=date('d.m.Y');
        $i=0;
        $checkbox=new ICheckBox("mag_id[]");
        $checkbox->set_range("action_followup_ck");
        //show the sub_action
        foreach ($a_row as $row)
        {
            $href='<A class="document" HREF="'.$p_base.HtmlInput::get_to_string(array("closed_action", "remind_date_end", "remind_date", "sag_ref", "only_internal", "state", "gDossier", "qcode", "ag_dest_query", "action_query", "tdoc", "date_start", "date_end", "hsstate", "searchtag", "ac"), "").'&sa=detail&ag_id='.$row['ag_id'].'" title="'.$row['name'].'">';
            $i++;
            $tr=($i%2==0)?'even':'odd';
            if ($row['ag_priority']<2)
                $tr='priority1';
            $st='';
            if ($row['my_date']==$today)
                $st=' style="font-weight:bold; border:2px solid orange;"';
            $date_remind=format_date($row['my_remind'], 'DD.MM.YYYY', 'YYYYMMDD');
            $date_today=date('Ymd');
            if ($date_remind!=""&&$date_remind==$date_today&&$row['ag_state']!=1&&$row['ag_state']!=3)
                $st=' style="font-weight:bold;background:orange"';
            if ($date_remind!=""&&$date_remind<$date_today&&$row['ag_state']!=1&&$row['ag_state']!=3)
                $st=' style="font-weight:bold;background:#FF0000;color:white;"';
            $r.="<tr class=\"$tr\" $st>";
            $checkbox->value=$row['ag_id'];
            $r.='<td name="ag_id_td" style="display:none">'.$checkbox->input().'</td>';
            $r.="<td>".$href.smaller_date($row['my_date']).'</a>'."</td>";
            //$r.="<td>".$href.$row['str_last_comment'].'</a>'."</td>";
            $r.="<td>".$href.smaller_date($row['my_remind']).'</a>'."</td>";
            $r.="<td>".$href.$row['ag_ref'].'</a>'."</td>";
            // Expediteur
            if ($row['qcode']!='')
            {
                $jsexp=sprintf("javascript:showfiche('%s')", $row['qcode']);
                $r.="<td>$href".$row['qcode'].'</a></td>';
            }
            else
                $r.="<td></td>";
 
             /*
             * State
             */
            switch ($row['ag_priority'])
            {
                case 1:
                    $priority=_('Haute');
                    break;
                case 2:
                    $priority=_("Normale");
                    break;
                case 3:
                    $priority=_("Basse");
                    break;
            }
            $r.=td($priority);
 
             $r.='<td>'.$href.
                    h($row['ag_title'])."</A></td>";
            $r.="<td>".$row['s_value']."</td>";
            $r.="<td>";
            if ($row['tags']!=""){
                $r.=$href;
                $aColor=explode(",", $row["tags_color"]);
                $aTags=explode(",", $row["tags"]);
                $nb_tag=count($aTags);
                for ( $x=0;$x<$nb_tag;$x++) {
                   $r.=sprintf('<span style="font-size:75%%;padding:1px;border-color:transparent" class="tagcell tagcell-color%s">%s</span>',$aColor[$x],$aTags[$x]);
                   $r.="&nbsp;";
                } // end loop $x
                $r.='</a>';
            }
            $r.="</td>";
            $r.="<td>".$href.h($row['dest']).'</a>'."</td>";
 
            
 
 
 
           
 
 
            $r.="</tr>";
        }
 
        $r.="</table>";
 
        $r.='<p>'.$bar.'</p>';
        $r.=ICheckBox::javascript_set_range("action_followup_ck");
        return $r;
    }
 
    /**
     * @brief display list of followup , used with card
     * @param $query string SQL query
     * @param $extra_sql string extra to add to the query , example "limit 20"
     * @return void
     */
    function view_list($query,$extra_sql="")
    {
        $sql=Follow_Up::SQL_list_action();
        $sql = " $sql $query order by ag_timestamp desc  $extra_sql";
        require_once NOALYSS_TEMPLATE."/follow_up-view_list.php";
    }
    //----------------------------------------------------------------------
    /**
     * \brief Update the data into the database, the field ag_description could contain some HTML tags and must be
     * saved in 2 column : one without formatting , one with.
     * @code
    [p_jrn] =>
    [sa] =>
    [gDossier] => 25
    [p_jrn_deb_max_line] => 10
    [p_ech_lib] => echeance
    [p_jrn_type] => FIN
    [p_jrn_name] => Financier
    [bank] => COMPTE
    [min_row] => 5
    [p_description] => Concerne tous les mouvements financiers (comptes en banque, caisses, visa...)
    [jrn_def_pj_pref] => FIN
    [jrn_def_pj_seq] => 0
    [jrn_enable] => 1
    [FIN_FICHEDEB] => (fd_id  )
    [defaultCurrency]
     *
    [action_frm] => update
     @endcode
     *
     * @note [defaultCurrency] => 0 exists in the array if the bank account has no operation and the currency can
     * be set
     *
     * \return true on success otherwise false
     */
    function update()
    {
 
        // if ag_id == 0 nothing to do
        if ($this->ag_id==0)
            return;
        // retrieve customer
        // f_id
 
        if (trim($this->qcode_dest??"")=="")
        {
            // internal document
            $this->f_id_dest=null; // internal document
        }
        else
        {
            $tiers=new Fiche($this->db);
            if ($tiers->get_by_qcode($this->qcode_dest)== 1) // Error we cannot retrieve this qcode
                $this->f_id_dest=null; // internal document
            else
                $this->f_id_dest=$tiers->id;
        }
        $contact=new Fiche($this->db);
        if ($contact->get_by_qcode($this->ag_contact)== 1)
            $contact->id=null;
 
        // reload the old one
        $old=new Follow_Up($this->db);
        $old->ag_id=$this->ag_id;
        $old->get();
        // If ag_ref changed then check if unique
        if ($old->ag_ref!=$this->ag_ref)
        {
            $nAg_ref=$this->db->get_value("select count(*) from action_gestion where ag_ref=$1", array($this->ag_ref));
            if ($nAg_ref!=0)
            {
                echo h2("Référence en double, référence non sauvée", 'class="error"');
                $this->ag_ref=$old->ag_ref;
            }
        }
 
 
        if ($this->ag_remind_date!=null)
        {
            $this->db->exec_sql("update action_gestion set ".
                    " ag_timestamp=to_date($1,'DD.MM.YYYY'),".
                    " ag_title=$2,".
                    " ag_type=$3, ".
                    " f_id_dest=$4, ".
                    "ag_state=$5,".
                    " ag_hour = $7 ,".
                    " ag_priority = $8 ,".
                    " ag_dest = $9 , ".
                    " ag_contact = $10, ".
                    " ag_ref = $11, ".
                    " ag_remind_date=to_date($12,'DD.MM.YYYY') ".
                    " where ag_id = $6", array(
                $this->ag_timestamp, /* 1 */
                $this->ag_title, /* 2 */
                $this->dt_id, /* 3 */
                $this->f_id_dest, /* 4 */
                $this->ag_state, /* 5 */
                $this->ag_id, /* 6 */
                $this->ag_hour, /* 7 */
                $this->ag_priority, /* 8 */
                $this->ag_dest, /* 9 */
                $contact->id, /* 10 */
                $this->ag_ref, /* 11 */
                $this->ag_remind_date /* 12 */
            ));
        }
        else
        {
            $this->db->exec_sql("update action_gestion set ".
                    " ag_timestamp=to_date($1,'DD.MM.YYYY'),".
                    " ag_title=$2,".
                    " ag_type=$3, ".
                    " f_id_dest=$4, ".
                    "ag_state=$5,".
                    " ag_hour = $7 ,".
                    " ag_priority = $8 ,".
                    " ag_dest = $9 , ".
                    " ag_contact = $10, ".
                    " ag_ref = $11, ".
                    " ag_remind_date=null ".
                    " where ag_id = $6", array(
                $this->ag_timestamp, /* 1 */
                $this->ag_title, /* 2 */
                $this->dt_id, /* 3 */
                $this->f_id_dest, /* 4 */
                $this->ag_state, /* 5 */
                $this->ag_id, /* 6 */
                $this->ag_hour, /* 7 */
                $this->ag_priority, /* 8 */
                $this->ag_dest, /* 9 */
                $contact->id, /* 10 */
                $this->ag_ref /* 11 */
            ));
        }
        // upload  documents
        $doc=new Document($this->db);
        $document_saved=$doc->upload($this->ag_id);
 
        /* save action details */
        $http=new HttpInput();
        $nb_item=$http->post("nb_item","number",0);        
        for ($i=0; $i< $nb_item ; $i++)
        {
            $act=new Follow_Up_Detail($this->db);
            $act->from_array($_POST, $i);
            if ($act->f_id==0&&$act->ad_id!=0)
                $act->delete();
            if ($act->f_id==0)
                continue;
            $act->save();
        }
        if (trim(strip_tags($this->ag_comment??"")) !='')
        {
            $notag_comment=strip_tags($this->ag_comment);
            $action_comment_id=$this->db->get_value("insert into action_gestion_comment (ag_id,tech_user,agc_comment,agc_comment_raw) values ($1,$2,$3,$4) returning agc_id"
                    , array($this->ag_id, $_SESSION[SESSION_KEY.'g_user'], $notag_comment,$this->ag_comment));
            // saved also documents for this comment
            if ( ! empty ($document_saved) && Document_Option::is_enable_comment($this->dt_id)) {
                foreach ($document_saved as $document_id) {
                    $this->db->exec_sql("insert into action_comment_document(document_id,action_gestion_comment_id) values ($1,$2)",
                    [$document_id,$action_comment_id]);
                }
            }
        }
        if (trim(strip_tags($this->ag_description??""))!='' )
        {
            if (  $this->ag_description_id <0)
                $this->ag_description_id =  $this->db->get_value("
                    insert into action_gestion_comment (ag_id,tech_user,agc_comment,agc_comment_raw) 
                    values ($1,$2,$3,$4)
                    returning  agc_id"
                    , array($this->ag_id, $_SESSION[SESSION_KEY.'g_user'],strip_tags($this->ag_description), $this->ag_description));
            else
                $this->db->exec_sql("
                    update action_gestion_comment 
                            set agc_comment = $1 , 
                            agc_comment_raw = $2,
                            tech_user= $3
                                where agc_id = $4 "
                    , array(strip_tags($this->ag_description), $this->ag_description, $_SESSION[SESSION_KEY.'g_user'],
                        $this->ag_description_id));
 
            if ( ! empty ($document_saved)) {
                foreach ($document_saved as $document_id) {
                    $this->db->exec_sql("insert into action_comment_document(document_id,action_gestion_comment_id) values ($1,$2)",
                        [$document_id,$this->ag_description_id]);
                }
            }
        }
        $this->insert_operation();
        $this->insert_action();
        return true;
    }
 
    /**
     * \brief generate the document and add it to the action
     * \param md_id is the id of the document_modele
     * \param $p_array contains normally the $_POST
     */
    function generate_document($md_id, $p_array)
    {
        $doc=new Document($this->db);
        $mod=new Document_Modele($this->db, $md_id);
        $mod->load();
        $doc->f_id=$this->f_id_dest;
        $doc->md_id=$md_id;
        $doc->ag_id=$this->ag_id;
        $doc->Generate($p_array,$this->ag_id);
    }
 
    /**
     * \brief put an array in the variable member, the indice
     * is the member name
     * \param $p_array to parse
     *      - ag_id id of the Follow_up
     *      - ag_ref reference of the action
     *      - qcode_dest quick_code of the card of dest
     *      - f_id_dest f_id of the card of dest
     *      - dt_id Document_Modele::dt_id
     *      - ag_state document_state::s_id (default:2)
     *      - ag_title title of the action
     *      - ag_hour
     *      - ag_dest Profile, profile of the user
     *      - ag_comment comment
     *      - ag_remind_date Remind Date
     *      - operation related operation
     *      - action related action 
     *      - op deprecated
     * \return nothing
     */
    function fromArray($p_array)
    {
        global $g_user;
        $http=new HttpInput();
        $http->set_array($p_array);
        $this->ag_id=$http->extract("ag_id","number",0);
        $this->ag_ref=$http->extract("ag_ref","string","");
        $this->qcode_dest=$http->extract("qcode_dest","string","");
        $this->f_id_dest=$http->extract("f_id_dest","string",null);
        $this->ag_timestamp=$http->extract("ag_timestamp","string",date('d.m.Y'));
        $this->ag_timestamp=(isDate($this->ag_timestamp)==0)?date('d.m.Y'):$this->ag_timestamp;
        $this->dt_id=$http->extract("dt_id","string","");
        $this->ag_state=$http->extract("ag_state","number",2);
        $this->ag_title=$http->extract("ag_title","string","");
        $this->ag_hour=$http->extract("ag_hour","string","");
        $this->ag_dest=$http->extract("ag_dest","string",$g_user->get_profile());
        $this->ag_priority=$http->extract("ag_priority","string","2");
        $this->ag_contact=$http->extract("ag_contact","string","");
        $ag_comment=trim($http->extract("ag_comment","raw",""));
        if ( strip_tags($ag_comment) == '')
            $this->ag_comment='';
        else
            $this->ag_comment=$ag_comment;
            $ag_description=trim($http->extract("ag_description","raw",""));
        if ( strip_tags($ag_description) == '')
            $this->ag_description='';
        else
            $this->ag_description=$ag_description;
        $this->ag_description_id=$http->extract("ag_description_id","string",-1);
        $this->ag_remind_date=$http->extract("ag_remind_date","string",null);
        $this->operation=$http->extract("operation","string",null);
        $this->action=$http->extract("action","string",null);
    }
 
    /**
     * \brief remove the action
     *
     */
    function remove()
    {
        $this->get();
        // remove the key
        $sql="delete from action_gestion where ag_id=$1";
        $this->db->exec_sql($sql, array($this->ag_id));
 
        /*  check the number of attached document */
        $doc=new Document($this->db);
        $aDoc=$doc->get_all($this->ag_id);
        if (!empty($aDoc))
        {
            // if there are documents
            for ($i=0; $i<sizeof($aDoc); $i++)
            {
                $aDoc[$i]->remove();
            }
        }
    }
 
    /**
     * \brief return the last p_limit operation into an array, there is a security
     * on user
     * \param $p_limit is the max of operation to return
     * \return $p_array of Follow_Up object
     */
    function get_last($p_limit)
    {
        
        $sql="select coalesce(vw_name,'Interne') as vw_name,ag_hour,quick_code,ag_id,ag_title,ag_ref, dt_value,to_char(ag_timestamp,'DD.MM.YYYY') as ag_timestamp_fmt,ag_timestamp ".
                " from action_gestion join document_type ".
                " on (ag_type=dt_id) "
                . "left join vw_fiche_attr on (f_id=f_id_dest) "
                . "where ag_state in (2,3) "
                . "and ".self::sql_security_filter($this->db,'R').
                        "order by ag_timestamp desc limit $p_limit";
        $array=$this->db->get_array($sql);
        return $array;
    }
 
    /**
     * @brief get the action where the remind day is today
     * @return array
     */
    function get_today()
    {
        $sql="select ag_ref,ag_hour,coalesce(vw_name,'Interne') as vw_name,ag_id,ag_title,ag_ref, dt_value,to_char(ag_remind_date,'DD.MM.YYYY') as ag_timestamp_fmt,ag_timestamp ".
                " from action_gestion join document_type ".
                " on (ag_type=dt_id) 
                  left join vw_fiche_attr on (f_id=f_id_dest) 
                  where 
                  ag_state not in (1,4)
                  and to_char(ag_remind_date,'DDMMYYYY')=to_char(now(),'DDMMYYYY')
                  and ". self::sql_security_filter($this->db,'R');
        $array=$this->db->get_array($sql);
        return $array;
    }
 
    /**
     * @brief get the action where the remind day is today
     * @return array
     */
    function get_late()
    {
        $sql="select ag_ref,ag_hour,coalesce(vw_name,'Interne') as vw_name,ag_id,ag_title,ag_ref, dt_value,to_char(ag_remind_date,'DD.MM.YYYY') as ag_timestamp_fmt,ag_timestamp ".
                " from action_gestion join document_type ".
                " on (ag_type=dt_id) left join vw_fiche_attr on (f_id=f_id_dest) where ag_state not in  (1,4)
                                and to_char(ag_remind_date,'YYMMDD') < to_char(now(),'YYMMDD') and ".self::sql_security_filter($this->db,'R');
        $array=$this->db->get_array($sql);
        return $array;
    }
 
    /**
     * @brief insert a related operation
     */
    function insert_operation()
    {
        if (trim($this->operation??"")=='')
            return;
        $array=explode(",", $this->operation);
        for ($i=0; $i<count($array); $i++)
        {
            if ($this->db->get_value("select count(*) from action_gestion_operation
                                where ag_id=$1 and jr_id=$2", array($this->ag_id, $array[$i]))==0)
            {
                $this->db->exec_sql("insert into action_gestion_operation (ag_id,jr_id) values ($1,$2)", array($this->ag_id, $array[$i]));
            }
        }
    }
 
    /**
     * @brief remove a related operation
     * @deprecated not used : dead_code
     * @todo to remove
     */
    function remove_operation_deprecated()
    {
        if ($this->op==null)
            return;
        $op=$this->op;
        for ($i=0; $i<count($op); $i++)
        {
            $this->db->exec_sql("delete from action_gestion_operation where ago_id=$1", array($op[$i]));
        }
    }
 
    /**
     * @brief Display only a search box for searching an action
     * @param $cn database connx
     * @param $inner true if coming from an ajax (ajax_search_action)
     */
    static function display_search($cn, $inner=false)
    {
        global $g_user;
        $http=new HttpInput();
        $a=$http->get("action_query","string","");
        $qcode=$http->get("qcode","string","");
        
        $supl_hidden=HtmlInput::array_to_hidden(['sc','sb','ac'], $_REQUEST);
        
        if (isset($_REQUEST['f_id']))
        {
            $f_id=$http->request('f_id','number');
            $supl_hidden.=HtmlInput::hidden('f_id', $f_id);
            $f=new Fiche($cn, $f_id);
            $supl_hidden.=HtmlInput::hidden('qcode_dest', $f->get_quick_code());
        }
 
        /**
         * Show the default button (add action, show search...)
         */
        if (!$inner)
            require_once NOALYSS_TEMPLATE.'/action_button.php';
 
        $w=new ICard();
        if ( $inner ) $w->autocomplete=0;
        $w->name='qcode';
        $w->id=$w->generate_id($w->name);
        $w->value=$qcode;
        $w->extra="all";
        $w->typecard='all';
        $w->jrn=0;
        $w->table=0;
        $list=$cn->make_list("select fd_id from fiche_def where frd_id in (4,8,9,14,15,16,25)");
        $w->extra=$list;
 
 
        /* type of documents */
        $type_doc=new ISelect('tdoc');
        $aTDoc=$cn->make_array('select dt_id,dt_value from document_type order by dt_value');
        $aTDoc[]=array('value'=>'-1', 'label'=>_('Tous les types'));
        $type_doc->value=$aTDoc;
        $type_doc->selected=(isset($_GET['tdoc']))?$_GET['tdoc']:-1;
 
        /* State of documents */
        $type_state=new ISelect('state');
        $aState=$cn->make_array('select s_id,s_value from document_state order by s_value');
        $aState[]=array('value'=>'-1', 'label'=>_('Tous les actions ouvertes'));
        $type_state->value=$aState;
        $type_state->selected=(isset($_GET['state']))?$_GET['state']:-1;
 
 
 
        /* Except State of documents */
        $hsExcptype_state=new ISelect('hsstate');
        $aExcpState=$cn->make_array('select s_id,s_value from document_state order by s_value');
        $aExcpState[]=array('value'=>'-1', 'label'=>_('Aucun'));
        $hsExcptype_state->value=$aExcpState;
        $hsExcptype_state->selected=(isset($_GET['hsstate']))?$_GET['hsstate']:-1;
 
 
        // date
        $start=new IDate('date_start');
        $start->value=(isset($_GET['date_start']))?$_GET['date_start']:"";
        $end=new IDate('date_end');
        $end->value=(isset($_GET['date_end']))?$_GET['date_end']:"";
 
        // Closed action
        $closed_action=new ICheckBox('closed_action');
        $closed_action->selected=(isset($_GET['closed_action']))?true:false;
 
        // Internal
        $only_internal=new ICheckBox('only_internal');
        $only_internal->selected=(isset($_GET['only_internal']))?true:false;
        // select profile
        $aAg_dest=$cn->make_array("select  p_id as value, ".
                "p_name as label ".
                " from profile where p_id in ".
                $g_user->sql_readable_profile().
                "order by 2");
        $aAg_dest[]=array('value'=>'-2', 'label'=>_('Tous les profils'));
        $ag_dest=new ISelect();
        $ag_dest->name="ag_dest_query";
        $ag_dest->value=$aAg_dest;
        $ag_dest->selected=$http->get("ag_dest_query","number",-2);
        $str_ag_dest=$ag_dest->input();
        $osag_ref=new IText("sag_ref");
        $osag_ref->value=$http->get('sag_ref',"string","");
        $remind_date=new IDate('remind_date');
        $remind_date->value=$http->get('remind_date',"string","");
        $remind_date_end=new IDate('remind_date_end');
        $remind_date_end->value=$http->get('remind_date_end',"string","");
        $otag=new Tag($cn);
 
        // show the  action in
        require_once NOALYSS_TEMPLATE.'/action_search.php';
    }
 
    /**
     * @brief display a form with the saved search
     * @return voidµ
     */
    public static function display_saved_search()
    {
        $http=new HttpInput();
        echo \HtmlInput::button_action("Recherches sauvées",sprintf("list_filter_followup('%s','%s')",Dossier::id(),$http->request("ac")));
 
    }
    /**
     * @brief show a list of documents
     * @param $cn database connextion
     * @param $p_base base URL
     */
    static function show_action_list($cn, $p_base)
    {
 
        Follow_Up::display_search($cn);
        Follow_Up::display_saved_search();
 
        $act=new Follow_Up($cn);
        /** \brief
         *  \note The field 'recherche' is   about a part of the title or a ref. number
         */
        $query=Follow_Up::create_query($cn);
 
        echo '<form method="POST" id="list_ag_frm" style="display:inline">';
        echo HtmlInput::request_to_hidden(array("gDossier", "ac", "sb", "sc", "f_id"));
        require_once NOALYSS_TEMPLATE.'/action_other_action.php';
        echo $act->myList($p_base, "", $query);
        echo '</form>';
 
    }
    /**
     * @brief Show a button for adding follow-up action, display the FORM 
     * @param array $pa_param , will be converted in a HIDDEN input type in the form
     */
    static function show_action_add($pa_param)
    {
        require_once NOALYSS_TEMPLATE.'/followup-show-action-add.php';
 
    }
 
    /**
     *@brief Create a subquery to filter thanks the selected tag
     * @param  $cn db connx
     * @param $p_array
     * @return SQL 
     */
    static function filter_by_tag($cn, $p_array=null)
    {
        if ($p_array==null)
            $p_array=$_GET;
 
        if (count($p_array['searchtag'])==0)
            return "";
        $query="";
        $operand = "1 = 0 ";
        if ($p_array['tag_option'] == 0 )
        {
            $operand=" and ";
        } elseif ($p_array['tag_option']==1)
        {
            $operand=" or ";
        }
        $and=" ";
        for ($i=0; $i<count($p_array['searchtag']); $i++)
        {
            if (isNumber($p_array['searchtag'][$i])==1) {
                $query .= $and .' ag_id in (select ag_id from action_tags where t_id= '.sql_string($p_array['searchtag'][$i]).')';
                $and = $operand;
            }
        }
        return "and (".$query.")";
    }
 
    /**
     * @brief Get date from $_GET and create the sql stmt for the query
     * @note the query is taken from $_GET
     * @see {Follow_Up::ShowActionList}
     * @return string SQL condition
     */
    static function create_query($cn, $p_array=null)
    {
        if ($p_array==null)             $p_array=$_GET;
        $http=new HttpInput();
        $http->set_array($p_array);
        $search_docid=0; // search for a document 
        $action_query="";
        $ag_state=""; //<! selected status of the event , if not set or equal to -1 , it is all of them
        //
        // search for a specific document id (ag_id) , if given then status and date doesn't count
         if (isset ($p_array['ag_id']) && isNumber($p_array['ag_id'])==1&&$p_array['ag_id']!=0)
        {
            $action_query=" and ag_id= ".sql_string($p_array['ag_id']);
            $search_docid=$p_array['ag_id']; 
            return $action_query;
        }
        if (isset($_REQUEST['action_query']) && trim($_REQUEST['action_query']??"") != "")
        {
            $action_query = $http->extract('action_query');
            // if a query is request build the sql stmt
            $action_query="and (ag_title ilike '%".sql_string($action_query)."%' ".
                    "or ag_ref ='".trim(sql_string($action_query??"")).
                    "' or ag_id in (select ag_id from action_gestion_comment ".
                    " where agc_comment ilike '%".trim(sql_string($action_query??""))."%')".
                    ")";
        }
 
        $str="";
        if (isset($p_array['qcode']))
        {
            // verify that qcode is not empty
            if (noalyss_strlentrim($p_array['qcode'])!=0)
            {
 
                $fiche=new Fiche($cn);
                $fiche->get_by_qcode($http->extract('qcode'));
                // if quick code not found then nothing
                if ($fiche->id==0)
                    $str=' and false ';
                else
                    $str=" and (f_id_dest= ".$fiche->id." or ag_id in (select ag_id from action_person as ap where ap.f_id=".$fiche->id.") or ag_contact=".$fiche->id."  )";
            }
        }
        if (isset($p_array['tdoc'])&&$p_array['tdoc'] !=-1)
        {
            $action_query .= ' and dt_id = '.sql_string($p_array['tdoc']);
        }
        if (isset($p_array['state'])&&$p_array['state'] !=-1)
        {
            $action_query .= ' and ag_state= '.sql_string($p_array['state']);
            // a status is selected
            $ag_state=$p_array['state'];
        }
        if (isset($p_array['hsstate'])&&$p_array['hsstate']!=-1)
        {
            $action_query .= ' and ag_state <> '.sql_string($p_array['hsstate']);
        }
        if (isset($p_array['sag_ref'])&&trim($p_array['sag_ref']??"")!="")
        {
            $action_query .= " and ag_ref= '".sql_string($p_array['sag_ref'])."'";
        }
 
        if (isset($_GET['only_internal']))
            $action_query .= ' and f_id_dest=0 ';
 
        if (isset($p_array['date_start'])&&isDate($p_array['date_start'])!=null)
        {
            $action_query.=" and ag_timestamp >= to_date('".$p_array['date_start']."','DD.MM.YYYY')";
        }
        if (isset($p_array['date_end'])&&isDate($p_array['date_end'])!=null)
        {
            $action_query.=" and ag_timestamp <= to_date('".$p_array['date_end']."','DD.MM.YYYY')";
        }
        if (isset($p_array['ag_dest_query'])&&$p_array['ag_dest_query']!=-2)
        {
            $action_query.= " and ((ag_dest = ".sql_string($p_array['ag_dest_query'])." and ".self::sql_security_filter($cn, "R").") or ".
                    "(ag_dest = ".sql_string($p_array['ag_dest_query'])." and ".self::sql_security_filter($cn, "R")." and ".
                    " ag_owner='".$_SESSION[SESSION_KEY.'g_user']."'))";
        }
        else
        {
            $action_query .=" and (ag_owner='".$_SESSION[SESSION_KEY.'g_user']."' or ".self::sql_security_filter($cn, "R")." or ag_dest=-1  )";
        }
 
 
        if (isset ($p_array['ag_id']) && isNumber($p_array['ag_id'])==1&&$p_array['ag_id']!=0)
        {
            $action_query .= " and to_date('".sql_string($p_array['$remind_date'])."','DD.MM.YYYY')<= ag_remind_date";
        }
        if (isset($p_array['remind_date_end'])&&$p_array['remind_date_end']!=""&&isDate($p_array['$remind_date_end'])==$p_array['remind_date_end'])
        {
            $action_query .= " and to_date('".sql_string($p_array['remind_date_end'])."','DD.MM.YYYY')>= ag_remind_date";
        }
        // only for open action or a closing status is selected
        if (!isset($p_array['closed_action']) && $ag_state == "")
        {
            $action_query.=" and s_status is null ";
        }
        if (isset($p_array['searchtag']))
        {
            $action_query .= Follow_Up::filter_by_tag($cn, $p_array);
        }
        if ( DEBUGNOALYSS > 1) {
            print __FILE__.__LINE__."QUERY = [ $action_query.$str]";
        }
        return $action_query.$str;
    }
 
    /**
     * @brief Show the result of a search in an inner windows, the result is limited to 25
     * @param Database $cn database connx
     * @param string $p_sql where clause of the query
     */
    static function short_list($cn, $p_sql)
    {
        $sql="
             select ag_id,to_char(ag_timestamp,'DD.MM.YY') as my_date,
                         f_id_dest,
             substr(ag_title,1,40) as sub_ag_title,dt_value,ag_ref, ag_priority,ag_state,
                        coalesce((select p_name from profile where p_id=ag_dest),'Aucun groupe') as dest,
                                (select ad_value from fiche_Detail where f_id=action_gestion.f_id_dest and ad_id=1) as name
             from action_gestion
             join document_type on (ag_type=dt_id)
                         join document_state on (s_id=ag_state)
             where $p_sql";
        $max_line=$cn->count_sql($sql);
 
        $limit=($max_line>25)?25:$max_line;
        $Res=$cn->exec_sql($sql."limit ".$limit);
        $a_row=Database::fetch_all($Res);
        require_once NOALYSS_TEMPLATE.'/action_search_result.php';
    }
 
    /**
     * @brief Insert a related action into the table action_gestion_related
     */
    function insert_action()
    {
        if (trim($this->action??"")=='')
            return;
        $array=explode(",", $this->action);
        for ($i=0; $i<count($array); $i++)
        {
            // Do not insert an option child of himself
            if ( $this->ag_id == $array[$i]) {
                continue;
            }
           
            if ($this->db->get_value("select count(*) from action_gestion_related
                                where (aga_least=$1 and aga_greatest=$2) or (aga_greatest=$1 and aga_least=$2)", array($array[$i], $this->ag_id))==0)
            {
                $this->db->exec_sql("insert into action_gestion_related(aga_least,aga_greatest) values ($1,$2)", array($this->ag_id, $array[$i]));
            }
        }
    }
    /**
     * @brief export to CSV the query the p_array has
     * @param array $p_array
      @see create_query
     */
    function export_csv($p_array)
    {
 
        
        $p_search=self::create_query($this->db, $p_array);
        $sql="
             select ag_id,
                        to_char(ag_timestamp,'DD.MM.YYYY') as my_date,
                         to_char(ag_remind_date,'DD.MM.YYYY') as my_remind,
                         to_char(coalesce((select max(agc_date) from action_gestion_comment as agc where agc.ag_id=ag_id),ag_timestamp),'DD.MM.YY') as last_comment,
                        array_to_string((select array_agg(t1.t_tag) from action_tags as a1 join tags as t1 on (a1.t_id=t1.t_id) where a1.ag_id=ag.ag_id ),',') as tags,
                                (select ad_value from fiche_Detail where f_id=ag.f_id_dest and ad_id=1) as name,
             ag_title,
                        dt_value,
                        ag_ref,
                        ag_priority,
                        ag_state,
                         
                        coalesce((select p_name from profile where p_id=ag_dest),'Aucun groupe') as dest
             from action_gestion as ag
             join document_type on (ag.ag_type=dt_id)
                         join document_state on(ag.ag_state=s_id)
             where  true  $p_search order by ag.ag_timestamp,ag.ag_id";
        $ret=$this->db->exec_sql($sql);
 
        if (Database::num_row($ret)==0)
            return;
        $this->db->query_to_csv($ret, array(
            array("title"=>"doc id", "type"=>"string"),
            array("title"=>"date", "type"=>"date"),
            array("title"=>"rappel", "type"=>"date"),
            array("title"=>"date dernier commentaire", "type"=>"date"),
            array("title"=>"tags", "type"=>"string"),
            array("title"=>"nom", "type"=>"string"),
            array("title"=>"titre", "type"=>"string"),
            array("title"=>"type document", "type"=>"string"),
            array("title"=>"ref", "type"=>"string"),
            array("title"=>"priorite", "type"=>"string"),
            array("title"=>"etat", "type"=>"string"),
            array("title"=>"profil", "type"=>"string")
                )
        );
    }
    /**
     * @brief export to CSV the detailled actions (tags, comment, related operations...), 
     * it could be a query in $p_array      *  task #0002035
     * @see create_query
     * @param array $p_array
     */
    function export_csv_detail($p_array)
    {
 
        
        $p_search=self::create_query($this->db, $p_array);
        $sql="
select ag_id,
        to_char(ag_timestamp,'DD.MM.YYYY') as my_date,
        to_char(ag_remind_date,'DD.MM.YYYY') as my_remind,
        to_char(coalesce((select max(agc_date) 
                            from action_gestion_comment as agc 
                            where agc.ag_id=ag.ag_id),ag.ag_timestamp),'DD.MM.YY HH24:MI') as last_comment,
        array_to_string((select array_agg(t1.t_tag) 
                            from action_tags as a1 join tags as t1 on (a1.t_id=t1.t_id) 
                            where a1.ag_id=ag.ag_id ),',') as tags,
        array_to_string((select array_agg(coalesce(jrn1.jr_pj_number,jrn1.jr_internal)) 
                            from action_gestion_operation ago join jrn jrn1 using (jr_id) 
                            where ago.ag_id=ag.ag_id),',') as related_operation,
        array_to_string((select array_agg(agc2.agc_comment order by agc2.agc_date desc) 
                        from  action_gestion_comment agc2 where agc2.ag_id=ag.ag_id ),'|') as action_comment,
        array_to_string((select  array_agg(followup_id) from (select agr1.aga_least followup_id
                            from action_gestion_related agr1
                            where agr1.aga_greatest = ag.ag_id 
                            union 
                            select agr2.aga_greatest 
                            from 
                            action_gestion_related agr2
                            where  agr2.aga_least=ag.ag_id) as followup),',') follow_up,
                                (select ad_value from fiche_Detail where f_id=ag.f_id_dest and ad_id=1) as name,
        ag_title,
        dt_value,
        ag_ref,
        ag_priority,
        ag_state,
        coalesce((select p_name from profile where p_id=ag_dest),'Aucun groupe') as dest
from action_gestion as ag
join document_type on (ag.ag_type=dt_id)
join document_state on(ag.ag_state=s_id)
where  
    true  $p_search order by ag.ag_timestamp,ag.ag_id";
        $ret=$this->db->exec_sql($sql);
 
        $nb_record=Database::num_row($ret);
        $export_csv=new Noalyss_Csv('action_gestion_detail');
        $export_csv->send_header();
        $export_csv->write_header([
            _("Document id"),
            _("Date"),
            _("Date rappel"),
            _("Destinataire"),
            _("Référence "),
            _("Titre "),
            _("Commentaires "),
            _("Date dernier commentaire "),
            _("Etiquette "),
            _("Priorité "),
            _("Etat "),
            _("Opérations"),
            _("Autres actions"),
            _("Type action"),
            _("Groupe "),
        ]);
        for ($i=0;$i<$nb_record;$i++)
        {
            $row=Database::fetch_array($ret,$i);
            $export_csv->add($row['ag_id']);
            $export_csv->add($row['my_date']);
            $export_csv->add($row['my_remind']);
            $export_csv->add($row['name']);
            $export_csv->add($row['ag_ref']);
            $export_csv->add($row['ag_title']);
            $export_csv->add($row['action_comment']);
            $export_csv->add($row['last_comment']);
            $export_csv->add($row['tags']);
            $export_csv->add($row['ag_priority']);
            $export_csv->add($row['ag_state']);
            $export_csv->add($row['related_operation']);
            $export_csv->add($row['follow_up']);
            $export_csv->add($row['dt_value']);
            $export_csv->add($row['dest']);
            
            $export_csv->write();
        }
        if ($nb_record==0)
            return;
    }
 
    static function get_all_operation($p_jr_id)
    {
        global $cn;
        $array=$cn->get_array("
                        select ag_id,ag_ref,ago_id,
                                ag_title
                                from action_gestion
                                join action_gestion_operation using(ag_id)
                                where
                                jr_id=$1", array($p_jr_id));
        return $array;
    }
 
    /**
     * @brief get the tags of the current objet
     * @return an array idx [ag_id,t_id,at_id,t_tag]
     */
    function tag_get()
    {
        if ($this->ag_id==0)
            return;
        $sql='select b.ag_id,b.t_id,b.at_id,a.t_tag,a.t_color'
                .' from '
                .' tags as a join action_tags as b on (a.t_id=b.t_id)'
                .' where ag_id=$1 '
                .' order by a.t_tag';
        $array=$this->db->get_array($sql, array($this->ag_id));
        return $array;
    }
 
    /**
     * @brief show the tags of the current objet
     * normally used by ajax. The same tag cannot be added twice
     * 
     */
    function tag_add($p_t_id)
    {
        if ($this->ag_id==0)
            return;
        $count=$this->db->get_value('select count(*) from action_tags'.
                ' where ag_id=$1 and t_id=$2', array($this->ag_id, $p_t_id));
        if ($count>0)
            return;
        $sql=' insert into action_tags (ag_id,t_id) values ($1,$2)';
        $this->db->exec_sql($sql, array($this->ag_id, $p_t_id));
    }
 
    /**
     * @brief remove the tags of the current objet
     * normally used by ajax
     */
    function tag_remove($p_t_id)
    {
        if ($this->ag_id==0)
            return;
        $sql=' delete from action_tags where ag_id=$1 and t_id=$2';
        $this->db->exec_sql($sql, array($this->ag_id, $p_t_id));
    }
 
    /**
     * @brief show the cell content in Display for the tags
     * called also by ajax
     */
    function tag_cell($p_view='UPD')
    {
        global $g_user;
        $a_tag=$this->tag_get();
        $c=count($a_tag);
        for ($e=0; $e<$c; $e++)
        {
            echo '<span class="tagcell tagcell-color'.$a_tag[$e]['t_color'].'">';
            echo $a_tag[$e]['t_tag'];
            if ($g_user->can_write_action($this->ag_id)==true && $p_view != 'READ')
            {
                $js_remove=sprintf("action_tag_remove('%s','%s','%s')", dossier::id(), $this->ag_id, $a_tag[$e]['t_id']);
                echo Icon_Action::trash(uniqid(), $js_remove);
            }
            echo '</span>';
            echo '&nbsp;';
            echo '&nbsp;';
        }
        
        if ($p_view != 'READ' && $g_user->can_write_action($this->ag_id)==true)
        {
            $js=sprintf("onclick=\"action_tag_select('%s','%s')\"", dossier::id(), $this->ag_id);
            echo HtmlInput::button('tag_bt', _('Ajout étiquette'), $js, 'smallbutton');
        }
    }
 
    static function action_tag_remove($cn, $p_array)
    {
        global $g_user;
        $mag_id=$p_array['mag_id'];
        $remtag=$p_array['remtag'];
        for ($i=0; $i<count($mag_id); $i++)
        {
            if ($g_user->can_write_action($mag_id[$i])==false)
                continue;
            for ($e=0; $e<count($remtag); $e++)
            {
                $a=new Follow_Up($cn, $mag_id[$i]);
                $a->tag_remove($remtag[$e]);
            }
        }
    }
 
    static function action_tag_add($cn, $p_array)
    {
        global $g_user;
        $mag_id=$p_array['mag_id'];
        $addtag=$p_array['addtag'];
        for ($i=0; $i<count($mag_id); $i++)
        {
            if ($g_user->can_write_action($mag_id[$i])==false)
                continue;
            for ($e=0; $e<count($addtag); $e++)
            {
                $a=new Follow_Up($cn, $mag_id[$i]);
                $a->tag_add($addtag[$e]);
            }
        }
    }
 
    static function action_tag_clear($cn, $p_array)
    {
        global $g_user;
        $mag_id=$p_array['mag_id'];
        for ($i=0; $i<count($mag_id); $i++)
        {
            if ($g_user->can_write_action($mag_id[$i])==false)
                continue;
            $a=new Follow_Up($cn, $mag_id[$i]);
            $a->tag_clear();
        }
    }
 
    static function action_print($cn, $p_array)
    {
        global $g_user;
        $mag_id=$p_array['mag_id'];
        for ($i=0; $i<count($mag_id); $i++)
        {
            if ($g_user->can_read_action($mag_id[$i])==false)
                continue;
            $a=new Follow_Up($cn, $mag_id[$i]);
            $a->get();
            echo '<div class="content">';
            echo $a->Display("READ", false, "");
            echo '</div>';
            echo '<P id="breakhere"> - - </p>';
        }
    }
 
    function tag_clear()
    {
        $this->db->exec_sql('delete from action_tags where ag_id=$1', array($this->ag_id));
    }
 
    static function action_set_state($cn, $p_array)
    {
 
        global $g_user;
        $mag_id=$p_array['mag_id'];
        $state=$p_array['ag_state'];
        for ($i=0; $i<count($mag_id); $i++)
        {
            if ($g_user->can_write_action($mag_id[$i])==false)
                continue;
            $cn->exec_sql('update action_gestion set ag_state=$1 where ag_id=$2', array($state, $mag_id[$i]));
        }
    }
 
    static function action_remove($cn, $p_array)
    {
        global $g_user;
 
        $mag_id=$p_array['mag_id'];
        for ($i=0; $i<count($mag_id); $i++)
        {
            if ($g_user->can_write_action($mag_id[$i])==false)
                continue;
            $cn->exec_sql('delete from action_gestion where ag_id=$1', array($mag_id[$i]));
        }
    }
 
    /**
     * Verify that data are correct
     * @throws Exception
     */
    function verify()
    {
        if ($this->dt_id==-1)
        {
            throw new Exception(_('Type action invalide'), 10);
        }
        if (isDate($this->ag_timestamp)!=$this->ag_timestamp)
            throw new Exception(_('Date invalide'), 20);
        if (isDate($this->ag_remind_date)!=$this->ag_remind_date)
            throw new Exception(_('Date invalide'), 30);
        if ($this->f_id_dest==0)
            $this->f_id_dest=null;
    }
 
   
    /**
     * @brief display a small form to enter a new event
     * 
     */
    function display_short()
    {
        $cn=$this->db;
        include NOALYSS_TEMPLATE.'/action_display_short.php'; 
    }
    /**
     * Add an event , with the minimum of informations, 
     * used in Dashboard and Scheduler
     */
    function save_short()
    {
        global $g_user;
        // check if we can add
        if ($g_user->can_add_action($this->ag_dest) == FALSE ) 
        {
                throw new Exception(_('SECURITE : Ajout impossible'));
        }
        
            
        
        // Get The sequence id,
        $seq_name="seq_doc_type_".$this->dt_id;
        $str_file="";
        $add_file='';
 
        
        $this->ag_id=$this->db->get_next_seq('action_gestion_ag_id_seq');
 
        // Create the reference
        $ag_ref=$this->db->get_value('select dt_prefix from document_type '
                . 'where dt_id=$1', array($this->dt_id))
                .'-'.$this->db->get_next_seq($seq_name);
        
        $this->ag_ref=$ag_ref;
        /**
         * If ag_ref already exist then compute a new one
         */
        
        // save into the database
        $sql="insert into action_gestion".
                    "(ag_id,ag_timestamp,ag_type,ag_title,f_id_dest,ag_ref, "
                . "ag_dest, ".
                    "  ag_priority,ag_owner,ag_state,ag_remind_date,ag_hour) ".
                    " values "
                . "($1,to_date($2,'DD.MM.YYYY'),$3,$4,$5,$6,"
                . "$7,"
                . "$8,$9,$10,to_date($11,'DD.MM.YYYY'),$12)";
        
        $this->db->exec_sql($sql, array(
            $this->ag_id, /* 1 */
            $this->ag_timestamp, /* 2 */
            $this->dt_id, /* 3 */
            $this->ag_title, /* 4 */
            $this->f_id_dest, /* 5 */
            $ag_ref, /* 6 */
            $this->ag_dest, /* 7 */
            $this->ag_priority, /* 8 */
            $_SESSION[SESSION_KEY.'g_user'], /* 9 */
            $this->ag_state, /* 10 */
            $this->ag_remind_date, /* 11 */
            $this->ag_hour /* 12 */
           )
        );
 
        if (trim($this->ag_comment??"")!='')
        {
            $this->db->exec_sql("insert into action_gestion_comment (ag_id,tech_user,agc_comment) values ($1,$2,$3)"
                    , array($this->ag_id, $_SESSION[SESSION_KEY.'g_user'], $this->ag_comment));
        }
    }
    /**
     * @brief Return the first parent of the event tree, or -1 if not found. The parent is an action with a lower id,
     * so it can happen than an action has several ones
     * @return arrary of integer (ag_id)
     */
    function get_parent() {
        $value=$this->db->get_array('
             with recursive t (aga_least,aga_greatest,depth) as (
                select aga_least,aga_greatest , 1
                from 
                  action_gestion_related
                 where aga_greatest=$1
              union all
                select p2.aga_least,p2.aga_greatest,depth + 1
                from 
                  t as p1, action_gestion_related as p2
                where
                  p2.aga_greatest is not null and
                  p2.aga_greatest = p1.aga_least
              ) select aga_least,aga_greatest,depth from t order by depth desc,aga_least asc 
                ' , array($this->ag_id)
                );
        if ( ! empty($value ) ) 
            return $value;
        else
            return -1;
    }
    /**
     * Compute an array of the complete tree depending of $p_id
     * @param $p_id ag_id
     * @return array 
     * key index :
     *     - uniq value  , path 
     *     - aga_least
     *     - aga_greatest
     *     - depth
     */
    function get_children($p_id) {
        // retrieve parent
        // Get information
         $sql = "with recursive t (key_path, aga_least,aga_greatest,depth) as (
                select 
                 aga_least::text||'-'||aga_greatest::text as key_path ,
                        aga_least,aga_greatest , 1
                from 
                        action_gestion_related
              where aga_least=$1  or aga_greatest = $1
                union all
            select key_path||'-'||p2.aga_greatest::text,
              p2.aga_least,p2.aga_greatest,depth + 1
            from 
              t as p1, action_gestion_related as p2
            where
              p1.aga_greatest is not null and
              p1.aga_greatest = p2.aga_least
          ) 
          select key_path,aga_greatest,ag_title as title,depth ,to_char(ag_timestamp,'DD/MM/YY') as str_date,ag_ref||' '||dt_value as action_ref
          from 
            action_gestion join t on (ag_id=aga_greatest)
            join document_type on (ag_type=dt_id)
             order by key_path
";
         $ret_array=$this->db->get_array($sql,array($p_id));
         // Empty returns
         if ( empty($ret_array)) return array();
         
         
         return $ret_array;
    }
 
    /**
     * @brief Display the tree of childrens of the current Action + related parents
     * @return return the tree of children in a unordered list , HTML string 
     */
    function display_children($p_view, $p_base)
    {
        /*
         * First we retrieve the parent
         */
        $parent_id=$this->db->get_value("select min( get_follow_up_tree) from comptaproc.get_follow_up_tree($1)",[$this->ag_id]);
        $http=new HttpInput();
        $base=HtmlInput::request_to_string(array("gDossier", "ac",  "sb", "sc",
                    "f_id"))."&amp;sa=detail";
        $parent=array();
        if (empty($parent_id))
        {
            echo _('Principal');
            $parent_id= $this->ag_id;
        }
 
 
        $fu_parent=new Follow_Up($this->db, $parent_id);
        $fu_parent->get();
        echo'<span class="highlight">';
        $xaction=sprintf('view_action(%d,%d,%d)', $fu_parent->ag_id,
                    Dossier::id(), 1);
        $showAction='<a class="line" href="javascript:'.$xaction.'">';
        echo $showAction.
            $fu_parent->ag_timestamp," ",
            h($fu_parent->ag_title),
            '('.h($fu_parent->ag_ref).')',
                '</a>';
 
        echo "</span>";
        echo '<ul style="padding-left:10px;list-style-type: none;">';
 
        $action=$this->get_children($parent_id);
        for ($o=0; $o<count($action); $o++)
        {
            $class=($this->ag_id == $action[$o]['aga_greatest'])?' class="highlight" ':'';
 
            // Count the number of direct parents
            $count_parent =$this->db->get_value('select count(*) from action_gestion_related where aga_greatest = $1',array($action[$o]['aga_greatest']));
            $direct_parent=($count_parent > 1 ) ? _('direct parent ').$count_parent:"";
 
            $margin=($action[$o]['depth']>1 )?str_repeat("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;",$action[$o]['depth']-1)."&#8680;":"";
            if ($p_view!='READ'&&$p_base!='ajax')
            {
                $rmAction=sprintf("return confirm_box(null,'"._('Voulez-vous effacer cette action ')."', function () {remove_action('%s','%s','%s');});",
                        dossier::id(), $action[$o]['aga_greatest'],
                        $http->request('ag_id',"number"));
                $showAction='<a class="line" href="'.$base."&ag_id=".$action[$o]['aga_greatest'].'">';
                $js=Icon_Action::trash("acact".$action[$o]['aga_greatest'], $rmAction);
                echo '<li '.$class.' id="act'.$action[$o]['aga_greatest'].'">'.$margin.$showAction.$action[$o]['str_date'].
                h($action[$o]['title']).'('.h($action[$o]['action_ref']).')'.$direct_parent.'</a>'." "
                .$js.'</li>';
            }
            else
            /*
             * Display detail requested from Ajax Div
             */
            if ($p_base=='ajax')
            {
                $xaction=sprintf('view_action(%d,%d,%d)', $action[$o]['aga_greatest'],
                        Dossier::id(), 1);
                $showAction='<a class="line" href="javascript:'.$xaction.'">';
                echo '<li  '.$class.' >'.$margin.$showAction.$action[$o]['str_date']." ".
                h($action[$o]['title']).'('.h($action[$o]['action_ref']).')'.$direct_parent.'</a>'." "
                .'</li>';
            }
            /*
             * READ ONLY
             */
            else
            {
                $showAction='<a class="line" href="'.$base."&ag_id=".$action[$o]['aga_greatest'].'">';
                echo '<li  '.$class.' >'.$margin.$showAction.$action[$o]['str_date']." ".
                h($action[$o]['title']).'('.h($action[$o]['action_ref']).')'.$direct_parent.'</a>'." "
                .'</li>';
            }
        }
        echo '</ul>';
 
    }
    /**
     * @brief Display the list of parent of the current Follow_Up
     * \param $p_view form will be in readonly mode (value: READ, UPD or NEW  )
     * \param $p_base is the ac parameter
     * \see Follow_Up::Display
     * \return None display the HTML list 
     */
    function display_parent($p_view,$p_base) 
    {
        $a_parent=$this->db->get_array(
                "
 select ag1.ag_id
      ,ag1.ag_title as title 
      ,to_char(ag1.ag_timestamp,'DD/MM/YY') as str_date
      ,ag1.ag_ref||' '||dt_value as action_ref
 from 
    action_gestion ag1
    join document_type on (ag_type=dt_id)
    join (select distinct get_follow_up_tree from comptaproc.get_follow_up_tree($1)) tree_ag 
            on (tree_ag.get_follow_up_tree=ag1.ag_id)
order by ag_id
                ", array($this->ag_id)
                );
        if ( empty($a_parent ) ) return;
        echo '<ul style="padding-left:10px;list-style-type: none;">';
        $base=HtmlInput::request_to_string(array("gDossier", "ac", "sa", "sb", "sc",
                    "f_id"));
        $http=new HttpInput();
        for ($o=0; $o<count($a_parent); $o++)
        {
            $class=($this->ag_id == $a_parent[$o]['ag_id'])?' class="highlight" ':'';
 
            if ($p_view!='READ'&&$p_base!='ajax')
            {
                $rmAction=sprintf("return confirm_box(null,'"._('Voulez-vous effacer cette action ')."', function () {remove_action('%s','%s','%s');});",
                        dossier::id(), $a_parent[$o]['ag_id'],
                        $http->request('ag_id',"number"));
                $showAction='<a class="line" href="'.$base."&ag_id=".$a_parent[$o]['ag_id'].'">';
 
                $js=\Icon_Action::trash('acact'.$a_parent[$o]['ag_id'],$rmAction);
                
                echo '<li '.$class.' id="act'.$a_parent[$o]['ag_id'].'">'.$showAction.$a_parent[$o]['str_date'].
                h($a_parent[$o]['title']).'('.h($a_parent[$o]['action_ref']).')'.'</a>'." "
                .$js.'</li>';
            }
            else
            /*
             * Display detail requested from Ajax Div
             */
            if ($p_base=='ajax')
            {
                $xaction=sprintf('view_action(%d,%d,%d)', $a_parent[$o]['ag_id'],
                        Dossier::id(), 1);
                $showAction='<a class="line" href="javascript:'.$xaction.'">';
                echo '<li  '.$class.' >'.$showAction.$a_parent[$o]['str_date']." ".
                h($a_parent[$o]['title']).'('.h($a_parent[$o]['action_ref']).')'.'</a>'." "
                .'</li>';
            }
            /*
             * READ ONLY
             */
            else
            {
                $showAction='<a class="line" href="'.$base."&ag_id=".$a_parent[$o]['ag_id'].'">';
                echo '<li  '.$class.' >'.$showAction.$a_parent[$o]['str_date']." ".
                h($a_parent[$o]['sub_title']).'('.h($a_parent[$o]['action_ref']).')'.'</a>'." "
                .'</li>';
            }
        }
        echo '</ul>';
    }
 
}