public List<EdmGroup> GetEdmGroupList(EdmGroup query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCount = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
query.Replace4MySQL();
sqlCount.Append("select count(eg.group_id) as totalCount ");
sql.Append(" select eg.group_id,eg.group_name,eg.group_total_email,eg.group_createdate,eg.group_updatedate,count(ec.content_id) as total_content ");
sqlFrom.Append("from edm_group eg ");
sqlFrom.Append(" left join edm_content ec on eg.group_id = ec.group_id ");
sqlWhere.Append(" where 1=1 ");
if (query.selectType == "0"&&!string.IsNullOrEmpty(query.search_con))
{
sqlWhere.AppendFormat(" and eg.group_id like '%{0}%'",query.search_con.ToString());
}
if (query.selectType == "1" && !string.IsNullOrEmpty(query.search_con))
{
sqlWhere.AppendFormat(" and eg.group_name like '%{0}%'", query.search_con.ToString());
}
if (query.dateCondition != -1)
{
if (query.dateCondition == 0)
{
sqlWhere.AppendFormat("AND group_createdate BETWEEN {0} AND {1}", query.start, query.end);
}
if (query.dateCondition == 1)
{
sqlWhere.AppendFormat("AND group_updatedate BETWEEN {0} AND {1}", query.start, query.end);
}
}
sqlWhere.AppendFormat(" group by eg.group_id, eg.group_name, eg.group_total_email, eg.group_createdate, eg.group_updatedate order by eg.group_id asc ");
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(sqlCount.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = _dt.Rows.Count;
}
sqlWhere.AppendFormat(" limit {0},{1};", query.Start, query.Limit);
}
return _access.getDataTableForObj<EdmGroup>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmGroupDao-->GetEdmGroupList-->" + ex.Message + sql.ToString() + sqlWhere.ToString(), ex);
}
}
public int UpEdmGroup(EdmGroup query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat("set sql_safe_updates=0;update edm_group set group_name ='{0}',group_updatedate='{1}' where group_id='{2}';set sql_safe_updates=1;", query.group_name, CommonFunction.GetPHPTime(), query.group_id);
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmGroupDao-->UpEdmGroup-->" + ex.Message + sql.ToString(), ex);
}
}
public List<EdmGroup> GetEdmGroupList(EdmGroup query, out int totalCount)
{
try
{
List<EdmGroup> store = new List<EdmGroup>();
store=_edmGroup.GetEdmGroupList(query, out totalCount);
foreach (var item in store)
{
item.s_group_createdate = CommonFunction.GetNetTime(item.group_createdate).ToString("yyyy-MM-dd HH:mm:ss");
item.s_group_updatedate = CommonFunction.GetNetTime(item.group_updatedate).ToString("yyyy-MM-dd HH:mm:ss");
}
return store;
}
catch (Exception ex)
{
throw new Exception("EdmGroupMgr-->GetEdmGroupList-->" + ex.Message, ex);
}
}
//define('SERIAL_ID_EDM_GROUP', 50); // 電子報流水號
public string SaveEdmGroup(EdmGroup query)
{
string json = string.Empty;
Serial serial = new Serial();
ArrayList arrList = new ArrayList();
try
{
if (query.group_id == 0)//新增
{
serial.Serial_id=50;
serial=_ISerialImpl.GetSerialById(serial.Serial_id);//根據ID得到serial_value;
query.group_id=Convert.ToUInt32(serial.Serial_Value) + 1;//將serial_value值加1就是group_id;
arrList.Add(_edmGroup.InsertEdmGroup(query));//insert edm_group
arrList.Add(_ISerialImpl.Update(serial.Serial_id));//update serial
if (_edmGroup.execSql(arrList))
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
else//編輯
{
if (_edmGroup.UpEdmGroup(query) > 0)
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
return json;
}
catch (Exception ex)
{
throw new Exception("EdmGroupMgr-->UpEdmGroup-->" + ex.Message, ex);
}
}
public string InsertEdmGroup(EdmGroup query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("insert into edm_group (group_id,group_name,group_createdate,group_updatedate)values('{0}','{1}','{2}','{2}');", query.group_id, query.group_name, CommonFunction.GetPHPTime());
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("EdmGroupDao-->InsertEdmGroup-->" + ex.Message + sql.ToString(), ex);
}
}
public DataTable Export(EdmGroup query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select ee.email_address,ege.email_status,ege.email_name FROM edm_email ee, edm_group_email ege where ege.group_id ='{0}' and ege.email_id = ee.email_id order by ee.email_id asc;", query.group_id);
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmGroupDao-->DeleteEdmGroup-->" + ex.Message + sql.ToString(), ex);
}
}
public string DeleteEdmGroupMail(EdmGroup query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("set sql_safe_updates=0;delete from edm_group_email where group_id='{0}';set sql_safe_updates=1;", query.group_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("EdmGroupDao-->DeleteEdmGroup-->" + ex.Message + sql.ToString(), ex);
}
}
public DataTable Export(EdmGroup query)
{
try
{
return _edmGroup.Export(query);
}
catch (Exception ex)
{
throw new Exception("EdmGroupMgr-->Export-->" + ex.Message, ex);
}
}
public HttpResponseBase Export()
{
string json = string.Empty;
try
{
EdmGroup query = new EdmGroup();
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToUInt32(Request.Params["group_id"]);
}
_edmGroup = new EdmGroupMgr(mySqlConnectionString);
DataTable _dt = _edmGroup.Export(query);
string fileName = DateTime.Now.ToString("yyyyMMdd_HHmmss") + "." + "csv";
DataTable _newdt = new DataTable();
DataRow dr;
_newdt.Columns.Add("電子信箱", typeof(string));
_newdt.Columns.Add("訂閱狀態", typeof(string));
_newdt.Columns.Add("姓名", typeof(string));
for (int i = 0; i < _dt.Rows.Count; i++)
{
dr = _newdt.NewRow();
_newdt.Rows.Add(dr);
_newdt.Rows[i]["電子信箱"] = _dt.Rows[i]["email_address"];
_newdt.Rows[i]["姓名"] = _dt.Rows[i]["email_name"];
uint email_status = Convert.ToUInt32(_dt.Rows[i]["email_status"].ToString());
if (email_status == 1)
{
_newdt.Rows[i]["訂閱狀態"] = "已訂閱";
}
else
{
_newdt.Rows[i]["訂閱狀態"] = "未訂閱";
}
}
StringWriter sw = ExcelHelperXhf.SetCsvFromData(_newdt, fileName);
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.Default;
Response.Write(sw);
Response.End();
}
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 DeleteEdmGroup()
{
string json = string.Empty;
EdmGroup query = null;
_edmGroup = new EdmGroupMgr(mySqlConnectionString);
List<EdmGroup> list = new List<EdmGroup>();
try
{
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 EdmGroup();
query.group_id = Convert.ToUInt32(id);
list.Add(query);
}
}
}
json = _edmGroup.DeleteEdmGroup(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 SaveEdmGroup()
{
EdmGroup query = new EdmGroup();
string json = string.Empty;
try
{
if (!string.IsNullOrEmpty(Request.Params["group_id"]))
{
query.group_id = Convert.ToUInt32(Request.Params["group_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["group_name"]))
{
query.group_name = (Request.Params["group_name"]);
}
_edmGroup = new EdmGroupMgr(mySqlConnectionString);
json = _edmGroup.SaveEdmGroup(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;
}
public HttpResponseBase GetEdmGroupList()
{
List<EdmGroup> store = new List<EdmGroup>();
EdmGroup query = new EdmGroup();
int totalCount = 0;
string json = string.Empty;
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["dateType"]))
{
query.dateCondition = Convert.ToInt32(Request.Params["dateType"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["timestart"]))
{
query.start = (uint)CommonFunction.GetPHPTime(Request.Params["timestart"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["timeend"]))
{
query.end = (uint)CommonFunction.GetPHPTime(Request.Params["timeend"].ToString());
}
try
{
_edmGroup = new EdmGroupMgr(mySqlConnectionString);
store = _edmGroup.GetEdmGroupList(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;
}