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); } }