<?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 - 002 - supprimer
        // -> suppression impossible
        unset($this->class_actions[2]);

        // ACTION - 01 - modifier
        //
        $this->class_actions[1]["condition"] = array("is_not_canceled","is_not_treated");

        // ACTION - 011 - pdf-edition
        //
        $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 - 012 - édition pdf operation trt
        $this->class_actions[12] = array(
            "identifier" => "pdf-edition_operation_trt",
            "permission_suffix" => "edition",
            "view" => "view_pdf_edition_operation_trt",
            "condition" => array(
                "exists",
                "has_edition"
            ),
        );
        // 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",
                "is_not_canceled"
            ),
        );
        // ACTION - 022 - annuler
        //
        $this->class_actions[22] = array(
            "identifier" => "annuler",
            "portlet" => array(
                "type" => "action-self",
                "libelle" => __("annuler"),
                "class" => "operation-annuler-16",
                "order" => 40,
                ),
            "permission_suffix" => "annuler",
            "method" => "annuler",
            "condition" => array(
                "exists",
                "is_not_canceled",
                "is_not_treated"
            ),
        );

        // ACTION - 30 - operation-redirect-categorie
        $this->class_actions[30] = array(
            "identifier" => "operation-redirect-categorie",
            "view" => "view_redirect_categorie",
            "permission_suffix" => "en_cours_tab",
            "condition" => array(
                "exists",
            ),
        );
    }

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

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

    /**
     * CONDITION - has_edition.
     *
     * @return boolean
     */
    function has_edition() {
        if (empty($this->getVal("edition_operation"))) {
            return false;
        }
        return true;
    }

    /**
     * VIEW - view_pdf_edition_operation_trt.
     *
     * @return void
     */
    function view_pdf_edition_operation_trt() {
        if ($this->has_edition() === true) {
            $pdfedition = $this->f->storage->get($this->getVal('edition_operation'));

            // Affichage du PDF
            $this->expose_pdf_output(
                $pdfedition['file_content'],
                $pdfedition['metadata']['filename']
            );

            return;
        }

        $this->correct = false;
        $this->f->displayMessage('error', __("Aucune édition enregistrée pour cette opération."));
        $this->retour();
        return false;
    }


    /**
     * 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";
        $qres = $this->f->get_all_results_from_db_query($sql, array(
            'origin' => __METHOD__
        ));
        //
        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 ($qres['row_count'] == 0) {
            echo(__("Aucun etat disponible pour cette operation.")."<br />");
        } else {
            foreach ($qres['result'] as $etat) {
                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 - annuler.
     *
     * @return void
     */

    function annuler($val = array()) {
        $this->begin_treatment(__METHOD__); // Début du traitement

        $idx = $this->getVal($this->clePrimaire);
        $valF = array("etat" => "annule");

        $qres = $this->f->execute_auto_db_query(
            'update',
            DB_PREFIXE.$this->table,
            $valF,
            $this->getCle($this->getVal($this->clePrimaire)),
            array("origin" => __METHOD__, "force_return" => true)
        );
        if ($qres["code"] === "KO") {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }
        $this->addToMessage("L'opération a été annulée avec succès.");
        return $this->end_treatment(__METHOD__, true);
    }

    /**
     * TREATMENT - valider.
     *
     * @return void
     */
    function valider($val = array()) {
        $this->begin_treatment(__METHOD__);
        /**
         * Récupération du paramétrage
         */
        //
        $inst_defunt = $this->f->get_inst__om_dbform(array(
            "obj" => 'defunt',
            "idx" => ']',
        ));

        $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, heure, 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;
        $qres =  $this->f->get_all_results_from_db_query($sql, array(
            'origin' => __METHOD__,
            'force_return' => true
        ));
        if ($qres['code'] === 'KO') {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }

        //
        foreach ($qres['result'] as $row) {
            //
            $dateoperation = $row['date'];
            $heureoperation = $row['heure'];
            //
            $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;
        $qres =  $this->f->get_all_results_from_db_query($sql, array(
            'origin' => __METHOD__,
            'force_return' => true
        ));
        if ($qres['code'] === 'KO') {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }
        $val = array();
        foreach ($qres['result'] as $row) {
            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';
            $val['x'] = NULL;
            $val['y'] = NULL;

            if ($categorie == 'inhumation') {
                $val['defunt']= $this->f->get_next_id_from_db_sequence(DB_PREFIXE.'defunt', array("origin" => __METHOD__))["result"];
                $val['emplacement']=$emplacement;
                $val['dateinhumation']=$dateoperation;
                $val['exhumation']='Non';
                $val['reduction']='Non';
                $val['taille'] =$inst_defunt->mapping_nature_defunt[$row['defunt_nature']]['taille'];

                $val['historique']=json_encode(array(array(
                    "id" => 1,
                    "categorie" => $categorie,
                    "type" => $categorie,
                    "dossier" => $dossier,
                    "date" => $dateoperation_fr,
                    "heure" => $heureoperation,
                    "emplacement_source" => $emplacement,
                    "emplacement_dest" => "",
                )));

                if (json_last_error() != JSON_ERROR_NONE) {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    $this->addToLog('Erreur lors de l\'encodage du json. Erreur :'.var_export(json_last_error_msg(), true));
                    return $this->end_treatment(__METHOD__, false);
                }

                $qres = $this->f->execute_auto_db_query('insert', DB_PREFIXE.'defunt', $val, null, array("origin" => __METHOD__, "force_return" => true));
                if ($qres['code'] === 'KO') {
                    $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'];
                $qres2 = $this->f->execute_db_query($sql, array(
                    'origin' => __METHOD__,
                    'force_return' => true
                ));
                if ($qres2['code'] === 'KO') {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    return $this->end_treatment(__METHOD__, false);
                }
            }

            $historique_decoded = array();
            $historique = $this->f->get_one_result_from_db_query(
                sprintf(
                    'SELECT
                        historique
                    FROM
                        %1$sdefunt
                    WHERE
                        defunt = %2$d',
                    DB_PREFIXE,
                    intval($row['defunt'])
                ),
                array(
                    "origin" => __METHOD__,
                )
            );
            $historique_decoded = json_decode(str_replace("'", '"', $historique['result']), true);

            if ($historique_decoded != null && json_last_error() != JSON_ERROR_NONE) {
                $this->correct = false;
                $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                $this->addToLog('Erreur lors du décodage du json. erreur :'.var_export(json_last_error_msg(), true));
                return $this->end_treatment(__METHOD__, false);
            }

            if ($historique_decoded == null) {
                $historique_decoded = array();
            }

            if ($categorie == 'reduction') {
                $lastElement = end($historique_decoded);
                $idValue = isset($lastElement['id']) ? $lastElement['id'] : 0;
                $reduction_exhumation = array(
                    "id" => $idValue + 1,
                    "categorie" => $categorie,
                    "type" => "exhumation",
                    "dossier" => $dossier,
                    "date" => $dateoperation_fr,
                    "heure" => $heureoperation,
                    "emplacement_source" => $emplacement,
                    "emplacement_dest" => ""
                );
                $reduction_inhumation = array(
                    "id" => $idValue + 2,
                    "categorie" => $categorie,
                    "type" => "inhumation",
                    "dossier" => $dossier,
                    "date" => $dateoperation_fr,
                    "heure" => $heureoperation,
                    "emplacement_source" => $emplacement,
                    "emplacement_dest" => ""
                );
                $historique_decoded[] = $reduction_exhumation;
                $historique_decoded[] = $reduction_inhumation;

                $val['historique'] = json_encode($historique_decoded);

                if (json_last_error() != JSON_ERROR_NONE) {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    $this->addToLog('Erreur lors de l\'encodage du json. Erreur :'.var_export(json_last_error_msg(), true));
                    return $this->end_treatment(__METHOD__, false);
                }

                $val['defunt']=$row['defunt'];
                $val['datereduction']=$dateoperation;
                $val['reduction']='Oui';
                $val['taille']=$taille_reduction;
                $val['nature']="boite";
                $cle= " defunt = ".$row['defunt'];
                $qres = $this->f->execute_auto_db_query('update', DB_PREFIXE.'defunt', $val, $cle, array("origin" => __METHOD__, "force_return" => true));
                if ($qres['code'] === 'KO') {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    return $this->end_treatment(__METHOD__, false);
                }
            }
            if ($categorie == 'transfert') {
                $lastElement = end($historique_decoded);
                $idValue = isset($lastElement['id']) ? $lastElement['id'] : 0;
                $transfert_exhumation = array(
                    "id" => $idValue +1,
                    "categorie" => $categorie,
                    "type" => "exhumation",
                    "dossier" => $dossier,
                    "date" => $dateoperation_fr,
                    "heure" => $heureoperation,
                    "emplacement_source" => $emplacement,
                    "emplacement_dest" => $emplacement_transfert
                );
                $transfert_inhumation = array(
                    "id" => $idValue + 2,
                    "categorie" => $categorie,
                    "type" => "inhumation",
                    "dossier" => $dossier,
                    "date" => $dateoperation_fr,
                    "heure" => $heureoperation,
                    "emplacement_source" => $emplacement,
                    "emplacement_dest" => $emplacement_transfert
                );
                $historique_decoded[] = $transfert_exhumation;
                $historique_decoded[] = $transfert_inhumation;

                $val['historique'] = json_encode($historique_decoded);

                if (json_last_error() != JSON_ERROR_NONE) {
                    $this->correct = false;
                    $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                    $this->addToLog('Erreur lors de l\'encodage du json. Erreur :'.var_export(json_last_error_msg(), true));
                    return $this->end_treatment(__METHOD__, false);
                }

                $val['emplacement']=$emplacement_transfert;
                $cle= " defunt = ".$row['defunt'];
                $qres = $this->f->execute_auto_db_query('update', DB_PREFIXE.'defunt', $val, $cle, array("origin" => __METHOD__, "force_return" => true));
                if ($qres['code'] === 'KO') {
                    $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;
        $qres = $this->f->get_one_result_from_db_query($sql, array("origin" => __METHOD__, "force_return" => true));
        if ($qres['code'] === 'KO') {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }
        $place = $qres['result'];
        if($place=='') $place=0;
        $sql = "update ".DB_PREFIXE."emplacement set placeoccupe=".$place.
               " where emplacement =".$emplacement;
        $qres2 =  $this->f->execute_db_query($sql, array(
            'origin' => __METHOD__,
            'force_return' => true
        ));
        if ($qres2['code'] === 'KO') {
            $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;
            $qres = $this->f->get_one_result_from_db_query($sql, array("origin" => __METHOD__, "force_return" => true));
            if ($qres['code'] === 'KO') {
                $this->correct = false;
                $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
                return $this->end_treatment(__METHOD__, false);
            }
            $place_transfert = $qres['result'];
            if ($place_transfert == '') {
                $place_transfert = 0;
            }
            //
            $sql = "update ".DB_PREFIXE."emplacement set placeoccupe=".$place_transfert." where emplacement =".$emplacement_transfert;
            $qres4 =  $this->f->execute_db_query($sql, array(
                'origin' => __METHOD__,
                'force_return' => true
            ));
            if ($qres4['code'] === 'KO') {
                $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;
        $qres3 =  $this->f->execute_db_query($sql, array(
            'origin' => __METHOD__,
            'force_return' => true
        ));
        if ($qres3['code'] === 'KO') {
            $this->correct = false;
            $this->addToMessage("Une erreur est survenue. Contactez votre administrateur.");
            return $this->end_treatment(__METHOD__, false);
        }

        // Ajout de l'édition pdf de l'opération
        $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";
        $qres = $this->f->get_all_results_from_db_query($sql, array(
            'origin' => __METHOD__
        ));

        $etat_result = array();

        foreach ($qres['result'] as $etat) {
            $etat_result['obj'][] = $etat['id'];
        }

        $pdfedition = $this->compute_pdf_output(
            "etat",
            implode(";", $etat_result["obj"]),
            null,
            $this->getVal($this->clePrimaire)
        );

        $metadata = array(
            "filename" => $pdfedition['filename'],
            "size" => strlen($pdfedition['pdf_output']),
            "mimetype" => "application/pdf",
        );
        $uid = $this->f->storage->create($pdfedition['pdf_output'], $metadata);

        $sql = "update ".DB_PREFIXE."operation SET edition_operation='".$uid."' where operation = ".$idx;
        $qres4 =  $this->f->execute_db_query($sql, array(
            'origin' => __METHOD__,
            'force_return' => true
        ));
        if ($qres4['code'] === 'KO') {
            $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",
            "operation.emplacement_transfert as emplacement_transfert",
            "operation.consigne_acces_transfert",
            "operation.demandeur",
            "'' 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.edition_operation",
            "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, '')
              ||'\n'||
              coalesce(numero_habilitation, '')
        ),
        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, '')
              ||'\n'||
              coalesce(numero_habilitation, '')
        ),
        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>";
    }

    function get_var_sql_forminc__sql_demandeur_by_emplacement() {
        return sprintf(
            "SELECT
                autorisation,
                CONCAT_WS(' ',
                    '(' ||
                        CASE WHEN nature IS NOT NULL AND nature = 'ayantdroit'
                            THEN
                                'ayant-droit'
                            ELSE
                                nature
                        END
                    || ')',
                    titre_de_civilite.libelle,
                    CASE WHEN autorisation.marital IS NOT NULL AND autorisation.marital != ''
                        THEN
                            autorisation.marital
                        ELSE
                            autorisation.nom
                    END,
                    prenom)
                FROM
                    %1\$sautorisation
                    LEFT JOIN %1\$stitre_de_civilite
                        ON autorisation.titre = titre_de_civilite.titre_de_civilite
                WHERE
                    emplacement = <id_emplacement>",
            DB_PREFIXE
        );
    }

    /**
     *
     * @return string
     */
    function get_var_sql_forminc__sql_demandeur_by_id() {
        return sprintf(
            "SELECT
                autorisation,
                CONCAT_WS(' ',
                    '(' ||
                        CASE WHEN nature IS NOT NULL AND nature = 'ayantdroit'
                            THEN
                                'ayant-droit'
                            ELSE
                                nature
                        END
                    || ')',
                    titre_de_civilite.libelle,
                    CASE WHEN autorisation.marital IS NOT NULL AND autorisation.marital != ''
                        THEN
                            autorisation.marital
                        ELSE
                            autorisation.nom
                    END,
                    prenom) as nom
                FROM
                    %1\$sautorisation
                    LEFT JOIN %1\$stitre_de_civilite
                        ON autorisation.titre = titre_de_civilite.titre_de_civilite
                WHERE
                    autorisation = <idx>
            ",
            DB_PREFIXE
        );
    }


    /**
     * 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);
        //
        $form->setType("edition_operation", "hidden");
        $form->setType('etat', 'selecthiddenstatic');

        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('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 ($this->categorie != 'transfert') {
                $form->setType("consigne_acces_transfert", "hidden");
            }
        }

        if ($maj == 2) {
			$form->setType('emplacement', 'hiddentstatic'); // pb de cle numerique
			$form->setType('emplacement_transfert', 'hiddentstatic'); // pb de cle numerique
			$form->setType('demandeur', 'selectstatic');
		}

        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');
            $form->setType('emplacement', 'link');

            if ($this->categorie != 'transfert') {
                $form->setType("consigne_acces_transfert", "hidden");
            }
        }

        if ($maj == 21 || $maj == 22) {
            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('demandeur', '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");
            if ($this->categorie == 'transfert') {
                $form->setType("emplacement_transfert", "selectstatic");
                $form->setType("consigne_acces_transfert", "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'));
            }
        }
    }

    /**
     * Permet de rediriger vers la bonne catégorie d'opération lors de la consultation
     * d'un enregistrement à partir du widget ou du listing des opérations en cours
     *
     * @return void
     */
    function view_redirect_categorie() {
        $inst_emplacement = $this->f->get_inst__om_dbform(array(
            "obj" => "emplacement",
            "idx" => $this->getVal('emplacement'),
        ));

        $emplacement_nature = $inst_emplacement->getVal('nature');
        $obj = $this->getVal('categorie');
        if ($this->getVal('categorie') != "transfert") {
            $obj = $this->getVal('categorie').'_'.$emplacement_nature;
        }
        header('Location: '.OM_ROUTE_FORM.'&obj='.$obj.'&action=3&specific_origin=operation_en_cours&idx='.$this->getVal('operation'));
    }

    /**
     *
     * @return string
     */
    function compose_form_url($case = "form", $override = array()) {
        //
        $out = parent::compose_form_url($case, $override);
        //
        if ($this->f->get_submitted_get_value("specific_origin") != null) {
            $out .= "&amp;specific_origin=".$this->f->get_submitted_get_value("specific_origin");
        }
        //
        return $out;
    }

    /**
     * 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(
                "'('||emplacement.emplacement||')'" => __("identifiant de l'emplacement (ex : (12))"),
                "emplacement.famille" => __("famille (ex : DUPONT)"),
                "'n°'||emplacement.numeroacte" => __("n° d'acte (ex : n°12512)"),
            ),
            // Tables liées
            'jointures' => array(
                'voie
                    ON emplacement.voie=voie.voie',
                'voie_type
                    ON voie.voietype=voie_type.voie_type',
                'zone
                    ON voie.zone=zone.zone',
                'zone_type
                    ON zone.zonetype=zone_type.zone_type',
                'cimetiere
                    ON zone.cimetiere=cimetiere.cimetiere',
            ),
            // Colonnes ID et libellé du champ
            // (si plusieurs pour le libellé alors une concaténation est faite)
            'identifiant' => "emplacement.emplacement",
            //
            'libelle' => array(
                "concat(
                    emplacement.nature,
                    ' (',
                    emplacement.emplacement,
                    ') ',
                    ' - Famille : ',
                    emplacement.famille,
                    ' - acte n°',
                    emplacement.numeroacte,
                    '<br/><i>',
                    emplacement.numero,
                    ' ',
                    emplacement.complement,
                    ' ',
                    voie_type.libelle,
                    ' ',
                    voie.voielib,
                    ' [',
                    zone_type.libelle,
                    ' ',
                    zone.zonelib,
                    '][',
                    cimetiere.cimetierelib,
                    ']</i>'
                )",
            ),
            //
            'where' => ($this->emplacement_nature === null ? "" : "nature='".$this->emplacement_nature."'"),
            //
            'group_by' => array(
                'emplacement.emplacement',
                'voie_type.libelle',
                'voie.voielib',
                'zone_type.libelle',
                'zone.zonelib',
                'cimetiere.cimetierelib',
            ),
            //
            'link_selection' => false,
        );
    }

    /**
     * SETTER_FORM - setSelect.
     *
     * @return void
     */
    function setSelect(&$form, $maj, &$dnu1 = null, $dnu2 = null) {
        parent::setSelect($form, $maj);

        // emplacement_transfert
        $this->init_select(
            $form,
            $this->f->get_db_reference(),
            $maj,
            null,
            "emplacement_transfert",
            $this->get_var_sql_forminc__sql("emplacement"),
            $this->get_var_sql_forminc__sql("emplacement_transfert_by_id"),
            false
        );

        // Filtre par emplacement pour demandeur
        $emplacement = "";
        $champ = "emplacement";
        if (isset($_POST[$champ])) {
            $emplacement = $_POST[$champ];
        } elseif ($this->getParameter($champ) != "") {
            $emplacement = $this->getParameter($champ);
        } elseif (isset($form->val[$champ])) {
            $emplacement = $form->val[$champ];
        } elseif ($this->getVal($champ) != null) {
            $emplacement = $this->getVal($champ);
        }
        if ($emplacement == "") {
            $emplacement = -1;
        }
        $this->init_select(
            $form,
            $this->f->get_db_reference(),
            $maj,
            null,
            "demandeur",
            str_replace(
                '<id_emplacement>',
                $emplacement,
                $this->get_var_sql_forminc__sql("demandeur_by_emplacement")
            ),
            $this->get_var_sql_forminc__sql("demandeur_by_id"),
            false
        );

        $tab_etat = array (
            array("actif", "trt", "annule",),
            array(__("actif"), __("trt"), __("annule"),),
        );
        $form->setSelect("etat", $tab_etat);


        if($maj<2){
            // AUTOCOMPLETE emplacement
            $form->setSelect(
                "emplacement",
                $this->get_widget_config__emplacement__autocomplete()
            );

            // entreprise_coordonnee
            $contenu = array();
            $qres = $this->f->get_all_results_from_db_query(
                $this->get_var_sql_forminc__sql("entreprise"),
                array(
                    'origin' => __METHOD__,
                    'mode' => DB_FETCHMODE_ORDERED
                )
            );
            $contenu[0][0] = '';
            $contenu[1][0] = __('choisir')."&nbsp;".__('entreprise');
            $k = 1;
            foreach ($qres['result'] as $row) {
                $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();
            $qres = $this->f->get_all_results_from_db_query(
                $this->get_var_sql_forminc__sql("pf"),
                array(
                    'origin' => __METHOD__,
                    'mode' => DB_FETCHMODE_ORDERED
                )
            );
            $contenu[0][0] = '';
            $contenu[1][0] = __('choisir')."&nbsp;".__('pf');
            $k = 1;
            foreach ($qres['result'] as $row) {
                $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);
        }
        // MODE CONSULTER
        if ($maj == 3) {
            $inst_emplacement = $this->f->get_inst__om_dbform(array(
                "obj" => "emplacement",
                "idx" => $this->getVal("emplacement"),
            ));
            $params = array();
            $params['obj'] = $inst_emplacement->getVal('nature');
            $params['libelle'] = $inst_emplacement->getVal('nature').' ('.$inst_emplacement->getVal('emplacement').') - Famille : '.$inst_emplacement->getval('famille').' - acte n°'.$inst_emplacement->getVal('numeroacte')."<br/><i>".$inst_emplacement->getval('adresse')."</i>";
            $params['title'] = "Consulter l'emplacement";
            $params['idx'] = $this->getVal("emplacement");
            $form->setSelect("emplacement", $params);
        }
    }

    /**
     *
     */
    function set_form_specificity(&$form, $maj) {
        //
        parent::set_form_specificity($form, $this->getParameter("maj"));

        /**
         * Gestion du champ demandeur
         */
        //
        // En mode AJOUTER OU MODIFIER
        if ($this->getParameter("maj") < 2) {
            // A la modification du champ emplacement alors on met à jour le champ
            // demandeur pour afficher les contacts liés à l'emplacement.
            $form->setOnchange('emplacement',
                "filterSelect(this.value, 'demandeur', 'emplacement', 'operation');");
        }
    }

    /**
     * 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"));
        if ($this->categorie == 'transfert') {
            $form->setLib('consigne_acces',__("consigne d'accès (origine)"));
        }
        $form->setLib('consigne_acces_transfert',__("consigne d'accès (destination)"));
        $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
                        if ($this->categorie == "transfert") {
                            $this->form->setBloc("emplacement", "D", __("emplacement"), "span10");
                                $this->form->setBloc("emplacement", "D",  "", "span11");
                                $this->form->setBloc("consigne_acces", "F");

                                $this->form->setBloc("emplacement_transfert", "D", "", "span11");
                                $this->form->setBloc("demandeur", "F");
                            $this->form->setBloc("demandeur", "F");
                        }
                        if ($this->categorie != "transfert") {
                            $this->form->setBloc("emplacement", "D",  __("emplacement"), "span12");
                            $this->form->setBloc("demandeur", "F");
                        }
                        //// BLOC emplacement - END
                    $this->form->setBloc("demandeur", "F");
                $this->form->setBloc("demandeur", "F");
            $this->form->setBloc("demandeur", "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.
         */
        $qres_search_path = $this->f->execute_db_query("set search_path=public;", array(
            'origin' => __METHOD__,
            'force_return' => true
        ));
        if ($qres_search_path['code'] === 'KO') {
            return null;
        }
        $query_sequence_exists = sprintf(
            'SELECT
                *
            FROM
                pg_class
            WHERE
                relkind = \'S\'
                AND oid::regclass::text = \'%s\'
            ;',
            $sequence_name
        );
        $qres = $this->f->get_one_result_from_db_query($query_sequence_exists, array("origin" => __METHOD__, "force_return" => true));
        if ($qres['code'] === 'KO') {
            return null;
        }
        $res_sequence_exists = $qres['result'];

        /**
         * 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->create_db_sequence($table_name);
            if ($res["code"] === "KO") {
                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
            );
            $qres = $this->f->get_one_result_from_db_query($sql_last_numdossier, array("origin" => __METHOD__, "force_return" => true));
            if ($qres['code'] === 'KO') {
                return null;
            }
            $last_numdossier = $qres['result'];
            if (intval($last_numdossier) !== 0) {
                $sql_maj_seq = sprintf('SELECT setval(\'%s\',%s);', $sequence_name, intval($last_numdossier));
                $qres_seq = $this->f->execute_db_query($sql_maj_seq, array(
                    'origin' => __METHOD__,
                    'force_return' => true
                ));
                if ($qres_seq['code'] === 'KO') {
                    return null;
                }
            }
        }

        /**
         * Récupère le prochain numéro de la séquence et le retourne
         */
        $qres_seq = $this->f->get_next_id_from_db_sequence($table_name, array("origin" => __METHOD__));
        if ($qres_seq["code"] === "KO") {
            return false;
        }
        return intval($qres_seq["result"]);
    }

    /**
     * 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.demandeur"] = "";
        if ($this->getVal('demandeur') != NULL) {
            $demandeur_libelle = $this->f->get_all_results_from_db_query(
                str_replace(
                    '<idx>',
                    $this->getVal('demandeur'),
                    $this->get_var_sql_forminc__sql('demandeur_by_id')
                ),
                array(
                        "origin" => __METHOD__,
                    )
            );
            $values["operation.demandeur"] = $demandeur_libelle['result'][0]['nom'];
        }
        //
        $values["operation.observation"] = str_replace("\n", "<br/>", $values["operation.observation"]);
        return $values;
    }

    /**
     * MERGE_FIELDS - get_all_merge_fields.
     *
     * - Gestion spécifique pour emplacement_transfert
     *
     * @return array
     */
    function get_all_merge_fields($type) {
        //
        $all = array(
            "emplacement" => "emplacement",
            "emplacement_transfert" => "emplacement_transfert",
            "autorisation" => "demandeur"
        );
        //
        switch ($type) {
            case 'values':
                //
                $values = $this->get_values_legacy_merge_fields();
                foreach ($all as $table => $field) {
                    // Gestion de l'instanciation pour les emplacement_transfert (objet emplacement)
                    $elem = $this->f->get_inst__om_dbform(array(
                        "obj" => $table == 'emplacement_transfert' ? 'emplacement' : $table,
                        "idx" => $this->getVal($field),
                    ));
                    $elem_values = $elem->get_merge_fields($type);
                    // Lors du passage sur les champs de l'emplacement_transfert
                    if ($field == 'emplacement_transfert') {
                        foreach ($elem_values as $key => $value) {
                            // On remplace le terme emplacement par emplacement_transfert dans les clés du tableau récupéré
                            $elem_values[str_replace('emplacement', 'emplacement_transfert', $key)] = $value;
                            // On enlève toutes les clés contenant emplacement.xxx
                            unset($elem_values[$key]);
                        }
                    }
                    $values = array_merge($values, $elem_values);
                }
                return $values;
                break;
            case 'labels':
                //
                $labels = $this->get_labels_legacy_merge_fields();
                foreach ($all as $table => $field) {
                    // Gestion de l'instanciation pour les emplacement_transfert (objet emplacement)
                    $elem = $this->f->get_inst__om_dbform(array(
                        "obj" => $table == 'emplacement_transfert' ? 'emplacement' : $table,
                        "idx" => 0,
                    ));
                    $elem_labels = $elem->get_merge_fields($type);
                    if ($field == 'emplacement_transfert') {
                        foreach ($elem_labels as $key => $values) {
                            foreach ($values as $keyval => $value) {
                                $elem_labels['emplacement_transfert'][str_replace('emplacement', 'emplacement_transfert', $keyval)] = $value;
                            }
                        }
                    }
                    $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))
        );
        $qres = $this->f->get_all_results_from_db_query($sql, array(
            'origin' => __METHOD__
        ));
        foreach ($qres['result'] as $row) {
            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" => "",
            ),
        );
    }

    /**
     * Retourne le lien de retour (VIEW formulaire et VIEW sousformulaire).
     *
     * @param string $view Appel dans le contexte de la vue 'formulaire' ou de
     *                     la vue 'sousformulaire'.
     *
     * @return string
     */
    function get_back_link($view = "formulaire") {
        // Récupération du mode de l'action
        $crud = $this->get_action_crud();
        //
        if ($view === "formulaire") {
            $tab_script = OM_ROUTE_TAB;
            $form_script = OM_ROUTE_FORM;
        } elseif ($view === "sousformulaire") {
            $tab_script = OM_ROUTE_SOUSTAB;
            $form_script = OM_ROUTE_SOUSFORM;
        }
        // On revient au tableau, ou au formulaire si le param retour vaut 'form'
        // et on n'a pas validé avec succès une suppression
        if ($this->get_back_target() === "form") {
            //
            $href = $form_script;
        } else {
            $href = $tab_script;
        }
        //
        if ($this->get_back_target() === "tab" && $this->f->get_submitted_get_value('specific_origin') == "operation_en_cours") {
            $href .= "&obj=operation_en_cours";
        } else if ($this->getParameter('maj') == '12') {
            $href .= "&obj=operation_trt";
        } else {
            $href .= "&obj=".$this->get_absolute_class_name();
        }

        //
        if ($this->get_back_target() === "form") {
            if (($crud === 'create'
                 || ($crud === null
                     && $this->getParameter('maj') == 0))
                && $this->correct == true) {
                $href .= "&idx=".$this->valF[$this->clePrimaire];
            } else {
                $href .= "&idx=".$this->getParameter("idx");
            }
            $href .= "&action=3";
        }
        //
        if ($view === "formulaire") {
            $href .= "&advs_id=".$this->getParameter("advs_id");
            $href .= "&premier=".$this->getParameter("premier");
            $href .= "&tricol=".$this->getParameter("tricol");
            $href .= "&valide=".$this->getParameter("valide");
            $href .= "&specific_origin=".$this->f->get_submitted_get_value("specific_origin");
        } elseif ($view === "sousformulaire") {
            $href .= "&retourformulaire=".$this->getParameter("retourformulaire");
            $href .= "&idxformulaire=".$this->getParameter("idxformulaire");
            $href .= "&advs_id=".$this->getParameter("advs_id");
            $href .= "&premier=".$this->getParameter("premiersf");
            $href .= "&tricol=".$this->getParameter("tricolsf");
            $href .= "&valide=".$this->getParameter("valide");
        }
        return $href;
    }
}
