/// <summary> /// 获取最新的ID /// </summary> /// <returns>ID</returns> private int GetLastID() { DataTable dt = null; try { SqlDbOperHandler doh = new SqlDbOperHandler();//开启连接数据库 doh.Reset(); doh.SqlCmd = "select top(1) [id] from [m_t_application] where 1 = 1 order by ticketCreate desc"; dt = doh.GetDataTable(); //获取返回的表格 doh.Dispose(); //释放资源 } catch (Exception e) { LogClass.CreateLog(e.Message.ToString()); } if (dt == null) { return(-1);//-1 代表无表 } else { if (dt.Rows.Count > 0) { return(Convert.ToInt16(dt.Rows[0]["id"].ToString())); } else { return(0); } } }
// 扣费SQL操作 public string WithdrawMoney(string QRCode) { //返回的字符串 string callBack = null; DataTable dt = null; //判读二维码,并执行相应的操作 int status = IfUsedOrOutTime(QRCode); switch (status) { case 100: callBack = "判读餐票状态错误"; break; case 101: callBack = "已消费,请勿重刷"; break; case 102: callBack = "已退款,请勿重刷"; break; case 1: callBack = "就餐时间已过,请按时就餐"; break; case -1: callBack = "未到就餐时间,请耐心等待"; break; case 0: //判读有效 try { SqlDbOperHandler doh = new SqlDbOperHandler(); //开启连接数据库 doh.Reset(); doh.SqlCmd = "update [m_t_application] set ticketStatus = '已消费',UsedTime = GETDATE() where identification = '" + QRCode + "'"; doh.AddConditionParameter("@identification", QRCode); dt = doh.GetDataTable(); //获取返回的表格 doh.Dispose(); //释放资源 } catch (Exception e) { LogClass.CreateLog(e.Message.ToString()); } callBack = "二维码验证成功"; break; case 404: callBack = "找不到该二维码"; break; default: callBack = "扣费查询错误,请联系行政管理员"; break; } return(callBack); }
// 判断二维码是否已经使用或者过期 public int IfUsedOrOutTime(string QRCode) { int ifHave = 404; //100代表判读状态失误,101代表已消费,102代表已退款 //0代表有效,-1代表未到吃饭时间,1代表超过吃饭时间 DataTable dt = null; DateTime meal = DateTime.Now; try { SqlDbOperHandler doh = new SqlDbOperHandler();//开启连接数据库 doh.Reset(); doh.SqlCmd = "select top(1) [identification],[meal],[ticketStatus] from [m_t_application] where identification = " + QRCode + " order by ticketCreate desc"; dt = doh.GetDataTable(); //获取返回的表格 doh.Dispose(); //释放资源 } catch (Exception e) { LogClass.CreateLog(e.Message.ToString()); } #region 判读餐票状态的逻辑 string ticketStatus = dt.Rows[0]["ticketStatus"].ToString(); if (ticketStatus == "未使用") { #region 判断就餐时间逻辑 string eatType = dt.Rows[0]["meal"].ToString(); string strEat = System.Configuration.ConfigurationManager.AppSettings["EatStrategy"].ToString(); string[] EatStrategy = strEat.Split('|'); foreach (string type in EatStrategy) { if (type == eatType) { ifHave = EatDateTime(type, meal, ifHave); } } #endregion } else if (ticketStatus == "已消费") { ifHave = 101; } else if (ticketStatus == "已退款") { ifHave = 102; } else { ifHave = 100; } #endregion return(ifHave); }
public string InsertTickets(DateTime meal_date, string meal_location, string meal_type, string meal, decimal amount, string operatorMan, int TicketCount) { //返回的字符串 StringBuilder CallbackStr = new StringBuilder(); //餐票生成的时间 DateTime ticketCreate = DateTime.Now; //餐票状态 //string ticketStatus //二维码标识 yyyy-MM-dd Random(6) id string identification = null; //生成失败的数量 int errorCount = 0; //生成二维码的唯一ID int OrderID = GetLastID() + 1; if (OrderID > 0) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < TicketCount; i++) { identification = ticketCreate.ToString("yyyyMMdd") + GetRandomString(6) + (OrderID + i).ToString(); //插入数据库 int error = InsertTicketsSql(meal_date, meal_location, meal_type, meal, amount, identification, operatorMan, ticketCreate); errorCount += error; if (error == 0) { sb.Append("|" + identification); } LogClass.CreateLog("------------- 餐票生成 -------------"); LogClass.CreateLog(operatorMan + "插入餐票:" + identification); LogClass.CreateLog("------------- 餐票生成 -------------"); } CallbackStr.Append(errorCount); CallbackStr.Append(sb.ToString()); } else { CallbackStr.Append("404|生成二维码时未检索到表的存在"); } return(CallbackStr.ToString()); }
/// <summary> /// 返回此次生成的二维码 /// </summary> /// <param name="meal_date">就餐日期</param> /// <param name="TicketCount">生成数量</param> /// <returns></returns> public DataTable SelectQRCode(DateTime meal_date, int TicketCount) { DataTable dt = null; try { SqlDbOperHandler doh = new SqlDbOperHandler();//开启连接数据库 doh.Reset(); doh.SqlCmd = "select top(@TicketCount) * from [m_t_application] where meal_date = @meal_date order by ticketCreate desc"; doh.AddConditionParameter("@TicketCount", TicketCount); doh.AddConditionParameter("@meal_date", meal_date.ToString("yyyy-MM-dd")); dt = doh.GetDataTable(); //获取返回的表格 doh.Dispose(); //释放资源 } catch (Exception e) { LogClass.CreateLog(e.Message.ToString()); } return(dt); }
private int InsertTicketsSql(DateTime meal_date, string meal_location, string meal_type, string meal, decimal amount, string identification, string operator_man, DateTime ticketCreate) { try { SqlDbOperHandler doh = new SqlDbOperHandler();//开启数据库连接 doh.Reset(); doh.SqlCmd = String.Format("insert into [m_t_application] (meal_date,meal_location,meal_type,meal,amount,identification,operator_man,ticketStatus,ticketCreate)" + " values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');", meal_date.Date, meal_location, meal_type, meal, amount.ToString("#0.00"), identification, operator_man, "未使用", ticketCreate); //"insert into [m_t_application] (meal_date,meal_location,,meal_type,meal,amount,identification,operator_man,ticketStatus,ticketCreate) values();";//sql语句 doh.ExecuteSqlNonQuery(); //执行不返回的方法 doh.Dispose(); //释放资源 } catch (Exception e) { //自己写的打印日志的工具类 LogClass.CreateLog("error:" + e.Message.ToString()); return(1); } return(0); }
// 退款SQL操作 public string Refund(string QRCode) { //返回的字符串 string callBack = null; DataTable dt = null; //判读二维码,并执行相应的操作 int status = IfUsedOrOutTime(QRCode); switch (status) { case 100: callBack = "判读餐票状态错误"; break; case 101: callBack = "已消费,请勿重刷"; break; case 102: callBack = "已退款,请勿重刷"; break; case 1: callBack = "就餐时间已过,无法退款"; break; case -1: try { SqlDbOperHandler doh = new SqlDbOperHandler(); doh.Reset(); doh.SqlCmd = "update [m_t_application] set ticketStatus = '已退款',UsedTime = GETDATE() where identification = '" + QRCode + "'"; dt = doh.GetDataTable(); doh.Dispose(); } catch (Exception e) { LogClass.CreateLog(e.Message.ToString()); } finally { callBack = "退款成功"; } break; case 0: //判读有效 try { SqlDbOperHandler doh = new SqlDbOperHandler(); doh.Reset(); doh.SqlCmd = "update [m_t_application] set ticketStatus = '已退款',UsedTime = GETDATE() where identification = '" + QRCode + "'"; dt = doh.GetDataTable(); doh.Dispose(); } catch (Exception e) { LogClass.CreateLog(e.Message.ToString()); } finally { callBack = "退款成功"; } break; case 404: callBack = "找不到该二维码"; break; default: callBack = "退款查询错误,请联系行政管理员"; break; } return(callBack); }