k_online_backend/utils/utils.class.php

<?php

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

/**
 * Description of utilsclass
 *
 * @author Administrator
 */
class utils {

    public static function checkPassword($password,$rule="",$user_login="") {
        $str ="";
        if (!empty($rule)) {
            //是否校验大小写
            if (!empty($rule["hasOneUpperChar"])&&$rule["hasOneUpperChar"]) {
                if (!preg_match('/[A-Z]/',$password)) {
                    $str ="Password must contain uppercase letters";
                }
            }
            //是否校验小写
            if (!empty($rule["hasOneLowerChar"])&&$rule["hasOneLowerChar"]) {
                if (!preg_match('/[a-z]/',$password)) {
                    $str ="Password must contain lowercase letters";
                }
            }
            //是否存在数字
            if (!empty($rule["hasOneNumberChar"])&&$rule["hasOneNumberChar"]) {
                if (!preg_match('/[0-9]/',$password)) {
                    $str ="Password must contain numbers";
                }
            }
            $sql = "select user_type from ra_online_user_roles_rel where upper(user_login)=upper('".$user_login."') and exists(select count(0) from ra_online_user where upper(user_login)=upper('".$user_login."') and is_desktop=true) order by id desc limit 1;";
            $user_type = common::excuteOneSql($sql);
            if (!empty($user_type)&&$user_type=="Super User") {
                if (strlen($password)<$rule["SuperMinLen"]||strlen($password)>$rule["SuperMaxLen"]) {
                    $str ="Super user password length between ".$rule["SuperMinLen"]." and ".$rule["SuperMaxLen"];
                }
            }else{
                //校验密码长度
                if (strlen($password)<$rule["MinLen"]||strlen($password)>$rule["MaxLen"]) {
                    $str ="Password length between ".$rule["MinLen"]." and  ".$rule["MaxLen"];
                }
            }
            return $str;
        }else{
            if (preg_match('/^\d*$/', $password) || preg_match('/^[a-zA-Z]+$/', $password)) {
                $str ="Must include letters and numbers";
            }
            $len = strlen($password);
            $t = substr($password, 0, 1);
            for ($i = 1; $i < $len; $i++) {
                $t1 = substr($password, $i, 1);
                if ($t != $t1) {
                    return "";
                }
            }
            return "error";
        }
    }

    //隐藏邮箱地址
    public static function maskEmail($email) {
        $idex = strlen($email) - strrpos($email, ".");
        $mask = substr($email, 0, 1) . str_repeat('*', 6) . "@" . str_repeat('*', 3) . substr($email, -$idex);
        return $mask;
    }

    public static function getInSql($str, $not = false, $sep = ";") {
        $str = trim($str);
        $str = trim($str, $sep);
        $str = trim($str);
        if (empty($str) && $str !== "0" && $str !== 0)
            return "1<>1";
        $str = strtolower($str);
        if (utils::checkExist($str, $sep)) {
            $aa = explode($sep, $str);
            $msg = "";
            foreach ($aa as $value) {
                $value = trim($value);
                if (empty($value))
                    continue;
                if (empty($msg))
                    $msg = "'" . common::check_input($value) . "'";
                else
                    $msg .= ",'" . common::check_input($value) . "'";
            }
            if ($not !== FALSE)
                return " not in (" . $msg . ")";
            else
                return " in (" . $msg . ")";
        } else {
            if ($not !== FALSE)
                return " != '" . common::check_input(trim($str)) . "'";
            else
                return " = '" . common::check_input(trim($str)) . "'";
        }
    }

    public static function checkExist($string, $search, $u = TRUE) {
        if ($u === TRUE) {
            if (stripos($string, $search) !== false)
                return TRUE;
        }else {
            if (strpos($string, $search) !== false)
                return TRUE;
        }
        return FALSE;
    }

    public static function endWith($string, $end, $u = TRUE) {
        if ($u === TRUE) {
            $string = strtolower($string);
            $end = strtolower($end);
            return strrchr($string, $end) == $end;
        }
        return strrchr($string, $end) == $end;
    }

    public static function _get($str) {
        $rs = isset($_POST[$str]) ? $_POST[$str] : null;
        if (empty($rs))
            $rs = isset($_GET[$str]) ? $_GET[$str] : null;
        return $rs;
    }

    public static function startWith($string, $start, $u = TRUE) {
        if ($u === TRUE)
            return stripos($string, $start) === 0;
        return strpos($string, $start) === 0;
    }

    public static function outDisplay($content, $is_time = 'f', $is_first = 'f', $is_boolean = 'f', $excel_export = FALSE) {
        if (empty($content) && $content !== 0 && $content !== "0")
            return "";
        if (strtolower($is_time) == 't')
            return utils::dealTimeDisplay($content);
        if (strtolower($is_first) == 't') {
            if ($excel_export !== FALSE)
                return utils::getCompanyName($content);
            else
                return '<span title="' . $content . '">' . utils::getCompanyName($content) . '</span>';
        }
        if (strtolower($is_boolean) == 't')
            return utils::outTrue($content);
        return nl2br($content);
    }

    public static function _output($value) {
        if (empty($value))
            return "&nbsp;";
        else
            return $value;
    }

    public static function dealTimeDisplay($date) {
        if (empty($date))
            return "";
        if (strlen($date) > 10)
            return date("m/d/Y H:i:s", strtotime($date));
        return date("m/d/Y", strtotime($date));
    }

    public static function outDisplayForMerge($frist,$last,$split = "/") {
        if($frist == $last){
            return $frist;
        }
        if (!empty($frist)){
            if(!empty($last)){
                return $frist.$split.$last;
            }else{
                return $frist;
            }
        }else{
            return $last;
        }
    }

    public static function outTrue($r) {
        if (empty($r))
            return "No";
        $r = strtolower($r);
        if ($r == "t")
            return "Yes";
        elseif ($r == "f")
            return "No";
        else
            return $r;
    }

    public static function getCompanyName($detail) {
        $detail = nl2br($detail);
        if (strpos($detail, '<br />') === FALSE)
            return $detail;
        return substr($detail, 0, strpos($detail, '<br />'));
    }

    public static function getEmail($serial_no) {
        $ocean = common::excuteObjectSql("select sales_rep, last_user, created_by, order_from, h_bol, consignee, dest_op, agent from public.kln_ocean where md5(serial_no)=md5('$serial_no') "
                        . "order by schem_not_display nulls last limit 1");
        $schema = $ocean["order_from"] . ".";
        $dest_op_from_agent = common::excuteOneSql("select dest_op_from_agent from " . $schema . "ocean where md5(serial_no)=md5('$serial_no')");
        if ($ocean["agent"] == "KYMTL" || $ocean["agent"] == "KYYYZ") {
            $email = array();
            $email["email"] = "";
            if (!empty($dest_op_from_agent)) {
                $so_email = common::excuteOneSql("select email from " . $schema . "employee where employee_id='" . $dest_op_from_agent . "' and active=true");
            }
            if (!empty($so_email)) {
                if (empty($email["email"])) {
                    $email["email"] = $so_email;
                } else {
                    $email["email"] .= ";" . $so_email;
                }
            }
            if (!empty($ocean["sales_rep"])) {
                $rep_email = common::excuteOneSql("select email from " . $schema . "employee where lower(salesopcode)='" . strtolower($ocean["sales_rep"]) . "' and active=true");
                if (!empty($rep_email)) {
                    if (empty($email["email"])) {
                        $email["email"] = $rep_email;
                    } else {
                        $email["email"] .= ";" . $rep_email;
                    }
                }
            }
        } else {
            $email = common::excuteObjectSql("select string_agg(e.email, ';') as email, string_agg(e.first_name, ';') as name from " . $schema . "ra_online_user u, " . $schema . "employee e WHERE u.employee_id = e.employee_id and "
                            . "lower(u.user_login) in ('" . strtolower($ocean["created_by"]) . "', '" . strtolower($ocean["last_user"]) . "')");
            if (empty($dest_op_from_agent)) {
                if (!empty($ocean["dest_op"])) {
                    $so_email = common::excuteOneSql("select email from " . $schema . "employee where employee_id='" . $ocean["dest_op"] . "' and active=true");
                }
            } else {
                $so_email = common::excuteOneSql("select email from " . $schema . "employee where employee_id='" . $dest_op_from_agent . "' and active=true");
            }
            if (empty($so_email)) {
                if ($ocean["agent"] == "APEXSFO") {
                    $so_email = "oid2@apexshipping.com";
                }
                if ($ocean["agent"] == "APEXLAX") {
                    $so_email = "laxoid@apexshipping.com";
                }
                if ($ocean["agent"] == "APEXNYC") {
                    $so_email = "NYCOID@APEXSHIPPING.COM";
                }
                if ($ocean["agent"] == "APEXPNW") {
                    $so_email = "pnwoid@apexshipping.com";
                }
                if ($ocean["agent"] == "STLUTA") {
                    $so_email = "starlinkOID@apexshipping.com ";
                }
                if ($ocean["agent"] == "APEXORD") {
                    $so_email = "ordoid@apexshipping.com";
                }
            }
            if (!empty($so_email)) {
                if (empty($email["email"])) {
                    $email["email"] = $so_email;
                } else {
                    $email["email"] .= ";" . $so_email;
                }
            }
            if (!empty($ocean["sales_rep"])) {
                $rep_email = common::excuteOneSql("select email from " . $schema . "employee where lower(salesopcode)='" . strtolower($ocean["sales_rep"]) . "' and active=true");
                if (!empty($rep_email)) {
                    if (empty($email["email"])) {
                        $email["email"] = $rep_email;
                    } else {
                        $email["email"] .= ";" . $rep_email;
                    }
                }
            }
        }
        $email["h_bol"] = $ocean["h_bol"];
        $email["consignee"] = $ocean["consignee"];
        return $email;
    }

    /***
     * 过滤json中的某个数据
     * @param unknown $json
     * @param unknown $search
     * @param unknown $replace
     * @return mixed
     */
    public static function jsonFiltration($search,$replace,$json){
        //处理json中将斜杠转义问题
        $json = str_replace("\\/", "/", $json);
        return str_replace($search, $replace, $json);
    }

    /*
     * calculate eta destination by etd port
    */
    public static function calculate_ETA_Des($serial_no) {
        $sql = "SELECT m_eta as eat, mport_of_discharge as poul, place_of_delivery as pod,service from ocean where lower(serial_no) = '" . strtolower($serial_no) . "'";
        $rs = common::excuteObjectSql($sql);
        $date = "";
        if (!empty($rs['eat'])) {
            $date = utils::calculate_ETA_Dest($rs['eat'], $rs['poul'], $rs['pod'], $rs['service']);
        }
        return $date;
    }

    public static function calculate_ETA_Dest($eta, $poul, $pod, $service) {
        if (empty($poul) || empty($pod))
            return $eta;
        $sql = "SELECT door_days, cy_days
               FROM eta_dest
              WHERE eta_dest.state::text = ((( SELECT unlocode.state
                       FROM ports, unlocode
                      WHERE ports.uncode::text = unlocode.uncode::text AND ports.code::text = '" . common::check_input($pod) . "'
                     LIMIT 1))::text) AND (','::text || eta_dest.pod::text) ~~* (('%,'::text || '" . common::check_input($poul) . "') || '%'::text)
             LIMIT 1";
        //$sql = "select door_days, cy_days from eta_dest where state = (select state from ports where code = '" . common::check_input($poul) . "' limit 1) and ','||pod ilike '%," . common::check_input($pod) . "%'";
        $rs = common::excuteObjectSql($sql);
        if (empty($rs))
            return $eta;
        if (utils::endWith($service, "cy"))
            return common::addDays($eta, $rs['cy_days']);
        else
            return common::addDays($eta, $rs['door_days']);
    }

    /*
     * password change, email alert
    */
    public static function sendEmailByPassword($username, $password, $email, $companyname='') {
        $sql = "select subject, ra_content as content from ra_online_email_tpl where lower(ra_type) = 'forgotpw'";
        $rs = common::excuteObjectSql($sql);
        if (!empty($rs)) {
            $subject = $rs['subject'];
            $content = $rs['content'];
        }
        if (!empty($subject) && !empty($content)) {
            $content = str_replace('<{username}>', $username, $content);
            $content = str_replace('<{password}>', $password, $content);
            $content = str_replace('<{companyname}>', $companyname, $content);

            global $db;
            common::excuteUpdateSql("INSERT INTO public.email_record_forgotpassword(type, title, from_email, to_email, content, insert_date, 
            cc_email) VALUES ('forgot_password', '" . common::check_input($subject) . "', 'US.KApex.Online@kerryapex.com', '" .
                    common::check_input($email) . "', '" . common::check_input($content) . "', now(), '');");
            return "success";
            //return Mail::sendMail($email, $subject, $content);
        } else
            return null;
    }

    public static function operation_log_records(){
        //排除opreation_log操作
        if( empty($_REQUEST["operate"])
            || ($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "verifcation_code")
            || ($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "check_uname")
            || ($_REQUEST["action"] == "ocean_order" && $_REQUEST["operate"] == "setting_ocean_order_display")
            || ($_REQUEST["action"] == "ocean_booking" && $_REQUEST["operate"] == "setting_display")){
            return;
        }
        if($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "tracking_checked"){
            //public tracking_checked 的user name 记录对应IP 地址
            $user_type = "Customer";
            $user_name = common::ip();
        } elseif($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "do_login"){
            //移除do_login 因为在登录的过程中，是没有用户信息的
            $user_name = $_REQUEST["uname"];
        } else{
            $user_name = _getLoginName();
        }

        $user_type = _isApexLogin() ? "Employee" : "Customer";
        //如果在没有登录前，没有登录信息，指定用户
        if(!isset($_SESSION['ONLINE_USER'])){
            $user_type = common::excuteOneSql("select user_type from public.ra_online_user u where lower(user_login) = '" . strtolower($user_name) . "'");
        }
        
        $operateInfo = utils::getPageByAction($_REQUEST["action"],$_REQUEST["operate"]);
        $page = $operateInfo["page"];
        $operation = $operateInfo["operate"];
        $operation_detail = utils::analyzeOperationDetail($_REQUEST["action"],$_REQUEST["operate"]);
        if(empty($operation_detail)){
            $operation_detail = common::check_input(utils::jsonFiltration("null", "\"\"", json_encode($_REQUEST)));
        }
        //过滤一分钟以内，相同用户的重复请求
        $exist_sql = "select count(1) from public.customer_service_operation_log 
            where user_name = '$user_name' 
                and page = '$page' and operation = '$operation' and operation_detail = '$operation_detail' 
                and operation_time > NOW() - INTERVAL '1 minute' limit 1;";
        $exist_obj = common::excuteOneSql($exist_sql); 
        if(empty($exist_obj)){     
            $sql = "INSERT INTO public.customer_service_operation_log(user_type, user_name, page, operation, operation_detail, 
                operation_time) 
                VALUES ('$user_type', '$user_name', '$page', '$operation', '$operation_detail', now())";
            common::excuteUpdateSql($sql);
        }
    }

    public static function getPageByAction($action,$operate){
        //取消
        $operationConvertName = array(
            "login=do_login" => array("page" =>"Login","operate"=>"Login"),
            "login=forgot_password" => array("page" =>"Login","operate"=>"Forgot_PPassword"),
            "login=logout" => array("page" =>"logout","operate"=>"logout"),
            "login=update_pwd_expires" => array("page" =>"Login","operate"=>"Reset password"),
            "ocean_booking=search" => array("page" =>"Booking","operate"=>"Search"),
            "Booking_Search=save_setting_display" => array("page" =>"Booking","operate"=>"Customize Coulumns"),
            "booking=autody" => array("page" =>"Booking","operate"=>"More Filter"),
            "booking=autoport" => array("page" =>"Booking","operate"=>"More Filter"),
            "ocean_booking=detail" => array("page" =>"Booking","operate"=>"Open Detailed Page"),
            "ocean_booking=excel" => array("page" =>"Booking","operate"=>"Download"),
            "ocean_booking=save_communication" => array("page" =>"Booking","operate"=>"Send Email"),
            "opreation_log=search" => array("page" =>"Opreation_log","operate"=>"Search"),

            "login=tracking_checked" => array("page" =>"Tracking","operate"=>"Public tracking"),
            "ocean_order=search" => array("page" =>"Tracking","operate"=>"Search"),
            "Ocean_Search=save_setting_display" => array("page" =>"Tracking","operate"=>"Customize Coulumns"),
            "tracking=autody" => array("page" =>"Tracking","operate"=>"More Filter"),
            "tracking=autoport" => array("page" =>"Tracking","operate"=>"More Filter"),
            "ocean_order=detail" => array("page" =>"Tracking","operate"=>"Open Detailed Page"),
            "ocean_order=excel" => array("page" =>"Tracking","operate"=>"Download"),
            "ocean_order=download" => array("page" =>"Tracking","operate"=>"Download"),
            "ocean_order=save_communication" => array("page" =>"Booking","operate"=>"Send Email"),
            "ocean_order=ams_isf_log" => array("page" =>"Tracking","operate"=>"AMS/ISF"),
            "ocean_order=ocean_vgm" => array("page" =>"Tracking","operate"=>"Enter VGM"),
            "ocean_order=save_ocean_vgm" => array("page" =>"Tracking","operate"=>"Save VGM"),
            "ocean_order=share_shipment" => array("page" =>"Tracking","operate"=>"Share shipment"),
            "ocean_order=document_upload" => array("page" =>"Tracking","operate"=>"document_upload"),
            "ocean_order=document_upload_do" => array("page" =>"Tracking","operate"=>"document_upload_do"),

            "tools=mark_save" => array("page" =>"Tools","operate"=>"Mark_Save"),
            "password=" => array("page" =>"Profile","operate"=>"Change password"));
        if($action == "ajax" && $operate == "save_setting_display"){
            $model_name = $_REQUEST['model_name'];
            return $operationConvertName[$model_name."=".$operate];
        }
        if($action == "ajax" && ($operate == "autody" || $operate == "autoport")){
            $model_name = $_REQUEST['search_mode'];
            return $operationConvertName[$model_name."=".$operate];
        }    
        return $operationConvertName[$action."=".$operate];
    }

    public static function analyzeOperationDetail($action,$operate){
        if($action == "login" && $operate == "do_login"){
            $detail = 'System Account';
            if($_REQUEST['token']){
                $detail = 'From Apex Online';
            }
        }
        if($action == "login" && $operate == "logout"){
            $detail = 'User Logout';
        }
        if($action == "login" && $operate == "tracking_checked"){
            $detail = 'Join public tracking action,Public tracking number:'.$_POST['reference_number'];
        }
        if($action == "password"){
            $detail = 'User Change password';
        }
        if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "search"){
            $detail = "";
            //还有一个同以分钟内，不记录相同的查询  这个需要建表查询
            //{"action":"ocean_booking","operate":"search","_ntype":"ocean_booking","cp":"1","ps":"100","rc":"-1","other_filed":"","uname":"ra.admin","psw":"abc123456789"}
            $filter_common_field = array("action","operate","_ntype","cp","ps","rc","other_filed","uname","psw");
            foreach($_REQUEST as $selected_key => $selected){
                if(!utils::in_array($selected_key, $filter_common_field)){
                    if(is_array($selected)){
                        $selected = utils::implode(",",$selected);
                    }
                    $detail .="$selected_key:".$selected."; ";
                }
            }
            if(empty($detail)){
                $detail .="No search condition";
            }
        }
        if($action == "ajax" && $operate == "save_setting_display"){
            $detail = "";
            $type = $_REQUEST['model_name'] == "Booking_Search" ? "Booking_Search" : "Ocean_Search";
            //记录最终save 和 default 字段相比的结果
            $default_ids = common::excuteListSql("select id,display_name from public.ra_online_search_display_cso where model_name = '$type' 
                and display_name in('Booking No.','MBL No.','HBL No.','Transportation Mode','Status',
                    'Shipper','Consignee','Origin Agent','Destination Agent','Creation Time','ETD','ETA',
                    'Voyage','Vessel','Week','Created by') order by default_order");

            $ids = utils::implode(";", $_POST['ids']);
            $save_ids = common::excuteListSql("select id,display_name from public.ra_online_search_display_cso where model_name = '$type' 
                    and id::text = any(regexp_split_to_array('$ids', ';')) order by default_order"); 

            $detele_detail = "";
            foreach($default_ids as $did){
                if(!utils::exist_array($did['id'],$save_ids)){
                    $detele_detail .=$did['display_name']."/";
                }
            }
            $add_detail = "";
            foreach($save_ids as $sid){
                if(!utils::exist_array($sid['id'],$default_ids)){
                    $add_detail .=$sid['display_name']."/";
                }
            }
            if(!empty($detele_detail)){
                $detail.="Detele fields: (".$detele_detail."). ";
            }
            if(!empty($add_detail)){
                $detail.="Add fields: (".$add_detail."). ";
            }
            if(empty($detail)){
                $detail = "The default field has not changed";
            }
        }

        if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "detail"){
            $tabel = $action == "ocean_booking" ? "kln_booking" : "kln_ocean";
            $serial_no = common::deCode($_GET['a'], 'D');
            $sql = "SELECT booking_no,h_bol from public.$tabel where serial_no = '$serial_no' limit 1";
            $data = common::excuteObjectSql($sql);
            if(!empty($data['booking_no'])){
                $detail = 'Booking No.: '.$data['booking_no'];
            }else{
                $detail = 'HBOL: '.$data['h_bol'];
            }
        }

        if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "save_communication"){
            $text = $_POST["text"];
            $detail = urldecode($text);  
        }
        if(($action == "ocean_order") && $operate == "ams_isf_log"){
            $detail = "Enter AMS/ISF Page";  
        }
        if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "excel"){
            $detail = "Filter_condition:" . $_REQUEST['excel_filter_condition']." Selected Fields:". $_REQUEST['selected_fields'];
        }
        //Tracking詳情頁download的file（顯示file名稱）
        if(($action == "ocean_order") && $operate == "download"){
            $filename = common::deCode($_GET['url'], 'D');
            $filename = str_replace("/", DIRECTORY_SEPARATOR, $filename);
            $filename = str_replace("\\", DIRECTORY_SEPARATOR, $filename);
            $display_name = basename($filename);
            if (!file_exists($filename)){
                $detail = "Tracking Detail Attachment Download But File Not Exist : $display_name";
            }else{
                $detail = "Tracking Detail Attachment Download: $display_name";
            }
        }

        //Tracking詳情頁Upload Files（顯示file名稱）
        if(($action == "ocean_order") && $operate == "document_upload"){
            $detail = "Enter Upload Files page";
        }
        
        return $detail;

    }

    public static function calculateTicks($minValue, $maxValue, $targetTickCount = 10) {
        $tickSpacing = ($maxValue - $minValue);
        $tickSpacing = intval($tickSpacing);
		$interval = ceil($tickSpacing / $targetTickCount);
        $len = strlen($interval);
        if ($len >1){
            $interval = ceil($interval/pow(10,$len-1)) *pow(10,$len-1);
        }
        return $interval;
    }

    //只记录Public tracking
    public static function single_operation_log_save($user_type,$user_name,$page,$operation,$operation_detail){
        $sql = "INSERT INTO public.customer_service_operation_log(user_type, user_name, page, operation, operation_detail, 
        operation_time) 
        VALUES ('$user_type', '$user_name', '$page', '$operation', '$operation_detail', now())";
        common::excuteUpdateSql($sql);
    }

    public static function uuid() {
        return strtoupper(md5(uniqid("", TRUE) . mt_rand()));
    }

    public static function count($variable){
        if (is_array($variable)) {
            $count = count($variable);
        } else {
            $count = 0;
        }
        return $count;
    }

    public static function implode($sp,$variable){
        $variable = isset($variable) && is_array($variable) ? $variable : array();
        return implode($sp, $variable);
    }

    public static function in_array($str, $arr){
        if (is_array($arr)) {
            return in_array($str, $arr);
        } else {
            return false;
        }
    }

    public static function exist_array($key,$arr){
        $flag = false;
        foreach($arr as $v){
            if($v['id'] == $key ){
                $flag = true;
            }
        }
        return $flag;
    }

    public static function getConpanyForNotify($_schemas,$type){
        //如果有多個就留空
        $company = "";
        if($type == 'ocean'){
            $company = $_SESSION['ONLINE_USER']['company_name'];
            $company_arr =  explode(";",$company);
            $temp = array();
            foreach($company_arr as $v){
                if(!empty($v)){
                    $temp[] = $v;
                }
            }
            if(count($temp) <= 1){
                return $company;
            }
        }
        if($type == 'air'){
            $company_id =  $_SESSION['ONLINE_USER']['air_customers']; 
            $company_id_arr =  explode(";",$company_id);
            $temp = array();
            foreach($company_id_arr as $v){
                if(!empty($v)){
                    $temp[] = $v;
                }
            }
            if(count($temp) == 1){
                if ($_schemas == "public") {//apex ocean和air 分开
                    $sql = "SELECT company  from ocean.contacts where contact_id = '" . common::check_input($temp[0]) . "' ";
                } else {
                    $sql = "SELECT company  from $_schemas.contacts where contact_id = '" . common::check_input($temp[0]) . "' ";
                }
                $company = common::excuteOneSql($sql);
            }
            return $company;
        }
    }


    public static function getKlnDocNotifyContent($bol,$file_type,$upload_user_name,$upload_user_email,$company,$date_time){
        $report_setting = common::excuteObjectSql("select * from ra_online_auto_report_config where report_type = 'KLN_DOC_Notify' ");
        $report_content = $report_setting['report_content'];
        $columns = common::excuteListSql("select display_name,model_name,database_column_name,excel_width,order_by from public.ra_online_search_display_cso where model_name = 'KLN_DOC_Notify'");
        $missing_packing_th = "<tr>";
        foreach ($columns as $colk => $colvalue) {
                $missing_packing_th.='<td style="width:'.$colvalue['excel_width'].'pt;border:solid black 1.0pt;background:#1F4E78;padding:0cm 5.4pt 0cm 5.4pt;height:14.25pt">
                                        <p class="MsoNormal" align="left" style="text-align:left"><b><span lang="EN-US" style="font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:white">'.$colvalue['display_name'].'</span></b></p>
                            </td>';      
        }
        $missing_packing_th .= "</tr>";
        $report_content = str_replace('<{missing_packing_th}>', $missing_packing_th, $report_content);
        
        $data = array(array("h_bol"=>$bol,"file_type"=>$file_type,"upload_by"=>$upload_user_name,"email"=>$upload_user_email,"company"=>$company,"upload_time"=>$date_time."(US/Pacific)"));
        $missing_packing_tr = "";
        foreach ($data as $rk => $rv) {
            $missing_packing_tr .= "<tr>";
            foreach ($columns as $ck => $cv) {
                if(utils::endWith($cv['display_name'], "email")){
                    $missing_packing_tr.='<td style="font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:black;border-top:none;
                        border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt;padding:0cm 5.4pt 0cm 5.4pt;height:14.25pt">
                            <a href="mailto:'.$rv[$cv['database_column_name']].'">'.$rv[$cv['database_column_name']].'</a>
                    </td>';
                }else{
                    $missing_packing_tr.='<td  style="font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:black;'
                        . 'border-top:none;border-left:none;border-bottom:solid black 1.0pt;border-right:solid black 1.0pt;padding:0cm 5.4pt 0cm 5.4pt;height:14.25pt">'.$rv[$cv['database_column_name']].'</td>';
                }
            }
            $missing_packing_tr .= "</tr>";
        }
        $report_content = str_replace('<{missing_packing_tr}>', $missing_packing_tr, $report_content);
        return $report_content;
    }
    

    public static function _getSql($ids, $type,$shipment_mode,$sqlWhere) {
        $ids_arr = explode(',', $ids);
        $sql = "";
        if($type == "co2e_orgin"){
            $str = "SUM(COALESCE(carbon_emission,0)) as catnum ";
            $filed = "shippr_uncode";
        } else {
            $str = "SUM(COALESCE(carbon_emission,0)) as catnum ";
            $filed = "consignee_uncode";
        }

        $shipment_mode_where = " 1=1 ";
        //这里处理为空，目前没有数据
        $shipment_mode_where = " transport_mode = '$shipment_mode' ";
        $shipment_mode_where .= $sqlWhere;
        foreach ($ids_arr as $value) {
            if (!empty($value)) {
                if (empty($sql)) {
                    $sql .= "SELECT $str FROM public.kln_ocean  where $shipment_mode_where  and $filed = '$value'";
                } else {
                    $sql .= " union all SELECT $str from public.kln_ocean  where $shipment_mode_where and $filed = '$value'";
                }
            }
        }
        return $sql;
    }

    public static function removeDuplicateArray($array){
        $result = array();
        foreach ($array as $value) {
            if (!in_array($value, $result)) {
                $result[] = $value;
            }
        }
        return $result;
    }

    /**
     * 读取文件内容后，重新生成无宏版本文件，支持docx，xlsx(这两个文件类型，被设计就是不能带宏的) 
     * 估计废除了，除非转pdf去宏，没有其他办法。libreoffice可以通过软件里面的tools栏位按钮去点击去宏处理，但没有命令行。
     * 使用unoconv转换文档，但是最新的unoconv，不支持unoconv -f docx --no-macro 命令，网上查询的命令有误
     */
    // public static function removeFIlemacro($inputFileName,$outputFileName,$file_type) {
    //     include_once ONLINE_ROOT . 'thirdparty_vendors' . DS . 'vendor' . DS . 'autoload.php';  
    //     if($file_type == "xlsx" || $file_type == "xls"){
    //         // 读取文件
    //         // $spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
    //         // // 生成新文件
    //         // $writerType = ucfirst($file_type);
    //         // $writer = PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, $writerType); // 使用 Xlsx 格式保存
    //         // $writer->save($outputFileName);
    //         // // 删除来源文件
    //         // unlink($inputFileName);
    //         // LibreOffice 的命令行命令
    //         // $command = "soffice --headless --convert-to pdf --outdir " . dirname($outputFileName) . " " . $inputFileName;
    //         // // 执行命令
    //         // exec($command, $output, $return_var);
    //         // // 检查命令是否成功执行
    //         // if ($return_var === 0) {
    //         //     echo "文档转换成功，已保存为无宏版本：$outputFile\n";
    //         // } else {
    //         //     echo "文档转换失败，返回代码：$return_var\n";
    //         //     echo "输出：\n" . implode("\n", $output);
    //         // }   
    //     }
    //     if($file_type == "docx" || $file_type == "doc"){
    //         //加载现有的 Word 文档
    //         // $phpWord = PhpOffice\PhpWord\IOFactory::load($inputFileName);
    //         // // 保存新的 Word 文档

    //         // // $newPhpWord = new PhpOffice\PhpWord\PhpWord();
    //         // // $newSection = $newPhpWord->addSection();
    //         // // $newSection->addText('Hello World !');
    //         // $writer = PhpOffice\PhpWord\IOFactory::createWriter($phpWord, 'Word2007'); // 使用 DOCX 格式保存
    //         // $writer->save($outputFileName);
    //         // //删除来源文件
    //         // unlink($inputFileName);

    //         // 使用unoconv转换文档
    //         //$command = "unoconv -f docx --no-macro '$inputFileName' -o '$outputFileName'";
            
    //         // $command = "libreoffice --headless --convert-to pdf $inputFileName --outdir D:\DOC\APEXTAO\20250127";
    //         // $output = shell_exec($command);
 
    //         // if ($output) {
    //         //     echo "文件转换成功！";
    //         // } else {
    //         //     echo "文件转换失败。";
    //         // }

    //         //LibreOffice 的命令行命令
    //         $command = "soffice --headless --convert-to pdf --outdir " . dirname($outputFileName) . " " . $inputFileName;
    //         // 执行命令
    //         exec($command." 2>&1", $output, $return_var);
    //         // 检查命令是否成功执行
    //         if ($return_var === 0) {
    //             echo "文档转换成功，已保存为无宏版本：$outputFile\n";
    //         } else {
    //             echo "文档转换失败，返回代码：$return_var\n";
    //             echo "输出：\n" . implode("\n", $output);
    //         }  

            
    //         // 移除宏的命令
    //         //$command = "soffice --convert-to docx:writer_pdf_Export --infilter=MS Word 97 --outdir /path/to/output $inputFileName";
    //         //exec($command);
            
    //         // 复制无宏的文档到新位置
    //         //copy("/path/to/output/document.pdf", $outputFileName);
    //     }
    // }


    // public static function hasMacros($filePath) {
    //     $extension = pathinfo($filePath, PATHINFO_EXTENSION);
    //     if ($extension === 'xlsx') {
    //         // 检查 .xlsx 文件
    //         $zip = new ZipArchive();
    //         if ($zip->open($filePath) === TRUE) {
    //             // 检查是否存在 vbaProject.bin 文件
    //             $hasMacros = $zip->locateName('xl/vbaProject.bin') !== false;
    //             $zip->close();
    //             return $hasMacros;
    //         } else {
    //             return false; // 无法打开文件
    //         }
    //     } elseif ($extension === 'docx') {
    //         // 检查 .docx 文件
    //         $zip = new ZipArchive();
    //         if ($zip->open($filePath) === TRUE) {
    //             // 检查是否存在 vbaProject.bin 文件
    //             $hasMacros = $zip->locateName('word/vbaProject.bin') !== false;
    //             $zip->close();
    //             return $hasMacros;
    //         } else {
    //             return false; // 无法打开文件
    //         }
    //     } else {
    //         return false; // 不是支持的文件类型
    //     }
    // }
    //$command = "soffice --headless --convert-to pdf --outdir " . dirname($outputFileName) . " " . $inputFileName;

    // public static function checkExcelMacros($filePath,$file_name) {
    //     // Construct the command to execute LibreOffice in headless mode.
    //     // Replace with the actual path to your LibreOffice executable if needed.
    //     $libreofficePath = '"C:\Program Files\LibreOffice\program\soffice.exe"'; // Example path, adjust as needed.
    //     //$command = "$libreofficePath --headless --convert-to txt:Text -o /tmp/output.txt \"$filePath\"";
    //     $command = "$libreofficePath --headless --convert-to txt --outdir " . dirname($filePath) . " " . $filePath;
    //     $convert_file = dirname($filePath)."\\".$file_name.".txt";
    //     // Execute the command.
    //     exec($command, $output, $returnCode);
    
    //     // Check for errors.
    //     if ($returnCode !== 0) {
    //         return "Error converting file: " . implode("\n", $output); // Or handle the error as needed.
    //     }
    
    //     // Read the (now text) representation of the file. We do this because the macro information is sometimes embedded in the file structure in a way that directly parsing the spreadsheet file is very difficult.
    //     $textFile = file_get_contents($convert_file);
    
    //     // Clean up the temporary file (important!).
    //     //unlink($convert_file);
    
    //     // Search for indicators of macros.  These are not foolproof, but they are a good start.
    //     // More sophisticated methods might involve inspecting the file's internal structure.
    //     // Look for VBA project stream.
    //     if (strpos($textFile, "VBA Project") !== false || strpos($textFile, "PROJECT") !== false || strpos($textFile, "ThisDocument") !== false || strpos($textFile, "Module") !== false || strpos($textFile, "Sub ") !== false || strpos($textFile, "Function ") !== false) {  // Common macro indicators
    //         return true; // Macros found.
    //     } else {
    //         return false; // No clear macro indicators found.
    //     }
    // }
}
?>