k_online_backend/service/multilingual.class.php

<?php

if (!defined('IN_ONLINE')) {
    exit('Access Denied');
}

/**
 * Description of multilingual
 *
 * @author Administrator
 */
class multilingual{

    private static $_multilingual;

    function __construct() {  
       
    }

    public static function getInstance() {
        global $memory_limit;
        $memory_limit = ini_get("memory_limit");
        ini_set("memory_limit", '2048M');
        if (!self::$_multilingual) {
            $c = __CLASS__;
            self::$_multilingual = new $c;
        }
        return self::$_multilingual;
    }
    public function multilingual_config() {
        $operate = utils::_get('operate');
        $operate = strtolower($operate);
        /*
         * multilingual_page_load
        */
        if ($operate == "multilingual_page") {
            //查询指定语言
            $langIds = [];
            $langkey = $_POST['langkey'];

            // 1. 统一处理 $langs 数组
            if (empty($langkey) || strtoupper($langkey) == "ALL") {
                // 如果没传或传 ALL，直接查询所有激活语言的 ID
                // 优化点：直接查 id，不需要先查 key 再查 id
                $langSql = "SELECT id FROM kln_i18n_languages"; 
                $langRows = common::excuteListSql($langSql);
                if ($langRows) {
                    $langIds = array_column($langRows, 'id');
                }
            } else {
                // 2. 如果指定了语言，使用 IN 查询一次性获取
                $langs = explode(',', $langkey);
                // 安全过滤：防止 SQL 注入，并确保只处理非空字符串
                $safeLangs = array();
                foreach ($langs as $l) {
                    $l = trim($l);
                    if (!empty($l)) {
                        $safeLangs[] = "'" . common::check_input($l) . "'";
                    }
                }
                if (!empty($safeLangs)) {
                    // 拼接 IN 条件：('english', 'french')
                    $inClause = implode(',', $safeLangs);
                    $langSql = "SELECT id FROM kln_i18n_languages WHERE lang_key IN ($inClause)";
                    $langRows = common::excuteListSql($langSql);
                    if ($langRows) {
                        $langIds = array_column($langRows, 'id');
                    }
                }
            }

            $pageData =  $this->_loadPageInfo($langIds);
            $langData =  $this->_loadLangInfo($langIds);

            common::echo_json_encode(200,array("pageData" =>$pageData,"langData" =>$langData));
        }

        /*
         * multilingual_load
        */
        if ($operate == "multilingual_init") {
            $this->_multilingual_init();
        }

        if ($operate == "multilingual_search") {
            $result = $this->_multilingual_search();
            common::echo_json_encode(200,$result);
        }

        /*
         * multilingual_save
         */
        if ($operate == "multilingual_save") {
            $multilingual_param = $_POST['multilingual_param'];
            $this->_multilingual_save($multilingual_param);
        }
    }

    private function _multilingual_init() {
        // 1. 获取并解析 JSON 数据
        $sql = "select * from public.kln_i18n_pages_init where page = 'login'";
        $data = common::excuteObjectSql($sql);
        $rawData = $data['data'];
        $mode = "update";

        $json = json_decode($rawData, true);
        if (!$json) {
            common::echo_json_encode(500, "Invalid JSON format");
            exit();
        }

        // 获取控制模式，默认为 'update'
        $mode = $mode == 'replace' ? 'replace' : 'update';

        $pageKey = isset($json['page']) ? common::check_input($json['page']) : '';
        $unverifiedNumber = isset($json['unverifiedNumber']) ? intval($json['unverifiedNumber']) : 0;
        $dataList = isset($json['data']) ? $json['data'] : [];
        //tracking   report  booking    destinationDelivery
        if (empty($pageKey)) {
            common::echo_json_encode(500, "Missing page key");
            exit();
        }

        // 2. 定义语言映射配置
        $langMap = [
            'traditionalChinese' => ['name' => '繁体中文', 'code' => 'zh-TW'],
            'simplifiedChinese'  => ['name' => '简体中文', 'code' => 'zh-CN'],
            'english'            => ['name' => '英语', 'code' => 'en-US'],
            'french'             => ['name' => '法语', 'code' => 'fr-FR'],
            'spanish'            => ['name' => '西班牙语', 'code' => 'es-ES'],
            'portuguese'         => ['name' => '葡萄牙语', 'code' => 'pt-BR']
        ];

        global $db; 
        $db->StartTrans();
        try {
            // 第一步：处理页面表 (kln_i18n_pages)
            $pageId = null;

            // --- 模式判断：如果是覆盖模式，先删除旧数据 ---
            if ($mode === 'replace') {
                // 1. 先删子表 (kln_i18n_keys)
                $kikSqlDelete = "DELETE FROM kln_i18n_keys WHERE page_id IN (SELECT id FROM kln_i18n_pages WHERE page_key = '$pageKey')";
                $db->Execute($kikSqlDelete) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $kikSqlDelete), 0));

                // 2. 再删主表 (kln_i18n_pages) - 虽然上面删了子表，这里为了保险或者重置页面信息也可以删，
                $delPageSql = "DELETE FROM kln_i18n_pages WHERE page_key = '$pageKey'";
                $db->Execute($delPageSql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $delPageSql), 0));
            }

            // --- 查询页面是否存在 ---
            $checkPageSql = "SELECT id FROM kln_i18n_pages WHERE page_key = '$pageKey'";
            $checkPageRes =  $db->GetRow($checkPageSql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $checkPageSql), 0));
            
            if (!empty($checkPageRes)) {
                // 存在 -> 更新
                $pageId = $checkPageRes['id'];
                $updatePageSql = "UPDATE kln_i18n_pages SET unverified_number = $unverifiedNumber WHERE id = $pageId";
                $db->Execute($updatePageSql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $updatePageSql), 0));
            } else {
                // 不存在 -> 插入
                $insertPageSql = "INSERT INTO kln_i18n_pages (page_key, unverified_number, description) VALUES ('$pageKey', $unverifiedNumber, '')";
                $db->Execute($insertPageSql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $insertPageSql), 0));
                
                // 获取新生成的 ID (PostgreSQL)
                $seqSql = "SELECT currval('kln_i18n_pages_id_seq')";
                $pageIdRow = $db->GetRow($seqSql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $seqSql), 0));
                $pageId = $pageIdRow['currval'];
            }

            // 第二步：循环处理数据
            foreach ($dataList as $item) {
                $transKey = $item['key'];
                $originValue = isset($item['orginEnglish']) ? $item['orginEnglish'] : '';

                foreach ($langMap as $jsonKey => $langInfo) {
                    // 只处理 JSON 中存在的数据
                    if (isset($item[$jsonKey])) {
                        $transValue = common::check_input($item[$jsonKey]);
                        $statusKey = $jsonKey . 'Status';
                        $status = isset($item[$statusKey]) ? intval($item[$statusKey]) : 0;

                        // --- 1. 处理语言表 (kln_i18n_languages) ---
                        $langKey = common::check_input($jsonKey); 
                        $langName = common::check_input($langInfo['name']);
                        $localeCode = common::check_input($langInfo['code']);

                        // 查询语言是否存在
                        $checkLangSql = "SELECT id FROM kln_i18n_languages WHERE lang_key = '$langKey'";
                        $checkLangRes = $db->GetRow($checkLangSql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $checkLangSql), 0));

                        if (!empty($checkLangRes)) {
                            // 存在 -> 更新
                            $langId = $checkLangRes['id'];
                            $updateLangSql = "UPDATE kln_i18n_languages SET lang_name = '$langName', locale_code = '$localeCode' WHERE id = $langId";
                            $db->Execute($updateLangSql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $updateLangSql), 0));
                        } else {
                            // 不存在 -> 插入
                            $insertLangSql = "INSERT INTO kln_i18n_languages (lang_key, lang_name, locale_code, is_active) VALUES ('$langKey', '$langName', '$localeCode', true)";
                            $db->Execute($insertLangSql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $updateLangSql), 0));
                            
                            // 获取 ID
                            $seqLangSql = "SELECT currval('kln_i18n_languages_id_seq')";
                            $langIdRow = $db->GetRow($seqLangSql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $seqLangSql), 0));
                            $langId = $langIdRow['currval'];
                        }

                        // --- 2. 处理词条表 (kln_i18n_keys) ---
                        // 查询词条是否存在
                        $checkKeySql = "SELECT id FROM kln_i18n_keys WHERE page_id = $pageId AND lang_id = $langId AND trans_key = '$transKey'";
                        $checkKeyRes = $db->GetRow($checkKeySql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $checkKeySql), 0));

                        if (!empty($checkKeyRes)) {
                            // 存在 -> 更新
                            $updateKeySql = "UPDATE kln_i18n_keys SET trans_value = '$transValue', orgin_value = '$originValue', status = $status WHERE id = " . $checkKeyRes['id'];
                            $db->Execute($updateKeySql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $updateKeySql), 0));
                        } else {
                            // 不存在 -> 插入
                            $insertKeySql = "INSERT INTO kln_i18n_keys (page_id, lang_id, trans_key, trans_value, orgin_value, status) VALUES ($pageId, $langId, '$transKey', '$transValue', '$originValue', $status)";
                            $db->Execute($insertKeySql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $insertKeySql), 0));
                        }
                    }
                }
            }

        } catch (Exception $e) {
            // 捕获异常，标记错误，以便回滚
            error_log("Exception in multilingual_init: " . $e->getMessage());
        }
        // 结束事务
        if ($db->CompleteTrans() === FALSE) {
            $msg = 'Save Error';
        } else {
            $msg = 'Save Success';
        }
        common::echo_json_encode(200, $msg);
        exit();
    }
        
    /**
     * 根据传入的 page_key 查询多语言信息并组装成 JSON 格式
     * 支持多个页面查询，将所有数据整合到一个 data 数组中
     *
     * @param string|array $pageKey 页面标识 (例如 'login' 或 ['login', 'register'])
     * @return array 返回组装好的数组 (通常最后会 json_encode 输出)
    */
    public function _multilingual_search() {

        $pageKey = $_POST['pagekey'];
        $pageIds = [];
        // 1. 统一处理 $pageIds 数组
        if (empty($pageKey) || strtoupper($pageKey) == "ALL") {
            // 如果没传或传 ALL，直接查询所有页面的 ID
            $pageSql = "SELECT id FROM kln_i18n_pages";
            $pageRows = common::excuteListSql($pageSql);
            
            if ($pageRows) {
                $pageIds = array_column($pageRows, 'id');
            }
        } else {
            // 2. 如果指定了页面，使用 IN 查询一次性获取
            $pages = explode(',', $pageKey);
            // 安全过滤：防止 SQL 注入，并确保只处理非空字符串
            $safePages = array();
            foreach ($pages as $p) {
                $p = trim($p);
                if (!empty($p)) {
                    $safePages[] = "'" . common::check_input($p) . "'";
                }
            }
            if (!empty($safePages)) {
                $inClause = implode(',', $safePages);
                // 优化点：一次查询获取所有匹配的 ID 和 unverified_number
                $pageSql = "SELECT id, unverified_number FROM kln_i18n_pages WHERE page_key IN ($inClause)";
                $pageRows = common::excuteListSql($pageSql);
                
                if ($pageRows) {
                    $pageIds = array_column($pageRows, 'id');
                }
            }
        }
        // 3. 如果没有找到任何页面，返回空数组
        if (empty($pageIds)) {
            return array();
        }

        //查询指定语言
        $langIds = [];
        $langkey = $_POST['langKey'];
        // 1. 统一处理 $langs 数组
        if (empty($langkey) || strtoupper($langkey) == "ALL") {
            // 如果没传或传 ALL，直接查询所有激活语言的 ID
            // 优化点：直接查 id，不需要先查 key 再查 id
            $langSql = "SELECT id FROM kln_i18n_languages"; 
            $langRows = common::excuteListSql($langSql);
                
            if ($langRows) {
                $langIds = array_column($langRows, 'id');
            }
        } else {
            // 2. 如果指定了语言，使用 IN 查询一次性获取
            $langs = explode(',', $langkey);
            // 安全过滤：防止 SQL 注入，并确保只处理非空字符串
            $safeLangs = array();
            foreach ($langs as $l) {
                $l = trim($l);
                if (!empty($l)) {
                    $safeLangs[] = "'" . common::check_input($l) . "'";
                }
            }
            if (!empty($safeLangs)) {
                // 拼接 IN 条件：('english', 'french')
                $inClause = implode(',', $safeLangs);
                $langSql = "SELECT id FROM kln_i18n_languages WHERE lang_key IN ($inClause)";
                $langRows = common::excuteListSql($langSql);
                if ($langRows) {
                    $langIds = array_column($langRows, 'id');
                }
            }
        }

        // 4. 查询所有指定页面的翻译词条（一次查询获取所有数据）
        $sql = "SELECT 
                    k.trans_key, 
                    k.trans_value, 
                    k.status, 
                    k.orgin_value,
                    l.lang_key,
                    p.page_key AS key_page
                FROM kln_i18n_keys k
                JOIN kln_i18n_languages l ON k.lang_id = l.id
                JOIN kln_i18n_pages p ON k.page_id = p.id
                WHERE k.page_id IN (" . implode(',', $pageIds) . ")
                AND k.lang_id IN (" . implode(',', $langIds) . ")
                ORDER BY k.trans_key, p.page_key";
        
        $rows = common::excuteListSql($sql);
        
        if ($rows === FALSE) {
            return array();
        }
        
        // 5. 数据重组（核心逻辑）
        $dataMap = array();
        
        foreach ($rows as $row) {
            $key = $row['trans_key'];
            $langKey = $row['lang_key'];
            
            // 如果这个 key 还没初始化，先初始化
            if (!isset($dataMap[$key])) {
                $dataMap[$key] = array(
                    'key' => $key,
                    'key_page' => $row['key_page']  // 记录所属页面
                );
            }
            
            // 动态填充语言字段
            $dataMap[$key][$langKey] = $row['trans_value'];
            
            // 填充状态字段
            $dataMap[$key][$langKey . 'Status'] = intval($row['status']);
            
            // 填充原始值（如果有）
            //if (!empty($row['orgin_value'])) {
            $dataMap[$key]['orginEnglish'] = $row['orgin_value'];
            //}
        }
        
        // 6. 将 Map 转换为数组列表
        $dataList = array_values($dataMap);
        
        // 7. 组装最终结果
        $result = array(
            'page'             => $pageKey,  
            'data'             => $dataList
        );

        //重新查询一下page load
        $pageData =  $this->_loadPageInfo($langIds);
        $result['pageData'] = $pageData;

        //langIds传空，意思是查全部语言
        $langData =  $this->_loadLangInfo(null,$pageIds);
        $result['langData'] = $langData;
        return $result;
    }

    /**
     * 保存多语言数据
     * 
     * @param array $multilingual_param 页面未审核数量参数，格式：[{unverifiedNumber: 3, page: 'login'}, ...]
     * @param array $multilingual_trans_param 翻译状态参数，格式：[{page: 'login', lang: 'traditionalChinese', trans_key: 'username', status: 1}, ...]
     * @return bool 是否保存成功
     */
    public function _multilingual_save($multilingual_param) {
        global $db;
        try {
            // 开启事务确保数据一致性
            $db->StartTrans();
            //2. 处理翻译词条状态更新
            foreach ($multilingual_param as $param) {
                $pageKey = common::check_input($param['page']);
                $langKey = common::check_input($param['lang']);
                $transKey = common::check_input($param['trans_key']);
                $trans_value = common::check_input($param['trans_value']);
                $status = $param['status'];
                
                // 查询页面ID
                $pageSql = "SELECT id FROM kln_i18n_pages WHERE page_key = '".$pageKey."'";
                $pageId = $db->GetOne($pageSql);
                if (!$pageId) {
                    throw new Exception("Page not found: " . $pageKey);
                }
                
                // 查询语言ID
                $langSql = "SELECT id FROM kln_i18n_languages WHERE lang_key = '".$langKey."'";
                $langId = $db->GetOne($langSql);
                if (!$langId) {
                    throw new Exception("Language not found: " . $langKey);
                }
                // 更新翻译状态
                $updateTransSql = "UPDATE kln_i18n_keys SET status = $status, trans_value = '$trans_value' ".
                                " WHERE page_id = " . $pageId . 
                                " AND lang_id = " . $langId . 
                                " AND trans_key = '".$transKey."'";
                $db->Execute($updateTransSql);
            }
        } catch (Exception $e) {
            // 捕获异常，标记错误，以便回滚
            error_log("Exception in multilingual_save: " . $e->getMessage());
        }
        // 结束事务
        if ($db->CompleteTrans() === FALSE) {
            $msg = 'Save Error';
        } else {
            $msg = 'Save Success';
        }
        common::echo_json_encode(200, $msg);
        exit();
    }


    /**
     *  load page info
    */
    public function _loadPageInfo($langIds){
        $pageSql = "SELECT 
                (select page_key from public.kln_i18n_pages where id = page_id) as page_key,
                COUNT(*) AS unverified_count
            FROM (
                    SELECT 
                        page_id,
                        trans_key,
                        BOOL_OR(status = 0) AS all_unverified
                    FROM kln_i18n_keys
                    WHERE lang_id IN (" . implode(',', $langIds) . ")
                    GROUP BY page_id, trans_key
                ) AS subquery
            WHERE all_unverified = TRUE GROUP BY page_id;";

        $pageData = common::excuteListSql($pageSql);
        $unverified_count = 0;
        foreach($pageData as $_pageData){
            $unverified_count += $_pageData['unverified_count'];
        }
        $newItem = [
            'page_key' => 'all', 
            'unverified_count' =>$unverified_count
        ];
        // 将新数组插入到 $pageDate 的开头
        array_unshift($pageData, $newItem);

        return $pageData;
    }

    /**
     *  load lang info
    */
    public function _loadLangInfo($langIds,$pageIds = array()){

        $page_id_sql = "";
        if(!empty($pageIds)){
            $page_id_sql = "AND k.page_id IN (" . implode(',', $pageIds) . ")";
        }

        $lang_id_sql = "";
        if(!empty($langIds)){
            $lang_id_sql = "AND l.id IN (" . implode(',', $langIds) . ")";
        }

        $langSql = "SELECT 
            l.lang_key, 
            COUNT(k.id) AS unverified_count
        FROM kln_i18n_languages l
        LEFT JOIN kln_i18n_keys k ON l.id = k.lang_id 
            $page_id_sql  
            AND k.status = 0    
        WHERE 
            l.is_active = TRUE
            $lang_id_sql
        GROUP BY 
            l.id, l.lang_key, l.lang_name;";

        $langData = common::excuteListSql($langSql);

        $unverified_count = 0;
        $retLangData = array('all'=>0);
        foreach($langData as $_langData){
            $unverified_count += $_langData['unverified_count'];
            $retLangData[$_langData['lang_key']] = intval($_langData['unverified_count']);
        }
        $retLangData['all'] =$unverified_count; 
        return $retLangData;
    }
}

?>