public static bool Login(string userId, string password, ref Person person)
{
password = ComputeSha256Hash(password);
bool isAdmin = false;
string query = string.Format("select userId, isAdmin from person where userId='{0}' AND password='******'", userId, password);;
using (var myDBReader = new MySqlDBReader(query))
{
var reader = myDBReader.Reader;
while (reader.Read())
{
person.UserId = reader.GetString("userId");
isAdmin = bool.Parse(reader.GetString("isAdmin"));
}
}
if (isAdmin)
{
person.Token = DBContext.GenerateToken("Admin", person.UserId, DateTime.UtcNow.Ticks);
}
else
{
person.Token = DBContext.GenerateToken("Normal", person.UserId, DateTime.UtcNow.Ticks);
}
return(true);
}
private static string InitiateReturnBook(string userId, string bookId)
{
string issuedOn = "";
string validTo = "";
string query = string.Format("select * from Books where bookId='{0}' and userId='{1}'", bookId, userId);
using (var myDBReader = new MySqlDBReader(query))
{
var reader = myDBReader.Reader;
while (reader.Read())
{
issuedOn = SafeGetString(reader, "issuedOn");
validTo = SafeGetString(reader, "validTo");
}
}
query = string.Format("Update Books Set " +
"userId='{0}'," +
"available=available+1," +
"issuedOn='{1}'," +
"issuedBy='{2}'," +
"validTo='{3}'," +
"returnStatus=1 where bookId='{4}' and userId='{5}'"
, string.Empty, "2018-01-01 00:00:00", string.Empty, "2018-01-01 00:00:00", bookId, userId);
try
{
string firstCheck = InitiateConnectionProcess(query);
if (firstCheck == "Success")
{
query = string.Format("Update bookuser Set returnedOn='{0}' where bookId='{1}' and userId='{2}'", DateTime.UtcNow.ToString("MM/dd/yyyy"), bookId, userId);
if (InitiateConnectionProcess(query) == "Success")
{
return("Success You have retured the book");
}
else
{
query = string.Format("Update Books Set " +
"userId='{0}'," +
"available=available-1," +
"issuedOn='{1}'," +
"issuedBy='{2}'," +
"validTo='{3}'," +
"returnStatus=0 where bookId='{4}' and userId='{5}'"
, userId, issuedOn, "Admin", validTo, bookId, userId);
return(InitiateConnectionProcess(query));
}
}
else
{
return(firstCheck);
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
public StockHolderData getStockHolderData(string stockID, string year, string season)
{
string table = "stock_stockholder_" + year + "q" + season;
string code = StockIDUtil.getPureCode(stockID);
string sql = "select * from " + table + " where code='" + code + "'";
List <string> rs = MySqlDBReader.querySql(sql);
if (rs != null && rs.Count > 0)
{
return(TushareDataConvertor.parseStockHolderData(rs));
}
return(null);
}
private static bool CheckAvailability(string bookId)
{
string query = string.Format("Select available from Books where bookId='{0}'", bookId);
using (var myDBReader = new MySqlDBReader(query))
{
var reader = myDBReader.Reader;
while (reader.Read())
{
int status = int.Parse(SafeGetString(reader, "available"));
if (status > 0)
{
return(true);
}
}
}
return(false);
}
public static List <UserBook> GetAllIssuedBook(string userId)
{
List <UserBook> userBooks = new List <UserBook>();
string query = string.Format("Select * from bookuser where userId='{0}'", userId);
using (var myDBReader = new MySqlDBReader(query))
{
var reader = myDBReader.Reader;
while (reader.Read())
{
userBooks.Add(new UserBook()
{
BookId = reader.GetString("bookId"),
UserId = reader.GetString("userId"),
IssuedOn = reader.GetString("issuedOn"),
ReturnedOn = reader.GetString("returnedOn")
});
}
}
return(userBooks);
}
public static List <Book> GetBooks(string user)
{
List <Book> books = new List <Book>();
string query;
int check = -1;
if (user == "Admin")
{
check = 1;
query = "select * from Books";
}
else
{
if (user != null)
{
check = 2;
query = string.Format("select returnStatus, bookId, available,userId, name, issuedOn, validTo from Books where userId='{0}'", user);
}
else
{
check = 3;
query = "select returnStatus, bookId, available, name from Books";
}
}
using (var myDBReader = new MySqlDBReader(query))
{
var reader = myDBReader.Reader;
if (reader.FieldCount == 0)
{
return(books);
}
while (reader.Read())
{
FillBooks(ref books, check, ref reader);
}
}
return(books);
}
private static bool CheckUserCurrentRecord(string userId, string bookId)
{
int maxAllowed = 2;
int issued = 0;
string query = string.Format("Select returnStatus, bookId from Books where userId='{0}'", userId);
using (var myDBReader = new MySqlDBReader(query))
{
var reader = myDBReader.Reader;
while (reader.Read())
{
int status = int.Parse(SafeGetString(reader, "returnStatus"));
if (SafeGetString(reader, "bookId") == null || SafeGetString(reader, "bookId") == "")
{
return(false);
}
if (status < 1)
{
issued++;
}
}
}
return(issued <= maxAllowed);
}
private DBConnectionManager()
{
Reader = new MySqlDBReader();
Writer = new MySqlDBWriter();
}