private void bttnPrint_Click(object sender, System.EventArgs e)
{
if (lvBooks.Items.Count > 0)
{
if (tabCategory.SelectedTab.Text.Equals("All Category"))
{
FORMViewer.sDeploy = "Books All";
FORMViewer.sSQL = "SELECT tblCategory.CategoryName, tblBooks.BookNo, tblBooks.ISBN, tblBooks.BookTitle, tblBooks.Qty, tblBooks.Barrowed, [tblBooks.Qty]-[tblBooks.Barrowed] AS Available " +
"FROM tblCategory RIGHT JOIN tblBooks ON tblCategory.IndexCategory = tblBooks.CategoryIndex " +
"GROUP BY tblCategory.CategoryName, tblBooks.BookNo, tblBooks.ISBN, tblBooks.BookTitle, tblBooks.Qty, tblBooks.Barrowed, [tblBooks.Qty]-[tblBooks.Barrowed] " +
"ORDER BY tblBooks.BookNo ASC";
}
else
{
FORMViewer.sDeploy = "Books List";
FORMViewer.sCategory = tabCategory.SelectedTab.Text;
FORMViewer.sSQL = "SELECT tblCategory.CategoryName, tblBooks.BookNo, tblBooks.ISBN, tblBooks.BookTitle, tblBooks.Qty, tblBooks.Barrowed, [tblBooks.Qty]-[tblBooks.Barrowed] AS Available " +
"FROM tblCategory RIGHT JOIN tblBooks ON tblCategory.IndexCategory = tblBooks.CategoryIndex " +
"GROUP BY tblCategory.CategoryName, tblBooks.BookNo, tblBooks.ISBN, tblBooks.BookTitle, tblBooks.Qty, tblBooks.Barrowed, [tblBooks.Qty]-[tblBooks.Barrowed] " +
"HAVING (((tblCategory.CategoryName)='" + tabCategory.SelectedTab.Text + "'))" +
"ORDER BY tblBooks.BookNo ASC";
}
try
{
FORMViewer.sTable = "tblBooks";
FORMViewer sForm = new FORMViewer();
sForm.ShowDialog();
}
catch (Exception ex) {}
}
else
{
MessageBox.Show("No records to preview. Pls try again", clsVariables.sMSGBOX, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void bttnHistory_Click(object sender, System.EventArgs e)
{
if (lvBooks.Items.Count > 0)
{
try
{
if (MessageBox.Show("Do you want to print the list of adjustments in the selected record?", clsVariables.sMSGBOX, MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.No)
{
FORMBooks_Monitoring.sBookNumber = lvBooks.Items[lvBooks.FocusedItem.Index].SubItems[0].Text;
FORMBooks_Monitoring sForm = new FORMBooks_Monitoring();
sForm.ShowDialog();
}
else
{
FORMViewer.sDeploy = "Adjustments";
FORMViewer.sBookNO = lvBooks.Items[lvBooks.FocusedItem.Index].SubItems[0].Text;
FORMViewer.sBookTitle = lvBooks.Items[lvBooks.FocusedItem.Index].SubItems[2].Text;
FORMViewer.sISBN = lvBooks.Items[lvBooks.FocusedItem.Index].SubItems[1].Text;
FORMViewer.sSQL = "SELECT tblBooks.BookNo, tblBooks.ISBN, tblBooks.BookTitle, tblCategory.CategoryName, tblLibrarian.LibrarianID, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS Fullname, tblAdjustments.DateAdjust " +
"FROM tblLibrarian INNER JOIN (tblCategory RIGHT JOIN (tblBooks RIGHT JOIN tblAdjustments ON tblBooks.BookNo = tblAdjustments.BookNumber) ON tblCategory.IndexCategory = tblBooks.CategoryIndex) ON tblLibrarian.LibrarianID = tblAdjustments.AdjustBy " +
"WHERE tblBooks.BookNo LIKE '" + lvBooks.Items[lvBooks.FocusedItem.Index].SubItems[0].Text + "' " +
"ORDER BY tblLibrarian.LibrarianID ASC";
FORMViewer.sTable = "tblAdjustments";
FORMViewer sForm = new FORMViewer();
sForm.ShowDialog();
}
}
catch (ArgumentOutOfRangeException aooreE) { MessageBox.Show("" + aooreE.Message); }
catch (NullReferenceException nreE) {}
}
else
{
MessageBox.Show("No record. Pls. select a new record", clsVariables.sMSGBOX, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void bttnPrint_Click(object sender, System.EventArgs e)
{
FORMViewer.sDeploy = "Librarians";
FORMViewer.sSQL = "SELECT tblLibrarian.LibrarianID, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS Fullname, tblLibrarian.StreetAddress, tblZipCodeList.CityTown, tblZipCodeList.Province, tblLibrarian.ContactNo, tblLibrarian.EmailAddress, tblLibrarian.UserName " +
"FROM tblLibrarian LEFT JOIN tblZipCodeList ON tblLibrarian.ZipCode = tblZipCodeList.ZipCode ORDER BY tblLibrarian.LibrarianID ASC";
FORMViewer.sTable = "tblLibrarian";
FORMViewer sForm = new FORMViewer();
sForm.ShowDialog();
}
private void bttnSelect_Click(object sender, System.EventArgs e)
{
if (listView1.Items.Count > 0)
{
try
{
if (sSYSelect == "Barrowers")
{
FORMBarrowers_Modify.publicBarrowerList.setSchoolYear(listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text, listView1.Items[listView1.FocusedItem.Index].SubItems[1].Text);
}
else if (sSYSelect == "Report List")
{
FORMViewer.sDeploy = "Barrowers";
FORMViewer.sSchoolYear = listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text;
FORMViewer.sSQL = "SELECT [YearStart] & ' - ' & [YearEnd] AS SY, tblCourse.CourseName, tblLevel.LevelName, tblBarrowers.BarrowerID, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS Fullname, " +
"tblBarrowers.StreetAddress, tblZipCodeList.CityTown, tblZipCodeList.Province, tblZipCodeList.ZipCode, tblBarrowers.ContactNO, tblBarrowers.EmailAddress " +
"FROM tblZipCodeList RIGHT JOIN (tblSchoolYear INNER JOIN (tblLevel INNER JOIN (tblCourse INNER JOIN tblBarrowers ON tblCourse.IndexCourse = tblBarrowers.Course) " +
"ON tblLevel.IndexLevel = tblBarrowers.LevelIndex) ON tblSchoolYear.IndexSY = tblBarrowers.SchoolYear) ON tblZipCodeList.ZipCode = tblBarrowers.ZipCode " +
"WHERE tblSchoolYear.IndexSY = " + listView1.Items[listView1.FocusedItem.Index].SubItems[1].Text + " " +
"ORDER BY [YearStart] & ' - ' & [YearEnd] ASC, tblCourse.CourseName ASC, tblLevel.LevelName ASC, tblBarrowers.BarrowerID ASC";
FORMViewer.sTable = "tblBarrowers";
FORMViewer sForm = new FORMViewer();
sForm.ShowDialog();
}
else if (sSYSelect == "Report Fines")
{
FORMViewer.sDeploy = "Barrowers Fines";
FORMViewer.sSchoolYear = listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text;
FORMViewer.sSQL = "SELECT [YearStart] & ' - ' & [YearEnd] AS SY, tblCourse.CourseName, tblLevel.LevelName, tblBarrowed.Returned, tblBarrowers.BarrowerID, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS Fullname, Sum(tblBarrowed.Fines) AS TotalFines " +
"FROM tblSchoolYear RIGHT JOIN (tblLevel RIGHT JOIN (tblCourse RIGHT JOIN (tblBarrowers RIGHT JOIN tblBarrowed ON tblBarrowers.BarrowerID = tblBarrowed.BarrowerID) ON tblCourse.IndexCourse = tblBarrowers.Course) ON tblLevel.IndexLevel = tblBarrowers.LevelIndex) ON tblSchoolYear.IndexSY = tblBarrowers.SchoolYear " +
"GROUP BY [YearStart] & ' - ' & [YearEnd], tblCourse.CourseName, tblLevel.LevelName, tblBarrowed.Returned, tblBarrowers.BarrowerID, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] " +
"HAVING ((([YearStart] & ' - ' & [YearEnd])='" + listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text + "') AND ((tblBarrowed.Returned)=Yes) AND ((Sum(tblBarrowed.Fines))<>0)) " +
"ORDER BY [YearStart] & ' - ' & [YearEnd] ASC, tblCourse.CourseName ASC, tblLevel.LevelName ASC, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] ASC";
FORMViewer.sTable = "tblBarrowers";
FORMViewer sForm = new FORMViewer();
sForm.ShowDialog();
}
else if (sSYSelect == "Report Barrowed Books")
{
FORMViewer.sDeploy = "Barrowed Books";
FORMViewer.sSchoolYear = listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text;
FORMViewer.sSQL = "SELECT [YearStart] & ' - ' & [YearEnd] AS SY, tblBarrowers.BarrowerID, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS Fullname, tblBooks.BookNo, tblBooks.ISBN, tblBooks.BookTitle, tblCategory.CategoryName, tblBarrowed.DateBarrowed, tblBarrowed.DateDue, tblBarrowed.Returned " +
"FROM tblSchoolYear RIGHT JOIN (tblCategory INNER JOIN (tblBooks INNER JOIN (tblBarrowers RIGHT JOIN tblBarrowed ON tblBarrowers.BarrowerID = tblBarrowed.BarrowerID) ON tblBooks.BookNo = tblBarrowed.BookNo) ON tblCategory.IndexCategory = tblBooks.CategoryIndex) ON tblSchoolYear.IndexSY = tblBarrowers.SchoolYear " +
"WHERE ((([YearStart] & ' - ' & [YearEnd])='" + listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text + "') AND ((tblBarrowed.Returned)=No)) " +
"ORDER BY [YearStart] & ' - ' & [YearEnd] ASC, tblBooks.BookNo ASC";
FORMViewer.sTable = "tblBarrowed";
FORMViewer sForm = new FORMViewer();
sForm.ShowDialog();
}
else if (sSYSelect == "Report Due Books") //Report Returned Books
{
FORMViewer.sDeploy = "Due Books";
FORMViewer.sSchoolYear = listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text;
FORMViewer.sSQL = "SELECT [YearStart] & ' - ' & [YearEnd] AS SY, tblBarrowers.BarrowerID, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS Fullname, tblBooks.BookNo, tblBooks.ISBN, tblBooks.BookTitle, tblCategory.CategoryName, tblBarrowed.DateBarrowed, tblBarrowed.DateDue, tblBarrowed.DateRetured, tblBarrowed.DelayedDays, tblBarrowed.Returned " +
"FROM tblSchoolYear RIGHT JOIN (tblCategory INNER JOIN (tblBooks INNER JOIN (tblBarrowers RIGHT JOIN tblBarrowed ON tblBarrowers.BarrowerID = tblBarrowed.BarrowerID) ON tblBooks.BookNo = tblBarrowed.BookNo) ON tblCategory.IndexCategory = tblBooks.CategoryIndex) ON tblSchoolYear.IndexSY = tblBarrowers.SchoolYear " +
"WHERE ((([YearStart] & ' - ' & [YearEnd])='" + listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text + "') AND ((tblBarrowed.DateDue)<=Now()) AND ((tblBarrowed.Returned)=No)) " +
"ORDER BY tblBooks.BookNo ASC";
FORMViewer.sTable = "tblBarrowed";
FORMViewer sForm = new FORMViewer();
sForm.ShowDialog();
}
else if (sSYSelect == "Report Returned Books")
{
FORMViewer.sDeploy = "Returned Books";
FORMViewer.sSchoolYear = listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text;
FORMViewer.sSQL = "SELECT [YearStart] & ' - ' & [YearEnd] AS SY, tblBarrowers.BarrowerID, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS Fullname, tblBooks.BookNo, tblBooks.ISBN, tblBooks.BookTitle, tblCategory.CategoryName, tblBarrowed.DateBarrowed, tblBarrowed.DateDue, tblBarrowed.DateRetured, tblBarrowed.DelayedDays, tblBarrowed.Returned " +
"FROM tblSchoolYear RIGHT JOIN (tblCategory INNER JOIN (tblBooks INNER JOIN (tblBarrowers RIGHT JOIN tblBarrowed ON tblBarrowers.BarrowerID = tblBarrowed.BarrowerID) ON tblBooks.BookNo = tblBarrowed.BookNo) ON tblCategory.IndexCategory = tblBooks.CategoryIndex) ON tblSchoolYear.IndexSY = tblBarrowers.SchoolYear " +
"WHERE ((([YearStart] & ' - ' & [YearEnd])='" + listView1.Items[listView1.FocusedItem.Index].SubItems[0].Text + "') AND ((tblBarrowed.Returned)=Yes)) " +
"ORDER BY tblBooks.BookNo ASC";
FORMViewer.sTable = "tblBarrowed";
FORMViewer sForm = new FORMViewer();
sForm.ShowDialog();
}
this.Close();
}
catch (Exception ex) {}
}
else
{
MessageBox.Show("No records to preview. Pls try again", clsVariables.sMSGBOX, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}