public EpaperContentQuery GetEpaperContentById(EpaperContentQuery query)
{
try
{
return _IEpaperContentDao.GetEpaperContentById(query);
}
catch (Exception ex)
{
throw new Exception("EpaperContentMgr-->GetEpaperContentById-->"+ex.Message,ex);
}
}
public List<EpaperContentQuery> GetEpaperContentList(EpaperContentQuery query, out int totalCount)
{
try
{
return _IEpaperContentDao.GetEpaperContentList(query,out totalCount);
}
catch (Exception ex)
{
throw new Exception("EpaperContentMgr-->GetEpaperContentList-->"+ex.Message,ex);
}
}
public HttpResponseBase GetEpaperContentList()
{
string json = string.Empty;
List<EpaperContentQuery> store = new List<EpaperContentQuery>();
EpaperContentQuery query = new EpaperContentQuery();
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
_IEpaperContentMgr = new EpaperContentMgr(mySqlConnectionString);
int totalCount = 0;
if (!string.IsNullOrEmpty(Request.Params["searchCon"]))
{
query.searchCon = Request.Params["searchCon"];
}
if (!string.IsNullOrEmpty(Request.Params["search_text"]))
{
query.search_text = Request.Params["search_text"];
}
if (!string.IsNullOrEmpty(Request.Params["dateCon"]))
{
query.dateCon = Request.Params["dateCon"];
}
if (!string.IsNullOrEmpty(Request.Params["date_start"]))
{
query.epaperShowStart = Convert.ToDateTime(Request.Params["date_start"]);
}
if (!string.IsNullOrEmpty(Request.Params["date_end"]))
{
query.epaperShowEnd = Convert.ToDateTime(Request.Params["date_end"]);
}
if (!string.IsNullOrEmpty(Request.Params["activeStatus"]))
{
query.epaperStatus = Request.Params["activeStatus"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["sizeCon"]))
{
query.epaper_size = Request.Params["sizeCon"];
}
store = _IEpaperContentMgr.GetEpaperContentList(query, out totalCount);
foreach (var item in store)
{
item.epaperShowStart = CommonFunction.GetNetTime(item.epaper_show_start);
item.epaperShowEnd = CommonFunction.GetNetTime(item.epaper_show_end);
item.epaperCreateDate = CommonFunction.GetNetTime(item.epaper_createdate);
item.epaperUpdateDate = CommonFunction.GetNetTime(item.epaper_updatedate);
item.epaper_content = Server.HtmlDecode(Server.HtmlDecode(item.epaper_content));
}
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 GetEpaperById()
{
string json = string.Empty;
EpaperContentQuery store = new EpaperContentQuery();
EpaperContentQuery query = new EpaperContentQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["rowid"]))
{
_IEpaperContentMgr = new EpaperContentMgr(mySqlConnectionString);
store.epaper_id = Convert.ToUInt32(Request.Params["rowid"]);
store = _IEpaperContentMgr.GetEpaperContentById(store);
store.epaper_content = Server.HtmlDecode(store.epaper_content);
store.epaperShowStart = CommonFunction.GetNetTime(store.epaper_show_start);
store.epaperShowEnd = CommonFunction.GetNetTime(store.epaper_show_end);
store.epaperCreateDate = CommonFunction.GetNetTime(store.epaper_createdate);
store.epaperUpdateDate = CommonFunction.GetNetTime(store.epaper_updatedate);
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 SaveEpaperContent()
{
string json = string.Empty;
List<EpaperContentQuery> store = new List<EpaperContentQuery>();
EpaperContentQuery OldQuery = new EpaperContentQuery();
EpaperContentQuery query = new EpaperContentQuery();
_IEpaperContentMgr = new EpaperContentMgr(mySqlConnectionString);
try
{
query.user_id = uint.Parse((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString());
if (string.IsNullOrEmpty(Request.Params["epaper_id"]))//新增
{
if (!string.IsNullOrEmpty(Request.Params["epaper_title"]))
{
query.epaper_title = Request.Params["epaper_title"];
}
if (!string.IsNullOrEmpty(Request.Params["epaper_short_title"]))
{
query.epaper_short_title = Request.Params["epaper_short_title"];
}
if (!string.IsNullOrEmpty(Request.Params["epaper_size"]))
{
query.epaper_size = Request.Params["epaper_size"];
}
if (!string.IsNullOrEmpty(Request.Params["type"]))
{
query.type = Convert.ToUInt32(Request.Params["type"]);
}
if (!string.IsNullOrEmpty(Request.Params["epaper_sort"]))
{
query.epaper_sort = Convert.ToUInt32(Request.Params["epaper_sort"]);
}
if (!string.IsNullOrEmpty(Request.Params["epaperShowStart"]))
{
query.epaperShowStart = Convert.ToDateTime(Request.Params["epaperShowStart"]);
}
if (!string.IsNullOrEmpty(Request.Params["epaperShowEnd"]))
{
query.epaperShowEnd = Convert.ToDateTime(Request.Params["epaperShowEnd"]);
}
if (!string.IsNullOrEmpty(Request.Params["kendoEditor"]))
{
query.epaper_content = Request.Params["kendoEditor"];
}
if (!string.IsNullOrEmpty(Request.Params["type"]))
{
if (Request.Params["newsType"] == "direct")//直接上稿
{
query.epaper_status = 1;
}
else
{
query.epaper_status = 0;
}
}
System.Net.IPAddress[] addlist = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList;
if (addlist.Length > 0)
{
query.epaper_ipfrom = addlist[0].ToString();
}
query.epaperCreateDate = DateTime.Now;
query.epaperUpdateDate = query.epaperCreateDate;
query.log_description = "add";
_IEpaperContentMgr.SaveEpaperContent(query);
json = "{success:true}";
}
else
{
query.epaper_id = Convert.ToUInt32(Request.Params["epaper_id"]);
OldQuery = _IEpaperContentMgr.GetEpaperContentById(query);
if (!string.IsNullOrEmpty(Request.Params["epaper_title"]))
{
query.epaper_title = Request.Params["epaper_title"];
}
else
{
query.epaper_title = OldQuery.epaper_title;
}
if (!string.IsNullOrEmpty(Request.Params["epaper_short_title"]))
{
query.epaper_short_title = Request.Params["epaper_short_title"];
}
if (!string.IsNullOrEmpty(Request.Params["epaper_size"]))
{
query.epaper_size = Request.Params["epaper_size"];
}
else
{
query.epaper_size = OldQuery.epaper_size;
}
if (!string.IsNullOrEmpty(Request.Params["type"]))
{
query.type = Convert.ToUInt32(Request.Params["type"]);
}
else
{
query.type = OldQuery.type;
}
if (!string.IsNullOrEmpty(Request.Params["epaper_sort"]))
{
query.epaper_sort = Convert.ToUInt32(Request.Params["epaper_sort"]);
}
else
{
query.epaper_sort = OldQuery.epaper_sort;
}
if (!string.IsNullOrEmpty(Request.Params["epaperShowStart"]))
{
query.epaperShowStart = Convert.ToDateTime(Request.Params["epaperShowStart"]);
}
else
{
query.epaperShowStart = OldQuery.epaperShowStart;
}
if (!string.IsNullOrEmpty(Request.Params["epaperShowEnd"]))
{
query.epaperShowEnd = Convert.ToDateTime(Request.Params["epaperShowEnd"]);
}
else
{
query.epaperShowEnd = OldQuery.epaperShowEnd;
}
if (!string.IsNullOrEmpty(Request.Params["kendoEditor"]))
{
query.epaper_content = Request.Params["kendoEditor"];
}
else
{
query.epaper_content = OldQuery.epaper_content;
}
if (!string.IsNullOrEmpty(Request.Params["epaper_status"]))
{
query.epaper_status = Convert.ToUInt32(Request.Params["epaper_status"]);
}
else
{
query.epaper_status = OldQuery.epaper_status;
}
if (!string.IsNullOrEmpty(Request.Params["fb_description"]))
{
query.fb_description = Request.Params["fb_description"];
}
else
{
query.fb_description = OldQuery.fb_description;
}
System.Net.IPAddress[] addlist = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList;
if (addlist.Length > 0)
{
query.epaper_ipfrom = addlist[0].ToString();
}
query.epaperUpdateDate = DateTime.Now;
query.log_description = "modify,status=" + query.epaper_status;
_IEpaperContentMgr.SaveEpaperContent(query);
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:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public int SaveEpaperContent(EpaperContentQuery query)
{
try
{
return _IEpaperContentDao.SaveEpaperContent(query);
}
catch (Exception ex)
{
throw new Exception("EpaperContentMgr-->SaveEpaperContent-->" + ex.Message, ex);
}
}
public List<EpaperContentQuery> GetEpaperContentList(EpaperContentQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlLimit = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
//sqlCount.AppendFormat("select count(1) as totalCount from epaper_content ec left join manage_user mu on ec.user_id=mu.user_id ");
//sql.AppendFormat("SELECT ec.epaper_id,mu.user_username as user_username,ec.user_id,ec.epaper_title,ec.epaper_short_title,ec.epaper_content,ec.epaper_sort,ec.epaper_status,ec.epaper_size,ec.epaper_show_start,ec.epaper_show_end,ec.fb_description,ec.epaper_createdate,ec.epaper_updatedate,ec.epaper_ipfrom,ec.type from epaper_content ec left join manage_user mu on ec.user_id=mu.user_id ");
sqlCount.AppendFormat(" select count(ec.epaper_id) as totalCount ");
sql.AppendFormat("SELECT ec.epaper_id,mu.user_username as user_username,ec.user_id,ec.epaper_title,ec.epaper_short_title,ec.epaper_content,ec.epaper_sort,ec.epaper_status,ec.epaper_size,ec.epaper_show_start,ec.epaper_show_end,ec.fb_description,ec.epaper_createdate,ec.epaper_updatedate,ec.epaper_ipfrom,ec.type ");
sqlWhere.Append(" from epaper_content ec ");
sqlWhere.Append(" left join manage_user mu on ec.user_id=mu.user_id ");
sqlWhere.AppendFormat(" where 1=1 ");
//查詢條件
if (query.searchCon == "1")
{
sqlWhere.AppendFormat(" and ec.epaper_title like '%{0}%' ", query.search_text);
}
else if (query.searchCon == "2")
{
sqlWhere.AppendFormat(" and ec.epaper_short_title like '%{0}%' ", query.search_text);
}
else if (query.searchCon == "3")
{
sqlWhere.AppendFormat(" and mu.user_username like '%{0}%' ", query.search_text);
}
else
{
sqlWhere.AppendFormat(" and ( ec.epaper_title like '%{0}%' or ec.epaper_short_title like '%{0}%' or mu.user_username like '%{0}%' ) ", query.search_text);
}
//日期條件
if (query.dateCon == "1")
{
if (!query.epaperShowStart.Equals(DateTime.MinValue))
{
sqlWhere.AppendFormat(" and ec.epaper_show_start > '{0}' ", CommonFunction.GetPHPTime(query.epaperShowStart.ToString()));
}
if (!query.epaperShowEnd.Equals(DateTime.MinValue))
{
sqlWhere.AppendFormat(" and ec.epaper_show_start<'{0}' ",CommonFunction.GetPHPTime(query.epaperShowEnd.ToString()));
}
}
else if (query.dateCon == "2")
{
if (!query.epaperShowStart.Equals(DateTime.MinValue))
{
sqlWhere.AppendFormat(" and ec.epaper_show_end > '{0}' ", CommonFunction.GetPHPTime(query.epaperShowStart.ToString()));
}
if (!query.epaperShowEnd.Equals(DateTime.MinValue))
{
sqlWhere.AppendFormat(" and ec.epaper_show_end<'{0}' ", CommonFunction.GetPHPTime(query.epaperShowEnd.ToString()));
}
}
else
{
//所有日期時不進行日期判斷；
//sqlWhere.AppendFormat(" and( ( ec.epaper_show_start > '{0}' and ec.epaper_show_start<'{1}') or ", CommonFunction.GetPHPTime(query.epaperShowStart.ToString()), CommonFunction.GetPHPTime(query.epaperShowEnd.ToString()));
//sqlWhere.AppendFormat(" ec.epaper_show_end > '{0}' and ec.epaper_show_end<'{1}' ) ", CommonFunction.GetPHPTime(query.epaperShowStart.ToString()), CommonFunction.GetPHPTime(query.epaperShowEnd.ToString()));
}
if (query.epaperStatus != "" && query.epaperStatus != "-1")
{
sqlWhere.AppendFormat(" and ec.epaper_status = '{0}' ", query.epaperStatus);
}
if (query.epaper_size != "" && query.epaper_size != "0")
{
if (query.epaper_size == "所有尺寸")
{
sqlWhere.AppendFormat(" and ec.epaper_size = '725px' or ec.epaper_size = '900px '");
}
else
{
sqlWhere.AppendFormat(" and ec.epaper_size = '{0}' ", query.epaper_size);
}
}
if (query.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 ec.epaper_show_start desc, ec.epaper_sort desc, ec.epaper_id desc ");
sql.AppendFormat(sqlWhere.ToString());
sqlLimit.AppendFormat(" limit {0},{1};", query.Start, query.Limit);
sql.AppendFormat(sqlLimit.ToString());
return _accessMySql.getDataTableForObj<EpaperContentQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EpaperContentDao-->GetEpaperContentList-->" + ex.Message + sql.ToString() + sqlWhere.ToString(), ex);
}
}
public HttpResponseBase LoadEpaperContent()
{
string json = string.Empty;
EpaperContentQuery query = new EpaperContentQuery();
EpaperContentQuery model = new EpaperContentQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["content_id"]))
{
_IEdmContentMgr = new EdmContentMgr(mySqlConnectionString);
query.epaper_id = Convert.ToUInt32(Request.Params["content_id"]);
_epaperMgr = new EpaperContentMgr(mySqlConnectionString);
model = _epaperMgr.GetEpaperContentById(query);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
if (model != null)
{
model.epaper_id = query.epaper_id;
switch (model.epaper_status)
{
case 0:
json = "{success:false" + ",data:" + JsonConvert.SerializeObject(model, Formatting.Indented, timeConverter) + ",msg:0}";
break;
case 1:
json = "{success:true" + ",data:" + JsonConvert.SerializeObject(model, Formatting.Indented, timeConverter) + "}";
break;
case 2:
json = "{success:false" + ",data:" + JsonConvert.SerializeObject(model, Formatting.Indented, timeConverter) + ",msg:1}";
break;
case 3:
json = "{success:false" + ",data:" + JsonConvert.SerializeObject(model, Formatting.Indented, timeConverter) + ",msg:2}";
break;
}
}
else
{
json = "{success:false,msg:3}";
}
}
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 string AddEpaperLog(EpaperContentQuery store)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("insert into epaper_log (log_id,epaper_id,user_id,log_description,log_ipfrom,log_createdate)");
sql.AppendFormat("values({0},{1},{2},'{3}','{4}',{5});", store.log_id, store.epaper_id, store.user_id, store.log_description, store.epaper_ipfrom, CommonFunction.GetPHPTime(DateTime.Now.ToString()));
return sql.ToString();
}
public string UpdateEpaperContent(EpaperContentQuery store)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("set sql_safe_updates=0;update epaper_content set epaper_title='{0}',epaper_short_title='{1}',epaper_content='{2}',", store.epaper_title, store.epaper_short_title, store.epaper_content);
sql.AppendFormat("epaper_sort={0},epaper_status={1},epaper_size='{2}',", store.epaper_sort, store.epaper_status, store.epaper_size);
sql.AppendFormat("epaper_show_start={0},epaper_show_end='{1}',fb_description='{2}',", CommonFunction.GetPHPTime(store.epaperShowStart.ToString()), CommonFunction.GetPHPTime(store.epaperShowEnd.ToString()), store.fb_description);
sql.AppendFormat("epaper_updatedate={0},epaper_ipfrom='{1}',type={2} ", CommonFunction.GetPHPTime(store.epaperUpdateDate.ToString()), store.epaper_ipfrom, store.type);
sql.AppendFormat(" where epaper_id={0};set sql_safe_updates=1;", store.epaper_id);
return sql.ToString();
}
public string InsertEpaperContent(EpaperContentQuery store)
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into epaper_content (epaper_id,user_id,epaper_title,epaper_short_title,epaper_size,");
sql.Append("epaper_content,epaper_sort,epaper_status,");
sql.Append("epaper_show_start,epaper_show_end,");
sql.Append("fb_description,epaper_ipfrom,type,");
sql.Append("epaper_createdate,epaper_updatedate) values(");
sql.AppendFormat("{0},{1},'{2}','{3}','{4}', ", store.epaper_id, store.user_id, store.epaper_title, store.epaper_short_title, store.epaper_size);
sql.AppendFormat("'{0}',{1},{2},", store.epaper_content, store.epaper_sort, store.epaper_status);
sql.AppendFormat("{0},{1},", CommonFunction.GetPHPTime(store.epaperShowStart.ToString()), CommonFunction.GetPHPTime(store.epaperShowEnd.ToString()));
sql.AppendFormat("'{0}','{1}',{2},", store.fb_description, store.epaper_ipfrom, store.type);
sql.AppendFormat("{0},{1});", CommonFunction.GetPHPTime(store.epaperCreateDate.ToString()), CommonFunction.GetPHPTime(store.epaperUpdateDate.ToString()));
return sql.ToString();
}
public int SaveEpaperContent(EpaperContentQuery query)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
int re = 0;
try
{
#region 新增
if (query.epaper_id == 0)
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
query.epaper_id = Convert.ToUInt32(GetSerialValue(24));//epapercontent
query.log_id = GetSerialValue(25);//log
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateSerialVal((Convert.ToInt32(query.epaper_id)), 24);
mySqlCmd.CommandText += InsertEpaperContent(query);
mySqlCmd.CommandText += UpdateSerialVal(query.log_id, 25);
mySqlCmd.CommandText += AddEpaperLog(query);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
#endregion
#region 編輯
else
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
//query.epaper_id = Convert.ToUInt32(GetSerialValue(24));//epapercontent
query.log_id = GetSerialValue(25);//log
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateEpaperContent(query);
mySqlCmd.CommandText += UpdateSerialVal(query.log_id, 25);
mySqlCmd.CommandText += AddEpaperLog(query);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
#endregion
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("NewsContentDao-->SaveEpaperContent-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
public EpaperContentQuery GetEpaperContentById(EpaperContentQuery query)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("select * from epaper_content where epaper_id='{0}'", query.epaper_id);
try
{
return _accessMySql.getSinggleObj<EpaperContentQuery>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("EpaperContentDao-->GetEpaperContentById-->" + ex.Message, ex);
}
}