//Implement Business Rule 7
//A constituent can only be assigned 3 active programs.
//Only load constituents with less than 3 assignments on active programs
private void LoadApplicableDonorName()
{
string sql = $@"
SELECT AccountId, KeyName
FROM Account
WHERE AccountId NOT IN
(
SELECT AccountID
FROM
(
SELECT AccountID,
Count(AccountId) AS 'NumAssn'
FROM
(
SELECT AccountId
FROM VolunteerAssignment
WHERE ProgramID IN
(
SELECT ProgramId
FROM VolunteerProgram
WHERE IsActive = 'true') ) AS p
GROUP BY AccountID) AS q
WHERE q.NumAssn >= 3
)
";
sql = DataAccess.SQLCleaner(sql);
DataTable dt = DataAccess.GetData(sql);
UIUtilities.FillListControl(cboDonorName, "KeyName", "AccountId", dt, true, "--Select a constituent--");
}
private void LoadDonorName()
{
DataTable dt = DataAccess.GetData("SELECT AccountId, KeyName FROM Account ORDER BY KeyName");
UIUtilities.FillListControl(cboDonorName, "KeyName", "AccountId", dt, true, "--Select a constituent--");
}
private void LoadDonorName(int id, string type)
{
DataTable dt = DataAccess.GetData($"SELECT AccountId, KeyName FROM Account WHERE ConstituencyTypeId = {id} ORDER BY KeyName ");
UIUtilities.FillListControl(cboDonorName, "KeyName", "AccountId", dt, true, $"-- All {type} donors --");
}
private void LoadConstituencyType()
{
DataTable dt = DataAccess.GetData("SELECT ConstituencyTypeId, ConstituencyTypeName FROM ConstituencyType ORDER BY ConstituencyTypeName");
UIUtilities.FillListControl(cboConstituencyType, "ConstituencyTypeName", "ConstituencyTypeId", dt, true, "-- All constituency type --");
}
//Implement Business Rule 7
// Constituents must be added to active programs (3 max). Only load active programs in the combobox during add event.
private void LoadActiveVolunteerProgram()
{
DataTable dt = DataAccess.GetData("SELECT ProgramId, ProgramName FROM VolunteerProgram WHERE IsActive = 'true' ORDER BY ProgramName");
UIUtilities.FillListControl(cboVolunterProgram, "ProgramName", "ProgramId", dt, true, "--Select a program--");
}
private void LoadDonorName(int id, string type)
{
DataTable dt = DataAccess.GetData($"SELECT DISTINCT Account.AccountId, KeyName FROM Account INNER JOIN VolunteerAssignment On Account.AccountId = VolunteerAssignment.AccountId AND ProgramId = {id} ORDER BY KeyName ");
UIUtilities.FillListControl(cboVolName, "KeyName", "AccountId", dt, true, $"-- All {type} volunteers --");
}
private void LoadVolunteerName()
{
DataTable dt = DataAccess.GetData("SELECT DISTINCT Account.AccountId, KeyName FROM Account INNER JOIN VolunteerAssignment On Account.AccountId = VolunteerAssignment.AccountId ORDER BY KeyName");
UIUtilities.FillListControl(cboVolName, "KeyName", "AccountId", dt, true, "-- All volunteers --"); //good
}
private void LoadProgram()
{
DataTable dt = DataAccess.GetData("SELECT ProgramId, ProgramName FROM VolunteerProgram ORDER BY ProgramName");
UIUtilities.FillListControl(cboProgram, "ProgramName", "ProgramId", dt, true, "-- All programs --"); //good
}