public List<EmailGroup> EmailGroupList(EmailGroup query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
query.Replace4MySQL();
try
{
sqlCount.Append("select count(eg.group_id) 'totalCount' from email_group eg");
sql.Append("select eg.group_id,eg.group_name,count(el.group_id) 'count',eg.group_updatedate,eg.group_update_userid,mu.user_username ");
sqlFrom.Append(" from email_group eg LEFT JOIN email_list el on eg.group_id=el.group_id ");
sqlFrom.Append(" left join manage_user mu on eg.group_update_userid=mu.user_id ");
sqlWhere.Append(" where 1=1 ");
if (!string.IsNullOrEmpty(query.group_name))
{
sqlWhere.AppendFormat(" and eg.group_name like '%{0}%' ", query.group_name);
}
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0][0]);
}
sqlWhere.Append(" group by eg.group_id desc ");
sqlWhere.AppendFormat("limit {0},{1};", query.Start, query.Limit);
return _access.getDataTableForObj<EmailGroup>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("EmailGroupDao-->EmailGroupList-->" + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString() + ex.Message, ex);
}
}
public List<EmailGroup> EmailGroupList(EmailGroup query, out int totalCount)
{
try
{
return _emailGroupDao.EmailGroupList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("EmailGroupMgr-->EmailGroupList-->" + ex.Message, ex);
}
}
/// <summary>
/// email_list匯入
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public bool ImportEmailList(EmailGroup query)
{
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.AppendFormat("insert into email_list (group_id,email_address,name) values('{0}','{1}','{2}');", query.group_id, query.email_address, query.name);
if (_access.execCommand(sql.ToString()) > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw new Exception("EmailGroupDao-->ImportEmailList-->" + sql.ToString() + ex.Message, ex);
}
}
public DataTable ImportEmailList(DataTable _dt, int group_id, out int totalCount)
{
DataTable _newDt = new DataTable();
totalCount = 0;
if (_dt != null && _dt.Rows.Count > 0)
{
EmailGroup query = new EmailGroup();
ArrayList arrList = new ArrayList();
query.group_id = group_id;
_newDt.Columns.Add("電子信箱地址", typeof(string));
_newDt.Columns.Add("收件人名稱", typeof(string));
_newDt.Columns.Add("匯入失敗原因", typeof(string));
string regex = @"^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$";
try
{
for (int i = 0; i < _dt.Rows.Count; i++)
{
if (_dt.Rows[i][0] != "")
{
totalCount++;
if (Regex.IsMatch(_dt.Rows[i][0].ToString(), regex))
{
if (!_emailGroupDao.IsExistEmail(_dt.Rows[i][0].ToString(), group_id))
{
query.email_address = _dt.Rows[i][0].ToString();
query.name = _dt.Rows[i][1].ToString();
try
{
if (!_emailGroupDao.ImportEmailList(query))
{
DataRow _dr = _newDt.NewRow();
_dr["電子信箱地址"] = _dt.Rows[i][0].ToString();
_dr["收件人名稱"] = _dt.Rows[i][1].ToString();
_dr["匯入失敗原因"] = "執行數據庫出錯";
_newDt.Rows.Add(_dr);
}
}
catch (Exception)
{
DataRow _dr = _newDt.NewRow();
_dr["電子信箱地址"] = _dt.Rows[i][0].ToString();
_dr["收件人名稱"] = _dt.Rows[i][1].ToString();
_dr["匯入失敗原因"] = "執行數據庫出錯";
_newDt.Rows.Add(_dr);
continue;
}
}
else
{
//數據重複
DataRow _dr = _newDt.NewRow();
_dr["電子信箱地址"] = _dt.Rows[i][0].ToString();
_dr["收件人名稱"] = _dt.Rows[i][1].ToString();
_dr["匯入失敗原因"] = "數據重複";
_newDt.Rows.Add(_dr);
}
}
else
{
//不符合郵件格式
DataRow _dr = _newDt.NewRow();
_dr["電子信箱地址"] = _dt.Rows[i][0].ToString();
_dr["收件人名稱"] = _dt.Rows[i][1].ToString();
_dr["匯入失敗原因"] = "不符合郵件格式";
_newDt.Rows.Add(_dr);
}
}
}
return _newDt;
}
catch (Exception ex)
{
throw new Exception("EmailGroupMgr-->ImportEmailList-->" + ex.Message, ex);
}
}
return _newDt;
}
public bool SaveEmailGroup(EmailGroup query)
{
ArrayList arrList = new ArrayList();
try
{
if (query.group_id == 0)
{
arrList.Add(_emailGroupDao.InsertEmailGroup(query));
}
else
{
arrList.Add(_emailGroupDao.UpdateEmailGroup(query));
}
return _mySqlDao.ExcuteSqlsThrowException(arrList);
}
catch (Exception ex)
{
throw new Exception("EmailGroupDao-->SaveEmailGroup-->" + ex.Message, ex);
}
}
public HttpResponseBase DelEmailGroupList()
{
string json = string.Empty;
EmailGroup query = new EmailGroup();
List<EmailGroup> list = new List<EmailGroup>();
try
{
_emailGroupMgr = new EmailGroupMgr(mySqlConnectionString);
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 EmailGroup();
query.group_id =Convert.ToInt32(id);
list.Add(query);
}
}
}
}
json = _emailGroupMgr.DelEmailGroupList(list);
}
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}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase EmailGroupStore()
{
string json = string.Empty;
try
{
EmailGroup query = new BLL.gigade.Model.EmailGroup();
query.group_id = 0;
query.group_name = "無";
List<EmailGroup> store = new List<EmailGroup>();
_emailGroupMgr = new EmailGroupMgr(mySqlConnectionString);
store = _emailGroupMgr.EmailGroupStore();
store.Insert(0, query);
json = "{success:true,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 = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase SaveEmailGroup()
{
string json = string.Empty;
EmailGroup query = new EmailGroup();
try
{
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToInt32(Request.Params["group_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_name"]))
{
query.group_name = Request.Params["group_name"];
}
query.group_create_userid = (Session["caller"] as Caller).user_id;
query.group_update_userid = (Session["caller"] as Caller).user_id;
_emailGroupMgr = new EmailGroupMgr(mySqlConnectionString);
if (_emailGroupMgr.SaveEmailGroup(query))
{
json = "{success:true}";
}
else
{
json = "{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);
json = "{success:false}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase EmailGroupList()
{
string json = string.Empty;
try
{
EmailGroup query = new EmailGroup();
List<EmailGroup> store = new List<EmailGroup>();
if (!string.IsNullOrEmpty(Request.Params["group_name"]))
{
query.group_name = Request.Params["group_name"];
}
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
int totalCount = 0;
_emailGroupMgr = new EmailGroupMgr(mySqlConnectionString);
store = _emailGroupMgr.EmailGroupList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";
}
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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// email_group編輯
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string UpdateEmailGroup(EmailGroup query)
{
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.AppendFormat("update email_group set group_name='{0}',group_updatedate=now(),group_update_userid='{1}' where group_id='{2}';", query.group_name, query.group_update_userid, query.group_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("EmailGroupDao-->UpdateEmailGroup-->" + sql.ToString() + ex.Message, ex);
}
}
/// <summary>
/// email_group新增
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string InsertEmailGroup(EmailGroup query)
{
StringBuilder sql = new StringBuilder();
query.Replace4MySQL();
try
{
sql.AppendFormat(" insert into email_group (group_name,group_createdate,group_updatedate,group_create_userid,group_update_userid) values('{0}',now(),now(),'{1}','{2}');", query.group_name, query.group_create_userid, query.group_update_userid);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("EmailGroupDao-->InsertEmailGroup-->" + sql.ToString() + ex.Message, ex);
}
}