/// <summary>
///活動留言列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<VoteMessageQuery> GetVoteMessageList(VoteMessageQuery query, out int totalCount)
{
List<VoteMessageQuery> list = new List<VoteMessageQuery>();
StringBuilder sbSql = new StringBuilder();
StringBuilder sbSqlCount = new StringBuilder();
StringBuilder sbSqlCondition = new StringBuilder();
try
{
sbSql.Append("select vm.message_id,vm.article_id,vm.ip,va.article_title,vm.message_status,vm.message_content,vm.create_time,vm.create_user,mu.user_username as create_name,mu2.user_username as update_name, vm.update_time,vm.update_user ");
sbSqlCondition.Append(" from vote_message vm ");
sbSqlCondition.Append(" left join manage_user mu on mu.user_id=vm.create_user ");
sbSqlCondition.Append(" left join manage_user mu2 on mu2.user_id=vm.update_user ");
sbSqlCondition.Append(" left join vote_article va on va.article_id=vm.article_id ");
sbSqlCondition.Append(" where 1=1 ");
sbSqlCount.Append("select count(vm.message_id) as totalCount ");
if (!string.IsNullOrEmpty(query.article_title))
{
sbSqlCondition.AppendFormat(" and va.article_title like N'%{0}%' ", query.article_title);
}
if (query.message_status != -1)
{
sbSqlCondition.AppendFormat(" and vm.message_status ='{0}' ", query.message_status);
}
if (query.article_id != 0)
{
sbSqlCondition.AppendFormat(" and vm.article_id ='{0}' ", query.article_id);
}
if (!string.IsNullOrEmpty(query.message_content))
{
sbSqlCondition.AppendFormat(" and vm.message_content like N'%{0}%' ", query.message_content);
}
if (query.message_id != 0)
{
sbSqlCondition.AppendFormat(" and vm.message_id ='{0}' ", query.message_id);
}
totalCount = 0;
if (query.IsPage)
{
sbSqlCount.Append(sbSqlCondition.ToString());
DataTable _dt = _access.getDataTable(sbSqlCount.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sbSqlCondition.Append(" order by vm.message_id desc ");
sbSqlCondition.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
else {
sbSqlCondition.Append(" order by vm.message_id desc ");
}
list = _access.getDataTableForObj<VoteMessageQuery>(sbSql.Append(sbSqlCondition).ToString());
}
catch (Exception ex)
{
throw new Exception("VoteMessageDao-->GetVoteMessageList-->" + ex.Message + sbSql.Append(sbSqlCondition).ToString()+sbSqlCount.ToString(), ex);
}
return list;
}
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddVoteMessage(VoteMessageQuery model)
{
try
{
return _voteMsgDao.AddVoteMessage(model);
}
catch (Exception ex)
{
throw new Exception("VoteMessageMgr-->AddVoteMessage-->" + ex.Message, ex);
}
}
/// <summary>
/// 回復消息列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<VoteMessageQuery> GetVoteMessageList(VoteMessageQuery query,out int totalCount)
{
try
{
return _voteMsgDao.GetVoteMessageList(query,out totalCount);
}
catch (Exception ex)
{
throw new Exception("VoteMessageMgr-->AddVoteMessage-->" + ex.Message, ex);
}
}
/// <summary>
///刪除列表頁
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int DelVoteMessage(VoteMessageQuery query)
{
try
{
return _voteMsgDao.DelVoteMessage(query);
}
catch (Exception ex)
{
throw new Exception("VoteMessageMgr-->DelVoteMessage-->" + ex.Message, ex);
}
}
/// <summary>
///更改活動狀態
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int UpVoteMessageStatus(VoteMessageQuery query)
{
int result = 0;
try
{
//if (query.message_status == 0)
//{
result = _voteMsgDao.UpVoteMessageStatus(query);
//}
//else
//{
//}
return result;
}
catch (Exception ex)
{
throw new Exception("VoteMessageMgr-->UpVoteMessageStatus-->" + ex.Message, ex);
}
}
/// <summary>
/// 新增活動留言
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddVoteMessage(VoteMessageQuery model)
{
StringBuilder sbSql = new StringBuilder();
model.Replace4MySQL();
sbSql.Append("insert into vote_message(article_id,create_time,create_user,update_time,update_user,ip,message_status,message_content)values(");
sbSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", model.article_id, CommonFunction.DateTimeToString(model.create_time), model.create_user, CommonFunction.DateTimeToString(model.update_time), model.update_user, model.ip, model.message_status, model.message_content);
sbSql.Append("SELECT @@IDENTITY;");
try
{
DataTable _dt = _access.getDataTable(sbSql.ToString());
if (_dt.Rows.Count > 0)
{
return Convert.ToInt32(_dt.Rows[0][0]);
}
else
{
return 0;
}
}
catch (Exception ex)
{
throw new Exception("VoteMessageDao-->AddVoteMessage-->" + ex.Message + sbSql.ToString(), ex);
}
}
/// <summary>
/// 批量刪除留言
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int DelVoteMessage(VoteMessageQuery query)
{
int result = 0;
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("delete from vote_message where message_id in({0});", query.message_id_in);
result = _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("VoteMessageDao.DelVoteMessage-->" + ex.Message + sql.ToString(), ex);
}
return result;
}
/// <summary>
/// 更改留言狀態
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int UpVoteMessageStatus(VoteMessageQuery query)
{
int result = 0;
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.Append("set sql_safe_updates=0;");
sql.AppendFormat("update vote_message set message_status='{0}', update_time='{1}'", query.message_status,CommonFunction.DateTimeToString(query.update_time));
sql.AppendFormat(" ,update_user='******',ip='{1}' where message_id='{2}'; ", query.update_user,query.ip,query.message_id);
sql.Append("set sql_safe_updates=1;");
result = _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("VoteMessageDao.UpVoteMessageStatus-->" + ex.Message + sql.ToString(), ex);
}
return result;
}
/// <summary>
/// 修改活動留言
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateVoteMessage(VoteMessageQuery model)
{
int result = 0;
StringBuilder sbSql = new StringBuilder();
model.Replace4MySQL();
sbSql.Append("set sql_safe_updates=0;");
sbSql.AppendFormat("update vote_message set article_id='{0}',update_time='{1}',update_user='******'", model.article_id, CommonFunction.DateTimeToString(model.update_time), model.update_user);
sbSql.AppendFormat(",message_content='{0}',ip='{1}' where message_id='{2}';", model.message_content,model.ip,model.message_id);
sbSql.Append("set sql_safe_updates=1;");
try
{
result = _access.execCommand(sbSql.ToString());
}
catch (Exception ex)
{
throw new Exception("VoteMessageDao-->UpdateVoteMessage-->" + ex.Message + sbSql.ToString(), ex);
}
return result;
}
/// <summary>
/// 留言管理列表
/// </summary>
/// <returns></returns>
public HttpResponseBase GetVoteMessageList()
{
List<VoteMessageQuery> store = new List<VoteMessageQuery>();
string json = string.Empty;
int tranInt = 0;
try
{
VoteMessageQuery query = new VoteMessageQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
_voteMsg = new VoteMessageMgr(mySqlConnectionString);
int totalCount = 0;
if (!string.IsNullOrEmpty(Request.Params["message"]))
{
query.message_content = Request.Params["message"];
//if (int.TryParse(Request.Params["message"], out tranInt))
//{
// query.message_id = int.Parse(Request.Params["message"].ToString());
//}
//else
//{
// query.message_content = Request.Params["message"];
//}
}
if (!string.IsNullOrEmpty(Request.Params["article"]))
{
query.article_id = int.Parse(Request.Params["article"].ToString());
//if (int.TryParse(Request.Params["article"], out tranInt))
//{
// query.article_id = int.Parse(Request.Params["article"].ToString());
//}
//else
//{
// query.article_title = Request.Params["article"];
//}
}
query.message_status = -1;
if (!string.IsNullOrEmpty(Request.Params["status"]))
{
query.message_status = int.Parse(Request.Params["status"].ToString());
}
store = _voteMsg.GetVoteMessageList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據
}
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 JsonResult UpdateVoteMessageStatus()
{
try
{
VoteMessageQuery query = new VoteMessageQuery();
if (!string.IsNullOrEmpty(Request.Params["message_id"].ToString()))
{
query.message_id = Convert.ToInt32(Request.Params["message_id"].ToString());
}
query.message_status = Convert.ToInt32(Request.Params["message_status"] ?? "0");
_voteMsg = new VoteMessageMgr(mySqlConnectionString);
query.update_user = (Session["caller"] as Caller).user_id;
System.Net.IPAddress[] addlist = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList;
query.ip = addlist[0].ToString();
query.update_time = DateTime.Now;
if (_voteMsg.UpVoteMessageStatus(query) > 0)
{
return Json(new { success = "true" });
}
else
{
return Json(new { success = "false" });
}
}
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);
return Json(new { success = "false" });
}
}
/// <summary>
/// 刪除留言列表
/// </summary>
/// <returns></returns>
public HttpResponseBase DeleteVoteMessage()
{
VoteMessageQuery query = new VoteMessageQuery();
string json = string.Empty;
try
{
string Row_id = "";
if (!string.IsNullOrEmpty(Request.Params["rowId"]))
{
Row_id = Request.Params["rowId"];
query.message_id_in = Row_id.TrimEnd(',');
}
_voteMsg = new VoteMessageMgr(mySqlConnectionString);
int result = _voteMsg.DelVoteMessage(query);
if (result > 0)
{
json = "{success:true,msg:\"" + result + "\"}";
}
else
{
json = "{success:false,msg:\"" + result + "\"}";
}
}
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,msg:'0'}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 保存留言管理
/// </summary>
/// <returns></returns>
public HttpResponseBase SaveVoteMessage()
{
string json = string.Empty;
try
{
VoteMessageQuery query = new VoteMessageQuery();
if (!string.IsNullOrEmpty(Request.Params["message_id"]))
{
query.message_id = Convert.ToInt32(Request.Params["message_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["article_id"]))
{
query.article_id = Convert.ToInt32(Request.Params["article_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["message_content"]))
{
query.message_content = Request.Params["message_content"];
}
System.Net.IPAddress[] addlist = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList;
query.ip = addlist[0].ToString();
query.create_user = (Session["caller"] as Caller).user_id;
query.update_user = (Session["caller"] as Caller).user_id;
query.create_time = DateTime.Now;
query.update_time = query.create_time;
_voteMsg = new VoteMessageMgr(mySqlConnectionString);
int result = 0;
if (query.message_id != 0)//編輯
{
result = _voteMsg.UpdateVoteMessage(query);
}
else //新增
{
result = _voteMsg.AddVoteMessage(query);
}
if (result > 0)
{
json = "{\"success\":\"true\",\"msg\":\"保存成功!\"}";
}
else
{
json = "{\"success\":\"false\",\"msg\":\"保存失敗!\"}";
}
}
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\",\"msg\":\"參數出錯!\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}