public static List <BookDelivery> GetAllBookDelivery(string fioReader)
{
string query = "select dateOfIssue,returnDate,fioReader,nameBook,fioLibrarian,BookDelivery.idInstance,CURDATE()-dateOfIssue" +
" from BookDelivery inner join Books inner join Readers" +
" inner join Librarians inner join Instances" +
" on BookDelivery.idReader = Readers.idReader" +
" and BookDelivery.idInstance = Instances.idInstance" +
" and BookDelivery.idLibrarian = Librarians.idLibrarian" +
" and Books.idBook = Instances.idBook " +
" where fioReader = '" + fioReader + "'" +
" and returnDate is null";
List <BookDelivery> result = new List <BookDelivery>();
Connection.Open();
MySqlCommand command = new MySqlCommand(query, Connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
result.Add(new BookDelivery(DateParser.FromStringToDate(reader[0].ToString()),
DateParser.FromStringToDate(reader[1].ToString()),
reader[2].ToString(),
reader[3].ToString(), reader[4].ToString(), Convert.ToInt32(reader[5]), Convert.ToInt32(reader[6])));
}
command.Dispose();
Connection.Close();
return(result);
}
public static List <BookDelivery> GetAllBookDelivery(List <QuerySettings> searchSettings, bool onlyDebtors)
{
string query = "select dateOfIssue,returnDate,fioReader,nameBook,fioLibrarian,BookDelivery.idInstance,CURDATE()-dateOfIssue" +
" from BookDelivery inner join Books inner join Readers" +
" inner join Librarians inner join Instances" +
" on BookDelivery.idReader = Readers.idReader" +
" and BookDelivery.idInstance = Instances.idInstance" +
" and BookDelivery.idLibrarian = Librarians.idLibrarian" +
" and Books.idBook = Instances.idBook ";
bool wasWhereWritten = false;
if (onlyDebtors)
{
query += "where fioReader in((select fioReader" +
" from Readers inner join BookDelivery" +
" on BookDelivery.idReader = Readers.idReader" +
" where returnDate is null and dateOfIssue<CURDATE()-30" +
" group by fioReader)) ";
wasWhereWritten = true;
}
for (int i = 0; i < searchSettings.Count; i++)
{
if (!wasWhereWritten)
{
query += " where " + searchSettings[i].Column + " Like '" + searchSettings[i].Value + "%'";
wasWhereWritten = true;
}
else
{
query += " and " + searchSettings[i].Column + " Like '" + searchSettings[i].Value + "%'";
}
}
List <BookDelivery> result = new List <BookDelivery>();
Connection.Open();
MySqlCommand command = new MySqlCommand(query, Connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
result.Add(new BookDelivery(DateParser.FromStringToDate(reader[0].ToString()),
DateParser.FromStringToDate(reader[1].ToString()),
reader[2].ToString(),
reader[3].ToString(), reader[4].ToString(), Convert.ToInt32(reader[5]), Convert.ToInt32(reader[6])));
}
command.Dispose();
Connection.Close();
return(result);
}
