public SatyamTaskTableEntry getRandomEntryByJobTemplateAndPriceAndMaxDoneScore(string JobTemplateType, int doneScore, double price)
{
string SQLCommandString = "SELECT TOP 1 * FROM " + TableName + " WHERE JobTemplateType = '" + JobTemplateType + "' AND DoneScore <= '" + doneScore + "' AND PricePerTask = '" + price + "' ORDER BY NEWID()";
SatyamTaskTableEntry entry = getEntry(SQLCommandString);
return(entry);
}
public SatyamTaskTableEntry getRandomEntry_FromTopK_ByJobTemplatePrice(string JobTemplateType, double price, int K)
{
string SQLCommandString = "SELECT TOP 1 * FROM (SELECT TOP " + K + " * FROM " + TableName + " WHERE JobTemplateType = '" + JobTemplateType + "' AND PricePerTask = '" + price + "' ORDER BY ID) AS A ORDER BY NEWID()";
SatyamTaskTableEntry entry = getEntry(SQLCommandString);
return(entry);
}
public SatyamTaskTableEntry getRandomEntryByJobTemplateAndGUID(string JobTemplateType, string GUID)
{
string SQLCommandString = "SELECT TOP 1 * FROM " + TableName + " WHERE JobTemplateType = '" + JobTemplateType + "' AND JobGUID = '" + GUID + "' ORDER BY NEWID()";
SatyamTaskTableEntry entry = getEntry(SQLCommandString);
return(entry);
}
public SatyamTaskTableEntry getMinimumTriedNewEntryForWorkerIDByTemplateAndPrice(string WorkerID, string JobTemplateType,
double Price)
{
int noTasks = getTasksCountByJobTemplateTypeAndPrice(JobTemplateType, Price);
if (noTasks == 0)
{
return(null);
}
//int num = 0;
//do
//{
// doneScore++;
// num = getRemainingTasksLessThanADoneScoreCountByJobTemplateTypeAndPrice(JobTemplateType, doneScore, Price);
//} while (num == 0);
int doneScore = getMinDoneScoreByJobTemplateTypeAndPrice(JobTemplateType, Price);
SatyamTaskTableEntry entry = new SatyamTaskTableEntry();
SatyamResultsTableAccess resultDB = new SatyamResultsTableAccess();
List <string> GUIDList = new List <string>();
List <int> TriedEntries = new List <int>();
for (int i = 0; i < 3; i++)
{
// try at most 3 times
//string SQLCommandString = "SELECT TOP 1 * FROM " + TableName + " WHERE DoneScore = '" + doneScore + "' AND JobTemplateType = '" + JobTemplateType + "' ORDER BY NEWID()";
//entry = getEntry(SQLCommandString);
//entry = getRandomEntry_FromTopK_ByJobTemplatePrice(JobTemplateType, Price, 10);
entry = getRandomEntryByJobTemplateAndPriceAndMaxDoneScore(JobTemplateType, doneScore, Price);
if (!GUIDList.Contains(entry.JobGUID))
{
GUIDList.Add(entry.JobGUID);
TriedEntries.AddRange(resultDB.getAllTaskIDsAWorkerHasDoneForAGUID(WorkerID, entry.JobGUID));
}
// the guid might change here if multiple guid are in play with same price and job template
// query by jobtemplate itself takes the whole table, too long.
if (!TriedEntries.Contains(entry.ID) || WorkerID == "Testing" || WorkerID == TaskConstants.AdminID)//
{
break;
}
else
{
entry = null;
}
}
//if (entry == null)
//{
// entry = getRandomEntryByJobTemplateDoneScorePrice(JobTemplateType, doneScore, Price);
//}
resultDB.close();
return(entry);
}
public SatyamTaskTableEntry getTopKNewEntryForWorkerIDByTemplateAndPrice(int K, string WorkerID, string JobTemplateType,
double Price)
{
int noTasks = getTasksCountByJobTemplateTypeAndPrice(JobTemplateType, Price);
if (noTasks == 0)
{
return(null);
}
//int num = 0;
//do
//{
// doneScore++;
// num = getRemainingTasksLessThanADoneScoreCountByJobTemplateTypeAndPrice(JobTemplateType, doneScore, Price);
//} while (num == 0);
int doneScore = getMinDoneScoreByJobTemplateTypeAndPrice(JobTemplateType, Price);
SatyamTaskTableEntry entry = new SatyamTaskTableEntry();
SatyamResultsTableAccess resultDB = new SatyamResultsTableAccess();
List <string> GUIDList = new List <string>();
List <int> TriedEntries = new List <int>();
for (int i = 0; i < 3; i++)
{
// try at most 3 times
entry = getRandomEntry_FromTopK_ByJobTemplatePrice(JobTemplateType, Price, K);
if (!GUIDList.Contains(entry.JobGUID))
{
GUIDList.Add(entry.JobGUID);
TriedEntries.AddRange(resultDB.getAllTaskIDsAWorkerHasDoneForAGUID(WorkerID, entry.JobGUID));
}
// the guid might change here if multiple guid are in play with same price and job template
// query by jobtemplate itself takes the whole table, too long.
if (!TriedEntries.Contains(entry.ID) || WorkerID == "Testing" || WorkerID == TaskConstants.AdminID)//
{
break;
}
else
{
entry = null;
}
}
if (entry == null)
{
// fall back to the minimum done score approach
entry = getMinimumTriedNewEntryForWorkerIDByTemplateAndPrice(WorkerID, JobTemplateType, Price);
}
resultDB.close();
return(entry);
}
public SatyamTaskTableEntry getMinimumTriedEntryByTemplateAndPrice(string JobTemplateType, double Price)
{
int noTasks = getTasksCountByJobTemplateTypeAndPrice(JobTemplateType, Price);
if (noTasks == 0)
{
return(null);
}
int doneScore = getMinDoneScoreByJobTemplateTypeAndPrice(JobTemplateType, Price);
//string SQLCommandString = "SELECT TOP 1 * FROM " + TableName + " WHERE DoneScore = '" + doneScore + "' AND JobTemplateType = '" + JobTemplateType + "' ORDER BY NEWID()";
//SatyamTaskTableEntry entry = getEntry(SQLCommandString);
SatyamTaskTableEntry entry = getRandomEntryByJobTemplateAndPriceAndMaxDoneScore(JobTemplateType, doneScore, Price);
return(entry);
}
public SatyamTaskTableEntry getMinimumTriedNewEntryForWorkerIDByTemplateAndPriceAndMaxDoneScore(string WorkerID, string JobTemplateType,
double Price, int MaxDoneScore)
{
int noTasks = getTasksCountByJobTemplateTypeAndPrice(JobTemplateType, Price);
if (noTasks == 0)
{
return(null);
}
int doneScore = getMinDoneScoreByJobTemplateTypeAndPrice(JobTemplateType, Price);
if (doneScore >= MaxDoneScore)
{
return(null);
}
SatyamTaskTableEntry entry = new SatyamTaskTableEntry();
SatyamResultsTableAccess resultDB = new SatyamResultsTableAccess();
List <string> GUIDList = new List <string>();
List <int> TriedEntries = new List <int>();
for (int i = 0; i < 3; i++)
{
entry = getRandomEntryByJobTemplateAndPriceAndMaxDoneScore(JobTemplateType, doneScore, Price);
if (!GUIDList.Contains(entry.JobGUID))
{
GUIDList.Add(entry.JobGUID);
TriedEntries.AddRange(resultDB.getAllTaskIDsAWorkerHasDoneForAGUID(WorkerID, entry.JobGUID));
}
// the guid might change here if multiple guid are in play with same price and job template
// query by jobtemplate itself takes the whole table, too long.
if (!TriedEntries.Contains(entry.ID) || WorkerID == "Testing" || WorkerID == TaskConstants.AdminID)//
{
break;
}
else
{
entry = null;
}
}
resultDB.close();
return(entry);
}
public List <SatyamTaskTableEntry> getEntries(string SQLCommandString)
{
List <SatyamTaskTableEntry> ret = new List <SatyamTaskTableEntry>();
SqlCommand sqlCommand = new SqlCommand(SQLCommandString, dbAccess.getSQLConnection());
sqlCommand.CommandTimeout = 200;
try
{
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
int ID = (int)reader["ID"];
string UserID = (string)reader["UserID"];
string JobGUID = (string)reader["JobGUID"];
string JobTemplateType = (string)reader["JobTemplateType"];
string JSonString = (string)reader["TaskParametersString"];
DateTime SubmitTime = (DateTime)reader["JobSubmitTime"];
int DoneScore = (int)reader["DoneScore"];
double PricePerTask = 0;
if (reader["PricePerTask"] != null)
{
PricePerTask = (double)reader["PricePerTask"];
}
SatyamTaskTableEntry entry = new SatyamTaskTableEntry(ID, JobTemplateType, UserID, JobGUID, JSonString, SubmitTime, DoneScore, PricePerTask);
ret.Add(entry);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
}
return(ret);
}
public SatyamTaskTableEntry getMinimumTriedEntryByTemplateAndMaxDoneScore(string JobTemplateType, int MaxDoneScore)
{
int noTasks = getTasksCountByJobTemplateType(JobTemplateType);
if (noTasks == 0)
{
return(null);
}
int doneScore = 0;
int num = 0;
while (num == 0 && doneScore < MaxDoneScore)
{
num = getRemainingTasksLessThanADoneScoreCountByJobTemplateType(JobTemplateType, doneScore);
doneScore++;
}
//string SQLCommandString = "SELECT TOP 1 * FROM " + TableName + " WHERE DoneScore = '" + doneScore + "' AND JobTemplateType = '" + JobTemplateType + "' ORDER BY NEWID()";
//SatyamTaskTableEntry entry = getEntry(SQLCommandString);
SatyamTaskTableEntry entry = getRandomEntryByJobTemplateAndMaxDoneScore(JobTemplateType, doneScore - 1);
return(entry);
}
public void AddEntry(SatyamTaskTableEntry entry)
{
AddEntry(entry.JobTemplateType, entry.UserID, entry.JobGUID, entry.TaskParametersString, entry.JobSubmitTime, entry.PricePerTask);
}