EchoTex_Payroll/HRM.DA/DA/PF/PFTransactionDA.cs

130 lines
5.4 KiB
C#
Raw Permalink Normal View History

2024-10-14 10:01:49 +06:00
using HRM.BO;
using Ease.Core.DataAccess;
using System;
using System.Data;
namespace HRM.DA
{
#region PFTransactionDA
internal class PFTransactionDA
{
#region Constructor
private PFTransactionDA()
{
}
#endregion
#region Insert function
internal static void Insert(TransactionContext tc, PFTransaction item)
{
tc.ExecuteNonQuery(
"INSERT INTO PFTransaction(PFTRANID, employeeID, tranDate, tranAmount, tranType, CreatedBy, CreationDate, USERID)" +
" VALUES(%n, %n, %d, %n, %n, %n, %d, %n)", item.ID, item.EmployeeID, item.MonthDate, item.TranAmount,
item.TranType, item.CreatedBy, item.CreatedDate, item.CreatedBy);
}
#endregion
#region Update function
internal static void Update(TransactionContext tc, PFTransaction item)
{
tc.ExecuteNonQuery("UPDATE PFTransaction SET tranAmount=%n, ModifiedBy=%n, ModifiedDate=%d" +
" WHERE employeeID=%n, tranDate=%d, tranType=%n", item.TranAmount, item.EmployeeID,
item.MonthDate, item.TranType
, item.ModifiedBy, item.ModifiedDate);
}
#endregion
#region Get Function
internal static IDataReader Get(TransactionContext tc)
{
return tc.ExecuteReader("SELECT * FROM PFTransaction");
}
internal static double GetPFAmount(TransactionContext tc, int nEmpID)
{
double nAmount = 0.0;
object obj = tc.ExecuteScalar("Select sum(tranAmount) from PFTransaction where employeeID=%n", nEmpID);
if (obj != null && obj.ToString() != "")
{
nAmount = Convert.ToDouble(obj);
}
else
{
nAmount = 0.0;
}
return nAmount;
}
internal static DataSet GetPFBalance(TransactionContext tc, int nEmpID, DateTime salaryMonth)
{
string sQl = SQLParser.MakeSQL(
"SELECT 1 TranTP, 'Own Contribution' DESCRIPTION, ISNULL(SUM(TranAmount),0) Amount FROM PFTransaction WHERE TranType=1 AND EmployeeID=%n and TranDate<%d" +
" UNION (SELECT 2 TranTP, 'Company Contribution' DESCRIPTION, ISNULL(SUM(TranAmount),0) Amount FROM PFTransaction WHERE TranType=2 AND EmployeeID=%n and TranDate<%d) " +
" UNION (SELECT 3 TranTP, 'Interest' DESCRIPTION, ISNULL(SUM(TranAmount),0) Amount FROM PFTransaction WHERE TranType=3 AND EmployeeID=%n and TranDate<%d)",
nEmpID, salaryMonth, nEmpID, salaryMonth, nEmpID, salaryMonth);
DataSet pfBalance = tc.ExecuteDataSet(sQl);
return pfBalance;
}
internal static DataSet GetPFBalance(TransactionContext tc, DateTime salaryMonth)
{
string sQl = SQLParser.MakeSQL(@"SELECT pf.EmployeeID, pf.pf,cpf.CPF,pfint.PFInt,cpfint.CPFInt from
(SELECT e.EmployeeID,SUM(p.TranAmount) PF FROM PFTransaction p, Employee e
WHERE e.EmployeeID=p.EmployeeID AND
e.JoiningDate<=%d AND p.TranType=1
GROUP BY e.EmployeeID) pf,
(SELECT e.EmployeeID,SUM(p.TranAmount) CPF FROM PFTransaction p, Employee e
WHERE e.EmployeeID=p.EmployeeID AND
e.JoiningDate<=%d AND p.TranType=2
GROUP BY e.EmployeeID)cpf,
(SELECT e.EmployeeID,SUM(p.TranAmount) PFInt FROM PFTransaction p, Employee e
WHERE e.EmployeeID=p.EmployeeID AND
e.JoiningDate<=%d AND p.TranType=4
GROUP BY e.EmployeeID)pfint,
(SELECT e.EmployeeID,SUM(p.TranAmount) CPFInt FROM PFTransaction p, Employee e
WHERE e.EmployeeID=p.EmployeeID AND
e.JoiningDate<=%d AND p.TranType=5
GROUP BY e.EmployeeID)cpfint
WHERE pf.EmployeeID=cpf.EmployeeID AND
cpf.EmployeeID=pfint.EmployeeID AND
pfint.EmployeeID=cpfint.EmployeeID", salaryMonth, salaryMonth, salaryMonth,
salaryMonth);
DataSet pfBalance = tc.ExecuteDataSet(sQl);
return pfBalance;
}
internal static DataSet GetPFBalanceBAT(TransactionContext tc, DateTime fromDate, DateTime endDate)
{
string sQl = SQLParser.MakeSQL(@"SELECT * FROM VWPFBALANCE WHERE TRANDATE =%d", fromDate);
DataSet pfBalance = tc.ExecuteDataSet(sQl);
return pfBalance;
}
#endregion
#region Delete function
internal static void Delete(TransactionContext tc, int employeeId, DateTime month, EnumPFTranType type)
{
tc.ExecuteNonQuery("DELETE FROM PFTransaction WHERE EmployeeId=%n AND trandate=%d AND tranType=%n",
employeeId, month, (int)type);
}
#endregion
}
#endregion
}