public HttpResponseBase GetNewsList()
{
string json = string.Empty;
List<NewsContentQuery> store = new List<NewsContentQuery>();
NewsContentQuery query = new NewsContentQuery();
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
_INewsContentMgr = new NewsContentMgr(mySqlConnectionString);
int totalCount = 0;
if (!string.IsNullOrEmpty(Request.Params["searchCon"]))
{
query.searchCon = Request.Params["searchCon"];
}
if (!string.IsNullOrEmpty(Request.Params["search_con"]))
{
query.search_con = Request.Params["search_con"];
}
if (!string.IsNullOrEmpty(Request.Params["date"]))
{
query.date = Request.Params["date"];
}
if (!string.IsNullOrEmpty(Request.Params["start_time"]))
{
query.start_time = Convert.ToDateTime(Request.Params["start_time"]);
}
if (!string.IsNullOrEmpty(Request.Params["end_time"]))
{
query.end_time = Convert.ToDateTime(Request.Params["end_time"]);
}
store = _INewsContentMgr.GetNewsList(query, out totalCount);
foreach (var item in store)
{
item.s_news_show_start = CommonFunction.GetNetTime(item.news_show_start);
item.s_news_show_end = CommonFunction.GetNetTime(item.news_show_end);
item.s_news_createdate = CommonFunction.GetNetTime(item.news_createdate);
item.s_news_updatedate = CommonFunction.GetNetTime(item.news_updatedate);
item.news_content = Server.HtmlDecode(Server.HtmlDecode(item.news_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 int NewsContentSave(NewsContentQuery store)
{
try
{
return _INewsContentDao.NewsContentSave(store);
}
catch (Exception ex)
{
throw new Exception("NewsContentMgr-->NewsContentSave" + ex.Message, ex);
}
}
public HttpResponseBase NewsContentSave()
{
string json = string.Empty;
List<NewsContentQuery> store = new List<NewsContentQuery>();
NewsContentQuery OldQuery = new NewsContentQuery();
NewsContentQuery query = new NewsContentQuery();
_INewsContentMgr = new NewsContentMgr(mySqlConnectionString);
try
{
query.user_id = uint.Parse((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString());
if (string.IsNullOrEmpty(Request.Params["news_id"]))//新增
{
if (!string.IsNullOrEmpty(Request.Params["news_title"]))
{
query.news_title = Request.Params["news_title"].ToString().Replace("\\", "\\\\");
}
if (!string.IsNullOrEmpty(Request.Params["news_content"]))
{
query.news_content = Request.Params["news_content"];
}
if (!string.IsNullOrEmpty(Request.Params["news_sort"]))
{
query.news_sort = Convert.ToUInt32(Request.Params["news_sort"]);
}
if (!string.IsNullOrEmpty(Request.Params["news_status"]))
{
query.news_status = Convert.ToUInt32(Request.Params["news_status"]);
}
if (!string.IsNullOrEmpty(Request.Params["s_news_show_start"]))
{
query.s_news_show_start = Convert.ToDateTime(Request.Params["s_news_show_start"]);
}
if (!string.IsNullOrEmpty(Request.Params["s_news_show_end"]))
{
query.s_news_show_end = Convert.ToDateTime(Request.Params["s_news_show_end"]);
}
if (!string.IsNullOrEmpty(Request.Params["kendoEditor"]))
{
query.news_content = Request.Params["kendoEditor"].ToString().Replace("\\", "\\\\");
}
if (!string.IsNullOrEmpty(Request.Params["type"]))
{
if (Request.Params["type"] == "direct")//直接上稿
{
query.news_status = 1;
}
else
{
query.news_status = 0;
}
}
//System.Net.IPAddress[] addlist = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList;
//if (addlist.Length > 0)
//{
// query.news_ipfrom = addlist[0].ToString();
//}
query.news_ipfrom = GetIP4Address(Request.UserHostAddress.ToString());
query.s_news_createdate = DateTime.Now;
query.s_news_updatedate = query.s_news_createdate;
query.log_description = "add";
_INewsContentMgr.NewsContentSave(query);
json = "{success:true}";
}
else
{
query.news_id = Convert.ToUInt32(Request.Params["news_id"]);
OldQuery = _INewsContentMgr.OldQuery(query.news_id);
if (!string.IsNullOrEmpty(Request.Params["news_title"]))
{
query.news_title = Request.Params["news_title"].ToString().Replace("\\", "\\\\");
}
else
{
query.news_title = OldQuery.news_title;
}
if (!string.IsNullOrEmpty(Request.Params["kendoEditor"]))
{
query.news_content = Request.Params["kendoEditor"].ToString().Replace("\\", "\\\\");
}
else
{
query.news_content = OldQuery.news_content;
}
if (!string.IsNullOrEmpty(Request.Params["news_sort"]))
{
query.news_sort = Convert.ToUInt32(Request.Params["news_sort"]);
}
else
{
query.news_sort = OldQuery.news_sort;
}
if (!string.IsNullOrEmpty(Request.Params["news_status"]))
{
query.news_status = Convert.ToUInt32(Request.Params["news_status"]);
}
else
{
query.news_status = OldQuery.news_status;
}
if (!string.IsNullOrEmpty(Request.Params["s_news_show_start"]))
{
query.s_news_show_start = Convert.ToDateTime(Request.Params["s_news_show_start"]);
}
else
{
query.s_news_show_start = CommonFunction.GetNetTime(query.news_show_start);
}
if (!string.IsNullOrEmpty(Request.Params["s_news_show_end"]))
{
query.s_news_show_end = Convert.ToDateTime(Request.Params["s_news_show_end"]);
}
else
{
query.s_news_show_end = CommonFunction.GetNetTime(query.news_show_end);
}
System.Net.IPAddress[] addlist = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList;
if (addlist.Length > 0)
{
query.news_ipfrom = addlist[0].ToString();
}
query.s_news_updatedate = DateTime.Now;
query.log_description = "modify,status=" + query.news_status;
_INewsContentMgr.NewsContentSave(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 string AddNewsLog(NewsContentQuery store)
{
StringBuilder sql = new StringBuilder();
if (store.news_id == 0)
{
sql.AppendFormat("insert into news_log (log_id,news_id,user_id,log_description,log_ipfrom,log_createdate)");
sql.AppendFormat("values({0},{1},{2},'{3}','{4}',{5});", store.log_value, store.serialvalue, store.user_id, store.log_description, store.news_ipfrom, CommonFunction.GetPHPTime(DateTime.Now.ToString()));
}
else
{
sql.AppendFormat("insert into news_log (log_id,news_id,user_id,log_description,log_ipfrom,log_createdate)");
sql.AppendFormat("values({0},{1},{2},'{3}','{4}',{5});", store.log_value, store.news_id, store.user_id, store.log_description, store.news_ipfrom, CommonFunction.GetPHPTime(DateTime.Now.ToString()));
}
return sql.ToString();
}
public List<NewsContentQuery> GetNewsList(NewsContentQuery store, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
StringBuilder sqlLimit = new StringBuilder();
totalCount = 0;
try
{
sqlCount.Append("select count(*) as totalCount ");
sql.Append("select nc.news_id ,nc.user_id,nc.news_title,nc.news_sort,nc.news_status,nc.news_show_start,nc.news_show_end,mu.user_username, nc.news_content,nc.news_ipfrom,nc.news_createdate,nc.news_updatedate ");
sqlFrom.Append("from news_content nc LEFT JOIN manage_user mu on nc.user_id=mu.user_id ");
sqlWhere.Append(" where 1=1 ");
#region 狀態
if (!string.IsNullOrEmpty(store.searchCon))
{
if (store.searchCon == "0")
{
sqlWhere.AppendFormat(" and nc.news_status=0 ");
}
else if (store.searchCon == "1")
{
sqlWhere.AppendFormat(" and nc.news_status=1 ");
}
else if (store.searchCon == "2")
{
sqlWhere.AppendFormat(" and nc.news_status=2 ");
}
else if (store.searchCon == "3")
{
sqlWhere.AppendFormat(" and nc.news_status=3 ");
}
}
#endregion
#region 標題
if (!string.IsNullOrEmpty(store.search_con))
{
sqlWhere.AppendFormat(" and nc.news_title like '%{0}%' ", store.search_con);
}
#endregion
#region 日期條件
if (!string.IsNullOrEmpty(store.date))
{
if (store.start_time != DateTime.MinValue && store.end_time != DateTime.MinValue)
{
if (store.date == "0")
{
sqlWhere.AppendFormat(" and nc.news_show_start >={0} and nc.news_show_end<={1} ", CommonFunction.GetPHPTime(store.start_time.ToString()), CommonFunction.GetPHPTime(store.end_time.ToString()));
}
else if (store.date == "1")
{
sqlWhere.AppendFormat(" and nc.news_show_start >={0} and nc.news_show_start<={1} ", CommonFunction.GetPHPTime(store.start_time.ToString()), CommonFunction.GetPHPTime(store.end_time.ToString()));
}
else if (store.date == "2")
{
sqlWhere.AppendFormat(" and nc.news_show_end>={0} and nc.news_show_end<={1} ", CommonFunction.GetPHPTime(store.start_time.ToString()), CommonFunction.GetPHPTime(store.end_time.ToString()));
}
}
}
#endregion
if (store.IsPage)
{
DataTable _dt = _accessMySql.getDataTable(sqlCount.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
}
sqlLimit.AppendFormat(" ORDER BY nc.news_show_start DESC,nc.news_sort DESC,nc.news_id desc limit {0},{1};", store.Start, store.Limit);
sql.AppendFormat(sqlFrom.ToString() + sqlWhere.ToString() + sqlLimit.ToString());
return _accessMySql.getDataTableForObj<NewsContentQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("NewsContentDao-->GetNewsList-->" + ex.Message + sql.ToString(), ex);
}
}
public string UpdateNewsContent(NewsContentQuery store)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("set sql_safe_updates=0;update news_content set news_title='{0}',news_content='{1}',news_sort={2},", store.news_title, store.news_content, store.news_sort);
sql.AppendFormat("news_status={0},news_show_start={1},news_show_end={2},", store.news_status, CommonFunction.GetPHPTime(store.s_news_show_start.ToString()), CommonFunction.GetPHPTime(store.s_news_show_end.ToString()));
sql.AppendFormat("news_updatedate={0},news_ipfrom='{1}'", CommonFunction.GetPHPTime(store.s_news_updatedate.ToString()), store.news_ipfrom);
sql.AppendFormat(" where news_id={0};set sql_safe_updates=1;", store.news_id);
return sql.ToString();
}
public string InsertNewsContent(NewsContentQuery store)
{
StringBuilder sql = new StringBuilder();
sql.Append("insert into news_content (news_id,user_id,news_title,news_content,news_sort,");
sql.Append("news_status,news_show_start,news_show_end,");
sql.Append("news_createdate,news_updatedate,");
sql.Append("news_ipfrom)");
sql.AppendFormat("values({0},{1},'{2}','{3}',{4},", store.serialvalue, store.user_id, store.news_title, store.news_content, store.news_sort);
sql.AppendFormat("{0},{1},{2}, ", store.news_status, CommonFunction.GetPHPTime(store.s_news_show_start.ToString()), CommonFunction.GetPHPTime(store.s_news_show_end.ToString()));
sql.AppendFormat("{0},{1},", CommonFunction.GetPHPTime(store.s_news_createdate.ToString()), CommonFunction.GetPHPTime(store.s_news_updatedate.ToString()));
sql.AppendFormat("'{0}');", store.news_ipfrom);
return sql.ToString();
}
public int NewsContentSave(NewsContentQuery store)
{
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
StringBuilder sql = new StringBuilder();
int re = 0;
try
{
#region 新增
if (store.news_id == 0)//新增
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
store.serialvalue = GetSerialValue(20);//news_content
store.log_value = GetSerialValue(21);//news_log
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateSerialVal(store.serialvalue, 20);
mySqlCmd.CommandText += InsertNewsContent(store);
mySqlCmd.CommandText += UpdateSerialVal(store.log_value, 21);
mySqlCmd.CommandText += AddNewsLog(store);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
#endregion
#region 編輯
else//編輯
{
store.Replace4MySQL();
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
store.serialvalue = GetSerialValue(20);//news_content
store.log_value = GetSerialValue(21);//news_log
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = UpdateNewsContent(store);
mySqlCmd.CommandText += UpdateSerialVal(store.log_value, 21);
mySqlCmd.CommandText += AddNewsLog(store);
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
#endregion
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("NewsContentDao-->NewsContentSave-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}