<?php
/**
 * i-doit
 *
 * Localization!
 *
 * This class has nothing to do with the language manager used in the
 * template library, but it is responsible for date and time formatting
 * based on specific settings. We do not use system- or database- locales,
 * we define all locales on our own!
 *
 * @internal
 * @package     i-doit
 * @subpackage  General
 * @author      Andre Woesten <awoesten@i-doit.de>
 * @version     0.9
 * @copyright   synetics GmbH
 * @license     http://www.i-doit.com/license
 * @todo        AW-only!! Beim Durchschreiten der Hypergate wird anhand der gewählten Mandantensprache hier die Default-Sprache selektiert. Bei existierendem Eintrag wird das nicht durchgeführt.
 */

use Symfony\Component\DependencyInjection\ContainerInterface;

if (!defined('LC_LANG')) {
    define('LC_LANG', 0);
}

class isys_locale
{
    /**
     * $_SESSION-Key for
     * storing user locales
     */
    const C__SESSION_CACHE_KEY = "user_setting";

    /**
     * Singleton instance.
     *
     * @var  isys_locale
     */
    private static $m_instance = null;


    /**
     * Locale configuration for all languages
     *
     * @var  array
     */
    private static $m_locales = [
        // Extend this in order to make a new language or localisation configuration.
        ISYS_LANGUAGE_ENGLISH => [
            // Language settings:
            LC_LANG     => [],
            // Time settings.
            LC_TIME     => [
                // Short date format
                "d_fmt_s" => "%Y-%m-%d",
                // Short date format but long year
                "d_fmt_m" => "%Y-%m-%d",
                // Long date format
                "d_fmt_l" => "%M %d, %Y",
                // Short time format
                "t_fmt_s" => "%H:%i",
                // Long time format
                "t_fmt_l" => "%H:%i:%s",
                // Months.
                "mon"     => [
                    "January",
                    "February",
                    "March",
                    "April",
                    "May",
                    "June",
                    "July",
                    "August",
                    "September",
                    "October",
                    "November",
                    "December"
                ]
            ],
            // Monetary and currency settings.
            LC_MONETARY => [
                // EUR x,xxx,xxx,xxx
                "mon_thousands_sep" => ",",
                // EUR x,xxx.yy
                "mon_decimal_point" => ".",
                // Grouping for the value (EUR 1,000,000)
                "mon_grouping"      => 3,
                // EUR 1999,xx
                "int_frac_digits"   => 2,
                // EUR 1999,xx
                "frac_digits"       => 2,
                // 1 if int_curr_symbol stands in front of the value, 0 if behind
                "p_ics_precedes"    => 0,
            ],
            // Numeric settings.
            LC_NUMERIC  => [
                "decimal_point" => ".",
                "thousand_sep"  => ",",
                "grouping"      => 3,
                "frac_digits"   => 2
            ]
        ],
        ISYS_LANGUAGE_GERMAN  => [
            // Language settings:
            LC_LANG     => [],
            // Time settings.
            LC_TIME     => [
                // Short date format
                "d_fmt_s" => "%d.%m.%y",
                // Short date format but long year
                "d_fmt_m" => "%d.%m.%Y",
                // Long date format
                "d_fmt_l" => "%d. %M %Y",
                // Short time format
                "t_fmt_s" => "%H:%i",
                // Long time format
                "t_fmt_l" => "%H:%i:%s",
                // Months.
                "mon"     => [
                    "Januar",
                    "Februar",
                    "März",
                    "April",
                    "Mai",
                    "Juni",
                    "Juli",
                    "August",
                    "September",
                    "Oktober",
                    "November",
                    "Dezember"
                ]
            ],
            // Monetary and currency settings.
            LC_MONETARY => [
                // EUR x.xxx.xxx.xxx
                "mon_thousands_sep" => ".",
                // EUR xxxx,yy
                "mon_decimal_point" => ",",
                // Grouping for the value (EUR 1.000.000)
                "mon_grouping"      => 3,
                // EUR 1999,xx
                "int_frac_digits"   => 2,
                // EUR 1999,xx
                "frac_digits"       => 2,
                // 1 if int_curr_symbol stands in front of the value, 0 if behind
                "p_ics_precedes"    => 0,
            ],
            // Numeric settings.
            LC_NUMERIC  => [
                "decimal_point" => ",",
                "thousand_sep"  => ".",
                "grouping"      => 3,
                "frac_digits"   => 2
            ]
        ]
    ];

    /**
     * Simple cache for fetched or manipulated variables.
     *
     * @var  array  Associative array
     */
    protected $m_cache;

    /**
     * Database access object used for global mandator settings
     *
     * @var  isys_component_dao_setting
     */
    private $m_daoSetting;

    /**
     * Database access object used for internal persons and general queries.
     *
     * @var  isys_component_dao_user
     */
    private $m_daoUser;

    /**
     * Database component.
     *
     * @var  isys_component_database
     */
    private $m_db;

    /**
     * Current user settings (set after init()).
     *
     * @var  array
     */
    private $m_userSettings = [
        LC_LANG     => ISYS_LANGUAGE_ENGLISH,
        LC_TIME     => ISYS_LANGUAGE_ENGLISH,
        LC_MONETARY => ISYS_LANGUAGE_ENGLISH,
        LC_NUMERIC  => ISYS_LANGUAGE_ENGLISH
    ];

    /**
     * Returns the dummy singleton instance (used before the login!)
     *
     * @return  isys_locale
     * @static
     */
    public static function dummy()
    {
        if (!isset(self::$m_instance)) {
            self::$m_instance = new isys_locale();
        }

        return self::$m_instance;
    }

    /**
     * Returns the singleton instance
     *
     * @param   isys_component_database $p_db
     * @param   integer                 $p_user_id
     *
     * @return  isys_locale
     * @static
     */
    public static function get(isys_component_database & $p_db, $p_user_id)
    {
        if (!is_object($p_db) || !is_numeric($p_user_id)) {
            throw new Exception("Internal Error! Could not configure localization (locales.inc.php)!");
        }

        if (!isset(self::$m_instance) || (isset(self::$m_instance) && $p_db !== self::$m_instance->m_db)) {
            self::$m_instance = new isys_locale($p_db);
            self::$m_instance->init($p_user_id);
        } else {
            self::$m_instance->init($p_user_id);
        }

        return self::$m_instance;
    }

    /**
     * @param isys_component_database $p_db
     * @param isys_component_session  $session
     *
     * @return isys_locale
     */
    public static function factory(isys_component_database $p_db, isys_component_session $session)
    {
        if (!isset(self::$m_instance) || (isset(self::$m_instance) && $p_db !== self::$m_instance->m_db)) {
            self::$m_instance = new isys_locale($p_db);
        }

        self::$m_instance->init($session->get_user_id());

        return self::$m_instance;
    }

    /**
     * Get instance
     *
     * @return isys_locale
     * @throws isys_exception_locale
     */
    public static function get_instance()
    {
        if (self::$m_instance !== null) {
            return self::$m_instance;
        } else {
            $database = isys_application::instance()->container->get('database', ContainerInterface::NULL_ON_INVALID_REFERENCE);

            if ($database) {
                return self::$m_instance = new isys_locale($database);
            }
        }

        throw new isys_exception_locale("Can not return an empty Instance of isys_locale. Please initialize one first by using isys_locale::init().");
    }

    /**
     * Resolves language by its constant.
     *
     * @param $p_constant
     *
     * @return string|null
     * @throws isys_exception_database
     */
    public function resolve_language_by_constant($p_constant): ?string
    {
        if ($this->get_setting('browser_language')) {
            return self::getPreferredLanguage();
        }

        if (is_string($p_constant)) {
            if (is_numeric($p_constant)) {
                $p_constant = intval($p_constant);
            } elseif (defined($p_constant)) {
                $p_constant = constant($p_constant);
            } else {
                $p_constant = $p_constant;
            }
        }

        if (!isset($this->m_cache['languages'])) {
            $l_result = (new isys_component_dao(isys_application::instance()->container->get('database_system')))
                ->retrieve("SELECT isys_language__const, isys_language__short FROM isys_language;");

            if ($l_result->num_rows() > 1) {
                while ($l_row = $l_result->get_row()) {
                    $this->m_cache['languages'][constant($l_row['isys_language__const'])] = $l_row['isys_language__short'];
                }
            }
        }

        if (isset($this->m_cache['languages'][$p_constant])) {
            return $this->m_cache['languages'][$p_constant];
        }

        return null;
    }

    /**
     * @param   $p_short_tag
     *
     * @return  mixed
     */
    public function resolve_language_constant_by_short_tag($p_short_tag)
    {
        if (!isset($this->m_cache['language_constants_by_short_tags'])) {
            global $g_comp_database_system;

            $l_dao = new isys_component_dao($g_comp_database_system);

            $l_result = $l_dao->retrieve("SELECT isys_language__const, isys_language__short FROM isys_language;");

            if ($l_result->num_rows() > 1) {
                while ($l_row = $l_result->get_row()) {
                    $l_key = $l_row['isys_language__short'];
                    $l_value = constant($l_row['isys_language__const']);
                    $this->m_cache['language_constants_by_short_tags'][$l_key] = $l_value;
                }
            }
        }

        if (isset($this->m_cache['language_constants_by_short_tags'][$p_short_tag])) {
            return $this->m_cache['language_constants_by_short_tags'][$p_short_tag];
        }

        return '';
    }

    /**
     * Numerical format of data.
     *
     * @param   mixed $p_data
     *
     * @return  string
     * @author  Niclas Potthast <npotthast@i-doit.org>
     */
    public function fmt_numeric($p_data)
    {
        if (!is_numeric($p_data)) {
            return $p_data;
        }

        $l_decSettings = self::$m_locales[$this->m_userSettings[LC_NUMERIC]][LC_NUMERIC];

        return number_format($p_data, $l_decSettings["frac_digits"], $l_decSettings["decimal_point"], $l_decSettings["thousand_sep"]);
    }

    /**
     * Formats a decimal number to a monetary one, dependent on the monetary setting made in locales.inc.php
     *
     * @param mixed       $value
     * @param bool|null   $symbolPrecedes
     * @param string|null $customSymbol
     *
     * @return string
     */
    public function fmt_monetary($value, ?bool $symbolPrecedes = null, ?string $customSymbol = null): string
    {
        if ($customSymbol === null && is_object($this->m_db)) {
            $symbol = $this->get_currency();
        } else {
            $symbol = $customSymbol;
        }

        $monetarySettings = self::$m_locales[$this->m_userSettings[LC_LANG]][LC_MONETARY];
        $numericValue = $this->fmt_numeric($value);

        if ($symbolPrecedes || $monetarySettings["p_ics_precedes"] == "1") {
            return "{$symbol} {$numericValue}";
        }

        return "{$numericValue} {$symbol}";
    }

    /**
     * Method to retrieve the configured currency symbol.
     *
     * @return string
     */
    public function get_currency(): string
    {
        $userSettingsArray = $this->m_daoUser->get_user_settings();

        if (!isset($userSettingsArray['isys_user_locale__isys_currency__id']) || !$userSettingsArray['isys_user_locale__isys_currency__id']) {
            $currencyId = (int)isys_application::instance()->container->get('settingsTenant')->get('gui.currency', '1');
        } else {
            $currencyId = (int)$userSettingsArray['isys_user_locale__isys_currency__id'];
        }

        if ($currencyId === 0) {
            $query = 'SELECT isys_currency__id AS id
                FROM isys_currency
                ORDER BY isys_currency__id ASC
                LIMIT 1;';

            $currencyId = (int)$this->m_daoUser->retrieve($query)->get_row_value('id');
        }

        $symbolData = isys_cmdb_dao_dialog::instance($this->m_db)
            ->set_table('isys_currency')
            ->get_data($currencyId);

        $symbol = $symbolData['isys_currency__title'];

        if (is_string($symbol) && strpos($symbol, ';') !== false) {
            $symbolData = explode(';', $symbol);
            $symbol = end($symbolData);
        }

        return (string)$symbol;
    }

    /**
     * @param   mixed   $p_data
     * @param   boolean $p_shortFormat
     *
     * @return  mixed
     */
    public function fmt_date($p_data, $p_shortFormat = true)
    {
        // First index is language, second index type of locale.
        $l_dateSettings = self::$m_locales[$this->m_userSettings[LC_TIME]][LC_TIME];
        if (!$l_dateSettings) {
            $l_dateSettings = self::$m_locales[ISYS_LANGUAGE_ENGLISH][LC_TIME];
        }

        $l_data = trim(strval($p_data));

        if (is_numeric($l_data)) {
            $l_data = date("Y-m-d", $l_data);
        }

        if (strpos($l_data, "0000-00-00") !== false || strpos($l_data, "1970-01-01") !== false) {
            return isys_tenantsettings::get('gui.empty_value', '-');
        }

        $l_field = ($p_shortFormat) ? "d_fmt_s" : "d_fmt_l";

        // @see ID-8137
        $regex = "/((?:\d\d)?\d\d)-(\d\d)-(\d\d)(?:)/";

        return preg_replace_callback($regex, function ($matches) use ($l_field, $l_dateSettings) { // @see ID-4255
            if (count($matches) > 5) {
                [, , , , , $l_year, $l_month, $l_day] = $matches;
            } else {
                [, $l_year, $l_month, $l_day] = $matches;
            }

            return $this->_fmt($l_dateSettings[$l_field], [
                "d" => sprintf("%02d", $l_day),
                "m" => sprintf("%02d", $l_month),
                "M" => $l_dateSettings["mon"][$l_month - 1],
                "y" => sprintf("%02d", $l_year),
                "Y" => sprintf("%04d", $l_year)
            ]);
        }, $l_data);
    }

    /**
     * @param   mixed   $p_data
     * @param   boolean $p_shortFormat
     *
     * @return  mixed
     */
    public function fmt_time($p_data, $p_shortFormat = true)
    {
        $l_timeSettings = self::$m_locales[$this->m_userSettings[LC_LANG]][LC_TIME];

        if (!$l_timeSettings) {
            $l_timeSettings = self::$m_locales[ISYS_LANGUAGE_ENGLISH][LC_TIME];
        }

        $l_data = trim(strval($p_data));

        if (is_numeric($l_data)) {
            $l_data = date('H:i:s', $l_data);
        }

        if (strpos($l_data, "00:00:00") !== false) {
            return isys_tenantsettings::get('gui.empty_value', '-');
        }

        $pattern = "/(\d\d)\:(\d\d)(?:\:(\d\d))?/";
        $format = $p_shortFormat ? 't_fmt_s' : 't_fmt_l';

        return preg_replace_callback($pattern, function ($matches) use ($format, $l_timeSettings) {
            $seconds = null;

            if (count($matches) > 3) {
                [, $hour, $minutes, $seconds] = $matches;
            } else {
                [, $hour, $minutes] = $matches;
            }
            $replaced = $this->_fmt($l_timeSettings[$format], [
                "H" => sprintf("%02d", $hour),
                "i" => sprintf("%02d", $minutes),
                "s" => sprintf("%02d", $seconds)
            ]);
            return $replaced;
        }, $l_data);
    }

    /**
     * Format date (and time if it's given).
     *
     * @param   string  $p_strData
     * @param   boolean $p_bShortDate
     * @param   boolean $p_bShortTime
     *
     * @return  string
     * @author  Niclas Potthast <npotthast@i-doit.org>
     */
    public function fmt_datetime($p_strData, $p_bShortDate = true, $p_bShortTime = true)
    {
        $l_strDate = $this->fmt_date($p_strData, $p_bShortDate);

        // First strip the time if it's just "0".
        if (strpos($p_strData, "00:00:00") !== false) {
            // Directly return just the date without time.
            return $l_strDate;
        }

        // Get the formatted time from the string.
        return $this->fmt_time($l_strDate, $p_bShortTime);
    }

    /**
     * Sets a user's locale setting
     *
     * @param integer $p_setting
     * @param integer $p_language
     *
     * @return boolean
     */
    public function set_setting($p_setting, $p_language)
    {
        if (array_key_exists($p_language, self::$m_locales) && array_key_exists($p_setting, self::$m_locales[$p_language])) {
            $this->m_userSettings[$p_setting] = $p_language;

            return true;
        }

        return false;
    }

    /**
     * Get a user's locale setting
     *
     * @param integer $p_setting
     *
     * @return integer (Language-ID)
     */
    public function get_setting($p_setting)
    {
        return $this->m_userSettings[$p_setting];
    }

    /**
     * Overloading method for getting a property
     *
     * Wraps to this->get
     *
     * @param string $p_setting
     *
     * @return integer
     */
    public function __get($p_setting)
    {
        return $this->get_setting(constant($p_setting));
    }

    /**
     * Overloading method for setting a property
     *
     * Wraps to this->set
     *
     * @param string  $p_setting
     * @param integer $p_language
     */
    public function __set($p_setting, $p_language)
    {
        $this->set_setting(constant($p_setting), $p_language);
    }

    /**
     * Saves the user's locale settings.
     *
     * @param int $p_user_id
     *
     * @return bool
     * @throws isys_exception_dao
     * @throws isys_exception_database
     * @deprecated
     */
    public function save_settings(int $p_user_id): bool
    {
        $l_currency_id = 0;
        $l_row = $this->get_settings_by_user_id($p_user_id)->get_row();

        if (empty($this->m_userSettings[LC_MONETARY])) {
            if (defined('C__CMDB__CURRENCY__EURO')) {
                $l_currency_id = constant('C__CMDB__CURRENCY__EURO');
            }
        } else {
            $l_currency_id = (int)$this->m_userSettings[LC_MONETARY];
        }

        $l_q = 'UPDATE isys_user_locale
				SET isys_user_locale__language = ' . $this->m_daoUser->convert_sql_int($this->m_userSettings[LC_LANG]) . ',
				isys_user_locale__language_time = ' . $this->m_daoUser->convert_sql_int($this->m_userSettings[LC_TIME]) . ',
				isys_user_locale__isys_currency__id = ' . $this->m_daoUser->convert_sql_id($l_currency_id) . ',
				isys_user_locale__language_numeric = ' . $this->m_daoUser->convert_sql_int($this->m_userSettings[LC_NUMERIC]) . '
				WHERE isys_user_locale__isys_user_setting__id = ' . $this->m_daoUser->convert_sql_id($l_row['isys_user_setting__id']) . ';';

        return ($this->m_daoUser->update($l_q) && $this->m_daoUser->apply_update());
    }

    /**
     * Initialize the per user's locale settings.
     *
     * @param  integer $p_user_id
     */
    public function init($p_user_id)
    {
        // Are the locales already cached?
        if (isset($_SESSION[self::C__SESSION_CACHE_KEY]) && !empty($_SESSION[self::C__SESSION_CACHE_KEY]) && is_array($_SESSION[self::C__SESSION_CACHE_KEY]) &&
            count($_SESSION[self::C__SESSION_CACHE_KEY])) {
            $this->m_userSettings = $_SESSION[self::C__SESSION_CACHE_KEY];
        } else {
            if ($p_user_id !== null) {
                $l_res = $this->get_settings_by_user_id((int)$p_user_id);

                if ($l_res !== null) {
                    $l_langrow = $l_res->get_row();

                    if (!empty($l_langrow["isys_user_locale__language"])) {
                        $this->m_userSettings[LC_LANG] = $l_langrow["isys_user_locale__language"];
                    } else {
                        // @see  ID-8780  Set the default language by setting
                        $this->m_userSettings[LC_LANG] = $this->resolve_language_constant_by_short_tag(isys_tenantsettings::get('system.default-language', 'en'));
                    }

                    if ($l_langrow["isys_user_locale__language_time"] > 0) {
                        $this->m_userSettings[LC_TIME] = $l_langrow["isys_user_locale__language_time"];
                    }

                    try {
                        $userSettingsArray = $this->m_daoUser->get_user_settings();
                        $this->m_userSettings[LC_MONETARY] = $userSettingsArray['isys_user_locale__isys_currency__id'];
                    } catch (isys_exception_database $e) {
                        $this->m_userSettings[LC_MONETARY] = defined_or_default('C__CMDB__CURRENCY__EURO', 1);
                    }

                    if ($l_langrow["isys_user_locale__language_numeric"] > 0) {
                        $this->m_userSettings[LC_NUMERIC] = $l_langrow["isys_user_locale__language_numeric"];
                    }

                $this->m_userSettings['tree_type'] = isys_application::instance()->container->get('settingsUser')->get('gui.default-tree-type', C__CMDB__VIEW__TREE_LOCATION__LOCATION);

                    if (isset($l_langrow["isys_user_locale__browser_language"])) {
                        $this->m_userSettings['browser_language'] = $l_langrow["isys_user_locale__browser_language"];
                    }
                }
            }


            $_SESSION[self::C__SESSION_CACHE_KEY] = $this->m_userSettings;
        }
    }

    /**
     * @param   integer $p_nSetting (i.e. LC_NUMERIC)
     *
     * @return  array
     * @author  Niclas Potthast <npotthast@i-doit.org>
     */
    public function get_user_settings($p_nSetting)
    {
        // @See ID-4682 Default language should always be english
        $langId = ISYS_LANGUAGE_ENGLISH;
        if ($this->get_setting('browser_language')) {
            $langIds = [
                'de' => ISYS_LANGUAGE_GERMAN,
                'en' => ISYS_LANGUAGE_ENGLISH
            ];

            $langId = $langIds[self::getPreferredLanguage()];

            return self::$m_locales[$langId][$p_nSetting];
        }

        if (isset($this->m_userSettings[$p_nSetting])) {
            $langId = $this->m_userSettings[$p_nSetting];
        }

        return self::$m_locales[$langId][$p_nSetting];
    }

    /**
     * Reset (and Rebuild) the user locale cache
     *
     * @param bool|integer $p_rebuild
     */
    public function reset_cache($p_rebuild = false)
    {
        /* Reset the Cache */
        $_SESSION[self::C__SESSION_CACHE_KEY] = null;

        /* Rebuild the cache */
        if ($p_rebuild) {
            $l_user_id = (is_numeric($p_rebuild)) ? $p_rebuild : isys_application::instance()->container->get('session')->get_user_id();

            $this->init($l_user_id);
        }
    }

    /**
     * Gets the date format
     *
     * @param bool $shortFormat
     * @param bool $sanitizeDate
     *
     * @return string
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    public function get_date_format($shortFormat = true, $sanitizeFormat = true)
    {
        $dateFormat = ($this->date_time_format_helper(($shortFormat ? 'd_fmt_m' : 'd_fmt_l')) ?: ($shortFormat ? 'Y-m-d' : 'd. M, Y'));

        return ($sanitizeFormat) ? str_replace('%', '', $dateFormat) : $dateFormat;
    }

    /**
     * Gets the time format
     *
     * @param bool $shortFormat
     * @param bool $sanitizeDate
     *
     * @return string
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    public function get_time_format($shortFormat = true, $sanitizeFormat = true)
    {
        $dateFormat = ($this->date_time_format_helper(($shortFormat ? 't_fmt_s' : 't_fmt_l')) ?: ($shortFormat ? 'H:i' : 'H:i:s'));

        return ($sanitizeFormat) ? str_replace('%', '', $dateFormat) : $dateFormat;
    }

    /**
     * Helper method to retrieve the specified date, time format
     *
     * @param $p_key
     *
     * @return mixed
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    private function date_time_format_helper($p_key)
    {
        $l_date_settings = $this->get_user_settings(LC_TIME);

        return $l_date_settings[$p_key];
    }

    /**
     * @return mixed
     */
    public function get_month_names()
    {
        $l_key = $this->resolve_language_constant_by_short_tag(isys_application::instance()->container->get('session')->get_language());

        if (!$l_key) {
            $l_key = ISYS_LANGUAGE_ENGLISH;
        }

        return self::$m_locales[$l_key][LC_TIME]['mon'];
    }

    /**
     * _fmt method.
     *
     * @param   string $p_string
     * @param   array  $p_repArray
     *
     * @return  mixed
     */
    private function _fmt($p_string, $p_repArray)
    {
        foreach ($p_repArray as $l_key => $l_value) {
            $p_string = str_replace("%" . $l_key, $l_value, $p_string);
        }

        return $p_string;
    }

    /**
     * Returns the user settings by the user-ID
     *
     * @param int $p_user_id
     *
     * @return isys_component_dao_result|null
     * @throws isys_exception_dao
     * @throws isys_exception_database
     */
    private function get_settings_by_user_id(int $p_user_id): ?isys_component_dao_result
    {
        if ($p_user_id > 0) {
            $l_q = "SELECT * FROM isys_user_locale
				INNER JOIN isys_user_setting ON isys_user_locale__isys_user_setting__id = isys_user_setting__id
				WHERE isys_user_setting__isys_obj__id = " . $p_user_id . ";";

            $l_res = $this->m_daoUser->retrieve($l_q);

            if ($l_res->num_rows() > 0) {
                return $l_res;
            } else {
                $l_language = isys_application::instance()->container->get('session')->get_language();

                if ($this->m_daoUser->get_user_setting_id($p_user_id)) {
                    // Otherwise create record and try again.
                    $l_q = "REPLACE INTO isys_user_locale (
						isys_user_locale__isys_user_setting__id,
						isys_user_locale__language,
						isys_user_locale__language_time,
						isys_user_locale__language_numeric
						) VALUES (
						'" . $this->m_daoUser->get_user_setting_id() . "',
						'" . $l_language . "',
						'" . $l_language . "',
						'" . $l_language . "');";

                    $this->m_daoUser->begin_update();

                    if ($this->m_daoUser->update($l_q) && $this->m_daoUser->apply_update()) {
                        return $this->get_settings_by_user_id($p_user_id);
                    }
                }
            }
        }

        return null;
    }

    /**
     * Retrieved preferred language by $_SERVER['HTTP_ACCEPT_LANGUAGE']
     *
     * @return string
     */
    public static function getPreferredLanguage()
    {
        // Open version has only english language file. See ID-4409
        if (!isys_application::isPro()) {
            return 'en';
        }

        $allowedLanguages = [
            'de-DE' => 'de',
            'de'    => 'de',
            'en-US' => 'en',
            'en'    => 'en'
        ];

        $preferredLanguage = 'en';

        if (isys_application::instance()->container->get('request')->server->get('HTTP_ACCEPT_LANGUAGE')) {
            $languages = [];
            $lang_parse = [];
            preg_match_all(
                '/([a-z]{1,8}(-[a-z]{1,8})?)\s*(;\s*q\s*=\s*(1|0\.[0-9]+))?/i',
                isys_application::instance()->container->get('request')->server->get('HTTP_ACCEPT_LANGUAGE'),
                $lang_parse
            );
            if (is_countable($lang_parse[1]) && count($lang_parse[1])) {
                $languages = array_combine($lang_parse[1], $lang_parse[4]);
                foreach ($languages as $lang => $val) {
                    $languages[$lang] = (float)$val;
                    if ($val === '') {
                        $languages[$lang] = 1.0;
                    }
                }

                arsort($languages, SORT_NUMERIC);
            }

            foreach ($languages as $key => $rating) {
                if (array_key_exists($key, $allowedLanguages)) {
                    $preferredLanguage = $allowedLanguages[$key];
                    break;
                }
            }
        }

        return $preferredLanguage;
    }

    /**
     * Constructor
     *
     * @param isys_component_database|null $p_db
     */
    public function __construct(isys_component_database $p_db = null)
    {
        if ($p_db) {
            $this->m_db = $p_db;
            $this->m_daoUser = isys_component_dao_user::instance($p_db);
            $this->m_daoSetting = new isys_component_dao_setting($p_db);
        }
    }
}
