public EdmGroupQuery GetEdmGroupById()
{
string strSql = string.Format("select group_id,group_name,group_total_email,group_createdate,group_updatedate from edm_group where group_id=1");
EdmGroupQuery endGroupInfo = new EdmGroupQuery();
try
{
endGroupInfo = _accessMySql.getSinggleObj<EdmGroupQuery>(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("UserEdmDao.GetEdmGroupById-->" + ex.Message + "sql--" + strSql, ex);
}
return endGroupInfo;
}
public void AddEdmGroup()
{
string strSql = String.Empty;
try
{
EdmGroupQuery edmGroup = new EdmGroupQuery();
edmGroup.group_id = 1;
edmGroup.group_name = "All Member";
edmGroup.group_total_email = 0;
edmGroup.group_createdate = (uint)Convert.ToInt32(DateTime.Now.ToString());
edmGroup.group_updatedate = (uint)Convert.ToInt32(DateTime.Now.ToString());
edmGroup.Replace4MySQL();
strSql = string.Format("insert into edm_group (group_id,group_name,group_total_email,group_createdate,group_updatedate) values({0},'{1}',{2},{3},{4})", edmGroup.group_id, edmGroup.group_name, edmGroup.group_total_email, edmGroup.group_createdate, edmGroup.group_updatedate);
_accessMySql.execCommand(strSql);
}
catch (Exception ex)
{
throw new Exception("UserEdmDao.AddEdmGroup-->" + ex.Message + "sql--" + strSql, ex);
}
}
/// <summary>
/// 更新edm_group表的群組人數
/// </summary>
/// <param name="group_id"></param>
/// <returns></returns>
public int UpdateCount(int group_id)
{
int num = 0;
try
{
//獲取群組訂閱總人數
DataTable dt = new DataTable();
EdmGroupQuery egquery = new EdmGroupQuery();
dt = _IEdmGroupEmailMgr.getCount(group_id);
if (dt != null && dt.Rows.Count > 0)
{
egquery.group_total_email = Convert.ToUInt32(dt.Rows[0][0]);
egquery.group_id = Convert.ToUInt32(group_id);
num = _IEdmGroupEmailMgr.updateEdmGroupCount(egquery);
return num;
}
else
{
return num;
}
}
catch (Exception ex)
{
throw new Exception("EdmGroupEmailMgr-->UpdateCount" + ex.Message, ex);
}
}
public int updateGroupCount(string email)
{
DataTable gids = new DataTable();
EdmGroupQuery query = new EdmGroupQuery();
int num = 0;
try
{
gids = _edmGroupEmailDao.GetGroupID(email);
if (gids != null && gids.Rows.Count > 0)
{
for (int i = 0; i < gids.Rows.Count; i++)
{
int group_id = Convert.ToInt32(gids.Rows[i][0]);
DataTable count = new DataTable();
count = _edmGroupEmailDao.getCount(group_id);
if (count != null && count.Rows.Count > 0)
{
query.group_total_email = Convert.ToUInt32(count.Rows[0][0]);
query.group_id = Convert.ToUInt32(group_id);
num = _edmGroupEmailDao.updateEdmGroupCount(query);
if (num > 0)
{
continue;
}
else
{
return num;
}
}
}
}
return num;
}
catch (Exception ex)
{
throw new Exception("EdmContentMgr-->updateGroupCount-->" + ex.Message, 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);
}
}
/// <summary>
/// 更新群組的訂閱總人數
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int updateEdmGroupCount(EdmGroupQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat("UPDATE edm_group SET group_total_email='{0}' WHERE group_id='{1}'", query.group_total_email,query.group_id);
return _accessMySql.execCommand(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmGroupEmailDao-->updateEdmGroupCount" + ex.Message + sb.ToString(), ex);
}
}
public HttpResponseBase Load()
{
string json = string.Empty;
_IEdmGroupEmailMgr = new EdmGroupEmailMgr(mySqlConnectionString);
EdmGroupQuery store = new EdmGroupQuery();
EdmGroupQuery query = new EdmGroupQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToUInt32(Request.Params["group_id"]);
}
store = _IEdmGroupEmailMgr.Load(query);
if (store != null)
{
json = "{success:true,group_id:" + store.group_id + ",group_name:'" + store.group_name + "',group_count:'" + store.group_total_email + "'}";
}
else
{
json = "{success:false,msg:1}";
}
}
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,msg:0}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
