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()); } }