智能提示含查询多列（html+JS+handler+ HttpRemoting）二、Remoting代码
程序员文章站
2022-10-05 15:32:20
1 /// 2 /// 智能查询类型 3 /// 4 5 public enum QueryType : byte 6 { 7 /// 8 /// 发货方联系人信息 9 /// 10 [DataMappingAttr ......
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 /// 公司ID
106 /// </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 = sortKey
107 });
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).SearchKeyColumn
144 , 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 else
156 {
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 ID
229 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 ID
240 from T_PARAM_CORP_INFO T
241 LEFT JOIN T_PARAM_CORP_STATION_INFO T2
242 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 }
此时，智能提示已正常结束