569 lines
46 KiB
C#
569 lines
46 KiB
C#
|
using HRM.BO;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using System;
|
|||
|
|
using System.Data;
|
|||
|
|
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
public class ObjectiveSetDA
|
|||
|
|
{
|
|||
|
|
internal static IDataReader Get(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSet");
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSet WHERE ObjectiveSetID = %n", id);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetSubordinatesAppraisalRating(TransactionContext tc, int pmpYearId, string InEmpSQL)
|
|||
|
|
{
|
|||
|
|
// string sql = SQLParser.MakeSQL(@"SELECT Subs.EMPLOYEENO CIF, subs.NAME, SubDesig.NAME Designation, SubDept.DESCRIPTION Department,
|
|||
|
|
// (CASE SubDept.TIRE WHEN 4 THEN SubDept1.DESCRIPTION WHEN 3 then SubDept2.DESCRIPTION
|
|||
|
|
// WHEN 2 THEN SubDept3.DESCRIPTION WHEN 1 THEN SubDept.DESCRIPTION ELSE '' END) Division, SubLoc.DESCRIPTION Branch,
|
|||
|
|
// LM.EMPLOYEENO LMCIF, LM.NAME LMName, LMDesig.NAME LMDesignation, Subs.JOININGDATE,
|
|||
|
|
// ObjSet.LMVALUERATING HowRating,
|
|||
|
|
// objSet.LMOBJRATING WhatRating,
|
|||
|
|
// objSet.LMOVERALLRATING CombinedRating
|
|||
|
|
// From Employee Subs
|
|||
|
|
// LEFT OUTER JOIN DESIGNATION SubDesig on SubDesig.DESIGNATIONID = Subs.DESIGNATIONID
|
|||
|
|
// LEFT OUTER JOIN DEPARTMENT SubDept on SubDept.DEPARTMENTID = Subs.DEPARTMENTID
|
|||
|
|
// LEFT OUTER JOIN DEPARTMENT SubDept3 ON SubDept3.DEPARTMENTID = SubDept.PARENTID
|
|||
|
|
// LEFT OUTER JOIN DEPARTMENT SubDept2 ON SubDept2.DEPARTMENTID = SubDept3.PARENTID
|
|||
|
|
// LEFT OUTER JOIN DEPARTMENT SubDept1 ON SubDept1.DEPARTMENTID = SubDept2.PARENTID
|
|||
|
|
// LEFT OUTER JOIN LOCATION SubLoc on SubLoc.LOCATIONID = Subs.LOCATIONID
|
|||
|
|
// LEFT OUTER JOIN ORGANEMPLOYEE orge ON orge.EMPLOYEEID = Subs.EMPLOYEEID
|
|||
|
|
// LEFT OUTER JOIN ORGANOGRAM org ON org.ORGANOGRAMID = orge.NODEID
|
|||
|
|
// LEFT OUTER JOIN ORGANEMPLOYEE orgel ON orgel.NODEID = org.PARENTID
|
|||
|
|
// LEFT OUTER JOIN EMPLOYEE LM ON LM.EMPLOYEEID = orgel.EMPLOYEEID
|
|||
|
|
// LEFT OUTER JOIN DESIGNATION LMDesig on LMDesig.DESIGNATIONID = LM.DESIGNATIONID
|
|||
|
|
// LEFT OUTER JOIN OBJECTIVESET ObjSet ON ObjSet.EMPLOYEEID = Subs.EMPLOYEEID
|
|||
|
|
// LEFT OUTER JOIN OBJECTIVESET PObjSet ON PObjSet.EMPLOYEEID = Subs.EMPLOYEEID And PObjSet.PMPYearId = (ObjSet.PMPYearId - 1)
|
|||
|
|
// LEFT OUTER JOIN OBJECTIVESET CPObjSet ON CPObjSet.EMPLOYEEID = Subs.EMPLOYEEID And CPObjSet.PMPYearId = (ObjSet.PMPYearId - 2)
|
|||
|
|
// Where Subs.EmployeeID IN (%q) AND ObjSet.PMPYEARID = %n", InEmpSQL, pmpYearId);
|
|||
|
|
|
|||
|
|
|
|||
|
|
string sql = SQLParser.MakeSQL(
|
|||
|
|
@"SELECT Subs.EMPLOYEENO CIF, subs.NAME, SubDesig.NAME Designation, SubDept.DESCRIPTION Department,
|
|||
|
|
(CASE SubDept.TIRE WHEN 4 THEN SubDept1.DESCRIPTION WHEN 3 then SubDept2.DESCRIPTION
|
|||
|
|
WHEN 2 THEN SubDept3.DESCRIPTION WHEN 1 THEN SubDept.DESCRIPTION ELSE '' END) Division, SubLoc.DESCRIPTION Branch,
|
|||
|
|
LM.EMPLOYEENO LMCIF, LM.NAME LMName, LMDesig.NAME LMDesignation, Subs.JOININGDATE,
|
|||
|
|
|
|||
|
|
CASE WHEN
|
|||
|
|
ISNULL(PObjSet.LMObjRating, 0)=1 AND ISNULL(PObjSet.LMValueRating, 0)=1 THEN 'G1L1' WHEN ISNULL(PObjSet.LMObjRating, 0)=1 AND ISNULL(PObjSet.LMValueRating, 0)=2
|
|||
|
|
THEN 'G1L2' WHEN ISNULL(PObjSet.LMObjRating, 0)=1 AND ISNULL(PObjSet.LMValueRating, 0)=3 THEN 'G1L3' WHEN ISNULL(PObjSet.LMObjRating, 0)=1 AND
|
|||
|
|
ISNULL(PObjSet.LMValueRating, 0)=4 THEN 'G1L4' WHEN ISNULL(PObjSet.LMObjRating, 0)=1 AND ISNULL(PObjSet.LMValueRating, 0)=5 THEN 'G1L5' WHEN
|
|||
|
|
ISNULL(PObjSet.LMObjRating, 0)=2 AND ISNULL(PObjSet.LMValueRating, 0)=1 THEN 'G2L1' WHEN ISNULL(PObjSet.LMObjRating, 0)=2 AND
|
|||
|
|
ISNULL(PObjSet.LMValueRating, 0)=2 THEN 'G2L2' WHEN ISNULL(PObjSet.LMObjRating, 0)=2 AND ISNULL(PObjSet.LMValueRating, 0)=3 THEN 'G2L3' WHEN
|
|||
|
|
ISNULL(PObjSet.LMObjRating, 0)=2 AND ISNULL(PObjSet.LMValueRating, 0)=4 THEN 'G2L4' WHEN ISNULL(PObjSet.LMObjRating, 0)=2 AND ISNULL(PObjSet.LMValueRating, 0)=5
|
|||
|
|
THEN 'G2L5' WHEN ISNULL(PObjSet.LMObjRating, 0)=3 AND ISNULL(PObjSet.LMValueRating, 0)=1 THEN 'G3L1' WHEN ISNULL(PObjSet.LMObjRating, 0)=3 AND
|
|||
|
|
ISNULL(PObjSet.LMValueRating, 0)=2 THEN 'G3L2' WHEN ISNULL(PObjSet.LMObjRating, 0)=3 AND ISNULL(PObjSet.LMValueRating, 0)=3 THEN 'G3L3' WHEN
|
|||
|
|
ISNULL(PObjSet.LMObjRating, 0)=3 AND ISNULL(PObjSet.LMValueRating, 0)=4 THEN 'G3L4' WHEN ISNULL(PObjSet.LMObjRating, 0)=3 AND
|
|||
|
|
ISNULL(PObjSet.LMValueRating, 0)=5 THEN 'G3L5' WHEN ISNULL(PObjSet.LMObjRating, 0)=4 AND ISNULL(PObjSet.LMValueRating, 0)=1 THEN 'G4L1' WHEN
|
|||
|
|
ISNULL(PObjSet.LMObjRating, 0)=4 AND ISNULL(PObjSet.LMValueRating, 0)=2 THEN 'G4L2' WHEN ISNULL(PObjSet.LMObjRating, 0)=4 AND
|
|||
|
|
ISNULL(PObjSet.LMValueRating, 0)=3 THEN 'G4L3' WHEN ISNULL(PObjSet.LMObjRating, 0)=4 AND ISNULL(PObjSet.LMValueRating, 0)=4 THEN 'G4L4' WHEN
|
|||
|
|
ISNULL(PObjSet.LMObjRating, 0)=4 AND ISNULL(PObjSet.LMValueRating, 0)=5 THEN 'G4L5' WHEN ISNULL(PObjSet.LMObjRating, 0)=5 AND
|
|||
|
|
ISNULL(PObjSet.LMValueRating, 0)=1 THEN 'G5L1' WHEN ISNULL(PObjSet.LMObjRating, 0)=5 AND ISNULL(PObjSet.LMValueRating, 0)=2 THEN 'G5L2' WHEN
|
|||
|
|
ISNULL(PObjSet.LMObjRating, 0)=5 AND ISNULL(PObjSet.LMValueRating, 0)=3 THEN 'G5L3' WHEN ISNULL(PObjSet.LMObjRating, 0)=5 AND
|
|||
|
|
ISNULL(PObjSet.LMValueRating, 0)=4 THEN 'G5L4' WHEN ISNULL(PObjSet.LMObjRating, 0)=5 AND ISNULL(PObjSet.LMValueRating, 0)=5 THEN 'G5L5' ELSE '0' END AS
|
|||
|
|
'Performance matrix Rating Last year (How+What)',
|
|||
|
|
|
|||
|
|
CASE ISNULL(ObjSet.EMPObjRating, 0) WHEN 1 THEN 'G1' WHEN 2 THEN 'G2' WHEN 3 THEN 'G3' WHEN 4 THEN 'G4' WHEN 5 THEN 'G5' ELSE '0' END AS 'Employee (What-G)Rating', CASE ISNULL(ObjSet.EMPValueRating, 0) WHEN 1 THEN 'L1' WHEN 2 THEN 'L2' WHEN 3 THEN 'L3' WHEN 4 THEN 'L4' WHEN 5 THEN 'L5' ELSE '0' END AS 'Employee (How- L)Rating',
|
|||
|
|
CASE WHEN ISNULL(ObjSet.EMPObjRating, 0)=1 AND ISNULL(ObjSet.EMPValueRating, 0)=1 THEN 'G1L1' WHEN ISNULL(ObjSet.EMPObjRating, 0)=1 AND ISNULL(ObjSet.EMPValueRating, 0)=2 THEN 'G1L2' WHEN ISNULL(ObjSet.EMPObjRating, 0)=1 AND ISNULL(ObjSet.EMPValueRating, 0)=3 THEN 'G1L3' WHEN ISNULL(ObjSet.EMPObjRating, 0)=1 AND ISNULL(ObjSet.EMPValueRating, 0)=4 THEN 'G1L4' WHEN ISNULL(ObjSet.EMPObjRating, 0)=1 AND ISNULL(ObjSet.EMPValueRating, 0)=5 THEN 'G1L5' WHEN ISNULL(ObjSet.EMPObjRating, 0)=2 AND ISNULL(ObjSet.EMPValueRating, 0)=1 THEN 'G2L1' WHEN ISNULL(ObjSet.EMPObjRating, 0)=2 AND ISNULL(ObjSet.EMPValueRating, 0)=2 THEN 'G2L2' WHEN ISNULL(ObjSet.EMPObjRating, 0)=2 AND ISNULL(ObjSet.EMPValueRating, 0)=3 THEN 'G2L3' WHEN ISNULL(ObjSet.EMPObjRating, 0)=2 AND ISNULL(ObjSet.EMPValueRating, 0)=4 THEN 'G2L4' WHEN ISNULL(ObjSet.EMPObjRating, 0)=2 AND ISNULL(ObjSet.EMPValueRating, 0)=5 THEN 'G2L5' WHEN ISNULL(ObjSet.EMPObjRating, 0)=3 AND ISNULL(ObjSet.EMPValueRating, 0)=1 THEN 'G3L1' WHEN ISNULL(ObjSet.EMPObjRating, 0)=3 AND ISNULL(ObjSet.EMPValueRating, 0)=2 THEN 'G3L2' WHEN ISNULL(ObjSet.EMPObjRating, 0)=3 AND ISNULL(ObjSet.EMPValueRating, 0)=3 THEN 'G3L3' WHEN ISNULL(ObjSet.EMPObjRating, 0)=3 AND ISNULL(ObjSet.EMPValueRating, 0)=4 THEN 'G3L4' WHEN ISNULL(ObjSet.EMPObjRating, 0)=3 AND ISNULL(ObjSet.EMPValueRating, 0)=5 THEN 'G3L5' WHEN ISNULL(ObjSet.EMPObjRating, 0)=4 AND ISNULL(ObjSet.EMPValueRating, 0)=1 THEN 'G4L1' WHEN ISNULL(ObjSet.EMPObjRating, 0)=4 AND ISNULL(ObjSet.EMPValueRating, 0)=2 THEN 'G4L2' WHEN ISNULL(ObjSet.EMPObjRating, 0)=4 AND ISNULL(ObjSet.EMPValueRating, 0)=3 THEN 'G4L3' WHEN ISNULL(ObjSet.EMPObjRating, 0)=4 AND ISNULL(ObjSet.EMPValueRating, 0)=4 THEN 'G4L4' WHEN ISNULL(ObjSet.EMPObjRating, 0)=4 AND ISNULL(ObjSet.EMPValueRating, 0)=5 THEN 'G4L5' WHEN ISNULL(ObjSet.EMPObjRating, 0)=5 AND ISNULL(ObjSet.EMPValueRating, 0)=1 THEN 'G5L1' WHEN ISNULL(ObjSet.EMPObjRating, 0)=5 AND ISNULL(ObjSet.EMPValueRating, 0)=2 THEN 'G5L2' WHEN ISNULL(ObjSet.EMPObjRating, 0)=5 AND ISNULL(ObjSet.EMPValueRating, 0)=3 THEN 'G5L3' WHEN ISNULL(ObjSet.EMPObjRating, 0)=5 AND ISNULL(ObjSet.EMPValueRating, 0)=4 THEN 'G5L4' WHEN ISNULL(ObjSet.EMPObjRating, 0)=5 AND ISNULL(ObjSet.EMPValueRating, 0)=5 THEN 'G5L5' ELSE '0' END AS 'Employee Performance matrix Rating (How+What)',
|
|||
|
|
CASE ISNULL(ObjSet.LMObjRating, 0) WHEN 1 THEN 'G1' WHEN 2 THEN 'G2' WHEN 3 THEN 'G3' WHEN 4 THEN 'G4' WHEN 5 THEN 'G5' ELSE '0' END AS 'Line Manager (What-G) Rating', CASE ISNULL(ObjSet.LMValueRating, 0) WHEN 1 THEN 'L1' WHEN 2 THEN 'L2' WHEN 3 THEN 'L3' WHEN 4 THEN 'L4' WHEN 5 THEN 'L5' ELSE '0' END AS 'Line Manager (How-L) Rating',
|
|||
|
|
CASE WHEN ISNULL(ObjSet.LMObjRating, 0)=1 AND ISNULL(ObjSet.LMValueRating, 0)=1 THEN 'G1L1' WHEN ISNULL(ObjSet.LMObjRating, 0)=1 AND ISNULL(ObjSet.LMValueRating, 0)=2 THEN 'G1L2' WHEN ISNULL(ObjSet.LMObjRating, 0)=1 AND ISNULL(ObjSet.LMValueRating, 0)=3 THEN 'G1L3' WHEN ISNULL(ObjSet.LMObjRating, 0)=1 AND ISNULL(ObjSet.LMValueRating, 0)=4 THEN 'G1L4' WHEN ISNULL(ObjSet.LMObjRating, 0)=1 AND ISNULL(ObjSet.LMValueRating, 0)=5 THEN 'G1L5' WHEN ISNULL(ObjSet.LMObjRating, 0)=2 AND ISNULL(ObjSet.LMValueRating, 0)=1 THEN 'G2L1' WHEN ISNULL(ObjSet.LMObjRating, 0)=2 AND ISNULL(ObjSet.LMValueRating, 0)=2 THEN 'G2L2' WHEN ISNULL(ObjSet.LMObjRating, 0)=2 AND ISNULL(ObjSet.LMValueRating, 0)=3 THEN 'G2L3' WHEN ISNULL(ObjSet.LMObjRating, 0)=2 AND ISNULL(ObjSet.LMValueRating, 0)=4 THEN 'G2L4' WHEN ISNULL(ObjSet.LMObjRating, 0)=2 AND ISNULL(ObjSet.LMValueRating, 0)=5 THEN 'G2L5' WHEN ISNULL(ObjSet.LMObjRating, 0)=3 AND ISNULL(ObjSet.LMValueRating, 0)=1 THEN 'G3L1' WHEN ISNULL(ObjSet.LMObjRating, 0)=3 AND ISNULL(ObjSet.LMValueRating, 0)=2 THEN 'G3L2' WHEN ISNULL(ObjSet.LMObjRating, 0)=3 AND ISNULL(ObjSet.LMValueRating, 0)=3 THEN 'G3L3' WHEN ISNULL(ObjSet.LMObjRating, 0)=3 AND ISNULL(ObjSet.LMValueRating, 0)=4 THEN 'G3L4' WHEN ISNULL(ObjSet.LMObjRating, 0)=3 AND ISNULL(ObjSet.LMValueRating, 0)=5 THEN 'G3L5' WHEN ISNULL(ObjSet.LMObjRating, 0)=4 AND ISNULL(ObjSet.LMValueRating, 0)=1 THEN 'G4L1' WHEN ISNULL(ObjSet.LMObjRating, 0)=4 AND ISNULL(ObjSet.LMValueRating, 0)=2 THEN 'G4L2' WHEN ISNULL(ObjSet.LMObjRating, 0)=4 AND ISNULL(ObjSet.LMValueRating, 0)=3 THEN 'G4L3' WHEN ISNULL(ObjSet.LMObjRating, 0)=4 AND ISNULL(ObjSet.LMValueRating, 0)=4 THEN 'G4L4' WHEN ISNULL(ObjSet.LMObjRating, 0)=4 AND ISNULL(ObjSet.LMValueRating, 0)=5 THEN 'G4L5' WHEN ISNULL(ObjSet.LMObjRating, 0)=5 AND ISNULL(ObjSet.LMValueRating, 0)=1 THEN 'G5L1' WHEN ISNULL(ObjSet.LMObjRating, 0)=5 AND ISNULL(ObjSet.LMValueRating, 0)=2 THEN 'G5L2' WHEN ISNULL(ObjSet.LMObjRating, 0)=5 AND ISNULL(ObjSet.LMValueRating, 0)=3 THEN 'G5L3' WHEN ISNULL(ObjSet.LMObjRating, 0)=5 AND ISNULL(ObjSet.LMValueRating, 0)=4 THEN 'G5L4' WHEN ISNULL(ObjSet.LMObjRating, 0)=5 AND ISNULL(ObjSet.LMValueRating, 0)=5 THEN 'G5L5' ELSE '0' END AS 'Performance matrix Rating (How+What)',ISNULL(ObjSet.AchivementPercent, 0) 'Achivement Percent'
|
|||
|
|
From Employee Subs
|
|||
|
|
LEFT OUTER JOIN DESIGNATION SubDesig on SubDesig.DESIGNATIONID = Subs.DESIGNATIONID
|
|||
|
|
LEFT OUTER JOIN DEPARTMENT SubDept on SubDept.DEPARTMENTID = Subs.DEPARTMENTID
|
|||
|
|
LEFT OUTER JOIN DEPARTMENT SubDept3 ON SubDept3.DEPARTMENTID = SubDept.PARENTID
|
|||
|
|
LEFT OUTER JOIN DEPARTMENT SubDept2 ON SubDept2.DEPARTMENTID = SubDept3.PARENTID
|
|||
|
|
LEFT OUTER JOIN DEPARTMENT SubDept1 ON SubDept1.DEPARTMENTID = SubDept2.PARENTID
|
|||
|
|
LEFT OUTER JOIN LOCATION SubLoc on SubLoc.LOCATIONID = Subs.LOCATIONID
|
|||
|
|
LEFT OUTER JOIN ORGANEMPLOYEE orge ON orge.EMPLOYEEID = Subs.EMPLOYEEID
|
|||
|
|
LEFT OUTER JOIN ORGANOGRAM org ON org.ORGANOGRAMID = orge.NODEID
|
|||
|
|
LEFT OUTER JOIN ORGANEMPLOYEE orgel ON orgel.NODEID = org.PARENTID
|
|||
|
|
LEFT OUTER JOIN EMPLOYEE LM ON LM.EMPLOYEEID = orgel.EMPLOYEEID
|
|||
|
|
LEFT OUTER JOIN DESIGNATION LMDesig on LMDesig.DESIGNATIONID = LM.DESIGNATIONID
|
|||
|
|
LEFT OUTER JOIN OBJECTIVESET ObjSet ON ObjSet.EMPLOYEEID = Subs.EMPLOYEEID
|
|||
|
|
LEFT OUTER JOIN OBJECTIVESET PObjSet ON PObjSet.EMPLOYEEID = Subs.EMPLOYEEID And PObjSet.PMPYearId = (ObjSet.PMPYearId - 1)
|
|||
|
|
LEFT OUTER JOIN OBJECTIVESET CPObjSet ON CPObjSet.EMPLOYEEID = Subs.EMPLOYEEID And CPObjSet.PMPYearId = (ObjSet.PMPYearId - 2)
|
|||
|
|
Where Subs.EmployeeID IN (%q) AND ObjSet.PMPYEARID = %n", InEmpSQL,
|
|||
|
|
pmpYearId);
|
|||
|
|
return tc.ExecuteDataSet(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetSubordinatesAppraisalRatingStatus(TransactionContext tc, int pmpYearId,
|
|||
|
|
string InEmpSQL)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(
|
|||
|
|
@"SELECT Subs.EMPLOYEENO CIF, subs.NAME, SubDept.DESCRIPTION Department, SubDesig.NAME Designation, SubLoc.DESCRIPTION Branch
|
|||
|
|
From Employee Subs
|
|||
|
|
LEFT OUTER JOIN DESIGNATION SubDesig on SubDesig.DESIGNATIONID = Subs.DESIGNATIONID
|
|||
|
|
LEFT OUTER JOIN DEPARTMENT SubDept on SubDept.DEPARTMENTID = Subs.DEPARTMENTID
|
|||
|
|
LEFT OUTER JOIN LOCATION SubLoc on SubLoc.LOCATIONID = Subs.LOCATIONID
|
|||
|
|
LEFT OUTER JOIN OBJECTIVESET ObjSet ON ObjSet.EMPLOYEEID = Subs.EMPLOYEEID
|
|||
|
|
LEFT OUTER JOIN EMPKRAMASTER ek ON ek.EMPLOYEEID = Subs.EMPLOYEEID
|
|||
|
|
Where Subs.EmployeeID IN (%q) AND ObjSet.PMPYEARID = %n", InEmpSQL,
|
|||
|
|
pmpYearId);
|
|||
|
|
return tc.ExecuteDataSet(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Save(TransactionContext tc, ObjectiveSet item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(
|
|||
|
|
@"Insert Into ObjectiveSet(ObjectiveSetID, PMPYearID, EmployeeID, EmployeeNodeID, LMID, LMNodeID, OverallOBRating,
|
|||
|
|
OverallValuesRating, OBEmpComplete, OBLMComplete, MYEmpComplete, MYLMComplete, MYEmpAgreed, YEEmpComplete, YELMComplete,
|
|||
|
|
YEEmpAgreed, YEESendLMCmplete, DevelopmentPlanSubmit, LMDevelopmentPlanSubmit,AuthorizedOBEmployeeID ,AuthorizedDPEmployeeID,
|
|||
|
|
AuthorizedMYEmployeeID,AuthorizedYEEmployeeID,OverallEmpComments,OverallLMComments,OverallSecondLMComments,RevertComment,
|
|||
|
|
OverallDirectorComments,SecondLMAgreed,DirectorAgreed,IsLMRecommend ,IsSecondLMRecommend,IsDirectorRecommend,
|
|||
|
|
YEDirectorCmplete,OBEmpCompleteDate,OBLMCompleteDate,NotificationStatus, IsEditing, PreCalibrationRating, KeyPerformance, Strength, Opportunity) Values
|
|||
|
|
(%n, %n, %n, %n, %n, %n, %n, %n, %b, %n, %n, %n, %n, %n, %n, %n, %n, %n, %n,%n,%n,%n,%n,%s,%s,%s,%s,%s,%b,%b,%b,%b,%b,%n,%d,%d,%n, %n, %n, %s, %s, %s)",
|
|||
|
|
item.ID, item.PMPYearID, item.EmployeeID, item.EmployeeNodeID, DataReader.GetNullValue(item.LMID),
|
|||
|
|
DataReader.GetNullValue(item.LMNodeID), item.OverallOBRating, item.OverallValuesRating,
|
|||
|
|
item.OBEmpComplete, (int)item.OBLMComplete, (int)item.MYEmpComplete, (int)item.MYLMComplete,
|
|||
|
|
(int)item.MYEmpAgreed, (int)item.YEEmpComplete, (int)item.YELMComplete, (int)item.YEEmpAgreed,
|
|||
|
|
(int)item.YEESendLMCmplete, (int)item.DevelopmentPlanSubmit, (int)item.LMDevelopmentPlanSubmit,
|
|||
|
|
DataReader.GetNullValue(item.AuthorizedOBEmployeeID),
|
|||
|
|
DataReader.GetNullValue(item.AuthorizedDPEmployeeID),
|
|||
|
|
DataReader.GetNullValue(item.AuthorizedMYEmployeeID),
|
|||
|
|
DataReader.GetNullValue(item.AuthorizedYEEmployeeID), item.OverallEmpComments, item.OverallLMComments,
|
|||
|
|
item.OverallSecondLMComments, item.RevertComment, item.OverallDirectorComments, item.SecondLMAgreed,
|
|||
|
|
item.DirectorAgreed, item.IsLMRecommend, item.IsSecondLMRecommend, item.IsDirectorRecommend,
|
|||
|
|
item.YEDirectorCmplete, DataReader.GetNullValue(item.OBEmpCompleteDate),
|
|||
|
|
DataReader.GetNullValue(item.OBLMCompleteDate), (int)item.NotificationStatus, item.IsEditing,
|
|||
|
|
item.PreCalibrationRating, item.KeyPerformance, item.Strength, item.Opportunity);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, ObjectiveSet item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(
|
|||
|
|
@"Update ObjectiveSet Set PMPYearID = %n, EmployeeID = %n, EmployeeNodeID = %n, LMID = %n, LMNodeID = %n,EMPOBJRating = %n,
|
|||
|
|
EMPValueRating = %n, LMOBJRating = %n, LMValueRating = %n, OBEmpComplete = %b, OBLMComplete = %n, MYEmpComplete = %n, MYLMComplete = %n,
|
|||
|
|
MYEmpAgreed = %n, YEEmpComplete = %n, YELMComplete = %n, YEDHComplete = %n, YEMTComplete = %n,YECEOComplete=%n, YEEmpAgreed = %n,
|
|||
|
|
YEESendLMCmplete = %n, AuthorizedOBEmployeeID=%n ,AuthorizedDPEmployeeID=%n,AuthorizedMYEmployeeID=%n ,AuthorizedYEEmployeeID=%n,
|
|||
|
|
OverallEmpComments=%s,OverallLMComments=%s,OverallSecondLMComments=%s,RevertComment=%s,OverallDirectorComments=%s,SecondLMAgreed=%b,
|
|||
|
|
DirectorAgreed=%b,IsLMRecommend=%b,IsSecondLMRecommend=%b,IsDirectorRecommend=%b,YEDirectorCmplete=%n,OBEmpCompleteDate=%d,
|
|||
|
|
OBLMCompleteDate=%d,NotificationStatus=%n,LMOverallRating=%n, EMPOverallRating=%n,DevPlanEmpStatus=%n,DevPlanLMStatus=%n,
|
|||
|
|
AchivementPercent=%n, YEEmpComments=%s, IsEditing=%n, PreCalibrationRating=%n, KeyPerformance=%s, Strength=%s, Opportunity=%s,
|
|||
|
|
SecondLMObjRating=%n, SecondLMValueRating=%n, SecondLMOverallRating=%n, YeTotalObjectiveEmpMark=%n, YeTotalObjectiveLmMark=%n Where ObjectiveSetID = %n",
|
|||
|
|
item.PMPYearID, item.EmployeeID, DataReader.GetNullValue(item.EmployeeNodeID),
|
|||
|
|
DataReader.GetNullValue(item.LMID), DataReader.GetNullValue(item.LMNodeID), item.EMPOBJRating,
|
|||
|
|
item.EMPValueRating, item.LMOBJRating, item.LMValueRating, item.OBEmpComplete, (int)item.OBLMComplete,
|
|||
|
|
(int)item.MYEmpComplete, (int)item.MYLMComplete, (int)item.MYEmpAgreed, (int)item.YEEmpComplete,
|
|||
|
|
(int)item.YELMComplete, (int)item.YEDHComplete, (int)item.YEMTComplete, (int)item.YECEOComplete,
|
|||
|
|
(int)item.YEEmpAgreed, (int)item.YEESendLMCmplete, DataReader.GetNullValue(item.AuthorizedOBEmployeeID),
|
|||
|
|
DataReader.GetNullValue(item.AuthorizedDPEmployeeID),
|
|||
|
|
DataReader.GetNullValue(item.AuthorizedMYEmployeeID),
|
|||
|
|
DataReader.GetNullValue(item.AuthorizedYEEmployeeID), item.OverallEmpComments, item.OverallLMComments,
|
|||
|
|
item.OverallSecondLMComments, item.RevertComment, item.OverallDirectorComments, item.SecondLMAgreed,
|
|||
|
|
item.DirectorAgreed, item.IsLMRecommend, item.IsSecondLMRecommend, item.IsDirectorRecommend,
|
|||
|
|
item.YEDirectorCmplete, DataReader.GetNullValue(item.OBEmpCompleteDate),
|
|||
|
|
DataReader.GetNullValue(item.OBLMCompleteDate), (int)item.NotificationStatus, item.LMOverallRating,
|
|||
|
|
item.EMPOverallRating, (int)item.DevPlanEmpStatus, (int)item.DevPlanLMStatus, item.AchivementPercent,
|
|||
|
|
item.YEEmpComments, item.IsEditing, item.PreCalibrationRating, item.KeyPerformance, item.Strength, item.Opportunity,
|
|||
|
|
item.SecondLMObjRating, item.SecondLMValueRating, item.SecondLMOverallRating, item.YeTotalObjectiveEmpMark, item.YeTotalObjectiveLmMark, item.ID);
|
|||
|
|
|
|||
|
|
//string sql = SQLParser.MakeSQL("Update ObjectiveSet Set PMPYearID = %n, EmployeeID = %n, EmployeeNodeID = %n, LMID = %n, LMNodeID = %n, OverallOBRating = %n, OverallValuesRating = %n, OBEmpComplete = %b, OBLMComplete = %n, MYEmpComplete = %n, MYLMComplete = %n, MYEmpAgreed = %n, YEEmpComplete = %n, YELMComplete = %n, YEEmpAgreed = %n, YEESendLMCmplete = %n, DevelopmentPlanSubmit = %n, LMDevelopmentPlanSubmit = %n,AuthorizedOBEmployeeID=%n ,AuthorizedDPEmployeeID=%n,AuthorizedMYEmployeeID=%n ,AuthorizedYEEmployeeID=%n,OverallEmpComments=%s,OverallLMComments=%s,OverallSecondLMComments=%s,RevertComment=%s,OverallDirectorComments=%s,SecondLMAgreed=%b,DirectorAgreed=%b,IsLMRecommend=%b,IsSecondLMRecommend=%b,IsDirectorRecommend=%b,YEDirectorCmplete=%n,OBEmpCompleteDate=%d,OBLMCompleteDate=%d,NotificationStatus=%n Where ObjectiveSetID = %n",
|
|||
|
|
// item.PMPYearID, item.EmployeeID, item.EmployeeNodeID, DataReader.GetNullValue(item.LMID, IDType), DataReader.GetNullValue(item.LMNodeID, IDType), item.OverallOBRating, item.OverallValuesRating, item.OBEmpComplete, (int)item.OBLMComplete, (int)item.MYEmpComplete, (int)item.MYLMComplete, (int)item.MYEmpAgreed, (int)item.YEEmpComplete, (int)item.YELMComplete, (int)item.YEEmpAgreed, (int)item.YEESendLMCmplete, (int)item.DevelopmentPlanSubmit, (int)item.LMDevelopmentPlanSubmit, DataReader.GetNullValue(item.AuthorizedOBEmployeeID, IDType), DataReader.GetNullValue(item.AuthorizedDPEmployeeID, IDType),
|
|||
|
|
// DataReader.GetNullValue(item.AuthorizedMYEmployeeID, IDType), DataReader.GetNullValue(item.AuthorizedYEEmployeeID, IDType), item.OverallEmpComments, item.OverallLMComments, item.OverallSecondLMComments, item.RevertComment, item.OverallDirectorComments, item.SecondLMAgreed, item.DirectorAgreed, item.IsLMRecommend, item.IsSecondLMRecommend, item.IsDirectorRecommend, item.YEDirectorCmplete, DataReader.GetNullValue(item.OBEmpCompleteDate), DataReader.GetNullValue(item.OBLMCompleteDate), (int)item.NotificationStatus, item.ID);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
Employee em = new Employee();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Delete From ObjectiveSet Where ObjectiveSetID = %n", id);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByEmployeeID(TransactionContext tc, int EmpID, int pMPYID)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSet WHERE EmployeeID = %n AND PMPYearID=%n",
|
|||
|
|
DataReader.GetNullValue(EmpID), DataReader.GetNullValue(pMPYID));
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetObjectiveSetByDepartment(TransactionContext tc, int deptId, int pmpYearId)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(";WITH CTE AS " +
|
|||
|
|
"(SELECT employeeid FROM EMPLOYEE e WHERE DEPARTMENTID=%n) " +
|
|||
|
|
"SELECT * FROM OBJECTIVESET o " +
|
|||
|
|
"INNER JOIN CTE e " +
|
|||
|
|
"ON o.employeeid = e.employeeid WHERE pmpYearId= %n;", deptId, pmpYearId);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByEmpIds(TransactionContext tc, int pmpYearId, string empIds)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSet WHERE PMPYearID=%n AND employeeId in (%q)" ,
|
|||
|
|
pmpYearId, empIds);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByObjectiveID(TransactionContext tc, int objectiveID)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSet WHERE ObjectiveSetID = (select objectiveSetID from Objective where objectiveid=%n)",
|
|||
|
|
objectiveID);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetByObjectiveSetID(TransactionContext tc, int iD)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Objective WHERE ObjectiveSetID = %n AND IsDraft=0", iD);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByPMPYear(TransactionContext tc, int iD, string InEmpSQL)
|
|||
|
|
{
|
|||
|
|
if (InEmpSQL == "" || InEmpSQL == null)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSet WHERE PMPYearID = %n ", iD);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSet WHERE PMPYearID = %n and EmployeeID in (%q)",
|
|||
|
|
iD, InEmpSQL);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByPMPYear(TransactionContext tc, int iD)
|
|||
|
|
{
|
|||
|
|
//string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSet WHERE PMPYearID = %n and IsDirectorRecommend=%b", iD, true);
|
|||
|
|
string sql =
|
|||
|
|
SQLParser.MakeSQL(
|
|||
|
|
"SELECT * FROM ObjectiveSet WHERE PMPYearID = %n AND (IsLMRecommend=%b OR IsSecondLMRecommend=%b) ",
|
|||
|
|
iD, true, true);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetObjectiveRatingDataset(TransactionContext tc, int id, string InEmpSQL)
|
|||
|
|
{
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
double totalEmployeeCount = 0;
|
|||
|
|
DataSet dSet = new DataSet();
|
|||
|
|
DataSet tempDataSet = null;
|
|||
|
|
string sTableName = string.Empty;
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: IDHelper.GetIDs(tc, InEmpSQL, out sTableName);
|
|||
|
|
string InEmpSQL2 = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: string.Format("WHERE EMPLOYEEID IN ({0})", InEmpSQL);
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: string.Format(
|
|||
|
|
"WHERE CATEGORYID = 1 AND DATEOFCONFIRMATION <= (SELECT FORMAT(CUTOFFDATE, 'dd MMM yyyy', 'en-us') year FROM PMP_PROCESS WHERE PMPPROCESSID = {0}) AND EMPLOYEEID IN ({1})",
|
|||
|
|
id, InEmpSQL);
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL("Select Count(*) from Employee %q", InEmpSQL);
|
|||
|
|
totalEmployeeCount = Convert.ToDouble(tc.ExecuteScalar(sql));
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(
|
|||
|
|
@"SELECT CAST(OBJRating AS int) OBJRating, Count(EmployeeID) HeadCount, Round((Count(EmployeeID) * 100.00 / %n), 2) HeadCountPercent
|
|||
|
|
FROM
|
|||
|
|
(SELECT e.EmployeeID, ISNULL(os.LMObjRating, 0) OBJRating
|
|||
|
|
FROM
|
|||
|
|
(Select * FROM EMPLOYEE %q) e
|
|||
|
|
LEFT JOIN
|
|||
|
|
(SELECT * FROM ObjectiveSet Where PMPYearID = %n) os
|
|||
|
|
ON e.EMPLOYEEID = os.EMPLOYEEID) x
|
|||
|
|
GROUP BY x.OBJRating
|
|||
|
|
ORDER BY x.OBJRating DESC", totalEmployeeCount, InEmpSQL, id);
|
|||
|
|
tempDataSet = tc.ExecuteDataSet(sql);
|
|||
|
|
tempDataSet.Tables[0].TableName = "LMOBJRatings";
|
|||
|
|
dSet.Tables.Add(tempDataSet.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(
|
|||
|
|
@"SELECT OBJRating, Count(EmployeeID) HeadCount, Round((Count(EmployeeID) * 100.00 / %n), 2) HeadCountPercent
|
|||
|
|
FROM
|
|||
|
|
(SELECT e.EmployeeID, ISNULL(os.EMPObjRating, 0) OBJRating
|
|||
|
|
FROM
|
|||
|
|
(Select * FROM EMPLOYEE %q) e
|
|||
|
|
LEFT JOIN
|
|||
|
|
(SELECT * FROM ObjectiveSet Where PMPYearID = %n) os
|
|||
|
|
ON e.EMPLOYEEID = os.EMPLOYEEID) x
|
|||
|
|
GROUP BY x.OBJRating
|
|||
|
|
ORDER BY x.OBJRating DESC", totalEmployeeCount, InEmpSQL, id);
|
|||
|
|
tempDataSet = tc.ExecuteDataSet(sql);
|
|||
|
|
tempDataSet.Tables[0].TableName = "EMPOBJRatings";
|
|||
|
|
dSet.Tables.Add(tempDataSet.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(@"SELECT
|
|||
|
|
CASE ISNULL(os.YELMComplete,0)
|
|||
|
|
WHEN %n THEN 'LMSubmitted'
|
|||
|
|
WHEN %n THEN 'LMDraft'
|
|||
|
|
ELSE 'LMPending'
|
|||
|
|
END StatusField,Count(*) COUNT
|
|||
|
|
FROM
|
|||
|
|
(SELECT * FROM EMPLOYEE %q) e
|
|||
|
|
LEFT JOIN
|
|||
|
|
(SELECT * FROM OBJECTIVESET WHERE PMPYEARID = %n) os
|
|||
|
|
ON e.EmployeeID = os.EmployeeID
|
|||
|
|
GROUP BY ISNULL(os.YELMComplete,0)
|
|||
|
|
Union
|
|||
|
|
SELECT
|
|||
|
|
CASE ISNULL(os.YEDHComplete,0)
|
|||
|
|
WHEN %n THEN 'DHSubmitted'
|
|||
|
|
ELSE 'DHPending'
|
|||
|
|
END StatusField,Count(*) COUNT
|
|||
|
|
FROM
|
|||
|
|
(SELECT * FROM EMPLOYEE %q) e
|
|||
|
|
LEFT JOIN
|
|||
|
|
(SELECT * FROM OBJECTIVESET WHERE PMPYEARID = %n) os
|
|||
|
|
ON e.EmployeeID = os.EmployeeID
|
|||
|
|
GROUP BY ISNULL(os.YEDHComplete,0)
|
|||
|
|
UNION
|
|||
|
|
SELECT
|
|||
|
|
CASE ISNULL(os.YEMTComplete,0)
|
|||
|
|
WHEN %n THEN 'MTSubmitted'
|
|||
|
|
ELSE 'MTPending'
|
|||
|
|
END StatusField,Count(*) Count
|
|||
|
|
FROM
|
|||
|
|
(SELECT * FROM EMPLOYEE %q) e
|
|||
|
|
LEFT JOIN
|
|||
|
|
(SELECT * FROM OBJECTIVESET WHERE PMPYEARID = %n) os
|
|||
|
|
ON e.EmployeeID = os.EmployeeID
|
|||
|
|
GROUP BY ISNULL(os.YEMTComplete,0)
|
|||
|
|
UNION
|
|||
|
|
SELECT
|
|||
|
|
CASE ISNULL(os.YECEOComplete,0)
|
|||
|
|
WHEN %n THEN 'CEOSubmitted'
|
|||
|
|
ELSE 'CEOPending'
|
|||
|
|
END StatusField,Count(*) Count
|
|||
|
|
FROM
|
|||
|
|
(SELECT * FROM EMPLOYEE %q) e
|
|||
|
|
LEFT JOIN
|
|||
|
|
(SELECT * FROM OBJECTIVESET WHERE PMPYEARID = %n) os
|
|||
|
|
ON e.EmployeeID = os.EmployeeID
|
|||
|
|
GROUP BY ISNULL(os.YECEOComplete,0)
|
|||
|
|
UNION
|
|||
|
|
SELECT 'TOTAL' StatusField, %n COUNT FROM PMP_PROCESS WHERE PMPProcessID = %n",
|
|||
|
|
EnumObjectiveFlowStatus.Submit, EnumObjectiveFlowStatus.Draft_For_Employee, InEmpSQL, id,
|
|||
|
|
EnumObjectiveFlowStatus.Submit, InEmpSQL, id,
|
|||
|
|
EnumObjectiveFlowStatus.Submit, InEmpSQL, id,
|
|||
|
|
EnumObjectiveFlowStatus.Submit, InEmpSQL, id,
|
|||
|
|
totalEmployeeCount, id);
|
|||
|
|
tempDataSet = tc.ExecuteDataSet(sql);
|
|||
|
|
tempDataSet.Tables[0].TableName = "CountTable";
|
|||
|
|
dSet.Tables.Add(tempDataSet.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(@"SELECT DHeadChecked FROM dbo.OBJECTIVESET %q and PMPYEARID=%n", InEmpSQL2, id);
|
|||
|
|
tempDataSet = tc.ExecuteDataSet(sql);
|
|||
|
|
tempDataSet.Tables[0].TableName = "DHeadChecked";
|
|||
|
|
dSet.Tables.Add(tempDataSet.Tables[0].Copy());
|
|||
|
|
|
|||
|
|
if (sTableName != "")
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("Drop TAble %q", sTableName);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return dSet;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetEmployeeWiseObjectiveRatingDataSet(TransactionContext tc, int id, string InEmpSQL)
|
|||
|
|
{
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
double totalEmployeeCount = 0;
|
|||
|
|
DataSet dSet = new DataSet();
|
|||
|
|
string sTableName = string.Empty;
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: IDHelper.GetIDs(tc, InEmpSQL, out sTableName);
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: string.Format("WHERE EMPLOYEEID IN ({0})", InEmpSQL);
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL("Select Count(*) from Employee %q", InEmpSQL);
|
|||
|
|
totalEmployeeCount = Convert.ToDouble(tc.ExecuteScalar(sql));
|
|||
|
|
|
|||
|
|
// Without Last Year's Data
|
|||
|
|
//sql = SQLParser.MakeSQL("SELECT e.EmployeeNo \"Employee No\",e.Name \"Employee Name\", convert(varchar, e.JOININGDATE, 106) DOJ, " +
|
|||
|
|
// "ISNULL(e.DESIGNATION, '') \"Position Name\", ISNULL(e.GRADE, '') \"Job Band\"," +
|
|||
|
|
// "ISNULL(e.Company, '') \"Department\", ISNULL(e.Division, '') \"Function\", ISNULL(e.LINEMANAGERNAME, '') \"Line Manager's Name\"," +
|
|||
|
|
|
|||
|
|
// "ISNULL(os.EMPObjRating, 0) \"Employee What Rating\", ISNULL(os.EMPValueRating, 0) \"Employee How Rating\"," +
|
|||
|
|
// "ISNULL(os.EMPOverallRating, 0) \"Employee Total Rating\"," +
|
|||
|
|
// "ISNULL(os.LMObjRating, 0) \"Line Manager What Rating\", ISNULL(os.LMValueRating, 0) \"Line Manager How Rating\"," +
|
|||
|
|
// "ISNULL(os.LMOverallRating, 0) \"Line Manager Total Rating\",ISNULL(os.AchivementPercent, 0) \"Achivement Percent\" " +
|
|||
|
|
// "FROM " +
|
|||
|
|
// "(SELECT * FROM VW_EMPLOYEEBASIC %q) e " +
|
|||
|
|
// "LEFT JOIN " +
|
|||
|
|
// "(SELECT * FROM ObjectiveSet Where PMPYearID = %n) os " +
|
|||
|
|
// "ON e.EMPLOYEEID = os.EMPLOYEEID WHERE e.CATEGORY = 'FTE' and e.DATEOFCONFIRMATION<= (SELECT FORMAT(CUTOFFDATE, 'dd MMM yyyy', 'en-us') year FROM PMP_PROCESS WHERE PMPPROCESSID = %n)", InEmpSQL, id, id);
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(
|
|||
|
|
"SELECT e.EmployeeNo \"Employee No\",e.Name \"Employee Name\", convert(varchar, e.JOININGDATE, 107) DOJ, " +
|
|||
|
|
"ISNULL(e.DESIGNATION, '') \"Position Name\", ISNULL(e.GRADE, '') \"Job Band\"," +
|
|||
|
|
"ISNULL(e.Company, '') \"Department\", ISNULL(e.Division, '') \"Function\", ISNULL(e.LINEMANAGERNAME, '') \"Line Manager's Name\"," +
|
|||
|
|
"CASE ISNULL(os.EMPObjRating, 0) WHEN 1 THEN 'G1' WHEN 2 THEN 'G2' WHEN 3 THEN 'G3' WHEN 4 THEN 'G4' WHEN 5 THEN 'G5' ELSE '0' END AS \"Employee (What-G)Rating\", CASE ISNULL(os.EMPValueRating, 0) WHEN 1 THEN 'L1' WHEN 2 THEN 'L2' WHEN 3 THEN 'L3' WHEN 4 THEN 'L4' WHEN 5 THEN 'L5' ELSE '0' END AS \"Employee (How- L)Rating\"," +
|
|||
|
|
"CASE WHEN ISNULL(os.EMPObjRating, 0)=1 AND ISNULL(os.EMPValueRating, 0)=1 THEN 'G1L1' WHEN ISNULL(os.EMPObjRating, 0)=1 AND ISNULL(os.EMPValueRating, 0)=2 THEN 'G1L2' WHEN ISNULL(os.EMPObjRating, 0)=1 AND ISNULL(os.EMPValueRating, 0)=3 THEN 'G1L3' WHEN ISNULL(os.EMPObjRating, 0)=1 AND ISNULL(os.EMPValueRating, 0)=4 THEN 'G1L4' WHEN ISNULL(os.EMPObjRating, 0)=1 AND ISNULL(os.EMPValueRating, 0)=5 THEN 'G1L5' WHEN ISNULL(os.EMPObjRating, 0)=2 AND ISNULL(os.EMPValueRating, 0)=1 THEN 'G2L1' WHEN ISNULL(os.EMPObjRating, 0)=2 AND ISNULL(os.EMPValueRating, 0)=2 THEN 'G2L2' WHEN ISNULL(os.EMPObjRating, 0)=2 AND ISNULL(os.EMPValueRating, 0)=3 THEN 'G2L3' WHEN ISNULL(os.EMPObjRating, 0)=2 AND ISNULL(os.EMPValueRating, 0)=4 THEN 'G2L4' WHEN ISNULL(os.EMPObjRating, 0)=2 AND ISNULL(os.EMPValueRating, 0)=5 THEN 'G2L5' WHEN ISNULL(os.EMPObjRating, 0)=3 AND ISNULL(os.EMPValueRating, 0)=1 THEN 'G3L1' WHEN ISNULL(os.EMPObjRating, 0)=3 AND ISNULL(os.EMPValueRating, 0)=2 THEN 'G3L2' WHEN ISNULL(os.EMPObjRating, 0)=3 AND ISNULL(os.EMPValueRating, 0)=3 THEN 'G3L3' WHEN ISNULL(os.EMPObjRating, 0)=3 AND ISNULL(os.EMPValueRating, 0)=4 THEN 'G3L4' WHEN ISNULL(os.EMPObjRating, 0)=3 AND ISNULL(os.EMPValueRating, 0)=5 THEN 'G3L5' WHEN ISNULL(os.EMPObjRating, 0)=4 AND ISNULL(os.EMPValueRating, 0)=1 THEN 'G4L1' WHEN ISNULL(os.EMPObjRating, 0)=4 AND ISNULL(os.EMPValueRating, 0)=2 THEN 'G4L2' WHEN ISNULL(os.EMPObjRating, 0)=4 AND ISNULL(os.EMPValueRating, 0)=3 THEN 'G4L3' WHEN ISNULL(os.EMPObjRating, 0)=4 AND ISNULL(os.EMPValueRating, 0)=4 THEN 'G4L4' WHEN ISNULL(os.EMPObjRating, 0)=4 AND ISNULL(os.EMPValueRating, 0)=5 THEN 'G4L5' WHEN ISNULL(os.EMPObjRating, 0)=5 AND ISNULL(os.EMPValueRating, 0)=1 THEN 'G5L1' WHEN ISNULL(os.EMPObjRating, 0)=5 AND ISNULL(os.EMPValueRating, 0)=2 THEN 'G5L2' WHEN ISNULL(os.EMPObjRating, 0)=5 AND ISNULL(os.EMPValueRating, 0)=3 THEN 'G5L3' WHEN ISNULL(os.EMPObjRating, 0)=5 AND ISNULL(os.EMPValueRating, 0)=4 THEN 'G5L4' WHEN ISNULL(os.EMPObjRating, 0)=5 AND ISNULL(os.EMPValueRating, 0)=5 THEN 'G5L5' ELSE '0' END AS \"Employee Performance matrix Rating (How+What)\"," +
|
|||
|
|
"CASE ISNULL(os.LMObjRating, 0) WHEN 1 THEN 'G1' WHEN 2 THEN 'G2' WHEN 3 THEN 'G3' WHEN 4 THEN 'G4' WHEN 5 THEN 'G5' ELSE '0' END AS \"Line Manager (What-G) Rating\", CASE ISNULL(os.LMValueRating, 0) WHEN 1 THEN 'L1' WHEN 2 THEN 'L2' WHEN 3 THEN 'L3' WHEN 4 THEN 'L4' WHEN 5 THEN 'L5' ELSE '0' END AS \"Line Manager (How-L) Rating\"," +
|
|||
|
|
"CASE WHEN ISNULL(os.LMObjRating, 0)=1 AND ISNULL(os.LMValueRating, 0)=1 THEN 'G1L1' WHEN ISNULL(os.LMObjRating, 0)=1 AND ISNULL(os.LMValueRating, 0)=2 THEN 'G1L2' WHEN ISNULL(os.LMObjRating, 0)=1 AND ISNULL(os.LMValueRating, 0)=3 THEN 'G1L3' WHEN ISNULL(os.LMObjRating, 0)=1 AND ISNULL(os.LMValueRating, 0)=4 THEN 'G1L4' WHEN ISNULL(os.LMObjRating, 0)=1 AND ISNULL(os.LMValueRating, 0)=5 THEN 'G1L5' WHEN ISNULL(os.LMObjRating, 0)=2 AND ISNULL(os.LMValueRating, 0)=1 THEN 'G2L1' WHEN ISNULL(os.LMObjRating, 0)=2 AND ISNULL(os.LMValueRating, 0)=2 THEN 'G2L2' WHEN ISNULL(os.LMObjRating, 0)=2 AND ISNULL(os.LMValueRating, 0)=3 THEN 'G2L3' WHEN ISNULL(os.LMObjRating, 0)=2 AND ISNULL(os.LMValueRating, 0)=4 THEN 'G2L4' WHEN ISNULL(os.LMObjRating, 0)=2 AND ISNULL(os.LMValueRating, 0)=5 THEN 'G2L5' WHEN ISNULL(os.LMObjRating, 0)=3 AND ISNULL(os.LMValueRating, 0)=1 THEN 'G3L1' WHEN ISNULL(os.LMObjRating, 0)=3 AND ISNULL(os.LMValueRating, 0)=2 THEN 'G3L2' WHEN ISNULL(os.LMObjRating, 0)=3 AND ISNULL(os.LMValueRating, 0)=3 THEN 'G3L3' WHEN ISNULL(os.LMObjRating, 0)=3 AND ISNULL(os.LMValueRating, 0)=4 THEN 'G3L4' WHEN ISNULL(os.LMObjRating, 0)=3 AND ISNULL(os.LMValueRating, 0)=5 THEN 'G3L5' WHEN ISNULL(os.LMObjRating, 0)=4 AND ISNULL(os.LMValueRating, 0)=1 THEN 'G4L1' WHEN ISNULL(os.LMObjRating, 0)=4 AND ISNULL(os.LMValueRating, 0)=2 THEN 'G4L2' WHEN ISNULL(os.LMObjRating, 0)=4 AND ISNULL(os.LMValueRating, 0)=3 THEN 'G4L3' WHEN ISNULL(os.LMObjRating, 0)=4 AND ISNULL(os.LMValueRating, 0)=4 THEN 'G4L4' WHEN ISNULL(os.LMObjRating, 0)=4 AND ISNULL(os.LMValueRating, 0)=5 THEN 'G4L5' WHEN ISNULL(os.LMObjRating, 0)=5 AND ISNULL(os.LMValueRating, 0)=1 THEN 'G5L1' WHEN ISNULL(os.LMObjRating, 0)=5 AND ISNULL(os.LMValueRating, 0)=2 THEN 'G5L2' WHEN ISNULL(os.LMObjRating, 0)=5 AND ISNULL(os.LMValueRating, 0)=3 THEN 'G5L3' WHEN ISNULL(os.LMObjRating, 0)=5 AND ISNULL(os.LMValueRating, 0)=4 THEN 'G5L4' WHEN ISNULL(os.LMObjRating, 0)=5 AND ISNULL(os.LMValueRating, 0)=5 THEN 'G5L5' ELSE '0' END AS \"Performance matrix Rating (How+What)\",ISNULL(os.AchivementPercent, 0) \"Achivement Percent\" " +
|
|||
|
|
" ,ISNULL(osr.YEARENDLMREMARKSONE,'') \"LM Comments\" " +
|
|||
|
|
"FROM " +
|
|||
|
|
"(SELECT * FROM VW_EMPLOYEEBASIC %q) e " +
|
|||
|
|
"LEFT JOIN " +
|
|||
|
|
"(SELECT * FROM ObjectiveSet Where PMPYearID = %n) os " +
|
|||
|
|
"ON e.EMPLOYEEID = os.EMPLOYEEID " +
|
|||
|
|
"LEFT JOIN (Select * from OBJECTIVESETREMARKS Where YEARENDLMREMARKSONE <>'' AND YEARENDLMREMARKSONE IS NOT NULL) osr " +
|
|||
|
|
"ON os.ObjectiveSetID = osr.ObjectiveSetID" +
|
|||
|
|
" WHERE e.CATEGORY = 'FTE' and e.DATEOFCONFIRMATION<= (SELECT FORMAT(CUTOFFDATE, 'dd MMM yyyy', 'en-us') year FROM PMP_PROCESS WHERE PMPPROCESSID = %n)",
|
|||
|
|
InEmpSQL, id, id);
|
|||
|
|
|
|||
|
|
dSet = tc.ExecuteDataSet(sql);
|
|||
|
|
dSet.Tables[0].TableName = "Team Ratings";
|
|||
|
|
|
|||
|
|
if (sTableName != "")
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("Drop TAble %q", sTableName);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return dSet;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateStatusYEMTComplete(TransactionContext tc, int id, string InEmpSQL,
|
|||
|
|
EnumObjectiveFlowStatus status)
|
|||
|
|
{
|
|||
|
|
string sTableName = string.Empty;
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: IDHelper.GetIDs(tc, InEmpSQL, out sTableName);
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: string.Format("AND EMPLOYEEID IN ({0})", InEmpSQL);
|
|||
|
|
|
|||
|
|
string sql = SQLParser.MakeSQL("UPDATE OBJECTIVESET SET YEMTComplete = %n WHERE PMPYEARID = %n %q",
|
|||
|
|
(int)status, id, InEmpSQL);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
|
|||
|
|
if (sTableName != "")
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("Drop TAble %q", sTableName);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateStatusYECEOComplete(TransactionContext tc, int id, string InEmpSQL,
|
|||
|
|
EnumObjectiveFlowStatus status)
|
|||
|
|
{
|
|||
|
|
string sTableName = string.Empty;
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: IDHelper.GetIDs(tc, InEmpSQL, out sTableName);
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: string.Format("AND EMPLOYEEID IN ({0})", InEmpSQL);
|
|||
|
|
|
|||
|
|
string sql = SQLParser.MakeSQL("UPDATE OBJECTIVESET SET YECEOComplete = %n WHERE PMPYEARID = %n %q",
|
|||
|
|
(int)status, id, InEmpSQL);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
|
|||
|
|
if (sTableName != "")
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("Drop TAble %q", sTableName);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateStatusYEDHComplete(TransactionContext tc, int id, string InEmpSQL,
|
|||
|
|
EnumObjectiveFlowStatus status)
|
|||
|
|
{
|
|||
|
|
string sTableName = string.Empty;
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: IDHelper.GetIDs(tc, InEmpSQL, out sTableName);
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: string.Format("AND EMPLOYEEID IN ({0})", InEmpSQL);
|
|||
|
|
|
|||
|
|
string sql = SQLParser.MakeSQL("UPDATE OBJECTIVESET SET YEDHComplete = %n WHERE PMPYEARID = %n %q",
|
|||
|
|
(int)status, id, InEmpSQL);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
|
|||
|
|
if (sTableName != "")
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("Drop TAble %q", sTableName);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateCheckStatusYEDHComplete(TransactionContext tc, int id, string InEmpSQL)
|
|||
|
|
{
|
|||
|
|
string sTableName = string.Empty;
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: IDHelper.GetIDs(tc, InEmpSQL, out sTableName);
|
|||
|
|
InEmpSQL = string.IsNullOrWhiteSpace(InEmpSQL)
|
|||
|
|
? string.Empty
|
|||
|
|
: string.Format("AND EMPLOYEEID IN ({0})", InEmpSQL);
|
|||
|
|
|
|||
|
|
string sql = SQLParser.MakeSQL("UPDATE OBJECTIVESET SET DHeadChecked = %n WHERE PMPYEARID = %n %q", 1, id,
|
|||
|
|
InEmpSQL);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
|
|||
|
|
if (sTableName != "")
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("Drop TAble %q", sTableName);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetObjectiveSetRemarks(TransactionContext tc, int iD)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSetRemarks WHERE ObjectiveSetID = %n", iD);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetYearEndRecomendation(TransactionContext tc, int iD)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM YearEndRecomendation WHERE ObjectiveSetID = %n", iD);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByEmployeeID(TransactionContext tc, int EmpiD)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM ObjectiveSet WHERE EmployeeID = %n",
|
|||
|
|
DataReader.GetNullValue(EmpiD));
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static void UpdateObjectivesetForReplace(TransactionContext tc, ObjectiveSet sObjName)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Update ObjectiveSet set Employeeid=%n where ObjectiveSetid=%n",
|
|||
|
|
sObjName.EmployeeID, sObjName.ID);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateObjectivesetStatus(TransactionContext tc, int objID,
|
|||
|
|
EnumPMSNotificationStatus status)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Update ObjectiveSet set NotificationStatus=%n where ObjectiveSetid=%n",
|
|||
|
|
(int)status, objID);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateObjectiveForReplace(TransactionContext tc, Objective sObjName)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Update Objective set Employeeid=%n where Objectiveid=%n",
|
|||
|
|
sObjName.EmployeeID, sObjName.ID);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateObjectiveEmployeeForReplace(TransactionContext tc, ObjectiveEmployees sObjName)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Update ObjectiveEmployees set Employeeid=%n where ObjectiveEmployeesid=%n",
|
|||
|
|
sObjName.EmployeeID, sObjName.ID);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UpdateOldObjectiveFlag(TransactionContext tc, Objective sObj)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Update Objective set IsOldObjective=%b where Objectiveid=%n",
|
|||
|
|
sObj.IsOldObjective, sObj.ID);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|