智能提示含查询多列（html+JS+handler+ HttpRemoting）二、Remoting代码

 ?1 ?/// <summary> ?2 ????/// 智能查询类型 ?3 ????/// </summary> ?4 ??5 ????public enum QueryType : byte ?6 ????{ ?7 ????????/// <summary> ?8 ????????/// 发货方联系人信息 ?9 ????????/// </summary> 10 ????????[DataMappingAttribute("T_PARAM_SHIPPER T LEFT JOIN T_PARAM_SHIPPER_LINK_INFO T2 ON T2.C_SHIPPER_ID=T.C_SHIPPER_ID", "", "T.V_SHIPPER_NAME", "T.C_SHIPPER_ID Id")] 11 ????????CustomerShipperInfo = 10 12 ????} 13 ?14 ????/// <summary> 15 ????/// 智能查询字段常量 16 ????/// </summary> 17 ?18 ????public enum QueryTypeKey 19 ????{ 20 ?21 ????????#region 发货方信息相关 22 ????????/// <summary> 23 ????????/// 发货方信息:发货方 24 ????????/// </summary> 25 ????????[DataMapping("T.V_SHIPPER_NAME")] 26 ????????发货方_发货方 = 19, 27 ????????/// <summary> 28 ????????/// 发货方信息:联系人 29 ????????/// </summary> 30 ????????[DataMapping("T2.V_LINK_MAN")] 31 ????????发货方_联系人 = 20, 32 ????????/// <summary> 33 ????????/// 发货方信息手机 34 ????????/// </summary> 35 ????????[DataMapping("T2.V_MOBILE")] 36 ????????发货方_手机 = 21, 37 ????????/// <summary> 38 ????????/// 发货方_单位 39 ????????/// </summary> 40 ????????[DataMapping("T2.V_UNIT")] 41 ????????发货方_单位 = 22, 42 ????????/// <summary> 43 ????????/// 发货方_电话 44 ????????/// </summary> 45 ????????[DataMapping("T2.V_TEL")] 46 ????????发货方_电话 = 23, 47 ????????/// <summary> 48 ????????/// 发货方_地址 49 ????????/// </summary> 50 ????????[DataMapping("T2.V_ADDRESS")] 51 ????????发货方_地址 = 24 52 ????????#endregion 53 ????} 54 ?55 ????/// <summary> 56 ????/// 搜索模板 57 ????/// </summary> 58 ????[Serializable] 59 ????public class QueryCondition 60 ????{ 61 ????????/// <summary> 62 ????????/// 搜索关键字 63 ????????/// </summary> 64 ????????public string SearchContext { set; get; } 65 ????????/// <summary> 66 ????????/// 搜索输出列表头 67 ????????/// </summary> 68 ????????public List<QueryTypeKey> OutColumns { set; get; } 69 ????????///// <summary> 70 ????????///// 输出数据 71 ????????///// </summary> 72 ????????//public string[,] OutData { set; get; } 73 ????????/// <summary> 74 ????????/// 排序字段 75 ????????/// </summary> 76 ????????public QueryTypeKey SortKey { set; get; } 77 ????????/// <summary> 78 ????????/// 搜索类型 79 ????????/// </summary> 80 ????????public QueryType QueryType { set; get; } 81 ????????/// <summary> 82 ????????/// 搜索站点代码 83 ????????/// </summary> 84 ????????public string StationCode { set; get; } 85 ????????/// <summary> 86 ????????/// 站点名称 87 ????????/// </summary> 88 ????????public string StationName { set; get; } 89 ?90 ????????/// <summary> 91 ????????/// 参数类别 92 ????????/// </summary> 93 ????????public string ParamType { set; get; } 94 ?????????95 ????????/// <summary> 96 ????????/// 扩展参数 97 ????????/// </summary> 98 ????????public object ExtParam { set; get; } 99 100 ????????/// <summary>101 ????????/// 省代码102 ????????/// </summary>103 ????????public string Province { set; get; }104 ????????/// <summary>105 ????????/// 公司ID106 ????????/// </summary>107 ????????public string CorpId { set; get; }108 ????}

 ?1 ???/// <summary> ?2 ????/// 智能查询服务 ?3 ????/// </summary> ?4 ????public class IntellQueryService : MarshalByRefObject, IIntellQuery ?5 ????{ ?6 ??7 ????????/// <summary> ?8 ????????/// 业务A集合 ?9 ????????/// </summary> 10 ????????private static IList<QueryType> cropFilterList = new List<QueryType>() { 11 ?????????????QueryType.Provice_City, 12 ?????????????QueryType.ProviceCityName 13 ????????}; 14 ????????/// <summary> 15 ????????/// 智能查询相关 16 ????????/// </summary> 17 ????????/// <param name="condition"></param> 18 ????????/// <param name="outData"></param> 19 ????????/// <param name="err"></param> 20 ????????/// <returns></returns> 21 ????????public bool Query(QueryCondition condition, ref string[,] outData, ref string err) 22 ????????{ 23 ????????????try 24 ????????????{ 25 ??????????????if (null == condition) 26 ????????????????{ 27 ????????????????????err = "condition不能为空!"; 28 ????????????????????return false; 29 ????????????????} 30 ????????????????if (null == condition.OutColumns) 31 ????????????????{ 32 ????????????????????err = "outColumns不能为空!"; 33 ????????????????????return false; 34 ????????????????} 35 ????????????????if (0 == condition.OutColumns.Count) 36 ????????????????{ 37 ????????????????????err = "outColumns不能为空!"; 38 ????????????????????return false; 39 ????????????????} 40 ????????????????if (condition.QueryType == QueryType.TransiCropInfo) 41 ????????????????{ 42 ????????????????????var dtInt = GetInterQuery(condition, 1); 43 ????????????????????outData = new string[dtInt.Rows.Count, dtInt.Columns.Count]; 44 ????????????????????for (var i = 0; i < dtInt.Rows.Count; i++) 45 ????????????????????{ 46 ????????????????????????for (var j = 0; j < dtInt.Columns.Count; j++) 47 ????????????????????????{ 48 ????????????????????????????outData[i, j] = dtInt.Rows[i].IsNull(j) ? "" : Convert.ToString(dtInt.Rows[i][j]); 49 ????????????????????????} 50 ????????????????????} 51 ????????????????????return true; 52 ????????????????} 53 ?54 ????????????????var sql = GetQuerySQL(condition); 55 ????????????????var dt = Glob.Singleton.OracleOperation.GetDataTableBySql(sql, ref err); 56 ????????????????if (null == dt) 57 ????????????????{ 58 ????????????????????return false; 59 ????????????????} 60 ????????????????var cCount = condition.OutColumns.Count + 1; 61 ????????????????outData = new string[dt.Rows.Count, cCount]; 62 ????????????????for (var i = 0; i < dt.Rows.Count; i++) 63 ????????????????{ 64 ????????????????????for (var j = 0; j < cCount; j++) 65 ????????????????????{ 66 ????????????????????????outData[i, j] = dt.Rows[i].IsNull(j) ? "" : Convert.ToString(dt.Rows[i][j]); 67 ????????????????????} 68 ????????????????} 69 ????????????????return true; 70 ????????????} 71 ????????????catch (Exception ex) 72 ????????????{ 73 ????????????????err = ex.ToString(); 74 ????????????????return false; 75 ????????????} 76 ????????} 77 ????????/// <summary> 78 ????????/// 智能查询相关 79 ????????/// </summary> 80 ????????/// <param name="queryType">查询归属</param> 81 ????????/// <param name="searchContext">搜索关键字</param> 82 ????????/// <param name="outColumns">返回列头</param> 83 ????????/// <param name="outData">返回数据</param> 84 ????????/// <param name="sortKey">排序列</param> 85 ????????/// <param name="outErrMsg">出错输出</param> 86 ????????/// <returns></returns> 87 ????????public bool Query(QueryType queryType, string searchContext, List<QueryTypeKey> outColumns, 88 ????????????ref string[,] outData, QueryTypeKey sortKey, ref string outErrMsg) 89 ????????{ 90 ????????????if (null == outColumns) 91 ????????????{ 92 ????????????????outErrMsg = "outColumns不能为空!"; 93 ????????????????return false; 94 ????????????} 95 ????????????if (0 == outColumns.Count) 96 ????????????{ 97 ????????????????outErrMsg = "outColumns不能为空!"; 98 ????????????????return false; 99 ????????????}100 101 ????????????var sql = GetQuerySQL(new QueryCondition()102 ????????????{103 ????????????????QueryType = queryType,104 ????????????????OutColumns = outColumns,105 ????????????????SearchContext = searchContext.FormatSQLValue(),106 ????????????????SortKey = sortKey107 ????????????});108 ????????????var dt = Singleton.OracleOperation.GetDataTableBySql(sql, ref outErrMsg);109 ????????????outData = new string[dt.Rows.Count, outColumns.Count + 1];110 ????????????for (var i = 0; i < dt.Rows.Count; i++)111 ????????????{112 ????????????????for (var j = 0; j < outColumns.Count; j++)113 ????????????????{114 ????????????????????outData[i, j] = dt.Rows[i].IsNull(j) ? "" : Convert.ToString(dt.Rows[i][j]);115 ????????????????}116 ????????????}117 ????????????return true;118 ????????}119 ????????private string GetQuerySQL(QueryCondition qc)120 ????????{121 ????????????var queryType = qc.QueryType;122 ????????????var outColumns = qc.OutColumns;123 ????????????var searchContext = qc.SearchContext.FormatSQLValue();124 ????????????var sortKey = qc.SortKey;125 ????????????var qtks = Enum.GetNames(typeof(QueryTypeKey));126 ????????????var sb = new StringBuilder();127 ????????????sb.Append("SELECT ");128 ????????????foreach (var ty in outColumns)129 ????????????{130 ????????????????foreach (var qs in qtks)131 ????????????????{132 ????????????????????if (ty.ToString() == qs)133 ????????????????????{134 ????????????????????????var at = EnumHelper.GetAttribute(ty);135 ????????????????????????sb.AppendFormat("{0},", at.Name);136 ????????????????????}137 ????????????????}138 ????????????}139 ????????????sb.AppendFormat("{0},", EnumHelper.GetAttribute(queryType).Id);140 ????????????sb.Remove(sb.Length - 1, 1);141 ????????????sb.AppendFormat(" FROM {0}", string.Format("{0} WHERE ?( {1} like ‘%{2}%‘",142 ????????????????EnumHelper.GetAttribute(queryType).Name,143 ????????????????EnumHelper.GetAttribute(queryType).SearchKeyColumn144 ????????????????, searchContext));145 ????????????var des = EnumHelper.GetAttribute(queryType).Description;146 ????????????if (!string.IsNullOrEmpty(des))147 ????????????{148 ????????????????var arr = des.Split(‘,‘);149 ????????????????foreach (var d in arr)150 ????????????????{151 ????????????????????sb.AppendFormat(" OR {0} like ‘%{1}%‘ ", d, searchContext);152 ????????????????}153 ????????????????sb.AppendFormat(" ) ");154 ????????????}155 ????????????else156 ????????????{157 ????????????????sb.AppendFormat(") ");158 ????????????}159 160 ????????????switch (queryType)161 ????????????{162 ????????????????case QueryType.CustomerReceiverInfo:163 ????????????????????{164 ????????????????????????if (null != qc.ExtParam)165 ????????????????????????{166 ????????????????????????????var json = qc.ExtParam;167 ????????????????????????????var intellCustomer = JsonHelper.DeserializeJsonToObject<IntellCustomer>(json.ToString());168 ????????????????????????????if (null != intellCustomer)169 ????????????????????????????{170 ????????????????????????????????if (!string.IsNullOrEmpty(intellCustomer.ShipperId))171 ????????????????????????????????{172 ????????????????????????????????????sb.AppendFormat(@" AND T.C_SHIPPER_ID={0}", intellCustomer.ShipperId.StringParseDBNULL());173 ????????????????????????????????}174 ????????????????????????????????if (!string.IsNullOrEmpty(intellCustomer.SendSiteName))175 ????????????????????????????????{176 ????????????????????????????????????sb.AppendFormat(@" AND T.V_STATION_NAME={0}", intellCustomer.SendSiteName.StringParseDBNULL());177 ????????????????????????????????}178 ????????????????????????????????if (!string.IsNullOrEmpty(intellCustomer.ArriveSiteName))179 ????????????????????????????????{180 ????????????????????????????????????sb.AppendFormat(@" AND T2.V_STATION_NAME={0}", intellCustomer.ArriveSiteName.StringParseDBNULL());181 ????????????????????????????????}182 ????????????????????????????}183 ????????????????????????}184 ????????????????????}185 ????????????????????break;186 ????????????????case QueryType.LKUserInfo:187 ????????????????????{188 189 ????????????????????}190 ????????????????????break;191 ????????????}192 ????????????if (!string.IsNullOrEmpty(qc.ParamType))193 ????????????{194 ????????????????sb.AppendFormat(" AND T.N_PARAM_TYPE={0} ", qc.ParamType.StringParseDBNULL());195 ????????????}196 ????????????if (!string.IsNullOrEmpty(qc.StationCode))197 ????????????{198 ????????????????sb.AppendFormat(" AND T.N_STATION_CODE={0} ", qc.StationCode.StringParseDBNULL());199 ????????????}200 ????????????if (!string.IsNullOrEmpty(qc.StationName))201 ????????????{202 ????????????????sb.AppendFormat(" AND T.V_STATION_Name={0} ", qc.StationName.StringParseDBNULL());203 ????????????}204 ????????????if (!string.IsNullOrEmpty(qc.Province))205 ????????????{206 ????????????????sb.AppendFormat(" AND {0}={1} ", EnumHelper.GetAttribute(queryType).AttachedCondition, qc.Province.Substring(0, 2).StringParseDBNULL());207 ????????????}208 ????????????if (!cropFilterList.Contains(qc.QueryType))209 ????????????{210 ????????????????if (!string.IsNullOrEmpty(qc.CorpId))211 ????????????????{212 ????????????????????sb.AppendFormat(" AND T.C_CORP_ID={0} ", qc.CorpId.StringParseDBNULL());213 ????????????????}214 ????????????}215 ????????????//TC-优化一下216 ????????????if (!string.IsNullOrEmpty(sortKey.ToString()) && sortKey.ToString() != "0")217 ????????????{218 ????????????????if (qtks.All(qs => sortKey.ToString() != qs))219 ????????????????????return sb.ToString();220 ????????????????var at = EnumHelper.GetAttribute(sortKey);221 ????????????????sb.AppendFormat("ORDER BY {0} ", at.Name);222 ????????????}223 ????????????return sb.ToString();224 ????????}225 ????????private DataTable GetInterQuery(QueryCondition qc, int type)226 ????????{227 ????????????var sbCropInfo = new StringBuilder();228 ????????????sbCropInfo.AppendFormat(@"select V_CORP_NAME,V_CORP_MOBILE,‘‘ AS V_STATION_TEL,‘‘ AS V_STATION_NAME,1 AS cType, c_corp_id ID229 ??from T_CORP_INFO");230 ????????????if (!string.IsNullOrEmpty(qc.SearchContext))231 ????????????{232 ????????????????sbCropInfo.AppendFormat(@" WHERE V_CORP_NAME like ‘%{0}%‘", qc.SearchContext.Filter());233 ????????????}234 ????????????var dtCropInfo = Singleton.OracleOperation.GetDataTableBySql(sbCropInfo.ToString());235 ????????????if (null == dtCropInfo)236 ????????????????dtCropInfo = new DataTable();237 238 ????????????var sbParamCropInfo = new StringBuilder();239 ????????????sbParamCropInfo.AppendFormat(@"select T.V_UNIT_NAME AS V_CORP_NAME,T.V_UNIT_TEL,T2.V_STATION_NAME,T2.V_STATION_TEL, 2 cType, T.c_param_id ID240 ??????????from T_PARAM_CORP_INFO T241 ??????????LEFT JOIN T_PARAM_CORP_STATION_INFO T2242 ????????????ON T.C_PARAM_ID = T2.C_PARAM_ID");243 244 ????????????if (!string.IsNullOrEmpty(qc.SearchContext))245 ????????????{246 ????????????????sbParamCropInfo.AppendFormat(@" WHERE (T.V_UNIT_MNEM like ‘%{0}%‘ OR T.V_UNIT_NAME like ‘%{0}%‘) ", qc.SearchContext.Filter());247 ????????????}248 ????????????if (!string.IsNullOrEmpty(qc.StationCode))249 ????????????{250 ????????????????sbParamCropInfo.AppendFormat(@" AND T2.V_STATION_NAME = {0}", qc.StationName.Filter().StringParseDBNULL());251 ????????????}252 ????????????if (!string.IsNullOrEmpty(qc.CorpId))253 ????????????{254 ????????????????sbParamCropInfo.AppendFormat(@" AND T.C_CORP_ID={0}", qc.CorpId.StringParseDBNULL());255 ????????????}256 ????????????var dtParamCropInfo = Singleton.OracleOperation.GetDataTableBySql(sbParamCropInfo.ToString());257 ????????????if (null == dtParamCropInfo)258 ????????????????dtParamCropInfo = new DataTable();259 ????????????DataTable dt = new DataTable();260 ????????????dt.Columns.Add("V_CORP_NAME");261 ????????????dt.Columns.Add("V_UNIT_TEL");262 ????????????dt.Columns.Add("V_STATION_NAME");263 ????????????dt.Columns.Add("V_STATION_TEL");264 ????????????dt.Columns.Add("cType");265 ????????????dt.Columns.Add("Id");266 ????????????//TC-优化一下267 ????????????dt = FillTableData(dt, dtCropInfo.Rows);268 ????????????return FillTableData(dt, dtParamCropInfo.Rows);269 ????????}270 271 ????????/// <summary>272 ????????/// 填充表数据273 ????????/// </summary>274 ????????/// <param name="fillDataTable"></param>275 ????????/// <param name="dataRow"></param>276 ????????private DataTable FillTableData(DataTable fillDataTable, DataRowCollection dataRow)277 ????????{278 ????????????foreach (DataRow dr in dataRow)279 ????????????{280 ????????????????DataRow drNew = fillDataTable.NewRow();281 ????????????????drNew["V_CORP_NAME"] = dr[0];282 ????????????????drNew["V_UNIT_TEL"] = dr[1];283 ????????????????drNew["V_STATION_NAME"] = dr[2];284 ????????????????drNew["V_STATION_TEL"] = dr[3];285 ????????????????drNew["cType"] = dr[4];286 ????????????????drNew["Id"] = dr[5];287 ????????????????fillDataTable.Rows.Add(drNew);288 ????????????}289 ????????????return fillDataTable;290 ????????}291 ????}