public HttpResponseBase LoadCondition()
{
string json = string.Empty;
List<EdmListConditoinSubQuery> store = new List<EdmListConditoinSubQuery>();
EdmListConditoinSubQuery query = new EdmListConditoinSubQuery();
_edmlistsubMgr = new EdmListConditoinSubMgr(sqlConnectionString);
try
{
if (!string.IsNullOrEmpty(Request.Params["conditionName"]))
{
query.elcm_name = Request.Params["conditionName"];
}
store = _edmlistsubMgr.LoadCondition(query);
if (store != null)
{
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd";
json = "{success:true" + ",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,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public int SaveListInfoCondition(EdmListConditoinSubQuery query)
{
try
{
int i = _edmlistsubDao.SaveListInfoCondition(query);
return i;
}
catch (Exception ex)
{
throw new Exception(" EdmListConditoinSubMgr-->SaveListInfoCondition " + ex.Message, ex);
}
}
public int UpdateCondition(EdmListConditoinSubQuery query)
{
int i = 0;
try
{
EdmListConditionMain model = new EdmListConditionMain();
model = _edmlistmainDao.SelectElcmIDByConditionName(query.elcm_name);
if (model != null)
{
query.elcm_id = model.elcm_id;
_edmlistsubDao.DeleteInfo(query);
i = _edmlistsubDao.SaveListInfoCondition(query);
}
return i;
}
catch (Exception ex)
{
throw new Exception(" EdmListConditionMainMgr-->UpdateCondition " + ex.Message, ex);
}
}
/// <summary>
/// EDM電子報發送mail
/// </summary>
/// <param name="q">elcm_id</param>
/// <returns>email</returns>
public DataTable GetUserEmail(int elcm_id)
{
try
{
EdmListConditoinSubQuery query = new EdmListConditoinSubQuery();
query.elcm_id=elcm_id;
DateTime dt;
List<EdmListConditoinSub> edm = _edmlistsubDao.LoadCondition(query);
foreach (var item in edm)
{
if (item.elcs_key == "gender")
{
query.chkGender = true;
query.genderCondition = int.Parse(item.elcs_value1.ToString());
}
if (item.elcs_key == "buy_times")
{
query.ChkBuy = true;
query.buyCondition = int.Parse(item.elcs_value1.ToString());
query.buyTimes = int.Parse(item.elcs_value2.ToString());
if (DateTime.TryParse(item.elcs_value3.ToString(), out dt))
{
query.buyTimeMin = dt;
}
if (DateTime.TryParse(item.elcs_value4.ToString(), out dt))
{
query.buyTimeMax = dt;
}
}
if (item.elcs_key == "age")
{
query.ChkAge = true;
query.ageMin = Convert.ToInt32(item.elcs_value1.ToString());
query.ageMax = Convert.ToInt32(item.elcs_value2.ToString());
}
if (item.elcs_key == "cancel_times")
{
query.ChkCancel = true;
query.cancelCondition = Convert.ToInt32(item.elcs_value1.ToString());
query.cancelTimes = Convert.ToInt32(item.elcs_value2.ToString());
if (DateTime.TryParse(item.elcs_value3.ToString(), out dt))
{
query.cancelTimeMin = dt;
}
if (DateTime.TryParse(item.elcs_value4.ToString(), out dt))
{
query.cancelTimeMax = dt;
}
}
if (item.elcs_key == "register_time")
{
query.ChkRegisterTime = true;
if (DateTime.TryParse(item.elcs_value1.ToString(), out dt))
{
query.registerTimeMin = dt;
}
if (DateTime.TryParse(item.elcs_value2.ToString(), out dt))
{
query.registerTimeMax = dt;
}
}
if (item.elcs_key == "return_times")
{
query.ChkReturn = true;
query.returnCondition = Convert.ToInt32(item.elcs_value1.ToString());
query.returnTimes = Convert.ToInt32(item.elcs_value2.ToString());
if (DateTime.TryParse(item.elcs_value3.ToString(), out dt))
{
query.returnTimeMin = dt;
}
if (DateTime.TryParse(item.elcs_value4.ToString(), out dt))
{
query.returnTimeMax = dt;
}
}
if (item.elcs_key == "last_order")
{
query.ChkLastOrder = true;
if (DateTime.TryParse(item.elcs_value3.ToString(), out dt))
{
query.lastOrderMin = dt;
}
if (DateTime.TryParse(item.elcs_value4.ToString(), out dt))
{
query.lastOrderMax = dt;
}
}
if (item.elcs_key == "replenishment_info")
{
query.ChkNotice = true;
query.noticeCondition = Convert.ToInt32(item.elcs_value1.ToString());
query.noticeTimes = Convert.ToInt32(item.elcs_value2.ToString());
}
if (item.elcs_key == "last_login")
{
query.ChkLastLogin = true;
if (DateTime.TryParse(item.elcs_value3.ToString(), out dt))
{
query.lastLoginMin = dt;
}
if (DateTime.TryParse(item.elcs_value4.ToString(), out dt))
{
query.lastLoginMax = dt;
}
}
if (item.elcs_key == "total_consumption")
{
query.ChkTotalConsumption = true;
query.totalConsumptionMin = Convert.ToInt32(item.elcs_value1.ToString());
query.totalConsumptionMax = Convert.ToInt32(item.elcs_value2.ToString());
}
if (item.elcs_key == "black_list")
{
query.ChkBlackList = true;
}
}
return _edmlistmainDao.GetUserNum(query);
}
catch (Exception ex)
{
throw new Exception(" EdmListConditionMainMgr-->GetUserNum " + ex.Message, ex);
}
}
public DataTable GetUserNum(EdmListConditoinSubQuery q)
{
try
{
return _edmlistmainDao.GetUserNum(q);
}
catch (Exception ex)
{
throw new Exception(" EdmListConditionMainMgr-->GetUserNum " + ex.Message, ex);
}
}
/// <summary>
/// 查詢符合該條件的所有會員以及人數
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public DataTable GetUserNum(EdmListConditoinSubQuery q)
{//需要主表條件的id
StringBuilder sql = new StringBuilder();
StringBuilder join = new StringBuilder();
StringBuilder where = new StringBuilder();
StringBuilder buywhere = new StringBuilder();
DateTime dtime = DateTime.Parse("2010-01-01");
try
{
if (q.MailorPhone == 2)
{
sql.Append(@"SELECT u.user_mobile,u.user_name,u.user_id FROM users u ");
}
else
{
sql.Append(@"SELECT u.user_email,u.user_name,u.user_id FROM users u ");
}
where.Append(@"WHERE 1=1 ");
if(q.chkGender)
{//性別
if (q.genderCondition < 2)
{
where.AppendFormat(" AND u.user_gender ='{0}' ", q.genderCondition);
}
}
if (q.ChkBuy)
{//購買次數
if (q.buyTimeMin >= dtime)
{//時間限制
buywhere.AppendFormat(" AND FROM_UNIXTIME(order_createdate) >= '{0}' ", q.buyTimeMin.ToString("yyyy/MM/dd 00:00:00"));
}
if (q.buyTimeMax >= q.buyTimeMin && q.buyTimeMax != DateTime.MinValue)
{
buywhere.AppendFormat(" AND FROM_UNIXTIME(order_createdate) <= '{0}' ", q.buyTimeMax.ToString("yyyy/MM/dd 23:59:59"));
}
if (q.buyCondition != 0 && q.buyTimes <= 1)
{
where.AppendFormat(" AND NOT EXISTS (SELECT om.user_id FROM order_master AS om WHERE om.user_id = u.user_id AND om.order_status NOT IN(90,91) {0} GROUP BY om.user_id) ", buywhere);
}
else
{
where.AppendFormat(" AND gm.gmcs {0} {1} ", q.buyCondition == 0 ? ">" : "<", q.buyTimes);
join.AppendFormat(" INNER JOIN (select user_id,count(om.order_id) as 'gmcs' FROM order_master om WHERE order_status NOT IN(90,91) {0} GROUP BY user_id) gm ON u.user_id=gm.user_id ", buywhere);
}
}
if (q.ChkAge)
{//年齡
where.AppendFormat(" AND u.user_birthday_year BETWEEN {0} AND {1} ", DateTime.Now.Year - q.ageMax, DateTime.Now.Year - q.ageMin);
}
if (q.ChkCancel)
{//取消次數
buywhere.Clear();
if (q.cancelTimeMin >= dtime)
{//時間限制
buywhere.AppendFormat(" AND FROM_UNIXTIME(order_createdate) >= '{0}' ", q.cancelTimeMin.ToString("yyyy/MM/dd 00:00:00"));
}
if (q.cancelTimeMax >= q.cancelTimeMin && q.cancelTimeMax != DateTime.MinValue)
{
buywhere.AppendFormat(" AND FROM_UNIXTIME(order_createdate) <= '{0}' ", q.cancelTimeMax.ToString("yyyy/MM/dd 23:59:59"));
}
if (q.cancelCondition != 0 && q.cancelTimes <= 1)
{
where.AppendFormat(" AND NOT EXISTS (select user_id FROM order_master om WHERE om.user_id = u.user_id AND order_status IN(90) {0} GROUP BY om.user_id)", buywhere);
}
else
{
where.AppendFormat(" AND qx.qxcs {0} {1} ", q.cancelCondition == 0 ? ">" : "<", q.cancelTimes);
join.AppendFormat(" INNER JOIN (select user_id,count(om.order_id) as 'qxcs' FROM order_master om WHERE order_status IN (90) {0} GROUP BY user_id) qx ON u.user_id=qx.user_id ", buywhere);
}
}
if (q.ChkRegisterTime)
{//註冊時間
if (q.registerTimeMin >= dtime)
{
where.AppendFormat(" AND u.user_reg_date >= '{0}'", CommonFunction.GetPHPTime(q.registerTimeMin.ToString("yyyy/MM/dd 00:00:00")));
}
if (q.registerTimeMax > dtime)
{
where.AppendFormat(" AND u.user_reg_date <= '{0}'", CommonFunction.GetPHPTime(q.registerTimeMax.ToString("yyyy/MM/dd 23:59:59")));
}
}
if (q.ChkReturn)
{//退貨次數
buywhere.Clear();
if (q.returnTimeMin >= dtime)
{//時間限制
buywhere.AppendFormat(" AND FROM_UNIXTIME(return_createdate) >= '{0}' ", q.returnTimeMin.ToString("yyyy/MM/dd 00:00:00"));
}
if (q.returnTimeMax >= q.returnTimeMin && q.returnTimeMax != DateTime.MinValue)
{
buywhere.AppendFormat(" AND FROM_UNIXTIME(return_createdate) <= '{0}' ", q.returnTimeMax.ToString("yyyy/MM/dd 23:59:59"));
}
if (q.returnCondition != 0 && q.returnTimes <= 1)
{
where.AppendFormat("AND NOT EXISTS (select DISTINCT om.user_id FROM order_return_master orm LEFT JOIN order_master om on orm.order_id=om.order_id WHERE om.user_id = u.user_id AND return_status=1 {0} GROUP BY om.user_id)", buywhere);
}
else
{
where.AppendFormat(" AND th.thcs {0} {1} ", q.returnCondition == 0 ? ">" : "<", q.returnTimes);
join.AppendFormat(" INNER JOIN (select om.user_id,Count(orm.return_id) as thcs FROM order_return_master orm LEFT JOIN order_master om on orm.order_id=om.order_id WHERE return_status=1 {0} GROUP BY om.user_id) th ON u.user_id=th.user_id ", buywhere);
}
}
if (q.ChkLastOrder)
{//最後訂單
join.Append(" LEFT JOIN (SELECT user_id,MAX(order_createdate) as 'order_createdate' from order_master GROUP BY user_id) om ON u.user_id = om.user_id ");
if (q.lastOrderMin >= dtime)
{
where.AppendFormat(" AND FROM_UNIXTIME(om.order_createdate) >= '{0}'", q.lastOrderMin.ToString("yyyy/MM/dd 00:00:00"));
}
if (q.lastOrderMax > dtime)
{
where.AppendFormat(" AND FROM_UNIXTIME(om.order_createdate) <= '{0}'", q.lastOrderMax.ToString("yyyy/MM/dd 23:59:59"));
}
}
if (q.ChkNotice)
{//貨到通知
if (q.noticeCondition != 0 && q.noticeTimes <= 1)
{
where.Append(" AND NOT EXISTS (SELECT a.user_id,Count(a.id) as hdtz from arrival_notice a WHERE a.user_id = u.user_id GROUP BY user_id )");
}
else
{
join.Append(" INNER JOIN (SELECT user_id,Count(id) as hdtz from arrival_notice GROUP BY user_id) an ON u.user_id = an.user_id ");
where.AppendFormat(" AND an.hdtz {0} {1} ", q.noticeCondition == 0 ? ">" : "<", q.noticeTimes);
}
}
if (q.ChkLastLogin)
{//最後登入
join.Append(" LEFT JOIN (select user_id,MAX(login_createdate) as 'login_createdate' FROM users_login GROUP BY user_id) ul ON u.user_id = ul.user_id ");
if (q.lastLoginMin >= dtime)
{
where.AppendFormat(" AND FROM_UNIXTIME(ul.login_createdate) >= '{0}'", q.lastLoginMin.ToString("yyyy/MM/dd 00:00:00"));
}
if (q.lastLoginMax > dtime)
{
where.AppendFormat(" AND FROM_UNIXTIME(ul.login_createdate) <= '{0}'", q.lastLoginMax.ToString("yyyy/MM/dd 23:59:59"));
}
}
if (q.ChkTotalConsumption)
{//消費金額
join.Append(" LEFT JOIN (SELECT om.user_id,od.single_money*od.buy_num as 'price' from order_master om LEFT JOIN order_slave os on om.order_id=os.order_id left JOIN order_detail od ON os.slave_id=od.slave_id where od.item_mode in (0,1) GROUP BY om.user_id ) money ON u.user_id = money.user_id ");
if (q.totalConsumptionMin >0)
{
where.AppendFormat(" AND money.price >= '{0}'", q.totalConsumptionMin);
}
if (q.totalConsumptionMax >= q.totalConsumptionMin && q.totalConsumptionMax != 0)
{
where.AppendFormat(" AND money.price <= '{0}'", q.totalConsumptionMax);
}
}
if (q.ChkBlackList)
{//不排除黑名單
where.Append(" AND u.user_id NOT IN (SELECT user_id from vip_user WHERE group_id='48' ) ");
}
if (q.ChkPhone)
{//排除拒收廣告簡訊的人
where.Append(" AND send_sms_ad=1 ");
}
return _dbAccess.getDataTable(sql.ToString() + join.ToString() + where.ToString());
}
catch (Exception ex)
{
throw new Exception(" EdmListConditionMainDao-->GetUserNum " + ex.Message + "sql:" + sql.ToString() + join.ToString() + where.ToString(), ex);
}
}
public List<EdmListConditoinSubQuery> LoadCondition(EdmListConditoinSubQuery query)
{
EdmListConditionMain model = new EdmListConditionMain();
List<EdmListConditoinSub> result = new List<EdmListConditoinSub>();
List<EdmListConditoinSubQuery> store = new List<EdmListConditoinSubQuery>();
try
{
model = _edmlistmainDao.SelectElcmIDByConditionName(query.elcm_name);
if (model != null)
{
query.elcm_id = model.elcm_id;
result = _edmlistsubDao.LoadCondition(query);
if (result != null)
{
EdmListConditoinSubQuery q = new EdmListConditoinSubQuery();
store.Add(q);
#region 保存條件狀態
for (int i = 0; i < result.Count; i++)
{
string key = result[i].elcs_key;
switch (key)
{
case "gender":
store[0].chkGender = true;
if (!string.IsNullOrEmpty(result[i].elcs_value1))
{
store[0].genderCondition = Convert.ToInt32(result[i].elcs_value1);
}
break;
case "buy_times":
store[0].ChkBuy = true;
if (Convert.ToInt32(result[i].elcs_value1) != 0)
{
store[0].buyCondition = Convert.ToInt32(result[i].elcs_value1);
}
if (Convert.ToInt32(result[i].elcs_value2) != 0)
{
store[0].buyTimes = Convert.ToInt32(result[i].elcs_value2);
}
if (!string.IsNullOrEmpty(result[i].elcs_value3))
{
if (Convert.ToDateTime(result[i].elcs_value3) != DateTime.MinValue)
{
store[0].buyTimeMin = Convert.ToDateTime(result[i].elcs_value3);
}
if (Convert.ToDateTime(result[i].elcs_value4) != DateTime.MinValue)
{
store[0].buyTimeMax = Convert.ToDateTime(result[i].elcs_value4);
}
}
break;
case "age":
store[0].ChkAge = true;
if (Convert.ToInt32(result[i].elcs_value1) != 0)
{
store[0].ageMin = Convert.ToInt32(result[i].elcs_value1);
}
if (Convert.ToInt32(result[i].elcs_value2) != 0)
{
store[0].ageMax = Convert.ToInt32(result[i].elcs_value2);
}
break;
case "cancel_times":
store[0].ChkCancel = true;
if (Convert.ToInt32(result[i].elcs_value1) != 0)
{
store[0].cancelCondition = Convert.ToInt32(result[i].elcs_value1);
}
if (Convert.ToInt32(result[i].elcs_value2) != 0)
{
store[0].cancelTimes = Convert.ToInt32(result[i].elcs_value2);
}
if (!string.IsNullOrEmpty(result[i].elcs_value3))
{
if (Convert.ToDateTime(result[i].elcs_value3) != DateTime.MinValue)
{
store[0].cancelTimeMin = Convert.ToDateTime(result[i].elcs_value3);
}
if (Convert.ToDateTime(result[i].elcs_value4) != DateTime.MinValue)
{
store[0].cancelTimeMax = Convert.ToDateTime(result[i].elcs_value4);
}
}
break;
case "register_time":
store[0].ChkRegisterTime = true;
if (!string.IsNullOrEmpty(result[i].elcs_value1))
{
if (Convert.ToDateTime(result[i].elcs_value1) != DateTime.MinValue)
{
store[0].registerTimeMin = Convert.ToDateTime(result[i].elcs_value1);
}
if (Convert.ToDateTime(result[i].elcs_value2) != DateTime.MinValue)
{
store[0].registerTimeMax = Convert.ToDateTime(result[i].elcs_value2);
}
}
break;
case "return_times":
store[0].ChkReturn = true;
if (Convert.ToInt32(result[i].elcs_value1) != 0)
{
store[0].returnCondition = Convert.ToInt32(result[i].elcs_value1);
}
if (Convert.ToInt32(result[i].elcs_value2) != 0)
{
store[0].returnTimes = Convert.ToInt32(result[i].elcs_value2);
}
if (!string.IsNullOrEmpty(result[i].elcs_value3))
{
if (Convert.ToDateTime(result[i].elcs_value3) != DateTime.MinValue)
{
store[0].returnTimeMin = Convert.ToDateTime(result[i].elcs_value3);
}
if (Convert.ToDateTime(result[i].elcs_value4) != DateTime.MinValue)
{
store[0].returnTimeMax = Convert.ToDateTime(result[i].elcs_value4);
}
}
break;
case "last_order":
store[0].ChkLastOrder = true;
if (!string.IsNullOrEmpty(result[i].elcs_value1))
{
if (Convert.ToDateTime(result[i].elcs_value1) != DateTime.MinValue)
{
store[0].lastOrderMin = Convert.ToDateTime(result[i].elcs_value1);
}
if (Convert.ToDateTime(result[i].elcs_value2) != DateTime.MinValue)
{
store[0].lastOrderMax = Convert.ToDateTime(result[i].elcs_value2);
}
}
break;
case "replenishment_info":
store[0].ChkNotice = true;
if (!string.IsNullOrEmpty(result[i].elcs_value1))
{
if (Convert.ToInt32(result[i].elcs_value1) != 0)
{
store[0].noticeCondition = Convert.ToInt32(result[i].elcs_value1);
}
if (Convert.ToInt32(result[i].elcs_value2) != 0)
{
store[0].noticeTimes = Convert.ToInt32(result[i].elcs_value2);
}
}
break;
case "last_login":
store[0].ChkLastLogin = true;
if (!string.IsNullOrEmpty(result[i].elcs_value1))
{
if (Convert.ToDateTime(result[i].elcs_value1) != DateTime.MinValue)
{
store[0].lastLoginMin = Convert.ToDateTime(result[i].elcs_value1);
}
if (Convert.ToDateTime(result[i].elcs_value2) != DateTime.MinValue)
{
store[0].lastLoginMax = Convert.ToDateTime(result[i].elcs_value2);
}
}
break;
case "total_consumption":
store[0].ChkTotalConsumption = true;
if (!string.IsNullOrEmpty(result[i].elcs_value1))
{
if (Convert.ToInt32(result[i].elcs_value1) != 0)
{
store[0].totalConsumptionMin = Convert.ToInt32(result[i].elcs_value1);
}
if (Convert.ToInt32(result[i].elcs_value2) != 0)
{
store[0].totalConsumptionMax = Convert.ToInt32(result[i].elcs_value2);
}
}
break;
case "black_list":
store[0].ChkBlackList = true;
break;
case "phone":
store[0].ChkPhone = true;
break;
}
}
#endregion
}
}
return store;
}
catch (Exception ex)
{
throw new Exception(" EdmListConditoinSubMgr-->LoadCondition " + ex.Message, ex);
}
}
public HttpResponseBase UpdateCondition()
{
string json = string.Empty;
_edmlistmainMgr = new EdmListConditionMainMgr(sqlConnectionString);
_edmlistsubMgr = new EdmListConditoinSubMgr(sqlConnectionString);
EdmListConditoinSubQuery query = new EdmListConditoinSubQuery();
try
{
SetQueryValue(query);
int i = _edmlistmainMgr.UpdateCondition(query);
if (i > 0)
{
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 EdmListConditoinSubQuery SetQueryValue(EdmListConditoinSubQuery query)
{
try
{
if (!string.IsNullOrEmpty(Request.Params["elcm_name"]))
{
query.elcm_name = Request.Params["elcm_name"];
}
if (!string.IsNullOrEmpty(Request.Params["chkGender"]))
{
if (Request.Params["chkGender"] == "true")
{
query.chkGender = true;
if (!string.IsNullOrEmpty(Request.Params["genderCondition"]))
{
query.genderCondition = Convert.ToInt32(Request.Params["genderCondition"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkBuy"]))
{
if (Request.Params["ChkBuy"] == "true")
{
query.ChkBuy = true;
if (!string.IsNullOrEmpty(Request.Params["buyCondition"]))
{
query.buyCondition = Convert.ToInt32(Request.Params["buyCondition"]);
}
if (!string.IsNullOrEmpty(Request.Params["buyTimes"]))
{
query.buyTimes = Convert.ToInt32(Request.Params["buyTimes"]);
}
if (!string.IsNullOrEmpty(Request.Params["buyTimeMin"]))
{
query.buyTimeMin = Convert.ToDateTime(Request.Params["buyTimeMin"]);
}
if (!string.IsNullOrEmpty(Request.Params["buyTimeMax"]))
{
query.buyTimeMax = Convert.ToDateTime(Request.Params["buyTimeMax"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkAge"]))
{
if (Request.Params["ChkAge"] == "true")
{
query.ChkAge = true;
if (!string.IsNullOrEmpty(Request.Params["ageMin"]))
{
query.ageMin = Convert.ToInt32(Request.Params["ageMin"]);
}
if (!string.IsNullOrEmpty(Request.Params["ageMax"]))
{
query.ageMax = Convert.ToInt32(Request.Params["ageMax"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkCancel"]))
{
if (Request.Params["ChkCancel"] == "true")
{
query.ChkCancel = true;
if (!string.IsNullOrEmpty(Request.Params["cancelCondition"]))
{
query.cancelCondition = Convert.ToInt32(Request.Params["cancelCondition"]);
}
if (!string.IsNullOrEmpty(Request.Params["cancelTimes"]))
{
query.cancelTimes = Convert.ToInt32(Request.Params["cancelTimes"]);
}
if (!string.IsNullOrEmpty(Request.Params["cancelTimeMin"]))
{
query.cancelTimeMin = Convert.ToDateTime(Request.Params["cancelTimeMin"]);
}
if (!string.IsNullOrEmpty(Request.Params["cancelTimeMax"]))
{
query.cancelTimeMax = Convert.ToDateTime(Request.Params["cancelTimeMax"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkRegisterTime"]))
{
if (Request.Params["ChkRegisterTime"] == "true")
{
query.ChkRegisterTime = true;
if (!string.IsNullOrEmpty(Request.Params["registerTimeMin"]))
{
query.registerTimeMin = Convert.ToDateTime(Request.Params["registerTimeMin"]);
}
if (!string.IsNullOrEmpty(Request.Params["registerTimeMax"]))
{
query.registerTimeMax = Convert.ToDateTime(Request.Params["registerTimeMax"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkReturn"]))
{
if (Request.Params["ChkReturn"] == "true")
{
query.ChkReturn = true;
if (!string.IsNullOrEmpty(Request.Params["returnCondition"]))
{
query.returnCondition = Convert.ToInt32(Request.Params["returnCondition"]);
}
if (!string.IsNullOrEmpty(Request.Params["returnTimes"]))
{
query.returnTimes = Convert.ToInt32(Request.Params["returnTimes"]);
}
if (!string.IsNullOrEmpty(Request.Params["returnTimeMin"]))
{
query.returnTimeMin = Convert.ToDateTime(Request.Params["returnTimeMin"]);
}
if (!string.IsNullOrEmpty(Request.Params["returnTimeMax"]))
{
query.returnTimeMax = Convert.ToDateTime(Request.Params["returnTimeMax"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkLastOrder"]))
{
if (Request.Params["ChkLastOrder"] == "true")
{
query.ChkLastOrder = true;
if (!string.IsNullOrEmpty(Request.Params["lastOrderMin"]))
{
query.lastOrderMin = Convert.ToDateTime(Request.Params["lastOrderMin"]);
}
if (!string.IsNullOrEmpty(Request.Params["lastOrderMax"]))
{
query.lastOrderMax = Convert.ToDateTime(Request.Params["lastOrderMax"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkNotice"]))
{
if (Request.Params["ChkNotice"] == "true")
{
query.ChkNotice = true;
if (!string.IsNullOrEmpty(Request.Params["noticeCondition"]))
{
query.noticeCondition = Convert.ToInt32(Request.Params["noticeCondition"]);
}
if (!string.IsNullOrEmpty(Request.Params["noticeTimes"]))
{
query.noticeTimes = Convert.ToInt32(Request.Params["noticeTimes"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkLastLogin"]))
{
if (Request.Params["ChkLastLogin"] == "true")
{
query.ChkLastLogin = true;
if (!string.IsNullOrEmpty(Request.Params["lastLoginMin"]))
{
query.lastLoginMin = Convert.ToDateTime(Request.Params["lastLoginMin"]);
}
if (!string.IsNullOrEmpty(Request.Params["lastLoginMax"]))
{
query.lastLoginMax = Convert.ToDateTime(Request.Params["lastLoginMax"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkTotalConsumption"]))
{
if (Request.Params["ChkTotalConsumption"] == "true")
{
query.ChkTotalConsumption = true;
if (!string.IsNullOrEmpty(Request.Params["totalConsumptionMin"]))
{
query.totalConsumptionMin = Convert.ToInt32(Request.Params["totalConsumptionMin"]);
}
if (!string.IsNullOrEmpty(Request.Params["totalConsumptionMax"]))
{
query.totalConsumptionMax = Convert.ToInt32(Request.Params["totalConsumptionMax"]);
}
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkBlackList"]))
{
if (Request.Params["ChkBlackList"] == "true")
{
query.ChkBlackList = true;
}
}
if (!string.IsNullOrEmpty(Request.Params["ChkPhone"]))
{
if (Request.Params["ChkPhone"] == "true")
{
query.ChkPhone = true;
}
}
int a;
if (int.TryParse(Request.Params["btn"],out a))
{
query.MailorPhone = a;
}
return query;
}
catch (Exception ex)
{
throw new Exception(" EdmListConditionMainMgr-->SetQueryValue " + ex.Message, ex);
}
}
public HttpResponseBase Export()
{
string json = string.Empty;
EdmListConditoinSubQuery query=new EdmListConditoinSubQuery();
try
{
_edmlistmainMgr = new EdmListConditionMainMgr(sqlConnectionString);
SetQueryValue(query);
DataTable _dt = new DataTable();
string filename = "EDM名單篩選_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
string newFileName = Server.MapPath(excelPath_export + filename);
DataTable _newdt = new DataTable();
DataRow dr;
if (query.MailorPhone == 1)
{
_dt = _edmlistmainMgr.GetUserNum(query);
string[] colName = { "電子信箱,用戶ID" };
_newdt.Columns.Add("user_email", typeof(string));
_newdt.Columns.Add("user_id", typeof(string));
for (int i = 0; i < _dt.Rows.Count; i++)
{
dr = _newdt.NewRow();
_newdt.Rows.Add(dr);
_newdt.Rows[i]["user_email"] = _dt.Rows[i]["user_email"];
_newdt.Rows[i]["user_id"] = _dt.Rows[i]["user_id"];
}
CsvHelper.ExportDataTableToCsv(_newdt, newFileName, colName, true);
json = "{success:true,fileName:\'" + filename + "\'}";
}
else
{
_dt = _edmlistmainMgr.GetUserNum(query);
string[] colName = { "手機號碼,用戶ID" };
_newdt.Columns.Add("user_mobile", typeof(string));
_newdt.Columns.Add("user_id", typeof(string));
for (int i = 0; i < _dt.Rows.Count; i++)
{
dr = _newdt.NewRow();
_newdt.Rows.Add(dr);
_newdt.Rows[i]["user_mobile"] = _dt.Rows[i]["user_mobile"];
_newdt.Rows[i]["user_id"] = _dt.Rows[i]["user_id"];
}
CsvHelper.ExportDataTableToCsv(_newdt, newFileName, colName, true);
json = "{success:true,fileName:\'" + filename + "\'}";
}
}
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 GetUserNum()
{
string json = string.Empty;
DataTable store = new DataTable();
int totalCount = 0;
_edmlistmainMgr = new EdmListConditionMainMgr(sqlConnectionString);
EdmListConditoinSubQuery query = new EdmListConditoinSubQuery();
try
{
SetQueryValue(query);
store = _edmlistmainMgr.GetUserNum(query);
if (store != null && store.Rows.Count > 0)
{
totalCount = store.Rows.Count;
}
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented) + "}";
}
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,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase SaveListInfo()
{
string json = string.Empty;
_edmlistmainMgr = new EdmListConditionMainMgr(sqlConnectionString);
_edmlistsubMgr = new EdmListConditoinSubMgr(sqlConnectionString);
EdmListConditoinSubQuery query = new EdmListConditoinSubQuery();
int id = 0;
int msg = 0;
try
{
SetQueryValue(query);
query.elcm_creator_id = Convert.ToInt32((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id.ToString());
int i = _edmlistmainMgr.SaveListInfoName(query, out id, out msg);
if (i > 0)
{
query.elcm_id = id;
_edmlistsubMgr.SaveListInfoCondition(query);
json = "{success:true}";
}
else if (msg == 1)
{
json = "{success:false,msg:1}"; //篩選條件名稱已存在
}
else
{
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;
}
/// <summary>
/// 儲存篩選條件內容
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int SaveListInfoCondition(EdmListConditoinSubQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
try
{
if (query.chkGender == true)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1) VALUES({0},'gender','{1}');", query.elcm_id, query.genderCondition);
}
if (query.ChkBuy == true)
{
if (query.buyTimeMin != DateTime.MinValue && query.buyTimeMax != DateTime.MinValue)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2,elcs_value3,elcs_value4) VALUES({0},'buy_times','{1}','{2}','{3}','{4}');", query.elcm_id, query.buyCondition, query.buyTimes, CommonFunction.DateTimeToString(query.buyTimeMin), CommonFunction.DateTimeToString(query.buyTimeMax));
}
else
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'buy_times','{1}','{2}');", query.elcm_id, query.buyCondition, query.buyTimes);
}
}
if (query.ChkAge == true)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'age','{1}','{2}');", query.elcm_id, query.ageMin, query.ageMax);
}
if (query.ChkCancel == true)
{
if (query.cancelTimeMin != DateTime.MinValue && query.cancelTimeMax != DateTime.MinValue)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2,elcs_value3,elcs_value4) VALUES({0},'cancel_times','{1}','{2}','{3}','{4}');", query.elcm_id, query.cancelCondition, query.cancelTimes, CommonFunction.DateTimeToString(query.cancelTimeMin), CommonFunction.DateTimeToString(query.cancelTimeMax));
}
else
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'cancel_times','{1}','{2}');", query.elcm_id, query.cancelCondition, query.cancelTimes);
}
}
if (query.ChkRegisterTime == true)
{
if (query.registerTimeMin != DateTime.MinValue && query.registerTimeMax != DateTime.MinValue)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'register_time','{1}','{2}');", query.elcm_id, CommonFunction.DateTimeToString(query.registerTimeMin), CommonFunction.DateTimeToString(query.registerTimeMax));
}
else
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'register_time','','');", query.elcm_id);
}
}
if (query.ChkReturn == true)
{
if (query.returnTimeMin != DateTime.MinValue)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2,elcs_value3,elcs_value4) VALUES({0},'return_times','{1}','{2}','{3}','{4}');", query.elcm_id, query.returnCondition, query.returnTimes, CommonFunction.DateTimeToString(query.returnTimeMin), CommonFunction.DateTimeToString(query.returnTimeMax));
}
else
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'return_times','{1}','{2}');", query.elcm_id, query.returnCondition, query.returnTimes);
}
}
if (query.ChkLastOrder == true)
{
if (query.lastOrderMin != DateTime.MinValue && query.lastOrderMax != DateTime.MinValue)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'last_order','{1}','{2}');", query.elcm_id, CommonFunction.DateTimeToString(query.lastOrderMin), CommonFunction.DateTimeToString(query.lastOrderMax));
}
else
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'last_order','','');", query.elcm_id);
}
}
if (query.ChkNotice == true)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'replenishment_info','{1}','{2}');", query.elcm_id, query.noticeCondition, query.noticeTimes);
}
if (query.ChkLastLogin == true)
{
if (query.lastLoginMin != DateTime.MinValue && query.lastLoginMax != DateTime.MinValue)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'last_login','{1}','{2}');", query.elcm_id, CommonFunction.DateTimeToString(query.lastLoginMin), CommonFunction.DateTimeToString(query.lastLoginMax));
}
else
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'last_login','','');", query.elcm_id);
}
}
if (query.ChkTotalConsumption == true)
{
if (query.totalConsumptionMin != 0 && query.totalConsumptionMax != 0)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'total_consumption','{1}','{2}');", query.elcm_id, query.totalConsumptionMin, query.totalConsumptionMax);
}
else
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1,elcs_value2) VALUES({0},'total_consumption','','');", query.elcm_id);
}
}
if (query.ChkBlackList == true)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1) VALUES({0},'black_list','{1}');", query.elcm_id, query.ChkBlackList);
}
if (query.ChkPhone == true)
{
sql.AppendFormat(@"INSERT INTO edm_list_conditoin_sub (elcm_id,elcs_key,elcs_value1) VALUES({0},'phone','{1}');", query.elcm_id, query.ChkPhone);
}
return _dbAccess.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception(" EdmListConditoinSubDao-->SaveListInfoCondition " + ex.Message, ex);
}
}
public List<EdmListConditoinSub> LoadCondition(EdmListConditoinSubQuery query)
{
query.Replace4MySQL();
StringBuilder sql = new StringBuilder();
List<EdmListConditoinSub> store = new List<EdmListConditoinSub>();
try
{
sql.AppendFormat(@"SELECT elcs_id,elcm_id,elcs_key,elcs_value1,elcs_value2,elcs_value3,elcs_value4 from edm_list_conditoin_sub WHERE elcm_id={0};", query.elcm_id);
store = _dbAccess.getDataTableForObj<EdmListConditoinSub>(sql.ToString());
return store;
}
catch (Exception ex)
{
throw new Exception(" EdmListConditoinSubDao-->LoadCondition " + ex.Message, ex);
}
}