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 <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 <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);
}