public EdmContentQuery GetEdmContentById(EdmContentQuery query)
{
try
{
return _IEdmContentDao.GetEdmContentById(query);
}
catch (Exception ex)
{
throw new Exception("EdmContentMgr-->GetEdmContentById-->" + ex.Message, ex);
}
}
public int EdmContentSave(EdmContentQuery store)
{
try
{
return _IEdmContentDao.EdmContentSave(store);
}
catch (Exception ex)
{
throw new Exception("EdmContentMgr-->EdmContentSave-->" + ex.Message, ex);
}
}
public List<EdmContentQuery> GetEdmContentList(EdmContentQuery store, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlLimit = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
store.Replace4MySQL();
//sqlCount.AppendFormat("select count(*) as totalCount FROM edm_content edm left join epaper_content ec on edm.info_epaper_id=ec.epaper_id ");
//sql.AppendFormat("SELECT edm.content_id,edm.group_id,edm.content_status,edm.content_start, edm.content_click,edm.content_person,edm.content_send_success,edm.content_send_failed,edm.content_from_name,edm.content_from_email,edm.content_reply_email,edm.content_body,edm.content_priority,edm.content_title,edm.content_createdate,edm.content_updatedate,ec.epaper_title,edm.info_epaper_id FROM edm_content edm left join epaper_content ec on edm.info_epaper_id=ec.epaper_id ");
//sqlWhere.AppendFormat(" where 1=1 ");
sqlCount.AppendFormat("select count(edm.content_id) as totalCount ");
sql.AppendFormat("SELECT edm.content_id,edm.group_id,edm.content_status,edm.content_start, edm.content_click,edm.content_person,edm.content_send_success,edm.content_send_failed,edm.content_from_name,edm.content_from_email,edm.content_reply_email,edm.content_body,edm.content_priority,edm.content_title,edm.content_createdate,edm.content_updatedate,ec.epaper_title,edm.info_epaper_id ");
sqlWhere.Append(" FROM edm_content edm ");
sqlWhere.Append(" left join epaper_content ec on edm.info_epaper_id=ec.epaper_id ");
sqlWhere.AppendFormat(" where 1=1 ");
if (store.search_text != "")
{
sqlWhere.AppendFormat(" and edm.content_title like N'%{0}%'", store.search_text);
}
if (store.searchStatus != "0")
{
sqlWhere.AppendFormat(" and edm.content_status= '{0}'", store.searchStatus);
}
if (store.s_content_start != DateTime.MinValue && store.s_content_end != DateTime.MinValue)
{
sqlWhere.AppendFormat(" and edm.content_start between '{0}' and '{1}'", CommonFunction.GetPHPTime(store.s_content_start.ToString()), CommonFunction.GetPHPTime(store.s_content_end.ToString()));
}
if (store.IsPage)
{
sqlCount.Append(sqlWhere.ToString());
DataTable _dt = _accessMySql.getDataTable(sqlCount.ToString() );
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
}
sqlWhere.AppendFormat(" ORDER BY content_id DESC ");
sqlLimit.AppendFormat(" limit {0},{1};", store.Start, store.Limit);
sql.AppendFormat(sqlWhere.ToString());
sql.AppendFormat(sqlLimit.ToString());
return _accessMySql.getDataTableForObj<EdmContentQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentDao-->GetEdmContentList-->" + ex.Message + sql.ToString()+sqlCount.ToString(), ex);
}
}
public int EditStatus(EdmContentQuery query)
{
try
{
string sql = _IEdmContentDao.EditStatus(query);
int i = _accessMySql.execCommand(sql);
return i;
}
catch (Exception ex)
{
throw new Exception("EdmContentMgr-->EditStatus-->" + ex.Message, ex);
}
}
/// <summary>
/// 獲取收件者名單
/// </summary>
/// <returns></returns>
public HttpResponseBase GetEdmGroup()
{
string json = string.Empty;
List<EdmContentQuery> store = new List<EdmContentQuery>();
EdmContentQuery query = new EdmContentQuery();
try
{
_IEdmContentMgr = new EdmContentMgr(mySqlConnectionString);
store = _IEdmContentMgr.GetEdmGroup();
json = "{success:true" + ",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);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetEpaperContentById()
{
string json = string.Empty;
EdmContentQuery store = new EdmContentQuery();
EdmContentQuery query = new EdmContentQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["rowid"]))
{
_IEdmContentMgr = new EdmContentMgr(mySqlConnectionString);
store.content_id = Convert.ToUInt32(Request.Params["rowid"]);
_epaperMgr = new EpaperContentMgr(mySqlConnectionString);
store = _IEdmContentMgr.GetEdmContentById(store);
store.content_body = Server.HtmlDecode(store.content_body);
store.s_content_start = CommonFunction.GetNetTime(store.content_start);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true" + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
else
{
json = "{success:false,data:[]}";
}
}
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);
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetWebContent()
{
string json = string.Empty;
string jsonStr = string.Empty;
EdmContentQuery cq = new EdmContentQuery();
IAreaPactetImplMgr _iareaPacketMgr = new AreaPacketMgr(mySqlConnectionString);
try
{
if (!string.IsNullOrEmpty(Request.Params["webtext"]))
{
jsonStr = Request.Params["webtext"].ToString();
Uri httpURL = new Uri(jsonStr);
//HttpWebRequest类继承于WebRequest，并没有自己的构造函数，需通过WebRequest的Creat方法 建立，并进行强制的类型转换
httpReq = (HttpWebRequest)WebRequest.Create(httpURL);
//通过HttpWebRequest的GetResponse()方法建立HttpWebResponse,强制类型转换
httpResp = (HttpWebResponse)httpReq.GetResponse();
//GetResponseStream()方法获取HTTP响应的数据流,并尝试取得URL中所指定的网页内容
//若成功取得网页的内容，则以System.IO.Stream形式返回，若失败则产生ProtoclViolationException错 误。在此正确的做法应将以下的代码放到一个try块中处理。这里简单处理
Stream respStream = httpResp.GetResponseStream();
//返回的内容是Stream形式的，所以可以利用StreamReader类获取GetResponseStream的内容，并以
//StreamReader类的Read方法依次读取网页源程序代码每一行的内容，直至行尾（读取的编码格式：UTF8）
StreamReader respStreamReader = new StreamReader(respStream, Encoding.UTF8);
byteRead = respStreamReader.Read(cbuffer, 0, 256);
while (byteRead != 0)
{
string strResp = new string(cbuffer, 0, byteRead);
strBuff = strBuff + strResp;
byteRead = respStreamReader.Read(cbuffer, 0, 256);
}
respStream.Close();
json = strBuff;
}
//int i = 0;
//if (i > 0)
//{
// json = "{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);
json = "";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase EditStatus()
{
string path = Server.MapPath(xmlPath);
SiteConfigMgr _siteConfigMgr = new SiteConfigMgr(path);
SiteConfig Mail_From = _siteConfigMgr.GetConfigByName("Mail_From");
SiteConfig Mail_Host = _siteConfigMgr.GetConfigByName("Mail_Host");
SiteConfig Mail_Port = _siteConfigMgr.GetConfigByName("Mail_Port");
SiteConfig Mail_UserName = _siteConfigMgr.GetConfigByName("Mail_UserName");
SiteConfig Mail_UserPasswd = _siteConfigMgr.GetConfigByName("Mail_UserPasswd");
string EmailFrom = Mail_From.Value;//發件人郵箱
string SmtpHost = Mail_Host.Value;//smtp服务器
string SmtpPort = Mail_Port.Value;//smtp服务器端口
string EmailUserName = Mail_UserName.Value;//郵箱登陸名
string EmailPassWord = Mail_UserPasswd.Value;//郵箱登陸密碼
string json = string.Empty;
EdmContentQuery query = new EdmContentQuery();
int i = 0;
try
{
if (!string.IsNullOrEmpty(Request.Params["content_id"]))
{
query.content_id = Convert.ToUInt32(Request.Params["content_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["status"]))
{
query.content_status = Convert.ToUInt32(Request.Params["status"]);
}
_IEdmContentMgr = new EdmContentMgr(mySqlConnectionString);
_edmContentMgr = new EdmContentMgr(mySqlConnectionString);
////////////
string FromName = string.Empty;
string EmailTile = string.Empty;
string strTemp = string.Empty;
string userEmail = string.Empty;
string userName = string.Empty;
DataTable _emaildt = _edmContentMgr.GetTestEmailById(query.content_id);
if (_emaildt.Rows.Count > 0)
{
EmailFrom = _emaildt.Rows[0]["content_from_email"].ToString();
FromName = _emaildt.Rows[0]["content_from_name"].ToString();
EmailTile = _emaildt.Rows[0]["content_title"].ToString();
strTemp = _emaildt.Rows[0]["content_body"].ToString();
}
if (query.content_status == 1)
{
//發送郵件
DataTable _dt = _edmContentMgr.GetAllTestEmail();
for (int index = 0; index < _dt.Rows.Count; index++)
{
userEmail = _dt.Rows[index]["email_address"].ToString();
userName = _dt.Rows[index]["test_username"].ToString();
bool result = sendmail(EmailFrom, FromName, userEmail, userName, EmailTile, strTemp, "", SmtpHost, Convert.ToInt32(SmtpPort), EmailUserName, EmailPassWord);
if (result)
json = "{success:true}";
}
}
if (query.content_status == 3)
{
//修改狀態
i = _IEdmContentMgr.EditStatus(query);
if (i > 0)
{
json = "{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);
json = "{success:true,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 新增編輯電子報信息
/// </summary>
/// <returns></returns>
public HttpResponseBase EdmContentSave()
{
string json = string.Empty;
string jsonStr = string.Empty;
EdmContentQuery cq = new EdmContentQuery();
_IEdmContentMgr = new EdmContentMgr(mySqlConnectionString);
try
{
if (string.IsNullOrEmpty(Request.Params["content_id"]))
{
//cq.content_id = Convert.ToUInt32(Request.Params["content_id"].ToString());
if (!string.IsNullOrEmpty(Request.Params["content_title"]))
{
cq.content_title = Request.Params["content_title"].ToString().Replace("\\", "\\\\");
}
if (!string.IsNullOrEmpty(Request.Params["kendoEditor"]))
{
cq.content_body = Request.Params["kendoEditor"].ToString().Replace("\\", "\\\\");
}
if (!string.IsNullOrEmpty(Request.Params["startdate"]))
{
DateTime dt = Convert.ToDateTime(Request.Params["startdate"]);
cq.content_start = Convert.ToUInt32(CommonFunction.GetPHPTime(dt.ToString("yyyy-MM-dd HH:mm:ss")));
}
if (!string.IsNullOrEmpty(Request.Params["content_from_name"]))
{
cq.content_from_name = Request.Params["content_from_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["content_from_email"]))
{
cq.content_from_email = Request.Params["content_from_email"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["content_reply_email"]))
{
cq.content_reply_email = Request.Params["content_reply_email"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
cq.group_id = Convert.ToUInt32(Request.Params["group_id"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["edm_dis"]))
{
cq.content_priority = Convert.ToUInt32(Request.Params["edm_dis"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["epaper_id"]))
{
int s = 0;
if (Int32.TryParse(Request.Params["epaper_id"], out s))
{
cq.info_epaper_id = s;
}
}
cq.content_email_id = 0;
cq.content_range = 300;
cq.content_single_count = 100;
cq.content_click = 0;
cq.content_person = 0;
cq.content_status = 1;
int i = _IEdmContentMgr.EdmContentSave(cq);
if (i > 0)
{
json = "{success:true}";
}
}
else
{
cq.content_id = Convert.ToUInt32(Request.Params["content_id"].ToString());
EdmContentQuery oldQuery = _IEdmContentMgr.GetEdmContentById(cq);
if (!string.IsNullOrEmpty(Request.Params["content_title"]))
{
cq.content_title = Request.Params["content_title"].ToString().Replace("\\", "\\\\"); ;
}
else
{
cq.content_title = oldQuery.content_title;
}
if (!string.IsNullOrEmpty(Request.Params["kendoEditor"]))
{
cq.content_body = Request.Params["kendoEditor"].ToString().Replace("\\", "\\\\"); ;
}
if (!string.IsNullOrEmpty(Request.Params["startdate"]))
{
DateTime dt = Convert.ToDateTime(Request.Params["startdate"]);
cq.content_start = Convert.ToUInt32(CommonFunction.GetPHPTime(dt.ToString("yyyy-MM-dd HH:mm:ss")));
}
else
{
cq.content_start = oldQuery.content_start;
}
if (!string.IsNullOrEmpty(Request.Params["content_from_name"]))
{
cq.content_from_name = Request.Params["content_from_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["content_from_email"]))
{
cq.content_from_email = Request.Params["content_from_email"].ToString();
}
else
{
cq.content_from_email = oldQuery.content_from_email;
}
if (!string.IsNullOrEmpty(Request.Params["content_reply_email"]))
{
cq.content_reply_email = Request.Params["content_reply_email"].ToString();
}
else
{
cq.content_reply_email = oldQuery.content_reply_email;
}
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
cq.group_id = Convert.ToUInt32(Request.Params["group_id"].ToString());
}
else
{
cq.group_id = oldQuery.group_id;
}
if (!string.IsNullOrEmpty(Request.Params["edm_dis"]))
{
cq.content_priority = Convert.ToUInt32(Request.Params["edm_dis"].ToString());
}
else
{
cq.content_priority = oldQuery.content_priority;
}
if (!string.IsNullOrEmpty(Request.Params["epaper_id"]))
{
int i = 0;
if (Int32.TryParse(Request.Params["epaper_id"], out i))
{
cq.info_epaper_id = i;
}
}
int j = _IEdmContentMgr.EdmContentSave(cq);
if (j > 0)
{
json = "{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);
json = "{success:true,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetEdmContentList()
{
string json = string.Empty;
List<EdmContentQuery> store = new List<EdmContentQuery>();
EdmContentQuery query = new EdmContentQuery();
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["searchStatus"]))
{
query.searchStatus = Request.Params["searchStatus"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["search_text"]))
{
query.search_text = Request.Params["search_text"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["tstart"]))
{
query.s_content_start = Convert.ToDateTime(Request.Params["tstart"]);
}
if (!string.IsNullOrEmpty(Request.Params["tend"]))
{
query.s_content_end = Convert.ToDateTime(Request.Params["tend"]);
}
_IEdmContentMgr = new EdmContentMgr(mySqlConnectionString);
int totalCount = 0;
store = _IEdmContentMgr.GetEdmContentList(query, out totalCount);
foreach (var item in store)
{
item.content_send_count = item.content_send_success + item.content_send_failed;
item.content_body = Server.HtmlDecode(Server.HtmlDecode(item.content_body));
item.s_content_start = CommonFunction.GetNetTime(item.content_start);
}
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 string EditStatus(EdmContentQuery query)
{
StringBuilder strSql = new StringBuilder();
try
{
strSql.AppendFormat(@"update edm_content set content_status={0} WHERE content_id={1}", query.content_status, query.content_id);
return strSql.ToString();
}
catch (Exception ex)
{
throw new Exception("EdmContentDao-->EditStatus-->" + strSql.ToString() + ex.Message, ex);;
}
}
public EdmContentQuery GetEdmContentById(EdmContentQuery query)
{
string strSql = string.Empty;
try
{
strSql = string.Format("select content_id,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,info_epaper_id from edm_content where content_id={0}", query.content_id);
return _accessMySql.getSinggleObj<EdmContentQuery>(strSql);
}
catch (Exception ex)
{
throw new Exception("EdmContentDao-->GetEdmContentById-->" + strSql.ToString() + ex.Message, ex);
}
}
public string InsertEdmContent(EdmContentQuery store)
{
store.Replace4MySQL();
StringBuilder sql = new StringBuilder();
sql.AppendFormat("insert into edm_content (content_id,group_id,content_status,content_email_id,content_start, ");
sql.AppendFormat("content_end,content_range,content_single_count,content_click,content_person,");
sql.AppendFormat("content_from_name,content_from_email,content_reply_email,content_priority,content_title,");
sql.AppendFormat("content_body,content_createdate,content_updatedate,info_epaper_id)");
sql.AppendFormat(" values({0},{1},{2},{3},{4},", store.serialvalue, store.group_id, store.content_status, store.content_email_id, store.content_start);
sql.AppendFormat("{0},{1},{2},{3},{4},", store.content_end, store.content_range, store.content_single_count, store.content_click, store.content_person);
sql.AppendFormat("'{0}','{1}','{2}',{3},'{4}',", store.content_from_name, store.content_from_email, store.content_reply_email, store.content_priority, store.content_title);
sql.AppendFormat("'{0}',{1},{2},'{3}');", store.content_body, CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), store.info_epaper_id);
return sql.ToString();
}
public int EdmContentSave(EdmContentQuery store)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sql = new StringBuilder();
int re = 0;
if (store.content_id == 0)//新增
{
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
store.serialvalue = GetSerialValue();
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateSerialVal(store.serialvalue);
mySqlCmd.CommandText += InsertEdmContent(store);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("EdmContentDao-->EdmContentSave-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
else //編輯
{
try
{
store.Replace4MySQL();
sql.AppendFormat("update edm_content set group_id={0},content_status={1},content_start={2},", store.group_id, store.content_status, store.content_start);
sql.AppendFormat("content_from_name='{0}',content_from_email='{1}',content_reply_email='{2}',content_priority={3},info_epaper_id='{4}',", store.content_from_name, store.content_from_email, store.content_reply_email, store.content_priority, store.info_epaper_id);
sql.AppendFormat("content_title='{0}',content_body='{1}',content_updatedate={2} where content_id={3};", store.content_title, store.content_body, CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), store.content_id);
return _accessMySql.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmContentDao-->EdmContentSave-->" + sql.ToString() + ex.Message, ex);
}
}
}
