public static IEnumerable <EmployeeJob> GetJobs(int employeeId)
{
List <int> jobIds = new List <int>();
SqlConnection cn = GetConnection();
using (SqlCommand cmd = new SqlCommand("SELECT EmployeePayRateEmployeeJobId FROM EmployeePayRate INNER JOIN EmployeeJob ON EmployeePayRateEmployeeJobId = EmployeeJobId WHERE EmployeePayRateEmployeeId=@EmployeePayRateEmployeeId", cn))
{
BuildSqlParameter(cmd, "@EmployeePayRateEmployeeId", SqlDbType.Int, employeeId);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
try
{
jobIds.Add(Convert.ToInt32(rdr[0]));
}
catch
{
}
}
}
foreach (int jobId in jobIds)
{
yield return(EmployeeJob.Get(jobId));
}
}
FinishedWithConnection(cn);
}
/// <summary>
/// Update an entry in the EmployeeJob table
/// </summary>
public static bool Update(EmployeeJob employeeJob)
{
bool result = false;
SqlConnection cn = GetConnection();
result = Update(cn, employeeJob);
FinishedWithConnection(cn);
return(result);
}
public static IEnumerable <int> GetDispatchableJobIds()
{
foreach (EmployeeJob job in EmployeeJob.GetAll())
{
if (job.AllowedDispatch)
{
yield return(job.Id);
}
}
}
/// <summary>
/// Get an entry from the EmployeeJob table
/// </summary>
public static EmployeeJob Get(int id)
{
EmployeeJob result = null;
SqlConnection cn = GetConnection();
result = Get(cn, id);
FinishedWithConnection(cn);
return(result);
}
private static EmployeeJob Get(SqlConnection cn, int id)
{
EmployeeJob result = null;
using (SqlCommand cmd = new SqlCommand("SELECT * FROM EmployeeJob WHERE EmployeeJobId=" + id, cn))
{
using (SqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
result = BuildEmployeeJob(rdr);
}
}
}
return(result);
}
/// <summary>
/// Delete an entry from the EmployeeJob table
/// </summary>
public static bool Delete(int id)
{
Int32 rowsAffected = 0;
SqlConnection cn = GetConnection();
EmployeeJob employeeJob = Get(cn, id);
if (employeeJob != null)
{
using (SqlCommand sqlCmd = cn.CreateCommand())
{
sqlCmd.CommandText = "DELETE FROM EmployeeJob WHERE EmployeeJobId=" + id;
rowsAffected = sqlCmd.ExecuteNonQuery();
}
}
FinishedWithConnection(cn);
return(rowsAffected != 0);
}
private static bool Update(SqlConnection cn, EmployeeJob employeeJob)
{
Int32 rowsAffected = 0;
using (SqlCommand sqlCmd = cn.CreateCommand())
{
sqlCmd.CommandText = "UPDATE EmployeeJob SET EmployeeJobDescription=@EmployeeJobDescription,EmployeeJobHasTips=@EmployeeJobHasTips,EmployeeJobAllowDispatch=@EmployeeJobAllowDispatch,EmployeeJobIsEnabled=@EmployeeJobIsEnabled WHERE EmployeeJobId=@EmployeeJobId";
BuildSqlParameter(sqlCmd, "@EmployeeJobId", SqlDbType.Int, employeeJob.Id);
BuildSqlParameter(sqlCmd, "@EmployeeJobDescription", SqlDbType.Text, employeeJob.Description);
BuildSqlParameter(sqlCmd, "@EmployeeJobHasTips", SqlDbType.Bit, employeeJob.HasTips);
BuildSqlParameter(sqlCmd, "@EmployeeJobAllowDispatch", SqlDbType.Bit, employeeJob.AllowedDispatch);
BuildSqlParameter(sqlCmd, "@EmployeeJobIsEnabled", SqlDbType.Bit, employeeJob.IsEnabled);
rowsAffected = sqlCmd.ExecuteNonQuery();
}
return(rowsAffected != 0);
}
/// <summary>
/// Add a new entry to the EmployeeJob table
/// </summary>
public static EmployeeJob Add(string description, bool hasTips, bool allowedDispatch)
{
EmployeeJob result = null;
SqlConnection cn = GetConnection();
string cmd = "AddEmployeeJob";
using (SqlCommand sqlCmd = new SqlCommand(cmd, cn))
{
sqlCmd.CommandType = CommandType.StoredProcedure;
BuildSqlParameter(sqlCmd, "@EmployeeJobDescription", SqlDbType.Text, description);
BuildSqlParameter(sqlCmd, "@EmployeeJobHasTips", SqlDbType.Bit, hasTips);
BuildSqlParameter(sqlCmd, "@EmployeeJobAllowDispatch", SqlDbType.Bit, allowedDispatch);
BuildSqlParameter(sqlCmd, "@EmployeeJobId", SqlDbType.Int, ParameterDirection.ReturnValue);
if (sqlCmd.ExecuteNonQuery() > 0)
{
result = new EmployeeJob(Convert.ToInt32(sqlCmd.Parameters["@EmployeeJobId"].Value),
description, hasTips, allowedDispatch, true);
}
}
FinishedWithConnection(cn);
return(result);
}
public bool Update()
{
return(EmployeeJob.Update(this));
}