public DataTable GetTicketMasterList(TicketMasterQuery tm, out int totalCount)
{
try
{
DataTable _dt = _tmDao.GetTicketMasterList(tm, out totalCount);
_dt.Columns.Add("s_order_createdate", typeof(string));
for (int i = 0; i < _dt.Rows.Count; i++)
{
if (tm.isSecret == "true")
{
_dt.Rows[i]["order_name"] = _dt.Rows[i]["order_name"].ToString().Substring(0, 1) + "**";
}
string time = _dt.Rows[i]["order_createdate"].ToString();
DateTime longTime = CommonFunction.GetNetTime(long.Parse(time));
_dt.Rows[i]["s_order_createdate"] = CommonFunction.DateTimeToString(longTime);
}
// return _tmDao.GetTicketMasterList(tm, out totalCount);
return _dt;
}
catch (Exception ex)
{
throw new Exception("TicketMasterMgr-->GetTicketMasterList" + ex.Message, ex);
}
}
public DataTable GetTicketMasterList(TicketMasterQuery tm, out int totalCount)
{
totalCount = 0;
StringBuilder sqlfield = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
StringBuilder sql = new StringBuilder();
sqlfield.AppendLine(@"select tm.ticket_master_id,td.ticket_detail_id,v.vendor_id,cd.course_detail_name,cd.start_date,cd.end_date,tm.order_name,");
sqlfield.AppendLine(@"tm.order_payment,para.parameterName as 'order_payment_string',tm.order_amount,tm.order_createdate,tm.invoice_status,tm.master_status, para2.remark as 'master_status_string' ,tm.billing_checked,tm.note_admin ");
sqlfield.AppendLine(@",tm.delivery_name,tm.delivery_mobile,tm.delivery_phone,tm.delivery_zip,tm.delivery_address ");
sqlfield.AppendLine(@" ,tm.order_mobile,tm.order_phone,tm.order_zip,tm.order_address");
sqlfrom.AppendLine(@" FROM ticket_master tm");
sqlfrom.Append(" LEFT JOIN ticket_detail td on tm.ticket_master_id=td.ticket_master_id LEFT JOIN vendor v on v.vendor_id=td.vendor_id ");
sqlfrom.Append(" LEFT JOIN course_detail_item cdi on cdi.course_detail_item_id=td.cd_item_id LEFT JOIN course_detail cd on cd.course_detail_id=cdi.course_detail_id ");
sqlfrom.Append(" LEFT JOIN course c on c.course_id=cd.course_id ");
sqlfrom.Append(" LEFT JOIN (select parameterType,parameterCode,parameterName from t_parametersrc where parameterType='payment') para ON para.parameterCode=tm.order_payment ");
sqlfrom.AppendLine(@" LEFT JOIN (select parameterType,parameterCode,remark from t_parametersrc where parameterType='order_status') para2 on para2.parameterCode=tm.master_status ");
sqlwhere.AppendLine(@"WHERE 1=1 ");
if (tm.ticket_master_id != 0)
{
sqlwhere.AppendFormat(@" AND tm.ticket_master_id='{0}' ", tm.ticket_master_id);
}
if (!string.IsNullOrEmpty(tm.order_name))
{
sqlwhere.AppendFormat(@" AND tm.order_name LIKE N'%{0}%' ", tm.order_name);
}
if (tm.master_status != -1)
{
sqlwhere.AppendFormat(@" AND tm.master_status='{0}' ", tm.master_status);
}
if (tm.order_payment != -1)
{
sqlwhere.AppendFormat(@" AND tm.order_payment='{0}' ", tm.order_payment);
}
if (tm.order_start != 0 && tm.order_end != 0)
{
sqlwhere.AppendFormat(@" AND tm.order_createdate>={0} AND tm.order_createdate<={1} ", tm.order_start, tm.order_end);
}
if (tm.course_id != 0)
{
sqlwhere.AppendFormat(@" AND c.course_id='{0}' ", tm.course_id);
}
if (!string.IsNullOrEmpty(tm.course_name))
{
sqlwhere.AppendFormat(@" AND cd.course_detail_name LIKE N'%{0}%' ", tm.course_name);
}
if (tm.start_date != DateTime.MinValue && tm.end_date != DateTime.MinValue)
{
sqlwhere.AppendFormat(@" AND cd.start_date>='{0}' AND cd.end_date<='{1}' ", tm.start_date.ToString("yyyy-MM-dd HH:mm:ss"), tm.end_date.ToString("yyyy-MM-dd HH:mm:ss"));
}
sqlwhere.AppendFormat(@" AND tm.billing_checked='{0}' ", tm.billing_checked);
sql.Append(sqlfield.ToString());
sql.Append(sqlfrom.ToString());
sql.Append(sqlwhere.ToString());
try
{
if (tm.IsPage)
{
sql.AppendFormat(@" ORDER BY tm.ticket_master_id DESC LIMIT {0},{1}; ", tm.Start, tm.Limit);
DataTable dt = _access.getDataTable(" select tm.ticket_master_id " + sqlfrom + sqlwhere);
totalCount = dt.Rows.Count;
}
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("TicketMasterDao-->GetTicketMasterList" + ex.Message + sql.ToString(), ex);
}
}
public string CancelOrderTD(TicketMasterQuery query)//TicketDetail
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" SET SQL_SAFE_UPDATES = 0;update ticket_detail set detail_status='{0}' where ticket_master_id='{1}';SET SQL_SAFE_UPDATES = 1;", query.master_status, query.ticket_master_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("TicketMasterDao-->CancelOrderTM" + ex.Message + sql.ToString(), ex);
}
}
public string CancelOrderTM(TicketMasterQuery query)//TicketMaster
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat(" update ticket_master set master_status='{0}' where ticket_master_id='{1}';", query.master_status,query.ticket_master_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("TicketMasterDao-->CancelOrderTM" + ex.Message + sql.ToString(), ex);
}
}
public HttpResponseBase CancleOrder()
{
string json = string.Empty;
TicketMasterQuery query = new TicketMasterQuery();
List<TicketMasterQuery> list = new List<TicketMasterQuery>();
try
{
if (!string.IsNullOrEmpty(Request.Form["rowID"]))
{
string rowIDs = Request.Form["rowID"];
if (rowIDs.IndexOf("|") != -1)
{
foreach (string id in rowIDs.Split('|'))
{
if (!string.IsNullOrEmpty(id))
{
query = new TicketMasterQuery();
query.master_status = 90;
query.detail_status = 90;
query.ticket_master_id = Convert.ToInt32(id);
list.Add(query);
}
}
}
_ITicketMaster = new TicketMasterMgr(mySqlConnectionString);
json = _ITicketMaster.ExecCancelOrder(list);
}
}
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 = "{failure:true}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetTicketMasterList()
{
string json = string.Empty;
int tranInt = 0;
try
{
List<TicketMasterQuery> store = new List<TicketMasterQuery>();
TicketMasterQuery query = new TicketMasterQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
int totalCount = 0;
_ITicketMaster = new TicketMasterMgr(mySqlConnectionString);
if (!string.IsNullOrEmpty(Request.Params["master_status"]))
{
query.master_status = int.Parse(Request.Params["master_status"]);
}
else
{
query.master_status = -1;
}
if (!string.IsNullOrEmpty(Request.Params["order_payment"]))
{
query.order_payment = int.Parse(Request.Params["order_payment"]);
}
else
{
query.order_payment = -1;
}
if (!string.IsNullOrEmpty(Request.Params["ticket_master_id"]))
{
query.ticket_master_id = int.Parse(Request.Params["ticket_master_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["order_name"]))
{
query.order_name = (Request.Params["order_name"]);
}
if (!string.IsNullOrEmpty(Request.Params["ticket_start"]))
{
query.order_start = (int)CommonFunction.GetPHPTime(Request.Params["ticket_start"]);
}
if (!string.IsNullOrEmpty(Request.Params["ticket_end"]))
{
query.order_end = (int)CommonFunction.GetPHPTime(Request.Params["ticket_end"]);
}
if (!string.IsNullOrEmpty(Request.Params["bill_check"]))
{
query.billing_checked = int.Parse(Request.Params["bill_check"]);
}
if (!string.IsNullOrEmpty(Request.Params["course_search"]))
{
if (int.TryParse(Request.Params["course_search"], out tranInt))
{
query.course_id = tranInt;
}
else
{
query.course_name = Request.Params["course_search"];
}
}
if (!string.IsNullOrEmpty(Request.Params["course_start"]))
{
query.start_date = DateTime.Parse(Request.Params["course_start"]);
}
if (!string.IsNullOrEmpty(Request.Params["course_end"]))
{
query.end_date = DateTime.Parse(Request.Params["course_end"]);
}
if (!string.IsNullOrEmpty(Request.Params["relation_id"]))
{
query.ticket_master_id = int.Parse(Request.Params["relation_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["isSecret"]))
{
query.isSecret = Request.Params["isSecret"].ToString();
}
DataTable dt = _ITicketMaster.GetTicketMasterList(query, out totalCount);
#region wasted code
//dt.Columns.Add("s_order_createdate", typeof(string));
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// if (query.isSecret == "true")
// {
// dt.Rows[i]["order_name"] = dt.Rows[i]["order_name"].ToString().Substring(0, 1) + "**";
// }
// string time = dt.Rows[i]["order_createdate"].ToString();
// DateTime longTime = CommonFunction.GetNetTime(long.Parse(time));
// dt.Rows[i]["s_order_createdate"] = CommonFunction.DateTimeToString(longTime);
//}
#endregion
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{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);
json = "{success:false,msg:0}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}