464 lines
24 KiB
C#
464 lines
24 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 IncomeTaxDA
|
|||
|
|
{
|
|||
|
|
#region Constructor
|
|||
|
|
|
|||
|
|
private IncomeTaxDA()
|
|||
|
|
{
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Insert function
|
|||
|
|
|
|||
|
|
internal static void Insert(TransactionContext tc, IncomeTax item, EnumIncomeTaxDataFrom saveto)
|
|||
|
|
{
|
|||
|
|
if (saveto != EnumIncomeTaxDataFrom.ProcessedData)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO %q ( employeeID, itemId, previousAmount, thisMonthAmount, projectedAmount, thisyeartotal, position, description, Itemcode, side)" +
|
|||
|
|
" VALUES( %n, %n, %n, %n, %n, %n, %n, %s, %n, %n)", IncomeTaxDA.GetTableName(saveto),
|
|||
|
|
item.EmployeeID, item.ItemID, item.PreviousAmount, item.ThisMonthAmount, item.ProjectedAmount,
|
|||
|
|
item.TotalAmount, item.Position, item.Description, item.ItemGroup, item.Side);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO %q ( employeeID, itemId, totalAmount, taxParamID, position, description, Itemcode, side, UserID)" +
|
|||
|
|
" VALUES(%n, %n, %n, %n, %n, %s, %n, %n, %n)", IncomeTaxDA.GetTableName(saveto),
|
|||
|
|
item.EmployeeID, item.ItemID,
|
|||
|
|
item.TotalAmount, item.TaxParameterID, item.Position,
|
|||
|
|
item.Description, item.ItemGroup, item.Side, item.CreatedBy);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void InsertforEdit(TransactionContext tc, IncomeTax item, EnumIncomeTaxDataFrom saveto)
|
|||
|
|
{
|
|||
|
|
if (saveto != EnumIncomeTaxDataFrom.ProcessedData)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO %q ( employeeID, itemId, previousAmount, thisMonthAmount, projectedAmount, thisyeartotal, position, description, Itemcode, side,ModifiedBy,ModifiedDate)" +
|
|||
|
|
" VALUES( %n, %n, %n, %n, %n, %n, %n, %s, %n, %n,%n,%d)", IncomeTaxDA.GetTableName(saveto),
|
|||
|
|
item.EmployeeID, item.ItemID, item.PreviousAmount, item.ThisMonthAmount, item.ProjectedAmount,
|
|||
|
|
item.TotalAmount, item.Position, item.Description, item.ItemGroup, item.Side, item.ModifiedBy,
|
|||
|
|
item.ModifiedDate);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"INSERT INTO %q ( employeeID, itemId, totalAmount, taxParamID, position, description, Itemcode, side, UserID,ModifiedBy,ModifiedDate)" +
|
|||
|
|
" VALUES(%n, %n, %n, %n, %n, %s, %n, %n, %n,%n,%d)", IncomeTaxDA.GetTableName(saveto),
|
|||
|
|
item.EmployeeID, item.ItemID,
|
|||
|
|
item.TotalAmount, item.TaxParameterID, item.Position,
|
|||
|
|
item.Description, item.ItemGroup, item.Side, item.CreatedBy, item.ModifiedBy, item.ModifiedDate);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Update function
|
|||
|
|
|
|||
|
|
internal static void Update(TransactionContext tc, IncomeTax item, EnumIncomeTaxDataFrom editto)
|
|||
|
|
{
|
|||
|
|
if (editto != EnumIncomeTaxDataFrom.ProcessedData)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(
|
|||
|
|
"UPDATE %q SET previousAmount=%n, thisMonthAmount=%n, projectedAmount=%n, thisyeartotal=%n, position=%n, description=%s, side=%n, ModifiedBy=%n, ModifiedDate=%d" +
|
|||
|
|
" WHERE EmployeeId=%n AND ItemCode=%n AND ItemId=%n", IncomeTaxDA.GetTableName(editto),
|
|||
|
|
item.PreviousAmount, item.ThisMonthAmount, item.ProjectedAmount,
|
|||
|
|
item.TotalAmount, item.Position,
|
|||
|
|
item.Description, item.Side, DataReader.GetNullValue(item.ModifiedBy),
|
|||
|
|
DataReader.GetNullValue(item.ModifiedDate), item.EmployeeID, item.ItemGroup, item.ItemID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(sql);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
//tc.ExecuteNonQuery(
|
|||
|
|
// "UPDATE %q SET totalAmount=%n, taxParamID=%n, position=%n, description=%s, side=%n, ModifiedBy=%n, ModifiedDate=%d" +
|
|||
|
|
// " WHERE EmployeeId=%n AND Itemcode=%n AND ItemId=%n", IncomeTaxDA.GetTableName(editto),
|
|||
|
|
// item.TotalAmount,
|
|||
|
|
// item.TaxParameterID, item.Position, item.Description,
|
|||
|
|
// item.Side, item.ModifiedBy, item.ModifiedDate,
|
|||
|
|
// item.EmployeeID, item.ItemGroup, item.ItemID);
|
|||
|
|
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"UPDATE %q SET totalAmount=%n, description=%s" +
|
|||
|
|
" WHERE EmployeeId=%n AND Itemcode=%n AND ItemId=%n", IncomeTaxDA.GetTableName(editto),
|
|||
|
|
item.TotalAmount,
|
|||
|
|
item.Description,
|
|||
|
|
item.EmployeeID, item.ItemGroup, item.ItemID);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static void UpdateTaxAmountFromSalary(TransactionContext tc, double nAmount, int employeeid)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE SalaryTempIT SET ThisMonthAmount=%n " +
|
|||
|
|
" WHERE EmployeeId=%n AND Itemcode=%n AND ItemId=%n", nAmount,
|
|||
|
|
employeeid, EnumIncomeTaxItemGroup.Tax_Deducted, (int)EnumIncomeTaxItemGroup.Tax_Deducted);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static void UpdateOT(TransactionContext tc, OTProcess otProcess)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE INCOMETAXTEMP SET ThisMonthAmount=%n " +
|
|||
|
|
" WHERE EmployeeId=%n AND Itemcode=%n AND ItemId=%n", otProcess.Amount,
|
|||
|
|
otProcess.EmployeeID, (int)EnumIncomeTaxItemGroup.TimeCard, 1);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void UndoOT(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("UPDATE INCOMETAXTEMP SET ThisMonthAmount=%n " +
|
|||
|
|
" WHERE Itemcode=%n AND ItemId=%n", 0, (int)EnumIncomeTaxItemGroup.TimeCard, 1);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Get Function
|
|||
|
|
|
|||
|
|
internal static IDataReader GetFromRunningYear(TransactionContext tc, int employeeId,
|
|||
|
|
EnumIncomeTaxDataFrom dataFrom)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM %q where employeeID=%n ORDER BY Position", GetTableName(dataFrom),
|
|||
|
|
employeeId);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader GetFromRunningYearByEmployeeIds(TransactionContext tc, string sEmployeeId,
|
|||
|
|
EnumIncomeTaxDataFrom dataFrom)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM %q where employeeID in (%q) ORDER BY Position", GetTableName(dataFrom),
|
|||
|
|
sEmployeeId);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static bool hasTaxDataInSalaryTemp(TransactionContext tc, int employeeId)
|
|||
|
|
{
|
|||
|
|
object isprocessed = tc.ExecuteScalar(@"select SUM(cnt) from (
|
|||
|
|
select count(*) cnt from SALARYTEMPIT WHERE EmployeeID =%n
|
|||
|
|
UNION
|
|||
|
|
select count(*) cnt from BONUSTEMPIT WHERE EmployeeID =%n ) a
|
|||
|
|
", employeeId, employeeId);
|
|||
|
|
if (isprocessed == DBNull.Value)
|
|||
|
|
return false;
|
|||
|
|
if (Convert.ToInt32(isprocessed) == 0) return false;
|
|||
|
|
else return true;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static EnumIncomeTaxDataFrom TaxDataFrom(TransactionContext tc, int employeeId)
|
|||
|
|
{
|
|||
|
|
object isprocessed = tc.ExecuteScalar(@"if exists(select * from SALARYTEMPIT where EmployeeID =%n ) select 3
|
|||
|
|
else begin if exists(select * from BONUSTEMPIT where EmployeeID =%n )
|
|||
|
|
select 4 else select 2 end ", employeeId, employeeId);
|
|||
|
|
if (isprocessed == DBNull.Value)
|
|||
|
|
return EnumIncomeTaxDataFrom.ProcessTempData;
|
|||
|
|
return (EnumIncomeTaxDataFrom) Convert.ToInt32(isprocessed);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static bool hasTaxDataInSalaryTemp(TransactionContext tc, string empids)
|
|||
|
|
{
|
|||
|
|
object isprocessed = tc.ExecuteScalar(@"select SUM(cnt) from(
|
|||
|
|
select count(*) cnt from SALARYTEMPIT WHERE EmployeeID IN (%q)
|
|||
|
|
UNION
|
|||
|
|
select count(*) cnt from BONUSTEMPIT WHERE EmployeeID IN (%q) ) a
|
|||
|
|
", empids, empids);
|
|||
|
|
if (isprocessed == DBNull.Value)
|
|||
|
|
return false;
|
|||
|
|
if (Convert.ToInt32(isprocessed) == 0) return false;
|
|||
|
|
else return true;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetByEmpID(TransactionContext tc, EnumIncomeTaxDataFrom dataFrom, int employeeId)
|
|||
|
|
{
|
|||
|
|
DataSet oIncomeTaxs = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
//oIncomeTaxs = tc.ExecuteDataSet("SELECT STI.EMPLOYEEID,STI.ITEMCODE,STI.ItemId,STI.DESCRIPTION IncomeHead,STI.PREVIOUSAMOUNT PaidTillDate,STI.THISMONTHAMOUNT ThisMonth,STI.PROJECTEDAMOUNT Projected,STI.THISYEARTOTAL AmountPerAnnum,STI.POSITION,STI.SIDE"
|
|||
|
|
// + " FROM SALARYTEMPIT STI WHERE STI.EMPLOYEEID=%n ORDER BY STI.SIDE", employeeId);
|
|||
|
|
|
|||
|
|
|
|||
|
|
oIncomeTaxs = tc.ExecuteDataSet(
|
|||
|
|
"SELECT * FROM (SELECT STI.EMPLOYEEID, STI.ITEMCODE, STI.ItemId, STI.DESCRIPTION IncomeHead, STI.PREVIOUSAMOUNT PaidTillDate,STI.THISMONTHAMOUNT ThisMonth,STI.PROJECTEDAMOUNT Projected,STI.THISYEARTOTAL AmountPerAnnum,STI.POSITION,STI.SIDE"
|
|||
|
|
+ " FROM %q STI WHERE STI.EMPLOYEEID=%n AND side != 7) rowData1 LEFT OUTER join"
|
|||
|
|
+ " (SELECT STI.ITEMCODE, (STI.PREVIOUSAMOUNT + STI.THISMONTHAMOUNT + STI.PROJECTEDAMOUNT) Exempted ,STI.SIDE"
|
|||
|
|
+ " FROM %q STI WHERE STI.EMPLOYEEID=%n AND side = 7) rowData2 ON rowData1.ITEMCODE - 1 = rowData2.ITEMCODE AND rowData1.Side + 1 = rowData2.Side AND rowData1.Side = 6 ORDER BY rowData1.Position",
|
|||
|
|
GetTableName(dataFrom), employeeId, GetTableName(dataFrom), employeeId);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return oIncomeTaxs;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetByEmpIDTaxTemp(TransactionContext tc, int employeeId, int nTaxparamID)
|
|||
|
|
{
|
|||
|
|
DataSet oIncomeTaxs = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
//oIncomeTaxs = tc.ExecuteDataSet("SELECT STI.EMPLOYEEID,STI.ITEMCODE,STI.ItemId,STI.DESCRIPTION IncomeHead,STI.PREVIOUSAMOUNT PaidTillDate,STI.THISMONTHAMOUNT ThisMonth,STI.PROJECTEDAMOUNT Projected,STI.THISYEARTOTAL AmountPerAnnum,STI.POSITION,STI.SIDE"
|
|||
|
|
// + " FROM SALARYTEMPIT STI WHERE STI.EMPLOYEEID=%n ORDER BY STI.SIDE", employeeId);
|
|||
|
|
|
|||
|
|
|
|||
|
|
oIncomeTaxs = tc.ExecuteDataSet("select * from IncomeTaxYearly where employeeID=%n and taxparamid=%n",
|
|||
|
|
employeeId, nTaxparamID);
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return oIncomeTaxs;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetEmpIDforOthTax(TransactionContext tc, EnumIncomeTaxDataFrom dataFrom, int employeeId)
|
|||
|
|
{
|
|||
|
|
DataSet oIncomeTaxs = new DataSet();
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
oIncomeTaxs = tc.ExecuteDataSet(
|
|||
|
|
"SELECT * FROM (SELECT STI.EMPLOYEEID, STI.ITEMCODE, STI.ItemId, STI.DESCRIPTION IncomeHead, STI.PREVIOUSAMOUNT PaidTillDate,STI.THISMONTHAMOUNT ThisMonth,STI.PROJECTEDAMOUNT Projected,STI.THISYEARTOTAL AmountPerAnnum,STI.POSITION,STI.SIDE"
|
|||
|
|
+ " FROM %q STI WHERE STI.EMPLOYEEID=%n AND side != 7) rowData1 LEFT OUTER join"
|
|||
|
|
+ " (SELECT STI.ITEMCODE, (STI.PREVIOUSAMOUNT + STI.THISMONTHAMOUNT + STI.PROJECTEDAMOUNT) Exempted ,STI.SIDE"
|
|||
|
|
+ " FROM %q STI WHERE STI.EMPLOYEEID=%n AND side = 7) rowData2 ON rowData1.ITEMCODE - 1 = rowData2.ITEMCODE AND rowData1.Side + 1 = rowData2.Side AND rowData1.Side = 6 ORDER BY rowData1.Position",
|
|||
|
|
GetTableName(dataFrom), employeeId, GetTableName(dataFrom), employeeId);
|
|||
|
|
}
|
|||
|
|
//rowData1.ItemCode desc
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
throw new Exception(ex.Message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return oIncomeTaxs;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteTempData(TransactionContext tc, EnumIncomeTaxDataFrom datafor, int nEmpID, EnumIncomeTaxItemGroup ItemGroup, int ItemID)
|
|||
|
|
{
|
|||
|
|
string sSQL = SQLParser.MakeSQL("DELETE FROM %q WHERE employeeID=%n and itemId=%n and Itemcode=%n", GetTableName(datafor), nEmpID, ItemID, ItemGroup);
|
|||
|
|
tc.ExecuteNonQuery(sSQL);
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumIncomeTaxDataFrom dataFrom, string sEmpIDs, int nTaxParamID)
|
|||
|
|
{
|
|||
|
|
if (EnumIncomeTaxDataFrom.SalaryITTempData == dataFrom || EnumIncomeTaxDataFrom.ProcessTempData == dataFrom)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM %q where employeeID IN(%q) ORDER BY Position,EMPLOYEEID", GetTableName(dataFrom), sEmpIDs);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM %q where employeeID IN(%q) and TAXPARAMID=%n ORDER BY EMPLOYEEID, Position", GetTableName(dataFrom), sEmpIDs, nTaxParamID);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumIncomeTaxDataFrom dataFrom, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
// do not change order by items, many function are using this order-by
|
|||
|
|
//string sql = SQLParser.MakeSQL("SELECT %q.* FROM %q, Employee where Employee.EmployeeId=%q.EmployeeID and "
|
|||
|
|
// + " Employee.PayrollTypeid=%n Order by Employee.EmployeeNo, position",
|
|||
|
|
// GetTableName(dataFrom), GetTableName(dataFrom), GetTableName(dataFrom), payrollTypeID);
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT %q.* FROM %q JOIN Employee ON Employee.EmployeeId=%q.EmployeeID where "
|
|||
|
|
+ " Employee.PayrollTypeid=%n Order by Employee.EmployeeNo, position",
|
|||
|
|
GetTableName(dataFrom), GetTableName(dataFrom), GetTableName(dataFrom), payrollTypeID);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumIncomeTaxDataFrom dataFrom, string empids,
|
|||
|
|
int payrollTypeID, int? taxparamid )
|
|||
|
|
{
|
|||
|
|
if (dataFrom == EnumIncomeTaxDataFrom.ProcessedData && taxparamid == null)
|
|||
|
|
throw new Exception("tax parameterid can't be null");
|
|||
|
|
|
|||
|
|
string sSql = SQLParser.MakeSQL(@"SELECT i.* FROM INCOMETAXTEMP i
|
|||
|
|
INNER JOIN EMPLOYEE e ON e.EMPLOYEEID=i.EMPLOYEEID
|
|||
|
|
WHERE e.PAYROLLTYPEID=%n AND i.EMPLOYEEID in (%q) ORDER BY i.ITEMID desc",
|
|||
|
|
payrollTypeID, empids);
|
|||
|
|
|
|||
|
|
return tc.ExecuteReader(sSql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumIncomeTaxDataFrom dataFrom, int nEmpID,
|
|||
|
|
int nTaxParamID)
|
|||
|
|
{
|
|||
|
|
if (EnumIncomeTaxDataFrom.BonusITTempData == dataFrom || EnumIncomeTaxDataFrom.SalaryITTempData == dataFrom || EnumIncomeTaxDataFrom.ProcessTempData == dataFrom)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL("SELECT * FROM %q where employeeID=%n ORDER BY Position",
|
|||
|
|
GetTableName(dataFrom), nEmpID);
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM %q where employeeID=%n ORDER BY Position",
|
|||
|
|
GetTableName(dataFrom), nEmpID);
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
string sql =
|
|||
|
|
SQLParser.MakeSQL("SELECT * FROM %q where employeeID=%n and TAXPARAMID=%n ORDER BY Position",
|
|||
|
|
GetTableName(dataFrom), nEmpID, nTaxParamID);
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM %q where employeeID=%n and TAXPARAMID=%n ORDER BY Position",
|
|||
|
|
GetTableName(dataFrom), nEmpID, nTaxParamID);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumIncomeTaxDataFrom dataFrom, int nEmpID,
|
|||
|
|
EnumIncomeTaxItemGroup groupCode, int itemID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM %q where EmployeeID=%n AND ItemCode=%n AND itemId=%n",
|
|||
|
|
GetTableName(dataFrom),
|
|||
|
|
nEmpID, groupCode, itemID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader Get(TransactionContext tc, EnumIncomeTaxDataFrom dataFrom, string sEmpIds,
|
|||
|
|
EnumIncomeTaxItemGroup groupCode, int itemID)
|
|||
|
|
{
|
|||
|
|
return tc.ExecuteReader("SELECT * FROM %q where EmployeeID IN (%q) AND ItemCode=%n AND itemId=%n",
|
|||
|
|
GetTableName(dataFrom),
|
|||
|
|
sEmpIds, groupCode, itemID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetPrvYear(TransactionContext tc,
|
|||
|
|
EnumIncomeTaxDataFrom dataFrom, int parameterId,
|
|||
|
|
EnumIncomeTaxItemGroup groupCode, int ItemId)
|
|||
|
|
{
|
|||
|
|
string sql = SQLParser.MakeSQL(@"SELECT * FROM %q where taxparamid=%n and ItemCode=%n, itemId=%n",
|
|||
|
|
GetTableName(dataFrom),
|
|||
|
|
parameterId, groupCode, ItemId);
|
|||
|
|
return tc.ExecuteReader(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetPrvYear(TransactionContext tc, int parameterId, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
// Please do not change/remove Order by Items, many function has been written on this order by.
|
|||
|
|
return tc.ExecuteReader("SELECT %q.* FROM %q, Employee where Employee.EmployeeId=%q.EmployeeID and "
|
|||
|
|
+ " Employee.PayrollTypeid=%n and taxparamid=%n Order by Employee.EmployeeNo, position",
|
|||
|
|
GetTableName(EnumIncomeTaxDataFrom.ProcessedData), GetTableName(EnumIncomeTaxDataFrom.ProcessedData),
|
|||
|
|
GetTableName(EnumIncomeTaxDataFrom.ProcessedData), payrollTypeID, parameterId);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public static string GetTableName(EnumIncomeTaxDataFrom dataFrom)
|
|||
|
|
{
|
|||
|
|
string tableName = "INCOMETAXTEMP";
|
|||
|
|
switch (dataFrom)
|
|||
|
|
{
|
|||
|
|
case EnumIncomeTaxDataFrom.ProcessedData:
|
|||
|
|
tableName = "INCOMETAXYEARLY";
|
|||
|
|
break;
|
|||
|
|
case EnumIncomeTaxDataFrom.ProcessTempData:
|
|||
|
|
tableName = "INCOMETAXTEMP";
|
|||
|
|
break;
|
|||
|
|
case EnumIncomeTaxDataFrom.SalaryITTempData:
|
|||
|
|
tableName = "SALARYTEMPIT";
|
|||
|
|
break;
|
|||
|
|
case EnumIncomeTaxDataFrom.BonusITTempData:
|
|||
|
|
tableName = "BONUSTEMPIT";
|
|||
|
|
break;
|
|||
|
|
default:
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return tableName;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
#region Delete function
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, int nEmpID, EnumIncomeTaxDataFrom datafor,
|
|||
|
|
EnumIncomeTaxItemGroup ItemGroup, int ItemID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM %q WHERE employeeID=%n and itemId=%n and Itemcode=%n",
|
|||
|
|
GetTableName(datafor), nEmpID, ItemID, ItemGroup);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void Delete(TransactionContext tc, int nEmpID, EnumIncomeTaxDataFrom deletefrom)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM %q WHERE employeeID=%n ", GetTableName(deletefrom), nEmpID);
|
|||
|
|
}
|
|||
|
|
internal static void Delete(TransactionContext tc, string EmpIDs, EnumIncomeTaxDataFrom deletefrom)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM %q WHERE employeeID in (%q) ", GetTableName(deletefrom), EmpIDs);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteYearlyData(TransactionContext tc, int nTaxParamID, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"DELETE FROM %q WHERE taxParamID=%n AND EmployeeID In ( Select employeeID From Employee Where PayrollTypeID=%n)",
|
|||
|
|
GetTableName(EnumIncomeTaxDataFrom.ProcessedData), nTaxParamID, payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteTaxTempData(TransactionContext tc, EnumIncomeTaxDataFrom datafrom, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"DELETE FROM %q WHERE EmployeeID In ( Select employeeID From Employee Where PayrollTypeID=%n)",
|
|||
|
|
GetTableName(datafrom), payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteCurrYearData(TransactionContext tc, int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery(
|
|||
|
|
"DELETE FROM %q WHERE EmployeeID In ( Select employeeID From Employee Where PayrollTypeID=%n)",
|
|||
|
|
GetTableName(EnumIncomeTaxDataFrom.ProcessTempData), payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
internal static void DeleteYearlyData(TransactionContext tc, int nTaxParamID, EnumIncomeTaxDataFrom datafor,
|
|||
|
|
int nEmpID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM %q WHERE TaxParamID=%n and employeeID=%n ", GetTableName(datafor),
|
|||
|
|
nTaxParamID, nEmpID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteYearlyData(TransactionContext tc, int nTaxParamID, EnumIncomeTaxDataFrom datafor,
|
|||
|
|
int nEmpID, EnumIncomeTaxItemGroup ItemGroup, int ItemID)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM %q WHERE TaxParamID=%n and employeeID=%n and itemId=%n and Itemcode=%n",
|
|||
|
|
GetTableName(datafor), nTaxParamID, nEmpID, ItemID, ItemGroup);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static void DeleteAll(TransactionContext tc)
|
|||
|
|
{
|
|||
|
|
tc.ExecuteNonQuery("DELETE FROM INCOMETAXTEMP");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static IDataReader GetByYear(TransactionContext tc, int parameterId, EnumIncomeTaxDataFrom dataFrom,
|
|||
|
|
int payrollTypeID)
|
|||
|
|
{
|
|||
|
|
// Please do not change/remove Order by Items, many function has been written on this order by.
|
|||
|
|
return tc.ExecuteReader("SELECT %q.* FROM %q, Employee where Employee.EmployeeId=%q.EmployeeID and "
|
|||
|
|
+ " Employee.PayrollTypeid=%n Order by Employee.EmployeeNo, position",
|
|||
|
|
GetTableName(dataFrom), GetTableName(dataFrom), GetTableName(dataFrom), payrollTypeID);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
internal static DataSet GetTaxRefreshedData(TransactionContext tc, DateTime fromDate, DateTime toDate)
|
|||
|
|
{
|
|||
|
|
// Please do not change/remove Order by Items, many function has been written on this order by.
|
|||
|
|
// return tc.ExecuteDataSet(@" SELECT s.EMPLOYEEID, sd.ITEMCODE, sd.ITEMID, sum(changedAmount) AMOUNT, 'salary' DTYPE FROM SALARYMONTHLY s, salarymonthlydetail sd WHERE s.SALARYMONTHLYID=sd.SALARYMONTHLYID and
|
|||
|
|
// s.SALARYMONTH between %d AND %d AND sd.ITEMGROUP =1 GROUP BY s.EMPLOYEEID, sd.ITEMID , sd.ITEMCODE
|
|||
|
|
// union
|
|||
|
|
// SELECT s.EMPLOYEEID, sd.ITEMCODE, sd.ITEMID, sum(changedAmount) AMOUNT, 'tax' FROM SALARYMONTHLY s, salarymonthlydetail sd WHERE s.SALARYMONTHLYID=sd.SALARYMONTHLYID and
|
|||
|
|
// s.SALARYMONTH between %d AND %d AND sd.ITEMGROUP =3 AND (itemid=-129 OR itemid=-128) GROUP BY s.EMPLOYEEID, sd.ITEMID , sd.ITEMCODE
|
|||
|
|
// union
|
|||
|
|
// SELECT b.EMPLOYEEID, 0, b.BONUSID, sum(b.CHANGEBONUSAMOUNT) AMOUNT, 'bonus' FROM BONUSPROCESSDETAIL b WHERE b.DISBURSEDATE between %d AND %d GROUP BY b.EMPLOYEEID, b.BONUSID ORDER BY EMPLOYEEID
|
|||
|
|
//
|
|||
|
|
// ", fromDate,toDate,fromDate,toDate,fromDate,toDate);
|
|||
|
|
return tc.ExecuteDataSet(
|
|||
|
|
@" SELECT s.EMPLOYEEID, sd.ITEMCODE, sd.ITEMID, sum(changedAmount) AMOUNT, 'salary' DTYPE FROM SALARYMONTHLY s, salarymonthlydetail sd WHERE s.SALARYMONTHLYID=sd.SALARYMONTHLYID and
|
|||
|
|
s.SALARYMONTH between %d AND %d AND sd.ITEMGROUP =1 GROUP BY s.EMPLOYEEID, sd.ITEMID , sd.ITEMCODE
|
|||
|
|
union
|
|||
|
|
SELECT s.EMPLOYEEID, sd.ITEMCODE, sd.ITEMID, sum(changedAmount) AMOUNT, 'tax' FROM SALARYMONTHLY s, salarymonthlydetail sd WHERE s.SALARYMONTHLYID=sd.SALARYMONTHLYID and
|
|||
|
|
s.SALARYMONTH between %d AND %d AND sd.ITEMGROUP =3 AND (ITEMCODE=-129 OR ITEMCODE=-128) GROUP BY s.EMPLOYEEID, sd.ITEMID , sd.ITEMCODE
|
|||
|
|
union
|
|||
|
|
SELECT b.EMPLOYEEID, 0, b.BONUSID, sum(b.CHANGEBONUSAMOUNT) AMOUNT, 'bonus' FROM BONUSPROCESSDETAIL b WHERE b.DISBURSEDATE between %d AND %d GROUP BY b.EMPLOYEEID, b.BONUSID ORDER BY EMPLOYEEID
|
|||
|
|
", fromDate, toDate, fromDate, toDate, fromDate, toDate);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|