/// <summary>
/// 新站訂單詳情管理:列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetTicketDetailTable(TicketDetailQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
try
{
query.Replace4MySQL();
sql.AppendFormat(@" select td.ticket_detail_id,td.ticket_master_id,td.single_cost,td.single_money,td.single_price,td.vendor_id,cd.course_id,td.product_name,
cd.start_date,cd.end_date ");
sql.AppendFormat(" ,(SELECT spec_name from product_spec ps where pi.product_id=ps.product_id and pi.spec_id_1=ps.spec_id )as spec_id_1 ");
sql.AppendFormat(" ,(SELECT spec_name from product_spec ps where pi.product_id=ps.product_id and pi.spec_id_2=ps.spec_id )as spec_id_2 ");
sql.AppendFormat(" ,(SELECT vendor_name_simple FROM vendor v WHERE v.vendor_id=td.vendor_id)AS vendor_name_simple,ct.flag,ct.ticket_code ");
sqlCondi.AppendFormat(" from ticket_detail td ");
sqlCondi.AppendFormat(" LEFT JOIN course_detail_item cdi ON cdi.course_detail_item_id=td.cd_item_id ");
sqlCondi.AppendFormat(" LEFT JOIN course_detail cd ON cd.course_detail_id=cdi.course_detail_id ");
sqlCondi.AppendFormat(" LEFT JOIN product_item pi ON pi.item_id=cdi.item_id ");
sqlCondi.AppendFormat(" LEFT JOIN course_ticket ct ON ct.ticket_detail_id=td.ticket_detail_id ");
sqlCondi.AppendFormat(" where 1=1 ");
if (query.flag != -1)
{
sqlCondi.AppendFormat(" and ct.flag='{0}' ", query.flag);
}
if (!string.IsNullOrEmpty(query.TimeStart))
{
sqlCondi.AppendFormat(" and cd.start_date>='{0}' ", query.TimeStart);
}
if (!string.IsNullOrEmpty(query.TimeEnd))
{
sqlCondi.AppendFormat(" and cd.end_date<='{0}' ", query.TimeEnd);
}
if (query.MDID != 0)
{
sqlCondi.AppendFormat(" and ( td.ticket_detail_id='{0}' or td.ticket_master_id='{0}' or cd.course_id='{0}') ", query.MDID);
}
sqlCondi.AppendFormat(" ORDER BY td.ticket_detail_id DESC ");
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable("select count(td.ticket_detail_id) as totalCount " + sqlCondi.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlCondi.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
sql.Append(sqlCondi.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("TicketDetailDao.GetTicketDetailList-->" + ex.Message + sql.ToString(), ex);
}
}
public DataTable GetTicketDetailTable(TicketDetailQuery query, out int totalCount)
{
try
{
return _ITicketDetailDao.GetTicketDetailTable(query, out totalCount);
}
catch (Exception ex)
{
throw new Exception("TicketDetailMgr-->GetTicketDetailTable" + ex.Message, ex);
}
}
//#region 課程訂單列表頁
//public HttpResponseBase GetTicketMasterList()
//{
// string json = string.Empty;
// int tranInt = 0;
// try
// {
// 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["order_status"]))
// {
// query.order_status = int.Parse(Request.Params["order_status"]);
// }
// else
// {
// query.order_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["order_id"]))
// {
// query.ticket_master_id = int.Parse(Request.Params["order_id"]);
// }
// 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"].Substring(0, 10) + " 23:59:59");
// }
// 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"].Substring(0, 10) + " 23:59:59");
// }
// DataTable dtTicketMaster = _ITicketMaster.GetTicketMasterList(query, out totalCount);
// IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
// //这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
// timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
// json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dtTicketMaster, 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;
//}
//public HttpResponseBase UpdateTicketMaster()
//{
// string json = string.Empty;
// try
// {
// TicketMaster tm = new TicketMaster();
// _ITicketMaster = new TicketMasterMgr(mySqlConnectionString);
// if (Request.Params["ticket_master_id"] != "0")
// {
// tm.ticket_master_id = int.Parse(Request.Params["ticket_master_id"]);
// }
// tm.delivery_name = Request.Params["delivery_name"];
// tm.delivery_mobile = Request.Params["delivery_mobile"];
// tm.delivery_phone = Request.Params["delivery_phone"];
// tm.delivery_address = Request.Params["delivery_address"];
// if (!string.IsNullOrEmpty(Request.Params["delivery_zip"]))
// {
// tm.delivery_zip = int.Parse(Request.Params["delivery_zip"]);
// }
// tm.order_name = Request.Params["order_name"];
// tm.order_mobile = Request.Params["order_mobile"];
// tm.order_phone = Request.Params["order_phone"];
// tm.order_address = Request.Params["order_address"];
// if (!string.IsNullOrEmpty(Request.Params["order_zip"]))
// {
// tm.order_zip = int.Parse(Request.Params["order_zip"]);
// }
// int i = _ITicketMaster.Update(tm);
// if (i > 0)
// {
// json = "{success:true}";//返回json數據
// }
// else
// {
// json = "{success:false}";//返回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;
//}
//#endregion
#region 課程訂購單明細表
/// <summary>
/// 訂單詳情頁面列表
/// </summary>
/// <returns></returns>
public HttpResponseBase GetTicketDetailList()
{
string jsonStr = string.Empty;
try
{
TicketDetailQuery query = new TicketDetailQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
if (!string.IsNullOrEmpty(Request.Params["limit"]))
{
query.Limit = Convert.ToInt32(Request.Params["limit"]);
}
query.flag = -1;
if (!string.IsNullOrEmpty(Request.Params["Search"]))
{
query.flag = int.Parse(Request.Params["Search"]);
}
if (!string.IsNullOrEmpty(Request.Params["MasterID"]))
{
query.MDID = int.Parse(Request.Params["MasterID"]);
}
if (!string.IsNullOrEmpty(Request.Params["TimeStart"]))
{
query.TimeStart = DateTime.Parse(Request.Params["TimeStart"]).ToString("yyyy/MM/dd 00:00:00");
}
if (!string.IsNullOrEmpty(Request.Params["TimeEnd"]))
{
query.TimeEnd = DateTime.Parse(Request.Params["TimeEnd"]).ToString("yyyy/MM/dd 23:59:59");
}
long a = CommonFunction.GetPHPTime("2014-02-01 00:00:00");
long s = CommonFunction.GetPHPTime("2014-02-28 23:29:59");
int totalCount = 0;
_ITicketDetail = new TicketDetailMgr(mySqlConnectionString);
DataTable _dt = _ITicketDetail.GetTicketDetailTable(query, out totalCount);
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>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetTicketDetailAllCodeTable(TicketDetailQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
try
{
sql.AppendFormat(@"select tm.ticket_master_id,c.course_name,cd.start_date,cd.end_date,ct.ticket_code,ct.flag,");
sql.AppendFormat("(SELECT spec_name from product_spec ps where pi.product_id=ps.product_id and pi.spec_id_1=ps.spec_id )as spec_id_1, ");
sql.AppendFormat("(SELECT spec_name from product_spec ps where pi.product_id=ps.product_id and pi.spec_id_2=ps.spec_id )as spec_id_2 ");
sqlCondi.AppendFormat(" from ticket_detail td ");
sqlCondi.AppendFormat(" LEFT JOIN ticket_master tm ON td.ticket_master_id=tm.ticket_master_id ");
sqlCondi.AppendFormat(" LEFT JOIN course_ticket ct ON ct.ticket_detail_id=td.ticket_detail_id ");
sqlCondi.AppendFormat(" LEFT JOIN course_detail_item cdi ON cdi.course_detail_item_id=td.cd_item_id ");
sqlCondi.AppendFormat(" LEFT JOIN course_detail cd ON cd.course_detail_id=cdi.course_detail_id ");
sqlCondi.AppendFormat(" LEFT JOIN product_item pi ON pi.item_id=cdi.item_id ");
sqlCondi.AppendFormat("LEFT JOIN course c ON c.course_id=cd.course_id ");
sqlCondi.Append(" where 1=1 ");
if (query.flag != -1)
{
sqlCondi.AppendFormat(" and ct.flag='{0}' ", query.flag);
}
//if (!string.IsNullOrEmpty(query.TimeStart))
//{
// sqlCondi.AppendFormat(" and cd.start_date>='{0}' ", query.TimeStart);
//}
//if (!string.IsNullOrEmpty(query.TimeEnd))
//{
// sqlCondi.AppendFormat(" and cd.end_date<='{0}' ", query.TimeEnd);
//}
if (!string.IsNullOrEmpty(query.ticket_code))
{
sqlCondi.AppendFormat(" and ( ct.ticket_code='{0}' or td.ticket_master_id='{0}' ) ", query.ticket_code);
}
sqlCondi.Append(" ORDER BY td.ticket_detail_id DESC ");
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable("select count(td.ticket_detail_id) as totalCount " + sqlCondi.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlCondi.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
sql.Append(sqlCondi.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("TicketDetailDao.GetTicketDetailList-->" + ex.Message + sql.ToString(), ex);
}
}