/// <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 bool DeleteRequestedMaterial(int jobID, string description)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "delMaterialsRequested";
colParameters.Add(new SqlParameter("@JobID", jobID));
if (!string.IsNullOrEmpty(description))
{
colParameters.Add(new SqlParameter("@MaterialDesc", description));
}
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;
}
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);
}
/// <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 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 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 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);
}
/// <summary>
/// Save, removing redundant items by cutoffDateForInclusion beforehand
/// </summary>
/// <param name="userID"></param>
/// <param name="cutoffDateForInclusion"></param>
/// <returns></returns>
public bool Save(string userID, DateTime cutoffDateForInclusion)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
SqlParameter objParameter = null;
string strStoredProcedure = "updUserNonAvailability";
if (cutoffDateForInclusion != DateTime.MinValue)
{
RemoveRedundantItems(cutoffDateForInclusion);
}
string strXML = BusinessObjects.Base.Serialize(this.GetType(), this);
colParameters.Add(new SqlParameter("@UserID", userID));
objParameter = new SqlParameter("@NonAvailabilitySerialized", SqlDbType.Xml);
objParameter.Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(strXML, System.Xml.XmlNodeType.Document, null));
if (this.Count == 0)
{
objParameter.Value = System.DBNull.Value;
}
colParameters.Add(objParameter);
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public bool Save(int jobID, eWMSourceSystem sourceSystem, DateTime dateRecorded, int jobInstanceNumber)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
SqlParameter objParameter = null;
string strStoredProcedure = "insJobTaskUpdate";
if (mdteDateUpdated == DateTime.MinValue)
{
mdteDateUpdated = dateRecorded;
}
string strXML = BusinessObjects.Base.Serialize(this.GetType(), this);
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
colParameters.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString()));
colParameters.Add(new SqlParameter("@TypeName", this.GetType().AssemblyQualifiedName));
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);
colParameters.Add(new SqlParameter("@IsComplete", true));
colParameters.Add(new SqlParameter("@ChangeDate", dateRecorded)); // NB: mdteDateUpdated not used as may have been set on the client
colParameters.Add(new SqlParameter("@UserID", mstrUserID));
if (mintID > 0)
{
colParameters.Add(new SqlParameter("@TaskID", mintID));
}
if (jobInstanceNumber > -1)
{
colParameters.Add(new SqlParameter("@JobInstanceNumber", jobInstanceNumber));
}
if (mintAssetID > 0)
{
colParameters.Add(new SqlParameter("@AssetID", mintAssetID));
}
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public bool Save(string xmlSerializedContent)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "insFailedJobAudit";
colParameters.Add(new SqlParameter("@WMSourceID", mintID));
colParameters.Add(new SqlParameter("@WMSourceSystem", mobjSourceSystem.ToString()));
colParameters.Add(new SqlParameter("@FailureType", mobjType.ToString()));
if (mdteDateRecorded == DateTime.MinValue)
{
mdteDateRecorded = DateTime.Now;
}
colParameters.Add(new SqlParameter("@FailureDate", mdteDateRecorded));
if (mstrExceptionMessage != null && mstrExceptionMessage != string.Empty)
{
colParameters.Add(new SqlParameter("@ExceptionMessage", mstrExceptionMessage));
}
colParameters.Add(new SqlParameter("@RetryCount", mintRetryCount));
if (!string.IsNullOrEmpty(xmlSerializedContent))
{
SqlParameter objParameter = new SqlParameter("@Serialized", SqlDbType.Xml);
objParameter.Value = new System.Data.SqlTypes.SqlXml(new System.Xml.XmlTextReader(xmlSerializedContent, System.Xml.XmlNodeType.Document, null));
colParameters.Add(objParameter);
}
if (!string.IsNullOrEmpty(mstrServiceWrapperType))
{
colParameters.Add(new SqlParameter("@ServiceWrapperType", mstrServiceWrapperType));
}
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
return (intReturn > 0);
}
public static bool UpdateMessageExpiryStatus(int messageID, bool isExpired, bool requiresClientConfirmation)
{
UserMessagingSection objSection = (UserMessagingSection)ConfigurationManager.GetSection("UserMessagingSection");
SQLServerDB databaseSettings = null;
DataAccess objADO = null;
ArrayList colParameters = null;
int intRowsAffected = 0;
if (objSection != null)
{
databaseSettings = objSection.DataStore;
}
if (databaseSettings == null)
{
throw new Exception("Unable to write User Message to SQL Server as Connection information was not specified");
}
try
{
// Create Data Access instance for communicating with SQL Server
objADO = new DataAccess(databaseSettings.ConnectionString);
// Write to the Log
colParameters = new ArrayList();
colParameters.Add(new SqlParameter("@UserMessageID", messageID));
colParameters.Add(new SqlParameter("@IsExpired", isExpired));
colParameters.Add(new SqlParameter("@RequiresClientConfirmation", requiresClientConfirmation));
intRowsAffected = objADO.ExecuteSQL("updUserMessageExpiryStatus", (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
}
catch (Exception excE)
{
throw new Exception("Unable to write User Message to SQL Server as Connection information was not specified", excE);
}
finally
{
objADO = null;
colParameters = null;
}
return (intRowsAffected > 0);
}
public static void WriteToDataStore(UserMessage userMessage)
{
UserMessagingSection objSection = (UserMessagingSection)ConfigurationManager.GetSection("UserMessagingSection");
SQLServerDB databaseSettings = null;
DataAccess objADO = null;
ArrayList colParameters = null;
string strMessage = userMessage.Body;
int intReturn = 0;
int intOutstandingMessageCharacters = 0;
if (objSection != null)
{
databaseSettings = objSection.DataStore;
}
if (databaseSettings == null)
{
throw new Exception("Unable to write User Message to SQL Server as Connection information was not specified");
}
try
{
// Create Data Access instance for communicating with SQL Server
objADO = new DataAccess(databaseSettings.ConnectionString);
// Write to the Log
intOutstandingMessageCharacters = userMessage.Body.Length;
while (intOutstandingMessageCharacters > 0)
{
// Send Message in multiple calls to accommodate limit on No. of Characters
if (userMessage.Body.Length > 7500)
{
strMessage = userMessage.Body.Substring(0, 7500);
intOutstandingMessageCharacters = (userMessage.Body.Length - 7500);
}
else
{
strMessage = userMessage.Body;
intOutstandingMessageCharacters = 0;
}
colParameters = new ArrayList();
colParameters.Add(new SqlParameter("@RecipientID", userMessage.Recipient));
colParameters.Add(new SqlParameter("@Body", userMessage.Body));
if (!string.IsNullOrEmpty(userMessage.Sender))
{
colParameters.Add(new SqlParameter("@ReplyAddress", userMessage.Sender));
}
if (!string.IsNullOrEmpty(userMessage.Subject))
{
colParameters.Add(new SqlParameter("@Subject", userMessage.Subject));
}
if (!string.IsNullOrEmpty(userMessage.TargetUrl))
{
colParameters.Add(new SqlParameter("@TargetUrl", userMessage.TargetUrl));
}
if (userMessage.ExpiryMinutesAfterCreation > -1)
{
colParameters.Add(new SqlParameter("@ExpiryMinutesAfterCreation", userMessage.ExpiryMinutesAfterCreation));
}
if (userMessage.ExpiryMinutesAfterSent > -1)
{
colParameters.Add(new SqlParameter("@ExpiryMinutesAfterCreation", userMessage.ExpiryMinutesAfterSent));
}
if (userMessage.PriorityLevel > 0)
{
colParameters.Add(new SqlParameter("@PriorityLevel", userMessage.PriorityLevel));
}
//workitem #36: Alert Escalation
if (userMessage.AlertID > 0)
{
colParameters.Add(new SqlParameter("@AlertID", userMessage.AlertID));
}
intReturn = objADO.ExecuteSQL(databaseSettings.StoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
if (intOutstandingMessageCharacters > 0)
{
string charactersNotWritten = userMessage.Body.Substring(7500, userMessage.Body.Length - 7500);
}
}
}
catch (Exception excE)
{
throw new Exception("Unable to write User Message to SQL Server as Connection information was not specified", excE);
}
finally
{
objADO = null;
colParameters = null;
}
}
/// <summary>
/// Archives the Job in TaskStore.tblJobArchive
/// </summary>
/// <returns></returns>
public static bool Remove(int jobID, eWMSourceSystem sourceSystem)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "insJobArchive";
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
colParameters.Add(new SqlParameter("@WMSourceSystem", sourceSystem.ToString()));
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
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 SaveSerializedObject(int jobID, int jobInstanceNumber, bool informEngineer, bool simpleUpdate,
bool deactivatingWithoutReplacing)
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
SqlParameter objParameter = null;
string strStoredProcedure = "updJobAppointment";
if (this.ID < 1)
{
this.Responsibility = this.LastUser;
}
string strXML = BusinessObjects.Base.Serialize(this.GetType(), this);
colParameters.Add(new SqlParameter("@WMSourceID", jobID));
colParameters.Add(new SqlParameter("@JobInstanceNumber", jobInstanceNumber));
colParameters.Add(new SqlParameter("@ChangeUser", this.LastUser));
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 (informEngineer)
{
colParameters.Add(new SqlParameter("@DateChangeDetected", DateTime.Now));
}
colParameters.Add(new SqlParameter("@DeactivatingWithoutReplacing", deactivatingWithoutReplacing));
colParameters.Add(new SqlParameter("@IsInternal", this.IsInternal));
colParameters.Add(new SqlParameter("@SimpleUpdate", simpleUpdate));
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
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 Save()
{
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
string strStoredProcedure = "insStatusConflict";
colParameters.Add(new SqlParameter("@WMSourceID", mintID));
colParameters.Add(new SqlParameter("@WMSourceSystem", mobjSourceSystem.ToString()));
colParameters.Add(new SqlParameter("@ProposedStatus", mobjProposedStatus.ToString()));
colParameters.Add(new SqlParameter("@CurrentStatus", mobjCurrentStatus.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 (mintInstanceNumber > -1)
{
colParameters.Add(new SqlParameter("@JobInstanceNumber", mintInstanceNumber));
}
if (mstrWindowsIdentity == null)
{
mstrWindowsIdentity = Environment.UserName;
}
colParameters.Add(new SqlParameter("@WindowsIdentity", mstrWindowsIdentity));
int intReturn = objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
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);
}
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 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 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 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);
}
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);
}
/// <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 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 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);
}
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 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 static EngineerNonAvailabilityCollection FindByUser(string userID, DateTime cutoffDateForInclusion, ref DateTime lastKnownUpdatedDate)
{
EngineerNonAvailabilityCollection nonAvailability = null;
DataAccess objADO = new DataAccess();
ArrayList colParameters = new ArrayList();
SqlParameter outputParameterSerialized;
SqlParameter outputParameterLastUpdated;
string strStoredProcedure = "selUserNonAvailability";
colParameters.Add(new SqlParameter("@UserID", userID));
if (lastKnownUpdatedDate != DateTime.MinValue)
{
colParameters.Add(new SqlParameter("@LastKnownUpdatedDate", lastKnownUpdatedDate));
}
outputParameterSerialized = new SqlParameter("@NonAvailabilitySerialized", SqlDbType.Xml, 1);
outputParameterSerialized.Direction = ParameterDirection.Output;
colParameters.Add(outputParameterSerialized);
outputParameterLastUpdated = new SqlParameter("@NonAvailabilityLastUpdatedDate", SqlDbType.DateTime);
outputParameterLastUpdated.Direction = ParameterDirection.Output;
colParameters.Add(outputParameterLastUpdated);
objADO.ExecuteSQL(strStoredProcedure, (SqlParameter[])colParameters.ToArray(typeof(SqlParameter)));
objADO = null;
if (!outputParameterSerialized.Value.Equals(DBNull.Value))
{
System.Xml.XmlDocument objDOM = new System.Xml.XmlDocument();
objDOM.LoadXml(outputParameterSerialized.Value.ToString());
nonAvailability = (EngineerNonAvailabilityCollection)BusinessObjects.Base.Deserialize(typeof(EngineerNonAvailabilityCollection), objDOM);
if (cutoffDateForInclusion != DateTime.MinValue)
{
nonAvailability.RemoveRedundantItems(cutoffDateForInclusion);
}
}
if (!outputParameterLastUpdated.Value.Equals(DBNull.Value))
{
lastKnownUpdatedDate = (DateTime)outputParameterLastUpdated.Value;
}
return nonAvailability;
}
