333 lines
17 KiB
C#
333 lines
17 KiB
C#
|
using System;
|
|||
|
|
using System.Data;
|
|||
|
|
using Ease.CoreV35.DataAccess;
|
|||
|
|
using Ease.CoreV35.Model;
|
|||
|
|
using Payroll.BO;
|
|||
|
|
|
|||
|
|
|
|||
|
|
namespace Payroll.Service
|
|||
|
|
{
|
|||
|
|
class EmployeeRequisitionDA
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
#region Get Functions
|
|||
|
|
|
|||
|
|
public static IDataReader Get(TransactionContext tc, int empRequisitionID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * from EmployeeRequisition WHERE EmpRequisitionID=%n", empRequisitionID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static IDataReader Get(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * from EmployeeRequisition");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static IDataReader GetByDepartmentID(TransactionContext tc, int departmentID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * from EmployeeRequisition WHERE DepartmentID=%n", departmentID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static IDataReader GetRequisitionUserByReqID(TransactionContext tc, int reqID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * from RequisitionUser WHERE EmpRequisitionID=%n", reqID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Datasets
|
|||
|
|
|
|||
|
|
internal static DataSet GetEmployeeRequisitionsByDepartmentID(TransactionContext tc, int departmentID,EnumEmpReqStatus reqStatus)
|
|||
|
|
{
|
|||
|
|
DataSet empRequisitionDS = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
switch (reqStatus)
|
|||
|
|
{
|
|||
|
|
case EnumEmpReqStatus.RM:
|
|||
|
|
sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID as ID,
|
|||
|
|
'['+grd.Code+']-'+grd.[Description] GradeCodeName,empR.Position,
|
|||
|
|
empR.MaxEmployee,
|
|||
|
|
CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
|
|||
|
|
CASE
|
|||
|
|
When empR.Gender = 0
|
|||
|
|
then 'Regardless'
|
|||
|
|
When empR.Gender = 1
|
|||
|
|
then 'Male'
|
|||
|
|
ELSE 'Female'
|
|||
|
|
END as Gender,
|
|||
|
|
CASE
|
|||
|
|
When empR.[Status] = 0
|
|||
|
|
then 'Draft'
|
|||
|
|
When empR.[Status] = 1
|
|||
|
|
then 'Sent to Department Head'
|
|||
|
|
When empR.[Status] = 2 and empR.IsCompleted = 0
|
|||
|
|
then 'Sent to HR'
|
|||
|
|
When empR.[Status] = 2 and empR.IsCompleted = 1
|
|||
|
|
then 'Declined by HR'
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 0
|
|||
|
|
then 'Send to CEO'
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 1
|
|||
|
|
then 'Declined by CEO'
|
|||
|
|
ELSE 'Approved by CEO'
|
|||
|
|
END as [Status]
|
|||
|
|
FROM EmployeeRequisition empR,Grades grd
|
|||
|
|
WHERE empR.GradeID = grd.GradeID AND empR.DepartmentID=%n", departmentID);
|
|||
|
|
break;
|
|||
|
|
case EnumEmpReqStatus.DH:
|
|||
|
|
sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID as ID,
|
|||
|
|
'['+grd.Code+']-'+grd.[Description] GradeCodeName,empR.Position,
|
|||
|
|
empR.MaxEmployee,
|
|||
|
|
CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
|
|||
|
|
CASE
|
|||
|
|
When empR.Gender = 0
|
|||
|
|
then 'Regardless'
|
|||
|
|
When empR.Gender = 1
|
|||
|
|
then 'Male'
|
|||
|
|
ELSE 'Female'
|
|||
|
|
END as Gender,
|
|||
|
|
CASE
|
|||
|
|
When empR.[Status] = 1
|
|||
|
|
then ' '
|
|||
|
|
When empR.[Status] = 2 and empR.IsCompleted = 0
|
|||
|
|
then 'Sent to HR'
|
|||
|
|
When empR.[Status] = 2 and empR.IsCompleted = 1
|
|||
|
|
then 'Declined by HR'
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 0
|
|||
|
|
then 'Send to CEO'
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 1
|
|||
|
|
then 'Declined by CEO'
|
|||
|
|
ELSE 'Approved by CEO'
|
|||
|
|
END as [Status]
|
|||
|
|
FROM EmployeeRequisition empR,Grades grd
|
|||
|
|
WHERE empR.GradeID = grd.GradeID AND
|
|||
|
|
empR.[Status]>0 And empR.DepartmentID=%n", departmentID);
|
|||
|
|
break;
|
|||
|
|
case EnumEmpReqStatus.Approved:
|
|||
|
|
sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID as ID,
|
|||
|
|
'['+grd.Code+']-'+grd.[Description] GradeCodeName,empR.Position,
|
|||
|
|
empR.MaxEmployee,
|
|||
|
|
CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
|
|||
|
|
CASE
|
|||
|
|
When empR.Gender = 0
|
|||
|
|
then 'Regardless'
|
|||
|
|
When empR.Gender = 1
|
|||
|
|
then 'Male'
|
|||
|
|
ELSE 'Female'
|
|||
|
|
END as Gender,
|
|||
|
|
CASE
|
|||
|
|
When empR.[Status] = 1
|
|||
|
|
then 'Draft'
|
|||
|
|
When empR.[Status] = 2 and empR.IsCompleted = 0
|
|||
|
|
then 'Sent to HR'
|
|||
|
|
When empR.[Status] = 2 and empR.IsCompleted = 1
|
|||
|
|
then 'Declined by HR'
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 0
|
|||
|
|
then 'Send to CEO'
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 1
|
|||
|
|
then 'Declined by CEO'
|
|||
|
|
ELSE 'Approved by CEO'
|
|||
|
|
END as [Status]
|
|||
|
|
FROM EmployeeRequisition empR,Grades grd
|
|||
|
|
WHERE empR.GradeID = grd.GradeID AND
|
|||
|
|
empR.[Status]>0 And empR.[Status]=%n", (int)reqStatus);
|
|||
|
|
break;
|
|||
|
|
default:
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
empRequisitionDS = tc.ExecuteDataSet(sql);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return empRequisitionDS;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetEmployeeRequisitionsForCEO(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
DataSet empRequisitionDS = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID as ID,empR.DepartmentID,
|
|||
|
|
dpt.DESCRIPTION as [Department],
|
|||
|
|
'['+grd.Code+']-'+grd.[Description] GradeCodeName,empR.Position,
|
|||
|
|
empR.MaxEmployee,
|
|||
|
|
CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
|
|||
|
|
CASE
|
|||
|
|
When empR.Gender = 0
|
|||
|
|
then 'Regardless'
|
|||
|
|
When empR.Gender = 1
|
|||
|
|
then 'Male'
|
|||
|
|
ELSE 'Female'
|
|||
|
|
END as Gender,
|
|||
|
|
CASE
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 0
|
|||
|
|
then 'Sent by HR Head'
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 1
|
|||
|
|
then 'Declined'
|
|||
|
|
ELSE 'Approved'
|
|||
|
|
END as [Status]
|
|||
|
|
FROM EmployeeRequisition empR,Grades grd,Department dpt
|
|||
|
|
WHERE empR.[Status]>2 And empR.GradeID = grd.GradeID
|
|||
|
|
AND dpt.DEPARTMENTID = empR.DepartmentID
|
|||
|
|
Order By empR.DepartmentID");
|
|||
|
|
|
|||
|
|
empRequisitionDS = tc.ExecuteDataSet(sql);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return empRequisitionDS;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetEmployeeRequisitionsForHR(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
DataSet empRequisitionDS = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
string sql = string.Empty;
|
|||
|
|
sql = SQLParser.MakeSQL(@"select empR.EmpRequisitionID as ID,empR.DepartmentID,
|
|||
|
|
dpt.DESCRIPTION as [Department],
|
|||
|
|
'['+grd.Code+']-'+grd.[Description] GradeCodeName,empR.Position,
|
|||
|
|
empR.MaxEmployee,
|
|||
|
|
CONVERT(VARCHAR(30),empR.RequisitionDate,106) RequisitionDate,
|
|||
|
|
CASE
|
|||
|
|
When empR.Gender = 0
|
|||
|
|
then 'Regardless'
|
|||
|
|
When empR.Gender = 1
|
|||
|
|
then 'Male'
|
|||
|
|
ELSE 'Female'
|
|||
|
|
END as Gender,
|
|||
|
|
CASE
|
|||
|
|
When empR.[Status] = 2 and empR.IsCompleted = 0
|
|||
|
|
then 'Sent by Department Head'
|
|||
|
|
When empR.[Status] = 2 and empR.IsCompleted = 1
|
|||
|
|
then 'Declined'
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 0
|
|||
|
|
then 'Send to CEO'
|
|||
|
|
When empR.[Status] = 3 and empR.IsCompleted = 1
|
|||
|
|
then 'Declined by CEO'
|
|||
|
|
ELSE 'Approved by CEO'
|
|||
|
|
END as [Status]
|
|||
|
|
FROM EmployeeRequisition empR,Grades grd,Department dpt
|
|||
|
|
WHERE empR.[Status]>1 And empR.GradeID = grd.GradeID
|
|||
|
|
AND dpt.DEPARTMENTID = empR.DepartmentID
|
|||
|
|
Order By empR.DepartmentID");
|
|||
|
|
|
|||
|
|
empRequisitionDS = tc.ExecuteDataSet(sql);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
return empRequisitionDS;
|
|||
|
|
}
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert Functions
|
|||
|
|
|
|||
|
|
public static void Insert(TransactionContext tc, EmployeeRequisition oItem)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"INSERT INTO EmployeeRequisition
|
|||
|
|
(EmpRequisitionID,DepartmentID,RequisitionDate,Position,
|
|||
|
|
GradeID,MaxEmployee,BasicPay,JobDescription,
|
|||
|
|
Justification,Academic,Technical,Age,
|
|||
|
|
Experience,Gender,Areas,OtherRequirements,
|
|||
|
|
Status,IsCompleted,IsHeadCount,CreatedBy,CreationDate)" +
|
|||
|
|
@" VALUES(%n,%n,%d,%s,
|
|||
|
|
%n,%n,%s,%s,
|
|||
|
|
%s,%s,%s,%s,
|
|||
|
|
%s,%n,%s,%s,
|
|||
|
|
%n,%b,%b,%n,%d)",
|
|||
|
|
oItem.ID.Integer, oItem.DepartmentID.Integer, oItem.RequisitionDate, oItem.Position, oItem.GradeID.Integer, oItem.MaxEmployee, DataReader.GetNullValue(oItem.BasicPay), DataReader.GetNullValue(oItem.JobDescription),
|
|||
|
|
DataReader.GetNullValue(oItem.Justification), DataReader.GetNullValue(oItem.Academic), DataReader.GetNullValue(oItem.Technical), DataReader.GetNullValue(oItem.Age),
|
|||
|
|
DataReader.GetNullValue(oItem.Experience), (int)oItem.Gender, DataReader.GetNullValue(oItem.Areas), DataReader.GetNullValue(oItem.OtherRequirements),
|
|||
|
|
(int)oItem.Status, oItem.IsCompleted, oItem.IsHeadCount, oItem.CreatedBy.Integer, oItem.CreatedDate);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void InsertRequisitionUser(TransactionContext tc, RequisitionUser oItem)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("INSERT INTO RequisitionUser(RequisitionUserID,EmpRequisitionID,EmployeeID,Status,ChangeDate,ChangeString,Remarks)" +
|
|||
|
|
" VALUES(%n,%n,%n,%n,%d,%s,%s)", oItem.ID.Integer, oItem.EmpRequisitionID.Integer, oItem.EmployeeID.Integer, oItem.Status, oItem.ChangeDate, DataReader.GetNullValue(oItem.ChangeString), DataReader.GetNullValue(oItem.Remarks));
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update Functions
|
|||
|
|
|
|||
|
|
public static void Update(TransactionContext tc, EmployeeRequisition oItem)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"Update EmployeeRequisition SET DepartmentID=%n,RequisitionDate=%d,Position=%s,GradeID=%n, MaxEmployee=%n,BasicPay=%s,JobDescription=%s,Justification=%s,Academic=%s,Technical=%s,Age=%s,Experience=%s,Gender=%n,Areas=%s,OtherRequirements=%s,Status=%n,IsCompleted=%b,IsHeadCount=%b,CreatedBy=%n,CreationDate=%d" +
|
|||
|
|
" Where EmpRequisitionID = %n", oItem.DepartmentID.Integer, oItem.RequisitionDate, oItem.Position, oItem.GradeID.Integer, oItem.MaxEmployee, DataReader.GetNullValue(oItem.BasicPay), DataReader.GetNullValue(oItem.JobDescription), DataReader.GetNullValue(oItem.Justification), DataReader.GetNullValue(oItem.Academic), DataReader.GetNullValue(oItem.Technical), DataReader.GetNullValue(oItem.Age), DataReader.GetNullValue(oItem.Experience), (int)oItem.Gender, DataReader.GetNullValue(oItem.Areas), DataReader.GetNullValue(oItem.OtherRequirements), (int)oItem.Status, oItem.IsCompleted, oItem.IsHeadCount, oItem.CreatedBy.Integer, oItem.CreatedDate, oItem.ID.Integer);
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void ChangeStatus(TransactionContext tc, int empReqID, EnumEmpReqStatus enumEmpReqStatus)
|
|||
|
|
{
|
|||
|
|
string sql;
|
|||
|
|
if (enumEmpReqStatus == EnumEmpReqStatus.Approved)
|
|||
|
|
{
|
|||
|
|
sql = SQLParser.MakeSQL(@"Update EmployeeRequisition
|
|||
|
|
Set Status =%n , IsCompleted = 1
|
|||
|
|
Where EmpRequisitionID=%n", (int)enumEmpReqStatus, empReqID);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
sql = SQLParser.MakeSQL(@"Update EmployeeRequisition
|
|||
|
|
Set Status =%n
|
|||
|
|
Where EmpRequisitionID=%n", (int)enumEmpReqStatus, empReqID);
|
|||
|
|
}
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void RequisitionDeclinedByHR(TransactionContext tc, EmployeeRequisition employeeRequisition)
|
|||
|
|
{
|
|||
|
|
string sql;
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(@"Update EmployeeRequisition
|
|||
|
|
Set Status =2 , IsCompleted = 1
|
|||
|
|
Where EmpRequisitionID=%n",employeeRequisition.ID.Integer);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void RequisitionDeclinedByCEO(TransactionContext tc, EmployeeRequisition employeeRequisition)
|
|||
|
|
{
|
|||
|
|
string sql;
|
|||
|
|
|
|||
|
|
sql = SQLParser.MakeSQL(@"Update EmployeeRequisition
|
|||
|
|
Set Status =3 , IsCompleted = 1
|
|||
|
|
Where EmpRequisitionID=%n", employeeRequisition.ID.Integer);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete Functions
|
|||
|
|
|
|||
|
|
public static void Delete(TransactionContext tc, int id)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("Delete from EmployeeRequisition where EmpRequisitionID=%n", id);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void DeleteRequisitionUsers(TransactionContext tc, int empRequisitionID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("Delete from RequisitionUser Where EmpRequisitionID = %n", empRequisitionID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|