/// <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="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; }