/// <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);
}
}
}
public static DataTable ShowMsg(string sql)
{
DataTable dt = new DataTable();
try
{
if (sql == "")
{
sql = "1=1";
}
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "select * from Enter_test where " + sql;
string a = "select * from Enter_test where" + sql;
dt = doh.GetDataTable();
doh.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
return(dt);
}
public static bool checkid(string name, string password)
{
bool rst = true;
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "select count (*) from Table_test where name = '" + name + "' and pssword= '" + password + "'";
DataTable dt = doh.GetDataTable();
doh.Dispose();
string r = dt.Rows[0][0].ToString();
if (r == "0")
{
rst = false;
return(rst);
}
return(rst);
}
catch (Exception e)
{
rst = false;
return(rst);
}
}
// 扣费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 void PicThird(string pic, string id)
{
DataTable dt = new DataTable();
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "UPDATE Table_test set image3 ='" + pic + "' where (id ='" + id + "') ";
dt = doh.GetDataTable();
doh.Dispose();
}
catch (Exception ex)
{
string result = ex.Message;
}
}
/// <summary>
/// 将相关信息返回给前台
/// </summary>
/// <param name="name"></param>
/// <param name="password"></param>
/// <param name="id"></param>
/// <returns></returns>
public static DataTable getToMsg(string name, string password)
{
DataTable dt = new DataTable();
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = " select * from Table_test where name = '" + name + "' and password = '******'";
dt = doh.GetDataTable();
doh.Dispose();
return(dt);
}
catch (Exception e)
{
return(dt);
}
}
public static DataTable SearchById(string id)
{
DataTable dt = new DataTable();
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "select * from Enter_test where id ='" + id + "'";
dt = doh.GetDataTable();
doh.Dispose();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(dt);
}
public static DataTable gettoMsg(string pageNow)
{
DataTable dt = new DataTable();
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "select top 2* from Enter_test as AA where id not in (select top " + (int.Parse(pageNow) - 1) * 2 + " id from Enter_test)";
dt = doh.GetDataTable();
doh.Dispose();
return(dt);
}
catch (Exception e)
{
return(dt);
}
}
public DataTable image1(string id)
{
DataTable dt = new DataTable();
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = " select * from Table_test where id = '" + id + "'";
dt = doh.GetDataTable();
doh.Dispose();
return(dt);
}
catch (Exception ex)
{
return(dt);
}
}
/// <summary>
/// 显示所有数据
/// </summary>
/// <returns></returns>
public static DataTable Select()
{
DataTable dt = new DataTable();
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "select * from course";
dt = doh.GetDataTable();
doh.Dispose();
return(dt);
}
catch (Exception ex)
{
return(dt);
}
}
public static bool Update(string name)
{
bool rst = false;
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "UPDATE Table_test set name ='" + name + "' where (Password ='******') ";
doh.ExecuteSqlNonQuery();
doh.Dispose();
rst = true;
return(rst);
}
catch (Exception e)
{
return(rst);
}
}
public static bool Added(string name, string Password, string id)
{
bool rst = false;
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "insert into Table_test (name,Password,id) values ('" + name + "'," + Password + ",'" + id + "')";
doh.ExecuteSqlNonQuery();
doh.Dispose();
rst = true;
return(rst);
}
catch (Exception e)
{
return(rst);
}
}
public static bool Added(string contend, string name, string time)
{
bool rst = false;
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "insert into Table_yan (contend,name,time) values ('" + contend + "','" + name + "','" + time + "')";
doh.ExecuteSqlNonQuery();
doh.Dispose();
rst = true;
return(rst);
}
catch (Exception e)
{
return(rst);
}
}
public static bool Deleteing(string name)
{
bool rst = false;
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "DELETE FROM Table_2 WHERE name = '" + name + "' ";
doh.ExecuteSqlNonQuery();
doh.Dispose();
rst = true;
return(rst);
}
catch (Exception e)
{
return(rst);
}
}
/// <summary>
/// 将注册的用户更新到表中
/// </summary>
/// <param name="name"></param>
/// <param name="password"></param>
/// <param name="id"></param>
/// <returns></returns>
public static bool insertToTabel(string name, string password)
{
bool rst = false;
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = " set rowcount 1 update Table_test set name = '" + name + "' where (name is null or name = '') and password = '******' and id = 1 '" + "'";
doh.ExecuteSqlNonQuery();
doh.Dispose();
rst = true;
return(rst);
}
catch (Exception e)
{
return(rst);
}
}
/// <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);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string result = "";
string type = "";
string total = "";
int pageSize = 2;
try
{
string poststr = getpost();
if (!string.IsNullOrEmpty(poststr))
{
JObject jobj = JObject.Parse(poststr);
type = jobject(jobj, "type");
string namevalue = jobject(jobj, "name");
string contendvalue = jobject(jobj, "content");
string pageNow = jobject(jobj, "pageNow");
string id = jobject(jobj, "id");
switch (type)
{
case "liuyan":
break;
case "all":
DataTable dt = gettoMsg(pageNow);
total = dt.Rows.Count.ToString();
result = "{\"code\":\"0\",\"total\" :\"" + total + "\",\"data\":" + DataTableToJson(dt) + "}";
break;
case "search":
string bstructure = " id|name|creditCode|regStatus|regDate";
string sql = ArrayKeyWord(contendvalue, bstructure);
DataTable dtMsg = ShowMsg(sql);
total = dtMsg.Rows.Count.ToString();
result = "{\"code\":\"0\",\"total\" :\"" + total + "\",\"data\":" + DataTableToJson(dtMsg) + "}";
break;
case "byId":
DataTable dt_id = SearchById(id);
total = dt_id.Rows.Count.ToString();
result = "{\"code\":\"0\",\"total\" :\"" + total + "\",\"data\":" + DataTableToJson(dt_id) + "}";
break;
case "dele":
string[] RegisterCodes = new string[5] {
"9", "8", "4", "6", "3"
};
var flag = true;
var registerCodesStr = string.Empty;
foreach (var registerCode in RegisterCodes)
{
if (string.IsNullOrEmpty(registerCode))
{
continue;
}
var workoreder = registerCode;
if (!flag || workoreder == null)
{
registerCodesStr += registerCode + " ; ";
flag = false;
continue;
}
if (int.Parse(registerCode) > 5)
{
flag = true;
}
else
{
flag = false;
}
if (flag == false)
{
registerCodesStr += registerCode; continue;
}
}
if (!string.IsNullOrEmpty(registerCodesStr))
{
Console.WriteLine("不成功的有:" + registerCodesStr);
}
break;
case "getname":
string urls = "http://*****:*****@"DownLoad\";
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}
var epoch = (DateTime.Now.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
var r1 = new Random().Next(9);
var r2 = new Random().Next(9);
var r3 = new Random().Next(9);
var r4 = new Random().Next(9);
var r = r1 + r2 + r3 + r4;
string filePath;
//只能使用xls格式的excel文件
filePath = dirPath + epoch + r + ".txt";
FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
fs.Write(results.ResultByte, 0, results.ResultByte.Length);
fs.Flush();
fs.Close();
break;
case "list":
string dataSource = "phone=15015051037";
var url = "https://www.e-irobot.com/api/get_phone";
// 使用ASCII码字符
byte[] datalogins = Encoding.ASCII.GetBytes(dataSource);
HttpWebRequest res = (HttpWebRequest)HttpWebRequest.Create(new Uri(url));
res.Method = "Post";
res.ContentType = "application/x-www-form-urlencoded";
res.ContentLength = datalogins.Length;
Stream newstream = res.GetRequestStream();
newstream.Write(datalogins, 0, datalogins.Length);
newstream.Close();
//接收相应
HttpWebResponse resp = (HttpWebResponse)res.GetResponse();
Stream respStream = resp.GetResponseStream();
StreamReader reader = new StreamReader(respStream, Encoding.UTF8);
string resultsss = reader.ReadToEnd();
reader.Close();
res.Abort();
////发送信息
//WebRequest req = WebRequest.Create(url);
//req.Method = "post";
//req.ContentType = "application/x-www-form-urlencoded";
//req.CookieContainer = cookies;//设置cookies
////Accept - Language:zh - CN,zh; q = 0.8
//req.Headers.Add("Accept-Language", "zh - CN,zh; q = 0.8");
////指定客户端代理的方式
////req.Headers.Add("User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2560.0 Safari/537.36");
////添加Post参数
//Stream reqStream = req.GetRequestStream();
//reqStream.Write(data, 0, data.Length);
//reqStream.Close();
////接收相应
//HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
//Stream respStream = resp.GetResponseStream();
//StreamReader reader = new StreamReader(respStream, Encoding.UTF8);
//string resultsss = reader.ReadToEnd();
//reader.Close();
break;
case "excel":
try
{
SqlDbOperHandler doh = new SqlDbOperHandler();
doh.Reset();
doh.SqlCmd = "select from Table_yan";
DataTable excelTab = doh.GetDataTable();
ExportExcel(excelTab);
}
catch (Exception ex)
{
WriteLog(ex, "C:\\Users\\Amazinghua\\Desktop\\log.txt");
}
break;
case "datatable": //excel读入内存表，批量插入数据库
string path = System.Web.HttpContext.Current.Server.MapPath("~/excel/");
string path_zip = path + "gs_0805.xls"; //目标文件
//DataTable fakeTable = ReadExcelToDataTable(path_zip);
//BatchInsertBySqlBulkCopy(fakeTable, "gs_0805");
FileInfo fi = new FileInfo(path_zip);
string test = (fi.Length / 1024).ToString();
if (int.Parse(test) <= 2)
{
}
else
{
}
break;
case "gstest":
break;
}
}
}
catch (Exception ex)
{
WriteLog(ex, "C:\\Users\\Amazinghua\\Desktop\\log.txt");
}
context.Response.Write(result);
context.Response.End();
}
// 退款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);
}