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                                 {