public string insertEGEInfo(EdmGroupEmailQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("INSERT INTO edm_group_email (group_id,email_id,email_name,email_status,email_createdate,email_updatedate) VALUES('{0}','{1}','{2}','{3}','{4}','{5}')", query.group_id, query.email_id, query.email_name, query.email_status, query.email_createdate, query.email_updatedate);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("EdmGroupDao-->insertEGEInfo" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 新增編輯處理
/// </summary>
/// <param name="EGEquery"></param>
/// <param name="EEquery"></param>
/// <returns></returns>
public string EdmGroupEmailEdit(Model.Query.EdmGroupEmailQuery query)
{
query.Replace4MySQL();
string json = string.Empty;
EdmEmail oldQuery = new EdmEmail();
EdmEmail emailQuery = new EdmEmail();
EdmGroupEmailQuery groupemailQuery = new EdmGroupEmailQuery();
List<EdmEmail> store = new List<EdmEmail>();
List<EdmGroupEmailQuery> listEGE=new List<EdmGroupEmailQuery>();
Serial serial = new Serial();
uint email_id=query.email_id;
try
{
emailQuery.email_name = query.email_name;
if (string.IsNullOrEmpty(query.email_name))
{//如果郵件名稱沒有填,則默認郵件地址@符號以前的內容
string[] strs = query.email_address.Split('@');
query.email_name = strs[0].ToString();
emailQuery.email_name = query.email_name;
}
oldQuery.email_address = query.email_address;
emailQuery.email_address = query.email_address;
#region 判斷郵件地址在edm_email表中是否存在
store = _IEdmGroupEmailMgr.getList(oldQuery);//判斷該郵件是否存在
if (store != null && store.Count > 0)//存在,獲取email_id
{
emailQuery.email_id = store[0].email_id;
emailQuery.email_updatedate = Convert.ToInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString()));
if (query.email_name != store[0].email_name)//檔修改的郵件名稱與已存在的不一致就更新成一致
{
if (_IEdmGroupEmailMgr.UpdateEdmEmail(emailQuery) <= 0)
{
json = "{success:true,msg:1,type:0}";//msg=1代表更新郵件時出錯,type=0代表更新郵件名稱時錯誤
return json;
}
groupemailQuery.email_name = query.email_name;
groupemailQuery.email_updatedate = Convert.ToUInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString()));
groupemailQuery.email_id = emailQuery.email_id;
DataTable ids = _IEdmGroupEmailMgr.GetGroupID(emailQuery.email_address);
for (int i = 0; i < ids.Rows.Count; i++)
{
groupemailQuery.group_id = Convert.ToUInt32(ids.Rows[i][0]);
int result = _IEdmGroupEmailMgr.UpdateEGEname(groupemailQuery);
if (result > 0)
{
continue;
}
else
{
json = "{success:true,msg:1,type:0}";
return json;
}
}
}
}
else
{ //不存在,在edm_email表新增
//uint largestID=0;
//string empty = string.Empty;
//uint id = 0;
// _edmemailDao.GetData(empty, out largestID, out id, out empty);
string sql = _serialDao.Update(51);//51代表edm_email表
serial = _IEdmGroupEmailMgr.execSql(sql);
emailQuery.email_id = Convert.ToUInt32(serial.Serial_Value);//largestID + 1;
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;
_IEdmGroupEmailMgr.insertEdmEmail(emailQuery);
}
#endregion
query.email_id = emailQuery.email_id;
listEGE = _IEdmGroupEmailMgr.Check(query);
#region 編輯
if (email_id > 0 || (listEGE != null && listEGE.Count > 0))//編輯
{
if (listEGE == null || listEGE.Count <= 0)//檔填寫的郵件地址對應的email_id在edm_group_email表中沒有對應的數據時,新增一條數據
{
query.email_createdate = Convert.ToUInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString()));
query.email_updatedate = Convert.ToUInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString()));
_IEdmGroupEmailMgr.insertEGEInfo(query);
}
query.email_updatedate = Convert.ToUInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString()));
if (email_id != 0 && email_id != query.email_id)//檔編輯時修改的郵件地址不是原來的郵件地址時,把原來的edm_group_email表中的數據刪除
{
EdmGroupEmailQuery egequery = new EdmGroupEmailQuery();
egequery.group_id = query.group_id;
egequery.email_ids = email_id.ToString();
_IEdmGroupEmailMgr.DeleteEdmGroupEmail(egequery);
}
int res = _IEdmGroupEmailMgr.UpdateEGE(query);//更新edm_group_email表數據
if (res > 0)
{
json = "{success:true,msg:0,type:1}";//0表示成功,type=1代表編輯
}
else
{
json = "{success:true,msg:3,type:1}";//0表示更新失敗,type=1代表編輯
return json;
}
}
#endregion
#region 新增
else
{ //新增
query.email_createdate = Convert.ToUInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString()));
query.email_updatedate = Convert.ToUInt32(Common.CommonFunction.GetPHPTime(DateTime.Now.ToString()));
int res = _IEdmGroupEmailMgr.insertEGEInfo(query);//新增edm_group_email表數據
if (res > 0)
{
json = "{success:true,msg:0,type:2}";//0表示成功,type=2代表新增操作
}
else
{
json = "{success:true,msg:2,type:2}";//msg=2表示新增時出錯了,type=2代表新增操作
return json;
}
}
#endregion
#region 更新edm_group表的群組人數
int num = UpdateCount(Convert.ToInt32(query.group_id));
if (num <= 0)
{
json = "{success:false}";
}
#endregion
return json;
}
catch (Exception ex)
{
throw new Exception("EdmGroupEmailMgr-->EdmGroupEmailEdit" + 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);
}
}
public string UpdateEGE(EdmGroupEmailQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("UPDATE edm_group_email SET email_name='{0}',email_status='{1}',email_updatedate='{2}' WHERE group_id='{3}' AND email_id='{4}'", query.email_name, query.email_status, query.email_updatedate, query.group_id, query.email_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("EdmGroupDao-->UpdateEGE" + ex.Message + sql.ToString(), ex);
}
}
public int UpdateEGEname(EdmGroupEmailQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat("UPDATE edm_group_email SET email_name='{0}',email_updatedate='{1}' WHERE group_id='{2}' AND email_id='{3}'", query.email_name, query.email_updatedate, query.group_id, query.email_id);
return _accessMySql.execCommand(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmGroupEmailDao-->UpdateEGE" + ex.Message + sb.ToString(), ex);
}
}
public int DeleteEdmGroupEmail(EdmGroupEmailQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat("DELETE FROM edm_group_email WHERE group_id='{0}' AND email_id IN ({1})", query.group_id,query.email_ids);
return _accessMySql.execCommand(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmGroupEmailDao-->DeleteEdmGroupEmail" + ex.Message + sb.ToString(), ex);
}
}
/// <summary>
/// 檢查在EGE表中是否存在
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List<EdmGroupEmailQuery> Check(EdmGroupEmailQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat("SELECT email_id FROM edm_group_email WHERE email_id='{0}' and group_id='{1}'", query.email_id, query.group_id);
return _accessMySql.getDataTableForObj<EdmGroupEmailQuery>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmGroupEmailDao-->Check" + ex.Message + sb.ToString(), ex);
}
}
public HttpResponseBase EdmGroupEmailEdit()
{
string json = string.Empty;
_IEdmGroupEmailMgr = new EdmGroupEmailMgr(mySqlConnectionString);
EdmGroupEmailQuery groupEmailQuery = new EdmGroupEmailQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
groupEmailQuery.group_id = Convert.ToUInt32(Request.Params["group_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["email_id"]))
{
groupEmailQuery.email_id = Convert.ToUInt32(Request.Params["email_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["email_address"]))
{
groupEmailQuery.email_address = Request.Params["email_address"].ToString().Trim();
}
if (!string.IsNullOrEmpty(Request.Params["email_name"]))
{
groupEmailQuery.email_name = Request.Params["email_name"].ToString().Trim();
}
if (!string.IsNullOrEmpty(Request.Params["email_status"]))
{
groupEmailQuery.email_status = Convert.ToUInt32(Request.Params["email_status"]);
}
json = _IEdmGroupEmailMgr.EdmGroupEmailEdit(groupEmailQuery);
}
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;
}
public HttpResponseBase DelEdmGroupEmail()
{
string json = string.Empty;
_IEdmGroupEmailMgr = new EdmGroupEmailMgr(mySqlConnectionString);
EdmGroupEmailQuery query = new EdmGroupEmailQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToUInt32(Request.Params["group_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["email_ids"]))
{
query.email_ids = Request.Params["email_ids"].ToString();
}
query.email_ids = query.email_ids.Substring(0, query.email_ids.Length - 1);
int res = _IEdmGroupEmailMgr.DeleteEdmGroupEmail(query);
if (res > 0)
{
json = "{success:true,msg:1}";
int num = _IEdmGroupEmailMgr.UpdateCount(Convert.ToInt32(query.group_id));
if (num <= 0)
{
json = "{success:true,msg:0}";
}
}
else
{
json = "{success:true,msg:0}";
}
}
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 GetEdmGroupEmailList()
{
string json = string.Empty;
_IEdmGroupEmailMgr = new EdmGroupEmailMgr(mySqlConnectionString);
List<EdmGroupEmailQuery> store = new List<EdmGroupEmailQuery>();
EdmGroupEmailQuery query = new EdmGroupEmailQuery();
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["selectType"]))
{
query.selectType = Request.Params["selectType"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["search_con"]))
{
query.search_con = Request.Params["search_con"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["email_status"]))
{
query.email_status = Convert.ToUInt32(Request.Params["email_status"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToUInt32(Request.Params["group_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["email_id"]))
{
query.email_id = Convert.ToUInt32(Request.Params["email_id"]);
}
int totalCount = 0;
store = _IEdmGroupEmailMgr.GetEdmGroupEmailList(query, out totalCount);
foreach (EdmGroupEmailQuery egeq in store)
{
if (Convert.ToBoolean(Request.Params["isSecret"]))
{
if (!string.IsNullOrEmpty(egeq.email_name))
{
egeq.email_name = egeq.email_name.Substring(0, 1) + "**";
}
if (!string.IsNullOrEmpty(egeq.email_address))
{
egeq.email_address = egeq.email_address.Split('@')[0] + "@***";
}
}
egeq.email_createdate_tostring = CommonFunction.GetNetTime(egeq.email_createdate);
egeq.email_updatedate_tostring = CommonFunction.GetNetTime(egeq.email_updatedate);
}
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;
}
public HttpResponseBase Import()
{
string json = string.Empty;
try
{
EdmGroupEmailQuery query = new EdmGroupEmailQuery();
if (!string.IsNullOrEmpty(Request.Params["ImportCsv"]))
{
query.group_id = Convert.ToUInt32(Request.Params["group_id"]);
HttpPostedFileBase file = Request.Files["ImportCsv"];
FileManagement fileManagement = new FileManagement();
string fileName = fileManagement.NewFileName(file.FileName);
string newFileName = Server.MapPath(excelPath_export) + fileName;
file.SaveAs(newFileName);
DataTable _dt = CsvHelper.ReadCsvToDataTable_CN(newFileName, true);
_edmGroup = new EdmGroupMgr(mySqlConnectionString);
json = _edmGroup.Import(_dt, query);
}
}
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;
}
