/// <summary>
/// 智能查询类型
/// </summary> public enum QueryType : byte
{
/// <summary>
/// 发货方联系人信息
/// </summary>
[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")]
CustomerShipperInfo =
} /// <summary>
/// 智能查询字段常量
/// </summary> public enum QueryTypeKey
{ #region 发货方信息相关
/// <summary>
/// 发货方信息:发货方
/// </summary>
[DataMapping("T.V_SHIPPER_NAME")]
发货方_发货方 = ,
/// <summary>
/// 发货方信息:联系人
/// </summary>
[DataMapping("T2.V_LINK_MAN")]
发货方_联系人 = ,
/// <summary>
/// 发货方信息手机
/// </summary>
[DataMapping("T2.V_MOBILE")]
发货方_手机 = ,
/// <summary>
/// 发货方_单位
/// </summary>
[DataMapping("T2.V_UNIT")]
发货方_单位 = ,
/// <summary>
/// 发货方_电话
/// </summary>
[DataMapping("T2.V_TEL")]
发货方_电话 = ,
/// <summary>
/// 发货方_地址
/// </summary>
[DataMapping("T2.V_ADDRESS")]
发货方_地址 =
#endregion
} /// <summary>
/// 搜索模板
/// </summary>
[Serializable]
public class QueryCondition
{
/// <summary>
/// 搜索关键字
/// </summary>
public string SearchContext { set; get; }
/// <summary>
/// 搜索输出列表头
/// </summary>
public List<QueryTypeKey> OutColumns { set; get; }
///// <summary>
///// 输出数据
///// </summary>
//public string[,] OutData { set; get; }
/// <summary>
/// 排序字段
/// </summary>
public QueryTypeKey SortKey { set; get; }
/// <summary>
/// 搜索类型
/// </summary>
public QueryType QueryType { set; get; }
/// <summary>
/// 搜索站点代码
/// </summary>
public string StationCode { set; get; }
/// <summary>
/// 站点名称
/// </summary>
public string StationName { set; get; } /// <summary>
/// 参数类别
/// </summary>
public string ParamType { set; get; } /// <summary>
/// 扩展参数
/// </summary>
public object ExtParam { set; get; } /// <summary>
/// 省代码
/// </summary>
public string Province { set; get; }
/// <summary>
/// 公司ID
/// </summary>
public string CorpId { set; get; }
}
QueryType实体类
/// <summary>
/// 智能查询服务
/// </summary>
public class IntellQueryService : MarshalByRefObject, IIntellQuery
{ /// <summary>
/// 业务A集合
/// </summary>
private static IList<QueryType> cropFilterList = new List<QueryType>() {
QueryType.Provice_City,
QueryType.ProviceCityName
};
/// <summary>
/// 智能查询相关
/// </summary>
/// <param name="condition"></param>
/// <param name="outData"></param>
/// <param name="err"></param>
/// <returns></returns>
public bool Query(QueryCondition condition, ref string[,] outData, ref string err)
{
try
{
if (null == condition)
{
err = "condition不能为空!";
return false;
}
if (null == condition.OutColumns)
{
err = "outColumns不能为空!";
return false;
}
if ( == condition.OutColumns.Count)
{
err = "outColumns不能为空!";
return false;
}
if (condition.QueryType == QueryType.TransiCropInfo)
{
var dtInt = GetInterQuery(condition, );
outData = new string[dtInt.Rows.Count, dtInt.Columns.Count];
for (var i = ; i < dtInt.Rows.Count; i++)
{
for (var j = ; j < dtInt.Columns.Count; j++)
{
outData[i, j] = dtInt.Rows[i].IsNull(j) ? "" : Convert.ToString(dtInt.Rows[i][j]);
}
}
return true;
} var sql = GetQuerySQL(condition);
var dt = Glob.Singleton.OracleOperation.GetDataTableBySql(sql, ref err);
if (null == dt)
{
return false;
}
var cCount = condition.OutColumns.Count + ;
outData = new string[dt.Rows.Count, cCount];
for (var i = ; i < dt.Rows.Count; i++)
{
for (var j = ; j < cCount; j++)
{
outData[i, j] = dt.Rows[i].IsNull(j) ? "" : Convert.ToString(dt.Rows[i][j]);
}
}
return true;
}
catch (Exception ex)
{
err = ex.ToString();
return false;
}
}
/// <summary>
/// 智能查询相关
/// </summary>
/// <param name="queryType">查询归属</param>
/// <param name="searchContext">搜索关键字</param>
/// <param name="outColumns">返回列头</param>
/// <param name="outData">返回数据</param>
/// <param name="sortKey">排序列</param>
/// <param name="outErrMsg">出错输出</param>
/// <returns></returns>
public bool Query(QueryType queryType, string searchContext, List<QueryTypeKey> outColumns,
ref string[,] outData, QueryTypeKey sortKey, ref string outErrMsg)
{
if (null == outColumns)
{
outErrMsg = "outColumns不能为空!";
return false;
}
if ( == outColumns.Count)
{
outErrMsg = "outColumns不能为空!";
return false;
} var sql = GetQuerySQL(new QueryCondition()
{
QueryType = queryType,
OutColumns = outColumns,
SearchContext = searchContext.FormatSQLValue(),
SortKey = sortKey
});
var dt = Singleton.OracleOperation.GetDataTableBySql(sql, ref outErrMsg);
outData = new string[dt.Rows.Count, outColumns.Count + ];
for (var i = ; i < dt.Rows.Count; i++)
{
for (var j = ; j < outColumns.Count; j++)
{
outData[i, j] = dt.Rows[i].IsNull(j) ? "" : Convert.ToString(dt.Rows[i][j]);
}
}
return true;
}
private string GetQuerySQL(QueryCondition qc)
{
var queryType = qc.QueryType;
var outColumns = qc.OutColumns;
var searchContext = qc.SearchContext.FormatSQLValue();
var sortKey = qc.SortKey;
var qtks = Enum.GetNames(typeof(QueryTypeKey));
var sb = new StringBuilder();
sb.Append("SELECT ");
foreach (var ty in outColumns)
{
foreach (var qs in qtks)
{
if (ty.ToString() == qs)
{
var at = EnumHelper.GetAttribute(ty);
sb.AppendFormat("{0},", at.Name);
}
}
}
sb.AppendFormat("{0},", EnumHelper.GetAttribute(queryType).Id);
sb.Remove(sb.Length - , );
sb.AppendFormat(" FROM {0}", string.Format("{0} WHERE ( {1} like '%{2}%'",
EnumHelper.GetAttribute(queryType).Name,
EnumHelper.GetAttribute(queryType).SearchKeyColumn
, searchContext));
var des = EnumHelper.GetAttribute(queryType).Description;
if (!string.IsNullOrEmpty(des))
{
var arr = des.Split(',');
foreach (var d in arr)
{
sb.AppendFormat(" OR {0} like '%{1}%' ", d, searchContext);
}
sb.AppendFormat(" ) ");
}
else
{
sb.AppendFormat(") ");
} switch (queryType)
{
case QueryType.CustomerReceiverInfo:
{
if (null != qc.ExtParam)
{
var json = qc.ExtParam;
var intellCustomer = JsonHelper.DeserializeJsonToObject<IntellCustomer>(json.ToString());
if (null != intellCustomer)
{
if (!string.IsNullOrEmpty(intellCustomer.ShipperId))
{
sb.AppendFormat(@" AND T.C_SHIPPER_ID={0}", intellCustomer.ShipperId.StringParseDBNULL());
}
if (!string.IsNullOrEmpty(intellCustomer.SendSiteName))
{
sb.AppendFormat(@" AND T.V_STATION_NAME={0}", intellCustomer.SendSiteName.StringParseDBNULL());
}
if (!string.IsNullOrEmpty(intellCustomer.ArriveSiteName))
{
sb.AppendFormat(@" AND T2.V_STATION_NAME={0}", intellCustomer.ArriveSiteName.StringParseDBNULL());
}
}
}
}
break;
case QueryType.LKUserInfo:
{ }
break;
}
if (!string.IsNullOrEmpty(qc.ParamType))
{
sb.AppendFormat(" AND T.N_PARAM_TYPE={0} ", qc.ParamType.StringParseDBNULL());
}
if (!string.IsNullOrEmpty(qc.StationCode))
{
sb.AppendFormat(" AND T.N_STATION_CODE={0} ", qc.StationCode.StringParseDBNULL());
}
if (!string.IsNullOrEmpty(qc.StationName))
{
sb.AppendFormat(" AND T.V_STATION_Name={0} ", qc.StationName.StringParseDBNULL());
}
if (!string.IsNullOrEmpty(qc.Province))
{
sb.AppendFormat(" AND {0}={1} ", EnumHelper.GetAttribute(queryType).AttachedCondition, qc.Province.Substring(, ).StringParseDBNULL());
}
if (!cropFilterList.Contains(qc.QueryType))
{
if (!string.IsNullOrEmpty(qc.CorpId))
{
sb.AppendFormat(" AND T.C_CORP_ID={0} ", qc.CorpId.StringParseDBNULL());
}
}
//TC-优化一下
if (!string.IsNullOrEmpty(sortKey.ToString()) && sortKey.ToString() != "")
{
if (qtks.All(qs => sortKey.ToString() != qs))
return sb.ToString();
var at = EnumHelper.GetAttribute(sortKey);
sb.AppendFormat("ORDER BY {0} ", at.Name);
}
return sb.ToString();
}
private DataTable GetInterQuery(QueryCondition qc, int type)
{
var sbCropInfo = new StringBuilder();
sbCropInfo.AppendFormat(@"select V_CORP_NAME,V_CORP_MOBILE,'' AS V_STATION_TEL,'' AS V_STATION_NAME,1 AS cType, c_corp_id ID
from T_CORP_INFO");
if (!string.IsNullOrEmpty(qc.SearchContext))
{
sbCropInfo.AppendFormat(@" WHERE V_CORP_NAME like '%{0}%'", qc.SearchContext.Filter());
}
var dtCropInfo = Singleton.OracleOperation.GetDataTableBySql(sbCropInfo.ToString());
if (null == dtCropInfo)
dtCropInfo = new DataTable(); var sbParamCropInfo = new StringBuilder();
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
from T_PARAM_CORP_INFO T
LEFT JOIN T_PARAM_CORP_STATION_INFO T2
ON T.C_PARAM_ID = T2.C_PARAM_ID"); if (!string.IsNullOrEmpty(qc.SearchContext))
{
sbParamCropInfo.AppendFormat(@" WHERE (T.V_UNIT_MNEM like '%{0}%' OR T.V_UNIT_NAME like '%{0}%') ", qc.SearchContext.Filter());
}
if (!string.IsNullOrEmpty(qc.StationCode))
{
sbParamCropInfo.AppendFormat(@" AND T2.V_STATION_NAME = {0}", qc.StationName.Filter().StringParseDBNULL());
}
if (!string.IsNullOrEmpty(qc.CorpId))
{
sbParamCropInfo.AppendFormat(@" AND T.C_CORP_ID={0}", qc.CorpId.StringParseDBNULL());
}
var dtParamCropInfo = Singleton.OracleOperation.GetDataTableBySql(sbParamCropInfo.ToString());
if (null == dtParamCropInfo)
dtParamCropInfo = new DataTable();
DataTable dt = new DataTable();
dt.Columns.Add("V_CORP_NAME");
dt.Columns.Add("V_UNIT_TEL");
dt.Columns.Add("V_STATION_NAME");
dt.Columns.Add("V_STATION_TEL");
dt.Columns.Add("cType");
dt.Columns.Add("Id");
//TC-优化一下
dt = FillTableData(dt, dtCropInfo.Rows);
return FillTableData(dt, dtParamCropInfo.Rows);
} /// <summary>
/// 填充表数据
/// </summary>
/// <param name="fillDataTable"></param>
/// <param name="dataRow"></param>
private DataTable FillTableData(DataTable fillDataTable, DataRowCollection dataRow)
{
foreach (DataRow dr in dataRow)
{
DataRow drNew = fillDataTable.NewRow();
drNew["V_CORP_NAME"] = dr[];
drNew["V_UNIT_TEL"] = dr[];
drNew["V_STATION_NAME"] = dr[];
drNew["V_STATION_TEL"] = dr[];
drNew["cType"] = dr[];
drNew["Id"] = dr[];
fillDataTable.Rows.Add(drNew);
}
return fillDataTable;
}
}
智能查询具体实现DB方法
此时，智能提示已正常结束