private void Load(eWMSourceSystem wmSourceSystem) { DataSet dsData = null; DataAccess objADO = new DataAccess(); ArrayList colParameters = new ArrayList(); string strStoredProcedure = "selJobsForMobile"; colParameters.Add(new SqlParameter("@WMSourceSystem", wmSourceSystem.ToString())); dsData = objADO.GetDataSet(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter))); objADO = null; if (dsData != null && dsData.Tables.Count > 0) { Populate(dsData); } }
public static DataTable JobUpdateDataTable(int jobID, eWMSourceSystem sourceSystem) { DataSet results = null; DataAccess dataAccess = new DataAccess(); System.Collections.ArrayList arrayList = new System.Collections.ArrayList(); System.Data.SqlClient.SqlParameter[] parameters = null; string storedProcedure = "selJobUpdatesSerialized"; dataAccess = Domain.GetADOInstance(); arrayList.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString())); arrayList.Add(new SqlParameter("@WMSourceID", jobID)); parameters = (SqlParameter[])arrayList.ToArray(typeof(SqlParameter)); results = dataAccess.GetDataSet(storedProcedure, parameters); if (results != null && results.Tables.Count > 0 && results.Tables[0] != null && results.Tables[0].Rows.Count > 0) { dataAccess = null; return results.Tables[0]; } dataAccess = null; return null; }
public void IncludeRelationallyStoredData(bool IncludeAssignmentConstraints) { DataSet dsData = null; DataAccess objADO = new DataAccess(); ArrayList colParameters = new ArrayList(); string strStoredProcedure = "selJobRelationalContent"; colParameters.Add(new SqlParameter("@WMSourceID", mintID)); dsData = objADO.GetDataSet(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter))); objADO = null; if (dsData != null && dsData.Tables.Count > 2) { dsData.Tables[0].TableName = "Comments"; dsData.Tables[1].TableName = "IncompleteHistory"; dsData.Tables[2].TableName = "OnHoldReasons"; if (dsData.Tables.Count > 3) { dsData.Tables[3].TableName = "MaterialsRequested"; } if (dsData.Tables.Count > 4) { dsData.Tables[4].TableName = "AssignmentConstraints"; } if (dsData.Tables.Count > 5) { dsData.Tables[5].TableName = "AssignmentSkills"; } mColComments = CommentAuditRecordCollection.Populate(dsData.Tables["Comments"]); mColIncompleteOutcomeHistory = JobIncompleteOutcomeCollection.Populate(dsData.Tables["IncompleteHistory"]); if (dsData.Tables.Count > 3) { mColOnHoldReasons = OnHoldReasonCollection.Populate(dsData.Tables["OnHoldReasons"], dsData.Tables["MaterialsRequested"]); if (dsData.Tables.Count > 4 && IncludeAssignmentConstraints) { //Load Assignment Constraints only ... ie. NO SKILLS if (dsData.Tables.Count == 5) { mobjAssignmentConstraints = AssignmentConstraints.PopulateAssignmentConstraints(dsData.Tables["AssignmentConstraints"], null); } else if(dsData.Tables.Count > 5) { //Load Assignment Constraints WITH SKILLS mobjAssignmentConstraints = AssignmentConstraints.PopulateAssignmentConstraints(dsData.Tables["AssignmentConstraints"], dsData.Tables["AssignmentSkills"]); } } } else { mColOnHoldReasons = OnHoldReasonCollection.Populate(dsData.Tables["OnHoldReasons"]); } } }
/// <summary> /// Return the instance that is effectively the latest, i.e. the latest instance that is not Cancelled or Incomplete (as this latter will have generated a new instance to /// replace it). /// </summary> /// <returns>The latest instance (or -1 if unsuccessful or null).</returns> public int GetEffectiveLatestInstance() { DataAccess objADO = new DataAccess(); SqlParameter[] colParameters = new SqlParameter[2]; string strStoredProcedure = "selLatestInstanceForJob"; colParameters[0] = new SqlParameter("@WMSourceID", mintID); colParameters[1] = new SqlParameter("@WMSourceSystem", mobjSourceSystem.ToString()); DataSet result = objADO.GetDataSet(strStoredProcedure, colParameters); objADO = null; if (result != null && result.Tables != null && result.Tables.Count > 0 && result.Tables[0].Rows.Count > 0) { if (result.Tables[0].Rows[0][0] == DBNull.Value) { return -1; } else { return (int)result.Tables[0].Rows[0][0]; } } else { return -1; } }
public static List<Job> GetJobsByReference(string[] sourceIDs, eWMSourceSystem sourceSystem, Type inheritedType, bool includeArchived) { List<Job> colJobs = new List<Job>(); Job deserializedJob = null; DataTable dtResults = null; DataAccess objADO = new DataAccess(); ArrayList colParameters = new ArrayList(); System.Data.SqlClient.SqlParameter[] arrParameters = null; DataAccess.ArrayListParameter objParameter = null; string strStoredProcedure = "selJobsByReference"; objADO = Domain.GetADOInstance(); objParameter = new DataAccess.ArrayListParameter("WMSourceIDs", sourceIDs); //If Source System is UnSpecified then it is not relevant .. so any will be returned if (sourceSystem != eWMSourceSystem.Unspecified) { colParameters.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString())); } if (includeArchived) { colParameters.Add(new SqlParameter("@IncludeArchived", includeArchived)); } arrParameters = (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)); dtResults = objADO.GetDataSet(strStoredProcedure, objParameter, arrParameters).Tables[0]; objADO = null; foreach (DataRow drSerializedJob in dtResults.Rows) { Type inheritedTypeToUse = null; if (inheritedType == null) { inheritedTypeToUse = Type.GetType(drSerializedJob["TypeName"].ToString()); } else { inheritedTypeToUse = inheritedType; } 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; colJobs.Add(deserializedJob); } } return colJobs; }
/// <summary> /// Find Jobs currently On Hold matching criteria paassed in /// </summary> /// <param name="parameters"></param> /// <returns></returns> public static System.Data.DataSet GetJobsOnHoldByCriteria(SearchParameterCollection parameters) { DataSet dsResults = null; DataAccess objADO = new DataAccess(); ArrayList colParameters = new ArrayList(); string strStoredProcedure = "selJobsOnHoldBySearchParameters"; objADO = Domain.GetADOInstance(); if (parameters != null) { foreach (SearchParameter parameter in parameters) { SqlParameter sqlParameter = new SqlParameter(); sqlParameter.ParameterName = "@" + parameter.Name; if (parameter.Value is string[]) { string arrayValues = string.Empty; foreach (string item in (parameter.Value as string[])) { if (arrayValues.Equals(string.Empty)) { arrayValues = item.Trim(); } else { arrayValues += "," + item.Trim(); } } sqlParameter.Value = arrayValues; } else { sqlParameter.Value = parameter.Value; } colParameters.Add(sqlParameter); } } dsResults = objADO.GetDataSet(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)), 600); // Slow query so set long commandTimeout objADO = null; dsResults.Tables[0].TableName = "JobsOnHold"; return dsResults; }
public static WorkerCollection GetTypedWorkers(BusinessObjects.WorkManagement.AreaDetails areaDetails, eMobileIdentityType identityType) { DataSet dsResults = null; FinalBuild.DataAccess objADO = new DataAccess(); System.Collections.ArrayList colParameters = new System.Collections.ArrayList(); string strStoredProcedure = "selWorkersMatchingCriteria"; // Assemble Parameters if(areaDetails.SubArea != null && !string.IsNullOrEmpty(areaDetails.SubArea.Name)) { colParameters.Add(new SqlParameter("@SubArea", areaDetails.SubArea.Name)); } colParameters.Add(new SqlParameter("@MobileIdentityType", identityType.ToString())); if(areaDetails.PrimaryArea != null && !string.IsNullOrEmpty(areaDetails.PrimaryArea.Name)) { colParameters.Add(new SqlParameter("@PrimaryArea", areaDetails.PrimaryArea.Name)); } dsResults = objADO.GetDataSet(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter))); return WorkerCollection.PopulateTyped(dsResults, string.Empty); }
public static DataTable GetTravelRelations(int[] jobIDs) { DataAccess objADO = new DataAccess(); System.Data.SqlClient.SqlParameter[] arrParameters; System.Collections.ArrayList colParameters = new System.Collections.ArrayList(); DataAccess.ArrayListParameter objParameter = null; string strStoredProcedure = "selTravelRelations"; DataSet dsResults = null; // Assemble Parameters objParameter = new DataAccess.ArrayListParameter("WMSourceIDs", jobIDs); arrParameters = (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)); dsResults = objADO.GetDataSet(strStoredProcedure, objParameter, arrParameters); objADO = null; return dsResults.Tables[0]; }
public static List<AssignmentConstraints> FindAssignmentConstraints(string userID, DateTime date, int dayRange) { DataAccess objADO = new DataAccess(); System.Data.SqlClient.SqlParameter[] arrParameters; System.Collections.ArrayList colParameters = new System.Collections.ArrayList(); string strStoredProcedure = "selAssignmentContraintsForAssignedJobs"; DataSet dsResults = null; // Assemble Parameters colParameters.Add(new SqlParameter("@UserID", userID)); colParameters.Add(new SqlParameter("@DayRange", dayRange)); if (date != DateTime.MinValue) { colParameters.Add(new SqlParameter("@Date", date)); } arrParameters = (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)); dsResults = objADO.GetDataSet(strStoredProcedure, arrParameters); objADO = null; return PopulateAssignmentConstraintsList(dsResults); }
public static List<AssignmentConstraints> FindAssignmentConstraints(string[] sourceIDs) { DataAccess objADO = new DataAccess(); System.Data.SqlClient.SqlParameter[] arrParameters; System.Collections.ArrayList colParameters = new System.Collections.ArrayList(); DataAccess.ArrayListParameter objParameter = null; string strStoredProcedure = "selAssignmentContraintsFromSourceIDs"; DataSet dsResults = null; // Assemble Parameters objParameter = new DataAccess.ArrayListParameter("WMSourceIDs", sourceIDs); colParameters.Add(new SqlParameter("@InstanceNumber", 0)); arrParameters = (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)); dsResults = objADO.GetDataSet(strStoredProcedure, objParameter, arrParameters); objADO = null; return PopulateAssignmentConstraintsList(dsResults); }
//public static bool SaveAssignmentConstraints(AssignmentConstraints assignmentConstraints) //{ // DataAccess objADO = new DataAccess(); // ArrayList colParameters = new ArrayList(); // DataAccess.ArrayListParameter arrayParameter = null; // SqlParameter returnParameter = null; // string strStoredProcedure = "insAssignmentContraints"; // int returnID = 0; // //SQL SP Paremeters as per below... // //======================================= // //@AssignmentConstraintID int = null, // //@WMSourceID int, // //@InstanceNumber int, // //@TimeWindow varchar(20)= null, // //@Duration int= null, // //@StartTime datetime= null, // //@EndTime datetime= null, // //@SpecificStartTime datetime= null, // //@MustBeCompletedByTime datetime= null // if (assignmentConstraints == null) // { // return false; // } // if (assignmentConstraints.ID > 0) // { // colParameters.Add(new SqlParameter("@AssignmentConstraintID", assignmentConstraints.ID)); // } // colParameters.Add(new SqlParameter("@WMSourceID", assignmentConstraints.WMSourceID)); // colParameters.Add(new SqlParameter("@InstanceNumber", assignmentConstraints.Instance)); // colParameters.Add(new SqlParameter("@TimeWindow", assignmentConstraints.TimeWindow.ToString())); // colParameters.Add(new SqlParameter("@Duration", assignmentConstraints.Duration)); // if (assignmentConstraints.StartTime != DateTime.MinValue) // { // colParameters.Add(new SqlParameter("@StartTime", assignmentConstraints.StartTime)); // } // if (assignmentConstraints.EndTime != DateTime.MinValue) // { // colParameters.Add(new SqlParameter("@EndTime", assignmentConstraints.EndTime)); // } // if (assignmentConstraints.SpecificStartTime != DateTime.MinValue) // { // colParameters.Add(new SqlParameter("@SpecificStartTime", assignmentConstraints.SpecificStartTime)); // } // if (assignmentConstraints.MustBeCompletedByTime != DateTime.MinValue) // { // colParameters.Add(new SqlParameter("@MustBeCompletedByTime", assignmentConstraints.MustBeCompletedByTime)); // } // colParameters.Add(new SqlParameter("@StartTime_IsUserSet", assignmentConstraints.IsUserSetStartDate)); // //Skills // if (assignmentConstraints.Skills != null && assignmentConstraints.Skills.Count > 0) // { // string[] skillCodes = new string[assignmentConstraints.Skills.Count]; // for (int intIndex = 0; intIndex < assignmentConstraints.Skills.Count; intIndex++) // { // skillCodes[intIndex] = assignmentConstraints.Skills[intIndex].Code; // } // arrayParameter = new DataAccess.ArrayListParameter("SkillCodes", skillCodes); // } // returnParameter = new SqlParameter("RETURN_VALUE", SqlDbType.Int); // returnParameter.Direction = ParameterDirection.ReturnValue; // colParameters.Add(returnParameter); // if (arrayParameter != null) // { // returnID = objADO.ExecuteSQL(strStoredProcedure, arrayParameter, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter))); // } // else // { // returnID = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter))); // } // objADO = null; // return (returnID > 0); //} public static AssignmentConstraints FindAssignmentConstraints(int WMSourceID, int InstanceNumber) { DataAccess objADO = new DataAccess(); ArrayList colParameters = new ArrayList(); string strStoredProcedure = "selAssignmentContraints"; DataSet dsResults = null; AssignmentConstraints returnAssignmentConstraints = null; colParameters.Add(new SqlParameter("@WMSourceID", WMSourceID)); colParameters.Add(new SqlParameter("@InstanceNumber", InstanceNumber)); dsResults = objADO.GetDataSet(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter))); objADO = null; if (dsResults != null) { returnAssignmentConstraints = PopulateAssignmentConstraints(dsResults); } return returnAssignmentConstraints; }
public static List<Location> GetGeoDataForJobs(string[] sourceIDs) { List<Location> colLocations = new List<Location>(); DataTable dtResults = null; DataAccess objADO = new DataAccess(); DataAccess.ArrayListParameter objParameter = null; string strStoredProcedure = "selGeoDataForJobs"; objADO = Domain.GetADOInstance(); objParameter = new DataAccess.ArrayListParameter("WMSourceIDs", sourceIDs); dtResults = objADO.GetDataSet(strStoredProcedure, objParameter, null).Tables[0]; objADO = null; Location location = null; foreach (DataRow locationRow in dtResults.Rows) { location = new Location(); location.ID = int.Parse(locationRow["WMSourceID"].ToString()); location.PostCode = locationRow["PostCode"].ToString(); if (!locationRow["Latitude"].Equals(DBNull.Value)) { location.Latitude = double.Parse(locationRow["Latitude"].ToString()); } if (!locationRow["Longitude"].Equals(DBNull.Value)) { location.Longitude = double.Parse(locationRow["Longitude"].ToString()); } if (!locationRow["Town"].Equals(DBNull.Value)) { location.Town = locationRow["Town"].ToString(); } if (!locationRow["Street"].Equals(DBNull.Value)) { location.Street = locationRow["Street"].ToString(); } if (!locationRow["HouseNo"].Equals(DBNull.Value)) { int parseResult = 0; if (int.TryParse(locationRow["HouseNo"].ToString(), out parseResult)) { location.HouseNo = parseResult; } } colLocations.Add(location); } return colLocations; }
public static System.Collections.Generic.SortedList<int, eJobStatus> GetStatusesOfJobs(int[] jobIDs) { System.Collections.Generic.SortedList<int, eJobStatus> jobStatusesList = new System.Collections.Generic.SortedList<int, eJobStatus>(); DataTable dtResults = null; DataAccess objADO = new DataAccess(); ArrayList colParameters = new ArrayList(); System.Data.SqlClient.SqlParameter[] arrParameters = null; DataAccess.ArrayListParameter objParameter = null; string strStoredProcedure = "selStatusesForWMSourceIDs"; objADO = Domain.GetADOInstance(); objParameter = new DataAccess.ArrayListParameter("WMSourceIDs", jobIDs); dtResults = objADO.GetDataSet(strStoredProcedure, objParameter, arrParameters).Tables[0]; objADO = null; if (dtResults.Rows.Count > 0) { foreach (DataRow drJobStatusInfo in dtResults.Rows) { jobStatusesList.Add(int.Parse(drJobStatusInfo["WMSourceID"].ToString()), (eJobStatus)Enum.Parse(typeof(eJobStatus),drJobStatusInfo["JobStatus"].ToString())); } } return jobStatusesList; }