/**
* This function gets UserID and QueryOption and returns the TaskID,TaskName,
* Status,ElapsedTime of all the tasks that have the given UserID and that they are
* in the state of the given QueryOpiton.
*/
public List <TaskStatus> getWorkDetails(String userID, QueryOption option)
{
// 1. Instantiate the connection
SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString());
SqlDataReader rdr = null;
List <TaskStatus> taskDetailsList = new List <TaskStatus>();
try
{
// 2. Open the connection
conn.Open();
SqlCommand cmd;
String statusString = "";
switch (option)
{
case QueryOption.ActiveTasks:
statusString = "ACTIVE";
break;
case QueryOption.IdleTasks:
statusString = "IDLE";
break;
case QueryOption.WaitingTasks:
statusString = "WAITING";
break;
default:
statusString = "";
break;
}
if (option == QueryOption.AllTasks)
{
cmd = new SqlCommand("SELECT TaskID,TaskName,Status,ElapsedTime from Task WHERE UserID=\'" + userID + "\'", conn);
}
else
{
cmd = new SqlCommand("SELECT TaskID,TaskName,Status,ElapsedTime from Task" +
" WHERE UserID=\'" + userID + "\' AND Status=\'" + statusString + "\'", conn);
}
// get query results
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
TaskStatus taskDetails = new TaskStatus(rdr["TaskID"].ToString());
taskDetails.setTaskElapsedTime((long)rdr["ElapsedTime"]);
taskDetails.setTaskName((String)rdr["TaskName"]);
Status statusOfTask = TaskStatus.convertToStatusObj((String)rdr["Status"]);
taskDetails.setTaskStatus(statusOfTask);
taskDetailsList.Add(taskDetails);
}
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// 5. Close the connection
if (conn != null)
{
conn.Close();
}
}
return(taskDetailsList);
}
public String createWorkResources(String userID, String taskName)
{
SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString());
String taskid = null;
SqlDataReader rdr = null;
try
{
conn.Open();
//insert new row to Task
String cmdtxt = "SELECT TaskName FROM Task WHERE UserID = \'" + userID + "\'";
SqlCommand cmd = new SqlCommand(cmdtxt, conn);
//Execute command
rdr = cmd.ExecuteReader();
//check if the inserted userid has a task named taskName
if (rdr.HasRows)
{
while (rdr.Read())
{
//Console.WriteLine(rdr["TaskName"]);
string nameExtacted = (string)rdr["TaskName"];
nameExtacted = nameExtacted.TrimEnd(' ');
if (nameExtacted == taskName)
{
throw new Exception("TaskName for the user allready exists");
}
}
}
if (rdr != null)
{
rdr.Close();
}
//if the taskName does not exist in the table for the inserted userid
//then insert it into the table.
cmdtxt = "INSERT INTO Task (UserID,TaskName,Status,ElapsedTime,LinkDepth,AllowUrlParam) " +
"VALUES (\'" + userID + "\',\'" + taskName + "\',\'IDLE\',0,4,\'false\')";
cmd.CommandText = cmdtxt;
cmd.ExecuteNonQuery();
//return the taskID of the new row created
cmdtxt = "SELECT TaskID FROM Task WHERE UserID=\'" + userID + "\' AND TaskName=\'" + taskName + "\'";
cmd.CommandText = cmdtxt;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
taskid = rdr["TaskID"].ToString();
}
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
return(taskid);
}
/**
* This function adds the URL result to the given categories (and it's fathers).
*/
public void addURLResult(String taskId, Result result)
{
SqlConnection conn = new SqlConnection(SettingsReader.getConnectionString());
SqlDataReader rdr = null;
String categoryID = result.getCategoryID();
try
{
conn.Open();
SqlCommand cmdcheck = new SqlCommand("SELECT TaskName FROM Task WHERE TaskID = \'" + taskId + "\'", conn);
rdr = cmdcheck.ExecuteReader();
if (rdr.HasRows)
{
if (rdr != null)
{
rdr.Close();
}
if ((categoryID == "") || (categoryID == "0") || (categoryID == null))
{
//System.Console.WriteLine("1> Cat ID: " + categoryID);
SqlCommand cmd = new SqlCommand("INSERT INTO Results (TaskID,Url,rank,TrustMeter) " +
"Values(\'" + taskId + "\',\'" + result.getUrl() + "\',\'" +
result.getRank() + "\',\'" +
result.getTrustMeter() + "\')", conn);
cmd.ExecuteNonQuery();
}
else
{
while ((categoryID != null) && (categoryID != ""))
{
//System.Console.WriteLine("2> Cat ID: " + categoryID);
SqlCommand cmd = new SqlCommand("INSERT INTO Results (TaskID,Url,CategoryID,rank,TrustMeter) " +
"Values(\'" + taskId + "\',\'" + result.getUrl() + "\',\'" +
categoryID + "\',\'" + result.getRank() + "\',\'" +
result.getTrustMeter() + "\')", conn);
cmd.ExecuteNonQuery();
SqlCommand cmnd = new SqlCommand("SELECT ParentCategory From Category WHERE CategoryID = \'" + categoryID + "\'", conn);
rdr = cmnd.ExecuteReader();
if (rdr.HasRows)
{
if (rdr.Read())
{
categoryID = rdr["ParentCategory"].ToString();
}
else
{
categoryID = null;
}
}
if (rdr != null)
{
rdr.Close();
}
}
}
}
}
catch (Exception e)
{
System.Console.WriteLine("Exception Caught: " + e.Message);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
