Beispiel #1
0
 public EdmSendQuery EdmSendLoad(EdmSendQuery query)
 {
     try
     {
         return _edmSendDao.EdmSendLoad(query);
     }
     catch (Exception ex)
     {
         throw new Exception("EdmSendMgr-->EdmSendLoad-->" + ex.Message);
     }
 }
Beispiel #2
0
 public DataTable EdmSendExportCSV(EdmSendQuery query)
 {
     try
     {
         return _edmSendDao.EdmSendExportCSV(query);
     }
     catch (Exception ex)
     {
         throw new Exception("EdmSendMgr-->EdmSendExportCSV-->" + ex.Message);
     }
 }
Beispiel #3
0
 public int GetMaxOpen(EdmSendQuery query)
 {
     try
     {
         return _edmSendDao.GetMaxOpen(query);
     }
     catch (Exception ex)
     {
         throw new Exception("EdmSendMgr-->GetMaxOpen-->" + ex.Message);
     }
 }
Beispiel #4
0
 public List<EdmSendQuery> GetStatisticsEdmSend(EdmSendQuery query, out int totalCount)
 {
     try
     {
         return _edmSendDao.GetStatisticsEdmSend(query, out totalCount);
     }
     catch (Exception ex)
     {
         throw new Exception("EdmSendMgr-->GetStatisticsEdmSend-->" + ex.Message);
     }
 }
Beispiel #5
0
 public DataTable GetSendRecordList(EdmSendQuery query,out int totalCount)
 {
     totalCount = 0;
     try
     {
         return _edmSendDao.GetSendRecordList(query,out totalCount);
     }
     catch (Exception ex)
     {
         
        throw new Exception("EdmSendMgr-->GetSendRecordList-->" + ex.Message);
     }
 }
Beispiel #6
0
        public DataTable GetSendRecordList(EdmSendQuery query,out int totalCount)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder sqlCount = new StringBuilder();
            StringBuilder sqlWhere = new StringBuilder();
            totalCount = 0;
            try
            {
                sql.AppendFormat(@"SELECT ee.send_status,ec.content_id,ec.content_title,ee.open_total,e.email_address,e.email_name,FROM_UNIXTIME(ee.send_datetime) as sendtime,FROM_UNIXTIME(ee.open_first) as firsttime ,FROM_UNIXTIME(ee.open_last) as lasttime 
");
                sqlWhere.AppendFormat(@" FROM edm_send ee LEFT JOIN edm_content ec ON ee.content_id = ec.content_id LEFT JOIN edm_email e on ee.email_id=e.email_id WHERE ee.email_id = {0} ", query.email_id);
                if (!string.IsNullOrEmpty(query.content_title)) //email_name
                {
                    sqlWhere.AppendFormat("  and ec.content_title like '%{0}%'", query.content_title);
                }
                if (query.date == 1) //寄信時間
                {
                    if (!string.IsNullOrEmpty(query.start_time)) //email_name
                    {
                        sqlWhere.AppendFormat("  and ee.send_datetime >='{0}'", CommonFunction.GetPHPTime(query.start_time));
                    }
                    if (!string.IsNullOrEmpty(query.end_time)) //email_name
                    {
                        sqlWhere.AppendFormat(" and ee.send_datetime <='{0}'", CommonFunction.GetPHPTime(query.end_time));
                    }
                }
                if (query.date == 2) //首次開信時間
                {
                    if (!string.IsNullOrEmpty(query.start_time)) //email_name
                    {
                        sqlWhere.AppendFormat("  and ee.open_first >='{0}'", CommonFunction.GetPHPTime(query.start_time));
                    }
                    if (!string.IsNullOrEmpty(query.end_time)) //email_name
                    {
                        sqlWhere.AppendFormat(" and ee.open_first <='{0}'", CommonFunction.GetPHPTime(query.end_time));
                    }
                }
                if (query.date == 3) //最近開信時間
                {
                    if (!string.IsNullOrEmpty(query.start_time)) //email_name
                    {
                        sqlWhere.AppendFormat("  and ee.open_last >='{0}'", CommonFunction.GetPHPTime(query.start_time));
                    }
                    if (!string.IsNullOrEmpty(query.end_time)) //email_name
                    {
                        sqlWhere.AppendFormat(" and ee.open_last <='{0}'", CommonFunction.GetPHPTime(query.end_time));
                    }
                }
                sqlWhere.AppendFormat("ORDER BY ec.content_id DESC");
                sqlCount.Append(@"SELECT count(ec.content_id) totalCount ");
                if (query.IsPage)
                {
                    DataTable dt = _access.getDataTable(sqlCount.ToString()+sqlWhere.ToString());
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
                    }
                    sqlWhere.AppendFormat(" limit {0},{1} ;", query.Start, query.Limit);
                }
                return _access.getDataTable(sql.ToString() + sqlWhere.ToString());
            }
            catch (Exception ex)
            {

                throw new Exception("EdmSendDao-->GetSendRecordList-->" + sql.ToString() + sqlWhere.ToString() + ex.Message, ex);
            }
        }
Beispiel #7
0
        public List<EdmSendQuery> GetStatisticsEdmSend(EdmSendQuery query, out int totalCount)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder sqlCount = new StringBuilder();
            StringBuilder sqlFrom = new StringBuilder();
            StringBuilder sqlWhere = new StringBuilder();
            totalCount = 1;//IFNULL(SUM(traveluser_totalppl),9)es.content_id = ' 1207 '
            try
            {
                sql.Append(@"SELECT	es.email_id,es.send_status,es.send_datetime,es.open_first,es.open_last,es.open_total,ee.email_name ");
                sqlWhere.Append(" FROM  edm_email ee,edm_send es WHERE es.email_id = ee.email_id  ");
                
                if (!string.IsNullOrEmpty(query.email_name)) //email_name
                {
                    sqlWhere.AppendFormat("  and ee.email_name like '%{0}%'", query.email_name);
                }
                if (query.date == 1) //寄信時間
                {
                    if (!string.IsNullOrEmpty(query.start_time)) //email_name
                    {
                        sqlWhere.AppendFormat("  and send_datetime >='{0}'", CommonFunction.GetPHPTime(query.start_time));
                    }
                    if (!string.IsNullOrEmpty(query.end_time)) //email_name
                    {
                        sqlWhere.AppendFormat(" and send_datetime <='{0}'", CommonFunction.GetPHPTime(query.end_time));
                    }
                }
                if (query.date == 2) //首次開信時間
                {
                    if (!string.IsNullOrEmpty(query.start_time)) //email_name
                    {
                        sqlWhere.AppendFormat("  and open_first >='{0}'", CommonFunction.GetPHPTime(query.start_time));
                    }
                    if (!string.IsNullOrEmpty(query.end_time)) //email_name
                    {
                        sqlWhere.AppendFormat(" and open_first <='{0}'", CommonFunction.GetPHPTime(query.end_time));
                    }
                }
                if (query.date == 3) //最近開信時間
                {
                    if (!string.IsNullOrEmpty(query.start_time)) //email_name
                    {
                        sqlWhere.AppendFormat("  and open_last >='{0}'", CommonFunction.GetPHPTime(query.start_time));
                    }
                    if (!string.IsNullOrEmpty(query.end_time)) //email_name
                    {
                        sqlWhere.AppendFormat(" and open_last <='{0}'", CommonFunction.GetPHPTime(query.end_time));
                    }
                }

                sqlWhere.AppendFormat(" and es.content_id = '{0}' ORDER BY open_total DESC, es.email_id ASC ", query.content_id);
                sqlCount.Append("select count(es.email_id) totalCount "); //
                if (query.IsPage)
                {                 
                    DataTable dt = _access.getDataTable(sqlCount.ToString()+sqlWhere.ToString());
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
                    }
                    sqlWhere.AppendFormat(" limit {0},{1} ;", query.Start, query.Limit);
                }
                return _access.getDataTableForObj<EdmSendQuery>(sql.ToString() + sqlWhere.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("EdmSendDao-->GetStatisticsEdmSend-->" + sql.ToString() + ex.Message);
            }
        }
Beispiel #8
0
        public EdmSendQuery EdmSendLoad(EdmSendQuery query)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.AppendFormat(@"SELECT	group_id,
					content_status,
					content_email_id,
					content_start,
					content_end,
					content_range,
					content_single_count,
					content_click,
					content_person,
					content_send_success,
					content_send_failed,
					content_from_name,
					content_from_email,
					content_reply_email,
					content_priority,
					content_title,
					content_body,
					content_createdate,
					content_updatedate
				FROM edm_content 
				WHERE content_id = '{0}' ", query.content_id);

                return _access.getSinggleObj<EdmSendQuery>(sql.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("EdmSendDao-->EdmSendLoad-->" + sql.ToString() + ex.Message, ex);
            }
        }
Beispiel #9
0
        public int GetMaxOpen(EdmSendQuery query)
        {
            int result = 0;
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.AppendFormat(@"SELECT max(open_total) open_total FROM  edm_send es WHERE content_id = '{0}' ", query.content_id);
                DataTable _dt = _access.getDataTable(sql.ToString());
                if(_dt.Rows.Count>0)
                {
                    result = Convert.ToInt32(_dt.Rows[0]["open_total"].ToString());
                }

                return result;
            }
            catch (Exception ex)
            {
                throw new Exception("EdmSendDao-->GetMaxOpen-->" + sql.ToString() + ex.Message,ex);
            }
        }
Beispiel #10
0
        public DataTable EdmSendExportCSV(EdmSendQuery query)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.Append(@"SELECT	es.email_id,es.send_status,es.send_datetime,es.open_first,es.open_last,es.open_total,ee.email_name,ee.email_address");
                sql.Append(" FROM  edm_email ee,edm_send es WHERE es.email_id = ee.email_id ");
                sql.AppendFormat(" and es.content_id = '{0}' ORDER BY open_total DESC, es.email_id ASC ", query.content_id);

                return _access.getDataTable(sql.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("EdmSendDao->EdmSendExportCSV->" + sql.ToString() + ex.Message, ex);
            }
        }
Beispiel #11
0
 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;
 }
Beispiel #12
0
        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;

        }
Beispiel #13
0
        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;
        }
Beispiel #14
0
        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;
        }