欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-08 16:47:11
1 /// 2 /// 智能查询类型 3 /// 4 5 public enum QueryType : byte 6 { 7 /// 8 /// 发货方联系人信息 9 /// 10 [DataMappingAttr ......
智能提示含查询多列(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         /// 公司ID
106         /// </summary>
107         public string CorpId { set; get; }
108     }
QueryType实体类
智能提示含查询多列(html+JS+handler+ HttpRemoting)二、Remoting代码
  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     }
智能查询具体实现DB方法

此时,智能提示已正常结束