75 lines
4.1 KiB
C#
75 lines
4.1 KiB
C#
|
using System;
|
|||
|
|
using System.Data;
|
|||
|
|
using Ease.Core.DataAccess;
|
|||
|
|
using HRM.BO;
|
|||
|
|
|
|||
|
|
namespace HRM.DA
|
|||
|
|
{
|
|||
|
|
public class CalibrationDA
|
|||
|
|
{
|
|||
|
|
internal static IDataReader GetByEmpIdAndPmpYearId(TransactionContext tc, int employeeId, int pmpYearId)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Calibration where employeeId=%n and pmpYearId=%n", employeeId, pmpYearId);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetCalibrationListByPmpYearId(TransactionContext tc, int pmpYearId)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Calibration where pmpYearId=%n", pmpYearId);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetSubordinatesCalibration(TransactionContext tc, int pmpYearId, int employeeId)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM Calibration WHERE EmployeeId IN " +
|
|||
|
|
"(SELECT EMPLOYEEID FROM EMPLOYEE WHERE linemanagerid=%n) AND PmpYearId = %n", employeeId, pmpYearId);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, Calibration item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Insert into Calibration(CalibrationId, EmployeeId, PmpYearId, Description, CalibrationStatus, CalibrationDate, Strength, Opportunity, KeyPerformance, PostCalibrationRating) " +
|
|||
|
|
"Values(%n, %n, %n, %s, %n, %d, %s, %s, %s, %n)", item.ID, item.EmployeeId, item.PmpYearId, item.Description, item.CalibrationStatus, item.CalibrationDate, item.Strength, item.Opportunity, item.KeyPerformance, item.PostCalibrationRating);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, Calibration item)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Update Calibration Set employeeId = %n, pmpYearId = %n, Description = %s, CalibrationStatus = %n, CalibrationDate = %d, Strength = %s, Opportunity = %s, KeyPerformance = %s, PostCalibrationRating = %n " +
|
|||
|
|
"where CalibrationId=%n", item.EmployeeId, item.PmpYearId , item.Description, item.CalibrationStatus, item.CalibrationDate, item.Strength, item.Opportunity, item.KeyPerformance, item.PostCalibrationRating,
|
|||
|
|
item.ID);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("Delete From Calibration Where CalibrationId = %n", id);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetEmployeeWithRatings(TransactionContext tc, int pmpYearId, string ratingStr)
|
|||
|
|
{
|
|||
|
|
DataSet ds = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("WITH cte AS " +
|
|||
|
|
"(SELECT employeeId EmployeeId, LMOVERALLRATING Rating " +
|
|||
|
|
"FROM OBJECTIVESET WHERE PmpyearId=%n AND Floor(lmoverallrating) IN (" + ratingStr + "))" +
|
|||
|
|
"SELECT e.employeeid EmployeeId, e.employeeNo EmployeeNo, e.NAME EmployeeName, " +
|
|||
|
|
"dept.description Department, des.name Designation, e.linemanagerid LineManagerId ," +
|
|||
|
|
"lm.employeeno + '-' + lm.Name LineManager, cte.Rating " +
|
|||
|
|
"FROM employee e INNER JOIN cte ON e.employeeId = cte.employeeid " +
|
|||
|
|
"LEFT JOIN DEPARTMENT dept ON e.departmentid = dept.departmentid " +
|
|||
|
|
"LEFT JOIN DESIGNATION DES ON e.DESIGNATIONID= DES.DESIGNATIONID " +
|
|||
|
|
"LEFT JOIN EMPLOYEE LM ON e.LINEMANAGERID = LM.EMPLOYEEID", pmpYearId);
|
|||
|
|
ds = tc.ExecuteDataSet(sql);
|
|||
|
|
}
|
|||
|
|
catch (Exception e)
|
|||
|
|
{
|
|||
|
|
Console.WriteLine(e);
|
|||
|
|
throw;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return ds;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|