k_online_backend/main_new_version.php

<?php

$max_ec = ini_get('max_execution_time'); //set max_excution_time
ini_set('max_execution_time', '600');
$memory_limit = "";
$action = "";
if (isset($_GET['action']))
    $action = strtolower($_GET['action']);
if (isset($_POST['action']))
    $action = strtolower($_POST['action']);

include "include.ini.php";
include 'service/ocean_booking.class.php';
include 'service/ocean_order.class.php';
include 'service/operation_log.class.php';
include 'service/search.class.php';
include 'service/column.class.php';
include 'service/ajax.class.php';
include 'service/login.class.php';
include 'service/tools.class.php';
include 'service/robot.class.php';
include 'service/AIClientFactory.php';
include 'service/destination_delivery.class.php';
include 'service/report.class.php';
include 'service/multilingual.class.php';

//为了调用，临时做一个登录动作  如果是正式版 要注释掉菜单System Settings
 //$_POST['uname'] = "ra.admin";
 //$_POST['psw'] = common::excuteOneSql("select ra_password from public.ra_online_user where user_login = 'ra.admin'");
//  $name = "dddde";
//  $name = "ra.admin";
//  $_POST['uname'] = $name;
//  $_POST['psw'] = common::excuteOneSql("select ra_password from public.ra_online_user where user_login = '".$name."'");
//  login::getInstance()->do_login_auto("test");
//验证路由权限
common::securityCheckHandNew($action);
if (_is_only_vgm()) {
    Header("Location:main.php?action=main");
}
error_log("--------------前端请求post参数---------------");
error_log(utils::jsonFiltration("null", "\"\"", json_encode($_POST)));
error_log("--------------前端请求get参数---------------");
error_log(utils::jsonFiltration("null", "\"\"", json_encode($_GET)));
//Operation log 日志记录
utils::operation_log_records();
switch ($action) {
    case 'ocean_booking':
        ocean_booking::getInstance()->ocean_booking();
        break;
    case 'ocean_order':
        ocean_order::getInstance()->ocean_order();
        break;
    case 'ajax':
        echo ajax::getInstance()->run();
        break;
    case 'opreation_log':
        echo operation_log::getInstance()->operation_log();
        break;
    case 'main':
        //如果在没有登录前，没有登录信息，指定用户
        if(!isset($_SESSION['ONLINE_USER'])){
            $menuList = array();
            $menuList[] = array("index"=>'1',"label"=>"Dashboard","cleaned_field_name"=>"menu.dashboard","icon"=>"icon_data_fill_b","path"=>"/dashboard");
            $menuList[] = array("index"=>'2',"label"=>"Booking","cleaned_field_name"=>"menu.booking","icon"=>"icon_booking__fill_b","path"=>"/booking");
            $menuList[] = array("index"=>'3',"label"=>"Tracking","cleaned_field_name"=>"menu.tracking","icon"=>"icon_tracking__fill_b","path"=>"/tracking");
        } else {
            //d登录后，根据配置的权限，是否显示
            if (_isAdmin())
                $sql = "select f_column, min(menu_id) as menu_id, max(menu_icon_font) as menu_icon_font from public.ra_online_permission where has_new_version=true group by f_column order by min(order_by)";
            elseif (_isNewUser())
                $sql = "select f_column, min(menu_id) as menu_id, max(menu_icon_font) as menu_icon_font from public.ra_online_permission where has_new_version=true and customer_check = true group by f_column ORDER BY min(order_by)";
            else
                $sql = "select p.f_column, min(menu_id) as menu_id, max(menu_icon_font) as menu_icon_font from public.ra_online_user_permission up left join public.ra_online_permission p on up.p_id = p.id where  has_new_version=true and lower(user_name) = '" . common::check_input(strtolower(_getLoginName())) . "' group by p.f_column ORDER BY min(p.order_by)";
            $rs = common::excuteListSql($sql);
            if (empty($rs) && _isApexLogin()) {
                $sql = "select f_column, min(menu_id) as menu_id, max(menu_icon_font) as menu_icon_font from public.ra_online_permission where has_new_version=true and default_check = true group by f_column ORDER BY min(order_by)";
                $rs = common::excuteListSql($sql);
            }
            $menuSetting = array("Ocean Booking" => array("label"=>"Booking","cleaned_field_name"=>"menu.booking","path"=>"/booking","icon"=>"icon_booking__fill_b"),
                                "Ocean B/L Info." => array("label"=>"Tracking","cleaned_field_name"=>"menu.tracking","path"=>"/tracking","icon"=>"icon_tracking__fill_b"));
            $menuList = array();
            $menuList[] = array("index"=>'1',"label"=>"Dashboard","cleaned_field_name"=>"menu.dashboard","icon"=>"icon_data_fill_b","path"=>"/dashboard");
            $index = 2;
            foreach ($rs as $value) {
                if ($value['f_column'] == "Shipment Info."){
                    if (_isAdmin())
                        $ll = common::excuteListSql("select s_column, url_action, sub_menu_id from public.ra_online_permission where has_new_version=true and f_column = '" . common::check_input($value['f_column']) . "' ORDER BY order_by");
                    elseif (_isNewUser())
                        $ll = common::excuteListSql("select s_column, url_action, sub_menu_id from public.ra_online_permission where has_new_version=true and customer_check = true and f_column = '" . common::check_input($value['f_column']) . "' ORDER BY order_by");
                    else
                        $ll = common::excuteListSql("select s_column, url_action, sub_menu_id from public.ra_online_permission p left join public.ra_online_user_permission gp on gp.p_id = p.id where has_new_version=true and f_column = '" . common::check_input($value['f_column']) . "' and lower(gp.user_name) = '" . common::check_input(strtolower(_getLoginName())) . "' ORDER BY p.order_by");
                    if (empty($ll) && _isApexLogin()) {
                        $ll = common::excuteListSql("select s_column, url_action, sub_menu_id from public.ra_online_permission where has_new_version=true and f_column = '" . common::check_input($value['f_column']) . "' and default_check = true ORDER BY order_by");
                    }
                    //单独拼接URL
                    foreach ($ll as $v) {
                        if($v['s_column'] =="Ocean B/L Info."){
                            $urlData = $menuSetting[$v['s_column']];
                            $menuList[] = array("index"=>"$index","label"=>$urlData['label'],"cleaned_field_name"=>$urlData['cleaned_field_name'],"icon"=>$urlData['icon'],"path"=>$urlData['path']);
                            $index = $index + 1;
                        }
                        //特殊处理book,需要加二级菜
                        if($v['s_column'] =="Ocean Booking"){
                            // $urlData = $menuSetting[$v['s_column']];
                            // $menuList[] = array("index"=>"$index","label"=>$urlData['label'],"icon"=>$urlData['icon'],"path"=>$urlData['path']);
                            $bookingManagement = array("index"=>"$index","label"=>"Booking","cleaned_field_name"=>"menu.booking","icon"=>"icon_booking__fill_b","type"=>"list");
                            $children  = array(); 

                            $urlData = $menuSetting[$v['s_column']];
                            $children[] = array("index"=>$index.'-1',"label"=>"Booking Management","cleaned_field_name"=>"menu.booking_management","path"=>$urlData['path']);
                            $children[] = array("index"=>$index.'-2',"label"=>"Destination Delivery","cleaned_field_name"=>"menu.destination_delivery","path"=>"/destination-delivery");
                            $bookingManagement["children"] = $children;
                            $menuList[] = $bookingManagement;
                            $index = $index + 1;
                        }
                    }
                }
            }
            //Report 暂时先这么写，后面这个kln 单独维护独有的菜单栏，和online 隔离开
            $urlData = $menuSetting[$v['s_column']];
            $menuList[] = array("index"=>"4","label"=>"Report","cleaned_field_name"=>"menu.report","icon"=>'icon_report__fill_b',"path"=>'/report');
            $index = $index + 1;

            $systemManagement = array("index"=>"$index","label"=>"System Management","cleaned_field_name"=>"menu.system_management","icon"=>"icon_system__management_fill_b","type"=>"list"); 
            $children  = array();
            if(_isVipReportAdmin(_getLoginName())){
                $children[] = array("index" =>$index.'-7',"label" =>"Template Management","cleaned_field_name"=>"menu.template_management","path"=>"/template-management");
            } 
            $children[] = array("index" =>$index.'-1',"label" =>"System Message","cleaned_field_name"=>"menu.system_message","path"=>"/system-message");
            $children[] = array("index" =>$index.'-2',"label" =>"System Settings","cleaned_field_name"=>"menu.system_settings","path"=>"/system-settings"); 
            if(_isTranslationReviewerAdmin(_getLoginName())){
                $children[] = array("index" =>$index.'-3',"label" =>"multilingual_config","cleaned_field_name"=>"menu.multilingual_config","path"=>"/multilingual-config");
            }   
            //只有chud的账户可以看日志记录
            if(strtolower(_getLoginName()) == "chud" 
                || strtolower(_getLoginName()) == strtolower("IT.Andywu")
                || strtolower(_getLoginName()) == "ra.admin"){
                    $children[] = array("index" =>$index.'-4',"label" =>"Chat Log","cleaned_field_name"=>"menu.chat_log","path"=>"/chat-log");
                    $children[] = array("index" =>$index.'-5',"label" =>"AI API Log","cleaned_field_name"=>"menu.ai_api_log","path"=>"/ai-api-log");
                    $children[] = array("index" =>$index.'-6',"label" =>"Operation Log","cleaned_field_name"=>"menu.operation_log","path"=>"/operation-log");
                    $children[] = array("index" =>$index.'-7',"label" =>"Prompt Configuration","cleaned_field_name"=>"menu.prompt_configuration","path"=>"/prompt-configuration");  
            }
            $systemManagement["children"] = $children;
            if (!empty($children)){
                $menuList[] = $systemManagement;
            }
        }
        common::echo_json_encode(200, $menuList);
        exit();
        break;
    case 'main_filter':
        $data  = common::getManagement();
        common::echo_json_encode(200, $data['dashboard_filter']);
        exit();
        break;    
    case 'main_report':
        //分担查询
        include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';

        //这里只剩下r3，r4的查询
        $type = $_REQUEST["r_type"];
       
        $sqlWhere = ' and ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere = " " . $sqlWhere;
        $sqlWhere .= common::getDashboardTransportationSqlWhere();
        $sqlWhere .= common::getDashboardContactIDSqlWhere();
        
        $sqlWhere = common::check_input($sqlWhere);

        // $list = common::excuteListSql("select * from public.online_order_status_date_kln_pending('$type'::text, '$sqlWhere'::text) "
        //             . "r (c bigint, e integer, b integer, d text)");
        $sql = "select * from public.online_order_status_date_kln_pending('$type'::text, '$sqlWhere'::text) "
                    . "r (c bigint, e integer, b integer, d text)";            
        $list =  $mapdb->GetAll($sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $sql), 0));
   
        $key = "[";
        $value = "[";
        $totalValue = 0;
        foreach ($list as $vv) {
            if ($key != "[") {
                $key .= ",";
                $value .= ",";
            }
            if ($type == "r2" || $type == "r3"  || $type == "r4") {
                $key .= '"' . $vv["d"] . '"';
                $value .= '{"value":"' . $vv["c"] . '","name":"' . $vv["d"] . '" }';
                $totalValue = $totalValue + $vv["c"];
            } else {
                if ($vv["e"] == -1) {
                    $key .= '"Over 80 Days"';
                    $value .= '{"value":"' . $vv["c"] . '","name":"Over 80 Days"}';
                } else {
                    $key .= '"' . $vv["b"] . '-' . $vv["e"] . ' Days"';
                    $value .= '{"value":"' . $vv["c"] . '","name":"' . $vv["b"] . '-' . $vv["e"] . ' Days"}';
                }
                $totalValue = $totalValue + $vv["c"];
            }
        }
        $key .= "]";
        $value .= "]";
        //处理返回原表数据格式 r1 r3 r4的数据返回
        $data = common::mian_repot_do($value,$type,$totalValue);
        common::echo_json_encode(200, $data);
        exit();
        break;
    case 'main_report_etd':
        //分担查询
        include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';

        //这里只有r1查询
        $type = "r1";
        $container_type = "";
        $date_type = strtolower($_REQUEST["date_type"]);
        if (empty($_REQUEST["date_start"])) {
            $bdate = "null";
        } else {
            $bdate = $_REQUEST["date_start"];
            $bdate = "'$bdate'";
        }
        if (empty($_REQUEST["date_end"])) {
            $edate = "null";
        } else {
            $edate = $_REQUEST["date_end"];
            $edate = "'$edate'";
        }
        $sqlWhere = ' and ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere = " " . $sqlWhere;
        $sqlWhere = $sqlWhere . common::getDashboardTransportationSqlWhere();
        $sqlWhere .= common::getDashboardContactIDSqlWhere();
        $sqlWhere = common::check_input($sqlWhere);

        // $list = common::excuteListSql("select * from public.online_order_status_date_kln_r1_r2('$type'::text, 'eta'::text, 'etd'::text, $bdate, $edate, '$sqlWhere'::text, '$container_type'::text,'$date_type'::text) "
        //                      . "r (c bigint, e integer, b integer, d text)"); 
        $sql = "select * from public.online_order_status_date_kln_r1_r2('$type'::text, 'eta'::text, 'etd'::text, $bdate, $edate, '$sqlWhere'::text, '$container_type'::text,'$date_type'::text) "
                             . "r (c bigint, e integer, b integer, d text)";            
        $list =  $mapdb->GetAll($sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $sql), 0));   

        $key = "[";
        $value = "[";
        $totalValue = 0;
        foreach ($list as $vv) {
            if ($key != "[") {
                $key .= ",";
                $value .= ",";
            }
            if ($type == "r2" || $type == "r3" || $type == "r4") {
                $key .= '"' . $vv["d"] . '"';
                $value .= '{"value":"' . $vv["c"] . '","name":"' . $vv["d"] . '" }';
                $totalValue = $totalValue + $vv["c"];
            } else {
                if ($vv["e"] == -1) {
                    $key .= '"Over 80 Days"';
                    $value .= '{"value":"' . $vv["c"] . '","name":"Over 80 Days"}';
                } else {
                    $key .= '"' . $vv["b"] . '-' . $vv["e"] . ' Days"';
                    $value .= '{"value":"' . $vv["c"] . '","name":"' . $vv["b"] . '-' . $vv["e"] . ' Days"}';
                }
                $totalValue = $totalValue + $vv["c"];
            }
        }
        $key .= "]";
        $value .= "]";
        //处理返回原表数据格式 r1 r3 r4的数据返回
        $data = common::mian_repot_do($value,$type,$totalValue);
        common::echo_json_encode(200, $data);
        exit();
        break;    
    case 'main_report_kpi':
        //分担查询
        include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';

        $type = $_REQUEST["r_type"];
        $sqlWhere = ' and ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere = " " . $sqlWhere;
        $sqlWhere = $sqlWhere . common::getDashboardTransportationSqlWhere();
        $sqlWhere .= common::getDashboardContactIDSqlWhere();
        $sqlWhere = common::check_input($sqlWhere);
        
        $date_type = strtolower($_REQUEST["date_type"]);
        if (isset($_REQUEST['date_start']) && !empty($_REQUEST['date_start']))
            $sqlWhere .= " and $date_type >= ''" . common::usDate2sqlDate($_REQUEST['date_start']) . " 00:00:00''";
        if (isset($_REQUEST['date_end']) && !empty($_REQUEST['date_end']))
            $sqlWhere .= " and $date_type <= ''" . common::usDate2sqlDate($_REQUEST['date_end']) . " 23:59:59''";
        
        // $list = common::excuteListSql("select * from public.online_order_status_date_kln_kpi('$type'::text,'$sqlWhere'::text) "
        //         . "r (c bigint, d text)");
        $sql = "select * from public.online_order_status_date_kln_kpi('$type'::text,'$sqlWhere'::text) "
                . "r (c bigint, d text)";                         
        $list =  $mapdb->GetAll($sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $sql), 0));              
    
        $key = "[";
        $value = "[";
        $totalValue = 0;
        foreach ($list as $vv) {
            if ($key != "[") {
                $key .= ",";
                $value .= ",";
            }
            if ($type == "ata_r3" || $type == "atd_r4" ) {
                $key .= '"' . $vv["d"] .'"';
                $value .= '{"value":"' . $vv["c"] . '","name":"' . $vv["d"] .'"}';
                $totalValue = $totalValue + $vv["c"];
            }
        }
        $key .= "]";
        $value .= "]";
        
        //处理返回原表数据格式
        $ETDList = array();
        $value_arr = json_decode($value,true);
        
        foreach($value_arr as $arr){
            $color = common::getItemStyle($type,$arr['name']);
            if($arr['name'] == "0 Day"){
                $arr['name'] = "≤1 Day";
            }
            if($arr['name'] == "7 Days"){
                $arr['name'] = "≥7 Days";
            }
            $ETDList[] = array("value" =>intval($arr['value']),"name" =>$arr['name'],"itemStyle" =>array("color" =>$color));
        }

        // 排序依据的键数组（这里我们用一个数字数组来表示）
        $order = ["≤1 Day", "1-2 Days", "3-6 Days","≥7 Days"]; 
        $ETDList = utils::arrayOrderBykeys($order,$ETDList,"name");

        if($type == 'atd_r4'){
            $data = array("ETDList" =>$ETDList,"ETD_Radius"=>array('30%','50%'),"title1" =>"Departure","title2" =>"(ATD-ETD)","download_name" => "KPI Departure");
        }
        if($type == 'ata_r3'){
            $data = array("ETDList" =>$ETDList,"ETD_Radius"=>array('30%','50%'),"title1" =>"Arrival","title2" =>"(ATA-ETA)","download_name" => "KPI Arrival");
        }
        common::echo_json_encode(200, $data);
        exit();
        break;  
    case 'main_report_container_bar':
        //新UI 20  40  45 3份数据同时查询，且是柱状图，所以与r3 r4查询分开
        $container_type ='';
        $ContainerCounSeries = array();

        $date_type = strtolower($_REQUEST["date_type"]);
        $type = $_REQUEST["r_type"];
        if (empty($_REQUEST["date_start"])) {
            $bdate = "null";
        } else {
            $bdate = $_REQUEST["date_start"];
            $bdate = "'$bdate'";
        }
        if (empty($_REQUEST["date_end"])) {
            $edate = "null";
        } else {
            $edate = $_REQUEST["date_end"];
            $edate = "'$edate'";
        }
        $list= array();
        common::set("tableName", "online_container");
        $sqlWhere = ' and ' . common::searchExtendHandNew("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere = common::check_input($sqlWhere);
        $sqlWhere .= common::getDashboardContactIDSqlWhere("container_bar");
        $sqlWhere = " " . $sqlWhere;
        common::set("tableName", "");
        //这个20  40  45都查询出来。否则值查询都对应的size,其他的不查询
        $value_arr = common::excuteListSql("select * from public.online_order_status_date_kln_r1_r2('$type'::text, 'eta'::text, 'etd'::text, $bdate, $edate, '$sqlWhere'::text, '$container_type'::text, '$date_type'::text) "
                    . "r (month_group text, _20_rc numeric, _40_rc numeric, _45_rc numeric)"); 

        $key_arr = array();
        $container_type_arr = array("20","40","45");
        foreach($container_type_arr as $_container_type){
            $data = array();
            //每一个size 的所有月份总计
            $total = 0;
            //每一个size 对应的最大值
            $max = 0;
            foreach($value_arr as $arr){
                $data[] = intval($arr['_'.$_container_type.'_rc']);
                $total = $total + intval($arr['_'.$_container_type.'_rc']);
                $max = $max < intval($arr['_'.$_container_type.'_rc']) ? intval($arr['_'.$_container_type.'_rc']) : $max;

                if ($_container_type == '20'){
                    $key_arr[] = $arr['month_group'];
                }
            }
            $ContainerCounSeries[$_container_type] = array("data"=>$data,"total"=>$total,"max"=>$max);
        }
        
        //处理返回时数据格式
        $ContainerCounSeries_return = array();
        //横坐标栏位
        $ContainerCount_Title = $key_arr;

        //所查的所有模式计算总和
        $total = 0;
        //所有size下的最大值，方便标注计算最长的Y坐标
        $max_all = 0;
        foreach($ContainerCounSeries as $k =>$v){
            //这个type 是页面传过来的。这个固定r2
            $color = common::getItemStyle($type,$k);
            $total = $total + $v['total'];
            $ContainerCounSeries_return[] = array("name"=>strval($k),"type"=>"bar","emphasis" => array("focus" =>"none"),
                "stack" =>"总计","data" =>$v['data'],"itemStyle" =>array("color" =>$color));
            $max_all = $max_all +  $v['max'];
        }
        //计算Y坐标的间隔
        $interval = utils::calculateTicks(0,$max_all,5);
        if($interval == 0){
            //处理返回默认值
            $interval = 2;
        }
        $returnData = array("ContainerCount_Title"=>"Total: $total","ContainerCountList" =>$ContainerCount_Title,"ContainerCounSeries" =>$ContainerCounSeries_return,
            "min" => 0,"Max" =>$interval*5,"interval" =>$interval,"download_name" => "Container Count");
        //demo    
        //  $json = '{"code":200,"data":{"ContainerCount_Title":"Total: 463518","ContainerCountList":["OCT,23","NOV,23","DEC,23","JAN,24","FEB,24","MAR,24","APR,24","MAY,24","JUN,24","JUL,24","AUG,24","SEP,24","OCT,24"],"ContainerCounSeries":[{"name":"20","type":"bar","emphasis":{"focus":"none"},"stack":"\u603b\u8ba1","data":[4400,8600,8804,9271,8961,8793,8534,9476,9530,10277,10081,8997,5784],"itemStyle":{"color":"#FF7500"}},{"name":"40","type":"bar","emphasis":{"focus":"none"},"stack":"\u603b\u8ba1","data":[13652,28021,30422,30136,26320,27035,23979,26494,28406,33318,32318,30962,16369],"itemStyle":{"color":"#FFAC66"}},{"name":"45","type":"bar","emphasis":{"focus":"none"},"stack":"\u603b\u8ba1","data":[189,337,415,437,439,321,250,320,324,603,328,415,200],"itemStyle":{"color":"#FFE3CC"}}],"min":0,"Max":45000,"interval":9000}}';    
        //  $returnData = json_decode($json,true);
        //  echo json_encode($returnData);
        //  exit();
        common::echo_json_encode(200, $returnData);
        exit();
        break;
    
    case 'main_report_co2e_bar':
        $returnData = common::getCo2eBar();
        common::echo_json_encode(200, $returnData);
        exit();
        break;   
    case 'main_report_top10_bar':
        $returnData = common::getTopBarNew();
        common::echo_json_encode(200, $returnData);
        exit();
        break;
    case 'main_report_revenue':
        $returnData = common::getRevenue();
        common::echo_json_encode(200, $returnData);
        exit();
        break;            
    case 'main_map':
        $uncode = $_POST["uncode"];
        $serial_no = $_POST["serial_no"];
        $unall = explode("|", $uncode);
        $sql = "";
        if (!empty($unall[0]) && $unall[0] != "''") {
            $sql .= "select lon as lng, lat as lat, uncode, 'Place of receipt: '||name1 as location_namewo, 'por' as type from vessel.vt_unlocode where "
                    . "lon<>0 and lat<>0 and lon is not null and lat is not null and uncode in "
                    . "($unall[0])";
        }
        if (!empty($unall[1]) && $unall[1] != "''") {
            if (!empty($sql)) {
                $sql .= " union ";
            }
            $sql .= "select lon as lng, lat as lat, uncode, 'POL: '||name1 as location_namewo, 'pol' as type from vessel.vt_unlocode where "
                    . "lon<>0 and lat<>0 and lon is not null and lat is not null and uncode in "
                    . "($unall[1])";
        }
        if (!empty($unall[2]) && $unall[2] != "''") {
            if (!empty($sql)) {
                $sql .= " union ";
            }
            $sql .= "select lon as lng, lat as lat, uncode, 'POD: '||name1 as location_namewo, 'pod' as type from vessel.vt_unlocode where "
                    . "lon<>0 and lat<>0 and lon is not null and lat is not null and uncode in "
                    . "($unall[2])";
        }
        if (!empty($unall[3]) && $unall[3] != "''") {
            if (!empty($sql)) {
                $sql .= " union ";
            }
            $sql .= "select lon as lng, lat as lat, uncode, 'Place of delivery: '||name1 as location_namewo, 'poe' as type from vessel.vt_unlocode where "
                    . "lon<>0 and lat<>0 and lon is not null and lat is not null and uncode in "
                    . "($unall[3])";
        }
        $tkStatus = common::excuteListSql("select tl.by_pickup, g.consignee_exp, g.pickup_from_exp, c.ctnr, g.deliveryto_exp, g.serial_no, tl.location, tl.type, tl.id, 
            to_char(tl.upload_time, 'MM/DD/YYYY HH24:MI:SS') as utime 
            from public.tk_general g, public.tk_commodity c left join public.tk_location tl on c.serial_no=tl.serial_no 
            where g.serial_no=c.serial_no and g.bol=(select m_bol from public.ocean where serial_no='$serial_no') order by tl.order_id");
        foreach ($tkStatus as $vv) {
            $ll = explode(",", $vv["location"]);
            if ($vv["by_pickup"] == "t") {
                $pf = explode("\r\n", $vv["pickup_from_exp"]);
                $pf = common::check_input($pf[0]) . "(" . $vv["ctnr"] . ")";
                $dt = explode("\r\n", $vv["consignee_exp"]);
                $dt = common::check_input($dt[0]) . "(" . $vv["ctnr"] . ")";
            } else {
                $pf = explode("\r\n", $vv["consignee_exp"]);
                $pf = common::check_input($pf[0]) . "(" . $vv["ctnr"] . ")";
                $dt = explode("\r\n", $vv["deliveryto_exp"]);
                $dt = common::check_input($dt[0]) . "(" . $vv["ctnr"] . ")";
            }
            if ($vv["type"] == "26" && !empty($ll[1])) {
                if (!empty($sql)) {
                    $sql .= " union ";
                }
                $sql .= "select '" . $ll[1] . "' as lng, '" . $ll[0] . "' as lat, '" . $vv["ctnr"] . "' as uncode, 'Pick up: " . $vv["utime"] . "<br>'||'" . $pf . "' as location_namewo, 'pickup' as type";
            } elseif ($vv["type"] == "25" && !empty($ll[1])) {
                if (!empty($sql)) {
                    $sql .= " union ";
                }
                $sql .= "select '" . $ll[1] . "' as lng, '" . $ll[0] . "' as lat, '" . $vv["ctnr"] . "' as uncode, 'Delivery: " . $vv["utime"] . "<br>'||'" . $dt . "' as location_namewo, 'delivery' as type";
            }
        }

        if (!empty($sql)) {
            $gps = common::excuteListSql($sql . "");
        }
        if (!empty($serial_no)) {
            $all = explode(",", $serial_no);
            foreach ($all as $a) {
                $rs = common::excuteOneSql("select public.get_vessel_position('$a'::text)");
                if (!empty($rs)) {
                    $rss = explode("|", $rs);
                    $lng_lat = explode(",", $rss[1]);
                    $gps[] = array("lng" => $lng_lat[0], "lat" => $lng_lat[1], "uncode" => "", "location_namewo" => "", "type" => "vessel", "vessel_name" => $rss[2], "m_shipname" => "", "mmsi" => "", "imo" => "", "m_shipstate" => "", "m_shiptype" => "", "m_dest" => "", "m_newshiparrive" => "", "m_newlasttime" => "", "lasttime" => "", "sub" => "");
                }
            }
        }
        $data = array("gps" => $gps);
        common::echo_json_encode("200", $data);
        exit();
        break;
    case 'main_map_new':
        include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';
        //修改添加port_of_transshipment 和port_of_transshipment_name的坐标获取(有为空的可能)
        $serial_no = $_REQUEST["serial_no"];
        $_schemas = $_REQUEST['_schemas'];
        if(empty($_schemas)){
            $_schemas ="public";
        }
        //$serial_no = "F41E6016-1A97-4C93-8198-53D8B3B26220";
        $transport_mode = common::excuteOneSql("SELECT transport_mode FROM public.kln_ocean ko 
                    WHERE ko.serial_no = '$serial_no' and order_from = '$_schemas'");
        if($transport_mode == "sea"){
            $sql = "with ss as (select '$serial_no' as sn)
            , aa as (
                select place_of_receipt_un, place_of_delivery_un, fport_of_loading_un, mport_of_discharge_un, port_of_loading, port_of_discharge, place_of_delivery_exp, place_of_receipt_exp,dd.*
                    from public.kln_ocean oo 
                    left join LATERAL (
                        select (select uncode from ports where  code = o.port_of_transshipment) as port_of_transshipment_un,
                                port_of_transshipment_name
                            from  $_schemas.ocean o where o.serial_no=oo.serial_no order by id desc limit 1
                    ) dd on true
                    where serial_no=(select sn from ss) limit 1
            )
            , rr as (
                    select lon as lng, lat as lat, 'Origin' as label, port_of_loading as infor, 1 as sort, null::timestamp without time zone as stime, 'pol'::text as ptype 
                        from vessel.vt_unlocode, aa where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=fport_of_loading_un
                    union all
                    select lon as lng, lat as lat, 'Destination' as label, port_of_discharge as infor, 2 as sort, null::timestamp without time zone as stime, 'pod'::text as ptype 
                        from vessel.vt_unlocode, aa where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=mport_of_discharge_un
                    union all
                    select lon as lng, lat as lat,  'Transfer' as label, port_of_transshipment_name as infor, 3 as sort, null::timestamp without time zone as stime, 'poe'::text as ptype 
                        from vessel.vt_unlocode, aa where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=port_of_transshipment_un
                )
            select * from rr order by sort, stime";
        } elseif ($transport_mode == "air"){
            $sql = "with ss as (select '$serial_no' as sn)
            , aa as (
                select place_of_receipt_un, place_of_delivery_un, fport_of_loading_un, mport_of_discharge_un, port_of_loading, port_of_discharge, place_of_delivery_exp, place_of_receipt_exp
                    from public.kln_ocean oo 
                    where serial_no=(select sn from ss) limit 1
            )
            , rr as (
                    select lon as lng, lat as lat, 'Origin' as label, port_of_loading as infor, 1 as sort, null::timestamp without time zone as stime, 'pol'::text as ptype 
                        from vessel.vt_unlocode, aa where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=fport_of_loading_un
                    union all
                    select lon as lng, lat as lat, 'Destination' as label, port_of_discharge as infor, 2 as sort, null::timestamp without time zone as stime, 'pod'::text as ptype 
                        from vessel.vt_unlocode, aa where (lat<=90 and lat>=-90) and (lon<=180 and lon>=-180) and lon is not null and lat is not null and uncode=mport_of_discharge_un
                )
            select * from rr order by sort, stime";
        }
        
        $rss = common::excuteListSql($sql);
        //$rss = $mapdb->GetAll($sql);
        //先固定死
        // $json = '[{"lng":"100.78594000","lat":"13.68521000","label":"Origin","infor":"LAT KRABANG, THAILAND","sort":"0","stime":null,"ptype":"por"},
        //           {"lng":"100.88333333","lat":"13.08333333","label":"Destination","infor":"LAEM CHABANG PORT,THAILAND","sort":"1","stime":null,"ptype":"pol"},
        //           {"lng":"-122.28640000","lat":"37.79784000","label":"Transfer","infor":"OAKLAND, CA, USA","sort":"2","stime":null,"ptype":"pod"}]';
        //$rss = json_decode($json,true);
        ///global $mapdb;
        //查询线(包含所有的线)  空运不查询航线
        if ($transport_mode == "air"){
            $Line =array();
        }else{
            error_log("select * from get_track_data('$serial_no',true)");
            $map_sql = "select * from get_track_data('$serial_no',true)";
            $Line = $mapdb->GetAll($map_sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $map_sql), 0));
        }

        $solidLine = array();
        $dottedLine = array();
        $rangePoint = array();
        foreach($Line as $line){
            if($line['tp'] == "1"){
                $solidLine[] = $line;
            }elseif($line['tp'] == "0"){
                $dottedLine[] = $line;
            }else{
                $rangePoint[] = $line;
            }
        }
        //如果没有虚线，这这个范围点也是异常的，不需要显示
        if(empty($dottedLine)){
            $rangePoint = array();
        }
        $data = array("point"=>$rss,"solidLine"=>$solidLine,"dottedLine"=>$dottedLine,"rangePoint"=>$rangePoint);
        common::echo_json_encode(200, $data);
        //common::echo_json_encode(200, $rss);
        exit();
        break;
    case 'main_welcome':
        //分担查询
        include ONLINE_ROOT . 'libs' . DS . 'map_config.ini.php';

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

        $sqlWhere = ' where ' . common::searchExtendHand_KLN("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere .= common::getDashboardTransportationSqlWhere();
        $sqlWhere .= common::getDashboardContactIDSqlWhere();
        
        // test dome 
        // $_POST['is_default'] = "no";
        // $_POST['date_start'] =null;
        // $_POST['date_end'] = null;
        //默认初始条件：ALL& ETD& Current Month 当前月的1号到最后一天 这个默认条件已经废除，前端不会这样传参数
        if (isset($_POST['is_default']) && strtolower($_POST['is_default']) == "yes" && false){
            $sqlWhere .= "and etd>='" . date("Y-m") . "-01' and etd<='" . date("Y-m") . "-01'::date + interval '1 month'";
        } else {
            $date_type = strtolower(common::check_input($_POST ['date_type']));
            if (isset($_POST['date_start']) && !empty($_POST['date_start']))
                $sqlWhere .= " and $date_type >= '" . common::usDate2sqlDate($_POST['date_start']) . " 00:00:00'";
            if (isset($_POST['date_end']) && !empty($_POST['date_end']))
                $sqlWhere .= " and $date_type <= '" . common::usDate2sqlDate($_POST['date_end']) . " 23:59:59'";
        }
        if (isset($_POST['customer']) && !empty($_POST['customer'])){
            $sqlWhere .= " and (shipper ilike '%".common::check_input($_POST['customer'])."%' or shipper_id ilike '%".common::check_input($_POST['customer'])."%' 
                or consignee ilike '%".common::check_input($_POST['customer'])."%' or consignee_id ilike '%".common::check_input($_POST['customer'])."%')";
        }

        $rc = $_POST ['rc'];
        if ($rc == - 1) {
            $sql = "SELECT count(1) from public.kln_ocean" . $sqlWhere;
            //$rc = common::excuteOneSql($sql);
            $rc =  $mapdb->GetOne($sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $sql), 0));
        }
        $tp = ceil($rc / $ps);
        if ($rc > 0) {
            $sql = "SELECT order_from as _schemas,serial_no, consignee, shipper, h_bol,final_desination_uncode, transport_mode,transport_mode_extend,
                        to_char(etd, 'MM/DD/YYYY'::text) as etd,
                        to_char(eta, 'MM/DD/YYYY'::text) AS eta, 
                        fport_of_loading_un, mport_of_discharge_un, place_of_receipt_un, place_of_delivery_un, booking_no,
                        f_vessel,f_voyage,origin,
                        CASE
                            WHEN ((m_iffbcf is not null or m_iffbcf is null) and m_iffcpu is null and m_iffrec is null and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Created'::text
                            WHEN ((m_iffcpu is not null or m_iffrec is not null) and m_iffdep is null and m_iffarr is null and m_iffdel is null) THEN 'Cargo Received'::text
                            WHEN (m_iffdep is not null and m_iffarr is null and m_iffdel is null) THEN 'Departed'::text
                            WHEN (m_iffarr is not null and m_iffdel is null) THEN 'Arrived'::text
                            WHEN (m_iffdel is not null) THEN 'Completed'::text
                            ELSE 'Created'::text
                        END AS new_status
                    FROM public.kln_ocean $sqlWhere";
            //$sqlWhere  and last_status_315_update_time is not null";
            $sql .= " ORDER BY eta desc,id DESC limit " . $ps . " offset " . ($cp - 1) * $ps;
            //$sql .= " ORDER BY last_status_315_update_time DESC limit " . $ps . " offset " . ($cp - 1) * $ps;
            //$rss = common::excuteListSql($sql);
            $rss =  $mapdb->GetAll($sql) or ( (!$mapdb->ErrorMsg()) or error_log(common::dbLog($mapdb, $sql), 0));
            //RecentStatusList
            $RecentStatusList = array();
            foreach ($rss as $key => $value) {
                $serial_no = $value["serial_no"];
                $_schemas_bk = $value['_schemas'];
                $_schemas = $value['_schemas'];
                if($_schemas == "public"){
                    $_schemas = "ocean";
                }

                //单独取查询milestone信息
                if ($value['transport_mode'] == "sea"){
                    $milestone_sql = "select a.act_date,a.act_time,sn.description,a.timezone,
                                a.code as dd_code
                            from  public.ocean_milestone a 
                                inner join public.customer_service_milestone_sno sn on sn.code=a.code and sn.type = 'sea'
                        where a.serial_no='$serial_no' 
                            and a.code in (select regexp_split_to_table('IFFBCF,IFFCPU,IFFREC,IFFDEP,IFFARR,IFFDEL', ','))
                            and a.act_date is not null
                        order by sn.sno desc limit 1";
                } elseif ($value['transport_mode'] == "air"){
                    //air milestone 的Departed实际描述要替换： IFFDEP =》 IFFONB 
                    $milestone_sql = "select a.act_date,a.act_time,sn.description,a.timezone,
                            a.code as dd_code
                        from  $_schemas_bk.air_milestone a 
                            inner join public.customer_service_milestone_sno sn on sn.code=a.code and sn.type = 'air'
                    where a.serial_no='$serial_no' 
                        and a.code in (select regexp_split_to_table('IFFBCF,IFFCPU,IFFREC,IFFONB,IFFARR,IFFDEL', ','))
                        and a.act_date is not null
                    order by sn.sno desc limit 1";
                }
                $milestone = common::excuteObjectSql($milestone_sql);
                $value["act_date"] = $milestone["act_date"];
                $value["act_time"] = $milestone["act_time"];
                $value["description"] = $milestone["description"];
                $value["timezone"] = $milestone["timezone"];
                $value["dd_code"] = $milestone["dd_code"];

                $timezone = "";
                //按照最新execl Timezone From 来自于origin 
                if($value['dd_code'] == "IFFBCF" || $value['dd_code'] == "IFFCPU"){
                    $sql = "SELECT (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'')) as timezone
                         FROM $_schemas.contacts c WHERE  c.contact_id = '".$value['origin']."'";
                    $timezone = common::excuteOneSql($sql);     
                }
                //按照最新execl Timezone From 来自于final_destination
                if($value['dd_code'] == "IFFDEL"){
                    $sql = "select time_zone from public.city_timezone where uncode = '".$value['final_desination_uncode']."'"; 
                    $timezone = common::excuteOneSql($sql);     
                }

                if($value['dd_code'] == "IFFREC" || $value['dd_code'] == "IFFDEP" || $value['dd_code'] == "IFFARR"){
                    $EDI315Time = array();
                    if ($value['transport_mode'] == "sea"){
                        //Timezone From 来自于EDI315
                        $EDI315Time = common::getEDI315Time($serial_no,$value['_schemas']);
                    }

                    if($value['dd_code'] == "IFFREC" || $value['dd_code'] == "IFFDEP"){
                        //先以EDI315 时区为准，如果没有这代表数据是手动输入，或者没有同步情况
                        $timezone = $EDI315Time[$value['dd_code']]['timezone'];
                        if(empty($timezone)){
                            if($value['dd_code'] == "IFFREC"){
                                $sql = "select time_zone from public.city_timezone where uncode = '".$value['place_of_receipt_un']."'"; 
                            }else{
                                $sql = "select time_zone from public.city_timezone where uncode = '".$value['fport_of_loading_un']."'";
                            }
                            $timezone = common::excuteOneSql($sql);
                        }     
                    }
                    if($value['dd_code'] == "IFFARR"){
                        $timezone = $EDI315Time[$value['dd_code']]['timezone'];
                        if(empty($timezone)){
                            $sql = "select time_zone from public.city_timezone where uncode = '".$value['mport_of_discharge_un']."'";
                            $timezone = common::excuteOneSql($sql);
                        }
                    }
                }
                
                $Arrived = $value['description'];

                $startStation=$value['fport_of_loading_un'];
                $endStation=$value['mport_of_discharge_un'];
                if ($value['transport_mode'] == "sea"){
                    $startStation=$value['place_of_receipt_un'];
                    $endStation=$value['place_of_delivery_un'];
                }
                if ($value['transport_mode'] == "air"){
                    $startStation=!empty($ocean['place_of_receipt_un'])? $ocean['place_of_receipt_un'] : $ocean['fport_of_loading_un'];
                    $endStation=!empty($ocean['place_of_delivery_un']) ? $ocean['place_of_delivery_un'] : $ocean['mport_of_discharge_un'];
                }

                $RecentStatusList[] = array("Title"=>$value['h_bol'],
                    "name"=>utils::outDisplayForMerge($value['f_vessel'],$value['f_voyage']),
                    "bookingNumber" =>$value['booking_no'],
                    "IsSubscribe" =>false,
                    "shipperName"=>$value['shipper'],
                    "consigneeName" =>$value['consignee'],
                    "startStation"=>$startStation,
                    "endStation"=>$endStation,
                    "ETD"=>$value['etd'],
                    "ETA"=>$value['eta'],
                    "type"=>$value['new_status'],
                    "Arrived"=>$Arrived,
                    "Time"=>$value["act_date"],
                    "act_time" =>$value["act_time"],
                    "timezone"=>$timezone,
                    "_schemas"=>$value["_schemas"],
                    "transport_mode"=>$value["transport_mode_extend"],
                    'is_subscribe' =>common::checkedSubscribe($value['serial_no']),
                    "a" =>common::deCode($value['serial_no'], 'E'));
            }
            $data  = common::getManagement();
            $arrTmp = array('searchData' => $RecentStatusList, 
               'Management' => $data['Management'],
               'dashboard_filter' => $data['dashboard_filter'],
               'is_customer' =>_isCustomerLogin(),
               'rc' => $rc,
               'ps' => $ps,
               'cp' => $cp,
               'tp' => $tp);
        } else {
            $data  = common::getManagement();
            $arrTmp = array('searchData' => array(),
                'Management' => $data['Management'],
                'dashboard_filter' => $data['dashboard_filter']);
        }
        common::echo_json_encode(200,$arrTmp);
        exit();
        break;
    case 'save_layout':
        $management = json_encode($_POST["management"]);
        $dashboard_filter = json_encode($_POST["dashboardObj"]);
        $_param = "";
        if(!empty($_POST["management"])){
            $_param .= " management = '$management' ";
        }
        if(isset($_POST["dashboardObj"]) && !empty($dashboard_filter)){
            $_param .= ",dashboard_filter = '$dashboard_filter' ";
        }
        if(!empty($_param)){
            common::excuteUpdateSql("update public.ra_online_user set $_param where lower(user_login) = '" . strtolower(_getLoginName()) . "'");
        }
        $returnData = array("msg" => "success");
        common::echo_json_encode(200, $returnData);
        exit();
        break; 
    case 'password':
        tools::getInstance()->updatePassword();
        break;    
    case 'tools':
        tools::getInstance()->markSystem();
        break;
    case 'robot':
        robot::getInstance()->robot_prompt_configuration();
        break;
    case 'robot_chat':
        robot::getInstance()->ai_chat();
        break;          
    case 'robot_chat_log':
        robot::getInstance()->robot_chat_log();
        break;
    case 'robot_api_log':
        robot::getInstance()->robot_api_log();
        break;        
    case 'system_setting':
        tools::getInstance()->user_system_setting();
        break;
    case 'monitoring_setting':
        tools::getInstance()->user_monitoring_setting();
        break;
    case 'notifications_rules':
        tools::getInstance()->notifications_rules();
        break;
    case 'destination_delivery_load':
        destination_delivery::getInstance()->destination_delivery_load();
        break;
    case 'destination_delivery_config':
        destination_delivery::getInstance()->destination_delivery_config();
        break; 
    case 'destination_delivery_booking':
        destination_delivery::getInstance()->destination_delivery_booking();
        break;
    case 'report_config':
        report::getInstance()->report_config();
        break;
    case 'shipment_status_report':
        report::getInstance()->shipment_status_report();
        break;                                         
    case 'user_guide':
        $rootPath = realpath(dirname(__FILE__)) . DS;
        //common::download_file($rootPath."images\ACE-M1_ISF_ACI_User_Guide\ACE-M1_ISF_ACI_User_Guide.pdf", "ACE-M1_ISF_ACI_User_Guide_V2.0.pdf");
        // PDF文件在服务器上的位置
        $filename = $rootPath."userFile". DS."KLN_Online_User_Guide_24.11.26.pdf";
        $display_name = "KLN_Online_User_Guide_24_11_26.pdf";
        // Header content type
        header("Content-type: application/pdf");
        header("Content-Length: " . filesize($filename));
        header("Content-Disposition:attachment;filename=\"" . $display_name . "\"");
        // 将文件发送到浏览器。
        readfile($filename);
        break;
    case 'feature_update':
        $id = $_REQUEST["id"];
        $rootPath = realpath(dirname(__FILE__)) . DS;
        //common::download_file($rootPath."images\ACE-M1_ISF_ACI_User_Guide\ACE-M1_ISF_ACI_User_Guide.pdf", "ACE-M1_ISF_ACI_User_Guide_V2.0.pdf");
        // PDF文件在服务器上的位置
        $filename = $rootPath."upload". DS."feature_update_".$id.".pdf";
        $display_name = "feature_update.pdf";
        // Header content type
        header("Content-type: application/pdf");
        header("Content-Length: " . filesize($filename));
        header("Content-Disposition:inline;filename=\"" . $display_name . "\"");
        // 将文件发送到浏览器。
        readfile($filename);
        break;
    case 'kam_customer_auto_login':
        $name = common::check_input($_POST['uname']);
        $_POST['psw'] = common::excuteOneSql("select ra_password from public.ra_online_user where user_login = '".$name."'");
        login::getInstance()->do_login_auto();
        break;
    case 'multilingual':
        multilingual::getInstance()->multilingual_config();
        break;                                  
    default:
        $data = array(
            'msg' => 'Page not found',
        );
        common::echo_json_encode(500, $data);  
}
$db->Close();
$db = null;
if ($max_ec > 0)
    ini_set('max_execution_time', $max_ec); //recover old value
if (!empty($memory_limit))
    ini_set('memory_limit', $memory_limit);
?>