public List<SiteAnalytics> GetSiteAnalyticsList(SiteAnalytics query, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); totalCount = 0; try { sql.Append(" select saly.sa_id,saly.sa_date,saly.sa_session, saly.sa_create_time,saly.sa_user,saly.sa_create_user,mu.user_username as 's_sa_create_user',mu1.user_username as sa_modify_username, "); sql.Append("sa_pageviews,sa_pages_session,sa_bounce_rate,sa_avg_session_duration,sa_modify_time "); sqlFrom.Append(" from site_analytics saly LEFT JOIN manage_user mu on mu.user_id=saly.sa_create_user "); sqlFrom.Append("left join manage_user mu1 on mu1.user_id=saly.sa_modify_user"); sqlWhere.Append(" where 1=1 "); if (query.search_con != 0) { sqlWhere.AppendFormat(" and saly.sa_date='{0}' ", query.s_sa_date); } if (query.IsPage) { DataTable _dt = _accessMySql.getDataTable("select count(saly.sa_id) as totalCount " + sqlFrom.ToString() + sqlWhere.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]); } } sqlWhere.AppendFormat(" order by sa_date desc limit {0},{1}; ", query.Start, query.Limit); return _accessMySql.getDataTableForObj<SiteAnalytics>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("SiteAnalyticsDao-->GetSiteAnalyticsList-->" + ex.Message + sql.ToString(), ex); } }
public List<SiteAnalytics> GetSiteAnalyticsList(SiteAnalytics query, out int totalCount) { try { return _siteAnalytics.GetSiteAnalyticsList(query, out totalCount); } catch (Exception ex) { throw new Exception("SiteAnalyticsMgr-->GetSiteAnalyticsList-->" + ex.Message, ex); } }
public bool ImportExcelToDt(SiteAnalytics query, DataTable _dt) { StringBuilder sql = new StringBuilder(); StringBuilder insertSql = new StringBuilder(); StringBuilder selectSql = new StringBuilder(); StringBuilder updateSql = new StringBuilder(); try { for (int i = 0; i < _dt.Rows.Count; i++) { selectSql = new StringBuilder(); if (_dt.Rows[i][0].ToString() != "" && _dt.Rows[i][1].ToString() != "" && _dt.Rows[i][2].ToString() != "") { string sa_date = Convert.ToDateTime(_dt.Rows[i][0]).ToString("yyyy-MM-dd"); selectSql.AppendFormat(" select sa_id from site_analytics where sa_date='{0}'", sa_date); DataTable _selectDt = _accessMySql.getDataTable(selectSql.ToString()); if (_selectDt.Rows.Count > 0)//有此條數據,更新 { updateSql.AppendFormat(" update site_analytics set sa_date='{0}',sa_work_stage='{1}',sa_user='******',sa_create_time='{3}' where sa_id='{4}';", _dt.Rows[i][0], _dt.Rows[i][1].ToString().Replace(',', ' ').Replace(" ", ""), _dt.Rows[i][2].ToString().Replace(',', ' ').Replace(" ", ""), CommonFunction.DateTimeToString(DateTime.Now), _selectDt.Rows[0][0]); } else//新增 { insertSql.Append("insert into site_analytics(sa_date,sa_work_stage,sa_user,sa_create_time,sa_create_user) values( "); insertSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}');", sa_date, _dt.Rows[i][1].ToString().Replace(',', ' ').Replace(" ", ""), _dt.Rows[i][2].ToString().Replace(',', ' ').Replace(" ", ""), CommonFunction.DateTimeToString(DateTime.Now), query.sa_create_user); } } } sql.Append(updateSql.ToString() + insertSql.ToString()); if (sql.ToString() != "") { if (_accessMySql.execCommand(sql.ToString()) > 0) { return true; } else { return false; } } else { return true; } } catch (Exception ex) { throw new Exception("SiteAnalyticsDao-->ImportExcelToDt-->" + ex.Message + sql.ToString(), ex); } }
public JsonResult SaveSiteAnalytics() { string json = string.Empty; SiteAnalytics query = new SiteAnalytics(); DateTime date; int number = 0; float number1 = 0; try { if (!string.IsNullOrEmpty(Request.Params["sa_pageviews"])) { if (int.TryParse(Request.Params["sa_pageviews"], out number)) { query.sa_pageviews = number; } } if (!string.IsNullOrEmpty(Request.Params["sa_pages_session"])) { if (float.TryParse(Request.Params["sa_pages_session"], out number1)) { query.sa_pages_session = number1; } } if (!string.IsNullOrEmpty(Request.Params["sa_bounce_rate"])) { if (float.TryParse(Request.Params["sa_bounce_rate"], out number1)) { query.sa_bounce_rate = number1; } } if (!string.IsNullOrEmpty(Request.Params["sa_avg_session_duration"])) { if (float.TryParse(Request.Params["sa_avg_session_duration"], out number1)) { query.sa_avg_session_duration = number1; } } if (!string.IsNullOrEmpty(Request.Params["sa_id"])) { query.sa_id = Convert.ToInt32(Request.Params["sa_id"]); } if (DateTime.TryParse(Request.Params["sa_date"], out date)) { query.s_sa_date = date.ToString("yyyy-MM-dd"); } if (!string.IsNullOrEmpty(Request.Params["sa_session"])) { if (int.TryParse(Request.Params["sa_session"], out number)) { query.sa_session = number; } } if (!string.IsNullOrEmpty(Request.Params["sa_user"])) { if (int.TryParse(Request.Params["sa_user"], out number)) { query.sa_user = number; } } query.sa_create_user = (Session["caller"] as Caller).user_id; query.sa_create_time=DateTime.Now; _siteAnalytics = new SiteAnalyticsMgr(mySqlConnectionString); query.sa_modify_user = query.sa_create_user; query.sa_modify_time=query.sa_create_time; if (query.sa_id == 0) { if (_siteAnalytics.InsertSiteAnalytics(query) > 0) { return Json(new { success = "true" }); } else { return Json(new { success = "false" }); } } else { if (_siteAnalytics.UpdateSiteAnalytics(query) > 0) { return Json(new { success = "true" }); } else { return Json(new { success = "false" }); } } } 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); return Json(new { success = "false" }); } }
public string InsertSNA(SiteAnalytics query) { StringBuilder insertSql = new StringBuilder(); try { insertSql.Append("insert into site_analytics(sa_date,sa_session,sa_user,sa_create_time,sa_create_user,sa_pageviews,sa_pages_session,sa_bounce_rate,sa_avg_session_duration,sa_modify_time,sa_modify_user) values( "); insertSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}');", query.s_sa_date, query.sa_session, query.sa_user, CommonFunction.DateTimeToString(query.sa_create_time), query.sa_create_user, query.sa_pageviews, query.sa_pages_session, query.sa_bounce_rate, query.sa_avg_session_duration, Common.CommonFunction.DateTimeToString(query.sa_modify_time),query.sa_modify_user ); return insertSql.ToString(); } catch (Exception ex) { throw new Exception("SiteAnalyticsDao-->InsertSNA-->" + ex.Message + insertSql.ToString(), ex); } }
public int UpdateSiteAnalytics(SiteAnalytics query) { string sql = string.Empty; try { sql = UpdateSNA(query); return _accessMySql.execCommand(sql); } catch (Exception ex) { throw new Exception("SiteAnalyticsDao-->UpdateSiteAnalytics-->" + ex.Message + sql.ToString(), ex); } }
public int IsExist(SiteAnalytics query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat(" select sa_id from site_analytics where sa_date='{0}'", query.s_sa_date); DataTable _dt = _accessMySql.getDataTable(sql.ToString()); if (_dt.Rows.Count > 0) { return Convert.ToInt32(_dt.Rows[0][0]); } else { return 0; } } catch (Exception ex) { throw new Exception("SiteAnalyticsDao-->IsExist-->" + ex.Message + sql.ToString(), ex); } }
public string UpdateSNA(SiteAnalytics query) { StringBuilder updateSql = new StringBuilder(); try { updateSql.AppendFormat(" update site_analytics set sa_date='{0}',sa_session='{1}',sa_user='******',sa_modify_time='{3}',", query.s_sa_date, query.sa_session, query.sa_user, CommonFunction.DateTimeToString(query.sa_modify_time)); updateSql.AppendFormat("sa_pageviews='{0}',sa_pages_session='{1}',sa_bounce_rate='{2}',sa_avg_session_duration='{3}',sa_modify_user='******' where sa_id='{4}';", query.sa_pageviews, query.sa_pages_session, query.sa_bounce_rate, query.sa_avg_session_duration,query.sa_id,query.sa_modify_user); return updateSql.ToString(); } catch (Exception ex) { throw new Exception("SiteAnalyticsDao-->UpdateSNA-->" + ex.Message + updateSql.ToString(), ex); } }
public JsonResult CheckSiteAnalytics() { try { SiteAnalytics query = new SiteAnalytics(); _siteAnalytics = new SiteAnalyticsMgr(mySqlConnectionString); DateTime date; if (DateTime.TryParse(Request.Params["sa_date"], out date)) { query.s_sa_date = date.ToString("yyyy-MM-dd"); int num = _siteAnalytics.IsExistSiteAnalytics(query); if (num > 0) { return Json(new { success = "true" }); } } } 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); } return Json(new { success = "false" }); }
public DataTable SiteAnalyticsDt(SiteAnalytics query) { StringBuilder sql = new StringBuilder(); StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); try { sql.Append(" select CASE saly.sa_date WHEN '0000-00-00' THEN '0001-01-01' ELSE sa_date END AS sa_date,saly.sa_session,saly.sa_user,sa_avg_session_duration,sa_bounce_rate,sa_pages_session,sa_pageviews from site_analytics saly "); sqlWhere.Append(" where 1=1 "); if (query.search_con != 0) { sqlWhere.AppendFormat(" and saly.sa_date ='{0}' ", query.s_sa_date); } sqlWhere.Append(" order by sa_date desc;"); return _accessMySql.getDataTable(sql.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("SiteAnalyticsDao-->SiteAnalyticsDt-->" + ex.Message + sql.ToString(), ex); } }
public int DeleteSiteAnalytics(SiteAnalytics query) { string sql = string.Empty; try { sql = string.Format("delete from site_analytics where sa_id in({0})", query.sa_ids); return _accessMySql.execCommand(sql); } catch (Exception ex) { throw new Exception("SiteAnalyticsDao-->UpdateSiteAnalytics-->" + ex.Message + sql.ToString(), ex); } }
public int IsExistSiteAnalytics(SiteAnalytics query) { try { return _siteAnalytics.IsExist(query); } catch (Exception ex) { throw new Exception("SiteAnalyticsMgr-->IsExistSiteAnalytics-->" + ex.Message, ex); } }
public void ExportExcel() { try { BLL.gigade.Model.SiteAnalytics query = new BLL.gigade.Model.SiteAnalytics(); _siteAnalytics = new SiteAnalyticsMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["search_con"])) { query.search_con = Convert.ToInt32(Request.Params["search_con"]); } if (!string.IsNullOrEmpty(Request.Params["serch_sa_date"])) { query.s_sa_date = (Convert.ToDateTime(Request.Params["serch_sa_date"]).ToString("yyyy-MM-dd")); } DataTable _dt = _siteAnalytics.SiteAnalyticsDt(query); DataTable _newDt = new DataTable(); _newDt.Columns.Add("日索引", typeof(string)); _newDt.Columns.Add("造訪數", typeof(string)); _newDt.Columns.Add("使用者", typeof(string)); _newDt.Columns.Add("瀏覽量", typeof(string)); _newDt.Columns.Add("單次造訪頁數", typeof(string)); _newDt.Columns.Add("跳出率", typeof(string)); _newDt.Columns.Add("平均停留時間(s)", typeof(string)); for (int i = 0; i < _dt.Rows.Count; i++) { DataRow newRow = _newDt.NewRow(); newRow[0] = Convert.ToDateTime(_dt.Rows[i]["sa_date"]).ToString("yyyy-MM-dd"); ; newRow[1] = _dt.Rows[i]["sa_session"].ToString(); newRow[2] = _dt.Rows[i]["sa_user"].ToString(); newRow[3] = _dt.Rows[i]["sa_pageviews"].ToString(); newRow[4] = _dt.Rows[i]["sa_pages_session"].ToString(); newRow[5] = _dt.Rows[i]["sa_bounce_rate"].ToString(); newRow[6] = _dt.Rows[i]["sa_avg_session_duration"].ToString(); _newDt.Rows.Add(newRow); } string fileName = "SiteAnalytics目標對" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDT(_newDt, ""); 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 HttpResponseBase ImportExcel() { string json = string.Empty; try { BLL.gigade.Model.SiteAnalytics query = new BLL.gigade.Model.SiteAnalytics(); if (Request.Files.Count > 0) { string path = Request.Params["ImportExcel"]; HttpPostedFileBase excelFile = Request.Files["ImportExcel"]; FileManagement fileManagement = new FileManagement(); string newExcelName = Server.MapPath(excelPath) + "analytics" + fileManagement.NewFileName(excelFile.FileName); excelFile.SaveAs(newExcelName); NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName); DataTable _dt = helper.ExcelToTableForXLSX(); _siteAnalytics = new SiteAnalyticsMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["search_con"])) { query.search_con = Convert.ToInt32(Request.Params["search_con"]); } if (!string.IsNullOrEmpty(Request.Params["serch_sa_date"])) { query.s_sa_date = (Convert.ToDateTime(Request.Params["serch_sa_date"]).ToString("yyyy-MM-dd")); } json = _siteAnalytics.ImportExcelToDt(_dt);//匯入成功 } } 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 HttpResponseBase GetSiteAnalyticsList() { string json = string.Empty; try { SiteAnalytics query = new BLL.gigade.Model.SiteAnalytics(); List<SiteAnalytics> store = new List<BLL.gigade.Model.SiteAnalytics>(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); int totalCount = 0; if (!string.IsNullOrEmpty(Request.Params["search_con"])) { query.search_con = Convert.ToInt32(Request.Params["search_con"]); } if (!string.IsNullOrEmpty(Request.Params["serch_sa_date"])) { query.s_sa_date = (Convert.ToDateTime(Request.Params["serch_sa_date"]).ToString("yyyy-MM-dd")); } _siteAnalytics = new SiteAnalyticsMgr(mySqlConnectionString); store = _siteAnalytics.GetSiteAnalyticsList(query, out totalCount); foreach (var item in store) { item.s_sa_date = (item.sa_date).ToString("yyyy-MM-dd"); item.s_sa_create_time = (item.sa_create_time).ToString("yyyy-MM-dd HH:mm:ss"); item.sa_modify_time_query = (item.sa_modify_time).ToString("yyyy-MM-dd HH:mm:ss"); } json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented) + "}"; } 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.ToString()); this.Response.End(); return this.Response; }
public DataTable SiteAnalyticsDt(SiteAnalytics query) { try { return _siteAnalytics.SiteAnalyticsDt(query); } catch (Exception ex) { throw new Exception("SiteAnalyticsMgr-->SiteAnalyticsDt-->" + ex.Message, ex); } }
public string ImportExcelToDt(DataTable _dt) { string json = "{success:'true'}"; ArrayList arrList = new ArrayList(); SiteAnalytics query = new SiteAnalytics(); try { float number = 0; for (int i = 0; i < _dt.Rows.Count; i++) { if (_dt.Rows[i][0].ToString() != "" && _dt.Rows[i][1].ToString() != "" && _dt.Rows[i][2].ToString() != "") { query = new SiteAnalytics(); query.s_sa_date = Convert.ToDateTime(_dt.Rows[i][0]).ToString("yyyy-MM-dd"); query.sa_id = _siteAnalytics.IsExist(query); query.s_sa_date = Convert.ToDateTime(_dt.Rows[i][0]).ToString("yyyy-MM-dd"); query.sa_session = Convert.ToInt32(_dt.Rows[i][1].ToString().Replace(',', ' ').Replace(" ", "")); query.sa_user = Convert.ToInt32(_dt.Rows[i][2].ToString().Replace(',', ' ').Replace(" ", "")); query.sa_pageviews = Convert.ToInt32(_dt.Rows[i][3].ToString().Replace(',', ' ').Replace(" ", "")); query.sa_pages_session = Convert.ToSingle(_dt.Rows[i][4].ToString().Replace(',', ' ').Replace(" ", "")); if (float.TryParse(_dt.Rows[i][5].ToString(), out number)) { if (number < 1) { query.sa_bounce_rate = Convert.ToSingle(_dt.Rows[i][5].ToString()); } else { return json = "{success:'false'}"; } } if (float.TryParse(_dt.Rows[i][6].ToString(), out number)) { query.sa_avg_session_duration = Convert.ToSingle(_dt.Rows[i][6].ToString()); } query.sa_create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; query.sa_create_time = DateTime.Now; query.sa_modify_time = query.sa_create_time; query.sa_modify_user = query.sa_create_user; if (query.sa_id > 0) { arrList.Add(_siteAnalytics.UpdateSNA(query)); } else { arrList.Add(_siteAnalytics.InsertSNA(query)); } } } if (arrList.Count > 0) { if (_siteAnalytics.ExecSql(arrList)) { json = "{success:'true'}"; } } return json; // return _siteAnalytics.ImportExcelToDt(query, _dt); } catch (Exception ex) { throw new Exception("SiteAnalyticsMgr-->ImportExcelToDt-->" + ex.Message, ex); } }
public JsonResult DeleteSiteAnalyticsById() { try { SiteAnalytics query = new SiteAnalytics(); if (!string.IsNullOrEmpty(Request.Params["ids"])) { query.sa_ids = Request.Params["ids"].TrimEnd(','); } _siteAnalytics = new SiteAnalyticsMgr(mySqlConnectionString); if (_siteAnalytics.DeleteSiteAnalytics(query) > 0) { return Json(new { success = "true" }); } return Json(new { success = "false" }); } 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); return Json(new { success = "false" }); } }
public int DeleteSiteAnalytics(SiteAnalytics query) { try { return _siteAnalytics.DeleteSiteAnalytics(query); } catch (Exception ex) { throw new Exception("SiteAnalyticsMgr-->DeleteSiteAnalytics-->" + ex.Message, ex); } }