public static DataTable GetLectuer(MyMySql sql)
{
string sqlstr = $@"
SELECT * FROM lecture";
return(sql.executeQuery(sqlstr));
}
public static bool ChangePrivillege(MyMySql sql, string userid, string privillege)
{
string SQLstr = $@"
UPDATE users SET userprivillege = {privillege} WHERE userid = {userid}";
return(sql.executeNonQuery(SQLstr) == 1);
}
public static DataTable GetBooks(MyMySql sql)
{
string SQLstr = $@"
SELECT * FROM books;";
return(sql.executeQuery(SQLstr));
}
public static bool AddTag(MyMySql sql, string bookid, string tagid)
{
string sqlstr = $@"
INSERT INTO book_tag(bookid, tagid)
VALUES ({bookid}, {tagid})";
return(sql.executeNonQuery(sqlstr) == 1);
}
public static bool AddBook(MyMySql sql, string bookname, string bookauthor, string bookcount)
{
string SQLstr = $@"
INSERT INTO books (bookname, bookauthor, bookcount)
VALUES ('{bookname.Trim()}','{bookauthor.Trim()}',{bookcount})";
return(sql.executeNonQuery(SQLstr) == 1);
}
public static bool RemoveTag(MyMySql sql, string bookid, string tagid)
{
string sqlstr = $@"
DELETE FROM book_tag
WHERE bookid = {bookid} AND tagid = {tagid}";
return(sql.executeNonQuery(sqlstr) != 0);
}
public static UserDescription GetUserDescription(MyMySql sql, string userid)
{
string sqlstr = $@"
SELECT username, password, sex, contact
FROM users
WHERE userid = {userid}";
DataRow row = sql.executeQueryFirst(sqlstr);
return(new UserDescription(row["username"].ToString(), row["password"].ToString(), int.Parse(row["sex"].ToString()), row["contact"].ToString()));
}
public static bool EditBook(MyMySql sql, string bookid, string bookname, string bookauthor, string bookcount)
{
string SQLstr = $@"
UPDATE books
SET
bookname = '{bookname}',
bookauthor = '{bookauthor}',
bookcount = {bookcount}
WHERE
bookid = {bookid}";
return(sql.executeNonQuery(SQLstr) == 1);
}
public static DataTable getUnAviliableSeats(MyMySql sql, string roomid)
{
string sqlstr = $@"
SELECT
deskid, seatid
FROM
seats
WHERE
roomid = {roomid}
AND backdatetime IS NULL";
return(sql.executeQuery(sqlstr));
}
public static bool Leave(MyMySql sql, string roomid, string deskid, string seatid)
{
string sqlstr = $@"
UPDATE
seats
SET
backdatetime = NOW()
WHERE
roomid = {roomid}
AND deskid = {deskid}
AND seatid = {seatid}";
return(sql.executeNonQuery(sqlstr) == 1);
}
static public int BorrowBooks(MyMySql sql, string userid, List <string> bookids)
{
int result = 0;
string booklist = "";
foreach (var bookid in bookids)
{
booklist += $"\n({userid}, {bookid}, NOW()),";
}
booklist = booklist.Substring(0, booklist.Length - 1);
string SQLstr = "INSERT INTO borrowlog(`userid`, `bookid`, `borrowdatetime`) VALUES" + booklist;
result = sql.executeNonQuery(SQLstr);
return(result);
}
public static string[] getBooksByTag(MyMySql sql, string tagid)
{
string sqlstr = $@"
SELECT bookid
FROM book_tag
WHERE tagid = {tagid}
GROUP BY bookid";
DataTable table = null;
if ((table = sql.executeQuery(sqlstr)) != null)
{
string[] result = table.AsEnumerable().Select(d => d.Field <string>("bookid")).ToArray <string>();
return(result);
}
return(null);
}
public static SeatInfo GetSeatByUser(MyMySql sql_seats, string userid)
{
string sqlstr = $@"
SELECT roomid, deskid, seatid
FROM seats
WHERE userid = {userid} AND backdatetime IS NULL";
DataRow row = sql_seats.executeQueryFirst(sqlstr);
if (row != null)
{
return(new SeatInfo(row["roomid"].ToString(), row["deskid"].ToString(), row["seatid"].ToString()));
}
else
{
return(new SeatInfo(null, null, null));
}
}
static public bool changeUserInfo(MyMySql mysql, string userid, string contact, int sex, string username = "", string password = "")
{
string passwd = EncryptionUtils.getMD5(password).ToUpper();
string SQLstr = "UPDATE users SET " +
(username.Equals("") ? "" : $"username = '******',") +
(password.Equals("") ? "" : $"password = '******',") +
$"contact = '{contact}', " +
$"sex = {sex} " +
$"WHERE userid = {userid}";
int result = mysql.executeNonQuery(SQLstr);
if (result != 1)
{
return(false);
}
return(true);
}
static public UserInfo ConfirmAlreadyMD5(string username, string password, MyMySql sql)
{
string passwd = password.ToUpper();
MySqlDataReader reader = sql.executeReader($"SELECT userid,userprivillege FROM users WHERE username = '******' AND password = '******'");
if (reader != null)
{
UserInfo info = null;
if (reader.Read())
{
info = new UserInfo(reader["userid"].ToString(), (USER_TYPE)int.Parse(reader["userprivillege"].ToString()));
}
reader.Close();
sql.TryClose();
if (info != null)
{
return(info);
}
}
return(new UserInfo("", USER_TYPE.INVAILD));
}
static public void ReturnBook(MyMySql sql, string logid)
{
string SQLstr = $"UPDATE borrowlog SET returndatetime = NOW() WHERE logid = {logid}";
sql.executeNonQuery(SQLstr);
}
public static DataTable GetAllUsers(MyMySql sql_users)
{
return(sql_users.executeQuery(@"SELECT * FROM users"));
}
public static bool Order(MyMySql sql, string roomid, string deskid, string seatid, string userid)
{
string sqlstr = $"INSERT INTO seats (roomid, userid, deskid, seatid, orderdatetime) VALUES ({roomid},{userid}, {deskid},{seatid}, NOW())";
return(sql.executeNonQuery(sqlstr) == 1);
}
