109 lines
4.7 KiB
C#
109 lines
4.7 KiB
C#
using System;
|
|
using Payroll.BO;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using Ease.CoreV35.Model;
|
|
using System.Data.SqlClient;
|
|
using Ease.CoreV35.DataAccess;
|
|
using System.Collections.Generic;
|
|
using Ease.CoreV35.DataAccess.SQL;
|
|
|
|
namespace Payroll.Service
|
|
{
|
|
#region FSTranDA
|
|
|
|
internal class FSTranDA
|
|
{
|
|
#region Constructor
|
|
|
|
private FSTranDA() { }
|
|
|
|
#endregion
|
|
|
|
#region Insert function
|
|
|
|
internal static void Insert(TransactionContext tc, FSTran item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO FSTran(FSTranID, ChangeTaxAmount, EmployeeID, LastSalaryPaidMonth, NetAmount, NoticeDate, Remarks,SettlementDate,TaxAmount,CreatedBy,CreationDate,ShortNoticePeriod,DiscontinueDate,BatchNumber)" +
|
|
" VALUES(%n, %n,%n, %d, %n, %d, %s, %d, %n,%n,%d,%n,%d,%s)", item.ID.Integer, item.ChangeTaxAmount, item.EmployeeID.Integer, item.LastSalaryPaidMonth == DateTime.MinValue ? item.DiscontinueDate : item.LastSalaryPaidMonth, item.NetAmount, item.NoticeDate, item.Remarks, item.SettlementDate, item.TaxAmount, item.CreatedBy.Integer, item.CreatedDate, item.ShortNoticePeriod, item.DiscontinueDate, item.BatchNumber);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Update function
|
|
|
|
internal static void Update(TransactionContext tc, FSTran item)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE FSTran SET ChangeTaxAmount=%n, DiscontinueDate=%d, EmployeeID=%n, LastSalaryPaidMonth=%d, NetAmount=%n, NoticeDate=%d, Remarks=%s,SettlementDate=%d,TaxAmount=%n,ShortNoticePeriod=%n,DiscontinueDate=%d,BatchNumber=%s" +
|
|
"WHERE FSTranID=%n", item.ChangeTaxAmount, item.DiscontinueDate, item.EmployeeID, item.LastSalaryPaidMonth, item.NetAmount, item.NoticeDate, item.Remarks, item.SettlementDate, item.TaxAmount,item.ShortNoticePeriod,item.DiscontinueDate,item.BatchNumber, item.ID.Integer);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Get Function
|
|
internal static IDataReader Get(TransactionContext tc)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM FSTran Order By FSTranID");
|
|
}
|
|
internal static IDataReader Get(TransactionContext tc, EnumStatus status)
|
|
{
|
|
if (EnumStatus.Active == status)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM FSTran where Status=%n order by FSTranID", status);
|
|
}
|
|
else
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM FSTran order by FSTranID");
|
|
}
|
|
|
|
}
|
|
internal static IDataReader Get(TransactionContext tc, string sEmpIDs)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM FSTran Where EmployeeID in(%q)", sEmpIDs);
|
|
}
|
|
internal static IDataReader Get(TransactionContext tc, ID nID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM FSTran Where FSTranID=%n", nID.Integer);
|
|
}
|
|
internal static IDataReader Get(TransactionContext tc, int nEmpID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM FSTran Where EmployeeID=%n", nEmpID);
|
|
}
|
|
internal static IDataReader Get(TransactionContext tc, DateTime dFromDate, DateTime dToDate)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM FSTran Where SettlementDate between %d AND %d", dFromDate,dToDate);
|
|
}
|
|
internal static IDataReader Get(TransactionContext tc, DateTime dFromDate, DateTime dToDate, int nType, string sBatchNumber)
|
|
{
|
|
if (nType == 1)
|
|
return tc.ExecuteReader("SELECT * FROM FSTran Where settlementdate between %d AND %d", dFromDate.ToString("dd MMM yyyy"), dToDate.ToString("dd MMM yyyy"));
|
|
else if (nType == 2)
|
|
return tc.ExecuteReader("SELECT * FROM FSTran Where DiscontinueDate between %d AND %d", dFromDate.ToString("dd MMM yyyy"), dToDate.ToString("dd MMM yyyy"));
|
|
else
|
|
return tc.ExecuteReader("SELECT * FROM FSTran Where BatchNumber= %s", sBatchNumber);
|
|
}
|
|
internal static double GetPFAmount(SqlConnection connection, string sEmpNo)
|
|
{
|
|
object ob;
|
|
SqlCommand command = new SqlCommand("SELECT TotalPayment " +
|
|
" FROM FinalSettlement" +
|
|
" WHERE EmpCode=@ID", connection);
|
|
command.Parameters.Add("@ID", SqlDbType.VarChar).Value = sEmpNo;
|
|
ob= command.ExecuteScalar();
|
|
return ob == DBNull.Value ? 0 : Convert.ToDouble(ob);
|
|
}
|
|
#endregion
|
|
|
|
#region Delete function
|
|
|
|
internal static void Delete(TransactionContext tc, ID nID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM [FSTran] Where FSTranID=%n", nID.Integer);
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
|
|
#endregion
|
|
}
|