/// <summary> /// 會員活動列表查詢 /// </summary> /// <param name="query"></param> /// <param name="totalCount"></param> /// <returns></returns> public List<MemberEventQuery> Query(MemberEventQuery query, out int totalCount) { StringBuilder strSql = new StringBuilder(); StringBuilder strCondition = new StringBuilder(); StringBuilder strSelect = new StringBuilder(); StringBuilder strTemp = new StringBuilder(); try { query.Replace4MySQL(); totalCount = 0; strSelect.AppendFormat(@"select me.rowID,me.me_name,me.me_desc,me.me_startdate,me.me_enddate,me.et_id,me.me_birthday,me.event_id, me.me_big_banner,me.me_banner_link, "); strSelect.AppendFormat(@"me.me_bonus_onetime,me.ml_code,me.me_status,me.k_date,me.k_user,"); strSelect.AppendFormat(@"me.m_date,me.m_user,et.et_name,et.et_date_parameter,et.et_starttime,et.et_endtime "); strCondition.AppendFormat(@" from member_event me "); strCondition.AppendFormat(@" left join event_type et on me.et_id=et.et_id "); strCondition.AppendFormat(@" where 1=1 "); if (query.rowID != 0) { strCondition.AppendFormat(@" and me.rowID='{0}' ",query.rowID); } if (!string.IsNullOrEmpty(query.ml_name)) { strCondition.AppendFormat(@" AND (me.rowID LIKE N'%{0}%' OR me.me_name LIKE '%{0}%' OR me.me_desc LIKE N'%{0}%') ", query.ml_name); } if (!string.IsNullOrEmpty(query.timestart)) { strCondition.AppendFormat(" and me.k_date>='{0}' ",query.timestart); } if (!string.IsNullOrEmpty(query.timestart)) { strCondition.AppendFormat(" and me.k_date<='{0}' ", query.timeend); } if (query.IsPage) { strSql.AppendFormat(@" select count(rowID) as totalCount "); strSql.AppendFormat(strCondition.ToString()); System.Data.DataTable _dt = _access.getDataTable(strSql.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]); } strSql.AppendFormat(";"); strCondition.AppendFormat(@" order by rowID desc "); } strTemp.AppendFormat(strSelect.ToString()); strTemp.AppendFormat(strCondition.ToString()); strTemp.AppendFormat(@" limit {0},{1}", query.Start, query.Limit); strTemp.AppendFormat(";"); strSql.AppendFormat(strTemp.ToString()); return _access.getDataTableForObj<MemberEventQuery>(strTemp.ToString()); } catch (Exception ex) { throw new Exception("MemberEventDao-->Query-->" + ex.Message + strSql.ToString(), ex); } }
/// <summary> /// 更新狀態 /// </summary> /// <param name="meq"></param> /// <returns></returns> public int UpdateState(MemberEventQuery meq) { try { return _MemberEventDao.UpdateState(meq); } catch (Exception ex) { throw new Exception("MemberEventMgr-->UpdateState-->" + ex.Message, ex); } }
/// <summary> /// 會員活動新增編輯 /// </summary> /// <param name="query"></param> /// <returns></returns> public int MemberEventSave(MemberEventQuery query) { try { return _MemberEventDao.MemberEventSave(query); } catch (Exception ex) { throw new Exception("MemberEventMgr-->MemberEventSave-->" + ex.Message, ex); } }
/// <summary> /// 會員活動列表查詢 /// </summary> /// <param name="store"></param> /// <param name="totalCount"></param> /// <returns></returns> public List<MemberEventQuery> Query(MemberEventQuery store, out int totalCount) { try { return _MemberEventDao.Query(store, out totalCount); } catch (Exception ex) { throw new Exception("MemberEventMgr-->Query-->" + ex.Message, ex); } }
/// <summary> /// 會員活動新增編輯 /// </summary> /// <param name="query"></param> /// <returns></returns> public int MemberEventSave(MemberEventQuery meq) { MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); StringBuilder sql = new StringBuilder(); meq.Replace4MySQL(); int i = 0; try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; //新增 if (meq.rowID == 0) { //獲得自增列下一個ID sql.AppendFormat(@"SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = 'event_type';"); mySqlCmd.CommandText = sql.ToString(); object ob = mySqlCmd.ExecuteScalar(); if (ob != null) { meq.et_id = int.Parse(ob.ToString()); } sql.AppendFormat(@"INSERT INTO member_event (me_name,me_desc,me_startdate,me_enddate,"); sql.AppendFormat(@" et_id,me_birthday,event_id,me_bonus_onetime,ml_code,me_status,"); sql.AppendFormat(@" me_big_banner,me_banner_link,k_date,k_user,m_date,m_user) "); sql.AppendFormat(@" VALUES('{0}','{1}','{2}','{3}',", meq.me_name, meq.me_desc, meq.me_startdate.ToString("yyyy-MM-dd"), meq.me_enddate.ToString("yyyy-MM-dd")); sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}','{5}',", meq.et_id, meq.me_birthday, meq.event_id, meq.me_bonus_onetime, meq.ml_code, meq.me_status); sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}','{5}'); ",meq.me_big_banner,meq.me_banner_link, meq.k_date.ToString("yyyy-MM-dd HH:mm:ss"), meq.k_user, meq.m_date.ToString("yyyy-MM-dd HH:mm:ss"), meq.m_user); sql.AppendFormat(@"INSERT INTO event_type (et_name,et_date_parameter,et_starttime,et_endtime) "); sql.AppendFormat(@"VALUES('{0}','{1}',", meq.et_name, meq.et_date_parameter); sql.AppendFormat(@" '{0}','{1}');", meq.et_starttime, meq.et_endtime); } //編輯 else { sql.AppendFormat(@"UPDATE member_event SET me_name='{0}',me_desc='{1}',", meq.me_name, meq.me_desc); sql.AppendFormat(@"me_startdate='{0}',me_enddate='{1}',", meq.me_startdate.ToString("yyyy-MM-dd"), meq.me_enddate.ToString("yyyy-MM-dd")); sql.AppendFormat(@"me_birthday='{0}',event_id='{1}',", meq.me_birthday, meq.event_id); sql.AppendFormat(@"me_bonus_onetime='{0}',ml_code='{1}',me_big_banner='{2}',me_banner_link='{3}',", meq.me_bonus_onetime, meq.ml_code, meq.me_big_banner,meq.me_banner_link); sql.AppendFormat(@"m_date='{0}',m_user='******',me_status='{2}' ", meq.m_date.ToString("yyyy-MM-dd HH:mm:ss"), meq.m_user,0); sql.AppendFormat(@" WHERE rowID={0};", meq.rowID); sql.AppendFormat(@"UPDATE event_type SET et_name='{0}',et_date_parameter='{1}',", meq.et_name, meq.et_date_parameter); sql.AppendFormat(@"et_starttime='{0}',et_endtime='{1}' ", meq.et_starttime, meq.et_endtime); sql.AppendFormat(@" WHERE et_id='{0}';", meq.et_id); } mySqlCmd.CommandText = sql.ToString(); i = mySqlCmd.ExecuteNonQuery(); mySqlCmd.Transaction.Commit(); } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("MemberEventDao-->MemberEventSave-->" + ex.Message + sql.ToString(), ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } return i; }
public DataTable UpdateRepeat(MemberEventQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("select me.rowID from member_event me where me.event_id='{0}' and ml_code='{1}' and rowID!='{2}';",query.event_id,query.ml_code,query.rowID); return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("MemberEventDao-->UpdateRepeat" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 更新狀態 /// </summary> /// <param name="meq"></param> /// <returns></returns> public int UpdateState(MemberEventQuery meq) { StringBuilder sql = new StringBuilder(); sql.AppendFormat(@"UPDATE member_event SET me_status='{0}',m_date='{1}' ", meq.me_status, meq.m_date.ToString("yyyy-MM-dd HH:mm:ss")); sql.AppendFormat(@" ,m_user='******' WHERE rowID='{1}'; ", meq.m_user, meq.rowID); try { return _access.execCommand(sql.ToString()); } catch (Exception ex) { throw new Exception("MemberEventDao-->UpdateState" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 更改狀態 啟用或者禁用 /// </summary> /// <returns></returns> public JsonResult UpdateState() { int id = 0; int activeValue = 0; _memberEvent = new MemberEventMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["id"])) { id = int.Parse(Request.Params["id"]); } if (!string.IsNullOrEmpty(Request.Params["active"])) { activeValue = int.Parse(Request.Params["active"]); } MemberEventQuery meq = new MemberEventQuery(); meq.rowID = id; meq.me_status = activeValue; meq.m_date = DateTime.Now; meq.m_user = (Session["caller"] as Caller).user_id; if (_memberEvent.UpdateState(meq) > 0) { return Json(new { success = "true", msg = "" }); } else { return Json(new { success = "false", msg = "" }); } }
/// <summary> /// 會員活動新增編輯 /// </summary> /// <returns></returns> public HttpResponseBase SaveMemberEvent() { string json = string.Empty; try { MemberEventQuery query = new MemberEventQuery(); _memberEvent = new MemberEventMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["rowID"])) { query.rowID = int.Parse(Request.Params["rowID"]); } query.me_name = Request.Params["me_name"]; query.me_desc = Request.Params["me_desc"]; query.event_id = Request.Params["event_id"]; if (!string.IsNullOrEmpty(Request.Params["me_startdate"])) { query.me_startdate = DateTime.Parse(Request.Params["me_startdate"]); } if (!string.IsNullOrEmpty(Request.Params["me_enddate"])) { query.me_enddate = DateTime.Parse(Request.Params["me_enddate"]); } if (!string.IsNullOrEmpty(Request.Params["me_birthday"])) { //query.me_birthday = int.Parse(Request.Params["me_birthday"]); if (Request.Params["me_birthday"] == "1") { query.me_birthday = 1; } else { query.me_birthday = 0; } } if (!string.IsNullOrEmpty(Request.Params["me_bonus_onetime"])) { if (Request.Params["me_bonus_onetime"] == "1") { query.me_bonus_onetime = 1; } else { query.me_bonus_onetime = 0; } } if (!string.IsNullOrEmpty(Request.Params["me_banner_link"])) { query.me_banner_link = Request.Params["me_banner_link"]; } if (!string.IsNullOrEmpty(Request.Params["code"])) { query.ml_code = Request.Params["code"].TrimEnd(','); } else { query.ml_code = "0"; } if (!string.IsNullOrEmpty(Request.Params["et_id"])) { query.et_id = int.Parse(Request.Params["et_id"]); } //query.et_name = Request.Params["et_name"]; switch (Request.Params["et_name"]) { case "1": query.et_name = "DD"; query.et_date_parameter = ""; break; case "2": query.et_name = "WW"; if (!string.IsNullOrEmpty(Request.Params["week"])) { query.et_date_parameter = Request.Params["week"].TrimEnd(','); } break; case "3": query.et_name = "MM"; if (!string.IsNullOrEmpty(Request.Params["month"])) { query.et_date_parameter = Request.Params["month"].TrimEnd(','); } break; } #region 處理每天的開始結束時間 query.et_starttime = Request.Params["et_starttime"]; query.et_endtime = Request.Params["et_endtime"]; #endregion query.k_user = (Session["caller"] as Caller).user_id; query.m_user = (Session["caller"] as Caller).user_id; query.k_date = DateTime.Now; query.m_date = query.k_date; string event_json=_memberEvent.IsGetEventID(query.event_id); if (event_json.IndexOf("true") > 0) { #region 判斷數據不能重複 if (_memberEvent.IsRepeat(query))//不重複 { #region 上傳圖片 string path = Server.MapPath(xmlPath); SiteConfigMgr _siteConfigMgr = new SiteConfigMgr(path); SiteConfig extention_config = _siteConfigMgr.GetConfigByName("PIC_Extention_Format"); SiteConfig minValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_Min_Element"); SiteConfig maxValue_config = _siteConfigMgr.GetConfigByName("PIC_Length_MaxValue"); SiteConfig admin_userName = _siteConfigMgr.GetConfigByName("ADMIN_USERNAME"); SiteConfig admin_passwd = _siteConfigMgr.GetConfigByName("ADMIN_PASSWD"); string extention = extention_config.Value == "" ? extention_config.DefaultValue : extention_config.Value; string minValue = minValue_config.Value == "" ? minValue_config.DefaultValue : minValue_config.Value; string maxValue = maxValue_config.Value == "" ? maxValue_config.DefaultValue : maxValue_config.Value; string localMemberEventPath = imgLocalPath + MemberEventPath;//圖片存儲地址 FileManagement fileLoad = new FileManagement(); int totalCount = 0; MemberEventQuery oldQuery = _memberEvent.Query(new MemberEventQuery { rowID = query.rowID }, out totalCount).FirstOrDefault(); if (Request.Files.Count > 0) { HttpPostedFileBase file = Request.Files[0]; string fileName = string.Empty;//當前文件名 string fileExtention = string.Empty;//當前文件的擴展名 fileName = fileLoad.NewFileName(file.FileName); if (fileName != "") { fileName = fileName.Substring(0, fileName.LastIndexOf(".")); fileExtention = file.FileName.Substring(file.FileName.LastIndexOf('.')).ToLower().ToString(); string NewFileName = string.Empty; BLL.gigade.Common.HashEncrypt hash = new BLL.gigade.Common.HashEncrypt(); NewFileName = hash.Md5Encrypt(fileName, "32"); string[] dirPath = new string[2]; //dirPath[0] = NewFileName.Substring(0, 2)+"/"; //dirPath[1] = NewFileName.Substring(2, 2)+"/"; string ServerPath = string.Empty; FTP f_cf = new FTP(); CreateFolder(localMemberEventPath, dirPath); fileName = NewFileName + fileExtention; NewFileName = localMemberEventPath + NewFileName + fileExtention;//絕對路徑 ServerPath = Server.MapPath(imgLocalServerPath + MemberEventPath); string ErrorMsg = string.Empty; //上傳之前刪除已有的圖片 if (query.rowID != 0) { if (oldQuery.me_big_banner != "") { string oldFileName = oldQuery.me_big_banner; CommonFunction.DeletePicFile(ServerPath + oldFileName);//刪除本地圖片 FTP ftp = new FTP(localMemberEventPath, ftpuser, ftppwd); List<string> tem = ftp.GetFileList(); if (tem.Contains(oldFileName)) { FTP ftps = new FTP(localMemberEventPath + oldFileName, ftpuser, ftppwd); ftps.DeleteFile(localMemberEventPath + oldFileName); } } } try { Resource.CoreMessage = new CoreResource("Product");//尋找product.resx中的資源文件 bool result = fileLoad.UpLoadFile(file, ServerPath, NewFileName, extention, int.Parse(maxValue), int.Parse(minValue), ref ErrorMsg, ftpuser, ftppwd); if (result) { query.me_big_banner = 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); } if (!string.IsNullOrEmpty(ErrorMsg)) { string jsonStr = string.Empty; json = "{success:true,msg:\"" + ErrorMsg + "\"}"; this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; } } else { query.me_big_banner = oldQuery.me_big_banner; } } #endregion if (_memberEvent.MemberEventSave(query) > 0) { //json = "{success:true}"; if (!string.IsNullOrEmpty(Request.Params["rowID"])) { json = "{success:true,msg:'修改成功!'}"; } else { json = "{success:true,msg:'新增成功!'}"; } } else { if (!string.IsNullOrEmpty(Request.Params["rowID"])) { json = "{success:false,msg:'修改失敗!'}"; } else { json = "{success:false,msg:'新增失敗!'}"; } } } else { json = "{success:false,msg:'數據重複!'}"; } #endregion } else { json = "{success:false,msg:'促銷編號錯誤或促銷活動未啟用'}"; } } 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,msg:'異常!'}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
/// <summary> /// 列表頁 /// </summary> /// <returns></returns> public HttpResponseBase GetMemberEventList() { MemberEventQuery query = new MemberEventQuery(); List<MemberEventQuery> stores = new List<MemberEventQuery>(); string json = string.Empty; try { query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 _memberEvent = new MemberEventMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["search_content"])) { query.ml_name = Request.Params["search_content"]; } if (!string.IsNullOrEmpty(Request.Params["timestart"])) { query.timestart = Convert.ToDateTime(Request.Params["timestart"].ToString()).ToString("yyyy-MM-dd 00:00:00"); } if (!string.IsNullOrEmpty(Request.Params["timeend"])) { query.timeend = Convert.ToDateTime(Request.Params["timeend"].ToString()).ToString("yyyy-MM-dd 23:59:59"); } int totalCount = 0; stores = _memberEvent.Query(query, out totalCount); foreach (var item in stores) { item.s_me_banner_link = imgServerPath + MemberEventPath + item.me_big_banner; } IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd"; //listUser是准备转换的对象 json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, Formatting.Indented, timeConverter) + "}";//返回json數據 } 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; }
///// <summary> ///// 群組名稱 ///// </summary> ///// <param name="query"></param> ///// <returns></returns> //public string GetMlName(MemberEventQuery query) //{ // try // { // return _MemberEventDao.GetMlName(query); // } // catch (Exception ex) // { // throw new Exception("MemberEventMgr-->GetMlName-->" + ex.Message, ex); // } //} public bool IsRepeat(MemberEventQuery query) { string json = string.Empty; bool result = true; DataTable _dt=new DataTable (); try { if (query.rowID != 0) { _dt = _MemberEventDao.UpdateRepeat(query); } else { _dt = _MemberEventDao.InsertRepeat(query); } if (_dt != null && _dt.Rows.Count > 0) { result = false;//數據重複 } else { result = true;//不重複 } return result; } catch (Exception ex) { throw new Exception("MemberEventMgr-->IsRepeat-->" + ex.Message, ex); } }