private void BookIssueDetailDialog_Load(object sender, System.EventArgs e) { try { using (var connection = new SqlConnection(ConnectionString)) { var command = new SqlCommand("Select s.StudentId,s.Name,d.DepartmentShortName,b.Isbn,b.Title,br.DayOfBorrowed,br.DayOfReturn,d.DepartmentFullName from Borrow br join Student s on br.StudentId=s.Id join Book b on br.BookId=b.BookId join Department d on br.DepartmentId=d.DepartmentId where s.StudentId=@studentId", connection); command.Parameters.AddWithValue("@studentId", studentId); connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { var objBookIssueDetails = new BookIssueDetails(); objBookIssueDetails.BookBrowwerId = reader[0].ToString(); objBookIssueDetails.Name = reader[1].ToString(); objBookIssueDetails.Department = reader[2].ToString() == "" ? reader[7].ToString() : reader[2].ToString(); objBookIssueDetails.Isbn = reader[3].ToString(); objBookIssueDetails.Title = reader[4].ToString(); objBookIssueDetails.IssueDate = Convert.ToDateTime(reader[5]); objBookIssueDetails.ReturnDate = Convert.ToDateTime(reader[6]); var modifiedIssueDate = string.Format("{0:dd-MM-yy}", objBookIssueDetails.IssueDate); var modifiedReturnDate = string.Format("{0:dd-MM-yy}", objBookIssueDetails.ReturnDate); var modifiedCurrentDate = string.Format("{0:yyyy-MM-dd}", DateTime.Now); var daysLeft = (Convert.ToDateTime(objBookIssueDetails.ReturnDate) - Convert.ToDateTime(modifiedCurrentDate)).Days; // var dleft = 0; if (daysLeft < 0) { availableIcon.Image = Resources.close; } else { availableIcon.Image = Resources.check_blue; } studentIdDisplayLabel.Text = objBookIssueDetails.BookBrowwerId; studentNameDisplayLabel.Text = objBookIssueDetails.Name; Studentdepartmentdisplaylabel.Text = objBookIssueDetails.Department; bookTitleDisplayLabel.Text = objBookIssueDetails.Title; bookIsbnDisplaylabel.Text = objBookIssueDetails.Isbn; issueDataDisplayLabel.Text = modifiedIssueDate; returnDisplayLabel.Text = modifiedReturnDate; dayaLeftDisplayLabel.Text = daysLeft.ToString(); } } } catch (Exception) { throw; } }
private void LoadGridData() { using (var connection = new SqlConnection(ConnectionString)) { var command = new SqlCommand(Query, connection); connection.Open(); var reader = command.ExecuteReader(); BookReservationDetailsView.Items.Clear(); while (reader.Read()) { var objBookIssueDetails = new BookIssueDetails(); objBookIssueDetails.BookBrowwerId = reader[0].ToString(); objBookIssueDetails.Name = reader[1].ToString(); objBookIssueDetails.Department = reader[2].ToString() == "" ? reader[7].ToString() : reader[2].ToString(); objBookIssueDetails.Isbn = reader[3].ToString(); objBookIssueDetails.Title = reader[4].ToString(); objBookIssueDetails.IssueDate = Convert.ToDateTime(reader[5]); objBookIssueDetails.ReturnDate = Convert.ToDateTime(reader[6]); var modifiedIssueDate = string.Format("{0:dd-MM-yy}", objBookIssueDetails.IssueDate); var modifiedReturnDate = string.Format("{0:dd-MM-yy}", objBookIssueDetails.ReturnDate); var modifiedCurrentDate = string.Format("{0:yyyy-MM-dd}", DateTime.Now); var daysLeft = (Convert.ToDateTime(objBookIssueDetails.ReturnDate) - Convert.ToDateTime(modifiedCurrentDate)).Days; var reservationDetailsListView = new ListViewItem(objBookIssueDetails.BookBrowwerId); reservationDetailsListView.SubItems.Add(objBookIssueDetails.Name); reservationDetailsListView.SubItems.Add(objBookIssueDetails.Department); reservationDetailsListView.SubItems.Add(objBookIssueDetails.Title); reservationDetailsListView.SubItems.Add(objBookIssueDetails.Isbn); reservationDetailsListView.SubItems.Add(modifiedIssueDate); reservationDetailsListView.SubItems.Add(modifiedReturnDate); if (daysLeft < 0) { // reservationDetailsListView.UseItemStyleForSubItems = false; reservationDetailsListView.SubItems.Add("Expired (" + +(-daysLeft) + " day(s)" + " )"); reservationDetailsListView.ForeColor = Color.White; reservationDetailsListView.BackColor = Color.Tomato; reservationDetailsListView.Font = new Font("Verdana", 10); } else { reservationDetailsListView.Font = new Font("Verdana", 10); reservationDetailsListView.SubItems.Add(daysLeft.ToString(CultureInfo.InvariantCulture)); } BookReservationDetailsView.Items.Add(reservationDetailsListView); CountLabel.Text = BookReservationDetailsView.Items.Count.ToString() + @" items"; } } }
private void ReservationFilterComboBox_DropDownClosed(object sender, EventArgs e) { try { using (var connection = new SqlConnection(ConnectionString)) { var command = new SqlCommand(Query, connection); connection.Open(); var reader = command.ExecuteReader(); BookReservationDetailsView.Items.Clear(); while (reader.Read()) { var bookReservationDetails = new BookIssueDetails { BookBrowwerId = reader[0].ToString(), Name = reader[1].ToString(), Department = reader[2].ToString(), Isbn = reader[3].ToString(), Title = reader[4].ToString(), IssueDate = Convert.ToDateTime(reader[5]), ReturnDate = Convert.ToDateTime(reader[6]) }; var modifiedIssueDate = string.Format("{0:dd-MM-yy}", bookReservationDetails.IssueDate); var modifiedReturnDate = string.Format("{0:dd-MM-yy}", bookReservationDetails.ReturnDate); var modifiedCurrentDate = string.Format("{0:yyyy-MM-dd}", DateTime.Now); var daysLeft = (Convert.ToDateTime(bookReservationDetails.ReturnDate) - Convert.ToDateTime(modifiedCurrentDate)).Days; var reservationDetailsListView = new ListViewItem(bookReservationDetails.BookBrowwerId); reservationDetailsListView.SubItems.Add(bookReservationDetails.Name); reservationDetailsListView.SubItems.Add(bookReservationDetails.Department); reservationDetailsListView.SubItems.Add(bookReservationDetails.Title); reservationDetailsListView.SubItems.Add(bookReservationDetails.Isbn); reservationDetailsListView.SubItems.Add(modifiedIssueDate); reservationDetailsListView.SubItems.Add(modifiedReturnDate); if (ReservationFilterComboBox.Text == @"Expired") { if (daysLeft < 0) { reservationDetailsListView.SubItems.Add("Expired (" + (-daysLeft) + " )"); BookReservationDetailsView.Items.Add(reservationDetailsListView); CountLabel.Text = BookReservationDetailsView.Items.Count + @" items"; CountLabel.Show(); } } else { if (daysLeft >= 0) { reservationDetailsListView.SubItems.Add(daysLeft.ToString()); BookReservationDetailsView.Items.Add(reservationDetailsListView); CountLabel.Text = BookReservationDetailsView.Items.Count.ToString() + @" items"; CountLabel.Show(); } } } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void ReservationSearchButton_Click(object sender, EventArgs e) { try { if (ReservationSearchTextBox.Text != string.Empty) { using ( var connection = new SqlConnection(ConnectionString)) { const string searchQuery = "Select s.StudentId,s.Name,d.DepartmentShortName,b.Isbn,b.Title,br.DayOfBorrowed,br.DayOfReturn from Borrow br join Student s on br.StudentId=s.Id join Book b on br.BookId=b.BookId join Department d on br.DepartmentId=d.DepartmentId where s.StudentId=@searchTerm"; var command = new SqlCommand(searchQuery, connection); command.Parameters.AddWithValue("@searchTerm", ReservationSearchTextBox.Text); connection.Open(); var reader = command.ExecuteReader(); BookReservationDetailsView.Items.Clear(); reader.Read(); var bookReservationDetails = new BookIssueDetails { BookBrowwerId = reader[0].ToString(), Name = reader[1].ToString(), Department = reader[2].ToString(), Isbn = reader[3].ToString(), Title = reader[4].ToString(), IssueDate = Convert.ToDateTime(reader[5]), ReturnDate = Convert.ToDateTime(reader[6]) }; var modifiedIssueDate = string.Format("{0:dd-MM-yy}", bookReservationDetails.IssueDate); var modifiedReturnDate = string.Format("{0:dd-MM-yy}", bookReservationDetails.ReturnDate); var currentdate = DateTime.Now.ToShortDateString(); var daysLeft = (int)(Convert.ToDateTime(string.Format("{0:MM/dd/yyyy}", bookReservationDetails.ReturnDate)) - Convert.ToDateTime(currentdate)).TotalDays; var reservationDetailsListView = new ListViewItem(bookReservationDetails.BookBrowwerId); reservationDetailsListView.SubItems.Add(bookReservationDetails.Name); reservationDetailsListView.SubItems.Add(bookReservationDetails.Department); reservationDetailsListView.SubItems.Add(bookReservationDetails.Title); reservationDetailsListView.SubItems.Add(bookReservationDetails.Isbn); reservationDetailsListView.SubItems.Add(modifiedIssueDate); reservationDetailsListView.SubItems.Add(modifiedReturnDate); if (daysLeft < 0) { reservationDetailsListView.SubItems.Add("Expired (" + +(-daysLeft) + " day(s)" + " )"); } else { reservationDetailsListView.SubItems.Add(daysLeft.ToString(CultureInfo.InvariantCulture)); } BookReservationDetailsView.Items.Add(reservationDetailsListView); CountLabel.Hide(); } } else { var objDialogWindow = new DialogWindow("Please enter a valid ID to search", "Warning Dialog", DialogWindow.DialogBoxIconType.Exclamation); objDialogWindow.ShowDialog(); } } catch (Exception ex) { var objDialogWindow = new DialogWindow(@"Nothing found for ID '" + " " + ReservationSearchTextBox.Text + @" '", "Warning Dialog", DialogWindow.DialogBoxIconType.Exclamation); objDialogWindow.ShowDialog(); } }