/// <summary>
/// Lists all schools for corrdinators or admins filtered by user type and user id.
/// </summary>
/// <param name="userId">The user id.</param>
/// <param name="userType">Type of the user.</param>
/// <returns></returns>
public static List<LPHSSchool> ListByCoordinator(int userId, LPHSUserTypes userType)
{
using (new LogScope("LPHSSchool.ListByCoordinator"))
{
try
{
StringBuilder oracleQuery = new StringBuilder();
oracleQuery.Append(" SELECT LPHSDB.SCHOOL.SCHOOL_ID, SCHOOL_NAME, SCHOOL_ADDRESS_1, SCHOOL_ADDRESS_2, SCHOOL_ADDRESS_3, SCHOOL_CITY, ");
oracleQuery.Append(" SCHOOL_STATE, SCHOOL_ZIP_CODE, SCHOOL_COORDINATOR_USER_ID, ACTIVE, REGION_ID, SCHOOL_SESSION ");
oracleQuery.Append(" FROM LPHSDB.SCHOOL, LPHSDB.SCHOOL_SESSION ");
oracleQuery.Append(" WHERE LPHSDB.SCHOOL.SCHOOL_ID = LPHSDB.SCHOOL_SESSION.SCHOOL_ID ");
if (userType == LPHSUserTypes.Coordinator)
{
oracleQuery.Append(" AND SCHOOL_COORDINATOR_USER_ID = :current_user_id");
}
oracleQuery.Append(" ORDER BY LPHSDB.SCHOOL.SCHOOL_NAME ");
var schools = new List<LPHSSchool>();
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(oracleQuery.ToString(), conn))
{
command.BindByName = true;
command.Parameters.Add("current_user_id", OracleDbType.Int32).Value = userId;
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (reader.Read())
{
schools.Add(ReadSchool(reader));
}
}
}
});
return schools;
}
catch (Exception ex)
{
Log.Write(ex, Log.Mask.Failure);
throw;
}
}
}
/// <summary>
/// Lists all users for the specified user type.
/// </summary>
/// <param name="userType">Type of the user.</param>
/// <returns></returns>
public static List<LPHSUser> ListUserBySchoolUserType(int schoolId, LPHSUserTypes userType)
{
StringBuilder oracleQuery = new StringBuilder();
oracleQuery.Append(" SELECT U.USER_ID, U.USER_TYPE_ID, U.USER_NAME, U.USER_DISPLAY_NAME, U.USER_EMAIL_ADDRESS, ");
oracleQuery.Append(" U.USER_STATUS, U.USER_LAST_LOGGED_ON, U.SCHOOL_ID, U.USER_SID, U.SUMTOTAL_USER_NAME, U.SUMTOTAL_USER_PWD ");
oracleQuery.Append(" FROM LPHSDB.LPHS_USER U ");
oracleQuery.Append(" WHERE 1=1 ");
oracleQuery.Append(" AND (USER_TYPE_ID = :usertype)");
if (schoolId > 0) { oracleQuery.Append(" AND (SCHOOL_ID = :schoolid)"); }
oracleQuery.Append("ORDER BY U.USER_DISPLAY_NAME ");
var users = new List<LPHSUser>();
Database.Oracle.Execute("LPHS", (conn) =>
{
using (OracleCommand command = new OracleCommand(oracleQuery.ToString(), conn))
{
command.BindByName = true;
command.Parameters.Add("usertype", (int)userType);
if (schoolId != null && schoolId > 0) { command.Parameters.Add("schoolid", schoolId); }
using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (reader.Read())
{
users.Add(ReadUser(reader));
}
}
}
});
return users;
}
