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