<?php
/**
 * Ce script définit la classe *courrier*.
 * 
 * Surcharge pour :
 * 
 * - Gestion du contexte
 *
 * @package opencimetiere
 * @version SVN : $Id$
 */

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

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

    /**
     * Liste les contextes possibles
     * 
     * - Gestion du contexte
     * 
     * Les première et dernière valeurs doivent impérativement
     * être "" et "autre" respectivement.
     */
    const CONTEXTE_VAL = array(
       "",
       "contrat",
       "reprise_abandon",
       "travaux",
       "autre",
    );

    /**
     *
     */
    function __construct($id, &$dnu1 = null, $dnu2 = null) {
        $this->constructeur($id);
        // XXX Modifier la base de données en conséquence ?
        $this->required_field[] = "lettretype";
        $this->required_field[] = "contexte";
    }

    /**
     * Définition des actions disponibles sur la classe.
     *
     * @return void
     */
    function init_class_actions() {
        parent::init_class_actions();
        // ACTION - 031 - edition pdf
        // Retourne le pdf généré
        $this->class_actions[31] = array(
            "identifier" => "pdf-edition",
            "portlet" => array(
                "type" => "action-blank",
                "libelle" => __("édition pdf"),
                "order" => 120,
                "class" => "pdf-16",
            ),
            "view" => "view_pdf_edition",
            "permission_suffix" => "pdf_edition",
        );
    }

    /**
     * GETTER_FORMINC - champs.
     * 
     * - Gestion du contexte
     *
     * @return array
     */
    function get_var_sql_forminc__champs() {
        return array(
            "courrier",
            "emplacement",
            "destinataire",
            "contexte",
            "contrat",
            "reprise_abandon",
            "travaux",
            "datecourrier",
            "lettretype",
            "objet",
            "complement",
        );
    }

    /**
     *
     * @return string
     */
    function get_default_libelle() {
        return _("id")." ".$this->getVal($this->clePrimaire)." - ".$this->getVal("lettretype")." du ".$this->dateDBToForm($this->getVal("datecourrier"));
    }

    /**
     * GETTER_FORMINC - sql_contrat.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_contrat() {
        // Filtre des contrats par emplacement
        $filter = "";
        if ($this->is_in_context_of_foreign_key('emplacement', $this->retourformulaire)) {
            $filter = " WHERE emplacement=".intval($this->getParameter("idxformulaire"))." ";
        }
        if($this->is_in_context_of_foreign_key('contrat', $this->retourformulaire)) {
            $filter = " WHERE contrat=".intval($this->getParameter("idxformulaire"))." ";
        }
        return "SELECT contrat.contrat, contrat.origine||' du '||to_char(contrat.datedemande, 'DD/MM/YYYY') as lib FROM ".DB_PREFIXE."contrat ".$filter." ORDER BY contrat";
    }

    /**
     * GETTER_FORMINC - sql_travaux.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_travaux() {
        // Filtre des travaux par emplacement
        $filter = "";
        if ($this->is_in_context_of_foreign_key('emplacement', $this->retourformulaire)) {
            $filter = " WHERE emplacement=".intval($this->getParameter("idxformulaire"))." ";
        }
        if($this->is_in_context_of_foreign_key('travaux', $this->retourformulaire)) {
            $filter = " WHERE travaux=".intval($this->getParameter("idxformulaire"))." ";
        }
        return "SELECT travaux.travaux, travaux_nature.libelle||' du '||to_char(travaux.datedebinter, 'DD/MM/YYYY')||' par '||entreprise.nomentreprise as lib FROM ".DB_PREFIXE."travaux INNER JOIN ".DB_PREFIXE."travaux_nature ON travaux.naturetravaux = travaux_nature.travaux_nature INNER JOIN ".DB_PREFIXE."entreprise ON travaux.entreprise = entreprise.entreprise ".$filter." ORDER BY travaux";
    }

    /**
     * GETTER_FORMINC - sql_contrat_by_id.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_contrat_by_id() {
        //
        return "SELECT contrat.contrat, contrat.origine||' du '||to_char(contrat.datedemande, 'DD/MM/YYYY') as lib FROM ".DB_PREFIXE."contrat WHERE contrat = <idx>";
    }

    /**
     * GETTER_FORMINC - sql_destinataire.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_destinataire() {
        // Filtre des destinataires par emplacement
        $filter = "";
        if ($this->is_in_context_of_foreign_key('emplacement', $this->retourformulaire)) {
            $filter = " WHERE emplacement=".intval($this->getParameter("idxformulaire"))." ";
        }
        if($this->is_in_context_of_foreign_key('contrat', $this->retourformulaire)) {
            $inst_contrat = $this->f->get_inst__om_dbform(array(
                "obj" => "contrat",
                "idx" => $this->getParameter("idxformulaire"),
            ));
            if ($inst_contrat->exists() === true) {
                $filter = " WHERE emplacement=".intval($inst_contrat->getVal("emplacement"))." ";
            }
        }
        return sprintf(
            'SELECT
                autorisation.autorisation,
                \'(\'||autorisation.nature||\') \'|| 
                    CASE WHEN autorisation.marital IS NULL 
                        THEN autorisation.nom 
                        ELSE autorisation.marital 
                    END ||\' \'||coalesce(prenom, \'-\') as nom
            FROM
                %1$sautorisation
            %2$s
            ORDER BY
                autorisation',
            DB_PREFIXE,
            $filter
        );
    }

    /**
     * GETTER_FORMINC - sql_destinataire.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_destinataire_by_id() {
        return sprintf(
            'SELECT
                autorisation.autorisation,
                \'(\'||autorisation.nature||\') \'|| 
                    CASE WHEN autorisation.marital IS NULL 
                        THEN autorisation.nom 
                        ELSE autorisation.marital 
                    END ||\' \'||coalesce(prenom, \'-\') as nom
            FROM
                %1$sautorisation
            WHERE autorisation = <idx>
            ORDER BY
                autorisation',
            DB_PREFIXE
        );
    }

    /**
     * GETTER_FORMINC - sql_om_lettretype.
     * 
     * - Gestion du contexte
     *
     * @return string
     */
    function get_var_sql_forminc__sql_om_lettretype_by_contexte() {
        return sprintf(
            "SELECT 
                om_lettretype.id, (om_lettretype.id||' '||om_lettretype.libelle) as libelle
            FROM
                %1\$som_lettretype
            WHERE 
                om_lettretype.actif is TRUE 
                AND om_lettretype.id LIKE '<contexte>'
            ORDER BY id",
            DB_PREFIXE
        );
    }

    /**
     * GETTER_FORMINC - sql_om_lettretype.
     * 
     * - Gestion du contexte
     * 
     * Permet de récupérer les lettres types associées au contexte autre
     * automatiquement en fonction de la constante CONTEXTE_VAL
     *
     * @return string
     */
    function get_var_sql_forminc__sql_om_lettretype_autre() {
        $where_condition = "";
        for ($i=1; $i < count(self::CONTEXTE_VAL) - 1; $i++) {
            $where_condition .= "AND om_lettretype.id NOT LIKE '" . self::CONTEXTE_VAL[$i] . "_%%' ";
        }
        return sprintf(
            "SELECT 
                om_lettretype.id, (om_lettretype.id||' '||om_lettretype.libelle) as libelle
            FROM
                %1\$som_lettretype
            WHERE 
                om_lettretype.actif is TRUE
                %2\$s
            ORDER BY id",
            DB_PREFIXE,
            $where_condition
        );
    }

    /**
     * GETTER_FORMINC - sql_om_lettretype_by_id.
     *
     * @return string
     */
    function get_var_sql_forminc__sql_om_lettretype_by_id() {
        return "SELECT om_lettretype.id, (om_lettretype.id||' '||om_lettretype.libelle) as libelle FROM ".DB_PREFIXE."om_lettretype WHERE om_lettretype.id='<idx>'";
    }

    /**
     *
     * @return string
     */
    function get_var_sql_forminc__sql_reprise_abandon_by_emplacement() {
        return sprintf(
            'SELECT 
                reprise_abandon.reprise_abandon, (\'id \' || reprise_abandon.reprise_abandon || \' - du \' || to_char(reprise_abandon.datecreation ,\'DD/MM/YYYY\'))
            FROM 
                %1$sreprise_abandon
            WHERE
                reprise_abandon.emplacement = <emplacement_id>
            ORDER BY reprise_abandon.emplacement ASC',
            DB_PREFIXE
        );
    }

    /**
     *
     * @return string
     */
    function get_var_sql_forminc__sql_reprise_abandon_by_id() {
        return sprintf(
            'SELECT 
                reprise_abandon.reprise_abandon, (\'id \' || reprise_abandon.reprise_abandon || \' - du \' || to_char(reprise_abandon.datecreation ,\'DD/MM/YYYY\'))
            FROM 
                %1$sreprise_abandon
            WHERE
                reprise_abandon = <idx>',
            DB_PREFIXE
        );
    }


    /**
     * SETTER_FORM - setType.
     * 
     * - Gestion du contexte
     *
     * @return void
     */
    function setType(&$form, $maj) {
        parent::setType($form, $maj);
        // Affichage de l'id de la table dans tous les cas
        $form->setType('courrier', 'hiddenstatic');
        // On cache l'id de l'emplacement dans tous les cas
        $form->setType('emplacement', 'hidden');
        //
        if ($maj == 0 || $maj == 1) {
            $form->setType('lettretype', 'select');
            $form->setType('complement', 'html');
            $form->setType('contexte', 'select');
            
        }
        //
        if ($maj == 3) {
            $form->setType('lettretype', 'selectstatic');
            $form->setType('contexte', 'selectstatic');
            $form->setType('contrat', 'selectstatic');
            $form->setType('reprise_abandon', 'selectstatic');
        }

        if($this->is_in_context_of_foreign_key('contrat', $this->retourformulaire)) {
            $form->setType('contexte', 'selecthiddenstatic');
        }

        // Gestion de l'affichage des champs liés au contexte
        foreach (self::CONTEXTE_VAL as $contexte) {
            if ($maj == 3 || $maj == 2) {
                $form->setType($contexte, "hidden");
                if ($this->getVal('contexte') == $contexte || $form->val['contexte'] == $contexte) {
                    $form->setType($contexte, "selectstatic");
                }
            }
            if ($this->is_in_context_of_foreign_key('contrat', $this->retourformulaire) && $maj < 2) {
                $form->setType($contexte, "hidden");
                if ($this->getVal('contexte') == $contexte || $form->val['contexte'] == $contexte) {
                    $form->setType($contexte, "selecthiddenstatic");
                }
            }
        }
    }

    /**
     * SETTER_FORM - setValsousformulaire (setVal).
     *
     * @return void
     */
    function setValsousformulaire(&$form, $maj, $validation, $idxformulaire, $retourformulaire, $typeformulaire, &$dnu1 = null, $dnu2 = null) {
        parent::setValsousformulaire($form, $maj, $validation, $idxformulaire, $retourformulaire, $typeformulaire);
        //
        if ($validation == 0) {
            //
            if ($maj == 0) {
                // Affichage de l'id de la table avec la valeur [...]
                $form->setVal('courrier', "[...]");
                //
                $form->setVal('datecourrier', date('Y-m-d'));
            }
        }

        if($this->is_in_context_of_foreign_key('contrat', $this->retourformulaire)) {
            $form->setVal('contexte', 'contrat');
        }
    }

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

        // Récupération du contexte
        $contexte = $this->get_id_in_any_context($form, "contexte");

        // S'il n'y a pas de contexte sélectionné alors on vide la liste déroulante des lettres type
        $form->setSelect("lettretype", array(
            array(''),
            array(__("choisir ").__("lettretype"),),
        ));

        if (!empty($contexte) && $contexte != -1) {
            $this->init_select(
                $form, 
                $this->f->db,
                $maj,
                null,
                "lettretype",
                $contexte == 'autre' ? 
                $this->get_var_sql_forminc__sql("om_lettretype_autre") :
                str_replace(
                    '<contexte>',
                    $contexte.'_%%',
                    $this->get_var_sql_forminc__sql("om_lettretype_by_contexte")
                ),
                $this->get_var_sql_forminc__sql("om_lettretype_by_id"),
                false
            );
        }

        $this->init_select(
            $form,
            $this->f->db,
            $maj,
            null,
            "destinataire",
            $this->get_var_sql_forminc__sql("destinataire"),
            str_replace('<idx>',
                    $this->getVal('destinataire'),
                    $this->get_var_sql_forminc__sql("destinataire_by_id")),
            false
        );

        // Gestion du select des contextes
        $form->setSelect("contexte", array(
            self::CONTEXTE_VAL,
            array( 
                __("choisir ").__("contexte"),
                __("contrat"),
                __("reprise_abandon"),
                __("travaux"),
                __("autre"),
            ),
        ));

        if ($this->is_in_context_of_foreign_key('emplacement', $this->retourformulaire)) {
            $this->init_select(
                $form, 
                $this->f->db,
                $maj,
                null,
                "reprise_abandon",
                str_replace(
                    '<emplacement_id>',
                    $this->get_id_in_any_context($form, "emplacement"),
                    $this->get_var_sql_forminc__sql("reprise_abandon_by_emplacement")
                ),
                $this->get_var_sql_forminc__sql("reprise_abandon_by_id"),
                false
            );
        }
    }

    /**
     * Permet de récupérer un identifiant quelque soit le contexte
     * dans lequel on se trouve
     * 
     * - Gestion du contexte
     * 
     * @return integer
     */
    function get_id_in_any_context($form, $champ) {
        if (isset($_POST[$champ])) {
            return $_POST[$champ];
        } elseif($this->getParameter($champ) != "") {
            return $this->getParameter($champ);
        } elseif(isset($form->val[$champ])) {
            return $form->val[$champ];
        } elseif($this->getVal($champ) != null) {
            return $this->getVal($champ);
        }
        return -1;
    }


    /**
     * SETTER_FORM - setValF.
     *
     * @return void
     */
    function setvalF($val = array()) {
        parent::setvalF($val);
        // Si on est dans le context d'un contrat alors on récupère l'emplacemente associé à ce contrat
        if($this->is_in_context_of_foreign_key('contrat', $this->retourformulaire)) {
            $inst_contrat = $this->f->get_inst__om_dbform(array(
                "obj" => "contrat",
                "idx" => $this->getParameter("idxformulaire"),
            ));
            $this->valF["emplacement"] = $inst_contrat->getVal("emplacement");
        }
    }

    /**
     * SETTER_FORM - setOnchange
     * 
     * - Gestion du contexte
     * 
     * @return void
     */
    function setOnchange(&$form, $maj) {
        parent::setOnchange($form, $maj);

        // Gestion du contexte
        $form->setOnchange('contexte', 'treatment_courrier_contexte_field(this)');
    }

    /**
     * SETTER_FORM - setLib.
     *
     * @return void
     */
    function setLib(&$form, $maj) {
        parent::setLib($form, $maj);
        //
        $form->setLib($this->clePrimaire, __("id"));
    }

    /**
     * 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("courrier", "D", "", "emplacement-form courrier-form form-action-".$maj);

        //// LIGNE ID - START
        $this->form->setBloc("courrier", "D", "", "row-fluid");
            //// BLOC ID - START
            $this->form->setBloc("courrier", "D", "", $bloc_global_width_full." emplacement-bloc emplacement-bloc-identification ");
                //// BLOC ID - START
                $this->form->setBloc("courrier", "D", "", "span12");
                $this->form->setBloc("emplacement", "F");
                //// BLOC ID - END
            $this->form->setBloc("emplacement", "F");
            //// BLOC ID - END
        $this->form->setBloc("emplacement", "F");
        //// LIGNE ID - END

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

    /**
     * VIEW - view_pdf_edition.
     *
     * @return void
     */
    function view_pdf_edition() {
        $this->checkAccessibility();
        // Génération du PDF
        $pdfedition = $this->compute_pdf_output(
            "lettretype",
            $this->getVal("lettretype"),
            null,
            $this->getVal($this->clePrimaire)
        );
        // Affichage du PDF
        $this->expose_pdf_output(
            $pdfedition['pdf_output'],
            $pdfedition['filename']
        );
    }

    /**
     * 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("datecourrier", );
        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("datecourrier", );
        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());
            }
        }
        //
        return $values;
    }

    /**
     * MERGE_FIELDS - get_all_merge_fields.
     *
     * @return array
     */
    function get_all_merge_fields($type) {
        //
        $all = array(
            "emplacement" => "emplacement",
            "autorisation" => "destinataire",
            "contrat" => "contrat",
            "reprise_abandon" => "reprise_abandon",
            "travaux" => "travaux",
        );
        //
        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() {
        $query = sprintf(
            'SELECT
                to_char(courrier.datecourrier, \'DD/MM/YYYY\') as datecourrier,
                courrier.complement,
                titre_de_civilite.code as titre,
                autorisation.nom, 
                autorisation.prenom, 
                autorisation.marital,
                autorisation.adresse1,
                autorisation.adresse2,
                autorisation.cp,
                autorisation.ville,
                autorisation.emplacement,
                autorisation.nature as autorisation_nature,
                emplacement.nature,
                emplacement.superficie,
                emplacement.numero as emplacement_adresse_numero,
                emplacement.complement as emplacement_adresse_complement,
                emplacement.typeconcession as typeconcession,
                voie_type.libelle as type_de_voie,
                voielib,
                zone_type.libelle as type_de_zone,
                zonelib,
                emplacement.terme, 
                emplacement.numeroacte,
                to_char(emplacement.dateterme,\'DD/MM/YYYY\') as dateterme,
                to_char(emplacement.datevente,\'DD/MM/YYYY\') as datevente,
                to_char(emplacement.daterenouvellement,\'DD/MM/YYYY\') as daterenouvellement,
                emplacement.famille,
                emplacement.duree,
                emplacement.placeconstat,
                cimetierelib

            FROM
                %1$scourrier
                LEFT JOIN %1$sautorisation on courrier.destinataire = autorisation.autorisation
                LEFT JOIN %1$stitre_de_civilite on autorisation.titre = titre_de_civilite.titre_de_civilite
                LEFT JOIN %1$semplacement on courrier.emplacement = emplacement.emplacement
                LEFT JOIN %1$svoie on emplacement.voie = voie.voie
                LEFT JOIN %1$svoie_type on voie.voietype = voie_type.voie_type
                LEFT JOIN %1$szone on voie.zone = zone.zone
                LEFT JOIN %1$szone_type on zone.zonetype = zone_type.zone_type
                LEFT JOIN %1$scimetiere on zone.cimetiere = cimetiere.cimetiere

            WHERE
                courrier.courrier = %2$s',
            DB_PREFIXE,
            intval($this->getVal($this->clePrimaire))
        );
        $res = $this->f->db->query($query);
        $this->addToLog(__METHOD__."(): db->query(\"".$query."\");", VERBOSE_MODE);
        $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(
                //
                "datecourrier" => "",
                "complement" => "",
                //
                "titre" => "",
                "nom" => "",
                "prenom" => "",
                "marital" => "",
                "adresse1" => "",
                "adresse2" => "",
                "cp" => "",
                "ville" => "",
                "autorisation_nature" => "",
                //
                "emplacement" => "",
                "nature" => "",
                "superficie" => "",
                "emplacement_adresse_numero" => "",
                "emplacement_adresse_complement" => "",
                "typeconcession" => "",
                "terme" => "",
                "numeroacte" => "",
                "dateterme" => "",
                "datevente" => "",
                "daterenouvellement" => "",
                "famille" => "",
                "duree" => "",
                "placeconstat" => "",
                //
                "type_de_voie" => "",
                "voielib" => "",
                //
                "type_de_zone" => "",
                "zonelib" => "",
                //
                "cimetierelib" => "",
            ),
        );
    }

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

        /**
         * Gestion du contexte
         */
        //
        // En mode AJOUTER OU MODIFIER
        if ($this->getParameter("maj") < 2) {
            // A la modification du champ contexte alors on met à jour le champ
            // lettretype pour afficher les lettretypes liés au contexte.
            // On affiche le champ sur lequel on lie soit les contrats, soit les courriers
            $form->setOnchange('contexte',
                "filterSelect(this.value, 'lettretype', 'contexte', 'courrier'); treatment_courrier_contexte_field(this);");
        }
    }
}
