public List<TicketReturnQuery> GetTicketReturnList(TicketReturnQuery query,out int totalCount )
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
totalCount = 0;
try
{
sql.Append(" select tr.tr_id,tr.ticket_master_id,tr.tr_note,tr.tr_bank_note,tr.tr_update_user,tr.tr_create_user, tr.tr_create_date,tr.tr_update_date,tr.tr_ipfrom,tr.tr_money,tr.tr_status,para.parameterName as 'tr_reason_type' ");
sqlFrom.Append(" from ticket_return tr ");
sqlFrom.Append(" LEFT JOIN (select parameterType,parameterCode,parameterName from t_parametersrc where parameterType='tr_reason_type') para on para.parameterCode=tr.tr_reason_type ");
sqlWhere.Append(" where 1=1 ");
if (query.start_date != DateTime.MinValue && query.end_date != DateTime.MinValue)
{
sqlWhere.AppendFormat(" and tr_create_date>='{0}' and tr_create_date<='{1}' ", CommonFunction.DateTimeToString(query.start_date),CommonFunction.DateTimeToString(query.end_date));
}
DataTable _dt = _access.getDataTable("select count(tr.tr_id) as 'totalCount' "+sqlFrom.ToString()+sqlWhere.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0][0]);
}
return _access.getDataTableForObj<TicketReturnQuery>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString());
}
catch (Exception ex)
{
throw new Exception("TicketReturnDao-->GetTicketReturnList-->" + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString() + ex.Message, ex);
}
}
public List<TicketReturnQuery> GetTicketReturnList(TicketReturnQuery query, out int totalCount)
{
try
{
return _ticketReturn.GetTicketReturnList(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("TicketReturnMgr-->GetTicketReturnList-->"+ex.Message,ex);
}
}
public string SaveTicketReturn(TicketReturnQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat("set sql_safe_updates=0;update ticket_return set tr_note ='{0}',tr_bank_note='{1}',tr_update_user='******'", query.tr_note, query.tr_bank_note, query.tr_update_user);
sql.AppendFormat(",tr_update_date='{0}',tr_money='{1}', tr_status='{2}',tr_reason_type='{3}'",CommonFunction.DateTimeToString(DateTime.Now),query.tr_money,query.tr_status,query.tr_reason_type);
sql.AppendFormat(" where tr_id='{0}';set sql_safe_updates=1; ", query.tr_id);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("TicketReturnDao-->SaveTicketReturn-->"+sql.ToString()+ex.Message,ex);
}
}
public string SaveTicketReturnLog(TicketReturnQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat("insert into ticket_return_change_log (tr_id,trcl_last_status,trcl_new_status,trcl_new_money,trcl_last_money, ");
sql.AppendFormat("trcl_create_user,trcl_create_date,trcl_note) ");
sql.AppendFormat("values('{0}','{1}','{2}','{3}','{4}',", query.tr_id, query.trcl_last_status, query.tr_status, query.tr_money, query.trcl_last_money);
sql.AppendFormat("'{0}','{1}','{2}');", query.trcl_create_user, CommonFunction.GetPHPTime() , query.tr_note);
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("TicketReturnDao-->SaveTicketReturnLog-->" + sql.ToString() + ex.Message, ex);
}
}
public string SaveTicketReturn(TicketReturnQuery query)
{
string json = string.Empty;
try
{
ArrayList arrList = new ArrayList();
arrList.Add( _ticketReturn.SaveTicketReturn(query));
arrList.Add(_ticketReturn.SaveTicketReturnLog(query));
if (_ticketReturn.ExecTicketReturnSql(arrList))
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
return json;
}
catch (Exception ex)
{
throw new Exception("TicketReturnMgr-->SaveTicketReturn-->" + ex.Message, ex);
}
}
public HttpResponseBase SaveTicketReturn()
{
string json = string.Empty;
try
{
TicketReturnQuery query = new TicketReturnQuery();
if (!string.IsNullOrEmpty(Request.Params["tr_id"]))
{
query.tr_id = Convert.ToInt32(Request.Params["tr_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["ticket_master_id"]))
{
query.ticket_master_id = Convert.ToInt32(Request.Params["ticket_master_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["tr_note"]))
{
query.tr_note = (Request.Params["tr_note"]);
}
if (!string.IsNullOrEmpty(Request.Params["tr_bank_note"]))
{
query.tr_bank_note = (Request.Params["tr_bank_note"]);
}
if (!string.IsNullOrEmpty(Request.Params["tr_money"]))
{
query.tr_money = Convert.ToInt32(Request.Params["tr_money"]);
}
if (!string.IsNullOrEmpty(Request.Params["trcl_last_money"]))
{
query.trcl_last_money = Convert.ToInt32(Request.Params["trcl_last_money"]);
}
if (!string.IsNullOrEmpty(Request.Params["tr_status"]))
{
query.tr_status = Convert.ToInt32(Request.Params["tr_status"]);
}
if (!string.IsNullOrEmpty(Request.Params["trcl_last_status"]))
{
query.trcl_last_status = Convert.ToInt32(Request.Params["trcl_last_status"]);
}
if (!string.IsNullOrEmpty(Request.Params["tr_reason_type"]))
{
query.tr_reason_type = (Request.Params["tr_reason_type"]);
}
query.tr_update_user = (Session["caller"] as Caller).user_id;
query.trcl_create_user = (Session["caller"] as Caller).user_id;
_ticketReturn = new TicketReturnMgr(mySqlConnectionString);
json = _ticketReturn.SaveTicketReturn(query);
}
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}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase TicketReturnList()
{
string json = string.Empty;
try
{
List<TicketReturnQuery> store = new List<TicketReturnQuery>();
TicketReturnQuery query = new TicketReturnQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
int totalCount = 0;
if (!string.IsNullOrEmpty(Request.Params["start_date"]))
{
query.start_date = Convert.ToDateTime(Request.Params["start_date"]);
}
if (!string.IsNullOrEmpty(Request.Params["end_date"]))
{
query.end_date = Convert.ToDateTime(Request.Params["end_date"]);
}
_ticketReturn = new TicketReturnMgr(mySqlConnectionString);
store = _ticketReturn.GetTicketReturnList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, 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;
}