/// <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);
}
