<?php

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

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

    /*
     * mysql check input
     */
    public static function check_input($value, $boolean = FALSE, $toupper = FALSE) {
        if ($boolean !== FALSE) {
            if ($value == "t")
                return "'t'";
            else
                return "'f'";
        }
        $value = trim($value);
        if ($toupper !== FALSE)
            $value = mb_strtoupper($value);
        if (!is_numeric($value)) {
            $value = pg_escape_string($value);
        }
        return $value;
    }
    
    public static function excuteOneSql($sql, $exception = FALSE) {
        if ($exception) {
            global $db;
            $result = $db->GetOne($sql);
            return $result === FALSE ? null : $result;
        } else {
            if (empty($sql))
                exit(json_encode("Program encountered an error."));
            global $db;
            $result = $db->GetOne($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0));
            return $result === FALSE ? null : $result;
        }
    }

    public static function excuteObjectSql($sql) {
        if (empty($sql))
            exit(json_encode("Program encountered an error."));
        global $db;
        $result = $db->GetRow($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0));
        return $result === FALSE ? null : $result;
    }

    public static function excuteUpdateSql($sql, $req_id = FALSE) {
        if (empty($sql))
            exit(json_encode("Program encountered an error."));
        global $db;
        $rs = $db->Execute($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0));
        if ($rs && $req_id !== FALSE)
            return $db->PO_Insert_ID();
        else
            return $rs;
    }
    
    public static function excuteListSql($sql, $exception = FALSE) {
        if ($exception) {
            global $db;
            $result = $db->GetAll($sql);
            return $result === FALSE ? null : $result;
        } else {
            if (empty($sql))
                exit(json_encode("Program encountered an error."));
            global $db;
            $result = $db->GetAll($sql) or ( (!$db->ErrorMsg()) or error_log(common::dbLog($db, $sql), 0));
            return $result === FALSE ? null : $result;
        }
    }

    /*
     * Database log output
    */
    public static function dbLog($db, $sql) {
        $backMsg = $db->errorMsg() . ' sql=' . $sql;
        return $backMsg;
    }

    /*
     * get IP
    */
    public static function ip() {
        if (getenv("HTTP_X_FORWARDED_FOR"))
            return getenv("HTTP_X_FORWARDED_FOR");
        if (getenv("HTTP_CLIENT_IP"))
            return getenv("HTTP_CLIENT_IP");
        if (getenv("REMOTE_ADDR"))
            return getenv("REMOTE_ADDR");
        if ($HTTP_SERVER_VARS["HTTP_X_FORWARDED_FOR"])
            return $HTTP_SERVER_VARS["HTTP_X_FORWARDED_FOR"];
        if ($HTTP_SERVER_VARS["HTTP_CLIENT_IP"])
            return $HTTP_SERVER_VARS["HTTP_CLIENT_IP"];
        if ($HTTP_SERVER_VARS["REMOTE_ADDR"])
            return $HTTP_SERVER_VARS["REMOTE_ADDR"];
        return "Unknown";
    }

    public static function getDBUuid() {
        global $db;
        $sql = "select uuid_generate_v1()";
        $uuid = $db->GetOne($sql);
        return $uuid;
    }

    public static function securityCheckHandNew($action) {
        if (strpos($action, "handset") === 0) {
            
        } elseif (strpos($action, "hand") === 0) {
            
        } else {
            common::sessionVerify();
            $httpAccept = $_SERVER['HTTP_ACCEPT']; // ajax request,is json or html
            $ajax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER
                            ['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest'; // is ajax request?
            if (!isset($_SESSION['ONLINE_USER'])) {
                self::timeoutPrintInfor($httpAccept, $ajax, 'no');
            } else {
                $time = time() - $_SESSION["LAST_OPERATE_TIME"];
                $set_session_timeout = $_SESSION['SESSION_TIMEOUT'];
                if (_getLoginName() == "ra.admin") {
                    $set_session_timeout = 4 * 3600;
                }
                if ($time > $set_session_timeout) {
                    self::timeoutPrintInfor($httpAccept, $ajax, 'yes');
                } else {
                    $_SESSION["LAST_OPERATE_TIME"] = time();
                    if (!_isAdmin() && $_GET["action"] != "linkcrm") {
                        if (!utils::checkExist($_SESSION['ONLINE_USER']['permission'], $action) && !(stripos($action, "main") === 0) && !(stripos($action, "ajax") === 0)) {
                            $data = array("msg"=>"Permission Denied");
                            common::echo_json_encode(403, $data);
                            exit();
                        }
                    }
                }
            }
            session_write_close();
        }
    }
    
    public static function getCompanySearch() {
        $sc_list = _get_schemas();
        if (count($sc_list) == 1) {
            return "";
        }
        if (_isCustomerLogin()) {
            $msg = '<input type="hidden" name="_apex_or_sfs" value=""/>';
        } else {
            $msg = '<div class="modules">Apex/SFS/Kerry :&nbsp;<select name="_apex_or_sfs" class="sl"><option value="">All</option>';
            if ($_SESSION['ONLINE_USER']['main_schemas'] == "public") {
                $msg .= '<option value="public">Apex</option>';
            } else {
                $msg .= '<option value="public">Apex</option>';
            }
            if ($_SESSION['ONLINE_USER']['main_schemas'] == "sfs") {
                $msg .= '<option value="sfs">SFS</option>';
            } else {
                $msg .= '<option value="sfs">SFS</option>';
            }
            if ($_SESSION['ONLINE_USER']['main_schemas'] == "kyiff") {
                $msg .= '<option value="kyiff">Kerry</option>';
            } else {
                $msg .= '<option value="kyiff">Kerry</option>';
            }
            $msg .= '</select></div>';
        }
        return $msg;
    }

    public static function sessionVerify() {
        if (!isset($_SESSION['user_agent'])) {
            $_SESSION['user_agent'] = MD5($_SERVER['REMOTE_ADDR']
                    . $_SERVER['HTTP_USER_AGENT']);
        } elseif ($_SESSION['user_agent'] != MD5($_SERVER['REMOTE_ADDR']
                        . $_SERVER['HTTP_USER_AGENT'])) {
            session_regenerate_id();
        }
    }

    public static function searchExtendHandNew($type, $user, $company_name = "station_name") {
        if (_isAdminHandNew($user)) {
            if(strtolower($type) == "air_booking" ) //|| strtolower($type) == "air"
            {
                return "1=1";
            }
            return " (schem_not_display is null or schem_not_display=false)";
        }
        if (_isDocAdmin($user["user_login"])) {
            if(strtolower($type) == "air_booking" ) //|| strtolower($type) == "air"
            {
                return "1=1";
            }
            return ' (schem_not_display is null or schem_not_display=false)';
        }
        if (strtolower($type) != "ocean" && strtolower($type) != "booking" && strtolower($type) != "air_booking"&& strtolower($type) != "air") {
            return " 1<>1";
        }
        if (empty($user["schemas_list"])) {
            $user["schemas_list"] = $_SESSION["schemas_list"];
        }
        $schemas_list = $user["schemas_list"];

        if ($user["is_kerry_shipment"] == "t") {
            $sqlWhere = " is_kerry_shipment is not null and is_kerry_shipment=true";
        } else if ($user["is_kerry_shipment"] == "f") {
            $sqlWhere = " (is_kerry_shipment is null or is_kerry_shipment=false)";
        } else {
            $sqlWhere = " 1=1";
        }

        if (!empty($_POST["_apex_or_sfs"])) {
            $sqlWhere .= " and order_from='" . $_POST["_apex_or_sfs"] . "'";
        }

        if (count($schemas_list) == 1) {
            $schames = $schemas_list[0]["schemas_name"];
            if(strtolower($type) == "air_booking" ||strtolower($type) == "air")
            {

            }
            else
            $sqlWhere .= " and order_from='$schames'";

            if ($schames == "sfs" && empty($user["sfs_ONLINE_USER"])) {
                $user = $_SESSION["sfs_ONLINE_USER"];
            }
            if (strtolower($type) == "ocean") {
                $sqlWhere .= self::_oceanHandNew($user, $schames);
                if ($company_name == "doc") {
                    if (empty($user["view_file_format"])) {
                        if (strtolower($user["user_type"]) == "customer") {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from $schames.ra_online_file_format where active = true and client_display = true)";
                        } else {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from $schames.ra_online_file_format where active = true)";
                        }
                    } else {
                        $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))";
                    }
                }
            }
            if (strtolower($type) == "booking") {
                $sqlWhere .= self::_bookingHandNew($user, $schames);
            }
            if (strtolower($type) == "air_booking") {
                $sqlWhere .= self::_airHandNew($user, $schames);
            }
            
            if (strtolower($type) == "air") {
                $sqlWhere .= self::_airHandNew($user, $schames);
                if ($company_name == "doc") {
                    if (empty($user["view_file_format"])) {
                        if (strtolower($user["user_type"]) == "customer") {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from $schames.ra_online_file_format where active = true and client_display = true)";
                        } else {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from $schames.ra_online_file_format where active = true)";
                        }
                    } else {
                        $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))";
                    }
                }
            }

        } elseif (count($schemas_list) == 2) {
            if(strtolower($type) == "air_booking" )//|| strtolower($type) == "air"
            {
                // $sqlWhere .= " and 1=1";
            }
            else
                $sqlWhere .= " and (schem_not_display is null or schem_not_display=false)";
            if (strtolower($type) == "ocean") {
                $sqlWhere .= " and ((order_from='public' ";
                $sqlWhere .= self::_oceanHandNew($user, "public");
                if ($company_name == "doc") {
                    if (empty($user["view_file_format"])) {
                        if (strtolower($user["user_type"]) == "customer") {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from public.ra_online_file_format where active = true and client_display = true)";
                        } else {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from public.ra_online_file_format where active = true)";
                        }
                    } else {
                        $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))";
                    }
                }
                $sqlWhere .= ") or (order_from='sfs' ";

                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_oceanHandNew($user, "sfs");
                if ($company_name == "doc") {
                    if (empty($user["view_file_format"])) {
                        if (strtolower($user["user_type"]) == "customer") {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from sfs.ra_online_file_format where active = true and client_display = true)";
                        } else {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from sfs.ra_online_file_format where active = true)";
                        }
                    } else {
                        $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))";
                    }
                }
                $sqlWhere .= ") )";
            }
            if (strtolower($type) == "booking") {
                $sqlWhere .= " and ((order_from='public' ";
                $sqlWhere .= self::_bookingHandNew($user, "public");
                $sqlWhere .= ") or (order_from='sfs' ";
                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_bookingHandNew($user, "sfs");
                $sqlWhere .= "))";
            }
            if (strtolower($type) == "air_booking") {
                $sqlWhere .= " and ((order_from='public' ";
                $sqlWhere .= self::_airHandNew($user, "public");
                $sqlWhere .= ") or (order_from='sfs' ";
                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_airHandNew($user, "sfs");
                $sqlWhere .= "))";

                // $sqlWhere .= self::_airHandNew($user, $schames);
            }
            
            if (strtolower($type) == "air") {
                $sqlWhere .= " and ((order_from='public' ";
                $sqlWhere .= self::_airHandNew($user, "public");
                if ($company_name == "doc") {
                    if (empty($user["view_file_format"])) {
                        if (strtolower($user["user_type"]) == "customer") {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from public.ra_online_file_format where active = true and client_display = true)";
                        } else {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from public.ra_online_file_format where active = true)";
                        }
                    } else {
                        $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))";
                    }
                }
                $sqlWhere .= ") or (order_from='sfs' ";

                if (empty($user["sfs_ONLINE_USER"])) {
                    $user = $_SESSION["sfs_ONLINE_USER"];
                }
                $sqlWhere .= self::_airHandNew($user, "sfs");
                if ($company_name == "doc") {
                    if (empty($user["view_file_format"])) {
                        if (strtolower($user["user_type"]) == "customer") {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from sfs.ra_online_file_format where active = true and client_display = true)";
                        } else {
                            $sqlWhere .= " and serial_no in (SELECT serial_no from sfs.ra_online_file_format where active = true)";
                        }
                    } else {
                        $sqlWhere .= " and serial_no in (SELECT regexp_split_to_table('" . $user["view_file_format"] . "',';'))";
                    }
                }
                $sqlWhere .= ") )";
            }
        } else {
            $sqlWhere = " 1<>1";
        }
        return $sqlWhere;
    }
    

    public static function getStatusStage($status) {
        if ($status == "EE" || $status == "I") {
            return 0;
        }
        if ($status == "AE" || $status == "VD" || $status == "VA_RELAY" || $status == "UV_RELAY" || $status == "AE_RELAY" || $status == "VD_RELAY") {
            return 1;
        }
        if ($status == "VA" || $status == "UV" || $status == "AL" || $status == "AR" || $status == "CU" || $status == "CT" || $status == "CR" || $status == "OA") {
            return 2;
        }
        if ($status == "AV" || $status == "D" || $status == "RD") {
            return 3;
        }
        return -1;
    }

    public static function getInNotInSql($contact_id, $type = 'in') {
        if (empty($contact_id))
            return " =''";
        $contact_id = trim($contact_id);
        $contact_id = trim($contact_id, ";");
        $contact_id = trim($contact_id);
        $contact_id = strtolower($contact_id);
        if ($type == 'in') {
            if (utils::checkExist($contact_id, ";")) {
                $ss = "";
                $aa = explode(";", $contact_id);
                foreach ($aa as $k => $v) {
                    $v = trim($v);
                    if (empty($ss))
                        $ss = "'" . common::check_input($v) . "'";
                    else
                        $ss .= ",'" . common::check_input($v) . "'";
                }
                return " in (" . $ss . ")";
            } else {
                return " = '" . common::check_input($contact_id) . "'";
            }
        } else {
            if (utils::checkExist($contact_id, ";")) {
                $ss = "";
                $aa = explode(";", $contact_id);
                foreach ($aa as $k => $v) {
                    $v = trim($v);
                    if (empty($ss))
                        $ss = "'" . common::check_input($v) . "'";
                    else
                        $ss .= ",'" . common::check_input($v) . "'";
                }
                return " in (" . $ss . ")";
            } else {
                return " != '" . common::check_input($contact_id) . "'";
            }
        }
    }

     /*
     * Encrypt a SQL query statement used to be passed as a parameter to get excel output
      encode  :DeCode('str','E');
      decode  :DeCode('enstr','D');
     */

     public static function deCode($string, $operation = "E") {
        $key = md5("uls_webwms");
        $key_length = strlen($key);

        if ($operation == "D")
            $string = rawurldecode($string);
        $string = $operation == 'D' ? base64_decode($string) : substr(md5($string . $key), 0, 8) . $string;
        $string_length = strlen($string);
        $rndkey = $box = array();
        $result = '';
        for ($i = 0; $i <= 255; $i++) {
            $rndkey [$i] = ord($key [$i % $key_length]);
            $box [$i] = $i;
        }
        for ($j = $i = 0; $i < 256; $i++) {
            $j = ($j + $box [$i] + $rndkey [$i]) % 256;
            $tmp = $box [$i];
            $box [$i] = $box [$j];
            $box [$j] = $tmp;
        }
        for ($a = $j = $i = 0; $i < $string_length; $i++) {
            $a = ($a + 1) % 256;
            $j = ($j + $box [$a]) % 256;
            $tmp = $box [$a];
            $box [$a] = $box [$j];
            $box [$j] = $tmp;
            $result .= chr(ord($string [$i]) ^ ($box [($box [$a] + $box [$j]) % 256]));
        }

        if ($operation == 'D') {
            if (substr($result, 0, 8) == substr(md5(substr($result, 8) . $key), 0, 8)) {
                return substr($result, 8);
            } else {
                return '';
            }
        } else {
            return rawurlencode(str_replace('=', '', base64_encode($result)));
        }
    }
    
    public static function getStatusDesc($code) {
        if (strtoupper($code) == "EE") {
            return "Empty Equipment Dispatched";
        }
        if (strtoupper($code) == "I") {
            return "Gate in full for a booking";
        }
        if (strtoupper($code) == "AE") {
            return "Container loaded on vessel";
        }
        if (strtoupper($code) == "VD") {
            return "Vessel Departure";
        }
        if (strtoupper($code) == "VA_RELAY") {
            return "Arrive Relay Port";
        }
        if (strtoupper($code) == "VD_RELAY") {
            return "Depart Relay Port";
        }
        if (strtoupper($code) == "AE_RELAY") {
            return "Loaded at Relay Port";
        }
        if (strtoupper($code) == "UV_RELAY") {
            return "Unloaded at Relay Port";
        }
        if (strtoupper($code) == "VA") {
            return "Vessel Arrival";
        }
        if (strtoupper($code) == "UV") {
            return "Unloaded From Vessel";
        }
        if (strtoupper($code) == "AL") {
            return "Container loaded on Rail";
        }
        if (strtoupper($code) == "AR") {
            return "Container unloaded from Rail";
        }
        if (strtoupper($code) == "CU") {
            return "Carrier and Customs Release";
        }
        if (strtoupper($code) == "CT") {
            return "Customs release";
        }
        if (strtoupper($code) == "CR") {
            return "Carrier release";
        }
        if (strtoupper($code) == "OA") {
            return "Gate out full from final discharge port";
        }
        if (strtoupper($code) == "AV") {
            return "Shipment available for pickup or delivery";
        }
        if (strtoupper($code) == "RD") {
            return "Container returned empty";
        }
        if (strtoupper($code) == "D") {
            return "Gate out for delivery to customer";
        }
    }

    public static function _toString($msg) {
        if ($msg == "" || $msg == NULL) {
            return "";
        }
        return $msg . "";
    }

    /*
     *  timeout output
     */

     public static function timeoutPrintInfor($httpAccept, $ajax, $login) {
        $data = array("msg"=>"session_time_out");
        self::echo_json_encode(403,$data);
        exit();
    }


    private static function _oceanHandNew($user, $schemas = "public") {
        $o = $user['ocean_station'];
        $o_or = $user['ocean_station_or'];
        $d = $user['ocean_agent'];
        $d_or = $user['ocean_agent_or'];
        $sales = $user['ocean_sales'];
        $sales_or = $user['ocean_sales_or'];
        $op = $user['ocean_dest_op'];
        $op_or = $user['ocean_dest_op_or'];
        $follow = $user['ocean_following_sales'];
        $follow_or = $user['ocean_following_sales_or'];

        if (strtolower($o_or) == "all" || strtolower($d_or) == "all" || strtolower($sales_or) == "all" || strtolower($op_or) == "all") {
            return " and 1=1";
        }
        $sqlWhere = "";
        if (_isCustomerLoginHandNew($user)) {
            //error_log("_oceanHandNew".$schemas);
            $sqlWhere .= " and " . _customerFilerSearchHandNew($user, $schemas);
        } else {
            if (empty($o) && empty($d) && empty($sales) && empty($op) && empty($follow)) {
                return " and 1<>1";
            }
            if ((strtolower($o) == 'all' || empty($o)) && (strtolower($d) == "all" || empty($d))) {
                
            } else {
                $sql = "1=1";
                if (!empty($o) && strtolower($o) != 'all') {
                    $sql .= " and lower(origin)";
                    $sql .= utils::getInSql($o);
                }
                if (!empty($d) && strtolower($d) != 'all') {
                    $sql .= " and lower(agent)";
                    $sql .= utils::getInSql($d);
                }
                $sqlWhere .= " and (" . $sql . ")";
            }
            if (strtolower($sales) == 'all' || empty($sales)) {
                
            } else {
                if (utils::checkExist($sales, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $sales);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                            $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                    }
                    $sqlWhere .= " and ($sql)";
                } else
                    $sqlWhere .= " and lower(sales_rep)='" . strtolower($sales) . "'";
            }

            if (strtolower($op) == 'all' || empty($op)) {
                
            } else {
                $sqlWhere .= " and lower(dest_op) " . utils::getInSql($op);
            }

            if (strtolower($follow) == 'all' || empty($follow)) {
                
            } else {
                if (utils::checkExist($follow, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $follow);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                            $sql .= " or following_sales ilike '" . $t . "'";
                    }
                    $sqlWhere .= " and ($sql)";
                } else
                    $sqlWhere .= " and following_sales ilike '" . $follow . "'";
            }
            $sqlWhere = " (1=1 $sqlWhere)";
            if (!empty($o_or)) {
                $sqlWhere .= " or lower(origin)";
                $sqlWhere .= utils::getInSql($o_or);
            }
            if (!empty($d_or)) {
                $sqlWhere .= " or lower(agent)";
                $sqlWhere .= utils::getInSql($d_or);
            }
            if (!empty($sales_or)) {
                if (utils::checkExist($sales_or, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $sales_or);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                            $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                    }
                    $sqlWhere .= " or ($sql)";
                } else
                    $sqlWhere .= " or lower(sales_rep)='" . strtolower($sales_or) . "'";
            }
            if (!empty($op_or)) {
                $sqlWhere .= " or lower(dest_op) " . utils::getInSql($op_or);
            }
            if (!empty($follow_or)) {
                if (utils::checkExist($follow_or, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $follow_or);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                            $sql .= " or following_sales ilike '" . $t . "'";
                    }
                    $sqlWhere .= " or ($sql)";
                }else {
                    $sqlWhere .= " or following_sales ilike '" . $follow_or . "'";
                }
            }

            $sqlWhere = " and ($sqlWhere)";
        }

        return $sqlWhere;
    }

    private static function _bookingHandNew($user, $schames) {
        //        if (_isDemo())
        //            return " serial_no = 'D4DD1D79-83F4-4E65-9773-CF5277D72738'";
        
                $o = $user['ocean_station'];
                $o_or = $user['ocean_station_or'];
                $d = $user['ocean_agent'];
                $d_or = $user['ocean_agent_or'];
                $sales = $user['ocean_sales'];
                $sales_or = $user['ocean_sales_or'];
                $op = $user['ocean_dest_op'];
                $op_or = $user['ocean_dest_op_or'];
                $follow = $user['ocean_following_sales'];
                $follow_or = $user['ocean_following_sales_or'];
        
                if (strtolower($o_or) == "all" || strtolower($d_or) == "all" || strtolower($sales_or) == "all") {
                    return " and 1=1";
                }
        //        $sc_list = $user['schemas_list'];
        //        if (empty($sc_list)) {
        //            $sc_list = $_SESSION["schemas_list"];
        //        }
        //        if (empty($sc_list)) {
        //            return " and 1<>1";
        //        }
        //        $sqlWhere = " and 1=1";
        //        if ($user["is_kerry_shipment"] == "t") {
        //            $sqlWhere = " and and is_kerry_shipment is not null and is_kerry_shipment=true";
        //        } else if ($user["is_kerry_shipment"] == "f") {
        //            $sqlWhere = " and (is_kerry_shipment is null or is_kerry_shipment=false)";
        //        }
        //        if (count($sc_list) == 1) {
        //            $sch = $sc_list[0]['schemas_name'];
        //            $sqlWhere .= " and order_from='$sch'";
        //        } else {
        //            $sqlWhere .= " and (schem_not_display is null or schem_not_display=false)";
        //        }
                $sqlWhere = "";
                if (_isCustomerLoginHandNew($user)) {
                    $sqlWhere .= " and " . _customerFilerSearchHandNew($user, $schames);
                } else {
                    if (empty($o) && empty($d) && empty($sales) && empty($op) && empty($follow)) {
                        return " and 1<>1";
                    }
                    if ((strtolower($o) == 'all' || empty($o)) && (strtolower($d) == "all" || empty($d))) {
                        
                    } else {
                        $sql = "1=1";
                        if (!empty($o) && strtolower($o) != 'all') {
                            $sql .= " and lower(origin)";
                            $sql .= utils::getInSql($o);
                        }
                        if (!empty($d) && strtolower($d) != 'all') {
                            $sql .= " and lower(agent)";
                            $sql .= utils::getInSql($d);
                        }
                        $sqlWhere .= " and (" . $sql . ")";
                    }
                    if (strtolower($sales) == 'all' || empty($sales)) {
                        
                    } else {
                        if (utils::checkExist($sales, ";")) {
                            $sql = "1!=1";
                            $tt = explode(";", $sales);
                            foreach ($tt as $t) {
                                $t = trim($t);
                                if (!empty($t))
                                    $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                            }
                            $sqlWhere .= " and ($sql)";
                        } else
                            $sqlWhere .= " and lower(sales_rep)='" . strtolower($sales) . "'";
                    }
                    if (strtolower($follow) == 'all' || empty($follow)) {
                        
                    } else {
                        if (utils::checkExist($follow, ";")) {
                            $sql = "1!=1";
                            $tt = explode(";", $follow);
                            foreach ($tt as $t) {
                                $t = trim($t);
                                if (!empty($t))
                                    $sql .= " or following_sales ilike '" . $t . "%'";
                            }
                            $sqlWhere .= " and ($sql)";
                        } else
                            $sqlWhere .= " and following_sales ilike '" . $follow . "%'";
                    }
        
                    $sqlWhere = " (1=1 $sqlWhere)";
                    if (!empty($o_or)) {
                        $sqlWhere .= " or lower(origin)";
                        $sqlWhere .= utils::getInSql($o_or);
                    }
                    if (!empty($d_or)) {
                        $sqlWhere .= " or lower(agent)";
                        $sqlWhere .= utils::getInSql($d_or);
                    }
                    if (!empty($sales_or)) {
                        if (utils::checkExist($sales_or, ";")) {
                            $sql = "1!=1";
                            $tt = explode(";", $sales_or);
                            foreach ($tt as $t) {
                                $t = trim($t);
                                if (!empty($t))
                                    $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                            }
                            $sqlWhere .= " or ($sql)";
                        } else
                            $sqlWhere .= " or lower(sales_rep)='" . strtolower($sales_or) . "%'";
                    }
                    if (!empty($follow_or)) {
                        if (utils::checkExist($follow_or, ";")) {
                            $sql = "1!=1";
                            $tt = explode(";", $follow_or);
                            foreach ($tt as $t) {
                                $t = trim($t);
                                if (!empty($t))
                                    $sql .= " or following_sales ilike '" . $t . "%'";
                            }
                            $sqlWhere .= " or ($sql)";
                        } else
                            $sqlWhere .= " or following_sales ilike '" . $follow_or . "%'";
                    }
        
                    $sqlWhere = " and ($sqlWhere)";
                }
                return $sqlWhere;
    }

    private static function _airHandNew($user, $schemas = "public") 
    {
        if($schemas=="public")
        {
            $station = $user['air_station'];
            $station_or = $user['air_station_or'];
     
            $sales = $user['air_sales'];
            $sales_or = $user['air_sales_or'];
        }
        else
        {
            $station = $user[$schemas."_ONLINE_USER"]['air_station'];
            $station_or = $user[$schemas."_ONLINE_USER"]['air_station_or'];
     
            $sales = $user[$schemas."_ONLINE_USER"]['air_sales'];
            $sales_or = $user[$schemas."_ONLINE_USER"]['air_sales_or'];
        }

 
        if (strtolower($station_or) == "all"  || strtolower($sales_or) == "all" ) //|| strtolower($d_or) == "all"|| strtolower($op_or) == "all"|| strtolower($follow_or) == "all"
        {
            return " and 1=1";
        }
        $sqlWhere = "";
        if (_isCustomerLoginHandNew($user)) {
            //error_log("_oceanHandNew".$schemas);
            $sqlWhere .= " and " . _customerFilerSearchHandNew_Air($user, $schemas);
        } 
        else 
        {
            if (empty($station)  && empty($sales) ) //&& empty($d)&& empty($op) && empty($follow)
            {
                return " and 1<>1";
            }
            if ((strtolower($station) == 'all' || empty($station)) ) //&& (strtolower($d) == "all" || empty($d))
            {
                
            } 
            else 
            {
                $sql = "1=1";
                if (!empty($station) && strtolower($station) != 'all') {
                    $sql .= " and (lower(origin)";
                    $sql .= utils::getInSql($station);
                    $sql .= " or lower(destination_station)";
                    $sql .= utils::getInSql($station);
                    $sql .= ")";
                }
                $sqlWhere .= " and (" . $sql . ")";
            }
            if (strtolower($sales) == 'all' || empty($sales)) {
                
            } else 
            {
                if (utils::checkExist($sales, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $sales);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                        {
                            $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                            $sql .= " or lower(following_sales)='" . strtolower($t) . "'";

                        }
                    }
                    $sqlWhere .= " and ($sql)";
                } else
                {
                    $sqlWhere .= " or lower(sales_rep)='" . strtolower($sales) . "'";
                    $sqlWhere .= " or lower(following_sales)='" . strtolower($sales) . "'";
                }

            }

           
            $sqlWhere = " (1=1 $sqlWhere)";
            if (!empty($station_or)) {
                $sqlWhere .= " or lower(origin)";
                $sqlWhere .= utils::getInSql($station_or);
                $sqlWhere .= " or lower(destination_station)";
                $sqlWhere .= utils::getInSql($station_or);
            }
            if (!empty($sales_or)) {
                if (utils::checkExist($sales_or, ";")) {
                    $sql = "1!=1";
                    $tt = explode(";", $sales_or);
                    foreach ($tt as $t) {
                        $t = trim($t);
                        if (!empty($t))
                        {
                            $sql .= " or lower(sales_rep)='" . strtolower($t) . "'";
                            $sql .= " or lower(following_sales)='" . strtolower($t) . "'";
                        }
                    }
                    $sqlWhere .= " or ($sql)";
                } else
                {
                    $sqlWhere .= " or lower(sales_rep)='" . strtolower($sales_or) . "'";
                    $sqlWhere .= " or lower(following_sales)='" . strtolower($sales_or) . "'";

                }
            }
            $sqlWhere = " and ($sqlWhere)";
        }

        return $sqlWhere;
    }

    public static function isNewVersion() {
        if (utils::checkExist($_SERVER['PHP_SELF'], "main_new_version.php")) {
            return "_new";
        }
        return "";
    }

    public static function removeTopOceanOldVersionSpecialField($rss){
        $rss_bk = $rss;
        $new_arr = array();
        foreach ($rss_bk as $k => $v) {
            if (utils::startWith($v['database_column_name'], "__") || utils::startWith($v['database_column_name'], "___")){
                //unset($rss[$k]);
            }else{
                $new_arr[] = $v;
            }
        }
        return $new_arr;
    }

    public static function echo_json_encode($code,$data){
        $resData = array();
        $resData["code"] = $code;
        $resData["data"] =$data;
        echo utils::jsonFiltration("null", "\"\"", json_encode($resData));
    }

    /*
     * MM/DD/YYYY To YYYY-MM-DD
     */

     public static function usDate2sqlDate($timestr) {
        if (empty($timestr))
            return '';
        $datearray = explode("/", $timestr);

        $m = $datearray [0];
        $d = $datearray [1];
        $y = $datearray [2];

        return $y . "-" . $m . "-" . $d;
    }

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

    /*
    * YYYYMMDD To MM/DD/YYYY
    */
    public static function date2usdate($datestr) {
        if (empty($datestr))
            return '';

        $y = substr($datestr, 0, 4);
        $m = substr($datestr, 4, 2);
        $d = substr($datestr, 6, 2);

        return $m . "/" . $d . "/" . $y;
    }

    /*
     * date add some days
    */
    public static function addDays($date, $days) {
        $time = strtotime($date) + $days * 24 * 3600;
        return date('m/d/Y', $time);
    }

    /*
     * download file from file system
     */

     public static function download_file($filename, $display_name = null, $delete = FALSE, $files = NULL) {
        $filename = str_replace("/", DIRECTORY_SEPARATOR, $filename);
        $filename = str_replace("\\", DIRECTORY_SEPARATOR, $filename);
        if (!file_exists($filename))
            exit('File Not Exist');
        if (empty($display_name))
            $display_name = basename($filename);
        //$file = fopen($filename, "r");
        header_remove("Content-type");
        header("Content-type:" . self::getContentType($filename));
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: can-cache");
        header("Accept-ranges:bytes");
        header("Accept-length:" . filesize($filename));
        header("Content-Disposition:attachment;filename=\"" . $display_name . "\"");
        //echo fread($file, filesize($filename));
        //fclose($file);
        //针对大文件，规定每次读取文件的字节数为4096字节，直接输出数据
        $read_buffer = 4096;
        $handle = fopen($filename, 'rb');
        //总的缓冲的字节数
        $sum_buffer = 0;
        $filesize = filesize($filename);

        //只要没到文件尾，就一直读取
        while (!feof($handle) && $sum_buffer < $filesize) { 
            echo fread($handle, $read_buffer);
            $sum_buffer += $read_buffer;
        }
        //关闭句柄
        fclose($handle);
        if ($delete !== FALSE) {
            unlink($filename);
        }
        if (!empty($files)) {
            foreach ($files as $f) {
                unlink($f);
            }
        }
    }

    public static function getContentType($filename) {
        $extend = self::getExtendFilename($filename);
        $filetype = array(
            'xls' => 'application/vnd.ms-excel',
            'doc' => 'application/msword',
            'gif' => 'image/gif',
            'jpg' => 'image/jpeg',
            'jpeg' => 'image/jpeg',
            'jpe' => 'image/jpeg',
            'bmp' => 'image/bmp',
            'png' => 'image/png',
            'tif' => 'image/tiff',
            'pdf' => 'application/pdf',
            'zip' => 'application/zip'
        );
        return $filetype[$extend];
    }

    /*
     * Get the file extension
    */
    public static function getExtendFilename($file_name) {
        $extend = pathinfo($file_name);
        $extend = strtolower($extend["extension"]);

        return $extend;
    }

    public static function getManagement(){
        //Management 自己配置需要创建表保存
        $user_management = common::excuteOneSql("select management from ra_online_user where user_login = '"._getLoginName()."'");
        //为空,配置使用默认值
        if(empty($user_management)){
            $Management = common::getdefaultManagement();
        }else{
            $Management = json_decode($user_management,true);
            foreach($Management as $key =>$val){
                $Management[$key]['id'] = intval($val['id']);
                $Management[$key]['switchValue'] = $val['switchValue'] == "true" ? true : false;
            }
        }
        return  $Management;
    }

    public static function getItemStyle($type,$code){
        if($type == 'r1'){
            $data =  array("0-20 Days" =>"#ffc594",
            "20-40 Days" =>"#ff9e4c",
            "40-60 Days" =>"#ff7500",
            "60-80 Days" =>"#ff3d00",
            "Over 80 Days" =>"#d50000");
            return $data[$code];
        }
        if($type == 'r4' || $type == 'r3' || $type == 'atd_r4' || $type == 'ata_r3'){
            $data =  array("0 Day" =>"#ffc594",
            "Today" =>"#ffc594",
            "1-2 Days" =>"#ff9e4c",
            "3-6 Days" =>"#ff7500",
            "7 Days" =>"#ff3d00");
            return $data[$code];
        }

        if($type == 'r2' || utils::startWith($type,"co2e")){
            $data =  array("45" =>"#FFE3CC",
            "40" =>"#FFAC66",
            "20" =>"#FF7500",
            "air" =>"#FFE3CC",
            "sea" =>"#FFAC66",
            "road" =>"#FF7500");
            return $data[$code];
        }
        if($type == "top"){
            $data =  array("1" =>"#FF7500",
            "2" =>"#ff9033",
            "3" =>"#ff9e4d",
            "4" =>"#ffac66",
            "5" =>"#ffba80",
            "6" =>"#ffc899",
            "7" =>"#ffd6b3",
            "8" =>"#ffe3cc",
            "9" =>"#fff1e6",
            "10" =>"#fff1e6");
            return $data[$code];
        }
    }

    //处理返回原表数据格式
    public static function mian_repot_do($value,$type,$totalValue){
        $data = array();
        $value_arr = json_decode($value,true);
        if($type == 'r1'){
            $ETDList = array();
            foreach($value_arr as $arr){
                $color = common::getItemStyle($type,$arr['name']);
                $ETDList[] = array("value" =>intval($arr['value']),"name" =>$arr['name'],"itemStyle" =>array("color" =>$color));
            }
            $ETD_Title = "Total: $totalValue";
            $data = array("ETDList" =>$ETDList,"ETD_Radius"=>array('50%','80%'),"ETD_Title" =>$ETD_Title);
        }
        if($type == 'r4' || $type == 'r3'){
            //重新整理一下数据，给UI一致
            $data_kd = array();
            foreach($value_arr as $arr){
                if(stripos("Today", $arr['name']) !== false){
                    $color = common::getItemStyle($type,"Today");
                    if(empty($data_kd["0"])){
                        $data_kd["0"] = array("value" =>intval($arr['value']),"name" =>"0 Day","itemStyle" =>array("color" =>$color));
                    }else{
                        $data_kd["0"]["value"] = $data_kd["0"]["value"] + intval($arr['value']);
                    }
                }
                if(stripos("+1 Days/+2 Days", $arr['name']) !== false){
                    $color = common::getItemStyle($type,"1-2 Days");
                    if(empty($data_kd["1"])){
                        $data_kd["1"] = array("value" =>intval($arr['value']),"name" =>"1-2 Days","itemStyle" =>array("color" =>$color));
                    }else{
                        $data_kd["1"]["value"] = $data_kd["1"]["value"] + intval($arr['value']);
                    }
                }
                if(stripos("+3 Days/+4 Days/+5 Days/+6 Days", $arr['name']) !== false){
                    $color = common::getItemStyle($type,"3-6 Days");
                    if(empty($data_kd["2"])){
                        $data_kd["2"] = array("value" =>intval($arr['value']),"name" =>"3-6 Days","itemStyle" =>array("color" =>$color));
                    }else{
                        $data_kd["2"]["value"] = $data_kd["2"]["value"] + intval($arr['value']);
                    }
                }
                if(stripos("+7 Days/Over 7 Days", $arr['name']) !== false){
                    $color = common::getItemStyle($type,"7 Days");
                    if(empty($data_kd["3"])){
                        $data_kd["3"] = array("value" =>intval($arr['value']),"name" =>"7 Days","itemStyle" =>array("color" =>$color));
                    }else{
                        $data_kd["3"]["value"] = $data_kd["3"]["value"] + intval($arr['value']);
                    }
                }
            }
            if($type == 'r4'){
                $data = array("ETDList" =>$data_kd,"ETD_Radius"=>array('30%','50%'),"title1" =>"Pending","title2" =>"(ATD-ETD)");
            }
            if($type == 'r3'){
                $data = array("ETDList" =>$data_kd,"ETD_Radius"=>array('30%','50%'),"title1" =>"Pending","title2" =>"(ATD-ETD)");
            }
            
        }
        return $data;
    }

    //单独处理co2e bar
    public static function getCo2eBar(){
        //新UI air sea road 目前只有sea
        $type = $_REQUEST["r_type"];
        $container_type_arr = array("air","sea","road");
        $container_type_param = 'sea';
        $ContainerCounSeries = array();
        $ContainerCount_Title = array("HKHKG","DKHKG","EKHKG","FKHKG","KKHKG","GKHKG","MKHKG","LKHKG","NKHKG","OKHKG"); 
        foreach($container_type_arr as $_container_type){
            //查询逻辑需要问，目前暂时写死ssh-keygen -t rsa -b 4096 -C "shuanghong.shuai@united-cn.net"
            $container_type = $_container_type;
            $data = array(1500, 1500, 2100, 1500, 2450, 1900, 1900, 900, 600, 500);
            $total = 0;
            $max = 0;
            // foreach($value_arr as $arr){
            //     $data[] = intval($arr['value']);
            //     $total = $total + intval($arr['value']);
            // }
            foreach($data as $arr){
                $total = $total + $arr;
                $max = $max < $arr ? $arr : $max;
             }
            $ContainerCounSeries[$container_type] = array("data"=>$data,"total"=>$total,"max"=>$max);
        }
        
        //处理返回时数据格式
        $max = 0;
        $ContainerCounSeries_return = array();
        foreach($ContainerCounSeries as $k =>$v){
            $color = common::getItemStyle($type,$k);
            $ContainerCounSeries_return[] = array("name"=>$k,"type"=>"bar","emphasis" => array("focus" =>"none"),
                "stack" =>"总计","data" =>$v['data'],"itemStyle" =>array("color" =>$color));  
            $max = $max + $v['max'];
            
        }
        //处理返回原表数据格式
        //计算刻度值 最小值是0，最大值是3000，刻度是500  interval
        $interval = utils::calculateTicks(0,$max,10);
        if($interval == 0){
            //处理返回默认值
            $interval = 1;
        }
        $returnData = array("ContainerCount_Title"=>"","ContainerCountList" =>$ContainerCount_Title,"ContainerCounSeries" =>$ContainerCounSeries_return,
            "min" => 0,"Max" =>$interval*10,"interval" =>$interval);
        return $returnData;
    }

    public static function getTopBar(){
        $toporigin = array();
        for($i = 0; $i<10; $i++){
            $num = $i + 1;
            $toporigin[] = array("name"=>"CNSGH$num","value"=>$num,"color"=>common::getItemStyle("top",$num));
        }

        $topdestination = array();
        for($i = 0; $i<10; $i++){
            $num = $i + 1;
            $topdestination[] = array("name"=>"CNSGH$num","value"=>$num,"color"=>common::getItemStyle("top",$num));
        }

        //处理返回原表数据格式
        $interval = utils::calculateTicks(0,10,10);
        if($interval == 0){
            //处理返回默认值
            $interval = 1;
        }
        $returnData = array("seller_data_list_origin"=>$toporigin,"seller_data_list_destination"=>$topdestination,
            "min" => 0,"Max" =>$interval*10,"interval" =>$interval);
        return $returnData;
    }

    public static function getdefaultManagement(){
        $Management = array();
        $Management[] = array("id"=>1 ,
            "title"=>"KPI",
            "switchValue"=>true,
            "text"=>"Pie chart showing figures of shipments KPI of Departure and Arrival.");
        $Management[] = array("id"=>2 ,
            "title"=>"Pending Departure & Arrival",
            "switchValue"=>true,
            "text"=>"Pie chart showing figures of shipments which are soon to depart/arrive (Calculated from ETD/ETA).");
        $Management[] = array("id"=>3 ,
            "title"=>"Recent Status",
            "switchValue"=>true,
            "text"=>"A shipment list with latest status update on top.");
        $Management[] = array("id"=>4 ,
            "title"=>"ETD to ETA (Days)",
            "switchValue"=>true,
            "text"=>"Pie chart showing figures of shipments which are soon to depart/arrive (Calculated from ETD/ETA).");
        $Management[] = array("id"=>5 ,
            "title"=>"Container Count",
            "switchValue"=>true,
            "text"=>"Pie chart showing figures of shipments which are soon to depart/arrive (Calculated from ETD/ETA).");
        $Management[] = array("id"=>6 ,
            "title"=>"Top 10 Origin/Destination",
            "switchValue"=>true,
            "text"=>"Figure of the top 10 origin/destination.",
            "title1"=>"Top 10 Origin",
            "title2"=>"Top 10 Destination");
        $Management[] = array("id"=>7 ,
            "title"=>"CO2e Emission by Origin (Top 10)",
            "switchValue"=>true,
            "text"=>"Figure of the CO2e Emission by origin.");
        $Management[] = array("id"=>8 ,
            "title"=>"CO2e Emission by Destination (Top 10)",
            "switchValue"=>true,
            "text"=>"Figure of the CO2e Emission by destination."); 
        return  $Management;   
    }

    /**
     * Destroy Session
     */
    public static function sessionDestroy() {
        session_destroy();
        setcookie(session_name(), '', time() - 3600);
        $_SESSION = array();
    }

    public static function retStationInfo($address_1,$address_2,$address_3,$address_4,$city,$state,$country,$zipcode){
        $stationInfo = "";
        if(!empty($address_1)){
            $stationInfo .= $address_1;
        }
        if(!empty($address_2)){
            $stationInfo .= " ".$address_2;
        }
        if(!empty($address_3)){
            $stationInfo .= " ".$address_3;
        }
        if(!empty($address_4)){
            $stationInfo .= " ".$address_4;
        }
        $temp_str  = "";
        if(!empty($city)){
            $temp_str .= $city." ";
        }
        if(!empty($state)){
            $temp_str .= $state." ";
        }
        if(!empty($zipcode)){
            $temp_str .= $zipcode." ";
        }
        if(!empty($country)){
            $temp_str .= $country." ";
        }
        if(!empty($temp_str)){
            return $stationInfo." ".trim($temp_str);
        }
        return $stationInfo;
    }

    public static function getInsertSqlNull($table_name, $values) {
        $field = "";
        $value = "";
        foreach ($values as $k => $v) {
            if ($k == 'tmp' || $k == 'action' || $k == 'operate' || $k == 'x' || $k == 'y') {
                continue;
            }
            if (is_array($v)) {
                $v = implode(",", $v);
            }
            if (empty($field)) {
                $field = $k;
                if (utils::checkExist($v, 'now()')) {
                    $value = $v;
                } elseif ($v == null) {
                    $value .= 'null';
                } elseif ($v == 'TRUE' || $v == 'FALSE') {
                    $value .= $v;
                } else {
                    $value = '\'' . common::check_input($v) . '\'';
                }
            } else {
                $field .= ',' . $k;
                if (utils::checkExist($v, 'now()'))
                    $value .= ', now()';
                elseif ($v == null) {
                    $value .= ',null';
                } else if ($v == 'TRUE' || $v == 'FALSE') {
                    $value .= ',' . $v;
                } else {
                    $value .= ', \'' . common::check_input($v) . '\'';
                }
            }
        }
        return 'insert into ' . $table_name . '(' . $field . ') values (' . $value . ')';
    }
}
?>