void UpdateNewSingleEntry(WorkerStatisticsTableEntry entry, bool approved, DateTime doneTime)
{
entry.TasksDone++;
if (approved)
{
entry.TasksApproved++;
}
entry.SuccessFraction = ((float)entry.TasksApproved + 1.0) / ((float)entry.TasksDone + 2.0);
if (doneTime > entry.LastUpdateTime)
{
entry.LastUpdateTime = doneTime;
}
String SQLCommandString = "UPDATE " + TableName + " SET TasksDone = '" + entry.TasksDone + "', TasksApproved = '" + entry.TasksApproved + "', SuccessFraction = '" + entry.SuccessFraction + "', LastUpdateTime = '" + entry.LastUpdateTime + "' WHERE WorkerId = '" + entry.WorkerId + "' AND JobTemplateType = '" + entry.JobTemplateType + "'";
//Console.WriteLine(SQLCommandString);
SqlCommand sqlCommand = new SqlCommand(SQLCommandString, dbAccess.getSQLConnection());
sqlCommand.CommandTimeout = 200;
try
{
sqlCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.Error.WriteLine(ex.ToString());
throw ex;
}
}
public WorkerStatisticsTableEntry getWorkerStatistics(string l_WorkerId, string l_JobTemplateType)
{
String SQLCommandString = "SELECT * FROM " + TableName + " WHERE WorkerId = '" + l_WorkerId + "' AND JobTemplateType = '" + l_JobTemplateType + "'";
SqlCommand sqlCommand = new SqlCommand(SQLCommandString, dbAccess.getSQLConnection());
sqlCommand.CommandTimeout = 200;
WorkerStatisticsTableEntry entry = null;
try
{
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
int Id = (int)reader["Id"];
string WorkerId = (string)reader["WorkerId"];
string JobTemplateType = (string)reader["JobTemplateType"];
int TasksDone = (int)reader["TasksDone"];
int TasksApproved = (int)reader["TasksApproved"];
double SuccessFraction = (double)reader["SuccessFraction"];
DateTime LastUpdateTime = (DateTime)reader["LastUpdateTime"];
entry = new WorkerStatisticsTableEntry(Id, WorkerId, JobTemplateType, TasksDone, TasksApproved, SuccessFraction, LastUpdateTime);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
}
return(entry);
}
public void UpdateEntry(string l_WorkerId,
string l_JobTemplateType,
int l_TasksDone,
int l_TasksApproved,
DateTime l_LastUpdateTime)
{
WorkerStatisticsTableEntry entry = getWorkerStatistics(l_WorkerId, l_JobTemplateType);
if (entry != null)
{
entry.TasksDone = l_TasksDone;
entry.TasksApproved = l_TasksApproved;
entry.SuccessFraction = (l_TasksApproved + 1) / (l_TasksDone + 2);
entry.LastUpdateTime = l_LastUpdateTime;
String SQLCommandString = "UPDATE " + TableName + " SET TasksDone = '" + entry.TasksDone + "', TasksApproved = '" + entry.TasksApproved + "', SuccessFraction = '" + entry.SuccessFraction + "', LastUpdateTime = '" + entry.LastUpdateTime + "' WHERE WorkerId = '" + entry.WorkerId + "' AND JobTemplateType = '" + entry.JobTemplateType + "'";
SqlCommand sqlCommand = new SqlCommand(SQLCommandString, dbAccess.getSQLConnection());
sqlCommand.CommandTimeout = 200;
try
{
sqlCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.Error.WriteLine(ex.ToString());
throw ex;
}
}
else
{
AddEntry(l_WorkerId, l_JobTemplateType, l_TasksDone, l_TasksApproved, l_LastUpdateTime);
}
//Console.WriteLine(SQLCommandString);
}
//adds or updates the entry
public void UpdateSingleEntry(
string l_WorkerId,
string l_JobTemplateType,
bool approved,
DateTime lastUpdateTime
)
{
WorkerStatisticsTableEntry entry = getWorkerStatistics(l_WorkerId, l_JobTemplateType);
if (entry != null)
{
UpdateNewSingleEntry(entry, approved, lastUpdateTime);
}
else
{
AddNewSingleEntry(l_WorkerId, l_JobTemplateType, approved, lastUpdateTime);
}
}
public SortedDictionary <string, SortedDictionary <string, WorkerStatisticsTableEntry> > getAllEntries()
{
SortedDictionary <string, SortedDictionary <string, WorkerStatisticsTableEntry> > entries = new SortedDictionary <string, SortedDictionary <string, WorkerStatisticsTableEntry> >();
String SQLCommandString = "SELECT * FROM " + TableName;
SqlCommand sqlCommand = new SqlCommand(SQLCommandString, dbAccess.getSQLConnection());
sqlCommand.CommandTimeout = 500;
try
{
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
int Id = (int)reader["Id"];
string WorkerId = (string)reader["WorkerId"];
string JobTemplateType = (string)reader["JobTemplateType"];
int TasksDone = (int)reader["TasksDone"];
int TasksApproved = (int)reader["TasksApproved"];
double SuccessFraction = (double)reader["SuccessFraction"];
DateTime LastUpdateTime = (DateTime)reader["LastUpdateTime"];
WorkerStatisticsTableEntry entry = new WorkerStatisticsTableEntry(Id, WorkerId, JobTemplateType, TasksDone, TasksApproved, SuccessFraction, LastUpdateTime);
if (!entries.ContainsKey(WorkerId))
{
entries.Add(WorkerId, new SortedDictionary <string, WorkerStatisticsTableEntry>());
}
entries[WorkerId].Add(JobTemplateType, entry);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
}
return(entries);
}
