/// <summary>
/// 有關mail的查詢（此方法可擴充，擴充后請詳細注釋）
/// 內容：根據群組編號或代碼查詢用戶email地址 add by shuangshuang0420j 2015.02.04
/// 修改：
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<MailGroupQuery> MailGroupQuery(MailGroupQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(@" select mu.user_mail,mu.user_name");
sql.Append(@" from mail_group mg");
sql.Append(@" left join mail_group_map mgm on mgm.group_id=mg.row_id ");
sql.Append(@" left join mail_user mu on mu.row_id=mgm.user_id ");
sql.Append(@" where 1=1");
if (query.row_id != 0)
{
sql.AppendFormat(@" and mg.row_id='{0}'", query.row_id);
}
if (!string.IsNullOrEmpty(query.group_code))
{
sql.AppendFormat(@" and mg.group_code='{0}'", query.group_code);
}
if (query.status == 1)//查詢啟用狀態的合法群組用戶時必須所有的狀態都為啟用
{
sql.Append(" and mg.`status`=1 and mgm.`status`=1 and mu.`status`=1");
}
else if (query.status == 0)//查詢停用的群組時只需群組的狀態為停用即可
{
sql.Append(" and mg.`status`=0");
}
return _access.getDataTableForObj<MailGroupQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("MapGroupDao-->MailGroupQuery-->" + sql.ToString() + ex.Message, ex);
}
}
public List<MailGroupQuery> MailGroupList(MailGroupQuery query,out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlLimit = new StringBuilder();
totalCount = 0;
try
{
sqlCount.Append("select count(mg.row_id) as totalCount from mail_group mg;");
sql.AppendFormat("select mg.row_id,mg.group_name,mg.remark,mg.`status`,mg.group_code,count(mgm.row_id) as callid ");
sqlFrom.AppendFormat(" from mail_group mg LEFT JOIN mail_group_map mgm on mg.row_id=mgm.group_id and mgm.`status`={0} GROUP BY mg.row_id ", query.status);
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(sqlCount.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"].ToString());
}
sqlLimit.AppendFormat("order by mg.row_id desc limit {0},{1}; ", query.Start, query.Limit);
}
return _access.getDataTableForObj<MailGroupQuery>(sql.ToString() + sqlFrom.ToString() + sqlLimit.ToString());
}
catch (Exception ex)
{
throw new Exception("MapGroupDao-->MailGroupList-->" + sql.ToString()+sqlFrom.ToString() + ex.Message, ex);
}
}
public int UpMailGroupStatus(MailGroupQuery query)
{
try
{
return _IMailGroupDao.UpMailGroupStatus(query);
}
catch (Exception ex)
{
throw new Exception("MailGroupMgr-->UpMailGroupStatus-->" + ex.Message, ex);
}
}
public int SaveMailGroup(MailGroupQuery query)
{
try
{
return _IMailGroupDao.SaveMailGroup(query);
}
catch (Exception ex)
{
throw new Exception("MailGroupMgr-->SaveMailGroup-->" + ex.Message, ex);
}
}
public List<MailGroupQuery> MailGroupList(MailGroupQuery query, out int totalCount)
{
try
{
return _IMailGroupDao.MailGroupList(query,out totalCount);
}
catch (Exception ex)
{
throw new Exception("MailGroupMgr-->MailGroupList-->"+ex.Message,ex);
}
}
public JsonResult UpMailGroupStatus()
{
string json = string.Empty;
try
{
MailGroupQuery query = new MailGroupQuery();
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
query.row_id = Convert.ToInt32(Request.Params["id"]);
}
if (!string.IsNullOrEmpty(Request.Params["active"]))
{
query.status = Convert.ToInt32(Request.Params["active"]);
}
query.update_time = DateTime.Now;
query.update_user = (Session["caller"] as Caller).user_id;
_IMailGroupMgr = new MailGroupMgr(mySqlConnectionString);
if (_IMailGroupMgr.UpMailGroupStatus(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 HttpResponseBase DeleteMailGroup()
{
string json = string.Empty;
MailGroupQuery query = null;
_IMailGroupMgr = new MailGroupMgr(mySqlConnectionString);
List<MailGroupQuery> list = new List<MailGroupQuery>();
try
{
if (!string.IsNullOrEmpty(Request.Form["rowID"]))
{
string rowIDs = Request.Form["rowID"];
if (rowIDs.IndexOf("|") != -1)
{
foreach (string id in rowIDs.Split('|'))
{
if (!string.IsNullOrEmpty(id))
{
query = new MailGroupQuery();
query.row_id = Convert.ToInt32(id);
list.Add(query);
}
}
}
if (_IMailGroupMgr.DeleteMailGroup(list))
{
json = "{success:true}";
}
else
{
json = "{failure: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 = "{failure:true}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase SaveMailGroup()
{
MailGroupQuery query = new MailGroupQuery();
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["groupName"]))
{
query.group_name = Request.Params["groupName"];
}
if (!string.IsNullOrEmpty(Request.Params["groupCode"]))
{
query.group_code = Request.Params["groupCode"];
}
if (!string.IsNullOrEmpty(Request.Params["remark"]))
{
query.remark = Request.Params["remark"];
}
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;
_IMailGroupMgr = new MailGroupMgr(mySqlConnectionString);
int result = _IMailGroupMgr.SaveMailGroup(query);
if (result > 0)
{
json = "{success:true}";
}
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 = "{failure:true,msg:'0'}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase MailGroupList()
{
string json = string.Empty;
MailGroupQuery query = new MailGroupQuery();
if (!string.IsNullOrEmpty(Request["isPage"]))
query.IsPage = bool.Parse(Request["isPage"]);
List<MailGroupQuery> store = new List<MailGroupQuery>();
_IMailGroupMgr = new MailGroupMgr(mySqlConnectionString);
int totalCount = 0;
try
{
query.status = 1;
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
store = _IMailGroupMgr.MailGroupList(query, out totalCount);
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented) + "}";
}
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 = "{failure:true}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 新增編輯羣組
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int SaveMailGroup(MailGroupQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.row_id == 0)
{
if (VerifyGroup(query))
{
sql.Append("insert into mail_group(`group_name`,`remark`,`status`,");
sql.Append("`create_time`,`create_user`,`update_time`,");
sql.Append("`update_user`,`group_code`) ");
sql.AppendFormat("values('{0}','{1}',{2}, ", query.group_name, query.remark, query.status);
sql.AppendFormat("'{0}',{1},'{2}',", CommonFunction.DateTimeToString(query.create_time), query.create_user, CommonFunction.DateTimeToString(query.update_time));
sql.AppendFormat("{0},'{1}');", query.update_user, query.group_code);
return _access.execCommand(sql.ToString());
}
else
{
return -1;//羣組名稱或編碼重複
}
}
else
{
if (VerifyGroup(query))
{
sql.AppendFormat("update mail_group set group_name='{0}',group_code='{1}',remark='{2}',", query.group_name, query.group_code, query.remark);
sql.AppendFormat("update_time='{0}',update_user={1} 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("MapGroupDao-->SaveMailGroup-->" + sql.ToString() + ex.Message, ex);
}
}
public int UpMailGroupStatus(MailGroupQuery query)
{
StringBuilder sqlGroup = new StringBuilder();
StringBuilder sqlMap = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
int re = 0;
try
{
sqlGroup.AppendFormat("update mail_group set status={0},update_time='{1}',update_user={2} where row_id={3};", query.status, CommonFunction.DateTimeToString(query.update_time), query.update_user, query.row_id);
//sqlMap.AppendFormat("set sql_safe_updates=0; update mail_group_map set status={0} where group_id={1};set sql_safe_updates=1; ", query.status, query.row_id);
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = sqlGroup.ToString();
// mySqlCmd.CommandText += sqlMap.ToString();
re = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
}
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("MapGroupDao-->UpMailGroupStatus-->" + mySqlCmd.ToString() + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return re;
}
/// <summary>
/// 刪除羣組
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string DeleteMailGroup(MailGroupQuery query)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("delete from mail_group where row_id={0};", query.row_id);
return sql.ToString();
}
public bool VerifyGroup(MailGroupQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
if (query.row_id == 0)//新增 count=0；
{
sql.AppendFormat("select count(row_id) as num from mail_group where group_name='{0}' or group_code='{1}'; ", query.group_name, query.group_code);
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_group where (group_name='{0}' or group_code='{1}') and row_id !={2}; ", query.group_name, query.group_code, 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);
}
}
