/// <summary>
/// 有關mail的查詢（此方法可擴充，擴充后請詳細注釋）
/// 內容：根據用戶編號查詢啟用狀態用戶email地址 add by shuangshuang0420j 2015.02.04
/// 修改：
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<MailUserQuery> MailUserQuery(MailUserQuery query)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
try
{
sql.Append(@" select row_id,user_mail,user_name,`status`,user_pwd,create_time,create_user,update_time,update_user ");
sqlCondi.Append(" from mail_user ");
sqlCondi.Append(" where 1=1 ");//啟用狀態
if (query.row_id != 0)
{
sqlCondi.AppendFormat(" and row_id={0}", query.row_id);
}
if (query.status != -1)
{
sqlCondi.AppendFormat(" and `status`={0}", query.status);
}
sql.Append(sqlCondi.ToString());
return _access.getDataTableForObj<MailUserQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("MailUserDao.MailUserQuery-->" + ex.Message + sql.ToString(), ex);
}
}
public int UpdateMailUserStatus(MailUserQuery query)
{
try
{
return _mailUserDao.UpdateMailUserStatus(query);
}
catch (Exception ex)
{
throw new Exception("MailUserMgr-->UpdateMailUserStatus-->" + ex.Message, ex);
}
}
public int DeleteMailUser(MailUserQuery query)
{
try
{
return _mailUserDao.DeleteMailUser(query);
}
catch (Exception ex)
{
throw new Exception("MailUserMgr-->DeleteMailUser-->" + ex.Message, ex);
}
}
public List<MailUserQuery> GetMailUserStore(MailUserQuery query, out int totalcount)
{
try
{
return _mailUserDao.GetMailUserStore(query, out totalcount);
}
catch (Exception ex)
{
throw new Exception("MailUserMgr-->GetMailUserStore-->" + ex.Message, ex);
}
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="totalcount"></param>
/// <returns></returns>
public List<MailUserQuery> GetMailUserStore(MailUserQuery query, out int totalcount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
try
{
sql.Append(@" select row_id,user_mail,user_name,`status`,user_pwd,create_time,create_user,update_time,update_user ");
sqlCondi.Append(" from mail_user ");
sqlCondi.Append(" where 1=1 ");
if (!string.IsNullOrEmpty(query.user_name))
{
sqlCondi.AppendFormat(" and user_name like '%{0}%' ", query.user_name);
}
if (!string.IsNullOrEmpty(query.user_mail))
{
sqlCondi.AppendFormat(" and user_mail like '%{0}%' ", query.user_mail);
}
if (query.row_id != 0)
{
sqlCondi.AppendFormat(" and row_id = '{0}' ", query.row_id);
}
totalcount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable("select count(row_id) as totalCount " + sqlCondi.ToString());
if (_dt.Rows.Count > 0)
{
totalcount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlCondi.Append(" order by row_id desc ");
sqlCondi.AppendFormat(" limit {0},{1}", query.Start, query.Limit);
}
else
{
sqlCondi.Append(" and status=1; ");
}
sql.Append(sqlCondi.ToString());
return _access.getDataTableForObj<MailUserQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("MailUserDao.GetMailUserStore-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 用戶管理列表頁
/// </summary>
/// <returns></returns>
public HttpResponseBase MailUserList()
{
string jsonStr = string.Empty;
try
{
List<MailUserQuery> MailUserStore = new List<MailUserQuery>();
MailUserQuery query = new MailUserQuery();
if (!string.IsNullOrEmpty(Request.Params["user_name"]))
{
query.user_name = Request.Params["user_name"];
}
if (!string.IsNullOrEmpty(Request.Params["user_mail"]))
{
query.user_mail = Request.Params["user_mail"];
}
if (!string.IsNullOrEmpty(Request.Params["relation_id"]))//待回覆
{
query.row_id = Convert.ToInt32(Request.Params["relation_id"]);
}
List<ManageUserQuery> ManageUseStore = new List<ManageUserQuery>();
ManageUserQuery ManageUserQuery = new ManageUserQuery();
ManageUserQuery.IsPage = false;
#region 查詢管理人員綁定到表中
DataTable _dtManageUser = new DataTable();
_dtManageUser.Columns.Add("user_id", typeof(String));
_dtManageUser.Columns.Add("user_name", typeof(String));
int total = 0;
_IManageUserMgr = new ManageUserMgr(mySqlConnectionString);
ManageUseStore = _IManageUserMgr.GetNameMail(ManageUserQuery, out total);
foreach (var item in ManageUseStore)
{
DataRow dr = _dtManageUser.NewRow();
dr[0] = item.user_id;
dr[1] = item.user_name;
_dtManageUser.Rows.Add(dr);
}
#endregion
if (!string.IsNullOrEmpty(Request.Params["pagers"]))
{
if (Convert.ToInt32(Request.Params["pagers"]) == 0)
{
query.IsPage = false;
}
}
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
_IMailUserMgr = new MailUserMgr(mySqlConnectionString);
int totalCount = 0;
MailUserStore = _IMailUserMgr.GetMailUserStore(query, out totalCount);//查询出供應商出貨單
foreach (var item in MailUserStore)
{
item.nameemail = item.user_name + "(" + item.user_mail + ")";
DataRow[] rows = _dtManageUser.Select("user_id='" + item.create_user + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，
{
item.create_user_name = item.create_user.ToString();
if (!string.IsNullOrEmpty(row["user_id"].ToString()))
{
item.create_user_name = row["user_name"].ToString();//---創建人
}
}
rows = _dtManageUser.Select("user_id='" + item.update_user + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，
{
item.update_user_name = item.update_user.ToString();
if (!string.IsNullOrEmpty(row["user_id"].ToString()))
{
item.update_user_name = row["user_name"].ToString();//---修改人
}
}
if (Convert.ToBoolean(Request.Params["isSecret"]))
{
if (!string.IsNullOrEmpty(item.user_name))
{
item.user_name = item.user_name.Substring(0, 1) + "**";
}
item.user_mail = item.user_mail.Split('@')[0] + "@***";
if (item.nameemail.ToString().Length > 3)
{
item.nameemail = item.nameemail.Substring(0, 3) + "***";
}
else
{
item.nameemail = item.nameemail + "***";
}
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(MailUserStore, 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);
jsonStr = "{success:false,msg:0}";
}
this.Response.Clear();
this.Response.Write(jsonStr.ToString());
this.Response.End();
return this.Response;
}
/// <summary>
/// 修改狀態
/// </summary>
/// <returns></returns>
public JsonResult UpdateActive()
{
string jsonStr = string.Empty;
try
{
MailUserQuery query = new MailUserQuery();
if (!string.IsNullOrEmpty(Request.Params["active"]))
{
query.status = Convert.ToInt32(Request.Params["active"]);
}
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
query.row_id = Convert.ToInt32(Request.Params["id"]);
}
query.update_user = (Session["caller"] as Caller).user_id;
query.update_time = DateTime.Now;
_IMailUserMgr = new MailUserMgr(mySqlConnectionString);
if (_IMailUserMgr.UpdateMailUserStatus(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 DeleteMailUser()
{
MailUserQuery query = new MailUserQuery();
string json = string.Empty;
try
{
if (!string.IsNullOrEmpty(Request.Params["rowId"]))
{
string Row_id = Request.Params["rowId"];
Row_id = Row_id.TrimEnd(',');
query.row_id_in = Row_id;
}
_IMailUserMgr = new MailUserMgr(mySqlConnectionString);
int result = _IMailUserMgr.DeleteMailUser(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 SaveMailUser()
{
MailUserQuery query = new MailUserQuery();
string json = string.Empty;
try
{
if (!string.IsNullOrEmpty(Request.Params["row_id"]))
{
query.row_id = Convert.ToInt32(Request.Params["row_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["user_name"]))
{
query.user_name = Request.Params["user_name"];
}
if (!string.IsNullOrEmpty(Request.Params["user_mail"]))
{
query.user_mail = Request.Params["user_mail"];
}
if (!string.IsNullOrEmpty(Request.Params["user_pwd"]))
{
query.user_pwd = Request.Params["user_pwd"];
}
query.create_time = DateTime.Now;
query.update_time = query.create_time;
query.create_user = (Session["caller"] as Caller).user_id;
query.update_user = (Session["caller"] as Caller).user_id;
query.status = 1;
_IMailUserMgr = new MailUserMgr(mySqlConnectionString);
int result = _IMailUserMgr.SaveMailUser(query);
if (result > 0)
{
json = "{success:true,msg:\"" + result + "\"}";
}
else
{
json = "{success:true,msg:\"" + result + "\"}";
}
//else if (result == -1)//羣組或編碼重複
//{
// json = "{failure:true,msg:'-1'}";
//}
//else
//{
// json = "{failure:true,msg:'0'}";
//}
}
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>
/// <param name="query"></param>
/// <returns></returns>
public int UpdateMailUserStatus(MailUserQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(@"update mail_group_map set status='{0}' where user_id='{1}';", query.status, query.row_id);
sql.AppendFormat(@"update mail_user set `status`='{0}', update_time='{1}' ", query.status, CommonFunction.DateTimeToString(query.update_time));
sql.AppendFormat(" ,update_user='******' where row_id='{1}'", query.update_user, query.row_id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("MailUserDao-->UpdateMailUserStatus-->" + sql.ToString() + ex.Message, ex);
}
}
/// <summary>
/// 刪除用戶信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int DeleteMailUser(MailUserQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (!string.IsNullOrEmpty(query.row_id_in))
{
sql.AppendFormat(@"delete from mail_user where row_id in({0})", query.row_id_in);
}
else
{
sql.AppendFormat(@"delete from mail_user where row_id ='{0}'", query.row_id);
}
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("MailUserDao-->DeleteMailUser-->" + sql.ToString() + ex.Message, ex);
}
}
public bool VerifyUserMail(MailUserQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.row_id == 0)//新增 count=0；
{
sql.AppendFormat("select count(row_id) as num from mail_user where user_mail='{0}' ; ", query.user_mail);
DataTable _dt = _access.getDataTable(sql.ToString());
if (Convert.ToInt32(_dt.Rows[0]["num"].ToString()) == 0)
{
return true;
}
else
{
return false;
}
}
else//編輯
{
sql.AppendFormat("select count(row_id) as num from mail_user where user_mail='{0}' and row_id !={1}; ", query.user_mail, query.row_id);
DataTable _dt = _access.getDataTable(sql.ToString());
if (Convert.ToInt32(_dt.Rows[0]["num"].ToString()) == 0)
{
return true;
}
else
{
return false;
}
}
}
catch (Exception ex)
{
throw new Exception("MapGroupDao-->VerifyGroup-->" + sql.ToString() + ex.Message, ex);
}
}
/// <summary>
/// 新增或修改用戶信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int SaveMailUser(MailUserQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (VerifyUserMail(query))
{
if (query.row_id == 0)
{
sql.Append(@"insert into mail_user(user_mail,user_name,status,user_pwd,create_time,create_user,update_time,update_user)values(");
sql.AppendFormat(" '{0}','{1}','{2}','{3}','{4}',", query.user_mail, query.user_name, query.status, query.user_pwd, CommonFunction.DateTimeToString(query.create_time));
sql.AppendFormat(" '{0}','{1}','{2}')", query.create_user, CommonFunction.DateTimeToString(query.update_time), query.update_user);
}
else
{
sql.AppendFormat(@"update mail_user set user_mail='{0}',user_name='{1}',user_pwd='{2}',", query.user_mail, query.user_name, query.user_pwd);
sql.AppendFormat(" update_time='{0}',update_user='******' where row_id='{2}' ", CommonFunction.DateTimeToString(query.update_time), query.update_user, query.row_id);
}
return _access.execCommand(sql.ToString());
}
else
{
return -1;
}
}
catch (Exception ex)
{
throw new Exception("MailUserDao-->SaveMailUser-->" + sql.ToString() + ex.Message, ex);
}
}