public HttpResponseBase GetList()
{
int totalCount = 0;
List<ProductSearchQuery> stores = new List<ProductSearchQuery>();
string json = string.Empty;
try
{
searchMgr = new ProductSearchMgr(SqlConnectionString, "sphinxHost", "sphinxPort");
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
try
{
ProductSearchQuery query = new ProductSearchQuery();
query.Start = Convert.ToInt32(Request.Params["Start"] ?? "0");
if (!string.IsNullOrEmpty(Request.Params["Limit"]))
{
query.Limit = Convert.ToInt32(Request.Params["Limit"]);
}
if (!string.IsNullOrEmpty(Request.Params["flag"]))
{
query.flag = Request.Params["flag"];
}
if (!string.IsNullOrEmpty(Request.Params["searchKey"]))
{
query.searchKey = Request.Params["searchKey"];
}
stores = searchMgr.GetProductSearchList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public List<ProductSearchQuery> GetProductSearchList(ProductSearchQuery query, out int totalCount)
{
List<int> searchList = new List<int>();
List<int> flagList=new List<int>();
List<int> idList = new List<int>();
List<string> keyList = new List<string>();
try
{
if (!string.IsNullOrEmpty(query.searchKey))
{
//根據關鍵字查詢出來的商品編號
searchList = dao.GetIdList(query.searchKey, query.MaxMatches);
}
if (query.flag == "1")
{
keyList = dao.GetKeyList(query);
flagList = dao.GetFlagList(keyList, query.MaxMatches);
List<int> sphinxIdList = dao.GetSphinxExcludeIdList();
#region 處理出最後的productID列表
if (string.IsNullOrEmpty(query.searchKey))
{
idList = flagList;
}
else if (!string.IsNullOrEmpty(query.searchKey))
{
foreach (int item in searchList)
{
if (flagList.Contains(item))
{
idList.Add(item);
}
}
}
foreach (int sphinxId in sphinxIdList)
{
if (idList.Contains(sphinxId))
{
idList.Remove(sphinxId);
}
}
#endregion
}
else
{
//如果選擇的是否
idList = dao.GetIdList(query.searchKey, query.MaxMatches);
}
//獲取最後的keylist
if (!string.IsNullOrEmpty(query.searchKey))
{
if (!keyList.Contains(query.searchKey))
{
keyList.Add(query.searchKey);
}
}
List<ProductSearchQuery> oldStore = dao.GetListDetail( query,idList,out totalCount);
return dao.GetListResult(oldStore,keyList);
}
catch (Exception ex)
{
throw new Exception("ProductSearchMgr->GetProductSearchList:" + ex.Message);
}
}
public List<string> GetKeyList(ProductSearchQuery query)
{
StringBuilder sql = new StringBuilder();
List<string> keyList = new List<string>();
try
{
///是食安關鍵字
if (query.flag == "1" )
{
sql.AppendFormat("SELECT key_word from sphinx_keyword where 1=1 AND flag='1' ");
}
else if (query.flag == "0")
{
sql.AppendFormat("SELECT key_word from sphinx_keyword where 1=1 AND flag='3'");
}
DataTable dt = _accessMySql.getDataTable(sql.ToString());
if (dt.Rows.Count > 0)
{
for (int index = 0; index < dt.Rows.Count; index++)
{
keyList.Add(dt.Rows[index][0].ToString());
}
}
}
catch (Exception ex)
{
throw new Exception("ProductSearchDao-->GetAllKey: " + sql.ToString() + ex.Message);
}
return keyList;
}
public List<ProductSearchQuery> GetListResult(List<ProductSearchQuery> oldStore, List<string> keyList)
{
List<ProductSearchQuery> listStore = new List<ProductSearchQuery>();
try
{
for (int index = 0; index < oldStore.Count; index++)
{
if (keyList.Count > 0)
{
ProductSearchQuery query = new ProductSearchQuery();
query = oldStore[index];
foreach (var key in keyList)
{
query.Product_Id = oldStore[index].Product_Id;
query.Product_Name = query.Product_Name.Replace(key, "<b> <span style='color:#ff0000'>" + key + "</span></b>");
query.Page_Content_1 = query.Page_Content_1.Replace(key, "<b> <span style='color:#ff0000'>" + key + "</span></b>");
query.Page_Content_2 = query.Page_Content_2.Replace(key, "<b> <span style='color:#ff0000'>" + key + "</span></b>");
query.Page_Content_3 = query.Page_Content_3.Replace(key, "<b> <span style='color:#ff0000'>" + key + "</span></b>");
query.Product_Keywords = query.Product_Keywords.Replace(key, "<b> <span style='color:#ff0000'>" + key + "</span></b>");
query.product_detail_text = oldStore[index].product_detail_text.Replace(key, "<b> <span style='color:#ff0000'>" + key + "</span></b>");
}
listStore.Add(query);
}
}
}
catch (Exception ex)
{
throw new Exception("ProductSearchDao-->GetListResult:" + ex.Message);
}
return listStore;
}
/// <summary>
/// 獲取包含id對應的數據
/// </summary>
/// <param name="query"></param>
/// <param name="idList"></param>
/// <returns></returns>
public List<ProductSearchQuery> GetListDetail(ProductSearchQuery query, List<int> idList, out int totalCount)
{
///獲取對應的數據
StringBuilder sql = new StringBuilder();
///搜索條件
StringBuilder sqlWhere = new StringBuilder();
///計算滿足數據的總數
StringBuilder sqlCount = new StringBuilder();
List<ProductSearchQuery> listStore = new List<ProductSearchQuery>();
///排除SphinxExclude的product_id
List<SphinxExclude> ListExclude = new List<SphinxExclude>();
totalCount = 0;
try
{
///獲得數據sql
sql.AppendFormat("select product_id,product_name,page_content_1,page_content_2,page_content_3,product_keywords,product_detail_text ");
///查詢總數sql
sqlCount.Append(" select count(product_id) as totalCount ");
///條件限制aql
sqlWhere.Append(" from product where 1=1 ");
if (idList.Count != 0)
{
sqlWhere.AppendFormat(" AND product_id in ( ");
for (int index = 0; index < idList.Count; index++)
{
sqlWhere.AppendFormat(" {0},", idList[index]);
}
sqlWhere.Remove(sqlWhere.Length - 1, 1);
sqlWhere.Append(")");
///是否分頁
if (query.IsPage)
{
sqlCount.Append(sqlWhere.ToString());
DataTable _dt = _accessMySql.getDataTable(sqlCount.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
//得到滿足條件的總行數
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
}
///得到應分頁的數據
sqlWhere.AppendFormat(" LIMIT {0},{1} ;", query.Start, query.Limit);
sql.Append(sqlWhere);
listStore = _accessMySql.getDataTableForObj<ProductSearchQuery>(sql.ToString());
}
return listStore;
}
catch (Exception ex)
{
throw new Exception("ProductSearchDao.GetListQuery-->" + ex.Message + sql.ToString());
}
}
