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