public static FinalBuild.DataAccess GetADOInstance()
{
FinalBuild.DataAccess objADO = null;
string strAppSettingKey = "sqlConn";
if (ConfigurationManager.AppSettings[strAppSettingKey] != null)
{
objADO = new FinalBuild.DataAccess(ConfigurationManager.AppSettings[strAppSettingKey]);
}
return objADO;
}
public static FinalBuild.DataAccess GetADOInstance(eConnectionName connectionName)
{
FinalBuild.DataAccess objADO = null;
string strAppSettingKey = "sql" + connectionName.ToString();
if (ConfigurationManager.AppSettings[strAppSettingKey] != null)
{
objADO = new FinalBuild.DataAccess(ConfigurationManager.AppSettings[strAppSettingKey]);
}
return objADO;
}
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);
}
}
/// <summary>
/// This is used in creating the Reader Name for Alerts
/// </summary>
/// <returns></returns>
public static string GetEmployeeName(string employeeNo)
{
DataAccess objADO = new DataAccess();
SqlParameter[] arrParameters = new SqlParameter[2];
SqlParameter objOutputParameter = null;
string strEmployeeName = employeeNo;
arrParameters[0] = new SqlParameter("@EmpNo", employeeNo);
objOutputParameter = new SqlParameter("@EmployeeName", SqlDbType.VarChar, 50);
objOutputParameter.Direction = ParameterDirection.Output;
arrParameters[1] = objOutputParameter;
objADO.ExecuteSQL("selEmployeeName", arrParameters);
if (objOutputParameter.Value != null)
{
strEmployeeName = objOutputParameter.Value.ToString();
}
objADO = null;
return strEmployeeName;
}
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 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 bool Save(string wmSourceID, eWMSourceSystem sourceSystem, eFileAssociationType fileAssociationType)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "updJobFileAssociation";
if (fileAssociationType == eFileAssociationType.UserRelated)
{
strStoredProcedure = "updUserFileAssociation";
}
int intReturn = 0;
foreach (FileAssociation fileMetadata in this)
{
if (fileAssociationType != eFileAssociationType.UserRelated)
{
colParameters.Add(new SqlParameter("@WMSourceID", wmSourceID));
colParameters.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString()));
}
else
{
colParameters.Add(new SqlParameter("@UserID", wmSourceID));
}
colParameters.Add(new SqlParameter("@RawFileName", fileMetadata.Path.Substring(fileMetadata.Path.LastIndexOf(@"\") + 1)));
colParameters.Add(new SqlParameter("@FilePath", fileMetadata.Path));
colParameters.Add(new SqlParameter("@FileDesc", fileMetadata.Description));
colParameters.Add(new SqlParameter("@AssociationType", fileMetadata.AssociationType.ToString()));
colParameters.Add(new SqlParameter("@SizeInBytes", fileMetadata.SizeInBytes));
colParameters.Add(new SqlParameter("@FileHash", fileMetadata.Hash));
colParameters.Add(new SqlParameter("@IsEncrypted", fileMetadata.IsEncrypted));
intReturn += objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
colParameters.Clear();
}
objADO = null;
return (intReturn > 0);
}
public bool Save()
{
DataAccess objADO = null;
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
//@StartTime_IsUserSet bit = null
//@SkillCodes varchar(8000) = null
if (this.UpdateInsideTransaction && this.AssignmentConstraintsDataAccess != null)
{
objADO = this.AssignmentConstraintsDataAccess;
}
else
{
objADO = new DataAccess();
}
if (this == null)
{
return false;
}
if (this.ID > 0)
{
colParameters.Add(new SqlParameter("@AssignmentConstraintID", this.ID));
}
colParameters.Add(new SqlParameter("@WMSourceID", this.WMSourceID));
colParameters.Add(new SqlParameter("@InstanceNumber", this.Instance));
colParameters.Add(new SqlParameter("@TimeWindow", this.TimeWindow.ToString()));
colParameters.Add(new SqlParameter("@Duration", this.Duration));
if (this.StartTime != DateTime.MinValue)
{
colParameters.Add(new SqlParameter("@StartTime", this.StartTime));
}
if (this.EndTime != DateTime.MinValue)
{
colParameters.Add(new SqlParameter("@EndTime", this.EndTime));
}
if (this.SpecificStartTime != DateTime.MinValue)
{
colParameters.Add(new SqlParameter("@SpecificStartTime", this.SpecificStartTime));
}
if (this.MustBeCompletedByTime != DateTime.MinValue)
{
colParameters.Add(new SqlParameter("@MustBeCompletedByTime", this.MustBeCompletedByTime));
}
colParameters.Add(new SqlParameter("@StartTime_IsUserSet", this.IsUserSetStartDate));
//Skills
if (this.Skills != null && this.Skills.Count > 0)
{
string[] skillCodes = new string[this.Skills.Count];
for (int intIndex = 0; intIndex < this.Skills.Count; intIndex++)
{
skillCodes[intIndex] = this.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)));
}
if (!this.UpdateInsideTransaction)
{
objADO = null;
}
return (returnID > 0);
}
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>
/// Save Lost Times from Mobile
/// </summary>
/// <param name="historyID">Jop History ID</param>
private void SaveHistoryLostTimes(int historyID, int jobInstanceNumber, EngineerFeedback jobFeedback)
{
if (jobFeedback != null && jobFeedback.LostTime != null)
{
FinalBuild.DataAccess dataAccess = new DataAccess();
ArrayList colParameters = new ArrayList();
try
{
foreach (LostTimeUpdate lostTimeUpdate in jobFeedback.LostTime)
{
if (lostTimeUpdate.Reason != null) // If it is null then it is bad data
{
// Job History ID
colParameters.Add(new SqlParameter("@JobHistoryID", historyID));
// Lost Time ID
colParameters.Add(new SqlParameter("@LostTimeID", lostTimeUpdate.Reason.ID));
// Lost Time Description ... check for null value as not mandatory
string description = lostTimeUpdate.Comments != null ? lostTimeUpdate.Comments : "";
colParameters.Add(new SqlParameter("@Description", description));
// Lost Time Minutes
colParameters.Add(new SqlParameter("@Minutes", lostTimeUpdate.Minutes));
// Job Instance Number
colParameters.Add(new SqlParameter("@JobInstanceNumber", jobInstanceNumber));
// Update
dataAccess.ExecuteSQL("insJobLostTime", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
// Clear Parameters ready for next Lost Time
colParameters.Clear();
}
}
}
catch (Exception exception)
{
throw exception;
}
finally
{
dataAccess = null;
colParameters = null;
}
}
}
public bool ExistsInSchedulingSystem()
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
SqlParameter objParameter = null;
string strStoredProcedure = "selJobExistsInClick";
int intReturn = 0;
colParameters.Add(new SqlParameter("@WMSourceID", mintID));
colParameters.Add(new SqlParameter("@WMSourceSystem", mobjSourceSystem.ToString()));
objParameter = new SqlParameter("RETURN_VALUE", SqlDbType.Int);
objParameter.Direction = ParameterDirection.ReturnValue;
colParameters.Add(objParameter);
objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (!objParameter.Value.Equals(DBNull.Value))
{
intReturn = (int)objParameter.Value;
}
return (intReturn > 0);
}
private bool DeleteStockItems(int quantity)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "delStockByMaterialAndJob";
colParameters.Add(new SqlParameter("@MaterialID", this[0].Material.ID));
//Now pass in the Material's description .. in case there is no valid Material ID
colParameters.Add(new SqlParameter("@MaterialDesc", this[0].Material.Description));
colParameters.Add(new SqlParameter("@JobID", this[0].JobID));
//If no quantity given then ALL matching records will be deleted
if (quantity > 0)
{
colParameters.Add(new SqlParameter("@Quantity", quantity));
}
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public bool UpdateDatabaseIsCompleteFlag()
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "updJobIsCompleteFlag";
colParameters.Add(new SqlParameter("@WMSourceID", mintID));
colParameters.Add(new SqlParameter("@IsComplete", mblnIsComplete));
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
private bool UpdateStockItems(int jobID)
{
DataAccess objADO;
StringBuilder sb = new StringBuilder();
string stockIDs = string.Empty;
DateTime? dataRetrievedDate = (this[0] as StockItem).DataRetrievedDate;
foreach (StockItem item in this)
{
sb.Append(item.ID.ToString() + ",");
}
stockIDs = sb.ToString();
if(stockIDs.EndsWith(","))
{
stockIDs = stockIDs.Remove(stockIDs.Length - 1);
}
if (this.UpdateInsideTransaction && this.StockDataAccess != null)
{
objADO = this.StockDataAccess;
}
else
{
objADO = new DataAccess();
}
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "updStock";
colParameters.Add(new SqlParameter("@StockIDs", stockIDs));
if (jobID > 0)
{
colParameters.Add(new SqlParameter("@JobID", jobID));
}
if (dataRetrievedDate != null && dataRetrievedDate!= DateTime.MinValue)
{
colParameters.Add(new SqlParameter("@DataRetrieved", dataRetrievedDate));
}
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
if (!this.UpdateInsideTransaction)
{
objADO = null;
}
return (intReturn > 0);
}
private bool UpdateStockItems(int jobID, Material material, string locationCode, int quantity, bool unallocateJob)
{
DataAccess objADO;
StringBuilder sb = new StringBuilder();
string stockIDs = string.Empty;
StockItem objStockItem = this[0] as StockItem;
DateTime? dataRetrievedDate = objStockItem.DataRetrievedDate;
if (this.UpdateInsideTransaction && this.StockDataAccess != null)
{
objADO = this.StockDataAccess;
}
else
{
objADO = new DataAccess();
}
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "updStockByMaterialAndJob";
colParameters.Add(new SqlParameter("@JobID", jobID));
colParameters.Add(new SqlParameter("@MaterialID", material.ID));
//Now pass in the Material's description .. in case there is no valid Material ID
colParameters.Add(new SqlParameter("@MaterialDesc", material.Description));
colParameters.Add(new SqlParameter("@Quantity", quantity));
if (!string.IsNullOrEmpty(locationCode))
{
colParameters.Add(new SqlParameter("@StockLocationCode", locationCode));
}
if (unallocateJob)
{
colParameters.Add(new SqlParameter("@ClearJobID", 1));
}
if (dataRetrievedDate != null && dataRetrievedDate != DateTime.MinValue)
{
colParameters.Add(new SqlParameter("@DataRetrieved", dataRetrievedDate));
}
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
if (!this.UpdateInsideTransaction)
{
objADO = null;
}
return (intReturn > 0);
}
public bool RecordPickup(string userID)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "insMaterialsCollected";
colParameters.Add(new SqlParameter("@UserID", userID));
colParameters.Add(new SqlParameter("@Material", mstrCode));
colParameters.Add(new SqlParameter("@Quantity", mdecQuantity));
colParameters.Add(new SqlParameter("@DateAcquired", DateTime.Now));
// colParameters.Add(new SqlParameter("@Depot", depot));
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public bool RecordUse(int jobID, string userID)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "insMaterialsUsed";
colParameters.Add(new SqlParameter("@JobID", jobID));
colParameters.Add(new SqlParameter("@UserID", userID));
colParameters.Add(new SqlParameter("@Material", mstrDescription));
colParameters.Add(new SqlParameter("@Quantity", mdecQuantity));
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public bool Request(int jobID)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "updMaterialsRequested";
colParameters.Add(new SqlParameter("@JobID", jobID));
colParameters.Add(new SqlParameter("@MaterialDesc", mstrDescription));
colParameters.Add(new SqlParameter("@Quantity", mdecQuantity));
// colParameters.Add(new SqlParameter("@DateReceived", dateReceived));
// colParameters.Add(new SqlParameter("@UserID", resolvingUserID));
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public bool SaveSerializedObject(bool isNew)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
SqlParameter xmlParameter = null;
SqlParameter returnValue = null;
string serializedInstance = BusinessObjects.Base.Serialize(this.GetType(), this);
string strStoredProcedure = "insJobUpdateSerialized";
switch (isNew)
{
case false:
strStoredProcedure = "updJobUpdateSerialized";
colParameters.Add(new SqlParameter("@ID", this._archiveID));
xmlParameter = new SqlParameter("@SerializedObject", SqlDbType.Xml);
xmlParameter.Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(serializedInstance, System.Xml.XmlNodeType.Document, null));
colParameters.Add(xmlParameter);
break;
default:
returnValue = new SqlParameter("RETURN_VALUE", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
colParameters.Add(returnValue);
colParameters.Add(new SqlParameter("@WMSourceID", mintID));
colParameters.Add(new SqlParameter("@WMSourceSystem", mobjSourceSystem.ToString()));
xmlParameter = new SqlParameter("@SerializedObject", SqlDbType.Xml);
xmlParameter.Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(serializedInstance, System.Xml.XmlNodeType.Document, null));
colParameters.Add(xmlParameter);
colParameters.Add(new SqlParameter("@HashValue", this.GetHashValueForInstance()));
break;
}
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
if (returnValue != null && returnValue.Value != null)
{
this._archiveID = (int)returnValue.Value;
}
objADO = null;
return (intReturn > 0);
}
public bool Audit(int jobInstanceNumber)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "insJobStatusAudit";
colParameters.Add(new SqlParameter("@WMSourceID", mintID));
colParameters.Add(new SqlParameter("@WMSourceSystem", mobjSourceSystem.ToString()));
colParameters.Add(new SqlParameter("@Status", mobjStatus.ToString()));
if (mdteLastUpdated == DateTime.MinValue)
{
mdteLastUpdated = DateTime.Now;
}
colParameters.Add(new SqlParameter("@ChangeDate", mdteLastUpdated));
if (mstrUserID != null && mstrUserID != string.Empty)
{
colParameters.Add(new SqlParameter("@UserID", mstrUserID));
}
if (mstrService != null && mstrService != string.Empty)
{
colParameters.Add(new SqlParameter("@ServiceName", mstrService));
}
if (jobInstanceNumber > -1)
{
colParameters.Add(new SqlParameter("@JobInstanceNumber", jobInstanceNumber));
}
if (mstrWindowsIdentity == null)
{
mstrWindowsIdentity = Environment.UserName;
}
colParameters.Add(new SqlParameter("@WindowsIdentity", mstrWindowsIdentity));
if (mobjIncompleteReason != null && !string.IsNullOrEmpty(mobjIncompleteReason.Description))
{
colParameters.Add(new SqlParameter("@IncompleteReason", mobjIncompleteReason.Description));
}
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public bool DeleteSerializedObject()
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "delJobUpdateSerialized";
string strXML = BusinessObjects.Base.Serialize(this.GetType(), this);
colParameters.Add(new SqlParameter("@ID", ArchiveID));
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public int Save(int jobID)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "insJobOnHoldReason";
int intRowsAffected = 0;
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
colParameters.Add(new SqlParameter("@OnHoldReasonID", mintID));
colParameters.Add(new SqlParameter("@OnHoldReasonCode", mstrCode));
colParameters.Add(new SqlParameter("@OnHoldReasonDesc", mstrDescription));
if (mColComments != null && mColComments.Count > 0)
{
SqlParameter xmlParameter = new SqlParameter("@Comments", SqlDbType.Xml);
xmlParameter.Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(BusinessObjects.Base.Serialize(mColComments.GetType(), mColComments), System.Xml.XmlNodeType.Document, null));
colParameters.Add(xmlParameter);
}
intRowsAffected = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return intRowsAffected;
}
/// <summary>
/// Save Job History from Mobile
/// </summary>
/// <param name="userID">NT Login ID</param>
/// <returns>New Job History ID</returns>
private int SaveHistory(string userID, int jobInstanceNumber, int mileage)
{
DataAccess dataAccess = new DataAccess();
ArrayList colParameters = new ArrayList();
SqlParameter returnParameter = null;
int historyID = -1;
try
{
// Return Value Parameter...
returnParameter = new SqlParameter("RETURN_VALUE", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
colParameters.Add(returnParameter);
// Job ID
colParameters.Add(new SqlParameter("@WMSourceID", this.ID));
// Source System
colParameters.Add(new SqlParameter("@WMSourceSystem", this.SourceSystem.ToString()));
// Start Date Time
colParameters.Add(new SqlParameter("@StartDateTime", this.StartDateTime));
// Stop Date Time
colParameters.Add(new SqlParameter("@StopDateTime", this.EndDateTime.Value));
// Miles
colParameters.Add(new SqlParameter("@Miles", mileage));
// Job Instance Number
colParameters.Add(new SqlParameter("@JobInstanceNumber", jobInstanceNumber));
// Last Updating User
colParameters.Add(new SqlParameter("@LstUpdUsr", userID));
// Update
dataAccess.ExecuteSQL("insJobHistory", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
// Return Value
historyID = (int)returnParameter.Value;
}
catch (Exception exception)
{
throw exception;
}
finally
{
dataAccess = null;
colParameters = null;
}
// Return
return historyID;
}
public int Save(int jobID, eWMSourceSystem sourceSystem, bool informEngineers)
{
if (string.IsNullOrEmpty(mstrText))
{
return 0;
}
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "insJobComment";
int intRowsAffected = 0;
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
colParameters.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString()));
colParameters.Add(new SqlParameter("@ChangeDate", mdteChangeDate));
colParameters.Add(new SqlParameter("@ChangeUser", mstrChangeUser));
colParameters.Add(new SqlParameter("@IsEngineerComment", mblnIsEngineerComment));
//CR : 2396 , IsCommentsCritical is used for filtering whether comments are critical / normal
//Changed as per RAB's review comments to IsCritical
colParameters.Add(new SqlParameter("@IsCritical", mblnIsCritical));
colParameters.Add(new SqlParameter("@CommentType", mobjType.ToString()));
colParameters.Add(new SqlParameter("@CommentText", mstrText));
if (informEngineers)
{
colParameters.Add(new SqlParameter("@InformEngineers", true));
}
if (!string.IsNullOrEmpty(mstrSortExpression))
{
colParameters.Add(new SqlParameter("@SortExpression", mstrSortExpression));
}
intRowsAffected = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return intRowsAffected;
}
public bool Update()
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "updStock";
DateTime? dataRetrievedDate = this.DataRetrievedDate;
colParameters.Add(new SqlParameter("@StockID", this.ID));
if (this.JobID > 0)
{
colParameters.Add(new SqlParameter("@JobID", this.JobID));
}
if (dataRetrievedDate != null && dataRetrievedDate != DateTime.MinValue)
{
colParameters.Add(new SqlParameter("@DataRetrieved", dataRetrievedDate));
}
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public bool RemoveMember(Engineer gangMember)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "delGangMember";
int intRowsAffected = 0;
colParameters.Add(new SqlParameter("@GangID", this.UserID));
colParameters.Add(new SqlParameter("@MemberID", gangMember.UserID));
intRowsAffected = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intRowsAffected > 0);
}
public bool Resolve(int jobID)
{
DataAccess objADO = new DataAccess(); // TaskStore
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "updJobOnHoldReason";
int intReturn = 0;
if (mdteDateResolved.Equals(DateTime.MinValue))
{
mdteDateResolved = DateTime.Now;
}
if (mstrResolvingUser == null || mstrResolvingUser == string.Empty)
{
mstrResolvingUser = "******";
}
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
colParameters.Add(new SqlParameter("@OnHoldReasonID", mintID));
colParameters.Add(new SqlParameter("@IsResolved", true));
colParameters.Add(new SqlParameter("@DateResolved", mdteDateResolved));
colParameters.Add(new SqlParameter("@ResolvingUser", mstrResolvingUser));
if (mColComments != null && mColComments.Count > 0)
{
SqlParameter xmlParameter = new SqlParameter("@Comments", SqlDbType.Xml);
xmlParameter.Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(BusinessObjects.Base.Serialize(mColComments.GetType(), mColComments), System.Xml.XmlNodeType.Document, null));
colParameters.Add(xmlParameter);
}
intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
/// <summary>
/// WorkItem No : 2404 , R3_AGA_1.5.5.1
/// Returns the User whether assigned Jobs IsFirst or IsLastInstance
/// </summary>
/// <param name="UserId"></param>
public void SetJobInstance(string UserId,out bool IsJobFirstInstance,out bool IsJobLastInstance)
{
IsJobFirstInstance = IsJobLastInstance = false;
SqlDataReader drData = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "selJobFirstandLastInstance";
colParameters.Add(new SqlParameter("@WMSourceID", mintID.ToString()));
colParameters.Add(new SqlParameter("@UserID", UserId));
drData = objADO.GetDataReader(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (drData != null)
{
if (drData.Read())
{
IsJobFirstInstance = Convert.ToBoolean(drData.GetValue(0));
IsJobLastInstance = Convert.ToBoolean(drData.GetValue(1));
}
}
}
public bool SaveSerializedObject(bool isNew, bool informEngineer, string clickCallID, int jobInstanceNumber)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
SqlParameter objParameter = null;
SqlParameter returnValueParameter = null;
string strStoredProcedure = "updJobSerialized";
string strXML = BusinessObjects.Base.Serialize(this.GetType(), this);
colParameters.Add(new SqlParameter("@WMSourceID", mintID));
colParameters.Add(new SqlParameter("@WMSourceSystem", mobjSourceSystem.ToString()));
objParameter = new SqlParameter("@SerializedObject", SqlDbType.Xml);
objParameter.Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(strXML, System.Xml.XmlNodeType.Document, null));
colParameters.Add(objParameter);
if (isNew)
{
strStoredProcedure = "insJobSerialized";
colParameters.Add(new SqlParameter("@DueDate", mdteDueDate));
colParameters.Add(new SqlParameter("@TypeName", this.GetType().AssemblyQualifiedName));
if (clickCallID != null && clickCallID != string.Empty)
{
colParameters.Add(new SqlParameter("@ClickCallID", clickCallID));
}
returnValueParameter = new SqlParameter("RETURN_VALUE", SqlDbType.Int);
returnValueParameter.Direction = ParameterDirection.ReturnValue;
colParameters.Add(returnValueParameter);
}
else
{
if (this.IsComplete)
{
colParameters.Add(new SqlParameter("@IsComplete", true));
}
if (informEngineer)
{
colParameters.Add(new SqlParameter("@DateChangeDetected", DateTime.Now));
}
}
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (returnValueParameter != null && returnValueParameter.Value != DBNull.Value)
{
// Set ID if Job is newly created & ID is required
if (mintID < 1)
{
mintID = (int)returnValueParameter.Value;
}
}
return (intReturn > 0);
}
public bool Delete()
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "delStock";
colParameters.Add(new SqlParameter("@StockID", this.ID));
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
