public ActionResult AllTitles()
{
using (Team45LibraryContext db = new Team45LibraryContext())
{
var query =
from t in db.Titles
join i in db.Inventory on t.Isbn equals i.Isbn
into join1
from j1 in join1.DefaultIfEmpty()
join c in db.CheckedOut on j1.Serial equals c.Serial
into join2
from j2 in join2.DefaultIfEmpty()
join p in db.Patrons on j2.CardNum equals p.CardNum
into join3
from j3 in join3.DefaultIfEmpty()
select new
{
isbn = t.Isbn,
title = t.Title,
author = t.Author,
serial = j1 == null ? null : (uint?)j1.Serial,
name = j3 == null ? "" : j3.Name
};
return(Json(query.ToArray()));
}
}
public IActionResult CheckLogin(string name, int cardnum)
{
bool loginSuccessful = false;
using (Team45LibraryContext db = new Team45LibraryContext())
{
// Performs a query that checks if the given name and cardnum are in the Patrons table
var query =
from p in db.Patrons
where p.Name == name &&
p.CardNum == cardnum
select p;
// Checks if any rows were returned, indicating the given user is a Patron
if (query.Any())
{
loginSuccessful = true;
}
}
if (!loginSuccessful)
{
return(Json(new { success = false }));
}
else
{
user = name;
card = cardnum;
return(Json(new { success = true }));
}
}
public ActionResult ReturnBook(int serial)
{
using (var cont = new Team45LibraryContext())
{
var book = (from c in cont.CheckedOut
where c.Serial == Convert.ToUInt32(serial) && c.CardNum == Convert.ToUInt32(card)
select c).FirstOrDefault();
if (book != null)
{
cont.CheckedOut.Remove(book);
cont.SaveChanges();
}
}
return(Json(new { success = true }));
}
public ActionResult CheckOutBook(int serial)
{
CheckedOut co = new CheckedOut
{
CardNum = Convert.ToUInt32(card),
Serial = Convert.ToUInt32(serial)
};
using (var cont = new Team45LibraryContext())
{
cont.CheckedOut.Add(co);
cont.SaveChanges();
}
return(Json(new { success = true }));
}
public ActionResult CheckOutBook(int serial)
{
using (Team45LibraryContext db = new Team45LibraryContext())
{
// Create a new CheckedOut object and fill in the cardnum and serial with the given serial
// and global cardnum
CheckedOut newCheckOut = new CheckedOut();
newCheckOut.CardNum = (uint)card;
newCheckOut.Serial = (uint)serial;
// Adds the new CheckedOut object to the CheckedOut table
db.CheckedOut.Add(newCheckOut);
db.SaveChanges();
}
return(Json(new { success = true }));
}
public ActionResult ReturnBook(int serial)
{
using (Team45LibraryContext db = new Team45LibraryContext())
{
// Finds the row in the CheckedOut table corresponding to the global cardnum
// and the given serial number
var query = from c in db.CheckedOut
where c.CardNum == (uint)card &&
c.Serial == (uint)serial
select c;
// Removes the returned row from the CheckedOut table
db.CheckedOut.RemoveRange(query);
db.SaveChanges();
}
return(Json(new { success = true }));
}
public IActionResult CheckLogin(string name, int cardnum)
{
bool loginSuccessful;
using (var cont = new Team45LibraryContext())
{
var result = from p in cont.Patrons where p.Name == name && p.CardNum == Convert.ToUInt32(cardnum) select p;
loginSuccessful = result.Any();
}
if (!loginSuccessful)
{
return(Json(new { success = false }));
}
user = name;
card = cardnum;
return(Json(new { success = true }));
}
public ActionResult ListMyBooks()
{
using (var cont = new Team45LibraryContext())
{
//select t.Title, t.Author, i.Serial
// from Titles t
//natural join Inventory i
//natural join CheckedOut c
//where c.CardNum = 1;
var result = from t in cont.Titles
join i in cont.Inventory on t.Isbn equals i.Isbn
join c in cont.CheckedOut on i.Serial equals c.Serial
where c.CardNum == Convert.ToUInt32(card)
select new { t.Title, t.Author, i.Serial };
return(Json(result.ToArray()));
}
}
public ActionResult ListMyBooks()
{
using (Team45LibraryContext db = new Team45LibraryContext())
{
var query =
from t in db.Titles
join i in db.Inventory on t.Isbn equals i.Isbn
join c in db.CheckedOut on i.Serial equals c.Serial
join p in db.Patrons on c.CardNum equals p.CardNum
where p.CardNum == card
select new
{
isbn = t.Isbn,
title = t.Title,
author = t.Author,
serial = i.Serial
};
return(Json(query.ToArray()));
}
}
public ActionResult AllTitles()
{
//object result;
using (var cont = new Team45LibraryContext())
{
//select t.*, i.Serial, case when p.Name is null then '' else p.Name end as Name
//from Titles t
// left join Inventory i on t.ISBN = i.ISBN
//left join CheckedOut c on i.Serial = c.Serial
//left join Patrons p on c.CardNum = p.CardNum;
var result = from t in cont.Titles
join i in cont.Inventory on t.Isbn equals i.Isbn into inv
from outer1 in inv.DefaultIfEmpty()
join c in cont.CheckedOut on outer1.Serial equals c.Serial into ser
from outer2 in ser.DefaultIfEmpty()
join p in cont.Patrons on outer2.CardNum equals p.CardNum into pat
from outer3 in pat.DefaultIfEmpty()
select new { t.Isbn, t.Title, t.Author, Serial = outer1 != null ? (uint?)outer1.Serial : null, Name = outer3 == null ? string.Empty : outer3.Name ?? string.Empty };
return(Json(result.ToArray()));
}
}
