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';

//为了调用，临时做一个登录动作
//  $_POST['uname'] = "ra.admin";
//  $_POST['psw'] = "abc123456789";
//  login::getInstance()->do_login_auto();
//验证路由权限
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':
        include ONLINE_ROOT . 'libs' . DS . 'ams_config.ini.php';
        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':
        $menuList = array();
        $menuList[] = array("index"=>'1',"label"=>"Dashboard","icon"=>"icon_data_fill_b","path"=>"/dashboard");
        $menuList[] = array("index"=>'3',"label"=>"Booking","icon"=>"icon_booking__fill_b","path"=>"/booking");
        $menuList[] = array("index"=>'4',"label"=>"Tracking","icon"=>"icon_tracking__fill_b","path"=>"/tracking");
        if(_isApexLogin()){
            $menuList[] = array("index"=>'6',"label"=>"System Management","icon"=>"icon_system__management_fill_b","type"=>"list",
                "children"=>array(array("index" =>'5-4',"label" =>"Operation Log","path"=>"/Operationlog")));
        }
        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':
        $type = $_REQUEST["r_type"];
        $container_type = $_REQUEST["container_type"];
        if (empty($_REQUEST["b_date"])) {
            $bdate = "null";
        } else {
            $bdate = $_REQUEST["b_date"];
            $bdate = "'$bdate'";
        }
        if (empty($_REQUEST["e_date"])) {
            $edate = "null";
        } else {
            $edate = $_REQUEST["e_date"];
            $edate = "'$edate'";
        }
        //Demo测试数据 日期全为空 :前端是由日期过来来的
        //$edate = "null";
        //$bdate = "null";

        $sqlWhere = ' and ' . common::searchExtendHandNew("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere = common::check_input($sqlWhere);
        $sqlWhere = " " . $sqlWhere;
        //online_order_status_date_new 与本地的区分，发布上去这去掉new
        $list = common::excuteListSql("select * from public.online_order_status_date('$type'::text, 'eta'::text, 'etd'::text, $bdate, $edate, '$sqlWhere'::text, '$container_type'::text) "
                        . "r (c bigint, e integer, b integer, d text)");               
        $key = "[";
        $value = "[";
        $totalValue = 0;
        foreach ($list as $vv) {
            if ($key != "[") {
                $key .= ",";
                $value .= ",";
            }
            if ($type == "r2" || $type == "r3" || $type == "r3_1" || $type == "r4" || $type == "r4_1") {
                $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 .= "]";
        //处理返回原表数据格式
        $data = common::mian_repot_do($value,$type,$totalValue);
        common::echo_json_encode(200, $data);
        exit();
        break;
    case 'main_report_kpi':
        $type = $_REQUEST["r_type"];
        $container_type = $_REQUEST["container_type"];
        if (empty($_REQUEST["b_date"])) {
            $bdate = "null";
        } else {
            $bdate = $_REQUEST["b_date"];
            $bdate = "'$bdate'";
        }
        if (empty($_REQUEST["e_date"])) {
            $edate = "null";
        } else {
            $edate = $_REQUEST["e_date"];
            $edate = "'$edate'";
        }
        //Demo测试数据 日期全为空 
        //$edate = "null";
        //$bdate = "null";

        $sqlWhere = ' and ' . common::searchExtendHandNew("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere = common::check_input($sqlWhere);
        $sqlWhere = " " . $sqlWhere;

        $list = common::excuteListSql("select * from public.online_order_status_date_kpi_new('$type'::text,'$sqlWhere'::text) "
                        . "r (c bigint, d text)");                
        $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));
        }
        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 要ALL  20  40  45 四份数据同时查询，且是柱状图，所以与r3 r4查询分开
        $container_type_arr = array("20","40","45");
        $container_type_param = $_REQUEST["container_type"];
        $ContainerCounSeries = array();

        foreach($container_type_arr as $_container_type){
            $container_type = $_container_type;
            $type = $_REQUEST["r_type"];
            if (empty($_REQUEST["b_date"])) {
                $bdate = "null";
            } else {
                $bdate = $_REQUEST["b_date"];
                $bdate = "'$bdate'";
            }
            if (empty($_REQUEST["e_date"])) {
                $edate = "null";
            } else {
                $edate = $_REQUEST["e_date"];
                $edate = "'$edate'";
            }
            $list= array();
            //all 查询所有
            $sqlWhere = ' and ' . common::searchExtendHandNew("ocean", $_SESSION["ONLINE_USER"]);
            $sqlWhere = common::check_input($sqlWhere);
            $sqlWhere = " " . $sqlWhere;
            //如果页面查询的是ALL 这个20  40  45都查询出来。否则值查询都对应的size,其他的不查询
            if(strtolower($container_type_param) == "all"){
                $list = common::excuteListSql("select * from public.online_order_status_date('$type'::text, 'eta'::text, 'etd'::text, $bdate, $edate, '$sqlWhere'::text, '$container_type'::text) "
                                . "r (c bigint, e integer, b integer, d text)");               
            }else{
                if($container_type_param == $_container_type){
                    $list = common::excuteListSql("select * from public.online_order_status_date('$type'::text, 'eta'::text, 'etd'::text, $bdate, $edate, '$sqlWhere'::text, '$container_type'::text) "
                                . "r (c bigint, e integer, b integer, d text)");
                }
            }
            
            $key = "[";
            $value = "[";
            $totalValue = 0;
            foreach ($list as $vv) {
                if ($key != "[") {
                    $key .= ",";
                    $value .= ",";
                }
                if ($type == "r2" || $type == "r3" || $type == "r3_1" || $type == "r4" || $type == "r4_1") {
                    $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 .= "]";
            $key_arr = json_decode($key,true);
            $value_arr = json_decode($value,true);

            $data = array();
            //每一个size 的所有月份总计
            $total = 0;
            //每一个size 对应的最大值
            $max = 0;
            foreach($value_arr as $arr){
                $data[] = intval($arr['value']);
                $total = $total + intval($arr['value']);
                $max = $max < intval($arr['value']) ? intval($arr['value']) : $max;
            }
            $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::getTopBar();
        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':
        //修改添加port_of_transshipment 和port_of_transshipment_name的坐标获取(有为空的可能)
        $serial_no = $_REQUEST["serial_no"];
        $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.online_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  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
                )
                , v1 as (select public.get_vessel_position(sn::text) bbb from ss)
                , t1 as (
                    select tl.upload_time, tl.by_pickup, split_part(g.consignee_exp,'\n',1) as consignee_exp, split_part(g.pickup_from_exp,'\n',1) as pickup_from_exp,
                            c.ctnr, split_part(g.deliveryto_exp,'\n',1) as 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=(select sn from ss)) order by tl.order_id)
                , 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 lon<>0 and lat<>0 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 lon<>0 and lat<>0 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 lon<>0 and lat<>0 and lon is not null and lat is not null and uncode=port_of_transshipment_un
                    )
            select * from rr order by sort, stime";
        $rss = common::excuteListSql($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);
        common::echo_json_encode(200, $rss);
        exit();
        break;
    case 'main_welcome':
        $cp = common::check_input($_POST ['cp']); //current_page
        $ps = common::check_input($_POST ['ps']); //ps
        if (empty($ps))
            $ps = 10;

        $sqlWhere = ' where ' . common::searchExtendHandNew("ocean", $_SESSION["ONLINE_USER"]);
        // 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.online_ocean" . $sqlWhere ." and last_status_315_update_time is not null";
            $rc = common::excuteOneSql($sql);
            //error_log($sql);
        }
        $tp = ceil($rc / $ps);
        if ($rc > 0) {
            $sql = "SELECT order_from as _schemas,serial_no,last_status_city, consignee, shipper, h_bol, 
                    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, ctnrs, count_cntr, last_status_ctnr,booking_no,
                    to_char(last_status_315_date, 'MM/DD/YYYY'::text) as last_status_315_date,last_status_315_code, last_status_loc, last_status_city, order_from,
                    f_vessel,f_voyage,
                    CASE
	    	            WHEN dd.status is null THEN 'Created'::text
	    	            ELSE dd.status::text
		            END AS new_status,
                    dd.act_date,dd.act_time,dd.description,
                    dd.timezone,dd.code as dd_code
                    FROM public.online_ocean
                    left join LATERAL (select case when a.code='IFFBCF' then 'Created'
                                    when a.code='IFFCPU' then 'Cargo Received'
                                    when a.code='IFFREC' then 'Cargo Received'
                                    when a.code='IFFDEP' then 'Departure'
                                    when a.code='IFFARR' then 'Arrived'
                                    when a.code='IFFAFD' then 'Completed'
                                    else 'Created' END as status,
                                    act_date,act_time,
                                    _dd.description as description,
                                    timezone,code
                            from  ocean_milestone a 
                                left join LATERAL (select sno,description  from  milestone where code=a.code and project_no = '*'  order by sno limit 1) _dd on true
                    where a.serial_no=online_ocean.serial_no 
                        and a.code in (select regexp_split_to_table('IFFBCF,IFFCPU,IFFREC,IFFDEP,IFFARR,IFFAFD', ','))
                        and a.act_date is not null
                    order by id desc limit 1) dd on true 
                    $sqlWhere  and last_status_315_update_time is not null";
            if (_isDemo()) {
                $sql .= " ORDER BY eta DESC limit " . $ps . " offset " . ($cp - 1) * $ps;
            } else {
                $sql .= " ORDER BY last_status_315_update_time DESC limit " . $ps . " offset " . ($cp - 1) * $ps;
            }
            $rss = common::excuteListSql($sql);
            //RecentStatusList
            $RecentStatusList = array();
            foreach ($rss as $key => $value) {
                $_schemas = $value['_schemas'];
                if($_schemas == "public"){
                    $_schemas = "ocean";
                }
                $timezone = "";
                if($value['dd_code'] == "IFFBCF" || $value['dd_code'] == "IFFCPU"){
                    $sql = "SELECT (select time_zone from public.city_timezone where uncode = LEFT(country, 2) || COALESCE(city_code,'')) as timezone
                         FROM $_schemas.contacts c WHERE  c.contact_id = '".$value['origin']."'";
                    $timezone = common::excuteOneSql($sql);     
                }
                if($value['dd_code'] == "IFFREC" || $value['dd_code'] == "IFFDEP"){
                    $sql = "select time_zone from public.city_timezone where uncode = '".$value['place_of_receipt_un']."'";
                    $timezone = common::excuteOneSql($sql);
                    if(empty($timezone)){
                        $sql = "select time_zone from public.city_timezone where uncode = '".$value['fport_of_loading_un']."'";
                        $timezone = common::excuteOneSql($sql);
                    }     
                }
                if($value['dd_code'] == "IFFARR"){
                    $sql = "select time_zone from public.city_timezone where uncode = '".$value['mport_of_discharge_un']."'";
                    $timezone = common::excuteOneSql($sql);
                }

                if($value['dd_code'] == "IFFAFD"){
                    $sql = "select time_zone from public.city_timezone where uncode = '".$value['place_of_delivery_un']."'";
                    $timezone = common::excuteOneSql($sql);
                }

                $Arrived = $value['description'];
                $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"=>$value['fport_of_loading_un'],
                    "endStation"=>$value['mport_of_discharge_un'],
                    "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"],
                    "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(!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;        
    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);
?>