_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 .= '

Year :

'; } 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 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; } } ?>