protected void btnInitStudentProgress_Click(object sender, EventArgs e)
{
OdbcConnectionClass3 jicsSpConn = helper.CONNECTION_SP;
Exception exStudentProgress = null;
string sqlStudentProgress = String.Format(@"EXECUTE CUS_spCheckIn_InitializeStudentProgress @intYear = {0}, @strSession = '{1}'", helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
try
{
jicsSpConn.ConnectToERP(sqlStudentProgress, ref exStudentProgress);
if (exStudentProgress != null)
{
throw exStudentProgress;
}
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException(String.Format("<p>Could not initialize student progress</p><p>{0}</p>", sqlStudentProgress), ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error,
ciHelper.FormatException(String.Format("<p>Could not initialize student progress</p><p>{0}</p>", sqlStudentProgress), ex, null, null, null, LogEventType.Error, LogScreen.SiteAdminTools, sqlStudentProgress));
}
finally
{
if (jicsSpConn.IsNotClosed())
{
jicsSpConn.Close();
}
}
}
public DataTable GetAthletics()
{
OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_SP;
DataTable dtAthletics = null;
Exception exAthletics = null;
string sqlAthletics = @"EXECUTE PROCEDURE ci_admin_facetedsearch_athletics_list()";
try
{
dtAthletics = cxConn.ConnectToERP(sqlAthletics, ref exAthletics);
if (exAthletics != null)
{
throw exAthletics;
}
}
catch (Exception ex)
{
FormatException("Could not retrieve athletic organizations", ex, null, true);
}
finally
{
if (cxConn.IsNotClosed())
{
cxConn.Close();
}
}
return(dtAthletics);
}
private void LoadCheckInSummary()
{
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
DataTable dtSummary = null;
Exception exSummary = null;
string sqlSummary = "EXECUTE CUS_spCheckIn_AdminChartSummary";
try
{
dtSummary = spConn.ConnectToERP(sqlSummary, ref exSummary);
if (exSummary != null)
{
throw exSummary;
}
chartCheckInSummary.DataSource = dtSummary;
chartCheckInSummary.DataBind();
}
catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatExceptionMessage(ex));
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
}
protected void btnProcessRollover_Click(object sender, EventArgs e)
{
OdbcConnectionClass3 jicsSpConn = helper.CONNECTION_SP;
Exception exProcessRollover = null;
string sqlProcessRollover = String.Format(@"EXECUTE CUS_spCheckIn_ProcessRollover @intYear = {0}, @strSession = '{1}'", helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
try
{
jicsSpConn.ConnectToERP(sqlProcessRollover, ref exProcessRollover);
if (exProcessRollover != null)
{
throw exProcessRollover;
}
}
catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error,
ciHelper.FormatException(String.Format("<p>Could not process rollover data</p><p>{0}</p>", sqlProcessRollover), ex, null, null, null, LogEventType.Error, LogScreen.SiteAdminTools, sqlProcessRollover));
}
finally
{
if (jicsSpConn.IsNotClosed())
{
jicsSpConn.Close();
}
}
}
protected void btnSearch_Click(object sender, EventArgs e)
{
if (!String.IsNullOrWhiteSpace(txtSearch.Text))
{
this.ParentPortlet.PortletViewState[ciHelper.VIEWSTATE_SEARCH_CRITERIA] = this.txtSearch.Text;
string sqlSearch = "EXECUTE CUS_spCheckIn_AdminSearchUsers @strSearchTerm = ?";
List <OdbcParameter> paramSearch = new List <OdbcParameter>()
{
new OdbcParameter("search", txtSearch.Text)
};
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
try
{
DataTable dtSearch = null;
Exception exSearch = null;
dtSearch = spConn.ConnectToERP(sqlSearch, ref exSearch, paramSearch);
if (exSearch != null)
{
throw exSearch;
}
if (dtSearch != null)
{
//If the search results only return a single record, go immediately to the detail view
if (dtSearch.Rows.Count == 1)
{
this.ParentPortlet.PortletViewState[ciHelper.VIEWSTATE_SEARCH_STUDENTID] = dtSearch.Rows[0]["CX ID"].ToString(); //this.txtSearch.Text;
this.ParentPortlet.NextScreen("Detail_Student");
}
else
{
this.lblSearchResults.Text = this.lblSearchResults2.Text = String.Format("Found {0} matches", dtSearch.Rows.Count.ToString());
this.lblSearchResults.Visible = this.lblSearchResults2.Visible = true;
gvSearchResults.DataSource = dtSearch;
gvSearchResults.DataBind();
}
}
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("An exception occurred while filtering search results", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error,
ciHelper.FormatException("An exception occurred while filtering search results", ex, PortalUser.Current, null, null, LogEventType.Error, LogScreen.CheckInAdminHelper, sqlSearch)
);
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
}
else
{
this.ParentPortlet.ShowFeedback(FeedbackType.Message, "Please enter either a last name or student ID");
}
}
//[Obsolete]
//public string GetPortalIDByHostID(int cxID)
//{
// string portalID = "";
// DataTable dtPortal = null;
// Exception exPortal = null;
// OdbcConnectionClass3 jicsConn = helper.CONNECTION_JICS;
// try
// {
// string sqlPortal = String.Format(@"SELECT ID FROM FWK_User WHERE HostID = {0}", cxID);
// dtPortal = jicsConn.ConnectToERP(sqlPortal, ref exPortal);
// if (exPortal != null) { throw exPortal; }
// if (dtPortal != null && dtPortal.Rows.Count > 0)
// {
// portalID = dtPortal.Rows[0]["ID"].ToString();
// }
// }
// catch (Exception ex)
// {
// FormatException("Error trying to match student ID with portal ID", ex, null, true);
// }
// return portalID;
//}
public DataTable GetTasks()
{
string sqlTasks = String.Format(@"EXECUTE dbo.CUS_spCheckIn_Tasks");
DataTable dtTasks = null;
Exception exTasks = null;
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
try
{
dtTasks = spConn.ConnectToERP(sqlTasks, ref exTasks);
if (exTasks != null)
{
throw exTasks;
}
if (dtTasks == null || dtTasks.Rows.Count == 0)
{
throw new Exception("No tasks returned by the stored procedure.");
}
}
catch (Exception ex)
{
FormatException("An exception occurred while retrieving task informamtion", ex, null, true);
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
return(dtTasks);
}
private void OfficeTaskInit()
{
this.lblSession1.Text = helper.ACTIVE_SESSION_TEXT;
this.lblYear1.Text = helper.ACTIVE_YEAR.ToString();
this.lblOTSCount.Text = GetOfficeTaskSession().Rows.Count.ToString();
//Add range of years
this.ddlInitRolloverYear.Items.AddRange(Enumerable.Range(helper.ACTIVE_YEAR, 4).Select(li => new ListItem(li.ToString())).OrderByDescending(li => li.Value).ToArray());
OdbcConnectionClass3 jicsSpConn = helper.CONNECTION_SP;
DataTable dtOfficeTaskSession = null;
Exception exOfficeTaskSession = null;
string sqlOfficeTaskSession = String.Format(@"
SELECT
O.OfficeID, O.OfficeName, OT.TaskID, OT.TaskName, OT.ViewColumn, O.OfficeName + ' - ' + OT.TaskName AS OfficeTaskLabel
FROM
CI_OfficeTask OT INNER JOIN CI_Office O ON OT.OfficeID = O.OfficeID
LEFT JOIN CI_OfficeTaskSession OTS ON OT.TaskID = OTS.OfficeTaskID
AND OTS.ActiveYear = {0}
AND OTS.ActiveSession = '{1}'
WHERE
OTS.OfficeTaskSessionID IS NULL
ORDER BY
O.Sequence, O.OfficeName, OT.Sequence, OT.TaskName"
, helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
try
{
dtOfficeTaskSession = jicsSpConn.ConnectToERP(sqlOfficeTaskSession, ref exOfficeTaskSession);
if (exOfficeTaskSession != null)
{
throw exOfficeTaskSession;
}
if (dtOfficeTaskSession != null && dtOfficeTaskSession.Rows.Count > 0)
{
this.lblOTCount.Text = dtOfficeTaskSession.Rows.Count.ToString();
this.cblOfficeTaskSession.DataSource = dtOfficeTaskSession;
this.cblOfficeTaskSession.DataBind();
}
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("<p>Could not load Office Task Session initialization.</p>", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error,
ciHelper.FormatException("<p>Could not load Office Task Session initialization.</p>", ex, null, null, null, LogEventType.Error, LogScreen.SiteAdminTools, sqlOfficeTaskSession));
}
finally
{
if (jicsSpConn.IsNotClosed())
{
jicsSpConn.Close();
}
}
}
public void handleStatusChange(string taskID, string status)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Message, String.Format("UPDATE CI_OfficeTask SET TaskStatus = '{0}' WHERE TaskID = '{1}'", status, taskID));
string feedbackUpdate = String.Format(@"EXECUTE CUS_spCheckIn_UpdateTask @uuidTaskID = '{0}', @strTaskStatus = '{1}', @uuidStatusUserID = '{2}', @intHostID = {3}",
taskID, status, PortalUser.Current.Guid.ToString(),
this.ParentPortlet.PortletViewState[ciHelper.VIEWSTATE_SEARCH_STUDENTID].ToString());
this.ParentPortlet.ShowFeedback(FeedbackType.Message, feedbackUpdate);
int studentID = int.Parse(this.ParentPortlet.PortletViewState[ciHelper.VIEWSTATE_SEARCH_STUDENTID].ToString());
try
{
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
Exception exUpdate = null;
string sqlUpdate = String.Format("EXECUTE CUS_spCheckIn_UpdateTask @uuidTaskID = ?, @strTaskStatus = ?, @uuidStatusUserID = ?, @intHostID = {0}",
studentID);
List <OdbcParameter> paramUpdate = new List <OdbcParameter>()
{
new OdbcParameter("taskID", taskID)
, new OdbcParameter("status", status)
, new OdbcParameter("statusUserID", PortalUser.Current.Guid.ToString())
};
spConn.ConnectToERP(sqlUpdate, ref exUpdate, paramUpdate);
if (exUpdate != null)
{
throw exUpdate;
}
//If the stored procedure executed successfully, reload the table to reflect the updated information
LoadStudentProgress(studentID);
}
catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("An exception occurred while updating the status of this task", ex, null, true));
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
}
public string GetTaskStatus(string task, string hostID)
{
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
DataTable dtTask = null;
Exception exTask = null;
string sql = String.Format(@"
EXECUTE dbo.CUS_spCheckIn_TaskStatusForStudent @intStudentID = {0}, @strViewColumn = ?
", hostID);
string taskStatus = "";
List <OdbcParameter> paramTask = new List <OdbcParameter>()
{
new OdbcParameter("viewcolumn", task)
};
try
{
dtTask = spConn.ConnectToERP(sql, ref exTask, paramTask);
if (exTask != null)
{
throw exTask;
}
if (dtTask != null && dtTask.Rows.Count > 0)
{
taskStatus = dtTask.Rows[0]["TaskStatus"].ToString();
}
}
catch (Exception ex)
{
helper.FormatException("An error occurred while looking for the status of a task", ex, true);
return("Error");
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
return(taskStatus);
}
private DataTable GetOfficeTaskSession()
{
OdbcConnectionClass3 jicsSpConn = helper.CONNECTION_SP;
DataTable dtOfficeTaskSession = null;
Exception exOfficeTaskSession = null;
string sqlOfficeTaskSession = String.Format(@"
SELECT
O.OfficeName, OT.TaskName, O.OfficeName + ' - ' + OT.TaskName AS OfficeTaskLabel, OT.TaskID, OTS.IsRollover, OTS.RolloverYear, OTS.RolloverSession, OTS.OfficeTaskSessionID
FROM
CI_OfficeTaskSession OTS INNER JOIN CI_OfficeTask OT ON OTS.OfficeTaskID = OT.TaskID
INNER JOIN CI_Office O ON OT.OfficeID = O.OfficeID
WHERE
ActiveYear = {0}
AND
ActiveSession = '{1}'
ORDER BY
O.Sequence, O.OfficeName, OT.Sequence, OT.TaskName
", helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
try
{
dtOfficeTaskSession = jicsSpConn.ConnectToERP(sqlOfficeTaskSession, ref exOfficeTaskSession);
if (exOfficeTaskSession != null)
{
throw exOfficeTaskSession;
}
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while retrieving OfficeTaskSession in session initialization interface.", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error,
ciHelper.FormatException("Error while retrieving OfficeTaskSession in session initialization interface.", ex, null, null, null, LogEventType.Error, LogScreen.SiteAdminTools, sqlOfficeTaskSession)
);
}
finally
{
if (jicsSpConn.IsNotClosed())
{
jicsSpConn.Close();
}
}
return(dtOfficeTaskSession);
}
protected void btnAddOTS_Click(object sender, EventArgs e)
{
string rolloverYear = String.IsNullOrWhiteSpace(this.ddlInitRolloverYear.SelectedValue) ? "NULL" : this.ddlInitRolloverYear.SelectedValue;
string rolloverSession = String.IsNullOrWhiteSpace(this.ddlInitRolloverSession.SelectedValue) ? "NULL" : this.ddlInitRolloverSession.SelectedValue;
string selectedOfficeTasks = String.Join(",", this.cblOfficeTaskSession.Items.Cast <ListItem>().Where(li => li.Selected == true).Select(li => "'" + li.Value + "'"));
string sqlInsert = String.Format(@"
INSERT INTO CI_OfficeTaskSession (OfficeTaskID, ActiveYear, ActiveSession, IsRollover, RolloverYear, RolloverSession)
SELECT
OT.TaskID, {0}, '{1}', 0, {2}, {3}
FROM
CI_OfficeTask OT
WHERE
OT.TaskID IN ({4})",
helper.ACTIVE_YEAR, helper.ACTIVE_SESSION, rolloverYear, rolloverSession, selectedOfficeTasks);
OdbcConnectionClass3 jicsConn = helper.CONNECTION_SP;
Exception exRollover = null;
try
{
jicsConn.ConnectToERP(sqlInsert, ref exRollover);
if (exRollover != null)
{
throw exRollover;
}
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while attempting to insert OfficeTaskSession records", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while attempting to insert OfficeTaskSession records", ex, null, null, null,
LogEventType.Error, LogScreen.SiteAdminTools, sqlInsert));
}
finally
{
if (jicsConn.IsNotClosed())
{
jicsConn.Close();
}
}
this.ParentPortlet.ShowFeedback(FeedbackType.Message, sqlInsert);
}
protected void gvConfigSettings_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow gvr = gvConfigSettings.Rows[e.RowIndex];
string configID = gvConfigSettings.DataKeys[e.RowIndex].Value.ToString();
string configKey = (gvr.FindControl("txtKey") as TextBox).Text;
string configValue = (gvr.FindControl("txtValue") as TextBox).Text;
string sqlUpdate = String.Format("UPDATE FWK_ConfigSettings SET [Value] = ? WHERE ID = ?", configValue, configID);
List <OdbcParameter> paramUpdate = new List <OdbcParameter>()
{
new OdbcParameter("value", configValue)
, new OdbcParameter("configID", configID)
};
this.ParentPortlet.ShowFeedback(FeedbackType.Message, sqlUpdate);
OdbcConnectionClass3 jicsConn = helper.CONNECTION_SP;
Exception exUpdate = null;
try
{
jicsConn.ConnectToERP(sqlUpdate, ref exUpdate, paramUpdate);
if (exUpdate != null)
{
throw exUpdate;
}
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Failed to update configuration settings", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Failed to update configuration settings", ex, null, null, null, LogEventType.Error, LogScreen.SiteAdminTools, sqlUpdate));
}
finally
{
if (jicsConn.IsNotClosed())
{
jicsConn.Close();
}
}
gvConfigSettings.EditIndex = -1;
BindGrid();
}
public DataTable GetOfficeAndTask()
{
//OdbcConnectionClass3 jicsConn = helper.CONNECTION_JICS;
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
DataTable dtOffice = null;
Exception exOffice = null;
// string sqlOffice = @"
// SELECT
// O.OfficeName, O.OfficeID, OT.TaskName, OT.TaskID, OT.ViewColumn
// FROM
// CI_OfficeTaskSession OTS INNER JOIN CI_OfficeTask OT ON OTS.OfficeTaskID = OT.TaskID
// INNER JOIN CI_Office O ON OT.OfficeID = O.OfficeID
// WHERE
// OTS.ActiveYear = (SELECT [Value] FROM FWK_ConfigSettings WHERE Category = 'C_CheckIn' AND [Key] = 'ActiveYear')
// AND
// OTS.ActiveSession = (SELECT [Value] FROM FWK_ConfigSettings WHERE Category = 'C_CheckIn' AND [Key] = 'ActiveSession')
// ORDER BY
// O.Sequence, OT.Sequence
// ";
string sqlOffice = "EXECUTE CUS_spCheckIn_GetOfficeAndTask";
try
{
dtOffice = spConn.ConnectToERP(sqlOffice, ref exOffice);
if (exOffice != null)
{
throw exOffice;
}
}
catch (Exception ex)
{
FormatException("An exception occurred while loading office/task table", ex, null, null, null, LogEventType.Error, LogScreen.CheckInAdminHelper, sqlOffice);
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
return(dtOffice);
}
protected void LoadStudentProgress(int cxID)
{
//OdbcConnectionClass3 jicsConn = helper.CONNECTION_JICS;
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
DataTable dtProgress = null;
Exception exProgress = null;
string sqlProgress = String.Format(@"EXECUTE CUS_spCheckIn_AdminGetStudentProgressByID @intHostID = {0}", cxID);
try
{
dtProgress = spConn.ConnectToERP(sqlProgress, ref exProgress);
if (exProgress != null)
{
throw exProgress;
}
if (dtProgress != null && dtProgress.Rows.Count > 0)
{
DataRow dr = dtProgress.AsEnumerable().FirstOrDefault();
this.shDetail.Text = String.Format("Student Detail View for {0} {1} (ID: {2})", dr["FirstName"].ToString(), dr["LastName"].ToString(), dr["HostID"].ToString());
}
dgTasks.DataSource = dtProgress;
dgTasks.DataBind();
}
catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("An error occurred while retrieving the student's record", ex, null, true));
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
}
private void BindGrid()
{
OdbcConnectionClass3 jicsConn = helper.CONNECTION_SP;
DataTable dtConfig = null;
Exception exConfig = null;
string sqlConfig = String.Format("SELECT ID, [Key] AS ConfigKey, [Value] AS ConfigValue, DefaultValue AS ConfigDefaultValue FROM FWK_ConfigSettings WHERE Category = ? ORDER BY [Key]");
List <OdbcParameter> paramConfig = new List <OdbcParameter>()
{
new OdbcParameter("checkInCategory", "C_CheckIn")
};
try
{
dtConfig = jicsConn.ConnectToERP(sqlConfig, ref exConfig, paramConfig);
if (exConfig != null)
{
throw exConfig;
}
gvConfigSettings.DataSource = dtConfig;
gvConfigSettings.DataBind();
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Unable to load configuration data", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error,
ciHelper.FormatException("Unable to load configuration data", ex, null, null, null, null, LogEventType.Error, LogScreen.SiteAdminTools, sqlConfig)
);
}
finally
{
if (jicsConn.IsNotClosed())
{
jicsConn.Close();
}
}
}
public bool LogEvent(string loggedInID = null, int?loggedInHostID = null, string studentID = null, int?studentHostID = null, LogEventType?eventType = null,
string message = null, LogScreen?screen = null, string sql = null, int?activeYear = null, string activeSession = null)
{
//Initialize ODBC connection
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
//Determine if log event was successful
bool logSuccessful = false;
//Initialize variables and SQL for query
Exception exLogEvent = null;
string sqlLogEvent = String.Format(@"
EXECUTE CUS_spCheckIn_LogEvent @strMessage = ?, @strScreen = ?, @intEventTypeSeq = ?
");
//Populate parameters for stored procedure
List <OdbcParameter> paramLogEvent = new List <OdbcParameter>()
{
new OdbcParameter("message", message)
, new OdbcParameter("screen", screen.ToDescriptionString())
, new OdbcParameter("eventSequence", eventType.HasValue ? eventType.ToDescriptionString() : LogEventType.Error.ToDescriptionString())
};
if (!String.IsNullOrWhiteSpace(loggedInID))
{
sqlLogEvent = String.Format("{0}, @uuidLoggedInID = ?", sqlLogEvent);
paramLogEvent.Add(new OdbcParameter("loggedInID", loggedInID));
}
if (loggedInHostID.HasValue)
{
sqlLogEvent = String.Format("{0}, @intLoggedInHostID = ?", sqlLogEvent);
paramLogEvent.Add(new OdbcParameter("loggedInHostID", loggedInHostID));
}
if (!String.IsNullOrWhiteSpace(studentID))
{
sqlLogEvent = String.Format("{0}, @uuidStudentID = ?", sqlLogEvent);
paramLogEvent.Add(new OdbcParameter("studentID", studentID));
}
if (studentHostID.HasValue)
{
sqlLogEvent = String.Format("{0}, @intStudentID = ?", sqlLogEvent);
paramLogEvent.Add(new OdbcParameter("studentHostID", studentHostID));
}
if (!String.IsNullOrWhiteSpace(sql))
{
sqlLogEvent = String.Format("{0}, @strSQL = ?", sqlLogEvent);
paramLogEvent.Add(new OdbcParameter("sql", sql));
}
if (activeYear.HasValue)
{
sqlLogEvent = String.Format("{0}, @intYear = ?", sqlLogEvent);
paramLogEvent.Add(new OdbcParameter("year", activeYear));
}
if (!String.IsNullOrWhiteSpace(activeSession))
{
sqlLogEvent = String.Format("{0}, @strSession = ?", sqlLogEvent);
paramLogEvent.Add(new OdbcParameter("session", activeSession));
}
try
{
spConn.ConnectToERP(sqlLogEvent, ref exLogEvent, paramLogEvent);
if (exLogEvent != null)
{
throw exLogEvent;
}
logSuccessful = true;
}
catch (Exception ex)
{
string messageBody = String.Format("{0}<p>SQL: {1}</p><ul>", FormatExceptionMessage(ex), sqlLogEvent);
foreach (OdbcParameter param in paramLogEvent)
{
messageBody = String.Format("{0}<li>{1}: {2}</li>", messageBody, param.ParameterName, param.Value);
}
messageBody = String.Format("{0}</ul>", messageBody);
Email.CreateAndSendMailMessage("*****@*****.**", "*****@*****.**", "Check-In: Error in Log", messageBody);
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
return(logSuccessful);
}
protected DataTable GetSearchResults()
{
OdbcConnectionClass3 jicsConn = helper.CONNECTION_JICS;
DataTable dtResults = new DataTable();
Exception exResults = null;
#region Dynamically build search SQL
//Get the offices and task which are active for the current year/session
DataTable dtOfficeTask = ciHelper.GetOfficeAndTask();
string sqlSelect = "", sqlFrom = "", sqlWhere = "";
//Loop through each task
foreach (DataRow dr in dtOfficeTask.Rows)
{
string viewColumn = dr["ViewColumn"].ToString();
string tableAlias = viewColumn.Replace("_", "");
//Using ToTitleCase() to capitalize each word in the column alias
TextInfo textinfo = new CultureInfo("en-US", false).TextInfo;
string columnAlias = textinfo.ToTitleCase(viewColumn.Replace('_', ' '));
//Build SELECT portion of SQL statment
sqlSelect = String.Format("{0}, {1}.TaskStatus AS '{2}'", sqlSelect, tableAlias, columnAlias);
//Build JOINS for SQL statement
sqlFrom = String.Format(@"{0}
LEFT JOIN CI_StudentProgress {1} ON U.ID = {1}.UserID
AND {1}.TaskID = (SELECT TaskID FROM CI_OfficeTask WHERE ViewColumn = '{2}')
AND {1}.Yr = {3}
AND {1}.Sess = '{4}'"
, sqlFrom, tableAlias, viewColumn, helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
//Get the collection of radio buttons which correspond to the current task
List <RadioButton> radioForTask = GetRadioGroup(tblOffices, String.Format("Task{0}", viewColumn));
//Was a radio button other than "Any" selected for this task?
RadioButton selectedRadio = radioForTask.FirstOrDefault(rb => rb.Checked == true && !rb.ID.EndsWith("*"));
if (radioForTask.Contains(selectedRadio))
{
//Because some columns address multiple statuses (Y/W, N/P, etc), turn each status into an item in a list and format it for the SQL statement
string status = "";
List <string> statusList = selectedRadio.ID.Split('_').Last().Select(chr => chr.ToString()).ToList();
foreach (string stat in statusList)
{
status = String.Format("{0}{1}'{2}'", status, String.IsNullOrWhiteSpace(status) ? "" : ",", stat);
}
sqlWhere = String.Format("{0} AND {1}.TaskStatus IN ({2})", sqlWhere, tableAlias, status);
}
}
#endregion
string sqlResults = String.Format(@"
SELECT
CAST(CAST(U.HostID AS INT) AS VARCHAR(10)) AS HostID, U.LastName AS 'Last Name', U.FirstName AS 'First Name', U.Email,
'' AS 'Admit Year', '' AS 'Admit Term', '' AS ClassCode, '' AS AcademicStanding{0}
FROM
CI_StudentMetaData SMD INNER JOIN FWK_User U ON SMD.UserID = U.ID
{1}
WHERE
SMD.ActiveYear = {3}
AND
SMD.ActiveSession = '{4}'
AND
SMD.IsActive = 1
{2}
ORDER BY
U.LastName, U.FirstName, U.Email
", sqlSelect, sqlFrom, sqlWhere, helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
if (PortalUser.Current.IsSiteAdmin)
{
ciHelper.FormatException("Formatted SQL for faceted search", new Exception("No problem, just testing log"), null, null, null, LogEventType.Error, LogScreen.FacetSearch, sqlResults, helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
this.ParentPortlet.ShowFeedback(FeedbackType.Message, sqlResults);
}
try
{
dtResults = jicsConn.ConnectToERP(sqlResults, ref exResults);
if (exResults != null)
{
throw exResults;
}
////////////////////////////////////////////////////////////////////
#region Faceted Search - Standing
if (!String.IsNullOrWhiteSpace(this.ddlStanding.SelectedValue))
{
OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_SP;
DataTable dtStanding = null;
Exception exStanding = null;
string sqlStanding = String.Format("EXECUTE PROCEDURE ci_admin_facetedsearch_newstudent({0}, '{1}', '{2}')", helper.ACTIVE_YEAR, helper.ACTIVE_SESSION, this.ddlStanding.SelectedValue);
// string sqlStanding = String.Format(@"
// SELECT
// TRIM(host_id) AS id
// FROM
// jenzcst_rec
// WHERE
// status_code {0} IN ('PFF','PTR')
// GROUP BY
// id
// ", (this.ddlStanding.SelectedValue == "N" ? "NOT" : ""));
try
{
dtStanding = cxConn.ConnectToERP(sqlStanding, ref exStanding);
if (exStanding != null)
{
throw exStanding;
}
if (dtStanding != null && dtStanding.Rows.Count > 0)
{
List <string> standingIDs = dtStanding.AsEnumerable().Select(standing => standing.Field <int>("student_id").ToString()).ToList();
var filteredRows = from row in dtResults.AsEnumerable()
where standingIDs.Contains(row.Field <string>("HostID"))
select row;
dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable();
}
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while filtering facet search based on standing", ex, null, true));
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while filtering facet search based on standing", ex, null, null, null, LogEventType.Error, LogScreen.FacetSearch, sqlStanding));
}
finally
{
if (cxConn.IsNotClosed())
{
cxConn.Close();
}
}
}
#endregion
#region Faceted Search - Athletics
List <ListItem> selectedSports = lbAthletics.Items.Cast <ListItem>().Where(item => item.Selected == true).ToList();
List <string> athleteIDs = new List <string>()
{
};
if (selectedSports.Count > 0)
{
OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_SP;
foreach (ListItem sport in selectedSports)
{
DataTable dtAthletics = null;
Exception exAthletics = null;
string sqlAthletics = String.Format("EXECUTE PROCEDURE ci_admin_facetedsearch_athletics_roster(?)");
List <OdbcParameter> paramAthletics = new List <OdbcParameter>()
{
new OdbcParameter("involve_code", sport.Value)
};
try
{
dtAthletics = cxConn.ConnectToERP(sqlAthletics, ref exAthletics, paramAthletics);
if (exAthletics != null)
{
throw exAthletics;
}
if (dtAthletics != null && dtAthletics.Rows.Count > 0)
{
athleteIDs = athleteIDs.Union <string>(dtAthletics.AsEnumerable().Select(row => row.Field <string>("id")).ToList()).ToList();
}
}
catch (Exception ex)
{
ciHelper.FormatException("An error occurred while building list of athlete IDs for the faceted search.", ex, null, null, null, LogEventType.Error, LogScreen.FacetSearch, sqlAthletics);
}
}
if (cxConn.IsNotClosed())
{
cxConn.Close();
}
var filteredRows = from row in dtResults.AsEnumerable()
where athleteIDs.Contains(row.Field <string>("HostID"))
select row;
dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable();
}
// if (selectedSports.Count > 0)
// {
// string athleticsList = String.Format("'{0}'", String.Join("','", selectedSports.Select(li => li.Value).ToList()));
// OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_LIVE;
// DataTable dtAthletics = null;
// Exception exAthletics = null;
// try
// {
// string sqlAthletics = String.Format(@"
// SELECT
// TRIM(IR.id::varchar(10)) AS id
// FROM
// involve_rec IR INNER JOIN invl_table IT ON TRIM(IR.invl) = TRIM(IT.invl)
// AND IT.sanc_sport = 'Y'
// WHERE
// TODAY BETWEEN IR.beg_date AND NVL(IR.end_date, TODAY)
// AND
// IT.invl IN ({0})
// GROUP BY
// IR.id
// ", athleticsList);
// dtAthletics = cxConn.ConnectToERP(sqlAthletics, ref exAthletics);
// if (exAthletics != null) { throw exAthletics; }
// List<string> athleteIDs = dtAthletics.AsEnumerable().Select(athlete => athlete.Field<string>("id")).ToList();
// var filteredRows = from row in dtResults.AsEnumerable()
// where athleteIDs.Contains(row.Field<string>("HostID"))
// select row;
// dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable();
// }
// catch (Exception ex)
// {
// this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while filtering facet search based on athletics", ex, null, true));
// }
// finally
// {
// if (cxConn.IsNotClosed()) { cxConn.Close(); }
// }
// }
#endregion
#region Faceted Search - Residency
List <ListItem> selectedResidency = cblResidency.Items.Cast <ListItem>().Where(li => li.Selected).ToList();
if (selectedResidency.Count > 0)
{
OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_LIVE;
DataTable dtResidency = null;
Exception exResidency = null;
string residencyList = String.Format("'{0}'", String.Join("','", selectedResidency.Select(li => li.Value).ToList()));
string sqlResidency = String.Format(@"
SELECT
TRIM(SSR.id::varchar(10)) AS id
FROM
stu_serv_rec SSR
WHERE
SSR.yr = {0}
AND
SSR.sess = '{1}'
AND
SSR.intend_hsg IN ({2})
", helper.ACTIVE_YEAR, helper.ACTIVE_SESSION, residencyList);
try
{
dtResidency = cxConn.ConnectToERP(sqlResidency, ref exResidency);
if (exResidency != null)
{
throw exResidency;
}
List <string> residentIDs = dtResidency.AsEnumerable().Select(res => res.Field <string>("id")).ToList();
var filteredRows = from row in dtResults.AsEnumerable()
where residentIDs.Contains(row.Field <string>("HostID"))
select row;
dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable();
}
catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException(String.Format("<p>Error while filtering facet search based on residency</p><p>{0}</p>", sqlResidency), ex, null, true));
}
finally
{
if (cxConn.IsNotClosed())
{
cxConn.Close();
}
}
}
#endregion
#region Faceted Search - Grad Candidacy
if (!String.IsNullOrWhiteSpace(this.ddlGradCandidacy.SelectedValue))
{
OdbcConnectionClass3 cxConn = helper.CONNECTION_CX_SP;
DataTable dtGrad = null;
Exception exGrad = null;
try
{
//string sqlGrad = String.Format("SELECT student_id FROM cc_stg_undergrad_candidacy WHERE datecreated >= TO_DATE('{0}', '%Y-%m-%d')", helper.START_DATE);
string sqlGrad = String.Format("EXECUTE PROCEDURE ci_admin_facetedsearch_undergradcandidacy('{0}')", helper.START_DATE);
dtGrad = cxConn.ConnectToERP(sqlGrad, ref exGrad);
if (exGrad != null)
{
throw exGrad;
}
if (dtGrad != null && dtGrad.Rows.Count > 0)
{
//The "student_id" field coming back from CX is an int so it needs to be re-cast as a string for the LINQ comparisons below
List <string> gradIDs = dtGrad.AsEnumerable().Select(grad => grad.Field <int>("student_id").ToString()).ToList();
//Generally "filteredRows" would be of type "var" but because it is initialized with null and used in one of the two branches, a data type must be specified
EnumerableRowCollection <DataRow> filteredRows = null;
if (this.ddlGradCandidacy.SelectedValue == "Y")
{
filteredRows = from row in dtResults.AsEnumerable()
where gradIDs.Contains(row.Field <string>("HostID"))
select row;
}
else
{
filteredRows = from row in dtResults.AsEnumerable()
where !gradIDs.Contains(row.Field <string>("HostID"))
select row;
}
dtResults = filteredRows == null || filteredRows.Count() == 0 ? new DataTable() : filteredRows.CopyToDataTable();
}
}
catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while filtering search on graduation candidacy.", ex, null, true));
}
finally
{
if (cxConn.IsNotClosed())
{
cxConn.Close();
}
}
}
#endregion
#region Include Additional Fields
if (dtResults != null && dtResults.Rows.Count > 0)
{
//Loop through all rows in the recordset. Processing occurs at this point because the recordset is in its smallest state since all filtering has been completed.
for (int ii = 0; ii < dtResults.Rows.Count; ii++)
{
//Establish database connection
OdbcConnectionClass3 cxSpConn = helper.CONNECTION_CX_SP;
//Initialize query variables
DataTable dtPER = null;
Exception exPER = null;
string sqlPER = String.Format(@"EXECUTE PROCEDURE ci_admin_facetedsearch_extrafields({0})", dtResults.Rows[ii]["HostID"].ToString());
try
{
dtPER = cxSpConn.ConnectToERP(sqlPER, ref exPER);
if (exPER != null)
{
throw exPER;
}
if (dtPER != null && dtPER.Rows.Count > 0)
{
DataRow dr = dtPER.Rows[0];
dtResults.Rows[ii]["Admit Year"] = dr["adm_yr"].ToString();
dtResults.Rows[ii]["Admit Term"] = dr["adm_sess"].ToString();
dtResults.Rows[ii]["ClassCode"] = dr["cl"].ToString();
dtResults.Rows[ii]["AcademicStanding"] = dr["acad_stat"].ToString();
}
}
catch (Exception ex)
{
//ciHelper.FormatException("Could not load program enrollment data in faceted search.", ex);
ciHelper.FormatException("Could not load program enrollment data in faceted search.", ex, null, null, null, LogEventType.Error, LogScreen.FacetSearch, sqlPER);
}
finally
{
//Always close database connection
if (cxSpConn.IsNotClosed())
{
cxSpConn.Close();
}
}
}
}
#endregion
////////////////////////////////////////////////////////////////////
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("An exception occurred while running the faceted search.", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error,
ciHelper.FormatException("An exception occurred while running the faceted search.", ex, null, null, null, LogEventType.Error, LogScreen.FacetSearch));
}
finally
{
if (jicsConn.IsNotClosed())
{
jicsConn.Close();
}
}
return(dtResults);
}
protected void btnIncomplete_Click(object sender, EventArgs e)
{
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
DataTable dtIncomplete = null;
Exception exIncomplete = null;
string sqlIncomplete = "EXECUTE dbo.CUS_spCheckIn_ExportIncompleteTasks";
try
{
dtIncomplete = spConn.ConnectToERP(sqlIncomplete, ref exIncomplete);
if (exIncomplete != null)
{
throw exIncomplete;
}
}
catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("An exception occurred while exporting incomplete tasks", ex, null, null, null, LogEventType.Error, LogScreen.Dashboard, sqlIncomplete));
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
this.gvIncomplete.DataSource = dtIncomplete;
this.gvIncomplete.DataBind();
this.gvIncomplete.Visible = true;
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=Check-In Students with Incomplete Tasks.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
/****************************************************************************
* Ordinarily, this control would be written using the RenderControl() method
* but because all JICS portlets exist as .ascx files, we do not have access
* to the <form> tag which causes an exception to be thrown. The simplest
* solution to circumvent the exception is outlined in this article:
* http://stackoverflow.com/questions/6343630/gridview-must-be-placed-inside-a-form-tag-with-runat-server-even-after-the-gri
*
* Essentially, the control is explicitly rendered step-by-step. Another option
* is to remove the GridView from the page's controls collection while performing
* the rendering and then re-adding it before the page loads. This approach was
* deemed more straightforward.
*****************************************************************************/
gvIncomplete.RenderBeginTag(hw);
gvIncomplete.HeaderRow.RenderControl(hw);
foreach (GridViewRow row in gvIncomplete.Rows)
{
row.RenderControl(hw);
}
gvIncomplete.FooterRow.RenderControl(hw);
gvIncomplete.RenderEndTag(hw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
this.gvIncomplete.Visible = false;
}
private void LoadStudentActivity()
{
//OdbcConnectionClass3 jicsConn = helper.CONNECTION_JICS;
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
DataTable dtStudentActivity = null;
Exception exStudentActivity = null;
string sqlStudentActivity = "EXECUTE CUS_spCheckIn_AdminStudentActivity";
#region Obsolete - 4 hour block logic
/* 4-hour blocks */
// string sqlStudentActivity = String.Format(@"
// SELECT
// HT.minTime, COUNT(SP.ProgressID) AS Completed,
// CASE
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 0 AND 3 THEN 1
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 4 AND 7 THEN 2
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 8 AND 11 THEN 3
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 12 AND 15 THEN 4
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 16 AND 19 THEN 5
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 20 AND 23 THEN 6
// END AS CmplHourSection,
// CASE
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 0 AND 3 THEN 'Midnight - 3 a.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 4 AND 7 THEN '4 a.m. - 7 a.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 8 AND 11 THEN '8 a.m. - 11 a.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 12 AND 15 THEN '12 p.m. - 3 p.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 16 AND 19 THEN '4 p.m. - 7 p.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 20 AND 23 THEN '8 p.m. - 11 p.m.'
// END AS CmplHour
// FROM
// (
// --Borrowed liberally from https://stackoverflow.com/questions/11479918/include-missing-months-in-group-by-query
// SELECT
// DATEADD(HOUR, n * 4, DATEADD(HOUR, DATEDIFF(HOUR, 0, (SELECT CAST(MIN(CompletedOn) AS DATE) FROM CI_StudentProgress)), 0)) AS minTime
// FROM (
// SELECT TOP ((DATEDIFF(DAY, (SELECT CAST(MIN(CompletedOn) AS DATE) FROM CI_StudentProgress), (SELECT CAST(MAX(CompletedOn) AS DATE) FROM CI_StudentProgress)) + 1) * 6)
// n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
// FROM sys.all_objects ORDER BY [object_id]
// ) hourTable
// ) AS HT LEFT JOIN CI_StudentProgress SP ON HT.minTime < SP.CompletedOn
// AND DATEADD(HOUR, 4, HT.minTime) > SP.CompletedOn
// WHERE
// ISNULL(SP.TaskStatus,'Y') = 'Y'
// GROUP BY
// HT.minTime,
// CASE
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 0 AND 3 THEN 1
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 4 AND 7 THEN 2
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 8 AND 11 THEN 3
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 12 AND 15 THEN 4
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 16 AND 19 THEN 5
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 20 AND 23 THEN 6
// END,
// CASE
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 0 AND 3 THEN 'Midnight - 3 a.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 4 AND 7 THEN '4 a.m. - 7 a.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 8 AND 11 THEN '8 a.m. - 11 a.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 12 AND 15 THEN '12 p.m. - 3 p.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 16 AND 19 THEN '4 p.m. - 7 p.m.'
// WHEN DATEPART(HOUR, HT.minTime) BETWEEN 20 AND 23 THEN '8 p.m. - 11 p.m.'
// END
// ORDER BY
// HT.minTime
// ");
#endregion
try
{
//dtStudentActivity = jicsConn.ConnectToERP(sqlStudentActivity, ref exStudentActivity);
dtStudentActivity = spConn.ConnectToERP(sqlStudentActivity, ref exStudentActivity);
if (exStudentActivity != null)
{
throw exStudentActivity;
}
chartStudentActivity.DataSource = dtStudentActivity;
chartStudentActivity.DataBind();
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error while drawing chart for student activity", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error,
ciHelper.FormatException("Error while drawing chart for student activity", ex, null, null, null, null, LogEventType.Error, LogScreen.Dashboard, sqlStudentActivity)
);
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
this.shStudentActivity.Text = String.Format("Student Activity for {0} {1}", helper.ACTIVE_SESSION_TEXT, helper.ACTIVE_YEAR);
}
protected void btnAddRollover_Click(object sender, EventArgs e)
{
string feedback = "";
OdbcConnectionClass3 jicsSpConn = helper.CONNECTION_SP;
Exception exReset = null;
string rolloverYear = String.IsNullOrWhiteSpace(this.ddlRolloverTaskYear.SelectedValue) ? "NULL" : this.ddlRolloverTaskYear.SelectedValue;
string rolloverSession = String.IsNullOrWhiteSpace(this.ddlRolloverTaskSession.SelectedValue) ? "NULL" : "'" + this.ddlRolloverTaskSession.SelectedValue + "'";
string sqlReset = String.Format(@"
UPDATE
CI_OfficeTaskSession
SET
IsRollover = 0
, RolloverYear = {0}
, RolloverSession = {1}
WHERE
ActiveYear = {2}
AND
ActiveSession = '{3}'
", rolloverYear, rolloverSession, helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
feedback = String.Format("{0}<p>{1}</p>", feedback, sqlReset);
try
{
jicsSpConn.ConnectToERP(sqlReset, ref exReset);
if (exReset != null)
{
throw exReset;
}
string selectedOfficeTaskSession = String.Join(",", this.cblRolloverTask.Items.Cast <ListItem>().Where(li => li.Selected == true).Select(li => "'" + li.Value + "'"));
if (!String.IsNullOrWhiteSpace(selectedOfficeTaskSession))
{
Exception exUpdate = null;
string sqlUpdate = String.Format(@"
UPDATE
CI_OfficeTaskSession
SET
IsRollover = 1
WHERE
OfficeTaskSessionID IN ({0})
", selectedOfficeTaskSession);
try
{
jicsSpConn.ConnectToERP(sqlUpdate, ref exUpdate);
if (exUpdate != null)
{
throw exUpdate;
}
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Could not update rollover data", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Could not update rollover data", ex, null, null, null, LogEventType.Error, LogScreen.SiteAdminTools, sqlUpdate));
}
feedback = String.Format("{0}<p>{1}</p>", feedback, sqlUpdate);
}
}
catch (Exception ex)
{
//this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Could not reset rollover data", ex));
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Could not reset rollover data", ex, null, null, null, LogEventType.Error, LogScreen.SiteAdminTools, sqlReset));
}
finally
{
if (jicsSpConn.IsNotClosed())
{
jicsSpConn.Close();
}
}
this.ParentPortlet.ShowFeedback(FeedbackType.Message, feedback);
}
public string GenerateStudentMetaData()
{
Stopwatch sw = new Stopwatch();
sw.Start();
string debug = "";
string sqlStudentsFromCX = "";
if (helper.ACTIVE_SESSION == "RA")
{
//debug = String.Format("{0}<p>Load students for Fall</p>", debug);
sqlStudentsFromCX = String.Format("EXECUTE PROCEDURE ci_get_students_fall({0})", helper.ACTIVE_YEAR);
}
else if (helper.ACTIVE_SESSION == "RC")
{
//debug = String.Format("{0}<p>Load students for Spring</p>", debug);
sqlStudentsFromCX = String.Format("EXECUTE PROCEDURE ci_get_students_spring({0}, 'RA', {1}, 'RC')", helper.ACTIVE_YEAR - 1, helper.ACTIVE_YEAR);
}
else
{
debug = String.Format("{0}<p>Unknown term '{1}' for data load</p>", debug, helper.ACTIVE_SESSION);
}
OdbcConnectionClass3 cxSpConn = helper.CONNECTION_CX_SP;
DataTable dtStudentsFromCX = null;
List <int> listStudentsFromCX = new List <int>()
{
};
Exception exStudentsFromCX = null;
try
{
dtStudentsFromCX = cxSpConn.ConnectToERP(sqlStudentsFromCX, ref exStudentsFromCX);
if (exStudentsFromCX != null)
{
throw exStudentsFromCX;
}
if (dtStudentsFromCX != null && dtStudentsFromCX.Rows.Count > 0)
{
listStudentsFromCX = dtStudentsFromCX.AsEnumerable().Select(row => row.Field <int>("cx_id")).ToList();
}
}
catch (Exception ex)
{
//debug = String.Format("{0}<p>Encountered a problem getting students from CX:</p><p>{1}</p>", debug, this.FormatException("", ex));
debug = String.Format("{0}<p>Encountered a problem getting students from CX:</p><p>{1}</p>", debug,
this.FormatException("", ex, null, null, null, LogEventType.Error, LogScreen.CheckInAdminHelper, sqlStudentsFromCX)
);
}
sw.Stop();
debug = String.Format("{0}<p>Time for getting {1} students from CX: {2}</p>", debug, listStudentsFromCX.Count, sw.Elapsed.ToString());
sw.Reset();
sw.Start();
OdbcConnectionClass3 jicsConn = helper.CONNECTION_SP;
DataTable dtStudentsFromJICS = null;
Exception exStudentsFromJICS = null;
string sqlStudentsFromJICS = String.Format(@"EXECUTE CUS_spCheckIn_GetStudentMetaData @intYear = {0}, @strSession = '{1}'", helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
try
{
dtStudentsFromJICS = jicsConn.ConnectToERP(sqlStudentsFromJICS, ref exStudentsFromJICS);
if (exStudentsFromJICS != null)
{
throw exStudentsFromJICS;
}
}
catch (Exception ex)
{
//debug = String.Format("{0}<p>Encountered problem getting students from JICS:</p><p>{1}</p>", debug, this.FormatException("", ex));
debug = String.Format("{0}<p>Encountered problem getting students from JICS:</p><p>{1}</p>", debug,
this.FormatException("", ex, null, null, null, LogEventType.Error, LogScreen.CheckInAdminHelper, sqlStudentsFromJICS)
);
}
//Initialize lists
List <int> disableJICS = new List <int>()
{
}, enableJICS = new List <int>()
{
}, createJICS = new List <int>()
{
};
//Identify which records need to be changed (created, enabled, or disabled)
try
{
//Flip IsActive from 1 to 0
disableJICS = dtStudentsFromJICS.AsEnumerable().Where(row => row.Field <bool>("IsActive") == true && !listStudentsFromCX.Contains(row.Field <int>("HostID"))).Select(row => row.Field <int>("HostID")).ToList();
//Flip IsActive from 0 to 1
enableJICS = dtStudentsFromJICS.AsEnumerable().Where(row => row.Field <bool>("IsActive") == false && listStudentsFromCX.Contains(row.Field <int>("HostID"))).Select(row => row.Field <int>("HostID")).ToList();
//Create StudentMetaData record
createJICS = listStudentsFromCX.Where(list => !dtStudentsFromJICS.AsEnumerable().Select(row => row.Field <int>("HostID")).Contains(list)).ToList();
debug = String.Format("{0}<p>Enable {1} records<br />Disable {2} records<br />Create {3} records</p>", debug, enableJICS.Count, disableJICS.Count, createJICS.Count);
}
catch (Exception ex)
{
//debug = String.Format("{0}<p>Encountered problem while determining presence or absence of StudentMetaData:</p><p>{1}</p>", debug, ciHelper.FormatException("", ex));
}
sw.Stop();
debug = String.Format("{0}<p>Time to assemble lists: {1}</p>", debug, sw.Elapsed.ToString());
sw.Reset();
OdbcConnectionClass3 jicsSpConn = helper.CONNECTION_SP;
Exception exSP = null;
string sqlSP = "";
#region Disable Student Meta Data
sw.Start();
foreach (int cxID in disableJICS)
{
try
{
sqlSP = String.Format("EXECUTE CUS_spCheckIn_DisableStudentMetaData @intHostID = {0}", cxID);
jicsSpConn.ConnectToERP(sqlSP, ref exSP);
if (exSP != null)
{
throw exSP;
}
}
catch (Exception ex)
{
//debug = String.Format("{0}<p>Error executing disable: {1}<br />{2}</p>", debug, sqlSP, this.FormatException("", ex));
debug = String.Format("{0}<p>Error executing disable: {1}<br />{2}</p>", debug, sqlSP,
this.FormatException("", ex, null, null, cxID, LogEventType.Error, LogScreen.CheckInAdminHelper, sqlSP));
}
}
sw.Stop();
debug = String.Format("{0}<p>Time to process disable list ({1}): {2}</p>", debug, disableJICS.Count, sw.Elapsed.ToString());
sw.Reset();
#endregion
#region Enable/Create Student Meta Data
sw.Start();
List <int> combinedIDs = enableJICS.Union(createJICS).ToList();
foreach (int cxID in combinedIDs)
{
try
{
sqlSP = String.Format("EXECUTE CUS_spCheckIn_InsertUpdateStudentMetaData @intHostID = {0}", cxID);
jicsSpConn.ConnectToERP(sqlSP, ref exSP);
if (exSP != null)
{
throw exSP;
}
}
catch (Exception ex)
{
//debug = String.Format("{0}<p>Error executing insert/update: {1}<br />{2}</p>", debug, sqlSP, this.FormatException("", ex));
debug = String.Format("{0}<p>Error executing insert/update: {1}<br />{2}</p>", debug, sqlSP,
this.FormatException("", ex, null, null, cxID, LogEventType.Error, LogScreen.CheckInAdminHelper, sqlSP));
}
}
sw.Stop();
debug = String.Format("{0}<p>Time to process create/update ({1}): {2}</p>", debug, combinedIDs.Count, sw.Elapsed.ToString());
sw.Reset();
#endregion
#region Initialize Student Progress
sw.Start();
string sqlInitStudentProgress = "EXECUTE CUS_spCheckIn_InitializeStudentProgress";
try
{
jicsSpConn.ConnectToERP(sqlInitStudentProgress, ref exSP);
if (exSP != null)
{
throw exSP;
}
}
catch (Exception ex)
{
debug = String.Format("{0}<p>Error when initializing student progress</p>", debug);
FormatException("Error when initializing student progress in GenerateStudentMetaData()", ex, null, null, null, LogEventType.Error, LogScreen.CheckInAdminHelper, sqlInitStudentProgress);
}
finally
{
sw.Stop();
debug = String.Format("{0}<p>Time to process student progress initialization: {1}</p>", debug, sw.Elapsed.ToString());
sw.Reset();
}
#endregion
if (jicsSpConn.IsNotClosed())
{
jicsSpConn.Close();
}
return(debug);
}
protected void btnUpdateRegStat_Click(object sender, EventArgs e)
{
string feedback = "<p>Begin processing reg_stat information</p>";
OdbcConnectionClass3 jicsSpConn = helper.CONNECTION_SP;
DataTable dtIncomplete = null;
Exception exIncomplete = null;
int recordsCompleted = 0;
try
{
//Get every incomplete task for every active student
string sqlIncomplete = String.Format("EXECUTE CUS_spCheckIn_GetIncompleteTasks");
dtIncomplete = jicsSpConn.ConnectToERP(sqlIncomplete, ref exIncomplete);
if (exIncomplete != null)
{
throw exIncomplete;
}
if (dtIncomplete != null && dtIncomplete.Rows.Count > 0)
{
//For each task, execute the appropriate CX stored procedure to determine if it has been completed outside the check-in process
foreach (DataRow dr in dtIncomplete.Rows)
{
try
{
CheckInTaskStatus result = helper.updatePortalTaskStatusFromCX(dr["ViewColumn"].ToString(), dr["UserID"].ToString(), helper.ACTIVE_YEAR, helper.ACTIVE_SESSION, int.Parse(dr["HostID"].ToString()));
if (result == CheckInTaskStatus.Yes)
{
recordsCompleted++;
}
}
catch (Exception ex)
{
//feedback = String.Format("{0}<p>Error while updating {1} for {2}<br />Message: {3}</p>", feedback, dr["ViewColumn"].ToString(), dr["UserID"].ToString(), ciHelper.FormatException("", ex));
feedback = String.Format("{0}<p>Error while updating {1} for {2}<br />Message: {3}</p>", feedback, dr["ViewColumn"].ToString(), dr["UserID"].ToString(),
ciHelper.FormatException("", ex, null, null, null, LogEventType.Error, LogScreen.Dashboard, sqlIncomplete)
);
}
}
//DataRow dr = dtIncomplete.Rows[0];
//try
//{
// CheckInTaskStatus result = helper.updatePortalTaskStatusFromCX(dr["ViewColumn"].ToString(), dr["UserID"].ToString(), helper.ACTIVE_YEAR, helper.ACTIVE_SESSION, int.Parse(dr["HostID"].ToString()));
// if (result == CheckInTaskStatus.Yes) { recordsCompleted++; }
//}
//catch (Exception ex)
//{
// feedback = String.Format("{0}<p>Error while updating {1} for {2}<br />Message: {3}</p>", feedback, dr["ViewColumn"].ToString(), dr["UserID"].ToString(), ciHelper.FormatException("", ex));
//}
feedback = String.Format("{0}<p>{1} incomplete tasks found; {2} were resolved.</p>", feedback, dtIncomplete.Rows.Count, recordsCompleted);
//Once the task statuses are current, run the process to update the "CompletedOn" field in CI_StudentMetaData and return the affected records so the CX updates can be made
DataTable dtComplete = null;
Exception exComplete = null;
string sqlComplete = "EXECUTE CUS_spCheckIn_AdminProcessCompleted";
try
{
dtComplete = jicsSpConn.ConnectToERP(sqlComplete, ref exComplete);
if (exComplete != null)
{
throw exComplete;
}
if (dtComplete != null && dtComplete.Rows.Count > 0)
{
feedback = String.Format("{0}<p>Preparing to update reg_stat for {1} record(s)</p>", feedback, dtComplete.Rows.Count);
OdbcConnectionClass3 cxSpConn = helper.CONNECTION_CX_SP;
int updateCount = 0;
string debugFailedID = "";
foreach (DataRow drComplete in dtComplete.Rows)
{
Exception exRegStat = null;
string sqlRegStat = String.Format("EXECUTE PROCEDURE ci_registrar_set_regstat({0}, {1}, '{2}')", drComplete["HostID"].ToString(), helper.ACTIVE_YEAR, helper.ACTIVE_SESSION);
try
{
cxSpConn.ConnectToERP(sqlRegStat, ref exRegStat);
if (exRegStat != null)
{
throw exRegStat;
}
updateCount++;
}
catch (Exception ex)
{
debugFailedID = String.Format("{0}<p>Failed to execute: {1}</p>", debugFailedID, sqlRegStat);
}
}
feedback = String.Format("{0}<p>Completed reg_stat update for {1} record(s)</p>", feedback, updateCount);
if (cxSpConn.IsNotClosed())
{
cxSpConn.Close();
}
//Send list of errors to administrator
if (!String.IsNullOrWhiteSpace(debugFailedID))
{
ciHelper.FormatException(debugFailedID, new Exception(), null, true);
}
}
}
catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error, ciHelper.FormatException("Error processing completed students", ex, null, true));
}
}
else
{
feedback = String.Format("{0}<p>No incomplete records found</p>", feedback);
}
}
catch (Exception ex)
{
//feedback = String.Format("{0}<p>{1}</p>", feedback, ciHelper.FormatException("Error while getting list of all incomplete tasks", ex));
feedback = String.Format("{0}<p>{1}</p>", feedback, ciHelper.FormatException("Error while getting list of all incomplete tasks", ex, null, null, null, LogEventType.Error, LogScreen.Dashboard));
}
finally
{
if (jicsSpConn.IsNotClosed())
{
jicsSpConn.Close();
}
}
ciHelper.LogEvent(null, null, null, LogEventType.Info, feedback, LogScreen.Dashboard);
this.ParentPortlet.ShowFeedback(FeedbackType.Message, feedback);
}
private void LoadStudentProgress()
{
#region Commented out
//DataTable dtStudentProgress = ciHelper.StudentProgressCounts();
//DataTable dtStudentProgressCounts = new DataTable();
//dtStudentProgressCounts.Columns.AddRange(new DataColumn[]{
// new DataColumn("Complete"),
// new DataColumn("Missing1"),
// new DataColumn("Started"),
// new DataColumn("NotStarted")
//});
//DataRow dr = dtStudentProgressCounts.NewRow();
//int totalTasks = ciHelper.GetTasks().Rows.Count;
//dr["Complete"] = dtStudentProgress.AsEnumerable().Count(row => row.Field<int>("completed_task_count") + row.Field<int>("waived_task_count") == totalTasks);
//dr["Missing1"] = dtStudentProgress.AsEnumerable().Count(row => row.Field<int>("completed_task_count") + row.Field<int>("waived_task_count") == totalTasks - 1);
//dr["Started"] = dtStudentProgress.AsEnumerable().Count(row => row.Field<int>("completed_task_count") + row.Field<int>("waived_task_count") < totalTasks - 1 && row.Field<int>("completed_task_count") > 0);
//dr["NotStarted"] = dtStudentProgress.AsEnumerable().Count(row => row.Field<int>("completed_task_count") == 0);
//dtStudentProgressCounts.Rows.Add(dr);
#endregion
#region Faster Progress Count
OdbcConnectionClass3 spConn = helper.CONNECTION_SP;
DataTable dtStudentProgressCounts = null;
Exception exStudentProgressCounts = null;
string sqlProgress = @"EXECUTE CUS_spCheckIn_GetStudentProgressSummary";
try
{
dtStudentProgressCounts = spConn.ConnectToERP(sqlProgress, ref exStudentProgressCounts);
if (exStudentProgressCounts != null)
{
throw exStudentProgressCounts;
}
//foreach (DataRow dr in dtStudentProgressCounts.Rows)
//{
// string seriesName = String.Format("Completed{0}", dr["TaskCount"].ToString());
// chartStudentProgress.Series.Add(seriesName);
// chartStudentProgress.Series[seriesName].ChartType = SeriesChartType.Column;
// chartStudentProgress.Series[seriesName].XValueMember = "TaskCount";
// chartStudentProgress.Series[seriesName].YValueMembers = "StudentCount";
// chartStudentProgress.Series[seriesName].IsValueShownAsLabel = true;
// //chartStudentProgress.Series[seriesName].IsVisibleInLegend = true;
//}
chartStudentProgress.ChartAreas["caStudentProgress"].AxisX.Maximum = dtStudentProgressCounts.Rows.Count - 1;
chartStudentProgress.DataSource = dtStudentProgressCounts;
chartStudentProgress.DataBind();
}
catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error, helper.FormatException("An error occurred while retrieving student progress counts", ex));
}
finally
{
if (spConn.IsNotClosed())
{
spConn.Close();
}
}
#endregion
this.shStudentProgress.Text = String.Format("Student Progress for {0} {1}", helper.ACTIVE_SESSION_TEXT, helper.ACTIVE_YEAR);
}