public DataTable GetBrowseDataList(BrowseDataQuery query, out int totalCount) { try { return _IBrowseDataDao.GetBrowseDataList(query, out totalCount); } catch (Exception ex) { throw new Exception("BrowseDataMgr-->GetBrowseDataList-->" + ex.Message, ex); } }
/// <summary> /// 商品點擊信息列表 /// </summary> /// <returns></returns> public HttpResponseBase BrowseDataList() { string json = string.Empty; DataTable dtBrowseData = new DataTable(); int totalCount = 0; BrowseDataQuery query = new BrowseDataQuery(); string type=Request.Params["type"]; string searchContent = Request.Params["searchContent"]; if (!string.IsNullOrEmpty(type)) { query.type = Convert.ToInt32(type); } if (!string.IsNullOrEmpty(searchContent)) { query.SearchCondition = searchContent; } if (!string.IsNullOrEmpty(Request.Params["true"])) { if (Request.Params["true"] == "true") { query.isSecret = true; } else { query.isSecret = false; } } if (!string.IsNullOrEmpty(Request.Params["searchType"])) { query.SearchType = Convert.ToInt32(Request.Params["searchType"]); } query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); _IBrowseDataMgr = new BrowseDataMgr(mySqlConnectionString); _IProductItemMgr=new ProductItemMgr(mySqlConnectionString); _IOrderMasterMgr = new OrderMasterMgr(mySqlConnectionString); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; try { dtBrowseData = _IBrowseDataMgr.GetBrowseDataList(query, out totalCount); if (dtBrowseData.Rows.Count>0) { dtBrowseData.Columns.Add("buyCount"); for (int i = 0; i < dtBrowseData.Rows.Count; i++) { string user_id = dtBrowseData.Rows[i]["user_id"].ToString(); string product_id = dtBrowseData.Rows[i]["product_id"].ToString(); string item_id = string.Empty; if (query.isSecret) { dtBrowseData.Rows[i]["user_name"] = dtBrowseData.Rows[i]["user_name"].ToString().Substring(0, 1) + "**"; } List<ProductItem> listProductItem = _IProductItemMgr.GetProductItemByID(Convert.ToInt32(product_id)); if (listProductItem.Count > 0) { item_id = listProductItem[0].Item_Id.ToString(); } OrderMasterQuery orderMasterQuery = new OrderMasterQuery { User_Id = Convert.ToUInt32(dtBrowseData.Rows[i]["user_id"]), Item_Id = Convert.ToInt32(item_id) }; dtBrowseData.Rows[i]["buyCount"] = _IOrderMasterMgr.GetBuyCount(orderMasterQuery); } json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dtBrowseData, Formatting.Indented, timeConverter) + "}"; } else { json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dtBrowseData, Formatting.Indented, timeConverter) + "}"; //json = "{success:false,msg:0}"; } } 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,msg:0}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
/// <summary> /// 獲取商品點擊信息 /// </summary> /// <param name="query">查詢條件</param> /// <param name="totalCount">數據總條數</param> /// <returns>商品點擊信息</returns> public DataTable GetBrowseDataList(BrowseDataQuery query, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlColumn = new StringBuilder(); StringBuilder sqlCount = new StringBuilder("SELECT count(bd.id) as totalCount "); StringBuilder sqlTable = new StringBuilder(); StringBuilder sqlCondition = new StringBuilder(); query.Replace4MySQL(); sqlColumn.Append("SELECT bd.id,bd.user_id,bd.product_id,bd.type,bd.count,u.user_name,p.product_name "); sqlTable.Append(" FROM browse_data bd "); sqlTable.Append(" LEFT JOIN users u ON bd.user_id=u.user_id "); sqlTable.Append(" LEFT JOIN product p ON bd.product_id=p.product_id "); sqlCondition.Append(" WHERE 1=1 "); if (query.id != 0) { sqlCondition.AppendFormat( " and bd.id='{0}' ",query.id); } if (query.type != 0) { //sqlCondition.AppendFormat(" AND db.type IN({0}) ", query.type); sqlCondition.AppendFormat(" AND bd.type ='{0}' ",query.type); } if (query.SearchType != 0) { if (!string.IsNullOrEmpty(query.SearchCondition)) { switch (query.SearchType) { case 1: sqlCondition.AppendFormat(" AND bd.user_id={0} ", query.SearchCondition); break; case 2: sqlCondition.AppendFormat(" AND bd.product_id={0} ", query.SearchCondition); break; case 3: sqlCondition.AppendFormat(" AND u.user_name LIKE '%{0}%' ", query.SearchCondition); break; case 4: sqlCondition.AppendFormat(" AND p.product_name LIKE '%{0}%' ", query.SearchCondition); break; } //sqlCondition.AppendFormat(" and (bd.user_id='{0}' or bd.product_id='{0}' or u.user_name LIKE '%{0}%' or p.product_name LIKE '%{0}%' ) ", query.SearchCondition); //sqlCondition.AppendFormat(" OR bd.user_id={0} ", query.SearchCondition); //sqlCondition.AppendFormat(" OR bd.product_id={0} ", query.SearchCondition); //sqlCondition.AppendFormat(" OR u.user_name LIKE '%{0}%' ", query.SearchCondition); //sqlCondition.AppendFormat(" OR p.product_name LIKE '%{0}%' ", query.SearchCondition); } } sqlCondition.Append(" ORDER BY bd.count DESC "); totalCount = 0; try { if (query.IsPage) { DataTable _dt = _accessMySql.getDataTable(sqlCount.ToString() + sqlTable.ToString() + sqlCondition.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]); } sqlCondition.AppendFormat(" limit {0},{1};", query.Start, query.Limit); } sql.Append(sqlColumn).Append(sqlTable).Append(sqlCondition); return _accessMySql.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("BrowseDataDao-->GetBrowseDataList -->" + ex.Message + sql.ToString(), ex); } }