public DataTable GetPersonList(EdmEmailQuery query, out int totalCount) { try { return _edmemailDao.GetPersonList(query, out totalCount); } catch (Exception ex) { throw new Exception("EdmTestMgr-->GetPersonList-->", ex); } }
public DataTable GetEdmPersonList(EdmEmailQuery query, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder count = new StringBuilder(); totalCount = 0; DataTable dt = new DataTable(); try { sql.AppendFormat(@"SELECT DISTINCT ee.email_id,ee.email_address,ee.email_name ,ege.email_status,count(ee.email_id) as group_count FROM edm_email ee INNER JOIN edm_group_email ege ON ee.email_id=ege.email_id WHERE 1=1 "); count = sql; if (query.email_name != string.Empty) { sql.AppendFormat(" AND ee.email_name LIKE N'%{0}%'", query.email_name); count.AppendFormat(" AND ee.email_name LIKE N'%{0}%'", query.email_name); } if (query.email_address != string.Empty) { sql.AppendFormat(" AND ee.email_address LIKE N'%{0}%'", query.email_address); count.AppendFormat(" AND ee.email_address LIKE N'%{0}%'", query.email_address); } if (query.email_id != 0) { sql.AppendFormat(" AND ee.email_id ={0}", query.email_id); count.AppendFormat(" AND ee.email_id={0}", query.email_id); } sql.AppendFormat(" GROUP BY(ee.email_id)"); if (query.IsPage) { dt = _access.getDataTable(count.ToString()); if (dt != null && dt.Rows.Count > 0) { totalCount = dt.Rows.Count; } sql.AppendFormat(" limit {0},{1}", query.Start, query.Limit); } return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("EdmEmailDao-->GetEdmPersonList-->" + sql.ToString() + ex.Message, ex); } }
public string insertEdmEmail(EdmEmailQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("INSERT INTO edm_email (email_id,email_name,email_address,email_check,email_createdate,email_updatedate) VALUES('{0}','{1}','{2}','{3}','{4}','{5}')", query.email_id, query.email_name, query.email_address, query.email_check, query.email_createdate, query.email_updatedate); return sql.ToString(); } catch (Exception ex) { throw new Exception("EdmGroupEmailDao-->insertEdmEmail" + ex.Message + sql.ToString(), ex); } }
public string Import(DataTable _dt, EdmGroupEmailQuery groupMailQuery) { string json = "{success:'true'}"; ArrayList arrList = new ArrayList(); Serial serial = new Serial(); EdmEmailQuery emailQuery = new EdmEmailQuery(); EdmGroupQuery query = new EdmGroupQuery(); List<EdmEmail> emailStore = new List<EdmEmail>(); List<EdmGroupEmailQuery> groupMailStore = new List<EdmGroupEmailQuery>(); string regex = @"^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$"; try { query.group_id = groupMailQuery.group_id; for (int i = 0; i < _dt.Rows.Count; i++) { if (_dt.Rows[i][0] != "") { if( Regex.IsMatch(_dt.Rows[i][0].ToString(), regex)) { emailQuery.email_address = _dt.Rows[i][0].ToString(); //若不為 0、1、未指定或有錯誤時,預設皆為訂閱。 #region 有2列 int n = 1; if (_dt.Columns.Count >= 2) { if (_dt.Rows[i][1] != "") { #region MyRegion if (int.TryParse(_dt.Rows[i][1].ToString(), out n)) { groupMailQuery.email_status = Convert.ToUInt32(_dt.Rows[i][1].ToString()); if (groupMailQuery.email_status != 1 && groupMailQuery.email_status != 0) { groupMailQuery.email_status = 1; } } else { groupMailQuery.email_status = 1; } #endregion } else { groupMailQuery.email_status = 1; } } else { groupMailQuery.email_status = 1; } #endregion #region 有3列 if (_dt.Columns.Count == 3) { if (_dt.Rows[i][2] == "")//或無指定,預設以電子信箱帳號代替。 { emailQuery.email_name = _dt.Rows[i][0].ToString().Substring(0, _dt.Rows[i][0].ToString().LastIndexOf("@")); } else { emailQuery.email_name = _dt.Rows[i][2].ToString(); } } else { emailQuery.email_name = _dt.Rows[i][0].ToString().Substring(0, _dt.Rows[i][0].ToString().LastIndexOf("@")); } #endregion groupMailQuery.email_name = emailQuery.email_name; //查看edm_mail中郵箱是否存在,如果重複則更新 emailStore = _IEdmGroupEmailMgr.getList(emailQuery); #region 存在,更新 if (emailStore.Count > 0)//存在,更新 { emailQuery.email_id = emailStore[0].email_id; emailQuery.email_updatedate = Convert.ToInt32(CommonFunction.GetPHPTime()); arrList.Add(_IEdmGroupEmailMgr.UpdateEdmEmailStr(emailQuery));//更新edm_mail表 groupMailQuery.email_id = emailQuery.email_id; // 查看 edm_group_email表中數據是否存在 if (_IEdmGroupEmailMgr.Check(groupMailQuery).Count > 0)//存在 { groupMailQuery.email_updatedate = Convert.ToUInt32(CommonFunction.GetPHPTime()); arrList.Add(_edmGroup.UpdateEGE(groupMailQuery)); } else { groupMailQuery.email_updatedate = Convert.ToUInt32(CommonFunction.GetPHPTime()); groupMailQuery.email_createdate = Convert.ToUInt32(CommonFunction.GetPHPTime()); arrList.Add(_edmGroup.insertEGEInfo(groupMailQuery)); } } #endregion #region 不存在新增 else//不存在新增 { //1 新增edm_email表 //2 新增edm_group_email表 string sql = _ISerialImpl.Update(51);//51代表edm_email表 serial = _IEdmGroupEmailMgr.execSql(sql); emailQuery.email_id = Convert.ToUInt32(serial.Serial_Value); emailQuery.email_createdate = Convert.ToInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString())); emailQuery.email_updatedate = Convert.ToInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString())); emailQuery.email_check = 0; arrList.Add(_edmGroup.insertEdmEmail(emailQuery)); groupMailQuery.email_createdate = Convert.ToUInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString())); groupMailQuery.email_updatedate = Convert.ToUInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString())); groupMailQuery.email_id = emailQuery.email_id; arrList.Add(_edmGroup.insertEGEInfo(groupMailQuery)); } #endregion } } } if (arrList.Count > 0) { if (_mysql.ExcuteSqlsThrowException(arrList)) { json = "{success:'true'}"; } else { json = "{success:'false'}"; } } DataTable _dtCount = _IEdmGroupEmailMgr.getCount(Convert.ToInt32(query.group_id)); if (_dtCount.Rows.Count > 0) { query.group_total_email = Convert.ToUInt32(_dtCount.Rows[0][0]); _IEdmGroupEmailMgr.updateEdmGroupCount(query); } return json; } catch (Exception ex) { throw new Exception("EdmGroupMgr-->Import-->" + ex.Message, ex); } }
public string UpdateEdmEmailStr(EdmEmailQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("UPDATE edm_email SET email_name='{0}',email_updatedate='{1}' WHERE email_id='{2}'", query.email_name, query.email_updatedate, query.email_id); return sql.ToString(); } catch (Exception ex) { throw new Exception("EdmGroupEmailDao-->UpdateEdmEmailStr" + ex.Message + sql.ToString(), ex); } }
public HttpResponseBase GetPersonList() { string json = string.Empty; EdmEmailQuery query = new EdmEmailQuery(); DataTable store = new DataTable(); int totalCount = 0; try { if (!string.IsNullOrEmpty(Request.Params["gname"])) { query.group_name = Request.Params["gname"].ToString().Replace("\\", "\\\\"); } if (!string.IsNullOrEmpty(Request.Params["gid"])) { query.group_id = Convert.ToInt16(Request.Params["gid"]); } if (!string.IsNullOrEmpty(Request.Params["email_id"])) { query.email_id = Convert.ToUInt32(Request.Params["email_id"]); } query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); _edmEmailMgr = new EdmEmailMgr(mySqlConnectionString); store = _edmEmailMgr.GetPersonList(query, out totalCount); for (int i = 0; i < store.Rows.Count; i++) { DataRow dr = store.Rows[i]; if (!string.IsNullOrEmpty(dr["email_name"].ToString())) { dr["email_name"] = dr["email_name"].ToString().Substring(0, 1) + "**"; } dr["email_address"] = dr["email_address"].ToString().Split('@')[0] + "@***"; } json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store) + "}"; } 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,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }