public BookResponse GetBook(string id)
{
BookResponse resp = new BookResponse();
int bookId;
if (int.TryParse(id, out bookId))
{
using (TablesContext context = new TablesContext())
{
resp.Book = context.Books.Find(bookId);
if (resp.Book != null && !resp.Book.Deleted)
{
resp.Status = (int)Constants.STATUSES.OK;
resp.Message = Constants.SUCCESS;
}
else
{
resp.Book = null;
resp.Status = (int)Constants.STATUSES.ERROR;
resp.Message = Constants.BOOK_NOT_FOUND;
}
}
}
else
{
resp.Status = (int)Constants.STATUSES.ERROR;
resp.Message = Constants.ENTER_INT;
}
return(resp);
}
public Order NewOrder(OrderRequest orderRequest)
{
using (TablesContext context = new TablesContext())
{
Order order = new Order
{
UserId = orderRequest.UserId,
TotalPayment = orderRequest.TotalPayment,
Status = orderRequest.Status,
};
context.Orders.Add(order);
context.SaveChanges();
orderRequest.Books.ForEach(delegate(int book)
{
BooksInOrder bookInOrder = new BooksInOrder
{
OrderId = context.Orders.ToList <Order>().Last().Id,
BookId = book
};
context.BooksInOrders.Add(bookInOrder);
context.SaveChanges();
});
return(order);
}
}
public UserResponse Auth(Auth data)
{
UserResponse response = new UserResponse();
if (data.Password.Equals(Constants.DEF_PASS))
{
using (TablesContext context = new TablesContext())
{
var users = from u in context.Users where u.Email.Equals(data.Email) select u;
if (users != null && users.Count() > 0)
{
response.Status = (int)Constants.STATUSES.OK;
response.Message = Constants.SUCCESS;
response.User = users.First <User>();
}
else
{
response.Status = (int)Constants.STATUSES.ERROR;
response.Message = Constants.WRONG_USER_DATA;
}
}
}
else
{
response.Status = (int)Constants.STATUSES.ERROR;
response.Message = Constants.WRONG_USER_DATA;
}
return(response);
}
public string RestoreBook(string id)
{
int bookId;
Book book;
if (int.TryParse(id, out bookId))
{
using (TablesContext context = new TablesContext())
{
book = context.Books.Find(bookId);
if (book.Deleted)
{
book.Deleted = false;
context.SaveChanges();
return(Constants.BOOK_RESTORED);
}
else
{
return(Constants.BOOK_NOT_DELETED);;
}
}
}
else
{
return(Constants.ENTER_INT);
}
}
public UserResponse GetUser(string id)
{
UserResponse resp = new UserResponse();
int userId;
if (int.TryParse(id, out userId))
{
using (TablesContext context = new TablesContext())
{
resp.User = context.Users.Find(userId);
if (resp.User != null && !resp.User.Deleted)
{
resp.Status = (int)Constants.STATUSES.OK;
resp.Message = Constants.SUCCESS;
}
else
{
resp.Status = (int)Constants.STATUSES.ERROR;
resp.User = null;
resp.Message = Constants.USER_NOT_FOUND;
}
}
}
else
{
resp.Status = (int)Constants.STATUSES.ERROR;
resp.Message = Constants.ENTER_INT;
}
return(resp);
}
public string PayOrder(string id)
{
int orderId;
if (int.TryParse(id, out orderId))
{
Order order;
using (TablesContext context = new TablesContext())
{
order = context.Orders.Find(orderId);
if (order != null)
{
if (!order.Status)
{
order.Status = true;
context.SaveChanges();
return(Constants.ORDER_PAYED);
}
else
{
return(Constants.ORDER_ALREADY_PAYED);
}
}
else
{
return(Constants.ORDER_NOT_FOUND);
}
}
}
else
{
return(Constants.ENTER_INT);
}
}
public InsertCommandContext(SchemaMetaData schemaMetaData, ParameterContext parameterContext, InsertCommand insertCommand) : base(insertCommand)
{
_tablesContext = new TablesContext(insertCommand.Table);
_columnNames = insertCommand.UseDefaultColumns() ? schemaMetaData.GetAllColumnNames(insertCommand.Table.GetTableName().GetIdentifier().GetValue()) : insertCommand.GetColumnNames();
_insertValueContexts = GetInsertValueContexts(parameterContext);
_generatedKeyContext = new GeneratedKeyContextEngine(schemaMetaData).CreateGenerateKeyContext(parameterContext, insertCommand);
}
public SelectCommandContext(SchemaMetaData schemaMetaData, string sql, ParameterContext parameterContext, SelectCommand sqlCommand) : base(sqlCommand)
{
_tablesContext = new TablesContext(sqlCommand.GetSimpleTableSegments());
_groupByContext = new GroupByContextEngine().CreateGroupByContext(sqlCommand);
_orderByContext = new OrderByContextEngine().CreateOrderBy(sqlCommand, _groupByContext);
_projectionsContext = new ProjectionsContextEngine(schemaMetaData).CreateProjectionsContext(sql, sqlCommand, _groupByContext, _orderByContext);
_paginationContext = new PaginationContextEngine().CreatePaginationContext(sqlCommand, _projectionsContext, parameterContext);
_containsSubQuery = ContainsSubQuery();
}
public void RemoveLibrarian(int id)
{
using (TablesContext db = new TablesContext())
{
Librarian librarian = db.Librarians.Find(id);
db.Librarians.Remove(librarian);
db.SaveChanges();
Console.WriteLine("Данные успешно удалены");
}
}
public void RemoveStudent(int id)
{
using (TablesContext db = new TablesContext())
{
Student student = db.Students.Find(id);
db.Students.Remove(student);
db.SaveChanges();
Console.WriteLine("Данные успешно удалены");
}
}
// TODO to be remove, for test case only
public SelectCommandContext(SelectCommand sqlCommand, GroupByContext groupByContext,
OrderByContext orderByContext, ProjectionsContext projectionsContext, PaginationContext paginationContext) : base(sqlCommand)
{
_tablesContext = new TablesContext(sqlCommand.GetSimpleTableSegments());
this._groupByContext = groupByContext;
this._orderByContext = orderByContext;
this._projectionsContext = projectionsContext;
this._paginationContext = paginationContext;
_containsSubQuery = ContainsSubQuery();
}
public Book NewBook(Book book)
{
using (TablesContext context = new TablesContext())
{
context.Books.Add(book);
context.SaveChanges();
}
return(book);
}
public AllBooksResponse AllBooks()
{
using (TablesContext context = new TablesContext())
{
AllBooksResponse resp = new AllBooksResponse();
resp.Status = 0;
resp.Message = "Success";
resp.Books = context.Books.ToList <Book>();
return(resp);
}
}
public OrdersByUser GetOrdersByUserId(string id)
{
OrdersByUser resp = new OrdersByUser();
int userId;
if (int.TryParse(id, out userId))
{
resp.OrderData = new List <OrderData>();
using (TablesContext context = new TablesContext())
{
var orders = from o in context.Orders where o.UserId.Equals(userId) select o;
if (orders != null && orders.Count() > 0)
{
orders.ToList <Order>().ForEach(delegate(Order order)
{
List <Book> booksInOrder = new List <Book>();
OrderData current = new OrderData
{
Id = order.Id,
UserId = order.UserId,
TotalPayment = order.TotalPayment,
Status = order.Status
};
var books = from b in context.BooksInOrders where b.OrderId.Equals(current.Id) select b;
if (books != null && books.Count() > 0)
{
books.ToList <BooksInOrder>().ForEach(delegate(BooksInOrder book)
{
booksInOrder.Add(context.Books.Find(book.BookId));
});
}
current.Books = booksInOrder;
resp.OrderData.Add(current);
});
resp.Status = (int)Constants.STATUSES.OK;
resp.Message = Constants.SUCCESS;
}
else
{
resp.Status = (int)Constants.STATUSES.ERROR;
resp.Message = Constants.NO_ORDERS;
}
}
}
else
{
resp.Status = (int)Constants.STATUSES.ERROR;
resp.Message = Constants.ENTER_INT;
}
return(resp);
}
public void Add()
{
string EBookName;
string EBookAuthor;
string EPublisher;
int EYearOfPublish;
string EGenre;
int EExemplarAmount;
try
{
Console.WriteLine("Введите название книги");
EBookName = Console.ReadLine();
Console.WriteLine("Введите автора книги");
EBookAuthor = Console.ReadLine();
Console.WriteLine("Введите издатель книги");
EPublisher = Console.ReadLine();
Console.WriteLine("Введите год издания книги");
EYearOfPublish = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Введите жанр книги");
EGenre = Console.ReadLine();
using (TablesContext db = new TablesContext())
{
Genre genre = new Genre {
Name = EGenre
};
Book books = new Book {
BookName = EBookName, BookAuthor = EBookAuthor, Publisher = EPublisher, YearOfPublish = EYearOfPublish
};
db.Genres.Add(genre);
db.Books.Add(books);
Console.WriteLine("Введите количество экземпляров книги");
EExemplarAmount = Convert.ToInt32(Console.ReadLine());
for (int i = 0; i < EExemplarAmount; i++)
{
Console.Write("Введите номер экземпляра {0} > ", i + 1);
string EExemplar = Console.ReadLine();
Exemplar exemplars = new Exemplar {
ExemplarNumber = EExemplar, BookId = books.Id, ExemplarStatus = "In Stock"
};
db.Exemplars.Add(exemplars);
}
db.SaveChanges();
Console.WriteLine("Объекты успешно сохранены");
}
}
catch (Exception) { Console.WriteLine("Данные введены не верно"); }
}
public void ShowLibrarian(int id)
{
using (TablesContext db = new TablesContext())
{
Librarian librarian = db.Librarians.Find(id);
Console.WriteLine("Логин: " + librarian.LibLogin);
Console.WriteLine("Имя: " + librarian.LibFirstName);
Console.WriteLine("Фамилия: " + librarian.LibLastName);
Console.WriteLine("Отчество: " + librarian.LibPatronymicName);
Console.WriteLine("Номер телефона: " + librarian.LibPhone);
Console.WriteLine("Адрес: " + librarian.LibAdress);
}
}
public void FindN(string name)
{
try
{
using (TablesContext db = new TablesContext())
{
var books = db.Books.Where(b => b.BookName == name).FirstOrDefault();
var table = new ConsoleTable("Id", "Название", "Автор");
table.AddRow(books.Id, books.BookName, books.BookAuthor);
table.Write();
}
}
catch (Exception) { Console.WriteLine("Поиск не дал результатов, попробуйте повторить запрос"); }
}
public void Show()
{
using (TablesContext db = new TablesContext())
{
var book = db.Books;
Console.WriteLine("Список всех книг:");
var table = new ConsoleTable("Id", "Название", "Автор");
foreach (Book b in book)
{
table.AddRow(b.Id, b.BookName, b.BookAuthor);
}
table.Write();
}
}
public void ShowLibrarian()
{
using (TablesContext db = new TablesContext())
{
var librarians = db.Librarians;
var table = new ConsoleTable("Id", "Логин", "Имя", "Фамилия", "Отчество");
Console.WriteLine("Список всех пользователей:\n");
foreach (Librarian l in librarians)
{
table.AddRow(l.Id, l.LibFirstName, l.LibFirstName, l.LibLastName, l.LibPatronymicName);
}
table.Write();
}
}
public OrderResponse GetOrder(string id)
{
OrderResponse resp = new OrderResponse();
int orderId;
if (int.TryParse(id, out orderId))
{
Order order;
List <Book> booksInOrder = new List <Book>();
using (TablesContext context = new TablesContext())
{
order = context.Orders.Find(orderId);
if (order != null)
{
resp.Status = (int)Constants.STATUSES.OK;
resp.Message = Constants.SUCCESS;
resp.OrderData = new OrderData
{
Id = order.Id,
UserId = order.UserId,
TotalPayment = order.TotalPayment,
Status = order.Status
};
var books = from b in context.BooksInOrders where b.OrderId.Equals(orderId) select b;
if (books != null && books.Count() > 0)
{
books.ToList <BooksInOrder>().ForEach(delegate(BooksInOrder book)
{
booksInOrder.Add(context.Books.Find(book.BookId));
});
resp.OrderData.Books = booksInOrder;
}
}
else
{
resp.Status = (int)Constants.STATUSES.ERROR;
resp.Message = Constants.ORDER_NOT_FOUND;
}
}
}
else
{
resp.Status = (int)Constants.STATUSES.ERROR;
resp.Message = Constants.ENTER_INT;
}
return(resp);
}
public void ShowStudent()
{
using (TablesContext db = new TablesContext())
{
var students = db.Students;
var table = new ConsoleTable("Id", "Логин", "Имя", "Фамилия", "Отчество");
Console.WriteLine("Список всех пользователей:\n");
foreach (Student s in students)
{
table.AddRow(s.Id, s.StudLogin, s.StudFirstName, s.StudLastName, s.StudPatronymicName);
}
table.Write();
}
}
public void ShowStudent(int id)
{
using (TablesContext db = new TablesContext())
{
Student student = db.Students.Find(id);
Console.WriteLine("Логин: " + student.StudLogin);
Console.WriteLine("Имя: " + student.StudFirstName);
Console.WriteLine("Фамилия: " + student.StudLastName);
Console.WriteLine("Отчество: " + student.StudPatronymicName);
Console.WriteLine("Номер читательского билета: " + student.StudCardNumber);
Console.WriteLine("Номер телефона: " + student.StudPhone);
Console.WriteLine("Адрес: " + student.StudAdress);
}
}
public void FindG(string name)
{
using (TablesContext db = new TablesContext())
{
var genres = db.Genres.Where(g => g.Name == name);
var table = new ConsoleTable("Id", "Название");
foreach (Genre g in genres)
{
foreach (Book b in g.Books)
{
table.AddRow(g.Id, b.BookName);
}
}
table.Write();
}
}
public void Remove(int id)
{
try
{
using (TablesContext db = new TablesContext())
{
Book books = db.Books.Find(id);
var exemplar = db.Exemplars.Where(e => e.BookId == id).FirstOrDefault();
db.Exemplars.Remove(exemplar);
db.Books.Remove(books);
db.SaveChanges();
Console.WriteLine("Данные успешно удалены");
}
}
catch (Exception) { Console.WriteLine("Данные введены не верно"); }
}
public String NewUser(User user)
{
using (TablesContext context = new TablesContext())
{
var users = from u in context.Users where u.Email.Equals(user.Email) select u;
if (users == null || users.Count() == 0)
{
context.Users.Add(user);
context.SaveChanges();
return(Constants.USER_CREATED);
}
else
{
return(Constants.USER_ALREADY_EXISTS);
}
}
}
public void FindByGenre(string GenreName)
{
using (TablesContext db = new TablesContext())
{
var book = db.Books.Where(b => b.Genre.Name == GenreName).FirstOrDefault();
var genre = db.Genres.Where(g => g.Name == GenreName).FirstOrDefault();
try
{
Console.WriteLine("Название книги: " + book.BookName);
Console.WriteLine("Автор книги: " + book.BookAuthor);
Console.WriteLine("Жанр книги: " + genre.Name);
Console.WriteLine("Издатель книги: " + book.Publisher);
Console.WriteLine("Год издания книги: " + book.YearOfPublish);
}
catch (Exception e) { Console.WriteLine("Книга не найдена"); }
}
}
public void EditStudent(int id)
{
string ELogin;
string EPassword;
string EFirstName;
string ELastName;
string EPatronymicName;
string ECardNumber;
string EPhone;
string EAdress;
using (TablesContext db = new TablesContext())
{
Student studentId = db.Students.Find(id);
Console.Write("Изменить логин \n {0} -> ", studentId.StudLogin);
ELogin = Console.ReadLine();
Console.Write("Изменить пароль \n {0} -> ", studentId.StudPassword);
EPassword = Console.ReadLine();
Console.Write("Изменить имя \n {0} -> ", studentId.StudFirstName);
EFirstName = Console.ReadLine();
Console.Write("Изменить фамилию \n {0} -> ", studentId.StudLastName);
ELastName = Console.ReadLine();
Console.Write("Изменить отчество \n {0} -> ", studentId.StudPatronymicName);
EPatronymicName = Console.ReadLine();
Console.Write("Изменить номер читательского билета \n {0}->", studentId.StudCardNumber);
ECardNumber = Console.ReadLine();
Console.Write("Изменить номер телефона \n {0} -> ", studentId.StudPhone);
EPhone = Console.ReadLine();
Console.Write("Изменить адрес проживания \n {0} -> ", studentId.StudAdress);
EAdress = Console.ReadLine();
studentId.StudLogin = ELogin;
studentId.StudPassword = EPassword;
studentId.StudFirstName = EFirstName;
studentId.StudLastName = ELastName;
studentId.StudPatronymicName = EPatronymicName;
studentId.StudCardNumber = ECardNumber;
studentId.StudAdress = EAdress;
studentId.StudPhone = EPhone;
db.SaveChanges();
Console.WriteLine("Объекты успешно сохранены\n");
}
}
public String UpdateUser(User updateData)
{
using (TablesContext context = new TablesContext())
{
User user = context.Users.Find(updateData.Id);
if (user != null && !user.Deleted)
{
user.FirstName = updateData.FirstName;
user.LastName = updateData.LastName;
user.Email = updateData.Email;
context.SaveChanges();
return(Constants.USER_UPDATED);
}
else
{
return(Constants.USER_NOT_FOUND);
}
}
}
public CompanyRegistrationModel Get()
{
using (TablesContext tc = new TablesContext())
{
var comps = (from c in tc.companies
join r in tc.registry
on c.Key equals r.Key
select
new CompanyRegistrationModel
{
FieldValue = r.FieldValue,
Name = c.Name,
Company = c.Company,
Industry = c.Industry,
Rank = c.Rank
}).ToList();
return(comps);
}
}
public String UpdateBook(Book updateData)
{
using (TablesContext context = new TablesContext())
{
Book book = context.Books.Find(updateData.Id);
if (book != null && !book.Deleted)
{
book.Name = updateData.Name;
book.Cost = updateData.Cost;
book.Description = updateData.Description;
context.SaveChanges();
return(Constants.BOOK_UPDATED);
}
else
{
return(Constants.BOOK_NOT_FOUND);
}
}
}
