public static async Task <List <Transaction> > GetByLabIDTypeAndDateAsync(int labID, int itemType, DateTime date)
{
var db = new SoftwareStudioContext();
List <string> reqList = new List <string> {
"transactions.uuid",
"book_date",
"transactions.created",
"transactions.item_id",
"time_id",
"transaction_type",
"transactions.user_id"
};
var reqString = db.ListToString(reqList);
string queryString = $@"
SELECT {reqString}
FROM transactions
LEFT JOIN items ON transactions.item_id = items.uuid
LEFT JOIN laboratory_items ON laboratory_items.item_id = items.uuid
WHERE laboratory_id = {labID} AND type = {itemType} AND book_date = '{date.ToString("yyyy-MM-dd")}';
";
List <Transaction> transactions = await db.transactions.FromSqlRaw(queryString).ToListAsync();
return(transactions);
}
public static async Task <List <Log> > GetAllAsync()
{
var db = new SoftwareStudioContext();
List <Log> logs = await db.logs.FromSqlRaw("SELECT * FROM logs").ToListAsync();
return(logs);
}
public static Laboratory GetByID(int labID)
{
var db = new SoftwareStudioContext();
Laboratory lab = db.laboratories.Find(labID);
return(lab);
}
public static async Task <List <Laboratory> > GetAllAsync()
{
var db = new SoftwareStudioContext();
List <Laboratory> labs = await db.laboratories.FromSqlRaw("SELECT * FROM laboratories").ToListAsync();
return(labs);
}
public async static Task <int> LoginAsync(string username, string password)
{
var db = new SoftwareStudioContext();
/// find in db user with username.
User user = await db.users.FirstOrDefaultAsync(user => user.username == username);
if (user == null)
{
return(-1);
}
else
{
/// verify incoming password with in db password.
bool result = BCrypt.Net.BCrypt.Verify(password, user.password);
if (result)
{
return(user.uuid);
}
else
{
return(-1);
}
}
}
public static async Task <List <Item> > GetByIDAsync(int itemID)
{
var db = new SoftwareStudioContext();
string queryString = $"SELECT * FROM items WHERE uuid = {itemID}";
List <Item> items = await db.items.FromSqlRaw(queryString).ToListAsync();
return(items);
}
public static async Task <List <ItemsLaboratoryTransaction> > GetAvailableItems(DateTime booking_datetime)
{
var db = new SoftwareStudioContext();
List <ItemsLaboratoryTransaction> items = await db.items_laboratory_transaction.FromSqlRaw(getAvailableItemsQueryString(booking_datetime)).ToListAsync();
return(items);
}
public static async Task <List <Log> > GetByBookDateAsync(DateTime bookDate)
{
var db = new SoftwareStudioContext();
string queryString = $"SELECT * FROM logs WHERE book_date = {bookDate}";
List <Log> logs = await db.logs.FromSqlRaw(queryString).ToListAsync();
return(logs);
}
public static async Task <List <Item> > GetAllAsync()
{
var db = new SoftwareStudioContext();
string queryString = $"SELECT * FROM items;";
List <Item> items = await db.items.FromSqlRaw(queryString).ToListAsync();
return(items);
}
public static async Task <List <Laboratory_item> > GetAllByLabIDAsync(int labID)
{
var db = new SoftwareStudioContext();
string queryString = $"SELECT * FROM laboratory_items WHERE laboratory_id = {labID}";
List <Laboratory_item> items = await db.laboratory_items.FromSqlRaw(queryString).ToListAsync();
return(items);
}
public static async Task <List <Transaction> > GetByDateAsync(DateTime date)
{
var db = new SoftwareStudioContext();
string queryString = $"SELECT * FROM transactions WHERE book_date = '{date}'";
List <Transaction> transactions = await db.transactions.FromSqlRaw(queryString).ToListAsync();
return(transactions);
}
public static async Task <List <Transaction> > GetAllAsync()
{
var db = new SoftwareStudioContext();
string queryString = $"SELECT * FROM transactions";
List <Transaction> transactions = await db.transactions.FromSqlRaw(queryString).ToListAsync();
return(transactions);
}
public static async Task <List <Transaction> > GetBookedItems(int user_id)
{
var db = new SoftwareStudioContext();
string query_string = $@"SELECT * FROM transactions WHERE transactions.user_id = {user_id}";
List <Transaction> booked_items = await db.transactions.FromSqlRaw(query_string).ToListAsync();
return(booked_items);
}
public static async Task <List <Transaction> > GetByUserIDAsync(int userID)
{
var db = new SoftwareStudioContext();
string queryString = $"SELECT * FROM transactions WHERE user_id = {userID}";
List <Transaction> transactions = await db.transactions.FromSqlRaw(queryString).ToListAsync();
return(transactions);
}
public static void Add(Log log)
{
var db = new SoftwareStudioContext();
if (log != null)
{
db.logs.Add(log);
db.SaveChanges();
}
}
public static async Task <List <User> > GetByIDAsync(int userID)
{
using (var db = new SoftwareStudioContext())
{
string queryString = $"SELECT * FROM users WHERE uuid = {userID}";
var user = await db.users.FromSqlRaw(queryString).ToListAsync();
return(user);
}
}
public static void AddWithTransaction(Transaction transaction)
{
var db = new SoftwareStudioContext();
if (transaction != null)
{
db.logs.Add(new Log(transaction));
db.SaveChanges();
}
}
public static void Remove(Item item)
{
var db = new SoftwareStudioContext();
if (item != null)
{
db.Remove(item);
db.SaveChanges();
}
}
public static void AddItem(int labID, int itemID)
{
var db = new SoftwareStudioContext();
Laboratory_item labItem = new Laboratory_item(labID, itemID);
if (labItem != null)
{
db.laboratory_items.Add(labItem);
db.SaveChanges();
}
}
public static async void RemoveItem(int itemID)
{
var db = new SoftwareStudioContext();
Laboratory_item labItem = await db.laboratory_items.FirstOrDefaultAsync(labItem => labItem.item_id == itemID);
if (labItem != null)
{
db.Remove(labItem);
db.SaveChanges();
}
}
public static void Delete(Transaction transaction)
{
var db = new SoftwareStudioContext();
if (transaction != null)
{
db.transactions.Remove(transaction);
transaction.transaction_type = (int)Transaction_type.give;
db.logs.Add(new Log(transaction));
db.SaveChanges();
}
}
public static void Cancel(Transaction transaction)
{
var db = new SoftwareStudioContext();
if (transaction != null)
{
var temp = db.transactions.Remove(transaction);;
transaction.transaction_type = (int)Transaction_type.cancel;
db.logs.Add(new Log(transaction));
db.SaveChanges();
}
}
public static int Register(User user)
{
Console.WriteLine("user {0}", user);
var db = new SoftwareStudioContext();
/// hash password.
string hashedPassword = BCrypt.Net.BCrypt.HashPassword(user.password);
user.password = hashedPassword;
db.users.Add(user);
db.SaveChanges();
return(0);
}
public static async Task <List <ItemDetail> > GetDetailByIDAsync(int itemID)
{
var db = new SoftwareStudioContext();
List <string> reqList = new List <string> {
"items.uuid",
"items.name",
"items.type",
"laboratory_items.laboratory_id",
};
string reqStr = db.ListToString(reqList);
string queryString = $"SELECT {reqStr} FROM items LEFT JOIN laboratory_items ON items.uuid = {itemID} WHERE laboratory_items.item_id = items.uuid";
List <ItemDetail> items = await db.itemDetails.FromSqlRaw(queryString).ToListAsync();
return(items);
}
public static async Task <int> GetLabItemCountByLabIDAndType(int labID, int itemType)
{
var db = new SoftwareStudioContext();
string queryString = $@"
SELECT laboratory_items.uuid
FROM laboratory_items
LEFT JOIN items ON laboratory_items.item_id = items.uuid
WHERE laboratory_items.laboratory_id = {labID} AND items.type = {itemType}
";
int labItemCount = await db.laboratory_items.FromSqlRaw(queryString).CountAsync();
return(labItemCount);
}
public static async Task <List <UserBlacklist> > GetBlacklistAsync()
{
var db = new SoftwareStudioContext();
string queryString = $@"select users.uuid, users.fname, users.lname, users.gender,
(case
when blacklist.user_id is NULL then False
else True
end) as is_blacklist
from users
LEFT JOIN blacklist on users.uuid = blacklist.user_id
WHERE users.uuid not in (6, 7, 8, 9 ,10)
order by is_blacklist;";
List <UserBlacklist> userBlacklists = await db.userBlacklists.FromSqlRaw(queryString).ToListAsync();
return(userBlacklists);
}
public static int Add(Item item)
{
var db = new SoftwareStudioContext();
if (item != null)
{
var change = db.items.Add(item);
db.SaveChanges();
return(change.Entity.uuid);
}
else
{
return(-1);
}
}
public static async Task <List <int> > GetAllQuantityAsync()
{
List <int> allItems = new List <int>();
var db = new SoftwareStudioContext();
string queryString = $"SELECT * FROM laboratory_items";
List <Laboratory_item> items = await db.laboratory_items.FromSqlRaw(queryString).ToListAsync();
for (int i = 0; i < items.Count; i++)
{
int labid = items[i].laboratory_id;
while (allItems.Count < labid)
{
allItems.Add(0);
}
allItems[labid - 1] = allItems[labid - 1] + 1;
}
return(allItems);
}
public static async Task <List <Transaction> > GetByLabIDAsync(int labID)
{
var db = new SoftwareStudioContext();
List <string> reqList = new List <string> {
"transactions.uuid",
"transactions.item_id",
"transactions.user_id",
"time_id",
"book_date",
"transaction_type",
"transactions.created",
};
var reqString = db.ListToString(reqList);
string queryString = $"SELECT {reqString} FROM transactions LEFT JOIN laboratory_items ON laboratory_id = {labID} WHERE transactions.item_id = laboratory_items.item_id; ";
List <Transaction> transactions = await db.transactions.FromSqlRaw(queryString).ToListAsync();
return(transactions);
}
public static async Task <List <TransactionItem> > GetWithItemByUserIDAsync(int userID)
{
var db = new SoftwareStudioContext();
List <string> reqList = new List <string> {
"transactions.uuid",
"transactions.item_id",
"time_id",
"book_date",
"items.type",
"items.name",
"transactions.created",
};
var reqString = db.ListToString(reqList);
string queryString = $"SELECT {reqString} FROM transactions LEFT JOIN items ON items.uuid = transactions.item_id WHERE transactions.user_id = {userID};";
List <TransactionItem> transactionItems = await db.transactionItems.FromSqlRaw(queryString).ToListAsync();
return(transactionItems);
}