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