<?php
/**
 *
 *
 * @package openfoncier
 * @version SVN : $Id: document_instruction.inc.php 4418 2021-07-30 15:40:00Z cgarcin $
 */

//
include "../sql/pgsql/instruction.inc.php";

$options[] = array(
    'type' => "pagination",
    'display' => false
);
$tab_actions['content']['lien'] = null;
$tab_actions['corner']['ajouter'] = null;
$tab_actions['left']['consulter'] = null;
$tab_actions['left']['previsualiser'] = array(
    'lien' => ''.OM_ROUTE_SOUSFORM.'&obj='.$obj.'&amp;action=401&amp;idxformulaire='.$idxformulaire.'&amp;idx=',
    'id' => '&amp;tri='.$tricolsf.'&amp;objsf='.$obj.'&amp;premiersf='.$premier.'&amp;retourformulaire='.$retourformulaire.'&amp;idxformulaire='.$idxformulaire.'&amp;trisf='.$tricolsf.'&amp;retour=tab',
    'lib' => '<span class="om-icon om-icon-16 om-icon-fix preview-16" title="Prévisualiser">
                Prévisualiser
            </span>',
    'rights' => array('list' => array($obj, $obj . '_previsualiser'), 'operator' => 'OR'),
    'ordre' => 100,
    'ajax' => false
);

// Action sur la deuxième colonne de contenu
$tab_actions['specific_content'][1] = array(
    'lien' => OM_ROUTE_FORM.'&obj=instruction&amp;action=405&amp;champ=om_fichier_instruction&amp;idx=',
    'id' => '" target="_blank',
    'ordre' => 10,
    'ajax' => false
);

// Add Join here ? 
$table = sprintf('(
        SELECT
            instruction.om_fichier_instruction,
            instruction.dossier,
            CAST(instruction.instruction AS TEXT),
            CONCAT (
                \'instruction_\',
                instruction.instruction
            ) AS file_name,
            om_lettretype.libelle,
            instruction.date_finalisation_courrier,
            instruction.date_retour_rar,
            instruction.date_evenement
            FROM
            %1$sinstruction
            INNER JOIN %1$som_lettretype
                ON om_lettretype.id = instruction.lettretype
            INNER JOIN %1$som_collectivite
                ON om_lettretype.om_collectivite = om_collectivite.om_collectivite
            
            LEFT JOIN %1$sdossier
            ON instruction.dossier = dossier.dossier
        WHERE instruction.om_fichier_instruction IS NOT NULL
            AND om_lettretype.actif IS TRUE
            AND (om_collectivite.om_collectivite = dossier.om_collectivite
                OR (om_collectivite.niveau = \'2\'
                AND NOT EXISTS (
                    SELECT
                        other_lettretype.om_lettretype
                    FROM
                        openads.om_lettretype AS other_lettretype
                        JOIN openads.om_collectivite
                            ON other_lettretype.om_collectivite = om_collectivite.om_collectivite
                    WHERE
                        om_lettretype.id = other_lettretype.id
                        AND om_collectivite.om_collectivite = dossier.om_collectivite
                )))
        UNION
        SELECT
                storage.uid,
                storage.info::json->>\'dossier\',
                CONCAT(\'STORAGE_\',storage.storage),
                storage.filename AS file_name,
                storage.info::json->>\'lettretype_libelle\',
                CASE WHEN storage.info::json->>\'date_retour_rar\' != \'\'
                    THEN CAST(storage.info::json->>\'date_retour_rar\' AS DATE)
                    ELSE NULL
                END AS date_retour_rar,
                CASE WHEN storage.info::json->>\'date_finalisation_courrier\' != \'\'
                    THEN CAST(storage.info::json->>\'date_finalisation_courrier\' AS DATE)
                    ELSE NULL
                END AS date_finalisation_courrier,
                CASE WHEN storage.info::json->>\'date_evenement\' != \'\'
                    THEN CAST(storage.info::json->>\'date_evenement\' AS DATE)
                    ELSE NULL
                END AS date_evenement
            FROM
                %1$sstorage
            WHERE
                storage.info::json->>\'can_be_document_instruction\' = \'true\'
    ) AS instruction',
    DB_PREFIXE
);

$champAffiche=array(
    "instruction.instruction as \""._("id")."\"",
    "CONCAT(
        '<span class=\"om-prev-icon reqmo-16\" title=\"Télécharger\">',
        instruction.file_name,
        '</span>'
    ) as \""._("nom du fichier")."\"",
    "instruction.libelle as type",
    "to_char(instruction.date_finalisation_courrier ,'DD/MM/YYYY') as \""._("date de finalisation")."\"",
    "to_char(instruction.date_retour_rar ,'DD/MM/YYYY') as \""._("date de notification")."\"",
);

// Limit filter here
// Filtre listing sous formulaire - dossier_instruction
if (in_array($retourformulaire, $foreign_keys_extended["dossier"])) {
    $selection = " WHERE (instruction.dossier = '".$f->db->escapeSimple($idxformulaire)."')";
}

$champRecherche = array(
    "instruction.instruction as \""._("nom du fichier")."\"",
    "instruction.lettretype as type",
    "instruction.date_finalisation_courrier as \""._("date de finalisation")."\"",
    "instruction.date_retour_rar as \""._("date de notification")."\"",
);