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() {
        return "select first_name,last_name,user_login,user_id,
        redant_decode(temp_password) as temp_password,(temp_password_expires > LOCALTIMESTAMP) as is_temp_password_expires,
        employee_company_name,employee_contact_id_user,employee_search_type,employee_air_company_name,employee_air_contact_id_user,employee_air_search_type,
        (select active from public.employee ee where ee.employee_id=u.employee_id) as employee_id_active,
        (select email from public.employee ee where ee.employee_id=u.employee_id) as employee_email,
         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,revenue_active,is_desensitization_kln,customer_destination_country from public.ra_online_user u where md5(lower(user_login)) = ?";
    }

    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']);
            //如是是token登录，则不用验证密码和verifcation_code
            if(!(isset($_POST['token']))){
                $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();
                }
            }


            //检查长度,大于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 = "<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{
                    //如是是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'] && ($rs['temp_password'] != $_POST['psw'] || $rs['is_temp_password_expires'] == 'f')) {   
                        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");
                //查询该用户下的mapping
                $kam_customers_name = common::excuteListSql("select 
                        (select user_login from public.ra_online_user u where u.user_id = m.responsible_customers_id) as kam_customers_name 
                    from public.ra_online_user_kam_mapping m where m.kam_account_id = '".$rs['user_id']."'");
                $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']),
                                    //"user_type"=>strtolower('customer'),
                                    "first_name"=>$rs['first_name'],
                                    "last_name"=>$rs['last_name'],
                                    "is_desensitization_kln"=>$rs['is_desensitization_kln'],
                                    "email"=>$rs['email'],
                                    "kam_customers_name"=>$kam_customers_name,
                                    "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  jsonb_agg(to_jsonb(ra_online_file_format)) as file_data,
                                        string_agg(standard, ';') as standard,
                                        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  jsonb_agg(to_jsonb(ra_online_file_format)) as file_data,
                                        string_agg(standard, ';') as standard,
                                        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  jsonb_agg(to_jsonb(air_file_format)) as file_data,
                                        string_agg(standard, ';') as standard,
                                        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  jsonb_agg(to_jsonb(air_file_format)) as file_data,
                                        string_agg(standard, ';') as standard,
                                        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,
                        can_visit_delivery,currency_group,revenue_active,is_desensitization_kln,customer_destination_country 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  jsonb_agg(to_jsonb(ra_online_file_format)) as file_data, 
                                            string_agg(standard, ';') as standard,
                                            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  jsonb_agg(to_jsonb(ra_online_file_format)) as file_data,
                                            string_agg(standard, ';') as standard,
                                            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  jsonb_agg(to_jsonb(air_file_format)) as file_data,
                                            string_agg(standard, ';') as standard,
                                            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  jsonb_agg(to_jsonb(air_file_format)) as file_data,
                                            string_agg(standard, ';') as standard,
                                            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 = "<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 = 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($is_demo = '') {
        $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']);
            //检查长度,大于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 = "<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(4002, $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(4002, $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'] && ($rs['temp_password'] != $_POST['psw'] || $rs['is_temp_password_expires'] == 'f')) {   
                        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(4003, $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(4002, $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"];
                    }
                }

                //Timeout
                if ($diffdate > $PASSWORD_CHANGE_CYCLE) {
                    //自动登录这里移除这个邮箱为空的检查
                    $kln_user_info = array("uname"=>$uname);
                    $result = array(
                        'msg' => "passwordExpires",
                        'uname' => $uname,
                        'user_info' => $kln_user_info
                    );
                    common::echo_json_encode(4001, $result);
                    exit();
                }

                //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");
                
                //查询该用户下的mapping
                // $kam_customers_name = common::excuteListSql("select 
                //         (select user_login from public.ra_online_user u where u.user_id = m.responsible_customers_id) as kam_customers_name 
                //     from public.ra_online_user_kam_mapping m where m.kam_account_id = '".$rs['user_id']."'");
                $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'],
                                    //"kam_customers_name"=>$kam_customers_name,
                                    "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)) {
                        if($is_demo <> 'test'){
                            //这个自动登录这里,允许登录的.返回200.不再是500
                            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"]
                        );
                        if($is_demo <> 'test'){
                            common::echo_json_encode(200, $data);
                        }
                    }

                    $online_user = $rs;
                    $online_user['user_login'] = $uname;
                    $online_user['company'] = $company;
                    $online_user['password'] = "";
                    if (!($rs['is_super'] == 't')) {
                        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(standard, ';') as standard,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(standard, ';') as standard,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(standard, ';') as standard,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(standard, ';') as standard,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_start();
                    //重新登录后，SESSION先注销，在创建
                    $_SESSION = [];

                    $_SESSION['ONLINE_USER'] = $online_user;
                    $_SESSION['LAST_OPERATE_TIME'] = time();
                    $_SESSION['SESSION_TIMEOUT'] = $SESSION_TIMEOUT;
                    
                    //判断是否记录密码
                    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,
                        can_visit_delivery,currency_group,revenue_active,is_desensitization_kln,customer_destination_country 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(standard, ';') as standard,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(standard, ';') as standard,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(standard, ';') as standard,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(standard, ';') as standard, 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");
                        }
                    }
                    //自动登录。为了方便调用,先注销掉
                    if($is_demo <> 'test'){
                        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();
            }
        }
    }

    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('tracking',$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 {
            $msg = 'verifcation_Invalid';
            if(empty($AES_encrypted)){
                $msg="Invalid link. Please use the original link from your activation email.";
            } else {
                $secret_key_exist = common::excuteOneSql("select secret_key from customer_service_secret_key where secret_key = '$verifcation_code' limit 1");
                if(!empty($secret_key_exist)){
                    $msg="Link expired (valid for 7 days).  Use \"Forgot Password\" on the login page to reset and activate.";
                } else {
                    $msg="Account already activated.  Please log in with your existing password.";
                }
            }
            $data = array(
                'msg' => $msg,
                'data' => ''
            );
        }
        common::echo_json_encode(500, $data);
        exit();
    }
}

?>