k_online_backend/service/ocean_booking.class.php

<?php

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

/**
 * Description of ocean
 *
 * @author Administrator
 */
class ocean_booking {

    private static $_ocean;

    function __construct() {  
       
    }

    public static function getInstance() {
        global $memory_limit;
        $memory_limit = ini_get("memory_limit");
        ini_set("memory_limit", '2048M');
        if (!self::$_ocean) {
            $c = __CLASS__;
            self::$_ocean = new $c;
        }
        return self::$_ocean;
    }

    public function ocean_booking() {
        $operate = utils::_get('operate');
        $operate = strtolower($operate);
        /*
         * index page
         */
        if (empty($operate)) {
            $data = array();
            if (_isCustomerLogin())
                $data['is_customer'] = "yes";
            else
                $data['is_customer'] = "no";
            //栏位信息
            $column = column::getInstance()->getDisplayColumn('Booking_Search');
            $BookingTableColumns = column::getInstance()->tableColumns('Booking_Search',$column);
            $data['BookingTableColumns'] = $BookingTableColumns;
            common::echo_json_encode(200,$data);
            exit();
        }
        /*
         * ocean order search
         */
        if ($operate == "search") {
            $this->_booking_search();
        }
        if ($operate == "setting_display") {
            column::getInstance()->settingDisplay('Booking_Search', 'main_new_version.php?action=ocean_booking');
        }
        if ($operate == "detail") {
            $this->_booking_detail();
        }
        if ($operate == "excel") {
            $this->_booking_excel();
        }
        if ($operate == "save_communication") {
            try {
                $content = $_POST["content"];
                $content = common::check_input($content);
                $text = $_POST["text"];
                $content = urldecode($content);
                $web_content = urldecode($text);

                $communication_cc = $_POST["communication_cc"];
                $serial_no = common::uuid();
                $email_uuid = $_POST["serial_no"];
                $add_by = _getLoginName();
                $refer_id = 0;
               
                $email = $this->getEmail($email_uuid);
                $from_email = "US.KApex.Online@kerryapex.com";
                $to_email = $email["email"];
                $cc_email = $_SESSION['ONLINE_USER']['email'] . ";ApexOnlineCommunication@apexshipping.com";
                $communication_cc = trim($communication_cc);
                if (!empty($communication_cc)) {
                    $communication_cc = common::check_input($communication_cc);
                    $cc_email .= ";" . $communication_cc;
                }


                $user_from = _getLoginName();
                $user_to = $email["name"];
                $user_cc = $_SESSION['ONLINE_USER']['first_name'];
                if (empty($user_cc)) {
                    $user_cc = _getLoginName();
                }
                $user_cc .= ";ApexOnlineCommunication";

                //邮件发送
                $poKey = "<br><br>Important note: when you reply this email, do not remove ApexOnlineCommunication@apexshipping.com<br>Below is system code, do not delete.<br>@@";
                $emailSql = "select encode(public.pgp_sym_encrypt('" . $serial_no . "','pom_key'), 'base64')::text;";
                $key = common::excuteOneSql($emailSql);

                $historyEmails = "";

                $title = "Communication from " . _getLoginName() . ", HBOL: " . $email["h_bol"] . ", Consignee: " . $email["consignee"];
                $emailContent = $content . $poKey . $key . ";" . "@@<br>For your convenience weblink: https://ra.kerryapex.com/<br><br>" . $historyEmails;

                common::excuteUpdateSql("insert into public.email_record (type,title,from_email,to_email,cc_email,content,insert_date)values('Communication','$title','$from_email','$to_email','$cc_email','$emailContent',now());");

                $rs = common::excuteUpdateSql("INSERT INTO public.online_ocean_communication(serial_no, email_uuid, content,web_content,user_from, user_to, user_cc, refer_id, add_by, add_time, cc_email) 
                VALUES ('$serial_no', '$email_uuid', '$emailContent','$web_content', '$user_from', '$to_email', '$cc_email', $refer_id, '$add_by', now(), '$communication_cc');");

                $emailRecords = $this->getCommunicationNew($email_uuid);
                common::echo_json_encode(200,array("msg" => "Sent Successfully", "emailRecords" => $emailRecords));
                exit();
            } catch (Exception $e) {
                common::echo_json_encode(500,array("msg" => "Sent Error."));
                exit();
            }
        }
    }

    private function _booking_search() {
        $cp = common::check_input($_POST ['cp']); //current_page
        $ps = common::check_input($_POST ['ps']); //ps
        if (empty($ps)){
            $ps = 50;
        }
        $sqlWhere = ' where ' . common::searchExtendHandNew("booking", $_SESSION["ONLINE_USER"]);
        $sqlWhere .= search::getInstance()->getSearchSQL("Booking_Search");

        //前端提交是数组
        if (!empty($_POST['filterTag']) && utils::count($_POST['filterTag']) < 4) {
            if (utils::count($_POST['filterTag']) == 1){
                $filterTag = $_POST['filterTag'][0];
            }else{
                $filterTag = utils::implode(",", $_POST['filterTag']);
            }
            $_sqlwhere = "1<>1";
            if (stripos($filterTag, "Confirmed") !== FALSE) {
                $_sqlwhere .= " or (bol_type = 'BOOKING' and status!='Cancelled')";
            }
            if (stripos($filterTag, "type2") !== FALSE) {
                $_sqlwhere .= " or (bol_type != 'BOOKING' and booking_no is not null and booking_no <> '')";
            }
            if (stripos($filterTag, "Cancelled") !== FALSE) {
                $_sqlwhere .= " or (status='Cancelled')";
            }
            if (stripos($filterTag, "Created") !== FALSE) {
                $_sqlwhere .= " or (bol_type != 'BOOKING' and (booking_no is null or booking_no = ''))";
            }
            if(strtolower($filterTag) <> "all" && !empty($filterTag)){
                $sqlWhere .= " and ($_sqlwhere)";
            }
        }
        //移除掉全文检索 但保留代码
        // if (!empty($_POST["_textSearch"])) {
        //     $sqlWhere .= " and text_search @@ (str_to_tsquery('" . common::check_input($_POST["_textSearch"]) . "'))";
        // }
        //处理reference类型的组合查询  Search booking No./HBL No./PO No./Carrier Booking No.
        if (!empty($_POST["_textSearch"])) {
            $textSearch_arr = $_POST['_textSearch'];
            if(!is_array($textSearch_arr)){
                $textSearch_arr = array($textSearch_arr);
            }
            $more_sql = "1<>1";
            foreach($textSearch_arr as $tsv){
                $more_sql .= " or  booking_no ilike '%" . common::check_input(trim($tsv)) . "%'";
                $more_sql .= " or  h_bol ilike '%" . common::check_input(trim($tsv)) . "%'";
                $more_sql .= " or  po_no ilike '%" . common::check_input(trim($tsv)) . "%'";
                $more_sql .= " or  carrier_booking ilike '%" . common::check_input(trim($tsv)) . "%'";
            }
            if ($more_sql <> "1<>1"){
                $sqlWhere .= " and ($more_sql)";
            } 
        }
        $rc = $_POST ['rc'];
        //这里都要查询，除非多传几个参数回来
        if ($rc == - 1 || true) {
            //查询ALL
            $sql = "SELECT count(1) from public.online_booking" . $sqlWhere;
            $rc = common::excuteOneSql($sql);
            error_log("online_booking_count_ALL_SQL: ".$sql);
            
            //查询Creatd
            $Creatd_sqlWhere = " and (1<>1 or (bol_type != 'BOOKING' and (booking_no is null or booking_no = '')))";
            $Creatd_sql = "SELECT count(1) from public.online_booking" . $sqlWhere.$Creatd_sqlWhere;
            $Creatd = common::excuteOneSql($Creatd_sql);
            error_log("online_booking_count_Creatd_SQL: ".$sql);

            //查询Confirmed
            $Confirmed_sqlWhere = " and (1<>1 or (bol_type = 'BOOKING' and status!='Cancelled'))";
            $Confirmed_sql = "SELECT count(1) from public.online_booking" . $sqlWhere.$Confirmed_sqlWhere;
            $Confirmed = common::excuteOneSql($Confirmed_sql);
            error_log("online_booking_count_Confirmed_SQL: ".$sql);

            //查询Concelled
            $Concelled_sqlWhere = " and (1<>1 or (status='Cancelled'))";
            $Concelled_sql = "SELECT count(1) from public.online_booking" . $sqlWhere.$Concelled_sqlWhere;
            $Concelled = common::excuteOneSql($Concelled_sql);
            error_log("online_booking_count_Concelled_SQL: ".$sql);

            //前端数据返回，不管有无数据
            if (!empty($_POST["filterTag"])) {
                $tagsList = array(array("name"=>"All","number"=>$rc,"type"=>"all","checked"=>utils::in_array('All', $_POST["filterTag"])? true : false),
                array("name"=>"Created","number"=>$Creatd,"type"=>"created","checked"=>utils::in_array('Created', $_POST["filterTag"])? true : false),
                array("name"=>"Confirmed","number"=>$Confirmed,"type"=>"confirmed","checked"=>utils::in_array('Confirmed', $_POST["filterTag"])? true : false),
                array("name"=>"Cancelled","number"=>$Concelled,"type"=>"cancelled","checked"=>utils::in_array('Cancelled', $_POST["filterTag"])? true : false));
            }else{
                //初始，前端有选择着带入选择
                $tagsList = array(array("name"=>"All","number"=>$rc,"type"=>"all","checked"=>true),
                array("name"=>"Created","number"=>$Creatd,"type"=>"created","checked"=>false),
                array("name"=>"Confirmed","number"=>$Confirmed,"type"=>"confirmed","checked"=>false),
                array("name"=>"Cancelled","number"=>$Concelled,"type"=>"cancelled","checked"=>false));
            }

            $TransportList = array(
                array("name"=>"Ocean Freight","number"=>$rc,"checked"=>false,"icon"=>"#icon-icon_ocean_b"));

            //现在下载交给前端，后台预先只返回全部字段的列,
            $allColumn = column::getInstance()->getDisplayColumnAllReomveDefault('Booking_Search');
            $allBookingColumns = column::getInstance()->tableColumns('Booking_Search',$allColumn);
        }
        $tp = ceil($rc / $ps);
        if ($rc > 0 ) {
            $order_by = " f_etd desc NULLS LAST, id";
            //TopOcean的不用考虑
            $ocean_dest_sql = "";
            //合并显示两个特殊字段 voyage_m_voyage/vessel_m_vessel
            if (!empty($_POST["_textSearch"])) {
                $sql = "SELECT order_from as _schemas, serial_no as __serial_no, created_time as __created_time, color,
                        m_voyage as __m_voyage,m_vessel as __m_vessel," .
                        column::getInstance()->getSearchSqlForDisplay('Booking_Search') . " from public.online_booking $ocean_dest_sql " . $sqlWhere . " "
                        . "order by $order_by limit " . $ps . " offset " . ($cp - 1) * $ps;       
            } else {
                $sql = "SELECT order_from as _schemas, serial_no as __serial_no, created_time as __created_time, color, 
                        f_voyage as __f_voyage, m_voyage as __m_voyage,f_vessel as __f_vessel, m_vessel as __m_vessel," .
                        column::getInstance()->getSearchSqlForDisplay('Booking_Search') . " from public.online_booking $ocean_dest_sql " . $sqlWhere . " order by $order_by limit " . $ps . " offset " . ($cp - 1) * $ps;
            }
            $rs = common::excuteListSql($sql);
            error_log("online_booking_search_SQL: ".$sql);

            //对查询的结果做特殊处理，比如要拼接某个值，合并值等
            foreach($rs as $index => $val) {
                //合并显示 vessel
                if(array_key_exists("f_vessel", $val)){
                    $rs[$index]["f_vessel"] = utils::outDisplayForMerge($val['f_vessel'],$val['__m_vessel']);
                }
                //合并显示 voyage
                if(array_key_exists("f_voyage", $val)){
                    $rs[$index]["f_voyage"] = utils::outDisplayForMerge($val['f_voyage'],$val['__m_voyage']);
                }
                //返回加密serial_no
                $rs[$index]["__serial_no"] = common::deCode($val['__serial_no'], 'E');

                //按新逻辑处理status,现在是管理员权限，客户权限少了一种状态，先设置默认值防止出错
                $status = "Created";
                if(!($val['color'] == "FFFF0000" || $val['color'] == "FF00FF00" || $val['color'] == "FF0000FF")){
                    $status = "Confirmed";
                }
                if ($val['color'] == "FF0000FF"){
                    $status = "Created";
                }
                if ($val['color'] == "FFFF0000"){
                    $status = "Cancelled";
                }
                $rs[$index]["Status"] = $status;
                $rs[$index]["Transportation Mode"] = "Ocean Freight";
            }
            
            $arrTmp = array('searchData' => $rs, 
                'tagsList' => $tagsList,
                'TransportList' => $TransportList,
                'allColums' => $allBookingColumns,
                'rc' => $rc,
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp,
                'tmp_search' => common::deCode($sql, 'E'),
                'type' => common::check_input($_POST ['_ntype']));
        } else {
            $arrTmp = array('searchData' => array(), 
                'tagsList' => $tagsList,
                'TransportList' => $TransportList,
                'allColums' => $allBookingColumns,
                'rc' => $rc,
                'ps' => $ps,
                'cp' => $cp,
                'tp' => $tp,
                'tmp_search' => "",
                'type' => common::check_input($_POST ['_ntype']));
        }

        common::echo_json_encode(200,$arrTmp);
        exit();
    }

    private function _booking_detail() {
        $serial_no = common::deCode($_GET['a'], 'D');
        $status = $_GET['status'];
        $_schemas = $_GET['_schemas'];
        if($_schemas == 'public'){
            $_schemas = "ocean";
        }
        $sql = "SELECT o.serial_no, o.booking_no as _booking_no, o.* ,sh.*, cn.* ,aa.*,dd.* from ocean o
            LEFT JOIN LATERAL ( SELECT company as cn_company,
                address_1 as cn_address_1,
                address_2 as cn_address_2,
                address_3 as cn_address_3,
                address_4 as cn_address_4,
                city as cn_city, state as cn_state, zipcode as cn_zipcode, country as cn_country,
                phone_1 as cn_phone
            FROM $_schemas.contacts c WHERE o.consignee::text = c.contact_id::text) cn ON true
            LEFT JOIN LATERAL ( SELECT company as sh_company,
                address_1 as sh_address_1,
                address_2 as sh_address_2,
                address_3 as sh_address_3,
                address_4 as sh_address_4,
                city as sh_city, state as sh_state, zipcode as sh_zipcode, country as sh_country,
                phone_1 as sh_phone
            FROM $_schemas.contacts c WHERE o.shipper::text = c.contact_id::text) sh ON true
            LEFT JOIN LATERAL ( SELECT company as aa_company,
                address_1 as aa_address_1,
                address_2 as aa_address_2,
                address_3 as aa_address_3,
                address_4 as aa_address_4,
                city as aa_city, state as aa_state, zipcode as aa_zipcode, country as aa_country,
                phone_1 as aa_phone
            FROM $_schemas.contacts c WHERE o.origin_station::text = c.contact_id::text) aa ON true
            LEFT JOIN LATERAL ( SELECT company as dd_company,
                address_1 as dd_address_1,
                address_2 as dd_address_2,
                address_3 as dd_address_3,
                address_4 as dd_address_4,
                city as dd_city, state as dd_state, zipcode as dd_zipcode, country as dd_country,
                phone_1 as dd_phone
            FROM $_schemas.contacts c WHERE o.destination_station::text = c.contact_id::text) dd ON true
        where lower(serial_no) = '" . strtolower($serial_no) . "'";
        $ocean = common::excuteObjectSql($sql);

        $vueData = $this->returnBookingDetailData($ocean,$status);

        //处理transportInfo信息数据
        $transportInfo = $vueData["transportInfo"];
        //处理basicInfo信息数据
        $basicInfo = $vueData["basicInfo"];
        //处理 拼接地址 ocean表单exp 字段无法精准分割电话和地址信息，只能从contacts表里查询
        $businessPartners = $vueData["businessPartners"];
        $marksAndDescription = $vueData["marksAndDescription"];
        //处理ocean_reference
        $ref_no = array();
        $ref_arr = common::excuteListSql("select * from ocean_reference where lower(serial_no) = '" . strtolower($serial_no) . "'");
        if(!empty($ref_arr)){
            foreach($ref_arr as  $ref) {
                $ref_no[] = array("label"=>$ref["ref_code"],"value"=>$ref["ref_value"]);
            }
        }
      
        //处理返回booking的 Container 信息数据
        $booking_container_column = array();
        $container_column = column::getInstance()->getDisplayColumnAll('Booking_Container');
        foreach($container_column as $cc){
            $booking_container_column[] =array("field" =>$cc['database_column_name'],"title" =>$cc['name'],"formatter" =>"","type" =>"normal");
        }
        if (!empty($ocean['_booking_no'])) {
            $sql = "SELECT " . column::getInstance()->getSearchSql('Booking_Container') . " from ocean_booking_container where lower(serial_no) = '" . strtolower($serial_no) . "'";
            $rss = common::excuteListSql($sql);
        } else {
            $sql = "SELECT " . column::getInstance()->getSearchSql('Booking_Container') . " from oc_container_booking_v where lower(serial_no) = '" . strtolower($serial_no) . "'";
            $rss = common::excuteListSql($sql);
        }
        $containers = array("container_column"=>$booking_container_column,"container_data" =>$rss);

        //packing
        $quantity_tolal = 0;
        $packages = "CTN";
        $g_weight_tolal = 0;
        $ch_weight_tolal = 0;
        $cbm_tolal = 0;
        foreach($rss as $pk => $pv){
            $quantity_tolal += $pv['qty'];
            $g_weight_tolal += $pv['kgs'];
            $ch_weight_tolal += $pv['kgs'];
            $cbm_tolal += $pv['cbm'];
            $packages = " ".$pv['unit'];
        }

        //$packing = array("Quantity/Unit"=>"47 CTN","G. Weight" => "480.25 KGS","Ch. Weight" => "689.26 KGS","Volume" => "3.801 CBM");
        $packing = array("Quantity/Unit"=>$quantity_tolal.$packages,"G. Weight" => $g_weight_tolal." KGS","Ch. Weight" => $ch_weight_tolal." LBS","Volume" => $cbm_tolal." CBM");
        
        //处理邮件信息返回
        $emailRecords = $this->getCommunicationNew($ocean["serial_no"]);
        $cc_email = common::excuteOneSql("select cc_email from public.online_ocean_communication where email_uuid='" . $ocean["serial_no"] . "' order by id desc limit 1");
        $email = $this->getEmail($ocean["serial_no"]);
        $email['cc_email'] = $cc_email;
        $email['emailRecords'] = $emailRecords;

        global $_COPYRIGHT;
        $data = array('transportInfo' => $transportInfo, 
                        'basicInfo' => $basicInfo,
                        'businessPartners' => $businessPartners,
                        'ref_no' => $ref_no,
                        'packing' => $packing,
                        'marksAndDescription' => $marksAndDescription,
                        'containers' => $containers,
                        'email' => $email,
                        'serial_no' => $serial_no,
                        '_schemas' => $_GET['_schemas'],
                        '__serial_no' => common::deCode($ocean['serial_no'], 'E'),
                        'copyright' =>$_COPYRIGHT);
        common::echo_json_encode(200,$data);                
        exit(); 
    }

    private function _booking_excel() {
        //现在下载交给前端，后台最后只返回全部字段的列,
        $allColumn = column::getInstance()->getDisplayColumnAll('Booking_Search');
        $allBookingColumns = column::getInstance()->tableColumns('Booking_Search',$allColumn);
        common::echo_json_encode(200,array("msg"=>"success","allColums" => $allBookingColumns));
        exit;
    }

    private function getEmail($serial_no) {
        return utils::getEmail($serial_no);
    }

    private function getCommunicationNew($serial_no) {
        $list = common::excuteListSql("select to_char(add_time, 'MM/dd/yyyy hh24:MI:ss') as add_times, * from public.online_ocean_communication where email_uuid='$serial_no' and refer_id = 0  order by id");
        $emialRecords =array();
        foreach ($list as $k => $v) {
            $msg =array();
            $msg["name"] = $v["add_by"];
            $msg["creatTime"] = $v["add_times"];
            $msg["content"] =  urldecode($v["web_content"]);
            $emialRecords[] = $msg;
        }
        return $emialRecords;
    }


    private 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;
    }

    private function returnBookingDetailData($ocean,$status){
        $data = array();
        //处理transportInfo信息数据
        $transportInfo = array("bookingNo." =>$ocean['booking_no'],"status"=>$status,"mode" => "","origin" =>$ocean['origin_station'],"destination" =>$ocean['destination_station'],
            "etd" =>$ocean['f_etd'],"atd" =>"","eta" =>$ocean['m_eta'],"ata" =>"");
        $data["transportInfo"] = $transportInfo;

        //处理basicInfo信息数据
        $vessel = utils::outDisplayForMerge($ocean['f_vessel'],$ocean['m_vessel']);
        $voyage = utils::outDisplayForMerge($ocean['f_voyage'],$ocean['m_voyage']);
        $basicInfo = array("bookingNo." =>$ocean['booking_no'],"HAWB/HBOL" => $ocean['h_bol'],"Carrier_Booking_No" =>$ocean['carrier_booking'],
            "PO_NO" =>$ocean['po_no'],"Vessel/Airline" =>$vessel,"Voyage/Filght" =>$voyage,
            "Incoterm" =>$ocean['incoterms'],"Service_Type" =>$ocean['service']);
        $data["basicInfo"] = $basicInfo;    

        //处理 拼接地址 ocean表单exp 字段无法精准分割电话和地址信息，只能从contacts表里查询
        $shipper_address = common::retStationInfo($ocean['sh_address_1'], $ocean['sh_address_2'], $ocean['sh_address_3'], $ocean['sh_address_4'], 
            $ocean['sh_city'], $ocean['sh_state'], $ocean['sh_zipcode'], $ocean['sh_country']);
        $consignee_address = common::retStationInfo($ocean['cn_address_1'], $ocean['cn_address_2'], $ocean['cn_address_3'], $ocean['cn_address_4'], 
            $ocean['cn_city'], $ocean['cn_state'], $ocean['cn_zipcode'], $ocean['cn_country']);
        $origin_address = common::retStationInfo($ocean['aa_address_1'], $ocean['aa_address_2'], $ocean['aa_address_3'], $ocean['aa_address_4'], 
            $ocean['aa_city'], $ocean['aa_state'], $ocean['aa_zipcode'], $ocean['aa_country']); 
        $destination_address = common::retStationInfo($ocean['dd_address_1'], $ocean['dd_address_2'], $ocean['dd_address_3'], $ocean['dd_address_4'], 
            $ocean['dd_city'], $ocean['dd_state'], $ocean['dd_zipcode'], $ocean['dd_country']);    

        $shipperPartners = array("company" =>$ocean['sh_company'],"address"=>$shipper_address,"phone"=>$ocean['sh_phone']);
        $consigneePartners = array("company" =>$ocean['cn_company'],"address"=>$consignee_address,"phone"=>$ocean['cn_phone']);
        $originPartners = array("company" =>$ocean['aa_company'],"address"=>$origin_address,"phone"=>$ocean['aa_phone']);
        $destinationPartners = array("company" =>$ocean['dd_company'],"address"=>$destination_address,"phone"=>$ocean['dd_phone']);
        $businessPartners = array("shipper"=>$shipperPartners,"consignee" => $consigneePartners,"origin" => $originPartners,"destination" => $destinationPartners);
        $data["businessPartners"] = $businessPartners;

        $marksAndDescription = array("marks"=>$ocean['marks'],"description"=>$ocean['description']);
        $data["marksAndDescription"] = $marksAndDescription;

        return $data;    
    }
}

?>