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