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