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