public static List <Subgroup> forGroupID(SqlConnection db, int groupID, int peopleID, int scheduleID, DateTime meetingDate)
{
List <Subgroup> subGroups = new List <Subgroup>();
DataTable table = new DataTable();
const string qSubGroups = @"SELECT
MAX(Id) AS id,
MAX(Name) AS name,
MAX(CAST(CheckInOpen AS TINYINT)) AS 'open',
MAX(CheckInCapacity) AS capacity,
MAX(lastAttend.MeetingDate) AS previous,
MAX(attendCount.Count) AS count
FROM dbo.MemberTags AS tags
LEFT JOIN (SELECT *
FROM dbo.Attend
WHERE OrganizationId = @groupID
AND PeopleId = @peopleID
AND AttendanceFlag = 1) AS lastAttend ON lastAttend.SubGroupID = tags.Id
LEFT JOIN (SELECT COUNT(*) AS Count, MAX(SubGroupID) AS SubGroupID
FROM dbo.Attend
WHERE OrganizationId = @groupID
AND AttendanceFlag = 1
AND MeetingDate = @meetingDate
GROUP BY SubGroupID) AS attendCount ON attendCount.SubGroupID = tags.Id
WHERE OrgId = @groupID
AND ScheduleId = @scheduleID
AND CheckIn = 1
GROUP BY Id
ORDER BY name" ;
using (SqlCommand cmd = new SqlCommand(qSubGroups, db)) {
SqlParameter groupParameter = new SqlParameter("groupID", groupID);
SqlParameter peopleParameter = new SqlParameter("peopleID", peopleID);
SqlParameter scheduleParameter = new SqlParameter("scheduleID", scheduleID);
SqlParameter dateParameter = new SqlParameter("meetingDate", meetingDate);
cmd.Parameters.Add(groupParameter);
cmd.Parameters.Add(peopleParameter);
cmd.Parameters.Add(scheduleParameter);
cmd.Parameters.Add(dateParameter);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(table);
}
foreach (DataRow row in table.Rows)
{
Subgroup subGroup = new Subgroup();
subGroup.populate(row);
subGroups.Add(subGroup);
}
return(subGroups);
}
public void populateSubgroups(SqlConnection db, AttendanceCacheSet cacheSet)
{
foreach (AttendanceGroup group in groups)
{
Organization org = cacheSet.getOrganization(group.groupID);
if (org != null)
{
var subgroups = Subgroup.forGroupID(db, group.groupID, peopleID);
if (subgroups.Count > 0)
{
group.subgroupName = subgroups.First().name;
}
}
}
}
public static List <Subgroup> forGroupID(SqlConnection db, int groupID, int peopleID)
{
List <Subgroup> subGroups = new List <Subgroup>();
DataTable table = new DataTable();
const string qSubGroups = @"SELECT
MAX(Id) AS id,
MAX(Name) AS name
FROM MemberTags AS tags
join OrgMemMemTags as mt on tags.Id = mt.MemberTagId
WHERE mt.OrgId = @groupID
AND mt.PeopleId = @peopleID
GROUP BY Id
ORDER BY name" ;
using (SqlCommand cmd = new SqlCommand(qSubGroups, db)) {
SqlParameter groupParameter = new SqlParameter("groupID", groupID);
SqlParameter peopleParameter = new SqlParameter("peopleID", peopleID);
cmd.Parameters.Add(groupParameter);
cmd.Parameters.Add(peopleParameter);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(table);
}
foreach (DataRow row in table.Rows)
{
Subgroup subGroup = new Subgroup();
subGroup.populate(row);
subGroups.Add(subGroup);
}
return(subGroups);
}
