/// <summary>
/// 通過查詢條件獲取VoteDetail列表
/// </summary>
/// <param name="query">查詢條件</param>
/// <param name="totalCount">數據總條數</param>
/// <returns>VoteDetail列表</returns>
public List<VoteDetailQuery> GetList(VoteDetailQuery query, out int totalCount)
{
try
{
return _voteDetailDao.GetList(query,out totalCount);
}
catch (Exception ex)
{
throw new Exception("VoteDetailMgr-->GetList-->" + ex.Message, ex);
}
}
public JsonResult UpdateVoteDetaiStatus()
{
try
{
VoteDetailQuery query = new VoteDetailQuery();
if (!string.IsNullOrEmpty(Request.Params["vote_id"].ToString()))
{
query.vote_id = Convert.ToInt32(Request.Params["vote_id"].ToString());
}
query.vote_status = Convert.ToInt32(Request.Params["vote_status"] ?? "0");
voteDetailMgr = new VoteDetailMgr(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 (voteDetailMgr.UpdateVoteDetaiStatus(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" });
}
}
public void VoteDetailExportExcel()
{
string json = string.Empty;
DataTable _dt = new DataTable();
DataTable dtHZ = new DataTable();
VoteDetailQuery query = new VoteDetailQuery();
try
{
if (Request.Params["article_id"]!="null")
{
query.article_id = Convert.ToInt32(Request.Params["article_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["searchContent"]))
{
query.searchContent = Request.Params["searchContent"];
}
if (!string.IsNullOrEmpty(Request.Params["time_start"]))
{
query.start_time = Convert.ToDateTime(Request.Params["time_start"]);
}
if (!string.IsNullOrEmpty(Request.Params["time_end"]))
{
query.end_time = Convert.ToDateTime(Request.Params["time_end"]);
}
//query.vote_status = -1;
if (Request.Params["vote_status"] != "null")
{
query.vote_status = Convert.ToInt32(Request.Params["vote_status"]);
}
//活動編號，會員編號，email，姓名，投那一支商品，投票日
string newExcelName = string.Empty;
dtHZ.Columns.Add("編號", typeof(String));
dtHZ.Columns.Add("活動編號", typeof(String));
dtHZ.Columns.Add("文章標題", typeof(String));
dtHZ.Columns.Add("會員編號", typeof(String));
// dtHZ.Columns.Add("Email", typeof(String));
dtHZ.Columns.Add("姓名", typeof(String));
dtHZ.Columns.Add("投票商品", typeof(String));
dtHZ.Columns.Add("投票日", typeof(String));
dtHZ.Columns.Add("是否啟用", typeof(String));
voteDetailMgr = new VoteDetailMgr(mySqlConnectionString);
List<VoteDetailQuery> list = new List<VoteDetailQuery>();
//cuQuery.search_type = Convert.ToInt32(Request.Params["search_type"]);
//cuQuery.searchcontent = Request.Params["searchcontent"];
//cuQuery.date_type = Convert.ToInt32(Request.Params["date_type"]);
//cuQuery.datestart = Convert.ToDateTime(Request.Params["dateStart"]);//建立時間
//cuQuery.dateend = Convert.ToDateTime(Request.Params["dateEnd"]);
//cuQuery.question_type = Convert.ToUInt32(Request.Params["qusetion_type"]);
//if (Convert.ToBoolean(Request.Params["radio2"]) == true)//待回覆
//{
// cuQuery.question_status = 3;
//}
//else if (Convert.ToBoolean(Request.Params["radio3"]) == true)//已回覆
//{
// cuQuery.question_status = 4;
//}
//else if (Convert.ToBoolean(Request.Params["radio4"]) == true)
//{
// cuQuery.question_status = 2;
//}
query.IsPage = false;
int totalCount = 0;
_dt = voteDetailMgr.GetDtVoteDetail(query, out totalCount);
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
for (int i = 0; i < _dt.Rows.Count; i++)
{
DataRow dr = dtHZ.NewRow();
dr[0] = _dt.Rows[i]["vote_id"];
dr[1] = "";
if (!string.IsNullOrEmpty(_dt.Rows[i]["event_id"].ToString()))
{
dr[1] = "【" + _dt.Rows[i]["event_id"].ToString() + "】" + _dt.Rows[i]["event_name"].ToString();
}
dr[2] = "";
if (!string.IsNullOrEmpty(_dt.Rows[i]["article_id"].ToString()))
{
dr[2] = "【" + _dt.Rows[i]["article_id"].ToString() + "】" + _dt.Rows[i]["article_title"].ToString();
}
dr[3] = _dt.Rows[i]["user_id"];
// dr[4] = _dt.Rows[i]["user_email"];
dr[4] = _dt.Rows[i]["user_name"];
dr[5] = "";
if (!string.IsNullOrEmpty(_dt.Rows[i]["product_id"].ToString()))
{
dr[5] ="【"+_dt.Rows[i]["product_id"].ToString()+"】" + _dt.Rows[i]["product_name"].ToString();
}
dr[6] = _dt.Rows[i]["create_time"];
dr[7] = Convert.ToInt32(_dt.Rows[i]["vote_status"]) == 0 ? "否" : "是";
dtHZ.Rows.Add(dr);
}
if (dtHZ.Rows.Count > 0)
{
string fileName = DateTime.Now.ToString("投票信息_yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "投票信息_" + DateTime.Now.ToString("yyyyMMddHHmmss"));
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
}
else
{
Response.Write("匯出數據不存在");
}
}
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:[]}";
}
}
/// <summary>
/// 保存投票管理
/// </summary>
/// <returns></returns>
public HttpResponseBase SaveVoteDetail()
{
string json = string.Empty;
string vote_id = Request.Params["vote_id"];
string article_id = Request.Params["article_id"];
string user_id = Request.Params["user_id"];
VoteDetailQuery query = new VoteDetailQuery();
if (!string.IsNullOrEmpty(vote_id))
{
query.vote_id = Convert.ToInt32(vote_id);
}
if (!string.IsNullOrEmpty(article_id))
{
query.article_id = Convert.ToInt32(article_id);
}
if (!string.IsNullOrEmpty(user_id))
{
query.user_id = Convert.ToInt32(user_id);
}
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;
DateTime currentTime = DateTime.Now;
query.create_time = currentTime;
query.update_time = currentTime;
voteDetailMgr = new VoteDetailMgr(mySqlConnectionString);
int result = 0;
try
{
if (query.vote_id != 0)//編輯
{
result = voteDetailMgr.Update(query);
}
else //新增
{
query.vote_status = 0;
result = voteDetailMgr.Add(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;
}
/// <summary>
/// 獲取投票管理列表
/// </summary>
/// <returns></returns>
public HttpResponseBase VoteDetailList()
{
string json = string.Empty;
int totalCount = 0;
List<VoteDetailQuery> list = new List<VoteDetailQuery>();
voteDetailMgr = new VoteDetailMgr(mySqlConnectionString);
VoteDetailQuery query = new VoteDetailQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");
string article_id = Request.Params["article_id"];
string searchContent = Request.Params["searchContent"];
string start_time = Request.Params["time_start"];
string end_time = Request.Params["time_end"];
string vote_status = Request.Params["vote_status"];
if (!string.IsNullOrEmpty(article_id) && Convert.ToInt32(article_id) != 0)
{
query.article_id = Convert.ToInt32(article_id);
}
if (!string.IsNullOrEmpty(searchContent))
{
query.searchContent = searchContent;
}
if (!string.IsNullOrEmpty(start_time))
{
query.start_time =Convert.ToDateTime(Convert.ToDateTime(start_time).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(end_time))
{
query.end_time = Convert.ToDateTime(Convert.ToDateTime(end_time).ToString("yyyy-MM-dd HH:mm:ss"));
}
query.vote_status = -1;
if (!string.IsNullOrEmpty(vote_status))
{
query.vote_status = Convert.ToInt32(vote_status);
}
if (!string.IsNullOrEmpty(Request.Params["relation_id"]))//待回覆
{
query.vote_id = Convert.ToInt32(Request.Params["relation_id"]);
}
try
{
list = voteDetailMgr.GetList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
if (Convert.ToBoolean(Request.Params["isSecret"]))
{
foreach (var item in list)
{
if (!string.IsNullOrEmpty(item.user_name))
{
item.user_name = item.user_name.Substring(0, 1) + "**";
}
}
}
//listUser是准备转换的对象
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(list, 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 int UpdateVoteDetaiStatus(VoteDetailQuery query)
{
int result = 0;
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.Append("set sql_safe_updates=0;");
sql.AppendFormat("update vote_detail set vote_status='{0}', update_time='{1}'", query.vote_status, CommonFunction.DateTimeToString(query.update_time));
sql.AppendFormat(" ,update_user='******',ip='{1}' where vote_id='{2}'; ", query.update_user, query.ip, query.vote_id);
sql.Append("set sql_safe_updates=1;");
result = _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("VoteDetailDao.UpdateVoteDetaiStatus-->" + ex.Message + sql.ToString(), ex);
}
return result;
}
public DataTable GetDtVoteDetail(VoteDetailQuery query, out int totalCount)
{
StringBuilder sbSql = new StringBuilder();
StringBuilder sbSqlCondition = new StringBuilder();
DataTable dt = new DataTable();
totalCount = 0;
try
{
sbSql.Append("select vd.vote_id,p.product_id,p.product_name,ve.event_id,ve.event_name,va.article_id,va.article_title,vd.user_id,u.user_name,vd.create_time,vd.vote_status ");
sbSql.Append(" from vote_detail vd ");
sbSqlCondition.Append(" LEFT JOIN vote_article va on va.article_id=vd.article_id ");
sbSqlCondition.Append(" left join vote_event ve on ve.event_id=va.event_id ");
sbSqlCondition.Append(" LEFT JOIN users u on u.user_id=vd.user_id ");
sbSqlCondition.Append(" left join product p on va.product_id=p.product_id ");
sbSqlCondition.Append(" where 1=1 ");
if (query.vote_id != 0)
{
//sbSqlCondition.AppendFormat(" and vd.vote_id in({0}) ", query.vote_id);
sbSqlCondition.AppendFormat(" and vd.vote_id ='{0}' ", query.vote_id);
}
if (query.article_id != 0)
{
//sbSqlCondition.AppendFormat(" and vd.article_id in({0}) ", query.article_id);
sbSqlCondition.AppendFormat(" and vd.article_id ='{0}' ", query.article_id);
}
if (!string.IsNullOrEmpty(query.searchContent))
{
sbSqlCondition.AppendFormat(" and (vd.article_id like N'%{0}%' or va.article_title like N'%{0}%' or u.user_id like N'%{0}%') ", query.searchContent);
}
if (query.start_time != null)
{
sbSqlCondition.AppendFormat(" and vd.create_time > '{0}' ", CommonFunction.DateTimeToString(query.start_time));
}
if (query.end_time != null)
{
sbSqlCondition.AppendFormat(" and vd.create_time < '{0}' ", CommonFunction.DateTimeToString(query.end_time));
}
if (query.vote_status != -1)
{
sbSqlCondition.AppendFormat(" and vd.vote_status = {0} ", query.vote_status);
}
if (query.IsPage)
{
dt = _access.getDataTable("select count(vd.vote_id) as totalCount " + sbSqlCondition.ToString());
if (dt != null && dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
}
sbSqlCondition.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
dt = _access.getDataTable(sbSql.Append(sbSqlCondition).ToString());
}
catch (Exception ex)
{
throw new Exception("VoteDetailDao-->GetDtVoteDetail-->" + ex.Message + sbSql.Append(sbSqlCondition).ToString(), ex);
}
return dt;
}
/// <summary>
/// 通過查詢條件獲取VoteDetail列表
/// </summary>
/// <param name="query">查詢條件</param>
/// <param name="totalCount">數據總條數</param>
/// <returns>VoteDetail列表</returns>
public List<VoteDetailQuery> GetList(VoteDetailQuery query, out int totalCount)
{
List<VoteDetailQuery> list = new List<VoteDetailQuery>();
StringBuilder sbSqlColumn = new StringBuilder();
StringBuilder sbSqlCount = new StringBuilder();
StringBuilder sbSqlTable = new StringBuilder();
StringBuilder sbSqlCondition = new StringBuilder();
totalCount = 0;
sbSqlColumn.Append("SELECT vote_id,vd.article_id,va.article_title,m.user_name,vd.user_id,ip,vote_status,vd.create_user,vd.update_user,vd.create_time,vd.update_time ");
sbSqlTable.Append(" FROM vote_detail as vd ");
sbSqlTable.Append(" LEFT JOIN vote_article as va ON vd.article_id=va.article_id ");
sbSqlTable.Append(" LEFT JOIN users m ON vd.user_id=m.user_id ");
sbSqlCondition.Append(" WHERE 1=1 ");
sbSqlCount.Append("select count(vote_id) as totalCount ");
if (query.vote_id != 0)
{
//sbSqlCondition.AppendFormat(" and vote_id in({0}) ", query.vote_id);
sbSqlCondition.AppendFormat(" and vd.vote_id ='{0}' ", query.vote_id);
}
if (query.article_id != 0)
{
//sbSqlCondition.AppendFormat(" and vd.article_id in({0}) ", query.article_id);
sbSqlCondition.AppendFormat(" and vd.article_id ='{0}' ", query.article_id);
}
if (!string.IsNullOrEmpty(query.searchContent))
{
sbSqlCondition.AppendFormat(" and (vd.article_id like N'%{0}%' or va.article_title like N'%{0}%' or vd.user_id like N'%{0}%') ", query.searchContent);
}
if (query.start_time != null)
{
sbSqlCondition.AppendFormat(" and vd.create_time > '{0}' ", CommonFunction.DateTimeToString(query.start_time));
}
if (query.end_time != null)
{
sbSqlCondition.AppendFormat(" and vd.create_time < '{0}' ", CommonFunction.DateTimeToString(query.end_time));
}
if (query.vote_status != -1)
{
//sbSqlCondition.AppendFormat(" and vote_status in ({0}) ", query.vote_status);
sbSqlCondition.AppendFormat(" and vote_status ='{0}' ", query.vote_status);
}
try
{
if (query.IsPage)
{
sbSqlCount.Append(sbSqlTable.ToString()+sbSqlCondition.ToString());
DataTable dt = _access.getDataTable( sbSqlCount.ToString());
if (dt != null && dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]);
}
}
sbSqlCondition.Append(" order by vote_id desc ");
sbSqlCondition.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
sbSqlColumn.Append(sbSqlTable.ToString()+sbSqlCondition.ToString());
list = _access.getDataTableForObj<VoteDetailQuery>(sbSqlColumn.ToString());
}
catch (Exception ex)
{
throw new Exception("VoteDetailDao-->GetList-->" + ex.Message + sbSqlColumn.ToString() + sbSqlCount.ToString(), ex);
}
return list;
}
public DataTable GetDtVoteDetail(VoteDetailQuery query, out int totalCount)
{
try
{
return _voteDetailDao.GetDtVoteDetail(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("VoteDetailMgr-->GetDtVoteDetail-->" + ex.Message, ex);
}
}
public int UpdateVoteDetaiStatus(VoteDetailQuery query)
{
try
{
return _voteDetailDao.UpdateVoteDetaiStatus(query);
}
catch (Exception ex)
{
throw new Exception("VoteDetailMgr-->UpdateVoteDetaiStatus-->" + ex.Message, ex);
}
}