public void getHerbariumInventory(string boxNumber)
{
// Database - Program Declaration
DatabaseConnection connection = new DatabaseConnection();
List <HerbariumSheet> herbariumSheets = new List <HerbariumSheet>();
// Query Command Setting
connection.setQuery("SELECT strAccessionNumber, strScientificName, strBoxNumber, strStatus " +
"FROM viewHerbariumInventory " +
"WHERE strBoxNumber = @boxNumber");
connection.addParameter("@boxNumber", SqlDbType.VarChar, boxNumber);
// Query Execution
SqlDataReader sqlData = connection.executeResult();
// Query Result
while (sqlData.Read())
{
herbariumSheets.Add(new HerbariumSheet()
{
AccessionNumber = sqlData[0].ToString(),
ScientificName = sqlData[1].ToString(),
BoxLocation = sqlData[2].ToString(),
Status = sqlData[3].ToString()
});
}
connection.closeResult();
dgrHerbariumSheets.ItemsSource = herbariumSheets;
}
private void getHerbariumSheet()
{
// Database - Program Declaration
DatabaseConnection connection = new DatabaseConnection();
List <HerbariumSheet> herbariumSheets = new List <HerbariumSheet>();
// Query Command Setting
connection.setQuery("SELECT strAccessionNumber, strScientificName, CONVERT(VARCHAR, dateVerified, 107), strCollector " +
"FROM viewHerbariumSheet " +
"WHERE strStatus = 'Verified'");
// Query Execution
SqlDataReader sqlData = connection.executeResult();
// Query Result
while (sqlData.Read())
{
herbariumSheets.Add(new HerbariumSheet()
{
AccessionNumber = sqlData[0].ToString(),
ScientificName = sqlData[1].ToString(),
DateValidated = sqlData[2].ToString(),
Collector = sqlData[3].ToString()
});
}
connection.closeResult();
dgrHerbariumSheets.ItemsSource = herbariumSheets;
}
private void getPlantDeposit()
{
// Database - Program Declaration
DatabaseConnection connection = new DatabaseConnection();
List <PlantDeposit> plantDeposits = new List <PlantDeposit>();
// Query Command Setting
connection.setQuery("SELECT strAccessionNumber, CONVERT(VARCHAR, dateDeposited, 107), strCollector " +
"FROM viewPlantDeposit " +
"WHERE strStatus = 'For Verification'");
// Query Execution
SqlDataReader sqlData = connection.executeResult();
// Query Result
while (sqlData.Read())
{
plantDeposits.Add(new PlantDeposit()
{
AccessionNumber = sqlData[0].ToString(),
DateDeposited = sqlData[1].ToString(),
Collector = sqlData[2].ToString()
});
}
connection.closeResult();
dgrVerifyingDeposit.ItemsSource = plantDeposits;
}
public void getLoanTable()
{
// Database - Program Declaration
DatabaseConnection connection = new DatabaseConnection();
List <PlantLoans> loans = new List <PlantLoans>();
// Query Command Setting
connection.setQuery("SELECT strLoanNumber, strCollector, dateLoan, dateReturning, strDuration, " +
"dateProcessed, strPurpose, strStatus " +
"FROM viewPlantLoans");
// Query Execution
SqlDataReader sqlData = connection.executeResult();
// Query Result
while (sqlData.Read())
{
loans.Add(new PlantLoans()
{
LoanNumber = sqlData[0].ToString(),
Collector = sqlData[1].ToString(),
StartDate = sqlData[2].ToString(),
ReturningDate = sqlData[3].ToString(),
Duration = sqlData[4].ToString(),
DateProcessed = sqlData[5].ToString(),
Purpose = sqlData[6].ToString(),
Status = sqlData[7].ToString()
});
}
connection.closeResult();
dgrPlantLoans.ItemsSource = loans;
}
private void getSpeciesList(List <ListGenus> genera)
{
dgrTaxonSpecies.ItemsSource = null;
List <ListSpecies> species = new List <ListSpecies>();
foreach (ListGenus genus in genera)
{
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT TS.strScientificName, COUNT(HI.intStoredSheetID) - ISNULL(SUM(LS.intCopies), 0) " +
"FROM viewTaxonSpecies TS " +
"LEFT JOIN viewHerbariumInventory HI ON TS.strScientificName = HI.strScientificName AND HI.boolLoanAvailable = 1 " +
"LEFT JOIN tblLoaningSpecies LS ON TS.intSpeciesID = LS.intSpeciesID " +
"LEFT JOIN tblPlantLoanTransaction LT ON LT.intLoanID = LS.intLoanID AND LT.strStatus IN('Approved', 'Requesting') " +
"WHERE TS.strGenusName = @genusname " +
"GROUP BY TS.strScientificName " +
"ORDER BY TS.strScientificName ASC");
connection.addParameter("@genusName", SqlDbType.VarChar, genus.GenusName);
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
if (Convert.ToInt32(sqlData[1]) > 0)
{
species.Add(new ListSpecies()
{
TaxonName = sqlData[0].ToString(),
Specimens = Convert.ToInt32(sqlData[1])
});
}
}
connection.closeResult();
}
dgrTaxonSpecies.ItemsSource = species;
}
private bool isDuplicateHerbarium(PlantDeposit deposit, ref string refAccession, ref string taxonName)
{
bool result;
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT DISTINCT strReferenceAccession, strScientificName " +
"FROM viewHerbariumSheet " +
"WHERE strCollector = @collector " +
"AND strFullLocality = @locality " +
"AND dateCollected = @dateCollected " +
"AND strDescription = @description");
connection.addParameter("@collector", SqlDbType.VarChar, deposit.Collector);
connection.addParameter("@locality", SqlDbType.VarChar, deposit.Locality);
connection.addParameter("@dateCollected", SqlDbType.Date, deposit.DateCollected);
connection.addParameter("@description", SqlDbType.VarChar, deposit.Description);
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
refAccession = sqlData[0].ToString();
taxonName = sqlData[1].ToString();
}
result = sqlData.HasRows;
connection.closeResult();
return(result);
}
public void getAvailableBoxes()
{
// Database - Program Declaration
DatabaseConnection connection = new DatabaseConnection();
List <FamilyBox> familyBoxes = new List <FamilyBox>();
// Query Command Setting
connection.setQuery("SELECT FB.strBoxNumber, FB.strFamilyName, FB.intBoxLimit - COUNT(HI.intStoredSheetID) " +
"FROM viewFamilyBox FB LEFT JOIN viewHerbariumInventory HI ON FB.strFamilyName = HI.strFamilyName " +
"GROUP BY FB.strBoxNumber, FB.strFamilyName, FB.intBoxLimit");
// Query Execution
SqlDataReader sqlData = connection.executeResult();
// Query Result
while (sqlData.Read())
{
familyBoxes.Add(new FamilyBox()
{
BoxNumber = sqlData[0].ToString(),
Family = sqlData[1].ToString(),
BoxLimit = Convert.ToInt32(sqlData[2])
});
}
connection.closeResult();
availableBoxes = familyBoxes;
}
private void checkRecords()
{
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT intAccountID FROM tblAccounts");
SqlDataReader sqlData = connection.executeResult();
noRecords = (sqlData.HasRows) ? false : true;
connection.closeResult();
}
private void txfCommonName_TextChanged(object sender, TextChangedEventArgs e)
{
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT strScientificName FROM viewTaxonSpecies WHERE strCommonName = @name");
connection.addParameter("@name", SqlDbType.VarChar, txfCommonName.Text);
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
cbxScientificName.SelectedItem = sqlData[0].ToString();
}
connection.closeResult();
}
private void getTaxonList()
{
cbxTaxonName.Reset();
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT strScientificName FROM viewTaxonSpecies ORDER BY strScientificName");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
cbxTaxonName.AddItem(sqlData[0]);
}
connection.closeResult();
}
private void getCollectorList()
{
cbxCollector.Items.Clear();
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT strFullName FROM viewCollector");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
cbxCollector.Items.Add(sqlData[0]);
}
connection.closeResult();
}
private void getLocalityList()
{
cbxLocality.Reset();
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT strShortLocation FROM tblLocality ORDER BY strShortLocation");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
cbxLocality.AddItem(sqlData[0]);
}
connection.closeResult();
}
private void getValidatorList()
{
cbxValidator.Reset();
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT strFullName FROM viewValidator ORDER BY strFullName");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
cbxValidator.AddItem(sqlData[0]);
}
connection.closeResult();
}
private void getSpeciesList()
{
cbxScientificName.Items.Clear();
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT strScientificName FROM viewTaxonSpecies");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
cbxScientificName.Items.Add(sqlData[0]);
}
connection.closeResult();
}
private void btnView_Click(object sender, RoutedEventArgs e)
{
// Database - Program Declaration
DatabaseConnection connection = new DatabaseConnection();
HerbariumSheet herbariumSheet = dgrHerbariumSheets.SelectedValue as HerbariumSheet;
// Query Command Setting
connection.setQuery("SELECT strAccessionNumber, picHerbariumSheet, strBoxNumber, strFamilyName, strScientificName, " +
"strCommonName, dateCollected, dateDeposited, dateVerified, strFullLocality, " +
"strCollector, strValidator, strDescription, boolLoanAvailable, strStatus " +
"FROM viewHerbariumInventory " +
"WHERE strAccessionNumber = @accessionNo");
connection.addParameter("@accessionNo", SqlDbType.VarChar, herbariumSheet.AccessionNumber);
// Query Execution
SqlDataReader sqlData = connection.executeResult();
// Query Result
while (sqlData.Read())
{
try
{
byte[] tempBlob = (byte[])sqlData[1];
picHerbariumSheet.Source = getHerbariumSheet(tempBlob);
}
catch (Exception) { }
lblAccessionNumber.Text = sqlData[0].ToString();
lblBox.Text = sqlData[2].ToString();
lblFamilyName.Text = " [" + sqlData[3].ToString() + "]";
lblScientificName.Text = sqlData[4].ToString();
lblCommonName.Text = sqlData[5].ToString();
lblDateCollected.Text = sqlData[6].ToString();
lblDateDeposited.Text = sqlData[7].ToString();
lblDateVerified.Text = sqlData[8].ToString();
lblLocality.Text = sqlData[9].ToString();
lblCollector.Text = sqlData[10].ToString();
lblValidator.Text = sqlData[11].ToString();
lblDescription.Text = sqlData[12].ToString();
lblAvail.Text = (bool)sqlData[13] ? "Available" : "Not Available";
lblStatus.Text = sqlData[14].ToString();
btnAvail.Visibility = (sqlData[14].ToString() == "Loaned") ? Visibility.Hidden : Visibility.Visible;
pnlPlantDeposit.Visibility = Visibility.Visible;
}
connection.closeResult();
}
private void btnVerify_Click(object sender, RoutedEventArgs e)
{
// Database - Program Declaration
DatabaseConnection connection = new DatabaseConnection();
PlantDeposit plantDeposit = dgrVerifyingDeposit.SelectedValue as PlantDeposit;
// Query Command Setting
connection.setQuery("SELECT strAccessionNumber, picHerbariumSheet, " +
"CONVERT(VARCHAR, dateCollected, 107), CONVERT(VARCHAR, dateDeposited, 107), " +
"strFullLocality, strCollector, strDescription " +
"FROM viewPlantDeposit " +
"WHERE strAccessionNumber = @accessionNo");
connection.addParameter("@accessionNo", SqlDbType.VarChar, plantDeposit.AccessionNumber);
// Query Execution
SqlDataReader sqlData = connection.executeResult();
// Query Result
pnlPlantDeposit.Visibility = Visibility.Visible;
while (sqlData.Read())
{
try {
byte[] tempBlob = (byte[])sqlData[1];
picHerbariumSheet.Source = getHerbariumSheet(tempBlob);
}
catch (Exception) { }
lblAccessionNumber.Text = sqlData[0].ToString();
lblDateCollected.Text = sqlData[2].ToString();
lblDateDeposited.Text = sqlData[3].ToString();
lblLocality.Text = sqlData[4].ToString();
lblCollector.Text = sqlData[5].ToString();
lblDescription.Text = sqlData[6].ToString();
plantDetails.AccessionNumber = sqlData[0].ToString();
plantDetails.DateCollected = sqlData[2].ToString();
plantDetails.DateDeposited = sqlData[3].ToString();
plantDetails.Locality = sqlData[4].ToString();
plantDetails.Collector = sqlData[5].ToString();
plantDetails.Description = sqlData[6].ToString();
}
connection.closeResult();
getSpeciesList();
}
private int getLoanAvailable()
{
int count = 0;
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT COUNT(intStoredSheetID) FROM viewHerbariumInventory WHERE boolLoanAvailable = 1");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
count = Convert.ToInt32(sqlData[0]);
}
connection.closeResult();
return(count);
}
private int getFamilyBox()
{
int count = 0;
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT COUNT(intBoxID) FROM viewFamilyBox");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
count = Convert.ToInt32(sqlData[0]);
}
connection.closeResult();
return(count);
}
private int getVerifiedSpecies()
{
int count = 0;
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT COUNT(intSpeciesID) FROM viewTaxonSpecies WHERE boolSpeciesIdentified = 1");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
count = Convert.ToInt32(sqlData[0]);
}
connection.closeResult();
return(count);
}
private void getAccessionList(string species)
{
cbxReferenceNumber.Reset();
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT DISTINCT strReferenceAccession FROM viewHerbariumSheet WHERE strScientificName = @taxonName ORDER BY strReferenceAccession");
connection.addParameter("@taxonName", System.Data.SqlDbType.VarChar, species);
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
cbxReferenceNumber.AddItem(sqlData[0]);
}
connection.closeResult();
}
private void getAccessionNumbers(string taxonname)
{
cbxReferenceNumber.Items.Clear();
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT DISTINCT strReferenceAccession FROM viewHerbariumSheet WHERE strScientificName = @taxonName");
connection.addParameter("@taxonName", SqlDbType.VarChar, taxonname);
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
cbxReferenceNumber.Items.Add(sqlData[0]);
}
connection.closeResult();
}
public void getFamilyBoxes()
{
DatabaseConnection connection = new DatabaseConnection();
List <FamilyBox> familyBoxes = new List <FamilyBox>();
connection.setQuery("SELECT strBoxNumber, strFamilyName FROM viewFamilyBox");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
familyBoxes.Add(new FamilyBox()
{
BoxNumber = sqlData[0].ToString(),
Family = sqlData[1].ToString()
});
}
connection.closeResult();
lstFamilyBox.ItemsSource = familyBoxes;
}
private void getFamilyList()
{
List <ListFamily> families = new List <ListFamily>();
dgrTaxonFamilies.Items.Clear();
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT strFamilyName FROM viewTaxonFamily ORDER BY strFamilyName ASC");
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
families.Add(new ListFamily()
{
FamilyName = sqlData[0].ToString()
});
}
connection.closeResult();
dgrTaxonFamilies.ItemsSource = families;
}
private void getGenusList(List <ListFamily> families)
{
dgrTaxonGenera.ItemsSource = null;
List <ListGenus> genera = new List <ListGenus>();
foreach (ListFamily family in families)
{
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT strGenusName FROM viewTaxonGenus WHERE strFamilyName = @familyName ORDER BY strGenusName ASC");
connection.addParameter("@familyName", SqlDbType.VarChar, family.FamilyName);
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
genera.Add(new ListGenus()
{
GenusName = sqlData[0].ToString()
});
}
connection.closeResult();
}
dgrTaxonGenera.ItemsSource = genera;
}
private void cbxScientificName_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
DatabaseConnection connection = new DatabaseConnection();
if (cbxScientificName.SelectedIndex == -1)
{
txfCommonName.Text = "";
}
else
{
connection.setQuery("SELECT strCommonName FROM viewTaxonSpecies WHERE strScientificName = @name");
connection.addParameter("@name", SqlDbType.VarChar, cbxScientificName.SelectedItem.ToString());
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
txfCommonName.Text = sqlData[0].ToString();
}
connection.closeResult();
getAccessionNumbers(cbxScientificName.SelectedItem.ToString());
if (isDuplicateHerbarium(plantDetails, ref referenceAccession, ref scientificName))
{
chkIsDuplicate.IsChecked = true;
chkIsDuplicate_CheckChanged(chkIsDuplicate, null);
cbxReferenceNumber.SelectedItem = referenceAccession;
cbxScientificName.SelectedItem = scientificName;
}
else
{
chkIsDuplicate.IsChecked = false;
chkIsDuplicate_CheckChanged(chkIsDuplicate, null);
}
}
}
private void btnClassify_Click(object sender, RoutedEventArgs e)
{
getAvailableBoxes();
// Database - Program Declaration
DatabaseConnection connection = new DatabaseConnection();
HerbariumSheet herbariumSheet = dgrHerbariumSheets.SelectedValue as HerbariumSheet;
// Query Command Setting
connection.setQuery("SELECT strAccessionNumber, strReferenceAccession, picHerbariumSheet, " +
"strFamilyName, strScientificName, strCommonName, CONVERT(VARCHAR, dateCollected, 107), " +
"CONVERT(VARCHAR, dateDeposited, 107), CONVERT(VARCHAR, dateVerified, 107), strFullLocality, " +
"strCollector, strValidator, strDescription " +
"FROM viewHerbariumSheet " +
"WHERE strAccessionNumber = @accessionNo");
connection.addParameter("@accessionNo", SqlDbType.VarChar, herbariumSheet.AccessionNumber);
// Query Execution
SqlDataReader sqlData = connection.executeResult();
// Query Result
while (sqlData.Read())
{
string family = sqlData[3].ToString();
var result = from box in availableBoxes
where box.Family == family
where box.BoxLimit > 0
select box.BoxNumber;
if (result.Count() == 0)
{
MessageBox.Show("No Available Family Box for this Herbarium Sheet");
}
else
{
try
{
byte[] tempBlob = (byte[])sqlData[2];
picHerbariumSheet.Source = getHerbariumSheet(tempBlob);
} catch (Exception) { }
lblAccessionNumber.Text = sqlData[0].ToString();
lblReferenceNumber.Text = sqlData[1].ToString();
lblScientificName.Text = sqlData[4].ToString();
lblCommonName.Text = sqlData[5].ToString();
lblDateCollected.Text = sqlData[6].ToString();
lblDateDeposited.Text = sqlData[7].ToString();
lblDateVerified.Text = sqlData[8].ToString();
lblLocality.Text = sqlData[9].ToString();
lblCollector.Text = sqlData[10].ToString();
lblValidator.Text = sqlData[11].ToString();
lblDescription.Text = sqlData[12].ToString();
lblBox.Text = result.First();
lblFamilyName.Text = " [" + sqlData[3].ToString() + "]";
pnlPlantDeposit.Visibility = Visibility.Visible;
}
}
connection.closeResult();
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (validateLoanSpecies())
{
MessageBoxResult response = MessageBox.Show("Do you want to Process this Loan Transaction?",
"Confirm Transaction",
MessageBoxButton.YesNo,
MessageBoxImage.Question);
if (response == MessageBoxResult.Yes)
{
int status;
string loanStatus = (StaticData.role == "STUDENT ASSISTANT") ? "Requesting" : "Approved";
string loanNumber = "";
DatabaseConnection connection = new DatabaseConnection();
connection.setStoredProc("dbo.procProcessLoan");
connection.addSprocParameter("@collectorName", SqlDbType.VarChar, lblCollector.Text);
connection.addSprocParameter("@startDate", SqlDbType.Date, loanDate);
connection.addSprocParameter("@endDate", SqlDbType.Date, returnDate);
connection.addSprocParameter("@purpose", SqlDbType.VarChar, lblPurpose.Text);
connection.addSprocParameter("@status", SqlDbType.VarChar, loanStatus);
status = connection.executeProcedure();
if (status == 0)
{
connection.setQuery("SELECT strLoanNumber " +
"FROM viewPlantLoans " +
"WHERE strCollector = @collector AND dateLoan = @startdate AND dateReturning = @enddate");
connection.addParameter("@collector", SqlDbType.VarChar, lblCollector.Text);
connection.addParameter("@startdate", SqlDbType.Date, loanDate);
connection.addParameter("@enddate", SqlDbType.Date, returnDate);
SqlDataReader sqlData = connection.executeResult();
while (sqlData.Read())
{
loanNumber = sqlData[0].ToString();
}
connection.closeResult();
foreach (ListSpecies list in dgrTaxonSpecies.Items)
{
if (list.IsChecked)
{
connection.setStoredProc("dbo.procLoanPlants");
connection.addSprocParameter("@loanNumber", SqlDbType.VarChar, loanNumber);
connection.addSprocParameter("@taxonName", SqlDbType.VarChar, list.TaxonName);
connection.addSprocParameter("@copies", SqlDbType.Int, list.Copies);
status = connection.executeProcedure();
if (status == 1)
{
break;
}
}
}
switch (status)
{
case 0:
MessageBox.Show("Plant Deposit Transaction Processed Successfully");
break;
case 1:
MessageBox.Show("Plant Deposit Transaction Processed with Some Error Records");
break;
}
}
else
{
MessageBox.Show("The System had run to an Error");
}
}
pnlPlantLoaningForm.Visibility = Visibility.Hidden;
getLoanTable();
}
}