= current_date - INTERVAL '3 months' limit 1"); //记录这次的密钥记录 common::excuteUpdateSql("INSERT INTO public.customer_service_secret_key(secret_key, create_time)VALUES ('$is_verify', now());"); if(!empty($AES_encrypted) && empty($secret_key)){ }else{ $data = array( 'msg' => 'verifcation_error', 'data' => '' ); common::echo_json_encode(400, $data); exit(); } } //检查长度,大于50,返回 no_exist common::checkUserNameLength($uname); $sql = $this->getLoginSql(); $rs = common::excuteObjectPrepareSql($sql,[md5(strtolower($uname))]); if (!empty($rs)) { if (empty($rs['belong_schemas'])) { $rs['belong_schemas'] = "public"; } if (empty($rs['main_schemas'])) { $rs['main_schemas'] = "public"; } //验证employee是否active if (!empty($rs["employee_id"]) && $rs["employee_id_active"] != "t") { if (strtolower(Soure) =='topocean'){ if (strtolower($rs['user_type']) == "employee" && utils::endWith($rs['email'], "cn")) { $data = "lilyyang@topocean.com.cn"; }else{ $data = "winnie@topocean.com"; } } if (strtolower(Soure) =='apex'){ $data = "maria.wang@apexshipping.com.cn"; } $data = array( 'code' => 'no_active', 'login_version' => $rs["login_version"], 'data' => $data, 'msg' => "Please check with Doc Center $data for searching function" ); common::echo_json_encode(500, $data); $this->failedLogin($uname, 'Employee not active'); exit(); } //处理登录状态 $userInfo = common::check_input($_COOKIE['userInfo']); $noCheckPwd = false; if (!empty($userInfo)) { $userInfoSplit = explode("_", $userInfo); if ($uname == $userInfoSplit[0]) { if ($userInfoSplit[1] == md5($rs['password'])) { $noCheckPwd = true; } } } if ($rs['error_login_count'] > $login_error_times && $rs['second'] < $lock_user_seconds) { $data = array( 'msg' => 'error_times', 'login_version' => $rs["login_version"], 'data' => ceil(($lock_user_seconds - $rs['second']) / 60) ); common::echo_json_encode(400, $data); $this->failedLogin($uname, 'Failed login too times'); exit(); } if ($rs['is_online'] != 't') { if (strtolower($rs['user_type']) != "employee") { $data =array( 'code' => 'no_online', 'login_version' => $rs["login_version"], 'data' => '', 'msg' => 'No activation or insufficient permissions' ); common::echo_json_encode(500, $data); $this->failedLogin($uname, 'Online is not active'); exit(); } } //if ($rs['decrypt_password'] != $_POST['psw']) { if ($noCheckPwd) { }else{ //如是是token登录，则不用验证密码 if(isset($_POST['token']) && !empty($_POST['token'])){ $is_verify = $_POST['token']; $AES_encrypted = utils::AES_encrypted($is_verify,true,"fT5!R1k$7Mv@4Q9X","1234567890123456"); $secret_key = common::excuteOneSql("select secret_key from customer_service_secret_key where secret_key = '$is_verify' and create_time >= current_date - INTERVAL '3 months' limit 1"); //记录这次的密钥记录 common::excuteUpdateSql("INSERT INTO public.customer_service_secret_key(secret_key, create_time)VALUES ('$is_verify', now());"); //密钥解析失败或者有重复的记录这提示登录失败 if(!(!empty($AES_encrypted) && empty($secret_key))){ $data = array( 'msg' => 'Invalid token', 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(400, $data); exit(); } }else{ if ($rs['password'] != $_POST['psw']) { common::excuteUpdateSql("update public.ra_online_user set error_login_count=error_login_count+1, error_login_time=now() where lower(user_login) = '" . strtolower($uname) . "'"); $data = array( 'msg' => 'password_error', 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(400, $data); $this->failedLogin($uname, 'Password is wrong'); exit(); } } } if ($rs['online_active'] != 't') { $data = array( 'code' => 'no_active', 'login_version' => $rs["login_version"], 'data' => '', 'msg' => 'Please check with Doc Center for searching function' ); common::echo_json_encode(500, $data); $this->failedLogin($uname, 'Online is not active'); exit(); } //check password length $tar = utils::checkPassword($rs['password']); if (!empty($tar)) { $data = array( 'code' => $tar, 'login_version' => $rs["login_version"], 'data' => '', 'msg' => $tar ); common::echo_json_encode(500, $data); exit(); } //第一次登录，改密码 if (empty($rs['last_pwd_change'])) { $data = array( 'login_version' => $rs["login_version"], 'data' => '', 'uname' =>$uname, 'user_info' => array("uname"=>$uname), 'msg' => 'First login, please change your password' ); common::echo_json_encode(400, $data); exit(); } //get more infor by employee_id or contact_id $sql = ''; $diffdate = $rs['last_pwd_change_date']; $user_type = $rs['user_type']; //if user is customer, check company if (strtolower($user_type) == 'customer') { $company = $rs['company_name']; } else { if (!empty($rs['station'])) $company = $rs['station']; } if (strtolower($uname) == 'ra.admin') { $company = 'Admin'; } // get system config $sql = "SELECT lower(ra_name) as ra_name, ra_value from ra_online_config where lower(ra_name) in ('employee_session_timeout', 'customer_session_timeout', 'password_change_alert', 'employee_password_change_cycle', 'customer_password_change_cycle')"; $rs1s = common::excuteListSql($sql); foreach ($rs1s as $rs1) { if ($rs1['ra_name'] == 'employee_session_timeout') $EMPLOYEE_SESSION_TIMEOUT = $rs1['ra_value']; if ($rs1['ra_name'] == 'customer_session_timeout') $CUSTOMER_SESSION_TIMEOUT = $rs1['ra_value']; if ($rs1['ra_name'] == 'password_change_alert') $PASSWORD_CHANGE_ALERT = $rs1['ra_value']; if ($rs1['ra_name'] == 'employee_password_change_cycle') $EMPLOYEE_PASSWORD_CHANGE_CYCLE = $rs1['ra_value']; if ($rs1['ra_name'] == 'customer_password_change_cycle') $CUSTOMER_PASSWORD_CHANGE_CYCLE = $rs1['ra_value']; } $sql="select item_value from config where item='passwordChangePeriod'"; $pcp = common::excuteObjectSql($sql); $passwordChangePeriod = json_decode($pcp["item_value"],true); if (strtolower($rs['user_type']) == 'employee') { $PASSWORD_CHANGE_CYCLE = $EMPLOYEE_PASSWORD_CHANGE_CYCLE; $SESSION_TIMEOUT = $EMPLOYEE_SESSION_TIMEOUT; //如果有新配置，则采用新配置 if (!empty($pcp)) { $PASSWORD_CHANGE_CYCLE = $passwordChangePeriod["Employee"]["days"]; $PASSWORD_CHANGE_ALERT = $passwordChangePeriod["Employee"]["advanceDays"]; } } else { $PASSWORD_CHANGE_CYCLE = $CUSTOMER_PASSWORD_CHANGE_CYCLE; $SESSION_TIMEOUT = $CUSTOMER_SESSION_TIMEOUT; //如果有新配置，则采用新配置 if (!empty($pcp)) { $PASSWORD_CHANGE_CYCLE = $passwordChangePeriod["Customer"]["days"]; $PASSWORD_CHANGE_ALERT = $passwordChangePeriod["Customer"]["advanceDays"]; } } $loginName = $rs['user_login']; $email = $rs['email']; //Timeout if ($diffdate > $PASSWORD_CHANGE_CYCLE) { if(empty($email)){ $data = array( 'status' => '0', 'code' => 'login user email is empty', 'login_version' => $rs["login_version"], 'msg' => 'login user email is empty' ); common::echo_json_encode(500, $data); exit(); }else{ $this -> passwordExpires($loginName,$email,$uname); } } //kln新版查询 date_format,numbers_format $kln_user = common::excuteObjectSql("select * from public.kln_user_extend where lower(user_login) = '".strtolower($uname)."'"); //检查用户是否是设置过subscribe_notification,加在这里，少一次请求 $count = common::excuteOneSql("select count(*) from public.notifications_rules where notifications_type = 'Subscribe' and lower(user_login) = '".strtolower($uname)."'"); $subscribe_notification_default_init = $count > 0 ? false:true; //添加FAQ客户的访问类型 $loginCount = common::excuteOneSql("select count(*) from public.ra_online_user_login_log where lower(user_name) = '".strtolower(common::check_input($uname))."' and date_time >= CURRENT_DATE - INTERVAL '30 day' AND date_time <= CURRENT_DATE"); $kln_user_info = array("uname"=>$rs['user_login'], "employee_email"=>$rs['employee_email'], "user_type"=>strtolower($rs['user_type']), "first_name"=>$rs['first_name'], "last_name"=>$rs['last_name'], "email"=>$rs['email'], "expire_day"=>$PASSWORD_CHANGE_CYCLE - $rs['last_pwd_change_date'], "PASSWORD_CHANGE_CYCLE"=>intval($PASSWORD_CHANGE_CYCLE), "subscribe_notification_default_init"=>$subscribe_notification_default_init, "last_pwd_change"=>$rs['last_pwd_change'], "date_format"=>$kln_user['date_format'], "numbers_format"=>$kln_user['numbers_format'], "loginCount"=>intval($loginCount)); //添加密码是否快过期的消息通知 7 天内，3天内的通知 $expire_day = ($PASSWORD_CHANGE_CYCLE - $rs['last_pwd_change_date']); if($expire_day <= 7){ $exist = common::excuteListSql("select notifiation_type from public.kln_notifiation_info where notifiation_type = 'Passwond_Notifcations' and lower(user_login) = '".strtolower($uname)."' and other_pnum = '".$expire_day."'"); if (empty($exist)){ $other_name = "Password Expiration in $expire_day Days"; $other_desc = "Your password will expire in $expire_day days. To ensure the security of your account, please change your password as soon as possible."; $message_sql ="INSERT INTO public.kln_notifiation_info(notifiation_type, other_name, other_desc, other_img, notifications_method, email_method, user_login, insert_date, readed_date, is_send_message, is_send_email, frequency_type, other_type, other_pnum) VALUES ('Passwond_Notifcations','".$other_name."','".$other_desc."','',true,false,'".$uname."',now(),null,null,null,'Instant','password','".$expire_day."');"; common::excuteUpdateSql($message_sql); } } if ($diffdate == $PASSWORD_CHANGE_CYCLE) {// Due today $login_tmp = array( 'msg' => 'today', "uname"=>$uname, 'user_info' => $kln_user_info, 'login_version' => $rs["login_version"], 'data' => '' ); } elseif ($diffdate >= ($PASSWORD_CHANGE_CYCLE - $PASSWORD_CHANGE_ALERT)) {// Password expires soon, JS Tips $login_tmp = array( 'msg' => 'last', "uname"=>$uname, 'user_info' => $kln_user_info, 'login_version' => $rs["login_version"], 'data' => $PASSWORD_CHANGE_CYCLE - $diffdate, 'is_only_vgm' => $rs["is_only_vgm"] ); } //insert into log table $ip = common::ip(); $sql = "insert into public.ra_online_user_login_log (user_name,manufacturer,from_app,ip,date_time, session_id) values ('" . common::check_input($uname) . "', '" . common::check_input($company) . "','Online','$ip',now(), '" . session_id() . "')"; if (common::excuteUpdateSql($sql)) { common::excuteUpdateSql("update public.ra_online_user set Last_Login_Time = now(), error_login_count=0, error_login_time=null where lower(user_login) = '" . strtolower($uname) . "'"); if (isset($login_tmp)) { common::echo_json_encode(200, $login_tmp); } else { $data = array( 'msg' => 'success', "uname"=>$uname, 'user_info' => $kln_user_info, 'login_version' => $rs["login_version"], 'data' => '', 'is_only_vgm' => $rs["is_only_vgm"] ); common::echo_json_encode(200, $data); } $online_user = $rs; $online_user['user_login'] = $uname; $online_user['company'] = $company; $online_user['password'] = ""; if (!_isAdmin()) { if ($rs["is_only_vgm"] == "t") {//VGM用户写死 $sql = "select array_to_string(ARRAY(select url_action from public.ra_online_permission where url_action in ('ocean_order','password','vgm') and menu_id in ('ship','profile') order by order_by asc), ',')"; $rrrs = common::excuteOneSql($sql); } else { $sql = "select array_to_string(ARRAY(select p.url_action from public.ra_online_user_permission up left join public.ra_online_user u on up.user_name = u.user_login left join public.ra_online_permission p on up.p_id = p.id where lower(u.user_login) = '" . common::check_input(strtolower($uname)) . "'), ',')"; $rrrs = common::excuteOneSql($sql); if (strtolower($rs['user_type']) == "employee" && empty($rrrs)) { $sql = "select array_to_string(ARRAY(select url_action from public.ra_online_permission where is_customer = true order by order_by asc), ',')"; $rrrs = common::excuteOneSql($sql); } } $online_user['permission'] = $rrrs; } if (!empty($online_user['docdownload'])) $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from ra_online_file_format where lower(serial_no) " . common::getInNotInSql($online_user['docdownload']) . " and active = true group by display_name order by min(id)"; else { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from ra_online_file_format where active = true"; if (strtolower($online_user['user_type']) == "customer") $sql .= " and client_display = true"; $sql .= " group by display_name order by min(id)"; } $online_user['view_doc_type'] = common::excuteListSql($sql); if (!empty($online_user['view_air_file_format'])) $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from air_file_format where lower(serial_no) " . common::getInNotInSql($online_user['view_air_file_format']) . " and active = true group by display_name order by min(id)"; else { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from air_file_format where active = true"; if (strtolower($online_user['user_type']) == "customer") $sql .= " and client_display = true"; $sql .= " group by display_name order by min(id)"; } $online_user['view_air_doc_type'] = common::excuteListSql($sql); //补充aci and ams CustomerLogin station - public $ocean_station_temp = $online_user['ocean_station']; $online_user['session_ocean_station'] = $this->getOriginOrAgent($ocean_station_temp); $ocean_agent_temp = $online_user['ocean_agent']; $online_user['session_ocean_agent'] = $this->getOriginOrAgent($ocean_agent_temp); $_SESSION['ONLINE_USER'] = $online_user; $_SESSION['LAST_OPERATE_TIME'] = time(); $_SESSION['SESSION_TIMEOUT'] = $SESSION_TIMEOUT; //判断是否记录密码 add if ($_POST['rememberpwd'] === 'true') { if (!$noCheckPwd) { $user_info = $uname . "_" . md5($rs['password']); setcookie('userInfo', $user_info, time() + 30 * 24 * 3600, "/"); } } else { setcookie('userInfo', '', time() - 1, "/"); } if ($rs['is_super'] == "t") { $schemas_list = common::excuteListSql("select * from schemas_list"); } else { $schemas_list = common::excuteListSql("select * from schemas_list where schemas_name=any(regexp_split_to_array('" . $rs['belong_schemas'] . "'::text, ';'::text))"); } ///if (count($schemas_list) > 1) { foreach ($schemas_list as $sk => $sv) { if ($sv['schemas_name'] == "public") { continue; } $ttdd = common::excuteObjectSql("select contact_id_user, employee_id, contact_id, user_type, email, user_webtype_id, active, is_online, station, allow_login_remote, can_see_amslog,can_view_eccn, can_see_isflog, can_see_isflog_withaddress, customer_search_type, customer_destination, can_add_ams, can_add_isf, air_station, air_sales, ocean_station, ocean_sales,ocean_following_sales,ocean_following_sales_or,air_following_sales,air_following_sales_or, trucking_station, ocean_dest_op, can_see_password, can_add_opsales_code, ocean_station_or, ocean_agent_or, ocean_sales_or, ocean_dest_op_or, air_station_or, air_sales_or, trucking_station_or, can_add_user, can_add_employee, can_add_contact, company_name, ams_email, isf_email, customer_discharge, online_active, is_super, ocean_agent,active, can_send_email, view_file_format as docdownload, container_status, consolidated_cbsa_code, can_add_aci, air_customers, air_customer_search_type,trucking_customers,trucking_customer_search_type, upload_document, view_file_format, event_type, po_status, view_air_file_format, special_customer_event, can_edi_vgm, isf_aci_ams_station, is_kerry_shipment from " . $sv['schemas_name'] . ".ra_online_user where lower(user_login) = '" . strtolower($uname) . "'"); if (empty($ttdd)) { unset($schemas_list[$sk]); continue; } if (!empty($ttdd['docdownload'])) { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from " . $sv['schemas_name'] . ".ra_online_file_format where lower(serial_no) " . common::getInNotInSql($ttdd['docdownload']) . " and active = true group by display_name order by min(id)"; } else { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from " . $sv['schemas_name'] . ".ra_online_file_format where active = true"; if (strtolower($ttdd['user_type']) == "customer") $sql .= " and client_display = true"; $sql .= " group by display_name order by min(id)"; } $ttdd['view_doc_type'] = common::excuteListSql($sql); if (!empty($ttdd['view_air_file_format'])) { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from " . $sv['schemas_name'] . ".air_file_format where lower(serial_no) " . common::getInNotInSql($ttdd['view_air_file_format']) . " and active = true group by display_name order by min(id)"; } else { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from " . $sv['schemas_name'] . ".air_file_format where active = true"; if (strtolower($ttdd['user_type']) == "customer") $sql .= " and client_display = true"; $sql .= " group by display_name order by min(id)"; } $ttdd['view_air_doc_type'] = common::excuteListSql($sql); //补充aci and ams CustomerLogin station - other like sfs $ocean_station_temp = $ttdd['ocean_station']; $ttdd['session_ocean_station'] = $this->getOriginOrAgent($ocean_station_temp); $ocean_agent_temp = $ttdd['ocean_agent']; $ttdd['session_ocean_agent'] = $this->getOriginOrAgent($ocean_agent_temp); $_SESSION[$sv['schemas_name'] . '_ONLINE_USER'] = $ttdd; } //} $_SESSION['schemas_list'] = $schemas_list; //不再返回登录页面，直接跳转 if (isset($_GET['up'])) { if($_GET['v'] == 'new'){ header("Location: main_new_version.php?action=main"); }else{ header("Location: main.php?action=main"); } } //处理登录成功后的记录保存 //$user_type,$user_name,$page,$operation,$operation_detail // $user_type = _isCustomerLogin() ? "Customer" : "Employee"; // $user_name = _getLoginName(); // $detail = 'System Account,Login successful'; // if(isset($_POST['token']) && !empty($_POST['token'])){ // $detail = 'From Apex Online,Login successful'; // } // utils::single_operation_log_save($user_type,$user_name,"Login","Login",$detail); exit(); } else { $data = array( 'code' => 'database_error', 'login_version' => $rs["login_version"], 'msg' => 'database_error' ); common::echo_json_encode(500, $data); exit(); } } else { $data = array( 'code' => 'no_exist', 'login_version' => $rs["login_version"], 'msg' => 'The username or password you entered is incorrect' ); common::echo_json_encode(500, $data); exit(); } } } public function check_uname(){ $uname = common::check_input($_POST['uname']); $sql = $this->getLoginSql(); $rs = common::excuteObjectPrepareSql($sql,[md5(strtolower($uname))]); if (!empty($rs)) { //只是验证用户是否存在，是否激活 //验证employee是否active if (!empty($rs["employee_id"]) && $rs["employee_id_active"] != "t") { if (strtolower(Soure) =='topocean'){ if (strtolower($rs['user_type']) == "employee" && utils::endWith($rs['email'], "cn")) { $data = "lilyyang@topocean.com.cn"; }else{ $data = "winnie@topocean.com"; } } if (strtolower(Soure) =='apex'){ $data = "maria.wang@apexshipping.com.cn"; } $data = array( 'msg' => 'no_active', ); common::echo_json_encode(200, $data); exit(); } if ($rs['is_online'] != 't') { if (strtolower($rs['user_type']) != "employee") { $data =array( 'msg' => 'no_online', ); common::echo_json_encode(200, $data); exit(); } } if ($rs['online_active'] != 't') { $data = array( 'msg' => 'no_active', ); common::echo_json_encode(200, $data); exit(); } //验证成功 $data = array( 'msg' => 'success', ); common::echo_json_encode(200, $data); exit(); } else { $data = array( 'msg' => 'no_exist', ); common::echo_json_encode(200, $data); exit(); } } public function verifcation_code(){ // 生成一个4位随机数作为验证码 //$random_num = mt_rand(1000, 9999); $random_num = $this->generateCaptcha(4); $_SESSION['captcha'] = $random_num; // 创建一个宽度为80像素、高度为30像素的图片 $width = 130; $height = 40; $image = imagecreate($width, $height); // 设置颜色 $white = imagecolorallocate($image, 255, 255, 255); // 白色作为背景 $black = imagecolorallocate($image, 0, 0, 0); // 黑色作为文字 // 填充背景 imagefilledrectangle($image, 0, 0, $width, $height, $white); // 在图片上绘制四个字符 $font_size = 18; $x = 34; $y = 12; for ($i = 0; $i < 4; $i++) { $char = substr($random_num, $i, 1); imagestring($image, $font_size, $x, $y, $char, $black); $x += $font_size+1; } // 返回Base64编码 ob_start(); imagepng($image); $image_data = ob_get_clean(); // 释放内存 imagedestroy($image); $data = array("imagePngBase64" =>base64_encode($image_data)); common::echo_json_encode(200, $data); exit(); } //邮件密码原文 public function forgot_password() { $login = common::check_input($_POST['login']); $email = common::check_input($_POST['email']); $is_verify = common::check_input($_POST['verifcation_code']); //首先校用户验证 $AES_encrypted = utils::AES_encrypted($is_verify); $secret_key = common::excuteOneSql("select secret_key from customer_service_secret_key where secret_key = '$is_verify' and create_time >= current_date - INTERVAL '3 months' limit 1"); //记录这次的密钥记录 common::excuteUpdateSql("INSERT INTO public.customer_service_secret_key(secret_key, create_time)VALUES ('$is_verify', now());"); if(!empty($AES_encrypted) && empty($secret_key)){ }else{ $data = array( 'msg' => 'verifcation_error', 'data' => '' ); common::echo_json_encode(400, $data); exit(); } $msg = ""; if (!empty($email) || !empty($login)) { $sql_p = "select user_login, ra_password as password,user_type, to_char(now(), 'Mon-DD-YYYY') as current_date,to_char(now(), 'Mon-DD-YYYY HH:mm:ss') as current_time from public.ra_online_user where md5(lower(user_login)) = ? and md5(lower(email)) = ?"; //$rs = common::excuteObjectSql($sql_p); $rs = common::excuteObjectPrepareSql($sql_p,[md5(strtolower($login)),md5(strtolower($email))]); if (!empty($rs)) { //$r = utils::sendEmailByPassword($login, $rs['password'], $email); $r = utils::sendEmailByResetPassword($rs, $email); if ($r == 'success') { $msg = "success"; } else { $msg = $r; } } else { $msg = "Can not find this user with give login id and email, please confirm!"; } } else { $msg = "Login Name or Email Required !"; } if($msg == 'success'){ $data = array( 'msg' => $msg, ); common::echo_json_encode(200, $data); }else{ $data = array( 'msg' => $msg, ); common::echo_json_encode(500, $data); } exit(); } public function generateCaptcha($length = 6) { $captcha = ''; $chars = "abcdefghijkmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789"; for ($i = 0; $i < $length; $i++) { // 随机选择字母或数字 $char = $chars[mt_rand(0, strlen($chars) - 1)]; $captcha .= strval($char); } return $captcha; } public function do_login_auto() { $uname = common::check_input($_GET['u']); $password = common::check_input($_GET['p']); $login_error_times = common::excuteOneSql("select ra_value from ra_online_config where ra_name='Login_Error_Times'"); $lock_user_seconds = common::excuteOneSql("select ra_value from ra_online_config where ra_name='Lock_User_Seconds'"); if (!empty($uname) || !empty($password)) { } else { $uname = common::check_input($_POST['uname']); $sql = $this->getLoginSql(); $rs = common::excuteObjectPrepareSql($sql,[md5(strtolower($uname))]); if (empty($rs['belong_schemas'])) { $rs['belong_schemas'] = "public"; } if (empty($rs['main_schemas'])) { $rs['main_schemas'] = "public"; } if (!empty($rs)) { //验证employee是否active if (!empty($rs["employee_id"]) && $rs["employee_id_active"] != "t") { if (strtolower(Soure) =='topocean'){ if (strtolower($rs['user_type']) == "employee" && utils::endWith($rs['email'], "cn")) { $data = "lilyyang@topocean.com.cn"; }else{ $data = "winnie@topocean.com"; } } if (strtolower(Soure) =='apex'){ $data = "maria.jj.wang@kln.com"; } $data = array( 'msg' => 'no_active', 'login_version' => $rs["login_version"], 'data' => $data ); common::echo_json_encode(500, $data); $this->failedLogin($uname, 'Employee not active'); exit(); } //add 处理登录状态 $userInfo = common::check_input($_COOKIE['userInfo']); $noCheckPwd = false; if (!empty($userInfo)) { $userInfoSplit = explode("_", $userInfo); if ($uname == $userInfoSplit[0]) { if ($userInfoSplit[1] == md5($rs['password'])) { $noCheckPwd = true; } } } //if (!$noCheckPwd) { if ($rs['error_login_count'] > $login_error_times && $rs['second'] < $lock_user_seconds) { $data = array( 'msg' => 'error_times', 'login_version' => $rs["login_version"], 'data' => ceil(($lock_user_seconds - $rs['second']) / 60) ); common::echo_json_encode(500, $data); $this->failedLogin($uname, 'Failed login too times'); exit(); } if ($rs['is_online'] != 't') { if (strtolower($rs['user_type']) != "employee") { $data =array( 'msg' => 'no_online', 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(500, $data); $this->failedLogin($uname, 'Online is not active'); exit(); } } //if ($rs['decrypt_password'] != $_POST['psw']) { if ($noCheckPwd) { }else{ if ($rs['password'] != $_POST['psw']) { common::excuteUpdateSql("update public.ra_online_user set error_login_count=error_login_count+1, error_login_time=now() where lower(user_login) = '" . strtolower($uname) . "'"); $data = array( 'msg' => 'password_error', 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(500, $data); $this->failedLogin($uname, 'Password is wrong'); exit(); } } if ($rs['online_active'] != 't') { $data = array( 'msg' => 'no_active', 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(500, $data); $this->failedLogin($uname, 'Online is not active'); exit(); } //check password length $tar = utils::checkPassword($rs['password']); if (!empty($tar)) { $data = array( 'msg' => $tar, 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(500, $data); exit(); } if (empty($rs['last_pwd_change'])) { $data = array( 'msg' => 'first_login', 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(500, $data); exit(); } //} //get more infor by employee_id or contact_id $sql = ''; $diffdate = $rs['last_pwd_change_date']; $user_type = $rs['user_type']; //if user is customer, check company if (strtolower($user_type) == 'customer') { $company = $rs['company_name']; } else { if (!empty($rs['station'])) $company = $rs['station']; } if (strtolower($uname) == 'ra.admin') { $company = 'Admin'; } // get system config $sql = "SELECT lower(ra_name) as ra_name, ra_value from ra_online_config where lower(ra_name) in ('employee_session_timeout', 'customer_session_timeout', 'password_change_alert', 'employee_password_change_cycle', 'customer_password_change_cycle')"; $rs1s = common::excuteListSql($sql); foreach ($rs1s as $rs1) { if ($rs1['ra_name'] == 'employee_session_timeout') $EMPLOYEE_SESSION_TIMEOUT = $rs1['ra_value']; if ($rs1['ra_name'] == 'customer_session_timeout') $CUSTOMER_SESSION_TIMEOUT = $rs1['ra_value']; if ($rs1['ra_name'] == 'password_change_alert') $PASSWORD_CHANGE_ALERT = $rs1['ra_value']; if ($rs1['ra_name'] == 'employee_password_change_cycle') $EMPLOYEE_PASSWORD_CHANGE_CYCLE = $rs1['ra_value']; if ($rs1['ra_name'] == 'customer_password_change_cycle') $CUSTOMER_PASSWORD_CHANGE_CYCLE = $rs1['ra_value']; } $sql="select item_value from config where item='passwordChangePeriod'"; $pcp = common::excuteObjectSql($sql); $passwordChangePeriod = json_decode($pcp["item_value"],true); if (strtolower($rs['user_type']) == 'employee') { $PASSWORD_CHANGE_CYCLE = $EMPLOYEE_PASSWORD_CHANGE_CYCLE; $SESSION_TIMEOUT = $EMPLOYEE_SESSION_TIMEOUT; //如果有新配置，则采用新配置 if (!empty($pcp)) { $PASSWORD_CHANGE_CYCLE = $passwordChangePeriod["Employee"]["days"]; $PASSWORD_CHANGE_ALERT = $passwordChangePeriod["Employee"]["advanceDays"]; } } else { $PASSWORD_CHANGE_CYCLE = $CUSTOMER_PASSWORD_CHANGE_CYCLE; $SESSION_TIMEOUT = $CUSTOMER_SESSION_TIMEOUT; //如果有新配置，则采用新配置 if (!empty($pcp)) { $PASSWORD_CHANGE_CYCLE = $passwordChangePeriod["Customer"]["days"]; $PASSWORD_CHANGE_ALERT = $passwordChangePeriod["Customer"]["advanceDays"]; } } /* if ($diffdate > $PASSWORD_CHANGE_CYCLE) {// Timeout echo json_encode(array( 'msg' => 'password_require_change', 'login_version' => $rs["login_version"], 'data' => '' )); $this->failedLogin($uname, 'Required password change'); exit(); }*/ $loginName = $rs['user_login']; $email = $rs['email']; if ($diffdate > $PASSWORD_CHANGE_CYCLE) {// Timeout if(empty($email)){ $data = array( 'status' => '0', 'msg' => 'login user email is empty', 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(500, $data); exit(); }else{ //$this -> passwordExpires($loginName,$email); } } if ($diffdate == $PASSWORD_CHANGE_CYCLE) {// Due today $login_tmp = array( 'msg' => 'today', 'login_version' => $rs["login_version"], 'data' => '' ); } elseif ($diffdate >= ($PASSWORD_CHANGE_CYCLE - $PASSWORD_CHANGE_ALERT)) {// Password expires soon, JS Tips $login_tmp = array( 'msg' => 'last', 'login_version' => $rs["login_version"], 'data' => $PASSWORD_CHANGE_CYCLE - $diffdate, 'is_only_vgm' => $rs["is_only_vgm"] ); } //insert into log table $ip = common::ip(); $sql = "insert into public.ra_online_user_login_log (user_name,manufacturer,from_app,ip,date_time, session_id) values ('" . common::check_input($uname) . "', '" . common::check_input($company) . "','Online','$ip',now(), '" . session_id() . "')"; if (common::excuteUpdateSql($sql)) { common::excuteUpdateSql("update public.ra_online_user set Last_Login_Time = now(), error_login_count=0, error_login_time=null where lower(user_login) = '" . strtolower($uname) . "'"); //自动登录。为了方便调用,先注销掉 if (isset($login_tmp)) { //common::echo_json_encode(500, $login_tmp); } else { $data = array( 'msg' => 'success', 'login_version' => $rs["login_version"], 'data' => '', 'is_only_vgm' => $rs["is_only_vgm"] ); //common::echo_json_encode("200", $data); } $online_user = $rs; $online_user['user_login'] = $uname; $online_user['company'] = $company; $online_user['password'] = ""; if (!_isAdmin()) { if ($rs["is_only_vgm"] == "t") {//VGM用户写死 $sql = "select array_to_string(ARRAY(select url_action from public.ra_online_permission where url_action in ('ocean_order','password','vgm') and menu_id in ('ship','profile') order by order_by asc), ',')"; $rrrs = common::excuteOneSql($sql); } else { $sql = "select array_to_string(ARRAY(select p.url_action from public.ra_online_user_permission up left join public.ra_online_user u on up.user_name = u.user_login left join public.ra_online_permission p on up.p_id = p.id where lower(u.user_login) = '" . common::check_input(strtolower($uname)) . "'), ',')"; $rrrs = common::excuteOneSql($sql); if (strtolower($rs['user_type']) == "employee" && empty($rrrs)) { $sql = "select array_to_string(ARRAY(select url_action from public.ra_online_permission where is_customer = true order by order_by asc), ',')"; $rrrs = common::excuteOneSql($sql); } } $online_user['permission'] = $rrrs; } if (!empty($online_user['docdownload'])) $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from ra_online_file_format where lower(serial_no) " . common::getInNotInSql($online_user['docdownload']) . " and active = true group by display_name order by min(id)"; else { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from ra_online_file_format where active = true"; if (strtolower($online_user['user_type']) == "customer") $sql .= " and client_display = true"; $sql .= " group by display_name order by min(id)"; } $online_user['view_doc_type'] = common::excuteListSql($sql); if (!empty($online_user['view_air_file_format'])) $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from air_file_format where lower(serial_no) " . common::getInNotInSql($online_user['view_air_file_format']) . " and active = true group by display_name order by min(id)"; else { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from air_file_format where active = true"; if (strtolower($online_user['user_type']) == "customer") $sql .= " and client_display = true"; $sql .= " group by display_name order by min(id)"; } $online_user['view_air_doc_type'] = common::excuteListSql($sql); //补充aci and ams CustomerLogin station - public $ocean_station_temp = $online_user['ocean_station']; $online_user['session_ocean_station'] = $this->getOriginOrAgent($ocean_station_temp); $ocean_agent_temp = $online_user['ocean_agent']; $online_user['session_ocean_agent'] = $this->getOriginOrAgent($ocean_agent_temp); $_SESSION['ONLINE_USER'] = $online_user; $_SESSION['LAST_OPERATE_TIME'] = time(); $_SESSION['SESSION_TIMEOUT'] = $SESSION_TIMEOUT; //判断是否记录密码 add if ($_POST['rememberpwd'] === 'true') { if (!$noCheckPwd) { $user_info = $uname . "_" . md5($rs['password']); setcookie('userInfo', $user_info, time() + 30 * 24 * 3600, "/"); } } else { setcookie('userInfo', '', time() - 1, "/"); } if ($rs['is_super'] == "t") { $schemas_list = common::excuteListSql("select * from schemas_list"); } else { $schemas_list = common::excuteListSql("select * from schemas_list where schemas_name=any(regexp_split_to_array('" . $rs['belong_schemas'] . "'::text, ';'::text))"); } ///if (count($schemas_list) > 1) { foreach ($schemas_list as $sk => $sv) { if ($sv['schemas_name'] == "public") { continue; } $ttdd = common::excuteObjectSql("select contact_id_user, employee_id, contact_id, user_type, email, user_webtype_id, active, is_online, station, allow_login_remote, can_see_amslog,can_view_eccn, can_see_isflog, can_see_isflog_withaddress, customer_search_type, customer_destination, can_add_ams, can_add_isf, air_station, air_sales, ocean_station, ocean_sales,ocean_following_sales,ocean_following_sales_or,air_following_sales,air_following_sales_or, trucking_station, ocean_dest_op, can_see_password, can_add_opsales_code, ocean_station_or, ocean_agent_or, ocean_sales_or, ocean_dest_op_or, air_station_or, air_sales_or, trucking_station_or, can_add_user, can_add_employee, can_add_contact, company_name, ams_email, isf_email, customer_discharge, online_active, is_super, ocean_agent,active, can_send_email, view_file_format as docdownload, container_status, consolidated_cbsa_code, can_add_aci, air_customers, air_customer_search_type,trucking_customers,trucking_customer_search_type, upload_document, view_file_format, event_type, po_status, view_air_file_format, special_customer_event, can_edi_vgm, isf_aci_ams_station, is_kerry_shipment from " . $sv['schemas_name'] . ".ra_online_user where lower(user_login) = '" . strtolower($uname) . "'"); if (empty($ttdd)) { unset($schemas_list[$sk]); continue; } if (!empty($ttdd['docdownload'])) { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from " . $sv['schemas_name'] . ".ra_online_file_format where lower(serial_no) " . common::getInNotInSql($ttdd['docdownload']) . " and active = true group by display_name order by min(id)"; } else { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from " . $sv['schemas_name'] . ".ra_online_file_format where active = true"; if (strtolower($ttdd['user_type']) == "customer") $sql .= " and client_display = true"; $sql .= " group by display_name order by min(id)"; } $ttdd['view_doc_type'] = common::excuteListSql($sql); if (!empty($ttdd['view_air_file_format'])) { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from " . $sv['schemas_name'] . ".air_file_format where lower(serial_no) " . common::getInNotInSql($ttdd['view_air_file_format']) . " and active = true group by display_name order by min(id)"; } else { $sql = "select string_agg(serial_no, ';') as serial_no, string_agg(m_h, ';') as m_h, display_name from " . $sv['schemas_name'] . ".air_file_format where active = true"; if (strtolower($ttdd['user_type']) == "customer") $sql .= " and client_display = true"; $sql .= " group by display_name order by min(id)"; } $ttdd['view_air_doc_type'] = common::excuteListSql($sql); //补充aci and ams CustomerLogin station - other like sfs $ocean_station_temp = $ttdd['ocean_station']; $ttdd['session_ocean_station'] = $this->getOriginOrAgent($ocean_station_temp); $ocean_agent_temp = $ttdd['ocean_agent']; $ttdd['session_ocean_agent'] = $this->getOriginOrAgent($ocean_agent_temp); $_SESSION[$sv['schemas_name'] . '_ONLINE_USER'] = $ttdd; } //} $_SESSION['schemas_list'] = $schemas_list; //不再返回登录页面，直接跳转 if (isset($_GET['up'])) { if($_GET['v'] == 'new'){ header("Location: main_new_version.php?action=main"); }else{ header("Location: main.php?action=main"); } } //自动登录。为了方便调用,先注销掉 //exit(); } else { $data = array( 'msg' => 'database_error', 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(500, $data); exit(); } } else { $data = array( 'msg' => 'no_exist', 'login_version' => $rs["login_version"], 'data' => '' ); common::echo_json_encode(500, $data); exit(); } } } private function failedLogin($uname, $company){ $ip = common::ip(); common::excuteUpdateSql("insert into public.ra_online_user_login_log (user_name,manufacturer,from_app,ip,date_time, session_id) values ('" . common::check_input($uname) . "', '" . common::check_input($company) . "','Online','$ip',now(), '" . session_id() . "')"); } //重置密码 public function passwordExpires($loginName,$email,$uname){ $kln_user_info = array("uname"=>$uname); $result = array( 'msg' => "passwordExpires", 'uname' => $uname, 'user_info' => $kln_user_info ); common::echo_json_encode(400, $result); exit(); } //submit change form -- change expires password public function update_pwd_expires(){ $loginName = common::check_input($_POST['uname']); $old_password = common::check_input($_POST['old_password']); $password = common::check_input($_POST['password']); if (empty($old_password) || empty($password)){ $data = array( 'msg' => 'Old password or New password is incorrect!', 'data' => '' ); common::echo_json_encode(500, $data); exit(); } //首先校验验证码 暂时注销掉 // $verifcation_code = ""; // $verifcation_code = common::check_input($_POST['verifcation_code']); // if (strtolower($_SESSION['captcha']) != strtolower($verifcation_code)) { // $data = array( // 'msg' => 'verifcation_error', // 'data' => '' // ); // common::echo_json_encode(400, $data); // exit(); // } $sql = "select ra_password as password from ra_online_user where md5(lower(user_login)) = ?"; $rs = common::excuteObjectPrepareSql($sql,[md5(strtolower($loginName))]); $str = ''; if (!empty($rs)) { if ($rs['password'] != $old_password) { $str = "Old password is incorrect!"; } } else { $str = "Old password is incorrect!"; } if(!empty($str)){ $data = array( 'msg' => $str, 'data' => '' ); common::echo_json_encode(500, $data); exit(); } //验证通过，进行修改密码 $msg = $this->updateExpirePassword($loginName, $password); if($msg == "success"){ $data = array( 'msg' => "success", 'data' => '' ); common::echo_json_encode(200, $data); exit(); } else { $data = array( 'msg' => $msg, 'data' => '' ); common::echo_json_encode(500, $data); exit(); } } //更新密码 public function updateExpirePassword($login,$new_password) { $str = common::checkPasswordRule($login, $new_password); //更新密码，擦除expire pwd痕迹 if (empty($str)) { $sql = "UPDATE public.ra_online_user SET ra_password = '" . common::check_input($new_password) . "',password_new=redant_encode('".$new_password."'), last_pwd_change = now(),password_expires_keycode = null,password_expires_time = null WHERE lower(user_login) = '" . common::check_input(strtolower($login)) . "';"; $rls = common::excuteUpdateSql($sql); if (!$rls) { $str = "Database Error, Try Later."; } else { $sql = "INSERT INTO public.ra_online_user_password_history (user_login, password, create_user, create_date) VALUES ('" . $login . "', " . common::check_input($new_password) . ", '" . $login . "', now());"; common::excuteUpdateSql($sql); $str = "success"; } } return $str; } public function getOriginOrAgent($ocean_station_temp){ $session_ocean_station = ""; if (strtolower($ocean_station_temp) == 'all'){ $session_ocean_station = $ocean_station_temp; } if (!(strtolower($ocean_station_temp) == 'all' || empty($ocean_station_temp))){ if (utils::checkExist($ocean_station_temp, ";")) { $ost = str_replace(";","','",strtolower($ocean_station_temp)); $sql="select kerry_station_id,contact_id from ocean.contacts where coalesce(kerry_station_id,'') <>''and lower(contact_id) in ('".$ost."')"; $tar = common::excuteListSql($sql); foreach ($tar as $tk => $tv) { $tar[$tv['contact_id']] = $tv['kerry_station_id']; } $_tt = explode(";", $ocean_station_temp); foreach ($_tt as $vv) { if (!empty($vv)){ $session_ocean_station .= trim($vv).';'; if (!empty($tar[$vv])) { $session_ocean_station .= $tar[trim($vv)].';'; } } } } else { $session_ocean_station .= trim($ocean_station_temp).';'; $temp_contacts = common::excuteObjectSql("select kerry_station_id from ocean.contacts where lower(contact_id)='".strtolower(common::check_input($ocean_station_temp))."'"); if (!empty($temp_contacts['kerry_station_id'])){ $session_ocean_station .= $temp_contacts['kerry_station_id'].';'; } } } return $session_ocean_station; } public function logout() { unset($_SESSION['ONLINE_USER']); unset($_SESSION['LAST_OPERATE_TIME']); unset($_SESSION['SESSION_TIMEOUT']); common::sessionDestroy(); session_write_close(); $data = array("msg" =>"logout Successful"); common::echo_json_encode(200, $data); exit(); } public function tracking_checked(){ $reference_number = common::check_input($_POST['reference_number']); $is_verify = common::check_input($_POST['verifcation_code']); if($this->signUpAndTrackingChecked($is_verify)){ $data = array("msg" =>"visit limit"); common::echo_json_encode(400, $data); exit(); }else{ $reference_number_lower = strtolower($reference_number); $checked = common::checkInputInval($reference_number_lower); if ($checked){ $online_ocean_sql = "select serial_no,order_from from public.kln_ocean where ((ARRAY['$reference_number_lower'] && array_append(ARRAY[lower(booking_no::text), lower(h_bol::text), lower(m_bol), lower(carrier_booking), lower(quote_no), lower(tracking_no)]||string_to_array(lower(ctnrs),','), ''::text)) or lower(po_no) like '%$reference_number_lower%' or lower(invoice_no) like '%$reference_number_lower%') and coalesce(schem_not_display, false)=false"; $online_ocean_arr = common::excuteListSql($online_ocean_sql); if(empty($online_ocean_arr)){ $data = array("msg" =>"No matches"); }elseif(!empty($online_ocean_arr) && utils::count($online_ocean_arr) > 1){ $data = array("msg" =>"Multiple results"); }else{ $data = $this->getTrackingInfo($online_ocean_arr[0]["serial_no"],$online_ocean_arr[0]["order_from"]); } } else { $data = array("msg" =>"No matches"); } common::echo_json_encode(200, $data); //记录查询log情况 $detail = ""; if($data['msg'] == "success"){ $detail = "Public tracking number:".$reference_number."; search successful"; } else { $detail = "Public tracking number:".$reference_number."; search fail(".$data['msg'].")"; } $user_name = common::ip(); utils::single_operation_log_save("Customer",$user_name,"Tracking","Public tracking",$detail); exit(); } } private function signUpAndTrackingChecked($is_verify){ $Tracking_Search_Count = common::excuteOneSql("select ra_value from ra_online_config where ra_name='Tracking_Search_Count'"); $ip = common::ip(); global $db; $db->StartTrans(); $db->Execute("update tracking_login_record set visit_count = 1, visit_time=now() " . " where ip = '$ip' and type ilike '".common::check_input($_POST['type'])."' and visit_time + '5 min' < NOW()::timestamp ") or ( (!$db->ErrorMsg()) or error_log($db->ErrorMsg(), 0)); $ipInfo = $db->GetRow("select ip, visit_count from tracking_login_record where ip = '$ip' and type ilike '".common::check_input($_POST['type'])."' and visit_time + '5 min' > NOW()::timestamp"); if(empty($ipInfo)){ $db->Execute("INSERT INTO public.tracking_login_record(ip, visit_count,visit_time,type)VALUES ('$ip', '1', now(),'".common::check_input($_POST['type'])."');") or ( (!$db->ErrorMsg()) or error_log($db->ErrorMsg(), 0)); }else{ if ($ipInfo['visit_count'] > $Tracking_Search_Count){ $AES_encrypted = utils::AES_encrypted($is_verify); $secret_key = common::excuteOneSql("select secret_key from customer_service_secret_key where secret_key = '$is_verify' and create_time >= current_date - INTERVAL '3 months' limit 1"); //记录这次的密钥记录 common::excuteUpdateSql("INSERT INTO public.customer_service_secret_key(secret_key, create_time)VALUES ('$is_verify', now());"); if(!empty($AES_encrypted) && empty($secret_key)){ //归零验证次数 $db->Execute("update tracking_login_record set visit_count = 1 " . " where ip = '$ip' and type ilike '".common::check_input($_POST['type'])."' and visit_time + '5 min' > NOW()::timestamp ") or ( (!$db->ErrorMsg()) or error_log($db->ErrorMsg(), 0)); }else{ return TRUE; } }else{ $db->Execute("update tracking_login_record set visit_count = visit_count::integer + 1 " . " where ip = '$ip' and type ilike '".common::check_input($_POST['type'])."' and visit_time + '5 min' > NOW()::timestamp ") or ( (!$db->ErrorMsg()) or error_log($db->ErrorMsg(), 0)); } } if ($db->CompleteTrans() === FALSE) { //出错拦截 return TRUE; } else { return FALSE; } } private function getTrackingInfo($serial_no,$order_from){ $sql = common::trackingSql($serial_no,$order_from); $ocean_arr = common::excuteListSql($sql); if(empty($ocean_arr)){ $data = array("msg" =>"No matches"); return $data; } if(!empty($ocean_arr) && utils::count($ocean_arr) > 1){ $data = array("msg" =>"Multiple results"); return $data; } $ocean = $ocean_arr[0]; //处理transportInfo信息数据 $transportInfo = array("Tracking No." =>$ocean['tracking_no'],"status"=>$ocean['new_status'], "mode" => $ocean['transport_mode'] == 'sea' ? "Ocean Freight" : ($ocean['transport_mode'] == 'air' ? "Air Freight": ""), "origin" =>$ocean['shippr_uncode'],"destination" =>$ocean['consignee_uncode'], "etd" =>$ocean['etd'],"atd" =>$ocean['atd'], "etd_timezone" =>$ocean['pol_timezone'], "atd_timezone" =>$ocean['pol_timezone'], "eta" =>$ocean['eta'],"ata" =>$ocean['ata'], "eta_timezone" =>$ocean['mpod_timezone'], "ata_timezone" =>$ocean['mpod_timezone']); $data['transportInfo'] = $transportInfo; //处理basicInfo信息数据 if($ocean['transport_mode'] == "sea"){ $vessel = utils::outDisplayForMerge($ocean['f_vessel'],$ocean['m_vessel']); $voyage = utils::outDisplayForMerge($ocean['f_voyage'],$ocean['m_voyage']); } elseif ($ocean['transport_mode'] == "air"){ $vessel = $ocean['vessel']; $voyage = $ocean['voyage']; } $basicInfo = array("MAWB/MBL No." =>$ocean['m_bol'],"HAWB/HBOL" => $ocean['h_bol'],"Carrier_Booking_No" =>$ocean['booking_no'], "PO_NO" =>$ocean['po_no'],"Vessel/Airline" =>$vessel,"Voyage/Filght" =>$voyage, "Incoterm" =>$ocean['incoterms'],"Service_Type" =>$ocean['service']); //处理 拼接地址 ocean表单exp 字段无法精准分割电话和地址信息，只能从contacts表里查询 $shipper_address = common::retStationInfo($ocean['sh_address_1'], $ocean['sh_address_2'], $ocean['sh_address_3'], $ocean['sh_address_4'], $ocean['sh_city'], $ocean['sh_state'], $ocean['sh_zipcode'], $ocean['sh_country']); $consignee_address = common::retStationInfo($ocean['cn_address_1'], $ocean['cn_address_2'], $ocean['cn_address_3'], $ocean['cn_address_4'], $ocean['cn_city'], $ocean['cn_state'], $ocean['cn_zipcode'], $ocean['cn_country']); $origin_address = common::retStationInfo($ocean['aa_address_1'], $ocean['aa_address_2'], $ocean['aa_address_3'], $ocean['aa_address_4'], $ocean['aa_city'], $ocean['aa_state'], $ocean['aa_zipcode'], $ocean['aa_country']); $destination_address = common::retStationInfo($ocean['dd_address_1'], $ocean['dd_address_2'], $ocean['dd_address_3'], $ocean['dd_address_4'], $ocean['dd_city'], $ocean['dd_state'], $ocean['dd_zipcode'], $ocean['dd_country']); $shipperPartners = array("company" =>$ocean['sh_company'],"address"=>$shipper_address,"phone"=>$ocean['sh_phone']); $consigneePartners = array("company" =>$ocean['cn_company'],"address"=>$consignee_address,"phone"=>$ocean['cn_phone']); $originPartners = array("company" =>$ocean['aa_company'],"address"=>$origin_address,"phone"=>$ocean['aa_phone']); $destinationPartners = array("company" =>$ocean['dd_company'],"address"=>$destination_address,"phone"=>$ocean['dd_phone']); $businessPartners = array("shipper"=>$shipperPartners,"consignee" => $consigneePartners,"origin" => $originPartners,"destination" => $destinationPartners); //处理marksAndDescription $marksAndDescription = array("marks"=>$ocean['marks'],"description"=>$ocean['description']); if($ocean['transport_mode'] == "sea"){ $sql = "SELECT " . column::getInstance()->getSearchSql('Ocean_Container') . " ,net_lbs from $order_from.oc_container where lower(serial_no) = '" . strtolower($ocean['serial_no']) . "'"; $rss = common::excuteListSql($sql); $quantity_unit = array(); $g_weight_tolal = 0; $ch_weight_tolal = 0; $ch_weight_tolal_grs_lbs = 0; $cbm_tolal = 0; foreach ($rss as $key => $rs) { $unit = $rs['unit']; if (array_key_exists($unit, $quantity_unit)) { $quantity_unit[$unit] = $quantity_unit[$unit] + $rs['qty']; } else { $quantity_unit[$unit] = $rs['qty']; } $g_weight_tolal += $rs['grs_kgs']; $ch_weight_tolal += $rs['net_lbs']; $ch_weight_tolal_grs_lbs += $rs['grs_lbs']; $cbm_tolal += $rs['cbm']; } $quantity_tolal = ""; foreach($quantity_unit as $uk => $uv){ $quantity_tolal.=$uv." ".$uk." "; } $ch_weight_tolal = empty($ch_weight_tolal) ? $ch_weight_tolal_grs_lbs : $ch_weight_tolal; //Packing 不确定信息 $packing = array("Quantity/Unit"=>$quantity_tolal,"G. Weight" => $g_weight_tolal." KGS","Ch. Weight" => $ch_weight_tolal." LBS","Volume" => $cbm_tolal." CBM"); } elseif ($ocean['transport_mode'] == "air"){ $quantity_tolal = empty($ocean['qty']) ? "" : $ocean['qty'].$ocean['qty_uom']; $g_weight_tolal = empty($ocean['piece_count']) ? "" : sprintf("%.3f", $ocean['piece_count'])." KGS"; $ch_weight_tolal = empty($ocean['piece_count']) ? "": sprintf("%.3f", $ocean['weight'])." KGS"; $cbm_tolal = empty($ocean['cbm']) ? "" : sprintf("%.4f", $ocean['cbm'])." CBM"; //Packing信息 $packing = array("Quantity/Unit"=>$quantity_tolal,"G. Weight" => $g_weight_tolal,"Ch. Weight" => $ch_weight_tolal,"Volume" => $cbm_tolal); } /* Container Status */ //sea 才有EDI315 if($ocean['transport_mode'] == "sea"){ $serial_no = $ocean["serial_no"]; $ctnr_sql = "SELECT oc.ctnr, oc.serial_no,oc.size FROM $order_from.oc_container oc LEFT JOIN ocean o ON oc.serial_no = o.serial_no WHERE o.serial_no='$serial_no'"; $ctnr_data = common::excuteListSql($ctnr_sql); foreach ($ctnr_data as $cd){ //存在柜号为空的数据情况 if(empty($cd['ctnr'])){ continue; } $ctnr_status_sql = "select s.source_id, s.event_base as event, to_char(to_timestamp(s.event_date, 'YYYYMMDD'), 'YYYY-MM-DD') as eventdate, to_char(to_timestamp(s.event_time, 'HH24MI'), 'HH24:MI') as eventtime, e.description,s.event_type as eventtype, s.event_code as eventcode, s.event_city as eventcity, (select time_zone from public.city_timezone where uncode = s.event_code) as timezone, s.event_city as uncity, case when s.event_base ='I' then 'IFFREC'::text when s.event_base ='AE' then 'IFFONB'::text when s.event_base ='VD' then 'IFFDEP'::text when s.event_base ='EB' or s.event_base ='VA' then 'IFFARR'::text when s.event_base ='UV' then 'IFFUND'::text when s.event_base ='VA' then 'IFFAFD'::text when s.event_base ='AV' then 'IFFCTA'::text when s.event_base ='CT' then 'IFFICC'::text when s.event_base ='OA' or s.event_base ='D' then 'IFFPPD'::text when s.event_base ='EE' then 'IFFECP'::text else '' ::text end as milestone_code from public.ra_online_container_status_v s left join ra_online_edi_event e on s.event_base = e.ra_name where s.serial_no = '" . pg_escape_string($cd['serial_no']) . "' and s.container_no = '" . pg_escape_string($cd['ctnr']) . "' and is_display = true order by to_timestamp(s.event_date, 'YYYYMMDD') asc, to_timestamp(s.event_time, 'HH24MI') asc,e.ra_order asc"; $ctnr_status = common::excuteListSql($ctnr_status_sql); //记录所有的信息 $EDI315TimeAndLocation = array(); foreach($ctnr_status as $event){ if(!empty($EDI315TimeAndLocation['IFFARR']) && $EDI315TimeAndLocation['IFFARR']['code'] == "EB"){ //如果存在EB 的EB 的优先级最高 continue; } if(!empty($EDI315TimeAndLocation['IFFPPD']) && $EDI315TimeAndLocation['IFFARR']['code'] == "OA"){ //如果存在OA 的OA 的优先级最高 continue; } $EDI315TimeAndLocation[$event['milestone_code']] = array("code"=>$event['event'],"timezone"=>$event['timezone'],"location"=>$event['uncity']); } } }else if ($ocean['transport_mode'] == "air"){ $EDI315TimeAndLocation = array(); } //Milestones 数据信息待定 $Milestones = common::getMilestonesInfo($ocean,$ocean['transport_mode'],$order_from,$EDI315TimeAndLocation); global $_COPYRIGHT; $data = array('transportInfo' => $transportInfo, 'basicInfo' => $basicInfo, 'businessPartners' => $businessPartners, 'packing' => $packing, 'marksAndDescription' => $marksAndDescription, 'Milestones' => $Milestones, 'copyright' =>$_COPYRIGHT); return array("msg" =>"success","data" =>$data); } private function trackingSql($serial_no,$order_from){ $_schemas = $order_from; if($_schemas == 'public'){ $_schemas = "ocean"; } $sql = "with o as( SELECT oo.*,m_bol as _m_bol, h_bol as _h_bol, (select time_zone from public.city_timezone where uncode = oo.fport_of_loading_un limit 1) as pol_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.fport_of_loading_un limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.fport_of_loading_un limit 1) end as pol_uncity, (select time_zone from public.city_timezone where uncode = oo.mport_of_discharge_un limit 1) as mpod_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.mport_of_discharge_un limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.mport_of_discharge_un limit 1) end as mpod_uncity, (select time_zone from public.city_timezone where uncode = oo.place_of_receipt_un limit 1) as por_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.place_of_receipt_un limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.place_of_receipt_un limit 1) end as por_uncity, (select time_zone from public.city_timezone where uncode = oo.place_of_delivery_un limit 1) as pod_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.place_of_delivery_un limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.place_of_delivery_un limit 1) end as pod_uncity, (select time_zone from public.city_timezone where uncode = oo.final_desination_uncode limit 1) as _fd_timezone, case when oo.transport_mode ='sea' then (select uncity from $order_from.ports where uncode = oo.final_desination_uncode limit 1) else (select city from sfs.airport where coalesce(airport.country_abb,'')||airport.airport_code = oo.final_desination_uncode limit 1) end as _pd_uncity, 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 'Departure'::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 oo where oo.serial_no = '" . $serial_no . "' and oo.order_from = '$order_from' ) SELECT o.* ,sh.*, cn.*,aa.*,dd.*,fd.* from o LEFT JOIN LATERAL ( SELECT company as cn_company, address_1 as cn_address_1, address_2 as cn_address_2, address_3 as cn_address_3, address_4 as cn_address_4, city as cn_city, state as cn_state, zipcode as cn_zipcode, country as cn_country, phone_1 as cn_phone FROM $_schemas.contacts c WHERE o.consignee_id::text = c.contact_id::text) cn ON true LEFT JOIN LATERAL ( SELECT company as sh_company, address_1 as sh_address_1, address_2 as sh_address_2, address_3 as sh_address_3, address_4 as sh_address_4, city as sh_city, state as sh_state, zipcode as sh_zipcode, country as sh_country, phone_1 as sh_phone FROM $_schemas.contacts c WHERE o.shipper_id::text = c.contact_id::text) sh ON true LEFT JOIN LATERAL ( SELECT company as aa_company, address_1 as aa_address_1, address_2 as aa_address_2, address_3 as aa_address_3, address_4 as aa_address_4, city as aa_city, state as aa_state, zipcode as aa_zipcode, country as aa_country, phone_1 as aa_phone, (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'') limit 1) as aa_timezone FROM $_schemas.contacts c WHERE o.origin::text = c.contact_id::text) aa ON true LEFT JOIN LATERAL ( SELECT company as dd_company, address_1 as dd_address_1, address_2 as dd_address_2, address_3 as dd_address_3, address_4 as dd_address_4, city as dd_city, state as dd_state, zipcode as dd_zipcode, country as dd_country, phone_1 as dd_phone, (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'') limit 1) as dd_timezone FROM $_schemas.contacts c WHERE o.agent::text = c.contact_id::text) dd ON true LEFT JOIN LATERAL ( SELECT city as fd_city, (select time_zone from public.city_timezone where uncode = LEFT(c.country, 2) || COALESCE(c.city_code,'') limit 1) as fd_timezone FROM $_schemas.contacts c WHERE o.final_desination::text = c.contact_id::text) fd ON true"; return $sql; } public function resetAndActivateUpdate(){ $verifcation_code = $_REQUEST['verifcation_code']; $AES_encrypted = utils::AES_encrypted($verifcation_code,true,"USAIandy20244Q9X","0123456123456789"); //七天内有效 $secret_key = common::excuteOneSql("select secret_key from customer_service_secret_key where secret_key = '$verifcation_code' and create_time >= current_date - INTERVAL '7 days' limit 1"); if(!empty($AES_encrypted) && !empty($secret_key)){ //使用后，移除之前数据库里安全密 common::excuteUpdateSql("delete from customer_service_secret_key where secret_key = '$verifcation_code'"); //提交的时候再次验证通过，进行修改密码 $loginName = $AES_encrypted; $password = $_REQUEST['password']; $msg = $this->updateExpirePassword($loginName, $password); if($msg == "success"){ $data = array( 'msg' => "success", 'data' => '' ); common::echo_json_encode(200, $data); exit(); } else { $data = array( 'msg' => $msg, 'data' => '' ); common::echo_json_encode(500, $data); exit(); } } else { $data = array( 'msg' => 'verifcation_Invalid', 'data' => '' ); } common::echo_json_encode(500, $data); exit(); } } ?>