public List<CourseTicket> Query(CourseTicket query)
{
StringBuilder sb = new StringBuilder();
try
{
query.Replace4MySQL();
sb.Append(@"select ticket_id, ticket_detail_id ,flag,ticket_code from course_ticket where 1=1 ");
if (query.ticket_id != 0)
{
sb.AppendFormat(" and ticket_id='{0}'", query.ticket_id);
}
if (query.ticket_detail_id != 0)
{
sb.AppendFormat(" and ticket_detail_id='{0}'", query.ticket_detail_id);
}
if (!string.IsNullOrEmpty(query.ticket_code))
{
sb.AppendFormat(" and ticket_code='{0}'", query.ticket_code);
}
return _dbAccess.getDataTableForObj<CourseTicket>(sb.ToString());
}
catch (Exception ex)
{
throw new Exception("CourseTicketDao-->Query" + ex.Message, ex);
}
}
public DataTable GetTicketCode(CourseTicket query, out int totalCount)
{
try
{
DataTable _dt = _courseTicketDao.GetTicketCode(query, out totalCount);
_dt.Columns.Add("course_name");
_dt.Columns.Add("spec_name_1");
_dt.Columns.Add("spec_name_2");
_dt.Columns.Add("start_date");
_dt.Columns.Add("end_date");
if (_dt != null)
{
CourseDao _courseDao = new CourseDao(_conn);
ProductSpecDao _psDao = new ProductSpecDao(_conn);
CourseDetailDao _cdDao = new CourseDetailDao(_conn);
foreach (DataRow item in _dt.Rows)
{
item["course_name"] = _courseDao.Query(new Course { Course_Id = Convert.ToInt32(item["course_id"]) }).FirstOrDefault().Course_Name;
item["spec_name_1"] = _psDao.query(Convert.ToInt32(item["spec_id_1"])).spec_name;
item["spec_name_2"] = _psDao.query(Convert.ToInt32(item["spec_id_2"])).spec_name;
CourseDetail store = _cdDao.QueryModel(new CourseDetail { Course_Id = Convert.ToInt32(item["course_id"]) }).FirstOrDefault();
item["start_date"] = store.Start_Date.ToString();
item["end_date"] = store.End_Date.ToString();
}
}
return _dt;
}
catch (Exception ex)
{
throw new Exception("CourseTicketMgr-->GetTicketCode" + ex.Message, ex);
}
}
public List<CourseTicket> Query(CourseTicket store)
{
try
{
return _courseTicketDao.Query(store);
}
catch (Exception ex)
{
throw new Exception("CourseTicketMgr-->Query" + ex.Message, ex);
}
}
/// <summary>
/// 保存單個課程細項信息
/// </summary>
/// <param name="c">一個CourseDetail對象</param>
/// <returns>受影響的行數</returns>
public string Save(CourseTicket ct)
{
StringBuilder sb = new StringBuilder();
try
{
//sb.Append(@"INSERT INTO course_ticket(`course_detail_id`,`ticket_Code`,`user_id`,`create_date`,`create_user`,`order_id`,`flag`)");
//sb.AppendFormat("VALUES({0},'{1}',{2},{3},{4},{5},{6})", ct.ticket_detail_id, ct.ticket_code, ct.User_Id, ct.Create_Date.ToString("yyyy-MM-dd HH:mm:ss"), ct.User_Id, ct.Order_Id, ct.Flag);
return sb.ToString();
}
catch (Exception ex)
{
throw new Exception("CourseTicketDao-->Save" + ex.Message, ex);
}
}
public bool TicketVerification(CourseTicket store, int user_type)
{
try
{
return _courseTicketDao.TicketVerification(store, user_type);
}
catch (Exception ex)
{
throw new Exception("CourseTicketMgr-->TicketVerification" + ex.Message, ex);
}
}
/// <summary>
/// 保存單個課程細項信息
/// </summary>
/// <param name="c">一個CourseDetail對象</param>
/// <returns>受影響的行數</returns>
public string Save(CourseTicket ct)
{
return _courseTicketDao.Save(ct);
}
public HttpResponseBase GetTicketDetail()
{
CourseTicket query = new CourseTicket();
string json = string.Empty;
int isTranInt = 0;
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");
if (!string.IsNullOrEmpty(Request.Params["select_ticket"]))
{
if (int.TryParse(Request.Params["select_ticket"].Trim(), out isTranInt))
{
query.ticket_detail_id = Convert.ToInt32(Request.Params["select_ticket"]);
}
}
if (!string.IsNullOrEmpty(Request.Params["select_code"]))
{
query.ticket_code = Request.Params["select_code"].Trim();
}
if (!string.IsNullOrEmpty(Request.Params["search_status"]))
{
query.flag = Convert.ToInt32(Request.Params["search_status"]);
}
int totalCount = 0;
_courseTicketMgr = new CourseTicketMgr(mySqlConnectionString);
DataTable _dt = _courseTicketMgr.GetTicketCode(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, Formatting.Indented, timeConverter) + "}";
}
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,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
public string UpdateFlag(CourseTicket store)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("set sql_safe_updates = 0;update course_ticket set flag=1,modify_user='******',modify_date='{2}' where ticket_id='{0}'; set sql_safe_updates = 1;", store.ticket_id, store.modify_user, Common.CommonFunction.DateTimeToString(store.modify_date));
return sql.ToString();
}
/// <summary>
/// 執行核銷動作
/// </summary>
/// <param name="store"></param>
/// <param name="user_type">變更人類型 1 吉甲地管理員 2供應商</param>
/// <returns></returns>
public bool TicketVerification(CourseTicket store, int user_type)
{
StringBuilder sql = new StringBuilder();
try
{
MySqlDao _sqlDao = new MySqlDao(connStr);
ArrayList list = new ArrayList();
list.Add(UpdateFlag(store));
list.Add(string.Format(@"insert into ticket_accept(ticket_id,ticket_detail_id,ta_create_user_type,ta_create_user,ta_create_date)
values('{0}','{1}','{2}','{3}','{4}');", store.ticket_id, store.ticket_detail_id, user_type, store.modify_user,
Common.CommonFunction.DateTimeToString(store.modify_date)));
return _sqlDao.ExcuteSqls(list);
}
catch (Exception ex)
{
throw new Exception("CourseTicketDao-->TicketVerification" + ex.Message, ex);
}
}
/// <summary>
/// 序號核銷列表頁
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetTicketCode(CourseTicket query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlFrom = new StringBuilder();
// StringBuilder sqlWhere = new StringBuilder();
StringBuilder sqlTemp = new StringBuilder();
totalCount = 0;
try
{
query.Replace4MySQL();
#region 條件查詢
if (query.flag != -1)
{
sqlTemp.AppendFormat(" and flag='{0}'", query.flag);
}
if (!string.IsNullOrEmpty(query.ticket_code))
{
sqlTemp.AppendFormat(" and ticket_code='{0}'", query.ticket_code);
}
if (query.ticket_detail_id != 0)
{
sqlTemp.AppendFormat(" and ticket_detail_id='{0}'", query.ticket_detail_id);
}
#endregion
sql.Append(@" SELECT cp.course_id, pi.item_id,pi.spec_id_1,pi.spec_id_2,ct.ticket_code ,ct.flag,ct.ticket_detail_id,ct.ticket_id");
if (sqlTemp.Length > 0)
{
sqlFrom.AppendFormat(" FROM (select ticket_id, ticket_detail_id ,flag,ticket_code from course_ticket where {0} ) as ct", sqlTemp.ToString().TrimStart().Remove(0, 3));
}
else
{
sqlFrom.Append(" FROM course_ticket ct");
}
sqlFrom.Append(@" LEFT JOIN ticket_detail td ON td.ticket_detail_id=ct.ticket_detail_id
LEFT JOIN course_detail_item cdi ON cdi.course_detail_item_id=td.cd_item_id
LEFT JOIN product_item pi ON pi.item_id=cdi.item_id
LEFT JOIN course_product cp ON cp.product_id=pi.product_id");
if (query.IsPage)
{
DataTable _dtCount = _dbAccess.getDataTable("select count(ct.ticket_code) as totalCount " + sqlFrom.ToString());
if (_dtCount.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dtCount.Rows[0]["totalCount"]);
}
sqlFrom.AppendFormat(" LIMIT {0},{1} ;", query.Start, query.Limit);
}
sql.Append(sqlFrom.ToString());
return _dbAccess.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("TicketDetailDao-->GetTicketCode-->" + sql.ToString() + ex.Message, ex);
}
}