<?php
// Libellé de la requête
$reqmo['libelle']=__('reqmo-libelle-api_operation');
$reqmo['reqmo_libelle']=__('reqmo-libelle-api_operation');
$ent=__('api_operation');

// Requête à effectuer
$reqmo['sql'] = sprintf('
  SELECT
    [operation.operation as operation],
    [emplacement.emplacement as emplacement],
    [operation.emplacement_transfert as emplacement_transfert],
    [cimetiere.cimetiere as cimetiere],
    [numdossier],
    [operation.date as date],
    [heure],
    [societe_coordonnee],
    [pf_coordonnee],
    [categorie],
    [consigne_acces],
    operation.etat,
    operation.particulier,
    CONCAT_WS(\' \',
    \'(\' || 
        CASE WHEN autorisation.nature IS NOT NULL AND autorisation.nature = \'ayantdroit\'
            THEN 
                \'ayant-droit\' 
            ELSE
                autorisation.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 demandeur,
    [operation.observation as observation],
    operation_defunt.defunt,
    operation_defunt.defunt_nature,
    (SELECT titre_de_civilite.libelle FROM %1$stitre_de_civilite WHERE titre_de_civilite.titre_de_civilite = operation_defunt.defunt_titre) as defunt_titre,
    operation_defunt.defunt_nom,
    operation_defunt.defunt_marital,
    operation_defunt.defunt_prenom,
    operation_defunt.defunt_datenaissance,
    operation_defunt.defunt_lieunaissance,
    operation_defunt.defunt_datedeces,
    operation_defunt.defunt_lieudeces,
    operation_defunt.defunt_parente
  FROM
    %1$soperation
    LEFT JOIN %1$soperation_defunt
      ON operation_defunt.operation = operation.operation
    LEFT JOIN %1$semplacement
      ON operation.emplacement = emplacement.emplacement
     LEFT JOIN %1$svoie
      ON emplacement.voie = voie.voie
    LEFT JOIN %1$szone
     ON voie.zone = zone.zone
    LEFT JOIN %1$scimetiere
     ON zone.cimetiere = cimetiere.cimetiere
    LEFT JOIN %1$sautorisation
     ON operation.demandeur = autorisation.autorisation
    LEFT JOIN %1$stitre_de_civilite 
     ON autorisation.titre = titre_de_civilite.titre_de_civilite
  ORDER BY operation.operation
  ',
  DB_PREFIXE
);

$reqmo['operation'] = "checked";
$reqmo['emplacement'] = "checked";
$reqmo['cimetiere'] = "checked";
$reqmo['numdossier'] = "checked";
$reqmo['date'] = "checked";
$reqmo['heure'] = "checked";
$reqmo['societe_coordonnee'] = "checked";
$reqmo['pf_coordonnee'] = "checked";
$reqmo['categorie'] = "checked";
$reqmo['observation'] = "checked";
$reqmo['emplacement_transfert'] = "checked";
$reqmo['consigne_acces'] = "checked";