/// <summary>
/// Insert a <see cref="Job"/> passed as an argument via Stored Procedure that returns the newly inserted Job Key
/// </summary>
/// <param name="aJob">A <see cref="Job"/>.</param>
/// <exception cref="ArgumentNullException">If <c>aJob</c> argument is <c>null</c>.</exception>
public static void Insert(Job aJob)
{
if (aJob == null)
{
throw new ArgumentNullException("aJob");
}
using (var vSqlCommand = new SqlCommand()
{
CommandType = CommandType.Text,
Connection = new SqlConnection(Connection.Instance.SqlConnectionString)
})
{
var vStringBuilder = new StringBuilder();
vStringBuilder.AppendLine("insert into JOB_Job");
vStringBuilder.AppendLine(" (CLN_Key, JOB_Name, DOC_Key, LAN_KeySource, LAN_KeyTarget,)");
vStringBuilder.AppendLine(" (JOB_DateCreated, JOB_DateStarted, JOB_DateFinished, JOB_DateDesCompletion, JOB_DateEstCompletion,)");
vStringBuilder.AppendLine(" (JOB_DateDesCompletion, JOB_DateActCompletion)");
vStringBuilder.AppendLine("values");
vStringBuilder.AppendLine(" (@CLNKey, @JOBName, @DOCKey, @JOBDateCreated, @JOBDateStarted, @JOBDateFinished,)");
vStringBuilder.AppendLine(" (@JOBDateEdited, @JOBDateDesCompletion, @JOBDateEstCompletion, @JOBDateActCompletion)");
vStringBuilder.AppendLine(";");
vStringBuilder.AppendLine("select SCOPE_IDENTITY()");
ObjectToData(vSqlCommand, aJob);
vSqlCommand.CommandText = vStringBuilder.ToString();
vSqlCommand.Connection.Open();
aJob.JobbKey = Convert.ToInt32(vSqlCommand.ExecuteScalar());
vSqlCommand.Connection.Close();
}
}
/// <summary>
/// The overloaded Load method that will return a specific <see cref="Job"/> object, with keys in <c>aJob</c>.
/// </summary>
/// <param name="aUserKey">A <see cref="UserKey"/> object.</param>
/// <param name="aJob">A <see cref="Job"/>.</param>
/// <exception cref="ArgumentNullException">If <c>aJob</c> is <c>null</c>.</exception>
public static void Load(UserKey aUserKey, Job aJob)
{
if (aJob == null)
{
throw new ArgumentNullException("Load Job Business");
}
if (!UserFunctionAccessData.HasModeAccess(aUserKey, "Job", AccessMode.Read))
{
throw new ZpAccessException("Access Denied", String.Format("{0}", aUserKey.UsrKey), AccessMode.Read, "Job");
}
JobData.Load(aJob);
}
/// <summary>
/// Assigns all <c>aSource</c> object's values to this instance of <see cref="ProviderCollection"/>.
/// </summary>
/// <param name="aSource">A source object.</param>
public override void AssignFromSource(object aSource)
{
if (!(aSource is JobCollection))
{
throw new ArgumentException("Invalid assignment source", "JobCollection");
}
_jobFilter.AssignFromSource((aSource as JobCollection)._jobFilter);
_jobList.Clear();
(aSource as JobCollection)._jobList.ForEach(vJobSource =>
{
Job vJobTarget = new Job();
vJobTarget.AssignFromSource(vJobSource);
_jobList.Add(vJobTarget);
});
}
/// <summary>
/// Load a <see cref="SqlDataReader"/> into a <see cref="Job"/> object.
/// </summary>
/// <param name="aJob">A <see cref="Job"/> argument.</param>
/// <param name="aSqlDataReader">A <see cref="SqlDataReader"/> argument.</param>
public static void DataToObject(Job aJob, SqlDataReader aSqlDataReader)
{
aJob.ClnKey = Convert.ToInt32(aSqlDataReader["CLN_Key"]);
aJob.ClnName = Convert.ToString(aSqlDataReader["CLN_Name"]);
aJob.JobbKey = Convert.ToInt32(aSqlDataReader["JOB_Key"]);
aJob.JobName = Convert.ToString(aSqlDataReader["JOB_Name"]);
aJob.DocKey = Convert.ToInt32(aSqlDataReader["DOC_Key"]);
aJob.DocName = Convert.ToString(aSqlDataReader["DOC_Name"]);
aJob.LanSourceKey = Convert.ToInt32(aSqlDataReader["SOURCEKEY"]);
aJob.LanTargetKey = Convert.ToInt32(aSqlDataReader["TARGETKEY"]);
aJob.LanguageSourceNames = string.Format("{0} ({1})", Convert.ToString(aSqlDataReader["SOURCEENGLISHNAME"]), Convert.ToString(aSqlDataReader["SOURCENAME"]));
aJob.LanguageTargetNames = string.Format("{0} ({1})", Convert.ToString(aSqlDataReader["TARGETENGLISHNAME"]), Convert.ToString(aSqlDataReader["TARGETNAME"]));
aJob.DocWordcount = CommonUtils.DbValueTo<int>(aSqlDataReader["DOC_SourceWordCount"], 0);
aJob.DateCreated = Convert.ToDateTime(aSqlDataReader["JOB_DateCreated"]);
aJob.DateStarted = CommonUtils.DbValueTo<DateTime?>(aSqlDataReader["JOB_DateStarted"], null);
aJob.DateFinished = CommonUtils.DbValueTo<DateTime?>(aSqlDataReader["JOB_DateFinished"], null);
aJob.DateEdited = CommonUtils.DbValueTo<DateTime?>(aSqlDataReader["JOB_DateEdited"], null);
aJob.DateDesCompletion = CommonUtils.DbValueTo<DateTime?>(aSqlDataReader["JOB_DateDesCompletion"], null);
aJob.DateEstCompletion = CommonUtils.DbValueTo<DateTime?>(aSqlDataReader["JOB_DateEstCompletion"], null);
aJob.DateActCompletion = CommonUtils.DbValueTo<DateTime?>(aSqlDataReader["JOB_DateActCompletion"], null);
}
/// <summary>
/// Delete a <see cref="Job"/> object passed as an argument.
/// </summary>
/// <param name="aJob">The <see cref="Job"/> object to be deleted.</param>
/// <exception cref="ArgumentNullException">If <c>aJob</c> argument is <c>null</c>.</exception>
public static void Delete(Job aJob)
{
if (aJob == null)
{
throw new ArgumentNullException("aJob");
}
using (var vSqlCommand = new SqlCommand()
{
CommandType = CommandType.Text,
Connection = new SqlConnection(Connection.Instance.SqlConnectionString)
})
{
var vStringBuilder = new StringBuilder();
vStringBuilder.AppendLine("delete JOB_Job");
vStringBuilder.AppendLine("where CLN_Key = @CLNKey");
vSqlCommand.Parameters.AddWithValue("@CLNKey", aJob.ClnKey);
vStringBuilder.AppendLine("and JOB_Key = @JOBKey");
vSqlCommand.Parameters.AddWithValue("@JOBKey", aJob.JobbKey);
vSqlCommand.CommandText = vStringBuilder.ToString();
vSqlCommand.Connection.Open();
vSqlCommand.ExecuteNonQuery();
vSqlCommand.Connection.Close();
}
}
/// <summary>
/// Add a <see cref="Job"/>.
/// </summary>
/// <param name="aUserToken">A <see cref="UserToken"/> object used for Access Control.</param>
/// <param name="aJob"><see cref="Job"/> object.</param>
public static void AddJob(UserToken aUserToken, Job aJob)
{
UserCallHandler.ServiceCall<Job>(aUserToken, "AddJob", aJob);
}
/// <summary>
/// Edit a specified <see cref="Job"/> including updating its Status Date.
/// </summary>
/// <param name="aUserToken">A <see cref="UserToken"/> object used for Access Control.</param>
/// <param name="aJob"><see cref="Job"/> object.</param>
public static void EditJobStatusDate(UserToken aUserToken, Job aJob)
{
UserCallHandler.ServiceCall<Job>(aUserToken, "EditJobStatusDate", aJob);
}
/// <summary>
/// Gets the <see cref="Job"/> by Key.
/// </summary>
/// <param name="aXmlArgument">XML Argument <see cref="string"/>.</param>
/// <returns>Job as XML <see cref="string"/>.</returns>
/// <exception cref="ArgumentNullException">If <c>aXmlArgument</c> is <c>null</c>.</exception>
public static string GetJob(UserKey aUserKey, string aXmlArgument)
{
if (aXmlArgument == null)
{
throw new ArgumentNullException("aXmlArgument of GetJob");
}
Job vJob = new Job();
vJob = XmlUtils.Deserialize<Job>(aXmlArgument);
JobBusiness.Load(aUserKey, vJob);
return XmlUtils.Serialize<Job>(vJob, true);
}
/// <summary>
/// Update a <see cref="Job"/> passed as an argument .
/// </summary>
/// <param name="aJob">A <see cref="Job"/>.</param>
public static void Update(Job aJob)
{
if (aJob == null)
{
throw new ArgumentNullException("aJob");
}
using (var vSqlCommand = new SqlCommand()
{
CommandType = CommandType.Text,
Connection = new SqlConnection(Connection.Instance.SqlConnectionString)
})
{
var vStringBuilder = new StringBuilder();
vStringBuilder.AppendLine("update JOB_Job");
vStringBuilder.AppendLine("set JOB_Name = @JOBName,");
vStringBuilder.AppendLine(" DOC_Key = @DOCKey,");
vStringBuilder.AppendLine(" LAN_KeySource = @LANKeySource,");
vStringBuilder.AppendLine(" LAN_KeyTarget = @LANKeyTarget,");
vStringBuilder.AppendLine(" JOB_DateCreated = @JOBDateCreated");
vStringBuilder.AppendLine(" JOB_DateStarted = @JOBDateStarted,");
vStringBuilder.AppendLine(" JOB_DateFinished = @JOBDateFinished,");
vStringBuilder.AppendLine(" JOB_DateEdited = @JOBDateEdited,");
vStringBuilder.AppendLine(" JOB_DateEstCompletion = @JOBDateEstCompletion,");
vStringBuilder.AppendLine(" JOB_DateDesCompletion = @JOBDateDesCompletion,");
vStringBuilder.AppendLine(" JOB_DateActCompletion = @JOBDateActCompletion");
vStringBuilder.AppendLine("where CLN_Key = @CLNKey");
vStringBuilder.AppendLine("and JOB_Key = @JOBKey");
ObjectToData(vSqlCommand, aJob);
vSqlCommand.Parameters.AddWithValue("@JOBKey", aJob.JobbKey);
vSqlCommand.CommandText = vStringBuilder.ToString();
vSqlCommand.Connection.Open();
vSqlCommand.ExecuteNonQuery();
vSqlCommand.Connection.Close();
}
}
/// <summary>
/// Loads the <see cref="SqlCommand"/> parameters with values from an <see cref="Job"/>.
/// </summary>
/// <param name="aSqlCommand">A <see cref="SqlDataReader"/> argument.</param>
/// <param name="aJob">A <see cref="Job"/> argument.</param>
public static void ObjectToData(SqlCommand aSqlCommand, Job aJob)
{
aSqlCommand.Parameters.AddWithValue("@CLNKey", aJob.ClnKey);
aSqlCommand.Parameters.AddWithValue("@JOBName", aJob.JobName);
aSqlCommand.Parameters.AddWithValue("@DOCKey", aJob.DocKey);
aSqlCommand.Parameters.AddWithValue("@LANKeySource", aJob.LanSourceKey);
aSqlCommand.Parameters.AddWithValue("@LANKeyTarget", aJob.LanTargetKey);
//DateCreated
if (aJob.DateCreated == null)
{
aSqlCommand.Parameters.Add("@JOBDateCreated", SqlDbType.Image).Value = DBNull.Value;
}
else
{
aSqlCommand.Parameters.AddWithValue("@JOBDateCreated", aJob.DateCreated);
}
//DateStarted
if (aJob.DateStarted == null)
{
aSqlCommand.Parameters.Add("@JOBDateStarted", SqlDbType.Image).Value = DBNull.Value;
}
else
{
aSqlCommand.Parameters.AddWithValue("@JOBDateStarted", aJob.DateStarted);
}
//DateFinished
if (aJob.DateFinished == null)
{
aSqlCommand.Parameters.Add("@JOBDateFinished", SqlDbType.Image).Value = DBNull.Value;
}
else
{
aSqlCommand.Parameters.AddWithValue("@JOBDateFinished", aJob.DateFinished);
}
//DateEdited
if (aJob.DateEdited == null)
{
aSqlCommand.Parameters.Add("@JOBDateEdited", SqlDbType.Image).Value = DBNull.Value;
}
else
{
aSqlCommand.Parameters.AddWithValue("@JOBDateEdited", aJob.DateEdited);
}
//DateDesCompletion
if (aJob.DateDesCompletion == null)
{
aSqlCommand.Parameters.Add("@JOBDateDesCompletion", SqlDbType.Image).Value = DBNull.Value;
}
else
{
aSqlCommand.Parameters.AddWithValue("@JOBDateDesCompletion", aJob.DateDesCompletion);
}
//DateEstCompletion
if (aJob.DateEstCompletion == null)
{
aSqlCommand.Parameters.Add("@JOBDateEstCompletion", SqlDbType.Image).Value = DBNull.Value;
}
else
{
aSqlCommand.Parameters.AddWithValue("@JOBDateEstCompletion", aJob.DateEstCompletion);
}
//DateActCompletion
if (aJob.DateActCompletion == null)
{
aSqlCommand.Parameters.Add("@JOBDateActCompletion", SqlDbType.Image).Value = DBNull.Value;
}
else
{
aSqlCommand.Parameters.AddWithValue("@JOBDateActCompletion", aJob.DateActCompletion);
}
}
/// <summary>
/// The overloaded Load method that will return a specific <see cref="Job"/>, with keys in the <c>aJob</c> argument.
/// </summary>
/// <param name="aJob">A <see cref="Job"/>.</param>
/// <exception cref="ArgumentNullException">If <c>aJob</c> argument is <c>null</c>.</exception>
/// <exception cref="Exception">If no record is found.</exception>
public static void Load(Job aJob)
{
if (aJob == null)
{
throw new ArgumentNullException("aJob");
}
using (var vSqlCommand = new SqlCommand()
{
CommandType = CommandType.Text,
Connection = new SqlConnection(Connection.Instance.SqlConnectionString)
})
{
var vStringBuilder = BuildSQL();
vStringBuilder.AppendLine("and t1.CLN_Key = @CLNKey");
vStringBuilder.AppendLine("and t2.JOB_Key = @JOBKey");
vSqlCommand.Parameters.AddWithValue("@CLNKey", aJob.ClnKey);
vSqlCommand.Parameters.AddWithValue("@JOBKey", aJob.JobbKey);
vSqlCommand.CommandText = vStringBuilder.ToString();
vSqlCommand.Connection.Open();
using (SqlDataReader vSqlDataReader = vSqlCommand.ExecuteReader())
{
if (!vSqlDataReader.HasRows)
{
throw new Exception(String.Format("Expected Job not found: CLN_Key = {0}, JOB_Key = {1}", aJob.ClnKey, aJob.JobbKey));
}
vSqlDataReader.Read();
DataToObject(aJob, vSqlDataReader);
vSqlDataReader.Close();
}
vSqlCommand.Connection.Close();
}
}
/// <summary>
/// The overloaded Load method that will fill the <c>JobList</c> property a <see cref="JobCollection"/> object as an
/// ordered <c>List</c> of <see cref="Job"/>, filtered by the filter properties of the passed <see cref="JobCollection"/>.
/// </summary>
/// <param name="aJobCollection">The <see cref="JobCollection"/> object that must be filled.</param>
/// <remarks>
/// The filter properties of the <see cref="JobCollection"/> must be correctly completed by the calling application.
/// </remarks>
/// <exception cref="ArgumentNullException">If <c>aJobCollection</c> argument is <c>null</c>.</exception>
public static void Load(JobCollection aJobCollection)
{
if (aJobCollection == null)
{
throw new ArgumentNullException("aJobCollection");
}
using (var vSqlCommand = new SqlCommand()
{
CommandType = CommandType.Text,
Connection = new SqlConnection(Connection.Instance.SqlConnectionString)
})
{
var vStringBuilder = BuildSQL();
if (aJobCollection.JobFilter.IsFiltered)
{
if (aJobCollection.JobFilter.ClientKeyFilter > 0)
{
vStringBuilder.AppendLine("and t1.CLN_Key = @CLNKey");
vSqlCommand.Parameters.AddWithValue("@CLNKey", aJobCollection.JobFilter.ClientKeyFilter);
}
}
vStringBuilder.AppendLine("order by t2.JOB_Name");
vSqlCommand.CommandText = vStringBuilder.ToString();
vSqlCommand.Connection.Open();
using (SqlDataReader vSqlDataReader = vSqlCommand.ExecuteReader())
{
while (vSqlDataReader.Read())
{
var vJob = new Job();
DataToObject(vJob, vSqlDataReader);
aJobCollection.JobList.Add(vJob);
}
vSqlDataReader.Close();
}
vSqlCommand.Connection.Close();
}
}