public static void UpdatePriorityDetails(EmployeePriority empPriority)
{
string commandText = "UPDATE PRIORITY SET ISSUENO=@issueno, SUBJECT=@subject, STATUS=@status, COMPLETED=@completed, DEVDUEDATE=@devdue, QADUEDATE=@qadue";
string whereClause = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
empPriority.IssueNumber = (empPriority.IssueNumber == null) ? "" : empPriority.IssueNumber;
empPriority.IssueSubject = (empPriority.IssueSubject == null) ? "" : empPriority.IssueSubject;
empPriority.Status = (empPriority.Status == null) ? "" : empPriority.Status;
empPriority.Completed = (empPriority.Completed == null) ? "0" : empPriority.Completed;
empPriority.DevDueDate = (empPriority.DevDueDate == null) ? "" : empPriority.DevDueDate;
empPriority.QADueDate = (empPriority.QADueDate == null) ? "" : empPriority.QADueDate;
paramList.Add(new OleDbParameter("@issueno", empPriority.IssueNumber));
paramList.Add(new OleDbParameter("@subject", empPriority.IssueSubject));
paramList.Add(new OleDbParameter("@status", empPriority.Status));
paramList.Add(new OleDbParameter("@completed", empPriority.Completed));
paramList.Add(new OleDbParameter("@devdue", empPriority.DevDueDate));
paramList.Add(new OleDbParameter("@qadue", empPriority.QADueDate));
if (empPriority.EmployeeID != null)
{
whereClause += (whereClause == "") ? " WHERE EMPID=@empId" : " AND EMPID=@empId";
paramList.Add(new OleDbParameter("@empId", empPriority.EmployeeID));
}
if (empPriority.Priority != null)
{
whereClause += (whereClause == "") ? " WHERE PRIORITY=@priority" : " AND PRIORITY=@priority";
paramList.Add(new OleDbParameter("@priority", empPriority.Priority));
}
commandText += whereClause;
DAOEmployee.ExecuteDMLCommand(commandText, paramList);
}
public static void DeleteEmployeePriority(string PID)
{
string commandText = "DELETE FROM PRIORITY";
string whereClause = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
if (PID != null)
{
whereClause += (whereClause == "") ? " WHERE PID=@pid" : " AND PID=@pid";
paramList.Add(new OleDbParameter("@pid", PID));
}
commandText += whereClause;
DAOEmployee.ExecuteDMLCommand(commandText, paramList);
}
public static void SaveUser(RegisterModel UserDetails)
{
string commandText = "INSERT INTO EMPLOYEES(EMPID,EMPNAME,EMAILID,USERNAME,PASS,ISMANAGER,ISACTIVE) VALUES(@empId, @empName, @emailId, @username, @pass, @IsManager, @IsActive)";
List <OleDbParameter> paramList = new List <OleDbParameter>();
paramList.Add(new OleDbParameter("@empId", UserDetails.EMPID));
paramList.Add(new OleDbParameter("@empName", UserDetails.FullName));
paramList.Add(new OleDbParameter("@emailId", UserDetails.EmailId));
paramList.Add(new OleDbParameter("@username", UserDetails.UserName));
paramList.Add(new OleDbParameter("@pass", UserDetails.Password));
paramList.Add(new OleDbParameter("@IsManager", UserDetails.IsManager));
paramList.Add(new OleDbParameter("@IsActive", "No"));
DAOEmployee.ExecuteDMLCommand(commandText, paramList);
}
public static void UpdateEmployeePriority(string priorityRowId, string priority)
{
string commandText = "UPDATE PRIORITY SET PRIORITY=@priority";
string whereClause = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
paramList.Add(new OleDbParameter("@priority", priority));
if (priorityRowId != null)
{
whereClause += (whereClause == "") ? " WHERE PID=@priorityRowId" : " AND PID=@priorityRowId";
paramList.Add(new OleDbParameter("@priorityRowId", priorityRowId));
}
commandText += whereClause;
DAOEmployee.ExecuteDMLCommand(commandText, paramList);
}
public static string GetEmployeeMaxPriority(string empId)
{
string commandText = "SELECT MAX(PRIORITY) FROM PRIORITY";
string whereClause = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
if (empId != null)
{
whereClause += (whereClause == "") ? " WHERE EMPID=@empId" : " AND EMPID=@empId";
paramList.Add(new OleDbParameter("@empId", empId));
}
commandText += whereClause;
string maxPriority = DAOEmployee.ExecuteScalar(commandText, paramList);
return(maxPriority);
}
public static DataTable GetEmployees(string empId)
{
string commandText = "SELECT * FROM EMPLOYEES";
string whereClause = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
if (empId != null)
{
whereClause += (whereClause == "") ? " WHERE EMPID=@empId" : " AND EMPID=@empId";
paramList.Add(new OleDbParameter("@empId", empId));
}
commandText += whereClause;
DataTable dtEmployee = DAOEmployee.FetchData(commandText, paramList);
return(dtEmployee);
}
public static DataTable GetEmployeePriorityById(string PID)
{
string commandText = "SELECT EMPLOYEES.EMPID,EMPNAME,PRIORITY,ISSUENO,SUBJECT,STATUS,COMPLETED,DEVDUEDATE,QADUEDATE,ENTEREDBY,ASSIGNEDBY,REASON FROM EMPLOYEES, PRIORITY";
string whereClause = " WHERE EMPLOYEES.EMPID=PRIORITY.EMPID";
string orderBy = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
if (PID != null)
{
whereClause += (whereClause == "") ? " WHERE PID=@pid" : " AND PID=@pid";
paramList.Add(new OleDbParameter("@pid", PID));
}
commandText += whereClause + orderBy;
DataTable dtEmpPriority = DAOEmployee.FetchData(commandText, paramList);
return(dtEmpPriority);
}
public static void UpdateEmployeesFollowingPriority(string empId, int priority)
{
string commandText = "UPDATE PRIORITY SET PRIORITY=PRIORITY-1";
string whereClause = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
if (empId != null)
{
whereClause += (whereClause == "") ? " WHERE EMPID=@empId" : " AND EMID=@empId";
paramList.Add(new OleDbParameter("@empId", empId));
}
if (priority > 0)
{
whereClause += (whereClause == "") ? " WHERE PRIORITY>@priority" : " AND PRIORITY>@priority";
paramList.Add(new OleDbParameter("@priority", priority));
}
commandText += whereClause;
DAOEmployee.ExecuteDMLCommand(commandText, paramList);
}
/*public static EmployeePriority EditEmployeePriority(string PID)
* {
* EmployeePriority empPriority = null;
* DataTable dtPriority = GetEmployeePriorityById(PID);
* if (dtPriority.Rows.Count > 0)
* {
* empPriority = new EmployeePriority();
* empPriority.EmployeeID = dtPriority.Rows[0]["EMPID"].ToString(); ;
* empPriority.EmployeeName = dtPriority.Rows[0]["EMPNAME"].ToString();
* empPriority.IssueNumber = dtPriority.Rows[0]["ISSUENO"].ToString();
* empPriority.IssueSubject = dtPriority.Rows[0]["SUBJECT"].ToString();
* empPriority.DevDueDate = dtPriority.Rows[0]["DEVDUEDATE"].ToString().Split(' ')[0];
* empPriority.QADueDate = dtPriority.Rows[0]["QADUEDATE"].ToString().Split(' ')[0];
* empPriority.Priority = dtPriority.Rows[0]["PRIORITY"].ToString();
* }
* return empPriority;
* }*/
public static void AddPriority(EmployeePriority empPriority)
{
string commandText = "INSERT INTO PRIORITY(EMPID,ISSUENO,SUBJECT,STATUS,COMPLETED,DEVDUEDATE,QADUEDATE,PRIORITY,ENTEREDBY,ASSIGNEDBY) VALUES(@empId,@issueno,@subject,@status,@completed,@devdue,@qadue,@priority,@enteredby,@assignedby)";
List <OleDbParameter> paramList = new List <OleDbParameter>();
empPriority.Completed = (empPriority.Completed == null) ? "0" : empPriority.Completed;
empPriority.DevDueDate = (empPriority.DevDueDate == null) ? "" : empPriority.DevDueDate;
empPriority.QADueDate = (empPriority.QADueDate == null) ? "" : empPriority.QADueDate;
paramList.Add(new OleDbParameter("@empId", empPriority.EmployeeID));
paramList.Add(new OleDbParameter("@issueno", empPriority.IssueNumber));
paramList.Add(new OleDbParameter("@subject", empPriority.IssueSubject));
paramList.Add(new OleDbParameter("@status", empPriority.Status));
paramList.Add(new OleDbParameter("@completed", empPriority.Completed));
paramList.Add(new OleDbParameter("@devdue", empPriority.DevDueDate));
paramList.Add(new OleDbParameter("@qadue", empPriority.QADueDate));
paramList.Add(new OleDbParameter("@priority", empPriority.Priority));
paramList.Add(new OleDbParameter("@enteredby", empPriority.EnteredBy));
paramList.Add(new OleDbParameter("@assignedby", empPriority.AssignedBy));
DAOEmployee.ExecuteDMLCommand(commandText, paramList);
}
//userDetails[0] - USERNAME
//userDetails[1] - PASSWORD
//userDetails[2] - EMPID
//userDetails[3] - EMAILID
public static Employee GetEmployee(params string[] userDetails)
{
Employee employee = null;
string commandText = "SELECT EMPID, EMPNAME, USERNAME, ISMANAGER, ISACTIVE FROM EMPLOYEES";
string whereClause = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
if ((userDetails.Length >= 1) && (userDetails[0] != null))
{
whereClause += (whereClause == "") ? " WHERE USERNAME=@username" : " AND USERNAME=@username";
paramList.Add(new OleDbParameter("@username", userDetails[0]));
}
if ((userDetails.Length >= 2) && (userDetails[1] != null))
{
whereClause += (whereClause == "") ? " WHERE PASS=@pass" : " AND PASS=@pass";
paramList.Add(new OleDbParameter("@pass", userDetails[1]));
}
if ((userDetails.Length >= 3) && (userDetails[2] != null))
{
whereClause += (whereClause == "") ? " WHERE EMPID=@empId" : " AND EMPID=@empId";
paramList.Add(new OleDbParameter("@empId", userDetails[2]));
}
if ((userDetails.Length >= 4) && (userDetails[3] != null))
{
whereClause += (whereClause == "") ? " WHERE EMAILID=@emailId" : " AND EMAILID=@emailId";
paramList.Add(new OleDbParameter("@emailId", userDetails[3]));
}
commandText += whereClause;
DataTable dtEmployee = DAOEmployee.FetchData(commandText, paramList);
if (dtEmployee.Rows.Count > 0)
{
employee = new Employee();
employee.EmpID = dtEmployee.Rows[0]["EMPID"].ToString();
employee.EmployeeName = dtEmployee.Rows[0]["EMPNAME"].ToString();
employee.UserName = dtEmployee.Rows[0]["USERNAME"].ToString();
employee.IsManager = dtEmployee.Rows[0]["ISMANAGER"].ToString();
employee.IsActive = dtEmployee.Rows[0]["ISACTIVE"].ToString();
}
return(employee);
}
public static DataTable GetEmployeePriority(string empId)
{
string commandText = "SELECT EMPNAME,PID,PRIORITY,ISSUENO,SUBJECT,STATUS,DEVDUEDATE,QADUEDATE FROM EMPLOYEES, PRIORITY";
string whereClause = " WHERE EMPLOYEES.EMPID=PRIORITY.EMPID";
string orderBy = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
if (empId == null)
{
orderBy = " ORDER BY EMPNAME, PRIORITY";
}
if (empId != null)
{
whereClause += (whereClause == "") ? " WHERE EMPLOYEES.EMPID=@empId" : " AND EMPLOYEES.EMPID=@empId";
paramList.Add(new OleDbParameter("@empId", empId));
orderBy = " ORDER BY PRIORITY";
}
commandText += whereClause + orderBy;
DataTable dtEmpPriority = DAOEmployee.FetchData(commandText, paramList);
return(dtEmpPriority);
}
public static void AssignEmployeePriority(string assignedBy, string assignTo, string pid, string status, string reason)
{
string commandText = "UPDATE PRIORITY SET EMPID=@empid, STATUS=@status, ASSIGNEDBY=@assignedby, REASON=@reason, PRIORITY=@priority";
string whereClause = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
paramList.Add(new OleDbParameter("@empid", assignTo));
paramList.Add(new OleDbParameter("@status", status));
paramList.Add(new OleDbParameter("@assignedby", assignedBy));
paramList.Add(new OleDbParameter("@reason", reason));
string strPriority = (GetEmployeeMaxPriority(assignTo));
int priority = (strPriority == "") ? 1 : (Convert.ToInt32(strPriority) + 1);
paramList.Add(new OleDbParameter("@priority", priority));
if (pid != null)
{
whereClause += (whereClause == "") ? " WHERE PID=@pid" : " AND PID=@pid";
paramList.Add(new OleDbParameter("@pid", pid));
}
commandText += whereClause;
DAOEmployee.ExecuteDMLCommand(commandText, paramList);
}
public static string GetEmployeePriorityRow(string empId, string priority)
{
string priorityRowId = "";
string commandText = "SELECT * FROM PRIORITY";
string whereClause = "";
List <OleDbParameter> paramList = new List <OleDbParameter>();
if (empId != null)
{
whereClause += (whereClause == "") ? " WHERE EMPID=@empId" : " AND EMPID=@empId";
paramList.Add(new OleDbParameter("@empId", empId));
}
if (priority != null)
{
whereClause += (whereClause == "") ? " WHERE PRIORITY=@priority" : " AND PRIORITY=@priority";
paramList.Add(new OleDbParameter("@priority", priority));
}
commandText += whereClause;
DataTable dtPriority = DAOEmployee.FetchData(commandText, paramList);
priorityRowId = dtPriority.Rows[0]["PID"].ToString();
return(priorityRowId);
}
