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