public BOOK EditBook(BOOK eBook)
{
using (DbLibrary db = new DbLibrary())
{
db.Configuration.LazyLoadingEnabled = false;
BOOK book = db.BOOKs.Include(b => b.AUTHORs).FirstOrDefault(x => x.ISBN.Equals(eBook.ISBN));
book.AUTHORs.Clear();
db.SaveChanges();
}
using (DbLibrary db = new DbLibrary())
{
db.Configuration.LazyLoadingEnabled = false;
BOOK book = db.BOOKs.Include(b => b.CLASSIFICATION).FirstOrDefault(x => x.ISBN.Equals(eBook.ISBN));
db.Entry(book).CurrentValues.SetValues(eBook);
db.ChangeTracker.Entries <CLASSIFICATION>().ToList().ForEach(a => a.State = EntityState.Unchanged);
book.AUTHORs = new List <AUTHOR>();
foreach (var author in eBook.AUTHORs)
{
db.AUTHORs.Attach(author);
book.AUTHORs.Add(author);
}
db.SaveChanges();
return(book);
}
}
public int GetPermissionLevel(string username)
{
using (DbLibrary db = new DbLibrary())
{
return(db.ADMINS.FirstOrDefault(x => x.Username == username).PermissionLevel);
}
}
public int GetNewID()
{
using (DbLibrary db = new DbLibrary())
{
return(db.CLASSIFICATIONs.Max(a => a.SignId) + 1);
}
}
public string SaveAsNew(string Program)
{
DbLibrary DbLibraryControl = new DbLibrary();
DataSet DsAddGameResult = new DataSet();
DataSet DsLogResult = new DataSet();
try
{
DsAddGameResult = DbLibraryControl.QueryDataSet(string.Format(@"insert into GameBasicInfo values (N'{0}',{1},{2},{3},{4},{5},{6},{7},
N'{8}',{9},{10},'{11}',{12},{13},'{14}','{15}',{16}); select SCOPE_IDENTITY() as GamePK;", this.GameName, this.MinPlayer, this.MaxPlayer, this.Time,
this.Difficulty, this.Luck, this.Strategy, this.Interaction, this.ImgName, this.RentalNumber, this.IsExtension, this.RentalStartDate,
this.Rent, this.Deposit, this.TeachingUrl, this.Description, this.IsOpen
), "AddGameResult");
foreach (string TreeItemPK in this.GameCategory)
{
DbLibraryControl.Query(string.Format(@"insert into GameCategory values ({0},{1})", DsAddGameResult.Tables["AddGameResult"].Rows[0]["GamePK"].ToString(),
TreeItemPK));
}
}
catch (Exception ex)
{
DsLogResult = DbLibraryControl.QueryDataSet(string.Format(@"insert into Log values ('{0}', '{1}', 'SaveAsNew', GetDate()); select SCOPE_IDENTITY() as LogPK;",
ex.ToString().Replace("'", ""), Program), "LogResult");
return(DsLogResult.Tables["LogResult"].Rows[0]["LogPK"].ToString());
}
return("0");
}
public GameBasicInfoStorage(int PK)
{
DbLibrary DbLibraryControl = new DbLibrary();
DataTable DtGameBasicInfoResult = new DataTable();
DtGameBasicInfoResult = DbLibraryControl.QueryDataSet(string.Format(@"select * from GameBasicInfo where PK = {0}", PK), "GameBasicInfoResult").Tables["GameBasicInfoResult"];
if (DtGameBasicInfoResult.Rows.Count != 0)
{
this.GameName = DtGameBasicInfoResult.Rows[0]["GameName"].ToString();
this.MinPlayer = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["MinPlayer"]);
this.MaxPlayer = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["MaxPlayer"]);
this.Time = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["Time"]);
this.Difficulty = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["Difficulty"]);
this.Luck = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["Luck"]);
this.Strategy = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["Strategy"]);
this.Interaction = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["Interaction"]);
this.ImgName = DtGameBasicInfoResult.Rows[0]["ImgName"].ToString();
this.RentalNumber = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["RentalNumber"]);
this.IsExtension = Convert.ToUInt16(DtGameBasicInfoResult.Rows[0]["IsExtension"]);
this.RentalStartDate = Convert.ToDateTime(DtGameBasicInfoResult.Rows[0]["RentalStartDate"]).ToString("yyyy-MM-dd");
this.Rent = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["Rent"]);
this.Deposit = Convert.ToInt16(DtGameBasicInfoResult.Rows[0]["Deposit"]);
this.TeachingUrl = DtGameBasicInfoResult.Rows[0]["TeachingUrl"].ToString();
this.Description = DtGameBasicInfoResult.Rows[0]["Description"].ToString();
this.IsOpen = Convert.ToUInt16(DtGameBasicInfoResult.Rows[0]["IsOpen"]);
}
}
public static LibraryDto ToDto(this DbLibrary lib)
{
return(new LibraryDto()
{
Id = lib.Id,
Name = lib.Name,
Address = lib.Address.ToDto()
});
}
private async Task <Loan> GetLoanAsync(DbLibrary db, ReservationMessage.Item item, DateTime createDate)
{
var ret = new Loan
{
Book = await db.Books.FirstOrDefaultAsync(x => x.Title.Contains(item.Name)),
Copy = await db.Copies.FirstOrDefaultAsync(x => x.Number == item.Number),
DueDate = createDate.AddDays(7)
};
return(ret);
}
public bool DoesBookExist(string isbn)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.BOOKs.Any(x => x.ISBN == isbn));
}
catch
{
return(false);
}
}
}
public List <BOOK> GetBooksFromClassification(int signId)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.CLASSIFICATIONs.Find(signId).BOOKs.ToList());
}
catch
{
return(null);
}
}
}
public BOOK GetBookFromIsbn(string isbn)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.BOOKs.Include(b => b.AUTHORs).Include(b => b.CLASSIFICATION).FirstOrDefault(x => x.ISBN.Equals(isbn)));
}
catch
{
return(null);
}
}
}
public CLASSIFICATION GetClassificationFromIsbn(BOOK book)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.CLASSIFICATIONs.FirstOrDefault(a => a.SignId == book.SignId));
}
catch
{
return(null);
}
}
}
public CLASSIFICATION GetClassificationFromID(int id)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.CLASSIFICATIONs.FirstOrDefault(x => x.SignId.Equals(id)));
}
catch
{
return(null);
}
}
}
public bool DoesClassificationContainBooks(CLASSIFICATION classification)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.CLASSIFICATIONs.Find(classification.SignId).BOOKs.ToList().Count() > 0);
}
catch
{
return(false);
}
}
}
public CLASSIFICATION GetClassificationFromName(string signum)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.CLASSIFICATIONs.FirstOrDefault(a => a.Signum == signum));
}
catch
{
return(null);
}
}
}
public List <CLASSIFICATION> GetAllClassifications()
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.CLASSIFICATIONs.OrderBy(x => x.Signum).ToList());
}
catch
{
return(null);
}
}
}
public AUTHOR GetAuthorDetailsFromDB(int id)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.AUTHORs.FirstOrDefault(x => x.Aid.Equals(id)));
}
catch
{
return(null);
}
}
}
public IPagedList <AUTHOR> GetAllAuthorsFromDB(int page, int itemsPerPage)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.AUTHORs.OrderBy(x => x.LastName).ToPagedList(page, itemsPerPage));
}
catch
{
return(null);
}
}
}
public AUTHOR GetAuthorFromDB(int id)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.AUTHORs.Find(id));
}
catch
{
return(null);
}
}
}
public List <AUTHOR> GetAllAuthorsFromDBToList()
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.AUTHORs.OrderBy(x => x.LastName).ToList());
}
catch
{
return(null);
}
}
}
public bool DoesClassificationExist(string signum)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.CLASSIFICATIONs.Any(x => x.Signum.Equals(signum)));
}
catch
{
return(false);
}
}
}
public IPagedList <AUTHOR> GetAuthorsFromSearchResult(string search, int page, int itemsPerPage)
{
using (DbLibrary db = new DbLibrary())
{
try
{
return(db.AUTHORs.Where(x => ((x.FirstName + " " + x.LastName).Contains(search))).OrderBy(x => x.LastName).ToPagedList(page, itemsPerPage));
}
catch
{
return(null);
}
}
}
public IPagedList <BOOK> GetBooksByAuthor(int id, int page)
{
using (DbLibrary db = new DbLibrary())
{
try
{
db.Configuration.LazyLoadingEnabled = false;
return(db.BOOKs.Include(b => b.AUTHORs).Where(b => b.AUTHORs.Any(a => a.Aid == id)).OrderBy(o => o.Title).ToPagedList(page, 100));
}
catch
{
return(null);
}
}
}
public HttpResponseMessage PutState(int CategoryId, bool State)
{
try
{
DbLibrary dbLibrary = new DbLibrary();
BO_Category boCategory = new BO_Category(dbLibrary);
DataMessage dataMessage = new DataMessage(boCategory.ChangeState(CategoryId, State));
return(Request.CreateResponse(HttpStatusCode.OK, dataMessage));
}
catch (Exception e)
{
ErrorMessage mensaje = new ErrorMessage("2.1", "Excepción en el cambio de estado del registro: " + e.GetBaseException().Message, e.ToString());
return(Request.CreateResponse(HttpStatusCode.BadRequest, mensaje));
}
}
public HttpResponseMessage Put([FromBody] Category category)
{
try
{
DbLibrary dbLibrary = new DbLibrary();
BO_Category boCategory = new BO_Category(dbLibrary);
DataMessage dataMessage = new DataMessage(boCategory.Update(category));
return(Request.CreateResponse(HttpStatusCode.OK, dataMessage));
}
catch (Exception e)
{
ErrorMessage mensaje = new ErrorMessage("2.1", "Excepción en la actualización del registro: " + e.GetBaseException().Message, e.ToString());
return(Request.CreateResponse(HttpStatusCode.BadRequest, mensaje));
}
}
public string Save(string Program)
{
DbLibrary DbLibraryControl = new DbLibrary();
try
{
}
catch (Exception ex)
{
DbLibraryControl.Query(string.Format("insert into Log values ('{0}', '{1}', 'Save', GetDate())", ex.ToString(), Program));
return("1");
}
return("0");
}
public List <ADMIN> GetAllAdmins()
{
using (DbLibrary db = new DbLibrary())
{
try
{
string query = "SELECT * FROM ADMINS ORDER BY PermissionLevel DESC, Username";
return(db.Database.SqlQuery <ADMIN>(query).ToList());
}
catch
{
return(null);
}
}
}
public HttpResponseMessage GetAll()
{
try
{
DbLibrary dbLibrary = new DbLibrary();
BO_Editorial boEditorial = new BO_Editorial(dbLibrary);
DataMessage dataMessage = new DataMessage(boEditorial.GetAll());
return(Request.CreateResponse(HttpStatusCode.OK, dataMessage));
}
catch (Exception e)
{
ErrorMessage mensaje = new ErrorMessage("2.1", "Excepción en la obtención del listado: " + e.GetBaseException().Message, e.ToString());
return(Request.CreateResponse(HttpStatusCode.BadRequest, mensaje));
}
}
public HttpResponseMessage Post([FromBody] Editorial editorial)
{
try
{
DbLibrary dbLibrary = new DbLibrary();
BO_Editorial boEditorial = new BO_Editorial(dbLibrary);
DataMessage dataMessage = new DataMessage(boEditorial.Create(editorial));
return(Request.CreateResponse(HttpStatusCode.OK, dataMessage));
}
catch (Exception e)
{
ErrorMessage mensaje = new ErrorMessage("2.1", "Excepción en la creación del registro: " + e.GetBaseException().Message, e.ToString());
return(Request.CreateResponse(HttpStatusCode.BadRequest, mensaje));
}
}
public ADMIN GetAdmin(string username)
{
using (DbLibrary db = new DbLibrary())
{
try
{
string query = "SELECT TOP 1 * FROM ADMINS WHERE ADMINS.Username = @user";
return(db.Database.SqlQuery <ADMIN>(query, new SqlParameter("@user", username)).SingleOrDefault());
}
catch
{
return(null);
}
}
}
public HttpResponseMessage GetCategory(int CategoryId)
{
try
{
DbLibrary dbLibrary = new DbLibrary();
BO_Category boCategory = new BO_Category(dbLibrary);
DataMessage dataMessage = new DataMessage(boCategory.GetId(CategoryId));
return(Request.CreateResponse(HttpStatusCode.OK, dataMessage));
}
catch (Exception e)
{
ErrorMessage mensaje = new ErrorMessage("2.1", "Excepción en la obtención del Id: " + e.GetBaseException().Message, e.ToString());
return(Request.CreateResponse(HttpStatusCode.BadRequest, mensaje));
}
}