125 lines
4.8 KiB
C#
125 lines
4.8 KiB
C#
using System;
|
|
using Ease.Core.Model;
|
|
using Ease.Core.DataAccess;
|
|
using HRM.BO;
|
|
using Ease.Core.Utility;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
|
|
namespace HRM.DA
|
|
{
|
|
internal class TaxInvestmentDA
|
|
{
|
|
#region Constructor
|
|
|
|
private TaxInvestmentDA()
|
|
{
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Insert function
|
|
|
|
internal static void Insert(TransactionContext tc, TaxInvestment item)
|
|
{
|
|
tc.ExecuteNonQuery(
|
|
"INSERT INTO INVTYPE(TypeID, typeCode, typeName, CreatedBy, CreationDate, SequenceNo, Status,MaxLimit)" +
|
|
" VALUES(%n, %s, %s, %n, %d, %n, %n,%n)", item.ID, item.Code, item.Name, item.CreatedBy, item.CreatedDate,
|
|
item.Sequence, item.Status, item.MaxLimit);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Update function
|
|
|
|
internal static void Update(TransactionContext tc, TaxInvestment item)
|
|
{
|
|
tc.ExecuteNonQuery(
|
|
"UPDATE INVTYPE SET typeCode=%s, typeName=%s, ModifiedBy=%n, ModifiedDate=%d, SequenceNo=%n, Status=%n,MaxLimit=%n" +
|
|
" WHERE TypeID=%n", item.Code, item.Name, item.ModifiedBy, item.ModifiedDate, item.Sequence,
|
|
item.Status,item.MaxLimit, item.ID);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Get Function
|
|
|
|
internal static IDataReader Get(TransactionContext tc, EnumStatus status)
|
|
{
|
|
if (EnumStatus.Active == status || EnumStatus.Inactive == status)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM INVTYPE where Status=%n Order By SequenceNo", status);
|
|
}
|
|
else
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM INVTYPE Order By SequenceNo");
|
|
}
|
|
}
|
|
|
|
internal static DataTable GetInfoForAdmin(TransactionContext tc, int taxParamId, int entryFrom, int taxEffectDone)
|
|
{
|
|
string subSql = String.Empty;
|
|
if (entryFrom > 0)
|
|
{
|
|
subSql += SQLParser.MakeSQL("And invest.entryFrom = %n ", entryFrom);
|
|
}
|
|
subSql += SQLParser.MakeSQL("And invest.taxEffectDone = %n", taxEffectDone);
|
|
string sql = SQLParser.MakeSQL(
|
|
@"SELECT invest.INVESTMENTID InvestmentId, e.Name EmployeeName, e.EmployeeNo EmployeeNo, iType.TYPENAME, invest.AMOUNT Amount, d.NAME Designation , invest.entryFrom EntryFrom, invest.taxeffectDone submitStatus
|
|
FROM ITINVESTMENT invest
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = invest.EMPLOYEEID
|
|
LEFT JOIN INVTYPE iType ON invest.typeId = iType.typeId
|
|
LEFT JOIN DESIGNATION d ON d.DESIGNATIONID = e.DESIGNATIONID
|
|
where invest.TAXPARAMID = %n %q order by invest.InvestmentId desc, e.EmployeeNo, iType.TYPENAME asc", taxParamId, subSql);
|
|
|
|
return tc.ExecuteDataTable(sql);
|
|
}
|
|
|
|
internal static DataTable GetInvestmentDetailAdmin(TransactionContext tc, int taxParamId)
|
|
{
|
|
string sql = SQLParser.MakeSQL(
|
|
@"SELECT e.Name AS EmployeeName, e.EmployeeNo AS EmployeeNo, iType.TYPENAME AS TypeName, d.NAME AS Designation,
|
|
SUM(invest.AMOUNT) AS TotalAmount, iType.MaxLimit AS Limit,
|
|
CASE
|
|
WHEN iType.MaxLimit > 100 THEN
|
|
CASE
|
|
WHEN iType.MaxLimit > SUM(invest.AMOUNT) THEN SUM(invest.AMOUNT)
|
|
ELSE iType.MaxLimit
|
|
END
|
|
ELSE iType.MaxLimit
|
|
END AS InvestmentConsider
|
|
|
|
FROM ITINVESTMENT invest
|
|
LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID = invest.EMPLOYEEID
|
|
LEFT JOIN INVTYPE iType ON invest.typeId = iType.typeId
|
|
LEFT JOIN DESIGNATION d ON d.DESIGNATIONID = e.DESIGNATIONID
|
|
WHERE invest.TAXPARAMID = %n
|
|
|
|
GROUP BY invest.typeId, e.Name, e.EMPLOYEEID, e.EmployeeNo, iType.TYPENAME, iType.TYPEID, d.NAME, iType.MaxLimit
|
|
ORDER BY e.EmployeeNo ASC, iType.TYPENAME ASC;", taxParamId);
|
|
|
|
return tc.ExecuteDataTable(sql);
|
|
}
|
|
|
|
internal static IDataReader Get(TransactionContext tc, int nID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM INVTYPE WHERE TypeID=%n", nID);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Delete function
|
|
|
|
internal static void Delete(TransactionContext tc, int nID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM INVTYPE WHERE TypeID=%n", nID);
|
|
}
|
|
|
|
internal static void DeleteInvestment(TransactionContext tc, int nID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM ITINVESTMENT WHERE INVESTMENTID=%n", nID);
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
} |