k_online_backend/service/report.class.php

<?php

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

/**
 * Description of operation_log
 *
 * @author Administrator
 */
class report {

    private static $_report;

    public static function getInstance() {
        if (!self::$_report) {
            $c = __CLASS__;
            self::$_report = new $c;
        }
        return self::$_report;
    }

    /**
     * report 配置
    */
    public function report_config(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

        if ($operate == "parity_id"){
            //search  parity id
            if(_isCustomerLogin()){
                //$ocean_contact_id = _getContactIDHandNew($_SESSION["ONLINE_USER"], 'public');
                $ocean_contact_id = _getCompanyContactHandNew($_SESSION["ONLINE_USER"]);
                $air_contact_id = _getAirContactID('public');

                $all_id = "ALL;".$ocean_contact_id;
                if (utils::endWith($ocean_contact_id,";")){
                    $all_id .=$air_contact_id;
                } else {
                    $all_id .=";".$air_contact_id;
                }
                $arr = explode(';', $all_id);
                $unique_arr = array_unique($arr);
            }else{
                $unique_arr= ['ALL'];
            }
            
            $option = array();
            foreach($unique_arr as $temp){
                $option[] =array("label"=>$temp,"value"=>$temp);
            }
            common::echo_json_encode(200, $option);
            exit();
        }

        /**
         * report的配置查询，
        */
        if ($operate == "search"){
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 100;
            if (empty($cp))
                $cp = 1;

            $sqlWhere = "1=1";
            $text_search = $_POST['text_search'];
            if (!empty($text_search)){
                $sqlWhere .= " and (lower(name) like '%".strtolower($text_search)."%')";
            }
            if(!empty($_POST['application_scope'])){
                $pplication_scope =  $_POST['application_scope'] =='all' ? "All Users" : "Specific Users";
                $sqlWhere .= " and access_type ='". common::check_input($pplication_scope)."'";
            }
            if(!empty($_POST['is_active'])){
                if($_POST['is_active'] == 'true'){
                    $sqlWhere .= " and is_active = true";
                }else{
                    $sqlWhere .= " and is_active = false";
                }
            }

            if(!empty($_POST['party_id'])){
                $unique_arr = $_POST['party_id'];
                //构建安全的数组字面量用于 PostgreSQL 的 && 交集判断
                // 转义每个 ID 防注入
                $escaped_ids = array();
                foreach($unique_arr as $id){
                    $id = trim($id);
                    if (empty($id))
                        continue;
                    $escaped_ids[] =  "'" .common::check_input($id) . "'";
                }
                if(!empty($escaped_ids)){
                    $partyIdsStr = 'ARRAY[' . implode(',', $escaped_ids) . ']';
                    $sqlWhere .= " and party_ids && $partyIdsStr ";
                }
                //$sqlWhere .= " and '".common::check_input($_POST['party_id'])."' = any(party_ids)";
            }
            //这里不需要控制,能进来这个页面的帐号，都可以配置任何权限的模板的
            // if(strtolower(_getLoginName()) == "chud" ||strtolower(_getLoginName()) == "it.andywu" ){
            //     //这两个帐号不受KlnReportControl控制
            // } else {
            //     $sqlWhereArr = common::getKlnReportControl();
            //     $sqlWhere_party_id = $sqlWhereArr['contactIdSqlWhere'];
            //     $sqlWhere_group_name = $sqlWhereArr['groupNameSqlWhere'];
            //     $systemAccountsqlWhere = " OR '".common::check_input(_getLoginName())."' = any(system_account)";
            //     $sqlWhere .= " and ( access_type = 'All Users' ".$sqlWhere_party_id . " ".$sqlWhere_group_name ." ".$systemAccountsqlWhere .")";
            // }

            $rc = $_POST ['rc'];
            if ($rc == -1) {
                $sql = "select count(*) from public.kln_report_template where " .$sqlWhere;
                $rc = common::excuteOneSql($sql);
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $sql = "select * from public.kln_report_template where " .$sqlWhere;
                $sql .= " order by id desc limit " . $ps . " offset " . ($cp - 1) * $ps;
                $rs = common::excuteListSql($sql);
                $arrTmp = array('searchData' => $rs,
                        'isDelete' =>_isVipReportAdmin(_getLoginName()), 
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
                common::echo_json_encode(200,$arrTmp);
                exit();
            }else{
                $arrTmp = array('searchData' =>array(),
                        'isDelete' =>_isVipReportAdmin(_getLoginName()), 
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
                common::echo_json_encode(200, $arrTmp);
                exit();
            }
        }

        if ($operate == "add"){
            $serial_no = $_REQUEST['serial_no'];
            if(!empty($serial_no)){
                //代表编辑
                $reportMain = common::excuteObjectSql("select *,
                        array_to_json(party_ids) as party_ids_json,
                        array_to_json(group_names) as group_names_json,
                        array_to_json(system_account) as system_account_json
                    from kln_report_template where serial_no = '$serial_no'");
                
                $reportFields = array();    
                $reportField = common::excuteListSql("select 
                        field_id as ids,
                        field_level as \"fieldLevel\",
                        field_type as \"fieldType\",
                        field_group_name as \"groupName\",
                        field_db as field,
                        field_display_name as title,
                        field_display_name_user as \"displayName\",
                        data_type as \"dataType\",
                        custom_fixed_value as value,
                        is_filter_enabled as \"isFilter\",
                        is_sort_enabled as  \"isSort\",
                        field_data_mapping as  mapping,
                        (select is_field_data_mapping from public.kln_report_field where display_name = field_display_name limit 1) as \"isFieldDataMapping\"
                    from kln_report_field_config  where template_serial_no = '$serial_no' order by id");
                foreach($reportField as  $_reportField){
                    $_reportField['isFilter'] = $_reportField['isFilter'] == 't' ? true : false;
                    $_reportField['isSort'] = $_reportField['isSort'] == 't' ? true : false;
                    $_reportField['mapping'] = empty($_reportField['mapping'])? array(): json_decode($_reportField['mapping'],true);
                    $reportFields[] = $_reportField;
                }

                $reportAccess = array("type"=>$reportMain['access_type'],
                    "partyId"=>json_decode($reportMain['party_ids_json'],true),
                    "groupName"=>json_decode($reportMain['group_names_json'],true),
                    "systemAccount"=>json_decode($reportMain['system_account_json'],true));
                $data = array("reportName"=>$reportMain['name'],"reportLevel"=>$reportMain['level'],"reportDescription"=>$reportMain['description'],
                    "reportFields"=>$reportFields,"reportAccess"=>$reportAccess);
            }else{
                $data = array();
            }
            common::echo_json_encode(200,$data);                
            exit(); 
        }
       
        if ($operate == "report_field_load"){
            column::getInstance()->settingDisplayForVIPReport();
        }

        if ($operate == "save"){
            $serial_no = $_POST['serial_no'];
            $name = common::check_input($_POST['report_name']);
            $description = common::check_input($_POST['report_description']);
            $level = common::check_input($_POST['report_level']);
            $access_type = common::check_input($_POST['access_type']);

            //Specific Roles 和 对应有值的时候，才取，否则取空数组更新
            $party_ids = $access_type == "Specific Roles" && !empty($_POST['party_ids']) ? $_POST['party_ids'] : array();
            $group_names = $access_type == "Specific Roles" && !empty($_POST['group_names']) ? $_POST['group_names'] : array();
            $system_account = $access_type == "Specific Roles" && !empty($_POST['system_account']) ? $_POST['system_account'] : array();

            //单独处理字段id
            $fieldsList = $_POST['fieldsList'];
            $field_ids = array();
            foreach($fieldsList as $_fieldsList){
                if (!empty($_fieldsList['ids'])){
                    $field_ids[] = $_fieldsList['ids'];
                }
            }

            $report_sql_data = common::excuteObjectSql("select sql,count_sql from public.kln_report_sql_manage where lower(level) = '".strtolower($level)."'");
            $sql = "";
            if (!empty($serial_no)){
                $updateSqlSet = " report_sql = '".common::check_input($report_sql_data['sql'])."', 
                                  count_sql = '".common::check_input($report_sql_data['count_sql'])."', 
                                  modify_by = '"._getLoginName()."',update_time = now()";
                if (!empty($name)) {
                    $updateSqlSet.= ", name = '$name' ";
                }
                if (!empty($description)) {
                    $updateSqlSet.= ", description = '$description' ";
                }
                if (!empty($level)) {
                    $updateSqlSet.= ", level = '$level' ";
                }
                if (!empty($access_type)) {
                    $updateSqlSet.= ", access_type = '$access_type' ";
                }
               
                $party_ids_filed= common::toPgTextArrayLiteral($party_ids);
                $updateSqlSet.= ", party_ids = $party_ids_filed";
                
                $group_names_filed = common::toPgTextArrayLiteral($group_names);
                $updateSqlSet.= ", group_names = $group_names_filed";
                
                $ids_filed =  common::toPgTextArrayLiteral($field_ids);
                $updateSqlSet.= ", field_ids = $ids_filed";
                
                if (!empty($system_account)) {
                    $system_account_filed = common::toPgTextArrayLiteral($system_account);
                    $updateSqlSet.= ", system_account = $system_account_filed";
                }
                //代表update 
                $sql .= "update public.kln_report_template set ".$updateSqlSet."
                    where serial_no = '$serial_no';";
            } else {
                $party_ids_filed = "NULL";
                if (!empty($party_ids)) {
                    $party_ids_filed= common::toPgTextArrayLiteral($party_ids);
                }

                $group_names_filed = "NULL";
                if (!empty($group_names)) {
                    $group_names_filed = common::toPgTextArrayLiteral($group_names);
                }

                $ids_filed = "NULL";
                if (!empty($field_ids)) {
                    $ids_filed =  common::toPgTextArrayLiteral($field_ids);
                }

                $system_account_filed = "NULL";
                if (!empty($system_account)) {
                    $system_account_filed = common::toPgTextArrayLiteral($system_account);
                }

                $serial_no = common::uuid();
                $sql .= "INSERT INTO public.kln_report_template(
                                serial_no,name, description, level, field_ids, access_type, report_sql,count_sql,
                                party_ids, group_names, system_account, create_by, created_time, modify_by, update_time )
                        VALUES ('$serial_no','$name', '$description', '$level', $ids_filed, '$access_type',
                                '".common::check_input($report_sql_data['sql'])."','".common::check_input($report_sql_data['count_sql'])."',
                                $party_ids_filed, $group_names_filed ,$system_account_filed,'"._getLoginName()."', now(), '"._getLoginName()."', now());";              
            }
            //先删除，后添加
            $sql .= "delete from  public.kln_report_field_config where template_serial_no = '$serial_no';";
            foreach($fieldsList as $key =>$_tempFieldsList){
                $_field_id = empty($_tempFieldsList['ids'])? "NULL": $_tempFieldsList['ids'];
                $_field_level = common::check_input($_tempFieldsList['fieldLevel']);
                $_field_type = common::check_input($_tempFieldsList['fieldType']);
                $_field_group_name = common::check_input($_tempFieldsList['groupName']);
                $_field_db = common::check_input($_tempFieldsList['field']);
                $_field_code = common::check_input($_tempFieldsList['title']);
                $_display_name = common::check_input($_tempFieldsList['displayName']);
                $_data_type = common::check_input($_tempFieldsList['dataType']);
                $_value_type =  $_field_type == "System" ? "" : (empty($_tempFieldsList['value']) ? "Blank" :"Fixed Value");
                $_fixed_value = common::check_input($_tempFieldsList['value']);
                $_field_data_mapping = empty($_tempFieldsList['mapping'])? "NULL" : "'".json_encode($_tempFieldsList['mapping'])."'";
                //这两个不能配置查询和过滤
                if($_field_group_name == 'Container Status' || $_field_group_name == 'Milestone'
                        ||$_field_code == 'Other reference No.'  || $_field_code == 'Shipper Zip Code' || $_field_code == 'Consignee Zip Code' ){
                    $_is_filter_enabled = "false";
                } else {
                    $_is_filter_enabled = $_tempFieldsList['isFilter'];
                }
                $_is_sort_enabled = $_tempFieldsList['isSort'];
                $sql .=  "INSERT INTO public.kln_report_field_config(
                            template_serial_no, field_id, field_level, field_type, field_db, field_group_name,
                            field_display_name, field_display_name_user, data_type, custom_value_type, 
                            custom_fixed_value, is_filter_enabled, is_sort_enabled, created_time,field_data_mapping)
                    VALUES ('$serial_no', $_field_id, '$_field_level', '$_field_type', '$_field_db', '$_field_group_name', 
                            '$_field_code', '$_display_name', '$_data_type', '$_value_type', 
                            '$_fixed_value', '$_is_filter_enabled', '$_is_sort_enabled',now(),$_field_data_mapping);";
            }
            if (!empty($sql)){
                common::excuteUpdateSql($sql);
                $data = array("msg" =>"success");
            }
            common::echo_json_encode(200,$data);                
            exit(); 
        }

        if ($operate == "active"){
            $serial_no =$_POST['serial_no'];
            $is_active = $_POST['is_active'];
            $sql = "update public.kln_report_template set is_active = '$is_active'  where serial_no = '$serial_no';";
            common::excuteUpdateSql($sql);
            $data = array("msg" =>"success");
            common::echo_json_encode(200,$data);                
            exit(); 
        }

        if ($operate == "delete"){
            $serial_no =$_POST['serial_no'];
            $sql = "delete from  public.kln_report_template  where serial_no = '$serial_no';";
            $sql .= "delete from  public.kln_report_field_config where template_serial_no = '$serial_no';";
            common::excuteUpdateSql($sql);
            $data = array("msg" =>"success");
            common::echo_json_encode(200,$data);                
            exit(); 
        }

    }

    /**
     *  shipment_status_report
    */
    public function shipment_status_report(){
        $operate = utils::_get('operate');
        $operate = strtolower($operate);

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

            $sqlWhere = "1=1 and is_active = true";
            $text_search = $_POST['text_search'];
            if (!empty($text_search)){
                $sqlWhere .= " and (lower(name) like '%".strtolower($text_search)."%')";
            }
            
            if(strtolower(_getLoginName()) == "chud" || strtolower(_getLoginName()) == "it.andywu" || strtolower(_getLoginName()) == "ra.admin"){
                //这两个帐号不受KlnReportControl控制
            } else {
                $sqlWhereArr = common::getKlnReportControl();
                $sqlWhere_party_id = $sqlWhereArr['contactIdSqlWhere'];
                $sqlWhere_group_name = $sqlWhereArr['groupNameSqlWhere'];
                $systemAccountsqlWhere = " OR '".common::check_input(_getLoginName())."' = any(system_account)";
                $sqlWhere .= " and ( access_type = 'All Users' ".$sqlWhere_party_id . " ".$sqlWhere_group_name ." ".$systemAccountsqlWhere .")";
            }
            $rc = $_POST ['rc'];
            if ($rc == - 1) {
                $sql = "select count(*) from public.kln_report_template where " .$sqlWhere;
                $rc = common::excuteOneSql($sql);
                error_log($sql);
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $sql = "select serial_no,name,description from public.kln_report_template where " .$sqlWhere;
                $sql .= " order by id desc limit " . $ps . " offset " . ($cp - 1) * $ps;
                $rs = common::excuteListSql($sql);
                $arrTmp = array('searchData' => $rs,
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
                common::echo_json_encode(200,$arrTmp);
                exit();
            }else{
                $arrTmp = array('searchData' => array(),
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
                common::echo_json_encode(200, $arrTmp);
                exit();
            }
        }
        
        if ($operate == "report_detail") {
            $serial_no = common::check_input($_POST ['serial_no']); 
            $dataReturn = array();
            $tableColumns = array();
            $filtersList = array();
            $sortByOptions = array();
            $mappingConfig = array();

            $CustomFiled = "";
            $reportFiled = common::excuteListSql("select * from public.kln_report_field_config where template_serial_no = '".$serial_no."' 
                and is_enabled = true order by id ");
            foreach($reportFiled as $filed){
                if($filed['is_filter_enabled'] == 't'){
                    $field_display_name = $filed['field_display_name'];
                    $field_display_name = strtolower($field_display_name);
                    $field_display_name = preg_replace('/[^a-z0-9]+/', '_', $field_display_name); // 非字母数字 → _
                    $field_display_name = trim($field_display_name, '_');
                    
                    $realSelect = common::getSelectedArray($filed['field_display_name']);
                    $data_type = $filed['data_type'];
                    if(!empty($realSelect)){
                        $data_type = 'select';
                    }
                    $filtersList[] = array(
                        "label"=>$filed['field_display_name_user'],
                        "field"=>$field_display_name,
                        "field_real"=>$filed['field_display_name'],
                        "data_type"=>$data_type,
                        "value"=>'',"options"=>$realSelect);
                }
                if($filed['is_sort_enabled'] == 't'){
                    $sortByOptions[] = $filed['field_db'];
                }

                //用户自定义字段
                if($filed['field_type'] == 'Custom'){
                    $CustomFiled .= " , '".$filed['custom_fixed_value']."' AS \"".$filed['field_display_name_user']."\"";
                }
                if(!empty($filed['field_data_mapping'])){
                    $mappingConfig[$filed['field_display_name']] =json_decode($filed['field_data_mapping'],true); 
                }

                $temp = array();
                $temp['field'] = $filed['field_display_name'];
                $temp['title'] = $filed['field_display_name_user'];
                $temp['type'] = $filed['field_db'] == "Status" ? "status" : "normal";
                if($filed['data_type'] == "date"){
                    $temp['formatter'] = "dateTime";
                }else{
                    $temp['formatter'] = "";
                }
                $tableColumns[] = $temp;
            }
            $dataReturn['tableColumns'] =  $tableColumns;
            $dataReturn['filtersList'] =  $filtersList;
            if(!empty($sortByOptions)){
                $dataReturn['sortBy'] =  array("field"=>$sortByOptions[0],"options"=>$sortByOptions,"order"=>"asc");
            }else{
                $dataReturn['sortBy'] =  array("field"=>"","options"=>[],"order"=>"asc");
            }
            
            $reportFiled = common::excuteObjectSql("select name,report_sql,count_sql,level from public.kln_report_template where serial_no = '".$serial_no."'");
            $sqlManage = common::excuteObjectSql("select sql,count_sql,level from public.kln_report_sql_manage where lower(level) = '".strtolower($reportFiled['level'])."'");
            $report_sql = $sqlManage["sql"];
            //$count_sql = $sqlManage["count_sql"];

            $filterSQLArr = $this->returnFilterSql_v1($filtersList);
            $count_sql = common::buildCountSql($filterSQLArr['filterFields'],$reportFiled['level']);
            //return array("vvSearchKLN"=>$vvSearchKLN,"klnOceanSearchKLN"=>$klnOceanSearchKLN,"ocItemSearchKLN"=>$ocItemSearchKLN);
            $count_sql = str_replace('<{klnOceanSearchKLN}>', $filterSQLArr['klnOceanSearchKLN'], $count_sql);
            $count_sql = str_replace('<{ocItemSearchKLN}>', $filterSQLArr['ocItemSearchKLN'], $count_sql);
            $count_sql = str_replace('<{vvSearchKLN}>', $filterSQLArr['vvSearchKLN'], $count_sql);
            $count_sql = str_replace('<{orderby}>', "", $count_sql);
            error_log($reportFiled['name'].": report_detail_count, ".$count_sql);

            $report_sql = str_replace('<{klnOceanSearchKLN}>', $filterSQLArr['klnOceanSearchKLN'], $report_sql);
            $report_sql = str_replace('<{ocItemSearchKLN}>', $filterSQLArr['ocItemSearchKLN'], $report_sql);
            $report_sql = str_replace('<{CustomFiled}>', $CustomFiled, $report_sql);
            $report_sql = str_replace('<{vvSearchKLN}>', $filterSQLArr['vvSearchKLN'], $report_sql);
            $report_sql = str_replace('<{orderby}>', $this->returnSortBySql(), $report_sql);

            //查询data
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 10;
            if (empty($cp))
                $cp = 1;
            
            $rc = $_POST ['rc'];
            if ($rc == -1) {
                $rc = common::excuteOneSql($count_sql);
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                //导出现在2万条
                $tmp_search_without_limit = $report_sql." limit  20000";
                $report_sql .= " limit " . $ps . " offset " . ($cp - 1) * $ps;
                error_log($reportFiled['name'].": report_detail_search, ".$report_sql);

                $rs = common::excuteListSql($report_sql);
                
                // 第一步：预处理 mapping，转为高效查找的哈希表
                $valueMapByField = [];
                foreach ($mappingConfig as $fieldName => $mapList) {
                    $valueMapByField[$fieldName] = [];
                    foreach ($mapList as $item) {
                        // 将 system 值作为 key，converted 作为 value
                        $valueMapByField[$fieldName][(string)$item['system']] = $item['converted'];
                    }
                }
                // 第二步：遍历结果集，逐行、逐字段替换值
                $convertedRs = [];
                foreach ($rs as $row) {
                    $newRow = [];
                    foreach ($row as $field => $value) {
                        // 如果该字段有配置映射，并且当前值在映射中，则替换
                        if (isset($valueMapByField[$field]) && isset($valueMapByField[$field][(string)$value])) {
                            $newRow[$field] = $valueMapByField[$field][(string)$value];
                        } else {
                            // 否则保留原值
                            $newRow[$field] = $value;
                        }
                    }
                    $convertedRs[] = $newRow;
                }
                $arrTmp = array('searchData' => $convertedRs, 
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
            }else{
                $arrTmp = array('searchData' => array());
            }
            $dataReturn['tableData'] =  $arrTmp;
            $dataReturn['tmp_search'] =  common::deCode($tmp_search_without_limit, 'E');
            $dataReturn['tmp_mapping'] =  common::deCode(json_encode($mappingConfig), 'E');
            $dataReturn['reportName'] =  $reportFiled['name'];
            common::echo_json_encode(200, $dataReturn);
            exit();
        }
        /*
         * export excel
        */
        if ($operate == "excel") {
            $max_ec = ini_get('max_execution_time'); //set max_excution_time
            ini_set('max_execution_time', '0');
            $memory_limit = ini_get("memory_limit");
            ini_set("memory_limit", '2048M');
            try {
            $sql = common::deCode($_POST['tmp_search'], 'D');
            $tmp_mapping = common::deCode($_POST['tmp_mapping'], 'D');
            $mappingConfig = json_decode($tmp_mapping,true);
            if(!empty($sql)){
                $rs = common::excuteListSql($sql);
            }

            // 第一步：预处理 mapping，转为高效查找的哈希表
            $valueMapByField = [];
            foreach ($mappingConfig as $fieldName => $mapList) {
                $valueMapByField[$fieldName] = [];
                foreach ($mapList as $item) {
                    // 将 system 值作为 key，converted 作为 value
                    $valueMapByField[$fieldName][(string)$item['system']] = $item['converted'];
                }
            }
                // 第二步：遍历结果集，逐行、逐字段替换值
            $convertedRs = [];
            foreach ($rs as $row) {
                $newRow = [];
                foreach ($row as $field => $value) {
                    //去除null
                    if(empty($value) || $value == null){
                        $value = "";
                    }

                    // 如果该字段有配置映射，并且当前值在映射中，则替换
                    if (isset($valueMapByField[$field]) && isset($valueMapByField[$field][(string)$value])) {
                        $newRow[$field] = $valueMapByField[$field][(string)$value];
                    } else {
                        // 否则保留原值
                        $newRow[$field] = $value;
                    }
                }
                $convertedRs[] = $newRow;
            }
            common::echo_json_encode(200,array("msg"=>"success","Data" => $convertedRs));
            } catch (Exception $exc) {
                error_log("report datail download:".$exc->getTraceAsString());
                //处理异常,标记归位
                ini_set('max_execution_time', $max_ec); //recover old value
                ini_set("memory_limit", $memory_limit);
            }
            
            ini_set('max_execution_time', $max_ec); //recover old value
            ini_set("memory_limit", $memory_limit);
            exit;
        }

        if ($operate == "manage_fileds") {
            $serial_no = common::check_input($_POST ['serial_no']); 
            $reportFiled = common::excuteListSql("select * from public.kln_report_field_config 
                where template_serial_no = '".$serial_no."' order by id ");
            $data = array(); 
            foreach($reportFiled as $_reportFiled){
                $_reportFiled['is_filter_enabled'] = $_reportFiled['is_filter_enabled'] == 't' ? true : false;
                $_reportFiled['is_sort_enabled'] = $_reportFiled['is_sort_enabled'] == 't' ? true : false;
                $_reportFiled['is_enabled'] = $_reportFiled['is_enabled'] == 't' ? true : false;
                $data[] = $_reportFiled;
            }    
            common::echo_json_encode(200,array("msg"=>"success","data" => $data));
            exit;
        }
        if ($operate == "manage_fileds_save") {
            $serial_no = common::check_input($_POST['serial_no']); 
            $fieldsList = $_POST['fieldsList'];
            //先删除，后添加  因为不涉及系统配置字段，全部用kln_report_field_config 字段name
            $sql = "delete from  public.kln_report_field_config where template_serial_no = '$serial_no';";
            foreach($fieldsList as $key =>$_tempFieldsList){
                $_field_id = empty($_tempFieldsList['field_id'])? "NULL": $_tempFieldsList['field_id'];
                $_field_level = common::check_input($_tempFieldsList['field_level']);
                $_field_type = common::check_input($_tempFieldsList['field_type']);
                $_field_group_name = common::check_input($_tempFieldsList['field_group_name']);
                $_field_db = common::check_input($_tempFieldsList['field_db']);
                $_field_code = common::check_input($_tempFieldsList['field_display_name']);
                $_display_name = common::check_input($_tempFieldsList['field_display_name_user']);
                $_data_type = common::check_input($_tempFieldsList['data_type']);
                $_value_type = common::check_input($_tempFieldsList['custom_value_type']);
                $_fixed_value = common::check_input($_tempFieldsList['custom_fixed_value']);
                $_is_filter_enabled = $_tempFieldsList['is_filter_enabled'];
                $_is_sort_enabled = $_tempFieldsList['is_sort_enabled'];
                $_is_enabled = $_tempFieldsList['is_enabled'];
                $sql .=  "INSERT INTO public.kln_report_field_config(
                            template_serial_no, field_id, field_level, field_type, field_db, field_group_name,
                            field_display_name, field_display_name_user, data_type, custom_value_type, 
                            custom_fixed_value, is_filter_enabled, is_sort_enabled,is_enabled, created_time)
                    VALUES ('$serial_no', $_field_id, '$_field_level', '$_field_type', '$_field_db', '$_field_group_name', 
                            '$_field_code', '$_display_name', '$_data_type', '$_value_type', 
                            '$_fixed_value', '$_is_filter_enabled', '$_is_sort_enabled','$_is_enabled',now());";
            }
            if (!empty($sql)){
                common::excuteUpdateSql($sql);
                $data = array("msg" =>"success");
            }

            common::echo_json_encode(200,array("msg"=>"success","Data" => ''));
            exit;
        }

        if ($operate == "report_schedule"){
            $serial_no = common::check_input($_POST ['serial_no']); 
            $schedule = common::excuteObjectSql("select schedule_order_field,validity_type,valid_from,valid_to,
                    data_reference_field,data_range_type,dynamic_start_offset,dynamic_end_offset,fixed_start_date,fixed_end_date,
                    delivery_frequency,TO_CHAR(daily_time, 'HH24:MI') as daily_time,
                    array_to_json(weekly_day) as weekly_days_json,TO_CHAR(weekly_time, 'HH24:MI') as weekly_time,
                    array_to_json(monthly_day) as monthly_day_json,TO_CHAR(monthly_time, 'HH24:MI') as monthly_time,
                    quarterly_month,quarterly_day,TO_CHAR(quarterly_time, 'HH24:MI') as quarterly_time,
                    array_to_json(yearly_month) as yearly_month_json,yearly_day, TO_CHAR(yearly_time, 'HH24:MI') as yearly_time,
                    timezone,email_recipients
                from public.kln_report_schedule where  serial_no = '$serial_no' and lower(create_by) = '".strtolower(_getLoginName())."'");

            $schedule["weekly_days"] = json_decode($schedule["weekly_days"],true);
            $schedule["monthly_day"] = json_decode($schedule["monthly_day"],true);
            $schedule["yearly_month"] = json_decode($schedule["yearly_month"],true);

            //处理成VUE 识别的格式
            $data = array();
            $data["validityPeriod"] = array("type"=>$schedule["validity_type"],"startDate"=>$schedule["valid_from"],"endDate"=>$schedule["valid_to"]);
            if($schedule["data_range_type"] == "dynamic_rolling"){
                $data["timeRange"] = array("fieldType"=>$schedule["data_reference_field"],"type"=>$schedule["data_range_type"],
                    "startDate"=>$schedule["dynamic_start_offset"],"endDate"=>$schedule["dynamic_end_offset"]);
            }else{
                $data["timeRange"] = array("fieldType"=>$schedule["data_reference_field"],"type"=>$schedule["data_range_type"],
                    "startDate"=>$schedule["fixed_start_date"],"endDate"=>$schedule["fixed_end_date"]);
            }
            $time = "";
            $week = array();
            $month = array();
            $quarterMonth = "";
            $day = array();
            $delivery_frequency = $schedule["delivery_frequency"];
            if($delivery_frequency ==  'daily'){
                $time =  $schedule["daily_time"];
            }elseif($delivery_frequency ==  'weekly'){
                $week= json_decode($schedule["weekly_days_json"],true);
                $time =  $schedule["weekly_time"];
            }elseif($delivery_frequency ==  'monthly'){
                $monthlyDay= json_decode($schedule["monthly_day_json"],true);
                $time =  $schedule["monthly_time"];
            }elseif($delivery_frequency ==  'quarterly'){
                $quarterMonth =  $schedule["quarterly_month"];
                $day =  $schedule["quarterly_day"];
                $time =  $schedule["quarterly_time"];
            }elseif($delivery_frequency ==  'yearly'){
                $month= json_decode($schedule["yearly_month_json"],true);
                $day = $schedule["yearly_day"];
                $time =  $schedule["yearly_time"];
            }
            $data["deliveryFrequency"] = array("emailRecipients"=>$schedule["email_recipients"],"orderBy"=>$schedule["schedule_order_field"],
                "timezone"=>$schedule["timezone"],"deliveryFrequency"=>$schedule["delivery_frequency"],
                "scheduleDetails" =>array("time"=>$time,"week"=>$week,"month"=>$month,"monthlyDay" =>$monthlyDay,"quarterMonth"=>$quarterMonth,"day"=>$day));
            common::echo_json_encode(200,array("msg"=>"success","showData" => $data));
            exit;
        }

        if ($operate == "report_schedule_search"){
            $dataReturn = array();
            $serial_no = common::check_input($_POST ['serial_no']);
            $data_reference_field =  strtolower(common::check_input($_POST ['fieldType']));
            $data_range_type =  common::check_input($_POST ['type']);
            if($data_range_type == "dynamic_rolling"){
                $dynamic_start_offset =  common::check_input($_POST ['startDate']);
                $dynamic_end_offset =  common::check_input($_POST ['endDate']);
                $fixed_start_date =  "null";
                $fixed_end_date =  "null";
            }else{
                $dynamic_start_offset =  "null";
                $dynamic_end_offset =  "null";
                $fixed_start_date =  common::check_input($_POST ['startDate']);
                $fixed_end_date =  common::check_input($_POST ['endDate']);
            }
            $schedule_order_field =  common::check_input($_POST ['orderBy']);


            //查询列名
            $tableColumns = array();
            $mappingConfig = array();
            $CustomFiled = "";
            $reportFiled = common::excuteListSql("select * from public.kln_report_field_config where template_serial_no = '".$serial_no."' 
                and is_enabled = true order by id ");
            foreach($reportFiled as $filed){
                $temp = array();
                $temp['field'] = $filed['field_display_name'];
                $temp['title'] = $filed['field_display_name_user'];
                $temp['type'] = $filed['field_db'] == "Status" ? "status" : "normal";
                $temp['formatter'] = "";
                $tableColumns[] = $temp;

                //用户自定义字段
                if($filed['field_type'] == 'Custom'){
                    $CustomFiled .= " , '".$filed['custom_fixed_value']."' AS \"".$filed['field_display_name_user']."\"";
                }
                if(!empty($filed['field_data_mapping'])){
                    $mappingConfig[$filed['field_display_name']] =json_decode($filed['field_data_mapping'],true); 
                }
            }
            $dataReturn['tableColumns'] =  $tableColumns;
            
            
            $schedule_search = common::excuteObjectSql("select t.name,t.level,t.report_sql,t.count_sql from public.kln_report_template t where  t.serial_no = '$serial_no'");

            $report_sql = $schedule_search["report_sql"];
            //$count_sql = $schedule_search["count_sql"];

            $filterFields = [];
            $count_sql = common::buildCountSql($filterFields,$schedule_search['level']);

            $klnOceanSearchKLN = " where " . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            //$klnOceanSearchKLN = " where  created_time > CURRENT_DATE - INTERVAL '1 years' and  " . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            $orderby = "";
            if(!empty($data_range_type)){
                //代表有用户设置的查询参数
                if($data_range_type == "dynamic_rolling"){
                    $klnOceanSearchKLN .= " and ".$data_reference_field." >= CURRENT_DATE - ".$dynamic_start_offset." and  ".$data_reference_field." <= CURRENT_DATE + ".$dynamic_end_offset."";
                } else {
                    if(!empty($fixed_start_date)){
                        $klnOceanSearchKLN .= " and ".$data_reference_field." >= '".common::usDate2sqlDate($fixed_start_date)."'";
                    }
                    if(!empty($fixed_end_date)){
                        $klnOceanSearchKLN .= " and  ".$data_reference_field." <=  '".common::usDate2sqlDate($fixed_end_date)."'";
                    }
                }
                if(!empty($schedule_order_field)){
                    $orderby = " order by \"".strtoupper($schedule_order_field)."\"";
                }
            } else {
                 //如果kln_report_schedule有值了。就直接这样查询  就页面而言不会进入这个分支
                $schedule_search = common::excuteObjectSql("select t.name,t.level,s.schedule_order_field,s.data_reference_field,s.data_range_type,
                        s.dynamic_start_offset,s.dynamic_end_offset,
                        s.fixed_start_date,s.fixed_end_date,
                        t.report_sql,t.count_sql
                    from public.kln_report_template t 
                        left join public.kln_report_schedule s on s.serial_no = t.serial_no  
                    where  s.serial_no = '$serial_no' and lower(s.create_by) = '".strtolower(_getLoginName())."'");

                if($schedule_search["data_range_type"] == "dynamic_rolling"){
                    $field_db = strtolower($schedule_search["data_reference_field"]);
                    $dynamic_start_offset_db = empty($schedule_search["dynamic_start_offset"]) ? 0 : $schedule_search["dynamic_start_offset"];
                    $dynamic_end_offset_db = empty($schedule_search["dynamic_end_offset"]) ? 0 : $schedule_search["dynamic_end_offset"];
                    $klnOceanSearchKLN .= " and ".$field_db." >= CURRENT_DATE - ".$dynamic_start_offset_db." and  ".$field_db." <= CURRENT_DATE + ".$dynamic_end_offset_db."";
                } else {
                    $field_db = strtolower($schedule_search["data_reference_field"]);
                    $fixed_start_date_db = $schedule_search["fixed_start_date"];
                    $fixed_end_date_db = $schedule_search["fixed_end_date"];

                    if(!empty($fixed_start_date_db)){
                        $klnOceanSearchKLN .= " and ".$field_db." >= '".$fixed_start_date_db."'";
                    }
                    if(!empty($fixed_end_date_db)){
                        $klnOceanSearchKLN .= " and  ".$field_db." <=  '".$fixed_end_date_db."'";
                    }
                }

                if(!empty($schedule_search["schedule_order_field"])){
                    $orderby = " order by \"".strtoupper($schedule_search["schedule_order_field"])."\"";
                }
            }

            $count_sql = str_replace('<{klnOceanSearchKLN}>', $klnOceanSearchKLN, $count_sql);
            $count_sql = str_replace('<{ocItemSearchKLN}>', " ", $count_sql);
            $count_sql = str_replace('<{vvSearchKLN}>', " ", $count_sql);
            $count_sql = str_replace('<{orderby}>', "", $count_sql);
            error_log($schedule_search['name'].": report_schedule_count, ".$count_sql);

            $report_sql = str_replace('<{klnOceanSearchKLN}>', $klnOceanSearchKLN, $report_sql);
            $report_sql = str_replace('<{ocItemSearchKLN}>', " ", $report_sql);
            $report_sql = str_replace('<{CustomFiled}>', $CustomFiled, $report_sql);
            $report_sql = str_replace('<{vvSearchKLN}>', " ", $report_sql);
            $report_sql = str_replace('<{orderby}>', $orderby, $report_sql);

            //查询sql
            $cp = common::check_input($_POST ['cp']); //current_page
            $ps = common::check_input($_POST ['ps']); //ps
            if (empty($ps))
                $ps = 10;
            if (empty($cp))
                $cp = 1;
           
            $rc = $_POST ['rc'];
            if ($rc == -1) {
                $rc = common::excuteOneSql($count_sql);
            }
            $tp = ceil($rc / $ps);
            if ($rc > 0) {
                $tmp_search_without_limit = $report_sql;
                $report_sql .= " limit " . $ps . " offset " . ($cp - 1) * $ps;
                error_log($schedule_search['name'].": report_schedule_search, ".$report_sql);

                $rs = common::excuteListSql($report_sql);

                // 第一步：预处理 mapping，转为高效查找的哈希表
                $valueMapByField = [];
                foreach ($mappingConfig as $fieldName => $mapList) {
                    $valueMapByField[$fieldName] = [];
                    foreach ($mapList as $item) {
                        // 将 system 值作为 key，converted 作为 value
                        $valueMapByField[$fieldName][(string)$item['system']] = $item['converted'];
                    }
                }
                // 第二步：遍历结果集，逐行、逐字段替换值
                $convertedRs = [];
                foreach ($rs as $row) {
                    $newRow = [];
                    foreach ($row as $field => $value) {
                        // 如果该字段有配置映射，并且当前值在映射中，则替换
                        if (isset($valueMapByField[$field]) && isset($valueMapByField[$field][(string)$value])) {
                            $newRow[$field] = $valueMapByField[$field][(string)$value];
                        } else {
                            // 否则保留原值
                            $newRow[$field] = $value;
                        }
                    }
                    $convertedRs[] = $newRow;
                }

                $arrTmp = array('searchData' => $convertedRs, 
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
            }else{
                $arrTmp = array('searchData' => array(),
                        'rc' => intval($rc),
                        'ps' => intval($ps),
                        'cp' => intval($cp),
                        'tp' => intval($tp));
            }
            $dataReturn['tableData'] =  $arrTmp;
            common::echo_json_encode(200, $dataReturn);
            exit();
            exit;
        }

        if ($operate == "report_schedule_save"){
            $serial_no = common::check_input($_POST ['serial_no']);

            $validity_type =  common::check_input($_POST ['validityPeriodType']);
            $valid_from = "null";
            $valid_to = "null";
            if($validity_type == "custom"){
                $valid_from =  "'".common::check_input($_POST ['validityPeriodStartDate'])."'";
                $valid_to =  "'".common::check_input($_POST ['validityPeriodEndDate'])."'";
            }

            $data_reference_field =  strtolower(common::check_input($_POST ['fieldType']));
            $data_range_type =  common::check_input($_POST ['type']);
            if($data_range_type == "dynamic_rolling"){
                $dynamic_start_offset =  "'".common::check_input($_POST ['startDate'])."'";
                $dynamic_end_offset =  "'".common::check_input($_POST ['endDate'])."'";
                $fixed_start_date =  "null";
                $fixed_end_date =  "null";
            }else{
                $dynamic_start_offset =  "null";
                $dynamic_end_offset =  "null";
                $fixed_start_date =  "'".common::check_input($_POST ['startDate'])."'";
                $fixed_end_date =  "'".common::check_input($_POST ['endDate'])."'";
            }
            $schedule_order_field =  common::check_input($_POST ['orderBy']);
            if(empty($schedule_order_field)){
                $schedule_order_field = "ETD";
            }

            $email_recipients =  common::check_input($_POST ['emailRecipients']);

            $timezone =  common::check_input($_POST ['timezone']);
            $daily_time = "null";
            $weekly_day = "null";
            $weekly_time = "null";
            $monthly_day = "null";
            $monthly_time = "null";
            $quarterly_month= "null";
            $quarterly_day= "null";
            $quarterly_time = "null";
            $yearly_month = "null";
            $yearly_day = "null";
            $yearly_time = "null";
            $delivery_frequency =  common::check_input($_POST ['deliveryFrequency']);
            if($delivery_frequency ==  'daily'){
                $daily_time =  "'".common::check_input($_POST ['time'])."'";
            }elseif($delivery_frequency ==  'weekly'){
                $weekly_day= common::toPgTextArrayLiteral($_POST ['week']);
                $weekly_time = "'".common::check_input($_POST ['time'])."'";
            }elseif($delivery_frequency ==  'monthly'){
                $monthly_day= common::toPgTextArrayLiteral($_POST ['monthlyDay']);
                $monthly_time = "'".common::check_input($_POST ['time'])."'";
            }elseif($delivery_frequency ==  'quarterly'){
                //检查数组是否有值
                $quarterly_month =  "'".common::check_input($_POST ['quarterMonth'])."'";
                $quarterly_day =  "'".common::check_input($_POST ['day'])."'";
                $quarterly_time = "'".common::check_input($_POST ['time'])."'";
            }elseif($delivery_frequency ==  'yearly'){
                $yearly_month= common::toPgTextArrayLiteral($_POST ['yearlyMonth']);
                $yearly_day = "'".common::check_input($_POST ['day'])."'";
                $yearly_time = "'".common::check_input($_POST ['time'])."'";
            }

            $sql = "";
            $klnOceanSearchKLN = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
            if (!empty($serial_no)){
                $exist = common::excuteObjectSql("select serial_no from public.kln_report_schedule where serial_no = '$serial_no' and lower(create_by) = '".strtolower(_getLoginName())."'");
                if (!empty($exist['serial_no'])) {
                    $updateSqlSet = " schedule_order_field = '".$schedule_order_field."', 
                                    validity_type = '".$validity_type."', 
                                    valid_from = ".$valid_from.", 
                                    valid_to = ".$valid_to.", 

                                    data_reference_field = '".$data_reference_field."', 
                                    data_range_type = '".$data_range_type."', 
                                    dynamic_start_offset = ".$dynamic_start_offset.", 
                                    dynamic_end_offset = ".$dynamic_end_offset.", 
                                    fixed_start_date = ".$fixed_start_date.", 
                                    fixed_end_date = ".$fixed_end_date.", 

                                    delivery_frequency = '".$delivery_frequency."', 
                                    daily_time = ".$daily_time.", 
                                    weekly_day = ".$weekly_day.", 
                                    weekly_time = ".$weekly_time.", 
                                    monthly_day = ".$monthly_day.", 
                                    monthly_time = ".$monthly_time.", 
                                    quarterly_month = ".$quarterly_month.", 
                                    quarterly_day = ".$quarterly_day.", 
                                    quarterly_time = ".$quarterly_time.", 
                                    yearly_month = ".$yearly_month.", 
                                    yearly_day = ".$yearly_day.", 
                                    yearly_time = ".$yearly_time.",
                                    timezone = '".$timezone."',
                                    email_recipients = '".$email_recipients."',
                                    next_run_time = null,
                                    search_extend_hand = '".common::check_input($klnOceanSearchKLN)."',
                                    create_by = '"._getLoginName()."',
                                    created_time = now()";
                    //代表update 
                    $sql .= "update public.kln_report_schedule set ".$updateSqlSet."
                        where serial_no = '$serial_no' and lower(create_by) = '".strtolower(_getLoginName())."';";
                } else {
                    $sql .= "INSERT INTO public.kln_report_schedule(
                        serial_no, search_extend_hand, schedule_order_field, 
                        validity_type, valid_from, valid_to, data_reference_field, data_range_type, 
                        dynamic_start_offset, dynamic_end_offset, fixed_start_date, fixed_end_date, 
                        delivery_frequency, daily_time, weekly_day, weekly_time, monthly_day, 
                        monthly_time, quarterly_month, quarterly_day, quarterly_time, 
                        yearly_month, yearly_day, yearly_time, timezone, email_recipients, 
                        next_run_time, create_by, created_time)
                    VALUES ('$serial_no', '".common::check_input($klnOceanSearchKLN)."', '".$schedule_order_field."',
                            '".$validity_type."', ".$valid_from.", ".$valid_to.", '".$data_reference_field."', '".$data_range_type."',
                            ".$dynamic_start_offset.", ".$dynamic_end_offset.", ".$fixed_start_date.", ".$fixed_end_date.",
                            '".$delivery_frequency."', ".$daily_time.",  ".$weekly_day.", ".$weekly_time.", ".$monthly_day.",
                            ".$monthly_time.", ".$quarterly_month.", ".$quarterly_day.", ".$quarterly_time.",
                            ".$yearly_month.", ".$yearly_day.", ".$yearly_time.", '".$timezone."', '".$email_recipients."',
                            null, '"._getLoginName()."',now());";
                }        
            }
            if (!empty($sql)){
                common::excuteUpdateSql($sql);
                //执行成功后，处理next_run_time 
                $config = common::excuteObjectSql("select delivery_frequency,daily_time,
                        array_to_json(weekly_day) as weekly_day_json,weekly_time,
                        array_to_json(monthly_day) as monthly_day_json,monthly_time,
                        quarterly_month,quarterly_day,quarterly_time,
                        array_to_json(yearly_month) as yearly_month_json,yearly_day,yearly_time,timezone 
                    from public.kln_report_schedule where  serial_no = '$serial_no' and lower(create_by) = '".strtolower(_getLoginName())."';");

                $config["weekly_day"] = json_decode($config["weekly_day_json"],true);
                $config["monthly_day"] = json_decode($config["monthly_day_json"],true);
                $config["yearly_month"] = json_decode($config["yearly_month_json"],true); 
                $next = common::calculateNextRunTime($config);
                $next_run_time = $next->format('Y-m-d H:i:s');   
                common::excuteUpdateSql("update public.kln_report_schedule set next_run_time = '$next_run_time' where  serial_no = '$serial_no' and lower(create_by) = '".strtolower(_getLoginName())."';");
                
                $data = array("msg" =>"success");
            }
            common::echo_json_encode(200,$data);                
            exit();
        }
    }

    /**
     * 根据提交的参数动态的拼接filter sql
     */
    public function returnFilterSql($filtersList){
        $klnVipDb = common::getReportRealDBFiled();

        $vvSearchKLN = " where 1=1 ";
        $klnOceanSearchKLN = " where  created_time > CURRENT_DATE - INTERVAL '1 years' and  " . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        //$klnOceanSearchKLN = " where   " . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $ocItemSearchKLN = " where 1=1 ";

        foreach($filtersList as $fiter){
            if(!empty($_POST[$fiter['field']])){
                $key = array_search($fiter['field'], $klnVipDb);
                if($key !== false){
                    //找到给key
                    $temp_sql_where = "";
                    if ($fiter['data_type'] == "string"){
                        $temp_sql_where .= " and ".$key." = '". common::check_input($_POST[$fiter['field']])."'"; 
                    } elseif ($fiter['data_type'] == "number"){
                        $temp_arr = $_POST [$fiter['field']];
                        if(!empty($temp_arr[0])){
                            $temp_sql_where .= " and ".$key."::integer >= '". common::check_input($temp_arr[0])."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $temp_sql_where .= " and ".$key."::integer <= '". common::check_input($temp_arr[1])."'"; 
                        } 
                    } elseif ($fiter['data_type'] == "date"){
                        $temp_arr = $_POST [$fiter['field']];
                        if(!empty($temp_arr[0])){
                            $date_from = common::check_input(common::usDate2sqlDate($temp_arr[0]) . ' 00:00:00');
                            $temp_sql_where .= " and ".$key." >= '". $date_from."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $date_to = common::check_input(common::usDate2sqlDate($temp_arr[1]) . ' 23:59:59');
                            $temp_sql_where .= " and ".$key." <= '". $date_to."'"; 
                        }
                    }

                    if(utils::startWith($key,"oc") || utils::startWith($key,"oi")){
                        $ocItemSearchKLN .= $temp_sql_where;
                    } else {
                        $klnOceanSearchKLN .= $temp_sql_where;
                    }
                } else {
                    //获取POST name  用户去别名得字段，放在vvSearchKLN上
                    $_post_field = $fiter['field'];
                    $_post_field = strtolower($_post_field);
                    $_post_field = preg_replace('/[^a-z0-9]+/', '_', $_post_field); // 非字母数字 → _
                    $_post_field = trim($_post_field, '_');

                    if ($fiter['data_type'] == "string"){
                        $vvSearchKLN .= " and \"".$fiter['field_real']."\" ilike '%". common::check_input($_POST [$_post_field])."%'"; 
                    } elseif ($fiter['data_type'] == "number"){
                        $temp_arr = $_POST [$_post_field];
                        if(!empty($temp_arr[0])){
                            $vvSearchKLN .= " and \"".$fiter['field_real']."\"::integer >= '". common::check_input($temp_arr[0])."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $vvSearchKLN .= " and \"".$fiter['field_real']."\"::integer <= '". common::check_input($temp_arr[1])."'"; 
                        }  
                    } elseif ($fiter['data_type'] == "date"){
                        $temp_arr = $_POST [$_post_field];
                        //先判断日期字符串是否为空,这里则有做 是因为sql 整合了柜子315时间，和 milestone的时间, 只能text转date
                        if(!empty($temp_arr[0])){
                            $date_from = common::check_input(common::usDate2sqlDate($temp_arr[0]) . ' 00:00:00');
                            $vvSearchKLN .= " and COALESCE(\"".$fiter['field_real']."\",''::text)<> ''::text ";  
                            $vvSearchKLN .= " and to_timestamp(\"".$fiter['field_real']."\", 'MM/DD/YYYY HH24:MI:SS') >= '". $date_from."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $date_to = common::check_input(common::usDate2sqlDate($temp_arr[1]) . ' 23:59:59');
                            $vvSearchKLN .= " and COALESCE(\"".$fiter['field_real']."\",''::text)<> ''::text ";  
                            $vvSearchKLN .= " and to_timestamp(\"".$fiter['field_real']."\", 'MM/DD/YYYY HH24:MI:SS') <= '". $date_to."'"; 
                        }
                    }
                }
            }
        }
        
        return array("vvSearchKLN"=>$vvSearchKLN,"klnOceanSearchKLN"=>$klnOceanSearchKLN,"ocItemSearchKLN"=>$ocItemSearchKLN);
    }

    /**
     * 根据提交的参数动态的拼接Count sql
     */
    public function returnFilterSql_v1($filtersList){
        $klnVipDb = common::getReportRealDBFiled();

        $vvSearchKLN = " where 1=1 ";
        //$klnOceanSearchKLN = " where  created_time > CURRENT_DATE - INTERVAL '1 years' and  " . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $klnOceanSearchKLN = " where   " . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $ocItemSearchKLN = " where 1=1 ";

        $filterFields = array();
        foreach($filtersList as $fiter){
            if(!empty($_POST[$fiter['field']])){
                //这里其实可以改成field_real
                $key = array_search($fiter['field'], $klnVipDb);
                //是数组，且为空，不加入
                if(is_array($_POST[$fiter['field']]) && common::isArrayAllEmpty($_POST[$fiter['field']])){

                }else{
                    $filterFields[] = $fiter['field_real'];
                }
                if($key !== false){
                    //找到给key
                    $temp_sql_where = "";
                    if ($fiter['data_type'] == "string" || $fiter['data_type'] == "select"){
                        $temp_sql_where .= " and ".$key." ilike '%". common::check_input($_POST[$fiter['field']])."%'"; 
                    } elseif ($fiter['data_type'] == "number"){
                        $temp_arr = $_POST [$fiter['field']];
                        if(trim((string)$temp_arr[0]) !== ''){
                            $temp_sql_where .= " and ".$key."::numeric >= '". common::check_input($temp_arr[0])."'"; 
                        }
                        if(trim((string)$temp_arr[1]) !== ''){
                            $temp_sql_where .= " and ".$key."::numeric <= '". common::check_input($temp_arr[1])."'"; 
                        } 
                    } elseif ($fiter['data_type'] == "date"){
                        $temp_arr = $_POST [$fiter['field']];
                        if(!empty($temp_arr[0])){
                            $date_from = common::check_input(common::usDate2sqlDate($temp_arr[0]) . ' 00:00:00');
                            $temp_sql_where .= " and ".$key." >= '". $date_from."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $date_to = common::check_input(common::usDate2sqlDate($temp_arr[1]) . ' 23:59:59');
                            $temp_sql_where .= " and ".$key." <= '". $date_to."'"; 
                        }
                    }

                    if (utils::startWith($key,"oc") || utils::startWith($key,"oi") || utils::startWith($key,"oe") || utils::startWith($key,"co2_r")
                        || $fiter['field'] == "total_distance"
                        || $fiter['field'] == "shipment_po_no"
                        || $fiter['field'] == "pickup_postal_code"
                        || $fiter['field'] == "pickup_mode_type"
                        || $fiter['field'] == "last_mile_delivery_postal_code"
                        || $fiter['field'] == "last_mile_delivery_mode_type"
                        || $fiter['field'] == "last_mile_delivery_date"
                        || $fiter['field'] == "last_mile_delivery_time"
                        || $fiter['field'] == "last_mile_delivery_city"
                        || $fiter['field'] == "last_mile_delivery _address"
                        || $fiter['field'] == "co2e_intensity"){
                        $ocItemSearchKLN .= $temp_sql_where;
                    } else {
                        $klnOceanSearchKLN .= $temp_sql_where;
                    }
                } else {
                    //获取POST name  用户去别名得字段，放在vvSearchKLN上
                    $_post_field = $fiter['field'];
                    $_post_field = strtolower($_post_field);
                    $_post_field = preg_replace('/[^a-z0-9]+/', '_', $_post_field); // 非字母数字 → _
                    $_post_field = trim($_post_field, '_');

                    if ($fiter['data_type'] == "string"){
                        $vvSearchKLN .= " and \"".$fiter['field_real']."\" ilike '%". common::check_input($_POST [$_post_field])."%'"; 
                    } elseif ($fiter['data_type'] == "number"){
                        $temp_arr = $_POST [$_post_field];
                        if(trim((string)$temp_arr[0]) !== ''){
                            $vvSearchKLN .= " and \"".$fiter['field_real']."\"::numeric >= '". common::check_input($temp_arr[0])."'"; 
                        }
                        if(trim((string)$temp_arr[1]) !== ''){
                            $vvSearchKLN .= " and \"".$fiter['field_real']."\"::numeric <= '". common::check_input($temp_arr[1])."'"; 
                        }  
                    } elseif ($fiter['data_type'] == "date"){
                        $temp_arr = $_POST [$_post_field];
                        //先判断日期字符串是否为空,这里则有做 是因为sql 整合了柜子315时间，和 milestone的时间, 只能text转date
                        if(!empty($temp_arr[0])){
                            $date_from = common::check_input(common::usDate2sqlDate($temp_arr[0]) . ' 00:00:00');
                            $vvSearchKLN .= " and COALESCE(\"".$fiter['field_real']."\",''::text)<> ''::text ";  
                            $vvSearchKLN .= " and to_timestamp(\"".$fiter['field_real']."\", 'MM/DD/YYYY HH24:MI:SS') >= '". $date_from."'"; 
                        }
                        if(!empty($temp_arr[1])){
                            $date_to = common::check_input(common::usDate2sqlDate($temp_arr[1]) . ' 23:59:59');
                            $vvSearchKLN .= " and COALESCE(\"".$fiter['field_real']."\",''::text)<> ''::text ";  
                            $vvSearchKLN .= " and to_timestamp(\"".$fiter['field_real']."\", 'MM/DD/YYYY HH24:MI:SS') <= '". $date_to."'"; 
                        }
                    }
                }
            }
        }
        
        return array("vvSearchKLN"=>$vvSearchKLN,"klnOceanSearchKLN"=>$klnOceanSearchKLN,"ocItemSearchKLN"=>$ocItemSearchKLN,"filterFields"=>$filterFields);
    }

    /**
     * 根据提交的参数动态的拼接sort by sql
     */
    public function returnSortBySql(){
        $sortByField = $_POST['sortByField'];
        $sortByOrder = $_POST['sortByOrder'];

        $sort_sql_temp = " ";
        if(!empty($sortByField) && !empty($sortByOrder)){
            $sort_sql_temp = " order by \"".$sortByField."\" ". $sortByOrder;
        }
        return  $sort_sql_temp;
    }
}

?>