public DataTable GetOrderAccumAmountTable(OrderAccumAmountQuery query, out int totalCount)
{
try
{
return _IOrderAccumAmountDao.GetOrderAccumAmountTable(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("OrderAccumAmountMgr-->GetOrderAccumAmountTable" + ex.Message, ex);
}
}
public int UpdateActive(OrderAccumAmountQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append(_IOrderAccumAmountDao.UpdateActive(query));
return _access.execCommand(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderAccumAmountMgr-->UpdateActive" + ex.Message + sb.ToString(), ex);
}
}
/// <summary>
/// 列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetOrderAccumAmountTable(OrderAccumAmountQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondition = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
try
{
sql.Append(@"select oaa.event_id,oaa.accum_amount,oaa.event_start_time,oaa.event_end_time,oaa.event_desc,oaa.event_name,oaa.event_desc_start,oaa.event_desc_end,oaa.event_status ");
sql.Append(",oaa.event_create_user,oaa.event_create_time,event_update_user,event_update_time,mu1.user_username as create_user,mu2.user_username as update_user ");
sqlCondition.Append(" from order_accum_amount oaa ");
sqlCondition.Append(" left join manage_user mu1 on mu1.user_id=oaa.event_create_user ");
sqlCondition.Append(" left join manage_user mu2 on mu2.user_id=oaa.event_update_user ");
sqlcount.Append("select count(oaa.event_id) as totalCount ");
sqlCondition.Append(" where 1=1 ");
if (query.event_id != 0)
{
sqlCondition.AppendFormat(" and oaa.event_id='{0}' ", query.event_id);
}
if (query.event_status != -1)
{
sqlCondition.AppendFormat(" and oaa.event_status='{0}' ", query.event_status);
}
if (!string.IsNullOrEmpty(query.event_name))
{
sqlCondition.AppendFormat(" and oaa.event_name like N'%{0}%' ", query.event_name);
}
if (query.dateCondition != 0)
{
if (query.event_start_time != DateTime.MinValue)
{
if (query.event_start_time != DateTime.MinValue)
{
switch (query.dateCondition)
{
case 1:
sqlCondition.AppendFormat(" and oaa.event_start_time between '{0}' and '{1}' ", query.event_start_time.ToString("yyyy-MM-dd 00:00:00"), query.event_end_time.ToString("yyyy-MM-dd 23:59:59"));
break;
case 2:
sqlCondition.AppendFormat(" and oaa.event_end_time between '{0}' and '{1}' ", query.event_start_time.ToString("yyyy-MM-dd 00:00:00"), query.event_end_time.ToString("yyyy-MM-dd 23:59:59"));
break;
case 3:
sqlCondition.AppendFormat(" and oaa.event_desc_start between '{0}' and '{1}' ", query.event_start_time.ToString("yyyy-MM-dd 00:00:00"), query.event_end_time.ToString("yyyy-MM-dd 23:59:59"));
break;
case 4:
sqlCondition.AppendFormat(" and oaa.event_desc_end between '{0}' and '{1}' ", query.event_start_time.ToString("yyyy-MM-dd 00:00:00"), query.event_end_time.ToString("yyyy-MM-dd 23:59:59"));
break;
case 5:
sqlCondition.AppendFormat(" and event_update_time between '{0}' and '{1}' ", query.event_start_time.ToString("yyyy-MM-dd 00:00:00"), query.event_end_time.ToString("yyyy-MM-dd 23:59:59"));
break;
}
}
}
}
sqlCondition.Append(" order by oaa.event_id desc ");
totalCount = 0;
if (query.IsPage)
{
sqlcount.Append(sqlCondition.ToString());
DataTable _dt = _access.getDataTable(sqlcount.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlCondition.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
sql.Append(sqlCondition.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderAccumAmountDao.GetOrderAccumAmountTable-->" + ex.Message + sql.ToString() + sqlcount.ToString(), ex);
}
}
/// <summary>
/// 刪除
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string DelOrderAccumAmount(OrderAccumAmountQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("delete from order_accum_amount where event_id in({0});", query.event_id_in);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderAccumAmountDao.DelOrderAccumAmount-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 更改狀態
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string UpdateActive(OrderAccumAmountQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("set sql_safe_updates=0;update order_accum_amount set event_status='{0}',event_update_user='******'", query.event_status, query.event_update_user);
sql.AppendFormat(",event_update_time='{0}' where event_id='{1}';set sql_safe_updates=1;", CommonFunction.DateTimeToString(query.event_update_time), query.event_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderAccumAmountDao.UpdateActive-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 編輯
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string UPOrderAccumAmount(OrderAccumAmountQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendFormat(" set sql_safe_updates=0;update order_accum_amount set accum_amount='{0}',event_start_time='{1}'", query.accum_amount, CommonFunction.DateTimeToString(query.event_start_time));
sb.AppendFormat(",event_end_time='{0}',event_desc='{1}',event_name='{2}' ", CommonFunction.DateTimeToString(query.event_end_time), query.event_desc, query.event_name);
sb.AppendFormat(",event_desc_start='{0}',event_desc_end='{1}' ", CommonFunction.DateTimeToString(query.event_desc_start),CommonFunction.DateTimeToString(query.event_desc_end));
sb.AppendFormat(",event_status='{0}',event_update_user='******'", query.event_status, query.event_update_user);
sb.AppendFormat(",event_update_time='{0}' where event_id='{1}';set sql_safe_updates=1;", CommonFunction.DateTimeToString(query.event_update_time), query.event_id);
return sb.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderAccumAmountDao.UPOrderAccumAmount-->" + ex.Message + sb.ToString(), ex);
}
}
/// <summary>
/// 新增
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public string AddOrderAccumAmount(OrderAccumAmountQuery query)
{
StringBuilder sb = new StringBuilder();
try
{
sb.Append(" insert into order_accum_amount(accum_amount,event_start_time,event_end_time,event_desc,event_name,event_desc_start ");
sb.Append(" ,event_desc_end,event_status,event_create_user,event_create_time,event_update_user,event_update_time)values( ");
sb.AppendFormat("'{0}','{1}','{2}'", query.accum_amount, CommonFunction.DateTimeToString(query.event_start_time), CommonFunction.DateTimeToString(query.event_end_time));
sb.AppendFormat(",'{0}','{1}','{2}'", query.event_desc, query.event_name, CommonFunction.DateTimeToString(query.event_desc_start));
sb.AppendFormat(",'{0}','{1}','{2}'", CommonFunction.DateTimeToString(query.event_desc_end), query.event_status, query.event_create_user);
sb.AppendFormat(",'{0}','{1}','{2}');", CommonFunction.DateTimeToString(query.event_create_time), query.event_update_user, CommonFunction.DateTimeToString(query.event_update_time));
return sb.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderAccumAmountDao.AddOrderAccumAmount-->" + ex.Message + sb.ToString(), ex);
}
}
/// <summary>
/// 會員累計金額列表頁
/// </summary>
/// <returns></returns>
public HttpResponseBase GetOrderAccumAmountList()
{
string jsonStr = string.Empty;
int tranInt = 0;
try
{
OrderAccumAmountQuery query = new OrderAccumAmountQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
if (!string.IsNullOrEmpty(Request.Params["limit"]))
{
query.Limit = Convert.ToInt32(Request.Params["limit"]);
}
query.event_status = -1;
if (int.Parse(Request.Params["event_status"])!=-1)
{
query.event_status = int.Parse(Request.Params["event_status"]);
}
if (!string.IsNullOrEmpty(Request.Params["event_search"]))
{
if (int.TryParse(Request.Params["event_search"], out tranInt))
{
query.event_id = int.Parse(Request.Params["event_search"].ToString());
}
else
{
query.event_name = Request.Params["event_search"];
}
}
if (!string.IsNullOrEmpty(Request.Params["date"]))
{
query.dateCondition = Convert.ToInt32(Request.Params["date"]);
}
if (!string.IsNullOrEmpty(Request.Params["TimeStart"]))
{
query.event_start_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["TimeStart"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["TimeEnd"]))
{
query.event_end_time = Convert.ToDateTime(Convert.ToDateTime(Request.Params["TimeEnd"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
}
int totalCount = 0;
_IOrderAccumAmount = new OrderAccumAmountMgr(mySqlConnectionString);
DataTable _dt = _IOrderAccumAmount.GetOrderAccumAmountTable(query, out totalCount);
//foreach (DataRow item in _dt.Rows)
//{
// string s=item["event_desc_start"].ToString().Substring(0,8);
// if (item["event_desc_start"].ToString().Substring(0, 8) == "0000-000-00")
// {
// }
//}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, 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);
jsonStr = "{success:false,msg:0}";
}
this.Response.Clear();
this.Response.Write(jsonStr.ToString());
this.Response.End();
return this.Response;
}
/// <summary>
/// 會員累積金額刪除
/// </summary>
/// <returns></returns>
public HttpResponseBase DeleteOrderAccumAmount()
{
OrderAccumAmountQuery query = new OrderAccumAmountQuery();
string json = string.Empty;
try
{
string Row_id = "";
if (!string.IsNullOrEmpty(Request.Params["rowId"]))
{
Row_id = Request.Params["rowId"];
query.event_id_in = Row_id.TrimEnd(',');
}
_IOrderAccumAmount = new OrderAccumAmountMgr(mySqlConnectionString);
int result = _IOrderAccumAmount.DelOrderAccumAmount(query);
if (result > 0)
{
json = "{success:true,msg:\"" + result + "\"}";
}
else
{
json = "{success:false,msg:\"" + result + "\"}";
}
}
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:'0'}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 會員累積金額修改狀態
/// </summary>
/// <returns></returns>
public JsonResult UpdateActiveQuestion()
{
try
{
OrderAccumAmountQuery query = new OrderAccumAmountQuery();
if (!string.IsNullOrEmpty(Request.Params["event_id"].ToString()))
{
query.event_id = Convert.ToInt32(Request.Params["event_id"].ToString());
}
query.event_status = Convert.ToInt32(Request.Params["event_status"] ?? "0");
_IOrderAccumAmount = new OrderAccumAmountMgr(mySqlConnectionString);
query.event_update_user = (Session["caller"] as Caller).user_id;
query.event_update_time= DateTime.Now;
if (_IOrderAccumAmount.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" });
}
}
/// <summary>
///會員累積金額新增與編輯
/// </summary>
/// <returns></returns>
public HttpResponseBase SaveOrderAccumAmount()
{
string json = string.Empty;
try
{
OrderAccumAmountQuery query = new OrderAccumAmountQuery();
if (!string.IsNullOrEmpty(Request.Params["event_id"]))
{
query.event_id = Convert.ToInt32(Request.Params["event_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["event_name"]))
{
query.event_name = Request.Params["event_name"];
}
if (!string.IsNullOrEmpty(Request.Params["event_desc"]))
{
query.event_desc = Request.Params["event_desc"];
}
if (!string.IsNullOrEmpty(Request.Params["event_start_time"]))
{
query.event_start_time =DateTime.Parse(Request.Params["event_start_time"]);
}
if (!string.IsNullOrEmpty(Request.Params["event_end_time"]))
{
query.event_end_time = DateTime.Parse(Request.Params["event_end_time"]);
}
if (!string.IsNullOrEmpty(Request.Params["event_desc_start"]))
{
query.event_desc_start = DateTime.Parse(Request.Params["event_desc_start"]);
}
if (!string.IsNullOrEmpty(Request.Params["event_desc_end"]))
{
query.event_desc_end = DateTime.Parse(Request.Params["event_desc_end"]);
}
if (!string.IsNullOrEmpty(Request.Params["accum_amount"]))
{
query.accum_amount = Convert.ToInt32(Request.Params["accum_amount"]);
}
if (!string.IsNullOrEmpty(Request.Params["event_status"]))
{
query.event_status = Convert.ToInt32(Request.Params["event_status"]);
}
query.event_create_user = (Session["caller"] as Caller).user_id;
query.event_update_user = (Session["caller"] as Caller).user_id;
query.event_create_time = DateTime.Now;
query.event_update_time = query.event_create_time;
_IOrderAccumAmount = new OrderAccumAmountMgr(mySqlConnectionString);
int result = 0;
if (query.event_id != 0)//編輯
{
result = _IOrderAccumAmount.UPOrderAccumAmount(query);
}
else //新增
{
result = _IOrderAccumAmount.AddOrderAccumAmount(query);
}
if (result > 0)
{
json = "{\"success\":\"true\",\"msg\":\"保存成功!\"}";
}
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;
}
