388 lines
16 KiB
C#
388 lines
16 KiB
C#
|
using HRM.BO;
|
||
|
|
using Ease.Core.DataAccess;
|
||
|
|
using System;
|
||
|
|
using System.Data;
|
||
|
|
|
||
|
|
|
||
|
|
namespace HRM.DA
|
||
|
|
{
|
||
|
|
#region LeaveProcess Data Access
|
||
|
|
|
||
|
|
public class LeaveProcessDA
|
||
|
|
{
|
||
|
|
#region Constructor
|
||
|
|
|
||
|
|
public LeaveProcessDA()
|
||
|
|
{
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Leave Process
|
||
|
|
|
||
|
|
#region Insert function
|
||
|
|
|
||
|
|
public static void Insert(TransactionContext tc, LeaveProcess oItem)
|
||
|
|
{
|
||
|
|
//oItem.ProcessYear = LeaveEntry.getJulyJuneLeaveYear(oItem.ProcessYear);
|
||
|
|
tc.ExecuteNonQuery(
|
||
|
|
"INSERT INTO LeaveProcess(ProcessId, LEAVEYEARID, ProcessDate, IsYearEnd, ProcessYearDesc, PayrollTypeID,createdBy, CreatedDate)" +
|
||
|
|
" VALUES(%n, %n, %d,%b, %s, %n,%n,%D)", oItem.ID, oItem.LeaveYearID,
|
||
|
|
oItem.ProcessDate, oItem.IsYearEnd, oItem.ProcessYearDescription, oItem.PayrollTypeID, oItem.CreatedBy, oItem.CreatedDate);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Update function
|
||
|
|
|
||
|
|
public static void Update(TransactionContext tc, LeaveProcess oItem)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery(
|
||
|
|
"UPDATE LeaveProcess SET LEAVEYEARID=%n, ProcessDate=%d, IsYearEnd=%b, ProcessYearDesc=%s " +
|
||
|
|
" WHERE ProcessId=%n", oItem.LeaveYearID, oItem.ProcessDate, oItem.IsYearEnd,
|
||
|
|
oItem.ProcessYearDescription, oItem.ID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void DoYearEnd(TransactionContext tc, LeaveProcess oProcess)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("UPDATE LeaveProcess SET IsYearEnd=%b WHERE ProcessId=%n", oProcess.IsYearEnd,
|
||
|
|
oProcess.ID);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region ID Generation function
|
||
|
|
|
||
|
|
public static int GetNewID(TransactionContext tc)
|
||
|
|
{
|
||
|
|
return tc.GenerateID("LeaveProcess", "ProcessId");
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Get Function
|
||
|
|
|
||
|
|
//public static IDataReader Get(TransactionContext tc)
|
||
|
|
//{
|
||
|
|
// return tc.ExecuteReader("SELECT * FROM LeaveProcess Where PayrollTypeID=%n", User.CurrentUser.LogInPayrollTypeID);
|
||
|
|
//}
|
||
|
|
public static IDataReader Get(TransactionContext tc, int nProcessId)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM LeaveProcess WHERE ProcessId=%n", nProcessId);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetByYearID(TransactionContext tc, int nProcessYear, int payrollTypeID)
|
||
|
|
{
|
||
|
|
//nProcessYear = 2010;// LeaveEntry.getJulyJuneLeaveYear(nProcessYear);
|
||
|
|
return tc.ExecuteReader("SELECT * FROM LeaveProcess WHERE LEAVEYEARID=%n and PayrollTypeID=%n",
|
||
|
|
nProcessYear, payrollTypeID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetByYearANDPayrollID(TransactionContext tc, int nProcessYear, int nPayrollTypeID)
|
||
|
|
{
|
||
|
|
//nProcessYear = 2010;// LeaveEntry.getJulyJuneLeaveYear(nProcessYear);
|
||
|
|
return tc.ExecuteReader("SELECT * FROM LeaveProcess WHERE LEAVEYEARID=%n and PayrollTypeID=%n",
|
||
|
|
nProcessYear, nPayrollTypeID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetLastProcess(TransactionContext tc, int payrollTypeID)
|
||
|
|
{
|
||
|
|
bool status = true;
|
||
|
|
int nProcessYear = 2010; //LeaveEntry.getJulyJuneLeaveYear(nProcessYear);
|
||
|
|
return tc.ExecuteReader(@"SELECT * FROM LeaveProcess WHERE LEAVEYEARID=(SELECT max(ProcessYear)
|
||
|
|
FROM LeaveProcess WHERE IsYearEnd=%b and PayrollTypeID=%n)", status, payrollTypeID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetYearEndValue(TransactionContext tc)
|
||
|
|
{
|
||
|
|
bool status = true;
|
||
|
|
return tc.ExecuteReader("SELECT * FROM LeaveYear WHERE ISCURRENT=%b ", status);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Delete function
|
||
|
|
|
||
|
|
public static void Delete(TransactionContext tc, int nProcessId)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM EmpLeaveStatus where ProcessId =%n", nProcessId);
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM LeaveProcess WHERE ProcessId=%n", nProcessId);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void DeleteByLeaveYear(TransactionContext tc, int leaveyearid, int payrolltypeid)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM EmpLeaveStatus where PROCESSID IN (select ProcessID FROM LeaveProcess WHERE LeaveYearID=%n and payrolltypeid=%n)", leaveyearid, payrolltypeid);
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM LeaveProcess WHERE LeaveYearID=%n and payrolltypeid=%n", leaveyearid, payrolltypeid);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void DeleteByProcessYear(TransactionContext tc, int nProcessYear)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery(
|
||
|
|
"DELETE FROM EmpLeaveStatus where ProcessId in(SELECT ProcessId from LeaveProcess where LeaveYearID=%n)",
|
||
|
|
nProcessYear);
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM LeaveProcess WHERE LeaveYearID=%n", nProcessYear);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void DeleteByProcessYearANDPayrollType(TransactionContext tc, int nProcessYear,
|
||
|
|
int nPayrollTypeID)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery(
|
||
|
|
"DELETE FROM EmpLeaveStatus where ProcessId in(SELECT ProcessId from LeaveProcess where LeaveYearID=%n AND PayrollTypeID=%n)",
|
||
|
|
nProcessYear, nPayrollTypeID);
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM LeaveProcess WHERE LeaveYearID=%n AND PayrollTypeID=%n", nProcessYear,
|
||
|
|
nPayrollTypeID);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Other supporting Functions
|
||
|
|
|
||
|
|
public static bool IsProcessed(TransactionContext tc, int nProcessYear, int payrollTypeID)
|
||
|
|
{
|
||
|
|
object obj = new object();
|
||
|
|
obj = tc.ExecuteScalar("SELECT count(*) FROM LEAVEYEAR WHERE ISENDED=1 AND LEAVEYEARID=%n and PayrollTypeid=%n ",
|
||
|
|
nProcessYear, payrollTypeID);
|
||
|
|
if (obj == DBNull.Value) return false;
|
||
|
|
return (Convert.ToInt32(obj) != 0);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Leave Process Detail
|
||
|
|
|
||
|
|
#region Insert function
|
||
|
|
|
||
|
|
public static void Insert(TransactionContext tc, EmpLeaveStatus oItem)
|
||
|
|
{
|
||
|
|
//oItem.ForBenifitedYear= LeaveEntry.getJulyJuneLeaveYear(oItem.ForBenifitedYear);
|
||
|
|
tc.ExecuteNonQuery(
|
||
|
|
"INSERT INTO EmpLeaveStatus(TranId, ProcessId, EmpId, LEAVEYEARID, LeaveId, FORBENIFITEDYEAR, CFDays, EncashDays, EncashAmount, NormalLeaveDays, YearEndBalance, LEAVEAVAILED,OPENINGBALANCE,FORFITEDDAYS)" +
|
||
|
|
" VALUES(%n, %n, %n, %n, %n,%n, %n, %n, %n, %n, %n,%n,%n,%n)", oItem.ID, oItem.ProcessId, oItem.EmpId,
|
||
|
|
oItem.LeaveYearID, oItem.LeaveId, oItem.CarryFromPrvYear, oItem.CFDays, oItem.EncashDays,
|
||
|
|
oItem.EncashAmount,
|
||
|
|
oItem.NormalLeaveDays, oItem.YearEndBalance, oItem.LeaveAvailed, oItem.OpeningBalance,
|
||
|
|
oItem.ForfitedDays);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Update function
|
||
|
|
|
||
|
|
public static void Update(TransactionContext tc, EmpLeaveStatus oItem)
|
||
|
|
{
|
||
|
|
//oItem.ForBenifitedYear = LeaveEntry.getJulyJuneLeaveYear(oItem.ForBenifitedYear);
|
||
|
|
tc.ExecuteNonQuery(
|
||
|
|
"UPDATE EmpLeaveStatus SET ProcessId=%n, EmpId=%n, LEAVEYEARID=%n, LeaveId=%n, CFDays=%n, EncashDays=%n, EncashAmount=%n, NormalLeaveDays=%n, YearEndBalance=%n,LEAVEAVAILED=%n,OPENINGBALANCE=%n,FORFITEDDAYS=%n" +
|
||
|
|
" WHERE TranId=%n", oItem.ProcessId, oItem.EmpId, oItem.LeaveYearID, oItem.LeaveId, oItem.CFDays, oItem.EncashDays, oItem.EncashAmount, oItem.NormalLeaveDays,
|
||
|
|
oItem.YearEndBalance, oItem.LeaveAvailed, oItem.OpeningBalance, oItem.ForfitedDays, oItem.ID);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region ID Generation function
|
||
|
|
|
||
|
|
public static int GetNewDetailID(TransactionContext tc)
|
||
|
|
{
|
||
|
|
return tc.GenerateID("EmpLeaveStatus", "TranId");
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Get Function
|
||
|
|
|
||
|
|
//public static IDataReader Get(TransactionContext tc)
|
||
|
|
//{
|
||
|
|
// return tc.ExecuteReader("SELECT * FROM EmpLeaveStatus");
|
||
|
|
//}
|
||
|
|
public static IDataReader GetStatus(TransactionContext tc, int empid, int leaveID, int leaveYearID)
|
||
|
|
{
|
||
|
|
//if(leaveID==0)
|
||
|
|
// return tc.ExecuteReader("SELECT * FROM EmpLeaveStatus where EMPID=%n AND LEAVEYEARID=%n",empid,leaveYearID);
|
||
|
|
|
||
|
|
return tc.ExecuteReader("SELECT * FROM EmpLeaveStatus where EMPID=%n AND LEAVEID=%n AND LEAVEYEARID=%n",
|
||
|
|
empid, leaveID, leaveYearID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetStatus(TransactionContext tc, int empid, int leaveYearID)
|
||
|
|
{
|
||
|
|
|
||
|
|
return tc.ExecuteReader("SELECT * FROM EmpLeaveStatus where EMPID=%n AND LEAVEYEARID=%n",
|
||
|
|
empid, leaveYearID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetProcessDetail(TransactionContext tc, int nTranId)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM EmpLeaveStatus WHERE TranId=%n", nTranId);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetAllStatus(TransactionContext tc, int empId)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM EmpLeaveStatus WHERE EMPID=%n", empId);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetByYear(TransactionContext tc, int ProcessYear, int LeaveId, int EmpId)
|
||
|
|
{
|
||
|
|
//ProcessYear = LeaveEntry.getJulyJuneLeaveYear(ProcessYear);
|
||
|
|
return tc.ExecuteReader(
|
||
|
|
"SELECT * FROM EmpLeaveStatus WHERE ProcessId in(select ProcessId from LeaveProcess where LEAVEYEARID=%n) and LeaveId=%n and EmpId=%n",
|
||
|
|
ProcessYear, LeaveId, EmpId);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetByYear(TransactionContext tc, int LeaveYearID)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM EmpLeaveStatus WHERE LEAVEYEARID=%n", LeaveYearID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetByEmpIDs(TransactionContext tc, string empIDs, int LeaveYearID)
|
||
|
|
{
|
||
|
|
string sql = SQLParser.MakeSQL(
|
||
|
|
"SELECT el.*, leave.Description FROM EmpLeaveStatus el Inner Join Employee e on e.employeeID=el.EmpID " +
|
||
|
|
" inner join leave on el.leaveid = leave.leaveid " +
|
||
|
|
"WHERE el.LEAVEYEARID=%n AND el.EMPID in (%q) Order by e.EmployeeNo asc ",
|
||
|
|
LeaveYearID, empIDs);
|
||
|
|
return tc.ExecuteReader(sql);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetByProcessId(TransactionContext tc, int nProcessId)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM EmpLeaveStatus WHERE ProcessId=%n", nProcessId);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Delete function
|
||
|
|
|
||
|
|
public static void DeleteProcessDetail(TransactionContext tc, int nTranId)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM EmpLeaveStatus WHERE TranId=%n", nTranId);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void Delete(TransactionContext tc, EmpLeaveStatus oItem)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM EmpLeaveStatus WHERE TranId=%n", oItem.ID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void DeleteByProcessId(TransactionContext tc, int nProcessId)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM EmpLeaveStatus WHERE ProcessId=%n", nProcessId);
|
||
|
|
}
|
||
|
|
|
||
|
|
internal static IDataReader GetByYearType(TransactionContext tc, int leaveYearID, int leaveId)
|
||
|
|
{
|
||
|
|
string Ssql = SQLParser.MakeSQL("SELECT * FROM EmpLeaveStatus WHERE LEAVEYEARID=%n And LEAVEID=%n",
|
||
|
|
leaveYearID, leaveId);
|
||
|
|
return tc.ExecuteReader(Ssql);
|
||
|
|
}
|
||
|
|
|
||
|
|
internal static void UpdateEncashAmount(TransactionContext tc, EmpLeaveStatus oStatus)
|
||
|
|
{
|
||
|
|
string sSQL = SQLParser.MakeSQL("UPDATE EmpLeaveStatus SET EncashAmount=%n WHERE TranId=%n",
|
||
|
|
oStatus.EncashAmount, oStatus.ID);
|
||
|
|
tc.ExecuteNonQuery(sSQL);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void DeleteByPayrollType(TransactionContext tc, int nProcessYear, int nPayrollTypeID,
|
||
|
|
int nLeaveID)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery(
|
||
|
|
"DELETE FROM EmpLeaveStatus where ProcessId in(SELECT ProcessId from LeaveProcess where LeaveYearID=%n AND PayrollTypeID=%n) AND LeaveID=%n",
|
||
|
|
nProcessYear, nPayrollTypeID, nLeaveID);
|
||
|
|
}
|
||
|
|
|
||
|
|
internal static DataSet GetEmpAllLeaveRelatedInfo(TransactionContext tc, int empID)
|
||
|
|
{
|
||
|
|
DataSet rootDataset = new DataSet();
|
||
|
|
DataSet tempdataset = new DataSet();
|
||
|
|
|
||
|
|
try
|
||
|
|
{
|
||
|
|
string query1 = SQLParser.MakeSQL(@"SELECT GETUTCDATE() sysdate ");
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query1);
|
||
|
|
tempdataset.Tables[0].TableName = "OperationDate";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
|
||
|
|
string query2 = SQLParser.MakeSQL(@"SELECT * FROM LeaveYear WHERE ISCURRENT=%b", true);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query2);
|
||
|
|
tempdataset.Tables[0].TableName = "CurrentLeaveYear";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query8 =
|
||
|
|
SQLParser.MakeSQL(@"SELECT top 1 * FROM LEAVEYear WHERE ISCURRENT=%b ORDER BY EndDate DESC", false);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query8);
|
||
|
|
tempdataset.Tables[0].TableName = "PreviousLeaveYear";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query3 = SQLParser.MakeSQL(@"SELECT DISTINCT TranType FROM LeaveSetupDetail");
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query3);
|
||
|
|
tempdataset.Tables[0].TableName = "LeaveSetupTypes";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query4 = SQLParser.MakeSQL(@"SELECT * FROM LeaveSetupDetail");
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query4);
|
||
|
|
tempdataset.Tables[0].TableName = "LeaveSetupDetails";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query5 = SQLParser.MakeSQL(@"SELECT * FROM LeaveParameter");
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query5);
|
||
|
|
tempdataset.Tables[0].TableName = "LeaveParameter";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query6 = SQLParser.MakeSQL(@"SELECT * FROM LeaveParamDetail");
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query6);
|
||
|
|
tempdataset.Tables[0].TableName = "LeaveParamDetail";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query7 = SQLParser.MakeSQL(
|
||
|
|
@"select LeaveYear, LeaveID,EmpId, Sum(APPTOTALDAYS) APRTOTALDAYS, LEAVESTATUS from LeaveEntry
|
||
|
|
where LeaveYear in (SELECT LeaveYearID FROM LeaveYear WHERE ISCURRENT=%b) and EmpId=%n AND LeaveStatus=%n group by LeaveYear, EmpId, LeaveID, LEAVESTATUS",
|
||
|
|
true, empID, (int)EnumLeaveStatus.Approved);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query7);
|
||
|
|
tempdataset.Tables[0].TableName = "AllAvailedLeave";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
int previousLeaveYearID = 0;
|
||
|
|
Object obj =
|
||
|
|
tc.ExecuteScalar("SELECT top 1 LEAVEYEARID FROM LEAVEYear WHERE ISCURRENT=%b ORDER BY EndDate DESC",
|
||
|
|
false);
|
||
|
|
previousLeaveYearID = Convert.ToInt32(obj);
|
||
|
|
|
||
|
|
string query9 =
|
||
|
|
SQLParser.MakeSQL(@"SELECT * FROM EmpLeaveStatus WHERE EmpId=%n and LEAVEYEARID IN (%n)", empID,
|
||
|
|
previousLeaveYearID);
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query9);
|
||
|
|
tempdataset.Tables[0].TableName = "PreviousYearStatus";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
|
||
|
|
string query10 = SQLParser.MakeSQL(@"SELECT * FROM Leave");
|
||
|
|
|
||
|
|
tempdataset = tc.ExecuteDataSet(query10);
|
||
|
|
tempdataset.Tables[0].TableName = "Leave";
|
||
|
|
rootDataset.Tables.Add(tempdataset.Tables[0].Copy());
|
||
|
|
}
|
||
|
|
catch (Exception ex)
|
||
|
|
{
|
||
|
|
throw new Exception(ex.Message);
|
||
|
|
}
|
||
|
|
|
||
|
|
return rootDataset;
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
}
|