//TO EDIT
public List <WorkflowApprover> getSortedWorkflowApprovers(string workflowID, string 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 wfsid=@wfsid order by level asc";
comm.Parameters.AddWithValue("@wfid", workflowID);
comm.Parameters.AddWithValue("@wfsid", workflow_subID);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
approver = new WorkflowApprover();
string wfid = (string)dr["wfid"];
Workflow wf = wfDAO.getWorkflowByID(wfid);
approver.setMainWF(wf);
string wfsid = (string)dr["wf_sub_id"];
WorkflowSub wfs = wfsDAO.getWorkflowSubByID(wfsid);
approver.setMainWFS(wfs);
string userID = (string)dr["userID"];
User user = userDAO.getUserByID(userID);
approver.setApprover(user);
approver.setLevel((int)dr["levels"]);
toReturn.Add(approver);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public static Boolean routeIndividual(TNF tnf)
{
User currentUser = tnf.getUser();
UserDAO userDAO = new UserDAO();
Workflow currentWorkflow = wfDAO.getCurrentActiveWorkflow();
int numOfCriteria = wfDAO.getNumberOfCriteriaByWorkflow(currentWorkflow.getWorkflowID());
string currentStatusOfTNF = tnf.getStatus();
List <WorkflowSub> workflowSubs = wfsDAO.getSortedWorflowSubByWorkflow(currentWorkflow.getWorkflowID());
float tnfTotalCost = 0.0F; //to get from tnfDAO and to confirm if gst is included in the fee of consideration
if (currentStatusOfTNF.Equals("Pending"))
{
for (int i = 0; i < numOfCriteria; i++)
{
WorkflowSub currentWFS = workflowSubs[i];
float low_limit = currentWFS.getAmount_low();
float high_limit = currentWFS.getAmount_high();
List <WorkflowApprover> approvers = wfaDAO.getSortedWorkflowApprovers(currentWorkflow.getWorkflowID(), currentWFS.getWorkflowSubID());
if (tnfTotalCost >= low_limit && tnfTotalCost <= high_limit)
{
WorkflowApprover currentWFApprover = approvers[tnf.getWFStatus()];
User approver = currentWFApprover.getApprover();
//to check if applicant's level is higher than approver's level (need to write another function to check)
if (currentUser != approver)
{
//sendApprovalNotification(tnf, approver);
return(true);
}
else
{
//do something if applicant is approver
}
}
}
//check bond criteria
float bondCriteria = currentWorkflow.getBondCriteria();
if (tnfTotalCost >= bondCriteria)
{
//create new bond object
return(true);
}
}
return(false);
}
public WorkflowApprover getWorkflowApproverByJobCategory(string jobCategory, int wfid, int wf_sub_id)
{
WorkflowApprover toReturn = new WorkflowApprover();
SqlConnection conn = new SqlConnection();
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 job_category=@job_category and wfid=@wfid and wf_sub_id=@wf_sub_id";
comm.Parameters.AddWithValue("@job_category", jobCategory);
comm.Parameters.AddWithValue("@wfid", wfid);
comm.Parameters.AddWithValue("@wf_sub_id", wf_sub_id);
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
int wf_id = (int)dr["wfid"];
Workflow wf = wfDAO.getWorkflowByID(wf_id);
toReturn.setMainWF(wf);
int wfsid = (int)dr["wf_sub_id"];
WorkflowSub wfs = wfsDAO.getWorkflowSubByID(wfsid);
toReturn.setMainWFS(wfs);
toReturn.setJobCategory((string)dr["job_category"]);
toReturn.setLevel((int)dr["levels"]);
}
dr.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(toReturn);
}
public static Boolean makeApproval(TNF tnf, User user, Notification noti, string remarks)
{
//check TNF status
if (!tnf.getStatus().ToLower().Equals("pending"))
{
return(false);
}
//check budget
Department currentDept = deptDAO.getDeptByName(tnf.getUser().getDepartment());
Course currentCourse = tnfDAO.getCourseFromTNF(tnf.getTNFID());
double courseCost = currentCourse.getPrice();
Boolean gotBudget = deptDAO.checkDeptBudget(currentDept.getDeptName(), currentCourse.getPrice());
int nextWFLevel = 0;
if (!gotBudget)
{
return(false);
}
//check if approver is last in chain
Boolean isLastApprover = false;
Workflow currentWorkflow = tnf.getWorkflow();
WorkflowSub currentWFS = tnf.getWorkflowSub();
WorkflowApprover nextApprover = new WorkflowApprover();
WorkflowApprover lastApprover = wfaDAO.getLastApproverInChain(currentWorkflow.getWorkflowID(), currentWFS.getWorkflowSubID());
List <WorkflowApprover> approvers = wfaDAO.getSortedWorkflowApprovers(currentWorkflow.getWorkflowID(), currentWFS.getWorkflowSubID());
if (user.getJobCategory().Contains(lastApprover.getJobCategory()))
{
isLastApprover = true;
}
else if (lastApprover.getJobCategory().Equals("superior"))
{
User currentTNFUser = tnf.getUser();
if (currentTNFUser.getSupervisor().Equals(user.getUserID()))
{
isLastApprover = true;
}
}
else
{
int levelOfUser = wfaDAO.getLevelByJobCategory(user.getJobCategory(), currentWorkflow.getWorkflowID(), currentWFS.getWorkflowSubID());
if (levelOfUser == -1)
{
nextApprover = lastApprover;
nextWFLevel = lastApprover.getLevel();
}
else
{
nextApprover = approvers[levelOfUser + 1];
nextWFLevel = levelOfUser + 1;
}
}
//If its HR, approve for all HR
List <User> allHR = userDAO.getAllHR();
List <Notification> allHRNotification = new List <Notification>();
foreach (User hr in allHR)
{
allHRNotification.Add(notificationDAO.getPendingNotificationByTnfIDandUserID(tnf.getTNFID(), hr.getUserID()));
}
allHRNotification = allHRNotification.Where(x => x != null).ToList();
if (allHRNotification.Count < 2)
{
if (isLastApprover)
{
notificationDAO.updateNotificationStatus(noti.getNotificationID(), "approved");
notificationDAO.updateNotificationApprovedDate(noti.getNotificationID());
if (remarks != null)
{
notificationDAO.updateNotificationRemarks(noti.getNotificationID(), remarks);
}
tnfDAO.updateTNFStatus(tnf.getTNFID(), "approved");
deptDAO.updateDeptBudget(currentDept.getDeptName(), (currentDept.getActualBudget() - currentCourse.getPrice()));
double bondCriteria = currentWorkflow.getBondCriteria();
if (currentCourse.getPrice() >= bondCriteria)
{
Bonds currentBond = bondDAO.getBondByTNFIDandUserID(tnf.getTNFID(), tnf.getUser().getUserID());
bondDAO.updateBondStatus(currentBond.getBondID(), "approved");
}
}
else
{
notificationDAO.updateNotificationStatus(noti.getNotificationID(), "approved");
notificationDAO.updateNotificationApprovedDate(noti.getNotificationID());
if (remarks != null)
{
notificationDAO.updateNotificationRemarks(noti.getNotificationID(), remarks);
}
tnfDAO.updateTNFWFStatus(tnf.getTNFID(), nextWFLevel);
tnf = tnfDAO.getIndividualTNFByID(tnf.getUser().getUserID(), tnf.getTNFID());
Workflow_Route.sendApprovalNotification(tnf, nextApprover);
}
}
else
{
if (isLastApprover)
{
foreach (Notification hrNoti in allHRNotification)
{
notificationDAO.updateNotificationStatus(hrNoti.getNotificationID(), "approved");
notificationDAO.updateNotificationApprovedDate(hrNoti.getNotificationID());
if (remarks != null)
{
notificationDAO.updateNotificationRemarks(noti.getNotificationID(), remarks);
}
}
tnfDAO.updateTNFStatus(tnf.getTNFID(), "approved");
deptDAO.updateDeptBudget(currentDept.getDeptName(), (currentDept.getActualBudget() - currentCourse.getPrice()));
double bondCriteria = currentWorkflow.getBondCriteria();
if (currentCourse.getPrice() >= bondCriteria)
{
Bonds currentBond = bondDAO.getBondByTNFIDandUserID(tnf.getTNFID(), tnf.getUser().getUserID());
bondDAO.updateBondStatus(currentBond.getBondID(), "approved");
}
}
else
{
foreach (Notification hrNoti in allHRNotification)
{
notificationDAO.updateNotificationStatus(hrNoti.getNotificationID(), "approved");
notificationDAO.updateNotificationApprovedDate(hrNoti.getNotificationID());
if (remarks != null)
{
notificationDAO.updateNotificationRemarks(noti.getNotificationID(), remarks);
}
}
tnfDAO.updateTNFWFStatus(tnf.getTNFID(), nextWFLevel);
tnf = tnfDAO.getIndividualTNFByID(tnf.getUser().getUserID(), tnf.getTNFID());
Workflow_Route.sendApprovalNotification(tnf, nextApprover);
}
}
return(true);
}
public static Boolean routeIndividual(TNF tnf)
{
User currentUser = tnf.getUser();
UserDAO userDAO = new UserDAO();
Workflow currentWorkflow = wfDAO.getCurrentActiveWorkflow("individual");
//int numOfCriteria = wfDAO.getNumberOfCriteriaByWorkflow(currentWorkflow.getWorkflowID());
string currentStatusOfTNF = tnf.getStatus();
int probationPeriod = currentWorkflow.getProbationPeriod();
TimeSpan ts = DateTime.Now.Subtract(currentUser.getStartDate());
Course currentCourse = tnfDAO.getCourseFromTNF(tnf.getTNFID());
List <WorkflowSub> workflowSubs;
if (ts.TotalDays < probationPeriod)
{
workflowSubs = wfsDAO.getSortedWorflowSubByWorkflowIDandType(currentWorkflow.getWorkflowID(), "ceo");
}
else if (currentCourse.getOverseas().Equals("y"))
{
workflowSubs = wfsDAO.getSortedWorflowSubByWorkflowIDandType(currentWorkflow.getWorkflowID(), "ceo");
}
else
{
workflowSubs = wfsDAO.getSortedWorflowSubByWorkflowIDandType(currentWorkflow.getWorkflowID(), "normal");
}
double tnfTotalCost = tnfDAO.getCourseFromTNF(tnf.getTNFID()).getPrice(); //to get from tnfDAO and to confirm if gst is included in the fee of consideration
if (currentStatusOfTNF.Equals("pending"))
{
//check bond criteria
double bondCriteria = currentWorkflow.getBondCriteria();
if (tnfTotalCost >= bondCriteria)
{
//create new bond object
BondDAO bondDAO = new BondDAO();
Bonds newBond = new Bonds(currentUser.getUserID(), tnf.getTNFID(), "pending");
int bondID = bondDAO.createBond(newBond);
DateTime bondStartDate = currentCourse.getEndDate();
bondStartDate = bondStartDate.AddDays(1);
bondDAO.updateBondStartDate(bondID, bondStartDate);
}
for (int i = 0; i < workflowSubs.Count; i++)
{
if (wfsDAO.getWorkflowSubType(workflowSubs[i].getWorkflowSubID()).Equals("superior"))
{
i = 0;
}
WorkflowSub currentWFS = workflowSubs[i];
double low_limit = currentWFS.getAmount_low();
double high_limit = currentWFS.getAmount_high();
List <WorkflowApprover> approvers = wfaDAO.getSortedWorkflowApprovers(currentWorkflow.getWorkflowID(), currentWFS.getWorkflowSubID());
if (tnfTotalCost >= low_limit && tnfTotalCost <= high_limit)
{
WorkflowApprover currentWFApprover = approvers[tnf.getWFStatus()];
string currentUser_job_category = currentUser.getJobCategory();
WorkflowApprover checkApprover = wfaDAO.getWorkflowApproverByJobCategory(currentUser_job_category, currentWorkflow.getWorkflowID(), currentWFS.getWorkflowSubID());
Boolean checkIfLevelHigher = checkJobLevelHigher(currentUser_job_category, currentWFApprover.getJobCategory());
//to check if applicant's level is higher than approver's level (need to write another function to check)
WorkflowApprover lastWFApprover = wfaDAO.getLastApproverInChain(currentWorkflow.getWorkflowID(), currentWFS.getWorkflowSubID());
int levelOfUser = wfaDAO.getLevelByJobCategory(currentUser.getJobCategory(), currentWorkflow.getWorkflowID(), currentWFS.getWorkflowSubID());
if (checkApprover.getJobCategory() == null)
{
if (!checkIfLevelHigher)
{
tnfDAO.updateTNFWFSub(tnf.getTNFID(), currentWFS.getWorkflowSubID());
sendApprovalNotification(tnf, currentWFApprover);
return(true);
}
else
{
Boolean checkIfLevelHigherThanLastApprover = checkJobLevelHigher(currentUser_job_category, lastWFApprover.getJobCategory());
if (!checkIfLevelHigherThanLastApprover)
{
for (int j = 0; i < approvers.Count; j++)
{
Boolean checkLevelWithCurrentApprover = checkJobLevelHigher(currentUser_job_category, approvers[j].getJobCategory());
if (checkLevelWithCurrentApprover)
{
continue;
}
else
{
WorkflowApprover nextWFApprover = approvers[j];
tnfDAO.updateTNFWFStatus(tnf.getTNFID(), j);
tnfDAO.updateTNFWFSub(tnf.getTNFID(), currentWFS.getWorkflowSubID());
sendApprovalNotification(tnf, nextWFApprover);
return(true);
}
}
}
else
{
workflowSubs = wfsDAO.getSortedWorflowSubByWorkflowIDandType(currentWorkflow.getWorkflowID(), "superior");
}
}
}
else
{
if (!currentUser.getJobCategory().Equals("ceo"))
{
if (levelOfUser < lastWFApprover.getLevel())
{
WorkflowApprover nextWFApprover = approvers[levelOfUser + 1];
tnfDAO.updateTNFWFSub(tnf.getTNFID(), currentWFS.getWorkflowSubID());
tnfDAO.updateTNFWFStatus(tnf.getTNFID(), levelOfUser + 1);
sendApprovalNotification(tnf, nextWFApprover);
return(true);
}
else
{
//handle last person in chain
workflowSubs = wfsDAO.getSortedWorflowSubByWorkflowIDandType(currentWorkflow.getWorkflowID(), "superior");
}
}
else
{
//no need handle CEO
}
}
}
}
}
return(false);
}
public static void sendApprovalNotification(TNF tnf, WorkflowApprover wfa)
{
User currentUser = tnf.getUser();
UserDAO userDAO = new UserDAO();
NotificationDAO notiDAO = new NotificationDAO();
User approver = new User();
List <User> hrApprovers = new List <User>();
if (tnf.getStatus().Equals("pending"))
{
int current_wf_status = tnf.getWFStatus();
int wfaLevel = wfa.getLevel();
//if (wfa.getLevel() == current_wf_status)
//{
//get out who to send next
string approverJobCat = wfa.getJobCategory();
if (approverJobCat.ToLower().Equals("ceo"))
{
approver = userDAO.getCEO();
}
else if (approverJobCat.ToLower().Equals("hod"))
{
approver = userDAO.getHODbyDepartment(currentUser.getDepartment());
}
else if (approverJobCat.ToLower().Equals("supervisor"))
{
approver = userDAO.getSupervisorbyDepartment(currentUser.getDepartment());
}
else if (approverJobCat.ToLower().Equals("hr hod"))
{
approver = userDAO.getHRHOD();
}
else if (approverJobCat.ToLower().Equals("hr"))
{
hrApprovers = userDAO.getAllHR();
}
else if (approverJobCat.ToLower().Equals("superior"))
{
string supervisorID = userDAO.getSupervisorIDOfUser(currentUser.getUserID());
approver = userDAO.getUserByID(supervisorID);
}
//insert notification
if (!hrApprovers.Any())
{
Notification newNotification = new Notification(currentUser.getUserID(), approver.getUserID(), tnf.getTNFID(), "pending");
notiDAO.createNotification(newNotification);
}
else
{
foreach (User hrUser in hrApprovers)
{
Notification newNotification = new Notification(currentUser.getUserID(), hrUser.getUserID(), tnf.getTNFID(), "pending");
notiDAO.createNotification(newNotification);
}
}
//}
}
}
