k_online_backend/service/login.class.php

<?php

if (!defined('IN_ONLINE')) {
    exit('Access Denied');
}

/**
 * Description of login
 *
 * @author Administrator
 */
class login {

    private static $_login;

    public static function getInstance() {
        if (!self::$_login) {
            $c = __CLASS__;
            self::$_login = new $c;
        }
        return self::$_login;
    }

    private function getLoginSql($uname) {
        return "select user_login,(select active from public.employee ee where ee.employee_id=u.employee_id) as employee_id_active, can_visit_vgm,can_add_booking, can_add_tk_status,truck_driver,po_booking,o_final_delivery_u,ipad_view_po,can_view_doc,can_upload_doc,can_add_catalog,can_add_po,packing_list_company,is_only_vgm,contact_id_user,is_demo, ra_password as password,employee_id, contact_id, user_type, last_pwd_change, EXTRACT(DAY from (now() - last_pwd_change)) as last_pwd_change_date, 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, belong_schemas, main_schemas, error_login_count, EXTRACT(EPOCH FROM (now()-COALESCE(error_login_time, now()))) as second, po_status, view_air_file_format, 
        special_customer_event, can_edi_vgm, isf_aci_ams_station,login_version,is_kerry_shipment,can_visit_delivery,currency_group from public.ra_online_user u where lower(user_login) = '" . strtolower($uname) . "'";
    }

    public function do_login() {
        $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']);
            $is_verify = common::check_input($_POST['verifcation_code']);
            //首先校用户登录
            $AES_encrypted = $this->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();
            }

            $sql = $this->getLoginSql($uname);
            $rs = common::excuteObjectSql($sql);
            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 = "<a href='mailto:lilyyang@topocean.com.cn'>lilyyang@topocean.com.cn</a>";
                        }else{
                            $data = "<a href='mailto:winnie@topocean.com'>winnie@topocean.com</a>";
                        }
                    }
                    if (strtolower(Soure) =='apex'){
                        $data = "<a href='mailto:maria.wang@apexshipping.com.cn'>maria.wang@apexshipping.com.cn</a>";
                    }
                    
                    $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{
                    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(
                        'code' => 'first_login',
                        'login_version' => $rs["login_version"],
                        'data' => '',
                        'msg' => 'First login, please change your password'
                    );
                    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"];
                    }
                }

                $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);
                    }
                }

                if ($diffdate == $PASSWORD_CHANGE_CYCLE) {// Due today
                    $login_tmp = array(
                        'msg' => 'today',
                        'uname' => $uname,
                        '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,
                        '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,
                            '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(
                        '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($uname);
        $rs = common::excuteObjectSql($sql);
        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 = "<a href='mailto:lilyyang@topocean.com.cn'>lilyyang@topocean.com.cn</a>";
                    }else{
                        $data = "<a href='mailto:winnie@topocean.com'>winnie@topocean.com</a>";
                    }
                }
                if (strtolower(Soure) =='apex'){
                    $data = "<a href='mailto:maria.wang@apexshipping.com.cn'>maria.wang@apexshipping.com.cn</a>";
                }
                $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 = $this->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 from public.ra_online_user where lower(user_login) = '" . strtolower($login) . "' and lower(email) = '" . strtolower($email) . "'";
            $rs = common::excuteObjectSql($sql_p);
            if (!empty($rs)) {
                $r = utils::sendEmailByPassword($login, $rs['password'], $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($uname);
            $rs = common::excuteObjectSql($sql);
            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 = "<a href='mailto:lilyyang@topocean.com.cn'>lilyyang@topocean.com.cn</a>";
                        }else{
                            $data = "<a href='mailto:winnie@topocean.com'>winnie@topocean.com</a>";
                        }
                    }
                    if (strtolower(Soure) =='apex'){
                        $data = "<a href='mailto:maria.wang@apexshipping.com.cn'>maria.wang@apexshipping.com.cn</a>";
                    }
                    
                    $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){
        $result = array(
            'msg' => "passwordExpires",
            'uname' => $uname,
        );
        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']);
    
        //首先校验验证码 暂时注销掉
        // $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 lower(user_login) = '" . strtolower($loginName) . "'";
        $rs = common::excuteObjectSql($sql);
        $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();
        $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{
            $data =  $this->getTrackingInfo($reference_number);
            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'].")";
            }
            utils::single_operation_log_save("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 = $this->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($reference_number){
        $reference_number = strtolower($reference_number);
        $sql = "with o as(
            SELECT  o.* 
                from public.ocean o 
                where o.status::text <> 'Cancelled'::text AND o.bol_type::text <> 'BOOKING'::text AND o.bol_type::text <> 'QUOTE'::text AND o.iscts = true 
                    AND o.iscts IS NOT NULL and ARRAY['$reference_number'] && array_append(array[lower(tracking_no)::text,lower(booking_no)::text,lower(h_bol)::text,lower(m_bol)::text, lower(po_no),lower(quote_no)::text,lower(invoice_no)],'')
            union       
            SELECT  o.* 
                from public.ocean o 
                where o.status::text <> 'Cancelled'::text AND o.bol_type::text <> 'BOOKING'::text AND o.bol_type::text <> 'QUOTE'::text AND o.iscts = true 
                    AND o.iscts IS NOT NULL and exists (select 1 from oc_container oc where o.serial_no = oc.serial_no and oc.ctnr = '$reference_number')

        )
        SELECT m_eta as _m_eta,  h_bol as _h_bol, m_bol as _m_bol,job_no as _job_bol,
                o.* ,sh.*, cn.* ,aa.*,dd.*,fd.*,oo.*,
                (select uncode from ports where  code = o.port_of_transshipment) as port_of_transshipment_un
            from o 
            LEFT JOIN LATERAL ( SELECT tracking_no as _tracking_no,shippr_uncode,shipper_city,
                    consignee_uncode,consignee_city,incoterms,
                    fport_of_loading_un,
                    mport_of_discharge_un,
                    place_of_receipt_un,
                    place_of_delivery_un,
                    (select timezonecode from public.city_ports where ctrycitycode = oo.fport_of_loading_un limit 1) as pol_timezone,
                    (select uncity from public.ports where uncode = oo.fport_of_loading_un limit 1) as pol_uncity,
                    (select timezonecode from public.city_ports where ctrycitycode = oo.mport_of_discharge_un limit 1) as mpod_timezone,
                    (select uncity from public.ports where uncode = oo.mport_of_discharge_un limit 1) as mpod_uncity,
                    (select timezonecode from public.city_ports where ctrycitycode = oo.place_of_receipt_un limit 1) as por_timezone,
                    (select uncity from public.ports where uncode = oo.place_of_receipt_un limit 1) as por_uncity,
                    (select timezonecode from public.city_ports where ctrycitycode = oo.place_of_delivery_un limit 1) as pod_timezone,
                    (select uncity from public.ports where uncode = oo.place_of_delivery_un limit 1) as pod_uncity,
                    po_no as _po_no,
                    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_iffafd 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_iffafd is null) THEN 'Cargo Received'::text
                        WHEN (m_iffdep is not null and m_iffarr is null and m_iffafd is null) THEN 'Departure'::text
                        WHEN (m_iffarr is not null and m_iffafd is null) THEN 'Arrived'::text
                        WHEN (m_iffafd is not null) THEN 'Completed'::text
                        ELSE 'Created'::text
                    END AS new_status
                FROM  public.online_ocean oo WHERE oo.serial_no::text = o.serial_no::text) oo ON true
            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 ocean.contacts c WHERE o.consignee::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 ocean.contacts c WHERE o.shipper::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 timezonecode from public.city_ports where ctrycitycode = LEFT(country, 2) || COALESCE(city_code,'') limit 1) as aa_timezone
            FROM ocean.contacts c WHERE o.origin_station::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 timezonecode from public.city_ports where ctrycitycode = LEFT(country, 2) || COALESCE(city_code,'') limit 1) as dd_timezone
            FROM ocean.contacts c WHERE o.destination_station::text = c.contact_id::text) dd ON true
            LEFT JOIN LATERAL ( SELECT 
                city as fd_city,
                (select timezonecode from public.city_ports where ctrycitycode = LEFT(country, 2) || COALESCE(city_code,'') limit 1) as fd_timezone
            FROM ocean.contacts c WHERE o.final_desination::text = c.contact_id::text) fd ON true";
        $ocean_arr = common::excuteListSql($sql);

        if(empty($ocean_arr)){
            $sql = $this->trackingSfsSql($reference_number);
            $ocean_arr = common::excuteListSql($sql);
            if(empty($ocean_arr)){
                $data = array("msg" =>"No matches");
                return $data;
            }
        }
        if(!empty($ocean_arr) && count($ocean_arr) > 1){
            $data = array("msg" =>"Multiple results");
            return $data;
        }

        $ocean = $ocean_arr[0];
        //获取对应uncode 对应的时间
        $uncodes = $ocean['fport_of_loading_un'].";".$ocean['mport_of_discharge_un'];
        $codeinfo = common::getCityPortsInfo($uncodes);

        //处理transportInfo信息数据
        $transportInfo = array("Tracking No." =>$ocean['_tracking_no'],"status"=>$ocean['new_status'],"mode" => "Ocean Freight",
            "origin" =>$ocean['shippr_uncode'],"destination" =>$ocean['consignee_uncode'],
            "etd" =>$ocean['f_etd'],"atd" =>$ocean['atd'],
            "etd_timezone" =>$codeinfo[$ocean['fport_of_loading_un']],
            "atd_timezone" =>$codeinfo[$ocean['fport_of_loading_un']],
            "eta" =>$ocean['m_eta'],"ata" =>$ocean['ata'],
            "eta_timezone" =>$codeinfo[$ocean['mport_of_discharge_un']],
            "ata_timezone" =>$codeinfo[$ocean['mport_of_discharge_un']]);
        $data['transportInfo'] = $transportInfo;

        //处理basicInfo信息数据
        $vessel = utils::outDisplayForMerge($ocean['f_vessel'],$ocean['m_vessel']);
        $voyage = utils::outDisplayForMerge($ocean['f_voyage'],$ocean['m_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']);
        
        $sql = "SELECT " . column::getInstance()->getSearchSql('Ocean_Container') . " ,net_lbs 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");

        /* Container Status */
        //数据库里配置好
        //$sql = "SELECT " . column::getInstance()->getSearchSql('Ocean_Container_Status') . " from oc_container_v where lower(serial_no) = '" . strtolower($serial_no) . "'";
        $serial_no = $ocean["serial_no"];
        $ctnr_sql = "SELECT oc.ctnr, oc.serial_no,oc.size 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 timezonecode from public.city_ports where ctrycitycode = s.event_code) as timezone,
                    (select uncity from public.ports where uncode = s.event_code) as uncity,
                    case when event ='I'  then 'IFFREC'::text
                        when event ='AE'  then 'IFFONB'::text
                        when event ='VD'  then 'IFFDEP'::text
                        when event ='EB' or event ='VA' then 'IFFARR'::text
                        when event ='UV'  then 'IFFUND'::text
                        when event ='VA'  then 'IFFAFD'::text
                        when event ='AV'  then 'IFFCTA'::text
                        when event ='CT'  then 'IFFICC'::text
                        when event ='OA' or event ='D'  then 'IFFPPD'::text
                        when event ='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']);
            }
        }

        //Milestones 数据信息待定
        $Milestones = common::getMilestonesInfo($ocean,$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 trackingSfsSql($reference_number){
        $sql = "with o as(
            SELECT  o.* 
                from sfs.ocean o 
                where o.status::text <> 'Cancelled'::text AND o.bol_type::text <> 'BOOKING'::text AND o.bol_type::text <> 'QUOTE'::text AND o.iscts = true 
                    AND o.iscts IS NOT NULL and ARRAY['$reference_number'] && array_append(array[lower(tracking_no)::text,lower(booking_no)::text,lower(h_bol)::text,lower(m_bol)::text, lower(po_no),lower(quote_no)::text,lower(invoice_no)],'')
            union       
            SELECT  o.* 
                from sfs.ocean o 
                where o.status::text <> 'Cancelled'::text AND o.bol_type::text <> 'BOOKING'::text AND o.bol_type::text <> 'QUOTE'::text AND o.iscts = true 
                    AND o.iscts IS NOT NULL and exists (select 1 from oc_container oc where o.serial_no = oc.serial_no and oc.ctnr = '$reference_number')
        )
        SELECT m_eta as _m_eta,  h_bol as _h_bol, m_bol as _m_bol,job_no as _job_bol,
                o.* ,sh.*, cn.* ,aa.*,dd.*,fd.*,oo.*,
                (select uncode from ports where  code = o.port_of_transshipment) as port_of_transshipment_un
            from sfs.ocean o 
            LEFT JOIN LATERAL ( SELECT tracking_no as _tracking_no,shippr_uncode,shipper_city,
                    consignee_uncode,consignee_city,incoterms,
                    fport_of_loading_un,
                    mport_of_discharge_un,
                    place_of_receipt_un,
                    place_of_delivery_un,
                    (select timezonecode from public.city_ports where ctrycitycode = oo.fport_of_loading_un limit 1) as pol_timezone,
                    (select uncity from public.ports where uncode = oo.fport_of_loading_un limit 1) as pol_uncity,
                    (select timezonecode from public.city_ports where ctrycitycode = oo.mport_of_discharge_un limit 1) as mpod_timezone,
                    (select uncity from public.ports where uncode = oo.mport_of_discharge_un limit 1) as mpod_uncity,
                    (select timezonecode from public.city_ports where ctrycitycode = oo.place_of_receipt_un limit 1) as por_timezone,
                    (select uncity from public.ports where uncode = oo.place_of_receipt_un limit 1) as por_uncity,
                    (select timezonecode from public.city_ports where ctrycitycode = oo.place_of_delivery_un limit 1) as pod_timezone,
                    (select uncity from public.ports where uncode = oo.place_of_delivery_un limit 1) as pod_uncity,
                    po_no as _po_no,
                    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_iffafd 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_iffafd is null) THEN 'Cargo Received'::text
                        WHEN (m_iffdep is not null and m_iffarr is null and m_iffafd is null) THEN 'Departure'::text
                        WHEN (m_iffarr is not null and m_iffafd is null) THEN 'Arrived'::text
                        WHEN (m_iffafd is not null) THEN 'Completed'::text
                        ELSE 'Created'::text
                    END AS new_status
                FROM  public.online_ocean oo WHERE oo.serial_no::text = o.serial_no::text) oo ON true
            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 sfs.contacts c WHERE o.consignee::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 sfs.contacts c WHERE o.shipper::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 timezonecode from public.city_ports where ctrycitycode = LEFT(country, 2) || COALESCE(city_code,'') limit 1) as aa_timezone
            FROM sfs.contacts c WHERE o.origin_station::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 timezonecode from public.city_ports where ctrycitycode = LEFT(country, 2) || COALESCE(city_code,'') limit 1) as dd_timezone
            FROM sfs.contacts c WHERE o.destination_station::text = c.contact_id::text) dd ON true
            LEFT JOIN LATERAL ( SELECT 
                city as fd_city,
                (select timezonecode from public.city_ports where ctrycitycode = LEFT(country, 2) || COALESCE(city_code,'') limit 1) as fd_timezone
            FROM sfs.contacts c WHERE o.final_desination::text = c.contact_id::text) fd ON true";
        return $sql;
    }

    private function AES_encrypted($encrypted_string){
        $key = 'fT5!R1k$7Mv@4Q9X'; // 16 bytes key
        $iv = '1234567890123456'; // 16 bytes IV
        $decrypted = openssl_decrypt(base64_decode($encrypted_string), 'AES-128-CBC', $key, OPENSSL_RAW_DATA, $iv);
        return $decrypted;
    }
}

?>