/// <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); } }