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