<?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.online_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($_REQUEST["action"] == "opreation_log" 
            || 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;
        }
        $user_type = _isCustomerLogin() ? "Customer" : "Employee";
        if($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "tracking_checked"){
            //public tracking_checked 的user name 记录对应IP 地址
            $user_name = common::ip();
        } elseif($_REQUEST["action"] == "login" && $_REQUEST["operate"] == "do_login"){
            $user_name = $_REQUEST["uname"];
        } else{
            $user_name = _getLoginName();
        }

        $operateInfo = utils::getPageByAction($_REQUEST["action"],$_REQUEST["operate"],$_REQUEST["model_name"]);
        $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,$model_name){
        //取消
        $operationConvertName = array(
            "login=do_login" => array("page" =>"Login","operate"=>"Login"),
            "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"),
            "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"),

            "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"),
            "ocean_order=detail" => array("page" =>"Tracking","operate"=>"Open Detailed Page"),
            "ocean_order=excel" => array("page" =>"Tracking","operate"=>"Download"),
            "ocean_booking=save_communication" => array("page" =>"Tracking","operate"=>"Send Email"),
            "ocean_booking=ams_isf_log" => array("page" =>"Tracking","operate"=>"AMS/ISF"),

            "password=" => array("page" =>"Profile","operate"=>"Change password"));
        if($action == "ajax" && $operate == "save_setting_display"){
            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($action == "login" && $operate == "logout"){
            $detail = 'User Logout';
        }
        if($action == "login" && $operate == "tracking_checked"){
            $detail = 'Join public tracking action';
        }
        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" ? "online_booking" : "online_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 = "AMS/ISF";  
        }
        if(($action == "ocean_booking" || $action == "ocean_order") && $operate == "excel"){
            $detail = "Filter_condition:" . $_REQUEST['excel_filter_condition']." Selected Fields:". $_REQUEST['selected_fields'];
        }
        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 static function single_operation_log_save($page,$operation,$operation_detail){
        $user_type = _isCustomerLogin() ? "Customer" : "Employee";
        $user_name = _getLoginName();
        if(!isset($_SESSION['ONLINE_USER'])){
            $user_type = "";
            $user_name  = "No Login Record";
        }
        $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 _getSql($ids, $type,$shipment_mode) {
        $ids_arr = explode(',', $ids);
        $sql = "";
        if($type == "co2e_orgin"){
            $str = "SUM(COALESCE(carbon_emission,0)::numeric(12,10)) as catnum ";
            $filed = "shippr_uncode";
        } else {
            $str = "SUM(COALESCE(carbon_emission,0)::numeric(12,10)) as catnum ";
            $filed = "consignee_uncode";
        }

        $shipment_mode_where = " 1=1 ";
        //这里处理为空，目前没有数据
        if($shipment_mode == "air" || $shipment_mode == "road"){
            $shipment_mode_where = " 1<>1 ";
        }
        foreach ($ids_arr as $value) {
            if (!empty($value)) {
                if (empty($sql)) {
                    $sql .= "SELECT $str FROM online_ocean  where $shipment_mode_where and $filed = '$value'";
                } else {
                    $sql .= " union all SELECT $str from online_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;
    }
}
?>