public DataTable GetEdmTestList(EdmTestQuery query, out int totalCount)
{
try
{
return _etestDao.GetEdmTestList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("EdmTestMgr-->GetEdmTestList-->", ex);
}
}
public bool AddEdmTest(EdmTestQuery query,out int msg)
{
ArrayList arryList = new ArrayList();
query.Replace4MySQL();
msg = 0;
try
{
_edmemailDao.GetData(query.email_address, out largestid, out id, out name);
if (id != 0)
{
query.email_id = id;
if (_etestDao.SelectExists(id))//存在id是true 不存在是false
{
msg = 1;
return false;
}
else
{
if (name != query.test_username)
{
arryList.Add(_etestDao.UpdateEdmEmailName(query));
}
arryList.Add(_etestDao.InsertEdmTest(query));
}
}
else
{
query.email_id = largestid + 1;
arryList.Add(_etestDao.InsertEdmEmail(query));
arryList.Add(_etestDao.InsertEdmTest(query));
}
}
catch (Exception ex)
{
throw new Exception("EdmTestMgr-->AddEdmTest-->", ex);
}
return _mysqlDao.ExcuteSqls(arryList);
}
public DataTable GetEdmTestList(EdmTestQuery query, out int totalCount)
{
StringBuilder strSql = new StringBuilder();
StringBuilder count = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
DataTable store = new DataTable();
totalCount = 0;
try
{
strSql.AppendFormat(@"SELECT et.email_id,et.test_username,ee.email_address,et.test_status,FROM_UNIXTIME(et.test_createdate) as test_createdate,FROM_UNIXTIME(et.test_updatedate) as test_updatedate FROM edm_email ee,edm_test et WHERE et.email_id = ee.email_id");
if (query.selectType == "0" && !string.IsNullOrEmpty(query.search_con))
{
sqlWhere.AppendFormat(" and ee.email_address like '%{0}%'", query.search_con.ToString());
}
if (query.selectType == "1" && !string.IsNullOrEmpty(query.search_con))
{
sqlWhere.AppendFormat(" and et.test_username like '%{0}%'", query.search_con.ToString());
}
if (query.dateCon == "1")//建立時間
{
if (query.date_start!=0)
{
sqlWhere.AppendFormat(" and et.test_createdate > '{0}' ", query.date_start);
}
if (query.date_end != 0)
{
sqlWhere.AppendFormat(" and et.test_createdate<'{0}' ", query.date_end);
}
}
else if (query.dateCon == "2")
{
if (query.date_start != 0)
{
sqlWhere.AppendFormat(" and et.test_updatedate > '{0}' ", query.date_start);
}
if (query.date_end != 0)
{
sqlWhere.AppendFormat(" and et.test_updatedate<'{0}' ", query.date_end);
}
}
if (query.test_status!=0)
{
sqlWhere.AppendFormat(" and et.test_status='{0}' ", query.test_status);
}
if (query.email_id != 0)
{
sqlWhere.AppendFormat(" and et.email_id = '{0}'", query.email_id);
}
count.AppendFormat(@"SELECT count(et.email_id)as totalCount FROM edm_email ee,edm_test et WHERE ee.email_id=et.email_id ");
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(count.ToString()+sqlWhere.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
strSql.AppendFormat("{0} limit {1},{2}",sqlWhere.ToString(), query.Start, query.Limit);
}
store = _access.getDataTable(strSql.ToString());
}
catch (Exception ex)
{
throw new Exception("EdmTesDao-->GetEdmTestList-->" + ex.Message + strSql.ToString() + count.ToString(), ex);
}
return store;
}
public string UpdateEdmTest(EdmTestQuery query)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"UPDATE edm_test SET test_username='******',test_status={1},test_updatedate='{2}' WHERE email_id={3}; ", query.test_username, query.test_status, query.test_updatedate, query.email_id);
return strSql.ToString();
}
public string UpdateEdmEmailAddress(EdmTestQuery query)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"UPDATE edm_email SET email_address='{0}' WHERE email_id={1}", query.email_address, query.email_id);
return strSql.ToString();
}
public string UpdateEdmEmailName(EdmTestQuery query)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"UPDATE edm_email SET email_name='{0}' WHERE email_id={1};", query.test_username, query.email_id);
strSql.AppendFormat(@"UPDATE edm_test SET test_username='******' WHERE email_id={1}", query.test_username, query.email_id);
return strSql.ToString();
}
public string InsertEdmEmail(EdmTestQuery query)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"INSERT INTO edm_email(email_id,email_name,email_address,email_createdate,email_updatedate) VALUES({0},'{1}','{2}','{3}','{4}')", query.email_id, query.test_username, query.email_address, query.test_createdate, query.test_updatedate);
return strSql.ToString();
}
public string InsertEdmTest(EdmTestQuery query)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"INSERT INTO edm_test(email_id,test_username,test_status,test_createdate,test_updatedate) VALUES({0},'{1}',{2},'{3}','{4}')", query.email_id, query.test_username, query.test_status, query.test_createdate, query.test_updatedate);
return strSql.ToString();
}
public bool EditEdmTest(EdmTestQuery query,out int msg)
{
query.Replace4MySQL();
msg = 0;
ArrayList arryList = new ArrayList();
try
{
arryList.Add(_etestDao.UpdateEdmEmailName(query));
EdmEmail e = _edmemailDao.GetModel(query.email_address);
if (e == null || e.email_id == query.email_id)
{
arryList.Add(_etestDao.UpdateEdmEmailAddress(query));
arryList.Add(_etestDao.UpdateEdmTest(query));
}
else
{
msg = 1;
return false;
}
}
catch (Exception ex)
{
throw new Exception("EdmTestMgr-->EditEdmTest-->", ex);
}
return _mysqlDao.ExcuteSqls(arryList);
}
public List<EdmTestQuery> GetModel(EdmTestQuery query)
{
List<EdmTestQuery> model = new List<EdmTestQuery>();
DataTable dt = new DataTable();
EdmTestQuery qu = new EdmTestQuery();
try
{
dt = _edmemailDao.GetEmailByID(query.email_id);
if (dt != null && dt.Rows.Count > 0)
{
qu.email_id = query.email_id;
qu.email_address = dt.Rows[0]["email_address"].ToString();
qu.test_username = dt.Rows[0]["email_name"].ToString();
model.Add(qu);
}
return model;
}
catch (Exception ex)
{
throw new Exception("EdmTestMgr-->GetModel" + ex.Message, ex);
}
}
public HttpResponseBase DeleteEdmTest()
{
string json = string.Empty;
try
{
_etestMgr = new EdmTestMgr(mySqlConnectionString);
EdmTestQuery query = new EdmTestQuery();
if (!string.IsNullOrEmpty(Request.Params["email_id"]))
{
foreach (string item in Request.Params["email_id"].Split('|'))
{
if (!string.IsNullOrEmpty(item))
{
_etestMgr.DeleteEdmTest(Convert.ToInt32(item));
}
}
json = "{success:true}";
}
}
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 EdmTestAddorEdit()
{
string json = string.Empty;
bool i = false;
int msg = 0;
try
{
_etestMgr = new EdmTestMgr(mySqlConnectionString);
//新增
if (string.IsNullOrEmpty(Request.Params["email_id"]))
{
EdmTestQuery query = new EdmTestQuery();
if (!string.IsNullOrEmpty(Request.Params["email_address"]))
{
query.email_address = Request.Params["email_address"];
}
if (!string.IsNullOrEmpty(Request.Params["test_username"]))
{
query.test_username = Request.Params["test_username"].Replace("\\", "\\\\");
}
if (!string.IsNullOrEmpty(Request.Params["test_status"]))
{
query.test_status = Convert.ToInt32(Request.Params["test_status"]);
}
query.test_createdate = Convert.ToInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString()));
query.test_updatedate = Convert.ToInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString()));
i = _etestMgr.AddEdmTest(query, out msg);
if (i)
{
json = "{success:true}";
}
if (msg == 1)
{
json = "{success:false,msg:1}";
}
}
else
{
EdmTestQuery oldQuery = new EdmTestQuery();
if (!string.IsNullOrEmpty(Request.Params["email_id"]))
{
oldQuery.email_id = Convert.ToUInt32(Request.Params["email_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["email_address"]))
{
oldQuery.email_address = Request.Params["email_address"];
}
if (!string.IsNullOrEmpty(Request.Params["test_username"]))
{
oldQuery.test_username = Request.Params["test_username"].Replace("\\", "\\\\");
}
if (!string.IsNullOrEmpty(Request.Params["test_status"]))
{
oldQuery.test_status = Convert.ToInt32(Request.Params["test_status"]);
}
oldQuery.test_updatedate = Convert.ToInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString()));
i = _etestMgr.EditEdmTest(oldQuery, out msg);
if (i)
{
json = "{success:true}";
}
if (msg == 1)
{
json = "{success:false,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 GetEdmTestList()
{
string json = string.Empty;
int totalCount = 0;
DataTable store = new DataTable();
try
{
_etestMgr = new EdmTestMgr(mySqlConnectionString);
EdmTestQuery query = new EdmTestQuery();
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["dateCon"]))
{
query.dateCon = Request.Params["dateCon"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["date_start"]))
{
query.date_start = (uint)CommonFunction.GetPHPTime(Request.Params["date_start"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["date_end"]))
{
query.date_end = (uint)CommonFunction.GetPHPTime(Request.Params["date_end"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["activeStatus"]))
{
query.test_status = Convert.ToInt32(Request.Params["activeStatus"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["email_id"]))
{
query.email_id = Convert.ToUInt32(Request.Params["email_id"]);
}
store = _etestMgr.GetEdmTestList(query, out totalCount);
for (int i = 0; i < store.Rows.Count; i++)
{
if (Convert.ToBoolean(Request.Params["isSecret"]))
{
DataRow dr = store.Rows[i];
if (!string.IsNullOrEmpty(dr["test_username"].ToString()))
{
dr["test_username"] = dr["test_username"].ToString().Substring(0, 1) + "**";
}
if (!string.IsNullOrEmpty(dr["test_username"].ToString()))
{
dr["email_address"] = dr["email_address"].ToString().Split('@')[0] + "@***";
}
}
}
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}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
