<?php

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

/**
 * Description of auto
 *
 * @author Administrator
 */
class ajax {

    private static $_ajax;

    public static function getInstance() {
        if (!self::$_ajax) {
            $c = __CLASS__;
            self::$_ajax = new $c;
        }
        return self::$_ajax;
    }

    public function run() {
        $time = time() - $_SESSION["LAST_OPERATE_TIME"];
        if ($time > $_SESSION['SESSION_TIMEOUT']) {
            exit(json_encode("session_time_out"));
        } else {
            $_SESSION["LAST_OPERATE_TIME"] = time();
        }
        $operate = utils::_get('operate');
        $operate = strtolower($operate);
        switch ($operate) {
            case 'save_setting_display':
                $this->save_setting_display();
                break;
            case 'autody':
                return json_encode($this->autody());
                break;
            case 'autoport':
                return json_encode($this->autoPort());
                break;        
            default :
                break;
        }
    }

    public function save_setting_display() {
        $ids = $_POST['ids'];
        if(!empty($ids)){
            $ss = utils::implode(",", $_POST['ids']);
            column::getInstance()->saveSettingDisplay(utils::implode(",", $_POST['ids']),"", $_POST['model_name']);
            $data = array(
                'msg' => 'success'
            );
            common::echo_json_encode(200,$data);
        }else{
            $data = array(
                'msg' => 'data error'
            );
            common::echo_json_encode(500,$data);
        }
    }

    //不查询全局的信息，查询该用户，最近3个月内输入过的信息自动补全
    public function autody(){
        $term = $_POST['term'];
        $type = $_POST['type'];
        $search_field = $_POST['search_field'];
        $search_mode = $_POST['search_mode'];
        
        $term = trim($term);
        if (strtolower($type) == "apex" || strtolower($type) == "contanct" || strtolower($type) == "sales"
            || strtolower($type) == "vessel" || strtolower($type) == "voyage") {
            if($search_mode == "tracking"){
                $sqlWhere = $this->getTrackingSearchWhere();
            }elseif($search_mode == "booking"){
                $sqlWhere = $this->getBookingSearchWhere();
            }

            //Notify Party Bill to Destination Operator 是tracking里的，但booking没有，所以不影响
            $search_field_mapping = array("Origin Agent"=>"origin as code",
                "Destination Agent"=>"agent as code",
                "Shipper Name"=>"shipper as code",
                "Consignee Name"=>"consignee as code",
                "Notify Party" =>"notify_party as code",
                "Bill to" =>"billto as code",
                "Destination Operator"=>"dest_op as code",
                "Sales"=>"sales_rep as code",
                "Voyage/Flight_booking"=>"f_voyage as code,m_voyage as code_2",
                "Vessel_booking"=>"f_vessel as code,m_vessel as code_2",
                "Voyage/Flight_tracking"=>"voyage as code,f_voyage as code_2",
                "Vessel_tracking"=>"vessel as code,f_vessel as code_2");

            $sql_where_mapping = array("Origin Agent"=>" and (origin ilike '%" . common::check_input($term) . "%')",
                "Destination Agent"=>" and (agent ilike '%" . common::check_input($term) . "%')",
                "Shipper Name"=>" and (shipper ilike '%" . common::check_input($term) . "%')",
                "Consignee Name"=>" and (consignee ilike '%" . common::check_input($term) . "%')",
                "Notify Party"=>" and (notify_party ilike 'v" . common::check_input($term) . "%')",
                "Bill to"=>" and (billto ilike '%" . common::check_input($term) . "%')",
                "Destination Operator"=>" and (dest_op ilike '%" . common::check_input($term) . "%')",
                "Sales"=>" and (sales_rep ilike '%" . common::check_input($term) . "%')",
                "Voyage/Flight_booking"=>" and (f_voyage ilike '%" . common::check_input($term) . "%' or m_voyage ilike '%" . common::check_input($term) . "%')",
                "Vessel_booking"=>" and (f_vessel ilike '%" . common::check_input($term) . "%' or m_vessel ilike '%" . common::check_input($term) . "%')",
                "Voyage/Flight_tracking"=>" and (voyage ilike '%" . common::check_input($term) . "%' or f_voyage ilike '%" . common::check_input($term) . "%')",
                "Vessel_tracking"=>" and (vessel ilike '%" . common::check_input($term) . "%' or f_vessel ilike '%" . common::check_input($term) . "%')");    

            //特殊处理一下合并Voyage和Vessel
            $_search_field  = $search_field;
            if ($search_field == "Voyage/Flight" || $search_field == "Vessel"){
                $_str = $search_mode == "tracking" ? "tracking" : "booking";
                $_search_field  = $search_field ."_".$_str;
            }    
            $str = $search_field_mapping[$_search_field];
            $sql_where = $sql_where_mapping[$_search_field];

            $table = $search_mode == "tracking" ? "public.online_ocean " : "public.online_booking ";

            if($search_mode == "booking"){
                $order_by = " f_etd desc NULLS LAST";
            }else{
                $order_by = " etd desc NULLS LAST";
                if (_isCustomerLogin()) {
                    $order_by = " eta desc NULLS LAST";
                }
            }
     
            $sql = "select DISTINCT * from (select $str from $table $sqlWhere $sql_where order by $order_by ) as temp";
            $sql .= " order by code limit 20";
            error_log($sql);
            $rs = common::excuteListSql($sql);
        }
        $rs = common::excuteListSql($sql);
        $array = array();
        foreach($rs as $v){
            if(!empty($v['code'])){
                $array[] = $v['code'];
            }
            if ($search_field == "Voyage/Flight" || $search_field == "Vessel"){
                $array[] = $v['code_2'];
            }
        }
        $array = utils::removeDuplicateArray($array);
        common::echo_json_encode(200,$array);
        exit();
    }

    public function autoPort() {
        $cp = common::check_input($_POST ['cp']); //current_page
        $ps = common::check_input($_POST ['ps']); //ps
        if (empty($ps))
            $ps = 20;

        $term = $_POST['term'];
        $term = trim($term);
        $search_field = $_POST['search_field'];
        $search_mode = $_POST['search_mode'];
        $_sql_arr = $this->getPortsUncity($search_field,$search_mode,$term);

        $rc = $_POST ['rc'];
        if ($rc == - 1) {
            $sql = $_sql_arr['count_sql'];
            $rc = common::excuteOneSql($sql);
            error_log($sql);
        }
        $tp = ceil($rc / $ps);
        if ($rc > 0) {
            $sql = $_sql_arr['sql'];
            $sql = "select * from ($sql) as temp ";
            $sql .= " order by city limit " . $ps . " offset " . ($cp - 1) * $ps;
            
            $rs = common::excuteListSql($sql);
            //前端要加上id 从1开始
            $retData = array();
            $i = 0;
            foreach($rs as $key => $val){
                if($search_field == "Port of Loading"){
                    if(!empty($val['uncode'])){
                        $val["id"] = $i +1;
                        $retData[] = $val;
                        $i = $i +1;
                    }
                }else{
                    if(!empty($val['city'])){
                        $val["id"] = $i +1;
                        $retData[] = $val;
                        $i = $i +1;
                    }
                }   
            }
            $arrTmp = array('searchData' => $retData, 
                    'rc' => $rc,
                    'ps' => $ps,
                    'cp' => $cp,
                    'tp' => $tp);
            common::echo_json_encode(200,$arrTmp);
            exit();
        }else{
            $arrTmp = array('searchData' => array(), 'rc' => 0);
            common::echo_json_encode(200, $arrTmp);
            exit();
        }
    }

    public function autoPort_old() {
        $cp = common::check_input($_POST ['cp']); //current_page
        $ps = common::check_input($_POST ['ps']); //ps
        if (empty($ps))
            $ps = 20;

        $term = $_POST['term'];
        $term = trim($term);
        $_curr_schemas = $_POST['model'];
        if (empty($_curr_schemas)) {
            $_curr_schemas = $_SESSION['ONLINE_USER']['main_schemas'];
            if (empty($_curr_schemas)) {
                $_curr_schemas = "public";
            }
        }

        $rc = $_POST ['rc'];
        if ($rc == - 1) {
            $sql = "SELECT count(1) from $_curr_schemas.ports where (isoname ilike '" . common::check_input($term) . "%' 
                or uncity ilike '" . common::check_input($term) . "%' or uncode ilike '" . common::check_input($term) . "%')";
            $rc = common::excuteOneSql($sql);
        }
        $tp = ceil($rc / $ps);
        if ($rc > 0) {
            $sql = "SELECT isoname as country, uncity as city,uncode from $_curr_schemas.ports where (isoname ilike '" . common::check_input($term) . "%' 
                    or uncity ilike '" . common::check_input($term) . "%' or uncode ilike '" . common::check_input($term) . "%')";
            $sql .= " order by code limit " . $ps . " offset " . ($cp - 1) * $ps;

            $rs = common::excuteListSql($sql);
            //前端要加上id 从1开始
            $retData = array();
            foreach($rs as $key => $val){
                if(!empty($val['country'])){
                    $val["id"] = $key +1;
                    $retData[] = $val;
                }
            }
            $arrTmp = array('searchData' => $retData, 
                    'rc' => $rc,
                    'ps' => $ps,
                    'cp' => $cp,
                    'tp' => $tp);
            common::echo_json_encode(200,$arrTmp);
            exit();
        }else{
            $arrTmp = array('searchData' => array());
            common::echo_json_encode(200, $arrTmp);
            exit();
        }
    }

    public function autody_old() {
        $term = $_POST['term'];
        $type = $_POST['type'];
        
        $term = trim($term);
        if (strtolower($type) == "apex") {
            $_curr_schemas = $_POST['model'];
            if (empty($_curr_schemas)) {
                $_curr_schemas = $_SESSION['ONLINE_USER']['main_schemas'];
                if (empty($_curr_schemas)) {
                    $_curr_schemas = "public";
                }
            }
            if ($_curr_schemas == "public") {
                $sql = "select contact_id as code from ocean.contacts where ('Apex-DST'=any(regexp_split_to_array(contact_type::text, ';'::text))"
                        . " or 'Apex-AGT'=any(regexp_split_to_array(contact_type::text, ';'::text))) "
                        . "and active=true and coalesce(hblcode, '')<>'' and  (contact_id ilike '" . common::check_input($term) . "%' or company ilike '" . common::check_input($term) . "%')";
            } else {
                $sql = "select contact_id as code from $_curr_schemas.contacts where ('Apex-DST'=any(regexp_split_to_array(contact_type::text, ';'::text))"
                        . " or 'Apex-AGT'=any(regexp_split_to_array(contact_type::text, ';'::text))) "
                        . "and active=true and coalesce(hblcode, '')<>'' and  (contact_id ilike '" . common::check_input($term) . "%' or company ilike '" . common::check_input($term) . "%')";
            }
            $sql .= " order by contact_id limit 20";
            $rs = common::excuteListSql($sql);
        }

        //Shipper 和Consignee  的查询,但是表contact_v ，从ocean和air. 如果不做区分的话，可以上面的apex的Origin Agent/Destination Agent，合并
        if (strtolower($type) == "contanct") {
            $sql = "SELECT company as code from contact_v where active = true and (contact_id ilike '" . common::check_input($term) . "%' or company ilike '" . common::check_input($term) . "%')";
            $sql .= " order by contact_id limit 20";
            $rs = common::excuteListSql($sql); 
        }

        if (strtolower($type) == "sales") {
            $_curr_schemas = $_POST['model'];
            if (empty($_curr_schemas)) {
                $_curr_schemas = $_SESSION['ONLINE_USER']['main_schemas'];
                if (empty($_curr_schemas)) {
                    $_curr_schemas = "public";
                }
            }
            $sql = "SELECT salesopcode as code from $_curr_schemas.employee where salesopcode is not null and salesopcode !='' and (salesopcode ilike '" . common::check_input($term) . "%' or employee_id ilike '" . common::check_input($term) . "%')";
            $sql .= " order by salesopcode limit 20";
        }

        if (strtolower($type) == "vessel") {
            $vessel = $_POST['vessel'];
            $sql = "SELECT carrier, vessel as code, flag from vessel_infor where vessel ilike '" . common::check_input($vessel) . "%'";
            $sql .= " order by vessel limit 20";
        }

        $rs = common::excuteListSql($sql);
        $array = array();
        foreach($rs as $v){
            if(!empty($v['code'])){
                $array[] = $v['code'];
            }
        }
        common::echo_json_encode(200,$array);
        exit();
    }

    public function getTrackingSearchWhere(){
        $sqlWhere = ' where ' . common::searchExtendHandNew("ocean", $_SESSION["ONLINE_USER"]);
        $sqlWhere .= search::getInstance()->getSearchSQL("Ocean_Search");
        
        if (!empty($_REQUEST["_reportRef"])) {
            $_reportType = $_REQUEST["_reportType"];
            $_reportRef = $_REQUEST["_reportRef"];
            if ($_reportType == "r3") {
                if ($_reportRef == "Previous 7 Days") {
                    $sqlWhere .= " and eta>(current_date-interval '7 days') and eta<current_date";
                } else if ($_reportRef == "0 Day") {
                    $sqlWhere .= " and eta=current_date";
                } else if ($_reportRef == "1-2 Days") {
                    $sqlWhere .= " and eta>current_date and eta<=(current_date+interval '2 days')";
                } else if ($_reportRef == "3-6 Days") {
                    $sqlWhere .= " and eta>(current_date+interval '2 days') and eta<=(current_date+interval '6 days')";
                } else if ($_reportRef == "7 Days") {
                    $sqlWhere .= " and eta>(current_date+interval '6 days')";
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "r4") {
                if ($_reportRef == "Previous 7 Days") {
                    $sqlWhere .= " and etd>(current_date-interval '7 days') and etd<current_date";
                } else if ($_reportRef == "0 Day") {
                    $sqlWhere .= " and etd=current_date";
                } else if ($_reportRef == "1-2 Days") {
                    $sqlWhere .= " and etd>current_date and etd<=(current_date+interval '2 days')";
                }  else if ($_reportRef == "3-6 Days") {
                    $sqlWhere .= " and etd>(current_date+interval '2 days') and etd<=(current_date+interval '6 days')";
                } else if ($_reportRef == "7 Days") {
                    $sqlWhere .= " and etd>(current_date+interval '6 days')";
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "ata_r3") {
                if ($_reportRef == "0 Day") {
                    $sqlWhere .= " and ata >= etd and (ata-etd) >= 0 and (ata-etd) < 1";
                } else if ($_reportRef == "1-2 Days") {
                    $sqlWhere .= " and ata >= etd and (ata-etd) >= 1 and (ata-etd) < 2";
                }  else if ($_reportRef == "3-6 Days") {
                    $sqlWhere .= " and ata >= etd and (ata-etd) >= 2 and (ata-etd) < 6";
                } else if ($_reportRef == "7 Days") {
                    $sqlWhere .= " and ata >= etd and (ata-etd) >= 6";
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "atd_r4") {
                if ($_reportRef == "0 Day") {
                    $sqlWhere .= " and atd >= etd and (atd-etd) >= 0 and (atd-etd) < 1";
                } else if ($_reportRef == "1-2 Days") {
                    $sqlWhere .= " and atd >= etd and (atd-etd) >= 1 and (atd-etd) < 2";
                }  else if ($_reportRef == "3-6 Days") {
                    $sqlWhere .= " and atd >= etd and (atd-etd) >= 2 and (atd-etd) < 6";
                } else if ($_reportRef == "7 Days") {
                    $sqlWhere .= " and atd >= etd and (atd-etd) >= 6 ";
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "top") {
                if (!empty($_reportRef)) {
                    $_reportStationType = $_REQUEST["_reportStationType"];
                            
                    if($_reportStationType == 'shippr_uncode'){
                        $sqlWhere = " and shippr_uncode  = '$_reportRef'";
                    }
                    if($_reportStationType == 'fport_of_loading_un'){
                        $sqlWhere = " and fport_of_loading_un  = '$_reportRef'";
                    }
                    if($_reportStationType == 'consignee_uncode'){
                        $sqlWhere .= " and  consignee_uncode = '$_reportRef'";
                    }
                    if($_reportStationType == 'mport_of_discharge_un'){
                        $sqlWhere .= " and  mport_of_discharge_un = '$_reportRef'";
                    }      
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else if ($_reportType == "co2e") {
                $_reportDataType = $_REQUEST["_reportDataType"];
                        //查询sea 其他的为空
                if (!empty($_reportRef) && $_reportDataType == "sea") {
                    $_reportStationType = $_REQUEST["_reportStationType"];
                    if($_reportStationType == 'origin'){
                        $sqlWhere .= " and  shippr_uncode = '$_reportRef'";
                    }else{
                        $sqlWhere .= " and  consignee_uncode = '$_reportRef'";
                    }
                            
                } else {
                    $sqlWhere .= " and 1<>1";
                }
            } else {
                $_reportRef = explode("-", $_REQUEST["_reportRef"]);
                $_reportRefb_date = $_REQUEST["_reportRefb_date"];
                $bdate = common::excuteOneSql("select to_date('$_reportRefb_date','mm/YYYY')");
                $_reportRefe_date = $_REQUEST["_reportRefe_date"];
                $edate = common::excuteOneSql("select to_date('$_reportRefe_date','mm/YYYY') + interval '1 month' - interval '1 day'");
                //$sqlWhere .= " and eta>='$bdate' and eta<='$edate'";
                if ($_REQUEST["_reportRef"] == "Over 80 Days") {
                    $sqlWhere .= " and (eta-etd)>80";
                } else {
                    $sqlWhere .= " and (eta-etd)>" . $_reportRef[0] . " and (eta-etd)<=" . substr($_reportRef[1], 0, 2);
                }
            }
        }
                //处理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)";
            } 
        }
        return $sqlWhere;
    }

    public function getBookingSearchWhere(){
        $sqlWhere = ' where ' . common::searchExtendHandNew("booking", $_SESSION["ONLINE_USER"]);
        $sqlWhere .= search::getInstance()->getSearchSQL("Booking_Search");
        //新版要排除type2情况，总数才会对的上
        $sqlWhere .= " and (not(bol_type != 'BOOKING' and booking_no is not null and booking_no <> ''))";
        //处理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)";
            } 
        } 
        return $sqlWhere;
    }

    PUBLIC function getPortsUncity($search_field,$search_mode,$term){
        //uncode 前两位便是 isoname，不用模糊查询这个了
        if($search_field == "Origin" && $search_mode == "booking"){
            $sqlWhere = $this->getBookingSearchWhere();
            $count_sql = "select COUNT(DISTINCT shipper_city)
                from public.online_booking $sqlWhere 
                    and COALESCE(shippr_uncode)<>''
                    and  (shipper_city ilike '%" . common::check_input($term) . "%' 
                    or shippr_uncode ilike '%" . common::check_input($term) . "%') ";

            $sql = "select DISTINCT ON (shipper_city) left(shippr_uncode, 2) as country,shipper_city as city, shippr_uncode as uncode 
                from public.online_booking $sqlWhere 
                    and COALESCE(shippr_uncode)<>''
                    and  (shipper_city ilike '%" . common::check_input($term) . "%' 
                    or shippr_uncode ilike '%" . common::check_input($term) . "%') ";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);      
        }

        if($search_field == "Destination" && $search_mode == "booking"){
            $sqlWhere = $this->getBookingSearchWhere();
            $count_sql = "select count(DISTINCT consignee_city)
                from public.online_booking $sqlWhere
                    and COALESCE(consignee_uncode)<>'' 
                    and  (consignee_city ilike '%" . common::check_input($term) . "%' 
                    or consignee_uncode ilike '%" . common::check_input($term) . "%') ";

            $sql = "select DISTINCT ON (consignee_city) left(consignee_uncode, 2) as country,consignee_city as city, consignee_uncode as uncode 
                from public.online_booking $sqlWhere
                    and COALESCE(consignee_uncode)<>'' 
                    and  (consignee_city ilike '%" . common::check_input($term) . "%' 
                    or consignee_uncode ilike '%" . common::check_input($term) . "%')";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);
        }

        if($search_field == "Place of Receipt" && $search_mode == "booking"){
            $sqlWhere = $this->getBookingSearchWhere();
            $count_sql = "select COUNT(DISTINCT place_of_receipt_exp)
                from public.online_booking $sqlWhere
                    and COALESCE(place_of_receipt_uncode)<>'' 
                    and  (place_of_receipt_exp ilike '%" . common::check_input($term) . "%' 
                    or place_of_receipt_uncode ilike '%" . common::check_input($term) . "%')";

            $sql = "select DISTINCT ON (place_of_receipt_exp) left(place_of_receipt_uncode, 2) as country,place_of_receipt_exp as city, place_of_receipt_uncode as uncode 
                from public.online_booking $sqlWhere
                    and COALESCE(place_of_receipt_uncode)<>'' 
                    and  (place_of_receipt_exp ilike '%" . common::check_input($term) . "%' 
                    or place_of_receipt_uncode ilike '%" . common::check_input($term) . "%')";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);
        }

        if($search_field == "Port of Loading" && $search_mode == "booking"){
            $sqlWhere = $this->getBookingSearchWhere();
            $count_sql = "select COUNT(DISTINCT fport_of_loading_exp)
                from public.online_booking $sqlWhere
                    and COALESCE(fport_of_loading_uncode)<>''  
                    and  (fport_of_loading_exp ilike '%" . common::check_input($term) . "%' 
                    or fport_of_loading_uncode ilike '%" . common::check_input($term) . "%')";
            
            $sql = "select DISTINCT ON (fport_of_loading_exp) left(fport_of_loading_uncode, 2) as country,fport_of_loading_exp as city, fport_of_loading_uncode as uncode 
                from public.online_booking $sqlWhere
                    and COALESCE(fport_of_loading_uncode)<>'' 
                    and  (fport_of_loading_exp ilike '%" . common::check_input($term) . "%' 
                    or fport_of_loading_uncode ilike '%" . common::check_input($term) . "%')";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);
        }

        if($search_field == "Place of delivery" && $search_mode == "booking"){
            $sqlWhere = $this->getBookingSearchWhere();
            $count_sql = "select COUNT(DISTINCT place_of_delivery_exp)
                from public.online_booking $sqlWhere
                    and COALESCE(place_of_delivery_uncode)<>'' 
                    and  (place_of_delivery_exp ilike '%" . common::check_input($term) . "%' 
                    or place_of_delivery_uncode ilike '%" . common::check_input($term) . "%')";

            $sql = "select DISTINCT ON (place_of_delivery_exp) left(place_of_delivery_uncode, 2) as country,place_of_delivery_exp as city, place_of_delivery_uncode as uncode
                from public.online_booking $sqlWhere
                    and COALESCE(place_of_delivery_uncode)<>'' 
                    and  (place_of_delivery_exp ilike '%" . common::check_input($term) . "%' 
                    or place_of_delivery_uncode ilike '%" . common::check_input($term) . "%')";

            return  array("count_sql"=>$count_sql,"sql"=>$sql);        
        }

        //tracking
        if($search_field == "Origin" && $search_mode == "tracking"){
            $sqlWhere = $this->getTrackingSearchWhere();
          
            $count_sql = "select COUNT(DISTINCT city) from (select left(shippr_uncode, 2) as country,shipper_city as city, shippr_uncode as uncode
                from public.online_ocean $sqlWhere 
                    and COALESCE(shippr_uncode)<>''
                    and  (shipper_city ilike '%" . common::check_input($term) . "%' 
                    or shippr_uncode ilike '%" . common::check_input($term) . "%') 
                union
                    select left(consignee_uncode, 2) as country,consignee_city as city, consignee_uncode as uncode
                from public.online_ocean $sqlWhere
                    and COALESCE(consignee_uncode)<>'' 
                    and  (consignee_city ilike '%" . common::check_input($term) . "%' 
                    or consignee_uncode ilike '%" . common::check_input($term) . "%')) as temp";

            $sql = "select * from(select DISTINCT ON (shipper_city)  left(shippr_uncode, 2) as country,shipper_city as city, shippr_uncode as uncode
                from public.online_ocean $sqlWhere
                    and COALESCE(shippr_uncode)<>'' 
                    and  (shipper_city ilike '%" . common::check_input($term) . "%' 
                    or shippr_uncode ilike '%" . common::check_input($term) . "%') 
                union
                    select DISTINCT ON (consignee_city) left(consignee_uncode, 2) as country,consignee_city as city, consignee_uncode as uncode
                from public.online_ocean $sqlWhere
                    and COALESCE(consignee_uncode)<>''  
                    and  (consignee_city ilike '%" . common::check_input($term) . "%' 
                    or consignee_uncode ilike '%" . common::check_input($term) . "%')) as temp";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);        
        }

        if($search_field == "Destination" && $search_mode == "tracking"){
            $sqlWhere = $this->getTrackingSearchWhere();
            $count_sql = "select COUNT(DISTINCT final_desination_exp)
                from public.online_ocean $sqlWhere
                    and COALESCE(final_desination_uncode)<>'' 
                    and  (final_desination_exp ilike '%" . common::check_input($term) . "%' 
                    or final_desination_uncode ilike '%" . common::check_input($term) . "%')";

            $sql = "select DISTINCT ON (final_desination_exp) left(final_desination_uncode, 2) as country,final_desination_exp as city, final_desination_uncode as uncode
                from public.online_ocean $sqlWhere
                    and COALESCE(final_desination_uncode)<>'' 
                    and  (final_desination_exp ilike '%" . common::check_input($term) . "%' 
                    or final_desination_uncode ilike '%" . common::check_input($term) . "%')";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);             
        }

        if($search_field == "Place of Receipt" && $search_mode == "tracking"){
            $sqlWhere = $this->getTrackingSearchWhere();
            $count_sql = "select COUNT(DISTINCT place_of_receipt_exp)
                from public.online_ocean $sqlWhere
                    and COALESCE(place_of_receipt_un)<>'' 
                    and  (place_of_receipt_exp ilike '%" . common::check_input($term) . "%' 
                    or place_of_receipt_un ilike '%" . common::check_input($term) . "%')";

            $sql = "select DISTINCT ON (place_of_receipt_exp) left(place_of_receipt_un, 2) as country,place_of_receipt_exp as city, place_of_receipt_un as uncode
                from public.online_ocean $sqlWhere
                    and COALESCE(place_of_receipt_un)<>'' 
                    and  (place_of_receipt_exp ilike '%" . common::check_input($term) . "%' 
                    or place_of_receipt_un ilike '%" . common::check_input($term) . "%')";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);          
        }

        if($search_field == "Port of Loading" && $search_mode == "tracking"){
            $sqlWhere = $this->getTrackingSearchWhere();
            $count_sql = "select COUNT(DISTINCT port_of_loading)
                from public.online_ocean $sqlWhere 
                    and COALESCE(fport_of_loading_un)<>''
                    and  (port_of_loading ilike '%" . common::check_input($term) . "%' 
                    or fport_of_loading_un ilike '%" . common::check_input($term) . "%')";

            $sql = "select DISTINCT ON (port_of_loading) left(fport_of_loading_un, 2) as country,port_of_loading as city, fport_of_loading_un as uncode
                from public.online_ocean $sqlWhere
                    and COALESCE(fport_of_loading_un)<>'' 
                    and  (port_of_loading ilike '%" . common::check_input($term) . "%' 
                    or fport_of_loading_un ilike '%" . common::check_input($term) . "%')";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);
        }

        if($search_field == "Place of Discharge" && $search_mode == "tracking"){
            $sqlWhere = $this->getTrackingSearchWhere();
            $count_sql = "select COUNT(DISTINCT port_of_discharge)
                from public.online_ocean $sqlWhere
                    and COALESCE(mport_of_discharge_un)<>'' 
                    and  (port_of_discharge ilike '%" . common::check_input($term) . "%' 
                    or mport_of_discharge_un ilike '%" . common::check_input($term) . "%')";

            $sql = "select DISTINCT ON (port_of_discharge) left(mport_of_discharge_un, 2) as country,port_of_discharge as city, mport_of_discharge_un as uncode
                from public.online_ocean $sqlWhere 
                    and COALESCE(mport_of_discharge_un)<>''
                    and  (port_of_discharge ilike '%" . common::check_input($term) . "%' 
                    or mport_of_discharge_un ilike '%" . common::check_input($term) . "%')";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);        
        }

        if($search_field == "Place of delivery" && $search_mode == "tracking"){
            $sqlWhere = $this->getTrackingSearchWhere();
            $count_sql = "select COUNT(DISTINCT place_of_delivery_exp)
                from public.online_ocean $sqlWhere
                    and COALESCE(place_of_delivery_un)<>'' 
                    and  (place_of_delivery_exp ilike '%" . common::check_input($term) . "%' 
                    or place_of_delivery_un ilike '%" . common::check_input($term) . "%')";

            $sql = "select DISTINCT ON (place_of_delivery_exp) left(place_of_delivery_un, 2) as country,place_of_delivery_exp as city, place_of_delivery_un as uncode
                from public.online_ocean $sqlWhere
                    and COALESCE(place_of_delivery_un)<>'' 
                    and  (place_of_delivery_exp ilike '%" . common::check_input($term) . "%' 
                    or place_of_delivery_un ilike '%" . common::check_input($term) . "%')";
            return  array("count_sql"=>$count_sql,"sql"=>$sql);        
        }
    }
}

?>