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