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 changeAvailability(string AccessionNumber, bool AvailableStatus)
{
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("UPDATE tblStoredHerbarium " +
"SET boolLoanAvailable = @availability " +
"WHERE intHerbariumSheetID = (SELECT intHerbariumSheetID " +
"FROM viewHerbariumSheet " +
"WHERE strAccessionNumber = @accessionNumber)");
connection.addParameter("@availability", SqlDbType.Bit, AvailableStatus);
connection.addParameter("@accessionNumber", SqlDbType.VarChar, AccessionNumber);
connection.executeCommand();
string message = AvailableStatus ?
"Herbarium Sheet is Now Available for Loaning" :
"Herbarium Sheet is Now Unavailable for Loaning";
MessageBox.Show(message, "Record Saved", MessageBoxButton.OK, MessageBoxImage.Information);
pnlPlantDeposit.Visibility = Visibility.Hidden;
btnReturn_Click(btnReturn, null);
getFamilyBoxes();
}
private void btnLogin_Click(object sender, RoutedEventArgs e)
{
txfError.Visibility = Visibility.Collapsed;
txfSuccess.Visibility = Visibility.Collapsed;
if (noRecords)
{
bool matchUsername = (txfUsername.Text == DefaultUsername);
bool matchPassword = (txfPassword.Password == DefaultPassword);
if (matchUsername && matchPassword)
{
txfSuccess.Visibility = Visibility.Visible;
StaticData.staffname = DefaultStaff;
StaticData.username = DefaultUsername;
StaticData.role = DefaultRole;
loginSuccess();
}
else
{
txfError.Visibility = Visibility.Visible;
txfPassword.Clear();
}
}
else
{
DatabaseConnection connection = new DatabaseConnection();
connection.setQuery("SELECT strFullName, strUsername, strRole " +
"FROM viewAccounts " +
"WHERE strUsername = @username AND strPassword = @password");
connection.addParameter("@username", System.Data.SqlDbType.VarChar, txfUsername.Text);
connection.addParameter("@password", System.Data.SqlDbType.VarChar, txfPassword.Password);
SqlDataReader sqlData = connection.executeResult();
if (sqlData.HasRows)
{
txfSuccess.Visibility = Visibility.Visible;
while (sqlData.Read())
{
StaticData.staffname = sqlData[0].ToString();
StaticData.username = sqlData[1].ToString();
StaticData.role = sqlData[2].ToString();
loginSuccess();
}
}
else
{
txfError.Visibility = Visibility.Visible;
txfPassword.Clear();
}
}
}
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 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 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 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 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 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();
}
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 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 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();
}
}
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();
}