k_online_backend/service/robot.class.php

<?php

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

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

    private static $_robot;

    function __construct() {  
       
    }

    public static function getInstance() {
        global $memory_limit;
        $memory_limit = ini_get("memory_limit");
        ini_set("memory_limit", '2048M');
        if (!self::$_robot) {
            $c = __CLASS__;
            self::$_robot = new $c;
        }
        return self::$_robot;
    }

    public function robot_chat_log() {
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if (empty($operate)) {
            $column = column::getInstance()->getDisplayColumn('Robot_Chat_Search');
            $OperationTableColumns = column::getInstance()->tableColumns('Robot_Chat_Search',$column);
            $data['OperationTableColumns'] = $OperationTableColumns;
            common::echo_json_encode(200,$data);
            exit();
        }

        if ($operate == "search") {
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 10;

            $sqlWhere = ' where 1=1';
            $text_search = common::check_input($_POST ['text_search']); 
            $user_type = common::check_input($_POST ['user_type']);
            $question_type = common::check_input($_POST ['question_type']); 
            $answer_type = common::check_input($_POST ['answer_type']); 
            $answer_satisfication = common::check_input($_POST ['answer_satisfication']);
            $response_duration_type = common::check_input($_POST ['response_duration_type']);  
            $response_duration_num = common::check_input($_POST ['response_duration_num']); 
            
            if (!empty($text_search)){
                $sqlWhere .= " and (question_id ilike '%" . $text_search . "%' or user_name ilike '%" . $text_search . "%')";
            }
            if (isset($_POST['question_date_start']) && !empty($_POST['question_date_start'])){
                $sqlWhere .= " and question_date >= '" . common::usDate2sqlDate($_POST['question_date_start']) . " 00:00:00'";
            }
            if (isset($_POST['question_date_end']) && !empty($_POST['question_date_end'])){
                $sqlWhere .= " and question_date <= '" . common::usDate2sqlDate($_POST['question_date_end']) . " 23:59:59'";
            }

            if (!empty($user_type)){
                $sqlWhere .= " and user_type = '".strtolower($user_type)."'";
            }
            if (!empty($question_type)){
                //$question_type = $question_type == 'Free Text' ? "Free Question" : $question_type;
                $sqlWhere .= " and question_type = '$question_type'";
            }
            if (!empty($answer_type)){
                $sqlWhere .= " and answer_type = '$answer_type'";
            }
            if (!empty($answer_satisfication)){
                $answer_satisfication = $answer_satisfication == 'Null' ? '' : $answer_satisfication;
                $sqlWhere .= " and COALESCE(answer_satisfication,'') = '$answer_satisfication'";
            }
            if (!empty($response_duration_type) && !empty($response_duration_num)){
               if($response_duration_type == "thanOrEqual"){
                    $sqlWhere .= " and response_duration >= '$response_duration_num'";
               } elseif ($response_duration_type == "equal"){
                    $sqlWhere .= " and response_duration = '$response_duration_num'";
               } else {
                    $sqlWhere .= " and response_duration <= '$response_duration_num'";
               }
            }
           
            $rc = $_POST ['rc'];
            if ($rc == - 1) {
                $sql = "SELECT count(1) from public.kln_robot_chat_log" . $sqlWhere;
                error_log($sql);
                $rc = common::excuteOneSql($sql);
            }
            $tp = ceil($rc / $ps);
            $order_by = " id desc";
        
            if ($rc > 0) {
                $sql = "SELECT ".column::getInstance()->getSearchSqlForDisplay('Robot_Chat_Search').", 
                    to_char(response_time, 'YYYY/MM/DD HH24:MI:SS') as _response_time,
                    to_char(request_time, 'YYYY/MM/DD HH24:MI:SS') as _request_time,
                    request_id 
                from public.kln_robot_chat_log " . $sqlWhere . 
                            " order by $order_by limit " . $ps . " offset " . ($cp - 1) * $ps;
                $rs = common::excuteListSql($sql);
                foreach($rs as $key => $val){
                    $rs[$key]["response_time"] = $val["_response_time"];
                    $rs[$key]["request_time"] = $val["_request_time"];
                }
                $arrTmp = array('searchData' => $rs, 
                'tmp_search' => common::deCode($sql, 'E'),
                'rc' => intval($rc),
                'ps' => intval($ps),
                'cp' => intval($cp),
                'tp' => intval($tp));
            } else {
            $arrTmp = array('searchData' => array(), 
                'tmp_search' => common::deCode($sql, 'E'),
                'rc' => intval($rc),
                'ps' => intval($ps),
                'cp' => intval($cp),
                'tp' => intval($tp));
            }
            common::echo_json_encode(200,$arrTmp);
            exit();
        }

        if ($operate == "excel") {
            $sql = common::deCode($_POST ['tmp_search'], 'D');
            $sql = substr($sql, 0, strripos($sql, " limit"));
            if(!empty($sql)){
                $rss = common::excuteListSql($sql);
            }
            foreach($rss as $key =>$val){
                $rs[$key]["response_time"] = $val["_response_time"];
                $rs[$key]["request_time"] = $val["_request_time"];
            }
            common::echo_json_encode(200,array("msg"=>"success","Data" => $rss));
            exit;
        }

        if ($operate == "api_log") {
            $request_id = common::check_input($_POST['request_id']);
            $sql = "select question_content,ai_response_content from public.kln_robot_chat_log where request_id = '$request_id'";
            $data = common::excuteObjectSql($sql);
            $question_content = $data['question_content'];
            $ai_response_content = json_decode($data['ai_response_content']);
            common::echo_json_encode(200,array("msg"=>"success","Data" => array("request_content" =>$question_content, "ai_response_content"=>$ai_response_content)));
            //common::echo_json_encode(200,array("msg"=>"success","Data" => $data));
            exit;
        }
        
    }

    public function robot_api_log() {
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if (empty($operate)) {
            $column = column::getInstance()->getDisplayColumn('Robot_API_Search');
            $OperationTableColumns = column::getInstance()->tableColumns('Robot_API_Search',$column);
            $data['OperationTableColumns'] = $OperationTableColumns;
            common::echo_json_encode(200,$data);
            exit();
        }

        if ($operate == "search") {
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 10;

            //只查询自由问题
            $sqlWhere = " where 1=1 and question_type = 'Free Text' ";
            $text_search = common::check_input($_POST ['text_search']); 
            $ai_model = common::check_input($_POST ['ai_model']); 
            $response_duration_type = common::check_input($_POST ['response_duration_type']);  
            $response_duration_num = common::check_input($_POST ['response_duration_num']);
            
            
            if (!empty($text_search)){
                $sqlWhere .= " and (question_id ilike '%" . $text_search . "%' or request_id ilike '%" . $text_search . "%')";
            }
            if (!empty($ai_model)){
                $sqlWhere .= " and lower(ai_model) = '".strtolower($ai_model)."'";
            }
            if (isset($_POST['request_date_start']) && !empty($_POST['request_date_start']))
                $sqlWhere .= " and request_time >= '" . common::usDate2sqlDate($_POST['request_date_start']) . " 00:00:00'";
            if (isset($_POST['request_date_end']) && !empty($_POST['request_date_end']))
                $sqlWhere .= " and request_time <= '" . common::usDate2sqlDate($_POST['request_date_end']) . " 23:59:59'";

            if (!empty($response_duration_type) && !empty($response_duration_num)){
               if($response_duration_type == "thanOrEqual"){
                    $sqlWhere .= " and response_duration >= '$response_duration_num'";
               } elseif ($response_duration_type == "equal"){
                    $sqlWhere .= " and response_duration = '$response_duration_num'";
               } else {
                    $sqlWhere .= " and response_duration <= '$response_duration_num'";
               }
            }

            $rc = $_POST ['rc'];
            if ($rc == - 1) {
                $sql = "SELECT count(1) from public.kln_robot_chat_log" . $sqlWhere;
                $rc = common::excuteOneSql($sql);
            }
            $tp = ceil($rc / $ps);
            $order_by = " id desc";
        
            if ($rc > 0) {
                $sql = "SELECT ".column::getInstance()->getSearchSqlForDisplay('Robot_API_Search').", 
                    to_char(response_time, 'YYYY/MM/DD HH24:MI:SS') as _response_time,
                    to_char(request_time, 'YYYY/MM/DD HH24:MI:SS') as _request_time 
                from public.kln_robot_chat_log " . $sqlWhere . 
                            " order by $order_by limit " . $ps . " offset " . ($cp - 1) * $ps;
                $rs = common::excuteListSql($sql);
                foreach($rs as $key => $val){
                    $rs[$key]["response_time"] = $val["_response_time"];
                    $rs[$key]["request_time"] = $val["_request_time"];
                }
                $arrTmp = array('searchData' => $rs, 
                'tmp_search' => common::deCode($sql, 'E'),
                'rc' => $rc,
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp);
            } else {
            $arrTmp = array('searchData' => array(), 
                'tmp_search' => common::deCode($sql, 'E'),
                'rc' => $rc,
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp);
            }
            common::echo_json_encode(200,$arrTmp);
            exit();
        }

        if ($operate == "excel") {
            $sql = common::deCode($_POST ['tmp_search'], 'D');
            $sql = substr($sql, 0, strripos($sql, " limit"));
            if(!empty($sql)){
                $rss = common::excuteListSql($sql);
            }
            foreach($rss as $key =>$val){
                $rs[$key]["response_time"] = $val["_response_time"];
                $rs[$key]["request_time"] = $val["_request_time"];
            }
            common::echo_json_encode(200,array("msg"=>"success","Data" => $rss));
            exit;
        }

        if ($operate == "api_log") {
            $request_id = common::check_input($_POST['request_id']);
            $sql = "select request_content,ai_response_content from public.kln_robot_chat_log where request_id = '$request_id'";
            $data = common::excuteObjectSql($sql);
            common::echo_json_encode(200,array("msg"=>"success","Data" => $data));
            exit;
        }
        
    }

    public function robot_prompt_configuration() {
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if (empty($operate)) {
           //查询所有配置信息 目前只有一个表一个数据
            $configuration = common::excuteObjectSql("select id, character_name,professional_field,main_tasks,table_name,table_description,
                table_structure_configuration,response_rule_configuration,output_format_configuration,fixed_problem_configuration
            from public.kln_robot_prompt_configuration where id = 1");

            $PromptAndVue = $this->dealPromptFormatAndVue($configuration);
            //tableDat-VUE 
            $tableData = $PromptAndVue["vuedata"]["tableData"];
            //stepData -VUE
            $stepData = $PromptAndVue["vuedata"]["stepData"]; 
            //formatList -VUE
            $formatListType  = $PromptAndVue["vuedata"]["formatListType"];
            $formatList = $PromptAndVue["vuedata"]["formatList"];
            //promptFormat
            $promptFormat =$PromptAndVue["promptFormat"];

            //获取历史变更记录
            $old_configuration = $this->getChangedLog($configuration["id"]);

            $id = $configuration['id'];
            $character_name = $configuration['character_name'];
            $professional_field = $configuration['professional_field'];
            $main_tasks =  $configuration['main_tasks'];
            $table_name = $configuration['table_name'];
            $table_description = $configuration['table_description'];

            $prompt_summary = array("您是专門从事".$professional_field."的".$character_name."...",
                "表名：{".$table_name."}...",
                "响应规则，请按以下步骤处理...");

            $data = array(
                "id"=>$id,
                "character_name"=>$character_name,
                "professional_field"=>$professional_field,
                "main_tasks"=>$main_tasks,
                "table_name"=>$table_name,
                "table_description"=>$table_description,
                "tableData"=>$tableData,
                "stepData"=>$stepData,
                "formatList"=>$formatList,
                "formatListType"=>$formatListType,
                "complete_prompt"=>$promptFormat,
                "prompt_summary"=>$prompt_summary,
                "prompt_log_record"=>$old_configuration
            );
            $data = utils::arrayRemoveNull($data);
            common::echo_json_encode(200,$data);                
            exit(); 
        }
        if ($operate == "save") {
            $id =common::check_input($_POST['id']);
            $character_name = common::check_input($_POST['role_name']);
            $professional_field = common::check_input($_POST['professional_field']);
            $main_tasks = common::check_input($_POST['main_tasks']);
            $table_name = common::check_input($_POST['table_name']);
            $table_description = common::check_input($_POST['table_description']);
            $outputvalue = common::check_input($_POST['outputvalue']);

            $tableDataList = $_POST['tableDataList'];
            $stepData = $_POST['stepData'];
            $formatList = $_POST['formatList'];


            $table_structure_configuration = common::check_input(json_encode($tableDataList));
            $response_rule_configuration = common::check_input(json_encode($stepData));

            $output_data = array("output_type"=>$outputvalue,"data"=>$formatList);
            $output_format_configuration = common::check_input(json_encode($output_data));

            if(empty($id)){
                $sql = "INSERT INTO public.kln_robot_prompt_configuration(
                        character_name, professional_field, main_tasks, table_name, 
                        table_description, table_structure_configuration, response_rule_configuration, 
                        output_format_configuration, created_by, created_time)
                VALUES ('$character_name', '$professional_field', '$main_tasks', '$table_name', 
                        '$table_description', '$table_structure_configuration', '$response_rule_configuration', 
                        '$output_format_configuration', '"._getLoginName()."', now());";
            }else{
                $sql = "update public.kln_robot_prompt_configuration 
                    set character_name = '$character_name',professional_field ='$professional_field', main_tasks = '$main_tasks',
                        table_name = '$table_name',table_description = '$table_description',
                        table_structure_configuration = '$table_structure_configuration',
                        response_rule_configuration = '$response_rule_configuration',
                        output_format_configuration = '$output_format_configuration'
                where id = '$id';";  

                //插入变更日志记录
                $sql .= "INSERT INTO public.kln_robot_prompt_configuration_log(ref_id, character_name, professional_field, main_tasks, table_name, 
                        table_description, table_structure_configuration, response_rule_configuration, 
                        output_format_configuration, created_by, created_time)
                VALUES ('$id','$character_name', '$professional_field', '$main_tasks', '$table_name', 
                        '$table_description', '$table_structure_configuration', '$response_rule_configuration', 
                        '$output_format_configuration', '"._getLoginName()."', now());";

            }
            error_log($sql);
            $rs = common::excuteUpdateSql($sql);
            if (!$rs) {
                $data = array("msg" => "save Error");
            } else {
                $data = array("msg" => "save Successful");
            }
            $data = array("msg" => "save Successful");
            common::echo_json_encode(200,$data);
            exit();
        }

        if($operate == "preview_propmpt_witout_save"){
            $character_name = common::check_input($_POST['role_name']);
            $professional_field = common::check_input($_POST['professional_field']);
            $main_tasks = common::check_input($_POST['main_tasks']);
            $table_name = common::check_input($_POST['table_name']);
            $table_description = common::check_input($_POST['table_description']);
            $outputvalue = common::check_input($_POST['outputvalue']);

            $tableDataList = $_POST['tableDataList'];
            $stepData = $_POST['stepData'];
            $formatList = $_POST['formatList'];
            $table_structure_configuration = common::check_input(json_encode($tableDataList));
            $response_rule_configuration = common::check_input(json_encode($stepData));
            $output_data = array("output_type"=>$outputvalue,"data"=>$formatList);
            $output_format_configuration = common::check_input(json_encode($output_data));

            //组合拼接需要的数据
            $configuration = array("character_name"=>$character_name,
                "professional_field"=>$professional_field,
                "main_tasks"=>$main_tasks,
                "table_name"=>$table_name,
                "table_description"=>$table_description,
                "professional_field"=>$professional_field,
                "table_structure_configuration"=>$table_structure_configuration,
                "response_rule_configuration" =>$response_rule_configuration,
                "output_format_configuration" =>$output_format_configuration
             );
            $PromptAndVue = $this->dealPromptFormatAndVue($configuration);
            //promptFormat
            $promptFormat =$PromptAndVue["promptFormat"];
            $character_name = empty($configuration['character_name']) ? "" :$configuration['character_name'];
            $professional_field = empty($configuration['professional_field']) ? "" :$configuration['professional_field'];
            $main_tasks =  empty($configuration['main_tasks']) ? "" :$configuration['main_tasks'];
            $table_name = empty($configuration['table_name']) ? "" :$configuration['table_name'];
            $table_description = empty($configuration['table_description']) ? "" :$configuration['table_description'];
            $prompt_summary = array("您是专門从事".$professional_field."的".$character_name."...",
                "表名：{".$table_name."}...",
                "响应规则，请按以下步骤处理...");

            $data = array(
                "complete_prompt"=>$promptFormat,
                "prompt_summary"=>$prompt_summary
            );
            common::echo_json_encode(200,$data);                
            exit();
        }

        if($operate == "test_with_ds_claude"){
            //$data =  "### 查询结果\n\n- **总hbol数量**: 3581667\n\n- **前10票hbol信息**:\n\n  | HBOL | MBOL | 运输方式 | 服务类型 | 发货人 | 收货人 | 数量 | 总重量(KG) | 预计离港时间 | 预计到港时间 |\n  |------|------|----------|----------|--------|--------|------|------------|--------------|--------------|\n    | E1205546127 | UASUCNNBO432262 | sea | CY/CY | HANGZHOU ZHONGSHEN BAGS & LUGGAGES CO.LTD. | C.Y IMPORT CORPORATION | 455 | 8689.000 | 2012-05-31 | 2012-07-09 |\n| E120537061 | HJSCFOC202818100 | sea | CY/DOOR | NANLIN(FUJIAN) INDUSTRIES CO.,LTD | CHICAGO STOOL & CHAIR INC | 1200 | 14400.000 | 2012-05-31 | 2012-06-18 |\n| E120537095 | MAEU602116826 | sea | CY/CY | LONG SHAN IMP & EXP CO LTD | COMPOSITE TECHNOLOGY INTERNATIONAL INC | 12 | 19900.000 | 2012-05-27 | 2012-07-02 |\n| E1205570012 | COSU6025917580 | sea | CY/DOOR | SHAANXI FUHUA CHEMICAL CO., LTD | CIMBAR PERFORMANCE MINERALS | 38 | 34396.000 | 2012-05-29 | 2012-07-06 |\n| E1205391561A | YMLUW230775660 | sea | CFS/CY | WISTRON NEWEB (KUNSHAN) CORPORATION | ARUBA NETWORKS | 30 | 391.000 | 2012-05-29 | 2012-06-16 |\n| A1208395110 | ZIMUSNH5455049 | sea | CFS/CFS | SHANGHAI EAST BEST INT'L BUSINESS DEV.CO | LUMINA GLOBAL, INC. | 2 | 2600.000 | 2012-08-25 | 2012-09-06 |\n| E1205391561 | YMLUW230775660 | sea | CY/CY | SERCOMM CORP. C/O SERNET | ARUBA NETWORKS | 354 | 2833.940 | 2012-05-29 | 2012-06-16 |\n| E1205395237 | YMLUW230777029 | sea | CY/CY | UNIPAK RESOURCES CO.,LTD. | PAN PACIFIC PLASTIC | 404 | 2424.000 | 2012-05-29 | 2012-06-16 |\n| X1205480323 | YMLUW241023500 | sea | CY/CY | SHANGHAI AUTOMOBILE IMPORT&EXPORT CO.,LTD. | AMERICAN CONDENSER | 1082 | 5178.000 | 2012-05-25 | 2012-06-12 |\n| E1205359270 | MAEU602108615 | sea | CY/CY | LZ CASKET MANUFACTURING CO.,LTD | BRICK ALLEY,LLC | 64 | 5901.700 | 2012-05-29 | 2012-07-06 |\n ";
            //$data =  "### 查询结果\n\n- **总hbol数量**: 3581667\n\n- **前10票hbol信息**:\n\n| HBOL | MBOL | 运输方式 | 服务类型 | 发货人 | 收货人 | 数量 | 总重量(KG) | 预计离港时间 | 预计到港时间 |\n|------|------|----------|----------|--------|--------|------|------------|--------------|--------------|\n| E1205546127 | UASUCNNBO432262 | sea | CY/CY | HANGZHOU ZHONGSHEN BAGS & LUGGAGES CO.LTD. | C.Y IMPORT CORPORATION | 455 | 8689.000 | 2012-05-31 | 2012-07-09 |\n| E120537061 | HJSCFOC202818100 | sea | CY/DOOR | NANLIN(FUJIAN) INDUSTRIES CO.,LTD | CHICAGO STOOL & CHAIR INC | 1200 | 14400.000 | 2012-05-31 | 2012-06-18 |\n| E120537095 | MAEU602116826 | sea | CY/CY | LONG SHAN IMP & EXP CO LTD | COMPOSITE TECHNOLOGY INTERNATIONAL INC | 12 | 19900.000 | 2012-05-27 | 2012-07-02 |\n| E1205570012 | COSU6025917580 | sea | CY/DOOR | SHAANXI FUHUA CHEMICAL CO., LTD | CIMBAR PERFORMANCE MINERALS | 38 | 34396.000 | 2012-05-29 | 2012-07-06 |\n| E1205391561A | YMLUW230775660 | sea | CFS/CY | WISTRON NEWEB (KUNSHAN) CORPORATION | ARUBA NETWORKS | 30 | 391.000 | 2012-05-29 | 2012-06-16 |\n| A1208395110 | ZIMUSNH5455049 | sea | CFS/CFS | SHANGHAI EAST BEST INT'L BUSINESS DEV.CO | LUMINA GLOBAL, INC. | 2 | 2600.000 | 2012-08-25 | 2012-09-06 |\n| E1205391561 | YMLUW230775660 | sea | CY/CY | SERCOMM CORP. C/O SERNET | ARUBA NETWORKS | 354 | 2833.940 | 2012-05-29 | 2012-06-16 |\n| E1205395237 | YMLUW230777029 | sea | CY/CY | UNIPAK RESOURCES CO.,LTD. | PAN PACIFIC PLASTIC | 404 | 2424.000 | 2012-05-29 | 2012-06-16 |\n| X1205480323 | YMLUW241023500 | sea | CY/CY | SHANGHAI AUTOMOBILE IMPORT&EXPORT CO.,LTD. | AMERICAN CONDENSER | 1082 | 5178.000 | 2012-05-25 | 2012-06-12 |\n| E1205359270 | MAEU602108615 | sea | CY/CY | LZ CASKET MANUFACTURING CO.,LTD | BRICK ALLEY,LLC | 64 | 5901.700 | 2012-05-29 | 2012-07-06 |\n";

            //$return = array("type"=>"markdown","data" =>$data);
            //common::echo_json_encode(200,$return);            
            //exit(); 
            //分担查询
            include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';

            $ai_method = $_POST["test_ai_method"];
            $systemPrompt = $_POST["prompt"];
            $message = $_POST["test_question"];
            $model = $ai_method == 'DS' ? "deepseek" : "claude";
            //question_date request_time
            $request_question_time = date("Y-m-d H:i:s");
            $question_time = date("H:i:s");

            $item_value = common::excuteOneSql("select item_value from public.config where item = 'AIAPISetting'");
            $config = json_decode($item_value,true);
            $response = AIClientFactory::create($model, $config[$model], $systemPrompt, $message, $history = []);
            $response_time = date("Y-m-d H:i:s");

            $input_token = "";
            $output_token = "";
            $date1 = new DateTime($request_question_time);
            $date2 = new DateTime($response_time);
            $response_duration = $date2->getTimestamp() - $date1->getTimestamp();

            //回答问题类型，在没有超时的情况下，固定:AI回答
            $answer_type = "AI Answer";
            if ($response_duration > 120){
                $answer_type = "Timeout";
            }
            //获取自然序列
            $sequence = common::getChatAiSequence();
            $name = $ai_method == 'DS' ? "DS" : "CD";   
            $request_id = "R".$name."".date("Ymd").$sequence; 
            $question_id = "Q"."".date("Ymd").$sequence; 

            //处理一下参数
            $user_name = common::check_input(_getLoginName());
            $user_type = _isApexLogin() ? "employee" : "customer";
            //这里的测试，固定写自由文本
            $question_type = 'Free Text';
            $question_id = common::check_input($question_id);
            $question_content = common::check_input($message);
            $answer_type = common::check_input($answer_type);
            //记录回答展示给客户的具体内容
            $answer = common::check_input(common::getChatAimessage($response['message']));

            $ai_model = common::check_input($model);
            $input_token = common::check_input($input_token);
            $request_content = common::check_input(json_encode($response['data']));
            $ai_response_content = common::check_input(json_encode($response['full_response']));
            $output_token = common::check_input($output_token);

            $answer_template = "";
            $message = json_decode(common::getChatAimessage($response['message']),true);
            if($message["can_query"] == "true" && !empty($message["sql"])){
                $reference = $message["reference"];
                $answer_template = common::check_input($message["reference"]);
                $sql = $message["sql"];
                //拆分sql 存在多条的情况
                $sqlArr = explode(";", $sql);
                //给所有sql 拼接用户权限
                $sqlWhere = '  ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
                //先不考虑总数，处理有难度
                foreach($sqlArr as $_sql){
                    error_log($_sql);
                    if(empty($_sql)){
                        continue;
                    }
                    //根据public.kln_ocean 和 WHERE 的位置关系，带入权限
                    $new_sql = utils::modifyString($_sql,$sqlWhere);
                    //处理limit 超过10 先限制10
                    $new_sql = utils::processLimitClause($new_sql);
                    //$rs = common::excuteListSql($new_sql);
                    $rs =  $mapdb->GetAll($new_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $new_sql), 0));
                    $reference = utils::replacementsMultiline($rs,$reference,$new_sql);
                }
                //有就去掉{{#each hbol_list}}\n  {{/each}}
                $reference = preg_replace([
                    '/\{\{[^}]+\}\}\n/',  // 匹配开始标签及换行
                    '/\{\{\/[^}]+\}\}/'   // 匹配结束标签
                ], '', $reference);

                //$reference = $reference.$message["reference"];
                $answer = $reference;
                //$response['message'] = json_decode(common::getChatAimessage($response['message']),true);
                //$answer = $response;
            }else{
                $answer = $message["response"];
            }

            //最后插入数据
            $sql = "INSERT INTO public.kln_robot_chat_log( question_id, user_name, user_type, question_type, question_content, 
                        answer_type, answer, answer_satisfication, answer_duration,answer_template, question_date, 
                        question_time, request_id, ai_model, request_content, input_token, 
                        ai_response_content, output_token, request_time, response_time, 
                        response_duration)
                VALUES ('$question_id', '$user_name', '$user_type', '$question_type', '$question_content', 
                        '$answer_type', '".common::check_input($answer)."', '', '$response_duration','$answer_template','$request_question_time', 
                        '$question_time', '$request_id', '$ai_model','$request_content',  '$input_token', 
                        '$ai_response_content', '$output_token', '$request_question_time', '$response_time', 
                        '$response_duration');";
            $rs = common::excuteUpdateSql($sql);
            if (!$rs) {
                $data = array("msg" => "AI Chat Save Error");
                common::echo_json_encode(200,$data);
                exit();
            }

            $return = array("type"=>"markdown","data" =>$answer);
            common::echo_json_encode(200,$return);            
            exit(); 
        }

        if ($operate == "download"){
            $sql = common::deCode($_REQUEST['tmp_search'], 'D');
            if(!empty($sql)){
                $rs = common::excuteListSql($sql);
            }
            common::echo_json_encode(200,array("msg"=>"success","Data" => $rs));
            exit;
        }
    }

    public function ai_chat(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if($operate == "ai_chat_prompt"){
            //查询所有配置信息 目前只有一个表一个数据
            $configuration = common::excuteObjectSql("select id, character_name,professional_field,main_tasks,table_name,table_description,
            table_structure_configuration,response_rule_configuration,output_format_configuration,fixed_problem_configuration
            from public.kln_robot_prompt_configuration where id = 1");

            $PromptAndVue = $this->dealPromptFormatAndVue($configuration);
            //promptFormat
            $promptFormat =$PromptAndVue["promptFormat"];

            $data = array("msg" => "succssful","prompt"=>$promptFormat);
            common::echo_json_encode(200,$data);
            exit();
        }

        if ($operate == "ai_chat_fixed_init"){
            $rs = common::excuteListSql("select fixed_faq,secondary_interaction_content from public.kln_robot_chat_fixed where active = true order by id");
            $data = array();
            foreach($rs as $v){
                $data[] = array("label" => $v['fixed_faq'],"value" =>$v['fixed_faq'],"isLong" => strlen($v['fixed_faq']) >80);
            }
            $data = array("msg" => "succssful","fixed_question"=>$data);
            common::echo_json_encode(200,$data);
            exit();
        }
        /**
         * ai 自由聊天
        */
        if($operate == "ai_chat"){
            try {
                //前端生成一个唯一的serial_no
                $serial_no = common::check_input($_POST["serial_no"]);
                //系统提示词
                $systemPrompt = common::check_input($_POST["prompt"]);
                $question_type = common::check_input($_POST['question_type']);
                $question_type = $question_type == 'Free Question' ? "Free Text" : $question_type;
                $question_content = common::check_input($_POST['question_content']);

                //固定问题的原始问题
                $fixed_faq = common::check_input($_POST['fixed_faq']);
                //claude  deepseek  根據賬號信息判断
                $model = common::getUserCountry();
                //$model = "deepseek"; 

                //获取自然序列
                $sequence = common::getChatAiSequence();
                $name = $model == 'deepseek' ? "DS" : "CD";   
                $request_id = "R".$name."".date("Ymd").$sequence; 
                $question_id = "Q"."".date("Ymd").$sequence; 

                //处理一下参数
                $user_name = common::check_input(_getLoginName());
                $user_type = _isApexLogin() ? "employee" : "customer";
                
                //首先生成基本记录数据 以便以后根据情况更新状态
                $sql = "INSERT INTO public.kln_robot_chat_log(serial_no, question_id, user_name, user_type, question_type, question_content, 
                        question_date, question_time, request_id, request_time,ai_model)
                VALUES ('$serial_no','$question_id', '$user_name', '$user_type', '$question_type', '$question_content', 
                        now(),to_char(CURRENT_TIME::time, 'HH24:MI:SS'), '$request_id',now(),'$model');";
                $rs = common::excuteUpdateSql($sql);
                if (!$rs) {
                    $data = array("type"=>"markdown","data" => "AI Chat Save Error");
                    common::echo_json_encode(200,$data);
                    exit();
                }

                if ($question_type == 'Predefined Question'){
                    $answer = $this->doFixedAnswerAndLog($serial_no,$fixed_faq,$question_content);

                    //判断固定回答是否结束
                    $is_fixedAnswer_end = false;
                    $fixedChat = common::excuteObjectSql("select * from public.kln_robot_chat_fixed where fixed_faq = '$fixed_faq'");
                    if (empty($fixedChat['secondary_interaction_content']) || $question_content != $fixedChat['fixed_faq'] ){
                        $is_fixedAnswer_end = true;
                    } 
                } else {
                    $answer = $this->doFreeAnswerAndLog($serial_no,$model,$systemPrompt, $question_content, $history = []);
                    //自由问题固定为true
                    $is_fixedAnswer_end = true;
                }

                $return = array("type"=>"markdown","is_fixedAnswer_end"=>$is_fixedAnswer_end,"data" =>$answer);
                common::echo_json_encode(200,$return);            
                exit();
            } catch (Exception $e) {
                $return = array("type"=>"markdown","is_fixedAnswer_end"=>true,"data" =>$e->getMessage());
                common::echo_json_encode(200,$return);            
                exit();
            }
        }

        if($operate == "ai_chat_stop"){
            $serial_no = common::check_input($_POST["serial_no"]);
            $updateSql = "update public.kln_robot_chat_log  set answer_type = 'Suspend' where serial_no = '$serial_no'";

            $updateRs = common::excuteUpdateSql($updateSql);
            if (!$updateRs) {
                $data = array("msg" => "AI Chat Response Stop Error");
            }else{
                $data = array("msg" => "AI Chat Response Stop succssful");
            }
            common::echo_json_encode(200,$data);            
            exit(); 
        }

        if($operate == "ai_chat_answer_mark"){
            $serial_no = common::check_input($_POST["serial_no"]);
            $answer_satisfication = common::check_input($_POST["feedback"]);
            $updateSql = "update public.kln_robot_chat_log  set answer_satisfication = '$answer_satisfication' where serial_no = '$serial_no'";

            $updateRs = common::excuteUpdateSql($updateSql);
            if (!$updateRs) {
                $data = array("msg" => "AI Chat Response Mark Error");
            }else{
                $data = array("msg" => "AI Chat Response Mark succssful");
            }
            common::echo_json_encode(200,$data);            
            exit(); 
        }

        if($operate == "download"){
            $faq = common::deCode($_GET['faq'], 'D');
            $sql = common::getFaqSql($faq);
            $columns = common::getFaqColumnSql($faq);

            //拼接用户权限
            $sqlWhere = '  ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            $sql = str_replace('<{ExtendHand_KLN}>', $sqlWhere, $sql);
            $rss = common::excuteListSql($sql);
            $rss = common::dealDataWithFaq($faq,$rss);
            
            //XLSXWriter输出
            $writer = new XLSXWriter();
            $sheetName = date('mdy');

            foreach ($rss as $k => $v) {
                $column = array();
                $value_temp = array();
                foreach($columns as $col){
                    $value_temp[] = $v[$col['database_column_name']];
                    if($k == 0){
                        $column[] =  $col['display_name'];
                    }
                }
                if($k == 0){
                    //输出column
                    $writer->writeSheetRow($sheetName, $column);
                }
                $writer->writeSheetRow($sheetName, $value_temp, []);
            }
            $outfile = "Complete Data_" .date("Y-m-d H:i:s") . ".xlsx";
            if (file_exists($outfile)) {
                $outfile = "Complete Data_" .date("Y-m-d H:i:s") . "_". md5(uniqid(rand(), true)) . ".xlsx";
            }
            ob_end_clean();
            header('Content-Type: application/vnd.ms-excel');
            header("Expires: 0");
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header("Pragma: can-cache");
            header('Content-Disposition:attachment;filename="' . $outfile . '"');
            $writer->writetostdout();
        }


    }

    public function dealPromptFormatAndVue($configuration){
        //tableDat-VUE 
        $tableData = array();
        $table_structure_text="[";
        $table_structure_configuration = json_decode($configuration['table_structure_configuration'],true);
        foreach($table_structure_configuration as $tk =>$tv){
            $tableData[] = $tv;

            $table_structure_text .='{  
                "name": "'.$tv["name"].'",  
                "type": "'.$tv["type"].'",  
                "description": "'.$tv["description"].'", 
                "exampledata": "'.$tv["exampledata"].'", 
            }';
            if(count($table_structure_configuration)-1 > $tk){
                $table_structure_text .=",";
            }
            $table_structure_text .=common::splicedLlineBreaks();
        }
        $table_structure_text.="]";

        //stepData -VUE
        $stepData = array(); 
        $response_rule_text = ""; 
        $response_rule_configuration = json_decode($configuration['response_rule_configuration'],true);
        foreach($response_rule_configuration as $rk =>$rv){
            $tempTable = array();
            $tempTable["description"] = $rv["description"];
            $stepData[] = $tempTable;

            $response_rule_text .=($rk+1).'.'.$rv["description"];
            $response_rule_text .=common::splicedLlineBreaks();
        }

        //formatList -VUE
        $output_format_text = "{"; 
        $output_format_configuration = json_decode($configuration['output_format_configuration'],true);
        $formatListType = $output_format_configuration['output_type'];
        $formatList = empty($output_format_configuration['data']) ? array(): $output_format_configuration['data'];
        foreach($formatList as $ok =>$ov){
            $describe = $ov['describe'];
            if ($ov['selecttype'] == '必填字段') {
                $describe .= " . 必填要求：所有情况必填";
            } elseif ($ov['selecttype'] == '有条件必填'){
                $requirements = $ov['requirements'];
                $describe .= " . 必填要求：".$ov['selecttype']."(".$requirements.")";
            }

            $output_format_text .="\"".$ov['name']."\"".": "."\"".$describe."\"";
            $output_format_text .=common::splicedLlineBreaks();
        }
        $output_format_text .= "}"; 

       
        $output_format_text_two = "";
        foreach($formatList as $ok =>$ov){
            $describe = $ov['describe'];
            if ($ov['selecttype'] == '必填字段') {
                $describe .= " . 必填要求：所有情况必填";
            } elseif ($ov['selecttype'] == '有条件必填'){
                $requirements = $ov['requirements'];
                $describe .= " . 必填要求：".$ov['selecttype']."(".$requirements.")";
            }

            $output_format_text_two .="-".$ov['name'].": ".$describe;
            $output_format_text_two .=common::splicedLlineBreaks();
        }


        $promptFormat = $this->getCompletePromptFormat();
        $promptFormat = str_replace('<{professional_field}>', $configuration['character_name'], $promptFormat);
        $promptFormat = str_replace('<{character_name}>', $configuration['character_name'], $promptFormat);
        $promptFormat = str_replace('<{main_tasks}>', $configuration['main_tasks'], $promptFormat);
        $promptFormat = str_replace('<{table}>', $configuration['table_name'], $promptFormat);
        $promptFormat = str_replace('<{table_structure_configuration}>', $table_structure_text, $promptFormat);
        $promptFormat = str_replace('<{response_rule_configuration}>', $response_rule_text, $promptFormat);
        $promptFormat = str_replace('<{number}>', count($response_rule_configuration)+1, $promptFormat);
        $promptFormat = str_replace('<{output_type}>', $formatListType, $promptFormat);
        $promptFormat = str_replace('<{output_format_configuration}>', $output_format_text_two, $promptFormat);

        return array("vuedata"=>array("tableData"=>$tableData,"stepData"=>$stepData,"formatList"=>$formatList,"formatListType"=>$formatListType),
            "promptFormat" =>$promptFormat);
    }

    public function getChangedLog($id){
        $changeLogList = array();
        $configurationArr = common::excuteListSql("select id, character_name,professional_field,main_tasks,table_name,table_description,
             created_by,TO_CHAR(created_time, 'YYYY-mm-dd HH24:MI:SS') as created_time,
            table_structure_configuration,response_rule_configuration,output_format_configuration,fixed_problem_configuration
        from public.kln_robot_prompt_configuration_log where  ref_id = '$id' order by id desc");

        foreach($configurationArr as $ca){
            $data = $this->dealPromptFormatAndVue($ca);
            $temp =array();
            $temp["time"] = $ca['created_time']." Prompt";
            $temp["person"] ="提交人：".$ca['created_by'];
            $temp["text"] = $data['promptFormat'];

            $changeLogList[] = $temp;
        }

        return $changeLogList;
    }

    public function getSystemPrompt(){
        //查询所有配置信息 目前只有一个表一个数据
        $configuration = common::excuteObjectSql("select id, character_name,professional_field,main_tasks,table_name,table_description,
            table_structure_configuration,response_rule_configuration,output_format_configuration,fixed_problem_configuration
            from public.kln_robot_prompt_configuration where id = 1");

        $PromptAndVue = $this->dealPromptFormatAndVue($configuration);
        //promptFormat
        $promptFormat =$PromptAndVue["promptFormat"];
        return $promptFormat;
    }

    public function getCompletePromptFormat() {
        $formatTpl = "
您是专门从<{professional_field}>的 <{character_name}>，请始终以<{output_type}>格式响应。您的主要任务是<{main_tasks}>

表名：<{table}>  
表结构：  \"columns\": <{table_structure_configuration}>

 
    响应规则，请按以下步骤处理：  
    <{response_rule_configuration}>  
    <{number}>.请严格按照以下<{output_type}>格式返回，不要包含任何其他文字或说明: 
    {
        \"can_query\": true,
        \"query_type\": \"货物跟踪\",
        \"reason\": \"分析原因\",
        \"sql\": \"生成的SQL語句\",
        \"reference\": \"Markdown格式的回复模板\",
        \"response\": \"直接回答內容或空字符串\"
    }

    字段說明：
    <{output_format_configuration}>

    重要：请只返回JSON对象，不要包含任何解释文字或代码块标记";
        return $formatTpl;
    }

    public function doFreeAnswerAndLog($serial_no,$model,$systemPrompt, $question_content, $history){
        //分担查询
        include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';

        //Ai response
        $item_value = common::excuteOneSql("select item_value from public.config where item = 'AIAPISetting'");
        $config = json_decode($item_value,true);
        $response = AIClientFactory::create($model, $config[$model], $systemPrompt, $question_content, $history = []);

        $input_token = "";
        $output_token = "";
        $answer_template = "";
        $answer ="";
        $response_pram = common::excuteObjectSql("select to_char(now(), 'YYYY-mm-dd HH24:MI:SS') as response_time,
            CEIL(EXTRACT(EPOCH FROM (now() - question_date))) AS response_duration from public.kln_robot_chat_log where serial_no = '$serial_no'");
        $response_duration =   $response_pram['response_duration'];
        //回答问题类型，在没有超时的情况下，固定:AI回答
        $answer_type = "AI Answer";
        if ($response_duration > 120){
            $answer_type = "Timeout";
        }
        $request_content = common::check_input(json_encode($response['data'],JSON_UNESCAPED_UNICODE));
        $ai_response_content = common::check_input(json_encode($response['full_response'],JSON_UNESCAPED_UNICODE));

        //提前记录AI 返回，如果sql异常掉了的话，可以查找原因
        $updateSql = "update public.kln_robot_chat_log set answer_duration = '$response_duration',answer_template='$answer_template',
            answer_type = case when COALESCE(answer_type,'') = 'Suspend' then 'Suspend' else '$answer_type' end,
            request_content='$request_content',
            input_token='$input_token',ai_response_content='$ai_response_content',
            output_token='$output_token',response_time=now(),response_duration='$response_duration'
        where serial_no = '$serial_no'";

        $updateRs = common::excuteUpdateSql($updateSql);
        if (!$updateRs) {
            $data = array("type"=>"markdown","data" => "AI Chat Response Save Error");
            common::echo_json_encode(200,$data);
            exit();
        }

        $message = json_decode(common::getChatAimessage($response['message']),true);
        if($message["can_query"] == "true" && !empty($message["sql"])){
            $reference = $message["reference"];
            $answer_template = common::check_input($message["reference"]);
            $sql = $message["sql"];
            //拆分sql 存在多条的情况
            $sqlArr = explode(";", $sql);
            //给所有sql 拼接用户权限
            $sqlWhere = '  ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);

            foreach($sqlArr as $_sql){
                if(empty($_sql)){
                    continue;
                }
                //首先检查sql 是否有非法的字符和不规范查询
                $safe = common::checkSafeSql($_sql);
                if(!$safe){
                    $reference = "The answer is incorrect. It has been filtered.";
                    break;
                }

                //根据public.kln_ocean 和 WHERE 的位置关系，带入权限
                $new_sql = utils::modifyString($_sql,$sqlWhere);
                
                //处理limit 超过10 先限制10
                //$new_sql = utils::processLimitClause($new_sql);
                
                //$rs = common::excuteListSql($new_sql);
                $rs =  $mapdb->GetAll($new_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $new_sql), 0));
                $reference = utils::replacementsMultiline($rs,$reference,$new_sql);
            }
            //有就去掉{{#each hbol_list}}\n  {{/each}}
            $reference = preg_replace([
                '/\{\{[^}]+\}\}\n/',  // 匹配开始标签及换行
                '/\{\{\/[^}]+\}\}/'   // 匹配结束标签
            ], '', $reference);
            $answer = $reference;
        }else{
            $answer = $message["response"];
            if(!$response['success']){
                $answer = $response["error"];
            }
        }

        //update 更新 answer
        $updateSql = "update public.kln_robot_chat_log set answer = '".common::check_input($answer)."'
            where serial_no = '$serial_no'";

        $updateRs = common::excuteUpdateSql($updateSql);
        if (!$updateRs) {
            $data = array("type"=>"markdown","data" => "AI Chat Response Save Error");
            common::echo_json_encode(200,$data);
            exit();
        }
        return $answer;
    }

    public function doFixedAnswerAndLog($serial_no,$fixed_faq,$question_content){
        $input_token = "";
        $output_token = "";
        $answer_template = "";
        $answer ="";
        $fixedChat = common::excuteObjectSql("select * from public.kln_robot_chat_fixed where fixed_faq = '$fixed_faq'");
        if (empty($fixedChat['secondary_interaction_content']) || $question_content != $fixedChat['fixed_faq'] ){
            $answer_template = common::check_input($fixedChat["answer_style"]);
            $answer =  common::FixedAnswerAndLogData($fixedChat,$question_content);
        } else {
            $answer = $fixedChat['secondary_interaction_content'];
        }

        //回答完毕计时
        $response_pram = common::excuteObjectSql("select to_char(now(), 'YYYY-mm-dd HH24:MI:SS') as response_time,
            CEIL(EXTRACT(EPOCH FROM (now() - question_date))) AS response_duration from public.kln_robot_chat_log where serial_no = '$serial_no'");
        $response_duration =   $response_pram['response_duration'];
        //回答问题类型，在没有超时的情况下，固定:AI回答
        $answer_type = "Predefined Template";
        if ($response_duration > 120){
            $answer_type = "Timeout";
        }
        
        $request_content = common::check_input($question_content);
        $ai_response_content = common::check_input($answer);
        //update 更新
        $updateSql = "update public.kln_robot_chat_log set answer_duration = '$response_duration',answer_template='$answer_template',answer = '".common::check_input($answer)."',
            answer_type = case when COALESCE(answer_type,'') = 'Suspend' then 'Suspend' else '$answer_type' end,
            request_content='$request_content',
            input_token='$input_token',ai_response_content='$ai_response_content',
            output_token='$output_token',response_time=now(),response_duration='$response_duration'
        where serial_no = '$serial_no'";

        $updateRs = common::excuteUpdateSql($updateSql);
        if (!$updateRs) {
            $data = array("type"=>"markdown","data" => "AI Chat Response Save Error");
            common::echo_json_encode(200,$data);
            exit();
        }
        return $answer;
    } 
}

?>