public List<PrefixListItemHP> GetDepartmentPrefixesFromHP()
{
List<PrefixListItemHP> results = new List<PrefixListItemHP>();
using (HPSADataContext ctx = new HPSADataContext())
{
var items = ctx.DEPT_DIV_Ms.OrderBy(d => d.DEPT_DIV).ToList<DEPT_DIV_M>();
foreach (var item in items)
{
results.Add(new PrefixListItemHP
{
DisplayTitle = item.DEPT_DIV + " (" + item.TITLE + ")",
PrefixText = item.DEPT_DIV
});
}
}
return results;
}
/// <summary>
/// GetCourseFootnote - This function returns the footnote from the classschedule database, based
/// on the parameter that is passed in.
/// </summary>
/// <param name="footId">A FOOTNOTE_ID value from the CLASS_D Table for a specific course offering.</param>
/// <returns>A string representing the footnote.</returns>
public string GetCourseFootnote(string footId)
{
string result = "";
using (HPSADataContext ctx = new HPSADataContext())
{
var item = ctx.FOOTNOTE_Ms.Where(f => f.FOOTNOTE_CD == footId).SingleOrDefault<FOOTNOTE_M>();
string footnote1 = "";
string footnote2 = "";
try
{
if (item != null)
{
footnote1 = item.FOOTNOTE_TEXT_1.ToString();
footnote2 = item.FOOTNOTE_TEXT_2.ToString();
footnote1 = ProcessInstructorEmail(footnote1.TrimEnd());
footnote2 = ProcessInstructorEmail(footnote2.TrimEnd());
result = footnote1 + footnote2;
//TODO: Decide whether we want to do this or not...
//result = ProcessWebsiteAddressInFootnote(result);
//Replace any multiple spaces with one space...
result = Regex.Replace(result, @"\s+", " ");
}
}
catch
{
result = "" + result;
}
}
return result;
}
/// <summary>
/// GetCourseTitle - This function retrives the latest Full Course Title from the ClassSchedule
/// database, if the course has one available. If not, it simply returns the partial title passed in
/// with the originalTitle parameter. If the course ID contains "255" or "155", this is a "Special Topics" or "DUmmy Course"
/// and as such, we just return the original title.
/// </summary>
/// <param name="Id">A Course ID, such as "ENGL&101".</param>
/// <param name="originalTitle">A short course title from the CLASS_D table.</param>
/// <param name="courseId">A string representing the course ID</param>
/// <returns>A string representing a Full course title if available, otherwise the originalTitle.</returns>
public string GetCourseTitle(string Id, string originalTitle, string courseId)
{
string result = originalTitle;
using (HPSADataContext ctx = new HPSADataContext())
{
if (courseId.Contains(SPECIAL_TOPICS_COURSE_NUMBER_LEV2) || courseId.Contains(SPECIAL_TOPICS_COURSE_NUMBER_LEV1))
{
result = originalTitle;
}
else
{
var item = ctx.COURSE_TITLE2_Ds.Where(c => c.COURSE_ID == Id).OrderByDescending(c => c.EFF_YRQ_BEG).Take(1).SingleOrDefault<COURSE_TITLE2_D>();
try
{
result = item.COURSE_TITLE2.Replace(CULTURAL_DIVERSITY_INDICATOR, "").ToString();
}
catch
{
result = originalTitle;
}
}
}
return result;
}
/// <summary>
/// GetCourseDays - Processes the Days value for use in the user interface. It mostly handles
/// values such as "ARRANGED" etc.
/// </summary>
/// <param name="dayCd">A DAY_CD representing the days of the week in which a course meets.</param>
/// <returns>A string representing the days of the week, such as "MWF" etc.</returns>
public string GetCourseDays(string dayCd)
{
string result = "";
if (dayCd.TrimEnd() == "")
{
return result;
}
using (HPSADataContext ctx = new HPSADataContext())
{
var item = ctx.DAY_Ds.Where(d => d.DAY_CD == dayCd).SingleOrDefault<DAY_D>();
string temp = item.ABBR_TITLE;
if (temp.TrimEnd() == HP_ARRANGED_VALUE)
{
result = REPLACE_ARRANGED_VALUE;
}
else if (temp.TrimEnd() == HP_DAILY_VALUE)
{
result = REPLACE_DAILY_VALUE;
}
else
{
result = temp;
}
//var item = ctx.
}
return result;
}
/// <summary>
/// GetCourseDescription2 - Returns the second Course Description and/or Requisites text from the ClassSchedule
/// database.
/// </summary>
/// <param name="Id">A COURSE_ID value, such as "ENGL&101".</param>
/// <returns>A string representing a course description and/or requisite information.</returns>
public string GetCourseDescription2(string Id, string yrq)
{
string result = "";
using (HPSADataContext ctx = new HPSADataContext())
{
try
{
//get the most recent quarter that is less than or equal to the current quarter...
string effYrqItem = GetLatestApplicableYearQuarterDescription2(yrq, Id);
//var effYrqItem = ctx.COURSE_DESC2_Ds.Where(c => c.COURSE_ID == Id).OrderByDescending(c => c.EFF_YRQ_BEG).Take(1).SingleOrDefault<COURSE_DESC2_D>();
string effYrq = effYrqItem;
var items = ctx.COURSE_DESC2_Ds.Where(c => c.COURSE_ID == Id && c.EFF_YRQ_BEG == effYrq).OrderBy(c => c.LN_NUM).ToList<COURSE_DESC2_D>();
foreach (var item in items)
{
result = result + " " + item.DESC_TEXT.ToString();
}
//Replace any multiple spaces with one space...
result = Regex.Replace(result, @"\s+", " ");
}
catch
{
result = "";
}
}
return result;
}
//instructor lists
private List<InstructorModel> GetNormalDepartmentInstructors(int deptId, string yrq)
{
List<InstructorModel> results = new List<InstructorModel>();
results.Add(new InstructorModel
{
DisplayName = ANY_INSTRUCTOR_VALUE,
InstructorName = ANY_INSTRUCTOR_VALUE
});
using (HPSADataContext ctx = new HPSADataContext())
{
var predicate = PredicateBuilder.True<vw_CLASS_D>();
//handle specifics for this department...
string[] departmentPrefixes = GetDepartmentPrefixes(deptId);
predicate = predicate.And(s => departmentPrefixes.Contains(s.CoursePrefix)); // && !s.CourseNumber.Contains("I"));
//specify yearquarter
predicate = predicate.And(s => s.YRQ == yrq);
//TODO: THIS NEEDS TO BE MODIFIED TO USE THE NEW SCHEMA...
//don't show any where misc_srt = "XX", and any high school completion courses also not show here.
predicate = predicate.And(s => !s.MISC_SRT.Contains("XX") && s.MISC_SRT != "HI");
//handle excluded admin units
string[] excludedAu = GetExcludedAdminUnits();
predicate = predicate.And(s => !excludedAu.Contains(s.ADMIN_UNIT));
string[] instrs = ctx.vw_CLASS_Ds.Where(predicate).OrderBy(d => d.INSTR_NAME).Select(d => d.INSTR_NAME).Distinct().ToArray<string>();
foreach (string s in instrs)
{
results.Add(new InstructorModel
{
InstructorName = s,
DisplayName = s
});
}
}
return results;
}
/// <summary>
/// FeeIsPerCredit - Checks to determine if the specified course fee is assessed as per credit
/// </summary>
/// <param name="feeCode">The fee code for the course offering.</param>
/// <param name="yrq">The year quarter for the course offering.</param>
/// <returns>True if the fee is a per credit fee.</returns>
public bool FeeIsPerCredit(string feeCode, string yrq)
{
//
bool result = false;
if (feeCode.TrimEnd() == "") return false;
//Create the fee key with the college code, fee code, and the academic year;
//using the yrq parameter, call GetAcademicYearForQuarter function
//so it would be, for example, "1213" + " " in order to create the
//fee key. (The HP requires the space at the end as this is how the database is...)
string currAyr = GetAcademicYearForQuarter(yrq);
string key = HP_COLLEGE_CODE_EDMONDS + feeCode.TrimEnd() + currAyr;
using (HPSADataContext ctx = new HPSADataContext())
{
var item = ctx.FEE_CD_TBL_Ms.Where(s => s.FEE_CD_TBL_KEY.Trim() == key).SingleOrDefault<FEE_CD_TBL_M>();
try
{
result = (item.FEE_UNIT.TrimEnd() == HP_FEE_PER_CREDIT_INDICATOR);
}
catch
{
result = false;
}
}
return result;
}
private string GetAcademicYearForQuarter(string yrq)
{
string result = "";
using (HPSADataContext ctx = new HPSADataContext())
{
var item = ctx.YRS_FYR_Ds.Where(y => y.YRQ == yrq).SingleOrDefault<YRS_FYR_D>();
try
{
result = item.FYR.ToString().Trim();
}
catch (Exception ex)
{
throw new Exception("Unable to retrive Year for " + yrq + ": " + ex.Message);
}
}
return result;
}
//handle special case departments
private DepartmentModel GetNormalDepartment(SearchModel criteria)
{
int deptId = GetDepartmentIDFromUrlName(criteria.DepartmentName);
//specific courses...
DepartmentModel result = GetDepartmentModelBasics(deptId);
result.HasChildDepartments = false;
using (HPSADataContext ctx = new HPSADataContext())
{
var predicate = PredicateBuilder.True<vw_CLASS_D>();
bool fPrefixes = false;
//specify yearquarter
predicate = predicate.And(s => s.YRQ == criteria.YearQuarterID);
//handle specifics for this department...
string[] departmentPrefixes = GetDepartmentPrefixes(deptId);
if (departmentPrefixes.Count() > 0)
{
predicate = predicate.And(s => departmentPrefixes.Contains(s.CoursePrefix)); //&& !s.CourseNumber.Contains("I"));
fPrefixes = true;
}
else
{
fPrefixes = false;
}
//Get the list of Misc Sort field values that will be used to filter results -
//so any class offering with these values in the misc sort field will be excluded.
string[] deptExcludedMiscSort = GetExcludedMiscSort(deptId);
//exclude the misc sort field value(s) if there are any specified.
if (deptExcludedMiscSort.Count() > 0)
{
predicate = predicate.And(s => !deptExcludedMiscSort.Contains(s.MISC_SRT.Trim()));
}
//if we are not including cancelled classes (such as for the print schedule) we
//want to exclude them from the search results...
if (criteria.IncludeCanceledClasses == false)
{
predicate = predicate.And(s => s.SCHD_CHNG != SCHEDULE_CHANGE_CANCELLED);
}
//Handles all inclusion and exclusion of MISC_SRT field values...
//Get the list of Misc Sort field values that will be used to filter results -
//so any class offering with these values in the misc sort field will be included.
string[] deptIncludedMiscSort = GetIncludedMiscSort(deptId);
//
//Robert.Pan 4/22/2013 - HACK!!!
//<HACK BEGIN>.
//Chad's original predicate construction was wrong. Need to OR this clause to the original clause - not AND it.
//I.e. Chad's code generated a predicate statement of (A && B && C && D) which is wrong. Correct predicate statement is: (A && B && C) || (A && D).
//
//Also reordered the predicate construction clauses to improve performance.
//
if (deptIncludedMiscSort.Count() > 0)
{
var predicate2 = PredicateBuilder.True<vw_CLASS_D>();
//specify yearquarter
predicate2 = predicate2.And(s => s.YRQ == criteria.YearQuarterID);
//include the misc sort field value(s) if there are any specified.
predicate2 = predicate2.And(s => deptIncludedMiscSort.Contains(s.MISC_SRT.Trim()));
//If there are no prefixes, then the predicate should only be predicate2. Else, it is
//predicate OR predicate2. See Vic and/or Beth for an explanation of this logic. Bob does
//not understand the logic. He only coded the fix as per instructions.
if (fPrefixes == false)
{
predicate = predicate2;
}
else
{
predicate = predicate.Or(predicate2);
}
}
//</HACK END>
//handle excluded admin units
string[] excludedAu = GetExcludedAdminUnits();
predicate = predicate.And(s => !excludedAu.Contains(s.ADMIN_UNIT));
//check to see what instructor criteria has been passed,
//and include it if a specific instructor has been specified. If not, don't
//add it as criteria.
if (criteria.InstructorName != "Any Instructor")
{
predicate = predicate.And(d => d.INSTR_NAME == criteria.InstructorName);
}
//If only online courses are specified, include it as a filter.
if (criteria.OnlineOnly)
{
predicate = predicate.And(s => s.BRANCH == "OL");
}
//If only hybrid courses are specified, include it as a filter.
if (criteria.HybridOnly)
{
predicate = predicate.And(s => s.BRANCH == "HY");
}
//If only Cultural Diversity courses are specified, include it as a filter.
if (criteria.CulturalDiversityOnly)
{
predicate = predicate.And(s => s.COURSE_TITLE.Contains(":CD"));
}
//If only evening courses are specified, include it as a filter.
if(criteria.EveningOnly)
{
DateTime startTime = DateTime.Parse("1900-01-01 17:00:00.000");
predicate = predicate.And(s => s.StartTime > startTime);
}
//If only late start courses are specified, include it as a filter.
if (criteria.LateStart)
{
predicate = predicate.And(d => d.STRT_DATE.Value > 0);
}
//If only Continuous Enrollment courses are specified, include it as a filter.
if (criteria.ContinuousEnrollment)
{
predicate = predicate.And(d => d.CONT_SEQ == "C");
}
//Handle filters for Start After and End Before criteria if applicable.
if (criteria.EndBefore != "Anytime")
{
DateTime endBefore = DateTime.Parse("1900-01-01 " + criteria.EndBefore);
predicate = predicate.And(d => d.EndTime < endBefore);
}
if (criteria.StartAfter != "Anytime")
{
DateTime startAfter = DateTime.Parse("1900-01-01 " + criteria.StartAfter);
predicate = predicate.And(d => d.StartTime > startAfter);
}
//Handle the days of the week filters if applicable.
if (criteria.Friday == false)
{
//
string[] fridayCodes = GetDayCodesForFriday();
predicate = predicate.And(d => !fridayCodes.Contains(d.DAY_CD));
}
if (criteria.Hybrid == false)
{
predicate = predicate.And(d => d.BRANCH != "HY");
}
if (criteria.Monday == false)
{
string[] mondayCodes = GetDayCodesForMonday();
predicate = predicate.And(d => !mondayCodes.Contains(d.DAY_CD));
}
if (criteria.Online == false)
{
predicate = predicate.And(d => d.BRANCH != "OL");
}
if (criteria.Saturday == false)
{
string[] saturdayCodes = GetDayCodesForSaturday();
predicate = predicate.And(d => !saturdayCodes.Contains(d.DAY_CD));
}
if (criteria.Sunday == false)
{
string[] sundayCodes = GetDayCodesForSunday();
predicate = predicate.And(d => !sundayCodes.Contains(d.DAY_CD));
}
if (criteria.Thursday == false)
{
string[] thursdayCodes = GetDayCodesForThursday();
predicate = predicate.And(d => !thursdayCodes.Contains(d.DAY_CD));
}
if (criteria.Tuesday == false)
{
string[] tuesdayCodes = GetDayCodesForTuesday();
predicate = predicate.And(d => !tuesdayCodes.Contains(d.DAY_CD));
}
if (criteria.Wednesday == false)
{
string[] wednesdayCodes = GetDayCodesForWednesday();
predicate = predicate.And(d => !wednesdayCodes.Contains(d.DAY_CD));
}
//Just getting the string for use in testing. This is commented out otherwise. For debugging
//purposes.
string sql = ctx.vw_CLASS_Ds.Where(predicate).OrderBy(d => d.CourseNumber).ToString();
//Get the collection of classes based on all of the above criteria...
var classes = ctx.vw_CLASS_Ds.Where(predicate).OrderBy(d=>d.CourseNumber).ToList<vw_CLASS_D>();
//Convert them into usable Course Objects for use by the user interface.
result.Courses = GetCourseObjectsFromClasses(classes);
}
return result;
}
/// <summary>
/// GetSectionInformation - returns information, such as Course Title, Online Registration,
/// and other values for display in the User Interface when a user clicks the Itm Number.
/// </summary>
/// <param name="itemNumber">The Item Number for a specific course offering.</param>
/// <param name="yrq">The Year Quarter Code for which the item number is to be located.</param>
/// <returns>A SectionStatusUIDataModel object.</returns>
public SectionStatusUIDataModel GetSectionInformation(string itemNumber, string yrq)
{
SectionStatusUIDataModel model = new SectionStatusUIDataModel();
model.OnlineRegistration = true;
using (HPSADataContext ctx = new HPSADataContext())
{
var item = ctx.vw_CLASS_Ds.Where(c => c.CLASS_ID == (itemNumber + yrq)).SingleOrDefault<vw_CLASS_D>();
try
{
model.CourseID = item.COURSE_ID;
model.CourseTitle = GetCourseTitle(item.COURSE_ID, item.COURSE_TITLE, item.COURSE_ID);
model.ItemNumber = itemNumber;
if (item.ALLOW_TTONE_REG.TrimEnd() == HP_NO_VALUE)
{
model.OnlineRegistration = false;
}
}
catch
{
model = new SectionStatusUIDataModel();
}
}
return model;
}
/// <summary>
/// GetSectionAlsoMeets - returns a section model for a course offering, to provide any "Also Meets"
/// value for a course found in the INSTR_ROOM_D of the ClassSchedule database. This is common for
/// courses that are team taught and/or have alternate meeting information.
/// </summary>
/// <param name="classID">A string value representing a Class ID (item number + Year Quarter).</param>
/// <returns>A SectionModel object list.</returns>
public List<SectionModel> GetSectionAlsoMeets(string classID)
{
List<SectionModel> models = new List<SectionModel>();
using (HPSADataContext ctx = new HPSADataContext())
{
var items = ctx.INSTR_ROOM_Ds.Where(s => s.CLASS_ID == classID).ToList<INSTR_ROOM_D>();
foreach (var item in items)
{
SectionModel sect = new SectionModel();
sect.Days = GetCourseDays(item.DAY_CD);
if (item.INSTR_NAME.TrimEnd() == "" || item.INSTR_NAME == null)
{
sect.Instructor = "";
}
else
{
sect.Instructor = item.INSTR_NAME.TrimEnd(); //GetInstructorName(item.INSTR_ID);// (item.INSTR_NAME.TrimEnd());
}
sect.Location = GetSectionLocationDisplay(item.ROOM_LOC);
if (item.STRT_TIME != null && item.END_TIME != null)
{
sect.Times = GetSectionTimeDisplay(item.STRT_TIME, item.END_TIME);
}
else
{
sect.Times = "";
}
models.Add(sect);
}
}
return models;
}
public string GetLatestApplicableYearQuarterDescription2(string yrq, string courseId)
{
string result = string.Empty;
//we need to find all of the year quarters available in the EFF_YRQ_BEG field...
using (HPSADataContext ctx = new HPSADataContext())
{
string[] quarters = ctx.COURSE_DESC2_Ds.Where(c => c.COURSE_ID == courseId).OrderByDescending(c => c.EFF_YRQ_BEG).Select(c => c.EFF_YRQ_BEG).Distinct().ToArray();
//now that we have the list...
if (quarters.Count() > 0)
{
var descQ = from d in quarters
orderby d descending
select d;
//we try to find the correct one...
foreach (string s in descQ)
{
if (string.Compare(s, yrq) <= 0)
{
result = s;
break;
}
}
}
else
{
result = string.Empty;
}
}
return result;
}
/// <summary>
/// GetInstructorListDefault - Returns a list of all instructors that are teaching courses
/// during the specified quarter.
/// </summary>
/// <param name="yrq">A YRQ Code representing the quarter.</param>
/// <returns>An InstructorModel list.</returns>
public List<InstructorModel> GetInstructorListDefault(string yrq)
{
List<InstructorModel> results = new List<InstructorModel>();
results.Add(new InstructorModel
{
DisplayName = ANY_INSTRUCTOR_VALUE,
InstructorName = ANY_INSTRUCTOR_VALUE
});
using (HPSADataContext ctx = new HPSADataContext())
{
string[] items = ctx.vw_CLASS_Ds.Where(d => d.YRQ == yrq).OrderBy(d => d.INSTR_NAME).Select(d => d.INSTR_NAME).Distinct().ToArray<string>();
foreach (string s in items)
{
if (s.TrimEnd() != "")
{
results.Add(new InstructorModel
{
DisplayName = s,
InstructorName = s
});
}
}
}
return results;
}
//********************************Administrative functions*********************************//
// //
//*****************************************************************************************//
public List<QuarterListItemHP> GetHPQuarterList()
{
List<QuarterListItemHP> results = new List<QuarterListItemHP>();
using (HPSADataContext ctx = new HPSADataContext())
{
var items = ctx.YRQ_Ms.Where(s => s.FIRST_DAY_YRQ > 120924).OrderByDescending(s => s.YRQ).ToList<YRQ_M>();
foreach (var item in items)
{
if (YearIsEqualToOrGreaterThan2012AcademicYear(item.FIRST_DAY_YRQ.Value))
{
results.Add(new QuarterListItemHP { QuarterID = item.YRQ, Title = item.ABBR_TITLE });
}
}
results.Add(new QuarterListItemHP { QuarterID = "Z999", Title = "Open-ended" });
}
return results;
}
