/// <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; }