public StatisticalOrder StatisticalCount()
{
StatisticalOrder statisticalOrder = new StatisticalOrder();
using (var dbContext = new LSSDataContext())
{
//获取正在使用的订单数。(开始时间<当前时间<结束时间)
string nowTempTime1 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
DateTime nowTime1 = Convert.ToDateTime(nowTempTime1);
statisticalOrder.usingOrderCount = (from o in dbContext.Order
where o.Ostime < nowTime1 && o.Oetime >= nowTime1
select o).ToList().Count;
//获取今明两天的订单总数
string nowTempTime2 = DateTime.Now.Date.ToString("yyyy-MM-dd");
DateTime nowTime2 = Convert.ToDateTime(nowTempTime2);
statisticalOrder.todayAndMorningOrderCount = (from o in dbContext.Order
where nowTime2 <= o.Ostime
select o).ToList().Count;
//获取违约订单总数
statisticalOrder.ViolationOrderCount = dbContext.Order.Where(x => x.Ostate == "00").ToList().Count;
//预约订单数(当前时间<开始时间)
statisticalOrder.bookOrderCount = (from o in dbContext.Order
where nowTime1 < o.Ostime
select o).ToList().Count;
}
return(statisticalOrder);
}
public string SeatState(int date, string seatid)
{
using (var context = new LSSDataContext())
{
//查询状态字段，
string str = null;
string str3 = null;
Seat seat = new Seat();
seat = context.Seat.FirstOrDefault(x => x.Tid == seatid);
if (seat != null)
{
str = seat.Tstate;
char[] str2 = new char[24];
if (date == 0)
{
str2 = str.Substring(0, 24).ToCharArray();
}
else
{
str2 = str.Substring(24, 24).ToCharArray();
}
str3 = new string(str2);
}
//判断是否是当天的状态
return(str3);
}
}
/// <summary>
///
/// 查询密码
/// </summary>
/// <param name="user">邮箱或学号</param>
/// <returns>密文密码</returns>
public string GetPassword(string user)
{
//正则匹配（区分邮箱和学号）IsStudentId（）
//根据对应的数据查询密码
string stu = null;
Student student = new Student();
using (var context = new LSSDataContext())
{
Help h = new Help();
if (h.IsStudentId(user))
{
student = context.Student.Single(b => b.Sid == user);
}
else
{
student = context.Student.Single(b => b.Semail == user);
}
if (student != null)
{
stu = student.Spassword;
}
}
return(stu);
}
public bool updateLibrary(int lid, string name)
{
bool flag = false;
using (var dbContext = new LSSDataContext())
{
//修改数据库信息最好有一些事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
var library = dbContext.Library.Where(x => x.Lid == lid).ToList().First();
library.Lname = name;
dbContext.Library.Update(library);
dbContext.SaveChanges();
transaction.Commit();
flag = true;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback();
}
return(flag);
}
}
}
/// <summary>
/// 查询某图书馆，某楼层，当前时间的所有座位的使用状况
/// </summary>
/// <param name="libraryName">传入图书馆名</param>
/// <param name="floor">传入楼层</param>
/// <param name="1">已预约或者使用中</param>
/// <param name="0">未预约</param>
/// <returns></returns>
public List <LibrarySeats> QueryAllLibrarySeats(string libraryName, int floor)
{
using (var dbContext = new LSSDataContext())
{
Library li = new Library(); //准备接受
List <LibrarySeats> librarySeats = new List <LibrarySeats>(); //准备接受
li = dbContext.Library.Where(x => x.Lname == libraryName).FirstOrDefault(); //根据图书馆名获取图书馆信息
if (li != null) //该图书馆不为空
{
var seats = (from list in dbContext.Seat
where list.Lid == li.Lid && list.Tfloor == floor
select list).GetEnumerator(); //获取该图书馆某楼层的所有座位
while (seats.MoveNext())
{ //遍历所有座位
LibrarySeats librarySeat = new LibrarySeats();
string s = DiscriminationStatus(seats.Current.Tstate); //获得相应时间下的座位状态
librarySeat.Tstate = s;
librarySeat.libraryName = libraryName;
librarySeat.Tfloor = floor;
librarySeat.Tid = seats.Current.Tid;
librarySeats.Add(librarySeat);
}
}
return(librarySeats);
}
}
public bool SetSeatState(string id, string operation)
{
bool flag = false;
using (var dbContext = new LSSDataContext())
{
Seat seat = new Seat();
//修改数据库信息最好有一些事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
seat = dbContext.Seat.Where(x => x.Tid == id).FirstOrDefault();
if (seat != null)
{
seat.Tstate = operation;
dbContext.Seat.Update(seat);
dbContext.SaveChanges();
transaction.Commit();
flag = true;
}
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
}
return(flag);
}
}
/// <summary>
/// 根据学生学号以及传入的代表第二天的参数 data（1），获取第二天的订单
/// </summary>
/// <param name="stuId">学号</param>
/// <param name="data">代表第二天的参数</param>
/// <returns>第二天的订单（如果不存在，返回 null）</returns>
public Order GetSecondOrder(string stuId, int data = 1)
{
//查询并封装为order对象
string s2 = DateTime.Now.AddDays(1).Date.ToString("yyyy-MM-dd") + " 00:00:00";
DateTime day2 = Convert.ToDateTime(s2);
string s1 = DateTime.Now.Date.ToString("yyyy-MM-dd") + " 00:00:00";
DateTime day1 = Convert.ToDateTime(s1);
List <Order> orders = new List <Order>();
using (var dbContext = new LSSDataContext())
{
if (data == 0)
{
var order = (from o in dbContext.Order
where o.Sid == stuId && day1 <= o.Ostime && o.Oetime <= day2
select o).GetEnumerator();
while (order.MoveNext())
{
orders.Add(order.Current);
}
}
else
{
var order = (from o in dbContext.Order
where o.Sid == stuId && day2 <= o.Ostime
select o).GetEnumerator();
while (order.MoveNext())
{
orders.Add(order.Current);
}
}
return(orders.Last());
}
}
/// <summary>
/// 修改该学生的积分
/// </summary>
/// <param name="studentid">学生id</param>
public bool AddGlory(string studentid)
{
//给该学生的信誉积分加1
Student student = new Student();
bool flag = false;
using (var context = new LSSDataContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
student = context.Student.FirstOrDefault(x => x.Sid == studentid);
if (student != null)
{
student.Svalue += student.Svalue;
context.Student.Update(student);
context.SaveChanges();
transaction.Commit();
flag = true;
}
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
return(flag);
}
}
}
public bool UpdataStudentFen(string sid)
{
using (var dbContext = new LSSDataContext())
{
bool flag = false;
//修改数据库信息最好有一些事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
var sql = @"Update Student SET Svalue = {0} WHERE Sid = {1}";
dbContext.Database.ExecuteSqlCommand(sql, 2, sid);
dbContext.SaveChanges();
transaction.Commit();
flag = true;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback();
}
return(flag);
}
}
}
/// <summary>
/// 添加图书馆
/// </summary>
/// <param name="library">图书馆对象</param>
public bool AddLibrary(Library library)
{
bool flag = false;
using (var context = new LSSDataContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
context.Add(library);
context.SaveChanges();
transaction.Commit();
flag = true;
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
return(false);
}
}
//将除id之外的字段添加进图书馆表中
}
public bool deleteSb(string sid)
{
bool flag = false;
using (var dbContext = new LSSDataContext())
{
//修改数据库信息最好有一些事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
var student = dbContext.Student.Where(x => x.Sid == sid).ToList().First();
dbContext.Student.Remove(student);
dbContext.SaveChanges();
transaction.Commit();
flag = true;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
transaction.Rollback();
}
return(flag);
}
}
}
/// <summary>
/// 修改该图书馆的起始楼层和结束楼层
/// </summary>
/// <param name="libraryname">图书馆名称</param>
/// <param name="startfloor">起始楼层</param>
/// <param name="endfloor">结束楼层</param>
public bool AddFloor(string libraryname, int startfloor, int endfloor)
{
//修改该图书馆的起始楼层和结束楼层
Library library = new Library();
bool flag = false;
using (var context = new LSSDataContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
library = context.Library.FirstOrDefault(x => x.Lname == libraryname);
if (library != null)
{
library.Lsfloor = startfloor;
library.Lefloor = endfloor;
context.Library.Update(library);
context.SaveChanges();
transaction.Commit();
flag = true;
}
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
return(flag);
}
}
}
/// <summary>
/// 修改学生的积分
/// </summary>
/// <param name="a">目标值</param>
public bool ResetGlory(int a)
{
//将所有学生的积分值都修改为a
List <Student> student = new List <Student>();
using (var context = new LSSDataContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
student = (from s in context.Student
select s
).ToList();
for (int i = 0; i < student.LongCount(); i++)
{
student[i].Svalue = a;
context.Student.Update(student[i]);
}
context.SaveChanges();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 向管理员表中写入该管理员的新密码
/// </summary>
/// <param name="username">管理员名</param>
/// <param name="newpassword">新密码</param>
public bool ChangePassword(string userId, string newpassword)
{
//直接写密码（管理员表）
bool flag = false;
using (var dbContext = new LSSDataContext())
{
Administor adminstor = new Administor();
//修改数据库信息最好有一些事务操作
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
adminstor = dbContext.Administor.Where(x => x.Aid == userId).FirstOrDefault();
if (adminstor != null)
{
adminstor.Apassword = newpassword;
dbContext.Administor.Update(adminstor);
dbContext.SaveChanges();
transaction.Commit();
flag = true;
}
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
}
return(flag);
}
}
/// <summary>
/// 根据ID直接改变订单状态
/// </summary>
/// <param name="oid">订单ID</param>
public bool ChangeOrderState(string oid, string operation)
{
//根据ID直接改变订单状态
bool flag = false;
using (var context = new LSSDataContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
var order = context.Order.Where(x => x.Oid == oid).FirstOrDefault();
if (order != null)
{
order.Ostate = operation;
context.Order.Update(order);
context.SaveChanges();
transaction.Commit();
flag = true;
}
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
}
}
return(flag);
}
public List <Seat> QueryAllEmptySeat(string libraryName, int floor, int startTime, int endTime, int data)
{
using (var dbContext = new LSSDataContext())
{
Library li = new Library(); //准备接受
List <Seat> seat = new List <Seat>(); //准备接受
//int starts = (int)DateTime.Now.Hour;
//int start = (int)startTime.Hour;//提取开始的时间在座位状态中的位置
//int end = (int)endTime.Hour;//提取结束的时间在座位状态中的位置
li = dbContext.Library.Where(x => x.Lname == libraryName).FirstOrDefault(); //根据图书馆名获取图书馆信息
if (li != null) //该图书馆不为空
{
var seats = (from list in dbContext.Seat
where list.Lid == li.Lid && list.Tfloor == floor
select list).GetEnumerator(); //获取该图书馆某楼层的所有座位
char[] a = new char[48]; //准备接受座位状态信息
if (data == 0) //第一天
{
while (seats.MoveNext())
{ //遍历所有座位
a = seats.Current.Tstate.ToCharArray();
bool flag = true; //设置标志
for (int i = startTime; i < endTime; i++)
{
if (a[i] != '0')
{
flag = false;//所选的时间段中有一个时间段被占用，则false。表示该座位不符合要求
}
}
if (flag)
{
seat.Add(seats.Current);
}
}
}
else
{
while (seats.MoveNext())
{ //遍历所有座位
a = seats.Current.Tstate.ToCharArray();
bool flag = true; //设置标志
for (int i = 24 + startTime - 1; i < 24 + endTime - 1; i++)
{
if (a[i] != '0')
{
flag = false;//所选的时间段中有一个时间段被占用，则false。表示该座位不符合要求
}
}
if (flag)
{
seat.Add(seats.Current);
}
}
}
}
return(seat);
}
}
public async Task <List <Student> > AllStudent()
{
List <Student> student = new List <Student>();
using (var context = new LSSDataContext())
{
var students = await context.Student.ToListAsync();
}
return(student);
}
/// <summary>
/// 根据id查询订单
/// </summary>
/// <param name="orderid">订单id</param>
/// <returns>返回订单对象</returns>
public Order GetOrder(string orderid)
{
//根据id查询一个订单记录，并返回Order对象
using (var context = new LSSDataContext())
{
Order order = new Order();
order = context.Order.FirstOrDefault(x => x.Oid == orderid);
return(order);
}
}
/// <summary>
/// 根据订单id查询订单
/// </summary>
/// <param name="orderid">订单id</param>
/// <returns>返回一个订单</returns>
public Order GetOrderById(string orderid)
{
Order order = new Order();
using (var context = new LSSDataContext())
{
order = context.Order.FirstOrDefault(x => x.Oid == orderid);
}
return(order);
}
/// <summary>
/// 按座位号查询座位封装为对象
/// </summary>
/// <param name="seatid">座位号</param>
/// <returns>返回座位对象</returns>
public Seat ReferSeatById(string seatid)
{
//直接在表中进行座位查询
Seat seat = new Seat();
using (var dbContext = new LSSDataContext())
{
seat = dbContext.Seat.Where(x => x.Tid == seatid).FirstOrDefault();
}
return(seat);
}
public Student QueryStudentSql(string sid, string password)
{
Student student = new Student();
using (var context = new LSSDataContext())
{
student = context.Student.FromSqlRaw("SELECT * FROM LSSData.Student")
.ToList().FirstOrDefault();
}
return(student);
}
/// <summary>
/// 根据图书馆 ID 返回一个图书馆对象
/// </summary>
/// <param name="libraryId">图书馆 ID</param>
/// <returns>图书馆对象</returns>
public Library GetLibraryById(int libraryId)
{
Library library = new Library();
using (var context = new LSSDataContext())
{
library = (from s in context.Library
where s.Lid == libraryId
select s).ToList().FirstOrDefault();
}
return(library);
}
public List <Student> QueryStudentsByPassword(string sid)
{
List <Student> student;
using (var context = new LSSDataContext())
{
student = context.Student.Where(b => b.Sid.Contains(sid)).ToList();
//var student = context.Student.Where(p => p.Sid.Contains("16")).ToList().GetEnumerator();
//while (student.MoveNext()) students.Add(student.Current);
}
return(student);
}
/// <summary>
/// 根据seatid获取该seat对象
/// </summary>
/// <param name="seatid"></param>
/// <returns>一个seat对象</returns>
public Seat GetSeat(string seatid)
{
Seat seat = new Seat();
using (var context = new LSSDataContext())
{
seat = (from s in context.Seat
where s.Tid == seatid
select s).ToList().FirstOrDefault();
}
return(seat);
}
/// <summary>
/// 获取所有的图书馆名称
/// </summary>
/// <returns>返回所有的图书馆名称集</returns>
public List <string> GetAllLibraryName()
{
List <string> libraryname = new List <string>();
Library library = new Library();
using (var context = new LSSDataContext())
{
libraryname = (from l in context.Library
select l.Lname).ToList();
}
return(libraryname);
}
/// <summary>
/// 在订单表中插入一条记录
/// </summary>
/// <param name="order"></param>
public bool SetOrder(Order order)
{
//插入一条订单记录到订单表（id,学号,座位号，订单开始时间，结束时间，打卡时间，订单状态）
//订单id（图书馆号+层号+座位号+系统时间）
bool flag = false;
using (var context = new LSSDataContext())
{
context.Order.Add(order);
context.SaveChanges();
flag = true;
}
return(flag);
}
/// <summary>
/// 修改学生邮箱
/// </summary>
/// <param name="username"></param>
/// <param name="newemail"></param>
/// <returns></returns>
public bool ChangeEmail(string username, string newemail)
{
Help h = new Help();
bool flags = false;
//正则表达匹配用户名，学号true
bool flag = h.IsStudentId(username);
Student s = new Student();
using (var context = new LSSDataContext())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
if (!flag)
{
s = context.Student.FirstOrDefault(x => x.Sid == username);
if (s != null)
{
s.Semail = newemail;
context.Student.Update(s);
context.SaveChanges();
transaction.Commit();
flags = true;
}
//修改对应邮箱，返回true
}
else
{
s = context.Student.FirstOrDefault(x => x.Semail == username);
if (s != null)
{
s.Semail = newemail;
context.Student.Update(s);
context.SaveChanges();
transaction.Commit();
flags = true;
}
//修改邮箱，返回false
}
}
catch (Exception e)
{
transaction.Rollback();
return(false);
}
return(flags);
}
}
}
/// <summary>
/// 根据学号获取学生的信誉积分
/// </summary>
/// <param name="stuId">学号</param>
/// <returns>信誉积分</returns>
public int GetGlory(string stuId)
{
using (var context = new LSSDataContext())
{
int level = 0;
Student student = new Student();
student = context.Student.FirstOrDefault(x => x.Sid == stuId);
if (student != null)
{
level = (int)student.Svalue;
}
return(level);
}
}
/// <summary>
/// 根据学号查询该学生的email
/// </summary>
/// <param name="id">学号</param>
/// <returns>返回email</returns>
public string getEmailById(string id)
{
string str = null;
using (var dbContext = new LSSDataContext())
{
Student student = dbContext.Student.FirstOrDefault(x => x.Sid == id);
if (student != null)
{
str = student.Semail;
}
}
return(str);
}
/// <summary>
///
/// 查询密码
/// </summary>
/// <param name="user">工号</param>
/// <returns>密文密码</returns>
public string GetPassword(string user)
{
using (var context = new LSSDataContext())
{
string str = null;
Administor administor = new Administor();
administor = context.Administor.FirstOrDefault(b => b.Aid == user);
if (administor != null)
{
str = administor.Apassword;
}
return(str);
}
}
