344 lines
20 KiB
C#
344 lines
20 KiB
C#
using HRM.BO;
|
|
using Ease.Core.DataAccess;
|
|
using System;
|
|
using System.Data;
|
|
using NPOI.OpenXmlFormats.Dml.Chart;
|
|
|
|
|
|
namespace HRM.DA
|
|
{
|
|
public class ObjectiveDA
|
|
{
|
|
internal static IDataReader Get(TransactionContext tc)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Objective");
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, int id)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Objective WHERE ObjectiveID = %n", id);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static void Insert(TransactionContext tc, Objective item)
|
|
{
|
|
string sql = SQLParser.MakeSQL(
|
|
"Insert Into Objective(ObjectiveID, ObjectiveSetID, PMPYearID, Description, Activities, MeasuresOfSuccess, Weightages, EmployeeID, EmployeeNodeID, ObjectiveDate, LMID, LMNodeID, LMRatingID, RagingDate, MYEmployeeComments, MYLMComments, IsDraft,ObjectiveType,YEEmployeeComments, YELMComments,IsOldObjective,PriorityPercent, StartDate, EndDate ,MYAssessRating,MYLMAssessRating,MYStatus,YEStatus,KPIType,yeempmark,yelmmark,GroupID,Tier, Title, MASTERKPIID) " +
|
|
"Values(%n, %n, %n, %s, %s, %s, %n, %n, %n, %d, %n, %n, %n, %d, %s, %s, %b,%n,%s,%s, %b,%n, %d, %d,%n,%n,%n,%n,%n,%n,%n,%n,%s,%s, %n)",
|
|
item.ID, DataReader.GetNullValue(item.ObjectiveSetID), DataReader.GetNullValue(item.PMPYearID),
|
|
item.Description, item.Activities, item.MeasuresOfSuccess, item.Weightages,
|
|
DataReader.GetNullValue(item.EmployeeID), DataReader.GetNullValue(item.EmployeeNodeID),
|
|
item.ObjectiveDate, DataReader.GetNullValue(item.LMID), DataReader.GetNullValue(item.LMNodeID),
|
|
item.LMRatingID, item.RagingDate, item.MYEmployeeComments, item.MYLMComments, item.IsDraft,
|
|
(int)item.ObjectiveType, item.YEEmployeeComments, item.YELMComments, item.IsOldObjective,
|
|
item.PriorityPercent, item.StartDate, item.EndDate,
|
|
item.MYAssessRating, item.MYLMAssessRating, (int)item.MYStatus, (int)item.YEStatus, item.KPIType,
|
|
item.YEEmpMark, item.YELMMark, DataReader.GetNullValue(item.GroupID), item.Tier, item.Title, item.MasterKpiId);
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
internal static void Update(TransactionContext tc, Objective item)
|
|
{
|
|
//string sql = SQLParser.MakeSQL("Update Objective Set ObjectiveSetID = %n, PMPYearID = %n, Description = %s, Activities = %s, MeasuresOfSuccess = %s, Weightages = %n, EmployeeID = %n, EmployeeNodeID = %n, ObjectiveDate = %d, LMID = %n, LMNodeID = %n, LMRatingID = %n, RagingDate = %d, EmployeeComments= %s, LMComments = %s, IsDraft = %b where ObjectiveID= %n",
|
|
// item.ObjectiveSetID, item.PMPYearID, item.Description, item.Activities, item.MeasuresOfSuccess, item.Weightages, item.EmployeeID, item.EmployeeNodeID, item.ObjectiveDate, item.LMID, item.LMNodeID, item.LMRatingID, item.RagingDate, item.EmployeeComments, item.LMComments, item.IsDraft, item.ID);
|
|
string sql = SQLParser.MakeSQL(
|
|
@"Update Objective Set ObjectiveSetID = %n, PMPYearID = %n, Description = %s, Activities = %s, MeasuresOfSuccess = %s, Weightages = %n, EmployeeID = %n, EmployeeNodeID = %n, ObjectiveDate = %d, LMID = %n, LMNodeID = %n, LMRatingID = %n, RagingDate = %d, ObjComment = %s, MYEmployeeComments= %s, MYLMComments = %s, IsDraft = %b,ObjectiveType=%n,YEEmployeeComments=%s, YELMComments=%s,PriorityPercent=%n,MYAssessRating=%n,MYLMAssessRating=%n,MYStatus=%n,YEStatus=%n,
|
|
YEEmpMark = %n, YELMMark =%n, KPIType =%n, StartDate =%d, EndDate =%d, GroupID = %n, Tier =%s, title = %s, MASTERKPIID = %n, YEASSESSRATING=%n, YELMASSESSRATING=%n
|
|
where ObjectiveID= %n",
|
|
DataReader.GetNullValue(item.ObjectiveSetID), item.PMPYearID, item.Description, item.Activities,
|
|
item.MeasuresOfSuccess, item.Weightages, item.EmployeeID, DataReader.GetNullValue(item.EmployeeNodeID),
|
|
item.ObjectiveDate, DataReader.GetNullValue(item.LMID), DataReader.GetNullValue(item.LMNodeID),
|
|
item.LMRatingID, item.RagingDate, item.ObjComment, item.MYEmployeeComments, item.MYLMComments,
|
|
item.IsDraft, (int)item.ObjectiveType, item.YEEmployeeComments, item.YELMComments, item.PriorityPercent,
|
|
item.MYAssessRating, item.MYLMAssessRating, (int)item.MYStatus, (int)item.YEStatus, item.YEEmpMark,
|
|
item.YELMMark, item.KPIType, item.StartDate, item.EndDate, DataReader.GetNullValue(item.GroupID),
|
|
item.Tier, item.Title, item.MasterKpiId, item.YEAssessRating, item.YELMAssessRating, item.ID);
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
internal static void Delete(TransactionContext tc, int id)
|
|
{
|
|
string sql = SQLParser.MakeSQL("Delete From Objective Where ObjectiveID = %n", id);
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
internal static void DeleteAllCompanyObj(TransactionContext tc, int yearID)
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL(
|
|
"delete From Objective WHERE PMPYEARID=%n and Objectivetype=(SELECT objectivetypeid FROM OBJECTIVETYPE WHERE iscompanyObjective=1)",
|
|
yearID);
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
internal static IDataReader GetDevelopmentPlans(TransactionContext tc, int iD)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM DevelopmentPlan WHERE ObjectiveID = %n", iD);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetLMObjectives(TransactionContext tc, int iD)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM LMObjective WHERE ParentID = %n", iD);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetObjectiveEmployees(TransactionContext tc, int iD)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveEmployees WHERE ObjectiveID = %n", iD);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetByEmployeeID(TransactionContext tc, int Empid, int PMPID, bool Isdrafted)
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL("SELECT * FROM Objective WHERE EmployeeID = %n AND pmpyearid=%n AND IsDraft=%b order by OBJECTIVETYPE",
|
|
Empid, PMPID, Isdrafted);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetForValidation(TransactionContext tc, int empId, int pmpYearId,
|
|
int objectiveTypeId)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"SELECT * FROM OBJECTIVE WHERE employeeid=%n AND PMPYEARID=%n AND OBJECTIVETYPE=%n", empId, pmpYearId,
|
|
objectiveTypeId);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetByEmployeeID(TransactionContext tc, string Empids, int PMPID, bool Isdrafted)
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL("SELECT * FROM Objective WHERE EmployeeID in(%q) AND pmpyearid=%n AND IsDraft=%b",
|
|
Empids, PMPID, Isdrafted);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetLMObjs(TransactionContext tc, int Empid, int PMPYID, int nLMID)
|
|
{
|
|
string sql = SQLParser.MakeSQL(
|
|
@"SELECT * FROM Objective o,objectiveset oset WHERE oset.EmployeeID=%n AND o.PMPYearID=%n AND ObjectiveID IN(
|
|
SELECT ObjectiveID FROM ObjectiveEmployees oe WHERE employeeid=%n AND oe.ObjectiveID IN(SELECT ObjectiveID FROM Objective o WHERE EmployeeID=%n AND o.PMPYearID=%n))AND o.ObjectiveSetID=oset.ObjectiveSetID AND oset.OBLMComplete IN(1,8)",
|
|
nLMID, PMPYID, Empid, nLMID, PMPYID);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetByPMPYearID(TransactionContext tc, int id, string InEmpSQL)
|
|
{
|
|
if (InEmpSQL == "")
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Objective WHERE PMPYearID = %n", id);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
else
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Objective WHERE PMPYearID = %n and EmployeeID IN (%q)",
|
|
id, InEmpSQL);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
}
|
|
|
|
internal static IDataReader GetByStatus(TransactionContext tc, EnumPMPStatus estatus, bool IsFinalized)
|
|
{
|
|
string sql =
|
|
SQLParser.MakeSQL(
|
|
"SELECT * FROM Objective WHERE pmpyearid in(Select pmpprocessid from PMP_Process where Pmpstatus = %n and IsFinalize=%b)",
|
|
(int)estatus, IsFinalized);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static void DeleteLMObjective(TransactionContext tc, int id)
|
|
{
|
|
string sql = SQLParser.MakeSQL("Delete From LMObjective Where ParentID = %n", id);
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
internal static void DeleteDevelopmentPlan(TransactionContext tc, int id)
|
|
{
|
|
string sql = SQLParser.MakeSQL("Delete From DevelopmentPlan Where ObjectiveID = %n", id);
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
internal static void DeleteObjectiveEmployees(TransactionContext tc, int id)
|
|
{
|
|
string sql = SQLParser.MakeSQL("Delete From ObjectiveEmployees Where ObjectiveID = %n", id);
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
internal static IDataReader GetByLMID(TransactionContext tc, int id)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Objective WHERE EmployeeID = %n", id);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetDraftObjectives(TransactionContext tc, int id, int draft)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Objective WHERE IsDraft = %n AND EmployeeID = %n", draft, id);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static IDataReader GetLineManager(TransactionContext tc, int id)
|
|
{
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Objective WHERE LMID = %n", id);
|
|
return tc.ExecuteReader(sql);
|
|
}
|
|
|
|
internal static DataSet GetComObjAssignedEmpID(TransactionContext tc, int PMPYearID)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"SELECT distinct o.EMPLOYEEID
|
|
FROM OBJECTIVE o
|
|
LEFT JOIN OBJECTIVETYPE ot ON o.OBJECTIVETYPE = ot.OBJECTIVETYPEID
|
|
WHERE ot.ISCOMPANYOBJECTIVE = 1 and o.PMPYEARID = %n", PMPYearID);
|
|
return tc.ExecuteDataSet(sql);
|
|
}
|
|
internal static DataSet GetPmsDataEmployeeWise(TransactionContext tc, int pmpYearId, string empIds)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
try
|
|
{
|
|
string sql = SQLParser.MakeSQL(
|
|
"SELECT e.EMPLOYEEID AS EmployeeId, e.EMPLOYEENO AS EmployeeNo, e.NAME AS EmployeeName, d.DESCRIPTION AS Department, " +
|
|
"deig.NAME AS Designation, o.TITLE AS 'kpiTitle', o.DESCRIPTION AS 'kpiDescription'," +
|
|
"o.WEIGHTAGES,o.MYLMASSESSRATING AS MidYearRating, o.YELMMARK AS FinalRating " +
|
|
"FROM OBJECTIVE o " +
|
|
"LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = o.EMPLOYEEID " +
|
|
"LEFT JOIN DEPARTMENT d ON e.DEPARTMENTID = d.DEPARTMENTID " +
|
|
"LEFT JOIN DESIGNATION deig ON e.DESIGNATIONID = deig.DESIGNATIONID " +
|
|
"WHERE o.PMPYEARID = %n " +
|
|
"AND o.OBJECTIVETYPE = 1 " +
|
|
"AND o.employeeId in (%q) ", pmpYearId, empIds);
|
|
ds = tc.ExecuteDataSet(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
|
|
internal static DataSet GetPmsLmGoalReport(TransactionContext tc, int pmpYearId, int empId)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
try
|
|
{
|
|
string sql1 = SQLParser.MakeSQL(
|
|
"SELECT e.EMPLOYEEID AS EmployeeId, e.EMPLOYEENO AS EmployeeNo, e.NAME AS EmployeeName, d.DESCRIPTION AS Department, " +
|
|
"deig.NAME AS Designation,o.TITLE AS Theme, o.DESCRIPTION AS Goal, o.targets AS Target, o.Scale AS Scale, o.MEASURESOFSUCCESS " +
|
|
"AS Measure, o.WEIGHTAGES,o.MYLMASSESSRATING AS MidYearRating, o.YELMMARK AS FinalRating " +
|
|
"FROM OBJECTIVE o " +
|
|
"LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = o.EMPLOYEEID " +
|
|
"LEFT JOIN DEPARTMENT d ON e.DEPARTMENTID = d.DEPARTMENTID " +
|
|
"LEFT JOIN DESIGNATION deig ON e.DESIGNATIONID = deig.DESIGNATIONID " +
|
|
"WHERE o.PMPYEARID = %n " +
|
|
"AND o.OBJECTIVETYPE = 1 " +
|
|
"AND e.employeeid IN (SELECT employeeid FROM EMPLOYEE WHERE LINEMANAGERID IN" +
|
|
"(SELECT employeeid FROM EMPLOYEE WHERE linemanagerId = %n)) ", pmpYearId, empId);
|
|
DataTable dataTable = tc.ExecuteDataSet(sql1).Tables[0];
|
|
dataTable.TableName = "SubordinateTeam";
|
|
ds.Tables.Add(dataTable.Copy());
|
|
|
|
|
|
string sql2 = SQLParser.MakeSQL(
|
|
"SELECT e.EMPLOYEEID AS EmployeeId, e.EMPLOYEENO AS EmployeeNo, e.NAME AS EmployeeName, d.DESCRIPTION AS Department, " +
|
|
"deig.NAME AS Designation,o.TITLE AS Theme, o.DESCRIPTION AS Goal, o.targets AS Target, o.Scale AS Scale, o.MEASURESOFSUCCESS " +
|
|
"AS Measure, o.WEIGHTAGES,o.MYLMASSESSRATING AS MidYearRating, o.YELMMARK AS FinalRating " +
|
|
"FROM OBJECTIVE o " +
|
|
"LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = o.EMPLOYEEID " +
|
|
"LEFT JOIN DEPARTMENT d ON e.DEPARTMENTID = d.DEPARTMENTID " +
|
|
"LEFT JOIN DESIGNATION deig ON e.DESIGNATIONID = deig.DESIGNATIONID " +
|
|
"WHERE o.PMPYEARID = %n " +
|
|
"AND o.OBJECTIVETYPE = 1 " +
|
|
"AND e.LINEMANAGERID = %n ", pmpYearId, empId);
|
|
dataTable = tc.ExecuteDataSet(sql2).Tables[0];
|
|
dataTable.TableName = "ImmediateSubordinates";
|
|
ds.Tables.Add(dataTable.Copy());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
|
|
internal static DataSet GetPmsIndividualGoalData(TransactionContext tc, int pmpYearId, int empId)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
try
|
|
{
|
|
string sql1 = SQLParser.MakeSQL(
|
|
"SELECT e.EMPLOYEEID AS EmployeeId, e.EMPLOYEENO AS EmployeeNo, e.NAME AS EmployeeName, d.DESCRIPTION AS Department, " +
|
|
"deig.NAME AS Designation,o.TITLE AS Theme, o.DESCRIPTION AS Goal, o.targets AS Target, o.Scale AS Scale, o.MEASURESOFSUCCESS " +
|
|
"AS Measure, o.WEIGHTAGES,o.MYLMASSESSRATING AS MidYearRating, o.YELMMARK AS FinalRating " +
|
|
"FROM OBJECTIVE o " +
|
|
"LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = o.EMPLOYEEID " +
|
|
"LEFT JOIN DEPARTMENT d ON e.DEPARTMENTID = d.DEPARTMENTID " +
|
|
"LEFT JOIN DESIGNATION deig ON e.DESIGNATIONID = deig.DESIGNATIONID " +
|
|
"WHERE o.PMPYEARID = %n " +
|
|
"AND o.OBJECTIVETYPE = 1 " +
|
|
"AND e.employeeId = %n ", pmpYearId, empId);
|
|
DataTable dataTable = tc.ExecuteDataSet(sql1).Tables[0];
|
|
dataTable.TableName = "IndividualData";
|
|
ds.Tables.Add(dataTable.Copy());
|
|
string sql2 = SQLParser.MakeSQL(
|
|
"SELECT e.EMPLOYEEID AS EmployeeId, e.EMPLOYEENO AS EmployeeNo, e.NAME AS EmployeeName, d.DESCRIPTION AS Department, " +
|
|
"deig.NAME AS Designation FROM EMPLOYEE e " +
|
|
"LEFT JOIN DEPARTMENT d ON e.DEPARTMENTID = d.DEPARTMENTID " +
|
|
"LEFT JOIN DESIGNATION deig ON e.DESIGNATIONID = deig.DESIGNATIONID " +
|
|
"WHERE e.employeeid = %n", empId);
|
|
dataTable = tc.ExecuteDataSet(sql2).Tables[0];
|
|
dataTable.TableName = "EmployeeInfo";
|
|
ds.Tables.Add(dataTable.Copy());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
internal static DataSet getPMSReportData(TransactionContext tc, int pmpYearID, int pmsReportType, string emps)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
try
|
|
{
|
|
string sql = "";
|
|
switch (pmsReportType)
|
|
{
|
|
case 1:
|
|
sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO AS EmployeeID, e.NAME AS Name, ot.NAME AS ObjectiveType, o.WEIGHTAGES AS Weightage, o.TITLE AS Title, o.DESCRIPTION AS DESCRIPTION,
|
|
o.MEASURESOFSUCCESS AS Threshold, FORMAT(o.STARTDATE, 'dd MMM yyyy') AS StartDate, FORMAT(o.ENDDATE, 'dd MMM yyyy') AS EndDate,
|
|
o.MYEMPLOYEECOMMENTS AS MidYearEmployeeComments, o.MYASSESSRATING AS EmployeeMidYearAssessment,
|
|
o.MYLMCOMMENTS AS MidYearLineManagerComments, o.MYLMASSESSRATING AS LineManagerMidYearAssessment,
|
|
o.YEEMPMARK AS YearEndEmployeeMark, o.YEASSESSRATING AS YearEndEmployeeRating, o.YEEMPLOYEECOMMENTS AS YearEndEmployeeComments,
|
|
o.YELMMARK AS YearEndLineManagerMark, o.YELMASSESSRATING AS YearEndLMRating, o.YELMCOMMENTS AS YearEndLMComments
|
|
FROM OBJECTIVE o
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID = o.EMPLOYEEID
|
|
INNER JOIN OBJECTIVETYPE ot ON ot.OBJECTIVETYPEID = o.OBJECTIVETYPE
|
|
WHERE o.PMPYEARID = %n AND o.EMPLOYEEID IN (%q)", pmpYearID, emps);
|
|
break;
|
|
case 2:
|
|
sql = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO, E.NAME, OS.YETOTALOBJECTIVEEMPMARK AS EmpObjectivePercent, ROUND(OS.EMPOBJRATING,2) AS EmpObjectiveRating,
|
|
ROUND(OS.EMPVALUERATING,2) AS EmpValueRating, ROUND(OS.EMPOVERALLRATING,2) AS EmpRating,
|
|
OS.YETOTALOBJECTIVELMMARK AS LmObjectivePercent,ROUND(OS.LMOBJRATING,2) AS LmObjectiveRating,
|
|
ROUND(OS.LMVALUERATING,2) AS LmValueRating, ROUND(OS.LMOVERALLRATING,2) AS LMRating, ROUND(OS.SECONDLMOVERALLRATING,2) AS SecondLmRating
|
|
FROM OBJECTIVESET OS
|
|
INNER JOIN EMPLOYEE E ON E.EMPLOYEEID = OS.EMPLOYEEID
|
|
WHERE os.PMPYEARID = %n AND os.EMPLOYEEID IN (%q)", pmpYearID, emps);
|
|
break;
|
|
default:
|
|
break;
|
|
}
|
|
ds = tc.ExecuteDataSet(sql);
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
} |