public int SaveMemberLevel(MemberLevelQuery query) { StringBuilder sql = new StringBuilder(); query.Replace4MySQL(); try { if (query.rowID == 0)//新增 { sql.Append("insert into member_level(ml_code,ml_name,ml_seq,ml_minimal_amount,ml_max_amount,"); sql.Append("ml_month_seniority,ml_last_purchase,ml_minpurchase_times,ml_birthday_voucher,ml_shipping_voucher,"); sql.Append("k_date,k_user,m_date,m_user,ml_status)"); sql.AppendFormat("values('{0}','{1}','{2}','{3}','{4}',", query.ml_code, query.ml_name, query.ml_seq, query.ml_minimal_amount,query.ml_max_amount); sql.AppendFormat(" '{0}','{1}','{2}','{3}','{4}',", query.ml_month_seniority, query.ml_last_purchase, query.ml_minpurchase_times, query.ml_birthday_voucher,query.ml_shipping_voucher); sql.AppendFormat(" '{0}','{1}','{2}','{3}','{4}');",CommonFunction.DateTimeToString(query.k_date),query.k_user,CommonFunction.DateTimeToString(query.m_date),query.m_user,query.ml_status); } else//編輯 { sql.AppendFormat("update member_level set ml_code='{0}',ml_name='{1}',ml_seq='{2}',ml_minimal_amount='{3}',ml_max_amount='{4}',", query.ml_code, query.ml_name, query.ml_seq, query.ml_minimal_amount,query.ml_max_amount); sql.AppendFormat(" ml_month_seniority='{0}',ml_last_purchase='{1}',ml_minpurchase_times='{2}',ml_birthday_voucher='{3}',ml_shipping_voucher='{4}', ", query.ml_month_seniority, query.ml_last_purchase, query.ml_minpurchase_times, query.ml_birthday_voucher,query.ml_shipping_voucher); sql.AppendFormat(" m_date='{0}',m_user='******' where rowID={2};", CommonFunction.DateTimeToString(query.m_date), query.m_user, query.rowID); } return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("MemberLevelDao-->SaveMemberLevel-->" + ex.Message + sql.ToString(), ex); } }
public bool DistinctSeq(MemberLevelQuery query) { try { return _memberLevel.DistinctSeq(query); } catch (Exception ex) { throw new Exception("MemberLevelMgr-->DistinctSeq-->" + ex.Message, ex); } }
public int SaveMemberLevel(MemberLevelQuery query) { try { return _memberLevel.SaveMemberLevel(query); } catch (Exception ex) { throw new Exception("MemberLevelMgr-->SaveMemberLevel-->" + ex.Message, ex); } }
public int UpdateActive(MemberLevelQuery query) { try { return _memberLevel.UpdateActive(query); } catch (Exception ex) { throw new Exception("MemberLevelMgr-->UpdateActive-->" + ex.Message, ex); } }
public List<MemberLevelQuery> MemberLevelList(MemberLevelQuery query, out int totalCount) { try { return _memberLevel.MemberLevelList(query, out totalCount); } catch (Exception ex) { throw new Exception("MemberLevelMgr-->MemberLevelList-->" + ex.Message, ex); } }
public int UpdateActive(MemberLevelQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("update member_level set ml_status='{0}',m_date='{1}',m_user='******' where rowID='{3}';", query.ml_status, CommonFunction.DateTimeToString(query.m_date), query.m_user, query.rowID); return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("MemberLevelDao-->UpdateActive-->" + ex.Message + sql.ToString(), ex); } }
public List<MemberLevelQuery> MemberLevelList(MemberLevelQuery query, out int totalCount ) { StringBuilder sql = new StringBuilder(); StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); query.Replace4MySQL(); totalCount = 0; try { sql.Append("select ml.rowID,ml.ml_code,ml.ml_name,ml.ml_seq,ml.ml_minimal_amount,ml_max_amount,ml.ml_month_seniority,ml.ml_last_purchase,ml.ml_minpurchase_times,ml.ml_birthday_voucher,ml.ml_shipping_voucher,ml.ml_status,ml.k_date,ml.k_user,ml.m_date,ml.m_user,mu1.user_username 'create_user',mu2.user_username 'update_user' "); sqlFrom.Append(" from member_level ml "); sqlFrom.Append(" LEFT JOIN manage_user mu1 on mu1.user_id=ml.k_user "); sqlFrom.Append(" LEFT JOIN manage_user mu2 on mu2.user_id=ml.m_user "); sqlWhere.Append(" where 1=1 "); if (!string.IsNullOrEmpty(query.code_name)) { sqlWhere.AppendFormat(" and (ml.ml_code='{0}' or ml.ml_name='{0}' ) ",query.code_name); } if (query.ml_status != -1) { sqlWhere.AppendFormat(" and ml.ml_status='{0}' ",query.ml_status); } if (query.IsPage) { DataTable dt = _access.getDataTable("select count(ml.rowID) as totalCount " + sqlFrom.ToString() + sqlWhere.ToString()); if (dt != null && dt.Rows.Count > 0) { totalCount = Convert.ToInt32(dt.Rows[0]["totalCount"]); } } sqlWhere.AppendFormat("order by ml.ml_seq limit {0},{1} ", query.Start, query.Limit); sql.Append(sqlFrom.ToString() + sqlWhere.ToString()); return _access.getDataTableForObj<MemberLevelQuery>(sql.ToString()); } catch (Exception ex) { throw new Exception("MemberLevelDao-->MemberLevelList-->" + ex.Message + sql.ToString(), ex); } }
public HttpResponseBase MemberLevelList() { MemberLevelQuery query = new MemberLevelQuery(); List<MemberLevelQuery> store = new List<MemberLevelQuery>(); string json = string.Empty; int totalCount = 0; try { query.Start = Convert.ToInt32(Request.Params["start"] ?? "0"); query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25"); if (!string.IsNullOrEmpty(Request.Params["code_name"])) { query.code_name = Request.Params["code_name"]; } if (!string.IsNullOrEmpty(Request.Params["mem_status"])) { query.ml_status = Convert.ToSByte(Request.Params["mem_status"]); } _memberLevel = new MemberLevelMgr(mySqlConnectionString); store = _memberLevel.MemberLevelList(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:true,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public JsonResult UpdateActive() { try { MemberLevelQuery query = new MemberLevelQuery(); if (!string.IsNullOrEmpty(Request.Params["rowID"].ToString())) { query.rowID = Convert.ToInt32(Request.Params["rowID"].ToString()); } query.ml_status = Convert.ToSByte(Request.Params["ml_status"] ?? "0"); query.m_date = DateTime.Now; query.m_user = (Session["caller"] as Caller).user_id; _memberLevel = new MemberLevelMgr(mySqlConnectionString); if (_memberLevel.UpdateActive(query) > 0) { return Json(new { success = "true" }); } else { return Json(new { success = "false" }); } } 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); return Json(new { success = "false" }); } }
public HttpResponseBase DistinctSeq() { string json = string.Empty; try { MemberLevelQuery query = new MemberLevelQuery(); if (!string.IsNullOrEmpty(Request.Params["ml_seq"])) { query.ml_seq = Convert.ToInt32(Request.Params["ml_seq"]); _memberLevel = new MemberLevelMgr(mySqlConnectionString); if (_memberLevel.DistinctSeq(query)) { json = "{success:false}";//排序重複 } else { 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 SaveMemberLevel() { string json = string.Empty; try { MemberLevelQuery query = new MemberLevelQuery(); _memberLevel = new MemberLevelMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["ml_name"])) { query.ml_name = Request.Params["ml_name"]; } if (!string.IsNullOrEmpty(Request.Params["ml_code"])) { query.ml_code = Request.Params["ml_code"]; } if (!string.IsNullOrEmpty(Request.Params["ml_minimal_amount"])) { query.ml_minimal_amount = Convert.ToInt32(Request.Params["ml_minimal_amount"]); } if (!string.IsNullOrEmpty(Request.Params["ml_max_amount"])) { query.ml_max_amount = Convert.ToInt32(Request.Params["ml_max_amount"]); } if (!string.IsNullOrEmpty(Request.Params["ml_month_seniority"])) { query.ml_month_seniority = Convert.ToInt32(Request.Params["ml_month_seniority"]); } if (!string.IsNullOrEmpty(Request.Params["ml_last_purchase"])) { query.ml_last_purchase = Convert.ToInt32(Request.Params["ml_last_purchase"]); } if (!string.IsNullOrEmpty(Request.Params["ml_minpurchase_times"])) { query.ml_minpurchase_times = Convert.ToInt32(Request.Params["ml_minpurchase_times"]); } if (!string.IsNullOrEmpty(Request.Params["ml_birthday_voucher"])) { query.ml_birthday_voucher = Convert.ToInt32(Request.Params["ml_birthday_voucher"]); } if (!string.IsNullOrEmpty(Request.Params["ml_shipping_voucher"])) { query.ml_shipping_voucher = Convert.ToInt32(Request.Params["ml_shipping_voucher"]); } if (!string.IsNullOrEmpty(Request.Params["ml_seq"])) { query.ml_seq = Convert.ToInt32(Request.Params["ml_seq"]); } if (!string.IsNullOrEmpty(Request.Params["rowID"]))//編輯 { query.rowID = Convert.ToInt32(Request.Params["rowID"]); query.m_user = (Session["caller"] as Caller).user_id; query.m_date = DateTime.Now; if (!string.IsNullOrEmpty(Request.Params["old_ml_seq"])) { query.old_ml_seq = Convert.ToInt32(Request.Params["old_ml_seq"]); } if (!string.IsNullOrEmpty(Request.Params["old_ml_code"])) { query.old_ml_code = (Request.Params["old_ml_code"]); } if (query.ml_seq != query.old_ml_seq && _memberLevel.DistinctSeq(query))//新代碼與老代碼不一樣並且重複 { json = "{success:'true',msg:'0'}";//排序重複 } else if (query.ml_code != query.old_ml_code && _memberLevel.DistinctCode(query)) { json = "{success:'true',msg:'2'}";//級別代碼重複 } else { if (_memberLevel.SaveMemberLevel(query) > 0) { json = "{success:'true',mag:'1'}";//保存成功 } else { json = "{success:'false'}";//保存失敗 } } } else//新增 { query.k_user = (Session["caller"] as Caller).user_id; query.k_date = DateTime.Now; query.k_user = (Session["caller"] as Caller).user_id; query.m_date = query.k_date; if (_memberLevel.DistinctSeq(query)) { json = "{success:'true',msg:'0'}";//排序重複 } else if (_memberLevel.DistinctCode(query)) { json = "{success:'true',msg:'2'}";//級別代碼重複 } else { if (_memberLevel.SaveMemberLevel(query) > 0) { json = "{success:'true',mag:'1'}";//保存成功 } else { json = "{success:'false'}";//保存失敗 } } } } 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 bool DistinctSeq(MemberLevelQuery query) { StringBuilder sql = new StringBuilder(); try { query.Replace4MySQL(); sql.AppendFormat("select ml_seq from member_level where ml_seq='{0}'; ", query.ml_seq); DataTable _dt = _access.getDataTable(sql.ToString()); if (_dt.Rows.Count > 0) { return true; } else { return false; } } catch (Exception ex) { throw new Exception("MemberLevelDao-->DistinctSeq-->" + ex.Message + sql.ToString(), ex); } }