public DataTable GetProductClickList(ProductClickQuery query, out int totalCount)
{
try
{
return pcDao.GetProductClickList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("ProductClickMgr-->GetProductClickList" + ex.Message, ex);
}
}
public HttpResponseBase GetProductClickList()
{
string json = string.Empty;
try
{
ProductClickQuery query = new ProductClickQuery();
pcMgr = new ProductClickMgr(mySqlConnectionString);
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["product_status"]))
{
query.product_status = uint.Parse(Request.Params["product_status"]);
}
//支持空格，中英文逗號隔開
if (!string.IsNullOrEmpty(Request.Params["product_id"]))
{
//query.pids = Request.Params["product_id"].Replace(" ", ",");
query.pids=Regex.Replace(Request.Params["product_id"].Trim(), "(\\s+)|(，)|(\\,)", ",");
//query.pids = Request.Params["product_id"].Replace("|", ",");
}
if (!string.IsNullOrEmpty(Request.Params["brand_id"]))
{
query.brand_id = uint.Parse(Request.Params["brand_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["prod_classify"]))
{
query.prod_classify = uint.Parse(Request.Params["prod_classify"]);
}
//if (!string.IsNullOrEmpty(Request.Params["startdate"]))
//{
// query.sclick_year = int.Parse(Request.Params["startdate"].Substring(0, 4));
// query.sclick_month = int.Parse(Request.Params["startdate"].Substring(5, 2));
// query.sclick_day = int.Parse(Request.Params["startdate"].Substring(8, 2));
//}
//if (!string.IsNullOrEmpty(Request.Params["enddate"]))
//{
// query.eclick_year = int.Parse(Request.Params["enddate"].Substring(0, 4));
// query.eclick_month = int.Parse(Request.Params["enddate"].Substring(5, 2));
// query.eclick_day = int.Parse(Request.Params["enddate"].Substring(8, 2));
//}
switch (Request.Params["type"])
{
case "b":
break;
case "y":
query.click_year = 1;
break;
case "m":
query.click_month = 1;
break;
case "d":
query.click_day = 1;
break;
}
if (!string.IsNullOrEmpty(Request.Params["startdate"]))
{
query.sclick_id = uint.Parse(Request.Params["startdate"].Substring(0, 10).Replace("-", "") + "00");
}
if (!string.IsNullOrEmpty(Request.Params["enddate"]))
{
query.eclick_id = uint.Parse(Request.Params["enddate"].Substring(0, 10).Replace("-", "") + "23");
}
int totalCount = 0;
DataTable dt = pcMgr.GetProductClickList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
//listUser是准备转换的对象
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dt, 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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public void OutProductClickExcel()
{
string json = string.Empty;
try
{
ProductClickQuery query = new ProductClickQuery();
pcMgr = new ProductClickMgr(mySqlConnectionString);
query.IsPage = false;
if (Request.Params["product_status"] != "null" && !string.IsNullOrEmpty(Request.Params["product_status"]))
{
query.product_status = uint.Parse(Request.Params["product_status"]);
}
if (!string.IsNullOrEmpty(Request.Params["product_id"]))
{
//支持空格，中英文逗號隔開
if (!string.IsNullOrEmpty(Request.Params["product_id"]))
{
query.pids = Regex.Replace(Request.Params["product_id"].Trim(), "(\\s+)|(，)|(\\,)", ",");
}
}
if (Request.Params["brand_id"] != "null" && !string.IsNullOrEmpty(Request.Params["brand_id"]))
{
query.brand_id = uint.Parse(Request.Params["brand_id"]);
}
if (Request.Params["prod_classify"] != "null" && !string.IsNullOrEmpty(Request.Params["prod_classify"]))
{
query.prod_classify = uint.Parse(Request.Params["prod_classify"]);
}
//if (!string.IsNullOrEmpty(Request.Params["startdate"]))
//{
// query.sclick_year = int.Parse(Request.Params["startdate"].Substring(0, 4));
// query.sclick_month = int.Parse(Request.Params["startdate"].Substring(5, 2));
// query.sclick_day = int.Parse(Request.Params["startdate"].Substring(8, 2));
//}
//if (!string.IsNullOrEmpty(Request.Params["enddate"]))
//{
// query.eclick_year = int.Parse(Request.Params["enddate"].Substring(0, 4));
// query.eclick_month = int.Parse(Request.Params["enddate"].Substring(5, 2));
// query.eclick_day = int.Parse(Request.Params["enddate"].Substring(8, 2));
//}
switch (Request.Params["type"])
{
case "b":
break;
case "y":
query.click_year = 1;
break;
case "m":
query.click_month = 1;
break;
case "d":
query.click_day = 1;
break;
}
if (!string.IsNullOrEmpty(Request.Params["startdate"]))
{
query.sclick_id = uint.Parse(Request.Params["startdate"].Substring(0, 10).Replace("-", "") + "00");
}
if (!string.IsNullOrEmpty(Request.Params["enddate"]))
{
query.eclick_id = uint.Parse(Request.Params["enddate"].Substring(0, 10).Replace("-", "") + "23");
}
int totalCount = 0;
DataTable dt = pcMgr.GetProductClickList(query, out totalCount);
dt.Columns["product_id"].ColumnName = "商品編號";
dt.Columns["product_name"].ColumnName = "商品名稱";
dt.Columns["brand_name"].ColumnName = "品牌名稱";
dt.Columns["prod_classify"].ColumnName = "商品館別";
dt.Columns["click_year"].ColumnName = "年";
dt.Columns["click_month"].ColumnName = "月";
dt.Columns["click_day"].ColumnName = "日";
dt.Columns["click_total"].ColumnName = "點擊次數";
switch (Request.Params["type"])
{
case "b":
break;
case "y":
dt.Columns.Remove("月");
dt.Columns.Remove("日");
break;
case "m":
dt.Columns.Remove("年");
dt.Columns.Remove("日");
break;
case "d":
dt.Columns.Remove("年");
dt.Columns.Remove("月");
break;
}
//dt.DefaultView.ToTable(false, new string[] { "product_id", "product_name", "brand_name_simple", "prod_classify" });
string fileName = "商品點擊次數統計_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dt, "");
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
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);
}
}
public DataTable GetProductClickList(ProductClickQuery query, out int totalCount)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
StringBuilder sqlcondi = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
totalCount = 0;
string total = string.Empty;
if (query.click_year != 1 && query.click_month != 1 && query.click_day != 1)
{
total = ",click_total ";
}
else
{
total = ",SUM(pc.click_total) AS click_total ";
}
sql.AppendFormat(@"SELECT pc.product_id,p.product_name,vb.brand_name,p.prod_classify,");
//sql.AppendFormat(@"pc.click_hour,pc.click_week,");
sql.AppendFormat(@"pc.click_year,pc.click_month,pc.click_day ");
sql.Append(total);
sqlwhere.AppendFormat(@" FROM product_click pc ");
sqlwhere.AppendFormat(@" LEFT JOIN product p ON pc.product_id=p.product_id");
sqlwhere.AppendFormat(@" LEFT JOIN vendor_brand vb ON p.brand_id=vb.brand_id");
if (!string.IsNullOrEmpty(query.pids))
{
sqlcondi.AppendFormat(@" AND pc.product_id IN({0})", query.pids);
}
if (query.product_status != 999)
{
sqlcondi.AppendFormat(@" AND p.product_status='{0}'", query.product_status);
}
if (query.prod_classify != 0)
{
sqlcondi.AppendFormat(@" AND p.prod_classify='{0}'", query.prod_classify);
}
if (query.brand_id != 0)
{
sqlcondi.AppendFormat(@" AND p.brand_id='{0}'", query.brand_id);
}
if (query.sclick_id != 0)
{
sqlcondi.AppendFormat(@" AND pc.click_id>='{0}' ", query.sclick_id);
}
if (query.eclick_id != 0)
{
sqlcondi.AppendFormat(@" AND pc.click_id<='{0}' ", query.eclick_id);
}
if (sqlcondi.Length != 0)
{
sqlwhere.Append(" WHERE ");
sqlwhere.Append(sqlcondi.ToString().TrimStart().Remove(0, 3));
}
//選擇的是按年統計
if (query.click_year == 1)
{
sqlwhere.AppendFormat(@"GROUP BY pc.product_id");
sqlwhere.AppendFormat(@", pc.click_year ");
}
else if (query.click_month == 1) //選擇的是按月統計
{
sqlwhere.AppendFormat(@"GROUP BY pc.product_id");
sqlwhere.AppendFormat(@", pc.click_month ");
}
else if (query.click_day == 1)//選擇的是按天統計
{
sqlwhere.AppendFormat(@"GROUP BY pc.product_id");
sqlwhere.AppendFormat(@", pc.click_day ");
}
sql.Append(sqlwhere.ToString());
try
{
if (query.IsPage)
{
sql.AppendFormat(@" LIMIT {0},{1};", query.Start, query.Limit);
DataTable dt = _access.getDataTable("SELECT count( pc.product_id) totalCount " + sqlwhere.ToString());
if (dt != null && dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
}
}
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("ProductClickDao-->GetProductClickList" + ex.Message + sql.ToString(), ex);
}
}
