= 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");
$AES_encrypted = utils::AES_encrypted($is_verify,true,"USAIandy20244Q9X","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'],
//"employee_email"=>"andy.wu@united-cn.net",
"user_type"=>strtolower($rs['user_type']),
"first_name"=>$rs['first_name'],
"last_name"=>$rs['last_name'],
"is_desensitization_kln"=>$rs['is_desensitization_kln'],
"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 HH24:MI: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,agent,from_station 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){
//當同一個hawb有超過一條數據時，選擇destination office和from station(job的創建站點）一致的shipment進行展示
$is_job_flag = true;
foreach($online_ocean_arr as $arr){
if ($arr['agent'] == $arr['from_station']){
$data = $this->getTrackingInfo($arr["serial_no"],$arr["order_from"]);
$is_job_flag = false;
break;
}
}
if ($is_job_flag) {
$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_extend'] == 'sea' ? "Ocean Freight" :
($ocean['transport_mode_extend'] == 'air' ? "Air Freight":
($ocean['transport_mode_extend'] == 'rail' ? "Rail Freight":
($ocean['transport_mode_extend'] == 'road' ? "Road 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)){
//提交的时候再次验证通过，进行修改密码
$loginName = $AES_encrypted;
$password = $_REQUEST['password'];
$msg = $this->updateExpirePassword($loginName, $password);
if($msg == "success"){
//使用后，移除之前数据库里安全密
common::excuteUpdateSql("delete from customer_service_secret_key where secret_key = '$verifcation_code'");
$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();
}
}
?>