public int Insert(SiteStatistics ss) { try { return ssDao.Insert(ss); } catch (Exception ex) { throw new Exception("SiteStatisticsDao-->Insert" + ex.Message, ex); } }
public int Delete(SiteStatistics ss) { try { return ssDao.Delete(ss); } catch (Exception ex) { throw new Exception("SiteStatisticsDao-->Delete" + ex.Message, ex); } }
public DataTable GetSiteStatisticsList(SiteStatistics query, out int totalCount) { try { return ssDao.GetSiteStatisticsList(query, out totalCount); } catch (Exception ex) { throw new Exception("SiteStatisticsDao-->GetSiteStatisticsList" + ex.Message, ex); } }
public string ImportExcelToDt(DataTable _dt) { string json = "{success:'true'}"; SiteStatistics query = new SiteStatistics(); query.ss_create_time = DateTime.Now; query.ss_create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; query.ss_modify_time = query.ss_create_time; query.ss_modify_user = query.ss_create_user; try { int total = 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.ss_date = Convert.ToDateTime(_dt.Rows[i][0]); query.ss_show_num = Convert.ToInt32(_dt.Rows[i][1]); query.ss_click_num = Convert.ToInt32(_dt.Rows[i][2]); query.ss_click_through = Convert.ToSingle(_dt.Rows[i][3]); query.ss_cost = Convert.ToSingle(_dt.Rows[i][4]); query.ss_newuser_number = Convert.ToInt32(_dt.Rows[i][5]); query.ss_converted_newuser = Convert.ToInt32(_dt.Rows[i][6]); query.ss_sum_order_amount = Convert.ToInt32(_dt.Rows[i][7]); query.ss_code = _dt.Rows[i][8].ToString().ToUpper(); DataTable dt=GetSiteStatisticsList(query, out total); if (dt.Rows.Count == 0) { ssDao.Insert(query); } else { query.ss_id = Convert.ToInt32(dt.Rows[0][0]); ssDao.Update(query); query.ss_id = 0; } } } } catch (Exception ex) { throw new Exception("SiteStatisticsDao-->ImportExcelToDt" + ex.Message, ex); } return json; }
public DataTable GetSiteStatisticsList(SiteStatistics query, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); totalCount = 0; sql.AppendFormat(@"SELECT ss_id,ss_show_num,ss_click_num,ss_click_through,ss_cost,ss_newuser_number,"); sql.AppendFormat(@" ss_converted_newuser,ss_sum_order_amount,ss_date,ss_code,ss_create_time,mu.user_username as ss_create_username,"); sql.AppendFormat(@" ss_modify_time,mu1.user_username as ss_modify_username "); sqlwhere.AppendFormat(@" FROM site_statistics ss LEFT JOIN manage_user mu on mu.user_id=ss.ss_create_user"); sqlwhere.Append(" left join manage_user mu1 on mu1.user_id=ss.ss_modify_user "); sqlwhere.AppendFormat(@" WHERE 1=1 "); if (query.ss_id != 0) { sqlwhere.AppendFormat(@" AND ss_id='{0}' ", query.ss_id); } if (!string.IsNullOrEmpty(query.ss_code)) { sqlwhere.AppendFormat(@" AND ss_code='{0}' ", query.ss_code); } if (query.ss_date != DateTime.MinValue) { sqlwhere.AppendFormat(@" AND ss_date ='{0}' ",query.ss_date.ToString("yyyy-MM-dd")); } if (query.sss_date != DateTime.MinValue && query.ess_date != DateTime.MinValue) { sqlwhere.AppendFormat(@" AND ss_date>='{0}' AND ss_date<='{1}' ", Common.CommonFunction.DateTimeToString(query.sss_date), Common.CommonFunction.DateTimeToString(query.ess_date)); } sql.Append(sqlwhere.ToString()); sql.AppendFormat(@" ORDER BY ss_id DESC "); try { if (query.IsPage) { sql.AppendFormat(@" LIMIT {0},{1};", query.Start, query.Limit); DataTable dt = _access.getDataTable("SELECT ss_id " + sqlwhere.ToString()); totalCount = dt.Rows.Count; } return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("SiteStatisticsDao-->GetSiteStatisticsList" + ex.Message + sql.ToString(), ex); } }
public int Insert(SiteStatistics ss) { StringBuilder sql = new StringBuilder(); try { sql.AppendLine(@"INSERT INTO site_statistics (ss_show_num,ss_click_num,ss_click_through,ss_cost,"); sql.AppendLine(@"ss_sum_order_amount,ss_newuser_number,ss_converted_newuser,ss_date,"); sql.AppendLine(@"ss_code,ss_create_time,ss_create_user,"); sql.AppendLine(@" ss_modify_time,ss_modify_user ) VALUES("); sql.AppendFormat(@" '{0}','{1}','{2}','{3}',", ss.ss_show_num, ss.ss_click_num, ss.ss_click_through, ss.ss_cost); sql.AppendFormat(@" '{0}','{1}','{2}','{3}',", ss.ss_sum_order_amount, ss.ss_newuser_number, ss.ss_converted_newuser, ss.ss_date.ToString("yyyy-MM-dd")); sql.AppendFormat(@" '{0}','{1}','{2}',", ss.ss_code, ss.ss_create_time.ToString("yyyy-MM-dd HH:mm:ss"), ss.ss_create_user); sql.AppendFormat(@" '{0}','{1}');", ss.ss_modify_time.ToString("yyyy-MM-dd HH:mm:ss"), ss.ss_modify_user); return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("SiteStatisticsDao-->Insert" + ex.Message + sql.ToString(), ex); } }
public int Update(SiteStatistics ss) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat(@"UPDATE site_statistics SET ss_show_num='{0}',ss_click_num='{1}',",ss.ss_show_num,ss.ss_click_num); sql.AppendFormat(@"ss_click_through='{0}',ss_cost='{1}', ",ss.ss_click_through,ss.ss_cost); sql.AppendFormat(@"ss_sum_order_amount='{0}',ss_newuser_number='{1}',ss_converted_newuser='******',ss_date='{3}',", ss.ss_sum_order_amount, ss.ss_newuser_number, ss.ss_converted_newuser, ss.ss_date.ToString("yyyy-MM-dd")); sql.AppendFormat(@"ss_code='{0}',",ss.ss_code); sql.AppendFormat(@" ss_modify_time='{0}',ss_modify_user='******' ", ss.ss_modify_time.ToString("yyyy-MM-dd HH:mm:ss"), ss.ss_modify_user); sql.AppendFormat(@" WHERE 1=1 "); if(ss.ss_id!=0) { sql.AppendFormat(@" AND ss_id='{0}';", ss.ss_id); } return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("SiteStatisticsDao-->Insert" + ex.Message + sql.ToString(), ex); } }
public void ExportExcelStatistics() { try { int totalCount = 0; SiteStatistics query = new SiteStatistics(); ssMgr = new SiteStatisticsMgr(mySqlConnectionString); query.ss_code = Request.Params["ss_code"]; if (!string.IsNullOrEmpty(Request.Params["startdate"])) { query.sss_date = Convert.ToDateTime(DateTime.Parse(Request.Params["startdate"]).ToString("yyyy-MM-dd 00:00:00")); } if (!string.IsNullOrEmpty(Request.Params["enddate"])) { query.ess_date = Convert.ToDateTime(DateTime.Parse(Request.Params["enddate"]).ToString("yyyy-MM-dd 23:59:59")); } DataTable _dt = ssMgr.GetSiteStatisticsList(query, out totalCount); 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("實際轉換", 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("異動時間", typeof(string)); for (int i = 0; i < _dt.Rows.Count; i++) { DataRow newRow = _newDt.NewRow(); newRow["日期"] = Convert.ToDateTime(_dt.Rows[i]["ss_date"]).ToString("MM/dd/yyyy"); ; newRow["曝光數"] = _dt.Rows[i]["ss_show_num"].ToString(); newRow["點閱數"] = _dt.Rows[i]["ss_click_num"].ToString(); newRow["點閱率"] = _dt.Rows[i]["ss_click_through"].ToString(); newRow["費用"] = _dt.Rows[i]["ss_cost"].ToString(); newRow["新會員數"] = _dt.Rows[i]["ss_newuser_number"].ToString(); newRow["實際轉換"] = _dt.Rows[i]["ss_converted_newuser"].ToString(); newRow["訂單金額"] = _dt.Rows[i]["ss_sum_order_amount"].ToString(); newRow["廠商代碼"] = _dt.Rows[i]["ss_code"].ToString(); //newRow["創建人員"] = _dt.Rows[i]["ss_create_username"].ToString(); //newRow["創建時間"] = _dt.Rows[i]["ss_create_time"].ToString(); //newRow["異動人員"] = _dt.Rows[i]["ss_modify_username"].ToString(); //newRow["異動時間"] = _dt.Rows[i]["ss_modify_time"].ToString(); _newDt.Rows.Add(newRow); } string fileName = "站臺訪問量統計" + 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); } }
/// <summary> /// 刪除 /// </summary> /// <returns></returns> public JsonResult Delete() { int id = Convert.ToInt32(Request.Params["id"]); ssMgr = new SiteStatisticsMgr(mySqlConnectionString); SiteStatistics ss = new SiteStatistics(); ss.ss_id = id; if (ssMgr.Delete(ss) > 0) { return Json(new { success = "true", msg = "" }); } else { return Json(new { success = "false", msg = "" }); } }
public HttpResponseBase SiteStatisticsEdit() { string json = string.Empty; SiteStatistics ss = new SiteStatistics(); ssMgr = new SiteStatisticsMgr(mySqlConnectionString); try { if (!string.IsNullOrEmpty(Request.Params["ss_show_num"])) { ss.ss_show_num = int.Parse(Request.Params["ss_show_num"]); } if (!string.IsNullOrEmpty(Request.Params["ss_click_num"])) { ss.ss_click_num = int.Parse(Request.Params["ss_click_num"]); } if (!string.IsNullOrEmpty(Request.Params["ss_click_through"])) { ss.ss_click_through = float.Parse(Request.Params["ss_click_through"]); } if (!string.IsNullOrEmpty(Request.Params["ss_cost"])) { ss.ss_cost = float.Parse(Request.Params["ss_cost"]); } if (!string.IsNullOrEmpty(Request.Params["ss_converted_newuser"])) { ss.ss_converted_newuser = int.Parse(Request.Params["ss_converted_newuser"]); } if (!string.IsNullOrEmpty(Request.Params["ss_sum_order_amount"])) { ss.ss_sum_order_amount = int.Parse(Request.Params["ss_sum_order_amount"]); } if (!string.IsNullOrEmpty(Request.Params["ss_newuser_number"])) { ss.ss_newuser_number = int.Parse(Request.Params["ss_newuser_number"]); } if (!string.IsNullOrEmpty(Request.Params["ss_date"])) { ss.ss_date = DateTime.Parse(Request.Params["ss_date"]); } ss.ss_code = Request.Params["ss_code"]; ss.ss_create_user = int.Parse((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString()); ss.ss_create_time = DateTime.Now; ss.ss_modify_user = ss.ss_create_user; ss.ss_modify_time = ss.ss_create_time; #region 新增 if (String.IsNullOrEmpty(Request.Params["ss_id"])) { if (ssMgr.Insert(ss) > 0) { json = "{success:true,msg:'新增成功!'}"; } } #endregion #region 編輯 else { ss.ss_id = int.Parse(Request.Params["ss_id"]); if (ssMgr.Update(ss) > 0) { json = "{success:true,msg:'修改成功!'}"; } } #endregion } catch (Exception ex) { json = "{success:false,msg:'異常!'}"; 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); } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase GetSiteStatisticsList() { string json = string.Empty; try { if (Request.Files.Count > 0) { string path = Request.Params["ImportExcel"]; HttpPostedFileBase excelFile = Request.Files["ImportExcel"]; FileManagement fileManagement = new FileManagement(); string savePath=Server.MapPath(excelPath) + "statistics"; string newExcelName = savePath+ fileManagement.NewFileName(excelFile.FileName); excelFile.SaveAs(newExcelName); NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName); DataTable _dt = helper.ExcelToTableForXLSX(); ssMgr = new SiteStatisticsMgr(mySqlConnectionString); json = ssMgr.ImportExcelToDt(_dt);//匯入成功 } else { SiteStatistics query = new SiteStatistics(); ssMgr = new SiteStatisticsMgr(mySqlConnectionString); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); query.ss_code = Request.Params["ss_code"]; if (!string.IsNullOrEmpty(Request.Params["startdate"])) { query.sss_date = Convert.ToDateTime(DateTime.Parse(Request.Params["startdate"]).ToString("yyyy-MM-dd 00:00:00")); } if (!string.IsNullOrEmpty(Request.Params["enddate"])) { query.ess_date = Convert.ToDateTime(DateTime.Parse(Request.Params["enddate"]).ToString("yyyy-MM-dd 23:59:59")); } #region 用來判斷相同的廠家代碼和時間是否已經存在 if (!string.IsNullOrEmpty(Request.Params["ss_id"])) { query.ss_id = int.Parse(Request.Params["ss_id"]); } if (!string.IsNullOrEmpty(Request.Params["ss_date"])) { query.ss_date = DateTime.Parse(Request.Params["ss_date"]); } if (!string.IsNullOrEmpty(Request.Params["ispage"])) { query.IsPage = bool.Parse(Request.Params["ispage"]); } #endregion int totalCount = 0; DataTable dt = ssMgr.GetSiteStatisticsList(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 int Delete(SiteStatistics ss) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat(@"DELETE FROM site_statistics WHERE ss_id='{0}';",ss.ss_id); return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("SiteStatisticsDao-->Delete" + ex.Message + sql.ToString(), ex); } }