public static IEnumerable <RepeatStaffPositionsVM> GetRepeatStaffPositions(string strPositionName)
{
List <RepeatStaffPositionsVM> retval = new List <RepeatStaffPositionsVM>();
// create and open a connection
NpgsqlConnection conn = DatabaseConnection.GetConnection();
conn.Open();
// Define a query
string query = "SELECT m.\"strName\", count(m.*)" +
" FROM members m, staff s" +
" WHERE s.\"strPosition\" = '" + strPositionName + "'" +
" AND s.\"intMemberID\" = m.\"intMemberID\"" +
" GROUP BY m.\"strName\"" +
" HAVING count(m.*) >= 2";
NpgsqlCommand cmd = new NpgsqlCommand(query, conn);
// Execute a query
NpgsqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
RepeatStaffPositionsVM tmpRepeatStaff = new RepeatStaffPositionsVM
{
StrName = dr["strName"].ToString(),
IntCount = Convert.ToInt32(dr["count"])
};
retval.Add(tmpRepeatStaff);
}
conn.Close();
return(retval);
}
public ActionResult GetRepeatStaff(string strPosition)
{
RepeatStaffPositionsVM model = new RepeatStaffPositionsVM()
{
LstPossiblePositions = AnalyticsDAL.GetPossiblePositions(),
LstRepeatStaffPositions = AnalyticsDAL.GetRepeatStaffPositions(strPosition),
StrPosition = strPosition
};
return(PartialView("AnalyticsPartials/_RepeatStaffs", model));
}
