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;
/* display index page */
case 'main':
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);
}
$msg = "";
foreach ($rs as $value) {
$msg .= '';
}
//处理返回的格式
$data = array();
if (_isCustomerLogin()) {
$data["isCustomerLogin"] = "yes";
}
$data["msg"] = $msg;
$data["redirectTarget"] = $_SESSION['redirectTarget'];
common::echo_json_encode("200",$data);
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;
$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 = array($key, $value, $totalValue);
//处理返回原表数据格式
$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('$type'::text, 'eta'::text, 'etd'::text, $bdate, $edate, '$sqlWhere'::text, '$container_type'::text) "
. "r (c bigint, d text)");
error_log("select * from public.online_order_status_date_kpi('$type'::text, 'eta'::text, 'etd'::text, $bdate, $edate, '$sqlWhere'::text, '$container_type'::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']);
$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)");
}
if($type == 'ata_r3'){
$data = array("ETDList" =>$ETDList,"ETD_Radius"=>array('30%','50%'),"title1" =>"Arrival","title2" =>"(ATD-ETD)");
}
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'";
}
//Demo测试数据 日期全为空
$edate = "null";
$bdate = "null";
$list= array();
//all 查询所有
$sqlWhere = ' and ' . common::searchExtendHandNew("ocean", $_SESSION["ONLINE_USER"]);
$sqlWhere = common::check_input($sqlWhere);
$sqlWhere = " " . $sqlWhere;
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();
$total = 0;
$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;
$max_all = 0;
foreach($ContainerCounSeries as $k =>$v){
//这个type 是页面传过来的
$color = common::getItemStyle($type,$k);
$total = $total + $v['total'];
$ContainerCounSeries_return[] = array("name"=>$k,"type"=>"bar","emphasis" => array("focus" =>"none"),
"stack" =>"总计","data" =>$v['data'],"itemStyle" =>array("color" =>$color));
$max_all = $max_all + $v['max'];
}
//处理返回原表数据格式
$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);
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"] . "
'||'" . $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"] . "
'||'" . $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':
$serial_no = $_POST["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 from public.online_ocean oo 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)
, vv as (select split_part(split_part(bbb, '|', 2), ',', 1)::numeric as lng, split_part(split_part(bbb, '|', 2), ',', 2)::numeric as lat, 'Vessel: '||split_part(bbb, '|', 3) as infor, 5 as sort, null::timestamp without time zone as stime, 'vessel'::text as ptype from v1 where bbb is not null and bbb<>'')
, tt as (select split_part(location, ',', 2)::numeric as lng, split_part(location, ',', 1)::numeric as lat, (
case when type='26' then ctnr||': Pick up: '||utime||', '||(case when by_pickup=true then pickup_from_exp else consignee_exp end)
else ctnr||': Delivery: '||utime||', '||(case when by_pickup=true then consignee_exp else deliveryto_exp end)
end
) as infor, 6 as sort, upload_time as stime, case when type='26' then 'pickup'::text else 'delivery'::text end as ptype from t1 where location is not null and location<>'')
, rr as (
select lon as lng, lat as lat, 'Place of receipt: '||place_of_receipt_exp as infor, 0 as sort, null::timestamp without time zone as stime, 'por'::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=place_of_receipt_un
union all
select lon as lng, lat as lat, 'POL: '||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, 'POD: '||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, 'Place of delivery: '||place_of_delivery_exp 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=place_of_delivery_un
union all
select * from vv
union all
select * from tt
)
select * from rr order by sort, stime";
$rss = common::excuteListSql($sql);
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"]);
//默认初始条件：ALL& ETD& Current Month 当前月的1号到最后一天
if(isset($_POST['is_default']) && strtolower($_POST['is_default']) == "yes"){
$sqlWhere .= "and etd>='" . date("Y-m") . "-01' and etd<='" . date("Y-m") . "-01'::date + interval '1 month'";
}else{
$date_type = 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'";
}
$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 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
FROM public.online_ocean
$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) {
$Arrived = utils::outDisplayForMerge(common::getStatusDesc($value["last_status_315_code"]),common::_toString($value["last_status_loc"]),";");
$RecentStatusList[] = array("Title"=>$value['h_bol'],
"name"=>"MSC SHANVI III / HD433A",
"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"=>"Completed",
"Arrived"=>$Arrived,
"Time"=>$value["last_status_315_date"],
"a" =>common::deCode($value['serial_no'], 'E'));
}
$arrTmp = array('searchData' => $RecentStatusList,
'Management' => common::getManagement(),
'is_customer' =>_isCustomerLogin(),
'rc' => $rc,
'ps' => $ps,
'cp' => $cp,
'tp' => $tp);
} else {
$arrTmp = array('searchData' => array(),
'Management' => common::getManagement());
}
common::echo_json_encode(200,$arrTmp);
exit();
break;
case 'save_layout':
$management = json_encode($_POST["management"]);
common::excuteUpdateSql("update public.ra_online_user set management = '$management' where lower(user_login) = '" . strtolower(_getLoginName()) . "'");
$returnData = array("msg" => "success");
common::echo_json_encode(200, $returnData);
exit();
break;
case 'password':
tools::getInstance()->updatePassword();
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);
?>