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); }