k_online_backend/service/search.class.php

<?php

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

class search {

    private static $_search;

    public static function getInstance() {
        if (!self::$_search) {
            $c = __CLASS__;
            self::$_search = new $c;
        }
        return self::$_search;
    }
    
     public function getDisplaySearch($model_name, $ids = "") {
        $rs = null;
        if (!empty($ids)) {
            $rs = $ids;
        } else {
            $sql = "select ids from public.ra_online_search_config where lower(login_name)='" . strtolower(_getLoginName()) . "' and lower(model_name) = '" . strtolower($model_name) . "'";
            $rs = common::excuteOneSql($sql);
            if (empty($rs)) {
                $sql = "select array_to_string(array(select id from public.ra_online_search where active = true and default_display = true and lower(model_name) = '" . strtolower($model_name) . "'";
                if (_isCustomerLogin())
                    $sql .= " and customer_display = true";
                $sql .= " order by order_by asc, search_name asc), ',')";
                $rs = common::excuteOneSql($sql);
            }
        }
        $result = "";
        $sql = $this->_getSql($rs, "html_value, value_name, search_condition, value_name");
        $rss = common::excuteListSql($sql);
        foreach ($rss as $key => $value) {
            if ($value['search_condition'] == "year") {
                $result .= '<div class="modules3">Year : <input  name="' . $value['value_name'] . '_start" id="' . $value['value_name'] . '_start" type="text" oninput = "value=value.replace(/[^\d]/g,\'\')" value=""><div class="line"> - </div>
                    <input name="' . $value['value_name'] . '_end" id="' . $value['value_name'] . '_end" type="text" oninput = "value=value.replace(/[^\d]/g,\'\')" value=""></div>';
            } else {
                $result .= $value['html_value'];
            }
        }
        return $result;
    }


    public function _getSql($ids, $str) {
        $ids_arr = explode(',', $ids);
        $sql = "";
        foreach ($ids_arr as $value) {
            if (!empty($value)) {
                if (empty($sql)) {
                    $sql .= "SELECT $str FROM public.ra_online_search_cso where id = " . $value;
                    if (_isCustomerLogin())
                        $sql .= ' and customer_display = true';
                } else {
                    $sql .= " union all SELECT $str FROM public.ra_online_search_cso where id = " . $value;
                    if (_isCustomerLogin())
                        $sql .= ' and customer_display = true';
                }
            }
        }
        return $sql;
    }

    public function saveSettingDisplay($newid, $model_name) {
        $login_name = _getLoginName();
        if ($this->checkUserDisplaySetting($model_name) === TRUE) {
            $sql = "UPDATE public.ra_online_search_config SET ids = '" . common::check_input($newid) . "' WHERE lower(model_name) = '" . common::check_input(strtolower($model_name)) . "' AND lower(login_name) = '" . common::check_input(strtolower($login_name)) . "'";
        } else {
            $sql = "INSERT INTO public.ra_online_search_config (login_name, model_name, ids) VALUES ('" . common::check_input($login_name) . "', '" . common::check_input($model_name) . "', '" . common::check_input($newid) . "')";
        }
        common::excuteUpdateSql($sql);
    }

    /*
     * check user had setting display
     */

    public function checkUserDisplaySetting($model_name) {
        $login_name = _getLoginName();
        $sql = "select ids from public.ra_online_search_config where lower(login_name)='" . strtolower($login_name) . "' and lower(model_name) = '" . strtolower($model_name) . "'"; //check login_name is setting
        $rs = common::excuteObjectSql($sql);
        if (!empty($rs)) {
            return TRUE;
        }
        return FALSE;
    }

    public function getSearchSQL($model_name) {
        $sql = "select ids from public.ra_online_search_config_cso where lower(login_name)='" . strtolower(_getLoginName()) . "' and lower(model_name) = '" . strtolower($model_name) . "'";
        $rs = common::excuteOneSql($sql);
        if (empty($rs)) {
            $sql = "select array_to_string(array(select id from public.ra_online_search_cso where active = true and lower(model_name) = '" . strtolower($model_name) . "'";
            if (_isCustomerLogin())
                $sql .= " and customer_display = true";
            $sql .= " order by order_by), ',')";
            $rs = common::excuteOneSql($sql);
        }
        $result = " and 1=1";
        $sql = $this->_getSql($rs, "value_name, search_condition, is_int");
        $rss = common::excuteListSql($sql);
        //因为正式服务器保持不变的前提下，手动的添加transport_mode.这里不用配置了。transport_mode已经单独的移到了外面特别判断了，
        // if(strtolower($model_name) == "booking_search" || strtolower($model_name) == "ocean_search"){
        //     $rss[] = array("value_name"=>"transport_mode","search_condition"=>"like","is_int"=>false);
        // }
        foreach ($rss as $v) {
            if ((isset($_POST[$v['value_name']]) && !empty($_POST[$v['value_name']])) || (isset($_POST[$v['value_name'] . '_start']) && !empty($_POST[$v['value_name'] . '_start'])) || (isset($_POST[$v['value_name'] . '_end']) && !empty($_POST[$v['value_name'] . '_end']))) {
                //transport_mode 查询统一处理
                if ((strtolower($model_name) == "booking_search" || strtolower($model_name) == "ocean_search") && strtolower($v['value_name']) == "transport_mode") {
                    //需要被拿出来，以不影响计总的数量计算
                    // $mode_param = "";
                    // if (strtolower($_POST["transport_mode"]) == "all") {
                    //     $mode_param = "'sea','air'";
                    // } else {
                    //     $transport_mode = str_replace(",", ";", $_POST["transport_mode"]);
                    //     //替换transport_mode对应的value
                    //     $transport_mode = str_replace("Ocean Freight", "sea", $transport_mode);
                    //     $transport_mode = str_replace("Air Freight", "air", $transport_mode);
                    //     $mode_param = common::getInNotInSqlForSearch($transport_mode);
                    // }
                    // $result .= " and transport_mode in ($mode_param)";
                } elseif (strtolower($model_name) == "ocean_search"  && (strtolower($v['value_name']) == "incoterms"|| strtolower($v['value_name']) == "service")){
                    //incoterms 和 service特殊处理
                    if(!empty($_POST[$v['value_name']])){
                        //前台这个字段，All是字符串，其他值的提交过来的是数组
                        $value_arr = $_POST[$v['value_name']];
                        if(!is_array($value_arr)){
                            $value_arr = array($value_arr);
                        }
                        $value_sql = "1<>1";
                        foreach($value_arr as $val){
                            if(strtolower($val) == "all"){
                                continue;
                            }else{
                                $value_sql .= " or lower(".$v['value_name'].") = '".strtolower($val)."'";
                            }
                        }
                        if ($value_sql <> "1<>1"){
                            $result .= " and ($value_sql)";
                        }
                    }
 
                } else {
                    if ($v['search_condition'] == '>=,<=') {
                        $s = $v['value_name'] . '_start';
                        $e = $v['value_name'] . '_end';
                        if (isset($_POST[$s]) && !empty($_POST[$s])) {
                            $result .= " and " . $v['value_name'] . " >= '" . common::check_input(common::usDate2sqlDate($_POST[$s]) . ' 00:00:00') . "'";
                        }
                        if (isset($_POST[$e]) && !empty($_POST[$e])) {
                            $result .= " and " . $v['value_name'] . " <= '" . common::check_input(common::usDate2sqlDate($_POST[$e]) . ' 23:59:59') . "'";
                        }
                    } else {
                        //处理多个输入查询情况包含一个 数组类型，也有可能单个字符串
                        $value_arr = $_POST[$v['value_name']];
                        if(!is_array($value_arr)){
                            $value_arr = array($value_arr);
                        }
                        $more_sql = "1<>1";
                        foreach($value_arr as $value){
                            //处理合并查询 包含一个
                            $value_name_arr = explode("/", $v['value_name']);
                            foreach($value_name_arr as $value_name){
                                if ($v['is_int'] == 't') {
                                    $more_sql .= " or " . " " . $value_name . " " . $v['search_condition'] . " " . trim($value);
                                } elseif ($v['search_condition'] == 'ilike') {
                                      $more_sql .= " or " . $value_name . " " . $v['search_condition'] . " '" . common::check_input(trim($value)) . "%'";
                                } elseif ($v['search_condition'] == 'like') {
                                    $more_sql .= " or " . $value_name . " " . $v['search_condition'] . " '%" . common::check_input(trim($value)) . "%'";
                                } else {
                                    $more_sql .= " or " . $value_name . " " . $v['search_condition'] . " '" . common::check_input(trim($value)) . "'";
                                }
                            }
                        }
                        if ($more_sql <> "1<>1"){
                            $result .= " and ($more_sql)";
                        }           
                    }  
                }
            }
        }
        return $result;
    }

    public function getDisplayColumn($model_name, $isin = true) {
        $rs = null;
        $sql = "select ids from public.ra_online_search_config where lower(login_name)='" . strtolower(_getLoginName()) . "' and lower(model_name) = '" . strtolower($model_name) . "'";
        $rs = common::excuteOneSql($sql);
        if (empty($rs)) {
            $sql = "select array_to_string(array(select id from public.ra_online_search where active = true and default_display = true and lower(model_name) = '" . strtolower($model_name) . "'";
            if (_isCustomerLogin())
                $sql .= " and customer_display = true";
            $sql .= " order by order_by asc, search_name asc), ',')";
            $rs = common::excuteOneSql($sql);
        }
        if ($isin) {
            $sql = $this->_getSql($rs, "id, search_name as name");
            $rss = common::excuteListSql($sql);
        } else {
            $sql = "SELECT id, search_name as name FROM public.ra_online_search WHERE active = true and lower(model_name) = '" . strtolower($model_name) . "' and id not in ($rs)";
            if (_isCustomerLogin())
                $sql .= ' and customer_display=true';
            $rss = common::excuteListSql($sql);
        }
        return $rss;
    }
}

?>