public TimeSpan GetLatestSessionDuration(DateTime sessionFinishTime) // This is the same as JobStatusChange.LastUpdated.
{
DataTable dtData = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selJobHistory";
TimeSpan sessionDuration = TimeSpan.MinValue;
colParameters.Add(new SqlParameter("@WMSourceID", this.ID));
colParameters.Add(new SqlParameter("@StopDateTime", sessionFinishTime));
dtData = objADO.GetDataTable(strStoredProcedure, "JobHistory", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (dtData != null && dtData.Rows.Count > 0)
{
DataRow drMember = dtData.Rows[0];
sessionDuration = ((DateTime)drMember["StopDateTime"]).Subtract((DateTime)drMember["StartDateTime"]);
}
return sessionDuration;
}
public static UserMessageCollection GetMessages(string recipientID)
{
UserMessageCollection colMessages = null;
DataTable dtMessages = new DataTable();
UserMessagingSection objSection = (UserMessagingSection)ConfigurationManager.GetSection("UserMessagingSection");
SQLServerDB objDatabaseSettings = null;
DataAccess objADO = null;
ArrayList colParameters = new ArrayList();
SqlParameter[] arrParameters = null;
try
{
if (objSection != null)
{
objDatabaseSettings = objSection.DataStore;
}
if (objDatabaseSettings == null)
{
throw new Exception("Unable to retrieve User Messages from SQL Server as Connection information was not specified");
}
objADO = new DataAccess(objDatabaseSettings.ConnectionString);
colParameters.Add(new SqlParameter("@RecipientID", recipientID));
arrParameters = (SqlParameter[])colParameters.ToArray(typeof(SqlParameter));
dtMessages = objADO.GetDataTable("selUserMessages", "UserMessages", arrParameters);
if (dtMessages.Rows.Count > 0)
{
colMessages = UserMessageCollection.Populate(dtMessages);
}
}
catch (Exception excE)
{
throw excE;
}
finally
{
objADO = null;
}
return colMessages;
}
public void LoadFileAssociations()
{
DataTable dtData = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selJobAssociatedFiles";
colParameters.Add(new SqlParameter("@WMSourceID", mintID));
colParameters.Add(new SqlParameter("@WMSourceSystem", mobjSourceSystem.ToString()));
dtData = objADO.GetDataTable(strStoredProcedure, "FileAssociations", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (dtData != null && dtData.Rows.Count > 0)
{
mColFileAssociations = new FileAssociationCollection();
FileAssociation fileMetadata = null;
foreach (DataRow drMember in dtData.Rows)
{
fileMetadata = new FileAssociation();
fileMetadata.Populate(drMember);
mColFileAssociations.Add(fileMetadata);
}
}
}
public static string[] GetAssigneeInformation(int jobID, eWMSourceSystem sourceSystem, int instanceNumber, bool includeArchived)
{
DataTable dtData = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selJobAssignees";
string[] clickEngineerIDs = null;
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
colParameters.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString()));
if (instanceNumber != -1)
{
colParameters.Add(new SqlParameter("@JobInstanceNumber", instanceNumber));
}
colParameters.Add(new SqlParameter("@IncludeArchived", includeArchived));
dtData = objADO.GetDataTable(strStoredProcedure, "AssigneeInformation", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (dtData != null && dtData.Rows.Count > 0)
{
clickEngineerIDs = new string[dtData.Rows.Count];
string assigneeID = string.Empty;
for (int intIndex = 0; intIndex < dtData.Rows.Count; intIndex++)
{
assigneeID = dtData.Rows[intIndex]["UserID"].ToString();
if (!dtData.Rows[intIndex]["EmpNo"].Equals(DBNull.Value) && dtData.Rows[intIndex]["EmpNo"] != string.Empty)
{
assigneeID += ":" + dtData.Rows[intIndex]["EmpNo"].ToString();
}
clickEngineerIDs[intIndex] = assigneeID;
}
}
return clickEngineerIDs;
}
/// <summary>
/// Returns the last start time that was sent from the mobile for a Job. This value is found in tblJobHistory.
/// </summary>
/// <param name="jobID">The ID of the Job in question.</param>
/// <returns>The latest start date.</returns>
public static DateTime GetLatestStartTime(int jobID)
{
DataTable dtData = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selJobHistory";
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
dtData = objADO.GetDataTable(strStoredProcedure, "JobHistory", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
DateTime latestStartTime = DateTime.MinValue;
if (dtData != null && dtData.Rows.Count > 0)
{
DateTime latestRecord = DateTime.MinValue;
foreach (DataRow row in dtData.Rows)
{
if ((DateTime)row["LastUpdatedDate"] > latestRecord)
{
latestRecord = (DateTime)row["LastUpdatedDate"];
latestStartTime = (DateTime)row["StartDateTime"];
}
}
}
return latestStartTime;
}
public static OnHoldReasonCollection FindByJob(int jobID)
{
OnHoldReasonCollection colMembers = new OnHoldReasonCollection();
OnHoldReason onHoldReason = null;
DataAccess objADO = new DataAccess(); // TaskStore database
DataTable dtResults = null;
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selJobOnHoldDetails";
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
dtResults = objADO.GetDataTable(strStoredProcedure, "OnHoldReasons", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (dtResults != null && dtResults.Rows.Count > 0)
{
foreach (DataRow drMember in dtResults.Rows)
{
onHoldReason = new OnHoldReason();
onHoldReason.ID = (int)drMember["OnHoldReasonID"];
onHoldReason.Code = drMember["OnHoldReasonCode"].ToString();
onHoldReason.Description = drMember["OnHoldReasonDesc"].ToString();
// Note. Additional columns expected in this method to other Find methods
if (!drMember["DateResolved"].Equals(DBNull.Value))
{
onHoldReason.DateResolved = (DateTime)drMember["DateResolved"];
}
if (!drMember["ResolvingUser"].Equals(DBNull.Value))
{
onHoldReason.ResolvingUser = drMember["ResolvingUser"].ToString();
}
if (!drMember["Comments"].Equals(DBNull.Value))
{
System.Xml.XmlDocument objDOM = new System.Xml.XmlDocument();
objDOM.LoadXml(drMember["Comments"].ToString());
onHoldReason.Comments = (CommentAuditRecordCollection)BusinessObjects.Base.Deserialize(typeof(CommentAuditRecordCollection), objDOM);
}
colMembers.Add(onHoldReason);
}
}
return colMembers;
}
public static FileAssociationCollection LoadByUser(string userID)
{
FileAssociationCollection collection = null;
DataTable dtData = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selUserAssociatedFiles";
colParameters.Add(new SqlParameter("@UserID", userID));
dtData = objADO.GetDataTable(strStoredProcedure, "FileAssociations", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (dtData != null && dtData.Rows.Count > 0)
{
collection = new FileAssociationCollection();
FileAssociation fileMetadata = null;
foreach (DataRow drMember in dtData.Rows)
{
fileMetadata = new FileAssociation();
fileMetadata.Populate(drMember);
collection.Add(fileMetadata);
}
}
return collection;
}
/// <summary>
/// Retrieve TaskUpdates submitted together for the Job
/// Optionally at a specific DateTime
/// Optionally for a specific User (and at a specific DateTime if set)
/// </summary>
/// <param name="wmSourceID"></param>
/// <param name="sourceSystem"></param>
/// <param name="specificChangeDate"></param>
/// <param name="userID"></param>
/// <returns></returns>
private static System.Collections.Generic.List<HistoricalTaskUpdate> GetStoredTaskUpdates(int wmSourceID, eWMSourceSystem sourceSystem, DateTime specificChangeDate, string userID, string typeName)
{
System.Collections.Generic.List<HistoricalTaskUpdate> serializedTaskUpdates = null;
DataTable dtData = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selJobTaskUpdates";
colParameters.Add(new SqlParameter("@WMSourceID", wmSourceID));
colParameters.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString()));
if (!string.IsNullOrEmpty(userID))
{
colParameters.Add(new SqlParameter("@UserID", userID));
}
if (specificChangeDate != DateTime.MinValue)
{
colParameters.Add(new SqlParameter("@ChangeDate", specificChangeDate));
}
if (!string.IsNullOrEmpty(typeName))
{
colParameters.Add(new SqlParameter("@TypeName", typeName));
}
dtData = objADO.GetDataTable(strStoredProcedure, "TaskUpdates", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (dtData != null && dtData.Rows.Count > 0)
{
serializedTaskUpdates = new System.Collections.Generic.List<HistoricalTaskUpdate>();
foreach (DataRow drMember in dtData.Rows)
{
serializedTaskUpdates.Add(new HistoricalTaskUpdate());
serializedTaskUpdates[serializedTaskUpdates.Count - 1].Serialized = new System.Xml.XmlDocument();
serializedTaskUpdates[serializedTaskUpdates.Count - 1].Serialized.LoadXml(drMember["Serialized"].ToString());
serializedTaskUpdates[serializedTaskUpdates.Count - 1].TypeName = (drMember["TypeName"].ToString());
}
}
return serializedTaskUpdates;
}
public static LostTimeUpdateCollection GetLostTimeUpdateHistory(int wmSourceID, eWMSourceSystem sourceSystem, string userID)
{
LostTimeUpdateCollection lostTimeUpdates = null;
DataTable dtData = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selJobLostTimeHistory";
colParameters.Add(new SqlParameter("@WMSourceID", wmSourceID));
colParameters.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString()));
if (userID != null && userID != string.Empty)
{
colParameters.Add(new SqlParameter("@UserID", userID));
}
dtData = objADO.GetDataTable(strStoredProcedure, "LostTimeUpdates", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (dtData != null && dtData.Rows.Count > 0)
{
lostTimeUpdates = new LostTimeUpdateCollection();
foreach (DataRow drMember in dtData.Rows)
{
LostTimeUpdate lostTimeUpdate = new LostTimeUpdate();
lostTimeUpdate.Reason = new LostTimeReason();
lostTimeUpdate.Reason.ID = (int)drMember["LostTimeID"];
lostTimeUpdate.Reason.Description = drMember["Description"].ToString();
lostTimeUpdate.Comments = drMember["Comments"].ToString();
lostTimeUpdate.Minutes = (int)drMember["Minutes"];
lostTimeUpdates.Add(lostTimeUpdate);
//////lostTimeUpdates.Add(new LostTimeUpdate());
//////lostTimeUpdates[lostTimeUpdates.Count - 1].LostTimeID = (int)drMember["LostTimeID"];
//////lostTimeUpdates[lostTimeUpdates.Count - 1].Comments = drMember["Description"].ToString();
//////lostTimeUpdates[lostTimeUpdates.Count - 1].Minutes = (int)drMember["Minutes"];
}
}
return lostTimeUpdates;
}
public static DataTable GetCurrentSchedulingInfo(int wmSourceID, eWMSourceSystem sourceSystem, out DateTime currentStartDate)
{
DataTable dtInfo = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
SqlParameter objOutputParameter = null;
string strStoredProcedure = "selJobSchedulingInfo";
currentStartDate = DateTime.MinValue;
colParameters.Add(new SqlParameter("@WMSourceID", wmSourceID));
colParameters.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString()));
objOutputParameter = new SqlParameter("@TargetStartDate", SqlDbType.DateTime);
objOutputParameter.Direction = ParameterDirection.Output;
colParameters.Add(objOutputParameter);
dtInfo = objADO.GetDataTable(strStoredProcedure, "SchedulingInfo", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
// Set currentStartDate out parameter if available
if (!objOutputParameter.Value.Equals(DBNull.Value))
{
currentStartDate = (DateTime)objOutputParameter.Value;
}
return dtInfo;
}
public static Job GetJobFromArchive(int jobID)
{
Job deserializedJob = null;
DataTable dtResults = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selArchivedJob";
objADO = Domain.GetADOInstance();
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
dtResults = objADO.GetDataTable(strStoredProcedure, "Job", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if(dtResults.Rows.Count > 0)
{
DataRow drSerializedJob = dtResults.Rows[0];
Type inheritedTypeToUse = null;
inheritedTypeToUse = Type.GetType(drSerializedJob["TypeName"].ToString());
if (!drSerializedJob["Serialized"].Equals(DBNull.Value))
{
System.Xml.XmlDocument objDOM = new System.Xml.XmlDocument();
objDOM.LoadXml(drSerializedJob["Serialized"].ToString());
deserializedJob = (Job)BusinessObjects.Base.Deserialize(inheritedTypeToUse, objDOM);
if (!drSerializedJob["DateReopened"].Equals(DBNull.Value))
{
deserializedJob.DateReopened = (DateTime)drSerializedJob["DateReopened"];
}
// NB: Set properties that are Instance specific to empty\default to avoid confusion
deserializedJob.Appointment = null;
}
}
return deserializedJob;
}
/// <summary>
/// Returns information about an archived Job not present in the Serialized content
/// </summary>
/// <param name="jobID"></param>
/// <param name="lastAssignee"></param>
/// <param name="lastUpdated"></param>
/// <returns></returns>
public static bool GetArchivedJobInformation(int jobID, out string lastAssignee, out DateTime lastUpdated)
{
DataTable dtResults = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selArchivedJobInformation";
lastAssignee = string.Empty;
lastUpdated = DateTime.MinValue;
objADO = Domain.GetADOInstance();
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
dtResults = objADO.GetDataTable(strStoredProcedure, "ArchivedInfo", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (dtResults != null && dtResults.Rows.Count > 0)
{
DataRow drArchivedInfo = dtResults.Rows[0];
lastAssignee = drArchivedInfo["UserID"].ToString();
lastUpdated = (DateTime)drArchivedInfo["LastUpdatedDate"];
}
// Return value indicates if Job is present in the Archive tables
return (dtResults != null && dtResults.Rows.Count > 0);
}