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