97 lines
4.6 KiB
C#
97 lines
4.6 KiB
C#
using Ease.Core.DataAccess;
|
|
using HRM.BO;
|
|
using System;
|
|
using System.Data;
|
|
|
|
namespace HRM.DA
|
|
{
|
|
internal class EmployeeConfirmationDA
|
|
{
|
|
#region Get
|
|
|
|
internal static IDataReader Get(TransactionContext tc, int ID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpConfirmation where EmpConfirmID=%n", ID);
|
|
}
|
|
|
|
internal static IDataReader GetByEmployeeID(TransactionContext tc, int ID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpConfirmation where EmployeeID=%n", ID);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpConfirmation");
|
|
}
|
|
internal static DataTable GetUpComingConfirmation(TransactionContext tc, DateTime Confirmmonth)
|
|
{
|
|
return tc.ExecuteDataTable(@"select a.*, b.EmpConfirmID, 0 month, b.Month prvExteded, a.CONFIRMMONTHDURATION confirmDuration, b.ConfirmDate from (
|
|
select e.EMPLOYEEID, e.EMPLOYEENO, g.confirmMonthDuration, e.NAME, e.JOININGDATE,
|
|
DATEDIFF(mm, e.JOININGDATE, GetDate()) --+1
|
|
duration ,
|
|
( select lm.employeeno +' ' + lm.name from employee lm where lm.employeeid = e.linemanagerid) lineManager
|
|
from Employee e, GRADES g where e.GRADEID = g.GRADEID
|
|
and e.STATUS =1 and DATEDIFF(mm, e.JOININGDATE, %d) +1 > g.confirmMonthDuration
|
|
and g.confirmMonthDuration !=0 and e.DATEOFCONFIRMATION is null ) a
|
|
left join (
|
|
select c.EmpConfirmID, c.EmployeeID, c.Month, c.ConfirmDate from EmpConfirmation c, EMPLOYEE e where c.EmployeeID = e.EMPLOYEEID
|
|
and e.DATEOFCONFIRMATION is null and e.STATUS =1) b on a.EMPLOYEEID = b.EmployeeID", Confirmmonth);
|
|
}
|
|
internal static DataTable GetByDateRange(TransactionContext tc, DateTime fromDate, DateTime toDate, int payrollTypeId)
|
|
{
|
|
DataSet ds = tc.ExecuteDataSet(@"SELECT TConf.EmpConfirmID,TConf.EmployeeID,TConf.[Month],
|
|
Convert(varchar(20),TConf.ConfirmDate,106) ConfirmDate,
|
|
TConf.CreatedBy,Convert(varchar(20),TConf.CreationDate,106) CreationDate,
|
|
TConf.ModifiedBy,Convert(varchar(20),TConf.ModifiedDate,106) ModifiedDate,
|
|
TEmp.EMPLOYEENO,TEmp.NAME As EmpName,
|
|
Convert(varchar(20), TEmp.JOININGDATE,106) JOININGDATE
|
|
FROM
|
|
(SELECT * FROM EmpConfirmation
|
|
Where ConfirmDate between %d and %d) AS TConf
|
|
INNER JOIN
|
|
(SELECT * FROM EMPLOYEE
|
|
WHERE STATUS = %n AND ISCONFIRMED = 0 AND PAYROLLTYPEID = %n) AS TEmp
|
|
ON TEmp.EMPLOYEEID = TConf.EmployeeID", fromDate, toDate, (int)EnumEmployeeStatus.Live, payrollTypeId);
|
|
|
|
return ds.Tables[0];
|
|
}
|
|
|
|
internal static void DeleteByEmployeeID(TransactionContext tc, int ID)
|
|
{
|
|
tc.ExecuteNonQuery("Delete FROM EmpConfirmation where EmployeeID=%n", ID);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Insert & Update
|
|
|
|
internal static void Insert(TransactionContext tc, EmployeeConfirmation item)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(
|
|
"INSERT INTO EmpConfirmation(EmpConfirmID, EmployeeID, Month, ConfirmDate, CreatedBy,CreationDate)" +
|
|
" VALUES(%n, %n, %n, %d , %n, %d)", item.ID, item.EmployeeID, item.Month, item.ConfirmDate,
|
|
DataReader.GetNullValue(item.CreatedBy), DataReader.GetNullValue(item.CreatedDate));
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
|
|
internal static void Update(TransactionContext tc, EmployeeConfirmation item)
|
|
{
|
|
string sSQL = SQLParser.MakeSQL(
|
|
"UPDATE EmpConfirmation SET EmployeeID=%n, Month=%n, ConfirmDate=%d, ModifiedBy=%n, ModifiedDate=%d" +
|
|
" WHERE EmpConfirmID=%n", item.EmployeeID, item.Month, item.ConfirmDate,
|
|
DataReader.GetNullValue(item.ModifiedBy), DataReader.GetNullValue(item.ModifiedDate), item.ID);
|
|
tc.ExecuteNonQuery(sSQL);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Delete
|
|
|
|
internal static void Delete(TransactionContext tc, int id)
|
|
{
|
|
tc.ExecuteNonQuery("Delete FROM EmpConfirmation where EmpConfirmID=%n", id);
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
} |