<?php
/**
 * Ce script définit la classe 'operation'.
 *
 * @package opencimetiere
 * @version SVN : $Id$
 */

require_once "../gen/obj/operation.class.php";

/**
 * Définition de la classe 'operation' (om_dbform).
 */
class operation extends operation_gen {

    /**
     * @var string
     */
    var $emplacement_nature = null;

    /**
     * @var array
     */
    var $required_field = array(
        "operation",
        "numdossier",
        "date",
        "heure",
        "etat",
        "categorie",
        "emplacement",
    );

    /**
     * Définition des actions disponibles sur la classe.
     *
     * @return void
     */
    function init_class_actions() {
        parent::init_class_actions();
        // ACTION - 011 - modifier
        //
        $this->class_actions[11] = array(
            "identifier" => "pdf-edition",
            "portlet" => array(
                "type" => "action-self",
                "libelle" => __("edition"),
                "class" => "pdf-16",
                "order" => 30,
                ),
            "permission_suffix" => "edition",
            "view" => "view_pdf_edition",
            "condition" => array(
                "exists",
            ),
        );
        // ACTION - 021 - valider
        //
        $this->class_actions[21] = array(
            "identifier" => "valider",
            "portlet" => array(
                "type" => "action-self",
                "libelle" => __("valider"),
                "class" => "operation-valider-16",
                "order" => 40,
                ),
            "permission_suffix" => "valider",
            "method" => "valider",
            "condition" => array(
                "exists",
                "is_not_treated",
            ),
        );
        //
    }

    /**
     * CONDITION - is_not_treated.
     *
     * @return boolean
     */
    function is_not_treated() {
        if ($this->getVal("etat") === "trt") {
            return false;
        }
        return true;
    }

    /**
     * VIEW - view_pdf_edition.
     *
     * @return void
     */
    function view_pdf_edition() {
        $this->checkAccessibility();
        if ($this->getParameter("validation") > 1) {
            // Génération du PDF
            $pdfedition = $this->compute_pdf_output(
                "etat",
                implode(";", $_POST["obj"]),
                null,
                $this->getVal($this->clePrimaire)
            );
            // Affichage du PDF
            $this->expose_pdf_output(
                $pdfedition['pdf_output'],
                $pdfedition['filename']
            );
            return;
        }
        //
        echo( '<form method="post" action="'.$this->getDataSubmit().'" target="_blank" name="f1">' );
        $this->f->displayDescription(
            __("Cet écran permet d'imprimer les éditions disponibles pour votre opération.")
        );
        // recherche des etats disponibles pour cet obj
        $categorie = $this->getVal("categorie");
        $sql = "select om_etat, id, libelle from ".DB_PREFIXE."om_etat where actif is TRUE and id like 'operation%".$categorie."' order by id";
        $res = $this->f->db->query($sql);
        $this->addToLog(__METHOD__."(): db->query(\"".$sql."\");", VERBOSE_MODE);
        $this->f->isDatabaseError($res);
        //
        echo "<fieldset class=\"cadre ui-corner-all ui-widget-content\">\n";
        echo "\t<legend class=\"ui-corner-all ui-widget-content ui-state-active\">";
        echo __("Choix de l'edtion")." ".$categorie;
        echo "</legend>\n";
        echo("<table><tr><td>");
        if ($res->numRows() == 0) {
            echo(__("Aucun etat disponible pour cette operation.")."<br />");
        } else {
            while ($etat =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
                echo "<input type=\"hidden\" name=\"idx\" value=\"".$this->getVal($this->clePrimaire)."\" />";
                echo("<input type='checkbox' name='obj[]' value='".$etat['id']."' checked='checked' /><b>".$etat['libelle']."</b>  <i>(".$etat['id'].")</i><br />");
            }
        }
        echo("</td></tr></table>");
        echo "</fieldset>\n";
        //
        $this->f->layout->display__form_controls_container__begin(array(
            "controls" => "bottom",
        ));
        $this->f->layout->display__form_input_submit(array(
            "value" => __("Télécharger"),
        ));
        $this->retour();
        $this->f->layout->display__form_controls_container__end();
        //
        $this->f->layout->display__form_container__end();
    }

    /**
     * TREATMENT - valider.
     *
     * @return void
     */
    function valider($val = array()) {
        $this->begin_treatment(__METHOD__);
        /**
         * Récupération du paramétrage
         */
        //
        $default_taille_urne = 0.1;
        $taille_urne = $this->f->getParameter("taille_urne");
        if (is_null($taille_urne)) {
            $taille_urne = $default_taille_urne;
        }
        //
        $default_taille_cercueil = 1;
        $taille_cercueil = $this->f->getParameter("taille_cercueil");
        if (is_null($taille_cercueil)) {
            $taille_cercueil = $default_taille_cercueil;
        }
        //
        $default_taille_reduction = 0.5;
        $taille_reduction = $this->f->getParameter("taille_reduction");
        if (is_null($taille_reduction)) {
            $taille_reduction = $default_taille_reduction;
        }

        /**
         * Initialisation des paramètres
         */
        $idx = $this->getVal($this->clePrimaire);
        $categorie = $this->getVal("categorie");
        $validation = 0;
        if (isset($_GET['validation'])) {
            $validation = $_GET['validation'];
        }

        //
        $sql = "select date, operation.emplacement, numdossier, famille, emplacement_transfert";
        $sql.= " from ".DB_PREFIXE."operation inner join ".DB_PREFIXE."emplacement on emplacement.emplacement = operation.emplacement ";
        $sql.= " where operation = ".$idx;
        $res =  $this->f->db->query($sql);
        if ($this->f->isDatabaseError($res, true) !== false) {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }

        //
        while ($row=& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
            //
            $dateoperation = $row['date'];
            //
            $dateoperation_fr = substr($dateoperation, 8, 2)."/".substr($dateoperation, 5, 2)."/".substr($dateoperation, 0, 4);
            $emplacement=$row['emplacement'];
            $dossier = $row['numdossier'];
            $famille=$row['famille'];
            $emplacement_transfert=$row['emplacement_transfert'];
        } 

        $sql = "select * from ".DB_PREFIXE."operation_defunt where operation = ".$idx;
        $res =  $this->f->db->query($sql);
        if ($this->f->isDatabaseError($res, true) !== false) {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }
        $val = array();
        while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
            if ($row['defunt_titre'] == "") {
                $val['titre'] = null;
            }else
                $val['titre']=$row['defunt_titre'];
            $val['nom']=$row['defunt_nom'];
            $val['prenom'] = $row['defunt_prenom'];
            $val['marital']=$row['defunt_marital'];
            if($row['defunt_datenaissance'])
                $val['datenaissance']=$row['defunt_datenaissance'];
            if($row['defunt_datedeces'])
                $val['datedeces'] = $row['defunt_datedeces'];
            $val['lieudeces'] = $row['defunt_lieudeces'];
            $val['lieunaissance'] = $row['defunt_lieunaissance'];
            $val['parente'] = $row['defunt_parente'];
            $val['nature'] = $row['defunt_nature'];
            $val['verrou']='Non';

            if ($categorie == 'inhumation') {
                $val['defunt']= $this->f->db->nextId(DB_PREFIXE.'defunt');
                $val['emplacement']=$emplacement;
                $val['dateinhumation']=$dateoperation;
                $val['exhumation']='Non';
                $val['reduction']='Non';
                if($val['nature'] == 'urne')
                    $val['taille'] =$taille_urne;
                else
                    $val['taille']=$taille_cercueil;
                $val['historique']= "".
                   $categorie." ".__("dossier")." ".$dossier." ".__("du")." ".$dateoperation_fr."";
                $res1 = $this->f->db->autoExecute(DB_PREFIXE.'defunt', $val, DB_AUTOQUERY_INSERT);
                if ($this->f->isDatabaseError($res1, true) !== false) {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    return $this->end_treatment(__METHOD__, false);
                }
                // recuperer identifiant defunt pour operation_defunt (clé secondaire)
                $sql = " update ".DB_PREFIXE."operation_defunt set defunt = ".$val['defunt'].
                       " where operation_defunt =".$row['operation_defunt'];
                $res2 = $this->f->db->query($sql);
                if ($this->f->isDatabaseError($res2, true) !== false) {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    return $this->end_treatment(__METHOD__, false);
                }
            }
            if ($categorie == 'reduction') {
                $sql="select historique from ".DB_PREFIXE."defunt where defunt =".$row['defunt'];
                $historique=$this->f->db->getOne($sql);
                if ($this->f->isDatabaseError($historique, true) !== false) {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    return $this->end_treatment(__METHOD__, false);
                }
                $val['historique']=$historique."\n".
                   $categorie." ".__("dossier")." ".$dossier." ".__("du")." ".$dateoperation_fr."";
                $val['defunt']=$row['defunt'];
                $val['datereduction']=$dateoperation;
                $val['reduction']='Oui';
                $val['taille']=$taille_reduction;
                $val['nature']="boite";
                $cle= " defunt = ".$row['defunt'];
                $res1 = $this->f->db->autoExecute(DB_PREFIXE.'defunt', $val, DB_AUTOQUERY_UPDATE, $cle);
                if ($this->f->isDatabaseError($res1, true) !== false) {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    return $this->end_treatment(__METHOD__, false);
                }
            }
            if ($categorie == 'transfert') {
                $sql="select historique from ".DB_PREFIXE."defunt where defunt =".$row['defunt'];
                $historique=$this->f->db->getOne($sql);
                if ($this->f->isDatabaseError($historique, true) !== false) {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    return $this->end_treatment(__METHOD__, false);
                }
                $val['historique']=$historique."\n".
                   $categorie." ".__("dossier")." ".$dossier." ".__("du")." ".$dateoperation_fr." ".
                            __("emplacement")." ".$emplacement." ".$famille;
                $val['emplacement']=$emplacement_transfert;
                $cle= " defunt = ".$row['defunt'];
                $res1 = $this->f->db->autoExecute(DB_PREFIXE.'defunt', $val, DB_AUTOQUERY_UPDATE, $cle);
                if ($this->f->isDatabaseError($res1, true) !== false) {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    return $this->end_treatment(__METHOD__, false);
                }
            }
            echo "<br>".$categorie." ".__('defunt'). " ".$val['nom']." ".$val['marital']." ".$val['prenom'];
        }
        
        // calcul place occupees emplacement origine
        $sql = "select sum(taille) from ".DB_PREFIXE."defunt where emplacement = ".$emplacement;
        $place =  $this->f->db->getOne($sql);
        if ($this->f->isDatabaseError($place, true) !== false) {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }
        if($place=='') $place=0;
        $sql = "update ".DB_PREFIXE."emplacement set placeoccupe=".$place.
               " where emplacement =".$emplacement;
        $res2 =  $this->f->db->query($sql);
        if ($this->f->isDatabaseError($res2, true) !== false) {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }
        echo "<br/>".__("place occupee")." : ".__("emplacement")." ".
                $emplacement." : ".$place;

        // calcul places occupees transfert
        if ($categorie == 'transfert') {
            //
            $sql = "select sum(taille) from ".DB_PREFIXE."defunt where emplacement = ".$emplacement_transfert;
            $place_transfert = $this->f->db->getOne($sql);
            if ($this->f->isDatabaseError($place_transfert, true) !== false) {
                $this->correct = false;
                $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                return $this->end_treatment(__METHOD__, false);
            }
            if ($place_transfert == '') {
                $place_transfert = 0;
            }
            //
            $sql = "update ".DB_PREFIXE."emplacement set placeoccupe=".$place_transfert." where emplacement =".$emplacement_transfert;
            $res4 =  $this->f->db->query($sql);
            if ($this->f->isDatabaseError($res4, true) !== false) {
                $this->correct = false;
                $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                return $this->end_treatment(__METHOD__, false);
            }
            echo "<br/>".__("place occupee")." ".__('transfert')." ".__("emplacement")." ".$emplacement_transfert." : ".$place_transfert;
        }
        // operation actif -> trt
        $sql = "update ".DB_PREFIXE."operation set etat='trt' where operation = ".$idx;
        $res3 =  $this->f->db->query($sql);
        if ($this->f->isDatabaseError($res3, true) !== false) {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }
        //
        $this->addToMessage(sprintf(
            __("L'opération %s est validée."),
            $this->getVal("numdossier")
        ));
        return $this->end_treatment(__METHOD__, true);
    }

    /**
     * GETTER_FORMINC - champs.
     *
     * @return array
     */
    function get_var_sql_forminc__champs() {
        return array(
            "operation.operation",
            "numdossier",
            "etat",
            "categorie",
            "date",
            "heure",
            "operation.emplacement as emplacement",
            "operation.consigne_acces",
            "emplacement_transfert",
            "'' as entreprise",
            "societe_coordonnee",
            "particulier",
            "'' as pf",
            "pf_coordonnee",
            "defunt_titre",
            "defunt_nom",
            "defunt_marital",
            "defunt_prenom",
            "defunt_datenaissance",
            "defunt_lieunaissance",
            "defunt_datedeces",
            "defunt_lieudeces",
            "defunt_parente",
            "defunt_nature",
            "operation.observation",
            "operation.prive"
        );
    }

    /**
     * GETTER_FORMINC - tableSelect.
     *
     * @return string
     */
    function get_var_sql_forminc__tableSelect() {
        return DB_PREFIXE."operation 
            inner join ".DB_PREFIXE."emplacement as e on e.emplacement = operation.emplacement
            left join ".DB_PREFIXE."emplacement as t on t.emplacement = operation.emplacement_transfert
            left join ".DB_PREFIXE."operation_defunt on operation_defunt.operation=operation.operation
        ";
    }

    /**
     * GETTER_FORMINC - sql_emplacement_by_id.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_emplacement_by_id() {
        return "SELECT emplacement.emplacement, ('n°'||emplacement.emplacement||' - Famille : '||emplacement.famille) FROM ".DB_PREFIXE."emplacement WHERE emplacement = <idx>";
    }

    /**
     * GETTER_FORMINC - sql_emplacement_transfert_by_id.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_emplacement_transfert_by_id() {
        return $this->get_var_sql_forminc__sql_emplacement_by_id();
    }

    /**
     * GETTER_FORMINC - sql_entreprise.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_entreprise() {
        //
        $sql_entreprise_common = "SELECT
        (
              coalesce(nomentreprise, '')
              ||'\n'||
              coalesce(adresse1, '')
              ||'\n'||
              coalesce(adresse2, '')
              ||'\n'||
              coalesce(cp, '')
              ||' '||
              coalesce(ville, '')
        ), 
        nomentreprise 
        FROM ".DB_PREFIXE."entreprise 
        ";
        //
        return $sql_entreprise_common." where pf='Non'";
    }

    /**
     * GETTER_FORMINC - sql_pf.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_pf() {
        //
        $sql_entreprise_common = "SELECT
        (
              coalesce(nomentreprise, '')
              ||'\n'||
              coalesce(adresse1, '')
              ||'\n'||
              coalesce(adresse2, '')
              ||'\n'||
              coalesce(cp, '')
              ||' '||
              coalesce(ville, '')
        ), 
        nomentreprise 
        FROM ".DB_PREFIXE."entreprise 
        ";
        //
        return $sql_entreprise_common." where pf='Oui'";
    }

    /**
     * GETTER_FORMINC - sql_defunt_titre.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_defunt_titre() {
        return "SELECT titre_de_civilite.titre_de_civilite, titre_de_civilite.libelle FROM ".DB_PREFIXE."titre_de_civilite WHERE ((titre_de_civilite.om_validite_debut IS NULL AND (titre_de_civilite.om_validite_fin IS NULL OR titre_de_civilite.om_validite_fin > CURRENT_DATE)) OR (titre_de_civilite.om_validite_debut <= CURRENT_DATE AND (titre_de_civilite.om_validite_fin IS NULL OR titre_de_civilite.om_validite_fin > CURRENT_DATE))) ORDER BY titre_de_civilite.libelle ASC";
    }

    /**
     * GETTER_FORMINC - sql_defunt_titre_by_id.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_defunt_titre_by_id() {
        return "SELECT titre_de_civilite.titre_de_civilite, titre_de_civilite.libelle FROM ".DB_PREFIXE."titre_de_civilite WHERE titre_de_civilite = <idx>";
    }

    /**
     * SETTER_TREATMENT - setValF (setValF).
     *
     * @return void
     */
    function setvalF($val = array()) {
        parent::setvalF($val);
        //
        unset($this->valF['entreprise']);
        // enregistrement operation_defunt
        unset($this->valF['defunt_titre']);
        unset($this->valF['defunt_nom']);
        unset($this->valF['defunt_marital']);
        unset($this->valF['defunt_prenom']);
        unset($this->valF['defunt_datenaissance']);
        unset($this->valF['defunt_datedeces']);
        unset($this->valF['defunt_lieudeces']);
        unset($this->valF['defunt_lieunaissance']);
        unset($this->valF['defunt_parente']);
        unset($this->valF['defunt_nature']);
        if(!is_numeric($val['emplacement_transfert'])) unset($this->valF['emplacement_transfert']);
        /*
        // *** gestion de la cle primaire ***
        // re-initialisation de la cle pour la methode ajouter -> message
        $this->clePrimaire = 'operation';
        // initialisation de la cle pour la methode modifier
        $this->id=$val['operation'];
        // ***
        */
    }

    /**
     * CHECK_TREATMENT - verifier.
     *
     * @return void
     */
    function verifier($val = array(), &$dnu1 = null, $dnu2 = null) {
        parent::verifier($val);
        // Vérifie l'existance de l'emplacement
        $emplacement = $this->f->get_inst__om_dbform(array(
            "obj" => "emplacement",
            "idx" => $this->valF['emplacement'],
        ));
        //
        if ($emplacement->getVal($emplacement->clePrimaire) === ''
            || $emplacement->getVal($emplacement->clePrimaire) === null) {
            //
            $this->correct = false;
            $this->addToMessage("<br />".__("L'emplacement renseigné n'existe pas"));
        }
    }

    /**
     * SETTER_FORM - setType.
     *
     * @return void
     */
    function setType(&$form, $maj) {
        parent::setType($form, $maj);
        //
        if ($maj < 2) { //ajouter et modifier
            $form->setType("operation", "hiddenstatic");
            $form->setType('numdossier', 'hiddenstatic'); // registre a terme
            $form->setType('emplacement', 'autocomplete'); // pb de cle numerique
            $form->setType('entreprise', 'select');
            $form->setType('societe_coordonnee', 'textareamulti');
            $form->setType('pf', 'select');
            $form->setType('pf_coordonnee', 'textareamulti');
            $form->setType('etat', 'hiddenstatic');
            $form->setType('particulier', 'checkbox');
            $form->setType('categorie', 'hiddenstatic');

            $form->setType('defunt_titre', 'hidden');
            $form->setType('defunt_nom', 'hidden');
            $form->setType('defunt_marital', 'hidden');
            $form->setType('defunt_prenom', 'hidden');
            $form->setType('defunt_datenaissance', 'hidden');
            $form->setType('defunt_datedeces', 'hidden');
            $form->setType('defunt_lieudeces', 'hidden');
            $form->setType('defunt_lieunaissance', 'hidden');
            $form->setType('defunt_parente', 'hidden');
            $form->setType('defunt_nature', 'hidden');

            $form->setType('emplacement_transfert', 'hidden');
        }
        
        if ($maj == 2) {
			$form->setType('emplacement', 'hiddentstatic'); // pb de cle numerique
			$form->setType('emplacement_transfert', 'hiddentstatic'); // pb de cle numerique
			
		}
        
        if ($maj == 3) {
            $form->setType('defunt_titre', 'hidden');
            $form->setType('defunt_nom', 'hidden');
            $form->setType('defunt_marital', 'hidden');
            $form->setType('defunt_prenom', 'hidden');
            $form->setType('defunt_datenaissance', 'hidden');
            $form->setType('defunt_datedeces', 'hidden');
            $form->setType('defunt_lieudeces', 'hidden');
            $form->setType('defunt_nature', 'hidden');
            $form->setType('defunt_lieunaissance', 'hidden');
            $form->setType('defunt_parente', 'hidden');
            $form->setType('emplacement_transfert', 'hidden');
            
            $form->setType('observation', 'hidden');
        }

        if ($maj == 21) {
            foreach ($this->champs as $champ) {
                $form->setType($champ, 'hidden');
            }
            $form->setType("operation", "static");
            $form->setType("numdossier", "static");
            $form->setType("date", "datestatic");
            $form->setType("heure", "static");
            $form->setType("emplacement", "selectstatic");
            $form->setType("societe_coordonnee", "textareastatic");
            $form->setType("pf_coordonnee", "textareastatic");
            $form->setType("etat", "static");
            $form->setType("categorie", "static");
            $form->setType("particulier", "static");
            $form->setType("emplacement_transfert", "hidden");
            $form->setType("observation", "textareastatic");
            $form->setType("consigne_acces", "static");
            $form->setType("prive", "checkboxstatic");
        }
    }

    /**
     * SETTER_FORM - set_form_default_values (setVal).
     *
     * @return void
     */
    function set_form_default_values(&$form, $maj, $validation) {
        parent::set_form_default_values($form, $maj, $validation);
        //
        if ($validation == 0) {
            if ($maj == 0) {
                $form->setVal("operation", "[...]");
                $form->setVal("numdossier", date("Y")."-?");
                $form->setVal('categorie', $this->categorie);
                $form->setVal('etat', "actif");
                $form->setVal('date', date('Y-m-d'));
            }
        }
    }

    /**
     * Configuration du widget de formulaire autocomplete du champ 'emplacement'.
     *
     * @return array
     */
    function get_widget_config__emplacement__autocomplete() {
        return  array(
            // Surcharge visée pour l'ajout
            'obj' => "emplacement",
            // Table de l'objet
            'table' => "emplacement",
            // Permission d'ajouter
            'droit_ajout' => false,
            // Critères de recherche
            'criteres' => array(
                "'n°'||emplacement.emplacement" => __("n° de l'emplacement (ex : n°12)"),
                "emplacement.emplacement" => __("identifiant de l'emplacement (ex : 12)"),
                "emplacement.famille" => __("famille (ex : DUPONT)"),
            ),
            // Tables liées
            'jointures' => array(
            ),
            // Colonnes ID et libellé du champ
            // (si plusieurs pour le libellé alors une concaténation est faite)
            'identifiant' => "emplacement.emplacement",
            //
            'libelle' => array(
                "('n°'||emplacement.emplacement||' - Famille : '||emplacement.famille)",
            ),
            //
            'where' => ($this->emplacement_nature === null ? "" : "nature='".$this->emplacement_nature."'"),
            //
            'group_by' => array(
            ),
            //
            'link_selection' => false,
        );
    }

    /**
     * SETTER_FORM - setSelect.
     *
     * @return void
     */
    function setSelect(&$form, $maj, &$dnu1 = null, $dnu2 = null) {
        parent::setSelect($form, $maj);
        //
        if($maj<2){
            // AUTOCOMPLETE emplacement
            $form->setSelect(
                "emplacement",
                $this->get_widget_config__emplacement__autocomplete()
            );

            // entreprise_coordonnee
            $contenu = array();
            $res = $this->f->db->query($this->get_var_sql_forminc__sql("entreprise"));
            $this->addToLog(
                __METHOD__."(): db->query(\"".$this->get_var_sql_forminc__sql("entreprise")."\");",
                VERBOSE_MODE
            );
            $this->f->isDatabaseError($res);
            $contenu[0][0] = '';
            $contenu[1][0] = __('choisir')."&nbsp;".__('entreprise');
            $k = 1;
            while ($row =& $res->fetchRow()) {
                $contenu[0][$k] = $row[0];
                $contenu[1][$k] = $row[1];
                $k++;
            }
            $form->setSelect("entreprise", $contenu);
            // multi
            $contenu=array();
            $contenu[0] ="entreprise";
            $form->setSelect("societe_coordonnee",$contenu);

            // pf_coordonnee
            $contenu = array();
            $res = $this->f->db->query($this->get_var_sql_forminc__sql("pf"));
            $this->addToLog(
                __METHOD__."(): db->query(\"".$this->get_var_sql_forminc__sql("pf")."\");",
                VERBOSE_MODE
            );
            $this->f->isDatabaseError($res);
            $contenu[0][0] = '';
            $contenu[1][0] = __('choisir')."&nbsp;".__('pf');
            $k = 1;
            while ($row =& $res->fetchRow()) {
                $contenu[0][$k] = $row[0];
                $contenu[1][$k] = $row[1];
                $k++;
            }
            $form->setSelect("pf", $contenu);
            // multi
            $contenu=array();
            $contenu[0] ="pf";
            $form->setSelect("pf_coordonnee",$contenu);
        }
    }

    /**
     * SETTER_FORM - setOnchange.
     *
     * @return void
     */
    function setOnchange(&$form, $maj) {
        parent::setOnchange($form, $maj);
        //
        $form->setOnchange('heure','ftime(this)');
    }

    /**
     * SETTER_FORM - setLib.
     *
     * @return void
     */
    function setLib(&$form, $maj) {
        parent::setLib($form, $maj);
        //
        $form->setLib($this->clePrimaire, __("id"));
        $form->setLib('emplacement', __($this->emplacement_nature));
        //
        $form->setLib('heure',__("heure"));
        $form->setLib('date',__("date"));
        $form->setLib('etat',__("etat"));
        $form->setLib('numdossier',__("numdossier"));
        $form->setLib('societe_coordonnee',"");
        $form->setLib('pf_coordonnee',"");
        $form->setLib('consigne_acces',__("consigne d'accès"));
        $form->setLib('prive',__("privé"));
        // Libellé différent pour la consultation et pour la suppression
        // que pour l'ajout et la modification
        if ($maj == 3 || $maj == 2) {
            $form->setLib('entreprise', __("entreprise"));
            $form->setLib('pf', __("pompe funebre"));
        } else {
            $form->setLib('entreprise', __("votre choix et validation  bouton ci-dessous"));
            $form->setLib('pf', __("votre choix et validation  bouton ci-dessous"));
        }
    }

    /**
     * SETTER_FORM - setLayout.
     *
     * @return void
     */
    function setLayout(&$form, $maj) {
        parent::setLayout($form, $maj);
        //
        $bloc_global_width_full = "span12";
        $bloc_global_width_half = "span6";

        //// CONTAINER GLOBAL - START
        $this->form->setBloc("operation", "D", "", "emplacement-form form-action-".$maj);
        //---------------------------------------------------------------------------------
           //// LIGNE num dossier - START
            $this->form->setBloc("operation", "D", "", "row-fluid");
                //// BLOC ID + LIBRE - START
                $this->form->setBloc("operation", "D", "", $bloc_global_width_full." emplacement-bloc emplacement-bloc-identification ");
                  //// BLOC ID - START
                  $this->form->setBloc("operation", "D", "", " span12");
                  $this->form->setBloc("heure", "F");
                $this->form->setBloc("heure", "F");
            //// LIGNE num dossier - END
             $this->form->setBloc("heure", "F");
           
            ///emplacement
            $this->form->setBloc("emplacement", "D", "", "row-fluid");
                //// BLOC emplacement - START
                $this->form->setBloc("emplacement", "D", "", $bloc_global_width_full." emplacement-bloc emplacement-bloc-adresse-localisation ");
                    //// LIGNE emplacement - START
                    $this->form->setBloc("emplacement", "D", "", "row-fluid");
                        //// BLOC emplacement - START
                        $this->form->setBloc("emplacement", "D",  __("emplacement"), "group span12");
                        $this->form->setBloc("emplacement_transfert", "F");
                        //// BLOC emplacement - END
                    $this->form->setBloc("emplacement_transfert", "F");
                $this->form->setBloc("emplacement_transfert", "F");
            $this->form->setBloc("emplacement_transfert", "F");
            /////
            //// entreprise+ + pompe funebre
            $this->form->setBloc("entreprise", "D", "", $bloc_global_width_full." emplacement-bloc emplacement-bloc-infos ");
            //// LIGNE entreprise + pompe funebre - START
            $this->form->setBloc("entreprise", "D", "", "row-fluid");
                    
                    //// BLOC entreprise - START
                    $this->form->setBloc("entreprise", "D", "", $bloc_global_width_half);
                    //// ligne entreprise - START
                     $this->form->setBloc("entreprise", "D", "", "row-fluid");
                            //// BLOC ENTREPRISE - START
                            $this->form->setBloc("entreprise", "D", __("entreprise"), " emplacement-bloc-acte span11");
                            $this->form->setBloc("entreprise", "D", "", "span11");
                            $this->form->setBloc("particulier", "F");
                            $this->form->setBloc("particulier", "F");
                     //// BLOC entreprise - END
                     $this->form->setBloc("particulier", "F");
                    //// LIGNE entreprise - END
                    $this->form->setBloc("particulier", "F");
                              
                              
                    //// BLOC pf - START
                    $this->form->setBloc("pf", "D", "", $bloc_global_width_half);
                    //// ligne pf - START
                     $this->form->setBloc("pf", "D", "", "row-fluid");
                            //// BLOC PF - START
                            $this->form->setBloc("pf", "D", __("pompe funebre"), " emplacement-bloc-acte span11");
                            $this->form->setBloc("pf", "D", "", "span11");
                            $this->form->setBloc("pf_coordonnee", "F");
                            $this->form->setBloc("pf_coordonnee", "F");
                             
                    //// BLOC pf - END
                     $this->form->setBloc("pf_coordonnee", "F");
                    //// LIGNE pf - END
                    $this->form->setBloc("pf_coordonnee", "F");
                    
            //// LIGNE entreprise + pompe funebre - END
            $this->form->setBloc("pf_coordonnee", "F");
        $this->form->setBloc("pf_coordonnee", "F");
        //defunt
        $this->form->setBloc("defunt_titre", "D", "", $bloc_global_width_full." emplacement-bloc emplacement-bloc-infos ");
            $this->form->setBloc("defunt_titre", "D", "", "row-fluid");
                    //// BLOC
                    $this->form->setBloc("defunt_titre", "D", "", $bloc_global_width_full);
                        //// LIGNE defunt - START
                        $this->form->setBloc("defunt_titre", "D", "", "row-fluid");
                            //// BLOC defunt - START
                            $this->form->setBloc("defunt_titre", "D", __("defunt"), " emplacement-bloc-acte span12");
                                $this->form->setBloc("defunt_titre", "D", "", "span12");
                                $this->form->setBloc("defunt_nature", "F");
                            $this->form->setBloc("defunt_nature", "F");
                            //// BLOC defunt- END
                        $this->form->setBloc("defunt_nature", "F");
                    //// LIGNE defunt - END
                    $this->form->setBloc("defunt_nature", "F");
                     //// LIGNE defunt - END
            $this->form->setBloc("defunt_nature", "F");
         $this->form->setBloc("defunt_nature", "F");

        //// LIGNE OBSERVATION - START
        $this->form->setBloc("observation", "D", "", "row-fluid");
            //// BLOC OBSERVATION - START
            $this->form->setBloc("observation", "D", "", $bloc_global_width_full." emplacement-bloc emplacement-bloc-observation ");
                $this->form->setBloc("observation", "D", "", "group span12");
                $this->form->setBloc("prive", "F");
            $this->form->setBloc("prive", "F");
            // BLOC OBSERVATION - END
        $this->form->setBloc("prive", "F");
        //// LIGNE OBSERVATION - START

        /// CONTAINER GLOBAL - END
        $this->form->setBloc("observation", "F");
    }

    /**
     * Récupération du numéro de séquence suivant pour l'année de l'opération.
     *
     * @return integer|null
     */
    function get_next_numdossier_sequence($year) {
        /**
         * On compose les identifiants qui composent la séquence.
         */
        // Clé unique.
        // Exemple : 2020
        $unique_key = sprintf('%s', $year);
        // Nom de la table représentant la séquence pour appel via la méthode
        // database::nextId() qui prend un nom de séquence sans son suffixe
        // '_seq'.
        // Exemple : opencimetiere.numdossier_2020
        $table_name = sprintf('%snumdossier_%s', DB_PREFIXE, $unique_key);
        // Nom de la séquence avec son suffixe 'seq'.
        // Exemple : opencimetiere.numdossier_2020_seq
        $sequence_name = sprintf('%s_seq', $table_name);

        /**
         * On interroge la base de données pour vérifier si la séquence existe
         * ou non. Si il y a un retour à l'exécution de la requête alors la
         * séquence existe et si il n'y en a pas alors la séquence n'existe
         * pas.
         *
         * Cette requête particulière (car sur la table pg_class) nécessite
         * d'être exécutée sur le schéma public pour fonctionner correctement.
         * En effet, par défaut postgresql positionne search_path avec la
         * valeur '"$user", public' ce qui peut causer des mauvais effets de
         * bord si l'utilisateur et le schéma sont identiques.
         * On force donc le schéma public sur le search_path pour être sûr que
         * la requête suivante s'exécute correctement.
         */
        $res_search_path = $this->f->db->query("set search_path=public;");
        if ($this->f->isDatabaseError($res_search_path, true) !== false) {
            return null;
        }
        $query_sequence_exists = sprintf(
            'SELECT 
                * 
            FROM 
                pg_class 
            WHERE
                relkind = \'S\' 
                AND oid::regclass::text = \'%s\'
            ;',
            $sequence_name
        );
        $res_sequence_exists = $this->f->db->getone($query_sequence_exists);
        $this->addToLog(
            __METHOD__.'(): db->getone("'.$res_sequence_exists.'");',
            VERBOSE_MODE
        );
        if ($this->f->isDatabaseError($res_sequence_exists, true) !== false) {
            return null;
        }

        /**
         * Si la séquence n'existe pas, alors on la créé.
         *
         * Puis on vérifie si il existe des opérations sur l'année pour
         * initialiser la séquence à la valeur suivante.
         */
        if ($res_sequence_exists === null) {
            $res = $this->f->db->createSequence($table_name);
            $this->f->addToLog(
                __METHOD__.'(): db->createSequence("'.$table_name.'");',
                VERBOSE_MODE
            );
            if ($this->f->isDatabaseError($res, true) !== false) {
                return null;
            }
            $sql_last_numdossier = sprintf(
                'SELECT
                    MAX(CAST(SPLIT_PART(numdossier, \'-\', 2) AS INT)) AS max_numdossier
                FROM
                    %1$soperation
                WHERE
                    numdossier ILIKE \'%2$s-%%\'
                ',
                DB_PREFIXE,
                $year
            );
            $last_numdossier = $this->f->db->getone($sql_last_numdossier);
            $this->f->addToLog(__METHOD__.'(): db->getone("'.$sql_last_numdossier.'");', VERBOSE_MODE);
            if ($this->f->isDatabaseError($last_numdossier, true) === true) {
                return null;
            }
            if (intval($last_numdossier) !== 0) {
                $sql_maj_seq = sprintf('SELECT setval(\'%s\',%s);', $sequence_name, intval($last_numdossier));
                $res_seq = $this->f->db->query($sql_maj_seq);
                $this->f->addToLog(__METHOD__.'(): db->query("'.$sql_maj_seq.'");', VERBOSE_MODE);
                if ($this->f->isDatabaseError($res_seq, true) === true) {
                    return null;
                }
            }
        }

        /**
         * Récupère le prochain numéro de la séquence et le retourne
         */
        $next_id = $this->f->db->nextId($table_name, false);
        $this->addToLog(__METHOD__.'(): db->nextId("'.$table_name.'", false);', VERBOSE_MODE);
        if ($this->f->isDatabaseError($next_id, true) !== false) {
            return false;
        }
        return intval($next_id);
    }

    /**
     * TRIGGER - triggerajouter.
     *
     * @return boolean
     */
    function triggerajouter($id, &$dnu1 = null, $val = array(), $dnu2 = null) {
        // Récupération du numéro de séquence suivant pour l'année de l'opération
        $annee = date('Y', strtotime(str_replace('/', '-', $val['date'])));
        $ret = $this->get_next_numdossier_sequence($annee);
        if ($ret === null) {
            $this->addToMessage(__("Erreur lors de la récupération du numéro de l'opération."));
            return $this->end_treatment(__METHOD__, false);
        }
        $this->valF['numdossier'] = $annee."-".$ret;
        //
        return $this->end_treatment(__METHOD__, true);
    }

    /**
     *
     * @return string
     */
    function get_default_libelle() {
        return _("id")." ".$this->getVal($this->clePrimaire)." - ".__("numdossier")." ".$this->getVal("numdossier");
    }

    /**
     *
     * @return string
     */
    function getFormTitle($ent) {
        $out = $ent;
        if ($this->getVal($this->clePrimaire) != "") {
            $out .= "<span class=\"libelle\"> -> ".$this->get_default_libelle()."</span>";
        }
        return $out;
    }

    /**
     * MERGE_FIELDS - merge_fields_to_avoid_obj.
     * @var array
     */
    var $merge_fields_to_avoid_obj = array(
        "emplacement",
    );

    /**
     * MERGE_FIELDS - get_labels_merge_fields.
     *
     * @return array
     */
    function get_labels_merge_fields() {
        $labels = parent::get_labels_merge_fields();
        //
        $date_fields_to_format = array("date", );
        foreach ($date_fields_to_format as $date_field_to_format) {
            $labels[_($this->table)][$this->table.".".$date_field_to_format."_format_jj_mois_aaaa"] = $labels[_($this->table)][$this->table.".".$date_field_to_format]." (Format : 14 janvier 1978)";
        }
        //
        return $labels;
    }

    /**
     * MERGE_FIELDS - get_values_merge_fields.
     *
     * @return array
     */
    function get_values_merge_fields() {
        $values = parent::get_values_merge_fields();
        //
        $date_fields_to_format = array("date", );
        foreach ($date_fields_to_format as $date_field_to_format) {
            $values[$this->table.".".$date_field_to_format."_format_jj_mois_aaaa"] = '';
            $date_value_as_string = $this->getVal($date_field_to_format);
            if (!empty($date_value_as_string)
                && preg_match('|^[0-9]{4}-[0-9]{2}-[0-9]{2}$|', $date_value_as_string)) {
                //
                $date_value_as_object = DateTime::createFromFormat('Y-m-d', $date_value_as_string);
                $values[$this->table.".".$date_field_to_format."_format_jj_mois_aaaa"] = strftime('%d %B %Y', $date_value_as_object->getTimestamp());
            }
        }
        $inst_titre = $this->f->get_inst__om_dbform(array(
            "obj" => "titre_de_civilite",
            "idx" => $this->getVal("defunt_titre"),
        ));
        $values["operation.defunt_titre"] = $inst_titre->getVal("libelle");
        //
        $values["operation.observation"] = str_replace("\n", "<br/>", $values["operation.observation"]);
        return $values;
    }

    /**
     * MERGE_FIELDS - get_all_merge_fields.
     *
     * @return array
     */
    function get_all_merge_fields($type) {
        //
        $all = array(
            "emplacement" => "emplacement",
        );
        //
        switch ($type) {
            case 'values':
                //
                $values = $this->get_values_legacy_merge_fields();
                foreach ($all as $table => $field) {
                    $elem = $this->f->get_inst__om_dbform(array(
                        "obj" => $table,
                        "idx" => $this->getVal($field),
                    ));
                    $elem_values = $elem->get_merge_fields($type);
                    $values = array_merge($values, $elem_values);
                }
                return $values;
                break;
            case 'labels':
                //
                $labels = $this->get_labels_legacy_merge_fields();
                foreach ($all as $table => $field) {
                    $elem = $this->f->get_inst__om_dbform(array(
                        "obj" => $table,
                        "idx" => 0,
                    ));
                    $elem_labels = $elem->get_merge_fields($type);
                    $labels = array_merge($labels, $elem_labels);
                }
                return $labels;
                break;
            default:
                return array();
                break;
        }
    }

    /**
     * MERGE_FIELDS - get_values_legacy_merge_fields.
     *
     * @return array
     */
    function get_values_legacy_merge_fields() {
        $sql = sprintf(
            'SELECT
                e.emplacement as numero,
                e.complement as complement,
                e.superficie as superficie,
                c.cimetierelib as cimetiere,
                to_char(o.date,\'DD/MM/YYYY\') as dateoperation,
                o.heure as heureoperation,
                e.numero as num_adresse,
                voie_type.libelle as type_de_voie,
                v.voielib as voielib,
                zone_type.libelle as type_de_zone,
                z.zonelib as zonelib,
                to_char(e.datevente,\'DD/MM/YYYY\') as datevente,
                e.typeconcession as typeconcession,
                e.superficie as surface,
                o.numdossier as numdossier,
                o.societe_coordonnee as societe,
                o.particulier as particulier,
                o.pf_coordonnee as pompesfunebres,
                o.observation as observation
            FROM 
                %1$soperation o,
                %1$semplacement e,
                %1$scimetiere c,
                %1$szone z
                    LEFT JOIN %1$szone_type on z.zonetype = zone_type.zone_type,
                %1$svoie v
                    LEFT JOIN %1$svoie_type on v.voietype = voie_type.voie_type
            WHERE 
                z.cimetiere = c.cimetiere AND
                v.zone = z.zone AND
                e.voie = v.voie AND
                e.emplacement = o.emplacement AND
                o.operation = %2$s',
            DB_PREFIXE,
            intval($this->getVal($this->clePrimaire))
        );
        $res = $this->f->db->query($sql);
        $this->f->isDatabaseError($res);
        while ($row =& $res->fetchRow(DB_FETCHMODE_ASSOC)) {
            return $row;
        }
        return array();
    }

    /**
     * MERGE_FIELDS - get_labels_legacy_merge_fields.
     *
     * @return array
     */
    function get_labels_legacy_merge_fields() {
        return array(
            "legacy" => array(
                // Emplacement
                "numero" => "",
                "complement" => "",
                "typeconcession" => "",
                "superficie" => "",
                "num_adresse" => "",
                "datevente" => "",
                "surface" => "",
                // Opération
                "dateoperation" => "",
                "heureoperation" => "",
                "numdossier" => "",
                "societe" => "",
                "particulier" => "",
                "pompesfunebres" => "",
                "observation" => "",
                // Voie
                "type_de_voie" => "",
                "voielib" => "",
                // Zone
                "type_de_zone" => "",
                "zonelib" => "",
                // Cimetière
                "cimetiere" => "",
            ),
        );
    }
}
