public HttpResponseBase GetSendRecordList()
{
string json = string.Empty;
_edmSendMgr = new EdmSendMgr(mySqlConnectionString);
EdmSendQuery query = new EdmSendQuery();
int totalCount = 0;
try
{
if (!string.IsNullOrEmpty(Request.Params["eid"]))
{
query.email_id = Convert.ToUInt32(Request.Params["eid"]);
}
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["content_title"]))
{
query.content_title = Request.Params["content_title"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["date"]))
{
query.date = Convert.ToInt32(Request.Params["date"]);
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.start_time = Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00");
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.end_time = Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59");
}
DataTable store = _edmSendMgr.GetSendRecordList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
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 HttpResponseBase GetStatisticsEdmList()
{
string json = string.Empty;
List<EdmListQuery> store = new List<EdmListQuery>();
EdmListQuery query = new EdmListQuery();
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["cid"]))
{
query.content_id = Convert.ToUInt32(Request.Params["cid"].ToString());
}
_edmSendMgr = new EdmSendMgr(mySqlConnectionString);
int totalCount = 0;
store = _edmSendMgr.GetStatisticsEdmList(query, out totalCount);
//計算圖表width和sum_total_click、sum_total_click
int sum_total_click = 0;
int sum_total_person = 0;
int max_open = _edmSendMgr.GetMaxClick(query, out sum_total_click, out sum_total_person);
double nTemp_Image_Rate = 1.00;
int nMax_Image_Width = 250;
if (max_open > 0)
{
nTemp_Image_Rate = (max_open > nMax_Image_Width) ? Math.Round((double)nMax_Image_Width / max_open, 2) : 1;
}
foreach (var item in store)
{
string temp_statistics_id = item.statistics_id.ToString();
string year = temp_statistics_id.Substring(0, 4);
string month = temp_statistics_id.Substring(4, 2);
string day = temp_statistics_id.Substring(6, 2);
DateTime d1 = Convert.ToDateTime(year + "-" + month + "-" + day + " 00:00:00");
d1.DayOfWeek.ToString();
switch (d1.DayOfWeek)
{
case DayOfWeek.Sunday:
item.week = "[日]";
break;
case DayOfWeek.Monday:
item.week = "[一]";
break;
case DayOfWeek.Tuesday:
item.week = "[二]";
break;
case DayOfWeek.Wednesday:
item.week = "[三]";
break;
case DayOfWeek.Thursday:
item.week = "[四]";
break;
case DayOfWeek.Friday:
item.week = "[五]";
break;
case DayOfWeek.Saturday:
item.week = "[六]";
break;
default:
break;
}
item.date = year + "-" + month + "-" + day;
if (sum_total_click > 0)
{
item.clickRate = Math.Round((double)item.total_click / sum_total_click * 100, 2);
}
if (sum_total_person > 0)
{
item.personRate = Math.Round((double)item.total_person / sum_total_person * 100, 2);
}
item.image_width = (uint)Math.Round(item.total_click * nTemp_Image_Rate, 0);
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
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 HttpResponseBase EdmSendExportCSV()
{
string json = string.Empty;
try
{
EdmSendQuery query = new EdmSendQuery();
if (!string.IsNullOrEmpty(Request.Params["cid"]))
{
query.content_id = Convert.ToUInt32(Request.Params["cid"].ToString());
}
_edmSendMgr = new EdmSendMgr(mySqlConnectionString);
DataTable _dt = _edmSendMgr.EdmSendExportCSV(query);
string fileName = "edm_status_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "." + "csv";
string newFileName = Server.MapPath(excelPath_export + fileName); //"電子信箱"
string[] colName = { "發信狀態", "郵件編號", "姓名", "開信次數", "寄信時間", "首次開信時間", "最近開信時間" };
DataTable _newdt = new DataTable();
foreach (string item in colName)
{
_newdt.Columns.Add(item, typeof(string));
}
for (int i = 0; i < _dt.Rows.Count; i++)
{
DataRow dr = _newdt.NewRow();//SELECT es.email_id,es.send_status,es.send_datetime,es.open_first,es.open_last,es.open_total,ee.email_name
dr[0] = _dt.Rows[i]["send_status"].ToString() == "1" ? "Success" : "Fail";
dr[1] = _dt.Rows[i]["email_id"];
dr[2] = _dt.Rows[i]["email_name"];
//dr[3] = _dt.Rows[i]["email_address"]; //不導出email_address
dr[3] = _dt.Rows[i]["open_total"];
if (Convert.ToUInt32(_dt.Rows[i]["send_datetime"]) != 0)
{
dr[4] = CommonFunction.GetNetTime(Convert.ToUInt32(_dt.Rows[i]["send_datetime"])).ToString("yyyy-MM-dd HH:mm:ss");
}
if (Convert.ToUInt32(_dt.Rows[i]["open_first"]) != 0)
{
dr[5] = CommonFunction.GetNetTime(Convert.ToUInt32(_dt.Rows[i]["open_first"])).ToString("yyyy-MM-dd HH:mm:ss");
}
if (Convert.ToUInt32(_dt.Rows[i]["open_last"]) != 0)
{
dr[6] = CommonFunction.GetNetTime(Convert.ToUInt32(_dt.Rows[i]["open_last"])).ToString("yyyy-MM-dd HH:mm:ss");
}
if (Request["st"].ToString() == "1" && Convert.ToUInt32(_dt.Rows[i]["open_first"]) != 0) //開信名單下載
{
_newdt.Rows.Add(dr);
}
if (Request["st"].ToString() == "0" && Convert.ToUInt32(_dt.Rows[i]["open_first"]) == 0)//未開信名單下載
{
_newdt.Rows.Add(dr);
}
}
CsvHelper.ExportDataTableToCsv(_newdt, newFileName, colName, true);
json = "{success:true,fileName:\'" + fileName + "\'}";
}
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 EdmSendLoad()
{
string json = string.Empty;
EdmSendQuery store = new EdmSendQuery();
EdmSendQuery query = new EdmSendQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["cid"]))
{
query.content_id = Convert.ToUInt32(Request.Params["cid"].ToString());
}
_edmSendMgr = new EdmSendMgr(mySqlConnectionString);
store = _edmSendMgr.EdmSendLoad(query);
int nMax_Image_Width = 250; //最大圖片寬度
if (store != null)
{
store.content_send = store.content_send_success + store.content_send_failed;
if (store.content_send > 0)
{
store.content_openRate = Math.Round((double)store.content_person / store.content_send * 100, 2);
store.content_imagewidth_send = nMax_Image_Width;
store.content_imagewidth_success = (int)Math.Round((double)store.content_send_success / store.content_send * nMax_Image_Width, 0);
store.content_imagewidth_failed = (int)Math.Round((double)store.content_send_failed / store.content_send * nMax_Image_Width, 0);
}
if (store.content_person > 0)
{
store.content_averageClick = Math.Round((double)store.content_click / store.content_person, 1);
}
store.content_start_s = CommonFunction.GetNetTime(store.content_start).ToString("yyyy-MM-dd HH:mm:ss");
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
}
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,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetStatisticsEdmSend()
{
string json = string.Empty;
List<EdmSendQuery> store = new List<EdmSendQuery>();
EdmSendQuery query = new EdmSendQuery();
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["cid"]))
{
query.content_id = Convert.ToUInt32(Request.Params["cid"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["email_name"]))
{
query.email_name = Request.Params["email_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["date"]))
{
query.date = Convert.ToInt32(Request.Params["date"]);
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.start_time = Convert.ToDateTime(Request.Params["start_time"]).ToString("yyyy-MM-dd 00:00:00");
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.end_time = Convert.ToDateTime(Request.Params["end_time"]).ToString("yyyy-MM-dd 23:59:59");
}
_edmSendMgr = new EdmSendMgr(mySqlConnectionString);
int totalCount = 0;
store = _edmSendMgr.GetStatisticsEdmSend(query, out totalCount);
foreach (var items in store)
{
items.email_name = items.email_name.ToString().Substring(0, 1) + "**";
}
//計算圖表width
int max_open = _edmSendMgr.GetMaxOpen(query);
double nTemp_Image_Rate = 1;
int nMax_Image_Width = 250;
if (max_open > 0)
{
nTemp_Image_Rate = (max_open > nMax_Image_Width) ? Math.Round((double)nMax_Image_Width / max_open, 2) : 1;
}
foreach (var item in store)
{
item.s_send_status = item.send_status == 1 ? "成功" : "失敗";
if (item.send_datetime != 0)
{
item.s_send_datetime = CommonFunction.GetNetTime(item.send_datetime).ToString("yyyy-MM-dd HH:mm:ss");
}
if (item.open_first != 0)
{
item.s_open_first = CommonFunction.GetNetTime(item.open_first).ToString("yyyy-MM-dd HH:mm:ss");
}
if (item.open_last != 0)
{
item.s_open_last = CommonFunction.GetNetTime(item.open_last).ToString("yyyy-MM-dd HH:mm:ss");
}
item.image_width = (uint)Math.Round(item.open_total * nTemp_Image_Rate, 0);
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
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;
}