//TO EDIT
public List <WorkflowApprover> getSortedWorkflowApprovers(int workflowID, int workflow_subID)
{
List <WorkflowApprover> toReturn = new List <WorkflowApprover>();
SqlConnection conn = new SqlConnection();
WorkflowApprover approver = null;
UserDAO userDAO = new UserDAO();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Workflow_approvers] where wfid=@wfid and wf_sub_id=@wfsid order by levels asc";
comm.Parameters.AddWithValue("@wfid", workflowID);
comm.Parameters.AddWithValue("@wfsid", workflow_subID);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
approver = new WorkflowApprover();
int wfid = (int)dr["wfid"];
Workflow wf = wfDAO.getWorkflowByID(wfid);
approver.setMainWF(wf);
int wfsid = (int)dr["wf_sub_id"];
WorkflowSub wfs = wfsDAO.getWorkflowSubByID(wfsid);
approver.setMainWFS(wfs);
approver.setJobCategory((string)dr["job_category"]);
approver.setLevel((int)dr["levels"]);
toReturn.Add(approver);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public List <WorkflowSub> getSortedWorflowSubByWorkflowIDandType(int workflowID, string type)
{
List <WorkflowSub> toReturn = new List <WorkflowSub>();
SqlConnection conn = new SqlConnection();
WorkflowSub wfs = null;
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [Workflow_Sub] where wfid=@wfid and type=@type order by criteria_lower_limit asc";
comm.Parameters.AddWithValue("@wfid", workflowID);
comm.Parameters.AddWithValue("@type", type);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
wfs = new WorkflowSub();
wfs.setMainWF(wfDAO.getWorkflowByID(workflowID));
wfs.setAmount_low((double)(dr["criteria_lower_limit"]));
wfs.setAmount_high((double)(dr["criteria_upper_limit"]));
wfs.setWorkflowSubID((int)dr["wf_sub_id"]);
toReturn.Add(wfs);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public List <TNF> getAllTNFByUserID(string userID)
{
SqlConnection conn = new SqlConnection();
List <TNF> toReturn = new List <TNF>();
try
{
conn = new SqlConnection();
string connstr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
conn.ConnectionString = connstr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "select * from [TNF] where userID=@userID";
comm.Parameters.AddWithValue("@userID", userID);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
TNF t = new TNF();
UserDAO userDAO = new UserDAO();
WorkflowDAO wfDAO = new WorkflowDAO();
WorkflowSubDAO wfsDAO = new WorkflowSubDAO();
User user = userDAO.getUserByID((string)dr["userID"]);
t.setUser(user);
t.setTNFID((int)dr["tnfid"]);
t.setType((string)dr["type"]);
t.setStatus((string)(dr["status"]));
t.setWFStatus((int)dr["wf_status"]);
t.setWorkflow(wfDAO.getWorkflowByID((int)dr["wfid"]));
if (!dr.IsDBNull(6))
{
t.setWorkflowSub(wfsDAO.getWorkflowSubByID((int)dr["wf_sub_id"]));
}
t.setApplicationDate(dr.GetDateTime(7));
toReturn.Add(t);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
