<?php
// +-------------------------------------------------+
// | 2002-2007 PMB Services / www.sigb.net pmb@sigb.net et contributeurs (voir www.sigb.net)
// +-------------------------------------------------+
// $Id: pmbesDatabase.class.php,v 1.12.2.1.4.1.2.8 2025/12/16 09:50:41 qvarin Exp $

if (stristr($_SERVER['REQUEST_URI'], ".class.php")) {
    die("no access");
}

global $base_path, $include_path, $class_path;
global $pmb_version_database_as_it_should_be, $pmb_subversion_database_as_it_shouldbe;

require_once $class_path . "/external_services.class.php";

if (! function_exists('form_relance')) {

    function form_relance($maj_suivante = "lancement")
    {
        return '';
    }
}

if (! function_exists('traite_rqt')) {

    function traite_rqt($requete = "", $message = "")
    {
        global $db_update_log_version;

        $retour = "Successful";

        pmb_mysql_query($requete);
        $erreur_no = pmb_mysql_errno();
        $erreur_msg = pmb_mysql_error();

        if ($erreur_no) {
            switch ($erreur_no) {
                case "1060":
                    $retour = "Field already exists, no problem.";
                    break;
                case "1061":
                    $retour = "Key already exists, no problem.";
                    break;
                case "1091":
                    $retour = "Object already deleted, no problem.";
                    break;
                default:
                    $retour = "Error may be fatal : " . $erreur_msg;
                    break;
            }
        }
        $result = pmb_mysql_query("SHOW TABLES LIKE 'db_update_logs'");
        if (pmb_mysql_num_rows($result)) {
            $query_log = " INSERT INTO db_update_logs (db_update_log_type, db_update_log_version, db_update_log_query, db_update_log_message, db_update_log_error, db_update_log_result)
                VALUES ('alter', '" . $db_update_log_version . "', '" . addslashes($requete) . "', '" . addslashes($message) . "', $erreur_no, '" . addslashes(strip_tags($retour)) . "')";
            pmb_mysql_query($query_log);
        }
        return "<span data-alter='action'>" . $message . "</span><span data-alter='result'>" . $retour . "</span>";
    }

}

class pmbesDatabase extends external_services_api_class
{
    /**
     * Liste des tables dont les blobs doivent etre convertis
     *
     * @var array
     */
    protected const TABLES_WITH_BLOB_FIELDS = [
        'acces_profiles' => [
            'id' => 'prf_id',
            'blobs' => ['prf_rule'],
        ],
        'caddie_procs' => [
            'id' => 'idproc',
            'blobs' => ['requete'],
        ],
        'empr_caddie_procs' => [
            'id' => 'idproc',
            'blobs' => ['requete'],
        ],
        'etagere' => [
            'id' => 'idetagere',
            'blobs' => ['comment'],
        ],
        'procs' => [
            'id' => 'idproc',
            'blobs' => ['requete'],
        ],
        'rss_flux' => [
            'id' => 'id_rss_flux',
            'blobs' => [
                'link_rss_flux',
                'descr_rss_flux',
                'copy_rss_flux',
                'img_url_rss_flux',
                'img_title_rss_flux',
                'img_link_rss_flux',
                'format_flux'
            ],
        ],
        'statopac_request' => [
            'id' => 'idproc',
            'blobs' => ['requete'],
        ],
        'infopages' => [
            'id' => 'id_infopage',
            'blobs' => ['content_infopage'],
        ]
    ];

    /**
     * Liste des tables dont les donnees serialisees doivent etre converties
     */
    protected const TABLES_WITH_SERIALIZED_FIELDS = [
        'bannettes' => [
            'id' => ['id_bannette'],
            'fields' => ['param_export'],
        ],
        'cache_amendes' => [
            'id' => ['id_empr', 'cache_date'],
            'fields' => ['data_amendes'],
        ],
        'connectors_sources' => [
        	'id' => ['source_id'],
        	'fields' => ['parameters'],
        ],
        'connectors_out_sources' => [
            'id' => ['connectors_out_source_id'],
            'fields' => ['connectors_out_source_config'],
        ],
        'equations' => [
            'id' => ['id_equation'],
            'fields' => ['requete'],
        ],
        'logopac' => [
            'id' => ['id_log'],
            'fields' => [
                'empr_carac',
                'empr_doc',
                'empr_expl',
                'gen_stat',
                'get_log',
                'nb_result',
                'post_log',
                'server_log',
            ],
        ],
        'opac_sessions' => [
            'id' => ['empr_id', 'date_rec'],
            'fields' => ['session'],
        ],
        'admin_session' => [
            'id' => ['userid'],
            'fields' => ['session'],
        ],
        'statopac' => [
            'id' => ['id_log'],
            'fields' => [
                'empr_carac',
                'empr_doc',
                'empr_expl',
                'gen_stat',
                'get_log',
                'nb_result',
                'post_log',
                'server_log',
            ],
        ],
        'opac_views' => [
            'id' => ['opac_view_id'],
            'fields' => [
                'opac_view_query',
            ],
        ],
        'shorturls' => [
            'id' => ['id_shorturl'],
            'fields' => [
                'shorturl_context',
            ],
        ]
    ];

    /**
     * Liste des tables dont les parametres d'encodage doivent etre convertis
     */
    protected const TABLES_WITH_ENCODING_PARAMETERS = [
        'caddie_procs',
        'procs',
        'empr_caddie_procs',
        'authorities_caddie_procs',
        'statopac_request'
    ];

    /**
     * Nombre maximum de lignes par iteration en traitement des donnees serialisees
     */
    protected const MAX_ROWS_PER_ITERATION = 10000;


    public function get_current_version()
    {
        $query = "select valeur_param from parametres where type_param = 'pmb' and sstype_param ='bdd_version'";
        $result = pmb_mysql_query($query);
        $pmb_bdd_version = "v1.0";
        if (pmb_mysql_num_rows($result)) {
            $pmb_bdd_version = pmb_mysql_result($result, 0, 0);
        }
        return $pmb_bdd_version;
    }

    public function get_current_subversion()
    {
        $query = "select valeur_param from parametres where type_param = 'pmb' and sstype_param ='bdd_subversion'";
        $result = pmb_mysql_query($query);
        $pmb_bdd_subversion = "0";
        if (pmb_mysql_num_rows($result)) {
            $pmb_bdd_subversion = pmb_mysql_result($result, 0, 0);
        }
        return $pmb_bdd_subversion;
    }

    public function get_version_informations()
    {
        global $pmb_version_database_as_it_should_be;
        global $pmb_subversion_database_as_it_shouldbe;

        $tmp = array(
            'currentVersion' => $this->get_current_version(),
            'currentSubVersion' => $this->get_current_subversion(),
            'shouldbeVersion' => $pmb_version_database_as_it_should_be,
            'shouldbeSubVersion' => $pmb_subversion_database_as_it_shouldbe
        );
        return $tmp;
    }

    public function need_update()
    {
        global $pmb_version_database_as_it_should_be;
        global $pmb_subversion_database_as_it_shouldbe;

       if ($this->get_current_version() != $pmb_version_database_as_it_should_be) {
            return 1;
        }
        if ($this->get_current_subversion() != $pmb_subversion_database_as_it_shouldbe) {
            return 1;
        }
        return 0;
    }

    public function update()
    {

        global $base_path;
        global $lang;
        global $class_path;
        global $include_path;
        global $pmb_version_database_as_it_should_be;
        global $pmb_subversion_database_as_it_shouldbe;
        global $action, $maj_a_faire;

        // Les requetes sont en iso / l'affichage est en iso ou en utf8
        // on harmonise pour eviter les melanges
        global $msg, $charset;
        $charset = 'iso-8859-1';
        pmb_mysql_query("set names latin1");

        // Allons chercher les messages
        include_once ("$class_path/XMLlist.class.php");
        $messages = new XMLlist("$include_path/messages/$lang.xml", 0);
        $messages->analyser();
        $msg = $messages->table;

        // On evite d'afficher des erreurs dans le message de retour
        if (! isset($REQUEST_URI)) {
            $REQUEST_URI = '';
        }
        // les globales PMB !
        include ($include_path . "/start.inc.php");

        // On evite d'afficher des erreurs dans le message de retour (suite)
        global $pmb_display_errors;
        $pmb_display_errors = 0;

        $update_result = array();
        $update_result['result'] = true;
        $update_result['informations'] = "";

        $check = $this->need_update();

		if($check == 1){

            ob_start();

            $pmb_bdd_version = $this->get_current_version();
            $pmb_bdd_subversion = $this->get_current_subversion();


            if ($pmb_bdd_version != $pmb_version_database_as_it_should_be) {

                // Ne pas supprimer $action, $pmb_bdd_version et $maj_a_faire car utilises dans les alter_vX.inc.php
                $action = "lancement";

                switch (substr($pmb_bdd_version, 0, 2)) {
                    case "v1":
                        include ($base_path . "/admin/misc/alter_v1.inc.php");
                        break;
                    case "v2":
                        include ($base_path . "/admin/misc/alter_v2.inc.php");
                        break;
                    case "v3":
                        include ($base_path . "/admin/misc/alter_v3.inc.php");
                        break;
                    case "v4":
                        if (substr($pmb_version_database_as_it_should_be, 0, 2) == "v5" && ($pmb_bdd_version == "v4.97" || $pmb_bdd_version == "v4.96" || $pmb_bdd_version == "v4.95" || $pmb_bdd_version == "v4.94")) {
                            include ($base_path . "/admin/misc/alter_v5.inc.php");
                        } else {
                            include ($base_path . "/admin/misc/alter_v4.inc.php");
                        }
                        break;
                    case "v5":
                        include ($base_path . "/admin/misc/alter_v5.inc.php");
                        break;
                    case "v6":
                        include ($base_path . "/admin/misc/alter_v6.inc.php");
                        break;
                }
                ob_clean();

                // Ne pas supprimer $action, $pmb_bdd_version et $maj_a_faire car utilises dans les alter_vX.inc.php
                $action = $maj_a_faire;
                switch (substr($action, 0, 2)) {
                    case "v1":
                        include ($base_path . "/admin/misc/alter_v1.inc.php");
                        break;
                    case "v2":
                        include ($base_path . "/admin/misc/alter_v2.inc.php");
                        break;
                    case "v3":
                        include ($base_path . "/admin/misc/alter_v3.inc.php");
                        break;
                    case "v4":
                        if (substr($pmb_version_database_as_it_should_be, 0, 2) == "v5" && ($pmb_bdd_version == "v4.97" || $pmb_bdd_version == "v4.96" || $pmb_bdd_version == "v4.95" || $pmb_bdd_version == "v4.94")) {
                            include ($base_path . "/admin/misc/alter_v5.inc.php");
                        } else {
                            include ($base_path . "/admin/misc/alter_v4.inc.php");
                        }
                        break;
                    case "v5":
                        include ($base_path . "/admin/misc/alter_v5.inc.php");
                        break;
                    case "v6":
                        include ($base_path . "/admin/misc/alter_v6.inc.php");
                        break;
                }
                $ob = ob_get_contents();
                if (false !== $ob) {

                    $ob = str_replace("<span data-alter='action'>", PHP_EOL . "Action : ", $ob);
                    $ob = str_replace("<span data-alter='result'>", PHP_EOL . "Result : ", $ob);
                    $ob = strip_tags($ob);
                    $ob = str_replace($msg["admin_misc_action"].$msg["admin_misc_resultat"], "",  $ob);
                    $ob = str_replace($msg[1807], PHP_EOL . PHP_EOL . $msg[1807] ,  $ob);
                    $update_result['informations'] = $ob;
                }
            } else {

                ob_clean();
                require_once ($base_path . "/admin/misc/addon.inc.php");
                $ob = ob_get_contents();
                if (false !== $ob) {

                    $ob = str_replace("<span data-alter='action'>", PHP_EOL . "Action : ", $ob);
                    $ob = str_replace("<span data-alter='result'>", PHP_EOL . "Result : ", $ob);
                    $ob = strip_tags($ob);
                    $ob = str_replace($msg["admin_misc_action"].$msg["admin_misc_resultat"], "",  $ob);
                    $ob = str_replace($msg[1807], PHP_EOL . PHP_EOL . $msg[1807] . PHP_EOL . PHP_EOL ,  $ob);
                    $update_result['informations'] = $ob;
                }
            }

            ob_end_clean();

        } else {

            // Et la le message est encore dans un autre charset
            $update_result['informations'] = $this->msg['update_msg_database_already_updated'];
            $is_utf8 = mb_detect_encoding($update_result['informations'], 'UTF-8', true);
            if ('UTF-8' != $is_utf8) {
                $update_result['informations'] = encoding_normalize::utf8_normalize($update_result['informations']);
            }
            return $update_result;
        }

        // on retourne systématiquement le message en utf8
        // il faudra corriger si besoin au niveau du connecteur
        $update_result['informations'] = encoding_normalize::utf8_normalize($update_result['informations']);
        return $update_result;

    }

    /**
     * Verifie si le charset de PMB est en UTF-8
     *
     * @return integer
     */
    public function is_utf8() {
        global $charset;

        if ('utf-8' == $charset) {
            return 1;
        }
        return 0;
    }

    /**
     * Convertit la base de donnees en UTF-8
     *
     * @return array
     */
    public function convert_to_utf8(int $step = 0)
    {
        $result = [
            'result' => false,
            'next_step' => 0,
            'informations' => ''
        ];

        switch ($step) {

            // ==================== Initialisation ====================
            case 0:

                $conversion_result = $this->consolidateStatopac();
                $result['result'] = $conversion_result['result'];
                $result['next_step'] = $step + 1;
                $result['informations'] = [
                    'step' => $step,
                    'step_name' => $this->msg['convert_to_utf8_consolidate_statopac'],
                    'logs' => $conversion_result['logs']
                ];
                break;

            // ==================== Conversion ====================
            case 1:

                $conversion_result = $this->convertDatabase();
                $result['result'] = $conversion_result['result'];
                $result['next_step'] = $step + 1;
                $result['informations'] = [
                    'step' => $step,
                    'step_name' => $this->msg['convert_to_utf8_convert_database'],
                    'logs' => $conversion_result['logs']
                ];
                break;

            case 2:

                $conversion_result = $this->convertCmsCadreContent();
                $result['result'] = $conversion_result['result'];
                $result['next_step'] = $step + 1;
                $result['informations'] = [
                    'step' => $step,
                    'step_name' => $this->msg['convert_to_utf8_convert_cms_cadre_content'],
                    'logs' => $conversion_result['logs']
                ];
                break;

            case 3:

                $conversion_result = $this->convertCmsManagedModules();
                $result['result'] = $conversion_result['result'];
                $result['next_step'] = $step + 1;
                $result['informations'] =  [
                    'step' => $step,
                    'step_name' => $this->msg['convert_to_utf8_convert_cms_managed_modules'],
                    'logs' => $conversion_result['logs']
                ];
                break;

            case 4:

                $conversion_result = $this->convertBlobs();
                $result['result'] = $conversion_result['result'];
                $result['next_step'] = $step + 1;
                $result['informations'] = [
                    'step' => $step,
                    'step_name' => $this->msg['convert_to_utf8_convert_blobs'],
                    'logs' => $conversion_result['logs']
                ];
                break;

            case 5:

                $conversion_result = $this->convertSerializedData();
                $result['result'] = $conversion_result['result'];
                $result['next_step'] = $step + 1;
                $result['informations'] = [
                    'step' => $step,
                    'step_name' => $this->msg['convert_to_utf8_convert_serialized_data'],
                    'logs' => $conversion_result['logs']
                ];
                break;

            case 6:

                $conversion_result = $this->convertParameters();
                $result['result'] = $conversion_result['result'];
                $result['next_step'] = 0;
                $result['informations'] = [
                    'step' => $step,
                    'step_name' => $this->msg['convert_to_utf8_convert_parameters'],
                    'logs' => $conversion_result['logs'], 'parameters' => $this->msg['convert_to_utf8_convert_parameters'],
                ];
                break;

            default:

                $result['result'] = false;
                $result['next_step'] = 0;
                $result['informations'] = 'Step not found';
                break;
        }

        $result['informations'] = encoding_normalize::utf8_normalize($result['informations']);
        return $result;
    }

    /**
     * Consolidation and cleaning of logopac and statopac tables
     *
     * @return array with 'result' and 'logs' information
     */
    protected function consolidateStatopac()
    {
        $result = [
            'result' => true,
            'logs' => [
                'truncate_logopac' => 'ok',
                'truncate_statopac' => 'ok',
            ]
        ];

        // TODO: Transfert de logopac vers statopac ?

        // TODO: Consolidation ?

        // Vidage tables logopac et statopac
        if (!pmb_mysql_query('TRUNCATE TABLE logopac')) {
            $result['result'] = 'false';
            $result['logs']['truncate_logopac'] = 'ko';
        }
        if (!pmb_mysql_query('TRUNCATE TABLE statopac')) {
            $result['result'] = 'false';
            $result['logs']['truncate_statopac'] = 'ko';
        }
        return $result;
    }

    /**
     * Convertit la base de donnees
     *
     * @return array
     */
    protected function convertDatabase()
    {
        $result = [
            'result' => true,
            'logs' => [
                'tables' => [],
                'database' => 'ok',
                'check' => [
                    'tables' => 'ok',
                    'columns' => 'ok',
                ],
            ]
        ];

        // Recuperation collation disponible
        if ($this->isCollationAvailable('utf8_unicode_ci') ) {
            $collation = 'utf8_unicode_ci';
            $character_set = 'utf8';
        } else {
            $collation = 'utf8mb3_unicode_ci';
            $character_set = 'utf8mb3';
        }

        // Recuperation de la liste des tables
        $tables = $this->getTables();

        // Conversion des tables
        foreach ($tables as $table) {
            pmb_mysql_query("SET foreign_key_checks = 0;");

            $query = "ALTER TABLE `$table` CONVERT TO CHARACTER SET $character_set COLLATE $collation;";
            if (!pmb_mysql_query($query)) {
                $result['result'] = false;
                $result['logs']['tables'][$table] = 'ko';
            } else {
                $result['logs']['tables'][$table] = 'ok';
            }

            pmb_mysql_query("SET foreign_key_checks = 1;");
        }

        // Conversion de la base
        if (!pmb_mysql_query("ALTER DATABASE `" . DATA_BASE . "` CHARACTER SET $character_set COLLATE $collation;")) {
            $result['result'] = false;
            $result['logs']['database'] = 'ko';
        }

        // Verification
        $verificationQuery = "SELECT * FROM information_schema.TABLES WHERE TABLE_COLLATION != '$collation' AND TABLE_SCHEMA='" . DATA_BASE . "';";
        $verificationResult = pmb_mysql_query($verificationQuery);
        if ($verificationResult->num_rows > 0) {
            $result['result'] = false;
            $result['check']['tables'] = 'ko';
        }

        $verificationQuery = "SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='" . DATA_BASE . "' AND (CHARACTER_SET_NAME IS NOT NULL OR COLLATION_NAME IS NOT NULL) AND (CHARACTER_SET_NAME != '$character_set') LIMIT 1;";
        $verificationResult =pmb_mysql_query($verificationQuery);
        if ($verificationResult->num_rows > 0) {
            $result['result'] = false;
            $result['check']['columns'] = 'ko';
        }
        return $result;
    }

    /**
     * Check if a specific collation is available
     *
     * @param string $collation
     * @return bool
     */
     protected function isCollationAvailable(string $collation): bool
    {
        $result = pmb_mysql_query("SHOW COLLATION LIKE '". addslashes($collation) ."'");
        return $result->num_rows > 0;
    }


    /**
     * Returns an array of tables in the database
     *
     * @return array
     */
    protected function getTables()
    {
        $r = pmb_mysql_query("SHOW FULL TABLES IN `". DATA_BASE ."` WHERE TABLE_TYPE NOT LIKE '%VIEW%' ;");
        $tables = [];
        while ($row = pmb_mysql_fetch_row($r)) {
            $tables[] = $row[0];
        }
        return $tables;
    }

    /**
     * Convertit le contenu des cadres CMS
     *
     * @return array
     */
    protected function convertCmsCadreContent() {
        $result = [
            'result' => true,
            'logs' => [
                'cms_cadre_content' => [],
            ]
        ];

        $data = $this->fetchCmsCadreContent();

        foreach ($data as $id_cadre_content => $cadre_content_data) {

            $unserialized = unserialize($cadre_content_data);
            $new_content = '';
            if(false === $unserialized) {
                $unserialized = unserialize(mb_convert_encoding($cadre_content_data, 'ISO-8859-1', 'UTF-8'));
                $new_content = mb_convert_encoding($unserialized, 'UTF-8', 'ISO-8859-1');
            } else {
                $new_content = $unserialized;
            }
            $serialized = serialize($new_content);

            if (false === $serialized) {

                $result['result'] = false;
                $result['logs']['cms_cadre_content'][$id_cadre_content] = 'ko';
                $serialized = $cadre_content_data;

            } else {

                $query = "UPDATE cms_cadre_content
                    SET cadre_content_data = '" . addslashes($serialized) . "'
                    WHERE id_cadre_content = " . intval($id_cadre_content);

                if (! pmb_mysql_query($query)) {
                    $result['result'] = false;
                    $result['logs']['cms_cadre_content'][$id_cadre_content] = 'ko';
                } else {
                    $result['logs']['cms_cadre_content'][$id_cadre_content] = 'ok';
                }
            }
        }
        return $result;
    }

    protected function fetchCmsCadreContent()
    {
        $data = [];
        $r = pmb_mysql_query("SELECT id_cadre_content, cadre_content_data FROM cms_cadre_content");
        if (pmb_mysql_num_rows($r)) {
            while ($row = pmb_mysql_fetch_assoc($r)) {
                $data[$row['id_cadre_content']] = $row['cadre_content_data'];
            }
        }
        return $data;
    }

    /**
     * Convertit les modules CMS gérés
     *
     * @return array
     */
    protected function convertCmsManagedModules()
    {
        $result = [
            'result' => true,
            'logs' => [
                'cms_managed_modules' => [],
            ]
        ];

        $data = $this->fetchCmsManagedModules();

        foreach ($data as $managed_module_name => $managed_module_box) {

            $unserialized = unserialize($managed_module_box);
            $new_content = '';
            if(false === $unserialized) {
                $unserialized = unserialize(mb_convert_encoding($managed_module_box, 'ISO-8859-1', 'UTF-8'));
                $new_content = mb_convert_encoding($unserialized, 'UTF-8', 'ISO-8859-1');
            } else {
                $new_content = $unserialized;
            }
            $serialized = serialize($new_content);

            if (false === $serialized) {

                $result['result'] = false;
                $result['logs']['cms_managed_modules'][$managed_module_name] = 'ko';
                $serialized = $managed_module_box;

            } else {

                $query = "UPDATE cms_managed_modules
                    SET managed_module_box = '" . addslashes($serialized) . "'
                    WHERE managed_module_name = '" . addslashes($managed_module_name) . "'";

                if (! pmb_mysql_query($query)) {
                    $result['result'] = false;
                    $result['logs']['cms_managed_modules'][$managed_module_name] = 'ko';
                } else {
                    $result['logs']['cms_managed_modules'][$managed_module_name] = 'ok';
                }
            }
        }
        return $result;
    }

    protected function fetchCmsManagedModules()
    {
        $data = [];
        $r = pmb_mysql_query("SELECT * FROM cms_managed_modules");
        if (pmb_mysql_num_rows($r)) {
            while ($row = pmb_mysql_fetch_assoc($r)) {
                $data[$row['managed_module_name']] = $row['managed_module_box'];
            }
        }
        return $data;
    }

    /**
     * Convertit les blobs
     *
     * @return array
     */
    protected function convertBlobs() {
        $result = [
            'result' => true,
            'logs' => []
        ];

        $data = $this->fetchBlobs();

        foreach ($data as $table => $rows) {
            $update_blobs_result = $this->updateBlobs($table, $rows);
            $result['logs'][] = [$table => $update_blobs_result['logs']];
            if (!$update_blobs_result['result']) {
                $result['result'] = false;
            }
        }

        return $result;
    }

    public function fetchBlobs()
    {
        $data = [];
        foreach (static::TABLES_WITH_BLOB_FIELDS as $table => $fields) {
            $data[$table] = [];

            $blobs_fields = implode(', ', $fields['blobs']);
            $r = pmb_mysql_query(
                "SELECT ".$fields['id']." AS id, ".$blobs_fields." FROM ".$table
            );

            while ($row = pmb_mysql_fetch_assoc($r)) {
                $id = $row['id'];
                unset($row['id']);
                $data[$table][$id] = $row;
            }
        }
        return $data;
    }

    /**
     * Update blobs in database
     *
     * @param string $table
     * @param array $rows
     * @return array
     */
    private function updateBlobs(string $table, array $rows)
    {
        $result = [
            'result' => true,
            'logs' => []
        ];
        foreach ($rows as $id => $blobs) {

            $blobs = $this->convertBlobsToUtf8($blobs);
            $query = $this->buildBlobQuery($table, $id, $blobs);
            if (pmb_mysql_query($query)) {
                $result['logs'][$id] = 'ok';
            } else {
                $result['result'] = false;
                $result['logs'][$id] ='ko';
            }
        }
        return $result;
    }

    private function convertBlobsToUtf8(array $blobs)
    {
        foreach ($blobs as $key => $blob) {
                $encoding = mb_detect_encoding($blob, ['UTF-8', 'ISO-8859-1']);
                if($encoding  == 'ISO-8859-1') {
                    $blobs[$key] = mb_convert_encoding($blob, 'UTF-8', 'ISO-8859-1');
                }
            }
        return $blobs;
    }

    /**
     * Build blob query
     *
     * @param string $table
     * @param integer $id
     * @param array $blobs
     * @return string
     */
    private function buildBlobQuery(string $table, int $id, array $blobs): string
    {
        $query = "UPDATE ".$table." SET ";

        $clause = [];
        foreach ($blobs as $field => $blob) {
            $clause[] = $field." = '". addslashes($blob) ."'";
        }

        $query .= implode(' , ', $clause);
        $query .= " WHERE ". static::TABLES_WITH_BLOB_FIELDS[$table]['id'] ." = ".$id;

        return $query;
    }

    /**
     * Convertit les donnees serialisees
     *
     * @return array
     */
    protected function convertSerializedData()
    {
        $result = [
            'result' => true,
            'logs' => []
        ];

        $data = $this->fetchSerializedData();

        foreach ($data as $info) {
            $nbPage = ceil($info['total'] / self::MAX_ROWS_PER_ITERATION);

            for ($i = 0; $i < $nbPage; $i++) {
                $update_serialized_data_result = $this->updateSerializedData($info['table'], $info, $i);
                if (!$update_serialized_data_result['result']) {
                    $result['result'] = false;
                }
                $result['logs'][$info['table']] = $update_serialized_data_result['logs'];
            }
        }

        return $result;
    }

    protected function fetchSerializedData() {
        $data = [];
        foreach (static::TABLES_WITH_SERIALIZED_FIELDS as $table => $columns) {
            $fields = implode(', ', $columns['id']);
            $fields .= ', '.implode(', ', $columns['fields']);

            $result = pmb_mysql_query("SELECT count(*) as total FROM " . $table);
            if ($result) {
                $row = $result->fetch_assoc();
                $result->free();

                if ($row['total'] <= 0) {
                    continue;
                }
                $data[$table] = [
                    'query' => "SELECT " . $fields . " FROM " . $table,
                    'table' => $table,
                    'total' => $row['total'],
                ];
            }
        }
        return $data;
    }

    protected function updateSerializedData(string $table, array $info, int $iteration)
    {
        $result = [
            'result' => true,
            'logs' => []
        ];

        $r = pmb_mysql_query($info['query'] . " LIMIT " . $iteration * self::MAX_ROWS_PER_ITERATION . ", " . self::MAX_ROWS_PER_ITERATION);
        if ($r) {
            while ($row = pmb_mysql_fetch_assoc($r)) {
                $this->convertSerializedDataToUtf8($table, $row);
                $query = $this->buildSerializedDataQuery($table, $row);
                if (pmb_mysql_query($query)) {
                    $result['logs'] = 'ok';
                } else {
                    $result['logs'] = 'ko';
                    $result['result'] = false;
                }
            }
            pmb_mysql_free_result($r);
        }
        return $result;
    }

    protected function convertSerializedDataToUtf8(string $table, array &$fields)
    {
        foreach (static::TABLES_WITH_SERIALIZED_FIELDS[$table]['fields'] as $field) {

            $unserialized = unserialize($fields[$field]);

            // La deserialisation a reussi
            if( false !== $unserialized) {
                continue;
            }

            // La deserialisation a echoue et le contenu n'est pas de type chaine|tableau|objet
            if ( (false === $unserialized) && !in_array(substr($field, 0, 2), ['s:', 'a:', 'O:']) ) {
                continue;
            }

            // La deserialisation a echoue et le contenu est de type chaine|tableau|objet
            if( (false === $unserialized) && in_array(substr($field, 0, 2), ['s:', 'a:', 'O:'])) {
                $unserialized = unserialize(mb_convert_encoding($fields[$field], 'ISO-8859-1', 'UTF-8'));
                $unserialized = mb_convert_encoding($unserialized, 'UTF-8', 'ISO-8859-1');
            }

            // On serialise
            $serialized = serialize($unserialized);
            if( false === $serialized) {
                continue;
            }
            $fields[$field] = $serialized;
        }
    }

    protected function buildSerializedDataQuery(string $table, array $fields): string
    {
        $values = [];
        foreach (static::TABLES_WITH_SERIALIZED_FIELDS[$table]['fields'] as $field) {
            $values[] = $field . " = '" . addslashes($fields[$field]) . "'";
        }

        $clause = [];
        foreach (static::TABLES_WITH_SERIALIZED_FIELDS[$table]['id'] as $field) {
            $clause[] = $field . " = '" . addslashes($fields[$field]) . "'";
        }

        $query = "UPDATE " . $table . " SET ";
        $query .= implode(", ", $values);
        $query .= " WHERE ";
        $query .= implode(" AND ", $clause);

        return $query;
    }


    /**
     * Convertit les parametres d'encodage
     *
     * @return array
     */
    protected function convertParameters() {
        $result = [
            'result' => true,
            'logs' => [
                'encoding_parameters' => [],
                'pdf_font' => 'ok',
                'pdf_fontfixed' => 'ok',
                'param_etiq_codes_barres' => 'ok'
            ]
        ];

        // Modification des encodages definis dans les parametres
        foreach (static::TABLES_WITH_ENCODING_PARAMETERS as $table) {

            $query = "UPDATE ". $table . " SET parameters=REPLACE(parameters, 'encoding=\"iso-8859-1\"', 'encoding=\"utf-8\"') WHERE parameters LIKE '%encoding=\"iso-8859-1\"%';";
            if(!pmb_mysql_query($query)) {
                $result['result'] = false;
                $result['logs']['encoding_parameters'][$table] = 'ko';
            } {
                $result['logs']['encoding_parameters'][$table] = 'ok';
            }
        }

        // Modification des polices
        $rs = pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='pmb' AND sstype_param='pdf_font' AND valeur_param='Helvetica'");
        if (pmb_mysql_num_rows($rs) > 0) {
            $ru = pmb_mysql_query("UPDATE parametres SET valeur_param='freeserif' WHERE type_param='pmb' AND sstype_param='pdf_font'");
            if(!$ru) {
                $result['result'] = false;
                $result['logs']['pdf_font'] = 'ko';
            }
        }

        $rs = pmb_mysql_query("SELECT 1 FROM parametres WHERE type_param='pmb' AND sstype_param='pdf_fontfixed' AND valeur_param='Courier'");
        if (pmb_mysql_num_rows($rs) > 0) {
            $ru = pmb_mysql_query("UPDATE parametres SET valeur_param='vera' WHERE type_param='pmb' AND sstype_param='pdf_fontfixed'");
            if(!$ru) {
                $result['result'] = false;
                $result['logs']['pdf_fontfixed'] = 'ko';
            }
        }

        //Modification des parametres d'etiquettes codes barres
        $rs = pmb_mysql_query("SELECT valeur_param FROM parametres WHERE type_param='pmb' AND sstype_param='param_etiq_codes_barres'");
        if (pmb_mysql_num_rows($rs) > 0) {

            $valeur_param = pmb_mysql_result($rs, 0, 0);

            $unserialized = unserialize($valeur_param);
            $new_content = '';
            if(false === $unserialized) {
                $unserialized = unserialize(mb_convert_encoding($valeur_param, 'ISO-8859-1', 'UTF-8'));
                $new_content = mb_convert_encoding($unserialized, 'UTF-8', 'ISO-8859-1');
            } else {
                $new_content = $unserialized;
            }
            $serialized = serialize($new_content);

            if (false === $serialized) {
                $result['result'] = false;
                $result['logs']['param_etiq_codes_barres'] = 'ko';
            }
            $ru = pmb_mysql_query("UPDATE parametres SET valeur_param='".addslashes($serialized)."' WHERE type_param='pmb' AND sstype_param='param_etiq_codes_barres'");
            if(!$ru) {
                $result['result'] = false;
                $result['logs']['param_etiq_codes_barres'] = 'ko';
            }
        }
        return $result;
    }
}
